order by 主键id导致全表扫描的问题

一 简介 在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢? 且看本文慢慢分析。

二 分析 案例中的MySQL数据库版本 5.6.16 将生产环境的sql做适当修改,where条件不变。读者朋友可以测试一下其他的版本。

  1. root@rac1 10:48:11>explain select id,gmt_create, gmt_modified,order_id,service_id, seller_id,seller_nick, sale_type from lol where seller_id= 1501204 and service_id= 1 and sale_type in(3, 4) and use_status in(3, 4, 5, 6) and process_node_id= 6 order by id desc limit 0,20 \G
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: lol
  6. type: index
  7. possible_keys: idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
  8. key: PRIMARY --- 应该选择 idx_sidustsvidtype
  9. key_len: 8
  10. ref: NULL
  11. rows: 3076
  12. Extra: Using where
  13. 1 row in set (0.00 sec)

分析: MySQL选择的执行计划是利用主键访问数据。注意执行计划中的 access type是index,而index 意味着这个SQL在查询二级索引的时候,对二级索引进行了全索引扫描,根本没有进行过滤这个行为是不合理的,因为where条件中含有 in 查询,合理的执行计划的access type应该是range。我们采用强制索引,看看结果

  1. root@rac1 10:48:07>explain select id, gmt_create,gmt_modified, order_id,service_id,seller_id,seller_nick, sale_type from lol force index(idx_sidustsvidtype)
  2. where seller_id= 1501204 and service_id= 1 and sale_type in(3, 4)
  3. and use_status in(3, 4, 5, 6)
  4. and process_node_id= 6 order by id desc limit 0,20 \G
  5. *************************** 1. row ***************************
  6. id: 1
  7. select_type: SIMPLE
  8. table: lol
  9. type: range
  10. possible_keys: idx_sidustsvidtype
  11. key: idx_sidustsvidtype
  12. key_len: 19
  13. ref: NULL
  14. rows: 5178
  15. Extra: Using where; Using filesort
  16. 1 row in set (0.00 sec)

分析 强制加上索引之后的执行计划是符合预期的,执行sql的时间由 1.57s 减少为 0.01s 。因此我们推测是在优化器选择索引的时候出现了问题。结合源码和optimize_trace我们发现第一阶段优化的时候,优化器确实选择了idx_sidustsvidtype 并且选择采用range访问,因为sql 语句中含有order by,在optimizer试图优化 order by limit的时候清空了保存访问方式的quick变量(原本保存的是range,但是被请空),最终发现采用排序索引(这里是id)的代价高于组合索引(这里是idx_sidustsvidtype)时,还是选择了idx_sidustsvidtype。但是悲剧的是这时候正确的访问方式已经被清空,无法还原,这就是这个 bug#78993 的根本成因。 根据分析,我们还可以使用另一种解决方法----去掉 order by 。当然这个对业务所有入侵必须和开发沟通确认sql的结果集是否唯一,如果不唯一还是要使用其他方法。

  1. root@rac1 10:48:15>explain select id,gmt_create,gmt_modified,order_id,service_id,seller_id, seller_nick,sale_typefrom lol
  2. where seller_id= 1501204 and service_id= 1 and sale_type in(3, 4) and use_status in (3, 4, 5, 6) and process_node_id= 6 \G
  3. *************************** 1. row ***************************
  4. id: 1
  5. select_type: SIMPLE
  6. table: lol
  7. type: range
  8. possible_keys: idx_sellerid,idx_uts_stp,idx_sid_stpe,idx_sidustsvidtype
  9. key: idx_sidustsvidtype
  10. key_len: 19
  11. ref: NULL
  12. rows: 5178
  13. Extra: Using where
  14. 1 row in set (0.00 sec)

三 总结

  1. 修改SQL,添加正确hint,缺点是失去了sql的灵活性,遇到过索引修改导致带有hint的sql执行失败的案例,导致故障。
  2. 去掉不必要的order by 需要和开发沟通确认是否影响业务逻辑。
  3. 修改优化的bug,保留多个访问路径,不清理保存访问方式的quick变量,发现orderby 的代价高于组合索引时,可以选择最优的访问路径。
  4. 特别感谢 江疑 的分析,Bug 请参考原文链接。

原文发表时间:2017-11-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python、Flask、Django

ElasticSearch 入门基本CRUD

1763
来自专栏大数据和云计算技术

hive拉链工具实战

这个丁延明同学写的一个实战工具,坚持用代码解决问题,推荐! 有相关业务的同学可以一起讨论,下面是正文。 ---- 1、背景 大家好 最近由于公司业务需要写了一篇...

3287
来自专栏数据和云

Real-time materialized view,面向开发者的12.2新特性

题记:在12.2之前,如果使用on command刷新物化视图,必须得有个job来定时的刷,那么,在一次job运行之后,下一次job到来之前,如果基表有数据变化...

3054
来自专栏运维技术迷

SQL复习之使用SQL语句创建数据库

创建一个数据库和一个日志文件,语句如下: USE master --当前指向操作的数据库 GO create database E_Market--创建数据库E...

3435
来自专栏ImportSource

构建物化视图的两种方式

构建物化视图的两种方式 章节:nosql distilled 第三章第四节 物化视图 There are two rough strategies to b...

34415
来自专栏杨建荣的学习笔记

关于权限管理的实用脚本(r4笔记第94天)

在工作中,可能会接触到很多的环境问题,对于权限问题,总是感觉心有余力而力不足,环境太多了,可能在赋予权限的时候会出差错, 比如下面的场景,数据都存储在owner...

3134
来自专栏不想当开发的产品不是好测试

mysql 删表引出的问题

背景 将测试环境的表同步到另外一个数据库服务器中,但有些表里面数据巨大,(其实不同步该表的数据就行,当时没想太多),几千万的数据!! 步骤 1. 既然已经把数据...

2817
来自专栏数据和云

腾讯游戏DBA利刃 - SQL审核工具介绍

作者介绍 ? 韩全安(willhan) 华中科技大学,硕士,现代数据库方向。2013年毕业,就职于腾讯到今,工作项目:TMySQL、SQL审核、InnoDB列压...

1.2K6
来自专栏张秀云的专栏

Spider 引擎分布式数据库解决方案(最全的 spider 教程)

最近开始负责财付通的数据库的相关维护工作,其中有几套系统使用的 spider 引擎,本文将 spider 引擎的功能、使用场景、部署、实战测试等做个简单的总结。

1.2K0
来自专栏性能与架构

格式化SQL来提高效率

对SQL语句进行格式化不会让其运行得更快,但对我们的工作效率是有很大好处的 (1)提高SQL的可读性大大提高,便于维护 (2)提高开发效率,方便测试 例如 ? ...

3469

扫码关注云+社区

领取腾讯云代金券