MariaDB中varchar虚拟生成列索引未被使用的原因及解决方法

索引未命中和varchar类型本身无直接关系，核心诱因有两个：

1. 从执行计划的rows字段可见当前表统计行数仅34行，属于极小表。MariaDB优化器会自动做成本估算：走二级索引查询后需要回表取整行数据，对于34行的表，全表顺序扫描的开销远低于二级索引查找+回表的开销，因此主动选择全表扫描，属于正常优化逻辑，不是索引失效。
2. 存在隐式转换风险：定义生成列时未显式指定字符集、排序规则、JSON_VALUE返回值类型，生成列实际的排序规则可能和查询传入的字符串常量的排序规则不匹配，触发隐式转换时会导致索引无法被命中。

• 首先验证小表成本判断逻辑：往表中灌入至少数千行携带不同phone_number值的测试数据，重新执行EXPLAIN，绝大多数场景下索引会被自动命中。
• 显式定义生成列全量属性，彻底规避隐式转换问题，重建生成列与索引：

-- 删除旧索引
DROP INDEX dp_order_phone_number ON dp_order;
-- 修改生成列，显式指定数据类型、字符集、排序规则、JSON返回值类型
ALTER TABLE dp_order
MODIFY COLUMN phone_number VARCHAR(20) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
AS (JSON_VALUE(detail, '$.phone_number' RETURNING VARCHAR(20))) VIRTUAL;
-- 重建索引
CREATE INDEX dp_order_phone_number ON dp_order(phone_number);

• 通过强制索引语法确认索引本身可用性：

EXPLAIN
SELECT * FROM dp_order FORCE INDEX (dp_order_phone_number)
WHERE phone_number = '081234567890';

如果返回的执行计划中type为ref、key字段显示为dp_order_phone_number，说明索引本身功能正常，之前未命中仅为优化器基于表大小做的成本选择。

补充说明：MariaDB 10.2及以上版本原生支持VIRTUAL类型生成列建索引，不需要将生成列改为STORED类型即可正常使用索引，无需额外调整生成列的存储属性。

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