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

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

使用覆蓋索引

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

2. 如下2個sql語句，執(zhí)行時間一樣。 因為查詢字段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要快一個數(shù)量級。 inner join影響結(jié)果集仍然是$start +30,但是數(shù)據(jù)獲取的過程（Sending data狀態(tài)）發(fā)生在索引文件中，而不是數(shù)據(jù)表文件，這樣所需要的系統(tǒng)開銷就比前一種普通的查詢低一個數(shù)量級，而主查詢的影響結(jié)果集只有30條，幾乎無開銷。但是切記，這里仍然涉及了太多的影響結(jié)果集操作

   其實也可以分成2條sql語句來做，第一條使用覆蓋索引查詢出id，在使用in查詢出需要的字段數(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ōu)化

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

2. 每次查詢的時候?qū)⒃擁摬樵兘Y(jié)果中最大的 $lastpost和最小的分別記錄為 $minlastpost 和 $maxlastpost

           
             上翻頁查詢?yōu)?
   
   select * from post where tagid=$tagid and lastpost<$minlastpost order by lastpost desc limit 30; 
   
   下翻頁為 
   
   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;
           
         

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

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

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

msyql索引使用原則

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

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

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

6. 外鍵和join盡量不用

mysql索引優(yōu)化