歡迎光臨
每天分享高質量文章

如何分析一條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(11NOT NULL,
  `a` int(11DEFAULT NULL,
  `b` int(11DEFAULT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB;

 

然後看下麵這條查詢陳述句,註意這個表目前只有一個主鍵索引,還沒有創建普通索引。

 

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)

 

其中 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 分析,看看有哪些是可以被優化的。

赞(0)

分享創造快樂