優化慢查詢

幾天來，我正在嘗試優化此查詢。但是我仍然沒有找到正確的解決方案來解決這個問題。

內容表包含大約 2000 萬條記錄。由於連接表擁有大約 6000 萬個。

查詢執行大約 20 秒：

SELECT *
FROM `contents` AS `n`
INNER JOIN `connect` AS `a` ON `a`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
INNER JOIN `connect` AS `b` ON `b`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
INNER JOIN `connect` AS `c` ON `c`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
WHERE `a`.`mcded_child_id_mcdno_id` = '1375'
 AND `a`.`mcded_structure_mcdes_id` = '85'
 AND `b`.`mcded_child_id_mcdno_id` = '13'
 AND `b`.`mcded_structure_mcdes_id` = '187'
 AND `c`.`mcded_child_id_mcdno_id` IN ('500065',
                                   '500066',
                                   '500067',
                                   '500068')
 AND `c`.`mcded_structure_mcdes_id` = '211'
 AND `n`.`mcdno_structure_mcdns_id` IN ('1')
GROUP BY `n`.`mcdno_id`
ORDER BY `n`.`mcdno_id` DESC LIMIT 14
OFFSET 42

但它每次都使用臨時表。

這是解釋：

+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+
| 編號 | 選擇類型 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 額外 | |
+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+
| 1 | 簡單 | 乙 | 索引合併 | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id | 4,8 | 空 | 37928 | 使用相交（FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id）；使用哪裡；使用臨時的；使用文件排序 |
| 1 | 簡單 | c | 參考 | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | IND_primary_id_結構 | 12 | b.mcded_primary_id_mcdno_id,const | 1 | 使用位置 |
| 1 | 簡單 | n | eq_ref | 初級，FK_mcdno_structure_mcdns_id | 初級 | 8 | c.mcded_primary_id_mcdno_id | 1 | 使用位置 |
| 1 | 簡單 | 一個 | 參考 | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | 未設置 | 20 | b.mcded_primary_id_mcdno_id,const,const | 1 | |
+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+

我嘗試使用 connect (a) 作為主表，但情況更糟。

有什麼建議？

• 內容

 CREATE TABLE `contents` (
 `mcdno_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `mcdno_created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `mcdno_changed_on` timestamp NULL DEFAULT NULL,
 `mcdno_structure_mcdns_id` int(11) NOT NULL,
 PRIMARY KEY (`mcdno_id`),
 KEY `FK_mcdno_structure_mcdns_id` (`mcdno_structure_mcdns_id`),
 KEY `IND_created_on` (`mcdno_created_on`),
 KEY `IND_changed_on` (`mcdno_changed_on`),
 KEY `IND_published_on` (`mcdno_published_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 連接

CREATE TABLE `connect` (
`mcded_id` bigint(11) NOT NULL AUTO_INCREMENT,
`mcded_primary_id_mcdno_id` bigint(20) NOT NULL,
`mcded_child_id_mcdno_id` bigint(20) NOT NULL,
`mcded_changed_on` datetime DEFAULT NULL,
`mcded_structure_mcdes_id` int(11) NOT NULL
PRIMARY KEY (`mcded_id`),
UNIQUE KEY `UN_set` (`mcded_primary_id_mcdno_id`,`mcded_child_id_mcdno_id`,`mcded_structure_mcdes_id`),
KEY `IND_created_on` (`mcded_created_on`),
KEY `FK_primary_id_id` (`mcded_primary_id_mcdno_id`),
KEY `FK_structure_mcdns_id_id` (`mcded_structure_mcdes_id`),
KEY `FK_child_id_mcdno_id_id` (`mcded_child_id_mcdno_id`),
KEY `IND_primary_id_structure`         (`mcded_primary_id_mcdno_id`,`mcded_structure_mcdes_id`)
),     ENGINE=InnoDB DEFAULT CHARSET=utf8

“使用 intersect”通常意味著複合索引會更好：

INDEX(mcded_child_id_mcdno_id, mcded_structure_mcdes_id) -- or the opposite order

更好的是，重新排列UN_set，從這兩個開始。

SELECT *除非您確實需要所有連接中的所有列，否則不要使用。它禁止使用“覆蓋”索引。

（你不覺得這些長名字很難區分嗎？而且相似性（mcded/mcdno/mcdes/mcdns；connect/content）讓我難以閱讀。簡單地在任何地方扔“mcd”會有所幫助。）

SELECT * 讓你很難猜測你需要什麼輸出，但這裡有一個猜測：

SELECT ...
FROM contents AS n
JOIN connect AS a 
   ON a.mcded_primary_id_mcdno_id = n.mcdno_id
  AND n.mcdno_structure_mcdns_id = '1'
JOIN (
   SELECT '1375' as x, '85' as y
   UNION ALL
   SELECT '13', '187'
   UNION ALL
   SELECT '500065', '211'
   UNION ALL 
   SELECT '500066', '211'
   UNION ALL
   SELECT '500067', '211'
   UNION ALL 
   SELECT '500068', '211'
) AS t
   ON a.mcded_child_id_mcdno_id = t.x
  AND a.mcded_structure_mcdes_id = t.y
ORDER BY n.mcdno_id DESC 
LIMIT 14 OFFSET 42;

您可能想要使用臨時表而不是我使用的派生表。結果將分佈在多行中，因此您必須在應用程序中修復它。

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