Compito
La scansione della tabella è troppo lenta. Oppure l'inserimento e l'aggiornamento dei record richiedono troppo tempo.

Soluzione
La clausola ALTER TABLE può rimuovere e aggiungere non solo colonne, ma anche indici su tali colonne. Tali operazioni spesso migliorano le prestazioni del database. In genere, l'indicizzazione di una colonna utilizzata di frequente velocizza le istruzioni SELECT poiché non è necessario eseguire la scansione completa delle tabelle. In alcuni casi, anche l'eliminazione dell'indice può essere utile. Ogni volta che una riga viene aggiornata, MySQL deve aggiornare tutti gli indici contenenti le colonne modificate. Se utilizzi raramente un indice, ciò potrebbe indicare che la tabella è sovraccarica di indici e la rimozione di uno di essi potrebbe migliorare l'efficienza dell'elaborazione della tabella.

Discussione
Per facilità d'uso, iniziamo creando una nuova istanza della tabella di test mytbl. Utilizziamo le clausole DROP TABLE e CREATE TABLE per eliminare la versione esistente e ricreare la tabella nella sua forma originale:

DROP TABLE mytbl;
CREA TABELLA mytbl
ioINT,
cCAR(1)
);

All'inizio del capitolo abbiamo utilizzato MOSTRA COLONNE per monitorare i risultati della modifica di una tabella.

Ora esamineremo le modifiche all'indice e visualizzeremo i risultati utilizzando MOSTRA INDICE anziché MOSTRA COLONNE. Al momento non sono presenti indici nella tabella perché non sono stati specificati nell'istruzione CREATE TABLE:

Mysql> MOSTRA INDICE DA mytbl;
Set vuoto (0,00 secondi)

Aggiunta di indici
Esistono quattro tipi di clausole che aggiungono indici a una tabella:

ALTER TABLE nome_tabella ADD PRIMARY KEY (column_list);
ALTER TABLE nome_tabella ADD UNIQUE nome_indice (elenco_colonne);
ALTER TABLE nome_tabella ADD INDEX nome_indice (elenco_colonne);
ALTER TABLE nome_tabella ADD FULLTEXT nome_indice (elenco_colonne);

La prima clausola aggiunge una chiave primaria (PRIMARY KEY), ovvero i valori indicizzati devono essere univoci e non contenere NULL. La seconda clausola crea un indice per il quale i valori devono essere univoci (ad eccezione dei valori NULL, che possono verificarsi più volte). La terza clausola aggiunge un indice regolare in cui qualsiasi valore può apparire più volte. Quest'ultimo crea uno speciale indice FULLTEXT, che viene utilizzato per visualizzare il testo.

Se è presente un nome_indice nelle costruzioni delle frasi, è facoltativo.

Se non lo specifichi, MySQL assegnerà automaticamente un nome all'indice.

Le colonne da indicizzare sono specificate nel parametro column_list, un elenco di uno o più nomi di colonne separati da virgole.

Diamo un'occhiata a due semplici esempi: il primo crea un indice a colonna singola su c, e il secondo crea un indice a più colonne che coinvolge c e i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

In molti casi, le colonne da indicizzare devono essere dichiarate diverse da NULL. Ad esempio, se crei mytbl come tabella ISAM, le istruzioni ADD INDEX di cui sopra falliranno perché le tabelle ISAM non consentono NULL in nessun tipo di indice. Inoltre, gli indici PRIMARY KEY non possono contenere valori NULL, indipendentemente dal tipo di tabella. Se stai tentando di aggiungere un indice e MySQL lamenta problemi NULL, utilizza la clausola ALTER TABLE per modificare le colonne pertinenti in non NULL e prova a creare nuovamente l'indice. Ad esempio, se provi a rendere la colonna i la chiave primaria, si verificherà un errore:

MySQL>
ERRORE 1171 alla riga 5: tutte le parti di una PRIMARY KEY devono essere NON NULL;
Se hai bisogno di NULL in una chiave, usa invece UNIQUE

Devi prima ridefinire la colonna i in modo che non consenta NULL:

Mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Tutto ha funzionato.

E nel primo caso, come si può vedere dal messaggio di errore, invece della chiave primaria, potresti creare un indice UNIQUE se hai bisogno di valori NULL nell'indice. Rimozione degli indici Per eliminare un indice, utilizzare uno dei seguenti frasi:

