MySQL怎么为JSON字段创建索引

浅浅的花香味﹌ 2024-03-24 19:51 85阅读 0赞

#### 多值索引简介 ####

从**MySQL 8.0.17** 开始， InnoDB支持创建多值索引(Multi-Valued Indexes)，该索引是在JSON存储值数组的列上定义的二级索引，对于单个数据记录可以有多个索引记录。此类索引特定的语法定义：

CAST(expression AS type ARRAY)，例如CAST(data->'$.zipcode' AS UNSIGNED ARRAY)。 跟普通索引一样，也可以在EXPLAIN中查看到。

#### 创建多值索引 ####

跟其他索引一样，多值索引可以在建表时添加，也可以通过ALTER TABLE或者CREATE INDEX创建。

##### JSON对象字段索引 #####

语法

复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_mv_custinfo_list( ( </code><code>CAST</code><code>( custinfo -&gt; </code><code>'$.key'</code> <code>AS</code> <code>UNSIGNED array ) ) );</code> </td> 
 </tr> 
 </tbody> 
</table>

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

测试案例

> PS：文中的案例是参考官方文档中的案例，只是作为测试，所以在命名等方面并不怎么规范，实际开发过程中要严格遵守公司团队的开发规范，不要偷懒！

复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 </td> 
 <td> <code>DROP</code> <code>TABLE</code> <code>IF EXISTS `customers`;</code> <code>/*建表语句*/</code> <code>CREATE</code> <code>TABLE</code> <code>customers ( </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id </code><code>BIGINT</code> <code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT </code><code>PRIMARY</code> <code>KEY</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>modified DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code> <code>ON</code> <code>UPDATE</code> <code>CURRENT_TIMESTAMP</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo JSON </code><code>NOT</code> <code>NULL</code> <code>);</code> <code>/*插入写测试数据*/</code> <code>INSERT</code> <code>INTO</code> <code>customers</code> <code>VALUES</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'{"key":94582,"value":"asdf"}'</code> <code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'{"key":94568,"value":"gjgasdasdf"}'</code> <code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'{"key":94477,"value":"ghasdfsdf"}'</code> <code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'{"key":94536,"value":"hagsdfgdf"}'</code> <code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'{"key":94507,"value":"wasfgjdf"}'</code> <code>);</code> <code>/*添加多值索引*/</code> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_mv_custinfo_list( ( </code><code>CAST</code><code>( custinfo -&gt; </code><code>'$.key'</code> <code>AS</code> <code>UNSIGNED array)) );</code> <code>/*测试 MEMBER </code><code>OF</code> <code>语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>94507 MEMBER </code><code>OF</code> <code>( custinfo -&gt; </code><code>'$.key'</code> <code>);</code> <code>/*测试 JSON_CONTAINS 语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>JSON_CONTAINS(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo -&gt; </code><code>'$.key'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CAST</code><code>( </code><code>'[94582]'</code> <code>AS</code> <code>JSON ));</code> <code>/*测试 JSON_OVERLAPS 语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>JSON_OVERLAPS (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo -&gt; </code><code>'$.key'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CAST</code><code>( </code><code>'[94477]'</code> <code>AS</code> <code>JSON ));</code> </td> 
 </tr> 
 </tbody> 
</table>

查看执行计划发现可以使用到索引：

![MySQL怎么为JSON字段创建索引][MySQL_JSON]

如果需要给字符类型创建多值索引，则必须是utf8mb4字符集且排序规则是utf8mb4\_0900\_as\_cs，否则报错该版本不支持：

如果要为binary二进制字符串创建多值索引的话，则排序规则必须是binary，否则报错不支持。

![MySQL怎么为JSON字段创建索引][MySQL_JSON 1]

修改排序规则后可成功添加索引：

![MySQL怎么为JSON字段创建索引][MySQL_JSON 2]

##### JSON数组对象索引 #####

语法

已复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_mv_custinfo_list( ( </code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED array ) ) );</code> </td> 
 </tr> 
 </tbody> 
</table>

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

**测试案例**

已复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 </td> 
 <td> <code>DROP</code> <code>TABLE</code> <code>IF EXISTS `customers`;</code> <code>/*建表语句*/</code> <code>CREATE</code> <code>TABLE</code> <code>customers ( </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id </code><code>BIGINT</code> <code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT </code><code>PRIMARY</code> <code>KEY</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>modified DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code> <code>ON</code> <code>UPDATE</code> <code>CURRENT_TIMESTAMP</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo JSON </code><code>NOT</code> <code>NULL</code> <code>);</code> <code>/*插入写测试数据*/</code> <code>INSERT</code> <code>INTO</code> <code>customers</code> <code>VALUES</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'[{"key":94582},{"key":94536}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'[{"key":94568},{"key":94507},{"key":94582}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'[{"key":94477},{"key":94507}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'[{"key":94536}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, NOW(), </code><code>'[{"key":94507},{"key":94582}]'</code><code>);</code> <code>/*添加多值索引*/</code> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_mv_custinfo_list( ( </code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED array)) );</code> <code>/*测试 MEMBER </code><code>OF</code> <code>语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>94507 MEMBER </code><code>OF</code> <code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>);</code> <code>/*测试 JSON_CONTAINS 语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>JSON_CONTAINS(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo -&gt; </code><code>'$[*].key'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CAST</code><code>( </code><code>'[94582, 94507]'</code> <code>AS</code> <code>JSON ));</code> <code>/*测试 JSON_OVERLAPS 语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>JSON_OVERLAPS (</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo -&gt; </code><code>'$[*].key'</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>CAST</code><code>( </code><code>'[94477, 94582]'</code> <code>AS</code> <code>JSON ));</code> </td> 
 </tr> 
 </tbody> 
</table>

查看执行计划发现可以使用到索引：

![MySQL怎么为JSON字段创建索引][MySQL_JSON 3]

##### 在组合索引中创建多值索引 #####

语法

语法跟普通组合索引差不多，同样也遵守最左匹配原则：

复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_age_custinfo$list_modified</code> <code>( age, (</code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED ARRAY )), modified );</code> </td> 
 </tr> 
 </tbody> 
</table>

注意：这里在CAST语法外面需要使用小括号括起来！

**测试案例**

复制

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 </td> 
 <td> <code>DROP</code> <code>TABLE</code> <code>IF EXISTS `customers`;</code> <code>/*建表语句*/</code> <code>CREATE</code> <code>TABLE</code> <code>customers ( </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id </code><code>BIGINT</code> <code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT </code><code>PRIMARY</code> <code>KEY</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>age tinyint(4) </code><code>not</code> <code>null</code><code>,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>modified DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code> <code>ON</code> <code>UPDATE</code> <code>CURRENT_TIMESTAMP</code><code>, </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>custinfo JSON </code><code>NOT</code> <code>NULL</code> <code>);</code> <code>/*插入写测试数据*/</code> <code>INSERT</code> <code>INTO</code> <code>customers</code> <code>VALUES</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, 21, NOW(), </code><code>'[{"key":94582},{"key":94536}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, 22, NOW(), </code><code>'[{"key":94568},{"key":94507},{"key":94582}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, 23, NOW(), </code><code>'[{"key":94477},{"key":94507}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, 24, NOW(), </code><code>'[{"key":94536}]'</code><code>),</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( </code><code>NULL</code><code>, 25, NOW(), </code><code>'[{"key":94507},{"key":94582}]'</code><code>);</code> <code>/*添加多值索引*/</code> <code>alter</code> <code>table</code> <code>customers </code><code>DROP</code> <code>INDEX</code> <code>idx_age_custinfo$list_modified ;</code> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_age_custinfo$list_modified ( age, (</code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED ARRAY )),modified );</code> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_age_custinfo$list_modified ((</code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED ARRAY )), age,modified&nbsp; );</code> <code>ALTER</code> <code>TABLE</code> <code>customers </code><code>ADD</code> <code>INDEX</code> <code>idx_age_custinfo$list_modified ( age,modified, (</code><code>CAST</code><code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>AS</code> <code>UNSIGNED ARRAY )) );</code> <code>/*测试 MEMBER </code><code>OF</code> <code>语法*/</code> <code>SELECT</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>* </code> <code>FROM</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>customers </code> <code>WHERE</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>94536 MEMBER </code><code>OF</code> <code>( custinfo -&gt; </code><code>'$[*].key'</code> <code>) </code><code>and</code> <code>modified = </code><code>'2021-08-05 10:36:34'</code> <code>and</code> <code>age = 21;</code> </td> 
 </tr> 
 </tbody> 
</table>

查看执行计划发现可以使用到索引：

![MySQL怎么为JSON字段创建索引][MySQL_JSON 4]

#### 多值索引的局限 ####

*  **一个多值索引只允许包含一个属性的值**
 *  **该索引目前只支持三个语法**

目前只有MEMBER OF、 JSON\_CONTAINS()、 JSON\_OVERLAB()三种语法可以使用到多值索引。

*  **索引值必须转成数组**

( CAST( custinfo -> '$.key' AS UNSIGNED array))，语法中的array是可以不加的，之所以要强制加是因为如果不加就不是数组结构，不是数组结构就没法直接使用上述三个语法，需要通过JSON\_ARRAY()等方法转换后才能使用，这样就会导致索引失效！因此不管需要加索引的字段是单个值的字段还是数组字段，都要加上array关键字。

*  **该索引不支持用于表关联**
 *  **不能结合前缀索引**
 *  **不支持在线创建多值索引**

这句话的意思是该操作使用 ALGORITHM=COPY，即通过新建一张表结构，再将数据复制过去的方式实现索引的创建。因此该过程中不允许DML操作。

*  **多值索引对字符集类型字段有明确的要求**

binary字符集的排序规则必须是binary

utf8mb4字符集的排序规则必须是utf8mb4\_0900\_as\_cs

其他任何字符集或排序规则都不能创建多值索引，创建时会报错当前版本不支持。

#### 应用场景 ####

多值索引的应用场景非常广泛！有了他之后很多关联关系表都可以不用了！举个简单的例子：用户标签，很多场景下会给用户贴上各种标签，比如1高 2富 3帅，为了后续的更高效的做统计或筛选查询，我们不能直接将这个标签作为一个字段存储，因为没有索引查询效率不高，所以很多时候会使用一张关联关系表来存储用户-标签的关系。但是现在有了多值索引，我们就可以将标签作为一个字段存储了！

这只是其中一个小场景，类似的场景非常多，用户可以换成任何事物，标签也可以换成其他任何属性，只要是这个事物存在多种属性值就行，存在一个多对多关系，那么在没有需要这个属性与其他表做表关联的请况下)，都可以使用多值索引实现！多值索引不支持表关联，因此如果需要用该字段在做表关联的话就不合适了。

[MySQL_JSON]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/06492991e1944ad88b2a719d3a639353.png
[MySQL_JSON 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/37e69a35d61a4bf899bfef0989954aa6.png
[MySQL_JSON 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/a7cdc0e4c0344124b9d0e38ebd420db7.png
[MySQL_JSON 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/77c028d253a04b90a774c93b19dc5968.png
[MySQL_JSON 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7a092cce26164b6eb2e9742e3bd0389b.png