最近博主看完了《SQL進(jìn)階教程》這本書(shū)，看完后給博主打開(kāi)了SQL世界的新大門，對(duì)于 SQL 的理解不在局限于以前的常規(guī)用法。借用其他讀者的評(píng)論，

讀完醍醐灌頂，對(duì)SQL做到了知其然更能知其所以然。全書(shū)從頭到尾強(qiáng)調(diào)了 SQL的內(nèi)在邏輯是基于集合論和謂詞邏輯，而著兩條主線恰恰在使用SQL起到了至關(guān)重要的指導(dǎo)作用。

(資料圖片僅供參考)

本文給大家總結(jié)如何讓SQL起飛（優(yōu)化）

一、SQL寫法優(yōu)化

在SQL中，很多時(shí)候不同的SQL代碼能夠得出相同結(jié)果。從理論上來(lái)說(shuō)，我們認(rèn)為得到相同結(jié)果的不同SQL之間應(yīng)該有相同的性能，但遺憾的是，查詢優(yōu)化器生成的執(zhí)行計(jì)劃很大程度上受到SQL代碼影響，有快有慢。因此如果想優(yōu)化查詢性能，我們必須知道如何寫出更快的SQL，才能使優(yōu)化器的執(zhí)行效率更高。

1.1 子查詢用EXISTS代替IN

當(dāng)IN的參數(shù)是子查詢時(shí)，數(shù)據(jù)庫(kù)首先會(huì)執(zhí)行子查詢，然后將結(jié)果存儲(chǔ)在一張臨時(shí)的工作表里（內(nèi)聯(lián)視圖），然后掃描整個(gè)視圖。很多情況下這種做法都非常耗費(fèi)資源。使用EXISTS的話，數(shù)據(jù)庫(kù)不會(huì)生成臨時(shí)的工作表。但是從代碼的可讀性上來(lái)看，IN要比EXISTS好。使用IN時(shí)的代碼看起來(lái)更加一目了然，易于理解。因此，如果確信使用IN也能快速獲取結(jié)果，就沒(méi)有必要非得改成EXISTS了。

這里用Class_A表和Class_B舉例，

我們?cè)囍鴱腃lass_A表中查出同時(shí)存在于Class_B表中的員工。下面兩條SQL語(yǔ)句返回的結(jié)果是一樣的，但是使用EXISTS的SQL語(yǔ)句更快一些。

--慢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);

復(fù)制

使用EXISTS時(shí)更快的原因有以下兩個(gè)。

如果連接列（id）上建立了索引，那么查詢 tb_b 時(shí)不用查實(shí)際的表，只需查索引就可以了。（同樣的IN也可以使用索引，這不是重要原因）如果使用EXISTS，那么只要查到一行數(shù)據(jù)滿足條件就會(huì)終止查詢，不用像使用IN時(shí)一樣掃描全表。在這一點(diǎn)上NOT EXISTS也一樣。

實(shí)際上，大部分情況在子查詢數(shù)量較小的場(chǎng)景下EXISTS和IN的查詢性能不相上下，由EXISTS查詢更快第二點(diǎn)可知，子查詢數(shù)量較大時(shí)使用EXISTS才會(huì)有明顯優(yōu)勢(shì)。

1.2 避免排序并添加索引

在SQL語(yǔ)言中，除了ORDER BY子句會(huì)進(jìn)行顯示排序外，還有很多操作默認(rèn)也會(huì)在暗中進(jìn)行排序，如果排序字段沒(méi)有添加索引，會(huì)導(dǎo)致查詢性能很慢。SQL中會(huì)進(jìn)行排序的代表性的運(yùn)算有下面這些。

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

如上列出的六種運(yùn)算（除了集合運(yùn)算符），它們后面跟隨或者指定的字段都可以添加索引，這樣可以加快排序。

實(shí)際上在DISTINCT關(guān)鍵字、GROUP BY子句、ORDER BY子句、聚合函數(shù)跟隨的字段都添加索引，不僅能加速查詢，還能加速排序。

1.3 用EXISTS代替DISTINCT

為了排除重復(fù)數(shù)據(jù)，我們可能會(huì)使用DISTINCT關(guān)鍵字。如1.2中所說(shuō)，默認(rèn)情況下，它也會(huì)進(jìn)行暗中排序。如果需要對(duì)兩張表的連接結(jié)果進(jìn)行去重，可以考慮使用EXISTS代替DISTINCT，以避免排序。這里用Items表和SalesHistory表舉例：

我們思考一下如何從上面的商品表Items中找出同時(shí)存在于銷售記錄表SalesHistory中的商品。簡(jiǎn)而言之，就是找出有銷售記錄的商品。

