由于工作需要，最近做了很多 BI 取數(shù)的工作，需要用到一些比較高級的 SQL 技巧，總結(jié)了一下工作中用到的一些比較騷的進(jìn)階技巧，特此記錄一下，以方便自己查閱。

[[327588]]

 

圖片來自 Pexels

主要目錄如下：

• SQL 的書寫規(guī)范
• SQL 的一些進(jìn)階使用技巧
• SQL 的優(yōu)化方法

SQL 的書寫規(guī)范

在介紹一些技巧之前，有必要強(qiáng)調(diào)一下規(guī)范，這一點我發(fā)現(xiàn)工作中經(jīng)常被人忽略，其實遵循好的規(guī)范可讀性會好很多，應(yīng)該遵循哪些規(guī)范呢?

①表名要有意義，且標(biāo)準(zhǔn) SQL 中規(guī)定表名的第一個字符應(yīng)該是字母。

②注釋，有單行注釋和多行注釋，如下：

-- 單行注釋 
-- 從SomeTable中查詢col_1  
SELECT col_1 
  FROM SomeTable; 
 
/* 
多行注釋 
從 SomeTable 中查詢 col_1  
*/ 
SELECT col_1 
  FROM SomeTable; 

多行注釋很多人不知道，這種寫法不僅可以用來添加真正的注釋，也可以用來注釋代碼，非常方便。

③縮進(jìn)

就像寫 Java，Python 等編程語言一樣 ，SQL 也應(yīng)該有縮進(jìn)，良好的縮進(jìn)對提升代碼的可讀性幫助很大。

以下分別是好的縮進(jìn)與壞的縮進(jìn)示例：

-- 好的縮進(jìn) 
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 

④空格

代碼中應(yīng)該適當(dāng)留有一些空格，如果一點不留，代碼都湊到一起， 邏輯單元不明確，閱讀的人也會產(chǎn)生額外的壓力。

以下分別是是好的與壞的示例：

-- 好的示例 
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; 

④大小寫

關(guān)鍵字使用大小寫，表名列名使用小寫，如下：

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 

花了這么多時間強(qiáng)調(diào)規(guī)范，有必要嗎，有!好的規(guī)范讓代碼的可讀性更好，更有利于團(tuán)隊合作，之后的 SQL 示例都會遵循這些規(guī)范。

SQL 的一些進(jìn)階使用技巧

①巧用 CASE WHEN 進(jìn)行統(tǒng)計

來看看如何巧用 CASE WHEN 進(jìn)行定制化統(tǒng)計,假設(shè)我們有如下的需求，希望根據(jù)左邊各個市的人口統(tǒng)計每個省的人口：

 

使用 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 進(jìn)行更新

現(xiàn)在某公司員人工資信息表如下：

 

現(xiàn)在公司出臺了一個奇葩的規(guī)定：

• 對當(dāng)前工資為 1 萬以上的員工，降薪 10%。
• 對當(dāng)前工資低于 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，執(zhí)行第一個 SQL 后，工資變?yōu)?10500*0.9=9450, 緊接著又執(zhí)行條件 2， 工資變?yōu)榱?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 結(jié)合使用的，但其實它是可以獨立使用的， 假設(shè)有如下表，第一列 seq 叫連續(xù)編號，但其實有些編號是缺失的，怎么知道編號是否缺失呢?

用 HAVING 表示如下：

SELECT '存在缺失的編號' AS gap 
  FROM SeqTbl 
HAVING COUNT(*) <> MAX(seq); 

④自連接

針對相同的表進(jìn)行的連接被稱為“自連接”(self join)，這個技巧常常被人們忽視，其實是有挺多妙用的。

刪除重復(fù)行：上圖中有三個橘子，需要把這些重復(fù)的行給刪掉，用如下自連接可以解決：

DELETE FROM Products P1 
 WHERE id < ( SELECT MAX(P2.id)  
                   FROM Products P2  
                  WHERE P1.name = P2.name  
                    AND P1.price = P2.price );  

排序：在 DB 中，我們經(jīng)常需要按分?jǐn)?shù)，人數(shù)，銷售額等進(jìn)行排名，有 Oracle, DB2 中可以使用 RANK 函數(shù)進(jìn)行排名，不過在 MySQL 中 RANK 函數(shù)未實現(xiàn)。

