原文: mysql索引優化

mysql 大數據分頁和索引使用

使用覆蓋索引

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

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

   其實也可以分成2條sql語句來做，第一條使用覆蓋索引查詢出id，在使用in查詢出需要的字段數據。

           
             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
           
         

上一頁，下一頁優化

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

2. 每次查詢的時候將該頁查詢結果中最大的 $lastpost和最小的分別記錄為 $minlastpost 和 $maxlastpost

           
             上翻頁查詢為 
   
   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;
   
   使用這種方式，影響結果集只有30條，效率極大提升。
           
         

order by排序優化

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 三個字段合并的結果排序。
   也就是說，建立了復合索引，少了一次排序操作。
   
2. 牢記數據查詢只能使用一個索引，每個字段建立獨立索引的情況下，也只能有一條索引被使用！
   

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

msyql索引使用原則

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

3. 查詢條件與索引的關系決定影響結果集

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

6. 外鍵和join盡量不用

mysql索引優化