文章/答案/技术大牛

发布

社区首页 >专栏 >MySQL 之 JSON 支持（二）—— JSON 索引

MySQL 之 JSON 支持（二）—— JSON 索引

用户1148526

发布于 2024-06-07 08:27:31

1.2K00

代码可运行

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

运行总次数：0

代码可运行

官方文档链接：

一、多值索引

从 MySQL 8.0.17 开始，InnoDB 支持多值索引。多值索引是在存储数组值的列上定义的辅助索引。“一般”索引对于每个数据记录有一个索引记录（1:1）。多值索引中单个数据记录可以具有多个索引记录（N:1）。多值索引用于对 JSON 数组进行索引。例如，在下面的 JSON 文档中，对邮政编码数组定义的多值索引为每个邮政编码创建一个索引记录，每个索引记录引用相同的数据记录。

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

1. 创建多值索引

可以在 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 语句中创建多值索引。这需要在索引定义中使用 CAST(... AS ... ARRAY)，它将 JSON 数组中相同类型的标量值强制转换为 SQL 数据类型的数组。然后使用 SQL 数据类型数组中的值透明地生成虚拟列；最后，在虚拟列上创建一个函数索引（也称为虚拟索引）。在 SQL 数据类型数组中的值的虚拟列上定义的函数索引，构成多值索引。

以下列表中的示例显示了在名为 customers 表的 custinfo JSON 列的 $.zipcode 数组上创建多值索引 zips 的三种不同方式。在每种情况下，JSON 数组都被强制转换为包含 UNSIGNED 整数值的 SQL 数据类型数组。

只建表

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
    );

建表后修改表

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

建表后建索引

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

多值索引也可以定义为复合索引的一部分。下面的示例显示了一个复合索引，它包括两个单值部分（对 id 和 modified 列）和一个多值部分（对 custinfo 列）：

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

一个复合索引中只能使用一个多值键部分。多值键部分可以相对于索引的其它部分以任何顺序使用。换句话说，刚才显示的 ALTER TABLE 语句可能使用 comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) 或任何其它排序，而且仍然有效。

2. 使用多值索引

在 WHERE 子句中指定以下函数时，优化器使用多值索引来获取记录：

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

可以通过使用以下 CREATE TABLE 和 INSERT 语句创建 customers 表并添加数据来演示这一点：

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

首先对 customers 表执行三个查询，每个查询分别使用 MEMBER OF()、JSON_CONTAINS() 和 JSON_OVERLAPS()，每个查询的结果如下所示：

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

下面对这三个查询中的每一个运行 EXPLAIN：

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

刚才显示的三个查询都不能使用任何键。为了解决这个问题，可以在 JSON 列（custinfo）的 zipcode 数组上添加一个多值索引，如下所示：

mysql> ALTER TABLE customers
    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

再来运行前面的 EXPLAIN 语句时，能看到查询可以（并且确实）使用刚创建的索引 zips：

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

多值索引可以定义为唯一键。如果定义为唯一键，当插入多值索引中已存在的值时会返回重复键错误。如果已经存在重复的值，则添加唯一的多值索引时会失败，如下所示：

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 多值索引的特性

多值索引具有以下附加特性：

影响多值索引的 DML 操作的处理方式与影响普通索引的 DML 操作相同，唯一的区别是单个聚集索引记录可能有多个插入或更新。
多值索引的可空性：
- 如果多值键部分具有空数组，则不会向索引中添加任何条目，并且无法通过索引扫描访问对应的数据记录。
- 如果多值键部分生成返回 NULL 值，则会将包含 NULL 的单个条目添加到多值索引中。如果键部分定义为 NOT NULL，则会报告错误。
- 如果类型数组列设置为 NULL，则存储引擎将存储一条包含指向数据记录的 NULL 的记录。
- 在被索引数组中不允许 JSON null 值。如果任何返回值为 NULL，则将其视为 JSON null，并报告 Invalid JSON value 错误。
因为多值索引是虚拟列上的虚拟索引，所以它们必须遵守与虚拟生成列上的辅助索引相同的规则。
不会为空数组添加索引记录。

4. 多值索引的限制

多值索引有以下限制：

每个多值索引只允许有一个多值键部分。但是，CAST(... AS ... ARRAY) 表达式可以引用 JSON 文档中的多个数组，如下所示：

CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)

在这种情况下，所有与 JSON 表达式匹配的值都作为单个扁平化数组存储在索引中。

具有多值键部分的索引不支持排序，因此不能用作主键。出于同样的原因，不能使用 ASC 或 DESC 关键字定义多值索引。

多值索引不能是覆盖索引。

多值索引的每条记录的最大值数由单个 undo log 页上可存储的数据量决定，该数据量为 65221 字节（64K 减去 315 字节的开销），这意味着键值的最大总长度也是 65221 字节。键的最大数量取决于各种因素，这会妨碍定义特定的限制。例如，测试表明，多值索引允许每条记录有多达 1604 个整数键。当达到限制时，会报告类似于以下的错误：错误3905（HY000）：ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s)。

多值键部分中唯一允许的表达式类型是 JSON 表达式。表达式不需要引用插入索引列的 JSON 文档中的现有元素，但其本身必须在语法上有效。

由于同一聚集索引中的索引记录分散在多值索引中，因此多值索引不支持范围扫描或仅索引扫描。

外键声明中不允许使用多值索引。

不能为多值索引定义索引前缀。

不能在转换为 BINARY 的数据上定义多值索引（参阅 CAST() 函数的描述）。

不支持在线创建多值索引，这意味着该操作使用了 ALGORITHM=COPY。参阅性能和空间要求。

多值索引不支持以下两种字符集和排序规则组合以外的字符集和排列规则：

具有默认二进制排序规则的二进制字符集。
使用默认 utf8mb4_0900_as_cs 排序规则的 utf8mb4 字符集。

与 InnoDB 表列上的其它索引一样，不能使用 USING HASH 创建多值索引；这样做会导致警告：This storage engine does not support the HASH index algorithm, storage engine default was used instead. (USING BTREE is supported as usual.)

二、辅助索引与生成列

InnoDB 的虚拟生成列上支持辅助索引，不支持其它索引类型。在虚拟列上定义的辅助索引有时被称为“虚拟索引”。

可以在一个或多个虚拟列上，或者在虚拟列和普通列的组合上，或者在存储的生成列上创建辅助索引。包括虚拟列的辅助索引可以定义为 UNIQUE。

在虚拟生成列上创建辅助索引时，生成的列值会物化到索引的记录中。如果索引是覆盖索引（包括查询检索的所有列），则生成的列值将从索引结构中的物化值中检索，而不是“动态”计算。

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

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

在虚拟列上添加或删除辅助索引是一种就地操作。

1. 为生成列创建索引以提供 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 语句中使用 EXPLAIN 时，如果该语句包含一个或多个使用 -> 或 ->> 运算符的表达式，则会使用 JSON_EXTRACT() 和（如果需要）JSON_UNQUOTE() 将这些表达式转换为等效表达式，如下面 EXPLAIN 语句后的 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 列。

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

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

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

NDB 将 JSON 列值作为 BLOB 在内部进行处理。这意味着，任何具有一个或多个 JSON 列的 NDB 表都必须有主键，否则它将无法记录在二进制日志中。
NDB 存储引擎不支持对虚拟列进行索引。由于生成列的默认值是 VIRTUAL，因此必须显式指定间接索引将要应用的生成列为 STORED。

用于创建此处显示的表 jempn 的 CREATE TABLE 语句是前面显示的 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)