MySQL中的慢SQL优化

之前写了PostgreSQL的explain指令使用文档，现在补充一份MySQL版本的文档。

开启慢查询日志

Mysql运行在Docker容器中配置较低，查询时间长是正常，这里只需要关注怎么优化SQL。

1.开启慢查询日志

首先开启慢查询日志，由参数slow_query_log控制是否开启，在MySQL命令行下输入下面的命令：

set global slow_query_log=on;

2.设置慢查询阈值

set global long_query_time=1;

只要SQL执行时间超过了这个阈值，就会被记录到慢查询日志里面，一般测试环境long_query_time设置的阀值比生产环境的小一些，更加容易发现慢SQL。

3.确定慢查询日志的文件名和路径

show global variables like 'slow_query_log_file'

查询带有quer的相关变量

show global variables like '%quer%';

image-20240710154038343

  • long_query_time是慢SQL的判定时间，目前是超过10s就认为是慢SQL

  • slow_query_log是慢SQL开关

  • slow_query_log_file是慢SQL日志文件地址

这里设置慢查询阈值为1s

set global long_query_time=1;

可以看到已经修改过来了

image-20240710154332106

重启mysql客户端设置和统计慢查询日志条数就会清零，在配置文件修改才能永久改变。

慢查询例子

数据表结构

CREATE TABLE `t_logbook` (  
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `title` VARCHAR (20), 
    PRIMARY KEY (`id`),  
    UNIQUE(`account`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

插入100W条测试数据。

执行查询SQL

SELECT name FROM t_logbook ORDER BY name DESC;

查询响应耗时20s

image-20240710170637099

这个20s并不是实际执行时间，实际执行时间得去慢查询日志去看Query_time参数

image-20240710170651284

可以看到Query_time: 17s，远远超过了1s，查询时间长一部分原因是Mysql运行在Docker容器中配置较低。

图中其他的参数解释如下：

  • Time：慢查询发生的时间
  • Query_time：查询时间
  • Lock_time：等待锁表的时间
  • Rows_sent：语句返回的行数
  • Rows_exanined：语句执行期间从存储引擎读取的行数

慢SQL优化处理

根据表结构可以知道，account是添加了唯一索引的字段，explain分析一下执行计划。

explain SELECT account FROM t_logbook ORDER BY account DESC;

image-20240710165740781

image-20240710164458470

重点需要关注select_type、type、possible_keys、key、Extra这些列。

account是添加了唯一索引的字段，explain分析了执行计划后

SELECT account FROM t_logbook ORDER BY account DESC;

直接按照account降序来查

image-20240710170847931

查看慢查询日志发现，使用索引之后，查询100W条数据的速度快了6s

然后给name字段加上索引

ALTER TABLE t_logbook ADD INDEX idx_name(name);

加上索引之后，继续看看查询name的sql执行计划

EXPLAIN SELECT name FROM t_logbook ORDER BY name DESC;

image-20240710170946405

对比一下前面name不加索引时的执行计划就会发现，加了索引后，type由ALL全表扫描变成index索引扫描。order by并没有 using filesort，而是using index，这里B+树已经将这个非聚集索引的索引字段的值排好序了，而不是等到查询的时候再去排序。

接着我们继续执行查询语句，此时name已经是添加了索引的。

image-20240710171113234

结果发现，name添加索引之前，降序查询name是花费19s，添加索引之后，降序查询name花费了12s，原因就是B+树的结果集已经是有序的了，查询时间减少了6s左右。

查询优化器影响

查询一下数据的条数，这里count(id)，分析一下sql执行计划

EXPLAIN SELECT count(id) FROM t_logbook;

image-20240710171235502

这里实际使用的索引是account唯一索引。

分析一下：实际使用哪个索引是查询优化器决定的，B+树的叶子结点就是链表结构，遍历链表就可以统计数量，但是这张表，有主键索引、唯一索引、普通索引，优化器选择了account这个唯一索引，这肯定不会使用主键索引，因为主键索引是聚集索引，每个叶子包含具体的一个行记录（很多列的数据都在里面），而非聚集索引每个叶子只包含下一个主键索引的指针，很显然叶子结点包含的数据是越少越好，查询优化器就不会选择主键索引

当然，也可以强制使用主键索引，然后分析sql执行计划

EXPLAIN SELECT count(id) FROM t_logbook FORCE INDEX (PRIMARY);

image-20240710171315818

优化器默认使用唯一索引大致执行时间664ms

image-20240710171427407

强制使用主键索引大致执行时间1170ms

image-20240710171354904

可以看到唯一索引更快，分析下原因：

索引类型：唯一索引通常是非聚簇索引，遍历索引树不需要访问实际数据行。

索引覆盖：唯一索引可以完全覆盖COUNT查询，避免访问实际数据行。

可以用force index强制指定索引，然后去分析执行计划看看哪个索引是更好的，因为查询优化器选择索引不一定是百分百准确的，具体情况可以根据实际场景分析来确定是否使用查询优化器选择的索引。

当SQL确实用上了索引，查询还是缓慢，可以再考虑联合索引来实现索引覆盖。
如果数据量确实很大，建议升配，或者分表分库、读写分离。