8.3.10 优化使用生成的列索引
MySQL 支持在生成的列上构建索引。例如:
CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));
生成的列gc
定义为 table 达式f1 + 1
。该列也已构建索引,优化程序可以在执行计划构建期间考虑该索引。在以下查询中,WHERE
子句引用gc
,并且优化器考虑该列上的索引是否产生更有效的计划:
SELECT * FROM t1 WHERE gc > 9;
即使在查询中没有按名称直接引用那些列的情况下,优化器也可以使用所生成列的索引来生成执行计划。如果WHERE
,ORDER BY
或GROUP BY
子句引用的 table 达式与某些索引生成的列的定义匹配,则会发生这种情况。以下查询不直接引用gc
,而是使用与gc
的定义匹配的 table 达式:
SELECT * FROM t1 WHERE f1 + 1 > 9;
优化器认识到 table 达式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
实际上,优化器已将 table 达式f1 + 1
替换为与 table 达式匹配的生成列的名称。在由SHOW WARNINGS显示的扩展EXPLAIN信息中可用的重写查询中,这也很明显:
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)
以下限制和条件适用于优化器对生成的列索引的使用:
-
为了使查询 table 达式与生成的列定义匹配,该 table 达式必须相同并且其结果类型必须相同。例如,如果生成的列 table 达式为
f1 + 1
,则如果查询使用1 + f1
或将f1 + 1
(整数 table 达式)与字符串进行比较,则优化器将无法识别匹配项。
对于BETWEEN和IN()以外的运算符,可以用匹配的生成列替换任何一个操作数。对于BETWEEN和IN(),只能将第一个参数替换为生成的匹配列,而其他参数必须具有相同的结果类型。涉及 JSON 值的比较尚不支持BETWEEN和IN()。
-
必须将生成的列定义为至少包含一个函数调用或前一项中提到的运算符之一的 table 达式。该 table 达式不能包含对另一列的简单引用。例如,
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()的情况下,优化器仅针对这些比较中的第一个比较检测到匹配项。
- 如果优化器无法选择所需的索引,则可以使用索引提示来强制优化器做出其他选择。