本文为大家介绍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()来获取表的数据总行。