MySQL SQL优化之覆盖索引
博客专区 > Mr船长 的博客 > 博客详情
MySQL SQL优化之覆盖索引
Mr船长 发表于3个月前
MySQL SQL优化之覆盖索引
  • 发表于 3个月前
  • 阅读 1397
  • 收藏 83
  • 点赞 10
  • 评论 22

腾讯云 学生专属云服务套餐 10元起购>>>   

摘要: 利用索引提升SQL的查询效率是我们经常使用的一个技巧,但是有些时候MySQL给出的执行计划却完全出乎我们的意料,我们预想MySQL会通过索引扫描完成查询,但是MySQL给出的执行计划却是通过全表扫描完成查询的,其中的某些场景我们可以利用覆盖索引进行优化。

前些天,有个同事跟我说:“我写了个SQL,SQL很简单,但是查询速度很慢,并且针对查询条件创建了索引,然而索引却不起作用,你帮我看看有没有办法优化?”。

我对他提供的case进行了优化,并将优化过程整理了下来。

 

我们先来看看优化前的表结构、数据量、SQL、执行计划、执行时间等。

1. 表结构:

CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` char(12) NOT NULL,
  `order_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

隐藏了部分不相关字段后,可以看到表足够简单, 并且在order_code上创建了唯一性索引uni_order_code。

2. 数据量:316977

这个数据量还是比较小的,不过如果SQL足够差,一样会查询很慢。

3. SQL:

select order_code, order_amount from t_order order by order_code limit 1000;

哇,SQL足够简单,不过有时候越简单也越难优化。

4. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order ALL NULL NULL NULL NULL 316350 Using filesort

全表扫描、文件排序,注定查询慢!

那为什么MySQL没有利用索引(uni_order_code)扫描完成查询呢?因为MySQL认为这个场景利用索引扫描并非最优的结果。我们先来看下执行时间,然后再来分析为什么没有利用索引扫描。

 5. 执行时间:260ms

原SQL执行过程

的确,执行时间太长了,如果表数据量继续增长下去,性能会越来越差。

 

我们来分析下MySQL为什么使用全表扫描、文件排序,而没有使用索引扫描、利用索引顺序:

1. 全表扫描、文件排序:

虽然是全表扫描,但是扫描是顺序的(不管机械硬盘还是SSD顺序读写性能都是高的),并且数据量不是特别大,所以这部分消耗的时间应该不是特别大,主要的消耗应该是在排序上。

2. 利用索引扫描、利用索引顺序:

uni_order_code是二级索引,索引上保存了(order_code,id),每扫描一条索引需要根据索引上的id定位(随机IO)到数据行上读取order_amount,需要1000次随机IO才能完成查询,而机械硬盘随机IO的效率是极低的(机械硬盘每秒寻址几百次)。

根据我们自己的分析选择全表扫描相对更优。如果把limit 1000改成limit 10,则执行计划会完全不一样。

 

既然我们已经知道是因为随机IO导致无法利用索引,那么有没有办法消除随机IO呢?

有,覆盖索引。

 

我们来看看利用覆盖索引优化后的索引、执行计划、执行时间。

1. 创建索引:

ALTER TABLE `t_order` 
ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

创建了复合索引idx_ordercode_orderamount(order_code,order_amount),将select的列order_amount也放到索引中。

2. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order index NULL idx_ordercode_
orderamount
42 NULL 1000 Using index

执行计划显示查询会利用覆盖索引,并且只扫描了1000行数据,查询的性能应该是非常好的。

3. 执行时间:13ms

优化后执行结果

从执行时间来看,SQL的执行时间提升到原来的1/20,已经达到我们的预期。

 

总结:

覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等。

要写出性能很好的SQL不仅需要学习SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。

 

共有 人打赏支持
粉丝 10
博文 5
码字总数 4059
评论 (22)
gm100861
我试了我们线上的表,有70多W数据, 没遇到你这个问题.
EXPLAIN SELECT order_code,order_amount FROM t_order ORDER BY order_code LIMIT 1000;

1  SIMPLE  t_order  index    order_code_idx  303    1000  
Mr船长

引用来自“gm100861”的评论

我试了我们线上的表,有70多W数据, 没遇到你这个问题.
EXPLAIN SELECT order_code,order_amount FROM t_order ORDER BY order_code LIMIT 1000;

