对 mysql8 json 索引问题的简单分析

1. 数据准备

测试采用的 mysql 版本为 8.0.27，laravel 版本为 9.0.0。

首先创建数据库迁移文件 php artisan make:model Building -m：

Schema::create('buildings', function (Blueprint $table) {
    $table->id();
    $table->json('region');
    $table->timestamps();

    $table->rawIndex('(CAST(region->>"$.china_code" AS CHAR(12)) COLLATE utf8mb4_bin)', 'region_china_code');
});

关注生成的索引如下：

KEY `region_china_code` (((cast(json_unquote(json_extract(`region`,_utf8mb4'$.china_code')) as char(12) charset utf8mb4) collate utf8mb4_bin)))

2. 发现的问题

打印出 laravel 中生成的查询 sql：

$sql = Building::query()->where('region->china_code', '500101')->toSql();

生成的 sql 如下：

select * from `buildings` where json_unquote(json_extract(`region`, '$."china_code"')) = '500101'

该 sql 执行时并不会用到刚才生成的索引 region_china_code，发现原因是在于该 sql 中的 '$."china_code"') 与 索引中所在的 '$.china_code' 地方不一致（即 laravel 中 生成的 sql 会多出一对“双引号”），导致索引失败。

3. 案例测试

以下测试均在 mysql 客户端进行

现在的索引 region_china_code 情况为：

*************************** 2. row ***************************
        Table: buildings
   Non_unique: 1
     Key_name: region_china_code
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`region`,_utf8mb4\'$.china_code\')) as char(12) charset utf8mb4) collate utf8mb4_bin)

1. 不会使用到索引

   EXPLAIN
   select * from `buildings` where region->>'$."china_code"' = '500101'

   等同于：

    EXPLAIN
    select * from `buildings` where json_unquote(json_extract(region, '$."china_code"'))='500101'

2. 能够使用到索引

   EXPLAIN
   select * from `buildings` where region->>'$.china_code' = '500101'

   等同于：

    EXPLAIN
    select * from `buildings` where json_unquote(json_extract(region, '$.china_code'))='500101'

一句话总结，json 索引中的 path 为 '$.china_code'，与此一样就会使用到索引，不一样则不会。

4. 解决办法

4.1 使用原生 sql

由于直接使用 laravel 中的 where 方法会在生成的 sql 中为 json.path 添加一对引号，可以使用 whereRaw 来避免这个问题。

1. 能够匹配到索引

   $sql = Building::query()->whereRaw('region->>"$.china_code" = :code', ['code' => '500101'])->toSql();

2. 不能匹配到索引

   $sql = Building::query()->where('region->china_code', '500101')->toSql();

4.2 调整原来的索引规则（推荐）

调整索引格式，使得与 laravel 中 where 方法生成的 json.path 保持一致。

$table->rawIndex("(CAST(region->>'$.\"china_code\"' AS CHAR(12)) COLLATE utf8mb4_bin)", 'region_china_code');

此时索引详情为：

*************************** 2. row ***************************
        Table: buildings
   Non_unique: 1
     Key_name: region_china_code
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`region`,_utf8mb4\'$."china_code"\')) as char(12) charset utf8mb4) collate utf8mb4_bin)

因为，laravel 的where 方法会将 json.path 中的键都包上一层引号（laravel 有这样的一个考虑，应该与 mysql 中规定的：“如果含有数字键必须用引号将数字键包上”有关系），而此时生成的索引中的 json.path 为 '$."china_code"' 就刚好与 where 方法符合，这样也能够使用到这个新的索引。

$sql = Building::query()->where('region->china_code', '500101')->toSql();

等同于使用 whereRaw：

$sql = Building::query()->whereRaw("region->>'$.\"china_code\"' = :code", ['code' => '500101'])->toSql();

等同于 sql：

EXPLAIN
select * from `buildings` where region->>'$."china_code"' = '500101'

如果此时不给 json.path 的键 china_code加上双引号，则不会用到索引：

EXPLAIN
select * from `buildings` where region->>'$.china_code' = '500101'

5. 总结

在 laravel 中，为 json 数据加函数索引时，应该用引号将键包裹起来。或许 mysql 应该针对这一情况，忽略引号对函数索引的影响。