Mysql中七种常见索引访问类型

---

前言

在少量数据的情况下,数据库有无索引，select语句是否走索引并不影响查询效率；
但是在现实世界中，数据是十分庞大的，这时候数据库有索引和select语句走索引对查询效率可能会有几十倍，几百倍甚至更多倍的提高！因此我们在写select语句时一定要通过explain的type来分析索引的访问类型，下面介绍七种种索引的访问类型。

建表语句

• employee表

CREATE TABLE `employee` (
  `e_id` varchar(11) NOT NULL COMMENT '员工号',
  `e_name` varchar(11) NOT NULL COMMENT '员工名',
  `d_id` varchar(11) NOT NULL COMMENT '所属部门号',
  `salary` int(11) NOT NULL COMMENT '工资',
  PRIMARY KEY (`e_id`),
  KEY `d_id` (`d_id`),
  KEY `salary` (`salary`),
  CONSTRAINT `d_id` FOREIGN KEY (`d_id`) REFERENCES `department` (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employee` */

insert  into `employee`(`e_id`,`e_name`,`d_id`,`salary`) values 
('E0001','张三','D01',12000),('E0002','李四','D03',20000),
('E0003','王五','D03',30000),('E0004','赵六','D02',8000),
('E0005','小明','D01',9000),('E0006','小红','D02',10000);

• deaprtment表

CREATE TABLE `department` (
  `d_id` varchar(11) NOT NULL COMMENT '部门号',
  `d_name` varchar(11) NOT NULL COMMENT '部门名',
  `dm_id` varchar(11) DEFAULT NULL COMMENT '部门经理',
  PRIMARY KEY (`d_id`),
  UNIQUE KEY `dm_id` (`dm_id`),
  CONSTRAINT `dm_id` FOREIGN KEY (`dm_id`) REFERENCES `employee` (`e_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `department` */

insert  into `department`(`d_id`,`d_name`,`dm_id`) values 
('D01','人事部','E0001'),('D02','后勤部','E0006'),
('D03','研发部','E0003');

1.ALL

• 定义:扫描全表数据
• sql

#本例没有用到任何索引,因此type为ALL
EXPLAIN SELECT * FROM employee;

• 执行结果
  

2.index

• 定义:按照索引顺序扫描全表数据
• sql

#本例查询的只是e_id,该字段为主键,有索引，因此type为index
#本例中e_id后面加salary,type也是index,因为salary也有索引
EXPLAIN SELECT e_id FROM employee ;

• 执行结果
  

3.range

• 定义:在指定索引的一定范围内查找数据(如使用>,between,in等)
• sql

#本例查询中用到了索引salary并且指定了>=10000的范围，因此type为range
EXPLAIN SELECT e.`e_id`,e.`salary` FROM employee e 
WHERE e.`salary`>=10000; 

• 执行结果
  

4.ref

• 定义:使用非唯一性索引查找数据，返回匹配某个单独值的所有行。
• sql

#使用了非唯一性索引e_did,只匹配"D01"的数据,因此type为ref
EXPLAIN SELECT e.`e_id` FROM employee e WHERE e.`d_id`='D01'; 

• 执行结果
  

5.eq_ref

• 定义:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。
• sql

#本例中采用唯一性索引e_id进行扫描,对于每一个e_id
#department表中最多只有一条记录与之匹配,因此type为eq_ref
EXPLAIN SELECT e.`e_name` FROM employee e,department d 
WHERE e.`e_id`=d.`dm_id`;

• 执行结果
  

6.const

• 定义:唯一性索引扫描,仅仅能查出一条的SQL语句
• sql

#本例中使用主键索引e_id进行等值查询,只能查到一个数据,因此type为const
EXPLAIN SELECT e.`salary` FROM employee e WHERE e.`e_id`='E0001';

• 执行结果
  

7.system

• 定义:const特例,出现于只有一条数据的系统表或衍生表只能有一条数据的主查询
• sql

#本例中先进行子查询,只能查出一条数据,此时外层select的type为system
EXPLAIN SELECT * FROM (SELECT * FROM employee e WHERE e.`e_id`='E0001')e1;

• 执行结果
  

访问效率

这7种访问类型的效率为：ALL<index<range<ref<eq_ref<const<system