Comment créer un index pour les champs JSON dans MySQL

Introduction aux index à valeurs multiples

À partir de MySQL 8.0.17, InnoDB prend en charge la création d'index à valeurs multiples (index multi-valeurs), qui sont des index secondaires définis sur les colonnes où JSON stocke la valeur tableaux. Il peut y avoir plusieurs enregistrements d’index pour un seul enregistrement de données. La définition de syntaxe spécifique pour ce type d'index :

CAST(expression AS type ARRAY), tel que CAST(data->'$.zipcode' AS UNSIGNED ARRAY). Comme les index ordinaires, ils peuvent également être consultés dans EXPLAIN.

Créer un index à valeurs multiples

Comme d'autres index, des index à valeurs multiples peuvent être ajoutés lors de la création de la table, ou créés via ALTER TABLE ou CREATE INDEX.

Index des champs d'objets JSON

Syntaxe

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

Remarque : Il y a ici deux couches de crochets simples en dehors de la syntaxe CAST ! , si vous en écrivez un de moins, une erreur sera signalée !

Cas de test

PS : Les cas dans l'article font référence aux cas dans les documents officiels et sont uniquement destinés aux tests, ils ne sont donc pas très standardisés en termes de dénomination et d'autres aspects, vous devez vous y conformer strictement. par les spécifications de développement de l'équipe de l'entreprise et ne soyez pas paresseux !

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 ));

Consultez le plan d'exécution et constatez que l'index peut être utilisé :

Si vous devez créer un index à valeurs multiples pour le type de caractère, il doit s'agir du jeu de caractères utf8mb4 et la règle de tri est utf8mb4_0900_as_cs , sinon une erreur sera signalée. Cette version ne prend pas en charge :

Si Si vous souhaitez créer un index à valeurs multiples pour une chaîne binaire binaire, la règle de tri doit être binaire, sinon une erreur sera signalée et ce n'est pas le cas. soutenu.

L'index peut être ajouté avec succès après avoir modifié les règles de tri :

Indice d'objet de tableau JSON

Syntaxe

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

Remarque : Il y a ici deux couches de crochets simples en dehors de la syntaxe CAST ! Si vous en écrivez un de moins, une erreur sera signalée !

Cas de test

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 ));

Affichez le plan d'exécution et constatez que l'index peut être utilisé :

Créez un index multi-valeurs dans l'index combiné

Syntaxe

La syntaxe est similaire à celle combinée ordinaire index, et suit également le principe de correspondance le plus à gauche :

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

Remarque : vous devez utiliser des parenthèses en dehors de la syntaxe CAST ici !

Cas de test

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;

Affichez le plan d'exécution et constatez que l'index peut être utilisé :

Limitations de l'index à valeurs multiples

• Un index à valeurs multiples n'est autorisé à contenir que la valeur d'un attribut

• L'index ne prend actuellement en charge que trois syntaxes

Actuellement, seuls MEMBER OF, JSON_CONTAINS() et JSON_OVERLAB() peuvent être utilisés pour les index à valeurs multiples.

• La valeur d'index doit être convertie en tableau

(CAST( custinfo -> '$.key' AS UNSIGNED array)). Le tableau dans la syntaxe peut être omis. est obligatoire, car si vous ne l'ajoutez pas, ce n'est pas une structure de tableau. Si ce n'est pas une structure de tableau, vous ne pouvez pas utiliser directement les trois syntaxes ci-dessus. Vous devez la convertir via JSON_ARRAY() et d'autres méthodes avant. il peut être utilisé. Cela entraînera l'échec de l'index ! Par conséquent, que le champ à indexer soit un champ à valeur unique ou un champ tableau, le mot-clé tableau doit être ajouté. "Cet index ne prend pas en charge l'association de tables" L'opération utilise ALGORITHM=COPY signifie créer un index en créant une nouvelle structure de table, puis en y copiant les données. Par conséquent, les opérations DML ne sont pas autorisées pendant ce processus.

• Les index à valeurs multiples ont des exigences claires pour les champs de type de jeu de caractères

• Le classement du jeu de caractères binaire doit être binaireLe classement du jeu de caractères utf8mb4 doit être utf8mb4_0900_as_cs

Tout autre jeu de caractères ou tri Aucune règle ne peut créer des index à valeurs multiples et une erreur sera signalée lors de leur création. La version actuelle ne les prend pas en charge.

• Scénarios d'applicationLes scénarios d'application de l'indice multi-valeurs sont très larges ! Avec lui, de nombreuses tables de relation ne peuvent plus être utilisées ! Prenons un exemple simple : les balises utilisateur. Dans de nombreux scénarios, les utilisateurs recevront diverses balises, telles que 1 grand, 2 riche, 3 beau. Afin de rendre les statistiques ultérieures ou de filtrer les requêtes plus efficaces, nous ne pouvons pas utiliser directement cette balise comme. un champ de stockage, car l'efficacité des requêtes n'est pas élevée sans index, une table d'association est souvent utilisée pour stocker la relation utilisateur-tag. Mais maintenant, avec les index à valeurs multiples, nous pouvons stocker la balise sous forme de champ !

  Ce n'est qu'une des petites scènes. Il existe de nombreuses scènes similaires. L'utilisateur peut la changer en n'importe quoi, et l'étiquette peut également être modifiée en n'importe quel autre attribut, à condition que la chose ait plusieurs valeurs d'attribut et qu'il y ait un. relation plusieurs-à-plusieurs, alors dans S'il n'est pas nécessaire que cet attribut soit associé à d'autres tables), vous pouvez utiliser des index à valeurs multiples ! Les index à valeurs multiples ne prennent pas en charge l'association de tables. Il n'est donc pas approprié d'utiliser ce champ pour l'association de tables.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!