這種情況我們可以使用自連接來實現(xiàn)，如對以下 Products 表按價格高低進(jìn)行排名：

使用自連接可以這么寫：

-- 排序從 1 開始。如果已出現(xiàn)相同位次，則跳過之后的位次  
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; 

結(jié)果如下：

name price rank  
----- ------ ------  
橘子    100     1  
西瓜     80     2  
蘋果     50     3  
葡萄     50     3  
香蕉     50     3  
檸檬     30     6 

⑤巧用 COALESCE 函數(shù)

此函數(shù)作用返回參數(shù)中的第一個非空表達(dá)式，假設(shè)有如下商品，我們重新格式化一樣，如果 city 為 null，代表商品不在此城市發(fā)行。

但我們在展示結(jié)果的時候不想展示 null，而想展示 'N/A', 可以這么做：

SELECT  
    COALESCE(city, 'N/A') 
  FROM 
    customers; 

 

SQL 性能優(yōu)化技巧

①參數(shù)是子查詢時，使用 EXISTS 代替 IN

如果 IN 的參數(shù)是(1，2，3)這樣的值列表時，沒啥問題，但如果參數(shù)是子查詢時，就需要注意了。

比如，現(xiàn)在有如下兩個表：

 

現(xiàn)在我們要查出同時存在于兩個表的員工，即田中和鈴木，則以下用 IN 和 EXISTS 返回的結(jié)果是一樣，但是用 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 運(yùn)行更快呢，有兩個原因：

