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索引最佳化