讓SQL起飛（優化）
最近博主看完了《SQL進階教學》這本書，看完後給博主開啟了SQL世界的新大門，對於 SQL 的理解不在侷限於以前的常規用法。借用其他讀者的評論，
❝讀完醍醐灌頂，對SQL做到了知其然更能知其所以然。全書從頭到尾強調了 SQL的內在邏輯是基於集合論和謂詞邏輯，而著兩條主線恰恰在使用SQL起到了至關重要的指導作用。
❞
本文給大家總結如何讓SQL起飛（優化） 
一、SQL寫法優化
在SQL中，很多時候不同的SQL程式碼能夠得出相同結果。從理論上來說，我們認為得到相同結果的不同SQL之間應該有相同的效能，但遺憾的是，查詢優化器生成的執行計劃很大程度上受到SQL程式碼影響，有快有慢。因此如果想優化查詢效能，我們必須知道如何寫出更快的SQL，才能使優化器的執行效率更高。
1.1 子查詢用EXISTS代替IN
當IN的引數是子查詢時，資料庫首先會執行子查詢，然後將結果儲存在一張臨時的工作表裡（內聯檢視），然後掃描整個檢視。很多情況下這種做法都非常耗費資源。使用EXISTS的話，資料庫不會生成臨時的工作表。但是從程式碼的可讀性上來看，IN要比EXISTS好。使用IN時的程式碼看起來更加一目瞭然，易於理解。因此，如果確信使用IN也能快速獲取結果，就沒有必要非得改成EXISTS了。
這裡用Class_A表和Class_B舉例， 
我們試著從Class_A表中查出同時存在於Class_B表中的員工。下面兩條SQL語句返回的結果是一樣的，但是使用EXISTS的SQL語句更快一些。
--慢
SELECT *
FROM Class_A
WHERE id IN (SELECT id
FROM Class_B);
--快
SELECT *
FROM Class_A A
WHERE EXISTS
(SELECT *
FROM Class_B B
WHERE A.id = B.id);使用EXISTS時更快的原因有以下兩個。
-
如果連線列（id）上建立了索引，那麼查詢 tb_b 時不用查實際的表，只需查索引就可以了。（同樣的IN也可以使用索引，這不是重要原因） -
「如果使用EXISTS，那麼只要查到一行資料滿足條件就會終止查詢，不用像使用IN時一樣掃描全表」。在這一點上NOT EXISTS也一樣。
實際上，大部分情況在子查詢數量較小的場景下EXISTS和IN的查詢效能不相上下，由EXISTS查詢更快第二點可知，子查詢數量較大時使用EXISTS才會有明顯優勢。
1.2 避免排序並新增索引
在SQL語言中，除了ORDER BY子句會進行顯示排序外，還有很多操作預設也會在暗中進行排序，如果排序欄位沒有新增索引，會導致查詢效能很慢。SQL中會進行排序的代表性的運算有下面這些。
-
GROUP BY子句 -
ORDER BY子句 -
聚合函數（SUM、COUNT、AVG、MAX、MIN） -
DISTINCT -
集合運運算元（UNION、INTERSECT、EXCEPT） -
視窗函數（RANK、ROW_NUMBER等）
如上列出的六種運算（除了集合運運算元），它們後面跟隨或者指定的欄位都可以新增索引，這樣可以加快排序。
❝「實際上在DISTINCT關鍵字、GROUP BY子句、ORDER BY子句、聚合函數跟隨的欄位都新增索引，不僅能加速查詢，還能加速排序。」
❞
1.3 用EXISTS代替DISTINCT
為了排除重複資料，我們可能會使用DISTINCT關鍵字。如1.2中所說，預設情況下，它也會進行暗中排序。如果需要對兩張表的連線結果進行去重，可以考慮使用EXISTS代替DISTINCT，以避免排序。這裡用Items表和SalesHistory表舉例： 
我們思考一下如何從上面的商品表Items中找出同時存在於銷售記錄表SalesHistory中的商品。簡而言之，就是找出有銷售記錄的商品。
在一（Items）對多（SalesHistory）的場景下，我們需要對item_no去重，使用DISTINCT去重，因此SQL如下：
SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
item_no
-------
10
20
30使用EXISTS代替DISTINCT去重，SQL如下：
SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT ＊
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);
item_no
-------
10
20
30這條語句在執行過程中不會進行排序。而且使用EXISTS和使用連線一樣高效。
1.4 集合運算ALL可選項
SQL中有UNION、INTERSECT、EXCEPT三個集合運運算元。在預設的使用方式下，這些運運算元會為了排除掉重複資料而進行排序。
❝MySQL還沒有實現INTERSECT和EXCEPT運算
❞
如果不在乎結果中是否有重複資料，或者事先知道不會有重複資料，請使用UNION ALL代替UNION。這樣就不會進行排序了。
1.5 WHERE條件不要寫在HAVING字句
例如，這裡繼續用SalesHistory表舉例，下面兩條SQL語句返回的結果是一樣的：
--聚合後使用HAVING子句過濾
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
--聚合前使用WHERE子句過濾
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;但是從效能上來看，第二條語句寫法效率更高。原因有兩個：
-
使用GROUP BY子句聚合時會進行排序，如果事先通過WHERE子句篩選出一部分行，就能夠減輕排序的負擔。 -
在WHERE子句的條件裡可以使用索引。HAVING子句是針對聚合後生成的檢視進行篩選的，但是很多時候聚合後的檢視都沒有繼承原表的索引結構。
二、真的用到索引了嗎
2.1 隱式的型別轉換
如下，col_1欄位是char型別：
SELECT * FROM SomeTable WHERE col_1 = 10; -- 走了索引
SELECT * FROM SomeTable WHERE col_1 ='10'; -- 沒走索引
SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2)); -- 走了索引當查詢條件左邊和右邊型別不一致時會導致索引失效。
2.2 在索引欄位上進行運算
如下：
SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;在索引欄位col_1上進行運算會導致索引不生效，把運算的表示式放到查詢條件的右側，就能用到索引了，像下面這樣寫就OK了。
WHERE col_1 > 100 / 1.1如果無法避免在左側進行運算，那麼使用函數索引也是一種辦法，但是不太推薦隨意這麼做。「使用索引時，條件表示式的左側應該是原始欄位請牢記」，這一點是在優化索引時首要關注的地方。
2.3 使用否定形式
下面這幾種否定形式不能用到索引。
-
<> -
!= -
NOT
這個是跟具體資料庫的優化器有關，如果優化器覺得即使走了索引，還是需要掃描很多很多行的哈，他可以選擇直接不走索引。平時我們用!=、<>、not in的時候，要注意一下。
2.4 使用OR查詢前後沒有同時使用索引
例如下表：
CREATE TABLE test_tb (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(55) NOT NULL
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;使用OR條件進行查詢
SELECT *
FROM test_tb
WHERE id = 1 OR name = 'tom'這個SQL的執行條件下，很明顯id欄位查詢會走索引，但是對於OR後面name欄位的查詢是需要進行全表掃描的。在這個場景下，優化器直接進行一遍全表掃描就完事了。
2.5 使用聯合索引時，列的順序錯誤
使用聯合索引需要滿足最左匹配原則，即最左優先。如果你建立一個（col_1, col_2, col_3）的聯合索引，相當於建立了 (col_1)、(col_1,col_2)、(col_1,col_2,col_3) 三個索引。如下例子：
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
-- 沒走索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
-- 沒走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
-- 沒走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;聯合索引中的第一列（col_1）必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒。
2.6 使用LIKE查詢
並不是用了like萬用字元，索引一定會失效，而是like查詢是以%開頭，才會導致索引失效。
-- 沒走索引
SELECT * FROM SomeTable WHERE col_1 LIKE'%a';
-- 沒走索引
SELECT * FROM SomeTable WHERE col_1 LIKE'%a%';
-- 走了索引
SELECT * FROM SomeTable WHERE col_1 LIKE'a%';2.7 連線欄位字元集編碼不一致
如果兩張表進行連線，關聯欄位編碼不一致會導致關聯欄位上的索引失效，這是博主線上上經歷一次SQL慢查詢後的得到的結果，舉例如下，有如下兩表，它們的name欄位都建有索引，但是編碼不一致，user表的name欄位編碼是utf8mb4，user_job表的name欄位編碼是utf8，
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER
SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (
`id` int NOT NULL,
`userId` int NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;進行SQL查詢如下：
EXPLAIN
SELECT *
from `user` u
join user_job j on u.name = j.name
由結果可知，user表的查詢沒有走索引。想要user表也走索引，那就需要把user表name欄位的編碼改成utf8即可。
三、減少中間表
在SQL中，子查詢的結果會被看成一張新表，這張新表與原始表一樣，可以通過程式碼進行操作。這種高度的相似性使得SQL程式設計具有非常強的靈活性，但是如果不加限制地大量使用中間表，會導致查詢效能下降。
頻繁使用中間表會帶來兩個問題，一是展開資料需要耗費記憶體資源，二是原始表中的索引不容易使用到（特別是聚合時）。因此，儘量減少中間表的使用也是提升效能的一個重要方法。
3.1 使用HAVING子句
對聚合結果指定篩選條件時，使用HAVING子句是基本原則。不習慣使用HAVING子句的人可能會傾向於像下面這樣先生成一張中間表，然後在WHERE子句中指定篩選條件。例如下面：
SELECT *
FROM (
SELECT sale_date, MAX(quantity) max_qty
FROM SalesHistory
GROUP BY sale_date
) tmp
WHERE max_qty >= 10然而，對聚合結果指定篩選條件時不需要專門生成中間表，像下面這樣使用HAVING子句就可以。
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;HAVING子句和聚合操作是同時執行的，所以比起生成中間表後再執行的WHERE子句，效率會更高一些，而且程式碼看起來也更簡潔。
3.2 對多個欄位使用IN
當我們需要對多個欄位使用IN條件查詢時，可以通過 || 操作將欄位連線在一起變成一個字串處理。
SELECT *
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state|| city
FROM Addresses2 A2);這樣一來，子查詢不用考慮關聯性，而且只執行一次就可以。
3.3 先進行連線再進行聚合
連線和聚合同時使用時，先進行連線操作可以避免產生中間表。原因是，從集合運算的角度來看，連線做的是「乘法運算」。連線表雙方是一對一、一對多的關係時，連線運算後資料的行數不會增加。而且，因為在很多設計中多對多的關係都可以分解成兩個一對多的關係，因此這個技巧在大部分情況下都可以使用。
到此本文講解完畢，感謝大家閱讀，感興趣的朋友可以點贊加關注，你的支援將是我更新動力