MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (11)

林员外聊编程 2020-08-21

258

二级索引和生成列

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

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

在虚拟生成列上创建二级索引时，生成列值在索引的记录中具体化。如果索引是一个覆盖索引（包含一个查询检索到的所有列），则生成列的值将从索引结构中的具体化值中检索，而不是“动态”地计算。

在虚拟列上使用二级索引时，需要考虑额外的写成本，这是由于在插入和更新操作期间在二级索引记录中物化虚拟列值时所执行的计算。即使有额外的写开销，虚拟列上的二级索引可能比生成的存储列更可取，这些存储列被物化在聚集索引中，导致需要更多磁盘空间和内存的更大的表。如果在虚拟列上没有定义二级索引，则会有额外的读取成本，因为每次检查列的行时都必须计算虚拟列的值。

索引虚拟列的值将被MVCC记录，以避免在回滚或清除操作期间不必要地重新计算生成列的值。记录值的数据长度受索引键限制，COMPACT 和 REDUNDANT 行格式为767字节，DYNAMIC 和 COMPRESSED 行格式为3072字节。

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

对生成列进行索引，以提供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)
复制

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

如果 SELECT 或其他 SQL 语句包含一个或多个使用 -> or ->> 操作符的表达式，在这些 SQL 上使用 EXPLAIN 时，这些表达式使用 JSON_EXTRACT() 和(如需要) JSON_UNQUOTE() 翻译成他们的等价语句，如下所示是 EXPLAIN 语句后立即执行 SHOW WARNINGS 的输出:

此技术还可为不能直接索引的其他类型的间接引用列提供索引，例如 GEOMETRY 列。

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)
复制

MySQL 8.0.21 及以后版本中，在 JSON 列上创建索引，还可以使用带有表达式的 JSON_VALUE() 函数，可用于优化使用该表达式的查询。

NDB集群中的JSON列和间接索引

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

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

2. NDB 存储引擎不支持对虚拟列进行索引。由于生成列默认是 VIRTUAL，因此必须显式地将生成列指定为 STORED，以便对其应用间接索引。

用于创建这里所示的表 jempn 的 CREATE TABLE 语句是前面所示的 jemp 表的一个版本，经过修改使其与 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语句填充此表：

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。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (11)

评论

相关阅读