記一次因線上mysql優化器誤判引起慢查詢事件的分享

這篇文章主要介紹了記一次因線上mysql優化器誤判引起慢查詢事件的相關資料以及最終的解決方案，分享給大家，希望能夠給大家一點啟發。

前言:

     收到瘋狂的慢查詢及請求超時報警，透過metrics分析出來自mysql請求的異常，cli —> show proceslist 看到很多慢查詢。 先前該sql是沒有的，後面因為資料量的成長才出現了這問題。 雖然feeds表大到一億，但因為feeds流資訊有近期熱的特徵，所以不是因為 innodb_buffer_pool_size 低效率引起的io頻繁。 後來經過進一步explain執行計劃分析得出了原因，mysql查詢優化器選擇了他認為高效的索引。

mysql查詢優化器大多數情況是可靠的!  但是你的sql語言含有多個索引時就要注意了，往往最後的結果令人有些徬徨了。因為mysql同一個sql只能使用一個索引，那麼選哪一個呢？ 在資料量小時候，mysql優化器會把主鍵索引後置，優先使用 index和unique 。 當你達到一個資料量級後，又因為你的查詢操作有 in ，那麼mysql查詢優化器很可能會選用主鍵的 ！

記住一句話，mysql查詢優化是基於檢索成本考慮，而不是基於時間成本考慮。 優化器是根據現有的資料狀態來推算代價，而不是真的去執行一遍sql.

所以，mysql優化器並不是每次都可以達到優化的效果的。 它並不能準確預估代價，如果要準確得到走各個索引的代價就要去真的執行一遍才能知道，所以代價分析只是做了一個預估，既然是預估那麼就有誤判。

我們這裡說的表是feed資訊流表，我們知道feeds資訊流表存取不僅頻繁，而且資料量也很大。 但是這個表的資料結構很簡單，索引也簡單.   總共就兩個索引，一個是主鍵索引， 一個是unique唯一鍵索引。

如下，該表的量級已經到億級別了，因為有足夠多的cache前頂，又因為這樣那樣的原因，所以沒來的及做分庫分錶。

問題是這樣的， 當資料量級不到一個億的時候，mysql優化器選擇使用index索引， 當資料量級超過一個億個後，mysql查詢優化器選擇使用主鍵索引了。  這樣帶來的問題就是 查詢速度太慢。

這是正常情況下:

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)     
AND cid IN (1001,1005,1054,1092,1093,1095)  
AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
  partitions: NULL
     type: range
possible_keys: PRIMARY,feed_user_target
     key: feed_user_target
   key_len: 6
     ref: NULL
     rows: 18
   filtered: 50.00
    Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

#同樣的sql語句，在資料量有較大變化後，mysql查詢最佳化器對索引的選擇也有了變化。

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)    
AND cid IN (1001,1005,1054,1092,1093,1095)    
AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
     type: range
possible_keys: PRIMARY,feed_user_target
     key: PRIMARY
   key_len: 4
     ref: NULL
     rows: 11873197
    Extra: Using where
1 row in set (0.00 sec)

那麼解決方法就是使用 force index，強制查詢最佳化器使用我們給的index 。 我在這裡是python開發環境，常見的python orm都有force index，ignore index，user index 參數的。

explain  SELECT * FROM `feed` force index (feed_user_target) WHERE user_id IN (116537309,116709093,116709377) ...

那我們該怎麼預防這種 因為資料的增進，mysql優化器選擇了一個低效索引的問題呢？

針對這個問題請教了幾個廠的dba，得到的答案和我們的方法是一樣的。 都是只能透過後期的慢查詢來發現問題，然後在sql語句中指定force index來解決索引問題。 另外，在系統上線初期就會做這類問題的規避，但往往業務開發人員初期都會配合dba們的審查工作，但後期為了省事，或者說自以為是認為沒有問題，所以造成了 mysql查詢事故。

我自己對於mysql優化器選擇索引規則一知半解的，後面準備花時間好好研究下規則

以上就是記一次因線上mysql優化器誤判引起慢查詢事件的分享的內容，更多相關內容請關注PHP中文網（www.php.cn）！