Mysql为json字段创建索引的两种方式
目录
一、前言
JSON 数据类型是在mysql5.7
版本后新增的,同 TEXT,BLOB
字段一样,JSON 字段不允许直接创建索引。即使支持,实际意义也不大,因为我们一般是基于文档中的元素进行查询,很少会基于整个 JSON 文档。基于此问题,在MySQL 8.0.17
及以后的版本中,InnoDB存储引擎支持JSON数组上的多值索引
。除此之外还可以通过MySQL 5.7
引入的虚拟列
,然后在虚拟列当中使用索引。
二、通过虚拟列添加索引(Secondary Indexes and Generated Columns)
- InnoDB支持在虚拟生成的列上建立二级索引。不支持其他索引类型(主键索引)。在虚拟列上定义的二级索引有时也称为“
虚拟索引
”。 - 二级索引可以在一个或多个虚拟列上创建,也可以在虚拟列与常规列或存储生成列的组合上创建。包含虚拟列的二级索引可以定义为
UNIQUE
。 - 当在虚拟列上使用辅助索引时,由于在
INSERT和UPDATE
操作期间在辅助索引(辅助又叫二级索引)记录中实现虚拟列值时执行计算,因此需要考虑额外的写成本。即使有额外的写成本,虚拟列上的二级索引也可能比生成的存储列更可取,生成的存储列在集群索引中具体化,从而导致需要更多磁盘空间和内存的更大的表。如果没有在虚拟列上定义二级索引,则会产生额外的读取成本,因为每次检查列的行时都必须计算虚拟列值。
关于什么是二级索引:https://blog.csdn.net/weixin_43888891/article/details/126073266
语法:ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 GENERATED ALWAYS as (表达式) [VIRTUAL | STORED];
MySQL 在处理 虚拟列存储问题的时候有两种方式:
- VIRTUAL(默认):不存储列值,在读取表的时候自动计算并返回,不消耗任何存储,这种存储方式仅 InnoDB 支持设置索引。
- STORED:在插入或更新时计算存储列值,存储的虚拟列需要存储空间,并且 MyISAM 也可以设置索引。
创建虚拟列可以在创建表的时候指定也可以在创建表过后指定。
如下示例就是通过创建表的时候指定的虚拟列,通过(c->"$.id")
表达式创建 了一个虚拟列g
,并且对虚拟列g
创建了索引,通过以下执行计划可以看出索引在查询 的时候已经生效了。
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: 0mysql> 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: rangepossible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003Message: 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执行计划解析:
SHOW WARNINGS
可以显示上一个命令的警告信息,以及真正执行的sql语句。
->>等价于json_unquote(json_extract())
在MySQL 8.0.21及更高版本中,还可以使用
JSON_VALUE()
函数在JSON列上创建索引,该函数带有一个表达式,可用于优化使用该表达式的查询。
三、多值索引(Using multi-valued Indexes)
多值的索引从MySQL 8.0.17
开始,InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引
。“普通”索引对每个数据记录有一个索引记录(1:1)。一个多值索引对于一个数据记录(N:1)可以有多个索引记录。多值索引用于索引JSON数组。
例如,在下面的JSON文档中,我们要对zipcode添加一个索引:
{ "user":"Bob", "user_id":31, "zipcode":[94477,94536]}
三种创建多值索引的方式: CREATE TABLE, ALTER TABLE, or CREATE INDEX
方式一:CREATE TABLE
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)) ));
方式二:ALTER TABLE
语法:ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );
注意:这里在CAST语法外面有两层单括号!,如果少写一个会报错!
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 INDEX
CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
准备好测试数据,然后使用上面任意一种方式创建出来索引:
INSERT INTO customersVALUES( 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]}' );
想要多值索引生效的条件是 where条件下使用了以下三个函数:
- MEMBER OF():查看数组是否有某个元素,如果有则该函数返回 1,否则返回 0。
语法:元素 value MEMBER OF(json_array)
- JSON_CONTAINS():该函数用于检验指定 JSON 文档是否包含在目标 JSON 文档中,或者是否在目标文档的指定路径上找到指定元素(如果提供了 path参数)。如果指定 JSON 文档包含在目标 JSON 文档中,该函数返回 1,否则返回 0。
语法:JSON_CONTAINS(target, candidate[, path])
- JSON_OVERLAPS():该函数用于比较两个 JSON 文档。如果两个文档具有共同的键值对(key-value)或数组元素(不要求全部一样,只要一个键值对一样就可以),则返回 1,否则返回 0。
语法:JSON_OVERLAPS(json_doc1, json_doc2)
EXPLAIN SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');EXPLAIN SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));EXPLAIN SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
执行结果如下,可以看到是使用了索引的:
使用的时候需要注意的:
- 多值索引可以定义为唯一键,不能作为主键,和外键。
- 可以作为组合索引使用
- 不支持utf8mb4编码配合utf8mb4_0900_as_cs排序规则使用,不支持默认的二进制排序规则和字符集。
- 多值索引不能是覆盖索引。
- 不能为多值索引定义索引前缀。
覆盖索引:索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫 做覆盖索引。
前缀索引:所谓前缀索引说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时指定),这样建立起来的索引更小,所以查询更快。这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引,只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用 Left 函数。
那么为什么不对整个字段建立索引呢?一般来说使用前缀索引,可能都是因为整个字段的数据量太大,没有必要针对整个字段建立索引,前缀索引仅仅是选择一个字段的部分字符作为索引,这样一方面可以节约索引空间,另一方面则可以提高索引效率,当然很明显,这种方式也会降低索引的选择性。
四、官网地址
关于虚拟列索引官网叙述:https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html
关于多值索引官网叙述:https://dev.mysql.com/doc/refman/8.0/en/create-index.html
来源地址:https://blog.csdn.net/weixin_43888891/article/details/130419850
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341