MySQL索引类型(type)分析
type索引类型
system > const > eq_ref > ref > range > index > all
优化级别从左往右递减,没有索引的⼀般为’all’。 推荐优化目标:至少要达到 range 级别, 要求是 ref 级别, 如果可以是 const 最好 ;index比all更优,但是并不明显,性能都很差。
Type级别说明
1、system级别
只有一条数据的系统表;
或衍生表只能有一条数据的主查询;
这是const类型的特列,实际开发中难以达到(基本不会出现)。
2、const级别
当使用 主键 或 唯一索引 进行等值查询时
实例
表结构
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
Primary key
explain select * from `user` u where id=1
unique索引
explain select * from `user` u where name='zhangsan'
3、eq_ref级别
当联表查询的关联字段为唯一索引或者主键时
实例
表结构
CREATE TABLE `user_job` (
`id` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE,
KEY `user_job_userId_IDX` (`userId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
数据
输出结果
explain select uj.id ,u.name ,uj.job from user_job uj left join `user` u on uj.userId =u.id
4、ref级别
当联表查询的关联字段或者单表查询的筛选字段为普通索引时
实例
表结构
CREATE TABLE `user_job` (
`id` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE,
KEY `user_job_userId_IDX` (`userId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
输出结果
explain select * from user_job uj where name ='xx'
5、range级别
使用主键或者索引,进行范围查询时
常用范围查询 (between , in , > , < , >=),in有时会失效为ALL
实例
表结构
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_name_IDX` (`name`) USING BTREE,
KEY `user_age_IDX` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
输出结果
explain select * from `user` u where id>1
explain select * from `user` u where name in('zhangsan','lisi')
explain select * from `user` u where age BETWEEN 10 and 20
6、index级别
遍历索引树,把索引的数据全部查出来
explain select id,name from `user` u
explain select age from `user` u
7、ALL级别
当不使用任何索引和主键时,进行全表扫描
explain select * from `user` u
评论 (0)