SQL優(yōu)化一直是備受關(guān)注的熱門(mén)話題，無(wú)論是在面試還是工作中，開(kāi)發(fā)人員都可能遇到。

當(dāng)開(kāi)發(fā)人員負(fù)責(zé)的在線界面出現(xiàn)性能問(wèn)題時(shí)，需要對(duì)其進(jìn)行優(yōu)化。在優(yōu)化的思路中，通常會(huì)首先考慮優(yōu)化SQL語(yǔ)句，因?yàn)樗霓D(zhuǎn)換成本要比修改代碼小得多。

本文分享一些優(yōu)化SQL的技巧，希望對(duì)讀者有所幫助。

1 避免使用select *

很多時(shí)候，開(kāi)發(fā)人員在編寫(xiě)SQL語(yǔ)句時(shí)，為了方便起見(jiàn)，喜歡直接使用select * 來(lái)一次性查出表中所有列的數(shù)據(jù)。

錯(cuò)誤示例

select * from user where id = 1;

實(shí)際的業(yè)務(wù)場(chǎng)景中，也許我們只真正需要使用其中的一兩列數(shù)據(jù)。查了很多數(shù)據(jù)，但是卻浪費(fèi)了數(shù)據(jù)庫(kù)資源，如內(nèi)存或者CPU。

此外，在通過(guò)網(wǎng)絡(luò)IO傳輸數(shù)據(jù)的過(guò)程中，數(shù)據(jù)傳輸時(shí)間也會(huì)增加。

另一個(gè)最重要的問(wèn)題是：select * 不會(huì)使用覆蓋索引，會(huì)產(chǎn)生大量的返回表操作，導(dǎo)致查詢(xún)SQL性能低下。

優(yōu)化如下：

正確示例

select name, age from user where id = 1;

在查詢(xún)SQL語(yǔ)句時(shí)，只檢查需要使用的列，不需要檢查多余的列。

2 用 "union all" 替換 "union"

眾所周知，在SQL語(yǔ)句中使用union關(guān)鍵字后，可以獲得重新排序后的數(shù)據(jù)。

而如果使用union all關(guān)鍵字，可以獲取包括重復(fù)數(shù)據(jù)在內(nèi)的所有數(shù)據(jù)。

錯(cuò)誤示例

(select * from user where id=1) 
union 
(select * from user where id=2);

重新排序的過(guò)程需要遍歷、排序和比較，耗時(shí)更長(zhǎng)，消耗更多的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ù)場(chǎng)景不允許出現(xiàn)重復(fù)數(shù)據(jù)，那么可以使用union。

3 小表驅(qū)動(dòng)大表

小表驅(qū)動(dòng)大表，即一個(gè)小表的數(shù)據(jù)集驅(qū)動(dòng)一個(gè)大表的數(shù)據(jù)集。

如果有兩個(gè)表，order和user，order表有1萬(wàn)條數(shù)據(jù)，user表有100條數(shù)據(jù)。

這時(shí)，如果你想查詢(xún)所有有效用戶下的訂單列表。

可以使用in關(guān)鍵字實(shí)現(xiàn)：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists關(guān)鍵字實(shí)現(xiàn)：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

在上述提到的業(yè)務(wù)場(chǎng)景中，更適合使用in關(guān)鍵字來(lái)實(shí)現(xiàn)業(yè)務(wù)需求。

因?yàn)閕n關(guān)鍵字包含在SQL語(yǔ)句中，會(huì)先執(zhí)行in子查詢(xún)語(yǔ)句，然后執(zhí)行in外部語(yǔ)句。如果in中的數(shù)據(jù)量較小，作為條件的查詢(xún)速度更快。

而如果SQL語(yǔ)句包含exists關(guān)鍵字，會(huì)先執(zhí)行exists左邊的語(yǔ)句（主查詢(xún)語(yǔ)句）。

然后將其作為條件與右邊的語(yǔ)句進(jìn)行匹配。如果匹配成功，就可以查詢(xún)數(shù)據(jù)。如果沒(méi)有匹配，數(shù)據(jù)就會(huì)被過(guò)濾掉。

在這個(gè)需求中，order表有1萬(wàn)條數(shù)據(jù)，user表有100條數(shù)據(jù)。

order是一個(gè)大表，user是一個(gè)小表。

如果order在左邊，更適合使用in關(guān)鍵字。

總結(jié)一下：

• in適用于左邊是大表，右邊是小表的情況。
• exists適用于左邊是小表，右邊是大表的情況。

4 批量操作

如果有一批需要在業(yè)務(wù)處理之后插入的數(shù)據(jù)怎么辦？

錯(cuò)誤示例

for (Order order: list) {
   orderMapper.insert(order);
}

通過(guò)循環(huán)逐個(gè)插入數(shù)據(jù)。

insert into order(id,code,user_id) 
values(123,'001',100);

