mysql - 在MySQL中，如果索引复合选择中的列在排序中没有完全引用，排序是否会是最佳的？

Question

对于这个问题:

• 有一个表 foo，其中包含 int 类型列 column1 和 column2
• 有一个复合索引(column1，column2)
• foo 中有几亿行

使用以下内容:

SELECT column2 FROM foo 
 WHERE column1 = 1
 ORDER BY column1 ASC, column2 ASC
LIMIT 1000

选择索引(column1，column2)是因为它完全覆盖了该查询。

假设有几十万个条目与子句 WHERE column1 = 1 匹配。

如果我删除排序的第一个参数:

SELECT column2 FROM foo 
 WHERE column1 IN (1,2,3) 
 ORDER BY column2 ASC
LIMIT 1000

解释确认它仍然使用索引。

问题

1. 删除 column1 ASC 参数会对 ORDER BY 中的排序产生负面影响吗？
2. 如果 where 子句是 WHERE column1 IN (1,2,3,4) 会怎样？

Answer 1

SELECT column2 FROM foo 
 WHERE column1 = 1
 ORDER BY column1 ASC, column2 ASC
LIMIT 1000

可以按此顺序有效使用INDEX(column1, column2)。它将“覆盖”(除非您向 SELECT 列表添加其他列)，并且无论表大小如何，它都可以在 1000 行中完成该任务。

但是...

SELECT column2 FROM foo 
 WHERE column1 IN (1,2,3)   -- More than one value
 ORDER BY column2 ASC
LIMIT 1000

只能部分使用任何索引。以下是处理方式:

1. 向下钻取 INDEX(column1, column2) 的 BTree 以查找包含 column1=1 的第一行。
2. 向前扫描，直到值发生变化。
3. 对column1=2 重复此操作，对column1=3 再次重复此操作。
4. 以上所有内容都收集到临时表中；对该表进行排序
5. 剥掉前 100 行。

对于您的 Q1，column1 ASC 不相关:

对于查询1:由于column1是一个常量，因此是否将其包含在ORDER BY中并不重要。
对于查询2:结果可能具有不同的顺序。但请遵循以下通用规则:如果您想要特定的订单，则必须有一个ORDER BY。将其留给优化器来“优化”ORDER BY 和/或根据需要使用特定索引。

The explain confirms it still uses the index.

使用索引有多种原因:

• 它有助于WHERE。
• 它有助于ORDER BY。
• 是“覆盖”；也就是说，查询中的所有列都包含在一个复合索引中。如果你有的话，它可能会选择INDEX(column99,column2,column1)!那么排序就会很神秘。

not fully referenced

• 在索引声明的末尾添加额外的列通常不会损害优化，但可能会将“完全引用”查询变成不完全查询。
• 上面的“覆盖”示例是未完全引用的极端情况。

各种事物的另一个例子:

WHERE x=1 AND y=2
ORDER BY z

最好通过 INDEX(x,y,z) 或 INDEX(y,x,z) 进行优化。在这种情况下，WHERE 和 ORDER BY 都在索引中处理。此外，加上LIMIT也将允许它被处理。

查看我的Cookbook .