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

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 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，优化器不recognize 匹配如果查询使用1 + f1，或将f1 + 1(整数表达式)与字符串进行比较。
优化操作适用于以下操作符：=，<，<=，>，>=，BETWEEN和IN()。

对于除BETWEEN和IN()之外的操作符，任何一个操作数都可以被替换为匹配生成列。对于BETWEEN和IN()，只能将第一个参数替换为匹配生成列，而其他参数必须具有相同的结果类型。BETWEEN和IN()尚未支持JSON值的比较操作。
生成列必须被定义为包含至少一个函数调用或前一项中提到的操作符的表达式。该表达式不能仅是一个简单的对另一个列的引用。例如，gc INT AS (f1) STORED只包含一个列引用，因此在gc上创建索引将被忽略。
对于字符串与计算JSON函数返回带引号的值的索引生成列的比较，需要在列定义中使用JSON_UNQUOTE()将函数值中的额外引号删除。 (对于直接比较字符串与函数结果，JSON comparator会自动移除引号，但这不适用于索引查找。) 例如，可以将列定义改为：
```
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