mysql - 为什么 Mysql 决定在 Order By 子句中指定的列上使用索引，而该索引不存在于 where 子句中？

Question

为什么 Mysql 决定在 Order By 子句中指定的列上使用索引，尽管该列不存在于 where 子句中？当 Order By + Limit 子句在查询中一起使用时，会发生这种情况。

示例查询:

select col1, col2,col3 from table_name where col1 = 'x' and col3='y' order by colY limit 3;

table_name 有 9M 条记录

在没有限制条款的情况下， mysql 使用 col1 列上的索引，速度更快。

Answer 1

更好

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 子句过滤的 JOIN 也会出现类似的问题。