high performance sqls

1、執(zhí)行計(jì)劃中的type column規(guī)避all以及index類(lèi)型優(yōu)化range condition選擇適當(dāng)?shù)乃饕龍?zhí)行計(jì)劃中的extra column規(guī)避using temporary 以及using filesortNo dependent subquery！執(zhí)行計(jì)劃中的type column查看MySQL對(duì)查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃，在查詢(xún)語(yǔ)句前加EXPLAIN關(guān)鍵字即可。這一節(jié)我們談一談執(zhí)行計(jì)劃中非常關(guān)鍵的列：type。Type列的枚舉值有這樣幾個(gè)：All，index，range，ref，eq_ref,const(system),null。情況由壞到好，其中null最好，MySQL可以在optimi

2、zation階段完成查詢(xún)，甚至無(wú)需讀取數(shù)據(jù)。下面對(duì)幾個(gè)枚舉值的含義作詮釋?zhuān)?.All，代表MySQL不得不對(duì)數(shù)據(jù)表做全表掃描。這是最差的type，除非sql本身就是全表查詢(xún)，否則一定要想辦法優(yōu)化，改變type為其他值。2.Index，代表MySQL需要對(duì)數(shù)據(jù)表做索引序上的全表掃描，這種掃描代價(jià)甚至可能會(huì)大于all類(lèi)型，因?yàn)閽呙鑼?duì)于硬盤(pán)是隨機(jī)取，io代價(jià)高于順序掃描的可能性很高。執(zhí)行計(jì)劃中的type column3.Range，代表MySQL根據(jù)where條件內(nèi)的某個(gè)過(guò)濾條件可以做出一個(gè)index range，從而縮小訪問(wèn)范圍，前提是過(guò)濾條件相關(guān)字段上有索引。常見(jiàn)的出現(xiàn)場(chǎng)景如depart_dat

3、eXXXXX或者between and運(yùn)算符。需要注意的是，IN（）在函數(shù)參數(shù)數(shù)目不太多的時(shí)候，也可以達(dá)成range條件，但是太多可能會(huì)退化為allType。4.Ref，典型的index access，MySQL可以讓索引和一個(gè)引用值匹配，以達(dá)到快速定位的目的，這個(gè)值可能是sql中的常量，也可能是多表查詢(xún)中前一個(gè)表中的某個(gè)引用。一般地，如果我們的執(zhí)行計(jì)劃type字段為此值，且rows字段值不太大，那么這條執(zhí)行計(jì)劃的時(shí)間代價(jià)都會(huì)比較小。執(zhí)行計(jì)劃中的type column5.eq_ref,這種類(lèi)型表明MySQL知道它至多只需要返回一行數(shù)據(jù)。這種type會(huì)出現(xiàn)在查詢(xún)中拿表的主鍵或者含唯一索引的列和特

4、定引用值作對(duì)比的時(shí)候。這種type的執(zhí)行很高效，因?yàn)镸ySQL一旦發(fā)現(xiàn)了一個(gè)匹配值，即可直接返回，無(wú)須再考慮其他匹配可能。6.Constant，往往出現(xiàn)于表的主鍵等于某個(gè)引用值的條件出現(xiàn)在where條件中，mysql可以把這種查詢(xún)轉(zhuǎn)換為常量，非常高效。規(guī)避all以及index type這兩種執(zhí)行計(jì)劃的出現(xiàn)，往往意味著你的查詢(xún)內(nèi)沒(méi)有使用到合適的索引?？梢园凑找韵虏襟E檢查：1.表的可用索引有哪些？你的where查詢(xún)條件中有用到和索引相關(guān)的字段嗎？2.有沒(méi)有可能是你的where子句內(nèi)的查詢(xún)條件相關(guān)字段不是某個(gè)聯(lián)合索引的最左前綴？類(lèi)似：如果我們寫(xiě):where stock_type = 2 limit

