前情引子
in 會不會走索引？很多人肯定會回答、廢話、如果命中了索引、那肯定會走。
其實我和大多數人一樣、一開始也是這麼想的、直至有一個血淋淋的案子讓我有所改觀、有所思考。
背景介紹
業務的工單表、我們分了64張、以userId作為分表鍵、業務實際場景中未使用到搜尋引擎、主要是一些B端業務。
業務有一個場景是使用userId作為條件 使用in語句查詢工單資料。
這裡分析一下、
- 第一個userId作為分表鍵作為查詢條件是合理的
- 第二個、該業務場景下的SQL為userId欄位新增了索引、是考慮到的
實際發生問題
該需求上線之後、我們發現個別B端使用人員、他需要查詢userId為5w左右的條件查詢、經日誌查詢該查詢的耗時大概在35S左右、正常查詢都是3S以內。當問題發生的時候、我就在分析、in 到底有沒有走索引、如下
- 5w/64張表=781 個 假設按照平均分配 每個表的in包含的個是不足1k
- 第二個每張分表其實都是新增了索引的
- 資料庫的監控服務沒有查詢到有慢SQL出現
綜合以上初步判斷、這麼小的量、如果命中索引、那不該需要查詢這麼長的時間。
解決方案
既然出現了問題、那肯定是要解決方案的、思考的角度如下：
- 分表情況下、無法使用大家熟悉的explain 語句 直接查詢資料庫、讓資料庫告訴你有沒有使用索引、當然、如果你指定其中一張分表還是可以使用explain語句的
- 資料庫分表、DB的操作實際上是將每張表的查詢結果出來之後、全部load到記憶體聚合之後再返回給實際呼叫他的Java服務的
- 假設這裡命中了索引、基於第二點那慢的另一個因素可能就是DB伺服器記憶體被打滿了
這裡我基於第三點的假設、對於業務程式碼進行了改造
使用in條件進行查詢
限制了每次查詢資料庫in所包含的userId個數最多是5000個、即時就是我們經常說的批次查詢、這樣子做、最大量的5w就會分成10批去查詢資料庫、結果再聚合。而分到每張表的in包含的個數、按平均情況就只有了78個左右了、改成這種寫法、從宏觀的角度、就是把DB的一部分壓力轉移到業務伺服器上。
結果如何
新的程式碼拿到正式環境進行驗證之後、使用同樣的使用者進行測試、in的條件個數仍是5w、但最後的查詢結果僅在3S左右就返回了、完成了從35S到3S的質的飛躍的提升。
對於解決問題而言、我們已經是成功的Solver、We are white cat or black cat.
But 這裡有仍有兩個疑問、
批次的數量具體是哪個值合適2k or 5k、這裡我的5k值是與我的正常業務的水平相一致的、所以我說是適合我的、但並不是適合所有場景、所有人。
從最後的結果提升來看、我更傾向於改造後的程式碼既是走了索引、也為DB減少了壓力、才會有這麼高的效能提升。
我請教一位現世高人
- 索引的型別和質量：B-TREE、不需要回表查詢、完全命中。
- in條件值的分佈：分佈均勻可能會使用到索引
- 成本估算：MYSQL的查詢最佳化器會基於統計資訊對不同的執行計劃進行成本估算？全表嫂 or 還是用索引比較合適呢？
- 系統配置和資源限制：innodb-buffer-pool-size？系統的資源使用情況 都會影響執行計劃的選擇
- 資料庫的版本和配置：5.5及以上查詢最佳化器對in操作進行了最佳化、但仍舊不能保證。