MySQL性能优化

wget -O install.sh https://gitee.com/hsowan/ausi/raw/master/install.sh && sudo bash install.sh xenial

sudo apt install mysql-server mysql-client

mysql -uroot -p # 进入mysql

# 修改root用户的host, 避免在其他主机上无法登录, 即远程登录
update mysql.user set host = '%' where user = 'root' and host = 'localhost';
flush privileges; # 上一步操作后必须使用该命令进行刷新

# 修改root的密码的几种方式
# 第一种
alter user 'root'@'%' identified by '';
# 第二种, 这种方式需要刷新权限
update user set authentication_string = password('') where user = 'root' and host = '%';
flush privileges;
# 第三种
set password for 'root'@'%' = password('');

# 忘记密码, 在配置文件(vi /etc/mysql/my.cnf)中添加以下内容
# 则可以不用密码使用root登录, 再做修改密码的操作
[mysqld]
skip-grant-tables

# 查看mysql读取配置文件的顺序, 靠后的配置文件会覆盖前面的配置文件
/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
# /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

show variables # 查看mysql的配置参数

set slow_query_log = on; # 开启慢查询日志
set slow_query_log_file = /var/lib/mysql/slow-query.log; # 设置慢查询日志的文件
set log_queries_not_using_indexes = on; # 记录那些未使用索引的查询日志
# 上面的设置只针对当前会话有效
# mysql5可以使用global设置当前mysql服务有效(set global slow_query_log = on;), 意味着重启mysql服务这些将会恢复默认
# mysql8可以使用persist进行持久化设置, 即使重启mysql服务也有效

\s 查看mysql版本信息
\G 输出结果旋转90度

sudo apt install perl \
git clone git@gitee.com:mirrors/percona-toolkit.git \
perl Makefile.PL \
make \
make test \
make install

mysqldumpslow /var/lib/mysql/slow-query.log

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=200.0 (200), root[root]@localhost
  select first_name from sakila.actor

pt-query-digest /var/lib/mysql/slow-query.log

# Query 6: 0 QPS, 0x concurrency, ID 0x68A4975F6684AA2CA7BDB4CD86DEDB2C at byte 5816
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-06-17T13:39:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3       1
# Exec time      3   365us   365us   365us   365us   365us       0   365us
# Lock time      4    98us    98us    98us    98us    98us       0    98us
# Rows sent     44     200     200     200     200     200       0     200
# Rows examine   5     200     200     200     200     200       0     200
# Query size     2      19      19      19      19      19       0      19
# String:
# Databases    sakila
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sakila` LIKE 'actor'\G
#    SHOW CREATE TABLE `sakila`.`actor`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from actor\G

mysql> explain select customer_id, first_name, last_name from sakila.customer;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  599 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

# 查询最近一次支付的时间
mysql> select max(payment_date) from sakila.payment;
+---------------------+
| max(payment_date)   |
+---------------------+
| 2006-02-14 15:16:03 |
+---------------------+
1 row in set (0.01 sec)

# 查看sql的执行计划
mysql> explain select max(payment_date) from sakila.payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 很明显这里进行了全表扫描, 所以我们需要在payment_date上建立索引
create index idx_payment_date on sakila.payment(payment_date);

# 再次查看sql的执行计划, 可以看到是直接命中查询结果的
mysql> explain select max(payment_date) from sakila.payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)