快捷導(dǎo)航

mysql索引篇explain命令詳解

更新時(shí)間：2022年08月25日 10:20:21 作者：會(huì)玩code

這篇文章主要介紹了mysql索引篇explain命令詳解，mysql中的explain命令可以用來(lái)查看sql語(yǔ)句是否使用了索引，用了什么索引，有沒(méi)有做全表掃描，更多相關(guān)內(nèi)容需要的小伙伴可以參考一下

前言

mysql中的explain命令可以用來(lái)查看sql語(yǔ)句是否使用了索引，用了什么索引，有沒(méi)有做全表掃描?？梢詭椭覀儍?yōu)化查詢(xún)語(yǔ)句。
explain出來(lái)的信息有10列，文章主要介紹type、key、Extra這幾個(gè)字段。

演示中涉及到的表結(jié)構(gòu)如下：

 CREATE TABLE `dept_desc` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  `desc` varchar(255) NOT NULL,
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB

CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB

上面的表都是mysql中測(cè)試庫(kù)的表，需要的同學(xué)可以自行去下載。
官方文檔：https://dev.mysql.com/doc/employee/en/employees-installation.html；

key

sql語(yǔ)句實(shí)際執(zhí)行時(shí)使用的索引列，有時(shí)候mysql可能會(huì)選擇優(yōu)化效果不是最好的索引，這時(shí)，我們可以在select語(yǔ)句中使用force index(INDEXNAME)來(lái)強(qiáng)制mysql使用指定索引或使用ignore index(INDEXNAME)強(qiáng)制mysql忽略指定索引

type

訪問(wèn)類(lèi)型，表示數(shù)據(jù)庫(kù)引擎查找表的方式，常見(jiàn)的type類(lèi)型有：all,index,range,ref,eq_ref，const。

all：

全表掃描，表示sql語(yǔ)句會(huì)把表中所有表數(shù)據(jù)全部讀取讀取掃描一遍。效率最低，我們應(yīng)盡量避免。

mysql> explain select * from dept_emp;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | dept_emp | ALL  | NULL          | NULL | NULL    | NULL | 331570 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+

index：

全索引掃描,表示sql語(yǔ)句將會(huì)把整顆二級(jí)索引樹(shù)全部讀取掃描一遍，因?yàn)槎?jí)索引樹(shù)的數(shù)據(jù)量比全表數(shù)據(jù)量小，所以效率比all高一些。一般查詢(xún)語(yǔ)句中查詢(xún)字段為索引字段，且無(wú)where子句時(shí)，type會(huì)為index。如下，mysql確定使用dept_no這個(gè)索引，然后掃描整個(gè)dept_no索引樹(shù)得到結(jié)果。

mysql> explain select dept_no  from dept_emp;
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | dept_emp | index | NULL          | dept_no | 4       | NULL | 331570 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+

range：

部分索引掃描，當(dāng)查詢(xún)?yōu)閰^(qū)間查詢(xún)，且查詢(xún)字段為索引字段時(shí)，這時(shí)會(huì)根據(jù)where條件對(duì)索引進(jìn)行部分掃描。

mysql> explain select * from dept_emp  where emp_no > '7';
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | dept_emp | range | PRIMARY       | PRIMARY | 4       | NULL | 165785 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+

ref：

出現(xiàn)于where操作符為‘=’，且where字段為非唯一索引的單表查詢(xún)或聯(lián)表查詢(xún)。

// 單表
mysql> explain select * from dept_emp where dept_no = 'd005';
+----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows   | Extra                 |
+----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+
|  1 | SIMPLE      | dept_emp | ref  | dept_no       | dept_no | 4       | const | 145708 | Using index condition |
+----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+

// 聯(lián)表
mysql> explain select * from dept_emp,departments where dept_emp.dept_no = departments.dept_no;
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+
| id | select_type | table       | type  | possible_keys | key       | key_len | ref                           | rows | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | departments | index | PRIMARY       | dept_name | 42      | NULL                          |    9 | Using index |
|  1 | SIMPLE      | dept_emp    | ref   | dept_no       | dept_no   | 4       | employees.departments.dept_no |    1 | NULL        |
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+

eq_ref：

出現(xiàn)于where操作符為‘=’，且where字段為唯一索引的聯(lián)表查詢(xún)。

