MySQL :: MySQL 8.4 Reference Manual :: 15.1.20.9 Secondary Indexes and Generated Columns

Documentation Home

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Secondary Indexes and Generated Columns

15.1.20.9 二级索引和生成的列

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

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

当创建在虚拟生成列上的二级索引时，生成列值将在索引记录中物化。如果该索引是覆盖索引（包含查询检索的所有列），那么生成列值将从索引结构中获取，而不是实时计算 “实时”。

使用虚拟列的次要索引时，还需要考虑额外的写入成本，因为在INSERT和UPDATE操作中，虚拟列值在次要索引记录中进行计算。即使存在额外的写入成本，虚拟列的次要索引也可能优于生成的存储列，因为后者在聚簇索引中materialized，导致表格更大，需要更多磁盘空间和内存。如果不定义次要索引，虚拟列值每次被检索时都需要计算。

虚拟列的索引值将MVCC日志，以避免在回滚或清除操作中重新计算生成列值。logged 值的数据长度受限于767字节（COMPACT和REDUNDANT行格式）或3072字节（DYNAMIC和COMPRESSED行格式）。

在虚拟列上添加或删除次要索引是原地操作。

如前所述，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)

(为了适应浏览器窗口，我们将上一个语句的输出进行了折叠。)

当你使用EXPLAIN语句对包含一个或多个使用->或->>运算符的SELECT或其他SQL语句时，这些表达式将被翻译成使用JSON_EXTRACT()和（如果需要）JSON_UNQUOTE()的等价形式，例如在SHOW WARNINGS语句中显示：

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列。

也可以使用JSON列上的函数JSON_VALUE()来创建索引，以优化使用该表达式的查询。请查看该函数的描述和示例。

JSON columns and indirect indexing in NDB Cluster

NDB将JSON列值内部处理为BLOB。因此，任何一个或多个JSON列的NDB表都必须有主键，否则不能记录到二进制日志中。
NDB存储引擎不支持虚拟列的索引。由于默认情况下生成列是VIRTUAL，因此必须明确指定要应用间接索引的生成列为STORED。

使用以下语句创建的表格jempn，是之前显示的jemp表格的版本，经过修改以适应NDB:

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语句来填充该表格：

INSERT INTO jempn (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"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,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。

PREV HOME UP NEXT