MySQL中VARCHAR列存数值时B-Tree索引的低效性问题

我正在使用MySQL数据库，其中有一个VARCHAR类型的列，但该列存储的是数值类型数据。我为该列创建了B-Tree索引，以优化如下查询：

E.x > n
n < E.x
n = E.x

（其中n为正浮点数，E为实体，x代表其属性）

但我怀疑该索引未带来预期的性能提升，我的推理如下：

• MySQL中的B-Tree索引依赖列数据的自然排序规则，而VARCHAR类型采用字典序而非数值排序。
• 由于B-Tree索引是基于既定排序规则高效运作的，字典序与数值序的不匹配可能导致数值比较时索引使用低效。
• 此外，查询中数据会被转换为DECIMAL类型，我认为这也会对性能产生影响。

问题： #

1. 我关于此类索引对VARCHAR列的数值比较无效的假设是否正确？
2. 有无无需修改列数据类型的变通方案或优化手段（如查询时转换）？
3. 切换为合适的数值类型（如INT或DECIMAL）并创建索引是否是唯一可行的真正解决方案？

回答 #

1. 假设基本正确

你的判断是对的。当对存储数值的VARCHAR列做数值比较时，MySQL会触发隐式类型转换：把VARCHAR列的所有值转换成数值类型再和n比较。这个转换过程会导致优化器无法直接使用B-Tree索引——因为索引是按字典序构建的，而转换后的数值排序和字典序完全不匹配，优化器只能选择全表扫描。即使是=比较，虽然可能偶尔命中索引，但一旦遇到带前导零的数值（比如'0123'和123），隐式转换后值相等，但字典序却不同，索引查找会漏掉数据，同时性能也远不如数值类型的索引。

2. 有无需修改列类型的临时方案，但有局限性

• 统一字符串格式后用字符串比较：把查询中的n转换成格式统一的字符串（比如所有数值都保留相同小数位数、不足位数补前导零），例如E.x > '00123.45'，这时能用到字典序的B-Tree索引。但这种方案维护成本极高，只要有一条数据格式不统一，查询结果就会出错（比如'99'字典序大于'100'）。
• 创建函数索引：MySQL 8.0.13及以上版本支持函数索引，可以基于数值转换后的结果创建索引。比如：

  CREATE INDEX idx_x_numeric ON E (CAST(x AS DECIMAL(18,2)));
  

  之后查询时使用CAST(E.x AS DECIMAL(18,2)) > n就能用到这个索引。但要注意函数索引会占用额外存储空间，且每次插入/更新数据时需要计算转换值，会增加写操作的开销。

3. 切换数值类型是最优解，而非唯一可行方案

切换成DECIMAL（适合精确小数）或FLOAT/DOUBLE（适合浮点数）并创建索引，是性能最优、维护成本最低的方案，因为这完全贴合数据的实际类型，索引能完美发挥B-Tree的排序和查找优势，也避免了任何类型转换的开销。

但这并不是唯一“真正可行”的方案——比如上面提到的函数索引也能解决问题，但长期来看，修改列类型是最合理的选择，因为函数索引始终是临时 workaround，存在额外的存储和写性能损耗，且如果后续有其他数值类查询，都需要适配函数索引的写法，不如直接修正列类型来得彻底。

内容的提问来源于stack exchange，提问作者Eduard Hauer