程式設計師必知必會：MySQL上15個常見SQL最佳化技巧

無論你在面試，還是工作中，sql最佳化都是大家繞不過去的話題。為啥？隨著時間的積累，業務資料量的增多，SQL的執行效率對程式的執行效率的影響逐漸增大，相對於改造程式碼，最佳化SQL語句是正本最低的，所以此時對SQL的最佳化更顯得非常必要。

1.避免使用select *

實際業務場景中不需要所有的欄位，只需要其中一兩個，只查詢用到

（1）浪費資料庫資源，記憶體，cup

（2）查出來的資料多，透過網路IO傳輸過程中也會增加傳輸時間

（3）select * 不會走覆蓋索引，會出現回表

2.用union all 代替union

（1）union會排重

（2）排重過程需要遍歷，排序，比較，更消耗cpu資源

（3）確定唯一，沒有重複資料能用union all儘量用

3.小表驅動大表

（1）in 的話裡面驅動外面，in適合裡子查詢是小表

（2）exist 的話外面驅動裡面，適合外面是小表

4.批次插入

（1）mybatis plus 的insertBatch

（2）當然一次插入量也不能太大，可以分批插入。

5.多用limit

6.in中值太多

（1）查詢出來數量太大，限制一次最大查詢條數

（2）還可以，多執行緒查詢，最後把查詢出來的資料彙總。

7.增量查詢

select name，age from user where id>#{lastId} limit 100;

查詢比上次id 大的100條

8.高效的分頁

select id,name,age from user limit 10000, 20;

mysql會查詢10020條，然後丟棄前面10000條，這個比較浪費資源

可以最佳化：

select id,name,age from user id>10000 limit 20;

找到上次分頁最大id

假如id是連續的，並且有序，可以用between

注意： between要在唯一索引上分頁，不然會出現每頁大小不一致問題。

9.用連線查詢代替子查詢

MySQL如果需要在兩張以上表中查詢資料的話，一般有兩種實現方式

（1）子查詢

（2）連線查詢

select * from order where user_id in (select id from user where name='zhang'); 

子查詢可以透過in實現，優點：這樣簡單，

但缺點是，MySQL執行子查詢時，需要建立臨時表，查詢完成後再刪除臨時表，有一些額外開銷。

可以改成連線查詢:

select o.* from order o inner join user u on o.user_id = u.id where u.name='zhang'; 

10.join表不宜過多

阿里巴巴開發者手冊規定，join表不宜超過3個

如果join太多，MySQL在選擇索引時會非常複雜，很容易選錯索引。

並且沒有命中，nested loop join 就會分別從兩個表讀一行資料進行對比，時間複雜度n^2。

11.join時需要注意

join用的最多的時left join 和 inner join

（1）left join：兩個表的交集和左表的剩餘資料

（2）inner join：兩個表的交集

inner join mysql會自動選擇小表驅動，

left join 左邊的表驅動右邊的表

12.控制索引數量

索引不是越多越好，索引需要額外的儲存空間，B+樹儲存索引，額外的效能消耗。

阿里巴巴開發者手冊中規定，單表索引數量儘量控制在5個以內，且單個索引欄位數量控制在5個以內。

13.選擇合理的欄位型別

char：固定字串型別，該型別在的欄位在儲存空間上是固定的，固定長度的可以用

varchar：變長字串型別

（1）能用數字型別就不用字串，字串處理速度比數字型別慢

（2）儘量用小型別，比如：用bit存布林值，用tinyint存列舉值等。

（3）長度固定字串用char，不固定用varchar

14.提升group by效率

主要功能去重，分組

先過濾資料，減少資料，再分組

select id, name ,age from user

group by id

having id <50; 

這種寫法就不好，

select id, name ,age from user

where id <50

group by id; 

15.索引最佳化

強制走哪個索引

force index

select * from user 

force index(索引)