MySQL為JSON字段創(chuàng)建索引方式(Multi-Valued?Indexes?多值索引)

更新時(shí)間：2022年08月25日 15:05:38 作者：keep丶

這篇文章主要介紹了MySQL為JSON字段創(chuàng)建索引方式(Multi-Valued?Indexes?多值索引)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

多值索引簡(jiǎn)介

從MySQL 8.0.17 開(kāi)始， InnoDB支持創(chuàng)建多值索引(Multi-Valued Indexes)，該索引是在JSON存儲(chǔ)值數(shù)組的列上定義的二級(jí)索引，對(duì)于單個(gè)數(shù)據(jù)記錄可以有多個(gè)索引記錄。此類索引特定的語(yǔ)法定義：

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

創(chuàng)建多值索引

跟其他索引一樣，多值索引可以在建表時(shí)添加，也可以通過(guò)ALTER TABLE或者CREATE INDEX創(chuàng)建。

JSON對(duì)象字段索引

語(yǔ)法

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

注意：這里在CAST語(yǔ)法外面有兩層單括號(hào)！，如果少寫(xiě)一個(gè)會(huì)報(bào)錯(cuò)！

測(cè)試案例

PS：文中的案例是參考官方文檔中的案例，只是作為測(cè)試，所以在命名等方面并不怎么規(guī)范，實(shí)際開(kāi)發(fā)過(guò)程中要嚴(yán)格遵守公司團(tuán)隊(duì)的開(kāi)發(fā)規(guī)范，不要偷懶！

DROP TABLE IF EXISTS `customers`;
/*建表語(yǔ)句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入寫(xiě)測(cè)試數(shù)據(jù)*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
	( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
	( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
	( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
	( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
/*測(cè)試 MEMBER OF 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );
/*測(cè)試 JSON_CONTAINS 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));
/*測(cè)試 JSON_OVERLAPS 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

查看執(zhí)行計(jì)劃發(fā)現(xiàn)可以使用到索引：

如果需要給字符類型創(chuàng)建多值索引，則必須是utf8mb4字符集且排序規(guī)則是utf8mb4_0900_as_cs，否則報(bào)錯(cuò)該版本不支持：

如果要為binary二進(jìn)制字符串創(chuàng)建多值索引的話，則排序規(guī)則必須是binary，否則報(bào)錯(cuò)不支持。

修改排序規(guī)則后可成功添加索引：

JSON數(shù)組對(duì)象索引

語(yǔ)法

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

注意：這里在CAST語(yǔ)法外面有兩層單括號(hào)！如果少寫(xiě)一個(gè)會(huì)報(bào)錯(cuò)！

測(cè)試案例

DROP TABLE IF EXISTS `customers`;
/*建表語(yǔ)句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入寫(xiě)測(cè)試數(shù)據(jù)*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, NOW(), '[{"key":94536}]'),
	( NULL, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
/*測(cè)試 MEMBER OF 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );
/*測(cè)試 JSON_CONTAINS 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));
/*測(cè)試 JSON_OVERLAPS 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

查看執(zhí)行計(jì)劃發(fā)現(xiàn)可以使用到索引：

在組合索引中創(chuàng)建多值索引

語(yǔ)法

語(yǔ)法跟普通組合索引差不多，同樣也遵守最左匹配原則：

ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );

注意：這里在CAST語(yǔ)法外面需要使用小括號(hào)括起來(lái)！

測(cè)試案例

DROP TABLE IF EXISTS `customers`;
/*建表語(yǔ)句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	age tinyint(4) not null,
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入寫(xiě)測(cè)試數(shù)據(jù)*/
INSERT INTO customers
VALUES
	( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, 24, NOW(), '[{"key":94536}]'),
	( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
alter table customers DROP INDEX idx_age_custinfo$list_modified ;
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified  );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
/*測(cè)試 MEMBER OF 語(yǔ)法*/
SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

查看執(zhí)行計(jì)劃發(fā)現(xiàn)可以使用到索引：

多值索引的局限

一個(gè)多值索引只允許包含一個(gè)屬性的值
該索引目前只支持三個(gè)語(yǔ)法

目前只有MEMBER OF、 JSON_CONTAINS()、 JSON_OVERLAB()三種語(yǔ)法可以使用到多值索引。

索引值必須轉(zhuǎn)成數(shù)組

( CAST( custinfo -> '$.key' AS UNSIGNED array))，語(yǔ)法中的array是可以不加的，之所以要強(qiáng)制加是因?yàn)槿绻患泳筒皇菙?shù)組結(jié)構(gòu)，不是數(shù)組結(jié)構(gòu)就沒(méi)法直接使用上述三個(gè)語(yǔ)法，需要通過(guò)JSON_ARRAY()等方法轉(zhuǎn)換后才能使用，這樣就會(huì)導(dǎo)致索引失效！因此不管需要加索引的字段是單個(gè)值的字段還是數(shù)組字段，都要加上array關(guān)鍵字。

該索引不支持用于表關(guān)聯(lián)
不能結(jié)合前綴索引
不支持在線創(chuàng)建多值索引

這句話的意思是該操作使用 ALGORITHM=COPY，即通過(guò)新建一張表結(jié)構(gòu)，再將數(shù)據(jù)復(fù)制過(guò)去的方式實(shí)現(xiàn)索引的創(chuàng)建。因此該過(guò)程中不允許DML操作。

多值索引對(duì)字符集類型字段有明確的要求

binary字符集的排序規(guī)則必須是binary

utf8mb4字符集的排序規(guī)則必須是utf8mb4_0900_as_cs

其他任何字符集或排序規(guī)則都不能創(chuàng)建多值索引，創(chuàng)建時(shí)會(huì)報(bào)錯(cuò)當(dāng)前版本不支持。

應(yīng)用場(chǎng)景

多值索引的應(yīng)用場(chǎng)景非常廣泛！有了他之后很多關(guān)聯(lián)關(guān)系表都可以不用了！舉個(gè)簡(jiǎn)單的例子：用戶標(biāo)簽，很多場(chǎng)景下會(huì)給用戶貼上各種標(biāo)簽，比如1高 2富 3帥，為了后續(xù)的更高效的做統(tǒng)計(jì)或篩選查詢，我們不能直接將這個(gè)標(biāo)簽作為一個(gè)字段存儲(chǔ)，因?yàn)闆](méi)有索引查詢效率不高，所以很多時(shí)候會(huì)使用一張關(guān)聯(lián)關(guān)系表來(lái)存儲(chǔ)用戶-標(biāo)簽的關(guān)系。但是現(xiàn)在有了多值索引，我們就可以將標(biāo)簽作為一個(gè)字段存儲(chǔ)了！

這只是其中一個(gè)小場(chǎng)景，類似的場(chǎng)景非常多，用戶可以換成任何事物，標(biāo)簽也可以換成其他任何屬性，只要是這個(gè)事物存在多種屬性值就行，存在一個(gè)多對(duì)多關(guān)系，那么在沒(méi)有需要這個(gè)屬性與其他表做表關(guān)聯(lián)的請(qǐng)況下)，都可以使用多值索引實(shí)現(xiàn)！多值索引不支持表關(guān)聯(lián)，因此如果需要用該字段在做表關(guān)聯(lián)的話就不合適了。

關(guān)于JSON函數(shù)，還有其他的文章介紹：

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: