MySQL 5.7中索引列函数查询失效后的优化方案问询

问题背景 #

表A的name列已创建索引，但原查询语句select * from A where length(name) >= 5;因在索引列上调用了length()函数，导致索引无法命中，触发全表扫描，需要优化查询以利用索引。

优化方案 #

方案1：使用生成列（推荐，MySQL 5.7及以上支持）

MySQL无法直接在索引列上计算函数值，我们可以把name列的长度预先计算为生成列，再给该列创建索引：

1. 添加存储型生成列（会实际存储数据，查询时无需实时计算）：

ALTER TABLE A ADD COLUMN name_len INT GENERATED ALWAYS AS (LENGTH(name)) STORED;

2. 给生成列创建索引：

CREATE INDEX idx_name_len ON A(name_len);

3. 修改查询语句，直接使用生成列过滤：

SELECT * FROM A WHERE name_len >= 5;

该方案能让查询直接命中idx_name_len索引，彻底避免全表扫描。

方案2：利用字符串排序特性（仅适用于特定场景）

如果name列使用单字节字符集（如latin1），且业务场景能保证所有长度≥5的字符串都大于某个固定的4字符值（比如业务中姓名首字母均大于'z'以外的字符），可以尝试用字符串比较替代函数：

SELECT * FROM A WHERE name >= 'zzzz';

注意：该方案通用性极差，因为字符串是逐字符比较的，例如'aaaaa'（5个a）会小于'zzzz'（4个z），无法覆盖所有符合条件的数据，仅作为特殊场景的补充方案。

原查询失效原因 #

MySQL的索引是基于列的原始值构建的，当在索引列上使用函数时，MySQL需要先计算每一行的函数结果才能过滤数据，无法直接利用索引的有序性，因此会放弃索引转而执行全表扫描。

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