MySQL JSON数组列加索引后JSON_CONTAINS与MEMBER OF结果异常

测试背景 #

MySQL版本8.0.28，创建含JSON列的测试表dummy_table2，插入10000行数据，每行的JSON列是包含10个随机值的JSON数组。无索引时，用JSON_CONTAINS和MEMBER OF查询值51的行数均为969；添加JSON数组索引（CAST(json_column as UNSIGNED ARRAY)）后出现以下现象：

• 两种MEMBER OF查询结果仍为969，但均执行全表扫描，未使用索引；
• JSON_CONTAINS(...) = 1查询结果为969，同样全表扫描；
• JSON_CONTAINS(...)查询结果变为130，且使用了索引。

问题1：多数案例为何不使用索引，如何强制MySQL使用该索引？ #

不使用索引的原因 #

1. 索引匹配语法限制：MySQL的JSON数组索引（基于CAST(... AS UNSIGNED ARRAY)创建）仅支持特定查询语义。MEMBER OF语法、JSON_CONTAINS(...) = 1这种显式等于1的写法，无法被优化器识别为符合索引匹配的条件；而直接写JSON_CONTAINS(...)时，优化器虽尝试匹配索引，但因语义转换错误导致结果异常。
2. 优化器成本判断：10000行属于极小数据集，优化器认为全表扫描的内存操作成本比走索引的成本更低，因此选择全表扫描。

强制使用索引的方法 #

• 使用FORCE INDEX显式指定索引，示例语句：

  -- 强制MEMBER OF查询使用索引
  SELECT COUNT(*) FROM dummy_table2 FORCE INDEX(json_array_idx) WHERE json_column MEMBER OF (51);
  -- 强制JSON_CONTAINS等于1的查询使用索引
  SELECT COUNT(*) FROM dummy_table2 FORCE INDEX(json_array_idx) WHERE JSON_CONTAINS(json_column, '51') = 1;
  

• 临时调整优化器成本参数（不推荐全局修改），比如降低eq_range_index_dive_limit，但此操作会影响所有查询的优化逻辑。

问题2：10k行数据下所有查询耗时均低于50ms的原因是什么？ #

1. 全内存操作：10000行数据量极小，能完全加载到InnoDB缓冲池中，避免了磁盘IO开销，纯内存读写速度极快。
2. 计算开销低：每行JSON数组仅含10个值，解析和匹配的CPU计算量非常小，几乎无性能损耗。
3. 小数据集优化：MySQL针对小数据集的全表扫描有专门的优化逻辑，执行路径短，无需复杂的索引遍历操作，耗时自然极低。

问题3：比较JSON_CONTAINS/MEMBER OF结果时，使用true/false和1/0是否存在差异？ #

存在明确差异：

• JSON_CONTAINS和MEMBER OF的返回值是整数类型的1（存在）/0（不存在），并非SQL标准的布尔值TRUE/FALSE。
• 当写JSON_CONTAINS(...) = TRUE时，MySQL会将TRUE隐式转换为整数1，效果等同于JSON_CONTAINS(...) = 1；但直接写JSON_CONTAINS(...)作为查询条件时，优化器会将其视为“非0即真”的判断，此时索引匹配逻辑会出现语义误解，导致结果异常（如你遇到的行数从969变为130）。
• 从语法严谨性和避免索引匹配问题的角度，建议使用=1或=0来匹配查询结果，避免隐式转换带来的问题。

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