MySQL order by + limit bug 触发原因及解决办法
2,207 total views, 1 views today
问题描述
下面这个SQL，应该走where的索引 idx_iv_pid，包含 id_value 、parent_id 两列。
但是，走的是排序列 add_time的索引add_time_idx。从而SQL执行速度慢。
但是，但是！ add_time_idx 并不在“possible_keys”里。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql>desc SELECT * FROM `ecs_ugo_comment` WHERE id_value = 23613 AND comment_type IN (0, 3) AND STATUS = 1 AND parent_id = 0 ORDER BY add_time DESC LIMIT 1 +--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+ | 1 | SIMPLE | ecs_ugo_comment | index | parent_id,id_value,idx_iv_at_ac,idx_iv_pid | add_time_idx | 4 | | 1601 | Using where | +--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+ 返回行数: [1], 耗时: [52ms] |
SQL真实执行时间为 1202ms 。
后来查到，这是一个bug。
bug触发条件
1、where条件中的索引过滤性较好
2、order by + limit 引导优化器尝试使用 order by上的索引进行优化，最总选择错了成本更高的order by索引。
这个优化器选择路径的过程，可以使用 optimizer_trace方式获取
1、打开优化器追踪
|
1 |
set optimizer_trace='enabled=on'; |
2、执行SQL
|
1 2 3 4 5 6 7 8 |
SELECT * FROM `ecs_ugo_comment` WHERE id_value = 23613 AND comment_type IN (0, 3) AND STATUS = 1 AND parent_id = 0 ORDER BY add_time DESC LIMIT 1 |
3、查看追踪数据
|
1 |
SELECT * FROM information_schema.OPTIMIZER_TRACE\G |
范围访问总结部分，显示选择了cost最低的idx_iv_pid索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
"chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "idx_iv_pid", "rows": 2180, "ranges": [ "23613 <= id_value <= 23613 AND 0 <= parent_id <= 0" ] }, "rows_for_plan": 2180, "cost_for_plan": 778.73, "chosen": true |
但是在“reconsidering_access_paths_for_index_ordering” 重新选择排序路径的时候选择了 order by列上的索引add_time_idx。
从而导致了走了错误的执行计划。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
"reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ], "index_order_summary": { "table": "`ecs_ugo_comment`", "index_provides_order": true, "order_direction": "desc", "index": "add_time_idx", "plan_changed": true, <---修改了原有执行计划 "access_type": "index" } } |
解决办法
在order by中加入主键列 comment_id 就能正常的走where条件的索引 idx_iv_pid(id_value,parent_id)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
desc SELECT * FROM `ecs_ugo_comment` WHERE id_value = 23613 AND comment_type IN (0, 3) AND STATUS = 1 AND parent_id = 0 ORDER BY add_time DESC,comment_id LIMIT 1 +--------------+-----------------------+-----------------+----------------+--------------------------------------------+---------------+-------------------+---------------+----------------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-----------------------+-----------------+----------------+--------------------------------------------+---------------+-------------------+---------------+----------------+-----------------------------+ | 1 | SIMPLE | ecs_ugo_comment | ref | parent_id,id_value,idx_iv_at_ac,idx_iv_pid | idx_iv_pid | 7 | const,const | 2179 | Using where; Using filesort | +--------------+-----------------------+-----------------+----------------+--------------------------------------------+---------------+-------------------+---------------+----------------+-----------------------------+ 返回行数: [1], 耗时: [69ms] |
SQL 真实执行时间 74ms，快了将近20倍。
参考
https://developer.aliyun.com/article/51065