5、200;這種情況下，mysql是無(wú)法走這個(gè)聯(lián)合索引的。3.若非聯(lián)合索引的問(wèn)題，可否在相關(guān)字段上加索引？加索引的基本原則是，字段對(duì)記錄的區(qū)分度足夠高（該字段的值集足夠大，不同值足夠多，區(qū)分效果好），且字長(zhǎng)不太長(zhǎng)。此原則系指導(dǎo)原則，無(wú)硬性標(biāo)準(zhǔn)，具體是否添加 需要結(jié)合具體場(chǎng)景。4.如果是多個(gè)查詢(xún)條件的與，且多重過(guò)濾的效果足夠好，可否考慮添加聯(lián)合索引?Mysql對(duì)range condition的優(yōu)化來(lái)看一個(gè)官網(wǎng)給出的例子：其中key1上含索引，而nonkey上則無(wú)索引。1.首先從原始的where子句開(kāi)始優(yōu)化2.首先，我們移除掉其中的nonkey=4以及key1 like %b條件，前者因?yàn)闊o(wú)法走索引

6、，后者則因?yàn)閙ysql不支持非最左前綴的索引匹配。為了確保結(jié)果集正確 ，我們?yōu)槠涮鎿Q上true。Mysql對(duì)range condition的優(yōu)化3. 目前的語(yǔ)句是這樣的： 可以注意到，key1 like abcde% or TRUE 是永真的，而key1 z是永假的，故這兩個(gè)條件可以被替換掉。替換之后4.把上面的語(yǔ)句中移除掉無(wú)必要的true和false，得到： 最后得到；至此range優(yōu)化工作完成。當(dāng)然，通過(guò)優(yōu)化后的條件走index lookup之后得到的記錄還需要經(jīng)過(guò)兩個(gè)無(wú)法走索引的條件的篩選。選擇合適的索引索引的目的，永遠(yuǎn)是讓MySQL快速定位記錄，縮短查詢(xún)時(shí)間的，所以選擇索引的宗旨，往往

7、是這個(gè)字段上的索引所帶來(lái)的區(qū)分度是否足夠高。比如，stk_nm.stouck_round ，系批次表，此表的round_code之前是無(wú)索引的，相關(guān)的sql查詢(xún)就比較慢，那么在它之上加索引到底有無(wú)必要呢？首先，以此字段作為過(guò)濾條件的sql語(yǔ)句執(zhí)行次數(shù)足夠高，有一萬(wàn)多次，每次平均1.3s，很有優(yōu)化的必要。其次，此字段對(duì)于記錄的篩選效果足夠好，關(guān)于此屬性，可以根據(jù)不重復(fù)的round_code總數(shù)目在表記錄中的占比大小來(lái)判定。故而盡管此字段屬varchar(19),字長(zhǎng)相對(duì)長(zhǎng)，但是有做索引的必要，做索引之后查詢(xún)效率可以得到大大提升。需要注意的是，變更非常頻繁的字段上盡量避免做index，諸如upda

8、te_time或者update_uid，因?yàn)镮nnoDB默認(rèn)的索引存儲(chǔ)方式是b樹(shù)，字段值變更之后相應(yīng)索引 也要變更，進(jìn)而引起整個(gè)b樹(shù)結(jié)構(gòu)的變化，是代價(jià)較高的操作。No dependent subquery！什么情況下會(huì)出現(xiàn)dependent subquery？獨(dú)立子查詢(xún)?cè)谏鲜龅膕ql中出現(xiàn)了，而它為何需要規(guī)避？MySQL會(huì)根據(jù)第一條執(zhí)行計(jì)劃，以stock_round表作為依賴(lài)，將獨(dú)立子查詢(xún)涉及的查詢(xún)語(yǔ)句執(zhí)行527442次！子查詢(xún)的特點(diǎn)就是，子查詢(xún)中的第一個(gè)SELECT語(yǔ)句直接依賴(lài)于外層結(jié)果集，這種強(qiáng)耦合的關(guān)系，造成了不必要的多次查詢(xún)，降低了查詢(xún)性能。在MySQL5.6及以上版本中，子查詢(xún)問(wèn)題得