在一（Items）對(duì)多（SalesHistory）的場(chǎng)景下，我們需要對(duì)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

復(fù)制

使用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

復(fù)制

這條語(yǔ)句在執(zhí)行過(guò)程中不會(huì)進(jìn)行排序。而且使用EXISTS和使用連接一樣高效。

1.4 集合運(yùn)算ALL可選項(xiàng)

SQL中有UNION、INTERSECT、EXCEPT三個(gè)集合運(yùn)算符。在默認(rèn)的使用方式下，這些運(yùn)算符會(huì)為了排除掉重復(fù)數(shù)據(jù)而進(jìn)行排序。

MySQL還沒(méi)有實(shí)現(xiàn)INTERSECT和EXCEPT運(yùn)算

如果不在乎結(jié)果中是否有重復(fù)數(shù)據(jù)，或者事先知道不會(huì)有重復(fù)數(shù)據(jù)，請(qǐng)使用UNION ALL代替UNION。這樣就不會(huì)進(jìn)行排序了。

1.5 WHERE條件不要寫在HAVING字句

例如，這里繼續(xù)用SalesHistory表舉例，下面兩條SQL語(yǔ)句返回的結(jié)果是一樣的：

--聚合后使用HAVING子句過(guò)濾SELECT sale_date, SUM(quantity)  FROM SalesHistory GROUP BY sale_dateHAVING sale_date = "2007-10-01";--聚合前使用WHERE子句過(guò)濾SELECT sale_date, SUM(quantity)  FROM SalesHistory WHERE sale_date = "2007-10-01" GROUP BY sale_date;

復(fù)制

但是從性能上來(lái)看，第二條語(yǔ)句寫法效率更高。原因有兩個(gè)：

使用GROUP BY子句聚合時(shí)會(huì)進(jìn)行排序，如果事先通過(guò)WHERE子句篩選出一部分行，就能夠減輕排序的負(fù)擔(dān)。在WHERE子句的條件里可以使用索引。HAVING子句是針對(duì)聚合后生成的視圖進(jìn)行篩選的，但是很多時(shí)候聚合后的視圖都沒(méi)有繼承原表的索引結(jié)構(gòu)。

二、真的用到索引了嗎

2.1 隱式的類型轉(zhuǎn)換

如下，col_1字段是char類型：

SELECT * FROM SomeTable WHERE col_1 = 10; -- 走了索引SELECT * FROM SomeTable WHERE col_1 ="10"; -- 沒(méi)走索引SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2)); -- 走了索引

復(fù)制

當(dāng)查詢條件左邊和右邊類型不一致時(shí)會(huì)導(dǎo)致索引失效。

2.2 在索引字段上進(jìn)行運(yùn)算

如下：

SELECT *  FROM SomeTable WHERE col_1 * 1.1 > 100;

復(fù)制

在索引字段col_1上進(jìn)行運(yùn)算會(huì)導(dǎo)致索引不生效，把運(yùn)算的表達(dá)式放到查詢條件的右側(cè)，就能用到索引了，像下面這樣寫就OK了。

WHERE col_1 > 100 / 1.1

復(fù)制

如果無(wú)法避免在左側(cè)進(jìn)行運(yùn)算，那么使用函數(shù)索引也是一種辦法，但是不太推薦隨意這么做。使用索引時(shí)，條件表達(dá)式的左側(cè)應(yīng)該是原始字段請(qǐng)牢記，這一點(diǎn)是在優(yōu)化索引時(shí)首要關(guān)注的地方。

2.3 使用否定形式

下面這幾種否定形式不能用到索引。

<>!=NOT

這個(gè)是跟具體數(shù)據(jù)庫(kù)的優(yōu)化器有關(guān)，如果優(yōu)化器覺(jué)得即使走了索引，還是需要掃描很多很多行的哈，他可以選擇直接不走索引。平時(shí)我們用!=、<>、not in的時(shí)候，要注意一下。

2.4 使用OR查詢前后沒(méi)有同時(shí)使用索引

例如下表：

CREATE TABLE test_tb ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(55) NOT NULLPRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

復(fù)制

使用OR條件進(jìn)行查詢

SELECT * FROM test_tb WHERE id = 1 OR name = "tom"

復(fù)制

這個(gè)SQL的執(zhí)行條件下，很明顯id字段查詢會(huì)走索引，但是對(duì)于OR后面name字段的查詢是需要進(jìn)行全表掃描的。在這個(gè)場(chǎng)景下，優(yōu)化器直接進(jìn)行一遍全表掃描就完事了。

