排序索引不適用於 MySQL

我有一張超過一百萬條記錄的表。我通過使用獲取數據LIMIT，但也ORDER BY。後者會導致巨大的性能問題。

我的查詢以這個結尾：

... ORDER BY `record`.`timestamp` DESC LIMIT 5;

執行此查詢需要 20 多秒。如果我刪除ORDER BY，執行時間mysql顯然0.00是一個巨大的差異。我知道它為什麼會發生 - 數據庫需要先對數據進行排序，然後再應用LIMIT它。我在分析時注意到該Creating sort index步驟需要很多時間，所以我決定創建一個這樣的索引：

alter table record add index timestampidx (timestamp desc);

不幸的是，它並沒有改變任何東西——執行時間和原來完全一樣。我正在執行的數據庫是通過docker-compose以下配置在本地生成的：

version: '3.9'
services:
 db:
   image: mysql:8
   ports:
     - "3306:3306"
   command: --default-authentication-plugin=mysql_native_password
   restart: always
   environment:
     MYSQL_ROOT_PASSWORD: db-password
   volumes:
     - ./data:/var/lib/mysql

我究竟做錯了什麼？我應該怎麼做才能使我的查詢更快？

編輯：

創建語句：

CREATE TABLE `record` (
 `id` int NOT NULL AUTO_INCREMENT,
 `name` varchar(64) NOT NULL,
 `thread` int NOT NULL,
 `timestamp` timestamp NOT NULL,
 `pass` int DEFAULT NULL,
 `fail` int DEFAULT NULL,
 `cyc_lmt` int DEFAULT NULL,
 `diag_err` int DEFAULT NULL,
 `not_run` int DEFAULT NULL,
 `pass_cyc` int DEFAULT NULL,
 `eff` float DEFAULT NULL,
 `cpu_lmt` int DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_index` (`name`,`thread`),
 KEY `thread` (`thread`),
 KEY `timestampidx` (`timestamp` DESC),
 CONSTRAINT `record_ibfk_1` FOREIGN KEY (`thread`) REFERENCES `thread` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4006386 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

完整的查詢：

   SELECT  `record`.`id`, `record`.`name`, `record`.`thread`, `record`.`timestamp`,
           `record`.`pass`, `record`.`fail`, `record`.`cyc_lmt`,
           `record`.`diag_err`, `record`.`not_run`, `record`.`pass_cyc`,
           `record`.`eff`, `record`.`cpu_lmt`,
           `thread`.`id`, `thread`.`name`, `thread`.`config`,
           `config`.`id`, `config`.`name`, `config`.`regression`,
           `regression`.`id`, `regression`.`name`, `regression`.`instance`,
           `instance`.`id`, `instance`.`name`
       FROM  (`record`
       INNER JOIN  (`thread`
       INNER JOIN  (`config`
       INNER JOIN  (`regression`
       INNER JOIN  `instance`  ON `regression`.`instance` = `instance`.`id`
                   )  ON `config`.`regression` = `regression`.`id`
                   )  ON `thread`.`config` = `config`.`id`
                   )  ON `record`.`thread` = `thread`.`id`
             )
       ORDER BY  `record`.`timestamp` DESC
       LIMIT  5;

執行計劃ORDER BY：

+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys      | key        | key_len | ref                       | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | instance   | NULL       | index | PRIMARY            | name       | 258     | NULL                      |   14 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | regression | NULL       | ref   | PRIMARY,instance   | instance   | 4       | regressions.instance.id   |  125 |   100.00 | NULL                                         |
|  1 | SIMPLE      | config     | NULL       | ref   | PRIMARY,regression | regression | 4       | regressions.regression.id |    7 |   100.00 | NULL                                         |
|  1 | SIMPLE      | thread     | NULL       | ref   | PRIMARY,config     | config     | 4       | regressions.config.id     |   10 |   100.00 | NULL                                         |
|  1 | SIMPLE      | record     | NULL       | ref   | thread             | thread     | 4       | regressions.thread.id     |    8 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+

沒有的執行計劃ORDER BY：

+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys      | key        | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | instance   | NULL       | index | PRIMARY            | name       | 258     | NULL                      |   14 |   100.00 | Using index |
|  1 | SIMPLE      | regression | NULL       | ref   | PRIMARY,instance   | instance   | 4       | regressions.instance.id   |  125 |   100.00 | NULL        |
|  1 | SIMPLE      | config     | NULL       | ref   | PRIMARY,regression | regression | 4       | regressions.regression.id |    7 |   100.00 | NULL        |
|  1 | SIMPLE      | thread     | NULL       | ref   | PRIMARY,config     | config     | 4       | regressions.config.id     |   10 |   100.00 | NULL        |
|  1 | SIMPLE      | record     | NULL       | ref   | thread             | thread     | 4       | regressions.thread.id     |    8 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+

考慮到 FROM 子句中的括號，查詢實際上是

SELECT ...
FROM       `instance`
INNER JOIN `regression` ON `regression`.`instance` = `instance`.`id`
INNER JOIN `config`     ON `config`.`regression` = `regression`.`id`
INNER JOIN `thread`     ON `thread`.`config` = `config`.`id`
INNER JOIN `record`     ON `record`.`thread` = `thread`.`id`
ORDER BY `record`.`timestamp` DESC LIMIT 5;

索引依據record (timestamp ASC/DESC)不能用於此查詢改進。

推薦 - 使用一些WHERE子句record.timestamp選擇一小部分行，但保證不少於 5 個。例如，WHERE record.timestamp > CURRENT_DATE - INTERVAL 1 DAY。並嘗試創建一個索引record (timestamp, thread)。

我想我從來沒有見過JOINs這樣的括號。建議你改成@Akina 的方式——不帶括號。這甚至可以解決性能問題。

如果不是，則將查詢由內向外翻轉：

SELECT ...
   FROM ( SELECT ... 
             FROM record
             ORDER BY timestamp DESC
             LIMIT 5
       ) AS r
   INNER JOIN ... ON ...
   INNER JOIN ... ON ...
   INNER JOIN ... ON ...
   INNER JOIN ... ON ...
   ORDER BY r.timestamp DESC   -- yes, again

但是，這可能無濟於事。這取決於是否JOINs擴展行數，從而使您開始的“5”無效。

而且，是的，替換INDEX(timestamp DESC)為 ‘covering’ INDEX(timestamp, thread)。（DESC在任何版本的 MySQL 上都無關緊要——在這種情況下。）

引用自：https://dba.stackexchange.com/questions/295451