13.1.18.8 次要索引和生成的列
InnoDB
支持虚拟生成的列上的二级索引。不支持其他索引类型。在虚拟列上定义的辅助索引有时称为“虚拟索引”。
可以在一个或多个虚拟列上或在虚拟列和常规列或存储的生成列的组合上创建二级索引。包含虚拟列的二级索引可以定义为UNIQUE
。
在虚拟生成的列上创建二级索引时,生成的列值将在索引 Logging 实现。如果索引是covering index(包括查询所检索的所有列的索引),则从索引结构中的物化值中检索生成的列值,而不是“即时”计算。
由于在INSERT和UPDATE操作期间在二级索引 Logging 实现虚拟列值时执行的计算,因此在虚拟列上使用二级索引时,还需要考虑其他写入成本。即使有额外的写入成本,虚拟列上的二级索引也可能比在聚集索引中实现的生成的“存储”列更可取,从而导致需要更多磁盘空间和内存的较大 table。如果未在虚拟列上定义二级索引,则读取会产生额外成本,因为每次检查列的行时都必须计算虚拟列值。
对索引的虚拟列的值进行 MVCC 记录,以避免在回滚或清除操作期间对生成的列值进行不必要的重新计算。记录值的数据长度受COMPACT
和REDUNDANT
行格式的索引键限制为 767 字节,而DYNAMIC
和COMPRESSED
行格式的索引键限制为 3072 字节。
在虚拟列上添加或删除二级索引是就地操作。
在 5.7.16 之前,外键约束不能引用在虚拟生成的列上定义的二级索引。
在 MySQL 5.7.13 和更早版本中,InnoDB
不允许在索引的已生成虚拟列的基列上使用级联引用动作定义外键约束。在 MySQL 5.7.14 中取消了此限制。
为生成的列构建索引以提供 JSON 列索引
如其他地方所述,不能直接为JSON列构建索引。要创建间接引用此类列的索引,您可以定义一个生成的列,该列提取应构建索引的信息,然后在生成的列上创建索引,如本示例所示:
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)
(在本示例中,我们包装了最后一条语句的输出以适合查看区域.)
MySQL 5.7.9 和更高版本支持->运算符。从 MySQL 5.7.13 开始支持->>运算符。
当您在SELECT或其他包含一个或多个使用->
或->>
运算符的 table 达式的 SQL 语句上使用EXPLAIN时,这些 table 达式将使用JSON_EXTRACT()
和(如果需要)JSON_UNQUOTE()
转换为它们的等效项,如SHOW WARNINGS的输出所示遵循此EXPLAIN
语句:
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)
有关其他信息和示例,请参见->和->>运算符以及JSON_EXTRACT()和JSON_UNQUOTE()函数的说明。
此技术还可用于提供间接引用无法直接构建索引的其他类型的列(例如GEOMETRY
列)的索引。
NDB 群集中的 JSON 列和间接索引
在以下情况下,也可以在 MySQL NDB Cluster 7.5.3 和更高版本中使用 JSON 列的间接索引:
-
NDB在内部将JSON列值作为BLOB处理。这意味着任何具有一个或多个 JSON 列的
NDB
table 都必须具有主键,否则无法将其记录在二进制日志中。 -
NDB存储引擎不支持虚拟列的索引。由于生成的列的默认值为
VIRTUAL
,因此您必须明确地将要应用间接索引的生成的列指定为STORED
。
用于创建此处显示的 tablejempn
的 CREATE TABLE
语句是先前显示的jemp
table 的版本,经过修改使其与NDB
兼容:
CREATE TABLE jempn (
a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
c JSON DEFAULT NULL,
g INT GENERATED ALWAYS AS (c->"$.name") STORED,
INDEX i (g)
) ENGINE=NDB;
我们可以使用以下INSERT语句填充此 table:
INSERT INTO jempn (a, c) VALUES
(NULL, '{"id": "1", "name": "Fred"}'),
(NULL, '{"id": "2", "name": "Wilma"}'),
(NULL, '{"id": "3", "name": "Barney"}'),
(NULL, '{"id": "4", "name": "Betty"}');
现在NDB
可以使用索引i
,如下所示:
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
type: range
possible_keys: i
key: i
key_len: 5
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
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`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)
1 row in set (0.00 sec)
您应该记住,存储的生成的列使用DataMemory,并且该列上的索引使用IndexMemory。