mysql - MySQL中有索引 "hopping"这样的东西吗？

Question

假设我们在 (A, B) 上有一个索引，在 (B, C) 上有一个索引。在进行如下查询时:

SELECT * FROM table WHERE A = const AND B = const ORDER BY C DESC

查询优化器是否会先在 (A,B) 索引中搜索以过滤 WHERE 类的行，然后再使用 (B, C) 索引进行快速排序？

或者查询仅限于一个索引？没有 B 树跳跃？

Answer 1

不，MySQL 不会执行您所描述的操作。

它将执行以下操作之一:

• 从 (A, B) 索引中读取，它将使用索引仅检查匹配的行，但需要额外的工作来执行文件排序以按 C。

• 从 (B, C) 索引读取，它将以正确的顺序读取行，因此跳过文件排序。但它会检查许多具有不匹配的 A 值的额外行，并且它必须逐一评估这些行并丢弃那些不匹配的行。

您可以通过将 (A, B) 索引替换为 (A, B, C) 上的索引来优化两者，这将只检查匹配的行, 并按所需顺序阅读它们，因此不需要文件排序。

InnoDB 总是以某种索引顺序读取行。二级索引或聚簇索引。

---

回复你的问题:

通常，MySQL 只从一个索引中读取每个表引用。这允许例如使用自连接的查询，因此同一张表有多个表引用。每个表引用可能使用不同的索引进行读取。

例如，经理与其员工的自连接:

SELECT ...
FROM employees AS m
JOIN employees AS e ON e.manager_id = m.id
WHERE m.hire_date = '2020-01-01'

在此示例中，它可能会使用 hire_date 上的索引来选择经理，并使用 manager_id 上的索引来选择经理的下属。这是两个不同的表引用，因此它们是分开阅读的。

MySQL 还有一个特性叫做 index merge optimization ，它可能会读取表的两个子集，可能使用不同的索引，然后使用并集或交集合并结果。但我发现这种情况并不像您想象的那样经常发生。

关于 ORDER BY DESC，https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html说:

previously, indexes could be scanned in reverse order but at a performance penalty.

在 MySQL 8.0 中，他们实现了对声明索引以降序构建的支持，以支持 ORDER BY DESC 查询。但是索引是为这些查询量身定制的，并且对 ASC 查询使用相同的索引会受到影响。因此，您可能需要在同一个表的相同列上创建两个索引。阅读我链接到的文档页面以了解更多详细信息。