如何分析一條sql的效能

這篇文章將為大家介紹如何使用 explain 來分析一則 sql 。

網路上其實已經有非常多的文章都很詳細的介紹了explain 的使用，這篇文章將實例和原理結合起來，盡量讓你有更好的理解，相信我，認真看完你應該會有特別的收穫。

explain 翻譯過來就是解釋的意思， 在 mysql 裡被稱作執行計劃，即可以透過該命令看出 mysql 在經過優化器分析後決定要如何執行該條 sql 。

說到優化器，再多說一句，mysql 內建了一個強大的優化器，優化器的主要任務就是把你寫的sql 再給優化一下，盡可能以更低成本去執行，例如掃描更少的行數，避免排序等。執行一則sql語句都經歷了什麼？我在前面的文章有介紹過優化器相關的。

你可能會問，通常在什麼時候會要用explain 呢，大多數情況下都是從mysql 的慢查詢日誌中揪出來一些查詢效率比較慢的sql 來使用explain 分析，也有的是就是在對mysql 進行最佳化的時候，例如添加索引，透過explain 來分析添加的索引能否被命中，還有的就是在業務開發的時候，在滿足需求的情況下，你可能需要透過explain 來選擇一個更高效的sql。

那麼 explain 該怎麼用呢，很簡單，直接在 sql 前面加上 explain 就行了，如下圖所示。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

可以看到，explain 會返回約10 個字段，不同版本返回的字段有些許差異，每個字段都代表著具體的意義，這篇文章我不打算把每個字段都詳細的介紹一遍，東西比較多，怕你也不容易記住，不如先把幾個重要的字段好好理解了。

其中 type、key、rows、Extra 這幾個欄位我認為是比較重要的，我們接下來透過具體的實例來幫你更好的理解這幾個欄位的意義。

首先有必要簡單介紹下這幾個欄位的字面意思。

type 表示mysql 存取資料的方式，常見的有全表掃描（all）、遍歷索引（index）、區間查詢（range）、常數或等值查詢（ref、eq_ref）、主鍵等值查詢（const）、當表中只有一筆記錄時（system）。以下是效率從最好到最差的一個排序。

system > const > eq_ref > ref > range > index > all

key 表示查詢程序實際上會用到的索引名稱。

rows 表示查詢過程中可能需要掃描的行數，這個資料不一定準確，是mysql 抽樣統計的一個資料。

Extra 表示一些額外的信息，通常會顯示是否使用了索引，是否需要排序，是否會用到臨時表等。

好了，接下來就正式開始實例分析。

還是沿用前面文章中創建的存儲引擎創建一個測試表，我們在這裡插入10 w 條測試數據，表結構如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

然後看下面這條查詢語句，注意這個表目前只有一個主鍵索引，還沒有建立普通索引。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

其中type 值為ALL，表示全表掃描了，大家注意看到rows 這個字段顯示有100332 條，實際上我們一共才10w 條數據，所以這個字段只是mysql 的一個預估，並不一定準確。這種全表掃描的效率非常低，是需要重點被最佳化的。

接下來我們分別為欄位 a 和 b 新增普通索引，然後再看下方新增索引後的幾個 sql 。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

上面這條sql 看起來是不是有點懷疑呢，type 竟然顯示剛剛不是給字段a 添加索引了麼，而且possible_keys 也顯示了有a_index 可用，但是key 顯示null，表示mysql 實際上並不會使用a 索引，這是為啥？

這裡是因為select * 的話還需要回到主鍵索引上查找b 字段，這個過程叫回表，這條語句會篩選出9w 條滿足條件的數據，也就是說這9w 條數據都需要回表操作，全表掃描都才10w 條數據，所以在mysql 的優化器看來還不如直接全表掃描得了，至少還免去了回表過程了。

當然也不是說只要有回表運算就不會命中索引，用不用索引關鍵還在於 mysql 認為哪種查詢代價更低，我們把上面的 sql 中 where 條件再稍微改造一下。

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

這回type 值為range 了，key 為a_index ，表示命中了a 索引，是一個不錯的選擇，是因為滿足這條sql 條件的只有1000 條數據，mysql 認為1000 條數據就算回表也要比全表掃描的代價低，所以說mysql 其實是個很聰明的傢伙。

我們還可以看到 Extra 欄位中值為 Using index condition，這個意思是指用到了索引，但是需要回表，再看下面這個語句。

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 select * ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort，意味着需要排序，这种是需要重点优化的的，也就是说查到数据后，还需要 mysql 在内存中对其进行排序，你要知道索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过，在没有创建复合索引的时候，是走的全表扫描，现在其实是利用了覆盖索引，同样是免去了回表过程，即在 （ab_index） 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了，本来准备了更多的例子，但每次都是写了一部分，思路也打乱了，好了，有问题欢迎在下面留言交流，文章对你有帮助，点个赞表示鼓励支持。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！

以上是如何分析一條sql的效能的詳細內容。更多資訊請關注PHP中文網其他相關文章！