提高跨多個表的查詢的查詢效率

我正在尋找方法來提高針對 PostgreSQL 和 MySQL 數據庫的一些相對複雜的查詢的性能。（數據庫支持的應用程序的要求之一是它必須盡可能“交叉兼容”）。

出於這個原因，我選擇使用 PHP 的 RedBeanPHP 項目來動態自動創建數據庫結構，因為我對數據庫管理不是很有經驗。查詢本身是使用我自己的自定義查詢建構器（一個 PHP 腳本）建構的，它與我自己的自定義 GUI 界面一起使用（如果需要，可以提供螢幕截圖）。

我正在執行以下查詢：

SELECT
   COUNT( DISTINCT( lead.id ) )
FROM lead WHERE lead.id IS NOT NULL
AND (  ( lead.fname IS NOT NULL AND lead.fname <> ? )  OR  ( lead.lname IS NOT NULL AND lead.lname <> ? )  )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.valid = :phone_valid )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.line_type IN ( ?,? ) )
AND  lead.id IN ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id WHERE email.valid_domain = :email_valid_domain )
AND  lead.country IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE :tag_tag )
AND  lead.fname <> :lead_fname

在超過 1000 萬條記錄的數據集上。目前執行此查詢需要 2 到 3 分鐘，這是有問題的，因為此查詢理想情況下應該在 1 分鐘內執行。

需要性能優化的 PostgreSQL 數據庫伺服器在 Amazon RDS db.r3.8xlarge 實例上執行，而應用程序在 r4.16xlarge 實例上執行，因此我現在幾乎已經為單個硬體實例提供了極限。

以下是已放置在以下列上的唯一約束/唯一索引（有些與此特定查詢無關，但上面的查詢是範例查詢）

• email.id
• email.email
• ip.ip
• language.lang
• lead.id
• leadmeta.id
• phone.number_numbers_only
• skype.skype

我非常感謝可以提供的任何建議，以幫助我朝著正確的方向前進。我已經考慮了以下選項，但我對數據庫管理的經驗或知識不足，無法了解它將如何影響系統：

• 向可搜尋表中的任何“可搜尋”列添加索引
• 創建臨時表以儲存來自子查詢的資訊，然後根據臨時表中的資訊執行頂級查詢
• 創建一個數據庫伺服器集群（我對此進行了一些研究，但我不明白它甚至不足以理解它是什麼）

請注意，因為我使用的是 AWS/RDS 伺服器，所以我沒有太多關於編輯數據庫伺服器配置本身的選項，或者至少我不知道如何去做。

編輯 1：查詢計劃

以下連結是我在 MySQL 和 PostrgreSQL 環境中執行“EXPLAIN”時的結果。請記住，MySQL 環境是一個開發/質量保證環境，總共有不到 10,000 條記錄。查詢本身是相同的，但顯然數據不是。

為了確保不失去任何資訊，我已將結果上傳並共享到共享的 Google 表格連結：

• MySQL 查詢計劃
• PostgreSQL 查詢計劃

編輯 2：填充查詢

我意識到如果沒有實際包含一些數據的範例查詢，工作起來會困難得多，所以這是一個查詢範例（沒有 COUNT ）

SELECT
   DISTINCT( lead.id )
FROM lead
WHERE lead.id IS NOT NULL
AND (  ( lead.fname IS NOT NULL AND lead.fname <> '' )  OR  ( lead.lname IS NOT NULL AND lead.lname <> '' )  )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.valid = '1' )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.line_type IN ( 'mobile','landline_or_mobile' ) )
AND  lead.id IN ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id WHERE email.valid_domain = '1' )
AND  lead.country IN ( 'AU','AT','CA','DK','FO','FI','DE','GL','HK','IS','IE','IT','JM','KW','LI','LU','MY','MC','NL','AN','NZ','NF','NO','PT','QA','SA','SG','ZA','ES','SE','CH','AE','GB','AX' )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE '%Binary%' )
AND  lead.fname <> 'None'  GROUP BY lead.id ORDER BY id ASC

編輯 3：更新

我已從DISTINCT查詢生成器中刪除，這縮短了查詢時間，但總體上我仍然遇到非常慢的性能

編輯 4：更新

因此，我重新編寫了查詢生成器，它正在建構查詢以生成看起來更高效的查詢。完成測試後我會發布結果，但我完全忽略的一件事是硬碟驅動器已滿，所以我正在調整它的大小並增加音量。全部完成後，我將更新這兩個過程的結果。

