奇怪的慢sql

我們先來(lái)看2條sql

第一條：

select * from acct_trans_log WHERE  acct_id = 1000000000009000757 order by create_time desc limit 0,10

  

第二條：

 select * from acct_trans_log WHERE  acct_id = 1000000000009003061 order by create_time desc limit 0,10

表的索引及數(shù)據(jù)總情況：

 

索引：acct_id，create_time分別是單列索引，數(shù)據(jù)庫(kù)總數(shù)據(jù)為500w

通過(guò)acct_id過(guò)濾出來(lái)的結(jié)果集在1w條左右

 

查詢(xún)結(jié)果：第一條要5.018s，第二條0.016s

為什么會(huì)是這樣的結(jié)果呢？第一，acct_id和create_time都有索引，不應(yīng)該出現(xiàn)5s查詢(xún)時(shí)間這么慢啊

 

那么先來(lái)看執(zhí)行計(jì)劃

第一條sql執(zhí)行計(jì)劃：

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 第二條執(zhí)行計(jì)劃：

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 仔細(xì)觀(guān)察會(huì)發(fā)現(xiàn)，索引只使用了idx_create_time，沒(méi)有用到idx_acct_id

這能解釋第一條sql很慢，因?yàn)閣here查詢(xún)未用到索引，那么第二條為什么這么快？

看起來(lái)匪夷所思，其實(shí)搞清楚mysql查詢(xún)的原理之后，其實(shí)很簡(jiǎn)單

 

我們來(lái)看這2條sql查詢(xún)，都用到了where order by limit

當(dāng)有l(wèi)imit存在時(shí)，查詢(xún)的順序就有可能發(fā)生變化，這時(shí)并不是從數(shù)據(jù)庫(kù)中先通過(guò)where過(guò)濾再排序再limit

因?yàn)槿绻@樣的話(huà)，從500萬(wàn)數(shù)據(jù)中通過(guò)where過(guò)濾就不會(huì)是5s了。

 

 

此時(shí)的執(zhí)行順序是，先根據(jù)idx_create_time索引樹(shù)，從最右側(cè)葉子節(jié)點(diǎn)，反序取出n條，然后逐條去跟where條件匹配

若匹配上，則得出一條數(shù)據(jù)，直至取滿(mǎn)10條為止，為什么第二條sql要快，因?yàn)檫\(yùn)氣好，剛好時(shí)間倒序的前幾條就全部滿(mǎn)足了。

 

搞清楚原理之后，我們了解了為什么第一條慢，第二條快的原因，但是問(wèn)題又來(lái)了

為什么mysql不用idx_acct_id索引，這是一個(gè)問(wèn)題，因?yàn)檫@樣的話(huà)，我們的建立的索引基本失效了，在此類(lèi)sql下

查詢(xún)效率將會(huì)是相當(dāng)?shù)?/span>

 

因?yàn)橥ㄟ^(guò)acct_id過(guò)濾出來(lái)的結(jié)果集比較大，有上萬(wàn)條，mysql認(rèn)為按時(shí)間排序如果不用索引，將會(huì)是filesort,這樣會(huì)很慢，而又不能2個(gè)索引都用上

，所以選擇了idx_create_time。

 

為什么mysql只用一個(gè)索引

這里為什么不能2個(gè)索引都用上，可能很多人也不知道為什么，其實(shí)道理很簡(jiǎn)單，每個(gè)索引在數(shù)據(jù)庫(kù)中都是一個(gè)索引樹(shù)，其數(shù)據(jù)節(jié)點(diǎn)存儲(chǔ)了指向?qū)嶋H

數(shù)據(jù)的指針，如果用一個(gè)索引來(lái)查詢(xún)，其原理就是從索引樹(shù)上去檢索，并獲得這些指針，然后去取出數(shù)據(jù)，試想，如果你通過(guò)一個(gè)索引，得到過(guò)濾后的指針，這時(shí)，你的另一個(gè)條件索引如果再過(guò)濾一遍，將得到2組指針的集合，如果這時(shí)候取交集，未必就很快，因?yàn)槿绻總€(gè)集合都很大的話(huà)，取交集的時(shí)候，等于掃描2個(gè)集合，效率會(huì)很低，所以沒(méi)法用2個(gè)索引。當(dāng)然有時(shí)候mysql會(huì)考慮臨時(shí)建立一個(gè)聯(lián)合索引，將2個(gè)索引聯(lián)合起來(lái)用，但是并不是每種情況都能奏效，同樣的道理，用一個(gè)索引檢索出結(jié)果集之后，排序時(shí)，也無(wú)法用上另一個(gè)索引了。

 

實(shí)際上用索引idx_acct_id大多數(shù)情況還是要比用索引idx_create_time要快，我們舉個(gè)例子：

select * from acct_trans_log force index(idx_acct_id) WHERE  acct_id = 1000000000009000757 order by create_time desc limit 0,10

耗時(shí)：0.057s

可以看出改情況用idx_acct_id索引是比較快的，那么是不是這樣就可以了呢，排序未用上索引，始終是有隱患的。

 

 

聯(lián)合索引讓where和排序字段同時(shí)用上索引

我們來(lái)看下一條sql:

select * from acct_trans_log force index(idx_acct_id) WHERE  acct_id = 3095  order by create_time desc limit 0,10

耗時(shí): 1.999s

執(zhí)行計(jì)劃：

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 該sql通過(guò)acct_id過(guò)濾出來(lái)的結(jié)果集有100萬(wàn)條，因此排序?qū)?huì)耗時(shí)較高，所幸這里只是取出前10條最大的然后排序

