MySQL
Indici e chiavi

Sintassi

• - Crea un indice semplice

  CREATE INDEX nome_indice ON nome_tabella ( nome_colonna1 [, nome_colonna2 , ...])

• - Crea un indice univoco

  CREATE UNICO INDICE nome_indice ON nome_tabella ( nome_colonna1 [, nome_colonna2 , ...]

• - Indice di caduta

  DROP INDEX nome_indice ON nome_tabella [algorithm_option | lock_option ] ...

  algorithm_option: ALGORITHM [=] {DEFAULT | INPLACE | COPY}

  lock_option: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Osservazioni

Crea indice

-- Create an index for column 'name' in table 'my_table'
CREATE INDEX idx_name ON my_table(name);

Crea un indice univoco

Un indice univoco impedisce l'inserimento di dati duplicati in una tabella. NULL valori NULL possono essere inseriti nelle colonne che fanno parte dell'indice univoco (poiché, per definizione, un valore NULL è diverso da qualsiasi altro valore, incluso un altro valore NULL )

-- Creates a unique index for column 'name' in table 'my_table'
CREATE UNIQUE INDEX idx_name ON my_table(name);

Indice di caduta

-- Drop an index for column 'name' in table 'my_table'
DROP INDEX idx_name ON my_table;

Crea indice composito

Questo creerà un indice composito di entrambe le chiavi, mystring e mydatetime e accelererà le query con entrambe le colonne nella clausola WHERE .

CREATE INDEX idx_mycol_myothercol ON my_table(mycol, myothercol)

Nota: l'ordine è importante! Se la query di ricerca non include entrambe le colonne nella clausola WHERE , può utilizzare solo l'indice più a sinistra. In questo caso, una query con mycol nel WHERE utilizzerà l'indice, una query alla ricerca di myothercol senza cercare anche mycol non lo farà. Per maggiori informazioni consulta questo post sul blog .

Nota: a causa del modo in cui funziona BTREE, le colonne che vengono solitamente interrogate in intervalli dovrebbero andare nel valore più a destra. Ad esempio, le colonne DATETIME vengono normalmente interrogate come WHERE datecol > '2016-01-01 00:00:00' . Gli indici BTREE gestiscono gli intervalli in modo molto efficiente, ma solo se la colonna interrogata come intervallo è l'ultima dell'indice composito.

Tasto AUTO_INCREMENT

CREATE TABLE (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY(id),
    ...  );

Note principali:

• Inizia con 1 e incrementi di 1 automaticamente quando non si riesce a specificarlo su INSERT o specificarlo come NULL .
• Gli ID sono sempre distinti l'uno dall'altro, ma ...
• Non fare alcuna ipotesi (nessuna lacuna, generata consecutivamente, non riutilizzata, ecc.) Sui valori dell'id tranne che essere univoci in un dato istante.

Note sottili:

• Al riavvio del server, il valore 'successivo' è 'calcolato' come MAX(id)+1 .
• Se l'ultima operazione prima dello spegnimento o crash andava a cancellare l'id più alto, quell'id può essere riutilizzato (dipende dal motore). Quindi, non fidarti di auto_increments essere permanentemente univoci ; sono unici in ogni momento.
• Per soluzioni multi-master o cluster, vedere auto_increment_offset e auto_increment_increment .
• Va bene avere qualcos'altro come PRIMARY KEY e semplicemente INDEX(id) . (Questa è un'ottimizzazione in alcune situazioni).
• L'utilizzo di AUTO_INCREMENT come "tasto PARTITION " è raramente utile; fare qualcosa di diverso.
• Varie operazioni possono "bruciare" i valori. Ciò accade quando pre-allocano i valori, quindi non li usano: INSERT IGNORE (con tasto dup), REPLACE (che è DELETE più INSERT ) e altri. ROLLBACK è un'altra causa di lacune negli ID.
• In Replica, non è possibile fidarsi degli ID per arrivare allo / agli schiavo / i in ordine crescente. Sebbene gli ID siano assegnati in ordine consecutivo, le istruzioni InnoDB vengono inviate agli slave in ordine COMMIT .