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