記一次因線上mysql優(yōu)化器誤判引起慢查詢事件

前言:

     收到瘋狂的慢查詢及請(qǐng)求超時(shí)報(bào)警，通過(guò)metrics分析出來(lái)自mysql請(qǐng)求的異常，cli —> show proceslist 看到很多慢查詢。 先前該sql是沒(méi)有的，后面因?yàn)閿?shù)據(jù)量的增長(zhǎng)才出現(xiàn)了這問(wèn)題。 雖然feeds表大到一個(gè)億，但因?yàn)閒eeds流信息有近期熱的特征，所以不是因?yàn)?innodb_buffer_pool_size 低效引起的io頻繁。 后來(lái)經(jīng)過(guò)進(jìn)一步explain執(zhí)行計(jì)劃分析得出了原因，mysql查詢優(yōu)化器選擇了他認(rèn)為高效的索引。

mysql查詢優(yōu)化器大多數(shù)情況是靠譜的!  但是你的sql語(yǔ)言含有多個(gè)索引時(shí)就要注意了，往往最后的結(jié)果令人有些彷徨了。因?yàn)閙ysql同一個(gè)sql只能使用一個(gè)索引，那么選擇哪個(gè)呢？ 在數(shù)據(jù)量小時(shí)候，mysql優(yōu)化器會(huì)把主鍵索引后置，優(yōu)先使用 index和unique 。 當(dāng)你達(dá)到一個(gè)數(shù)據(jù)量級(jí)后，又因?yàn)槟愕牟樵儾僮饔?in ，那么mysql查詢優(yōu)化器很可能會(huì)選用主鍵的 ！

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

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

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

如下，該表的量級(jí)已經(jīng)到億級(jí)別了，因?yàn)橛凶銐蚨嗟腸ache前頂，又因?yàn)檫@樣那樣的原因，所以沒(méi)來(lái)的及做分庫(kù)分表。

問(wèn)題是這樣的， 當(dāng)數(shù)據(jù)量級(jí)不到一個(gè)億的時(shí)候，mysql優(yōu)化器選擇使用 index索引， 當(dāng)數(shù)據(jù)量級(jí)超過(guò)一個(gè)億后，mysql查詢優(yōu)化器選擇使用 主鍵索引了。  這樣帶來(lái)的問(wèn)題就是 查詢速度太慢。

這是正常情況下:

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語(yǔ)句，在數(shù)據(jù)量有較大變化后，mysql查詢優(yōu)化器對(duì)索引的選擇也有了變化。

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，強(qiáng)制查詢優(yōu)化器使用我們給出的index 。 我這里是python開發(fā)環(huán)境，常見(jiàn)的python orm都有force index，ignore index，user index 參數(shù)的。

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

那么我們應(yīng)該怎么預(yù)防這種 因?yàn)閿?shù)據(jù)的增進(jìn)，mysql優(yōu)化器選擇了一個(gè)低效索引的問(wèn)題呢？

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

我自己對(duì)于mysql優(yōu)化器選擇索引規(guī)則一知半解的，后面準(zhǔn)備花時(shí)間好好研究下規(guī)則