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