mysql索引篇explain命令详解-木庄网络博客

本文整理自网络，侵删。

前言

mysql中的explain命令可以用来查看sql语句是否使用了索引，用了什么索引，有没有做全表扫描。可以帮助我们优化查询语句。
explain出来的信息有10列，文章主要介绍type、key、Extra这几个字段。

演示中涉及到的表结构如下：

 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中测试库的表，需要的同学可以自行去下载。
官方文档：https://dev.mysql.com/doc/employee/en/employees-installation.html；

key

sql语句实际执行时使用的索引列，有时候mysql可能会选择优化效果不是最好的索引，这时，我们可以在select语句中使用force index(INDEXNAME)来强制mysql使用指定索引或使用ignore index(INDEXNAME)强制mysql忽略指定索引

type

访问类型，表示数据库引擎查找表的方式，常见的type类型有：all,index,range,ref,eq_ref，const。

all：

全表扫描，表示sql语句会把表中所有表数据全部读取读取扫描一遍。效率最低，我们应尽量避免。

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语句将会把整颗二级索引树全部读取扫描一遍，因为二级索引树的数据量比全表数据量小，所以效率比all高一些。一般查询语句中查询字段为索引字段，且无where子句时，type会为index。如下，mysql确定使用dept_no这个索引，然后扫描整个dept_no索引树得到结果。

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：

部分索引扫描，当查询为区间查询，且查询字段为索引字段时，这时会根据where条件对索引进行部分扫描。

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：

阅读剩余部分