为何MySQL关联查询时不选用JSON函数索引却选用生成列索引？

我在MySQL 8.0.35中大量使用JSON列，通常会为JSON属性创建函数索引来加速查询。根据MySQL官方文档，函数索引以隐藏虚拟生成列的方式实现，但在关联查询场景中，它的表现却和生成列上的常规索引存在差异，以下通过示例说明：

示例场景 #

现有product和purchase两张表，purchase表的JSON属性$.productUuid关联product表。

创建表与插入数据 #

CREATE TABLE IF NOT EXISTS product (
    id         BINARY(16) NOT NULL,
    payload    JSON       NOT NULL,
    
    CONSTRAINT pk_product PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS purchase (
    id         BINARY(16) NOT NULL,
    payload    JSON       NOT NULL,
    
    CONSTRAINT pk_purchase PRIMARY KEY (id),
    INDEX `i_purchase_product` (
        ( CAST(payload->>'$.productUuid' AS CHAR(36)) COLLATE utf8mb4_bin )
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO product (id, payload)
VALUES
  ( UUID_TO_BIN(UUID(), TRUE), '{ "name": "random drink" }' ),
  ( UUID_TO_BIN(UUID(), TRUE), '{ "name": "random dish" }' ),
  ( UUID_TO_BIN(UUID(), TRUE), '{ "name": "random tool" }' )
;

INSERT INTO purchase (id, payload)
SELECT
  UUID_TO_BIN(UUID(), TRUE), JSON_SET(payload, '$.productUuid', BIN_TO_UUID(id))
FROM product
;

执行关联查询 #

SELECT * FROM product a INNER JOIN purchase b ON BIN_TO_UUID(a.id) = b.payload->>'$.productUuid';

函数索引下的执行计划

+----+-------------+-------+---------------+------+--------------------------------------------+
| id | select_type | table | possible_keys | key  | Extra                                      |
+----+-------------+-------+---------------+------+--------------------------------------------+
|  1 | SIMPLE      | a     | NULL          | NULL | NULL                                       |
|  1 | SIMPLE      | b     | NULL          | NULL | Using where; Using join buffer (hash join) |
+----+-------------+-------+---------------+------+--------------------------------------------+

该执行计划显示，函数索引未被选用。

使用生成列+常规索引的对比 #

创建带生成列及常规索引的purchase表：

CREATE TABLE IF NOT EXISTS purchase (
    id           BINARY(16)  NOT NULL,
    payload      JSON        NOT NULL,
    product_uuid VARCHAR(36) GENERATED ALWAYS AS (payload->>'$.productUuid') STORED NOT NULL,
    
    CONSTRAINT pk_purchase PRIMARY KEY (id),
    INDEX `i_purchase_product` (product_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行相同查询后，生成的执行计划如下：

生成列索引下的执行计划

+----+-------------+-------+--------------------+--------------------+-----------------------+
| id | select_type | table | possible_keys      | key                | Extra                 |
+----+-------------+-------+--------------------+--------------------+-----------------------+
|  1 | SIMPLE      | a     | NULL               | NULL               | NULL                  |
|  1 | SIMPLE      | b     | i_purchase_product | i_purchase_product | Using index condition |
+----+-------------+-------+--------------------+--------------------+-----------------------+

该执行计划显示索引被成功选用。

疑问 #

请问该行为是否有官方文档可查的解释？

内容的提问来源于stack exchange，提问作者Thomas K.