問(wèn)題描述
今天遇到一個(gè)問(wèn)題坛掠，在一個(gè)報(bào)表導(dǎo)出的功能中赊锚，需要導(dǎo)出用戶(hù)的某些記錄的所有數(shù)據(jù)，有些用戶(hù)有多達(dá)10萬(wàn)的記錄屉栓，導(dǎo)出過(guò)程非常慢舷蒲，需要10多秒。經(jīng)過(guò)排查友多，發(fā)現(xiàn)是查詢(xún)數(shù)據(jù)庫(kù)的時(shí)候的問(wèn)題牲平，查詢(xún)數(shù)據(jù)居然用了7秒左右。起初看到sql沒(méi)發(fā)現(xiàn)什么問(wèn)域滥，sql寫(xiě)得中規(guī)中矩纵柿，只查詢(xún)需要的列，使用索引启绰，遵循索引規(guī)則等等昂儒，但是它就是要那么長(zhǎng)時(shí)間。
問(wèn)題分析
表面上看不出問(wèn)題委可，那只能用explain大法來(lái)分析分析了
原sql（這里隱去關(guān)鍵信息）
select drawno,col from `tb_loxxx` where drawno >'15010101' order by drawno desc;
expalin結(jié)果
從extra里出現(xiàn)了Using filesort渊跋，說(shuō)明這里是沒(méi)有走索引的，而且type為ALL着倾，說(shuō)明進(jìn)行了一次全表掃描拾酝。而該表確實(shí)對(duì)drawno字段建立了索引，那么是什么原因?qū)е逻@里出現(xiàn)Using filesort呢卡者？在繼續(xù)分析前先來(lái)了解下Using filesort蒿囤。如果對(duì)mysql稍有了解的話(huà)，就會(huì)知道Using filesort代表著mysql進(jìn)行了排序操作崇决。下面是官方的描述
Using filesort：
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.
Mysql需要額外的一次傳遞材诽，以找出如何按排序順序檢索行镶摘，通過(guò)根據(jù)聯(lián)接類(lèi)型瀏覽所有行并為所有匹配where子句的行保存排序關(guān)鍵字和行的指針來(lái)完成排序，然后關(guān)鍵字被排序岳守，并按排序順序檢索行凄敢。
這句話(huà)直接翻譯過(guò)來(lái)有點(diǎn)難以理解，簡(jiǎn)單說(shuō)就是由于索引不滿(mǎn)足你的sql湿痢，mysql需要對(duì)數(shù)據(jù)行進(jìn)行一次額外的排序操作涝缝，這個(gè)排序操作==既費(fèi)空間又費(fèi)時(shí)間==。當(dāng)數(shù)據(jù)量較少的時(shí)候并不會(huì)對(duì)應(yīng)用產(chǎn)生多大影響譬重，但數(shù)據(jù)量一多拒逮，就會(huì)出現(xiàn)非常可怕的后果臀规，輕則服務(wù)響應(yīng)變慢滩援，重則拖垮服務(wù)，甚至引發(fā)雪崩效應(yīng)導(dǎo)致應(yīng)用宕機(jī)塔嬉。
再回來(lái)看看我的sql玩徊，查詢(xún)列和搜索條件應(yīng)該都沒(méi)有問(wèn)題，那么應(yīng)該order by影響了谨究。通過(guò)搜索發(fā)現(xiàn)恩袱，order by 使用不當(dāng)確實(shí)會(huì)導(dǎo)致索引失效。
解決方案
1.強(qiáng)制索引 FORCE INDEX（key）
force index 的作用是讓mysql強(qiáng)制使用某個(gè)索引胶哲，對(duì)應(yīng)的有ignore index 強(qiáng)制忽略索引畔塔。除非非常明確sql目的和運(yùn)行效率情況下，一般不推薦使用這2個(gè)操作：一是強(qiáng)制索引可能導(dǎo)致其他索引失效鸯屿，二是強(qiáng)制索引不一定會(huì)提高sql效率澈吨，還會(huì)導(dǎo)致mysql優(yōu)化器沒(méi)有作用，三是強(qiáng)制使用的索引如果被刪除了寄摆，會(huì)導(dǎo)致程序異常谅辣。
強(qiáng)制索引使用方式如下：
select drawno,col from `tb_loxxx` force index(tb_loxxx_01) where drawno >'15010101' order by drawno desc;
再次進(jìn)行expalin
可以看到使用了索引，type也變成了range冰肴。再次執(zhí)行sql屈藐，發(fā)現(xiàn)sql的效率又回到正常水平了，僅用300多毫米就完成了這次查詢(xún)熙尉。
2.聯(lián)合索引
聯(lián)合索引就行將需要的列組成一個(gè)索引联逻。這個(gè)需要根據(jù)具體sql分析。
針對(duì)我這個(gè)sql检痰，是因?yàn)槲业膐rder by 和搜索列不匹配包归，order by drawno，而搜索列除了drawno铅歼，還有其他列公壤，根據(jù)sql條件换可，我建立相應(yīng)索引后，sql效率也回歸到正常水平厦幅。
從效率上來(lái)說(shuō)聯(lián)合索引和強(qiáng)制索引基本是一樣沾鳄，從應(yīng)用上來(lái)說(shuō)聯(lián)合索引會(huì)好一些，但是如果sql復(fù)雜或者數(shù)據(jù)庫(kù)設(shè)計(jì)的混亂确憨，可能造成一個(gè)表建立一堆索引的情況译荞，索引太多對(duì)表性能也有很多影響，最根本的解決方案還是簡(jiǎn)化sql休弃，去掉不必要的查詢(xún)等等吞歼。具體使用哪種方式，就看大家自己需要了塔猾。
