SQL優化一直是備受關注的熱門話題，無論是在面試還是工作中，開發人員都可能遇到。

當開發人員負責的在線界面出現性能問題時，需要對其進行優化。在優化的思路中，通常會首先考慮優化SQL語句，因為它的轉換成本要比修改代碼小得多。

本文分享一些優化SQL的技巧，希望對讀者有所幫助。

1 避免使用select *

很多時候，開發人員在編寫SQL語句時，為了方便起見，喜歡直接使用select * 來一次性查出表中所有列的數據。

錯誤示例

select * from user where id = 1;

實際的業務場景中，也許我們只真正需要使用其中的一兩列數據。查了很多數據，但是卻浪費了數據庫資源，如內存或者CPU。

此外，在通過網絡IO傳輸數據的過程中，數據傳輸時間也會增加。

另一個最重要的問題是：select * 不會使用覆蓋索引，會產生大量的返回表操作，導致查詢SQL性能低下。

優化如下：

正確示例

select name, age from user where id = 1;

在查詢SQL語句時，只檢查需要使用的列，不需要檢查多余的列。

2 用 "union all" 替換 "union"

眾所周知，在SQL語句中使用union關鍵字后，可以獲得重新排序后的數據。

而如果使用union all關鍵字，可以獲取包括重復數據在內的所有數據。

錯誤示例

