mysql索引失效

字段类型隐式转换

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test WHERE stu_no = 1234;
-- 执行上一条sql，where条件为int，执行结果的type=ALL，并没有走索引

EXPLAIN SELECT * FROM test WHERE stu_no = '1234';
-- 执行where条件为字符串时候，执行结果的type=ref，key=id_stu_no，发现索引生效

结论:为什么第一条sql没有走索引呢？因为where条件中stu_no为varchar类型，在与数字类型进行匹配时，MySQL会做隐式类型转换，导致索引失效；因此，编写sql时要保证索引字段与匹配数据类型一致。

查询条件中包含or

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test WHERE stu_no = '1234' OR name = '1234'; 
-- 执行where or的时候，执行结果type=ALL，并没有走索引

结论:为什么以上sql没走索引呢，因为Mysql存在优化器，当单独按照stu_no查询时走索引，单独按照age查询时全表扫描，就需要索引+全表+合并三步，优化器考虑性能和成本，直接全表扫描也是合理的。

拓展:

or走索引与否，与优化器的预估有关，即使连接条件都设置了索引，也可能因为回表导致索引失效。
索引优化器的存在，就是找到一个索引扫描行数最少的方案去执行语句,扫描行数根据统计信息来预估的值，这个统计信息就是我们常说的索引的"区分度"。
一个索引上不同的值越多，索引的区分度就越好。我们把一个索引上不同的值的个数，称之为 "索引基数"。也就是说，基数越大，索引的区分度就越好，执行查询的行数就越少。因此索引最好创建在不同值较多的字段上。

like通配符%错误使用

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test WHERE stu_no like '%234';
-- 执行当前sql，匹配符%在左边，执行结果type=ALL，发现并没有走索引

EXPLAIN SELECT * FROM test WHERE stu_no like '%23%';
-- 执行当前sql，匹配符%在左右两边，执行结果type=ALL，发现并没有走索引

EXPLAIN SELECT * FROM test WHERE stu_no like '123%';
-- 执行当前sql，匹配符%在右边，执行结果type=range，发现索引生效

EXPLAIN SELECT stu_no FROM test WHERE stu_no like '%23%';
-- 执行当前sql，匹配符%虽然在左右两边，但是只查询了stu_no列，发现索引生效

结论:like查询以%开头，会导致索引失效。可以有两种方式优化

使用覆盖索引优化，只查询索引列
把%放右边，索引生效

拓展:索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

联合索引最左匹配原则

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_stu_no_age`(`stu_no`,`age`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test WHERE stu_no = '1234' AND age = 18;
-- 执行当前sql，发现where条件为stu_no和name时，执行结果type=ref，索引生效

EXPLAIN SELECT * FROM test WHERE age = 18;
-- 执行当前sql，发现where条件单独为age的时候，执行结果type=ALL，发现并没有走索引

EXPLAIN SELECT stu_no,age FROM test WHERE age = 18;
-- 执行当前sql，发现where条件单独为age的时候，但是仅仅查询stu_no,age列，执行结果type=index，索引生效

结论:当我们创建一个联合索引的时候，如(a,b,c)，相当于创建了（a）、(a,b)和(a,b,c)三个索引，这就是最左匹配原则；联合索引不满足最左原则，索引一般会失效，但是上述第三种情况命中索引是因为查询列覆盖索引。

索引列使用函数或者计算

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_age`(`age`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test WHERE ABS(age) = 18;
-- 执行当前sql，发现where使用了mysql的内置函数ABS绝对值，执行结果type=ALL，导致了索引失效

EXPLAIN SELECT * FROM test WHERE age - 1 = 17;
-- 执行当前sql，发现where对索引列进行计算，执行结果type=ALL，导致了索引失效

使用了select *，导致索引失效

虽然在规范手册中没有提到索引方面的问题，但禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引,查询字段均为索引时; select * 容易带来如下问题：

增加查询分析器的解析成本
增减字段容易与 resultMap 配置不一致
无用字段增加网络消耗，尤其是 text 类型的字段

order by

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `id_age`(`age`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = 'utf8mb4';
-- 测试数据表结构

EXPLAIN SELECT * FROM test ORDER BY age ASC
-- 执行当前sql，发现使用了ORDER BY排序后，执行结果type=ALL，索引不生效

结论:由于使用ORDER BY需要对全表数据进行排序，因此会索引失效，但是有个特例，如果ORDER BY后面跟的是主键，也会走索引，有时候也与mysql的优化器有关。

group by

结尾

两字段列做比较，导致索引失效
左连接、右连接关联字段编码不一致，索引失效
使用is null，is not null，!=, <>, not in导致索引失效
大表的where in