mysql - ORDER BY 优化是否在下面的 SELECT 语句中生效？

Question

我有一个要优化的 SELECT 语句。 mysql - order by optimization表示在某些情况下索引不能用于优化 ORDER BY。具体点:

You use ORDER BY on nonconsecutive parts of a key
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

让我想到，情况可能就是这样。我正在使用以下索引:

UNIQUE KEY `met_value_index1` (`RTU_NB`,`DATETIME`,`MP_NB`),
KEY `met_value_index` (`DATETIME`,`RTU_NB`)

使用以下 SQL 语句:

SELECT * FROM met_value
WHERE rtu_nb=constant
AND mp_nb=constant
AND datetime BETWEEN constant AND constant
ORDER BY mp_nb, datetime

• 删除索引 met_value_index1 并使用新顺序创建它是否足够 RTU_NB、MP_NB、DATETIME?
• 我是否必须将 RTU_NB 包含到 ORDER BY 子句中？

---

结果:我尝试了@meriton 的建议并添加了索引 met_value_index2。 SELECT 在 1.2 秒后完成，之前它在 5.06 秒后完成。以下不属于问题，但作为旁注:经过一些其他尝试后，我将引擎从 MyISAM 切换到 InnoDB - 以 rtu_nb, mp_nb, datetime 作为主键 - 并且语句在之后完成0.13 秒!

Answer 1

我没有收到你的询问。如果一行必须匹配 mp_np = constant 才能返回，则返回的所有行都将具有相同的 mp_nb，因此在 order by 子句中包括 mp_nb没有效果。我建议您使用语义上等效的语句:

SELECT * FROM met_value
WHERE rtu_nb=constant
AND mp_nb=constant
AND datetime BETWEEN constant AND constant
ORDER BY datetime

避免不必要地混淆查询优化器。

现在，对于您的问题:如果数据库知道底层访问将以正确的顺序返回行，则它可以实现不排序的 order by 子句。对于索引，这意味着如果与 where 子句匹配的行以 order by 子句请求的顺序出现在索引中，则索引可以协助排序。

这里就是这种情况，因此数据库实际上可以在 met_value_index1 上对 rtu_nb=constant AND datetime BETWEEN constant AND constant 的行进行索引范围扫描，并且然后检查这些行中的每一行是否 mp_nb=constant，但如果 mp_nb=constant 具有高选择性，这将相当于检查比必要更多的行。换句话说，如果匹配的行在索引中是连续的，那么索引是最有用的，因为这意味着索引范围扫描将只触及实际需要返回的行。

因此，以下索引将对此查询更有帮助:

UNIQUE KEY `met_value_index2` (`RTU_NB`,`MP_NB`, `DATETIME`),

因为所有匹配的行将在索引中彼此相邻，并且这些行按照 order by 子句请求的顺序出现在索引中。我不能说查询优化器是否足够聪明，可以做到这一点，所以您应该检查执行计划。