mysql> explain select * from departments,dept_desc where departments.dept_name=dept_desc.dept_name;
+----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+
| id | select_type | table       | type   | possible_keys | key       | key_len | ref                           | rows | Extra       |
+----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | dept_desc   | ALL    | NULL          | NULL      | NULL    | NULL                          |    1 | NULL        |
|  1 | SIMPLE      | departments | eq_ref | dept_name     | dept_name | 42      | employees.dept_desc.dept_name |    1 | Using index |
+----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+

const：

出現(xiàn)于where操作符為‘=’，且where字段為唯一索引的單表查詢(xún),此時(shí)最多只會(huì)匹配到一行。

mysql> explain select * from departments where dept_no = 'd005';
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | departments | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

綜上，單從type字段考慮效率，const > eq_ref > ref > range > index > all.

注意：我們不能僅僅根據(jù)type去判斷兩條sql的執(zhí)行速度。例如type為range的查詢(xún)不一定比type為index的全表查詢(xún)速度要快，還要看具體的sql。因?yàn)閠ype為index時(shí)，查詢(xún)是不需要回表操作的，而type為range時(shí)，有可能需要回表操作。如sqlA("select dept_no from dept_emp;")和sqlB("select from_date from dept_emp where dept_no > 'd005';"),這個(gè)時(shí)候sqlB根據(jù)where條件掃描索引樹(shù)后，需要回表查詢(xún)相應(yīng)的行數(shù)據(jù)，以獲取from_date的值，而sqlA雖然掃描了整顆索引樹(shù)，但并不需要回表，所以速度可能會(huì)比sqlB更快。

回表操作、索引相關(guān)可以閱讀mysql索引(覆蓋索引,聯(lián)合索引,索引下推)這篇文章

Extra

extra列會(huì)包含一些十分重要的信息，我們可以根據(jù)這些信息進(jìn)行sql優(yōu)化

using index: sql語(yǔ)句沒(méi)有where查詢(xún)條件，使用覆蓋索引，不需要回表查詢(xún)即可拿到結(jié)果
using where: 沒(méi)有使用索引/使用了索引但需要回表查詢(xún)且沒(méi)有使用到下推索引
using index && useing where: sql語(yǔ)句有where查詢(xún)條件，且使用覆蓋索引，不需要回表查詢(xún)即可拿到結(jié)果。
Using index condition:使用索引查詢(xún)，sql語(yǔ)句的where子句查詢(xún)條件字段均為同一索引字段，且開(kāi)啟索引下推功能，需要回表查詢(xún)即可拿到結(jié)果。
Using index condition && using where:使用索引查詢(xún)，sql語(yǔ)句的where子句查詢(xún)條件字段存在非同一索引字段，且開(kāi)啟索引下推功能，需要回表查詢(xún)即可拿到結(jié)果。
using filesort: 當(dāng)語(yǔ)句中存在order by時(shí)，且orderby字段不是索引，這個(gè)時(shí)候mysql無(wú)法利用索引進(jìn)行排序，只能用排序算法重新進(jìn)行排序，會(huì)額外消耗資源。
Using temporary：建立了臨時(shí)表來(lái)保存中間結(jié)果，查詢(xún)完成之后又要把臨時(shí)表刪除。會(huì)很影響性能，需盡快優(yōu)化。

下推索引、覆蓋索引相關(guān)介紹可閱讀mysql索引(覆蓋索引,聯(lián)合索引,索引下推)這篇文章

有時(shí)在extra字段中會(huì)出現(xiàn)"Impossible WHERE noticed after reading const tables"這種描述。翻看網(wǎng)上資料后，個(gè)人發(fā)現(xiàn)這是mysql一種很怪的處理方式。

當(dāng)sql語(yǔ)句滿足：

1、根據(jù)主鍵查詢(xún)或者唯一性索引查詢(xún)；
2、where操作符為"="時(shí)。

在sql語(yǔ)句優(yōu)化階段，mysql會(huì)先根據(jù)查詢(xún)條件找到相關(guān)記錄，這樣，如果這條數(shù)據(jù)不存在，實(shí)際上就進(jìn)行了一次全掃描，然后得出一個(gè)結(jié)論，該數(shù)據(jù)不在表中。這樣對(duì)于并發(fā)較高的數(shù)據(jù)庫(kù)，會(huì)加大負(fù)載。所以，如果數(shù)據(jù)不用唯一的話，普通的索引比唯一索引更好用。（文章鏈接：MySQL慢查詢(xún)現(xiàn)象解決案例）

到此這篇關(guān)于mysql索引篇explain命令詳解的文章就介紹到這了,更多相關(guān)mysql explain命令內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: