为什么字符串字段和数字比较不能走字段上的索引

这是一个并不复杂的问题，我们先说结论，再来探寻背后的原理

字符串字段和数字查询条件进行比较时，会先把字符串转换为数字再比较，而对字段进行了类型转换，是不能使用索引的

结论很简单，但是这背后的原理是什么呢？ 同时引申出来另一个问题：为什么字符串和数字比较时，要把字段转换为数字，而不是把数字转换为字符串比较呢？

接下来我们一起去探究一下这 2 个问题。

准备工作

第 1 步，先建个测试的表

1CREATE TABLE `test` (
2  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3  `str1` varchar(255) NOT NULL DEFAULT '',
4  `i1` int(11) NOT NULL DEFAULT '0',
5  `str2` varchar(255) NOT NULL DEFAULT '',
6  `i2` int(11) NOT NULL DEFAULT '0',
7  PRIMARY KEY (`id`),
8  KEY `idx_str1` (`str1`),
9) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第 2 步，再插入几条记录，整个表中的记录如下图（图 1）：

第 3 步，我们再画一个 idx_str1 索引的示意图

因为我们插入的记录有点少，实际上一个索引页就能全部存下了。

为了达到演示索引的 B+ 树结构的目的，我们假设索引的每 1 页最多能存储 2 条记录。

基于这个假设，idx_str1 索引的 B+ 树的简化结构图（图 2）如下：

上图中的 idx_str1 索引分为 3 层： 页 104、105、106、107 所在层级的 level 为 0（简称为 level 0） 页 102、103 所在层级的 level 为 1（简称为 level 1） 页 101 所在层级的 level 为 2（简称为 leve 2）

level 0、level 1 中的页在各自的层级都是按照 idx_str1 索引的字段 str1 排好序的。

图中颜色块说明：

1灰色块：当前页的页号
2蓝色块：二级索引字段值
3绿色块：主键字段值
4黄色块：非叶子结点中的目录项，指向的子页面的页号

为什么索引字段发生类型转换就不能走索引

上面的准备工作完成了，接下来开始进入正题，以 1 条查询语句来分析为什么不能走索引：

1SELECT * FROM t5 WHERE str1 > 20000 and str1 < 30000

这个问题要从正面来解释清楚似乎不太容易，我们从反面来论证。 我们先假设上面的 SQL 语句能走索引，顺着这个思路来看看，如果使用索引会出现什么问题。

1按照索引的工作流程，需要根据 WHERE 条件中的 str1 > 20000 和 str1 < 30000 来确定索引的扫描区间
21. 先根据 str1 > 20000 来确定扫描区间的第 1 条记录
3
42. 从索引根页面 101 开始，比较页中的各记录 str1 字段的值和 20000 的大小
5   没有找到大于 20000 的记录，接着到页 101 中最大的记录 2090 指向的子结点页 103 中查找
6
73. 页 103 中也没有找到大于 20000 的记录，接着到页 103 中最大的记录 380 指向的子结点页107 中查找
8
94. 找完页 107 中也没有找到 str1 的值大于 20000 的记录，至此确定扫描区间的过程结束

通过上面的过程，发现在确定索引扫描区间时，会被认为该索引中没有符合查询条件的记录，就不会再去引擎层查询记录了，而是就此结束，最终给客户端返回的查询结果是空。

如果按照字符串转换为数字的比较规则，表中 str1 的值为 20083、20095、24363 的这 3 条记录显然是符合查询条件，应该包含在查询结果中的，然后实际返回的结果却是空的。

分析到这里，我们就能回答本小节提出的问题了

索引列发生类型转换后，在确定查询条件的扫描区间时，相对于类型转换后需要的记录顺序来说，索引中记录的顺序是无序的，这样得到的索引扫描区间就是错误的

根据得到的错误扫描区间去引擎层取数据，取到的数据就是错的了 既然转换索引类型后，得不到正确的数据，那自然也就不能使用索引进行查询了

为什么字符串和数字比较时，不是把数字转换为字符串进行比较？

我们还是从反面来论证，假设字符串字段和数字比较时，是把数字转成字符串再比较

接下来还是以 1 条 SQL 语句来说明:

1SELECT * FROM t5 WHERE str1 > 280 and str1 < 30000
2
3-- 基于上面的假设，这条 SQL 语句相当于：
4SELECT * FROM t5 WHERE str1 > '280' and str1 < '30000'
5
6-- 执行上面的语句得到的查询结果为空

按照字符串的比较规则，'280' 大于 '30000'，如果 MySQL 把字符串转换为数字

那么上面语句的查询条件 str1 > 280 and str1 < 30000 变成 str1 > '280' and str1 < '30000' 之后，该查询条件的含义发生了变化，不符合代数关系中的等价变换