MySQL 优化。

MySQL 优化。

---

---

数据库优化目的。

• 避免出现页面访问错误。

由于数据库连接 timeout 产生页面 5xx 错误。
由于慢查询造成页面无法加载。
由于阻塞造成数据无法提交。

• 增加数据库的稳定性。

很多数据库问题都是低效查询引起的。

• 优化用户体验。

流畅页面的访问速度。
良好的网站功能体验。

---

sakila 数据库 sample。

https://dev.mysql.com/doc/sakila/en/sakila-installation.html

慢查询。

• 开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/using-system-variables.html

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_queries_not_using_indexes = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.01 sec)

mysql> 

• 测试。

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from payment;

• 慢查询日志位置。

mysql> show variables like'slow%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | ON                          |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

• 查看慢查询日志。

[root@192 ~]# cat /var/lib/mysql/192-slow.log 
/usr/sbin/mysqld, Version: 5.7.29 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2020-05-05T08:13:34.390778Z
# User@Host: root[root] @ localhost []  Id:    48
# Query_time: 0.050212  Lock_time: 0.000107 Rows_sent: 16049  Rows_examined: 16049
use sakila;
SET timestamp=1588666414;
select * from payment;

---

慢查询日志分析工具 ~ mysqldumpslow。

[root@192 ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

[root@192 ~]# 

[root@192 ~]# mysqldumpslow -t 3 /var/lib/mysql/192-slow.log 

Reading mysql slow query log from /var/lib/mysql/192-slow.log
Count: 1  Time=0.05s (0s)  Lock=0.00s (0s)  Rows=16049.0 (16049), root[root]@localhost
  select * from payment

Died at /usr/bin/mysqldumpslow line 167, <> chunk 1.

---

慢查询日志分析工具 ~ pt-query-digest。

https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

https://www.percona.com/downloads/percona-toolkit/LATEST/

---

优化。

通过慢日志发现有问题的 sql。

• 查询次数多且每次查询占用时间长的 sql。

通常为 pt-query-digest 分析的闪几个查询。

• IO 大的 sql。

pt-query-digest 分析中的 Rows examine。

• 未命中索引的 sql。

pt-query-digest 分析中 Rows examine 的 Rows Send 对比。

---

explain。

使用 explain 查询 sql 的执行计划。

mysql> explain select * from 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)

• table
  这一行的数据是关于哪张表的。
• type
  这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 const、eq-reg、ref、range、index 和 ALL。
• posible_keys
  可能应用在这张表中的索引。如果为空，没有可能的索引。
• key
  实际使用的索引。如果为 NULL，则没有使用索引。
• key_len
  使用的索引的长度。在不损失精确性的情况下，长度越短越好。
• ref
  显示索引的哪一列被使用了，如果可能的话，是一个常数。
• rows
  MySql 认为必须检查的用来返回请求数据的行数。

Extra 列需要注意的返回值。

• Using filesort。
  看到这个的时候，查询就需要优化了。MySQL 需要进行额外的步骤来发现如果对返回的行排序。ta 根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
• Using temporary。
  看到这个的时候，查询就需要优化了。这里，MySQL 需要创建一个临时表来存储结果，这通常发生在对不同的列集进行 ORDER BY 上，而不是 GROUP BY 上。