Mysql数据类型+索引失效
一：背景介绍
MySql数据库的is_delete字段，两个不同的表，一个表内有217069条数据，另一个表内有76015条数据，查询起来发现速度特别的慢，推测是索引失效的问题，让我们来尝试进复现
二：思路&方案
Mysql数据类型
在进行验证之前，我们先来学习一下mysql的三种数据类型。Mysql主要分为三种类型** 数值**、日期/时间和**字符串(字符)**类型。
数值类型
| 类型 | 大小(bytes) | 范围（无符号） | 范围（带符号） | 用途 |
| tinyint | 1 | (-128，127) | （0,255） | 小整数值 |
| smallint | 2 | (-32 768，32 767) | （0,65 535） | 大整数值 |
| mediumint | 3 | (-8 388 608，8 388 607) | （0,16 777 215） | 大整数值 |
| int | 4 | (-2 147 483 648，2 147 483 647) | （0,4 294 967 295） | 大整数值 |
| bigint | 8 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | （0,18 446 744 073 709 551 615） | 极大整数值 |
日期/时间类型
| 类型 | 大小(bytes) | 范围 | 格式 | 用途 |
| date | 3 | 1000-01–1/9999-12.31 | YYYY-MM_DD | 日期值 |
| time | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| year | 1 | 1901/2155 | YYYY | 年份值 |
| datetime | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM_DD hh:mm:ss | 混合日期和时间值 |
| timestamp | 4 | 有专有的自动更新特性 | YYYY-MM_DD hh:mm:ss | 混合日期和时间值，时间戳 |
字符串（字符）类型
| 类型 | 大小（bytes） | 用途 |
| char | 0 - 255 | 定长字符串 |
| varchar | 0 - 65 535 | 变长字符串 |
| tinyblob | 0 - 255 | 不超过255个字符的二进制字符串 |
| tinytext | 0 - 255 | 短文本字符串 |
| blob | 0 - 65535 | 二进制形式长文本数据 |
| text | 0 - 65535 | 长文本数据 |
| mediumblob | 0 - 16 777 215 | 二进制形式的中等长度文本数据 |
| mediumtext | 0 - 16 777 215 | 中等长度文本数据 |
| longblob | 0 - 4 294 967 295 | 二进制形式极大文本数据 |
| longtext | 0 - 4 294 967 295 | 极大文本数据 |
索引失效复现
首先我们摆出结论。where条件里，字符类型的列如过传递数值类型可以查出来数据，但是会索引失效。
对应的索引
is_delete 字段的类型
使用数值型进行查询
我们使用EXPLAIN关键字分析可以发现。type是 ALL，也就是全表扫描，并没有走索引。
使用字符型查询
我们发现 type类型变成了ref，说明是走了我们新建的索引的。
结论
数值类型的的转换，会使我们的索引失效，所以我们设计实体类的数值类型的时候，一定要和数据库的数据类型进行对应，避免索引失效的情况的出现。
三：扩展
既然这次出现了索引失效，那么我们就总结一下，常见的索引失效的情况。
索引失效
对索引使用左或者左右模糊匹配
使用 %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表示两个条件满足一个就可以。所以只有一个列有索引的话，没有作用，还是会去全表扫描。
四：总结
开发的过程中，要谨记这些索引失效的大坑，这样以后我们在使用的时候，才能够避免！！
