如何加快我的 SQL 查詢？

我有一個查詢花費的時間比我預期的要長得多，我不知道如何加快它。

對於表結構的一些背景知識，這些是審計記錄。有一個父記錄，其中有許多子記錄。如果修改了父記錄，則會為其編寫審計（但不為子記錄編寫）。如果修改了子記錄，則會為父項和子項（而不是其他子項）寫入審計記錄。

這兩個表都有一個整數 ID 和一個修訂號。子表還有一個父 ID 列。通常訪問這些的方式是選擇父記錄，然後引入對父修訂號最近修訂的子記錄。

例如，如果父 A 的修訂為 100，而子 B 的記錄的修訂為 101、98 和 90。將為子 B 引入修訂 98。

現在，這通常是在每個父母的基礎上完成的，而且查詢非常快。我正在嘗試進行類似的查詢，該查詢將為我提供每個孩子的所有相關 ID 和修訂號。這是我的查詢 -

SELECT c.id AS cId, MAX(c.REV) AS cRev FROM Parent_AUD p 
INNER JOIN Child_AUD c ON p.id = c.parent_id AND p.REV >= c.REV
GROUP BY p.id, p.REV, c.id

這需要 20 多分鐘才能執行（我從來沒有真正讓它完成，所以我不確定它需要多長時間）。大約有 200 萬條父記錄和 1300 萬條子記錄，我認為這不需要那麼多時間。

這是 EXPLAIN 的輸出 -

+------+-------------+--------+-------+----------------------------------------------------+-----------------------------+---------+----------------------+---------+----------------------------------------------+
| id   | select_type | table  | type  | possible_keys                                      | key                         | key_len | ref                  | rows    | Extra                                        |
+------+-------------+--------+-------+----------------------------------------------------+-----------------------------+---------+----------------------+---------+----------------------------------------------+
|    1 | SIMPLE      | parent | index | PRIMARY,FK_PARENT_REV                              | FK_PARENT_REV               | 4       | NULL                 | 2150722 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | child  | ref   | FK_CHILD_REV,parent_id_index                       | parent_id_index             | 9       | db.p.id              |      68 | Using where; Using index                     |
+------+-------------+--------+-------+----------------------------------------------------+-----------------------------+---------+----------------------+---------+----------------------------------------------+

我在子表中的父 ID、子 ID、父修訂號、子修訂號和父 ID 上有單列索引。

有人對我如何加快速度有任何想法嗎？

編輯：這是兩個表的 SHOW CREATE TABLE 的輸出（更改了不相關的列名）-

Parent_AUD | CREATE TABLE `Parent_AUD` (
 `id` bigint(20) NOT NULL,
 `REV` int(11) NOT NULL,
 `REVTYPE` tinyint(4) DEFAULT NULL,
 `dateModified` datetime(6) DEFAULT NULL,
 `colOne` longblob DEFAULT NULL,
 `colTwo` int(11) DEFAULT NULL,
 `colThree` bigint(20) DEFAULT NULL,
 `colFour` bigint(20) DEFAULT NULL,
 `colFive` varchar(255) DEFAULT NULL,
 `colSix` tinyint(1) DEFAULT NULL,
 `colSeven` varchar(255) DEFAULT NULL,
 `colEight` bigint(20) DEFAULT NULL,
 `colNine` tinyint(1) DEFAULT NULL,
 `colTen` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`,`REV`),
 KEY `FK_PARENT_REV` (`REV`),
 CONSTRAINT `FK_PARENT_REV` FOREIGN KEY (`REV`) REFERENCES `REVINFO` (`REV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Child_AUD | CREATE TABLE `Child_AUD` (
 `id` bigint(20) NOT NULL,
 `REV` int(11) NOT NULL,
 `REVTYPE` tinyint(4) DEFAULT NULL,
 `colOne` longblob DEFAULT NULL,
 `colTwo` longblob DEFAULT NULL,
 `colThree` bit(1) DEFAULT NULL,
 `colFour` int(11) DEFAULT NULL,
 `colFive` longblob DEFAULT NULL,
 `parent_id` bigint(20) DEFAULT NULL,
 `colSix` varchar(255) DEFAULT NULL,
 `colSeven` tinyint(1) DEFAULT NULL,
 `colEight` longblob DEFAULT NULL,
 `colNine` tinyint(1) DEFAULT NULL,
 `colTen` tinyint(1) DEFAULT NULL,
 `dateModified` datetime(6) DEFAULT NULL,
 PRIMARY KEY (`id`,`REV`),
 KEY `FK_CHILD_REV` (`REV`),
 KEY `parent_id_index` (`parent_id`),
 CONSTRAINT `FK_CHILD_REV` FOREIGN KEY (`REV`) REFERENCES `REVINFO` (`REV`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

KEY `parent_id_index` (`parent_id`),

–>

KEY (parent_id, REV, id)

順序很關鍵。首先它過濾parent_id =，然後沒有範圍REV，最後必須id使索引“覆蓋”。

由於 JOIN 將永遠不會增加 MAX，因此您可以嘗試使用 EXISTS 進行過濾：

SELECT c.id AS cId, MAX(c.REV) AS cRev 
FROM Child_AUD c
WHERE EXISTS (
   SELECT 1
   FROM Parent_AUD p 
   WHERE p.id = c.parent_id 
     AND p.REV >= c.REV
)
GROUP BY c.id;

像這樣的索引：

CREATE INDEX ON Child_AUD (ID, PARENT_ID, REV);

可能會有所幫助。

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