這個(gè)操作需要多次向數(shù)據(jù)庫(kù)發(fā)起請(qǐng)求才能完成這批數(shù)據(jù)的插入。

但是眾所周知，在我們的代碼中，每次遠(yuǎn)程請(qǐng)求數(shù)據(jù)庫(kù)都會(huì)消耗一定的性能。

而且如果我們的代碼需要多次請(qǐng)求數(shù)據(jù)庫(kù)來(lái)完成這個(gè)業(yè)務(wù)功能，那就必然會(huì)消耗更多的性能。

正確的方式是提供一個(gè)批量插入數(shù)據(jù)的方法。

正確示例

orderMapper.insertBatch(list);
# insert into order(id,code,user_id) 
# values(123,'001',100),(124,'002',100),(125,'003',101);

這樣，只需要遠(yuǎn)程請(qǐng)求一次數(shù)據(jù)庫(kù)，SQL 的性能會(huì)得到提升。數(shù)據(jù)越多，改進(jìn)效果越大。

然而，需要注意的是不建議一次性批量操作過(guò)多的數(shù)據(jù)。如果數(shù)據(jù)過(guò)多，數(shù)據(jù)庫(kù)的響應(yīng)會(huì)非常緩慢。

批量操作需要把握一個(gè)度，建議每個(gè)批次的數(shù)據(jù)盡量控制在 500 以?xún)?nèi)。如果數(shù)據(jù)超過(guò) 500，可以分多個(gè)批次進(jìn)行處理。

5 使用 limit

有時(shí)候，我們需要查詢(xún)某些數(shù)據(jù)的第一條記錄，例如：查詢(xún)某個(gè)用戶的第一筆訂單，并且想要看到他的第一筆訂單的時(shí)間。

錯(cuò)誤示例

select id, create_date 
 from order 
where user_id=123 
order by create_date asc;

根據(jù)用戶 ID 查詢(xún)訂單，按照訂單時(shí)間排序，首先找出用戶的所有訂單數(shù)據(jù)，得到一個(gè)訂單集合。

然后在代碼中，獲取第一個(gè)元素的數(shù)據(jù)，也就是第一筆訂單的數(shù)據(jù)，以獲取第一筆訂單的時(shí)間。

List list = orderMapper.getOrderList();
Order order = list.get(0);

雖然這種方式在功能上沒(méi)有問(wèn)題，但是非常低效。它需要先查詢(xún)所有的數(shù)據(jù)，有點(diǎn)浪費(fèi)資源。

優(yōu)化如下：

正確示例

select id, create_date 
 from order 
where user_id=123 
order by create_date asc 
limit 1;

使用 limit 1 只返回用戶最早的訂單時(shí)間的數(shù)據(jù)。

另外，在刪除或修改數(shù)據(jù)時(shí)，為了防止誤操作導(dǎo)致刪除或修改不相關(guān)的數(shù)據(jù)，也可以在 SQL 語(yǔ)句的末尾添加 limit。

update order set status=0,edit_time=now(3) 
where id>=100 and id<200 limit 100;

這樣，即使操作錯(cuò)誤，例如 id 錯(cuò)誤，也不會(huì)影響太多的數(shù)據(jù)。

6 不要在 in 關(guān)鍵字中使用過(guò)多的值

對(duì)于批量查詢(xún)接口，通常使用 in 關(guān)鍵字來(lái)過(guò)濾數(shù)據(jù)。例如，我想通過(guò)一些指定的 id 批量查詢(xún)用戶信息。

SQL 語(yǔ)句如下：

select id,name from category
where id in (1,2,3...100000000);

如果不加任何限制，查詢(xún)語(yǔ)句可能會(huì)一次性查詢(xún)大量的數(shù)據(jù)，這很容易導(dǎo)致接口超時(shí)。

那么應(yīng)該怎么做呢？

select id,name from category
where id in (1,2,3...100)
limit 500;

可以在 SQL 中使用 limit 來(lái)限制數(shù)據(jù)。

不過(guò)，我們更多地是在業(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 中的記錄超過(guò) 500 條，可以使用多線程來(lái)分批查詢(xún)數(shù)據(jù)。每個(gè)批次只檢查 500 條記錄，最后將查詢(xún)到的數(shù)據(jù)聚合并返回。

然而，這只是一個(gè)臨時(shí)解決方案，不適用于 ids 過(guò)多的場(chǎng)景。因?yàn)?ids 很多，即使數(shù)據(jù)可以快速檢測(cè)，如果返回的數(shù)據(jù)量過(guò)大，網(wǎng)絡(luò)傳輸會(huì)非常消耗性能，接口性能也不會(huì)有太大提升。

7 增量查詢(xún)

有時(shí)候，需要通過(guò)遠(yuǎn)程接口查詢(xún)數(shù)據(jù)，然后將其同步到另一個(gè)數(shù)據(jù)庫(kù)中。