(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之后，結果集中出現重復數據，而業務場景不允許出現重復數據，那么可以使用union。

3 小表驅動大表

小表驅動大表，即一個小表的數據集驅動一個大表的數據集。

如果有兩個表，order和user，order表有1萬條數據，user表有100條數據。

這時，如果你想查詢所有有效用戶下的訂單列表。

可以使用in關鍵字實現：

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

也可以使用exists關鍵字實現：

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

在上述提到的業務場景中，更適合使用in關鍵字來實現業務需求。

因為in關鍵字包含在SQL語句中，會先執行in子查詢語句，然后執行in外部語句。如果in中的數據量較小，作為條件的查詢速度更快。

而如果SQL語句包含exists關鍵字，會先執行exists左邊的語句（主查詢語句）。

然后將其作為條件與右邊的語句進行匹配。如果匹配成功，就可以查詢數據。如果沒有匹配，數據就會被過濾掉。

在這個需求中，order表有1萬條數據，user表有100條數據。

order是一個大表，user是一個小表。

如果order在左邊，更適合使用in關鍵字。

總結一下：

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

4 批量操作

如果有一批需要在業務處理之后插入的數據怎么辦？

錯誤示例

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

通過循環逐個插入數據。

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

這個操作需要多次向數據庫發起請求才能完成這批數據的插入。

但是眾所周知，在我們的代碼中，每次遠程請求數據庫都會消耗一定的性能。

而且如果我們的代碼需要多次請求數據庫來完成這個業務功能，那就必然會消耗更多的性能。

正確的方式是提供一個批量插入數據的方法。

正確示例

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

這樣，只需要遠程請求一次數據庫，SQL 的性能會得到提升。數據越多，改進效果越大。

然而，需要注意的是不建議一次性批量操作過多的數據。如果數據過多，數據庫的響應會非常緩慢。

批量操作需要把握一個度，建議每個批次的數據盡量控制在 500 以內。如果數據超過 500，可以分多個批次進行處理。

5 使用 limit

有時候，我們需要查詢某些數據的第一條記錄，例如：查詢某個用戶的第一筆訂單，并且想要看到他的第一筆訂單的時間。

錯誤示例

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

根據用戶 ID 查詢訂單，按照訂單時間排序，首先找出用戶的所有訂單數據，得到一個訂單集合。

然后在代碼中，獲取第一個元素的數據，也就是第一筆訂單的數據，以獲取第一筆訂單的時間。

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

雖然這種方式在功能上沒有問題，但是非常低效。它需要先查詢所有的數據，有點浪費資源。

優化如下：

正確示例

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

使用 limit 1 只返回用戶最早的訂單時間的數據。

另外，在刪除或修改數據時，為了防止誤操作導致刪除或修改不相關的數據，也可以在 SQL 語句的末尾添加 limit。

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

這樣，即使操作錯誤，例如 id 錯誤，也不會影響太多的數據。

6 不要在 in 關鍵字中使用過多的值

對于批量查詢接口，通常使用 in 關鍵字來過濾數據。例如，我想通過一些指定的 id 批量查詢用戶信息。

SQL 語句如下：

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

如果不加任何限制，查詢語句可能會一次性查詢大量的數據，這很容易導致接口超時。

那么應該怎么做呢？

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

可以在 SQL 中使用 limit 來限制數據。

不過，我們更多地是在業務代碼中添加限制。偽代碼如下：

public List getCategory(List ids) {
   if(CollectionUtils.isEmpty(ids)) {
      return null;
   }
   if(ids.size() > 500) {
      throw new BusinessException("太多了")
   }
   return mapper.getCategoryList(ids);
}

另一種解決方案是：如果 ids 中的記錄超過 500 條，可以使用多線程來分批查詢數據。每個批次只檢查 500 條記錄，最后將查詢到的數據聚合并返回。

然而，這只是一個臨時解決方案，不適用于 ids 過多的場景。因為 ids 很多，即使數據可以快速檢測，如果返回的數據量過大，網絡傳輸會非常消耗性能，接口性能也不會有太大提升。

7 增量查詢

有時候，需要通過遠程接口查詢數據，然后將其同步到另一個數據庫中。

錯誤示例

select * from user;

如果直接獲取全部數據，然后進行同步。雖然這樣非常方便，但是帶來一個很大的問題，即如果數據量很大，查詢性能會非常差。

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

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

這種增量查詢方法可以提高單次查詢的效率。

8 高效分頁

有時，在列表頁面查詢數據時，為了避免一次性返回過多數據影響接口的性能，我們通常對查詢接口進行分頁處理。

MySQL中常用于分頁的limit關鍵字：

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

如果表中的數據量較小，使用limit關鍵字進行分頁是沒有問題的。但是如果表中的數據量很大，使用limit關鍵字會導致性能問題。

例如，現在分頁參數變為：

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

MySQL會找到1,000,020條數據，然后丟棄前1,000,000條數據，只查詢最后的20條數據，這是一種資源浪費。

那么，如何對這些海量數據進行分頁呢？

優化SQL語句：

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

首先，找到上一頁的最大id，然后利用id的索引進行查詢。但是，在這種方案中，id需要連續有序。

還可以使用between進行分頁優化。

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

需要注意的是，between應該在唯一索引上進行分頁，否則每頁的大小會不一致。

9 使用連接查詢替代子查詢

如果在MySQL中需要從兩個以上的表中查詢數據，通常有兩種實現方法：子查詢和連接查詢。

子查詢的示例如下：

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

子查詢語句可以通過in關鍵字實現，一個查詢語句的條件落在另一個select語句的查詢結果之內。程序先運行最內層的嵌套語句，然后再運行外層語句。

子查詢語句的優點是，如果涉及的表的數量較少，它簡單且結構清晰。

但是，子查詢執行時需要創建臨時表，查詢完成后需要刪除這些臨時表，這會帶來一些額外的性能消耗。

這時，可以改為連接查詢。

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

10 連接的表不能太多

錯誤的示例

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在選擇索引時會變得非常復雜，容易選擇錯誤的索引。

而且如果沒有命中，嵌套循環連接是從兩個表中讀取一行數據進行逐對比較，復雜度為n²。

因此，應盡量控制連接的表數量。

正確的示例

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

如果在業務場景的實現中需要查詢其他表中的數據，可以在a、b、c表中添加冗余的特定字段，例如在表a中添加冗余的d_name字段來保存需要查詢的數據。

然而，也有一些ERP系統，雖然并發量不大，但業務比較復雜，需要連接十幾個表來查詢數據。

因此，連接的表數量應根據系統的實際情況來確定，不能一概而論，越少越好。