原文: mysql索引優(yōu)化

mysql 大數(shù)據(jù)分頁(yè)和索引使用

使用覆蓋索引

1. 一個(gè)表建立在id,create_time上建立了索引。
   

2. 如下2個(gè)sql語(yǔ)句，執(zhí)行時(shí)間一樣。 因?yàn)椴樵?xún)字段id被索引覆蓋。

           
             select id from order_manage where  create_time > '2014-01-01'
   
   order by create_time desc  limit 100000,10
   
   
   
   
   
   select a.id from order_manage a
   
   inner join ( select id from order_manage 
   
   where  create_time > '2014-01-01' 
   
   order by create_time desc limit 1000,10) b on a.id = b.id
           
         

3. 如下2條sql，使用inner join要快一個(gè)數(shù)量級(jí)。 inner join影響結(jié)果集仍然是$start +30,但是數(shù)據(jù)獲取的過(guò)程（Sending data狀態(tài)）發(fā)生在索引文件中，而不是數(shù)據(jù)表文件，這樣所需要的系統(tǒng)開(kāi)銷(xiāo)就比前一種普通的查詢(xún)低一個(gè)數(shù)量級(jí)，而主查詢(xún)的影響結(jié)果集只有30條，幾乎無(wú)開(kāi)銷(xiāo)。但是切記，這里仍然涉及了太多的影響結(jié)果集操作

   其實(shí)也可以分成2條sql語(yǔ)句來(lái)做，第一條使用覆蓋索引查詢(xún)出id，在使用in查詢(xún)出需要的字段數(shù)據(jù)。

           
             select * from order_manage where  create_time > '2014-01-01'
   
   order by create_time desc  limit 100000,10
   
   
   
   
   
   select * from order_manage a
   
   inner join ( select id from order_manage 
   
   where  create_time > '2014-01-01' 
   
   order by create_time desc limit 1000,10) b on a.id = b.id
           
         

上一頁(yè)，下一頁(yè)優(yōu)化

1. 背景，常見(jiàn)論壇帖子頁(yè) SQL: select * from post where tagid=$tagid order by lastpost limit $start, $end 翻頁(yè) 。索引為 tagid+lastpost 復(fù)合索引
   ?挑戰(zhàn)， 超級(jí)熱帖，幾萬(wàn)回帖，用戶(hù)頻頻翻到末頁(yè)，limit 25770,30 一個(gè)操作下來(lái)，影響結(jié)果集巨大(25770+30)，查詢(xún)緩慢。
   

2. 每次查詢(xún)的時(shí)候?qū)⒃擁?yè)查詢(xún)結(jié)果中最大的 $lastpost和最小的分別記錄為 $minlastpost 和 $maxlastpost

           
             上翻頁(yè)查詢(xún)?yōu)?
   
   select * from post where tagid=$tagid and lastpost<$minlastpost order by lastpost desc limit 30; 
   
   下翻頁(yè)為 
   
   select * from post where tagid=$tagid and lastpost>$maxlastpost order by lastpost limit 30;
   
   使用這種方式，影響結(jié)果集只有30條，效率極大提升。
           
         

order by排序優(yōu)化

1. 如下sql :

           
             select * from user where area=’$area’ and sex=’$sex’ order by lastlogin desc limit 0,30;
           
         

   建立復(fù)合索引并， area+sex+lastlogin 三個(gè)字段的復(fù)合索引（注意順序），order by的字段要在最后。where條件字段，唯一性最好的要在最前。

   Area+sex+lastlogin復(fù)合索引時(shí)（切記lastlogin在最后），該索引基于area+sex+lastlogin 三個(gè)字段合并的結(jié)果排序。
   也就是說(shuō)，建立了復(fù)合索引，少了一次排序操作。
   
2. 牢記數(shù)據(jù)查詢(xún)只能使用一個(gè)索引，每個(gè)字段建立獨(dú)立索引的情況下，也只能有一條索引被使用！
   

3. 復(fù)合索引的使用是符合左邊原則。a,b,c的復(fù)合索引
   abc,ab,a,可以使用索引,其他情況都不能使用索引。
   復(fù)合索引的使用原則是第一個(gè)條件應(yīng)該是復(fù)合索引的第一列必須使用，并且不能夸列。ac是不能使用索引的。

