MySQL為JSON字段創(chuàng)建索引方式(Multi-Valued?Indexes?多值索引)
多值索引簡介
從MySQL 8.0.17 開始， InnoDB支持創(chuàng)建多值索引(Multi-Valued Indexes)，該索引是在JSON存儲(chǔ)值數(shù)組的列上定義的二級(jí)索引，對(duì)于單個(gè)數(shù)據(jù)記錄可以有多個(gè)索引記錄。此類索引特定的語法定義：
CAST(expression AS type ARRAY)，例如CAST(data->'$.zipcode' AS UNSIGNED ARRAY)。 跟普通索引一樣，也可以在EXPLAIN中查看到。
創(chuàng)建多值索引
跟其他索引一樣，多值索引可以在建表時(shí)添加，也可以通過ALTER TABLE或者CREATE INDEX創(chuàng)建。
JSON對(duì)象字段索引
語法
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );
注意：這里在CAST語法外面有兩層單括號(hào)！，如果少寫一個(gè)會(huì)報(bào)錯(cuò)！
測試案例
PS：文中的案例是參考官方文檔中的案例，只是作為測試，所以在命名等方面并不怎么規(guī)范，實(shí)際開發(fā)過程中要嚴(yán)格遵守公司團(tuán)隊(duì)的開發(fā)規(guī)范，不要偷懶！
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
);
/*插入寫測試數(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)) );
/*測試 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 ));
查看執(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ì)象索引
語法
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );
注意：這里在CAST語法外面有兩層單括號(hào)！如果少寫一個(gè)會(huì)報(bào)錯(cuò)！
測試案例
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
);
/*插入寫測試數(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)) );
/*測試 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 ));
查看執(zhí)行計(jì)劃發(fā)現(xiàn)可以使用到索引：
在組合索引中創(chuàng)建多值索引
語法
語法跟普通組合索引差不多，同樣也遵守最左匹配原則：
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );
注意：這里在CAST語法外面需要使用小括號(hào)括起來！
測試案例
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
);
/*插入寫測試數(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 )) );
/*測試 MEMBER OF 語法*/
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è)語法
目前只有MEMBER OF、 JSON_CONTAINS()、 JSON_OVERLAB()三種語法可以使用到多值索引。
- 索引值必須轉(zhuǎn)成數(shù)組
( CAST( custinfo -> '$.key' AS UNSIGNED array))，語法中的array是可以不加的，之所以要強(qiáng)制加是因?yàn)槿绻患泳筒皇菙?shù)組結(jié)構(gòu)，不是數(shù)組結(jié)構(gòu)就沒法直接使用上述三個(gè)語法，需要通過JSON_ARRAY()等方法轉(zhuǎn)換后才能使用，這樣就會(huì)導(dǎo)致索引失效！因此不管需要加索引的字段是單個(gè)值的字段還是數(shù)組字段，都要加上array關(guān)鍵字。
- 該索引不支持用于表關(guān)聯(lián)
- 不能結(jié)合前綴索引
- 不支持在線創(chuàng)建多值索引
這句話的意思是該操作使用 ALGORITHM=COPY，即通過新建一張表結(jié)構(gòu)，再將數(shù)據(jù)復(fù)制過去的方式實(shí)現(xiàn)索引的創(chuàng)建。因此該過程中不允許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)用場景
多值索引的應(yīng)用場景非常廣泛！有了他之后很多關(guān)聯(lián)關(guān)系表都可以不用了！舉個(gè)簡單的例子：用戶標(biāo)簽，很多場景下會(huì)給用戶貼上各種標(biāo)簽，比如1高 2富 3帥，為了后續(xù)的更高效的做統(tǒng)計(jì)或篩選查詢，我們不能直接將這個(gè)標(biāo)簽作為一個(gè)字段存儲(chǔ)，因?yàn)闆]有索引查詢效率不高，所以很多時(shí)候會(huì)使用一張關(guān)聯(lián)關(guān)系表來存儲(chǔ)用戶-標(biāo)簽的關(guān)系。但是現(xiàn)在有了多值索引，我們就可以將標(biāo)簽作為一個(gè)字段存儲(chǔ)了！
這只是其中一個(gè)小場景，類似的場景非常多，用戶可以換成任何事物，標(biāo)簽也可以換成其他任何屬性，只要是這個(gè)事物存在多種屬性值就行，存在一個(gè)多對(duì)多關(guān)系，那么在沒有需要這個(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è)參考，也希望大家多多支持腳本之家。
相關(guān)文章
看到園子里總結(jié)的Mysql用法，我覺得沒有我的全面，我的從登錄到高級(jí)的存儲(chǔ)過程都涉及到，這部分是我平常不會(huì)或是出現(xiàn)問題都會(huì)拿來看，不過現(xiàn)在就和我一起來使用命令模式學(xué)習(xí)一下數(shù)據(jù)庫最基本的吧2012-05-05
這篇文章主要介紹了多種不同的 MySQL 的 SSL 配置,非常不錯(cuò)的一篇文章，而且很實(shí)用,需要的朋友可以參考下2015-03-03
mysql實(shí)現(xiàn)向某個(gè)字段前或后添加字符
這篇文章主要介紹了mysql實(shí)現(xiàn)向某個(gè)字段前或后添加字符，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-09-09
這篇文章主要介紹了mysql持久化統(tǒng)計(jì)信息的相關(guān)資料，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-12-12
mysql數(shù)據(jù)庫重置表主鍵id的實(shí)現(xiàn)
在我們的開發(fā)過程中,難免在做測試的時(shí)候會(huì)生成一些雜亂無章的SQL主鍵數(shù)據(jù),本文主要介紹了mysql數(shù)據(jù)庫重置表主鍵id的實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下2025-03-03
Ubuntu?18.04.4安裝mysql的過程詳解?親測可用
這篇文章主要介紹了Ubuntu?18.04.4安裝mysql-親測可用,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-12-12
mysql 使用存儲(chǔ)過程實(shí)現(xiàn)樹節(jié)點(diǎn)的獲取方法
這篇文章主要介紹了mysql 使用存儲(chǔ)過程實(shí)現(xiàn)樹節(jié)點(diǎn)的獲取方法，本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-06-06
本文介紹了七種MySQL索引類型。在數(shù)據(jù)庫表中，對(duì)字段建立索引可以大大提高查詢速度。通過善用這些索引，可以令MySQL的查詢和運(yùn)行更加高效。2015-07-07
深入解析MySQL中的longtext與longblob及應(yīng)用場景
MySQL作為廣泛應(yīng)用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),提供了豐富的數(shù)據(jù)類型以滿足各種數(shù)據(jù)存儲(chǔ)需求,本文將深入探討MySQL中l(wèi)ongtext和longblob的特性、區(qū)別以及在實(shí)際項(xiàng)目中的應(yīng)用場景,感興趣的朋友跟隨小編一起看看吧2024-05-05
MySQL中表復(fù)制:create table like 與 create table as select
這篇文章主要介紹了MySQL中表復(fù)制:create table like 與 create table as select,需要的朋友可以參考下2014-12-12