15?種?SQL?優(yōu)化中，老司機才懂的處理技巧
時間：2021-12-07 14:00:17
手機看文章
掃描二維碼
隨時隨地手機看文章
[導讀]前言SQL優(yōu)化是一個大家都比較關注的熱門話題，無論你在面試，還是工作中，都很有可能會遇到。如果某天你負責的某個線上接口，出現(xiàn)了性能問題，需要做優(yōu)化。那么你首先想到的很有可能是優(yōu)化SQL語句，因為它的改造成本相對于代碼來說也要小得多。那么，如何優(yōu)化SQL語句呢？這篇文章從15個方面...
前言
SQL優(yōu)化是一個大家都比較關注的熱門話題，無論你在面試，還是工作中，都很有可能會遇到。
如果某天你負責的某個線上接口，出現(xiàn)了性能問題，需要做優(yōu)化。那么你首先想到的很有可能是優(yōu)化SQL語句，因為它的改造成本相對于代碼來說也要小得多。
那么，如何優(yōu)化SQL語句呢？
這篇文章從15個方面，分享了SQL優(yōu)化的一些小技巧，希望對你有所幫助。
一、避免使用select *
很多時候，我們寫SQL語句時，為了方便，喜歡直接使用select *，一次性查出表中所有列的數(shù)據(jù)。
反例：
select * from user where id=1;在實際業(yè)務場景中，可能我們真正需要使用的只有其中一兩列。查了很多數(shù)據(jù)，但是不用，白白浪費了數(shù)據(jù)庫資源，比如：內(nèi)存或者cpu。
此外，多查出來的數(shù)據(jù)，通過網(wǎng)絡IO傳輸?shù)倪^程中，也會增加數(shù)據(jù)傳輸?shù)臅r間。
還有一個最重要的問題是：select *不會走覆蓋索引，會出現(xiàn)大量的回表操作，而從導致查詢SQL的性能很低。
那么，如何優(yōu)化呢？
正例：
select name,age from user where id=1;SQL語句查詢時，只查需要用到的列，多余的列根本無需查出來。
二、用union all代替union
我們都知道SQL語句使用union關鍵字后，可以獲取排重后的數(shù)據(jù)。
而如果使用union all關鍵字，可以獲取所有數(shù)據(jù)，包含重復的數(shù)據(jù)。
反例：
(select * from user where id=1) union (select * from user where id=2);排重的過程需要遍歷、排序和比較，它更耗時，更消耗cpu資源。
所以如果能用union all的時候，盡量不用union。
正例：
(select * from user where id=1) union all(select * from user where id=2);除非是有些特殊的場景，比如union all之后，結果集中出現(xiàn)了重復數(shù)據(jù)，而業(yè)務場景中是不允許產(chǎn)生重復數(shù)據(jù)的，這時可以使用union。
三、小表驅(qū)動大表
小表驅(qū)動大表，也就是說用小表的數(shù)據(jù)集驅(qū)動大表的數(shù)據(jù)集。
假如有order和user兩張表，其中order表有10000條數(shù)據(jù)，而user表有100條數(shù)據(jù)。
這時如果想查一下，所有有效的用戶下過的訂單列表。
可以使用in關鍵字實現(xiàn)：
select * from orderwhere user_id in (select id from user where status=1)也可以使用exists關鍵字實現(xiàn)：
select * from orderwhere exists (select 1 from user where order.user_id = user.id and status=1)前面提到的這種業(yè)務場景，使用in關鍵字去實現(xiàn)業(yè)務需求，更加合適。
為什么呢？
因為如果SQL語句中包含了in關鍵字，則它會優(yōu)先執(zhí)行in里面的子查詢語句，然后再執(zhí)行in外面的語句。如果in里面的數(shù)據(jù)量很少，作為條件查詢速度更快。
而如果SQL語句中包含了exists關鍵字，它優(yōu)先執(zhí)行exists左邊的語句（即主查詢語句）。然后把它作為條件，去跟右邊的語句匹配。如果匹配上，則可以查詢出數(shù)據(jù)。如果匹配不上，數(shù)據(jù)就被過濾掉了。
這個需求中，order表有10000條數(shù)據(jù)，而user表有100條數(shù)據(jù)。order表是大表，user表是小表。如果order表在左邊，則用in關鍵字性能更好。
總結一下：
- in 適用于左邊大表，右邊小表。
- exists 適用于左邊小表，右邊大表。
不管是用in，還是exists關鍵字，其核心思想都是用小表驅(qū)動大表。
四、批量操作
如果你有一批數(shù)據(jù)經(jīng)過業(yè)務處理之后，需要插入數(shù)據(jù)，該怎么辦？
反例：
for(Order order: list){ orderMapper.insert(order):}在循環(huán)中逐條插入數(shù)據(jù)。
insert into order(id,code,user_id) values(123,'001',100);該操作需要多次請求數(shù)據(jù)庫，才能完成這批數(shù)據(jù)的插入。
但眾所周知，我們在代碼中，每次遠程請求數(shù)據(jù)庫，是會消耗一定性能的。而如果我們的代碼需要請求多次數(shù)據(jù)庫，才能完成本次業(yè)務功能，勢必會消耗更多的性能。
那么如何優(yōu)化呢？
正例：
orderMapper.insertBatch(list):提供一個批量插入數(shù)據(jù)的方法。
insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);這樣只需要遠程請求一次數(shù)據(jù)庫，SQL性能會得到提升，數(shù)據(jù)量越多，提升越大。
但需要注意的是，不建議一次批量操作太多的數(shù)據(jù)，如果數(shù)據(jù)太多數(shù)據(jù)庫響應也會很慢。批量操作需要把握一個度，建議每批數(shù)據(jù)盡量控制在500以內(nèi)。如果數(shù)據(jù)多于500，則分多批次處理。
五、多用limit
有時候，我們需要查詢某些數(shù)據(jù)中的第一條，比如：查詢某個用戶下的第一個訂單，想看看他第一次的首單時間。
反例：
select id, create_date from order where user_id=123 order by create_date asc;根據(jù)用戶id查詢訂單，按下單時間排序，先查出該用戶所有的訂單數(shù)據(jù)，得到一個訂單集合。然后在代碼中，獲取第一個元素的數(shù)據(jù)，即首單的數(shù)據(jù)，就能獲取首單時間。
List list = orderMapper.getOrderList(); Order order = list.get(0);雖說這種做法在功能上沒有問題，但它的效率非常不高，需要先查詢出所有的數(shù)據(jù)，有點浪費資源。
那么，如何優(yōu)化呢？
正例：
select id, create_date from order where user_id=123 order by create_date asc limit 1;使用limit 1，只返回該用戶下單時間最小的那一條數(shù)據(jù)即可。
此外，在刪除或者修改數(shù)據(jù)時，為了防止誤操作，導致刪除或修改了不相干的數(shù)據(jù)，也可以在SQL語句最后加上limit。
例如：
update order set status=0,edit_time=now(3) where id>=100 and id<200 limit 100;這樣即使誤操作，比如把id搞錯了，也不會對太多的數(shù)據(jù)造成影響。
六、in中值太多
對于批量查詢接口，我們通常會使用in關鍵字過濾出數(shù)據(jù)。比如：想通過指定的一些id，批量查詢出用戶信息。
SQL語句如下：
select id,name from categorywhere id in (1,2,3...100000000);如果我們不做任何限制，該查詢語句一次性可能會查詢出非常多的數(shù)據(jù)，很容易導致接口超時。
這時該怎么辦呢？
select id,name from categorywhere id in (1,2,3...100)limit 500;可以在SQL中對數(shù)據(jù)用limit做限制。
不過我們更多的是要在業(yè)務代碼中加限制，偽代碼如下：
public List getCategory(List ids) { if(CollectionUtils.isEmpty(ids)) { return null; } if(ids.size() > 500) { throw new BusinessException("一次最多允許查詢500條記錄") } return mapper.getCategoryList(ids);}還有一個方案就是：如果ids超過500條記錄，可以分批用多線程去查詢數(shù)據(jù)。每批只查500條記錄，最后把查詢到的數(shù)據(jù)匯總到一起返回。
不過這只是一個臨時方案，不適合于ids實在太多的場景。因為ids太多，即使能快速查出數(shù)據(jù)，但如果返回的數(shù)據(jù)量太大了，網(wǎng)絡傳輸也是非常消耗性能的，接口性能始終好不到哪里去。
七、增量查詢
有時候，我們需要通過遠程接口查詢數(shù)據(jù)，然后同步到另外一個數(shù)據(jù)庫。
反例：
select * from user;如果直接獲取所有的數(shù)據(jù)，然后同步過去。這樣雖說非常方便，但是帶來了一個非常大的問題，就是如果數(shù)據(jù)很多的話，查詢性能會非常差。
這時該怎么辦呢？
正例：
select * from user where id>#{lastId} and create_time >= #{lastCreateTime} limit 100;按id和時間升序，每次只同步一批數(shù)據(jù)，這一批數(shù)據(jù)只有100條記錄。每次同步完成之后，保存這100條數(shù)據(jù)中最大的id和時間，給同步下一批數(shù)據(jù)的時候用。
通過這種增量查詢的方式，能夠提升單次查詢的效率。
八、高效的分頁
有時候，列表頁在查詢數(shù)據(jù)時，為了避免一次性返回過多的數(shù)據(jù)影響接口性能，我們一般會對查詢接口做分頁處理。
在MySQL中分頁一般用的limit關鍵字：
select id,name,age from user limit 10,20;如果表中數(shù)據(jù)量少，用limit關鍵字做分頁，沒啥問題。但如果表中數(shù)據(jù)量很多，用它就會出現(xiàn)性能問題。
比如現(xiàn)在分頁參數(shù)變成了：
select id,name,age from user limit 1000000,20;MySQL會查到1000020條數(shù)據(jù)，然后丟棄前面的1000000條，只查后面的20條數(shù)據(jù)，這個是非常浪費資源的。
那么，這種海量數(shù)據(jù)該怎么分頁呢？
優(yōu)化SQL：
select id,name,age from user where id > 1000000 limit 20;先找到上次分頁最大的id，然后利用id上的索引查詢。不過該方案，要求id是連續(xù)的，并且有序的。
還能使用between優(yōu)化分頁。
select id,name,age from user where id between 1000000 and 1000020;需要注意的是between要在唯一索引上分頁，不然會出現(xiàn)每頁大小不一致的問題。
九、用連接查詢代替子查詢
MySQL中如果需要從兩張以上的表中查詢出數(shù)據(jù)的話，一般有兩種實現(xiàn)方式：子查詢 和 連接查詢。
子查詢的例子如下：
select * from orderwhere user_id in (select id from user where status=1)子查詢語句可以通過in關鍵字實現(xiàn)，一個查詢語句的條件落在另一個select語句的查詢結果中。程序先運行在嵌套在最內(nèi)層的語句，再運行外層的語句。
子查詢語句的優(yōu)點是簡單，結構化，如果涉及的表數(shù)量不多的話。
但缺點是MySQL執(zhí)行子查詢時，需要創(chuàng)建臨時表，查詢完畢后，需要再刪除這些臨時表，有一些額外的性能消耗。
這時可以改成連接查詢。具體例子如下：
select o.* from order oinner join user u on o.user_id = u.idwhere u.status=1十、join的表不宜過多
根據(jù)阿里巴巴開發(fā)者手冊的規(guī)定，join表的數(shù)量不應該超過3個。
反例：
select a.name,b.name.c.name,d.namefrom a inner join b on a.id = b.a_idinner join c on c.b_id = b.idinner join d on d.c_id = c.idinner join e on e.d_id = d.idinner join f on f.e_id = e.idinner join g on g.f_id = f.id如果join太多，MySQL在選擇索引的時候會非常復雜，很容易選錯索引。
并且如果沒有命中中，nested loop join 就是分別從兩個表讀一行數(shù)據(jù)進行兩兩對比，復雜度是 n^2。
所以我們應該盡量控制join表的數(shù)量。
正例：
select a.name,b.name.c.name,a.d_name from a inner join b on a.id = b.a_idinner join c on c.b_id = b.id如果實現(xiàn)業(yè)務場景中需要查詢出另外幾張表中的數(shù)據(jù)，可以在a、b、c表中冗余專門的字段，比如：在表a中冗余d_name字段，保存需要查詢出的數(shù)據(jù)。
不過我之前也見過有些ERP系統(tǒng)，并發(fā)量不大，但業(yè)務比較復雜，需要join十幾張表才能查詢出數(shù)據(jù)。
所以join表的數(shù)量要根據(jù)系統(tǒng)的實際情況決定，不能一概而論，盡量越少越好。
十一、join時要注意
我們在涉及到多張表聯(lián)合查詢的時候，一般會使用join關鍵字。
而join使用最多的是left join和inner join。
- left join：求兩個表的交集外加左表剩下的數(shù)據(jù)。
- inner join：求兩個表交集的數(shù)據(jù)。
使用inner join的示例如下：
select o.id,o.code,u.name from order o inner join user u on o.user_id = u.idwhere u.status=1;如果兩張表使用inner join關聯(lián)，MySQL會自動選擇兩張表中的小表，去驅(qū)動大表，所以性能上不會有太大的問題。
使用left join的示例如下：
select o.id,o.code,u.name from order o left join user u on o.user_id = u.idwhere u.status=1;如果兩張表使用left join關聯(lián)，MySQL會默認用left join關鍵字左邊的表，去驅(qū)動它右邊的表。如果左邊的表數(shù)據(jù)很多時，就會出現(xiàn)性能問題。
要特別注意的是在用left join關聯(lián)查詢時，左邊要用小表，右邊可以用大表。如果能用inner join的地方，盡量少用left join。
十二、控制索引的數(shù)量
眾所周知，索引能夠顯著的提升查詢SQL的性能，但索引數(shù)量并非越多越好。
因為表中新增數(shù)據(jù)時，需要同時為它創(chuàng)建索引，而索引是需要額外的存儲空間的，而且還會有一定的性能消耗。
阿里巴巴的開發(fā)者手冊中規(guī)定，單表的索引數(shù)量應該盡量控制在5個以內(nèi)，并且單個索引中的字段數(shù)不超過5個。
MySQL使用的B 樹的結構來保存索引的，在insert、update和delete操作時，需要更新B 樹索引。如果索引過多，會消耗很多額外的性能。
那么，問題來了，如果表中的索引太多，超過了5個該怎么辦？
這個問題要辯證的看，如果你的系統(tǒng)并發(fā)量不高，表中的數(shù)據(jù)量也不多，其實超過5個也可以，只要不要超過太多就行。
但對于一些高并發(fā)的系統(tǒng)，請務必遵守單表索引數(shù)量不要超過5的限制。
那么，高并發(fā)系統(tǒng)如何優(yōu)化索引數(shù)量？
能夠建聯(lián)合索引，就別建單個索引，可以刪除無用的單個索引。
將部分查詢功能遷移到其他類型的數(shù)據(jù)庫中，比如：Elastic Seach、HBase等，在業(yè)務表中只需要建幾個關鍵索引即可。
十三、選擇合理的字段類型
char表示固定字符串類型，該類型的字段存儲空間的固定的，會浪費存儲空間。
alter table order add column code char(20) NOT NULL;varchar表示變長字符串類型，該類型的字段存儲空間會根據(jù)實際數(shù)據(jù)的長度調(diào)整，不會浪費存儲空間。
alter table order add column code varchar(20) NOT NULL;如果是長度固定的字段，比如用戶手機號，一般都是11位的，可以定義成char類型，長度是11字節(jié)。
但如果是企業(yè)名稱字段，假如定義成char類型，就有問題了。
如果長度定義得太長，比如定義成了200字節(jié)，而實際企業(yè)長度只有50字節(jié)，則會浪費150字節(jié)的存儲空間。
如果長度定義得太短，比如定義成了50字節(jié)，但實際企業(yè)名稱有100字節(jié)，就會存儲不下，而拋出異常。
所以建議將企業(yè)名稱改成varchar類型，變長字段存儲空間小，可以節(jié)省存儲空間，而且對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
我們在選擇字段類型時，應該遵循這樣的原則：
- 能用數(shù)字類型，就不用字符串，因為字符的處理往往比數(shù)字要慢。
- 盡可能使用小的類型，比如：用bit存布爾值，用tinyint存枚舉值等。
- 長度固定的字符串字段，用char類型。
- 長度可變的字符串字段，用varchar類型。
- 金額字段用decimal，避免精度丟失問題。
還有很多原則，這里就不一一列舉了。
十四、提升group by的效率
我們有很多業(yè)務場景需要使用group by關鍵字，它主要的功能是去重和分組。
通常它會跟having一起配合使用，表示分組后再根據(jù)一定的條件過濾數(shù)據(jù)。
反例：
select user_id,user_name from ordergroup by user_idhaving user_id <= 200;這種寫法性能不好，它先把所有的訂單根據(jù)用戶id分組之后，再去過濾用戶id大于等于200的用戶。
分組是一個相對耗時的操作，為什么我們不先縮小數(shù)據(jù)的范圍之后，再分組呢？
正例：
select user_id,user_name from orderwhere user_id <= 200group by user_id使用where條件在分組前，就把多余的數(shù)據(jù)過濾掉了，這樣分組時效率就會更高一些。
其實這是一種思路，不僅限于group by的優(yōu)化。我們的SQL語句在做一些耗時的操作之前，應盡可能縮小數(shù)據(jù)范圍，這樣能提升SQL整體的性能。
十五、索引優(yōu)化
SQL優(yōu)化當中，有一個非常重要的內(nèi)容就是：索引優(yōu)化。
很多時候SQL語句，走了索引，和沒有走索引，執(zhí)行效率差別很大。所以索引優(yōu)化被作為SQL優(yōu)化的首選。
索引優(yōu)化的第一步是：檢查SQL語句有沒有走索引。
那么，如何查看SQL走了索引沒？
可以使用explain命令，查看MySQL的執(zhí)行計劃。
例如：
explain select * from `order` where code='002';結果：
通過這幾列可以判斷索引使用情況，執(zhí)行計劃包含列的含義如下圖所示：
如果你想進一步了解explain的詳細用法，可以看看另一篇文章《explain | 索引優(yōu)化的這把絕世好劍，你真的會用嗎？》
說實話，SQL語句沒有走索引，排除沒有建索引之外，最大的可能性是索引失效了。
下面說說索引失效的常見原因：
如果不是上面的這些原因，則需要再進一步排查一下其他原因。
此外，你有沒有遇到過這樣一種情況：明明是同一條SQL，只有入?yún)⒉煌?。有的時候走的索引a，有的時候卻走的索引b？
沒錯，有時候MySQL會選錯索引。
必要時可以使用force index來強制查詢SQL走某個索引。
至于為什么MySQL會選錯索引，后面有專門的文章介紹的，這里先留點懸念。
作者丨因為熱愛所以堅持ing來源丨公眾號：蘇三說技術（ID：susanSayJava）
下載文檔
