MySQL中JSON数组字段添加索引后查询未命中问题排查

1. 字段名前后不匹配 #

• 你建表时存储国家代码的字段名为 country_codes_by_target_groups，但你写的查询语句、以及你描述的索引创建语句中，错误使用了不存在的字段名 country_codes / country_code，字段不匹配的情况下必然会默认走全表扫描。
• 首先修正所有语句里的字段引用，保证和建表的字段名完全一致。

2. 查询条件与索引定义不匹配 #

MySQL的函数索引要求查询条件中的表达式必须和索引定义的表达式完全一致，才会触发索引命中：

• 你当前的查询写法是 "US" MEMBER OF(字段名)，但你创建的索引是对字段cast成char(2)数组后的结果，优化器不会自动做隐式转换匹配索引。
• 修正查询写法如下即可匹配你创建的country_codes2索引：

SELECT id, country_codes_by_target_groups 
FROM offers 
WHERE 'US' MEMBER OF(cast(`country_codes_by_target_groups` as char(2) array))

注：你创建的country_codes3索引是提取JSON中$.arr路径下的数组做的索引，而你存储的内容根节点就是数组、不存在arr键，该索引完全不适用你的查询场景，可以直接删除。

3. 字段类型与JSON合法性校验 #

• 你当前country_codes_by_target_groups字段类型是text，需要保证存储的内容是合法JSON数组格式，不能有多余的转义字符、格式错误，否则cast转换失败也会导致索引无法命中。
• 如果业务允许，建议直接将字段类型改为JSON类型，既可以减少转换开销，也能避免非法JSON内容写入。

4. 命中校验 #

修改完查询语句后重新执行explain，如果索引正常命中，type列会从ALL变为ref或者range，不会再提示全表扫描。

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