Crea indice in MySQL: tutorial sull'indice MySQL [2022]
Pubblicato: 2021-01-01Un Data Scientist o un programmatore dovrà lavorare con grandi quantità di dati e deve essere in grado di gestirli in modo efficiente per un'esecuzione più rapida. Hanno anche bisogno di sapere come sono organizzati i dati e i metodi più veloci per accedere facilmente ai dati particolari necessari.
Poiché MySQL è un sistema di gestione di database relazionali che ha applicazioni in vari settori come database web, applicazioni di e-commerce, social media ecc. i data scientist devono conoscere tutte le tecniche relative a MySQL, una delle quali è quella degli indici. L'articolo descrive come creare Index in MySQL.
Sommario
Che cos'è un indice?
In MySQL, gli indici vengono utilizzati per ordinare i dati in modo sequenziale organizzato e recuperare i dati in modo efficiente e rapido. Vengono creati utilizzando una o più colonne che verranno utilizzate per filtrare i dati. Gli indici sono un tipo di tabella che mantiene un campo primario o di indice e un puntatore a ciascun record nella tabella principale.
Per una migliore comprensione, è possibile utilizzare come esempio un indice di un libro. Invece di cercare una pagina sfogliando tutte le pagine di un libro, è possibile controllare l'indice del libro per trovare la pagina che si sta cercando.
Nella maggior parte dei sistemi di database sono necessarie prestazioni elevate e veloci. Molte aziende investono ingenti somme di denaro in hardware per recuperi e manipolazioni di dati più rapidi. Ma ottimizzando il database, i costi possono essere ridotti.
Il tempo di risposta è in genere più lungo perché i record vengono archiviati in modo casuale e le query di ricerca devono trovare i dati necessari scorrendo l'intero record archiviato. Gli indici rendono questa ricerca più facile e veloce.
Leggi: Rinomina il nome della colonna in SQL
Crea indice
Puoi creare un indice in MySQL su una tabella usando la seguente sintassi di base.
CREATE INDEX nome_indice nome_tabella (nome_colonna);
Puoi anche creare un indice univoco su una tabella. Ciò significa che il nome è univoco e che due righe qualsiasi non possono avere lo stesso nome. La seguente sintassi può essere utilizzata per questo.
CREA INDICE UNICO nome_indice ON nome_tabella (nome_colonna);
Se viene creato un indice secondario per una colonna, MySQL memorizza i valori in una struttura dati separata come B-Tree e Hash. In MySQL, se le colonne sono colonne stringa, puoi creare un indice per la parte iniziale dei valori della colonna della colonna stringa usando la sintassi:
nome_colonna(lunghezza)
Per creare la parte chiave del prefisso di colonna quando viene creata la tabella, è possibile utilizzare la seguente sintassi:
CREATE TABLE nome_tabella(
elenco_colonne,
INDEX(nome_colonna(lunghezza))
);
E per aggiungere un indice a una tabella esistente:
CREATE INDEX nome_indice ON nome_tabella (nome_colonna (lunghezza));
La lunghezza qui è il numero di caratteri se si tratta di un tipo di stringa non binaria come CHAR, VARCHAR e TEXT e il numero di byte se si tratta di un tipo di stringa binaria come BINARY, VARBINARY e BLOB. Puoi anche creare parti chiave del prefisso di colonna per le colonne CHAR, VARCHAR, BINARY e VARBINARY in MySQL. È necessario specificare le parti chiave del prefisso di colonna se sono stati creati indici per le colonne BLOB e TEXT.
In caso di indici FULLTEXT supportati solo per le tabelle InnoDB e MyISAM, possono includere solo colonne CHAR, VARCHAR e TEXT. Non supporta l'indicizzazione del prefisso di colonna e se viene specificata una lunghezza del prefisso, viene ignorata.
Da leggere: Idee per progetti SQL
Modifica delle tabelle per aggiungere o eliminare l'indice
Per aggiungere indici a una tabella, è possibile utilizzare quattro tipi di istruzioni:
- Per aggiungere una chiave primaria:
ALTER TABLE nome_tabella AGGIUNGI CHIAVE PRIMARIA (lista_colonna);
I valori indicizzati qui devono essere univoci e non possono essere NULL.
- Per un indice con valori univoci:
ALTER TABLE nome_tabella ADD UNIQUE nome_indice (elenco_colonna);
I valori devono essere univoci, ad eccezione di NULL che può apparire più volte.
- Per aggiungere un indice ordinario:
ALTER TABLE nome_tabella ADD INDEX nome_indice (elenco_colonna);
Qui i valori possono apparire più di una volta.
- Per creare un indice FULLTEXT speciale:
ALTER TABLE nome_tabella ADD FULLTEXT nome_indice (elenco_colonna);
L'indice FULLTEXT può essere utilizzato per la ricerca di testo.
Nelle tabelle esistenti
Per aggiungere un indice in qualsiasi tabella esistente, è possibile utilizzare la seguente sintassi:
ALTER TABLE nome_tabella ADD INDEX (nome_indice);
Per eliminare un indice in una tabella, viene utilizzata l'istruzione DROP INDEX:
ALTER TABLE nome_tabella DROP INDEX (nome_indice);
Visualizza le informazioni sull'indice
Per elencare tutti gli indici associati a qualsiasi tabella, viene utilizzato il comando SHOW INDEX.
MOSTRA INDICE DA nome_tabella\G
Il '\G' viene utilizzato per creare l'elenco in un formato verticale, questo evita la lunga riga a capo.
Leggi anche: Domande e risposte sull'intervista SQL
Indici discendenti
InnoDB può memorizzare le voci in ordine decrescente quando l'indice è un indice decrescente. Pertanto, quando nella query viene richiesto l'ordine decrescente, l'ottimizzatore sceglie questo indice. Questo è più efficiente per le query con clausole ORDER BY. Questi sono supportati solo dal motore di archiviazione InnoDB.
La sintassi per la creazione o l'aggiunta di indici discendenti è come alterare o creare le sintassi utilizzate sopra.
ALTER TABLE nome_tabella ADD nome_indice (col1_name desc, col2_name asc);
CREATE INDEX nome_indice ON nome_tabella (nome_col1 desc, nome_col2 asc);
Indici invisibili
Per contrassegnare gli indici come non disponibili per Query Optimizer, è possibile utilizzare gli indici invisibili. MySQL aggiorna gli indici invisibili quando i dati nelle colonne associate all'indice cambiano. Gli indici sono visibili di default e per renderli invisibili è necessario dichiarare esplicitamente la visibilità al momento della creazione o utilizzando il comando ALTER TABLE.
Per creare un indice invisibile, viene utilizzata la seguente sintassi:
CREATE INDEX nome_indice ON nome_tabella (c1, c2…) INVISIBILE;
Per modificare la visibilità degli indici esistenti, viene utilizzata la seguente sintassi:
ALTER TABLE nome_tabella ALTER INDEX nome_indice [VISIBLE | INVISIBILE];
Iscriviti ai corsi di sviluppo software online delle migliori università del mondo. Guadagna programmi Executive PG, programmi di certificazione avanzati o programmi di master per accelerare la tua carriera.
Conclusione
Le prestazioni delle query di ricerca MySQL possono essere migliorate utilizzando gli indici. Questi possono essere definiti quando viene creata una tabella o possono essere aggiunti in seguito se la tabella è già stata creata.
Questo articolo fornisce una breve descrizione di cos'è un indice e perché viene utilizzato. La sezione successiva spiega come creare un indice in MySQL, come aggiungerlo a una tabella esistente e come creare un indice univoco.
Sono stati descritti anche i diversi comandi per modificare le tabelle e creare o eliminare un indice insieme alla visualizzazione delle informazioni sull'indice. Sono stati anche brevemente spiegati gli indici discendenti e invisibili.
Tutto sommato, l'apprendimento e l'esercitazione pratica su tutti i database menzionati in questo articolo si aggiungeranno alla tua esperienza. Esamina la panoramica del corso, l'apprendimento e le opportunità di lavoro coinvolte: piattaforme come upGrad offrono corsi ricchi di energia progettati da istituti altamente rinomati come IIIT-B nello sviluppo completo dello stack del programma Executive PG.
