mysql在大表上慢选择order by

Question

我在mysql中存储了一个很大的表(基本上就是一个日志)，如下所示：

  CREATE TABLE `log` (
    `ID_1` int(10) unsigned NOT NULL,
    `ID_2` int(10) unsigned NOT NULL,
    `DELTA` tinyint(3) unsigned NOT NULL,
    `ACTIVE` tinyint(1) NOT NULL,
    `DATA` bigint(20) unsigned NOT NULL,
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8$$

没有索引。数据在随机时刻与随机数据一起插入，它工作得很好。然后，该表将离线(没有对其进行读/写操作)。此时的表大小约为180M条记录。我添加了一个索引的ID_1，ID_2，增量，活动字段(所有4，升序)。它的工作速度相当快(3-4分钟)。

现在，我试图通过ID_1、ID_2、DELTA、ACTIVE (与索引相同的字段和顺序)从表中获取所有数据，但select会在“排序结果”(在SHOW PROCESSLIST中)中保留很长时间，直到返回第一行。我试图提示/强制SELECT语句使用索引(即FORCE index / use INDEX)，但没有任何区别。关于如何提高这种查询的响应速度的任何提示：

SELECT `ID_1`, `ID_2`, `DELTA`, `ACTIVE` FROM `log` ORDER BY `ID_1`, `ID_2`, `DELTA`, `ACTIVE` ASC;

这里提出了一个类似的问题：Slow ORDER BY in large table --但没有答案。虽然这是一个好主意，张贴喜欢它，也许有人已经知道答案了。

谢谢!

Answer 1

要有效地对数据进行排序，必须使用要在ORDER BY子句中使用的所有列来添加复合索引。索引中列的顺序必须与ORDER BY子句中的列顺序相同。

在您的情况下，它将是：

ALTER TABLE `log` ADD INDEX mySortIndex(`ID_1`, `ID_2`, `DELTA`, `ACTIVE`);

要检查MySQL是如何执行查询的，请使用：

EXPLAIN SELECT `ID_1`, `ID_2`, `DELTA`, `ACTIVE` FROM `log` ORDER BY `ID_1`, `ID_2`, `DELTA`, `ACTIVE` ASC;

输出将告诉您使用了哪些索引。

如果您只需要已知数量的结果行，则可以使用LIMIT阻止MySQL对整个表进行排序：

SELECT `ID_1`, `ID_2`, `DELTA`, `ACTIVE`
FROM `log` ORDER BY `ID_1`, `ID_2`, `DELTA`, `ACTIVE` ASC
LIMIT 100;