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

  • por

Índices multivaluados

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 únicamente:

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 más ALTER TABLE:

    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 más CREATE INDEX:

    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.

  • Un índice con una parte de clave multivaluada no admite el ordenamiento y, por tanto, no puede utilizarse como clave primaria. Por la misma razón, un índice multivaluado no puede definirse utilizando la palabra clave ASC o DESC.

  • Un índice multivaluado no puede ser un índice de cobertura.

  • El número máximo de valores por registro para un índice multivaluado está determinado por la cantidad de datos que se pueden almacenar en una sola página de registro de deshacer, que es de 65221 bytes (64K menos 315 bytes para la sobrecarga), lo que significa que la longitud total máxima de los valores de las claves es también de 65221 bytes. El número máximo de claves depende de varios factores, lo que impide definir un límite específico. Las pruebas han demostrado que un índice multivalor permite hasta 1604 claves enteras por registro, por ejemplo. Cuando se alcanza el límite, se informa de un error similar al siguiente ERROR 3905 (HY000): Se ha superado el número máximo de valores por registro para el índice multivaluado ‘idx’ en 1 valor(es).

  • El único tipo de expresión que se permite en una parte clave multivaluada es una expresión JSON. La expresión no necesita hacer referencia a un elemento existente en un documento JSON insertado en la columna indexada, pero debe ser en sí misma sintácticamente válida.

  • Debido a que los registros de índice para el mismo registro de índice agrupado están dispersos a lo largo de un índice multivaluado, un índice multivaluado no admite escaneos de rango o escaneos de sólo índice.

  • Los índices multivaluados no están permitidos en las especificaciones de claves foráneas.

  • No se pueden definir prefijos de índice para índices multivaluados.

  • No se pueden definir índices multivaluados en datos emitidos como BINARY (véase la descripción de la función CAST()).

  • No se admite la creación en línea de un índice multivalor, lo que significa que la operación utiliza ALGORITHM=COPY. Consulte los requisitos de rendimiento y espacio.

  • 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:

    1. El conjunto de caracteres binary con la colación por defecto binary.

    2. El conjunto de caracteres utf8mb4 con la colación por defecto utf8mb4_0900_as_cs.

  • Al igual que con otros índices sobre columnas de las tablas InnoDB, no se puede crear un índice multivalor con USING HASH; al intentarlo se produce una advertencia: Este motor de almacenamiento no admite el algoritmo de índice HASH, en su lugar se ha utilizado el motor de almacenamiento por defecto. (USING BTREE se admite como siempre.)

  • Deja una respuesta

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *