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

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。

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

相關文章