为什么MySQL不考虑在联接中使用(JSON)函数索引，而考虑在生成的列上使用索引

在MySQL(版本8.0.35)中，我经常使用JSON列.通常，我会在JSON属性上创建函数索引以加快查询速度.根据MySQL docs,Functional indexes are implemented as hidden virtual generated columns.但是，它们的行为似乎与联接中生成的列上的索引不同.我将用一个不知何故构建的例子来解释我的观点.

示例:给出了两个表product和purchase.purchase具有引用product的JSON属性$.productUuid.

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_product 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';

MySQL生成以下计划:

+----+-------------+-------+---------------+------+--------------------------------------------+
| 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) |
+----+-------------+-------+---------------+------+--------------------------------------------+

该计划显示，甚至没有考虑功能指数.如果我创建的表包含一个生成的列和一个常规索引，则情况会有所不同.

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;

现在，MySQL生成的计划显示该索引已被考虑:

+----+-------------+-------+--------------------+--------------------+-----------------------+
| 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 |
+----+-------------+-------+--------------------+--------------------+-----------------------+

对于这种行为，有没有什么解释，最好是有记录的？