数据准备
表
CREATE TABLE `single_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`key1` VARCHAR(45) not null default ''COMMENT 'key1',
`key2` int(11) not null default 0 COMMENT 'key2',
`key3` VARCHAR(100) not null default '' COMMENT 'key3',
`key_part1` VARCHAR(100) not null COMMENT 'key_part1',
`key_part2` VARCHAR(100) not null COMMENT 'key_part2',
`key_part3` VARCHAR(100) not null COMMENT 'key_part3',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_key_part`(`key_part1`, `key_part2`, `key_part3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COMMENT='test_table';
sql:
explain select key_part1,key_part2,key_part3 from single_table where key_part2 = 'key2';
为什么它可以
不用最左原则也能命中索引
执行计划:
可以看到命中了idx_key_part索引
但是type:index级别(索引物理文件全扫描)
查询结果字段和条件都在idx_key_part索引中
这种情况虽然命中了索引但是物理文件全扫描,如果数据量很大 速度也是很慢的
阿里开发手册-索引规约
- 【推荐】SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。 说明: 1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。 2) ref 指的是使用普通的索引(normal index)。 3) range 对索引进行范围检索。 反例:explain 表的结果,type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range 还低,与全表扫描是小巫见大巫。
索引失效了
表
CREATE TABLE `single_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`key1` VARCHAR(45) not null default ''COMMENT 'key1',
`key2` int(11) not null default 0 COMMENT 'key2',
`key3` VARCHAR(100) not null default '' COMMENT 'key3',
`key_part1` VARCHAR(100) not null COMMENT 'key_part1',
`key_part2` VARCHAR(100) not null COMMENT 'key_part2',
`key_part3` VARCHAR(100) not null COMMENT 'key_part3',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_key_part`(`key_part1`(10), `key_part2`, `key_part3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COMMENT='test_table';
sql
explain select key_part1,key_part2,key_part3 from single_table where key_part2 = 'key2';
为什么它不行
执行计划
type:ALL
全表扫描了,因为key_part1
(10) 取了前10个字符创建索引
idx_key_part 这课索引树上没有key_part1字段的全部数据