More  

收藏本站

電腦請使用 Ctrl + D 加入最愛
手機請使用 收藏
關閉

小編的世界 優質文選 資料

MySQL 數據庫表上做查詢慢的原因-愛可生


字體大小:
2020年12月03日 -
:     
 

愛可生雲數據庫

我們還分析了執行計劃改寫後的 SQL,通過猜測,增加了 hint 來解決問題:

這一期,我們通過工具來分析一下:MySQL 為什麼會使用一個低效的執行計劃,以致於我們不得已用 hint 來調優 SQL?

實驗

我們接著使用 26 問中的環境,使用 optimizer trace 工具,觀察 MySQL 對 SQL 的優化處理過程。

我們先調大 optimizer trace 的內存容量(否則 trace 的輸出會被截斷),然後開啟了optimizer trace 功能。

跑完 SQL 後,可以在 INFORMATION_SCHEMA.OPTIMIZER_TRACE 看到 SQL 的優化處理過程:

這會是個巨大的 json,我們將其複制出來,找個 json 的可視化編輯器來分析一下。

小貼士

如果 MySQL 啟動時有配置 --secure-file-priv,那可以用,

SELECT TRACE INTO DUMPFILE <filename> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

將 trace 導出到文件裏,會更方便一些。

這裏我們選擇了一個在線的 json 編輯器,使用起來會方便一點:

可以看到整個優化過程分為 6 個步驟,前兩步都跟創建臨時表相關,然後是 join 的准備工作,再是兩步 join 優化,最後是 join 的執行。

回憶一下 26 問中,我們的子查詢應使用物化方式,但實際使用了 exists 子句方式,我們猜測這個選擇是在 join 的優化階段做出的。

仔細翻一翻,就會找到可疑的部分:

上圖中的中文,是從英文翻譯過來的。看上去我們找對了位置。

接下來我們逐步看看這個決策的依據是什麼:

顯然不物化的代價更小,那麼優化器選擇不物化是正確的選擇。

但使用 exists 子句進行子查詢的代價,顯然不可能為 0,MySQL 對這個代價的計算可能有誤。

我們得來看看 MySQL 是如何計算這個代價的:

執行 exists 子查詢的代價 = 執行一次子查詢的代價 * 子查詢需要執行的次數

顯然這個子查詢不可能只需要執行 0 次

這裏需要做一個額外的思考:在這個場景下,子查詢需要執行的次數,與父查詢的行數相同。

也就是紅框內需要執行的次數,取決於紅框外的 SQL 的結果集條數。

這裏 MySQL 將父表的結果集條數 稱為 "扇出度"(fanout)

顯然,這裏父表 information_schema.columns 的扇出度為 0,直接導致了優化器放棄了物化的策略

那 information_schema.columns 的扇出度為什麼是 0 呢?

查看 information_schema.tables 中對於 COLUMNS 表的描述,我們看到 MySQL 將 information_schema 中的元數據表做了特殊對待,其行數估計是沒有意義的。

到此我們找到了問題所在:MySQL 5.7 對元數據表使用了區別設計,與普通表的行數估算方式不同。

以後大家在 MySQL 5.7 中使用 information_schema 中的元數據表做複雜查詢時,需要額外注意執行計劃,可能需要使用 hint 指導優化器工作。

MySQL 8.0 中進行了數據字典的改造,information_schema 中的元數據表大部分都變成了視圖,其真實的數據源是 mysql 庫中的隱藏元數據表。

對 MySQL 8.0 的元數據表進行複雜查詢,執行計劃會比 MySQL 5.7 更加合理。