效能優化案例-SQL優化

最近剛做了系統遷移，需要將老系統的資料遷移到新的系統中，老系統中是分了100張表，表的資料量分配不均勻，有些表有40G左右的資料，而有些表的資料又比較少。

剛開始處理的方式是，先獲取該表資料的總量，然後進行分頁處理，遷移的sql如下：
第一步：
`

select count(1) from some_table where type_id = #type_id#;

`

第二步：

`
select * from some_table where type_id = #type_id# limit #offset#,#pageSize#;
`

如果對於小名單數量在100萬以內的，這個sql的效能還是挺能滿足的，但是當越到後面的名單的時候，SQL效能越來越差。

原因

limit有兩個引數，第一個引數是offset，第二個引數是取的資料量。當offset很大時，mysql需要掃描從頭到offset的資料量，這個時間會隨著offset越大而越大。

第一次優化

1. 使用id分割槽. 先獲取最小的id，然後按照id進行過濾，取最近100條。然後下次迴圈的時候，重新設定id值。
2. 再次迴圈獲取下一個100條資料。

select min(id) from some_table where type_id = #type_id#;
`select * from some_table where type_id = #type_id# and id > #min# order by id asc limit #pageSize#;
`

1. 通過上面的優化後，在100萬的名單隻需要10分鐘就可以查詢完畢，這個優化解決了大部分的名單遷移，但是在遷移到一些表的資料仍然不行，因為增加id的排序，導致每次的排序，需要做幾億的資料的掃描：

第二次優化

因為我們的目標就是將所有的資料全部讀出來，所以可以先把min和max的id取出來，然後通過id增加固定步調的方式來進行處理。

select min(id) from some_table where type_id = #typeId#

select max(id) from some_table where type_id = #typeId#

虛擬碼:
`
int pageSize = 500;
for(int i = minId; i<= maxId; i+=pageSize){

           select * from some_table where type_id =#type_id# where id between i and i+ pageSize;

}
`
由於沒有排序的操作，所以每次的取的資料範圍非常小，通過執行計劃也是可以看出：

總結

sql的寫法不同，執行計劃完全不同，作為開發人員，不能只單純的滿足業務功能就可以了，而是要想想自己寫得sql會怎麼執行，會走那些索引，資料量大得情況下會怎麼樣。並且要善於用一些工具幫助自己分析，(比如idb上得執行計劃的功能就非常棒), 共勉。