提高具有一對多關係的 MYSQL 查詢的效能

Question

我的資料庫中有一個查詢需要 25 秒才能傳回結果，這太長了。看起來應該是很簡單的。兩張桌子；主表（文檔）是一個帶有一些資料列的標準表，連接表是一個只有兩個欄位（parent_id，division_id）的映射表。以前映射表上沒有索引，所以我添加了一個索引，並更改了“解釋”以包含索引，但似乎對效能沒有影響。

查詢如下所示：

explain SELECT DISTINCT doc.*  
FROM document doc  
LEFT JOIN multi_division_mapper divisions ON doc.id = divisions.parent_id  
WHERE doc.clientId = 'SOME_GUID'  
AND (divisions.division_id IS NULL OR divisions.division_id  IN ('SOME_GUID'));

解釋的結果是：

文檔中的總行數：6720 映射器中的總行數：6173

根據我收集到的信息，我需要改進「類型」或「額外」以使查詢更快。我在這裡能做什麼？

建立表格語句：

CREATE TABLE `document` (
   `id` varchar(36) NOT NULL,
   `addedBy` varchar(255) DEFAULT NULL,
   `addedDate` datetime NOT NULL,
   `editedBy` varchar(255) DEFAULT NULL,
   `editedDate` datetime NOT NULL,
   `deleted` bit(1) DEFAULT NULL,
   `clientId` varchar(36) NOT NULL,
   `departmentId` varchar(36) DEFAULT NULL,
   `documentParentId` varchar(36) DEFAULT NULL,
   `documentParent` varchar(50) DEFAULT NULL,
   `fileId` varchar(255) DEFAULT NULL,
   `fileUrl` varchar(600) DEFAULT NULL,
   `documentName` varchar(500) NOT NULL,
   `displayName` varchar(255) NOT NULL,
   `documentId` varchar(45) DEFAULT NULL,
   `notes` varchar(1000) DEFAULT NULL,
   `visibility` varchar(45) NOT NULL DEFAULT 'PRIVATE',
   `documentType` varchar(45) NOT NULL,
   `restrictDelete` bit(1) NOT NULL,
   `customData` text,
   `releaseDate` datetime NOT NULL,
   `expirationDate` datetime NOT NULL,
   `isApproved` bit(1) NOT NULL DEFAULT b'0',
   `userSupplier` varchar(36) DEFAULT NULL,
   `complianceCertificateId` varchar(36) DEFAULT NULL,
   `Status` varchar(50) DEFAULT 'NEUTRAL',
   PRIMARY KEY (`id`),
   KEY `idx_client` (`clientId`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 CREATE TABLE `multi_division_mapper` (
    `parent_id` varchar(36) NOT NULL,
    `division_id` varchar(36) NOT NULL,
    PRIMARY KEY (`parent_id`,`division_id`),
    KEY `idx_parent` (`parent_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

透過建立以下索引，我能夠在測試中獲得更有利的 EXPLAIN 報告：

ALTER TABLE multi_division_mapper
  DROP INDEX idx_parent,
  ADD INDEX (division_id, parent_id);

我還刪除了 idx_parent 因為它是多餘的；它是主鍵的前綴。

id	選擇類型	表	分割區	類型	可能的鍵	鍵	key_len	參考	行	已過濾	額外
1	簡單	文件	空	參考	idx_client	idx_client	110	常數	1	100.00	使用暫存
1	簡單	部門	空	參考	主要，division_id	部門 ID	38	常數	1	100.00	使用地點；使用索引；獨特