以下SELECT语句中是否会生效ORDER BY优化？

Question

我有一条SELECT语句，希望对其进行优化。mysql - order by optimization中提到，在某些情况下，索引无法用于优化ORDER BY。具体来说是指：

您在键的非连续部分上使用ORDER BY
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创建它是否足够？
• 我需要在ORDER BY子句中包含RTU_NB吗？

---

结果：我尝试了@meriton建议的方法，并添加了索引met_value_index2。 SELECT完成时间为1.2秒，之前为5.06秒。以下不属于问题，只是附注：在其他尝试之后，我将引擎从MyISAM切换到InnoDB - 并将rtu_nb, mp_nb, datetime设置为主键 - 语句完成时间为0.13秒！

- Christian Ammer

Answer 1

我认为它不会使用任何索引来进行 ORDER BY。但是你应该查看执行计划。或者在这里。

- Olivier Jacot-Descombes

Answer 2

我不明白你的查询。如果一行必须匹配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

为了避免不必要地混淆查询优化器，可以这样做。

现在回到你的问题：如果数据库知道底层访问将以正确的顺序返回行，则可以实现按顺序排列。对于索引，这意味着如果由 where 子句匹配的行以 order by 子句请求的顺序出现在索引中，则索引可以帮助排序。

这就是本例情况，因此数据库实际上可以通过 met_value_index1 进行索引范围扫描，以获取 datetime BETWEEN constant AND constant 的行，并检查这些行中每个是否符合 mp_nb=constant，但如果 mp_nb=constant 具有高选择性，则会检查比必要更多的行。换句话说，如果匹配的行在索引中连续出现，那么索引最有用，因为这意味着索引范围扫描只会触及实际需要返回的行。

因此，下面的索引对此查询将更有帮助：

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

由于所有匹配的行都将紧挨在索引中，而且这些行按照order by子句请求的顺序出现在索引中。我无法确定查询优化器是否足够聪明以理解这一点，因此您应该检查执行计划。

- meriton

---

谢谢您的解释和建议。我会尽快尝试并报告结果 - 目前远程机器已关闭 - 我很好奇会发生什么变化。 - Christian Ammer

Answer 3

在WHERE子句中出现的字段顺序必须与索引中的顺序相匹配。因此，根据您当前的查询，您需要一个按照rtu_nb、mp_nb和datetime顺序排列的索引。

- Anony

---

这不是真的，与问题也无关。 - Michael Fredrickson

在MySQL中，这是绝对正确的。特别是对于innodb引擎索引而言更是如此。请参考Meritons的回答或阅读Zawodnys的相关教材。 - Anony