實(shí)戰(zhàn)5：如何優(yōu)化你的 SQL 查詢(xún)

1. 前言

在前面的小節(jié)和實(shí)戰(zhàn)中，我們一直在學(xué)習(xí)和討論如何寫(xiě) SQL，如何用 SQL 完成一個(gè)業(yè)務(wù)功能點(diǎn)。本小節(jié)，我們將以優(yōu)化的角度來(lái)探討一下如何優(yōu)化 SQL，讓 SQL 更加高效的運(yùn)行。

SQL 優(yōu)化是一個(gè)很大的專(zhuān)題，本節(jié)會(huì)介紹幾種常見(jiàn)的 SQL 優(yōu)化手段和一些好用的優(yōu)化工具。

2. 工具

SQL 優(yōu)化并不簡(jiǎn)單，因此我們可以利用一些工具來(lái)幫助我們。

2.1 soar

soar是小米開(kāi)源的一款 SQL 優(yōu)化和改寫(xiě)的工具，它使用簡(jiǎn)單而且特性十分豐富，你可以點(diǎn)擊此鏈接來(lái)安裝 soar，安裝成功后，我們來(lái)一起使用一下 soar。

2.1.1 soar 實(shí)例

舉個(gè)簡(jiǎn)單的例子：

soar -query 'SELECT * FROM imooc_user WHERE id=1;'

soar 的使用十分簡(jiǎn)單，通過(guò)query參數(shù)指定一條需要分析的SQL語(yǔ)句即可，調(diào)用成功后，soar會(huì)自動(dòng)在控制臺(tái)打印出分析結(jié)果，如下：

# Query: 93A5517F0971C47A

★ ★ ★ ★ ☆ 95分

?```sql

SELECT
  *
FROM
  imooc_user
WHERE
  id= 1
?```

## 不建議使用 SELECT * 類(lèi)型查詢(xún)

* **Item:**  COL.001

* **Severity:**  L1

* **Content:**  當(dāng)表結(jié)構(gòu)變更時(shí)，使用 \* 通配符選擇所有列將導(dǎo)致查詢(xún)的含義和行為會(huì)發(fā)生更改，可能導(dǎo)致查詢(xún)返回更多的數(shù)據(jù)。

soar 分析的結(jié)果默認(rèn)以markdown的格式展現(xiàn)，且分析結(jié)果十分豐富，不僅給出了格式化后易讀的 SQL 和建議，還打了分。

其中Item是規(guī)則代碼，每個(gè)規(guī)則都有相應(yīng)的代號(hào)，Severity是等級(jí)，等級(jí)越高代表越危險(xiǎn)，越需要優(yōu)化，L1是較低的等級(jí)，Content指明了優(yōu)化原因。

2.1.2 soar 優(yōu)化 SQL

上面的語(yǔ)句中，建議不使用*，因?yàn)樽侄巫兏鼘?dǎo)致數(shù)據(jù)發(fā)生變化，按照 soar 的提示我們優(yōu)化一下 SQL：

soar -query 'SELECT id,username,age FROM imooc_user WHERE id=1;' > profile.md

我們不僅優(yōu)化了*，且將分析結(jié)果保存到了本地的profile.md文件，內(nèi)容如下：

# Query: 54BE4DEFF01C4432

★ ★ ★ ★ ★ 100分

?```sql

SELECT
  id, username, age
FROM
  imooc_user
WHERE
  id= 1
?```

## OK

優(yōu)化后，直接獲得了 100 分（滿(mǎn)分）。

soar 是一款簡(jiǎn)單且好用的工具，它還有很多特性值得大家去挖掘和探索，你可以點(diǎn)開(kāi)它的文檔去觀閱一番，對(duì)于它的介紹這里也將告一段落了。

2.2 EXPLAIN

explain是數(shù)據(jù)庫(kù)自帶的 SQL 分析工具，簡(jiǎn)單、實(shí)用且強(qiáng)大。下面我們以 MySQL 的explain工具為例來(lái)介紹一下它的使用。