查詢(xún)概況，我們發(fā)現(xiàn)時(shí)間基本消耗在排序上，其實(shí)這是內(nèi)存排序，對(duì)內(nèi)存消耗是很高的。

 

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 那么我們有沒(méi)有其它解決方案呢，這種sql是我們最常見(jiàn)的，如果處理不好，在大數(shù)據(jù)量的情況下，耗時(shí)以及對(duì)數(shù)據(jù)庫(kù)資源的消耗都很高

，這是我們所不能接受的，我們的唯一解決方案就是讓where條件和排序字段都用上索引

 

解決辦法就是建立聯(lián)合索引：

alter table acct_trans_log add index idx_acct_id_create_time(acct_id,create_time)

然后執(zhí)行sql:

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

耗時(shí): 0.016s

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 聯(lián)合索引讓where條件字段和排序字段都用上了索引，問(wèn)題解決了！

 

聯(lián)合索引使用的原理

但是為什么能解決這個(gè)問(wèn)題呢，這時(shí)大家可能就會(huì)記住一個(gè)死理，就是聯(lián)合索引可以解決where過(guò)濾和排序的問(wèn)題，也不去了解

其原理，這樣是不對(duì)的，因?yàn)楫?dāng)情況發(fā)生變化，就懵逼了，下面我們?cè)倏匆粋€(gè)sql：

select * from acct_trans_log force index(idx_acct_id_create_time) WHERE  acct_id in(3095,1000000000009000757)  order by create_time desc limit 0,10

耗時(shí)：1.391s

索引還是用idx_acct_id_create_time，時(shí)間居然慢下來(lái)了

執(zhí)行計(jì)劃是：

 

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 

  看執(zhí)行計(jì)劃，排序用到了filesort，也就是說(shuō)，排序未用到索引。

 

那么我們還是來(lái)看看，索引排序的原理，我們先來(lái)看一個(gè)sql:

select * from acct_trans_log ORDER BY create_time limit 0,100

耗時(shí)：0.029s

執(zhí)行計(jì)劃為：

平面設(shè)計(jì)培訓(xùn),網(wǎng)頁(yè)設(shè)計(jì)培訓(xùn),美工培訓(xùn),游戲開(kāi)發(fā),動(dòng)畫(huà)培訓(xùn)

 這里執(zhí)行的步驟是，先從索引樹(shù)中，按時(shí)間升序取出前100條，因?yàn)樗饕桥藕眯虻?，直接左序遍歷即可了

因此，這里mysql并沒(méi)有做排序動(dòng)作，如果想降序，則右序遍歷索引樹(shù)，取出100條即可，查詢(xún)固然快，

 

那么聯(lián)合索引的時(shí)候，是怎樣的呢？

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

使用組合索引：idx_acct_id_create_time

這個(gè)時(shí)候，因?yàn)閍cct_id是聯(lián)合索引的前綴，因此可以很快實(shí)行檢索，

如果sql是

select * from acct_trans_log WHERE  acct_id = 3095

出來(lái)的數(shù)據(jù)是按如下邏輯排序的

3095+time1

3095+time2

3095+time3

默認(rèn)是升序的，也就是說(shuō)，次sql相當(dāng)于

select * from acct_trans_log WHERE  acct_id = 3095 order by create_time

他們是等效的。

如果我們把條件換成order by create_time desc limit 0,10呢

這時(shí)候，應(yīng)該從idx_acct_id_create_time樹(shù)右邊葉子節(jié)點(diǎn)倒序遍歷，取出前10條即可

因?yàn)閿?shù)據(jù)的前綴都是3095，后綴是時(shí)間升序。那么我們倒序遍歷出的數(shù)據(jù)，剛好滿(mǎn)足order by create_time desc

因此也無(wú)需排序。

 

那么語(yǔ)句：

select * from acct_trans_log force index(idx_acct_id_create_time) WHERE  acct_id in(3095,1000000000009000757)  order by create_time desc limit 0,10

為什么排序無(wú)法用索引呢？

我們先分析下索引的排序規(guī)則

已知：id1<id2<id3...  time1<time2<time3....

查詢(xún)結(jié)果集排序如下：

id1+time1

id1+time2

id1+time3

id2+time1

id2+time2

id2+time3

 

索引出來(lái)的默認(rèn)排序是這樣的，id是有序的，時(shí)間是無(wú)序的，因?yàn)橛?個(gè)id，優(yōu)先按id排序，時(shí)間就是亂的了，

這樣排序?qū)?huì)用filesort，這就是慢的原因，也是排序沒(méi)有用到索引的原因。

  

查詢(xún)計(jì)劃使用以及使用說(shuō)明

table：顯示這一行數(shù)據(jù)是關(guān)于哪張表的

type：顯示使用了何種類(lèi)型，從最好到最差的連接類(lèi)型為const,eq_ref,ref,range,index,all

possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒(méi)有可能的索引

key：實(shí)際使用的索引，如果為null，則沒(méi)有使用索引。

key_len：使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話(huà)，是一個(gè)常數(shù)

rows：mysql認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)

 

作者：風(fēng)過(guò)無(wú)痕-唐
出處：http://www.cnblogs.com/tangyanbo/
本文以學(xué)習(xí)、研究和分享為主，歡迎轉(zhuǎn)載，但必須在文章頁(yè)面明顯位置給出原文連接。 如果文中有不妥或者錯(cuò)誤的地方還望高手的你指出，以免誤人子弟。如果覺(jué)得本文對(duì)你有所幫助不如【推薦】一下！如果你有更好的建議，不如留言一起討論，共同進(jìn)步！ 再次感謝您耐心的讀完本篇文章。歡迎加QQ討論群

http://www.cnblogs.com/tangyanbo/p/6378741.html