慢 Sql 查找与优化思路

首先要开启慢查询日志 ( slow query log ) 记录慢 sql

• 虽然叫慢查询日志，但不光会记录 select 语句，其他慢语句也会记录

• MySql 主要定义了三个与慢 Sql 相关的 MySql 系统参数（ MySql 系统参数可以通过 show variables like ‘slow_query%’ 语法查看，通过 SET GLOBAL slow_query_log = ON 语法修改）

  • slow_query_log ：是否开启了慢 sql 记录，默认 OFF ，修改为 ON 表示开启
  • slow_query_log_file ：慢 sql 日志的存放位置
  • long_query_time ：慢 sql 的阈值，单位秒

• MySql 定义了 slow_queries 状态信息（状态信息可以通过 show status like ‘%slow_que%’ 语法进行查看）统计已经出现了的慢 sql 总数

• 这里我们开启慢 sql 记录，设置日志位置为 D:\Files\slow-query.log ，并设置慢 sql 的阈值为 0.1s （需要重新连接数据库才会生效）

然后准备一条慢 sql 并执行

-- 建表
CREATE TABLE `person_info_large` (
  `id` int,
  `account` varchar(255),
  PRIMARY KEY (`id`)
)

-- 创建存储过程，这里是向上表循环插入了 100w 条数据（实际上运行了 8 分钟插入了 5w 多条数据）
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000000 DO
INSERT INTO person_info_large(id,account) VALUES(i,'哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈');
commit;
SET i = i+1;
END WHILE;
END $
DELIMITER ;

-- 执行存储过程
CALL proc_initData();

-- 执行慢 sql
select * from person_info_large where account like '%哈哈%';

-- 慢 sql 日志中出现慢 sql 信息
# Time: 2022-11-24T05:50:40.965329Z
# User@Host: root[root] @ localhost [::1]  Id:    14
# Query_time: 0.226450  Lock_time: 0.000001 Rows_sent: 56594  Rows_examined: 56594
use mybatis_result;
SET timestamp=1669269040;
select * from person_info_large where account like '%哈哈%';

出现慢 sql 后，通过 explain 关键字查看该 sql 的执行计划

explain select * from person_info_large where account like '%哈哈%';

执行结果中重点关注 type 、rows 、extra 、key 四个字段

• type ：描述了查询是如何执行的，从优到差列举以下几种类型（是否进行优化需要具体分析）

  • const ：使用主键或者唯一索引扫描，且匹配的结果只有一条记录，如 where id = 1
  • eq_ref ：主键或唯一索引上的范围扫描，在 where 中进行 between 、< 、> 、in 操作
  • ref ：非唯一性索引扫描，但只匹配单个值，如 where 索引列 = ‘…’
  • range ：非唯一性索范围扫描，在 where 中进行 between 、< 、> 、in 操作
  • index ：全索引扫描，比如查询所有记录的一个索引字段
  • all ：全表扫描

• key ：sql 语句用到的索引字段

• rows ：检索的行数预估值

• extra ：额外的信息说明，但又比较重要

  • Using index ：sql 中使用了索引覆盖
  • Using where ：sql 中使用了索引
  • Using temporary ：使用了临时表

Sql 优化的思路

• 首先检查 sql 是否按照预计方式执行，一般检查是否按预计方式走了索引
• 如果没按预计方式走索引，具体分析原因
• 如果已经按预计方式走索引，分析表是否过大、连接查询是否过多、查询方式是否有问题
• 下面列举了几种常见的会导致慢 sql 的原因
  • 隐式转换：索引字段为字符串类型，但传入了数字过去，会导致索引失效；反过来不会
  • 最左匹配
  • 深度分页
  • in 后元素过多导致查索引次数过多，甚至有可能会全表扫描
  • 虽然字段加了索引，但是用了 != 、<> 、not in 这些查询条件，Mysql 优化器此时觉得走索引不划算