小編的世界 優質文選 資料
字體大小:
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 更加合理。