為什麼所有的查詢條件都命中索引還是那麼慢？記一次慢查詢最佳化過程

jeremy1127發表於2019-10-10
索引

話說，自從前段時間用granafa配好後端微服務prometheus監控的Dashboard後，我就有了一個新習慣，每天上班第一件事就是盯著這個Dashboard瞅一會兒。

你還別說，很快我就發現了問題，一些名字看似不復雜的查詢介面，卻慢到要2到3秒，這是很不正常的。

於是，我就是這樣盯上了一個介面。

這是一個查詢文章列表的介面，支援分頁，最終執行的SQL如下：

SELECT DISTINCT
    C.*,
    B.nickname,
    D.feed_id,
    D.home_feed,
    D.read_scope,
    D.publish_time
FROM
    XXcloud.mp_pubno_info A,
    XXcloud.wechat_pubno B,
    XXcloud.wechat_article C
        LEFT JOIN
    XXcloud.mp_feed_info D ON D.element_id = C.article_id
        AND D.element_type = 3
        AND D.valid = 1
        AND D.app_id = 'pf'
WHERE
     A.app_id = 'pf' AND A.pubno = C.pubno AND A.pubno = B.pubno
ORDER BY C.create_time DESC
LIMIT 0 , 20

咋一看這麼多表聯查，還有一個左聯接，心想能快就奇怪了！

一開始真不想理這段sql的業務邏輯，就快速用工具分析了一下查詢計劃，想著如果是沒加索引，那偷個懶:）加個索引就好了。

slow_analysis.png

結果一看，竟然全部都命中索引了… >_<!!!

心想有點麻煩呀，沒辦法只能耐著性子一點點看起。

首先，對於select部分，因為是C表在左的左聯，那麼distinct關鍵字是明顯不需要的。但是去掉後，也沒有快太多。

接著，因為個人風格偏好，內聯查詢更喜歡寫inner join on，於是，我把sql的from和where整理了一下，雖然看起來規整些，但並無任何提升。

這時，我把注意力轉移到limit部分，心想如果能早點做limit，這樣就可以減少表聯接笛卡爾積的集合大小。

突破口就在這裡！

當我把sql調整成，

SELECT
    C.*, B.nickname,
    D.feed_id,
    D.home_feed,
    D.read_scope,
    D.publish_time
FROM
    XXcloud.wechat_article C
INNER JOIN (
    SELECT
        C2.article_id
    FROM
        XXcloud.mp_pubno_info A
    INNER JOIN XXcloud.wechat_article C2 ON A.app_id = 'sc'
    AND A.pubno = C2.pubno
    ORDER BY
        C2.create_time DESC
    LIMIT 20
) ai ON C.article_id = ai.article_id
INNER JOIN XXcloud.wechat_pubno B ON C.pubno = B.pubno
LEFT JOIN XXcloud.mp_feed_info D ON D.valid = 1
AND D.app_id = 'sc'
AND D.element_type = 3
AND D.element_id = C.article_id
ORDER BY
    C.create_time DESC

再分析一下查詢計劃，

quick_analysis.png

清爽的看到query cost從1975降到107!

但還有最佳化空間，在這裡為了能夠一句sql實現查詢，使用了臨時表，實際上是可以在程式碼中透過兩次查詢，一次查詢出前20的article_id, 第二次查詢時，直接把這20個article_id當成in的命中條件即可。

話說，這次排查慢sql，打破了我原有的一個認知偏誤，以為命中了索引查詢速度就不會慢；同時也讓我加深了對左聯加內聯查詢效能消耗的認識，左聯真心是拖油瓶，本質上還是會將一個表的資料全部查出，如果這個表的資料還是逐漸增加的，那麼上了生產環境變慢是必然的。

值得反思的是，目前公司專案的後端是使用的微服務架構，但涉及到資料庫查詢方面還是比較隨便，同庫的表連線也許還可以接受，跨庫的表連線實在是後患無窮。

我能想到的未來改進方向是：

  • 要麼是加索引，不斷最佳化查詢計劃；
  • 要麼就是嚴格限制單表查詢，在程式碼中完成聯接等操作，方便以後分庫分表的擴充套件；
  • 特別複雜的需要group by的查詢考慮場景做離線的ETL，或者用引入ELK。

最後，各位看官，關於資料庫應用查詢方面的最佳化，你們有什麼好的實戰經驗可以分享一下嗎？

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章