ALTER TABLE nome_tabella DROP PRIMARY KEY;
ALTER TABLE nome_tabella DROP INDEX nome_indice;

Il modo più semplice per eliminare un indice PRIMARY KEY è perché non è necessario conoscere il nome dell'indice:

ALTER TABLE mytbl DROP PRIMARY KEY;

Per eliminare un indice che non è una chiave primaria, è necessario specificarne il nome. Se non sai come si chiama l'indice, usa SHOW INDEX. Per evitare di produrre righe troppo lunghe, utilizzare l'output verticale (\G):

Mysql> MOSTRA INDICE DA mytbl\G
************************** 1. riga ******************** * ******
Tabella: mytbl
Non_unico: 1
Nome_chiave: c
Seq_in_indice: 1
Nome_colonna: c
Collazione: A
Cardinalità: NULL
Sotto_parte: NULL
Imballato: NULL
Commento:
************************** 2. riga ******************** * ******
Tabella: mytblNon_unique: 1
Nome_chiave: c_2
Seq_in_indice: 1
Nome_colonna: c
Collazione: A
Cardinalità: NULL
Sotto_parte: NULL
Imballato: NULL
Commento:
************************** 3. riga ******************** * ******
Tabella: mytbl
Non_unico: 1
Nome_chiave: c_2
Seq_in_index: 2
Nome_colonna: i
Collazione: A
Cardinalità: NULL
Sotto_parte: NULL
Imballato: NULL
Commento:

I valori Key_name e Seq_in_index corrispondono ai nomi degli indici e alle posizioni delle colonne nell'indice. Ora sai che la tabella mytbl ha un indice a colonna singola denominato c e un indice a più colonne denominato c_2 (questi nomi sono stati scelti da MySQL per i due indici che abbiamo creato in precedenza). L'istruzione che rimuove gli indici sarebbe:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

Come puoi vedere, è possibile eseguire più operazioni in un'unica istruzione ALTER TABLE, che deve essere separata da virgole.

L'indicizzazione delle tabelle viene utilizzata per ridurre i tempi di ricerca dei dati. Senza indici la ricerca avviene semplicemente enumerando tutti i valori della tabella, operazione che richiede molto tempo.

Nelle tabelle MySQL vengono utilizzati i seguenti tipi di indici:

Chiave primaria
Indice univoco
Indice regolare
Indice del testo completo

Oltre agli indici di cui sopra, viene utilizzato anche indice del testo completo, ma non se ne parla in questo articolo.

Chiave primaria

La chiave primaria viene utilizzata per identificare in modo univoco i record in una tabella. Tra i punti principali riguardanti la chiave primaria si segnala quanto segue:

1. una tabella non può avere una sola chiave univoca;

2. il valore della chiave deve essere univoco all'interno della tabella;

3. La chiave primaria non può essere di tipo NULL.

Esempio di creazione di una chiave primaria:

Creazione di una chiave primaria durante la creazione di una tabella nella descrizione del campo

Esempio 3:

Se la chiave primaria è un campo di testo allora è necessario indicare tra parentesi il numero di caratteri coinvolti nell'indicizzazione.

Esempio 5:

Se la tabella è già stata creata, puoi aggiungere un indice primario come segue.

$query = "ALTER table auto ADD CONSTRAINT PRIMARY KEY (id_number)";
$risultato = $connessione->query($query);
?>

Indici regolari e univoci

Oltre all'indice primario, una tabella può contenere anche indici regolari e univoci. A differenza dell'indice primario, possono esistere più di questi indici. La differenza tra un indice regolare e un indice univoco è che un indice univoco non può avere valori identici.

La dichiarazione degli indici regolari viene eseguita utilizzando parole chiave chiave O indice. Se è necessario dichiarare un indice univoco, prima chiave O indice viene inserita la parola chiave unico.

Esempio 6:

Crea un indice univoco

Rimozione degli indici

La rimozione degli indici si effettua utilizzando il comando drop index; utilizzandolo, eventuali indici vengono cancellati.

Esempio 8:

$connessione = new mysqli("localhost","root","","mybase");
$query = "rilascia modello indice in automatico";
$risultato = $connessione->query($query);
?>

piè di pagina in Word 2007, 0000002111

Utilizzo degli indici in MySQL

