Índices MySQL

La creación de índices de MySQL es muy importante para el funcionamiento eficiente de MySQL, los índices pueden aumentar enormemente la velocidad de búsqueda de MySQL.

Haciendo una analogía, si el diseño y uso razonables de MySQL con índices es un Lamborghini, entonces MySQL sin diseño y uso de índices es una triciclo humano.

Tomemos como ejemplo la página de índice del diccionario chino (índice), podemos buscar rápidamente la palabra necesaria mediante la clasificación de pinyin, trazos, componentes laterales, etc.

Los índices se dividen en índices de una sola columna y índices compuestos. Un índice de una sola columna, es decir, un índice que contiene solo una columna, una tabla puede tener varios índices de una sola columna, pero esto no es un índice compuesto. Un índice compuesto, es decir, un índice que contiene múltiples columnas.

Al crear un índice, debes asegurarte de que el índice se aplique a la condición de la consulta SQL (generalmente como condición de la cláusula WHERE).

En realidad, el índice también es una tabla, que guarda la clave principal y los campos de índice, y apunta a los registros de la tabla real.

Todo lo dicho anteriormente son los beneficios del uso de índices, pero un uso excesivo de índices causará abuso. Por lo tanto, los índices también tienen sus defectos: aunque los índices aumentan enormemente la velocidad de búsqueda, también pueden reducir la velocidad de actualización de la tabla, como INSERT, UPDATE y DELETE en la tabla. Porque al actualizar la tabla, MySQL no solo debe guardar los datos, sino también guardar el archivo de índice.

La creación de índices ocupará espacio en disco del archivo de índice.

Índice normal

创建索引

Este es el índice más básico, que no tiene ninguna restricción. Tiene las siguientes formas de creación:

CREATE INDEX indexName ON table_name (column_name)

Si es de tipo CHAR o VARCHAR, la longitud puede ser menor que la longitud real del campo; si es de tipo BLOB o TEXT, debe especificarse length.

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

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

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

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

修改表结构

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

创建表的时候直接指定

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

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下示例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下示例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Agregar y eliminar claves principales usando el comando ALTER

La clave principal actúa en la columna (puede ser una columna o una combinación de columnas como clave principal), al agregar el índice de clave principal, debe asegurarse de que la clave principal no sea vacía por defecto (NOT NULL). Ejemplo a continuación:

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

También puede usar el comando ALTER para eliminar la clave principal:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Al eliminar la clave principal, solo necesita especificar PRIMARY KEY, pero al eliminar el índice, debe saber el nombre del índice.

Mostrar información de índices

Puede usar el comando SHOW INDEX para listar la información de índices relacionados de la tabla. Añadiendo \G puede formatear la salida de información.

Pruebe los siguientes ejemplos:

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