一 简介
在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢？ 且看本文慢慢分析。
二 分析
案例中的MySQL数据库版本 5.6.16 将生产环境的sql做适当修改,where条件不变。读者朋友可以测试一下其他的版本。
分析
MySQL选择的执行计划利用主键进行访问数据。注意执行计划中的 access type是index,而index 意味着这个SQL在查询二级索引的时候,对二级索引进行了全索引扫描，根本没有进行过滤
这个行为是不合理的,因为where条件中含有 in 查询,合理的执行计划的access type应该是range。
我们采用强制索引，看看结果
分析
强制加上索引之后的执行计划是符合预期的,执行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的结果集是否唯一，如果不唯一还是要使用其他方法。
三 总结
a 修改SQL,添加正确hint。
b 去掉不必要的order by 需要和开发沟通确认是否影响业务逻辑。
c 修改优化的bug,保留多个访问路径，不清理保存访问方式的quick变量,发现orderby 的代价高于组合索引时，可以选择最优的访问路径。
特别感谢 江疑 的分析，同时也推荐 《 排序sql升级5.6变慢原因分析 》
在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢？ 且看本文慢慢分析。
二 分析
案例中的MySQL数据库版本 5.6.16 将生产环境的sql做适当修改,where条件不变。读者朋友可以测试一下其他的版本。
- 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
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: lol
- type: index
- possible_keys: idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
- key: PRIMARY
- key_len: 8
- ref: NULL
- rows: 3076
- Extra: Using where
- 1 row in set (0.00 sec)
MySQL选择的执行计划利用主键进行访问数据。注意执行计划中的 access type是index,而index 意味着这个SQL在查询二级索引的时候,对二级索引进行了全索引扫描，根本没有进行过滤
这个行为是不合理的,因为where条件中含有 in 查询,合理的执行计划的access type应该是range。
我们采用强制索引，看看结果
- 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)
- -> 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
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: lol
- type: range
- possible_keys: idx_sidustsvidtype
- key: idx_sidustsvidtype
- key_len: 19
- ref: NULL
- rows: 5178
- Extra: Using where; Using filesort
- 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的结果集是否唯一，如果不唯一还是要使用其他方法。
- root@rac1 10:48:15>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 \G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: lol
- type: range
- possible_keys: idx_sellerid,idx_uts_stp,idx_sid_stpe,idx_sidustsvidtype
- key: idx_sidustsvidtype
- key_len: 19
- ref: NULL
- rows: 5178
- Extra: Using where
- 1 row in set (0.00 sec)
a 修改SQL,添加正确hint。
b 去掉不必要的order by 需要和开发沟通确认是否影响业务逻辑。
c 修改优化的bug,保留多个访问路径，不清理保存访问方式的quick变量,发现orderby 的代价高于组合索引时，可以选择最优的访问路径。
特别感谢 江疑 的分析，同时也推荐 《 排序sql升级5.6变慢原因分析 》