Gli indici vengono utilizzati per trovare rapidamente le righe con un valore specificato in una colonna. Senza un indice, una tabella viene letta attraverso l'intera tabella, a partire dal primo record, finché non vengono trovate le righe corrispondenti. Più grande è il tavolo, maggiore sarà il sovraccarico. Se la tabella contiene un indice sulle colonne in questione, MySQL può determinare rapidamente la posizione di ricerca al centro del file di dati senza esaminare tutti i dati. Per una tabella contenente 1000 righe, l'operazione sarà almeno 100 volte più veloce rispetto all'iterazione di tutti i record in sequenza. Tuttavia, nel caso in cui sia necessario accedere a quasi tutte le 1000 righe, le letture sequenziali saranno più veloci perché non sono richieste ricerche sul disco.

Tutti gli indici MySQL (PRIMARY, UNIQUE e INDEX) sono archiviati come alberi B. Le stringhe vengono compresse automaticamente, rimuovendo il prefisso e gli spazi finali.

Gli indici vengono utilizzati per:

Trova rapidamente le righe che corrispondono a una clausola WHERE.

Recupera righe da altre tabelle durante l'esecuzione dei join.

Trova i valori MAX() o MIN() per una determinata colonna indicizzata. Questa operazione è ottimizzata dal preprocessore, che controlla che non si stia utilizzando WHERE key_part_4 = costante, su tutte le parti della chiave composta< N . В этом случае MySQL сделает один просмотр ключа и заменит выражение константой MIN() . Если все выражения заменяются константой, запрос моментально вернет результат:

SELEZIONA MIN(parte_chiave2),MAX(parte_chiave2) FROM nome_tabella dove parte_chiave1=10

Eseguire l'ordinamento o il raggruppamento in una tabella se queste operazioni vengono eseguite sul prefisso più a sinistra della chiave utilizzata (ad esempio, ORDER BY parte_chiave_1,parte_chiave_2). Se tutte le parti di una chiave sono seguite da DESC , la chiave viene letta in ordine inverso

In alcuni casi, la query può essere ottimizzata per recuperare valori senza accedere al file di dati. Se tutte le colonne utilizzate in una tabella sono numeriche e formano il prefisso più a sinistra di una chiave, per garantire una maggiore velocità, i valori da cercare possono essere recuperati direttamente dall'albero dell'indice:

SELEZIONA parte_chiave3 DA nome_tabella DOVE parte_chiave1=1

Supponiamo che venga chiamata la seguente istruzione SELECT:

MySQL> SELEZIONA * FROM nome_tbl DOVE col1=val1 AND col2=val2;

Se esiste un indice a più colonne sulle colonne col1 e col2, le righe corrispondenti possono essere selezionate direttamente. Nel caso in cui esistano indici separati sulle colonne col1 e col2, l'ottimizzatore tenta di trovare l'indice più restrittivo determinando quale indice trova il minor numero di righe e utilizza tale indice per recuperare tali righe.

Se una determinata tabella ha un indice a più colonne, l'ottimizzatore può utilizzare qualsiasi prefisso più a sinistra di tale indice per trovare le righe. Ad esempio, se si dispone di un indice su tre colonne (col1,col2,col3), è possibile effettuare ricerche indicizzate su (col1), (col1,col2) e (col1,col2,col3).

In MySQL non è possibile utilizzare un indice parziale a meno che le colonne non formino il prefisso più a sinistra di quell'indice. Supponiamo che tu abbia i comandi SELECT mostrati di seguito:

MySQL> SELEZIONA * FROM nome_tbl DOVE col1=val1; mysql> SELEZIONA * FROM nome_tbl DOVE col2=val2; mysql> SELEZIONA * FROM nome_tbl DOVE col2=val2 AND col3=val3;

Se esiste un indice su (col1,col2,col3), solo la prima query mostrata sopra utilizza quell'indice. La seconda e la terza query includono colonne indicizzate, ma (col2) e (col2,col3) non sono la parte più a sinistra dei prefissi (col1,col2,col3).

MySQL utilizza anche gli indici per i confronti LIKE se l'argomento nell'espressione LIKE è una stringa costante che non inizia con un carattere jolly. Ad esempio, i seguenti comandi SELECT utilizzano gli indici:

MySQL> SELEZIONA * DA tbl_name DOVE key_col LIKE "Patrick%"; mysql> SELEZIONA * DA tbl_name DOVE key_col LIKE "Pat%_ck%";

