分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)
這篇文章將給大家介紹如何使用 explain 來分析一條 sql 。
網(wǎng)上其實已經(jīng)有非常多的文章都很詳細的介紹了 explain 的使用，這篇文章將實例和原理結(jié)合起來，盡量讓你有更好的理解，相信我，認真看完你應(yīng)該會有特別的收獲。
explain 翻譯過來就是解釋的意思， 在 mysql 里被稱作執(zhí)行計劃，即可以通過該命令看出 mysql 在經(jīng)過優(yōu)化器分析后決定要如何執(zhí)行該條 sql 。
說到優(yōu)化器，再多說一句，mysql 內(nèi)置了一個強大的優(yōu)化器，優(yōu)化器的主要任務(wù)就是把你寫的 sql 再給優(yōu)化一下，盡可能以更低成本去執(zhí)行，比如掃描更少的行數(shù)，避免排序等。執(zhí)行一條sql語句都經(jīng)歷了什么？ 我在前面的文章中有介紹過優(yōu)化器相關(guān)的。
你可能會問，一般在什么時候會要用 explain 呢，大多數(shù)情況下都是從 mysql 的慢查詢?nèi)罩局芯境鰜硪恍┎樵冃时容^慢的 sql 來使用 explain 分析，也有的是就是在對 mysql 進行優(yōu)化的時候，比如添加索引，通過 explain 來分析添加的索引能否被命中，還有的就是在業(yè)務(wù)開發(fā)的時候，在滿足需求的情況下，你可能需要通過 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 訪問數(shù)據(jù)的方式，常見的有全表掃描（all）、遍歷索引（index）、區(qū)間查詢（range）、常量或等值查詢（ref、eq_ref）、主鍵等值查詢（const）、當(dāng)表中只有一條記錄時（system）。下面是效率從最好到最差的一個排序。
system > const > eq_ref > ref > range > index > all
key 表示查詢過程實際會用到的索引名稱。
rows 表示查詢過程中可能需要掃描的行數(shù)，這個數(shù)據(jù)不一定準(zhǔn)確，是mysql 抽樣統(tǒng)計的一個數(shù)據(jù)。
Extra 表示一些額外的信息，通常會顯示是否使用了索引，是否需要排序，是否會用到臨時表等。
好了，接下來正式開始實例分析。
還是沿用前面文章中創(chuàng)建的存儲引擎創(chuàng)建一個測試表，我們這里插入 10 w 條測試數(shù)據(jù)，表結(jié)構(gòu)如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
然后看下面這條查詢語句，注意這個表目前只有一個主鍵索引，還沒有創(chuàng)建普通索引。
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 條數(shù)據(jù)，所以這個字段只是 mysql 的一個預(yù)估，并不一定準(zhǔn)確。這種全表掃描的效率非常低，是需要重點被優(yōu)化的。
接下來我們分別給字段 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 條滿足條件的數(shù)據(jù)，也就是說這 9w 條數(shù)據(jù)都需要回表操作，全表掃描都才 10w 條數(shù)據(jù)，所以在 mysql 的優(yōu)化器看來還不如直接全表掃描得了，至少還免去了回表過程了。
當(dāng)然也不是說只要有回表操作就不會命中索引，用不用索引關(guān)鍵還在于 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 條數(shù)據(jù)，mysql 認為 1000 條數(shù)據(jù)就算回表也要比全表掃描的代價低，所以說 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 * ，只查詢業(yè)務(wù)需要的字段即可，這樣可以盡可能避免回表。
再來看一個需要排序的。
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，意味著需要排序，這種是需要重點優(yōu)化的的，也就是說查到數(shù)據(jù)后，還需要 mysql 在內(nèi)存中對其進行排序，你要知道索引本身就是有序的，所以一般來講要盡量利用索引的有序性，比如像下面這樣寫。
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)
我們再創(chuàng)建一個復(fù)合索引看看。
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 剛剛在上面也有講到過，在沒有創(chuàng)建復(fù)合索引的時候，是走的全表掃描，現(xiàn)在其實是利用了覆蓋索引，同樣是免去了回表過程，即在 （ab_index） 索引上就能找出要查詢的字段。
這篇文章通過幾個實例介紹了如何使用 explain 分析一條 sql 的執(zhí)行計劃，也提到了一些常見的索引優(yōu)化，事實上還有更多的可能性，你也可以自己去寫一個 sql ，然后使用 explain 分析，看看有哪些是可以被優(yōu)化的。
相關(guān)文章
這篇文章主要為大家詳細介紹了CentOS6.8使用cmake安裝MySQL5.7.18的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
這篇文章主要介紹了MySQL事務(wù)的隔離級別詳情，事務(wù)隔離級別越高，為避免沖突所花費的性能也就越多，即效率低。在“可重復(fù)讀”級別，實際上可以解決部分的虛讀問題，但是不能防止update更新產(chǎn)生的虛讀問題，要禁止虛讀產(chǎn)生，還是需要設(shè)置串行化隔離級別2023-02-02
MySQL中DATE_FORMAT()函數(shù)將Date轉(zhuǎn)為字符串
時間、字符串、時間戳之間的互相轉(zhuǎn)換很常用,下面這篇文章主要給大家介紹了關(guān)于MySQL中DATE_FORMAT()函數(shù)將Date轉(zhuǎn)為字符串的相關(guān)資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2022-09-09
MyEclipse連接Mysql數(shù)據(jù)庫的方法（一）
這篇文章主要介紹了MyEclipse連接Mysql數(shù)據(jù)庫的方法（一）的相關(guān)資料,非常實用，具有參考價值，需要的朋友可以參考下2016-05-05
MySQL服務(wù)器權(quán)限與對象權(quán)限詳解
這篇文章主要介紹了MySQL服務(wù)器權(quán)限與對象權(quán)限,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
MySQL緩存機制是提升數(shù)據(jù)庫查詢性能的關(guān)鍵技術(shù),本文深入介紹了MySQL的查詢緩存、表緩存和InnoDB緩沖池等緩存類型,以及它們的查詢和清除命令,需要的朋友可以參考下2024-09-09
兩天有人問mysql中如何加外鍵，今天抽時間總結(jié)一下。mysql中MyISAM和InnoDB存儲引擎都支持外鍵（foreign key），但是MyISAM只能支持語法，卻不能實際使用。2015-03-03
這篇文章主要介紹了MySQL查詢性能優(yōu)化索引下推，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下，希望對你的學(xué)習(xí)有所幫助2022-08-08
淺談MySQL數(shù)據(jù)庫中日期中包含零值的問題
下面小編就為大家?guī)硪黄獪\談MySQL數(shù)據(jù)庫中日期中包含零值的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
MySQL Server維護了幾個時區(qū),本文主要介紹了MySQL Server時區(qū)支持的使用,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07