索引 MySQL

La creazione di indici è molto importante per il funzionamento efficiente di MySQL, gli indici possono migliorare notevolmente la velocità di ricerca di MySQL.

Facciamo un esempio, se un MySQL progettato e utilizzato correttamente con indici è una Lamborghini, allora un MySQL senza progettazione e utilizzo degli indici è come una tricycle a mano.

Prendendo a esempio la pagina del catalogo di un dizionario cinese (indice), possiamo trovare rapidamente la parola desiderata attraverso l'ordinamento delle pagine (indice) per pronuncia, tratti, radici laterali, ecc.

Gli indici si distinguono in indici singoli e indici composti. Un indice singolo contiene solo una colonna, una tabella può avere più indici singoli, ma questo non è un indice composto. Un indice composto contiene più colonne.

Quando si crea un indice, è necessario assicurarsi che l'indice venga applicato alla condizione della query SQL (solitamente come condizione della clausola WHERE).

In realtà, l'indice è anche una tabella, che保存了主键与索引字段，并指向实体表的记录。

Finora si è parlato dei benefici dell'uso dell'indice, ma un uso eccessivo può causare abuso. Pertanto, l'indice ha anche i suoi svantaggi: sebbene l'indice aumenti notevolmente la velocità di ricerca, può anche ridurre la velocità di aggiornamento della tabella, come INSERT, UPDATE e DELETE. Poiché durante l'aggiornamento della tabella, MySQL deve salvare non solo i dati, ma anche il file dell'indice.

La creazione dell'indice richiede uno spazio di archiviazione per il file dell'indice.

Indice normale

Crea un indice

Questo è l'indice di base, non ha alcuna limitazione. Ha i seguenti modi di creazione:

CREATE INDEX indexName ON table_name (column_name)

Se CHAR o VARCHAR, la lunghezza può essere inferiore alla lunghezza effettiva del campo; se si tratta di BLOB e TEXT, è necessario specificare la lunghezza.

Modifica la struttura della tabella (aggiungi indice)

ALTER table tableName ADD INDEX indexName(columnName)

Specifica direttamente quando si crea la tabella

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
INDEX [indexName] (username(length))　　
　
);

Sintassi per eliminare l'indice

DROP INDEX [indexName] ON mytable;

Indice unico

È simile all'indice ordinario precedente, la differenza è che: il valore dell'indice della colonna deve essere unico, ma è permesso che ci siano valori vuoti. Se è un indice composto, la combinazione dei valori delle colonne deve essere unica. Esistono diversi modi per crearlo:

Crea un indice

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modifica la struttura della tabella

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Specifica direttamente quando si crea la tabella

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
UNIQUE [indexName] (username(length))　　
　
);

Aggiungi e elimina indici utilizzando l'istruzione ALTER

Ci sono quattro modi per aggiungere un indice a un data table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Questa istruzione aggiunge una chiave primaria, il che significa che il valore dell'indice deve essere unico e non può essere NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Il valore dell'indice creato deve essere unico (eccetto NULL, che può apparire più volte).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Aggiungi un indice ordinario, il valore dell'indice può apparire più volte.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Questa istruzione specifica l'indice come FULLTEXT, utilizzato per l'indicizzazione full-text.

Ecco un esempio di aggiunta di un indice nella tabella.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Puoi anche utilizzare la clausola DROP nell'istruzione ALTER per eliminare l'indice. Prova l'esempio seguente per eliminare l'indice:

mysql>　ALTER　TABLE　testalter_tbl　DROP　INDEX　c;

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。示例如下：

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

你也可以使用 ALTER 命令删除主键：

mysql>　ALTER　TABLE　testalter_tbl　DROP　PRIMARY　KEY;

删除主键时只需指定 PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下示例:

mysql>　SHOW　INDEX　FROM　table_name;　\G
........