主頁 > 知識(shí)庫 > 分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)

分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)

熱門標(biāo)簽:騰訊地圖標(biāo)注提升 美國反騷擾電話機(jī)器人 悟空科技電話機(jī)器人 福建外呼系統(tǒng)定制化 銅陵防封電銷卡 電銷卡外呼系統(tǒng)供應(yīng)商 怎么在地圖標(biāo)注位置生成圖片 400電話可以免費(fèi)申請嗎 真人語音電話機(jī)器人

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

網(wǎng)上其實(shí)已經(jīng)有非常多的文章都很詳細(xì)的介紹了 explain 的使用,這篇文章將實(shí)例和原理結(jié)合起來,盡量讓你有更好的理解,相信我,認(rèn)真看完你應(yīng)該會(huì)有特別的收獲。

explain 翻譯過來就是解釋的意思, 在 mysql 里被稱作執(zhí)行計(jì)劃,即可以通過該命令看出 mysql 在經(jīng)過優(yōu)化器分析后決定要如何執(zhí)行該條 sql 。

說到優(yōu)化器,再多說一句,mysql 內(nèi)置了一個(gè)強(qiáng)大的優(yōu)化器,優(yōu)化器的主要任務(wù)就是把你寫的 sql 再給優(yōu)化一下,盡可能以更低成本去執(zhí)行,比如掃描更少的行數(shù),避免排序等。執(zhí)行一條sql語句都經(jīng)歷了什么? 我在前面的文章中有介紹過優(yōu)化器相關(guān)的。

你可能會(huì)問,一般在什么時(shí)候會(huì)要用 explain 呢,大多數(shù)情況下都是從 mysql 的慢查詢?nèi)罩局芯境鰜硪恍┎樵冃时容^慢的 sql 來使用 explain 分析,也有的是就是在對(duì) mysql 進(jìn)行優(yōu)化的時(shí)候,比如添加索引,通過 explain 來分析添加的索引能否被命中,還有的就是在業(yè)務(wù)開發(fā)的時(shí)候,在滿足需求的情況下,你可能需要通過 explain 來選擇一個(gè)更高效的 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 會(huì)返回約 10 個(gè)字段,不同版本返回的字段有些許差異,每個(gè)字段都代表著具體的意義,這篇文章我不打算把每個(gè)字段都詳細(xì)的介紹一遍,東西比較多,怕你也不容易記住,不如先把幾個(gè)重要的字段好好理解了。

其中 type、key、rows、Extra 這幾個(gè)字段我認(rèn)為是比較重要的,我們接下來通過具體的實(shí)例來幫你更好的理解這幾個(gè)字段的含義。

首先有必要簡單介紹下這幾個(gè)字段的字面意思。

type 表示 mysql 訪問數(shù)據(jù)的方式,常見的有全表掃描(all)、遍歷索引(index)、區(qū)間查詢(range)、常量或等值查詢(ref、eq_ref)、主鍵等值查詢(const)、當(dāng)表中只有一條記錄時(shí)(system)。下面是效率從最好到最差的一個(gè)排序。

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

key 表示查詢過程實(shí)際會(huì)用到的索引名稱。

rows 表示查詢過程中可能需要掃描的行數(shù),這個(gè)數(shù)據(jù)不一定準(zhǔn)確,是mysql 抽樣統(tǒng)計(jì)的一個(gè)數(shù)據(jù)。

Extra 表示一些額外的信息,通常會(huì)顯示是否使用了索引,是否需要排序,是否會(huì)用到臨時(shí)表等。

好了,接下來正式開始實(shí)例分析。

還是沿用前面文章中創(chuàng)建的存儲(chǔ)引擎創(chuàng)建一個(gè)測試表,我們這里插入 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;

然后看下面這條查詢語句,注意這個(gè)表目前只有一個(gè)主鍵索引,還沒有創(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 這個(gè)字段顯示有 100332 條,實(shí)際上我們一共才 10w 條數(shù)據(jù),所以這個(gè)字段只是 mysql 的一個(gè)預(yù)估,并不一定準(zhǔn)確。這種全表掃描的效率非常低,是需要重點(diǎ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 看起來是不是有點(diǎn)疑惑呢,type 竟然顯示剛剛不是給字段 a 添加索引了么,而且 possible_keys 也顯示了有 a_index 可用,但是 key 顯示 null,表示 mysql 實(shí)際上并不會(huì)使用 a 索引,這是為啥?

這里是因?yàn)?select * 的話還需要回到主鍵索引上查找 b 字段,這個(gè)過程叫回表,這條語句會(huì)篩選出 9w 條滿足條件的數(shù)據(jù),也就是說這 9w 條數(shù)據(jù)都需要回表操作,全表掃描都才 10w 條數(shù)據(jù),所以在 mysql 的優(yōu)化器看來還不如直接全表掃描得了,至少還免去了回表過程了。

當(dāng)然也不是說只要有回表操作就不會(huì)命中索引,用不用索引關(guān)鍵還在于 mysql 認(rèn)為哪種查詢代價(jià)更低,我們把上面的 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 索引,是一個(gè)不錯(cuò)的選擇,是因?yàn)闈M足這條 sql 條件的只有 1000 條數(shù)據(jù),mysql 認(rèn)為 1000 條數(shù)據(jù)就算回表也要比全表掃描的代價(jià)低,所以說 mysql 其實(shí)是個(gè)很聰明的家伙。

我們還可以看到 Extra 字段中值為 Using index condition,這個(gè)意思是指用到了索引,但是需要回表,再看下面這個(gè)語句。

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)

這個(gè) Extra 中的值為 Using where; Using index ,表示查詢用到了索引,且要查詢的字段在索引中就能拿到,不需要回表,顯然這種效率比上面的要高,所以不要輕易寫 select * ,只查詢業(yè)務(wù)需要的字段即可,這樣可以盡可能避免回表。

再來看一個(gè)需要排序的。

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)

這個(gè) Extra 中返回了一個(gè) Using filesort,意味著需要排序,這種是需要重點(diǎn)優(yōu)化的的,也就是說查到數(shù)據(jù)后,還需要 mysql 在內(nèi)存中對(duì)其進(jìn)行排序,你要知道索引本身就是有序的,所以一般來講要盡量利用索引的有序性,比如像下面這樣寫。

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)建一個(gè)復(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ù)合索引的時(shí)候,是走的全表掃描,現(xiàn)在其實(shí)是利用了覆蓋索引,同樣是免去了回表過程,即在 (ab_index) 索引上就能找出要查詢的字段。

這篇文章通過幾個(gè)實(shí)例介紹了如何使用 explain 分析一條 sql 的執(zhí)行計(jì)劃,也提到了一些常見的索引優(yōu)化,事實(shí)上還有更多的可能性,你也可以自己去寫一個(gè) sql ,然后使用 explain 分析,看看有哪些是可以被優(yōu)化的。

您可能感興趣的文章:
  • 如何提高M(jìn)ySQL Limit查詢性能的方法詳解
  • 通過MySQL慢查詢優(yōu)化MySQL性能的方法講解
  • MySQL通過show processlist命令檢視性能的講解
  • MySQL Limit性能優(yōu)化及分頁數(shù)據(jù)性能優(yōu)化詳解
  • MySQL limit性能分析與優(yōu)化

標(biāo)簽:聊城 云浮 白銀 湖北 臨汾 武威 湖南 烏海

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)》,本文關(guān)鍵詞  分析,一條,sql,的,性能,標(biāo)準(zhǔn),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)》相關(guān)的同類信息!
  • 本頁收集關(guān)于分析一條sql的性能的標(biāo)準(zhǔn)總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章