MYSQL慢查询日志（开启慢查询配置、explain执行计划SQL优化、各个字段详解、索引失效）

大家好，我是此林。

今天来分享一下MYSQL慢查询日志记录。

1. 定义

慢查询日志记录了所有执行时间超过了long_query_time的SQL语句。

默认情况下MYSQL未开启慢查询日志。

2. 开启慢查询

方法一：命令行

开启慢查询（未加global是会话级别）

设置慢查询时间

重启MYSQL

docker restart mysql-master

# 非docker环境下
systemctl restart mysqld

方法二：修改配置文件

docker环境下：

1. 执行命令

# 切换到docker挂载到本地的MYSQL配置文件目录
cd /root/mysql/master/conf/
# 编辑
vi my.cnf

2. 按下i进入INSERT模式，写入后按下ESC退出，按下:wq保存。（注意：若my.cnf不存在，开头须加上 [mysqld] ）。这里设置慢查询时间为1秒。

3. 重启MYSQL

docker restart mysql-master

4. 开始实验

第一个Session窗口

第二个Session窗口

此时，第一个窗口执行提交事务。

可以看到，第二个窗口查询耗时12.27秒。（for update排他锁阻塞）

在/root/mysql/master/data/目录下已生成慢查询日志。（非docker环境下位var/lib/mysql）

查看log，已经记录。

3. explain性能分析

以上我们通过了show_query_log和long_query_time两个参数来配置慢查询。

接下来介绍explain执行计划进行SQL性能分析。

各个字段介绍：

1. id：select查询顺序id，id越大的越先执行；若id相同，执行顺序从上到下。（因为如果是子查询，会有多个explain行）

2. select_type：select的类型。SIMPLE（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）等。

3. type（重点）：

连接类型，性能由好到坏：NULL、system、const、eq_ref、ref、range、index、all。

system：访问系统表的时候出现
const：根据主键和唯一索引访问
ref：根据非唯一索引访问
range：走了索引，但是范围查询
index：全扫描整个索引树
all：全表扫描

一般我们在SQL优化时候，把type优化到range及以上即可。

4. possible_key：可能用到的索引。

5. key：实际用到的索引。

6. key_len：索引中使用的字节数（我们通常用这个字段来判断联合索引中实际用了哪些索引列）

7. rows：MYSQL认为要执行查询的行数（估计值）

8. filtered：过滤率，表示返回结果占需要读取行数的百分比，越大越好。

9. extra：额外信息。

using index：使用覆盖索引
using index condition：使用了索引
using where：使用了where条件查询
using filesort：order by 没有使用索引，建议优化。

4. 索引失效

1. 最左前缀法则

对于一个联合索引，遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不能跳过索引中的列。

若不遵守这个法则，索引会失效。

2. 对字段做运算、字段类型不匹配

1. 场景1，id+1 = 2，索引失效。

select * from user where id+1 = 2;

2. 场景2，id = '1'，索引不失效（id为int）。

select * from user where id = '1';

因为MySQL会自动做隐式转换。

3. 场景3，name = 123，索引失效（name是索引，类型为varchar）

select * from user where name = 123;

explain select * from user where name = 123;

4. 场景4，substring(name, 1, 1)，从第一位开始截取1个字符，索引失效。

 select * from user where substring(name, 1, 1) = '张';

3. 模糊匹配

只要%在开头，索引一定失效。%不在开头，在末尾，索引不失效。

 select * from user where name like '%张';

当然，以上SQL也可能会全扫描索引树，即type=index，性能也比较低，和ALL类似。

4. OR 连接条件

如果or连接条件中有的列不是索引，那么其他索引一律不会被使用。

（注：id和name都是索引，其他非索引）

附上一张图，对比and 和 or 的索引使用情况。

5. 数据分布

如果评估使用索引比全表扫描更慢，则不使用索引。

5. 强制使用索引

语法

# 展示所有索引
show index from table_name;

# 建议MySQL使用索引
select * from user using index(idx_name) where name = '张三';

# 建议MySQL忽略索引
select * from user ignore index(idx_name) where name = '张三';

# 强制MySQL使用索引
select * from user force index(idx_name) where name = '张三';