MySQL怎么為JSON字段創建索引

這篇文章主要介紹“MySQL怎么為JSON字段創建索引”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL怎么為JSON字段創建索引”文章能幫助大家解決問題。

多值索引簡介

從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對象字段索引

語法

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

注意：這里在CAST語法外面有兩層單括號！，如果少寫一個會報錯！

測試案例

PS：文中的案例是參考官方文檔中的案例，只是作為測試，所以在命名等方面并不怎么規范，實際開發過程中要嚴格遵守公司團隊的開發規范，不要偷懶！

DROP TABLE IF EXISTS `customers`;
/*建表語句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入寫測試數據*/
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)) );
/*測試 MEMBER OF 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );
/*測試 JSON_CONTAINS 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));
/*測試 JSON_OVERLAPS 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

查看執行計劃發現可以使用到索引：

如果需要給字符類型創建多值索引，則必須是utf8mb4字符集且排序規則是utf8mb4_0900_as_cs，否則報錯該版本不支持： 

如果要為binary二進制字符串創建多值索引的話，則排序規則必須是binary，否則報錯不支持。

修改排序規則后可成功添加索引：

JSON數組對象索引

語法

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

注意：這里在CAST語法外面有兩層單括號！如果少寫一個會報錯！

測試案例

DROP TABLE IF EXISTS `customers`;
/*建表語句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入寫測試數據*/
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)) );
/*測試 MEMBER OF 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );
/*測試 JSON_CONTAINS 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));
/*測試 JSON_OVERLAPS 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

查看執行計劃發現可以使用到索引：

在組合索引中創建多值索引

語法

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

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

注意：這里在CAST語法外面需要使用小括號括起來！

測試案例

DROP TABLE IF EXISTS `customers`;
/*建表語句*/
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 
);
/*插入寫測試數據*/
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 )) );
/*測試 MEMBER OF 語法*/
SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

查看執行計劃發現可以使用到索引：

多值索引的局限

• 一個多值索引只允許包含一個屬性的值

• 該索引目前只支持三個語法

目前只有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字段創建索引”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。