實戰5：如何優化你的 SQL 查詢

1. 前言

在前面的小節和實戰中，我們一直在學習和討論如何寫 SQL，如何用 SQL 完成一個業務功能點。本小節，我們將以優化的角度來探討一下如何優化 SQL，讓 SQL 更加高效的運行。

SQL 優化是一個很大的專題，本節會介紹幾種常見的 SQL 優化手段和一些好用的優化工具。

2. 工具

SQL 優化并不簡單，因此我們可以利用一些工具來幫助我們。

2.1 soar

soar是小米開源的一款 SQL 優化和改寫的工具，它使用簡單而且特性十分豐富，你可以點擊此鏈接來安裝 soar，安裝成功后，我們來一起使用一下 soar。

2.1.1 soar 實例

舉個簡單的例子：

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

soar 的使用十分簡單，通過query參數指定一條需要分析的SQL語句即可，調用成功后，soar會自動在控制臺打印出分析結果，如下：

# Query: 93A5517F0971C47A

★ ★ ★ ★ ☆ 95分

?```sql

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

## 不建議使用 SELECT * 類型查詢

* **Item:**  COL.001

* **Severity:**  L1

* **Content:**  當表結構變更時，使用 \* 通配符選擇所有列將導致查詢的含義和行為會發生更改，可能導致查詢返回更多的數據。

soar 分析的結果默認以markdown的格式展現，且分析結果十分豐富，不僅給出了格式化后易讀的 SQL 和建議，還打了分。

其中Item是規則代碼，每個規則都有相應的代號，Severity是等級，等級越高代表越危險，越需要優化，L1是較低的等級，Content指明了優化原因。

2.1.2 soar 優化 SQL

上面的語句中，建議不使用*，因為字段變更將導致數據發生變化，按照 soar 的提示我們優化一下 SQL：

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

我們不僅優化了*，且將分析結果保存到了本地的profile.md文件，內容如下：

# Query: 54BE4DEFF01C4432

★ ★ ★ ★ ★ 100分

?```sql

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

## OK

優化后，直接獲得了 100 分（滿分）。

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

2.2 EXPLAIN

explain是數據庫自帶的 SQL 分析工具，簡單、實用且強大。下面我們以 MySQL 的explain工具為例來介紹一下它的使用。

請先執行一下語句方便進行測試：

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的使用很簡單，在它的后面接上需要分析的 SQL 語句即可，如下：

EXPLAIN SELECT * FROM imooc_user WHERE id=1;

執行成功后，得到如下結果：

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

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

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

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

因此從分析結果可以看出，這條語句性能極好，除非數據庫波動，否則完全不用擔心查詢速度問題。

2.2.2 explain 優化 SQL

那么什么樣的語句查詢效率比較低了，我們看一下這個語句：

EXPLAIN SELECT * FROM imooc_user WHERE age=22;

分析結果如下：

+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+
| 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 |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+

我們仍然截取了部分信息，我們將目光聚焦在type和rows上，這里的type不再是const而是ALL，ALL表示全表掃描，是最慢的一個級別，rows為5，表示這次查詢將會掃描5條記錄，而我們總共才5條記錄。

這個查詢的性能是極為糟糕的，試想一下，如果該表的數據是幾萬行乃至幾十萬行，一次查詢得掃描全部，那得多慢啊。

既然這么慢，可以優化嗎？當然可以，如果你有相關的經驗，第一個想到的就是建索引。

CREATE INDEX age_index ON imooc_user(age);

索引建立完畢后，我們再次分析：

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也是一種速度很快的類型，即查詢使用到了常數匹配索引，在結果中key字段也指明了，該次查詢有使用到我們新建的索引age_index。

explain 的內容很多，而且不同的數據庫的實現也不同，如果你需要使用它，請按照你使用的數據庫查閱該數據庫權威的文檔來學習。

3. 實踐

接下來，我們以實踐的角度來看一個面試題——一條SQL語句執行的很慢，導致慢的原因有哪些了？。

首先，考慮到數據庫可能會有波動，我們分類來談論這個問題。

3.1 偶爾很慢，平時都 OK

一條語句在檢測的情況下，大部分時間都比較快，只是偶爾會突然很慢，那么造成它慢的原因有很多種，我們挑幾個常見的：

• 數據庫在刷新數據，寫磁盤：數據庫是以頁的形式來讀、寫數據的，突然有時候頁需要更新或者刪除了，數據庫就必須執行它，于是查詢就慢了下來。
• 數據庫在同步、備份：有時候數據庫會找個特定的時間備份那么一次，剛好被你給撞到了，當然這個概率很低。
• 沒有鎖，我要等待別人釋放鎖：查詢的數據被別人鎖住了，我需要等待，自然就慢了。

3.2 一直很慢

如果出現某條語句一直都很慢的情況，那么大概率是語句本身或者數據表索引的問題了。

• 沒有索引：如上面age字段沒有索引，全表掃描，當然很慢。
• 沒走索引：有索引，可是因為使用函數或者模糊搜索導致查詢沒有走索引；有索引，可是SQL語句不明確，導致數據庫走錯索引，應該優化SQL語句，或者USING INDEX強制使用索引。
• 語句本身：使用了POW，CONTACT等函數使數據庫沒法走索引。

正如小節開頭所說，SQL 優化是一個很大的專題，一本極厚的書可能也無法全部囊括。不過這也不代表你無法學習，先熟練掌握幾個好用的工具，如本小節提到的兩個工具，然后慢慢的學習和實踐，相信你能在優化的路上走的很遠。

4. 小結

• 一般情況下，SQL 優化的落腳點其實就是使用索引，索引能夠大幅加快查詢速度，提高性能。

• 對于 SQL 語句本身的優化，除了soar以外，你也可以查閱相關的資料獲取經驗。