MySQL :: MySQL 8.0 Reference Manual :: 13.1.15 CREATE INDEX Statement

A partire da MySQL 8.0.17, InnoDB supporta gli indici multi-valutati. Un indice multi-valore è un indice secondario definito su una colonna che memorizza un array di valori. Un indice “normale” ha un record di indice per ogni record di dati (1:1). Un indice multi-valutato può avere più record di indice per un singolo record di dati (N:1). Gli indici multi-valore sono destinati all’indicizzazione degli array JSON. Per esempio, un indice multi-valore definito sull’array di codici postali nel seguente documento JSON crea un record di indice per ogni codice postale, con ogni record di indice che fa riferimento allo stesso record di dati.

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

Creare indici multi-valore

È possibile creare un indice multi-valore in una dichiarazione CREATE TABLEALTER TABLE, o CREATE INDEX. Ciò richiede l’uso di CAST(... AS ... ARRAY) nella definizione dell’indice, che trasforma i valori scalari dello stesso tipo in un array JSON in un array di tipo SQL. Una colonna virtuale viene quindi generata in modo trasparente con i valori nell’array di tipi di dati SQL; infine, un indice funzionale (chiamato anche indice virtuale) viene creato sulla colonna virtuale. È l’indice funzionale definito sulla colonna virtuale dei valori dell’array di tipi di dati SQL che forma l’indice multi-valore.

Gli esempi nella lista seguente mostrano i tre diversi modi in cui un indice multi-valorevalued index zips può essere creato su un array $.zipcode su una JSON colonna custinfo in una tabella chiamata customers. In ogni caso, l’array JSON viene convertito in un array di tipo dati SQL di UNSIGNED valori interi.

Un indice multi-valore può anche essere definito come parte di un indice composito. Questo esempio mostra un indice composito che include due parti a valore singolo (per le colonne id e modified), e una parte a più valori (per la colonna 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)) );

In un indice composito può essere usata solo una parte di chiave multi-valore. La parte di chiave multi-valore può essere usata in qualsiasi ordine relativo alle altre parti della chiave. In altre parole, la dichiarazione ALTER TABLE appena mostrata avrebbe potuto usare comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (o qualsiasi altro ordine) ed essere ancora valida.

Usare gli indici multi-valore

L’ottimizzatore usa un indice multi-valore per recuperare i record quando le seguenti funzioni sono specificate in una clausola WHERE:

Possiamo dimostrarlo creando e popolando la tabella customers utilizzando le seguenti dichiarazioni CREATE TABLE e 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

Prima eseguiamo tre query sulla tabella customers, una ciascuno usando MEMBER OF()JSON_CONTAINS(), e JSON_OVERLAPS(), con il risultato di ogni query mostrato qui:

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)

Successivamente, eseguiamo EXPLAIN su ciascuna delle tre query precedenti:

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)

Nessuna delle tre query appena mostrate è in grado di utilizzare alcuna chiave. Per risolvere questo problema, possiamo aggiungere un indice multi-valore sull’array zipcode nella colonna JSONcustinfo), così:

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 eseguiamo nuovamente le precedenti dichiarazioni EXPLAIN, possiamo ora osservare che le query possono (e lo fanno) utilizzare l’indice zips appena creato:

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 indice multi-valore può essere definito come chiave unica. Se definito come chiave unica, il tentativo di inserire un valore già presente nell’indice multi-valore restituisce un errore di chiave duplicata. Se i valori duplicati sono già presenti, il tentativo di aggiungere un indice multi-valore unico fallisce, come mostrato qui:

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)

In questo caso, tutti i valori corrispondenti all’espressione JSON sono memorizzati nell’indice come un singolo array piatto.

Un indice con una parte di chiave multi-valore non supporta l’ordinamento e quindi non può essere usato come chiave primaria. Per la stessa ragione, un indice multi-valutato non può essere definito usando la parola chiave ASC o DESC.

Un indice multi-valore non può essere un indice di copertura.

Il numero massimo di valori per record per un indice multi-valore è determinato dalla quantità di dati che può essere memorizzata in una singola pagina di log degli annullamenti, che è 65221 byte (64K meno 315 byte per l’overhead), il che significa che la massima lunghezza totale dei valori chiave è anch’essa 65221 byte. Il numero massimo di chiavi dipende da vari fattori, il che impedisce di definire un limite specifico. I test hanno mostrato che un indice multi-valore permette fino a 1604 chiavi intere per record, per esempio. Quando il limite viene raggiunto, viene riportato un errore simile al seguente: ERRORE 3905 (HY000): Superato il numero massimo di valori per record per l’indice multi-valutato ‘idx’ di 1 valore(i).

L’unico tipo di espressione che è permesso in una parte di chiave multi-valore è un’espressione JSON. L’espressione non deve necessariamente fare riferimento a un elemento esistente in un documento JSON inserito nella colonna indicizzata, ma deve essere essa stessa sintatticamente valida.

Poiché i record dell’indice per lo stesso record dell’indice clusterizzato sono dispersi in un indice multi-valore, un indice multi-valore non supporta scansioni di intervallo o scansioni di solo indice.

Gli indici multi-valore non sono permessi nelle specifiche delle chiavi esterne.

I prefissi degli indici non possono essere definiti per gli indici multi-valore.

Gli indici multi-valore non possono essere definiti su dati fusi come BINARY (vedere la descrizione della funzione CAST()).

La creazione online di un indice multi-valore non è supportata, il che significa che l’operazione utilizza ALGORITHM=COPY. Vedere Prestazioni e requisiti di spazio.

Insiemi di caratteri e ordinamenti diversi dalle seguenti due combinazioni di set di caratteri e ordinamento non sono supportati per gli indici multi-valore:

1. Il binary set di caratteri con l’ordinamento predefinito binary

2. Il utf8mb4 set di caratteri con l’ordinamento predefinito utf8mb4_0900_as_cs.

Come per altri indici su colonne di tabelle InnoDB, un indice multi-valore non può essere creato con USING HASH; il tentativo di farlo genera un avviso: Questo motore di archiviazione non supporta l’algoritmo dell’indice HASH, è stato invece utilizzato il motore di archiviazione predefinito. (USING BTREE è supportato come al solito.)