Tipi di indici Mysql e loro vantaggi e svantaggi

IndiceÈ un file speciale (gli indici delle tabelle InnoDB sono parte di uno spazio di archiviazione), che contiene i puntatori di riferimento per tutti i record della tabella.
Nota:
[1] Gli indici non sono onnipotenti! Gli indici possono accelerare le operazioni di ricerca dei dati, ma possono rallentare le operazioni di modifica dei dati. Ogni volta che si modifica un record di dati, l'indice deve essere aggiornato. Per compensare in qualche modo questo difetto, molte istruzioni SQL hanno un'opzione DELAY_KEY_WRITE. Questa opzione ha il ruolo di fermare temporaneamente MySQL dall'aggiornare l'indice immediatamente dopo l'inserimento di un nuovo record e la modifica di un record esistente, l'aggiornamento dell'indice verrà effettuato solo dopo l'inserimento o la modifica di tutti i record. In casi in cui è necessario inserire molti nuovi record in una tabella, l'opzione DELAY_KEY_WRITE avrà un effetto molto evidente.
[2] Inoltre, gli indici consumano un ampio spazio su disco. Pertanto, è consigliabile creare indici solo per le colonne che vengono查询 e ordinati più frequentemente. Attenzione, se una colonna contiene molti contenuti duplicati, la creazione di un indice non avrà un grande effetto pratico.
Teoricamente, è possibile creare un indice per ogni campo della tabella, ma MySQL limita il numero totale di indici nella stessa tabella a 16.

1. Indici delle tabelle InnoDB

Contrariamente alle tabelle MyISAM, gli indici sono di gran lunga più importanti per InnoDB. Sugli indici delle tabelle InnoDB, gli indici sono estremamente importanti per le tabelle InnoDB. Gli indici non solo agiscono durante la ricerca dei record di dati, ma sono anche fondamentali per il meccanismo di locking a livello di riga dei dati. 'Locking a livello di riga dei dati' significa bloccare i record individuali che vengono elaborati durante l'esecuzione delle operazioni transazionali, impedendo agli altri utenti di accedervi. Questo locking influenzerà (ma non è limitato a) i comandi SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE e anche gli comandi INSERT, UPDATE e DELETE.
Per motivi di efficienza, il locking a livello di riga dei dati delle tabelle InnoDB avviene effettivamente sugli indici, non sulla tabella stessa. Chiaramente, il meccanismo di locking a livello di riga dei dati funziona solo quando la tabella interessata ha un indice appropriato disponibile per il locking.

2. Limitazioni

Se la clausola WHERE contiene un operatore di disuguaglianza (WHERE coloum != ...), MySQL non sarà in grado di utilizzare l'indice.
Analogamente, se la clausola WHERE utilizza funzioni (WHERE DAY(column) = …), MySQL non può utilizzare l'indice.
Similmente, in operazioni JOIN (quando è necessario estrarre dati da più tabelle), MySQL può utilizzare l'indice solo quando i tipi di dati delle chiavi primarie e delle chiavi esterne sono gli stessi.
Se la clausola WHERE utilizza operatori di confronto LIKE e REGEXP, MySQL può utilizzare l'indice solo quando il primo carattere del modello di ricerca non è un carattere di wildcard. Ad esempio, se la condizione di ricerca è LIKE 'abc%', MySQL utilizzerà l'indice; se la condizione di ricerca è LIKE '%abc', MySQL non utilizzerà l'indice.
In operazioni ORDER BY, MySQL utilizza l'indice solo quando la condizione di ordinamento non è un'espressione di condizione di ricerca. (Nonostante ciò, in query che coinvolgono più tabelle, anche se sono disponibili indici, quegli indici non hanno alcun effetto nell'accelerare ORDER BY)
Se una colonna di dati contiene molti valori ripetuti, anche se è stato creato un indice per essa, non avrà un buon effetto. Ad esempio, se una colonna di dati contiene solo valori come "0/1" o "Y/N", non c'è bisogno di creare un indice per essa.　

Indice ordinario, indice univoco e indice primario

1. Indice ordinario

L'indice ordinario (definito dalla parola chiave KEY o INDEX) ha un unico compito, che è accelerare l'accesso ai dati. Pertanto, dovrebbe essere creato un indice solo per quelle colonne di dati che compaiono più spesso nei termini di ricerca (WHERE column = …) o nelle condizioni di ordinamento (ORDER BY column). Se possibile, dovrebbe essere scelto una colonna di dati più ordinata e più compatta (ad esempio, una colonna di tipo intero) per creare un indice.

2. Indice univoco
L'indice ordinario permette che la colonna di dati indicizzata contenga valori ripetuti. Ad esempio, poiché è possibile che due persone abbiano lo stesso nome, lo stesso nome può apparire due o più volte nella tabella dei dati del profilo personale degli impiegati.
Se è possibile determinare che una colonna di dati conterrà solo valori diversi, quando si crea un indice per questa colonna di dati, dovrebbe essere definito come un indice univoco utilizzando la parola chiave UNIQUE. I vantaggi di questo: primo, semplifica il lavoro di gestione dell'indice da parte di MySQL, e quindi l'indice diventa più efficiente; secondo, MySQL controllerà automaticamente se il valore del nuovo record nella colonna in questione è già apparso in qualche altro record in questa colonna; se lo è, MySQL rifiuterà di inserire quel nuovo record. Questo significa che l'indice univoco può garantire l'unicità dei record di dati. Di fatto, in molti casi, l'obiettivo della creazione di un indice univoco non è migliorare la velocità di accesso, ma solo evitare la duplicazione dei dati.

3. Indice primario

È stato ripetutamente enfatizzato in precedenza: è necessario creare un indice per il campo del primario, che è ciò che si chiama 'indice primario'. L'unica differenza tra l'indice primario e l'indice univoco è che il primo utilizza la parola PRIMARY anziché UNIQUE durante la definizione.

4. Indice del vincolo esterno

Se viene definita una condizione di vincolo esterno per un campo esterno, MySQL definisce un indice interno per aiutarsi a gestire e utilizzare il vincolo esterno in modo più efficiente.

5. Indice composto

L'indice può coprire più colonne di dati, come l'indice INDEX(columnA, columnB). La caratteristica di questo tipo di indice è che MySQL può scegliere di utilizzare uno di questi indici. Se l'operazione di ricerca necessita solo di un indice sulla colonna columnA, può utilizzare l'indice composto INDEX(columnA, columnB). Tuttavia, questo uso è applicabile solo alle combinazioni di colonne elencate in primo luogo nell'indice composto. Ad esempio, INDEX(A, B, C) può essere utilizzato come indice di A o (A, B), ma non come indice di B, C o (B, C).

6. Lunghezza dell'indice

Quando si definisce un indice per le colonne di tipo CHAR e VARCHAR, è possibile limitare la lunghezza dell'indice a un numero di caratteri specificato (questo numero deve essere inferiore al numero massimo di caratteri permessi da questo campo). Questo metodo ha il vantaggio di generare un file di indice di dimensioni più piccole e veloce nella ricerca. Nella maggior parte delle applicazioni, i dati di stringa nel database sono prevalentemente composti da vari nomi, quindi impostare la lunghezza dell'indice su 10-15 caratteri è sufficiente per restringere la ricerca a pochi record.
Quando si crea un indice per le colonne di tipo BLOB e TEXT, è necessario limitare la lunghezza dell'indice; la lunghezza massima dell'indice permessa da MySQL è di 255 caratteri.
Indice full-text

　　 L'indice ordinario sul campo del testo può solo accelerare la ricerca delle stringhe che appaiono all'inizio del campo (cioè i primi caratteri del campo). Se il campo contiene testi più lunghi composti da una o più parole, l'indice ordinario non ha alcun effetto. Questo tipo di ricerca si presenta spesso nella forma LIKE %word%, il che è complesso per MySQL e se la quantità di dati da elaborare è grande, il tempo di risposta sarà lungo.
Queste occasioni sono proprio il luogo in cui l'indice full-text (full-text index) può fare la sua comparsa. Durante la creazione di questo tipo di indice, MySQL crea un elenco di tutte le parole che appaiono nel testo, e l'operazione di ricerca si basa su questo elenco per trovare i record pertinenti. L'indice full-text può essere creato insieme al database o utilizzato in seguito quando necessario.

Il seguente comando aggiunge:}}
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

Con l'indice full-text, è possibile utilizzare il comando SELECT per cercare i record di dati che contengono uno o più dei termini specificati. Ecco la sintassi di base di questo tipo di comando di query:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

Il seguente comando recupererà tutti i record di dati che contengono word1, word2 e word3 nei campi column1 e column2.

Note: Le tabelle InnoDB non supportano l'indice full-text.

Ottimizzazione delle query e degli indici

　　　　　 Solo quando nel database ci sono abbastanza dati di test, i risultati del test di prestazioni hanno un valore di riferimento reale. Se nel database di test ci sono solo alcune centinaia di record di dati, spesso vengono caricati tutti nella memoria dopo l'esecuzione del primo comando di query, il che farà sì che tutte le successive query eseguite siano molto rapide - indipendentemente dall'uso dell'indice. Solo quando i record nel database superano 1000 e la quantità di dati anche supera la capacità della memoria del server MySQL, i risultati del test di prestazioni del database hanno senso.

　　　　　 Quando non si sa in quali colonne creare l'indice, spesso si può ottenere alcune aiuto dal comando EXPLAIN SELECT. Questo è semplicemente aggiungere una parola chiave EXPLAIN come prefisso a un comando SELECT normale. Con questa parola chiave, MySQL non esegue il comando SELECT, ma lo analizza. MySQL elenca in tabella il processo di esecuzione della query e le informazioni sugli indici (se presenti).
Nel risultato dell'output del comando EXPLAIN, la prima colonna indica il nome della tabella dei dati letti dal database, elencati nell'ordine di lettura. La colonna type specifica la relazione di associazione (JOIN) tra questa tabella e altre tabelle. Tra i vari tipi di relazioni di associazione, la più efficiente è system, seguita da const, eq_ref, ref, range, index e All (All significa: per ogni record della tabella di livello superiore, tutti i record di questa tabella devono essere letti - questo tipo di situazione può spesso essere evitato utilizzando un indice).

La colonna possible_keys indica tutte le varie indici che MySQL può utilizzare per cercare record. La colonna key indica l'indice effettivamente utilizzato da MySQL, la lunghezza in byte di questo indice è indicata nella colonna key_len. Ad esempio, per un'indice INTEGER, la lunghezza in byte sarà 4. Se viene utilizzato un indice composto, nella colonna key_len è possibile vedere quali parti specifiche di MySQL ha utilizzato. Di solito, è meglio che il valore nella colonna key_len sia più piccolo (cioè più veloce).
La colonna ref indica il nome della colonna in un altro tavolo di relazione. La colonna row indica il numero di righe che MySQL prevede di leggere da questo tavolo durante l'esecuzione della query. Il prodotto di tutti i numeri nella colonna row ci permette di capire approssimativamente quante combinazioni deve gestire questa query.
Infine, la colonna extra fornisce informazioni aggiuntive relative all'operazione JOIN, ad esempio, se MySQL deve creare una tabella dati temporanea durante l'esecuzione di questa query, vedrai il termine using temporary nella colonna extra.

Questo è tutto il contenuto dell'articolo, spero che sia utile per la tua apprendimento, e ti prego di sostenere fortemente il tutorial Yell.

Dichiarazione: il contenuto di questo articolo è stato raccolto da Internet, è di proprietà del rispettivo proprietario, il contenuto è stato contribuito e caricato autonomamente dagli utenti di Internet, il sito web non detiene i diritti di proprietà, non è stato editato manualmente e non assume alcuna responsabilità legale. Se trovi contenuti sospetti di copyright, sei invitato a inviare una e-mail a: notice#oldtoolbag.com (al momento dell'invio dell'e-mail, sostituisci # con @) per segnalare, fornendo prove pertinenti. Una volta verificata, il sito web rimuoverà immediatamente i contenuti sospetti di copyright.