編輯 5：更新

新的查詢建構器在開發和 QA 中執行良好，但在生產中崩潰和燒毀。在這裡重新閱讀所有內容，看看有什麼用。

首先，當查詢不清楚也不合適時，討論索引是沒有意義的。

其次也是最重要的，當你使用這麼多條件時，為什麼還要使用 DISTINCT。

您是否無法消除重複記錄？在沒有數據的情況下，“DISTINCT”具有誤導性。

同樣，當您的查詢正確時，請使用 count(*)，同時您只能使用 *。

恕我直言，如果不正確，我的腳本方向是正確的。

SELECT
   *
FROM lead l  (  ( lead.fname IS NOT NULL AND lead.fname <> ? )  OR  ( lead.lname IS NOT NULL AND lead.lname <> ? )  )
AND  lead.country IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE :tag_tag )
AND  lead.fname <> :lead_fname

AND exists( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE  l.id=lead_phone.lead_id  and phone.valid = :phone_valid and phone.line_type IN ( ?,? ))

AND  exists ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id 
WHERE l.id=email_lead.lead_id and   email.valid_domain = :email_valid_domain )

查詢，更具可讀性：

SELECT DISTINCT( lead.id )
   FROM  lead
   WHERE  lead.id IS NOT NULL
     AND  ( ( lead.fname IS NOT NULL
                     AND  lead.fname <> '' )
             OR  ( lead.lname IS NOT NULL
                     AND  lead.lname <> '' ) 
          )
     AND  lead.id IN (
       SELECT  lead_phone.lead_id
           FROM  lead_phone
           LEFT JOIN  phone  ON lead_phone.phone_id = phone.id
           WHERE  phone.valid = '1' 
                     )
     AND  lead.id IN (
       SELECT  lead_phone.lead_id
           FROM  lead_phone
           LEFT JOIN  phone  ON lead_phone.phone_id = phone.id
           WHERE  phone.line_type IN ( 'mobile','landline_or_mobile' ) 
                     )
     AND  lead.id IN (
       SELECT  email_lead.lead_id
           FROM  email_lead
           LEFT JOIN  email  ON email_lead.email_id = email.id
           WHERE  email.valid_domain = '1' 
                     )
     AND  lead.country IN ( 'AU','AT','CA','DK','FO','FI','DE',
               'GL','HK','IS','IE','IT','JM','KW','LI','LU','MY','MC',
               'NL','AN','NZ','NF','NO','PT','QA','SA','SG','ZA','ES',
               'SE','CH','AE','GB','AX' 
                         )
     AND  lead.id IN (
       SELECT  lead_tag.lead_id
           FROM  lead_tag
           LEFT JOIN  tag  ON lead_tag.tag_id = tag.id
           WHERE  tag.tag LIKE '%Binary%' 
                     )
     AND  lead.fname <> 'None'
   GROUP BY  lead.id
   ORDER BY  id ASC

• 我們已經討論了神秘的lead.id IS NOT NULL. 如果我正確閱讀了 MySQL EXPLAIN，這不是一個大問題。（仍然更改為1=1將不那麼神秘。 TRUE是另一種選擇。）
• lead_phone LEFT JOIN phone被提及兩次，優化器未能結合這兩個參考——但你可能可以。簡單地結合WHERE條款。
• 這種模式：AND id IN ( SELECT lead_id FROM .. LEFT JOIN .. ON .. WHERE .. )似乎工作正常，但效率低下。刪除LEFT會（我認為）給你相同的答案，但更快： AND id IN ( SELECT lead_id FROM .. JOIN .. ON .. WHERE .. ). 根據您正在執行的版本，變成這樣可能會更好（或者優化器可能正在這樣做） AND EXISTS ( SELECT lead_id FROM .. JOIN .. ON .. WHERE .. )：.
• 我認為，但不能肯定地說，你是否違反了我的經驗法則之一：“規範化，但不要過度規範化”。我猜lead_phone,email_lead和lead_tag是用於規範化的“多：多”映射表嗎？
• 以下是我改進此類映射表中索引的技巧。
• 對於lead_tagand tag（也許是其他人），您最好擺脫tag並替換tag_id為實際的tag. （“過度規範化”的可能情況。）
• 有些列似乎是不必要NULL的。NOT NULL在適當的地方更改。（線索：key_len = 5。）

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