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
, or
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
对于and 以外的运算符IN()
,任一操作数都可以替换为匹配的生成列。对于BETWEEN
andIN()
,只有第一个参数可以替换为匹配的生成列,其他参数必须具有相同的结果类型。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()
列定义中,优化器只会检测第一个比较的匹配项。如果优化器选择了错误的索引,可以使用索引提示来禁用它并强制优化器做出不同的选择。