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

愛可生雲數據庫

我們還分析了執行計劃改寫後的 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 更加合理。