Il primo comando esamina solo le righe con "Patrick"<= key_col < "Patricl" , а во второй - только строки с "Pat" <= key_col < "Pau" .

I seguenti comandi SELECT non utilizzeranno gli indici:

MySQL> SELEZIONA * DA tbl_name DOVE key_col LIKE "%Patrick%"; mysql> SELEZIONA * FROM nome_tbl DOVE key_col COME other_col;

Nel primo comando, il valore LIKE inizia con un carattere jolly. Nel secondo comando, il valore LIKE non è una costante.

MySQL 4.0 introduce una diversa ottimizzazione sull'espressione LIKE. Se viene utilizzata l'espressione... LIKE "%string%" e la lunghezza della stringa è maggiore di 3 caratteri, MySQL utilizzerà l'algoritmo Turbo Boyer-Moore per inizializzare un modello per la stringa e quindi utilizzerà quel modello per eseguire la ricerca più veloce.

Durante la ricerca utilizzando nome_colonna IS NULL, verranno utilizzati gli indici se nome_colonna è un indice.

MySQL solitamente utilizza l'indice che trova il minor numero di righe. L'indice viene utilizzato sulle colonne confrontate utilizzando i seguenti operatori: =, >, >=,<, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something% .

Se un indice non copre tutti i livelli AND nella clausola WHERE, non viene utilizzato per ottimizzare la query. In altre parole: affinché un indice sia utilizzabile, il prefisso di quell'indice deve apparire in ciascun gruppo AND.

Le seguenti clausole WHERE utilizzano gli indici:

WHERE indice_parte1=1 AND indice_parte2=2 AND altra_colonna=3 ... WHERE indice=1 OR A=10 AND indice=2 /* indice = 1 OR indice = 2 */ ... WHERE indice_parte1="ciao" AND indice_parte_3= 5 /* ottimizzato come "index_part1="ciao"" */ ... WHERE indice1=1 e indice2=2 o indice1=3 e indice3=3; /* È possibile utilizzare un indice su indice1, ma non su indice2 o indice 3 */

Le seguenti clausole WHERE Non utilizzare gli indici:

WHERE indice_parte2=1 AND indice_parte3=2 /* indice_parte_1 non viene utilizzato */ ... WHERE indice=1 OR A=10 /* L'indice non viene utilizzato in entrambe le parti dell'AND */ ... WHERE indice_parte1=1 OR indice_parte2 =10 /* Non esiste un indice che copra tutte le righe*/

In alcuni casi, MySQL non utilizza un indice anche se è possibile. Di seguito sono riportati alcuni esempi di tali situazioni:

Se l'utilizzo di un indice richiede che MySQL attraversi più del 30% delle righe in una determinata tabella (in questi casi, l'attraversamento della tabella sarà probabilmente molto più veloce poiché saranno necessarie meno ricerche). Una cosa da tenere a mente è che se una query come questa utilizza LIMIT solo sul sottoinsieme di righe da recuperare, MySQL utilizzerà comunque l'indice, poiché un piccolo numero di righe può essere trovato molto più velocemente per restituire il risultato.

Indici di colonna

Colonne di tutti i tipi possono essere indicizzate in MySQL. L'utilizzo degli indici sulle colonne pertinenti è un buon modo per velocizzare le operazioni SELECT.

Il numero massimo di chiavi e la lunghezza massima degli indici sono determinati dal motore delle tabelle. Puoi avere almeno 16 chiavi su tutti i motori di tabella e una lunghezza totale dell'indice di almeno 256 byte.

Per le colonne CHAR e VARCHAR, è possibile indicizzare il prefisso della colonna. Questa operazione è molto più veloce e richiede meno spazio su disco rispetto all'indicizzazione dell'intera colonna. La sintassi utilizzata nel comando CREATE TABLE per indicizzare un prefisso di colonna è simile alla seguente:

CHIAVE nome_indice (nome_col(lunghezza))

L'esempio seguente crea un indice sui primi 10 caratteri nella colonna del nome:

MySQL> CREA TABELLA prova ( -> nome CHAR(200) NOT NULL, -> CHIAVE nome_indice(nome(10)));

Per le colonne BLOB e TEXT, il prefisso della colonna deve essere indicizzato. Non è possibile indicizzare un'intera colonna.

