Documentation Home
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.1 数据定义语句  / 13.1.20 CREATE TABLE 语句  /  13.1.20.9 二级索引和生成的列

13.1.20.9 二级索引和生成的列

InnoDB支持虚拟生成列的二级索引。不支持其他索引类型。在虚拟列上定义的二级索引有时称为虚拟索引

二级索引可以在一个或多个虚拟列上创建,或者在虚拟列和常规列或存储生成列的组合上创建。包含虚拟列的二级索引可以定义为UNIQUE.

当在虚拟生成列上创建二级索引时,生成列值将具体化在索引的记录中。如果索引是 覆盖索引(包含查询检索到的所有列的索引),则生成的列值是从索引结构中的物化值中检索的,而不是即时计算的。

INSERT由于在和 UPDATE操作 期间在二级索引记录中具体化虚拟列值时执行的计算,在虚拟列上使用二级索引时需要考虑额外的写入成本。即使有额外的写入成本,虚拟列上的二级索引也可能比生成的存储列更可取,后者在聚集索引中具体化,从而导致需要更多磁盘空间和内存的更大表。如果未在虚拟列上定义二级索引,则会产生额外的读取成本,因为每次检查列的行时都必须计算虚拟列值。

索引虚拟列的值记录在 MVCC 中,以避免在回滚或清除操作期间对生成的列值进行不必要的重新计算。记录值的数据长度受索引键限制,行格式为 767 字节 , COMPACTREDUNDANT格式为 3072 字节。 DYNAMICCOMPRESSED

在虚拟列上添加或删除二级索引是就地操作。

索引生成的列以提供 JSON 列索引

正如别处所指出的,JSON 不能直接对列进行索引。要创建间接引用此类列的索引,您可以定义一个生成的列来提取应建立索引的信息,然后在生成的列上创建索引,如本例所示:

Press CTRL+C to copy
mysql> CREATE TABLE jemp ( -> c JSON, -> g INT GENERATED ALWAYS AS (c->"$.id"), -> INDEX i (g) -> ); Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO jemp (c) VALUES > ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), > ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}'); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT c->>"$.name" AS name > FROM jemp WHERE g > 2; +--------+ | name | +--------+ | Barney | | Betty | +--------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT c->>"$.name" AS name > FROM jemp WHERE g > 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jemp partitions: NULL type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) 1 row in set (0.00 sec)

(我们包装了本例中最后一条语句的输出以适合查看区域。)

当您EXPLAINSELECT包含一个或多个使用 ->or运算符的表达式的 SQL 语句或其他 SQL 语句上使用时,这些表达式将使用and(如果需要)->> 转换为它们的等价物,如紧接此 语句 后的输出所示:JSON_EXTRACT()JSON_UNQUOTE()SHOW WARNINGSEXPLAIN

Press CTRL+C to copy
mysql> EXPLAIN SELECT c->>"$.name" > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jemp partitions: NULL type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS `c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by json_extract(`test`.`jemp`.`c`,'$.name') 1 row in set (0.00 sec)

有关其他信息和示例, 请参阅 -> and ->> 运算符以及 JSON_EXTRACT()and 函数的描述。JSON_UNQUOTE()

该技术还可用于提供索引,这些索引间接引用不能直接索引的其他类型的列,例如GEOMETRY列。

在 MySQL 8.0.21 及更高版本中,还可以JSON使用带有表达式的函数在列上创建索引,JSON_VALUE()该表达式可用于优化使用该表达式的查询。有关更多信息和示例,请参阅该函数的描述。

NDB Cluster 中的 JSON 列和间接索引

也可以在 MySQL NDB Cluster 中使用 JSON 列的间接索引,但要满足以下条件:

  1. NDBJSON在内部将列值处理 为BLOB. 这意味着任何NDB具有一个或多个 JSON 列的表都必须有一个主键,否则它不能记录在二进制日志中。

  2. NDB存储引擎不支持虚拟列的索引 。由于生成列的默认值为VIRTUAL,因此您必须明确指定将间接索引应用到的生成列为STORED

CREATE TABLE用于创建此处显示的表 的语句jempn是先前显示的表的一个版本jemp,并进行了修改使其兼容 NDB

Press CTRL+C to copy
CREATE TABLE jempn ( a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c JSON DEFAULT NULL, g INT GENERATED ALWAYS AS (c->"$.id") STORED, INDEX i (g) ) ENGINE=NDB;

我们可以使用以下 INSERT语句填充此表:

Press CTRL+C to copy
INSERT INTO jempn (c) VALUES ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'), ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');

现在NDB可以使用 index i,如下所示:

Press CTRL+C to copy
mysql> EXPLAIN SELECT c->>"$.name" AS name -> FROM jempn WHERE g > 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jempn partitions: p0,p1,p2,p3 type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using pushed condition (`test`.`jempn`.`g` > 2) 1 row in set, 1 warning (0.01 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from `test`.`jempn` where (`test`.`jempn`.`g` > 2) 1 row in set (0.00 sec)

您应该记住,存储的生成列以及此类列上的任何索引都使用 DataMemory.