9、到了很大程度上優(yōu)化，故此問(wèn)題在這個(gè)版本以上不是那么重要，但是我們公司用的是5.5，所以這種執(zhí)行計(jì)劃已然必須規(guī)避。No dependent subquery！最推薦的規(guī)避方案，是使用聯(lián)表方式替代獨(dú)立子查詢(xún)。如果遇到了聯(lián)表無(wú)法表達(dá)獨(dú)立子查詢(xún)的語(yǔ)義的時(shí)候，至少也應(yīng)該拆分查詢(xún)并在java代碼內(nèi)拼裝數(shù)據(jù)，而不能對(duì)獨(dú)立子查詢(xún)視而不見(jiàn)。執(zhí)行計(jì)劃中的extra列執(zhí)行計(jì)劃中的extra列中常見(jiàn)的枚舉值是using index，using where，using temporary 以及using filesort。首先談?wù)剈sing index。這里的using index，并不是在說(shuō)MySQL會(huì)對(duì)此執(zhí)行計(jì)劃走

10、索引，而是代表著MySQL可以走covering index，只讀b樹(shù)，甚至不去訪問(wèn)真實(shí)記錄，查詢(xún)效率很高！也就是說(shuō)，我們?nèi)绻赡艿脑?，?yīng)該盡量讓extra內(nèi)出現(xiàn)此hint。下面看幾個(gè)例子：e.g1為何能走using index？因?yàn)閟tock_type和id上都有索引，MySQL在檢索b樹(shù)的時(shí)候就能直接根據(jù)索引返回結(jié)果，無(wú)需再去訪問(wèn)真實(shí)數(shù)據(jù)記錄。執(zhí)行計(jì)劃中的extra列e.g2這個(gè)例子用到了一點(diǎn)不是很容易想得到的小技巧?？紤]這樣一個(gè)語(yǔ)句：SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;我

11、們考慮created字段上已經(jīng)有索引了，但是只要limit偏移量大些，查詢(xún)效率依然高不起來(lái)。那么有沒(méi)有什么辦法優(yōu)化呢？修改為以下格式：Select id,title,content from article where id in (select id from article order by created desc limit 10000,10);這樣修改之后，子查詢(xún)可以使用covering index，大幅提升查詢(xún)效率。e.g3SELECT COUNT(*) FROM articles WHERE category_id = .只要在category_id上做了索引，此查詢(xún)就可以走co

12、vering index，因?yàn)镸ySQL只需要在訪問(wèn)b樹(shù)的時(shí)候?qū)M(mǎn)足條件的記錄進(jìn)行計(jì)數(shù)，無(wú)需訪問(wèn)真實(shí)記錄。執(zhí)行計(jì)劃中的extra列一般形式：SELECT a FROM . WHERE b = .如果遇到了這種形式的sql，我們至少應(yīng)該考慮下是否要走covering index，即作出idx_b_a(b,a)這樣的索引。如果a不止一個(gè)字段，那么需要考量，因?yàn)樘L(zhǎng)的聯(lián)合索引維護(hù)代價(jià)很大。Using WhereUsing where的出現(xiàn)，表示MySQL在從存儲(chǔ)引擎讀取出記錄之后，需要根據(jù)過(guò)濾條件進(jìn)行后過(guò)濾（post-filter),using where和using index有點(diǎn)對(duì)立的意思，因?yàn)?/p>

13、一旦MySQL試用了covering index完成了查詢(xún) ，那么它不可能再去訪問(wèn)存儲(chǔ)引擎，也就不可能出現(xiàn)using where。Using temporary以及Using filesort這兩個(gè)hint的出現(xiàn)意味著你的sql有必要優(yōu)化了，因?yàn)镸ySQL不得不產(chǎn)生臨時(shí)表和做內(nèi)存內(nèi)或者硬盤(pán)內(nèi)額外排序以滿(mǎn)足需求。執(zhí)行計(jì)劃中的extra列Using temporary 很多時(shí)候是因?yàn)椴磺‘?dāng)?shù)膅roup by計(jì)劃引起的，下面談一談MySQL對(duì)于group by語(yǔ)句的優(yōu)化。比較好的group by execution plan應(yīng)該走index access，否則MySQL就只能創(chuàng)建臨時(shí)表了，這正是我們

14、不希望的。走index access有兩種方案：1.Loose index scan（松散索引掃描）觸發(fā)條件： I：?jiǎn)伪聿樵?xún)（多表查，且groupby子句內(nèi)字段非全是驅(qū)動(dòng)表有索引字段，或者不按照驅(qū)動(dòng)表索引有序，都會(huì)產(chǎn)生Using temporary）。 II：最左前綴原則?？紤]一個(gè)表有這樣的索引：idx(c1,c2,c3),那么group by c1 , group by c1,c2 都可以觸發(fā)，但是類(lèi)似group by c2,c3則無(wú)法觸發(fā)。III：集合函數(shù)僅允許包含min()以及max(),其中包含的列必須一致，且必須在聯(lián)合索引內(nèi)，或者含單字段索引。IV：包含varchar等變長(zhǎng)字段類(lèi)型等

15、，字段必須含全文索引，不支持字段上的左前綴索引。執(zhí)行計(jì)劃中的extra列如果loose index scan成功觸發(fā)，你應(yīng)該能在exrta內(nèi)看到Using index for group-by。下面的語(yǔ)句均可以觸發(fā)上述前提的LIS：在MySQL5.5中，增加了對(duì)以下函數(shù)的支持：AVG(DISTINCT), SUM(DISTINCT), and COUNT(DISTINCT) are supported. AVG(DISTINCT) and SUM(DISTINCT)take a single argument。Tight index scan：執(zhí)行計(jì)劃中的extra列在loose index

16、scan無(wú)法被觸發(fā)條件下，MySQL也能有另外一條途徑避免因group by引起臨時(shí)表的創(chuàng)建，即tight index scan，如果where子句內(nèi)含range condition，MySQL可以根據(jù)這個(gè)條件鎖定一個(gè)范圍的index ，從而避免臨時(shí)表，如果沒(méi)有，MySQL就做全表的index scan，也可以避免臨時(shí)表。Using filesort：這個(gè)hint往往是對(duì)記錄排序引起的。Using filesort意味著mysql需要對(duì)order by在內(nèi)存甚至硬盤(pán)內(nèi)做一次額外的排序，產(chǎn)生相應(yīng)的io代價(jià)等，如果能夠規(guī)避，需要規(guī)避。規(guī)避它的辦法，還是依靠索引，下圖所示的情況都可以讓mysql索引

17、排序，而非額外排序：執(zhí)行計(jì)劃中的extra列如圖示，可見(jiàn)除開(kāi)order by中的列均能對(duì)應(yīng)上相關(guān)索引以外，where字居中包含對(duì)索引列字段和常量之間的比較的時(shí)候，也是可以規(guī)避using Filesort的。然而在以下情況下，MySQL無(wú)法使用索引來(lái)解決order by，只能產(chǎn)生額外排序：執(zhí)行計(jì)劃中的extra列1.對(duì)不同索引列進(jìn)行排序，如Select * from t1 order by key1, key2其中key1和key2都是索引列。2.Order by字句跟的不是索引或者索引的最左前綴。3.既使用到了asc也使用到了desc，這時(shí)MySQL無(wú)法走索引以完成order by。4.Where字句內(nèi)的過(guò)濾條件對(duì)應(yīng)的索引和order by之后對(duì)應(yīng)的索引不匹配。5.Order by字句包含了表達(dá)式，包括函數(shù)表達(dá)式等。6. 多表聯(lián)查時(shí)，你想要order b