• 可以`用到索引，如果連接列 (id) 上建立了索引，那么查詢 Class_B 時不用查實際的表，只需查索引就可以了。
• 如果使用 EXISTS，那么只要查到一行數(shù)據(jù)滿足條件就會終止查詢， 不用像使用 IN 時一樣掃描全表。在這一點上 NOT EXISTS 也一樣。

另外如果 IN 后面如果跟著的是子查詢，由于 SQL 會先執(zhí)行 IN 后面的子查詢，會將子查詢的結(jié)果保存在一張臨時的工作表里(內(nèi)聯(lián)視圖)，然后掃描整個視圖。

顯然掃描整個視圖這個工作很多時候是非常耗時的，而用 EXISTS 不會生成臨時表。

當(dāng)然了，如果 IN 的參數(shù)是子查詢時，也可以用連接來代替，如下：

-- 使用連接代替 IN SELECT A.id, A.name 
FROM Class_A A INNER JOIN Class_B B ON A.id = B.id; 

用到了 「id」列上的索引，而且由于沒有子查詢，也不會生成臨時表。

②避免排序

SQL 是聲明式語言，即對用戶來說，只關(guān)心它能做什么，不用關(guān)心它怎么做，這樣可能會產(chǎn)生潛在的性能問題：排序。

會產(chǎn)生排序的代表性運(yùn)算有下面這些：

• GROUP BY 子句
• ORDER BY 子句
• 聚合函數(shù)(SUM、COUNT、AVG、MAX、MIN)
• DISTINCT
• 集合運(yùn)算符(UNION、INTERSECT、EXCEPT)
• 窗口函數(shù)(RANK、ROW_NUMBER 等)

如果在內(nèi)存中排序還好，但如果內(nèi)存不夠?qū)е滦枰谟脖P上排序上的話，性能就會急劇下降，所以我們需要減少不必要的排序。

怎樣做可以減少排序呢?有如下幾點：

使用集合運(yùn)算符的 ALL 可選項：SQL 中有 UNION，INTERSECT，EXCEPT 三個集合運(yùn)算符。

默認(rèn)情況下，這些運(yùn)算符會為了避免重復(fù)數(shù)據(jù)而進(jìn)行排序，對比一下使用 UNION 運(yùn)算符加和不加 ALL 的情況：

 

注意：加 ALL 是優(yōu)化性能非常有效的手段，不過前提是不在乎結(jié)果是否有重復(fù)數(shù)據(jù)。

使用 EXISTS 代表 DISTINCT：為了排除重復(fù)數(shù)據(jù)，DISTINCT 也會對結(jié)果進(jìn)行排序，如果需要對兩張表的連接結(jié)果進(jìn)行去重，可以考慮用 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); 

既用到了索引，又避免了排序?qū)π阅艿膿p耗。

②在極值函數(shù)中使用索引(MAX/MIN)

使用 MAX/ MIN 都會對進(jìn)行排序，如果參數(shù)字段上沒加索引會導(dǎo)致全表掃描，如果建有索引，則只需要掃描索引即可，對比如下：

-- 這樣寫需要掃描全表  
SELECT MAX(item) 
  FROM Items; 
 
-- 這樣寫能用到索引  
SELECT MAX(item_no) 
  FROM Items; 

注意：極值函數(shù)參數(shù)推薦為索引列中并不是不需要排序，而是優(yōu)化了排序前的查找速度(畢竟索引本身就是有序排列的)。

③能寫在 WHERE 子句里的條件不要寫在 HAVING 子句里

下列 SQL 語句返回的結(jié)果是一樣的：

-- 聚合后使用 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 子句進(jìn)行聚合時會進(jìn)行排序，如果事先通過 WHERE 子句能篩選出一部分行，能減輕排序的負(fù)擔(dān)。
• 在 WHERE 子句中可以使用索引，而 HAVING 子句是針對聚合后生成的視頻進(jìn)行篩選的，但很多時候聚合后生成的視圖并沒有保留原表的索引結(jié)構(gòu)。

④在 GROUP BY 子句和 ORDER BY 子句中使用索引

GROUP BY 子句和 ORDER BY 子句一般都會進(jìn)行排序，以對行進(jìn)行排列和替換，不過如果指定帶有索引的列作為這兩者的參數(shù)列，由于用到了索引，可以實現(xiàn)高速查詢，由于索引是有序的，排序本身都會被省略掉

⑤使用索引時，條件表達(dá)式的左側(cè)應(yīng)該是原始字段

假設(shè)我們在 col 列上建立了索引，則下面這些 SQL 語句無法用到索引：

SELECT * 
  FROM SomeTable 
 WHERE col * 1.1 > 100; 
 
SELECT * 
  FROM SomeTable 
 WHERE SUBSTR(col, 1, 1) = 'a'; 

以上第一個 SQL 在索引列上進(jìn)行了運(yùn)算, 第二個 SQL 對索引列使用了函數(shù)，均無法用到索引，正確方式是把列單獨放在左側(cè)，如下：

SELECT * 
  FROM SomeTable 
 WHERE col_1 > 100 / 1.1; 

當(dāng)然如果需要對此列使用函數(shù)，則無法避免在左側(cè)運(yùn)算，可以考慮使用函數(shù)索引，不過一般不推薦隨意這么做。

⑥盡量避免使用否定形式

如下的幾種否定形式不能用到索引：

 

• <>
• !=
• NOT IN

所以以下 了SQL 語句會導(dǎo)致全表掃描：

SELECT * 
  FROM SomeTable 
 WHERE col_1 <> 100; 

可以改成以下形式：

SELECT * 
  FROM SomeTable 
 WHERE col_1 > 100 or col_1 < 100; 

⑦進(jìn)行默認(rèn)的類型轉(zhuǎn)換

假設(shè) 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 會默認(rèn)把 10 轉(zhuǎn)成 '10'，但這種默認(rèn)類型轉(zhuǎn)換不僅會增加額外的性能開銷，還會導(dǎo)致索引不可用，所以建議使用的時候進(jìn)行類型轉(zhuǎn)換。

⑧減少中間表

在 SQL 中，子查詢的結(jié)果會產(chǎn)生一張新表，不過如果不加限制大量使用中間表的話，會帶來兩個問題：一是展示數(shù)據(jù)需要消耗內(nèi)存資源，二是原始表中的索引不容易用到，所以盡量減少中間表也可以提升性能。

⑨靈活使用 HAVING 子句

這一點與上面第八條相呼應(yīng)，對聚合結(jié)果指定篩選條件時，使用 HAVING 是基本的原則，可能一些工程師會傾向于使用下面這樣的寫法：

SELECT * 
  FROM (SELECT sale_date, MAX(quantity) AS max_qty 
          FROM SalesHistory  
         GROUP BY sale_date) TMP 
         WHERE max_qty >= 10; 

雖然上面這樣的寫法能達(dá)到目的，但會生成 TMP 這張臨時表，所以應(yīng)該使用下面這樣的寫法：

SELECT sale_date, MAX(quantity)  
  FROM SalesHistory 
 GROUP BY sale_date 
HAVING MAX(quantity) >= 10; 

HAVING 子句和聚合操作是同時執(zhí)行的，所以比起生成中間表后再執(zhí)行 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); 

這段代碼用到了兩個子查詢，也就產(chǎn)生了兩個中間表，可以像下面這樣寫：

SELECT * 
  FROM Addresses1 A1 
 WHERE id || state || city 
 IN (SELECT id || state|| city 
       FROM Addresses2 A2); 

這樣子查詢不用考慮關(guān)聯(lián)性，沒有中間表產(chǎn)生，而且只執(zhí)行一次即可。

⑪使用延遲查詢優(yōu)化 limit [offset]，[rows]

經(jīng)常出現(xiàn)類似以下的 SQL 語句：

SELECT * FROM film LIMIT 100000, 10 

Offset 特別大!這是我司出現(xiàn)很多慢 SQL 的主要原因之一，尤其是在跑任務(wù)需要分頁執(zhí)行時，經(jīng)常跑著跑著 Offset 就跑到幾十萬了，導(dǎo)致任務(wù)越跑越慢。

LIMIT 能很好地解決分頁問題，但如果 Offset 過大的話，會造成嚴(yán)重的性能問題。

原因主要是因為 MySQL 每次會把一整行都掃描出來，掃描 Offset 遍，找到 Offset 之后會拋棄 Offset 之前的數(shù)據(jù)，再從 Offset 開始讀取 10 條數(shù)據(jù)，顯然，這樣的讀取方式問題。

可以通過延遲查詢的方式來優(yōu)化，假設(shè)有以下 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 取得唯一行

數(shù)據(jù)庫引擎只要發(fā)現(xiàn)滿足條件的一行數(shù)據(jù)則立即停止掃描，，這種情況適用于只需查找一條滿足條件的數(shù)據(jù)的情況。

⑬注意組合索引，要符合最左匹配原則才能生效

假設(shè)存在這樣順序的一個聯(lián)合索引“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，導(dǎo)致無法命中索引， 另外如果無法保證查詢條件里列的順序與索引一致，可以考慮將聯(lián)合索引 拆分為多個索引。

⑭使用 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%'; 

上例中，只有第三條會命中索引，前面兩條進(jìn)行后方一致或中間一致的匹配無法命中索引。

⑮簡單字符串表達(dá)式

模型字符串可以使用 _ 時，盡可能避免使用 %，假設(shè)某一列上為 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 作為主鍵

比如現(xiàn)在有一個用戶表，有人說身份證是唯一的，也可以用作主鍵，理論上確實可以，不過用身份證作主鍵的話，一是占用空間相對于自增主鍵大了很多，二是很容易引起頻繁的頁分裂，造成性能問題。

主鍵選擇的幾個原則：自增，盡量小，不要對主鍵進(jìn)行修改。

⑰如何優(yōu)化 count(*)

使用以下 SQL 會導(dǎo)致慢查詢：

SELECT COUNT(*) FROM SomeTable 
SELECT COUNT(1) FROM SomeTable 

原因是會造成全表掃描，有人說 COUNT(*) 不是會利用主鍵索引去查找嗎，怎么還會慢，這就要談到 MySQL 中的聚簇索引和非聚簇索引了。

聚簇索引葉子節(jié)點上存有主鍵值+整行數(shù)據(jù)，非聚簇索葉子節(jié)點上則存有輔助索引的列值+主鍵值，如下：

 

所以就算對 COUNT(*) 使用主鍵查找，由于每次取出主鍵索引的葉子節(jié)點時，取的是一整行的數(shù)據(jù)，效率必然不高。

但是非聚簇索引葉子節(jié)點只存儲了「列值+主鍵值」，這也啟發(fā)我們可以用非聚簇索引來優(yōu)化，假設(shè)表有一列叫 status，為其加上索引后，可以用以下語句優(yōu)化：

SELECT COUNT(status) FROM SomeTable 

有人曾經(jīng)測過(見文末參考鏈接)，假設(shè)有 100 萬行數(shù)據(jù)，使用聚簇索引來查找行數(shù)的，比使用 COUNT(*) 查找速度快 10 幾倍。不過需要注意的是通過這種方式無法計算出 status 值為 null 的那些行。

如果主鍵是連續(xù)的，可以利用 MAX(id) 來查找，MAX 也利用到了索引，只需要定位到最大 id 即可，性能極好，如下，秒現(xiàn)結(jié)果：

SELECT MAX(id) FROM SomeTable 

說句題句話，有人說用 MyISAM 引擎調(diào)用 COUNT(*) 非?？?，那是因為它提前把行數(shù)存在磁盤中了，直接拿，當(dāng)然很快，不過如果有 WHERE 的限制。

⑱避免使用 SELECT *，盡量利用覆蓋索引來優(yōu)化性能

SELECT * 會提取出一整行的數(shù)據(jù)，如果查詢條件中用的是組合索引進(jìn)行查找，還會導(dǎo)致回表(先根據(jù)組合索引找到葉子節(jié)點，再根據(jù)葉子節(jié)點上的主鍵回表查詢一整行)，降低性能。

而如果我們所要的數(shù)據(jù)就在組合索引里，只需讀取組合索引列，這樣網(wǎng)絡(luò)帶寬將大大減少,假設(shè)有組合索引列 (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() 強(qiáng)制走某個索引

業(yè)務(wù)團(tuán)隊曾經(jīng)出現(xiàn)類似以下的慢 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 索引會很快查詢出來，但實現(xiàn)了通過 EXPLAIN 發(fā)現(xiàn)走的卻是 id 的索引(這里隱含了一個常見考點，在多個索引的情況下, MySQL 會如何選擇索引)。

而 id > 0 這個查詢條件沒啥用，直接導(dǎo)致了全表掃描， 所以在有多個索引的情況下一定要慎用。

可以使用 force index 來強(qiáng)制走某個索引，以這個例子為例，可以強(qiáng)制走 post_id 索引，效果立桿見影。

這種由于表中有多個索引導(dǎo)致 MySQL 誤選索引造成慢查詢的情況在業(yè)務(wù)中也是非常常見。

一方面是表索引太多，另一方面也是由于 SQL 語句本身太過復(fù)雜導(dǎo)致， 針對本例這種復(fù)雜的 SQL 查詢，其實用 ElasticSearch 搜索引擎來查找更合適，有機(jī)會到時出一篇文章說說。

⑳使用 EXPLAIN 來查看 SQL 執(zhí)行計劃

上個點說了，可以使用 EXPLAIN 來分析 SQL 的執(zhí)行情況，如怎么發(fā)現(xiàn)上文中的最左匹配原則不生效呢，執(zhí)行 「EXPLAIN+SQL 語句」可以發(fā)現(xiàn) key 為 None，說明確實沒有命中索引：

 

我司在提供 SQL 查詢的同時，也貼心地加了一個 EXPLAIN 功能及 SQL 的優(yōu)化建議，建議各大公司效仿，如圖示：

 

• 批量插入，速度更快

當(dāng)需要插入數(shù)據(jù)時，批量插入比逐條插入性能更高。

推薦用：

-- 批量插入 
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 執(zhí)行效率高的主要原因是合并后日志量 MySQL 的 binlog 和 innodb 的事務(wù)讓日志減少了，降低日志刷盤的數(shù)據(jù)量和頻率，從而提高了效率。

• 慢日志 SQL 定位

前面我們多次說了 SQL 的慢查詢，那么該怎么定位這些慢查詢 SQL 呢，主要用到了以下幾個參數(shù)：

 

這幾個參數(shù)一定要配好，再根據(jù)每條慢查詢對癥下藥，像我司每天都會把這些慢查詢提取出來通過郵件給形式發(fā)送給各個業(yè)務(wù)團(tuán)隊，以幫忙定位解決。

小結(jié)：業(yè)務(wù)生產(chǎn)中可能還有很多 CASE 導(dǎo)致了慢查詢，其實細(xì)細(xì)品一下，都會發(fā)現(xiàn)這些都和 MySQL 索引的底層數(shù)據(jù) B+ 樹有莫大的關(guān)系。

總結(jié)

本文一開始花了挺大的篇幅來講解 SQL 的規(guī)范，請大家務(wù)必重視這部分內(nèi)部，良好的規(guī)范有利于團(tuán)隊協(xié)作，對于代碼的閱讀也比較友好。

之后介紹了一些 SQL 的比較高級的用法，巧用這些技巧確實能達(dá)到事半功倍的效果。