为什么Mysql决定在Order By 子句中指定的列上使用索引,尽管该列在where子句中不存在? 当查询中同时使用"顺序依据 + 限制"子句时,会发生这种情况。
示例查询:
select col1, col2,col3 from table_name where col1 = 'x' and col3='y' order by colY limit 3;
table_name有900万条记录
在没有限制条款的情况下, MySQL使用col1列上的索引,这更快。
更好
select col1, col2,col3
from table_name
where col1 = 'x'
and col3 = 'y'
order by col4
limit 3;
最佳指数是以下两个之一:
INDEX(col1, col3, col4)
INDEX(col3, col1, col4)
在两者中,优化器都可以完全解析WHERE
并执行ORDER BY
,甚至由于LIMIT
而在 3 行后停止。
最好。更好的性能将来自在任一末尾添加col2
。 这使它成为一个"覆盖"索引,因此所有工作都可以在索引的 BTree 中完成,而无需触及数据的 BTree。
回到你的问题
如果您没有这些索引之一,优化器就会陷入困境,并且经常从两个可能的选项中选择错误的。 假设你只有
INDEX(col1), INDEX(col4)
计划 A 侧重于过滤:使用col1
,但在剥离 3 之前必须对所有匹配的行进行排序。 但它可能会得到一百万行,并且必须对它们进行排序。
计划 B 避免排序:按col4
顺序扫描索引。 如果真的很幸运,前 3 行将与WHERE
子句匹配。 如果真的很倒霉,它会扫描整个表而不找到 3 个可接受的行。 但它们会被排序!
"统计数据"微不足道,无法在这两种选择之间做出现实的决定。
任何一个计划都可能非常缓慢。
对两个表进行筛选的WHERE
子句的JOINs
也会出现类似的问题。