請(qǐng)先執(zhí)行一下語(yǔ)句方便進(jìn)行測(cè)試：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

2.2.1 使用 explain

explain的使用很簡(jiǎn)單，在它的后面接上需要分析的 SQL 語(yǔ)句即可，如下：

EXPLAIN SELECT * FROM imooc_user WHERE id=1;

執(zhí)行成功后，得到如下結(jié)果：

+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+
| id | select_type | table      | type  | possible_keys | key     | ref   | rows | filtered | Extra  |
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | imooc_user | const | PRIMARY       | PRIMARY | const | 1    | 100.0    | <null> |
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+

我們并未貼上全部結(jié)果，而是選取了其中重要的部分。id是SELECT語(yǔ)句的 id，select_type代表這次查詢(xún)僅僅是一條簡(jiǎn)單的查詢(xún)，table無(wú)需贅言，possible_keys表示可能用到的索引，extra是一些額外信息。

而剩下的就是一些比較重要的信息了：

• type是針對(duì)單表的訪(fǎng)問(wèn)方法類(lèi)型，const是常數(shù)類(lèi)型，表示查詢(xún)速度極快，在常數(shù)時(shí)間內(nèi)即可返回；
• key表示使用到的索引，PRIMARY表示用到了主鍵索引；
• ref意思是使用索引等值查詢(xún)時(shí)，與索引列比較的對(duì)象信息，這個(gè)比較抽象，大致的意思是，索引使用了何種類(lèi)型進(jìn)行比較，const即使用常數(shù)比較，id 1 就是常數(shù)；
• rows是預(yù)估需要讀取記錄的條數(shù)，1代表只需要讀取一行，rows 越小越好；
• filtered表示查詢(xún)過(guò)濾后未搜索到的記錄百分比，100.0表示未搜索到的幾乎占100%，filtered 越大越好。

因此從分析結(jié)果可以看出，這條語(yǔ)句性能極好，除非數(shù)據(jù)庫(kù)波動(dòng)，否則完全不用擔(dān)心查詢(xún)速度問(wèn)題。

2.2.2 explain 優(yōu)化 SQL

那么什么樣的語(yǔ)句查詢(xún)效率比較低了，我們看一下這個(gè)語(yǔ)句：

EXPLAIN SELECT * FROM imooc_user WHERE age=22;

分析結(jié)果如下：

+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+
| id | select_type | table      | type | possible_keys | key    | ref    | rows | filtered | Extra       |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+
| 1  | SIMPLE      | imooc_user | ALL  | <null>        | <null> | <null> | 5    | 20.0     | Using where |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+

我們?nèi)匀唤厝×瞬糠中畔?，我們將目光聚焦?code>type和rows上，這里的type不再是const而是ALL，ALL表示全表掃描，是最慢的一個(gè)級(jí)別，rows為5，表示這次查詢(xún)將會(huì)掃描5條記錄，而我們總共才5條記錄。

這個(gè)查詢(xún)的性能是極為糟糕的，試想一下，如果該表的數(shù)據(jù)是幾萬(wàn)行乃至幾十萬(wàn)行，一次查詢(xún)得掃描全部，那得多慢啊。

既然這么慢，可以?xún)?yōu)化嗎？當(dāng)然可以，如果你有相關(guān)的經(jīng)驗(yàn)，第一個(gè)想到的就是建索引。

CREATE INDEX age_index ON imooc_user(age);

索引建立完畢后，我們?cè)俅畏治觯?/p>

EXPLAIN SELECT * FROM imooc_user WHERE age=22;

+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+
| id | select_type | table      | type | possible_keys | key       | ref   | rows | filtered | Extra  |
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+
| 1  | SIMPLE      | imooc_user | ref  | age_index     | age_index | const | 1    | 100.0    | <null> |
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+

type從ALL變成了ref，rows也僅僅只有1行；ref也是一種速度很快的類(lèi)型，即查詢(xún)使用到了常數(shù)匹配索引，在結(jié)果中key字段也指明了，該次查詢(xún)有使用到我們新建的索引age_index。

explain 的內(nèi)容很多，而且不同的數(shù)據(jù)庫(kù)的實(shí)現(xiàn)也不同，如果你需要使用它，請(qǐng)按照你使用的數(shù)據(jù)庫(kù)查閱該數(shù)據(jù)庫(kù)權(quán)威的文檔來(lái)學(xué)習(xí)。

3. 實(shí)踐

接下來(lái)，我們以實(shí)踐的角度來(lái)看一個(gè)面試題——一條SQL語(yǔ)句執(zhí)行的很慢，導(dǎo)致慢的原因有哪些了？。

首先，考慮到數(shù)據(jù)庫(kù)可能會(huì)有波動(dòng)，我們分類(lèi)來(lái)談?wù)撨@個(gè)問(wèn)題。

3.1 偶爾很慢，平時(shí)都 OK

一條語(yǔ)句在檢測(cè)的情況下，大部分時(shí)間都比較快，只是偶爾會(huì)突然很慢，那么造成它慢的原因有很多種，我們挑幾個(gè)常見(jiàn)的：

• 數(shù)據(jù)庫(kù)在刷新數(shù)據(jù)，寫(xiě)磁盤(pán)：數(shù)據(jù)庫(kù)是以頁(yè)的形式來(lái)讀、寫(xiě)數(shù)據(jù)的，突然有時(shí)候頁(yè)需要更新或者刪除了，數(shù)據(jù)庫(kù)就必須執(zhí)行它，于是查詢(xún)就慢了下來(lái)。
• 數(shù)據(jù)庫(kù)在同步、備份：有時(shí)候數(shù)據(jù)庫(kù)會(huì)找個(gè)特定的時(shí)間備份那么一次，剛好被你給撞到了，當(dāng)然這個(gè)概率很低。
• 沒(méi)有鎖，我要等待別人釋放鎖：查詢(xún)的數(shù)據(jù)被別人鎖住了，我需要等待，自然就慢了。

3.2 一直很慢

如果出現(xiàn)某條語(yǔ)句一直都很慢的情況，那么大概率是語(yǔ)句本身或者數(shù)據(jù)表索引的問(wèn)題了。

• 沒(méi)有索引：如上面age字段沒(méi)有索引，全表掃描，當(dāng)然很慢。
• 沒(méi)走索引：有索引，可是因?yàn)?strong>使用函數(shù)或者模糊搜索導(dǎo)致查詢(xún)沒(méi)有走索引；有索引，可是SQL語(yǔ)句不明確，導(dǎo)致數(shù)據(jù)庫(kù)走錯(cuò)索引，應(yīng)該優(yōu)化SQL語(yǔ)句，或者USING INDEX強(qiáng)制使用索引。
• 語(yǔ)句本身：使用了POW，CONTACT等函數(shù)使數(shù)據(jù)庫(kù)沒(méi)法走索引。

正如小節(jié)開(kāi)頭所說(shuō)，SQL 優(yōu)化是一個(gè)很大的專(zhuān)題，一本極厚的書(shū)可能也無(wú)法全部囊括。不過(guò)這也不代表你無(wú)法學(xué)習(xí)，先熟練掌握幾個(gè)好用的工具，如本小節(jié)提到的兩個(gè)工具，然后慢慢的學(xué)習(xí)和實(shí)踐，相信你能在優(yōu)化的路上走的很遠(yuǎn)。

4. 小結(jié)

• 一般情況下，SQL 優(yōu)化的落腳點(diǎn)其實(shí)就是使用索引，索引能夠大幅加快查詢(xún)速度，提高性能。

• 對(duì)于 SQL 語(yǔ)句本身的優(yōu)化，除了soar以外，你也可以查閱相關(guān)的資料獲取經(jīng)驗(yàn)。