当你在MySQL表上加了索引,但执行SQL查询的时候却没有用到索引,你会很疑惑,这是为什么呢?这里给你讲一讲索引失效的几种原因,帮你避免踩坑。
- 数据准备
首先,我们来看一个例子,假设我们有一个用户表,有id、name和age三个字段,我们在name字段上加了一个索引。
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='用户表';
- 2. Explain详解
想要查看一条SQL是否用到索引?用到了哪种类型的索引?可以使用explain关键字,查看SQL执行计划。例如:
EXPLAIN SELECT * FROM user WHERE id=1;
执行结果会显示一个type字段,表示使用了哪种类型的索引,常见的type类型有以下几种:
1)system:表只有一行记录,这是const类型的特例;
2)const:使用主键或唯一索引查询时,只有一行匹配,效率最高;
3)eq_ref:使用主键或唯一索引查询时,只有一行匹配,但需要多表联查;
4)ref:非唯一索引查询时,使用了ref或者in操作;
5)range:使用范围索引查询时,如between、in等;
6)index:全表扫描,但只需要读取索引树;
7)all:全表扫描,需要读取全部数据。
3. 失效原因
现在,让我们来看一下为什么索引会失效。
(1)数据类型隐式转换
首先,我们需要注意的是数据类型转换。假设我们要根据name查询用户,但查询条件是数值类型,这时就会产生数据类型转换,虽然不会报错,但无法用到索引。
例如:
EXPLAIN SELECT * FROM user WHERE name = '乔乔';
EXPLAIN SELECT * FROM user WHERE name = 18;
第一条SQL可以用到索引,但第二条无法用到索引,因为查询条件不是字符串类型。
(2)模糊查询like以%开头
第二个问题是模糊查询,如果我们在name字段上使用了like操作,并且以%开头,也是无法用到索引的。
EXPLAIN SELECT * FROM user WHERE name LIKE '张%';
EXPLAIN SELECT * FROM user WHERE name LIKE '%张';
这两条SQL都无法用到索引,因为like操作是需要全表扫描的。
(3)or前后没有同时使用索引
第三个问题是or操作,如果我们在查询条件中使用了or操作,并且or前后没有同时使用索引,就会导致全表扫描。
例如:
EXPLAIN SELECT * FROM user WHERE name = '乔乔' OR age = 18;
虽然name字段上加了索引,但是age字段没有索引,使用or的时候会全表扫描。
(4)联合索引,没有使用第一列索引