2.5 使用聯(lián)合索引時(shí)，列的順序錯(cuò)誤

使用聯(lián)合索引需要滿足最左匹配原則，即最左優(yōu)先。如果你建立一個(gè)（col_1, col_2, col_3）的聯(lián)合索引，相當(dāng)于建立了 (col_1)、(col_1,col_2)、(col_1,col_2,col_3) 三個(gè)索引。如下例子：

-- 走了索引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 ;-- 沒(méi)走索引SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;-- 沒(méi)走索引SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;-- 沒(méi)走索引SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

復(fù)制

聯(lián)合索引中的第一列（col_1）必須寫在查詢條件的開(kāi)頭，而且索引中列的順序不能顛倒。

2.6 使用LIKE查詢

并不是用了like通配符，索引一定會(huì)失效，而是like查詢是以%開(kāi)頭，才會(huì)導(dǎo)致索引失效。

-- 沒(méi)走索引SELECT  *  FROM  SomeTable  WHERE  col_1  LIKE"%a";-- 沒(méi)走索引SELECT  *  FROM  SomeTable  WHERE  col_1  LIKE"%a%";-- 走了索引SELECT  *  FROM  SomeTable  WHERE  col_1  LIKE"a%";

復(fù)制

2.7 連接字段字符集編碼不一致

如果兩張表進(jìn)行連接，關(guān)聯(lián)字段編碼不一致會(huì)導(dǎo)致關(guān)聯(lián)字段上的索引失效，這是博主在線上經(jīng)歷一次SQL慢查詢后的得到的結(jié)果，舉例如下，有如下兩表，它們的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;

復(fù)制

進(jìn)行SQL查詢?nèi)缦拢?/p>

EXPLAINSELECT * from `user` u join user_job j on u.name = j.name

復(fù)制

由結(jié)果可知，user表的查詢沒(méi)有走索引。想要user表也走索引，那就需要把user表name字段的編碼改成utf8即可。

三、減少中間表

在SQL中，子查詢的結(jié)果會(huì)被看成一張新表，這張新表與原始表一樣，可以通過(guò)代碼進(jìn)行操作。這種高度的相似性使得SQL編程具有非常強(qiáng)的靈活性，但是如果不加限制地大量使用中間表，會(huì)導(dǎo)致查詢性能下降。

頻繁使用中間表會(huì)帶來(lái)兩個(gè)問(wèn)題，一是展開(kāi)數(shù)據(jù)需要耗費(fèi)內(nèi)存資源，二是原始表中的索引不容易使用到（特別是聚合時(shí)）。因此，盡量減少中間表的使用也是提升性能的一個(gè)重要方法。

3.1 使用HAVING子句

對(duì)聚合結(jié)果指定篩選條件時(shí)，使用HAVING子句是基本原則。不習(xí)慣使用HAVING子句的人可能會(huì)傾向于像下面這樣先生成一張中間表，然后在WHERE子句中指定篩選條件。例如下面：

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

復(fù)制

然而，對(duì)聚合結(jié)果指定篩選條件時(shí)不需要專門生成中間表，像下面這樣使用HAVING子句就可以。

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

復(fù)制

HAVING子句和聚合操作是同時(shí)執(zhí)行的，所以比起生成中間表后再執(zhí)行的WHERE子句，效率會(huì)更高一些，而且代碼看起來(lái)也更簡(jiǎn)潔。

3.2 對(duì)多個(gè)字段使用IN

當(dāng)我們需要對(duì)多個(gè)字段使用IN條件查詢時(shí)，可以通過(guò) || 操作將字段連接在一起變成一個(gè)字符串處理。

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

復(fù)制

這樣一來(lái)，子查詢不用考慮關(guān)聯(lián)性，而且只執(zhí)行一次就可以。

3.3 先進(jìn)行連接再進(jìn)行聚合

連接和聚合同時(shí)使用時(shí)，先進(jìn)行連接操作可以避免產(chǎn)生中間表。原因是，從集合運(yùn)算的角度來(lái)看，連接做的是“乘法運(yùn)算”。連接表雙方是一對(duì)一、一對(duì)多的關(guān)系時(shí)，連接運(yùn)算后數(shù)據(jù)的行數(shù)不會(huì)增加。而且，因?yàn)樵诤芏嘣O(shè)計(jì)中多對(duì)多的關(guān)系都可以分解成兩個(gè)一對(duì)多的關(guān)系，因此這個(gè)技巧在大部分情況下都可以使用。

到此本文講解完畢，感謝大家閱讀。

關(guān)鍵詞：