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")

表达式创建了一个虚拟列

，并且对虚拟列

创建了索引，通过以下执行计划可以看出索引在查询的时候已经生效了。

mysql>CREATETABLE jemp (->     c JSON,->     g INT GENERATED ALWAYS AS(c->"$.id"),->INDEX i (g)->);
Query OK,0rows affected (0.28 sec)

mysql>INSERTINTO jemp (c)VALUES>('{"id": "1", "name": "Fred"}'),('{"id": "2", "name": "Wilma"}'),>('{"id": "3", "name": "Barney"}'),('{"id": "4", "name": "Betty"}');
Query OK,4rows affected (0.04 sec)
Records: 4  Duplicates: 0Warnings: 0

mysql>SELECT c->>"$.name"AS name FROM jemp WHERE g >2;+--------+| name   |+--------+| Barney || Betty  |+--------+2rowsinset(0.00 sec)

mysql>EXPLAINSELECT c->>"$.name"AS name FROM jemp WHERE g >2\G
***************************1.row***************************
           id: 1
  select_type: SIMPLEtable: jemp
   partitions: NULLtype: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULLrows: 2
     filtered: 100.00
        Extra: Usingwhere1rowinset,1 warning (0.00 sec)

mysql>SHOWWARNINGS\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)1rowinset(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

CREATETABLE customers (
    id BIGINTNOTNULLAUTO_INCREMENTPRIMARYKEY,
    modified DATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    custinfo JSON,INDEX zips((CAST(custinfo->'$.zipcode'ASUNSIGNED ARRAY))));

方式二：ALTER TABLE

语法：

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );

注意：这里在CAST语法外面有两层单括号！，如果少写一个会报错！

CREATETABLE customers (
    id BIGINTNOTNULLAUTO_INCREMENTPRIMARYKEY,
    modified DATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    custinfo JSON
);ALTERTABLE customers ADDINDEX zips((CAST(custinfo->'$.zipcode'ASUNSIGNED ARRAY)));

方式三：CREATE INDEX

CREATEINDEX zips ON customers ((CAST(custinfo->'$.zipcode'ASUNSIGNED ARRAY)));

准备好测试数据，然后使用上面任意一种方式创建出来索引：

INSERTINTO 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]}');

想要多值索引生效的条件是 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)

EXPLAINSELECT*FROM customers WHERE94507 MEMBER OF(custinfo->'$.zipcode');EXPLAINSELECT*FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON));EXPLAINSELECT*FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]'AS JSON));

执行结果如下，可以看到是使用了索引的：

在这里插入图片描述
使用的时候需要注意的：

多值索引可以定义为唯一键，不能作为主键，和外键。
可以作为组合索引使用
不支持utf8mb4编码配合utf8mb4_0900_as_cs排序规则使用，不支持默认的二进制排序规则和字符集。
多值索引不能是覆盖索引。
不能为多值索引定义索引前缀。

覆盖索引：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

前缀索引：所谓前缀索引说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时指定），这样建立起来的索引更小，所以查询更快。这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。
那么为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性。