奇怪的慢SQL

我們先來看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ù)庫總數(shù)據(jù)為500w
通過acct_id過濾出來的結(jié)果集在1w條左右
查詢結(jié)果：第一條要5.018s齐苛，第二條0.016s
為什么會是這樣的結(jié)果呢吼畏？
acct_id和create_time都有索引妖啥，不應(yīng)該出現(xiàn)5s查詢時間這么慢啊

那么先來看執(zhí)行計劃

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

image

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

image

仔細(xì)觀察會發(fā)現(xiàn)陨晶，索引只使用了idx_create_time芝硬，沒有用到idx_acct_id
這能解釋第一條sql很慢蚜点，因?yàn)閣here查詢未用到索引，那么第二條為什么這么快拌阴？
看起來匪夷所思绍绘，其實(shí)搞清楚mysql查詢的原理之后，其實(shí)很簡單
我們來看這2條sql查詢皮官，都用到了where order by limit
當(dāng)有l(wèi)imit存在時脯倒，查詢的順序就有可能發(fā)生變化，這時并不是從數(shù)據(jù)庫中先通過where過濾再排序再limit
因?yàn)槿绻@樣的話捺氢，從500萬數(shù)據(jù)中通過where過濾就不會是5s了藻丢。
此時的執(zhí)行順序是，先根據(jù)idx_create_time索引樹摄乒，從最右側(cè)葉子節(jié)點(diǎn)悠反，反序取出n條，然后逐條去跟where條件匹配馍佑，若匹配上斋否，則得出一條數(shù)據(jù)，直至取滿10條為止拭荤，為什么第二條sql要快茵臭，因?yàn)檫\(yùn)氣好，剛好時間倒序的前幾條就全部滿足了舅世。
搞清楚原理之后旦委，我們了解了為什么第一條慢奇徒，第二條快的原因，但是問題又來了

為什么mysql不用idx_acct_id索引

這是一個問題缨硝，因?yàn)檫@樣的話摩钙，我們的建立的索引基本失效了，在此類sql下查詢效率將會是相當(dāng)?shù)筒楸纾驗(yàn)橥ㄟ^acct_id過濾出來的結(jié)果集比較大胖笛，有上萬條，mysql認(rèn)為按時間排序如果不用索引宜岛，將會是filesort,這樣會很慢长踊，而又不能2個索引都用上，所以選擇了idx_create_time谬返。

為什么mysql只用一個索引

這里為什么不能2個索引都用上之斯，可能很多人也不知道為什么，其實(shí)道理很簡單遣铝，每個索引在數(shù)據(jù)庫中都是一個索引樹佑刷，其數(shù)據(jù)節(jié)點(diǎn)存儲了指向?qū)嶋H數(shù)據(jù)的指針，如果用一個索引來查詢酿炸，其原理就是從索引樹上去檢索瘫絮，并獲得這些指針，然后去取出數(shù)據(jù)填硕，試想麦萤，如果你通過一個索引，得到過濾后的指針扁眯，這時壮莹，你的另一個條件索引如果再過濾一遍，將得到2組指針的集合姻檀，如果這時候取交集命满，未必就很快，因?yàn)槿绻總€集合都很大的話绣版，取交集的時候胶台，等于掃描2個集合，效率會很低杂抽，所以沒法用2個索引诈唬。當(dāng)然有時候mysql會考慮臨時建立一個聯(lián)合索引，將2個索引聯(lián)合起來用缩麸，但是并不是每種情況都能奏效铸磅，同樣的道理，用一個索引檢索出結(jié)果集之后，排序時愚屁，也無法用上另一個索引了济竹。

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

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

耗時：0.057s
可以看出改情況用idx_acct_id索引是比較快的霎槐，那么是不是這樣就可以了呢，排序未用上索引梦谜，始終是有隱患的丘跌。

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

我們來看下一條sql:

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

耗時: 1.999s
執(zhí)行計劃：

image

該sql通過acct_id過濾出來的結(jié)果集有100萬條，因此排序?qū)臅r較高唁桩，所幸這里只是取出前10條最大的然后排序闭树。
查詢概況，我們發(fā)現(xiàn)時間基本消耗在排序上荒澡，其實(shí)這是內(nèi)存排序报辱，對內(nèi)存消耗是很高的。

image

那么我們有沒有其它解決方案呢单山，這種sql是我們最常見的碍现，如果處理不好，在大數(shù)據(jù)量的情況下米奸，耗時以及對數(shù)據(jù)庫資源的消耗都很高昼接，這是我們所不能接受的，我們的唯一解決方案就是讓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

耗時: 0.016s

image

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

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

但是為什么能解決這個問題呢铡溪，這時大家可能就會記住一個死理漂辐，就是聯(lián)合索引可以解決where過濾和排序的問題，也不去了解其原理棕硫，這樣是不對的髓涯，因?yàn)楫?dāng)情況發(fā)生變化，就懵逼了饲帅，下面我們再看一個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

耗時：1.391s
索引還是用idx_acct_id_create_time复凳，時間居然慢下來了
執(zhí)行計劃是：

image

看執(zhí)行計劃，排序用到了filesort灶泵，也就是說育八，排序未用到索引。
那么我們還是來看看赦邻，索引排序的原理髓棋，我們先來看一個sql:

select * from acct_trans_log ORDER BY create_time limit 0,100

耗時：0.029s
執(zhí)行計劃為：

image

這里執(zhí)行的步驟是，先從索引樹中，按時間升序取出前100條按声，因?yàn)樗饕桥藕眯虻纳庞蹋苯幼笮虮闅v即可了。
因此签则，這里mysql并沒有做排序動作须床，如果想降序，則右序遍歷索引樹渐裂，取出100條即可豺旬，查詢固然快。

那么聯(lián)合索引的時候柒凉，是怎樣的呢族阅？

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

使用組合索引：idx_acct_id_create_time
這個時候，因?yàn)閍cct_id是聯(lián)合索引的前綴膝捞，因此可以很快實(shí)行檢索坦刀，如果sql是

select * from acct_trans_log WHERE  acct_id = 3095

出來的數(shù)據(jù)是按如下邏輯排序的
3095+time1
3095+time2
3095+time3
默認(rèn)是升序的，也就是說蔬咬，次sql相當(dāng)于

select * from acct_trans_log WHERE acct_id = 3095 order by create_time

他們是等效的鲤遥。
如果我們把條件換成order by create_time desc limit 0,10呢
這時候，應(yīng)該從idx_acct_id_create_time樹右邊葉子節(jié)點(diǎn)倒序遍歷计盒，取出前10條即可渴频。
因?yàn)閿?shù)據(jù)的前綴都是3095，后綴是時間升序北启。那么我們倒序遍歷出的數(shù)據(jù)卜朗，剛好滿足order by create_time desc，因此也無需排序咕村。
那么語句：

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

為什么排序無法用索引呢场钉？
我們先分析下索引的排序規(guī)則
已知：id1<id2<id3... time1<time2<time3....
查詢結(jié)果集排序如下：
id1+time1
id1+time2
id1+time3
id2+time1
id2+time2
id2+time3
索引出來的默認(rèn)排序是這樣的，id是有序的懈涛，時間是無序的逛万，因?yàn)橛?個id，優(yōu)先按id排序批钠，時間就是亂的了宇植，這樣排序?qū)胒ilesort，這就是慢的原因埋心，也是排序沒有用到索引的原因指郁。

查詢計劃使用以及使用說明

• table：顯示這一行數(shù)據(jù)是關(guān)于哪張表的
• type：顯示使用了何種類型，從最好到最差的連接類型為const,eq_ref,ref,range,index,all
• possible_keys：顯示可能應(yīng)用在這張表中的索引拷呆。如果為空闲坎，沒有可能的索引
• key：實(shí)際使用的索引疫粥，如果為null，則沒有使用索引腰懂。
• key_len：使用的索引的長度梗逮。在不損失精確性的情況下，長度越短越好
• ref：顯示索引的哪一列被使用了绣溜，如果可能的話慷彤，是一個常數(shù)
• rows：mysql認(rèn)為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)