msyql索引使用原則

1. 牢記數(shù)據(jù)查詢(xún)只能使用一個(gè)索引，每個(gè)字段建立獨(dú)立索引的情況下，也只能有一條索引被使用！msyql會(huì)選擇最優(yōu)化的索引。當(dāng)然你可以強(qiáng)制使用索引，不過(guò)不建議這么做。
   
2. 在進(jìn)行索引分析和SQL優(yōu)化時(shí)，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎(chǔ)。涉及到復(fù)合索引情況，復(fù)合索引按照索引順序拼湊成一個(gè)字段，想象為單一有序序列，并以此作為分析的基礎(chǔ)。
   

3. 查詢(xún)條件與索引的關(guān)系決定影響結(jié)果集

   • 影響結(jié)果集不是輸出結(jié)果數(shù)，不是查詢(xún)返回的記錄數(shù)，而是索引所掃描的結(jié)果數(shù)。
     
   • 影響結(jié)果集越趨近于實(shí)際輸出或操作的目標(biāo)結(jié)果集，索引效率越高
     
   • 影響結(jié)果集與查詢(xún)開(kāi)銷(xiāo)的關(guān)系可以理解為線(xiàn)性相關(guān)。減少一半影響結(jié)果集，即可提升一倍查詢(xún)效率！當(dāng)一條搜索query可以符合多個(gè)索引時(shí)，選擇影響結(jié)果集最少的索引。
     
   • SQL的優(yōu)化，核心就是對(duì)結(jié)果集的優(yōu)化，認(rèn)識(shí)索引是增強(qiáng)對(duì)結(jié)果集的判斷，基于索引的認(rèn)識(shí)，可以在編寫(xiě)SQL的時(shí)候，對(duì)該SQL可能的影響結(jié)果集有預(yù)判，并做出適當(dāng)?shù)膬?yōu)化和調(diào)整。
     
   • 如果索引與查詢(xún)條件和排序條件完全命中，影響結(jié)果集就是limit后面的數(shù)字（$start + $end），比如 limit 200,30 影響結(jié)果集是230. 而不是30.
     
   • 如果索引只命中部分查詢(xún)條件，甚至無(wú)命中條件，在無(wú)排序條件情況下，會(huì)在索引命中的結(jié)果集 中遍歷到滿(mǎn)足所有其他條件為止。比如 select * from user limit 10; 雖然沒(méi)用到索引，但是因?yàn)椴簧婕岸魏Y選和排序，系統(tǒng)直接返回前10條結(jié)果，影響結(jié)果集依然只有10條，就不存在效率影響
     
   • ?如果搜索所包含的排序條件沒(méi)有被索引命中，則系統(tǒng)會(huì)遍歷是所有索引所命中的結(jié)果，并且排序。例如 Select * from user order by timeline desc limit 10; 如果timeline不是索引，影響結(jié)果集是全表，就存在需要全表數(shù)據(jù)排序，這個(gè)效率影響就巨大。再比如 Select * from user where area=’廈門(mén)’ order by timeline desc limit 10; 如果area是索引，而area+timeline未建立索引，則影響結(jié)果集是所有命中 area=’廈門(mén)’的用戶(hù)，然后在影響結(jié)果集內(nèi)排序。
     
4. 基于影響結(jié)果集的理解去優(yōu)化，不論從數(shù)據(jù)結(jié)構(gòu)，代碼，還是涉及產(chǎn)品策略上，都需要貫徹下去。核心就是小表驅(qū)動(dòng)大表，索引的使用要篩選出最少的結(jié)果集。
   
5. 涉及 limit $start,$num的搜索，如果$start巨大，則影響結(jié)果集巨大，搜索效率會(huì)非常難過(guò)低，盡量用其他方式改寫(xiě)為 limit 0,$num； 確系無(wú)法改寫(xiě)的情況下，先從索引結(jié)構(gòu)中獲得 limit $start,$num 或limit $start,1 ；再用in操作或基于索引序的 limit 0,$num 二次搜索。
   

6. 外鍵和join盡量不用

mysql索引優(yōu)化