《Mysql學(xué)習(xí)記一次因線上mysql優(yōu)化器誤判引起慢查詢事件》要點：
本文介紹了Mysql學(xué)習(xí)記一次因線上mysql優(yōu)化器誤判引起慢查詢事件，希望對您有用。如果有疑問，可以聯(lián)系我們。

前言:MYSQL入門

???? 收到瘋狂的慢查詢及請求超時報警,通過metrics分析出來自mysql請求的異常,cli ―> show proceslist 看到很多慢查詢. 先前該sql是沒有的,后面因為數(shù)據(jù)量的增長才出現(xiàn)了這問題. 雖然feeds表大到一個億,但因為feeds流信息有近期熱的特征,所以不是因為 innodb_buffer_pool_size 低效引起的io頻繁. 后來經(jīng)過進一步explain執(zhí)行計劃分析得出了原因,mysql查詢優(yōu)化器選擇了他認為高效的索引.MYSQL入門

mysql查詢優(yōu)化器大多數(shù)情況是靠譜的!? 但是你的sql語言含有多個索引時就要注意了,往往最后的結(jié)果令人有些彷徨了.因為mysql同一個sql只能使用一個索引,那么選擇哪個呢? 在數(shù)據(jù)量小時候,mysql優(yōu)化器會把主鍵索引后置,優(yōu)先使用 index和unique . 當你達到一個數(shù)據(jù)量級后,又因為你的查詢操作有 in ,那么mysql查詢優(yōu)化器很可能會選用主鍵的 !MYSQL入門

記住一句話,mysql查詢優(yōu)化是基于檢索成本考慮,而不是基于時間成本考慮. 優(yōu)化器是根據(jù)現(xiàn)有的數(shù)據(jù)狀態(tài)來推算代價,而不是真的去執(zhí)行一遍sql.MYSQL入門

所以,mysql優(yōu)化器并不是每次都可以達到優(yōu)化的效果的. 它并不能準確預(yù)估代價,如果要準確得到走各個索引的代價就要去真的執(zhí)行一遍才能知道,所以代價分析只是做了一個預(yù)估,既然是預(yù)估那么就有誤判.MYSQL入門

我們這里說的表是feed信息流表,我們知道feeds信息流表訪問不僅頻繁,而且數(shù)據(jù)量也很大. 但是這個表的數(shù)據(jù)結(jié)構(gòu)很簡單,索引也簡單.?? 一共就兩個索引,一個是主鍵索引, 一個是unique唯一鍵索引.MYSQL入門

如下,該表的量級已經(jīng)到億級別了,因為有足夠多的cache前頂,又因為這樣那樣的原因,所以沒來的及做分庫分表.MYSQL入門

MYSQL入門

問題是這樣的, 當數(shù)據(jù)量級不到一個億的時候,mysql優(yōu)化器選擇使用 index索引, 當數(shù)據(jù)量級超過一個億后,mysql查詢優(yōu)化器選擇使用 主鍵索引了.? 這樣帶來的問題就是 查詢速度太慢.MYSQL入門

這是正常情況下: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語句,在數(shù)據(jù)量有較大變化后,mysql查詢優(yōu)化器對索引的選擇也有了變化.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,強制查詢優(yōu)化器使用我們給出的index . 我這里是python開發(fā)環(huán)境,常見的python orm都有force index,ignore index,user index 參數(shù)的.MYSQL入門

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

那么我們應(yīng)該怎么預(yù)防這種 因為數(shù)據(jù)的增進,mysql優(yōu)化器選擇了一個低效索引的問題呢?MYSQL入門

針對這個問題請教了幾個廠的dba,得到的答案和我們的方法是一樣的. 都是只能通過后期的慢查詢來發(fā)現(xiàn)問題,然后在sql語句中指定force index來解決索引問題. 另外,在系統(tǒng)上線初期就會做這類問題的規(guī)避,但往往業(yè)務(wù)開發(fā)人員初期都會配合dba們的審查工作,但后期為了省事,或者說自以為是認為沒有問題,所以造成了 mysql查詢事故.MYSQL入門

我自己對于mysql優(yōu)化器選擇索引規(guī)則一知半解的,后面準備花時間好好研究下規(guī)則MYSQL入門

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/4990.html