赵明中的数据库分享

本文为大家介绍MySQL查看执行计划时，12种type的可能性
性能由低到高介绍
ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref_or_null < fulltext < ref < const < system

ALL

全表扫描，扫描整张表去匹配符合条件的数据。

表结构:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

复现SQL

select * from t1;

select * from t1 where age=11;

select name from t1 where name='aaa';

index

全索引扫描，通过索引树的扫描即可获得匹配到的数据

表结构:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB 

复现SQL

select name from t1;

select name from t1 group by name ;

select name from t1 order by name;

select id from t1;

select name from t1 where name like '%aaa%'

range

索引范围扫描，只需要遍历索引树的部分内容即可获得匹配的数据

表结构:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB 

复现SQL

select name from t1 where name>'aaa';

select name from t1 where name like 'aaa%';

select * from t1 where name like 'aaa%';

select * from t1 where name in ('aaa','bbb');

select * from t1 where id<11;

index_subquery

使用子查询的结果是非唯一索引

表结构:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB 
--------------------------------------------------------
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB 

复现SQL

update t1 set age=11 where name in (select name from t2 where name like 'aaa%')

因为在select查询时，优化器通常会将子查询优化为关联查询，所以type一般不会显示为index_subquery。

unique_subquery

与index_subquery类似，使用子查询的结果是唯一索引或主键索引

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB
-------------------------------------------------------
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

update t1 set age=11 where id in (select id from t2 where name like 'aaa%');

update t1 set name='rrr' where age in (select id from t2);

index_merge

索引合并优化，通常发生在单表中多个索引字段合并扫描

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

select * from t1 where name = 'aaa' or id=1;

select * from t1 where name = 'aaa' or age=1;

select * from t1 where id=3 or age=10

ref_or_null

与ref类似，但是多包含null值

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

select * from t1 where name='aaa' or name is null;

select * from t1 where age=11 or age is null;

fulltext

使用全文搜索索引

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  FULLTEXT KEY `idx_name` (`name`)
) ENGINE=InnoDB

复现SQL

SELECT * FROM t1 WHERE MATCH (name) AGAINST ('search_term');

ref

辅助索引等值查询

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

select * from t1 where name='aaa'；

select name from t1 where name='aaa';

eq_ref

多表连接时，连接字段为主键或唯一键索引

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB
------------------------------------------
CREATE TABLE `t2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

select a.* from t1 as a left join t2 as b on a.id=b.id；

select * from t1 as a left join t2 as b on a.age=b.age;

select * from t1 as a left join t2 as b on a.id=b.age;

const

主键或唯一键索引等值查询

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB

复现SQL

select * from t1 where id=1；

select * from t1 where age=11;

system

该表只有一行（=系统表）

表结构
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=MyISAM
--------------------------------------------------------------------------------
该表中只有一条数据

复现SQL

select * from t1；

彩蛋

只有表引擎为MyISAM且表中只有一条数据type才会为“system”。
因为innodb表需要支持事务和隔离级别，所以只能靠count()来获取表的数据总行。