MySQL order by + limit bug 触发原因及解决办法
问题描述 下面这个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
Read more