由于工作需要，最近做了很多 BI 取數的工作，需要用到一些比較高級的 SQL 技巧，總結了一下工作中用到的一些比較騷的進階技巧，特此記錄一下，以方便自己查閱。
圖片來自 Pexels
主要目錄如下：
- SQL 的書寫規范
- SQL 的一些進階使用技巧
- SQL 的優化方法
SQL 的書寫規范
在介紹一些技巧之前，有必要強調一下規范，這一點我發現工作中經常被人忽略，其實遵循好的規范可讀性會好很多，應該遵循哪些規范呢?
①表名要有意義，且標準 SQL 中規定表名的第一個字符應該是字母。
②注釋，有單行注釋和多行注釋，如下：
-- 單行注釋
-- 從SomeTable中查詢col_1
SELECT col_1
FROM SomeTable;
/*
多行注釋
從 SomeTable 中查詢 col_1
*/
SELECT col_1
FROM SomeTable;
多行注釋很多人不知道，這種寫法不僅可以用來添加真正的注釋，也可以用來注釋代碼，非常方便。
③縮進
就像寫 JAVA，Python 等編程語言一樣 ，SQL 也應該有縮進，良好的縮進對提升代碼的可讀性幫助很大。
以下分別是好的縮進與壞的縮進示例：
-- 好的縮進
SELECT col_1,
col_2,
col_3,
COUNT(*)
FROM tbl_A
WHERE col_1 = 'a'
AND col_2 = ( SELECT MAX(col_2)
FROM tbl_B
WHERE col_3 = 100 )
GROUP BY col_1,
col_2,
col_3
-- 壞的示例
SELECT col1_1, col_2, col_3, COUNT(*)
FROM tbl_A
WHERE col1_1 = 'a'
AND col1_2 = (
SELECT MAX(col_2)
FROM tbl_B
WHERE col_3 = 100
) GROUP BY col_1, col_2, col_3
④空格
代碼中應該適當留有一些空格，如果一點不留，代碼都湊到一起， 邏輯單元不明確，閱讀的人也會產生額外的壓力。
以下分別是是好的與壞的示例：
-- 好的示例
SELECT col_1
FROM tbl_A A, tbl_B B
WHERE ( A.col_1 >= 100 OR A.col_2 IN ( 'a', 'b' ) )
AND A.col_3 = B.col_3;
-- 壞的示例
SELECT col_1
FROM tbl_A A,tbl_B B
WHERE (A.col_1>=100 OR A.col_2 IN ('a','b'))
AND A.col_3=B.col_3;
④大小寫
關鍵字使用大小寫，表名列名使用小寫，如下：
SELECT col_1, col_2, col_3,
COUNT(*)
FROM tbl_A
WHERE col_1 = 'a'
AND col_2 = ( SELECT MAX(col_2)
FROM tbl_B
WHERE col_3 = 100 )
GROUP BY col_1, col_2, col_3
花了這么多時間強調規范，有必要嗎，有!好的規范讓代碼的可讀性更好，更有利于團隊合作，之后的 SQL 示例都會遵循這些規范。
SQL 的一些進階使用技巧
①巧用 CASE WHEN 進行統計
來看看如何巧用 CASE WHEN 進行定制化統計,假設我們有如下的需求，希望根據左邊各個市的人口統計每個省的人口：
使用 CASE WHEN 如下：
SELECT CASE pref_name
WHEN '長沙' THEN '湖南'
WHEN '衡陽' THEN '湖南'
WHEN '海口' THEN '海南'
WHEN '三亞' THEN '海南'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;
②巧用 CASE WHEN 進行更新
現在某公司員人工資信息表如下：
現在公司出臺了一個奇葩的規定：
- 對當前工資為 1 萬以上的員工，降薪 10%。
- 對當前工資低于 1 萬的員工，加薪 20%。
一些人不假思索可能寫出了以下的 SQL：
--條件1
UPDATE Salaries
SET salary = salary * 0.9 WHERE salary >= 10000;
--條件2
UPDATE Salaries
SET salary = salary * 1.2
WHERE salary < 10000;
這么做其實是有問題的， 什么問題，對小明來說，他的工資是 10500，執行第一個 SQL 后，工資變為 10500*0.9=9450, 緊接著又執行條件 2， 工資變為了 9450*1.2=11340，反而漲薪了!
如果用 CASE WHEN 可以解決此類問題，如下：
UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;
③巧用 HAVING 子句
一般 HAVING 是與 GROUP BY 結合使用的，但其實它是可以獨立使用的， 假設有如下表，第一列 seq 叫連續編號，但其實有些編號是缺失的，怎么知道編號是否缺失呢?
用 HAVING 表示如下：
SELECT '存在缺失的編號' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
④自連接
針對相同的表進行的連接被稱為“自連接”(self join)，這個技巧常常被人們忽視，其實是有挺多妙用的。
刪除重復行：上圖中有三個橘子，需要把這些重復的行給刪掉，用如下自連接可以解決：
DELETE FROM Products P1
WHERE id < ( SELECT MAX(P2.id)
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price );
排序：在 DB 中，我們經常需要按分數，人數，銷售額等進行排名，有 Oracle, DB2 中可以使用 RANK 函數進行排名，不過在 MySQL 中 RANK 函數未實現。
這種情況我們可以使用自連接來實現，如對以下 Products 表按價格高低進行排名：
使用自連接可以這么寫：
-- 排序從 1 開始。如果已出現相同位次，則跳過之后的位次
SELECT P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM Products P2
WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;
結果如下：
name price rank
----- ------ ------
橘子 100 1
西瓜 80 2
蘋果 50 3
葡萄 50 3
香蕉 50 3
檸檬 30 6
⑤巧用 COALESCE 函數
此函數作用返回參數中的第一個非空表達式，假設有如下商品，我們重新格式化一樣，如果 city 為 null，代表商品不在此城市發行。
但我們在展示結果的時候不想展示 null，而想展示 'N/A', 可以這么做：
SELECT
COALESCE(city, 'N/A')
FROM
customers; 
SQL 性能優化技巧
①參數是子查詢時，使用 EXISTS 代替 IN
如果 IN 的參數是(1，2，3)這樣的值列表時，沒啥問題，但如果參數是子查詢時，就需要注意了。
比如，現在有如下兩個表：
現在我們要查出同時存在于兩個表的員工，即田中和鈴木，則以下用 IN 和 EXISTS 返回的結果是一樣，但是用 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 的 SQL 運行更快呢，有兩個原因：
- 可以`用到索引，如果連接列 (id) 上建立了索引，那么查詢 Class_B 時不用查實際的表，只需查索引就可以了。
- 如果使用 EXISTS，那么只要查到一行數據滿足條件就會終止查詢， 不用像使用 IN 時一樣掃描全表。在這一點上 NOT EXISTS 也一樣。
另外如果 IN 后面如果跟著的是子查詢，由于 SQL 會先執行 IN 后面的子查詢，會將子查詢的結果保存在一張臨時的工作表里(內聯視圖)，然后掃描整個視圖。
顯然掃描整個視圖這個工作很多時候是非常耗時的，而用 EXISTS 不會生成臨時表。
當然了，如果 IN 的參數是子查詢時，也可以用連接來代替，如下：
-- 使用連接代替 IN SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
用到了 「id」列上的索引，而且由于沒有子查詢，也不會生成臨時表。
②避免排序
SQL 是聲明式語言，即對用戶來說，只關心它能做什么，不用關心它怎么做，這樣可能會產生潛在的性能問題：排序。
會產生排序的代表性運算有下面這些：
- GROUP BY 子句
- ORDER BY 子句
- 聚合函數(SUM、COUNT、AVG、MAX、MIN)
- DISTINCT
- 集合運算符(UNION、INTERSECT、EXCEPT)
- 窗口函數(RANK、ROW_NUMBER 等)
如果在內存中排序還好，但如果內存不夠導致需要在硬盤上排序上的話，性能就會急劇下降，所以我們需要減少不必要的排序。
怎樣做可以減少排序呢?有如下幾點：
使用集合運算符的 ALL 可選項：SQL 中有 UNION，INTERSECT，EXCEPT 三個集合運算符。
默認情況下，這些運算符會為了避免重復數據而進行排序，對比一下使用 UNION 運算符加和不加 ALL 的情況：
注意：加 ALL 是優化性能非常有效的手段，不過前提是不在乎結果是否有重復數據。
使用 EXISTS 代表 DISTINCT：為了排除重復數據，DISTINCT 也會對結果進行排序，如果需要對兩張表的連接結果進行去重，可以考慮用 EXISTS 代替 DISTINCT，這樣可以避免排序。
如何找出有銷售記錄的商品，使用如下 DISTINCT 可以：
SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
不過更好的方式是使用 EXISTS：
SELECT item_no FROM Items I
WHERE EXISTS
(SELECT *
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);
既用到了索引，又避免了排序對性能的損耗。
②在極值函數中使用索引(MAX/MIN)
使用 MAX/ MIN 都會對進行排序，如果參數字段上沒加索引會導致全表掃描，如果建有索引，則只需要掃描索引即可，對比如下：
-- 這樣寫需要掃描全表
SELECT MAX(item)
FROM Items;
-- 這樣寫能用到索引
SELECT MAX(item_no)
FROM Items;
注意：極值函數參數推薦為索引列中并不是不需要排序，而是優化了排序前的查找速度(畢竟索引本身就是有序排列的)。
③能寫在 WHERE 子句里的條件不要寫在 HAVING 子句里
下列 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 子句是針對聚合后生成的視頻進行篩選的，但很多時候聚合后生成的視圖并沒有保留原表的索引結構。
④在 GROUP BY 子句和 ORDER BY 子句中使用索引
GROUP BY 子句和 ORDER BY 子句一般都會進行排序，以對行進行排列和替換，不過如果指定帶有索引的列作為這兩者的參數列，由于用到了索引，可以實現高速查詢，由于索引是有序的，排序本身都會被省略掉
⑤使用索引時，條件表達式的左側應該是原始字段
假設我們在 col 列上建立了索引，則下面這些 SQL 語句無法用到索引：
SELECT *
FROM SomeTable
WHERE col * 1.1 > 100;
SELECT *
FROM SomeTable
WHERE SUBSTR(col, 1, 1) = 'a';
以上第一個 SQL 在索引列上進行了運算, 第二個 SQL 對索引列使用了函數，均無法用到索引，正確方式是把列單獨放在左側，如下：
SELECT *
FROM SomeTable
WHERE col_1 > 100 / 1.1;
當然如果需要對此列使用函數，則無法避免在左側運算，可以考慮使用函數索引，不過一般不推薦隨意這么做。
⑥盡量避免使用否定形式
如下的幾種否定形式不能用到索引：
- <>
- !=
- NOT IN
所以以下 了SQL 語句會導致全表掃描：
SELECT *
FROM SomeTable
WHERE col_1 <> 100;
可以改成以下形式：
SELECT *
FROM SomeTable
WHERE col_1 > 100 or col_1 < 100;
⑦進行默認的類型轉換
假設 col 是 char 類型，則推薦使用以下第二，三條 SQL 的寫法，不推薦第一條 SQL 的寫法：
× 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));
雖然第一條 SQL 會默認把 10 轉成 '10'，但這種默認類型轉換不僅會增加額外的性能開銷，還會導致索引不可用，所以建議使用的時候進行類型轉換。
⑧減少中間表
在 SQL 中，子查詢的結果會產生一張新表，不過如果不加限制大量使用中間表的話，會帶來兩個問題：一是展示數據需要消耗內存資源，二是原始表中的索引不容易用到，所以盡量減少中間表也可以提升性能。
⑨靈活使用 HAVING 子句
這一點與上面第八條相呼應，對聚合結果指定篩選條件時，使用 HAVING 是基本的原則，可能一些工程師會傾向于使用下面這樣的寫法：
SELECT *
FROM (SELECT sale_date, MAX(quantity) AS max_qty
FROM SalesHistory
GROUP BY sale_date) TMP
WHERE max_qty >= 10;
雖然上面這樣的寫法能達到目的，但會生成 TMP 這張臨時表，所以應該使用下面這樣的寫法：
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;
HAVING 子句和聚合操作是同時執行的，所以比起生成中間表后再執行 HAVING 子句，效率會更高，代碼也更簡潔。
⑩需要對多個字段使用 IN 謂詞時，將它們匯總到一處
一個表的多個字段可能都使用了 IN 謂詞，如下：
SELECT id, state, city
FROM Addresses1 A1
WHERE state IN (SELECT state
FROM Addresses2 A2
WHERE A1.id = A2.id)
AND city IN (SELECT city
FROM Addresses2 A2
WHERE A1.id = A2.id);
這段代碼用到了兩個子查詢，也就產生了兩個中間表，可以像下面這樣寫：
SELECT *
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state|| city
FROM Addresses2 A2);
這樣子查詢不用考慮關聯性，沒有中間表產生，而且只執行一次即可。
?使用延遲查詢優化 limit [offset]，[rows]
經常出現類似以下的 SQL 語句：
SELECT * FROM film LIMIT 100000, 10
Offset 特別大!這是我司出現很多慢 SQL 的主要原因之一，尤其是在跑任務需要分頁執行時，經常跑著跑著 Offset 就跑到幾十萬了，導致任務越跑越慢。
LIMIT 能很好地解決分頁問題，但如果 Offset 過大的話，會造成嚴重的性能問題。
原因主要是因為 MySQL 每次會把一整行都掃描出來，掃描 Offset 遍，找到 Offset 之后會拋棄 Offset 之前的數據，再從 Offset 開始讀取 10 條數據，顯然，這樣的讀取方式問題。
可以通過延遲查詢的方式來優化，假設有以下 SQL，有組合索引(sex，rating)：
SELECT <cols> FROM profiles where sex='M' order by rating limit 100000, 10;
則上述寫法可以改成如下寫法：
SELECT <cols>
FROM profiles
inner join
(SELECT id form FROM profiles where x.sex='M' order by rating limit 100000, 10)
as x using(id);
這里利用了覆蓋索引的特性，先從覆蓋索引中獲取 100010 個 id，再丟充掉前 100000 條 id，保留最后 10 個 id 即可，丟掉 100000 條 id 不是什么大的開銷，所以這樣可以顯著提升性能。
?利用 LIMIT 1 取得唯一行
數據庫引擎只要發現滿足條件的一行數據則立即停止掃描，，這種情況適用于只需查找一條滿足條件的數據的情況。
?注意組合索引，要符合最左匹配原則才能生效
假設存在這樣順序的一個聯合索引“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_2 = 100 AND col_3 = 500 ;
前面兩條會命中索引，第三條由于沒有先匹配 col_1，導致無法命中索引， 另外如果無法保證查詢條件里列的順序與索引一致，可以考慮將聯合索引 拆分為多個索引。
?使用 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%';
上例中，只有第三條會命中索引，前面兩條進行后方一致或中間一致的匹配無法命中索引。
?簡單字符串表達式
模型字符串可以使用 _ 時，盡可能避免使用 %，假設某一列上為 char(5)。
不推薦：
SELECT
first_name,
last_name,
homeroom_nbr
FROM Students
WHERE homeroom_nbr LIKE 'A-1%';
推薦：
SELECT first_name, last_name
homeroom_nbr
FROM Students
WHERE homeroom_nbr LIKE 'A-1__'; --模式字符串中包含了兩個下劃線
?盡量使用自增 id 作為主鍵
比如現在有一個用戶表，有人說身份證是唯一的，也可以用作主鍵，理論上確實可以，不過用身份證作主鍵的話，一是占用空間相對于自增主鍵大了很多，二是很容易引起頻繁的頁分裂，造成性能問題。
主鍵選擇的幾個原則：自增，盡量小，不要對主鍵進行修改。
?如何優化 count(*)
使用以下 SQL 會導致慢查詢：
SELECT COUNT(*) FROM SomeTable
SELECT COUNT(1) FROM SomeTable
原因是會造成全表掃描，有人說 COUNT(*) 不是會利用主鍵索引去查找嗎，怎么還會慢，這就要談到 MySQL 中的聚簇索引和非聚簇索引了。
聚簇索引葉子節點上存有主鍵值+整行數據，非聚簇索葉子節點上則存有輔助索引的列值+主鍵值，如下：
所以就算對 COUNT(*) 使用主鍵查找，由于每次取出主鍵索引的葉子節點時，取的是一整行的數據，效率必然不高。
但是非聚簇索引葉子節點只存儲了「列值+主鍵值」，這也啟發我們可以用非聚簇索引來優化，假設表有一列叫 status，為其加上索引后，可以用以下語句優化：
SELECT COUNT(status) FROM SomeTable
有人曾經測過(見文末參考鏈接)，假設有 100 萬行數據，使用聚簇索引來查找行數的，比使用 COUNT(*) 查找速度快 10 幾倍。不過需要注意的是通過這種方式無法計算出 status 值為 null 的那些行。
如果主鍵是連續的，可以利用 MAX(id) 來查找，MAX 也利用到了索引，只需要定位到最大 id 即可，性能極好，如下，秒現結果：
SELECT MAX(id) FROM SomeTable
說句題句話，有人說用 MyISAM 引擎調用 COUNT(*) 非常快，那是因為它提前把行數存在磁盤中了，直接拿，當然很快，不過如果有 WHERE 的限制。
?避免使用 SELECT *，盡量利用覆蓋索引來優化性能
SELECT * 會提取出一整行的數據，如果查詢條件中用的是組合索引進行查找，還會導致回表(先根據組合索引找到葉子節點，再根據葉子節點上的主鍵回表查詢一整行)，降低性能。
而如果我們所要的數據就在組合索引里，只需讀取組合索引列，這樣網絡帶寬將大大減少,假設有組合索引列 (col_1, col_2)。
推薦用：
SELECT col_1, col_2
FROM SomeTable
WHERE col_1 = xxx AND col_2 = xxx
不推薦用：
SELECT *
FROM SomeTable
WHERE col_1 = xxx AND col_2 = xxx
?如有必要，使用 force index() 強制走某個索引
業務團隊曾經出現類似以下的慢 SQL 查詢：
SELECT *
FROM SomeTable
WHERE `status` = 0
AND `gmt_create` > 1490025600
AND `gmt_create` < 1490630400
AND `id` > 0
AND `post_id` IN ('67778', '67811', '67833', '67834', '67839', '67852', '67861', '67868', '67870', '67878', '67909', '67948', '67951', '67963', '67977', '67983', '67985', '67991', '68032', '68038'/*... omitted 480 items ...*/)
order by id asc limit 200;
post_id 也加了索引，理論上走 post_id 索引會很快查詢出來，但實現了通過 EXPLAIN 發現走的卻是 id 的索引(這里隱含了一個常見考點，在多個索引的情況下, MySQL 會如何選擇索引)。
而 id > 0 這個查詢條件沒啥用，直接導致了全表掃描， 所以在有多個索引的情況下一定要慎用。
可以使用 force index 來強制走某個索引，以這個例子為例，可以強制走 post_id 索引，效果立桿見影。
這種由于表中有多個索引導致 MySQL 誤選索引造成慢查詢的情況在業務中也是非常常見。
一方面是表索引太多，另一方面也是由于 SQL 語句本身太過復雜導致， 針對本例這種復雜的 SQL 查詢，其實用 ElasticSearch 搜索引擎來查找更合適，有機會到時出一篇文章說說。
?使用 EXPLAIN 來查看 SQL 執行計劃
上個點說了，可以使用 EXPLAIN 來分析 SQL 的執行情況，如怎么發現上文中的最左匹配原則不生效呢，執行 「EXPLAIN+SQL 語句」可以發現 key 為 None，說明確實沒有命中索引：
我司在提供 SQL 查詢的同時，也貼心地加了一個 EXPLAIN 功能及 SQL 的優化建議，建議各大公司效仿，如圖示：
- 批量插入，速度更快
當需要插入數據時，批量插入比逐條插入性能更高。
推薦用：
-- 批量插入
INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a'),(2,3,'b');
不推薦用：
INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a');
INSERT INTO TABLE (id, user_id, title) VALUES (2,3,'b');
批量插入 SQL 執行效率高的主要原因是合并后日志量 MySQL 的 binlog 和 innodb 的事務讓日志減少了，降低日志刷盤的數據量和頻率，從而提高了效率。
- 慢日志 SQL 定位
前面我們多次說了 SQL 的慢查詢，那么該怎么定位這些慢查詢 SQL 呢，主要用到了以下幾個參數：
這幾個參數一定要配好，再根據每條慢查詢對癥下藥，像我司每天都會把這些慢查詢提取出來通過郵件給形式發送給各個業務團隊，以幫忙定位解決。
小結：業務生產中可能還有很多 CASE 導致了慢查詢，其實細細品一下，都會發現這些都和 MySQL 索引的底層數據 B+ 樹有莫大的關系。
總結
本文一開始花了挺大的篇幅來講解 SQL 的規范，請大家務必重視這部分內部，良好的規范有利于團隊協作，對于代碼的閱讀也比較友好。
之后介紹了一些 SQL 的比較高級的用法，巧用這些技巧確實能達到事半功倍的效果。
