MySQL :: Manual de Referência MySQL 8.0 :: 13.1.15 CREATE INDEX Statement

A partir do MySQL 8.0.17, InnoDB suporta índices multi-valorizados. Um índice multi-valorizado é um índice secundário definido numa coluna que armazena um conjunto de valores. Um índice “normal” tem um registo de índice para cada registo de dados (1:1). Um índice multivalorizado pode ter vários registos de índice para um único registo de dados (N:1). Os índices multi-valorizados destinam-se à indexação JSON arrays. Por exemplo, um índice multi-valorizado definido na matriz de códigos postais no seguinte documento JSON cria um registo de índice para cada código postal, com cada registo de índice referenciando o mesmo registo de dados.

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

Criação de índices multi-valorizados

Pode-se criar um índice multi-valorizado numa declaração CREATE TABLE, ou CREATE INDEX. Isto requer a utilização de CAST(... AS ... ARRAY) na definição de índice, que lança valores escalares do mesmo tipo num JSON array para um array de tipo de dados SQL. Uma coluna virtual é então gerada de forma transparente com os valores na matriz de tipos de dados SQL; finalmente, um índice funcional (também referido como um índice virtual) é criado na coluna virtual. É o índice funcional definido na coluna virtual de valores da matriz de tipos de dados SQL que forma o índice multi-valorizado.

Os exemplos da lista seguinte mostram as três formas diferentes em que um índice multiíndice valorizado zips pode ser criado num array $.zipcode num JSON coluna custinfo numa tabela chamada customers. Em cada caso, a matriz do JSON é fundida numa matriz de tipo de dados SQL de UNSIGNED valores inteiros.

Um índice multivalorizado também pode ser definido como parte de um índice composto. Este exemplo mostra um índice composto que inclui duas peças de valor único (para a coluna id e modified), e uma peça de valor múltiplo (para a coluna 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)) );

Apenas uma peça chave multi-valorizada pode ser usada num índice composto. A peça chave multi-valorizada pode ser utilizada em qualquer ordem em relação às outras peças da chave. Por outras palavras, a declaração ALTER TABLE que acabou de ser mostrada poderia ter usado comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (ou qualquer outra encomenda) e ainda assim ter sido válida.

Usando índices multi-valorizados

O optimizador usa um índice multi-valorizado para ir buscar registos quando as seguintes funções são especificadas numa cláusula WHERE:

Podemos demonstrar isto criando e preenchendo a tabela customers usando a seguinte tabela CREATE TABLE e INSERT declarações:

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

Primeiro executamos três consultas na tabela customers, um usando cada um MEMBER OF()JSON_CONTAINS(), e JSON_OVERLAPS(), com o resultado de cada consulta aqui mostrado:

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 seguir, corremos EXPLAIN em cada uma das três 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)

Nenhuma das três consultas apenas mostradas é capaz de utilizar quaisquer chaves. Para resolver este problema, podemos adicionar um índice multi-valorizado na coluna zipcode array no JSONcustinfo), desta forma:

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

Quando executamos as declarações anteriores EXPLAIN novamente, podemos agora observar que as consultas podem (e fazem) usar o índice zips que acabou de ser criado:

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)

Um índice multi-valorizado pode ser definido como uma chave única. Se definida como uma chave única, tentar inserir um valor já presente no índice multivalorizado retorna um erro de chave duplicado. Se já estiverem presentes valores duplicados, a tentativa de adicionar um índice multivalorizado único falha, como aqui mostrado:

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)

Neste caso, todos os valores que correspondem à expressão JSON são armazenados no índice como uma única matriz plana.

Um índice com uma peça chave multi-valorizada não suporta a ordenação e, portanto, não pode ser utilizado como chave primária. Pela mesma razão, um índice multi-valorizado não pode ser definido usando a palavra-chave ASC ou DESC.

p> Um índice multivalorizado não pode ser um índice de cobertura.

p> O número máximo de valores por registo para um índice multivalorizado é determinado pela quantidade de dados que podem ser armazenados numa única página de registo de desfazer, que é 65221 bytes (64K menos 315 bytes para despesas gerais), o que significa que o comprimento máximo total de valores chave é também 65221 bytes. O número máximo de chaves depende de vários factores, o que impede a definição de um limite específico. Os testes mostraram um índice multi-valorizado para permitir até 1604 chaves inteiras por registo, por exemplo. Quando o limite é atingido, é comunicado um erro semelhante ao que se segue: ERRO 3905 (HY000): Excedeu o número máximo de valores por registo para índice multi-valorizado ‘idx’ por 1 valor(es).

p> O único tipo de expressão permitida numa parte chave multi-valorizada é uma expressão JSON. A expressão não precisa de fazer referência a um elemento existente num documento JSON inserido na coluna indexada, mas deve ela própria ser sintacticamente válida.

p> Porque os registos de índice para o mesmo registo de índice agrupado estão dispersos por um índice multi-valorizado, um índice multi-valorizado não suporta varreduras de intervalo ou varreduras apenas de índice.

p> Os índices multivalorizados não são permitidos em especificações de chaves estrangeiras.

p> Os prefixos de índice não podem ser definidos para índices multivalorizados.

Os índices multivalorizados não podem ser definidos em dados fundidos como BINARY (ver a descrição da função CAST()).

p> não é suportada a criação online de um índice de múltiplos valores, o que significa que a operação usa ALGORITHM=COPY. Ver Desempenho e requisitos de espaço.

Conjuntos de caracteres e colações diferentes das duas combinações seguintes de conjunto de caracteres e colações não são suportadas para índices multi-valorizados:

1. O conjunto de caracteres binary com o padrão binary colação

2. p> O utf8mb4 conjunto de caracteres com o padrão utf8mb4_0900_as_cs colação.

Como com outros índices em colunas de InnoDB tabelas, um índice multivalorizado não pode ser criado com USING HASH; a tentativa de o fazer resulta num aviso: Este motor de armazenamento não suporta o algoritmo de índice HASH; em vez disso, foi utilizado por defeito o motor de armazenamento. (USING BTREE é suportado como habitualmente.)