Índices MySQL
El índice MySQL es una estructura de datos utilizada para acelerar la velocidad y el rendimiento de las consultas de la base de datos.
La creación de índices MySQL es importante para el funcionamiento eficiente de MySQL; los índices pueden mejorar en gran medida la velocidad de búsqueda de MySQL.
El índice MySQL es similar al índice de un libro; al almacenar punteros a las filas de datos, puede localizar y acceder rápidamente a datos específicos en la tabla.
Por poner una analogía, si un MySQL con un diseño y uso razonable de índices es un Lamborghini, entonces un MySQL sin diseño ni uso de índices es un triciclo de pedal.
Tomando como analogía la página del índice de un diccionario chino, podemos encontrar rápidamente los caracteres necesarios mediante el índice (ordenado por pinyin, trazos, radicales, etc.).
Los índices se dividen en índices de una sola columna e índices compuestos:
- Un índice de una sola columna, es decir, un índice que contiene una sola columna; una tabla puede tener varios índices de una sola columna.
- Un índice compuesto, es decir, un índice que contiene varias columnas.
Al crear un índice, debes asegurarte de que el índice se aplique en la condición de la consulta SQL (generalmente como condición de la cláusula WHERE).
En realidad, un índice también es una tabla, que almacena la clave primaria y los campos del índice, y apunta a los registros de la tabla real.
Aunque los índices pueden mejorar el rendimiento de las consultas, también hay que tener en cuenta los siguientes puntos:
- Los índices ocupan espacio de almacenamiento adicional.
- Al realizar operaciones de inserción, actualización y eliminación en la tabla, los índices requieren mantenimiento, lo que puede afectar el rendimiento.
- Demasiados índices o índices irrazonables pueden provocar una degradación del rendimiento, por lo que es necesario seleccionar y planificar los índices con cuidado.
Índice normal
Los índices pueden mejorar significativamente la velocidad de las consultas, especialmente al buscar en tablas grandes. Al usar índices, MySQL puede localizar directamente las filas de datos que cumplen las condiciones de la consulta sin escanear toda la tabla fila por fila.
Crear índice
UsarCREATE INDEXLa declaración puede crear un índice normal.
El índice normal es el tipo de índice más común y se utiliza para acelerar las consultas de datos en una tabla.
CREATE INDEXLa sintaxis de:
CREATE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE INDEX: La palabra clave para crear un índice normal.index_name: Especifica el nombre del índice que se va a crear. El nombre del índice debe ser único en la tabla.table_name: Especifica en qué tabla se va a crear el índice.(column1, column2, ...): Especifica los nombres de las columnas de la tabla a indexar. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.ASCyDESC(Opcional): Se utiliza para especificar el orden de clasificación del índice. De forma predeterminada, el índice se ordena en orden ascendente (ASC).
El siguiente ejemplo supone que tenemos una tabla llamada students que contiene las columnas id, name y age; crearemos un índice normal en la columna name.
CREATE INDEX idx_name ON students (name);
La declaración anterior creará un índice normal llamado idx_name en la columna name de la tabla students, lo que ayudará a mejorar el rendimiento de las consultas realizadas por nombre.
Cabe señalar que si la cantidad de datos en la tabla es grande, la creación del índice puede llevar algún tiempo, pero una vez creado, el rendimiento de las consultas mejorará significativamente.
Modificar la estructura de la tabla (agregar índice)
Podemos usarALTER TABLEEl comando puede crear un índice en una tabla existente.
ALTER TABLE te permite modificar la estructura de la tabla, incluida la adición, modificación o eliminación de índices.
Sintaxis de ALTER TABLE para crear un índice:
ALTER TABLE table_name ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
ALTER TABLE: La palabra clave para modificar la estructura de la tabla.table_name: Especifica el nombre de la tabla a modificar.ADD INDEX: La cláusula para agregar un índice.ADD INDEXSe utiliza para crear un índice normal.index_name: Especifica el nombre del índice que se va a crear. El nombre del índice debe ser único en la tabla.(column1, column2, ...): Especifica los nombres de las columnas de la tabla a indexar. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.ASCyDESC(Opcional): Se utiliza para especificar el orden de clasificación del índice. De forma predeterminada, el índice se ordena en orden ascendente (ASC).
A continuación se muestra un ejemplo: crearemos un índice normal en la tabla existente llamada employees:
ALTER TABLE employees ADD INDEX idx_age (age);
La declaración anterior creará un índice normal llamado idx_age en la columna age de la tabla employees.
Especificar directamente al crear la tabla
Podemos al crear la tabla; tú puedesCREATE TABLEEn la declaración, especificar directamente el índice, para crear la combinación de tabla e índice.
CREATE TABLE table_name ( column1 data_type, column2 data_type, ..., INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...) );
CREATE TABLE: La palabra clave para crear una nueva tabla.table_name: Especifica el nombre de la tabla que se va a crear.(column1 data_type, column2 data_type, ...): Define los nombres de las columnas y los tipos de datos de la tabla. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.INDEX: La palabra clave para crear un índice normal.index_name: Especifica el nombre del índice que se va a crear. El nombre del índice debe ser único en la tabla.(column1, column2, ...): Especifica los nombres de las columnas de la tabla a indexar. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.ASCyDESC(Opcional): Se utiliza para especificar el orden de clasificación del índice. De forma predeterminada, el índice se ordena en orden ascendente (ASC).
A continuación se muestra un ejemplo: vamos a crear una tabla llamada students y crearemos un índice normal en la columna age.
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_age (age) );
En el ejemplo anterior, creamos un índice normal llamado idx_age en la columna age de la tabla students.
Sintaxis para eliminar un índice
Podemos usarDROP INDEXla declaración para eliminar el índice.
Sintaxis de DROP INDEX:
DROP INDEX index_name ON table_name;
DROP INDEX: La palabra clave para eliminar un índice.index_name: Especifica el nombre del índice que se va a eliminar.ON table_name: Especifica en qué tabla se va a eliminar el índice.
UsandoALTER TABLEla sintaxis de la declaración para eliminar un índice es la siguiente:
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE: La palabra clave para modificar la estructura de la tabla.table_name: Especifica el nombre de la tabla a modificar.DROP INDEX: La cláusula para eliminar un índice.index_name: Especifica el nombre del índice que se va a eliminar.
El siguiente ejemplo supone que tenemos una tabla llamada employees y que hay un índice llamado idx_age en la columna age; ahora vamos a eliminar este índice:
DROP INDEX idx_age ON employees;
o usarALTER TABLEdeclaración:
ALTER TABLE employees DROP INDEX idx_age;
Ambos comandos eliminarán el índice llamado idx_age de la tabla employees.
Si el índice no existe, se producirá un error al ejecutar el comando. Por lo tanto, antes de eliminar un índice es mejor confirmar si el índice existe, o utilizar un mecanismo de manejo de errores para tratar las posibles situaciones de error.
Índice único
En MySQL, puedes usarCREATE UNIQUE INDEXla declaración para crear un índice único.
El índice único garantiza que los valores en el índice sean únicos, no permite valores duplicados.
Crear índice
CREATE UNIQUE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE UNIQUE INDEX: La combinación de palabras clave para crear un índice único.index_name: Especifica el nombre del índice único que se va a crear. El nombre del índice debe ser único en la tabla.table_name: Especifica en qué tabla se va a crear el índice único.(column1, column2, ...): Especifica los nombres de las columnas de la tabla a indexar. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.ASCyDESC(Opcional): Se utiliza para especificar el orden de clasificación del índice. De forma predeterminada, el índice se ordena en orden ascendente (ASC).
A continuación se muestra un ejemplo de creación de un índice único: supongamos que tenemos una tabla llamada employees que contiene las columnas id y email; ahora queremos crear un índice único en la columna email para asegurar que la dirección de correo electrónico de cada empleado sea única.
CREATE UNIQUE INDEX idx_email ON employees (email);
El ejemplo anterior creará un índice único llamado idx_email en la columna email de la tabla employees.
Modificar la estructura de la tabla para agregar índice
Podemos usarALTER TABLEel comando para crear un índice único.
ALTER TABLEEl comando te permite modificar la estructura de una tabla existente, incluida la adición de nuevos índices.
ALTER table table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
ALTER TABLE: La palabra clave para modificar la estructura de la tabla.table_name: Especifica el nombre de la tabla a modificar.ADD CONSTRAINT: Esta es la palabra clave para agregar restricciones (incluidos los índices únicos).unique_constraint_name: Especifica el nombre del índice único que se va a crear; el nombre de la restricción debe ser único en la tabla.UNIQUE (column1, column2, ...): Especifica los nombres de las columnas de la tabla a indexar. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, textuales o de fecha.
A continuación se muestra un ejemplo de uso deALTER TABLEEjemplo del comando para crear un índice único: supongamos que tenemos una tabla llamada employees, que contiene las columnas id y email, y ahora queremos crear un índice único en la columna email para asegurar que la dirección de correo electrónico de cada empleado sea única.
ALTER TABLE employees ADD CONSTRAINT idx_email UNIQUE (email);
El ejemplo anterior creará un índice único llamado idx_email en la columna email de la tabla employees.
Ten en cuenta que si ya hay valores duplicados en la columna email en la tabla, la adición del índice único fallará. Antes de crear el índice único, es posible que debas asegurarte de que no haya valores duplicados en la columna email de la tabla.
Especificar directamente al crear la tabla
También podemos, al crear la tabla, puedesCREATE TABLEen la declaración usarUNIQUEla palabra clave para crear un índice único.
Esto definirá la restricción de índice único al mismo tiempo que se crea la tabla.
CREATE TABLESintaxis para crear un índice único en la declaración:
CREATE TABLE table_name ( column1 data_type, column2 data_type, ..., CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...) );
CREATE TABLE: la palabra clave para crear una nueva tabla.table_name: especifica el nombre de la tabla a crear.(column1 data_type, column2 data_type, ...): define los nombres de las columnas y los tipos de datos de la tabla. Puedes especificar una o más columnas como combinación del índice. Los tipos de datos de estas columnas suelen ser numéricos, texto o fecha.CONSTRAINT: la palabra clave para agregar restricciones.index_name: especifica el nombre del índice único a crear. El nombre de la restricción debe ser único en la tabla.UNIQUE (column1, column2, ...): especifica el nombre de la columna de la tabla a indexar.ASCyDESC(Opcional): se utiliza para especificar el orden de clasificación del índice. De forma predeterminada, el índice se ordena en orden ascendente (ASC).
El siguiente es un ejemplo de creación de un índice único al crear la tabla: supongamos que queremos crear una tabla llamada employees, que contenga las columnas id, name y email, y esperamos que los valores de la columna email sean únicos, por lo tanto, queremos definir un índice único al crear la tabla.
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) UNIQUE );
En este ejemplo, la columna email se define como un índice único porque se le agrega la palabra clave UNIQUE después de ella.
Ten en cuenta que al usarUNIQUEdespués de la palabra clave, el nombre del índice se generará automáticamente, y también puedes especificar el nombre del índice según sea necesario.
Usar el comando ALTER para agregar y eliminar índices
Hay cuatro formas de agregar índices a una tabla de datos:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):Esta declaración agrega una clave primaria. Los valores en las columnas de la clave primaria deben ser únicos. La lista de columnas de la clave primaria puede ser una o más columnas y no puede contener valores NULL.
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):Los valores del índice creado por esta declaración deben ser únicos (excepto NULL; NULL puede aparecer varias veces).
- ALTER TABLE tbl_name ADD INDEX index_name (column_list):Agrega un índice normal; los valores del índice pueden aparecer varias veces.
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Esta declaración especifica que el índice es FULLTEXT, utilizado para la búsqueda de texto completo.
El siguiente ejemplo es agregar un índice a la tabla.
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
También puedes usar la cláusula DROP en el comando ALTER para eliminar índices. Intenta el siguiente ejemplo para eliminar un índice:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
Usar el comando ALTER para agregar y eliminar claves primarias
La clave primaria actúa sobre las columnas (puede ser una columna o varias columnas combinadas como clave primaria). Al agregar una clave primaria, debes asegurarte de que la clave primaria no esté vacía por defecto (NOT NULL). Ejemplo:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
También puedes usar el comando ALTER para eliminar la clave primaria:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
Al eliminar la clave primaria, solo necesitas especificar PRIMARY KEY, pero al eliminar un índice, debes conocer el nombre del índice.
Mostrar información del índice
Puedes usarSHOW INDEXel comando para listar la información de los índices relevantes en la tabla.
Puedes agregar\Gpara formatear la información de salida.
SHOW INDEXDeclaración:
mysql> SHOW INDEX FROM table_name\G ........
SHOW INDEX: la palabra clave para mostrar información de índice.FROM table_name: especifica el nombre de la tabla cuya información de índice deseas ver.\G: formatea la información de salida.
Después de ejecutar el comando anterior, se mostrará la información detallada de todos los índices en la tabla especificada, incluidos el nombre del índice (Key_name), la columna del índice (Column_name), si es un índice único (Non_unique), el método de ordenamiento (Collation), la cardinalidad del índice (Cardinality), etc.
Otras extensiones