Mysql8 多值索引

没头脑

作者 没头脑

创建时间 2023-12-07

更新时间 2024-03-02

阅读 135

评论 0

Mysql

多值索引

Mysql8.0引入了一个新的特性 多值索引(Muti-Valued Indexing), 彻底改变了JSON数组的索引和访问方式.

多值索引是一个二级索引, 定义了存储JSON数组的列.不同于索引和数据记录一对一的传统索引,多值索引为数组中的每个元素和数据之间生成一个索引记录.这意味着查询和访问json数组变得非常简单、高效.
简单来说,多值索引用于索引类似[1, 2, 9, 122]的JSON字段

CAST函数

coast用于将字段转成指定类型的值,目标类型包括BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED

CAST(data as data_type)

创建多值索引

以如下表结构为例

CREATE TABLE `location_data` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_info` json DEFAULT NULL,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_id`)
)

location_info 字段的内容如下

{
    "city": "New York", 
    "state": "NY", 
    "country": "America",
    "postal_codes": [10001,10001,10002,10002,10003,10004]
}

使用 ALTER TABLE

ALTER TABLE location_data ADD INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));

使用 CREATE INDEX

CREATE INDEX idx_postal_codes ON location_data ((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));

在复合索引中使用多值索引

ALTER TABLE location_data ADD INDEX idx_postal_codes_modified((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)), modified);

通过函数访问多值索引

MEMBER OF 、JSON_CONNTIANS 和 JSON_OVERLAPS 

SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");

使用多值索引优化查询

多值索引注意事项

  1. 多值索引的DML操作和普通的索引类似, 主要的区别就是可能会为一条记录创建(更新)多个索引
  2. 多值索引不会为空数组创建索引,如果想通过多值索引查询空数组,不会有任何匹配
  3. 声明多值索引的数组元素中不能有null元素.如果数组元素中有null元素,它将被视为JSON null值，并可能因无效的JSON值而导致错误。
  4. 符合索引只能容纳一个多值索引
  5. 多值索引不支持数据的排序,因此不适合用作主键, 并且不能和ASC 或 DESC排序一起使用

原文链接

提 交
暂无评论