MySQL - Left Join 耗時太長，如何優化查詢？
一個領導者可能有很多追隨者。當
notification_followers領導者添加帶有條目leader_id 1和notifiable_id 0（表中的 id 1,2）的文章時，表會收到一個通知。14噹噹前使用者被某人關注時，同一個表會收到一個通知，其中包含一個條目leader_id 0和notifiable_id 14（表中的 id 3）。
notification_followers（id 是 PRIMARY，除了 data 之外的每個欄位都是一個索引）| id | uuid | leader_id | notifable_id | data | created_at ----------------------------------------------------------------------------------- | 1 | 001w2cwfoqzp8F3... | 1 | 0 | Post A | 2018-04-19 00:00:00 | 2 | lvbuX4d5qCHJUIN... | 1 | 0 | Post B | 2018-04-20 00:00:00 | 3 | eEq5r5g5jApkKgd... | 0 | 14 | Follow | 2018-04-21 00:00:00所有關注者相關的通知現在都在一個地方，這是完美的。
我們現在需要檢查使用者
14是否是 的關注者，leader_id 1以了解是否向他們顯示通知1和2. 為此，我們掃描user_follows表以查看登錄使用者是否存在，followed_id以便leader_id他們知道通知，但前提是他們在發布通知之前關注了領導者（新關注者不應該在關注時收到較舊的發布通知使用者，只有新使用者）。
user_follows（id 是 PRIMARY，每個欄位都是自己的索引）| id | leader_id | follower_id | created_at ---------------------------------------------------- | 1 | 1 | 14 | 2018-04-18 00:00:00 // followed before, has notifs | 2 | 1 | 15 | 2018-04-22 00:00:00 // followed after, no notifs最後要注意的是，使用者應該知道通知是否被讀取，這就是
notification_followers_read表的來源。它儲存所有已讀通知的 以及它們的follower_id時間戳。notification_uuid``read_at
notification_followers_read（notification_uuid、follower_id 上的複合索引）| notification_uuid | follower_id | read_at -------------------------------------------------------- qIXE97AP49muZf... | 17 | 2018-04-21 00:00:00 // not for 14, we ignore it我們現在要返回按
nf.iduser 的自動遞增 desc排序的最新 10 條通知14。他們應該看到所有 3 條通知notification_followers，因為該使用者還沒有閱讀它們。第 2 次，因為他們在領導發帖之前跟隨領導，第 3 次通知，因為他們被關注並且他們notifiable_id是14。這是有效的查詢，但需要太長時間**~9 secs**：
SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at FROM notification_followers nf LEFT JOIN user_follows uf ON uf.leader_id = nf.leader_id AND uf.follower_id = 14 LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14 WHERE (nf.created_at > uf.created_at OR notifiable_id = 14) ORDER BY nf.id DESC LIMIT 10
notification_followers有大約 10 萬條記錄，我們正在使用 InnoDB。這是EXPLAIN查詢的：
我們如何優化查詢使其在幾毫秒內執行？
使用 SQL 轉儲更新
SQL DUMP TO REPRODUCE LOCALLY只需在本地創建
speed_test數據庫並導入文件即可查看所有表數據*（~100K 行）*的慢查詢問題。
評論摘要：
到目前為止，我得到了最好的結果
CREATE INDEX nfr_fid_nuuid ON notification_followers_read (follower_id, notification_uuid);和
CREATE INDEX uf_fid_lid ON user_follows (follower_id, leader_id);除主要索引外，所有其他索引均已刪除。因為
notification_followers它PRIMARY和我一起使用了索引。PRIMARY到目前為止，我找不到比這張桌子更好的東西了。測試是在 Windows 7 32 位上的 MySQL v5.7.21 32 位上完成的。
執行時間約為 4 秒，沒有上述索引，0.2 秒。
**關於如何，為什麼以及其他的一些內容：（**評論中沒有空間）
（免責聲明：我在這方面的知識總體上不應該是壞的。雖然，在某些方面我的理解可能是可改進的或完全錯誤的。如果我在某個地方錯了，任何人都可以隨時糾正我——歡迎編輯或評論。）
就性能而言，連接的一般情況：
正如評論中已經提到的，連接的一個目標是盡可能早地保持連接的集合盡可能小。舉例說明：在最壞的情況下，當必須應用嵌套循環連接時，
A JOIN B需要*#A*#B*（讓*#A是**A中的行數，模擬B*）比較操作。因此，可以在應用實際連接操作之前排除A（或B ）中的任何行，這將不僅減少1還減少*#B*（或*#A*）的操作數。人們會希望在性能方面做到這一點。如果可以通過索引完成連接，特別是在某種程度上，DBMS 可以輕鬆地定位與連接相關的索引部分（即保持集合較小），這可能是一個巨大的助推器。當然，索引在這裡還可以提供一些其他優點（例如：行已經可以通過排序方式訪問，支持更有效的連接方法，索引可能會明顯更小並且很大程度上適合記憶體，從而減少對恆定磁碟 IO 的需求， …）。
但所有這些都是一個主題，所以這只是一個粗略的摘要。
在查詢中，問題是關於：
現在首先要注意查詢：它是一個
LEFT OUTER JOIN（好吧實際上是兩個，但這對於這個想法並不重要）。notification_followers是這裡的左表，所以它的記錄集不會被連接減少，只是WHERE可以做到這一點。
WHERE不幸的是一個OR. 與AND. 它更像是一個聯合，因此保持集合很大，而不是像一個相交減少集合的基數（比較：對於A OR B結果集是所有行，WHERE A UNION所有行WHERE B，相比之下，A AND B結果集是所有行，WHERE A INTERSECT所有行WHERE B）。因此，這
WHERE並不是一個很有希望的目標，無法在一次執行中僅從一個索引中得到答案。此外，來自( ) 的ORed 操作之一取決於連接的數據，因此只能在連接之後（或最多同時）應用。WHERE``nf.created_at > uf.created_at還有
ORDER BY一個可能很昂貴，尤其是當結果集太大而無法保存在記憶體中時。然後需要通過不斷地從磁碟寫入和讀取來對其進行排序（對於更大的緩衝區）。並且磁碟訪問需要很多時間。所以我希望
notification_followers找到一個複合索引，它可以支持ORDER並且理想情況下至少有一個ORed 比較。如前所述，我失敗了。但鑑於上面對那部分的討論，我對此的期望也不是太高。或者
PRIMARY在 DBMS 看來就足夠了，這可能很好。據我了解，在 InnoDB 中具有主鍵的表實際上儲存為聚集索引。我在文件中找不到（很快）的是，如果記錄也按主鍵的順序雙重連結。這將允許通過該鍊錶的反向遍歷PRIMARY來支持，並且確實是一個不錯的選擇。ORDER``PRIMARY與和相比，連接表的
ONs 相當容易。（範例性我將使用與 的連接，是模擬的。）這裡我們有兩個相關的列，和。WHERE``ORDER``user_follows``notification_followers_read``leader_id``follower_id
follower_id似乎更適合複合索引的第一列。它與文字進行比較，因此獨立於連接的伙伴行。索引的相關部分，一個子樹——MySQL 中的“正常”索引是一些 B 樹變體——因此可以（重新）用於所有連接夥伴。還要注意從這裡減少可能的行集user_follows！然後，在該索引中也有
leader_id一個列應該使連接的user_follows’ 部分可以單獨從這個索引中回答。確實奏效了。請注意，語句中列的順序對於它們的索引不一定相同。任何可交換的東西都會被優化器交換，如果它承諾會更好的話。因此，無論如何都不一定會保留訂單。要為索引找到良好的列順序，必須首先考慮以最“激進”的方式對索引進行分區的順序（使剩餘部分盡可能小）。