意外的極長查詢時間（使用嵌套的 WHEN-IN 約 5 分鐘）

第一次，我遇到了一個非常長的 MySQL 查詢執行時間（約 5 分鐘）。

數據庫中的數據是高度（而非任意）規範化的。它在組織和改組數據方面非常有效，以許多非常有用的方式顯示以用於不同的目的，除了這個特定的查詢正在向它扔扳手。

我無法理解它的原因。但是，一些背景資訊可能會為其他任意複雜的查詢提供一些啟示。

---

該公司已將世界劃分為許多團隊（宏觀區域）。根據他們的專業知識，每個人都屬於一兩個團隊。

• 例如，有許多不同的團隊。幾個例子是Spanish, Sahara, Iberia, Portuguese,Jungle團隊。每個團隊都與其他團隊有相當多的重疊，但在某種意義上是獨立的。
• 團隊與團隊Arabic密切合作，Sahara因為數據庫告訴他們，由於地理位置重疊，他們必須在某些任務上一起工作。和團隊也密切合作，他們都與團隊合作，團隊也與Spanish團隊合作，團隊也是如此。Portuguese``Americas``Europe``Portuguese``Africa``Arabic
• 每個團隊都有一組給定的區域，這些區域也不是該特定團隊獨有的。例如，該Mediterranean地區屬於大約 12 個團隊，當那裡發生事件時，他們都會一起工作。
• 每個國家都屬於一個或多個地區。Turkey屬於Central Asia,Europe甚至Mediterranean, 和其他一些人。

鑑於這一切，有必要向每個人展示他們團隊中的其他人在做什麼，以及不在他們團隊中但有重疊區域的人。

查詢 1完美地完成了這一點，而且非常快，不到 0.09 秒。

---

               SELECT report_name 
               FROM reports 
               WHERE region IN (
                   SELECT distinct region 
                   FROM macroregions
                   WHERE macroregion IN (
                       SELECT distinct macroregion
                       FROM users 
                       WHERE callsign = '$thisuser'
                   )
               ) 

report_name當查詢 1 認為登錄的個人應該知道它時，人們可以看到 的每次出現也很重要。Reports_names為每個地理位置自動生成和重複使用

• 如果我在East Asia團隊中並且我所在地區的某個人正在與Spanish團隊合作，那麼數據庫中將有兩個條目：

  • 20120210JOMX01 Japan Okinawa
  • 20120210JOMX01 Mexico Nuevo Leon

查詢 2接受查詢 1並在其周圍包裹一層WHEN-INs。它可以讓East Asia團隊成員知道他們團隊中的某個人正在工作Mexico。但是性能延遲是不可接受的，完全無法使用；完成一次查詢需要近5分鐘！

---

       SELECT * 
       FROM reports
       WHERE report_name IN (
           SELECT report_name 
           FROM reports 
           WHERE region IN (
               SELECT distinct region 
               FROM macroregions
               WHERE macroregion IN (
                   SELECT distinct macroregion
                   FROM users 
                   WHERE callsign = '$thisuser'
               )
           )    
       )

---

儘管查詢有效，但需要很長時間才能生效。同樣，這個特定查詢需要多長時間令人吃驚。而界面中的任何其他內容都沒有給我帶來任何類型的性能問題（即它們都需要不到一秒鐘的時間）。

我可以採取哪些步驟來解決此問題？

讓我們從您的原始查詢開始

  SELECT *  
   FROM reports 
   WHERE report_name IN ( 
       SELECT report_name  
       FROM reports  
       WHERE region IN ( 
           SELECT distinct region  
           FROM macroregions 
           WHERE macroregion IN ( 
               SELECT distinct macroregion 
               FROM users  
               WHERE callsign = '$thisuser' 
           ) 
       )     
   ) 

您只能（分階段）收集鍵，然後將鍵與reports表連接。這是我提出的新查詢

SELECT reports.* FROM
(
   SELECT rpts.report_name FROM
   (
       SELECT DISTINCT regions.region FROM
       (SELECT DISTINCT macroregion
       FROM users WHERE callsign = '$thisuser') users
       INNER JOIN
       (SELECT DISTINCT macroregion,region FROM regions) regions
       USING (macroregion)
   ) regionkeys
   INNER JOIN
   (SELECT region,report_name FROM reports) rpts
   USING (region)
) reportnamekeys
INNER JOIN reports
USING (report_name);

如果您對 USING 子句不滿意，這是我在不使用 USING 子句的情況下提出的新查詢：

SELECT reports.* FROM
(
   SELECT rpts.report_name FROM
   (
       SELECT DISTINCT regions.region FROM
       (SELECT DISTINCT macroregion
       FROM users WHERE callsign = '$thisuser') users
       INNER JOIN
       (SELECT DISTINCT macroregion,region FROM regions) regions
       ON users.macroregion = regions.macroregion
   ) regionkeys
   INNER JOIN
   (SELECT region,report_name FROM reports) rpts
   ON regionkeys.region = rpts.region
) reportnamekeys
INNER JOIN reports
ON reportkeys.report_name = reports.report_name;

您將需要一些索引來支持子查詢

ALTER TABLE users   ADD INDEX callsign_macroregion_ndx (callsign,macroregion);
ALTER TABLE regions ADD INDEX macroregion_region_ndx (macroregion,region);
ALTER TABLE reports ADD INDEX region_report_name_ndx (region,report_name);
ALTER TABLE reports ADD INDEX report_name_ndx (report_name);

前 3 個索引稱為覆蓋索引。之所以這樣稱呼它們，是因為子查詢只需要那些確切的列。因此，無需從表中讀取。數據僅從索引中獲取。

• http://peter-zaitsev.livejournal.com/6949.html
• http://ronaldbradford.com/blog/tag/covering-index/
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/

您還應該研究如何重構查詢以收集鍵、儘早執行 WHERE 子句以及最後執行 JOIN。這是關於如何做到這一點的很棒的 YouTube 影片：http: //youtu.be/ZVisY-fEoMw（基於本書：重構 SQL 應用程序）

試一試 ！！！

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