同事问我,SQL 语句明明命中了索引,为什么执行很慢?
我们都知道,业务开发涉及到数据库的SQL
操作时,一定要 review
是否命中索引。否则,会走 全表扫描
,如果表数据量很大时,会慢的要死。
假如命中了索引呢?是不是就不会有慢查询?
殊不知,我们习以为常的常识有时也会误导我们!
人生好难!
聊这个话题,要有一定技术基础,需了解 B+ 树的存储结构
1、工作准备:建表,造数据
id
的主键索引,和一个 user_name
的普通索引。CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
`age` int(11) NOT NULL COMMENT '年龄',
`address` varchar(128) COMMENT '地址',
PRIMARY KEY (`id`),
key `idx_user_name` (user_name),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
user
表中插入 10000 条数据。@GetMapping("/insert_batch")
public Object insertBatch(@RequestParam("batch") int batch) {
for (int j = 1; j <= batch; j++) {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= 100; i++) {
User user = User.builder().userName("Tom哥-" + ((j - 1) * 100 + i)).age(29).address("上海").build();
userList.add(user);
}
userMapper.insertBatch(userList);
}
return "success";
}
MySQL的慢查询日志是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
slow_query_log:是否开启慢查询日志,1表示开启,0表示关闭。 log-slow-queries:旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log long_query_time:慢查询阈值,当查询时间高于设定的阈值时,记录到日志 log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)
slow_query_log
的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log
的值来开启,如下所示:使用set global slow_query_log=1 开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。如果要永久生效,必须修改配置文件 my.cnf
long_query_time
的默认值为10 秒,支持二次修改。线上我们一般会设置成1秒,如果业务对延迟敏感的话,我们根据需要设置一个更低的值。explain select * from user;
,发现 key
这列为NULL,说明了没有命中索引,走了全表扫描。explain select * from user where id=10;
,发现 key
这列为 PRIMARY
,说明使用了主键索引。explain select user_name from user;
,发现 key
这列为 idx_user_name
,说明使用了二级普通索引。rows
扫描行为 9968,说明走了全表扫描。性能很差。explain select * from user where id>0;
时,发现使用了主键索引。id>0
的值,虽然走了索引但其实还是全表扫描。扫描行数
。过滤性
是否足够好。
5、回表优化
user
表 增加一个 user_name
和 age
的联合索引。ALTER TABLE `user` ADD INDEX idx_user_name_age ( `user_name`,`age` );
explain select * from user where user_name like 'Tom哥-1%' and age =29;
① 首先在 idx_user_name_age
索引树,查找第一个以Tom哥-1
开头的记录对应的主键id② 根据主键id从主键索引树找到整行记录,并根据 age
做判断过滤,等于29则留下,否则丢弃。这个过程也称为回表
③ 然后,在 idx_user_name_age
联合索引树上向右遍历,找到下一个主键id④ 再执行第二步 ⑤ 后面重复执行第三步、第四步,直到 user_name
不是以Tom哥-1
开头,则结束⑥ 返回所有查询结果
user_name
的前缀匹配,idx_user_name_age
二级索引中的 age
部分并没有发挥作用。导致了大量回表
查询,性能较差。Index Condition Pushdown Optimization
https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
① 首先在 idx_user_name_age
索引树,查找第一个以Tom哥-1
开头的索引记录② 然后,判断这个索引记录中的 age
是否等于 29。如果是,回表
取出整行数据,作为后面的结果返回;如果不是,则丢弃③ 在 idx_user_name_age
联合索引树上向右遍历,重复第二步,直到user_name
不是以Tom哥-1
开头,则结束④ 返回所有查询结果
age 是否等于 29
放在了遍历联合索引过程中进行,不需要回表判断,大大降低了回表的次数,提升性能。当然这个优化依然没有绕开最左前缀原则,索引的过滤性仍然有提升空间。
虚拟列
的概念。ALTER TABLE `user` add user_name_first varchar(12) generated always as
(left(user_name,6)) , add index(user_name_first,age);
explain select * from user where user_name_first like 'Tom哥-1%' and age =29;
row
变小了,证明优化有效果。slow_query_log
收集到的慢 SQL ,结合 explain
分析是否命中索引,结合扫描行数,有针对性的优化慢 SQL。有道无术,术可成;有术无道,止于术
欢迎大家关注Java之道公众号
好文章,我在看❤️