MySQL关联查询中JSON数组索引未生效的差异原因分析

解答 #

核心差异在于连接条件中d.id是常量还是动态变量，这直接决定了MySQL能否利用directory表的idx_datasets索引：

1. 第一个查询（where d.id = 111）：常量驱动的连接

当你用d.id = 111过滤时，MySQL对dataset表的处理是常量查找（执行计划里type: const）——通过主键索引直接定位到唯一一行，拿到d.id = 111这个固定值。

此时连接条件JSON_CONTAINS(dir.datasets, cast(d.id as json))等价于JSON_CONTAINS(dir.datasets, '111')，111是一个明确的常量。MySQL可以将这个条件转化为：查找dir.datasets数组中包含111的行，而你创建的idx_datasets是基于cast(datasets as unsigned array)的函数索引，正好支持这种数组包含查询，所以directory表可以用range类型的索引扫描快速定位符合条件的行。

2. 第二、三个查询（where d.name like '111'/d.name = '111'）：变量驱动的连接

当过滤条件是d.name时，MySQL先通过idx_name索引找到符合条件的dataset行（执行计划里d表的type是range或ref），但这些行的d.id是动态变量——即使这里只返回1行，MySQL依然将其视为不确定的变量。

对于directory表的连接条件JSON_CONTAINS(dir.datasets, cast(d.id as json))，MySQL无法将这个变量参数与idx_datasets索引结合使用：因为idx_datasets是针对dir.datasets数组构建的索引，MySQL没有办法高效地用一个动态变化的d.id值去匹配数组索引中的元素。因此，MySQL只能对directory表做全表扫描（type: ALL），然后通过哈希连接（hash join）逐一检查每个dir.datasets是否包含当前d.id的值。

简单总结：只有当连接条件中的查找值是已知常量时，MySQL才能利用directory表的数组索引；如果是来自关联表的动态变量，就只能走全表扫描+连接缓冲区的方式。

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