優(yōu)化SQL語句，十條建議讓SQL速度飛起來
SQL優(yōu)化一直是備受關(guān)注的熱門話題，無論是在面試還是工作中，開發(fā)人員都可能遇到。
當(dāng)開發(fā)人員負(fù)責(zé)的在線界面出現(xiàn)性能問題時，需要對其進(jìn)行優(yōu)化。在優(yōu)化的思路中，通常會首先考慮優(yōu)化SQL語句，因為它的轉(zhuǎn)換成本要比修改代碼小得多。
本文分享一些優(yōu)化SQL的技巧，希望對讀者有所幫助。
1 避免使用select *
很多時候，開發(fā)人員在編寫SQL語句時，為了方便起見，喜歡直接使用select * 來一次性查出表中所有列的數(shù)據(jù)。
錯誤示例
select * from user where id = 1;實際的業(yè)務(wù)場景中，也許我們只真正需要使用其中的一兩列數(shù)據(jù)。查了很多數(shù)據(jù)，但是卻浪費了數(shù)據(jù)庫資源，如內(nèi)存或者CPU。
此外，在通過網(wǎng)絡(luò)IO傳輸數(shù)據(jù)的過程中，數(shù)據(jù)傳輸時間也會增加。
另一個最重要的問題是：select * 不會使用覆蓋索引，會產(chǎn)生大量的返回表操作，導(dǎo)致查詢SQL性能低下。
優(yōu)化如下：
正確示例
select name, age from user where id = 1;在查詢SQL語句時，只檢查需要使用的列，不需要檢查多余的列。
2 用 "union all" 替換 "union"
眾所周知，在SQL語句中使用union關(guān)鍵字后，可以獲得重新排序后的數(shù)據(jù)。
而如果使用union all關(guān)鍵字，可以獲取包括重復(fù)數(shù)據(jù)在內(nèi)的所有數(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之后，結(jié)果集中出現(xiàn)重復(fù)數(shù)據(jù)，而業(yè)務(wù)場景不允許出現(xiàn)重復(fù)數(shù)據(jù)，那么可以使用union。
3 小表驅(qū)動大表
小表驅(qū)動大表，即一個小表的數(shù)據(jù)集驅(qū)動一個大表的數(shù)據(jù)集。
如果有兩個表，order和user，order表有1萬條數(shù)據(jù)，user表有100條數(shù)據(jù)。
這時，如果你想查詢所有有效用戶下的訂單列表。
可以使用in關(guān)鍵字實現(xiàn)：
select * from order
where user_id in (select id from user where status=1)也可以使用exists關(guān)鍵字實現(xiàn)：
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)在上述提到的業(yè)務(wù)場景中，更適合使用in關(guān)鍵字來實現(xiàn)業(yè)務(wù)需求。
因為in關(guān)鍵字包含在SQL語句中，會先執(zhí)行in子查詢語句，然后執(zhí)行in外部語句。如果in中的數(shù)據(jù)量較小，作為條件的查詢速度更快。
而如果SQL語句包含exists關(guān)鍵字，會先執(zhí)行exists左邊的語句（主查詢語句）。
然后將其作為條件與右邊的語句進(jìn)行匹配。如果匹配成功，就可以查詢數(shù)據(jù)。如果沒有匹配，數(shù)據(jù)就會被過濾掉。
在這個需求中，order表有1萬條數(shù)據(jù)，user表有100條數(shù)據(jù)。
order是一個大表，user是一個小表。
如果order在左邊，更適合使用in關(guān)鍵字。
總結(jié)一下：
- in適用于左邊是大表，右邊是小表的情況。
- exists適用于左邊是小表，右邊是大表的情況。
4 批量操作
如果有一批需要在業(yè)務(wù)處理之后插入的數(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ù)庫發(fā)起請求才能完成這批數(shù)據(jù)的插入。
但是眾所周知，在我們的代碼中，每次遠(yuǎn)程請求數(shù)據(jù)庫都會消耗一定的性能。
而且如果我們的代碼需要多次請求數(shù)據(jù)庫來完成這個業(yè)務(wù)功能，那就必然會消耗更多的性能。
正確的方式是提供一個批量插入數(shù)據(jù)的方法。
正確示例
orderMapper.insertBatch(list);
# insert into order(id,code,user_id)
# values(123,'001',100),(124,'002',100),(125,'003',101);這樣，只需要遠(yuǎn)程請求一次數(shù)據(jù)庫，SQL 的性能會得到提升。數(shù)據(jù)越多，改進(jìn)效果越大。
然而，需要注意的是不建議一次性批量操作過多的數(shù)據(jù)。如果數(shù)據(jù)過多，數(shù)據(jù)庫的響應(yīng)會非常緩慢。
批量操作需要把握一個度，建議每個批次的數(shù)據(jù)盡量控制在 500 以內(nèi)。如果數(shù)據(jù)超過 500，可以分多個批次進(jìn)行處理。
5 使用 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ù)時，為了防止誤操作導(dǎo)致刪除或修改不相關(guān)的數(shù)據(jù)，也可以在 SQL 語句的末尾添加 limit。
update order set status=0,edit_time=now(3)
where id>=100 and id<200 limit 100;這樣，即使操作錯誤，例如 id 錯誤，也不會影響太多的數(shù)據(jù)。
6 不要在 in 關(guān)鍵字中使用過多的值
對于批量查詢接口，通常使用 in 關(guān)鍵字來過濾數(shù)據(jù)。例如，我想通過一些指定的 id 批量查詢用戶信息。
SQL 語句如下：
select id,name from category
where id in (1,2,3...100000000);如果不加任何限制，查詢語句可能會一次性查詢大量的數(shù)據(jù)，這很容易導(dǎo)致接口超時。
那么應(yīng)該怎么做呢？
select id,name from category
where id in (1,2,3...100)
limit 500;可以在 SQL 中使用 limit 來限制數(shù)據(jù)。
不過，我們更多地是在業(yè)務(wù)代碼中添加限制。偽代碼如下：
public List getCategory(List ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException("太多了")
}
return mapper.getCategoryList(ids);
}另一種解決方案是：如果 ids 中的記錄超過 500 條，可以使用多線程來分批查詢數(shù)據(jù)。每個批次只檢查 500 條記錄，最后將查詢到的數(shù)據(jù)聚合并返回。
然而，這只是一個臨時解決方案，不適用于 ids 過多的場景。因為 ids 很多，即使數(shù)據(jù)可以快速檢測，如果返回的數(shù)據(jù)量過大，網(wǎng)絡(luò)傳輸會非常消耗性能，接口性能也不會有太大提升。
7 增量查詢
有時候，需要通過遠(yuǎn)程接口查詢數(shù)據(jù)，然后將其同步到另一個數(shù)據(jù)庫中。
錯誤示例
select * from user;如果直接獲取全部數(shù)據(jù)，然后進(jìn)行同步。雖然這樣非常方便，但是帶來一個很大的問題，即如果數(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ù)時使用。
這種增量查詢方法可以提高單次查詢的效率。
8 高效分頁
有時，在列表頁面查詢數(shù)據(jù)時，為了避免一次性返回過多數(shù)據(jù)影響接口的性能，我們通常對查詢接口進(jìn)行分頁處理。
MySQL中常用于分頁的limit關(guān)鍵字：
select id,name,age
from user limit 10,20;如果表中的數(shù)據(jù)量較小，使用limit關(guān)鍵字進(jìn)行分頁是沒有問題的。但是如果表中的數(shù)據(jù)量很大，使用limit關(guān)鍵字會導(dǎo)致性能問題。
例如，現(xiàn)在分頁參數(shù)變?yōu)椋?/p>
select id,name,age
from user limit 1000000,20;MySQL會找到1,000,020條數(shù)據(jù)，然后丟棄前1,000,000條數(shù)據(jù)，只查詢最后的20條數(shù)據(jù)，這是一種資源浪費。
那么，如何對這些海量數(shù)據(jù)進(jìn)行分頁呢？
優(yōu)化SQL語句：
select id,name,age
from user where id > 1000000 limit 20;首先，找到上一頁的最大id，然后利用id的索引進(jìn)行查詢。但是，在這種方案中，id需要連續(xù)有序。
還可以使用between進(jìn)行分頁優(yōu)化。
select id,name,age
from user where id between 1000000 and 1000020;需要注意的是，between應(yīng)該在唯一索引上進(jìn)行分頁，否則每頁的大小會不一致。
9 使用連接查詢替代子查詢
如果在MySQL中需要從兩個以上的表中查詢數(shù)據(jù)，通常有兩種實現(xiàn)方法：子查詢和連接查詢。
子查詢的示例如下：
select * from order
where user_id in (select id from user where status=1)子查詢語句可以通過in關(guān)鍵字實現(xiàn)，一個查詢語句的條件落在另一個select語句的查詢結(jié)果之內(nèi)。程序先運行最內(nèi)層的嵌套語句，然后再運行外層語句。
子查詢語句的優(yōu)點是，如果涉及的表的數(shù)量較少，它簡單且結(jié)構(gòu)清晰。
但是，子查詢執(zhí)行時需要創(chuàng)建臨時表，查詢完成后需要刪除這些臨時表，這會帶來一些額外的性能消耗。
這時，可以改為連接查詢。
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=110 連接的表不能太多
錯誤的示例
select a.name,b.name.c.name,d.name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id如果join太多，MySQL在選擇索引時會變得非常復(fù)雜，容易選擇錯誤的索引。
而且如果沒有命中，嵌套循環(huán)連接是從兩個表中讀取一行數(shù)據(jù)進(jìn)行逐對比較，復(fù)雜度為n2。
因此，應(yīng)盡量控制連接的表數(shù)量。
正確的示例
select a.name,b.name.c.name,a.d_name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id如果在業(yè)務(wù)場景的實現(xiàn)中需要查詢其他表中的數(shù)據(jù)，可以在a、b、c表中添加冗余的特定字段，例如在表a中添加冗余的d_name字段來保存需要查詢的數(shù)據(jù)。
然而，也有一些ERP系統(tǒng)，雖然并發(fā)量不大，但業(yè)務(wù)比較復(fù)雜，需要連接十幾個表來查詢數(shù)據(jù)。
因此，連接的表數(shù)量應(yīng)根據(jù)系統(tǒng)的實際情況來確定，不能一概而論，越少越好。
