mysql 多值索引详解

news/2024/10/25 9:23:15/

一、多值索引

从MySQL 8.0.17开始，InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引。“正常”索引对于每个数据记录有一个索引记录（1:1）。多值索引可以为单个数据记录有多个索引记录（N:1）。多值索引主要用于索引JSON数组。例如，在以下JSON文档中的邮编数组上定义的多值索引为每个邮编创建一个索引记录，每个索引记录都引用相同的数据记录。

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

二、创建多值索引

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

以下列表中的示例展示了三种不同的方式，可以在名为 customers 的表中的 JSON 列 custinfo 上的数组 $.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))（或任何其他排序）并且仍然有效。

三、使用多值索引

当在 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）的邮编数组上添加一个多值索引，如下所示：

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: 0mysql> 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

四、多值索引的特性

多值索引还具有以下列出的额外特性：

  • 影响多值索引的 DML 操作与影响正常索引的 DML 操作处理方式相同，唯一的不同是对单个聚簇索引记录可能有多于一个的插入或更新。

  • 空值和多值索引：

    • 如果多值键部分有一个空数组，则不会向索引添加条目，并且数据记录不能通过索引扫描访问。
    • 如果多值键部分生成返回 NULL 值，则在多值索引中添加包含 NULL 的单个条目。如果键部分定义为 NOT NULL，则报告错误。
    • 如果将类型化数组列设置为 NULL，则存储引擎存储一个包含 NULL 的单个记录，该记录指向数据记录。
    • 索引数组中不允许使用 JSON null 值。如果任何返回值为 NULL，它被视为 JSON null 并报告无效的 JSON 值错误。
    • 因为多值索引是虚拟列上的虚拟索引，所以它们必须遵守虚拟生成列上的二级索引的相同规则。
    • 对于空数组，不添加索引记录。

五、多值索引的限制和约束

多值索引受到下列限制和约束：

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

在这种情况下，所有匹配 JSON 表达式的值都以单个平面数组的形式存储在索引中。

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

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

  • 对于多值索引，每个记录的最大值数量由单个撤销日志页可以存储的数据量决定，即65221字节（64K减去315字节的开销），这意味着键值的最大总长度也是65221字节。键的最大数量取决于各种因素，这阻止了定义特定的限制。测试已经显示，例如，一个多值索引允许每个记录最多1604个整数键。当达到限制时，会报告类似以下的错误：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。请参阅性能和空间需求。

  • 除了以下两种字符集和排序的组合外，其他字符集和排序不支持多值索引：

    • binary字符集与默认的binary排序
    • utf8mb4字符集与默认的utf8mb4_0900_as_cs排序。

  • 就像InnoDB表的列上的其他索引一样，不能用USING HASH创建多值索引；尝试这样做会产生警告：This storage engine does not support the HASH index algorithm, storage engine default was used instead. (如常规一样支持USING BTREE。)


http://www.ppmy.cn/news/1200034.html

相关文章

YOLO v2（2016.12）

YOLO v2（2016.12）

文章目录 AbstractIntroduction当前存在的困境基于此，我们给出了方法 BetterBatch NormalizationHigh Resolution ClassifierConvolutional With Anchor BoxesDimension ClustersDirect location predictionFine-Grained FeaturesMulti-Scale TrainingFurther Exper…
阅读更多...
Android Studio(RecyclerView)

Android Studio(RecyclerView)

前言 ListView的缺点&#xff0c;在RecyclerView得到了补充改善&#xff08;横纵向排列子元素、多列布局等等&#xff09; 代码 前面在适配器章节已经介绍了其对应的适配器&#xff0c;这里就简单展示一下多列布局的页面效果和相关代码 <androidx.recyclerview.widget.Recyc…
阅读更多...
mpv播放器input.conf配置，解决方向键快进快退无效的问题（总是跳到关键帧）

mpv播放器input.conf配置，解决方向键快进快退无效的问题（总是跳到关键帧）

关键在于 LEFT no-osd seek -5 exact #方向键左 后退 RIGHT no-osd seek 5 exact #方向键右 前进 要写 no-osd和exact&#xff0c;网上搜到的input.conf都是套话废话&#xff0c;都没有写这两个关键词 mpv.conf ##解码/渲染设置###视频输出驱动&#xff0c;Windows可选…
阅读更多...
浅析LiveMedia智能视频网关的AI识别技术及应用场景

浅析LiveMedia智能视频网关的AI识别技术及应用场景

一、行业背景 &#xff08;1&#xff09;AI技术在安防领域大量落地应用 随着近几年人工智能的快速发展&#xff0c;深度学习方法及性能日益提升&#xff0c;计算机视觉、图像处理、视频结构化和大数据分析等技术也不断完善&#xff0c;使得安防产品逐步走向智能化。在技术成熟…
阅读更多...
在idea命令行，or linux 终端执行命令，快速获取通过脚本上证指数、创业板实时行情

在idea命令行，or linux 终端执行命令，快速获取通过脚本上证指数、创业板实时行情

脚本编写 编写shell脚本如下，并保存命名为stock curl http://hq.sinajs.cn/list=s_sh000001 -H User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:70.0) Gecko/20100101 Firefox/70.0 -H Accept: */* -H Accept-Language: en-US,en;q=0.5 --compressed -H Referer…
阅读更多...
LeetCode--571. 给定数字的频率查询中位数

LeetCode--571. 给定数字的频率查询中位数

文章目录 1 题目描述2 测试用例3 解题思路3.2 解法 1 相似题目 1 题目描述 表: Employee ----------------------- | Column Name | Type | ----------------------- | id | int | | company | varchar | | salary | int | ---------------…
阅读更多...
使用new_zeros和zeros_like分别返回与输入相同尺寸/类型/device的tensor和ndarray

使用new_zeros和zeros_like分别返回与输入相同尺寸/类型/device的tensor和ndarray

torch.Tensor.new_zeros(size, dtypeNone, deviceNone) 返回尺寸为size的全为0的tensor&#xff0c;默认&#xff0c;返回的tensor与该tensor具有相同的dtype和device&#xff0c;可以用于在模型训练过程中创建新tensor&#xff0c;并保证该tensor在对应的device上 1&#xff0…
阅读更多...
Go 中的反射

Go 中的反射

本教程有以下部分。 什么是反射&#xff1f;需要检查变量并查找其类型吗&#xff1f;反射包 反射类型和反射值反射.KindNumField() 和 Field() 方法Int() 和 String() 方法 完整的程序是否应该使用反射&#xff1f; 现在让我们一一讨论这些部分。 什么是反射&#xff1f; 反…
阅读更多...
最新文章

Copyright @ 2022~2023