MySQL :: Manual de referencia de MySQL 8.0 :: 13.1.15 Declaración CREATE INDEX

A partir de MySQL 8.0.17, InnoDB soporta índices multivaluados. Un índice multivaluado es un índice secundario definido sobre una columna que almacena un array de valores. Un índice «normal» tiene un registro de índice por cada registro de datos (1:1). Un índice multivaluado puede tener varios registros de índice para un único registro de datos (N:1). Los índices multivaluados están pensados para indexar matrices JSON. Por ejemplo, un índice multivaluado definido en la matriz de códigos postales del siguiente documento JSON crea un registro de índice para cada código postal, y cada registro de índice hace referencia al mismo registro de datos.

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

Creación de índices multivaluados

Puede crear un índice multivaluado en una sentencia CREATE TABLEALTER TABLE, o CREATE INDEX. Esto requiere el uso de CAST(... AS ... ARRAY) en la definición del índice, que convierte los valores escalares del mismo tipo en una matriz JSON en una matriz de tipo de datos SQL. A continuación, se genera una columna virtual de forma transparente con los valores de la matriz de tipo de datos SQL; por último, se crea un índice funcional (también denominado índice virtual) en la columna virtual. Es el índice funcional definido en la columna virtual de valores del array de tipos de datos SQL el que forma el índice multivalor.

Los ejemplos de la siguiente lista muestran las tres formas diferentes en las que se puede crear un índice multivalor.valorado zips puede crearse sobre un array $.zipcode en una columna JSONcustinfo en una tabla denominada customers. En cada caso, la matriz JSON se convierte en una matriz de tipo de datos SQL de UNSIGNED valores enteros.

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

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 zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );

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 índice multivaluado también puede definirse como parte de un índice compuesto. Este ejemplo muestra un índice compuesto que incluye dos partes de un solo valor (para las columnas id y modified), y una parte multivaluada (para la columna 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)) );

Sólo se puede utilizar una parte clave multivaluada en un índice compuesto. La parte de clave multivaluada puede utilizarse en cualquier orden relativo a las otras partes de la clave. En otras palabras, la declaración ALTER TABLE que se acaba de mostrar podría haber utilizado comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (o cualquier otro orden) y seguiría siendo válida.

Utilización de índices multivaluados

El optimizador utiliza un índice multivaluado para obtener registros cuando se especifican las siguientes funciones en una cláusula WHERE:

Podemos demostrarlo creando y poblando la tabla customers utilizando las siguientes sentencias CREATE TABLE y INSERT:

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

En primer lugar ejecutamos tres consultas sobre la tabla customers, una cada una usando MEMBER OF()JSON_CONTAINS(), y JSON_OVERLAPS(), con el resultado de cada consulta mostrado aquí:

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)

A continuación, ejecutamos EXPLAIN en cada una de las tres consultas anteriores:

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)

Ninguna de las tres consultas que acabamos de mostrar es capaz de utilizar ninguna clave. Para solucionar este problema, podemos añadir un índice multivaluado en el array zipcode de la columna JSONcustinfo), así:

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

Cuando volvemos a ejecutar las sentencias anteriores EXPLAIN, ahora podemos observar que las consultas pueden utilizar (y utilizan) el índice zips que se acaba de crear:

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 índice multivaluado puede definirse como una clave única. Si se define como clave única, al intentar insertar un valor ya presente en el índice multivaluado se produce un error de clave duplicada. Si los valores duplicados ya están presentes, el intento de añadir un índice multivaluado único falla, como se muestra aquí:

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)

En este caso, todos los valores que coinciden con la expresión JSON se almacenan en el índice como una única matriz plana.

Los conjuntos de caracteres y las colaciones que no sean las dos siguientes combinaciones de conjunto de caracteres y colación no se admiten para los índices multivalor: