MySQL : : Manuel de référence de MySQL 8.0 : : 13.1.15 Instruction CREATE INDEX

Depuis MySQL 8.0.17, InnoDB supporte les index multivalués. Un index multivalué est un index secondaire défini sur une colonne qui stocke un tableau de valeurs. Un index « normal » comporte un enregistrement d’index pour chaque enregistrement de données (1:1). Un index multivalent peut avoir plusieurs enregistrements d’index pour un seul enregistrement de données (N:1). Les index à valeurs multiples sont destinés à indexer des tableaux JSON. Par exemple, un index multivalué défini sur le tableau de codes postaux dans le document JSON suivant crée un enregistrement d’index pour chaque code postal, chaque enregistrement d’index référençant le même enregistrement de données.

{ "user":"Bob", "user_id":31, "zipcode":}

Créer des index multivalués

Vous pouvez créer un index multivalué dans une déclaration CREATE TABLEALTER TABLE ou CREATE INDEX. Cela nécessite l’utilisation de CAST(... AS ... ARRAY) dans la définition de l’index, qui convertit les valeurs scalaires de même type dans un tableau JSON en un tableau de type de données SQL. Une colonne virtuelle est ensuite générée de manière transparente avec les valeurs du tableau de type de données SQL ; enfin, un index fonctionnel (également appelé index virtuel) est créé sur la colonne virtuelle. C’est l’index fonctionnel défini sur la colonne virtuelle des valeurs du tableau de types de données SQL qui forme l’index multivalué.

Les exemples de la liste suivante montrent les trois différentes façons de créer un index à valeurs multiples.valued index zips peut être créé sur un tableau $.zipcode sur une colonne JSONcustinfo dans une table nommée customers. Dans chaque cas, le tableau JSON est coulé en un tableau de type de données SQL de UNSIGNED valeurs entières.

CREATE TABLE customers ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON );CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );

Un indice à valeurs multiples peut également être défini comme faisant partie d’un indice composite. Cet exemple montre un index composite qui comprend deux parties à valeur unique (pour les colonnes id et modified), et une partie à valeurs multiples (pour la colonne custinfo) :

CREATE TABLE customers ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, custinfo JSON );ALTER TABLE customers ADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

Une seule partie de clé multivaluée peut être utilisée dans un index composite. La partie de clé à valeurs multiples peut être utilisée dans n’importe quel ordre par rapport aux autres parties de la clé. En d’autres termes, l’énoncé ALTER TABLE qui vient d’être présenté aurait pu utiliser comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (ou tout autre ordre) et aurait quand même été valide.

Utilisation d’index multivalués

L’optimiseur utilise un index multivalué pour récupérer les enregistrements lorsque les fonctions suivantes sont spécifiées dans une clause WHERE :

Nous pouvons le démontrer en créant et en alimentant la table customers à l’aide des déclarations CREATE TABLE et INSERT suivantes :

mysql> CREATE TABLE customers ( -> id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> custinfo JSON -> );Query OK, 0 rows affected (0.51 sec)mysql> INSERT INTO customers VALUES -> (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":}'), -> (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":}'), -> (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":}'), -> (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":}'), -> (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":}');Query OK, 5 rows affected (0.07 sec)Records: 5 Duplicates: 0 Warnings: 0

D’abord nous exécutons trois requêtes sur la table customers, chacune utilisant MEMBER OF()JSON_CONTAINS(), et JSON_OVERLAPS(), le résultat de chaque requête étant présenté ici :

mysql> SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+---------------------+-------------------------------------------------------------------+| id | modified | custinfo |+----+---------------------+-------------------------------------------------------------------+| 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": } || 3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": } || 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": } |+----+---------------------+-------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> SELECT * FROM customers -> WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('' AS JSON));+----+---------------------+-------------------------------------------------------------------+| id | modified | custinfo |+----+---------------------+-------------------------------------------------------------------+| 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": } || 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": } |+----+---------------------+-------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> SELECT * FROM customers -> WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('' AS JSON));+----+---------------------+-------------------------------------------------------------------+| id | modified | custinfo |+----+---------------------+-------------------------------------------------------------------+| 1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": } || 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": } || 3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": } || 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": } |+----+---------------------+-------------------------------------------------------------------+4 rows in set (0.00 sec)

Puis, nous exécutons EXPLAIN sur chacune des trois requêtes précédentes :

mysql> EXPLAIN SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT * FROM customers -> WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('' AS JSON));+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT * FROM customers -> WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('' AS JSON));+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

Aucune des trois requêtes qui viennent d’être montrées ne peut utiliser de clé. Pour résoudre ce problème, nous pouvons ajouter un index multivalué sur le tableau zipcode dans la colonne JSONcustinfo), comme ceci :

mysql> ALTER TABLE customers -> ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );Query OK, 0 rows affected (0.47 sec)Records: 0 Duplicates: 0 Warnings: 0

Lorsque nous exécutons à nouveau les déclarations EXPLAIN précédentes, nous pouvons maintenant observer que les requêtes peuvent utiliser (et utilisent) l’index zips qui vient d’être créé :

mysql> EXPLAIN SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->'$.zipcode');+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | ref | zips | zips | 9 | const | 1 | 100.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT * FROM customers -> WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('' AS JSON));+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | range | zips | zips | 9 | NULL | 6 | 100.00 | Using where |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT * FROM customers -> WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('' AS JSON));+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | customers | NULL | range | zips | zips | 9 | NULL | 6 | 100.00 | Using where |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

Un index à valeurs multiples peut être défini comme une clé unique. S’il est défini comme une clé unique, la tentative d’insertion d’une valeur déjà présente dans l’index multivalué renvoie une erreur de clé dupliquée. Si des valeurs dupliquées sont déjà présentes, la tentative d’ajouter un index multivalué unique échoue, comme illustré ici :

mysql> ALTER TABLE customers DROP INDEX zips;Query OK, 0 rows affected (0.55 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE customers -> ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));ERROR 1062 (23000): Duplicate entry '' AS UNSIGNED ARRAY)

Dans ce cas, toutes les valeurs correspondant à l’expression JSON sont stockées dans l’index sous forme de tableau plat unique.

.