mysql 多值索引详解

最新推荐文章于 2024-04-26 13:29:49 发布
最新推荐文章于 2024-04-26 13:29:49 发布
阅读量406 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jkzyx123/article/details/134243362
版权

一、多值索引

从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: 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

四、多值索引的特性

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

  • 影响多值索引的 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。)

半桶水专家
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql覆盖索引详解
12-16
概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作 判断标准 使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值 2、Hash 和full-text索引不存储值，因此MySQL只能使用B-TREE 3、并且不同的存储引擎实现覆盖索引都是不同的 4、并不是所有的存储引擎都支持它们 5、如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，
MySQL Innodb 索引原理详解
02-28
MySQL Innodb 索引原理详解
MySQL索引详解大全
02-01
1、索引 　索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。2.索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度索引的优点是可以提高检索数据的速度索引的缺点是创建和维护索引需要耗费时间索引可以提高查询速度，会减慢写入速度1.普通索引2.唯一索引3.全文索引4.单列索引5.多列索引6.空间索引7.主键索引8.组合索引
MySQL的索引详解
09-09
主要介绍了MySQL的索引的一些资料，对于mysql的优化来说索引是不得不说的，这里就为大家介绍一下，需要的朋友可以参考一下
Mysql Json字段 函数索引、多值索引使用
qvc180的博客
09-02 1330
Mysql Json字段索引
MySQL为JSON字段创建索引(Multi-Valued Indexes 多值索引)
我的博客
08-05 8600
从MySQL 8.0.17开始， InnoDB支持创建多值索引(Multi-Valued Indexes)，该索引是在JSON存储值数组的列上定义的二级索引，对于单个数据记录可以有多个索引记录。跟普通索引一样，也可以在`EXPLAIN`中查看到。
MYSQL json数组创建多值索引
ggggd233的博客
11-16 1710
MYSQL json数组创建多值索引有感 ##函数索引 ##多值索引
mysql8多值索引（Multi-Valued Indexes）使用方法和性能测试
chongcilingjian的专栏
01-28 3106
msyql8 多值索引（Multi-valued Indexes）的使用说明和性能测试
mysql explain解析一 extra中的using index，using where，using index condition
weixin_30486037的博客
07-28 308
1.简单介绍 using index 和using where只要使用了索引我们基本都能经常看到，而using index condition则是在mysql5.6后新加的新特性，我们先来看看mysql文档对using index condition的描述 附上mysql文档链接：https://dev.mysql.com/doc/refman/5.7/en/index-condition...
Mysql8 多值索引相同查询不同排序顺序结果不同问题
qvc180的博客
10-15 399
Mysql8 多值索引相同查询不同排序顺序结果不同问题
MySQL-数据库-索引详解
08-30
MySQL-数据库-索引详解
MySQL学习(四)之索引篇
weixin_65743593的博客
01-19 2061
MySQL索引，B树，B+树，为什么需要注意联合索引中的顺序？为什么要建立联合索引？或者说联合索引的好处？什么是最左前缀法则？什么情况索引会失效？
在MySQL中当有多个索引时 你知道MySQL是如何选择索引的吗 ？？？
代码小疯子
03-11 6124
MySQL 中一张表可以支持多个索引，在 SQL 语句也并不主动指定使用的索引，具体使用哪个索引是由 MySQL 自己来选择的。
MySQL索引合并Index Merge 一条SQL使用多个索引情况分析，以及索引合并失效情况
xzh_blog
04-23 3310
MySQL在5.0版本加入了索引合并优化（Index Merge），索引合并可以同时查询多个索引的范围扫描，并将其结果合并为一个。索引合并只可以合并来自单个表的索引扫描，而不支持跨多个表的扫描。合并可以生成其基础扫描的并集（unions）、交集（intersections）或交集的并集（unions-of-intersections）。
Mysql为json字段创建索引的两种方式
怪咖@的博客
04-29 7517
JSON 数据类型是在mysql5.7版本后新增的，同TEXT，BLOB字段一样，JSON 字段不允许直接创建索引。即使支持，实际意义也不大，因为我们一般是基于文档中的元素进行查询，很少会基于整个 JSON 文档。基于此问题，在MySQL 8.0.17及以后的版本中，InnoDB存储引擎支持JSON数组上的多值索引。除此之外还可以通过MySQL 5.7引入的虚拟列，然后在虚拟列当中使用索引。
深入理解MYSQL索引优化：多列索引
iqifenxia的博客
01-01 342
　　索引是什么 　　是存储引擎用于找到数据的一种数据结构。 　　MYSQL索引优化视频详解：「链接」 　　索引的性能 　　在数据量小的时候，一个坏的索引往往作用没有那么明显，但是在数据量比较大的时候一个坏的索引和好的索引有巨大的区别。 　　在查询优化的时候应该首先考虑索引优化。这个是最简单的，也是效果最好。 　　索引的执行流程 　　索引=> 索引值=> 数据行 　　mysql> explain select first_name from actor where act..
MySQL-多表查询
qq_57157023的博客
04-25 987
MySQL多表查询
Mysql_数据库事务
最新发布
weixin_69252724的博客
04-26 1180
事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于化学当中的原子（一种不可分割的最小单位）。往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。
88张表-Mysql
2301_80151755的博客
04-25 232
【代码】88张表-Mysql。
mysql 索引下推详解
09-16
MySQL索引下推是一种优化查询性能的技术。传统的索引使用方法是首先通过索引找到满足条件的记录，然后再对这些记录进行进一步的过滤。而索引下推则是在进行索引查询时，将过滤条件应用于索引的过程中，减少了需要...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值