1  SIMPLE  t_order  index    order_code_idx  303    1000  
这个不是绝对的,MySQL会评估各种执行路径的代价。
我是在我们的测试库上跑的,机械硬盘,你们生产环境应该是SSD吧,两者之间的随机IO性能差别特别大。
你可以将limit 1000改成limit 10000试试或者删除limit ?条件试试,应该会出现我说的现象。

盼回复确认结果
xiaoluos
现在的表中就发现数据量多order by 效果非常慢,你说覆盖索引需要把所有查询列 都加入索引,但如果查询列过多,这种办法并不适应,楼主还有其他办法了?
Mr船长

引用来自“xiaoluos”的评论

现在的表中就发现数据量多order by 效果非常慢,你说覆盖索引需要把所有查询列 都加入索引,但如果查询列过多,这种办法并不适应,楼主还有其他办法了?
的确是的,覆盖索引虽然高效,但是还是有很多使用限制。
优化order by通常需要利用索引的顺序,然后配合limit,不过还要看具体的业务场景,没有完全通用的解决办法。
如果可以的话,把你的表结构、数据量、sql贴出来,我们一起看下,看看有没好的办法。
withileyl
很厉害,喵喵
Mr船长

引用来自“withileyl”的评论

很厉害,喵喵
继续努力
LarryYan
覆盖索引确实能解决部分问题,但是如果返回的列比较多的情况下使用这种还可行吗?楼主能否发表一篇关于“ 要写出性能很好的SQL不仅需要学习SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。”,授人以鱼不如授人以渔嘛。如果发了求@我
Mr船长

引用来自“LarryYan”的评论

覆盖索引确实能解决部分问题,但是如果返回的列比较多的情况下使用这种还可行吗?楼主能否发表一篇关于“ 要写出性能很好的SQL不仅需要学习SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。”,授人以鱼不如授人以渔嘛。如果发了求@我
要写一篇全面的SQL优化方面的文章还是有很大难度的,如果将来写的话,一定会@ 你
LarryYan

引用来自“LarryYan”的评论

覆盖索引确实能解决部分问题,但是如果返回的列比较多的情况下使用这种还可行吗?楼主能否发表一篇关于“ 要写出性能很好的SQL不仅需要学习SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。”,授人以鱼不如授人以渔嘛。如果发了求@我

引用来自“Mr船长”的评论

要写一篇全面的SQL优化方面的文章还是有很大难度的,如果将来写的话,一定会@ 你
好的,不过我平常也会学习mysql的,说不定我会先写出来的
ylmotol7
有时候很奇怪,类似的表,结构类似,可以说是相同的表,只不过表名和列名不同,索引也类似,但是执行计划却大不相同
walk_lai
学习了。
开源中国首席董事长
666
shuaia
上ddl的成本太大了。对一个大表做onlineddl基本上每次都是几十个小时,如果有唯一索引还经常思索。真是让人头大!这里我觉得可以这样改。分两步查!第一步select order_code from t_order order by order_code limit 1000;第二步ordercode查就好了
shuaia
这个语句应该也能搞定
select t2.order_code,t2.order_amount from t_order t1
join t_order t2 on t1.id = t2.id
order by t1.order_code desc
limit 1000;
springlin
请教一下,分析执行计划任务的工具叫什么,谢谢。
chdy
请问兄台,执行时间截图使用的是什么工具
Mr船长

引用来自“springlin”的评论

请教一下,分析执行计划任务的工具叫什么,谢谢。
Navicat for MySQL
Mr船长

引用来自“chdy”的评论

请问兄台,执行时间截图使用的是什么工具
Navicat for MySQL
Mr船长

引用来自“shuaia”的评论

上ddl的成本太大了。对一个大表做onlineddl基本上每次都是几十个小时,如果有唯一索引还经常思索。真是让人头大!这里我觉得可以这样改。分两步查!第一步select order_code from t_order order by order_code limit 1000;第二步ordercode查就好了
多大表要几十个小时?分两步查也行,不过where in数量不能太大,否则也有性能问题。
Mr船长

引用来自“shuaia”的评论

这个语句应该也能搞定
select t2.order_code,t2.order_amount from t_order t1
join t_order t2 on t1.id = t2.id
order by t1.order_code desc
limit 1000;
这样还是没消灭随机IO或排序操作
×
Mr船长
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: