碼農唐磊 程式猿石頭
(請原諒我, 标題黨一回, 花幾分鐘看看, 或許對你有幫助)
最近工作上遇到一個”神奇”的問題, 或許對大家有幫助, 是以形成本文.
問題大概是, 我有兩個表 TableA, TableB, 其中 TableA 表大概百萬行級别(存量業務資料), TableB 表幾行(新業務場景, 資料還未膨脹起來), 語義上 TableA.columnA = TableB.columnA, 其中 columnA 上建立了索引, 但查詢的時候确巨慢無比, 基本上到 5-6 秒, 明顯跟預期不符合.
下面我以一個具體的例子來說明吧, 模拟其中的 SQL 查詢場景.
user_info 表, 為了場景盡量簡單, 我隻 mock 了其中的三列資料.
user_score 表, 其中 uid 和 user_info.uid 語義一緻.
其中資料情況如下, 都是很常見的場景.
索引情況是
查詢業務場景: 已知 user_score.id, 需要關聯查詢對應user_info的資訊, (大家先忽略這個具體業務場景是否合理哈). 那麼對應的 SQL 很自然的如下:
請忽略其中的資料, 我剛開始 mock 了 100W, 然後又重複導入了兩遍, 是以資料有一些重複. 300W 資料, 最後查詢出來也是 1.18 秒. 按道理應該更快的. 老規矩 explain 看看啥情況?
發現 user_info表沒用上索引, 全表掃描近 300W 資料? 現象是這樣, 為什麼呢?
你不妨思考一下, 如果你遇到這種場景, 應該怎麼去排查?
(分割線, 花 10 秒想想?)
我當時也是”一頓操作猛如虎”, 然并卵? 嘗試了什麼多種 sql 寫法來完成這個操作. 比如更換Join表的順序(驅動表/被驅動表), 再比如用子查詢. 最終, 還是沒有結果. 但直接單表查詢寫 SQL 确能用上索引.
嘗試更換檢索條件, 比如更換 uid 直接關聯查詢, 索引仍然用不上, 差點放棄了都. 在準備求助 DBA 前, 看了下表的建表語句.
完全有理由懷疑因為字元集不一緻的問題導緻索引失效的問題了.
于是修改了小表(真實線上環境可别亂操作)的字元集與大表一緻, 再測試下.
果然 work 了.
其實深究原因, 就是網上各種 MySQL軍規/規約所提到的, “索引列不要參與計算”. 這次這個 case, 如果知道 explain extended + show warnings 這個工具的話, (以前都不知道explain後面還能加 extended 參數), 可能就盡早”恍然大悟”了. (最新的 MySQL 8.0版本貌似不需要另外加這個關鍵字).
看下效果. (啊, 我還得把字元集改回去!!!)
(滑動看右邊)
索引列參與計算了, 每次都要根據字元集去轉換, 全表掃描, 你說能快得起來麼?
至于這個問題為什麼會發生? 綜合來看, 就是因為曆史原因, 老業務場景中的原表是假 utf8, 新業務新表采用了真 utf8mb4.
考慮新表的時候, 忽略和原庫字元集的比較. 其實, 發現庫裡面的不同表可能都有不同的字元集, 不同人建的時候可能都依據個人喜好去選擇了不同的字元集. 由此可見, 開發規範有多重要.
雖然知道索引列不能參與計算, 但這個場景下都是相同的類型, varchar(64) 最終查詢過程中仍然發生了類型轉換. 是以需要把字段字元集不一緻等同于字段類型不一緻.
如果這個 case, 利用 fail-fast 的理念的話, 發現不一緻, 直接不讓 join 會不會更好? (就像 char v.s varchar 不能 join 一樣).
說明: 本文測試場景基于 MySQL 5.6, 另外, 本文案例隻是為了說明問題, 其中的 SQL 并不規範(例如盡量别用 select * 之類的), 請勿模仿(模仿了我也不負責圖檔). 為了寫本文, 可花了不少時間, 建 DB, mock資料, 包括排版公衆号(啊,公衆号背景對代碼格式還是不友好, markdown 轉來代碼格式還是有問題)等等, 如果覺得有用, 還望你幫忙"在看", "轉發". 最後留一個思考題供讨論, 歡迎留言說出你的看法.
你能解釋如下情況嗎? 查詢結果表現為何不一緻? 注意一下 SQL 的執行順序, 查詢優化器工作流程, 以及其中的 Using join buffer (Block Nested Loop), 可以多看看 MySQL 官方手冊 深入了解背後的過程和原理.
阿裡雲ECS彈性計算服務是阿裡雲的最重要的雲服務産品之一。彈性計算服務是一種簡單高效，處理能力可彈性伸縮的計算服務。我們始終緻力于利用和創造業界最新的前沿技術，讓更多的客戶輕松享受這些技術紅利，在雲上快速建構更穩定、安全的應用，提升運維效率，降低IT成本，使客戶更專注于自己的核心業務創新。彈性計算重新定義了人們使用計算資源的方式，這一新的方式正在并且将一直影響着關于計算資源的生态和經濟圈。我們正在創造曆史，我們真誠地邀請您加入我們的隊伍。
最近團隊釋放不少 HC, 誠招 P6/P7/P8 的同學, 本組同學主要招聘後端研發同學(JD在此), 感興趣的同學可掃描下面二維碼加我聯系.
另外, 2021 屆校招/實習生崗位也正在進行中(詳情請戳), 如果你是 2020-11 — 2021-07 月之間畢業, 同時對阿裡巴巴感興趣, 也歡迎聯系我幫忙内推.
explain-extended 文檔
mock資料生成器
Block Nested-Loop and Batched Key Access Joins
點閱讀原文, 有相關連結, 若覺得有用, 請右下角點選"在看", 幫忙轉發, 感謝.