mysql数据类型+索引失效

• • 1、背景
  • • 现状：一个表内有217069条数据，另一个表内有76015条数据，查询起来发现速度特别的慢，推测是**索引失效**的问题，是不是因为咱们写的sql语句中的is_delete 传值是数字，但是表中的数据类型是字符类型的，所以导致了慢的问题呢？
  • 2、思路&解决方案
  • • 结论：
    • 数值类型的的转换，会使我们的索引失效，所以我们设计实体类的数值类型的时候，一定要和数据库的数据类型进行对应，避免索引失效的情况的出现。
  • 3、扩展（索引失效情况）：
  • • 对索引使用左或者左右模糊匹配
    • 对索引使用函数
    • 对索引进行表达式计算
    • 对索引隐式类型转换
    • 联合索引非最左匹配
    • WHERE 子句中的 OR
  • 4、总结

1、背景

现状：一个表内有217069条数据，另一个表内有76015条数据，查询起来发现速度特别的慢，推测是索引失效的问题，是不是因为咱们写的sql语句中的is_delete 传值是数字，但是表中的数据类型是字符类型的，所以导致了慢的问题呢？

2、思路&解决方案

复现上述问题：
首先我们摆出结论。where条件里，字符类型的列如过传递数值类型可以查出来数据，但是会索引失效。
1、
对应的索引：


is_delete 是字符型的，
但是下边查询是数值型的。
实际查询：
我们使用EXPLAIN关键字分析可以发现。type是 ALL，也就是全表扫描，并没有走索引。

我们换成字符型的值进行查询。

我们发现 type类型变成了ref，说明是走了我们新建的索引的。

结论：

数值类型的的转换，会使我们的索引失效，所以我们设计实体类的数值类型的时候，一定要和数据库的数据类型进行对应，避免索引失效的情况的出现。

3、扩展（索引失效情况）：

索引失效

对索引使用左或者左右模糊匹配

使用 %xx 或者 %xx% 都会造成索引失效问题。但是 xx% 不会造成索引失效。

对索引使用函数

在where条件里，使用函数。比如 select * from user where length(name) = 6
这里进行的就是全表扫描，因为索引里存储的是列的原始值而不是计算后的值

对索引进行表达式计算

例如：select * from user where age + 1 = 10 。
这里也将会进行全表扫描，原因与对索引使用函数一样，都是因为索引内存储的是原始值而不是计算后的值。但是如果将上文的查询修改为 select * from user where age = 10 - 1 的话就没有问题，是会正常走age的索引的

对索引隐式类型转换

我们上文讲到，mysql有三种数据类型，分别是 数值型、日期型、字符型 ,能够隐式转换的就是 数值型和字符型了。
1.如果索引字段是字符型，但是条件查询时，传入的是整型的话，会出现索引失效问题。
2.如果索引是整型，但是条件查询的时候，传入的是字符型，不会出现索引失效问题。
这是因为mysq在遇到字符串和数字比较的时候，会默认将字符串转换为数值类型进行处理

联合索引非最左匹配

假设我们对字段 a，b ，c 建立了一个联合索引（a，b，c）。
如果我们的查询是以下几种，则会正常走联合索引：

where a = 1;
where a = 2 and b = 2;
where a = 2 and b = 2 and c = 2;
如果是如下几种则不会走联合索引：

where b = 2;
where c = 2;
where b = 2 and c = 3;
通过对比我们可以看出，索引的生效是遵循最左匹配原则的。在使用的时候，必须从最左侧的索引开始。

还有一种特殊的情况

where a = 2 and c = 2 ；
这种情况的话，在MySql 5.5的时候，前面的a会走索引，找到对应的主键值后，开始进行回表查询。Mysql 5.6 后，会进行索引下推，速度更快。索引还是会生效。

WHERE 子句中的 OR

如果where 子句里 or前的列是索引列，or后面的条件列不是索引列，索引就会失效。
例如：
select * from user where id = 2 or age = 10 ;
id是主键，默认是主键索引。 age如果没有添加为主键的话，都会进行全表扫描。 因为or表示两个条件满足一个就可以。所以只有一个列有索引的话，没有作用，还是会去全表扫描。

4、总结

防患于未然。胆大心细。