In MySQL versione 3.23.23 e successive, puoi anche creare indici FULLTEXT personalizzati. Sono utilizzati per la ricerca del testo completo. Gli indici full-text FULLTEXT supportano solo le tabelle MyISAM. Possono essere creati solo su colonne VARCHAR e TEXT. L'indicizzazione viene sempre eseguita sull'intera colonna e l'indicizzazione parziale non è supportata.

Indici a più colonne

MySQL può creare indici su più colonne. Un indice può includere fino a 15 colonne (sulle colonne CHAR e VARCHAR è possibile utilizzare anche il prefisso della colonna come parte dell'indice)

Un indice a più colonne può essere pensato come un array ordinato contenente valori creati concatenando i valori delle colonne indicizzate.

MySQL utilizza indici a più colonne in modo tale che le query vengano eseguite rapidamente quando si specifica la parte nota per la prima colonna dell'indice nella clausola WHERE, anche se non si specificano valori per le altre colonne.

Supponiamo che venga creata la seguente tabella:

MySQL> CREA TABELLA prova ( -> id INT NOT NULL, -> cognome CHAR(30) NOT NULL, -> nome_char(30) NON NULL, -> CHIAVE PRIMARIA (id), -> INDICE nome(cognome,nome));

L'indice del nome è un indice sulle colonne last_name e first_name. Questo indice verrà utilizzato per le query che specificano valori nella regione nota per last_name o per entrambe le colonne last_name e first_name. Pertanto, l'indice dei nomi verrà utilizzato nelle seguenti query:

MySQL> SELECT * FROM test WHERE last_name="Widenius"; mysql> -> AND nome="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND (nome_nome="Michael" O nome_nome="Monty"); mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND nome >="M" AND nome< "N";

Per ulteriori informazioni su come MySQL utilizza gli indici per migliorare le prestazioni delle query,

Apertura e chiusura di tabelle in MySQL

I parametri table_cache, max_connections e max_tmp_tables specificano il numero massimo di file che il server mantiene aperti. Se aumenti uno o entrambi questi parametri, potresti riscontrare il limite del sistema operativo sul numero di descrittori di file aperti per processo. In molti sistemi, tuttavia, questo limite può essere aumentato. Poiché la modalità di modifica di questo valore può variare da sistema a sistema, è necessario consultare la documentazione del sistema operativo per ogni caso specifico.

I valori table_cache e max_connections sono correlati. Ad esempio, per 200 connessioni simultanee, è necessaria una cache delle tabelle di almeno 200 * n, dove n è il numero massimo di tabelle correlate. È inoltre necessario riservare alcuni descrittori di file aggiuntivi per tabelle e file temporanei.

Dovresti assicurarti che il tuo sistema operativo possa gestire il numero di descrittori di file aperti presupposti da questa impostazione table_cache. Se table_cache è impostato su un valore troppo alto, MySQL può esaurire i descrittori di file, interrompere la connessione, fallire le query e diventare molto inaffidabile. Dovresti anche tenere presente che il motore di tabelle MyISAM richiede due descrittori di file per ciascuna tabella aperta univoca. Il numero di descrittori di file consentiti da MySQL può essere aumentato utilizzando l'opzione di avvio --open-files-limit=#.

Il numero massimo di tabelle nella cache delle tabelle aperte sarà il numero specificato in table_cache (il valore predefinito è 64; questo numero può essere modificato utilizzando l'opzione -O table_cache=# per mysqld). Tieni presente che MySQL potrebbe temporaneamente aprire più tabelle per eseguire query.

Una tabella inutilizzata viene chiusa e rimossa dalla cache delle tabelle nelle seguenti situazioni:

Quando la cache è piena e il thread tenta di aprire una tabella che non è presente in questa cache.

Quando la cache contiene più di valori di input di table_cache e il thread non utilizza più la tabella.

Quando qualcuno esegue mysqladmin refresh o mysqladmin flush-tables .

Quando qualcuno esegue FLUSH TABLES .

Quando la cache della tabella si riempie, il server utilizza la seguente procedura per allocare gli input della cache da utilizzare:

Le tabelle che non sono attualmente in uso vengono liberate in ordine di utilizzo più vecchio.

Se la cache è piena e non è possibile liberare alcuna tabella ed è necessario aprire una nuova tabella, la cache viene temporaneamente espansa quanto necessario.

Se la cache è in uno stato temporaneamente esteso e una tabella passa dallo stato utilizzato a quello non utilizzato, la tabella viene chiusa e liberata dalla cache.

La tabella viene aperta ad ogni accesso contemporaneo. Ciò significa che se ci sono due thread che accedono alla stessa tabella, o se si accede alla stessa tabella due volte nella stessa query (utilizzando AS), allora quella tabella deve essere aperta due volte. L'apertura di una tabella per la prima volta richiede due descrittori di file; per ogni utilizzo aggiuntivo - solo uno. Per il file indice viene utilizzato un ulteriore handle di prima apertura; questo handle è condiviso da tutti i thread.

Quando apri una tabella con il comando HANDLER table_name OPEN, viene creato un oggetto tabella dedicato per quel thread. Questo oggetto tabella non è accessibile ad altri thread e non verrà chiuso finché il thread non emette il comando HANDLER table_name CLOSE o il thread stesso non viene terminato. Se ciò accade, la tabella viene reinserita nella cache della tabella (se non è piena).

Per vedere se la cache della tabella è troppo piccola, dovresti controllare la variabile Opened_tables. Se il valore è sufficientemente grande, anche se non hai eseguito il comando FLUSH TABLES molto spesso, devi aumentare la cache della tabella.

intestazioni e piè di pagina in Word 2007, 000000211111

Tutti gli indici MySQL (PRIMARY, UNIQUE e INDEX) sono archiviati come alberi B. Le stringhe vengono compresse automaticamente, rimuovendo il prefisso e gli spazi finali (vedere la sezione 6.5.7 Sintassi dell'istruzione CREATE INDEX).

Gli indici vengono utilizzati per:

Trova rapidamente le righe che corrispondono a una clausola WHERE.
Recupera righe da altre tabelle durante l'esecuzione dei join.
Trova i valori MAX() o MIN() per una determinata colonna indicizzata. Questa operazione è ottimizzata da un preprocessore che controlla se lo stai utilizzando DOVE parte_chiave_4 = costante, su tutte le parti della chiave composta . In questo caso, MySQL eseguirà una ricerca sulla chiave e sostituirà l'espressione con la costante MIN(). Se tutte le espressioni vengono sostituite con una costante, la query restituirà immediatamente il risultato: SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Eseguire l'ordinamento o il raggruppamento su una tabella se queste operazioni vengono eseguite sul prefisso più a sinistra della chiave utilizzata (ad esempio ORDINA PER parte_chiave_1,parte_chiave_2). Se tutte le parti di una chiave sono seguite da DESC , la chiave viene letta in ordine inverso (vedere sezione 5.2.7 Come MySQL ottimizza ORDER BY ).
In alcuni casi, la query può essere ottimizzata per recuperare valori senza accedere al file di dati. Se tutte le colonne utilizzate in una determinata tabella sono numeriche e formano il prefisso più a sinistra per una determinata chiave, quindi per fornire maggiore velocità, i valori cercati possono essere recuperati direttamente dall'albero dell'indice: SELECT key_part3 FROM table_name WHERE key_part1=1

Supponiamo che venga chiamata la seguente istruzione SELECT:

Mysql> SELECT * FROM nome_tbl DOVE col1=val1 AND col2=val2;

In MySQL non è possibile utilizzare un indice parziale a meno che le colonne non formino il prefisso più a sinistra di quell'indice. Supponiamo che tu abbia i comandi SELECT mostrati di seguito:

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM nome_tbl DOVE col2=val2 AND col3=val3;

Mysql> SELECT * FROM tbl_name DOVE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name DOVE key_col LIKE "Pat%_ck%";

Il primo comando esamina solo le righe con "Patrick" e nella seconda riga solo con "Pat" .

I seguenti comandi SELECT non utilizzeranno gli indici:

Mysql> SELECT * FROM tbl_name DOVE key_col LIKE "%Patrick%"; mysql> SELECT * FROM nome_tbl DOVE key_col COME other_col;

Nel primo comando, il valore LIKE inizia con un carattere jolly. Nel secondo comando, il valore LIKE non è una costante.

MySQL 4.0 introduce una diversa ottimizzazione sull'espressione LIKE. Se viene utilizzata l'espressione ... LIKE "%string%" e la lunghezza della stringa è maggiore di 3 caratteri, MySQL utilizzerà l'algoritmo Turbo Boyer-Moore per inizializzare un modello per la stringa e quindi utilizzerà quel modello per eseguire la ricerca più veloce.

Durante la ricerca utilizzando nome_colonna IS NULL, verranno utilizzati gli indici se nome_colonna è un indice.

MySQL solitamente utilizza l'indice che trova il minor numero di righe. Un indice viene utilizzato sulle colonne confrontate utilizzando i seguenti operatori: =, >, >= e LIKE con un prefisso che non contiene un carattere jolly come qualcosa% .

Se un indice non copre tutti i livelli AND in una clausola WHERE, non viene utilizzato per ottimizzare la query. In altre parole: affinché un indice sia utilizzabile, il prefisso di quell'indice deve apparire in ciascun gruppo AND.

Le seguenti clausole WHERE utilizzano gli indici:

Le seguenti clausole WHERE Non utilizzare gli indici:

In alcuni casi, MySQL non utilizza un indice anche se è possibile. Di seguito sono riportati alcuni esempi di tali situazioni:

Se l'utilizzo di un indice richiede che MySQL attraversi più del 30% delle righe in una determinata tabella (in questi casi, l'attraversamento della tabella sarà probabilmente molto più veloce poiché saranno necessarie meno ricerche). Tieni presente che se una query come questa utilizza LIMIT solo sul sottoinsieme di righe da recuperare, MySQL utilizzerà comunque l'indice, poiché un numero limitato di righe può essere trovato molto più velocemente per restituire il risultato.
Se l'intervallo di modifica dell'indice può contenere valori NULL quando si utilizzano le espressioni ORDER BY ... DESC.

Se hai altre domande o qualcosa non è chiaro, benvenuto nel ns

Negli articoli precedenti ho spesso menzionato indici in MySQL. e ho promesso che ve ne avrei parlato presto. Quindi, questa volta è arrivata e oggi imparerai Indici MySQL, sul loro scopo e su come crearli.

Gli indici vengono utilizzati per accelerare il recupero dei dati dalle tabelle del database. Infatti, indice in MySQL- questo sta ordinando un campo specifico in una tabella. Cioè, se un campo viene reso indice, l'intera tabella verrà ordinata in base a questo campo. Perché è vantaggioso?

Diciamo che la nostra tabella contiene 1000000 record. Ogni voce ha un identificatore univoco ID. E diciamo che dobbiamo estrarre un disco da ID = 530124. Se non è presente alcun indice, allora MySQL scorrerà tutti i record nella tabella finché non troverà quello di cui ha bisogno. Nel peggiore dei casi, sarà costretto a risolvere 1000000 record. Naturalmente, questo sarà un tempo molto lungo. E se ci fosse un indice (ovvero, il campo verrebbe ordinato), il record verrebbe campionato in media in 100.000 volte Più veloce. Come puoi vedere, i vantaggi sono evidenti.

Tuttavia, gli indici presentano un difetto significativo, che non consente a ciascun campo della tabella di essere un indice. In effetti, un indice è un'altra tabella, ma semplicemente con il campo corrispondente ordinato. Cioè, creando un indice su un campo, ne crei un'altro esattamente la stessa tabella, che occuperà ulteriore spazio su disco.

Un altro piccolo inconveniente indici in MySQL Il problema è che le richieste di inserimento di nuovi record costringono a riordinare la tabella. Di conseguenza, l'inserimento di nuovi record richiederà un po' più tempo del solito. Ma non dimenticare che nella maggior parte dei casi devi farlo molto meno frequentemente del campionamento, quindi questo meno non è significativo.

Come creare un indice in MySQL?

Per le chiavi primarie ( CHIAVE PRIMARIA) l'indice viene creato automaticamente, ma per gli altri campi la sequenza di azioni è PHPMyAdmin Prossimo:

E infine vorrei fare un breve riassunto per farvi capire: " Quando creare indici MySQL":

Se un campo viene campionato molto spesso, allora dovrebbe essere creato un indice.
Se i record vengono aggiunti alla tabella molto spesso e il campionamento avviene raramente (questo a volte accade), non è necessario creare indici.

E un'altra cosa. Se all'improvviso vedi che le tue richieste di campionamento sono molto lente, analizzane il motivo. Molto probabilmente, devi solo aggiungere un file index. In generale, provalo e tutto diventerà chiaro.