錯(cuò)誤示例

select * from user;

如果直接獲取全部數(shù)據(jù)，然后進(jìn)行同步。雖然這樣非常方便，但是帶來(lái)一個(gè)很大的問(wèn)題，即如果數(shù)據(jù)量很大，查詢(xún)性能會(huì)非常差。

select * from user 
where id>#{lastId} and create_time >= #{lastcreateTime} 
limit 100;

按照 id 和時(shí)間升序，每次只同步一批數(shù)據(jù)，這批數(shù)據(jù)只有 100 條記錄。每次同步完成后，保存這 100 條數(shù)據(jù)中最大的 id 和時(shí)間，用于同步下一批數(shù)據(jù)時(shí)使用。

這種增量查詢(xún)方法可以提高單次查詢(xún)的效率。

8 高效分頁(yè)

有時(shí)，在列表頁(yè)面查詢(xún)數(shù)據(jù)時(shí)，為了避免一次性返回過(guò)多數(shù)據(jù)影響接口的性能，我們通常對(duì)查詢(xún)接口進(jìn)行分頁(yè)處理。

MySQL中常用于分頁(yè)的limit關(guān)鍵字：

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

如果表中的數(shù)據(jù)量較小，使用limit關(guān)鍵字進(jìn)行分頁(yè)是沒(méi)有問(wèn)題的。但是如果表中的數(shù)據(jù)量很大，使用limit關(guān)鍵字會(huì)導(dǎo)致性能問(wèn)題。

例如，現(xiàn)在分頁(yè)參數(shù)變?yōu)椋?/p>

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

MySQL會(huì)找到1,000,020條數(shù)據(jù)，然后丟棄前1,000,000條數(shù)據(jù)，只查詢(xún)最后的20條數(shù)據(jù)，這是一種資源浪費(fèi)。

那么，如何對(duì)這些海量數(shù)據(jù)進(jìn)行分頁(yè)呢？

優(yōu)化SQL語(yǔ)句：

select id,name,age 
from user where id > 1000000 limit 20;

首先，找到上一頁(yè)的最大id，然后利用id的索引進(jìn)行查詢(xún)。但是，在這種方案中，id需要連續(xù)有序。

還可以使用between進(jìn)行分頁(yè)優(yōu)化。

select id,name,age 
from user where id between 1000000 and 1000020;

需要注意的是，between應(yīng)該在唯一索引上進(jìn)行分頁(yè)，否則每頁(yè)的大小會(huì)不一致。

9 使用連接查詢(xún)替代子查詢(xún)

如果在MySQL中需要從兩個(gè)以上的表中查詢(xún)數(shù)據(jù)，通常有兩種實(shí)現(xiàn)方法：子查詢(xún)和連接查詢(xún)。

子查詢(xún)的示例如下：

select * from order
where user_id in (select id from user where status=1)

子查詢(xún)語(yǔ)句可以通過(guò)in關(guān)鍵字實(shí)現(xiàn)，一個(gè)查詢(xún)語(yǔ)句的條件落在另一個(gè)select語(yǔ)句的查詢(xún)結(jié)果之內(nèi)。程序先運(yùn)行最內(nèi)層的嵌套語(yǔ)句，然后再運(yùn)行外層語(yǔ)句。

子查詢(xún)語(yǔ)句的優(yōu)點(diǎn)是，如果涉及的表的數(shù)量較少，它簡(jiǎn)單且結(jié)構(gòu)清晰。

但是，子查詢(xún)執(zhí)行時(shí)需要?jiǎng)?chuàng)建臨時(shí)表，查詢(xún)完成后需要?jiǎng)h除這些臨時(shí)表，這會(huì)帶來(lái)一些額外的性能消耗。

這時(shí)，可以改為連接查詢(xún)。

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10 連接的表不能太多

錯(cuò)誤的示例

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在選擇索引時(shí)會(huì)變得非常復(fù)雜，容易選擇錯(cuò)誤的索引。

而且如果沒(méi)有命中，嵌套循環(huán)連接是從兩個(gè)表中讀取一行數(shù)據(jù)進(jìn)行逐對(duì)比較，復(fù)雜度為n²。

因此，應(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ù)場(chǎng)景的實(shí)現(xiàn)中需要查詢(xún)其他表中的數(shù)據(jù)，可以在a、b、c表中添加冗余的特定字段，例如在表a中添加冗余的d_name字段來(lái)保存需要查詢(xún)的數(shù)據(jù)。

然而，也有一些ERP系統(tǒng)，雖然并發(fā)量不大，但業(yè)務(wù)比較復(fù)雜，需要連接十幾個(gè)表來(lái)查詢(xún)數(shù)據(jù)。

因此，連接的表數(shù)量應(yīng)根據(jù)系統(tǒng)的實(shí)際情況來(lái)確定，不能一概而論，越少越好。