索引失效的几种情况

本文只是个人笔记，如有错误还望提醒

最佳左前缀法则

范围查询右边失效

like索引失效原理

隐式转换造成的索引失效

看这个表

CREATE TABLE `test1` (
    `id` int(11) NOT NULL,
    `num1` int(11) NOT NULL DEFAULT '0',
    `num2` varchar(11) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `num1` (`num1`),
    KEY `num2` (`num2`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8

SQL 测试
先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。

1: SELECT * FROM `test1` WHERE num1 = 10000;
2: SELECT * FROM `test1` WHERE num1 = '10000';
3: SELECT * FROM `test1` WHERE num2 = 10000;
4: SELECT * FROM `test1` WHERE num2 = '10000';
1
2
3
4

其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.001 ~ 0.005 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。
为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：
可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大。
查阅 MySQL 相关文档发现是隐式转换造成的，看一下官方的描述：

官方文档： 12.2 Type Conversion in Expression Evaluation
当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：

1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较
   根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件num1 = ‘10000’，左边是int类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。

先看第 2 条 SQL：SELECT * FROMtest1WHERE num1 = ‘10000’; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型’10000’，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

第 3 条 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左边是字符串类型’10000’，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，‘10000’转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如’10000a’，‘010000’，'10000’等等都能转为浮点数10000，这样的情况下，是不能用到索引的。
查阅相关资料发现规则如下：

1. 不以数字开头的字符串都将转换为0。如’abc’、‘a123bc’、'abc123’都会转化为0；
2. 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如’123abc’会转换为123，'012abc’会转换为012也就是12，'5.3a66b78c’会转换为5.3，其他同理。

分析和总结

通过上面的测试我们发现 MySQL 使用操作符的一些特性：

1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。

字符集和排序规则不相同导致索引失效

其它：

1. 最好全值匹配
2. 不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描

explain select * from ti where b+1=5;  -- 对字段操作走不了索引
1

3. 尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select *
4. 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
5. 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
6. 索引字段使用 or 时，会导致索引失效而转向全表扫描