谁说MySQL索引没坑，我差点把数据都“踩“没了

当你在MySQL表上加了索引，但执行SQL查询的时候却没有用到索引，你会很疑惑，这是为什么呢？这里给你讲一讲索引失效的几种原因，帮你避免踩坑。

 

1.  数据准备

首先，我们来看一个例子，假设我们有一个用户表，有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）联合索引，没有使用第一列索引