type
status
date
slug
summary
tags
category
icon
password
從
8.0.17 開始，InnoDB 支持 Multi-Valued Indexes (多值索引) 用來為 JSON 型態中的 ARRAY 建立索引。一般索引中每一個筆資料都只會對應到一個索引紀錄 (1:1)，但是多值索引中每一個資料可以對應到多個索引紀錄 (1:N)。建立多值索引時，必須使用
CAST(... AS ... ARRAY) 來定義索引可以將多值索引定義為複合索引的一部分，但是在同一個複合索引中不能同時有多個多值索引
事例
以下建立了一張表進行示範，在尚未建立
Multi-Valued Indexes (多值索引) 時，可以看到對應的查詢只能使用全表掃描接著我們建立
Multi-Valued Indexes (多值索引)除了同樣使用
cast 作為查詢條件可以使用到 多值索引，以下三種 json function 也能使用value MEMBER OF(json_array)：若 value 是 json_array 其中的元素之一，則返回 true
JSON_CONTAINS(target, candidate)：若 candidate json 包含在 target json 中，則返回 true
JSON_OVERLAPS(json_doc1, json_doc2)：若2個 json_doc 有共同的元素，則返回 true
特徵
- 多值索引 DML 操作對索引的維護和一般索引大致相同，唯一的不同是對於一個聚簇索引可能有多個 insert 和 update
- 多值索引的實現是透過生成
hidden virtual generated columns (隱藏的虛擬列)並在該列上建立Functional index，因此也必須遵守相應的規則
- 不會為 empty array 建立索引紀錄
限制
- 多值索引無法排序，因此不可以作為
primary key，同樣也不可以加上ASC | DESC的定義
- 無法使用覆蓋索引
- 不支持
range scans或index-only scans
- 外鍵約束不能使用多值索引
- 不能為多值索引建立
index prefixes
- 不允許用於外鍵規範
- 多值索引定義中的
CAST()不可轉換成BINARY和JSON
- 建立多值索引只能走
ALGORITHM=COPY，這意味著拷表操作，並且不允許 DML
- 多值索引不支持以下兩種以外的 character set 和 collation 的組合
- The
binarycharacter set with the defaultbinarycollation - The
utf8mb4character set with the defaultutf8mb4_0900_as_cscollation
- 不能建立成
HASH索引
大小限制
The maximum number of values per record for a multi-valued index is determined by the amount of data than can be stored on a single undo log page, which is 65221 bytes (64K minus 315 bytes for overhead), which means that the maximum total length of key values is also 65221 bytes. The maximum number of keys depends on various factors, which prevents defining a specific limit. Tests have shown a multi-valued index to permit as many as 1604 integer keys per record, for example. When the limit is reached, an error similar to the following is reported: ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s).
個人心得
- 上述是使用
key:array的方式示範，以下事例arrary的情境
- 建立 INDEX 時 CAST 的型態，要按照 ARRAY 內的實際資料調整，如：
UNSIGNED、CHAR(255)
- 只有
JSON_CONTAINS(target, candidate)和JSON_OVERLAPS(json_doc1, json_doc2)可以一次查詢多個值並吃到 index，value MEMBER OF(json_array)只能查詢一個值。
BUG
在 MySQL 8.0.21 中出現使用 multi-valued 的 Table 在 DML 操作時發生 crash，查看 MySQL 8.0.22 Bugs Fixed 中，有一個修正項目：