mysql - LIMIT 和未索引的列似乎阻止了 ORDER BY 优化

Question

接下来是我的表格。有些列不需要重现，但这是我真实世界的实际表格，而且它很小，所以我发布了整个内容:

CREATE TABLE `Alarms` (
  `AlarmId`        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '32-bit ID',
  `Code`           BIGINT(20) UNSIGNED NOT NULL,
  `GenerationTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ExpiryTime`     TIMESTAMP NULL DEFAULT NULL,
  `ExpiryCause`    ENUM('natural','artificial') DEFAULT NULL,
  `AckTime`        TIMESTAMP NULL DEFAULT NULL,
  `ClearTime`      TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (`AlarmId`),
  KEY `AlarmExpiry` (`ExpiryTime`),
  KEY `AlarmLevels` (`AckTime`,`ClearTime`),
  KEY `AlarmTime`   (`GenerationTime`),
) ENGINE=INNODB;

现在，至少，如何重现我的问题。

---

以下使用 AlarmTime 索引进行快速排序和 LIMITing:

SELECT
   `AlarmId`
FROM
   `Alarms`
ORDER BY
   `GenerationTime` DESC
 , `AlarmId` DESC
LIMIT
   2055820, 20

解释:

id    select_type    table   type   possible_keys   key         key_len   ref      rows      Extra
1     SIMPLE         Alarms  index  (NULL)          AlarmTime   4         (NULL)   2050259   Using index

---

以下不是。它缓慢地进行文件排序(大约 200 万行需要几秒钟):

SELECT
   `AlarmId`
 , `Code`
FROM
   `Alarms`
ORDER BY
   `GenerationTime` DESC
 , `AlarmId` DESC
LIMIT
   2055820, 20

解释:

id    select_type    table   type   possible_keys   key         key_len   ref      rows      Extra
1     SIMPLE         Alarms  ALL    (NULL)          (NULL)      (NULL)    (NULL)   2050259   Using filesort

---

我不明白为什么 LIMIT 似乎阻止我选择任何不构成表键一部分的列。

两个看似相关的手册页 [1][2] 似乎没有提到这种情况。

我错过了表索引的哪些细微差别？

[1]: https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
[2]: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

Answer 1

在对我的 MySQL 服务器进行了一些测试之后，我只能确认 Sloan Thrasher 所说的......

优化器实际上会考虑所选列以及排序依据列。添加以下索引是所需要的。

ALTER TABLE alarms 
ADD INDEX GenerationTime_AlarmId_Code (GenerationTime DESC, AlarmId DESC, Code);

更新:

此后我也确认了您的数据集...以 DESC 顺序创建上述组合索引会产生最佳结果。在我的系统上从 2.278 秒下降到 0.765 秒。

作为旁注，如果您的 Order By 中不需要 AlarmId DESC，

(例如，如果您有 2 个具有完全相同的 GenerationTime 的 AlarmId，您不关心列表中第一个显示哪个 AlarmId)

我没有理由使用它。所以我说将其从 Order by 中删除。 Makes 稍微快一点。 “.749”

另一方面，如果您需要您的 AlarmId 始终以相同的顺序列出，您还需要按确定性列 (PK) 进行分组以确保它们以相同的顺序显示。参见 Here

如果在使用和不使用 LIMIT 时确保相同的行顺序很重要，请在 ORDER BY 子句中包含额外的列以使顺序具有​​确定性。