MySQL :: MySQL 8.3 Reference Manual :: 10.3.11 Optimizer Use of Generated Column Indexes

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Optimizer Use of Generated Column Indexes

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()，则优化器仅检测到第一个比较的匹配项。
如果优化器选择了错误的索引，可以使用索引提示来禁用它，并强制优化器做出不同的选择。

PREV HOME UP NEXT