Guida di studio sull'indice MySQL

In MySQL, gli indici possono essere classificati in due tipi: indice hash e indice BTREE.

In quali casi può essere utilizzato l'indice BTREE?

1. Corrispondenza completa dell'indice

Ad esempio:

orderID="123"

2. Ricerca a intervalli del prefisso più a sinistra dell'indice

Ad esempio: creare un indice combinato sui campi userid e date.
Quindi se si inserisce userId come condizione, questo userid può utilizzare l'indice, se si inserisce direttamente date come condizione, non può utilizzare l'indice.

3. Ricerca a intervalli del prefisso della colonna

Ad esempio: order_sn like '134%' in questo modo può utilizzare l'indice.

4. Corrispondenza della ricerca a intervalli

createTime>'2015-01-09' and createTime<'2015-01-10'

5. Corrispondenza esatta della colonna più a sinistra e corrispondenza a intervalli dell'altra colonna

Ad esempio:

userId=1 and createTime>'2016-9-18'

6. Le ricerche che accedono solo all'indice, dette indici di copertura, e l'indice include i dati delle colonne della query.

Limitazioni dell'indice BTREE

1. Se la ricerca non inizia dalla colonna più a sinistra dell'indice, non può utilizzare l'indice.

Ad esempio, creare un indice combinato:

Il campo orderId e createTime creano un indice combinato, se si inserisce solo la condizione createTime senza la condizione orderId, allora non può essere utilizzato questo indice.

2. Non è possibile saltare le colonne dell'indice durante l'uso dell'indice.

Tre colonne:

La data, il nome, il numero di telefono formano una colonna e un indice, se durante la ricerca si inseriscono solo la data e il numero di telefono, allora può essere utilizzato solo l'indice della data per filtrare.

3. Le operazioni NOT IN e <> non possono utilizzare l'indice.

4. Se la query contiene una ricerca a intervalli su una colonna, tutte le colonne a destra non possono utilizzare l'indice.

Caratteristiche dell'indice hash

L'indice hash è implementato su una tabella hash, può essere utilizzato l'indice hash solo quando le condizioni di ricerca corrispondono esattamente a tutte le colonne dell'indice hash. Solo le ricerche di uguaglianza sono ammesse.

Per tutte le colonne dell'indice hash, il motore di archiviazione calcola un codice hash per ogni riga, e l'indice hash memorizza il codice hash.

Limitazioni:

1. Deve essere letto due volte, prima di leggere l'indice hash per trovare la riga corrispondente, poi leggere i dati della riga corrispondente.

2. L'indice hash non può essere utilizzato per ordinare.

3. Supporta solo ricerca esatta, non ricerca su indici parziali né ricerca a intervalli.

Conflitto hash:

L'indice hash non può essere utilizzato per campi con scarsa selezionalità, ma deve essere utilizzato per creare indici hash su colonne con alta selezionalità.

Ad esempio: non creare un indice hash sulla colonna del genere.

Perché utilizzare gli indici?

1. Gli indici riducono significativamente la quantità di dati che il motore di archiviazione deve scansionare. Gli indici sono più piccoli della dimensione dei dati.

2. Gli indici ci aiutano a ordinare per evitare l'uso di tabelle temporanee. Gli indici sono ordinati.

3. Gli indici possono trasformare I/O casuale in I/O sequenziale.

È meglio avere più indici?

1. Gli indici aumentano il costo delle operazioni di scrittura.

2. Troppi indici possono aumentare il tempo di ottimizzazione delle query e di selezione.

Strategia di creazione di indici

1. Non è possibile utilizzare espressioni o funzioni sulle colonne indizzate.

Ad esempio: select * from product where to_days(out_date) –to_days(current_date)<=30, out_date è una colonna indizzata.

Modificato in:

select* from product where out_date<date_add(current_date,interval 30 day)

2. La dimensione degli indici non può superare un valore determinato.
Le colonne indizzate innoDB hanno una lunghezza di 200 caratteri.

3. Scegliere la lunghezza prefissa e la选择性 delle colonne indizzate.

create index idx_NAME on table (account);

4. Indici combinati

Come scegliere l'ordine delle colonne indizzate.

1. Le colonne che vengono spesso indizzate.

2. Dare la priorità alle colonne con alta选择性.

3. Creare indici per le colonne più piccole.

Dichiarazione: il contenuto di questo articolo è stato raccolto da Internet, il copyright spetta ai rispettivi proprietari, il contenuto è stato contribuito e caricato autonomamente dagli utenti di Internet, questo sito non detiene il diritto di proprietà, non è stato editato manualmente e non assume alcuna responsabilità legale correlata. Se trovi contenuti sospetti di violazione del copyright, ti preghiamo di inviare una e-mail a: notice#oldtoolbag.com (al momento dell'invio dell'e-mail, sostituisci # con @) per segnalare il problema e fornire prove pertinenti. Una volta verificata, questo sito eliminerà immediatamente il contenuto sospetto di violazione del copyright.