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

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.3 优化和索引 / 10.3.11 优化器使用生成的列索引

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.3.11 优化器使用生成列索引10.3.11 Optimizer Use of Generated Column Indexes

MySQL 支持在生成列上创建索引。例如：MySQL supports indexes on generated columns. For example:

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

生成的列 gc 被定义为表达式 f1 + 1。该列也被建立了索引，优化器在构建执行计划时可以考虑该索引。在以下查询中，WHERE 子句引用了 gc，优化器会考虑该列上的索引是否能产生更高效的执行计划：The generated column, gc, is defined as the expression f1 + 1. The column is also indexed and the optimizer can take that index into account during execution plan construction. In the following query, the WHERE clause refers to gc and the optimizer considers whether the index on that column yields a more efficient plan:

SELECT * FROM t1 WHERE gc > 9;

优化器可以使用生成列上的索引来生成执行计划，即使查询中没有直接按名称引用这些列。如果 WHERE、ORDER BY 或 GROUP BY 子句引用的表达式与某个已建立索引的生成列的定义匹配，也会发生这种情况。以下查询未直接引用 gc，但确实使用了与 gc 定义匹配的表达式：The optimizer can use indexes on generated columns to generate execution plans, even in the absence of direct references in queries to those columns by name. This occurs if the WHERE, ORDER BY, or GROUP BY clause refers to an expression that matches the definition of some indexed generated column. The following query does not refer directly to gc but does use an expression that matches the definition of gc:

SELECT * FROM t1 WHERE f1 + 1 > 9;

优化器识别出表达式 f1 + 1 与 gc 的定义匹配，并且 gc 已建立索引，因此在构建执行计划时会考虑该索引。你可以使用 EXPLAIN 查看这一点：The optimizer recognizes that the expression f1 + 1 matches the definition of gc and that gc is indexed, so it considers that index during execution plan construction. You can see this using 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查看：In effect, the optimizer has replaced the expression f1 + 1 with the name of the generated column that matches the expression. That is also apparent in the rewritten query available in the extended EXPLAIN information displayed by 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)

以下限制和条件适用于优化器使用生成列索引：The following restrictions and conditions apply to the optimizer's use of generated column indexes:

要使查询表达式与生成列定义匹配，该表达式必须完全相同，并且必须具有相同的结果类型。例如，如果生成列表达式是f1 + 1，则如果查询使用1 + f1，或者将一个整数表达式f1 + 1与字符串进行比较，优化器都不会识别为匹配。For a query expression to match a generated column definition, the expression must be identical and it must have the same result type. For example, if the generated column expression is f1 + 1, the optimizer does not recognize a match if the query uses 1 + f1, or if f1 + 1 (an integer expression) is compared with a string.
该优化适用于以下运算符：=、<、<=、>、>=、BETWEEN 和 IN()。The optimization applies to these operators: =, <, <=, >, >=, BETWEEN, and IN().
对于除了 BETWEEN 和 IN() 之外的运算符，任一操作数都可以被匹配的生成列替换。对于 BETWEEN 和 IN()，只有第一个参数可以被匹配的生成列替换，其他参数必须具有相同的结果类型。BETWEEN 和 IN() 目前尚不支持涉及 JSON 值的比较。For operators other than BETWEEN and IN(), either operand can be replaced by a matching generated column. For BETWEEN and IN(), only the first argument can be replaced by a matching generated column, and the other arguments must have the same result type. BETWEEN and IN() are not yet supported for comparisons involving JSON values.
生成列必须定义为至少包含一个函数调用或前一项中提到的运算符的表达式。表达式不能仅由对另一个列的简单引用组成。例如，gc INT AS (f1) STORED 仅由列引用组成，因此对 gc 的索引不被考虑。The generated column must be defined as an expression that contains at least a function call or one of the operators mentioned in the preceding item. The expression cannot consist of a simple reference to another column. For example, gc INT AS (f1) STORED consists only of a column reference, so indexes on gc are not considered.
对于将字符串与计算自返回带引号字符串的 JSON 函数值的索引生成列进行比较的情况，需要在列定义中使用 JSON_UNQUOTE() 来去除函数值中的额外引号。（对于将字符串与函数结果进行直接比较，JSON 比较器会处理引号移除，但对于索引查找则不会发生此操作。）例如，不要这样写列定义：For comparisons of strings to indexed generated columns that compute a value from a JSON function that returns a quoted string, JSON_UNQUOTE() is needed in the column definition to remove the extra quotes from the function value. (For direct comparison of a string to the function result, the JSON comparator handles quote removal, but this does not occur for index lookups.) For example, instead of writing a column definition like this:
```
doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED
```
这样写：Write it like this:
```
doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
```
使用后一种定义，优化器可以检测到这两次比较的匹配：With the latter definition, the optimizer can detect a match for both of these comparisons:
```
... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...
```
在列定义中如果没有 JSON_UNQUOTE()，优化器只会检测到这些比较中的第一个匹配。Without JSON_UNQUOTE() in the column definition, the optimizer detects a match only for the first of those comparisons.
如果优化器选择了错误的索引，可以使用索引提示来禁用它，并强制优化器做出不同的选择。If the optimizer picks the wrong index, an index hint can be used to disable it and force the optimizer to make a different choice.

首页上一级下一节