MySQL :: MySQL 8.4 参考手册 :: 10.3.11 优化器对生成列索引的使用

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 优化器对生成列索引的使用

10.3.11 优化器对生成列索引的使用

MySQL 支持对生成列进行索引。例如

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成列 gc 定义为表达式 f1 + 1。该列也被索引，优化器可以在构建执行计划期间考虑该索引。在以下查询中，WHERE 子句引用了 gc，优化器会考虑该列上的索引是否会产生更有效的计划

SELECT * FROM t1 WHERE gc > 9;

即使查询中没有直接按名称引用这些列，优化器也可以使用生成列上的索引来生成执行计划。如果 WHERE、ORDER BY 或 GROUP BY 子句引用了与某个索引生成列的定义匹配的表达式，就会发生这种情况。以下查询没有直接引用 gc，但使用了与 gc 的定义匹配的表达式

SELECT * FROM t1 WHERE f1 + 1 > 9;

优化器识别出表达式 f1 + 1 与 gc 的定义匹配，并且 gc 已被索引，因此它在构建执行计划期间会考虑该索引。您可以使用 EXPLAIN 查看这一点

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

实际上，优化器已经用与表达式匹配的生成列的名称替换了表达式 f1 + 1。这在 EXPLAIN 信息中显示的重写查询中也很明显，该信息由 SHOW WARNINGS 显示

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

以下限制和条件适用于优化器对生成列索引的使用

要使查询表达式与生成列定义匹配，该表达式必须完全相同，并且必须具有相同的结果类型。例如，如果生成列表达式为 f1 + 1，则如果查询使用 1 + f1，或者如果 f1 + 1（一个整数表达式）与字符串进行比较，则优化器不会识别匹配。
该优化适用于以下运算符：=、<、<=、>、>=、BETWEEN 和 IN()。
对于 BETWEEN 和 IN() 以外的运算符，任何一个操作数都可以替换为匹配的生成列。对于 BETWEEN 和 IN()，只有第一个参数可以替换为匹配的生成列，其他参数必须具有相同的结果类型。 BETWEEN 和 IN() 尚不支持涉及 JSON 值的比较。
生成列必须定义为包含至少一个函数调用或前一项中提到的某个运算符的表达式。表达式不能仅包含对另一列的简单引用。例如，gc INT AS (f1) STORED 仅包含一个列引用，因此不会考虑 gc 上的索引。
对于将字符串与计算来自返回带引号字符串的 JSON 函数的值的索引生成列进行比较，需要在列定义中使用 JSON_UNQUOTE() 从函数值中删除额外的引号。（对于字符串与函数结果的直接比较，JSON 比较器会处理引号删除，但索引查找不会发生这种情况。）例如，不要编写如下所示的列定义
```
doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED
```
请像这样编写
```
doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
```
使用后一种定义，优化器可以检测这两个比较的匹配
```
... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...
```
如果列定义中没有 JSON_UNQUOTE()，则优化器只会为第一个比较找到匹配项。
如果优化器选择了错误的索引，则可以使用索引提示来禁用它，并强制优化器做出不同的选择。