MySQL Índice: Tutorial para crear, añadir y eliminar

Por: Marcus Allen Marcus Allen
Hours Actualizado

⚡ Resumen inteligente

MySQL El tutorial sobre índices explica cómo los índices ordenan y localizan datos rápidamente. Un índice es una estructura de búsqueda ordenada creada a partir de una o más columnas; CREATE INDEX lo agrega, SHOW INDEXES lo inspecciona y DROP INDEX lo elimina cuando las tablas con mucho tráfico de escritura superan los beneficios de la lectura.

  • 📚 Trate los índices como si fueran un diccionario: Ordenan los valores de las columnas para que el motor pueda localizar las filas sin tener que escanear toda la tabla.
  • 🛠️ Crear en la tabla o después: Defina un índice directamente en CREATE TABLE o agréguelo posteriormente con CREATE INDEX en una tabla activa.
  • 🔍 Inspeccione con MOSTRAR ÍNDICES: Utilice SHOW INDEXES FROM table_name para listar todos los índices, partes de clave, cardinalidad e indicadores de unicidad.
  • 🧹 Caída de la caché cuando el coste de escritura es demasiado alto: Los índices ralentizan las operaciones de INSERCIÓN y ACTUALIZACIÓN; elimine los que no utilice con DROP INDEX para recuperar el rendimiento de escritura.
  • 🤖 Utilice la IA para el diseño de índices: Los asistentes de IA leen los registros de consultas lentas, sugieren el orden de las columnas para los índices compuestos y explican los planes EXPLAIN línea por línea.
Descubre más

MySQL Concepto de índice

¿Qué es MySQL ¿Índice?

An índice in MySQL Un índice es una estructura de datos que almacena los valores de las columnas de forma ordenada para que el motor pueda buscar filas rápidamente. Los índices se crean en la columna o columnas que se utilizan con mayor frecuencia para filtrar datos. Un índice se asemeja a una lista ordenada alfabéticamente: es mucho más rápido encontrar un nombre en una lista ordenada que en una desordenada.

Los índices implican una contrapartida: cada INSERT o UPDATE debe mantener el índice, por lo que agregar demasiados índices a una tabla con muchas escrituras puede perjudicar el rendimiento general. Como regla general, indexe las columnas que aparecen en las cláusulas WHERE, JOIN y ORDER BY en tablas que se leen con más frecuencia de la que se escriben.

¿Por qué utilizar un índice?

A nadie le gustan los sistemas lentos. El alto rendimiento es una prioridad para casi todas las aplicaciones basadas en bases de datos. Las empresas invierten mucho en hardware para agilizar las consultas, pero el hardware por sí solo tiene sus limitaciones. Optimizar los índices es una solución más económica y eficaz.

MySQL Concepto de índice

Los tiempos de respuesta lentos generalmente se deben a que las filas se almacenan en orden físico en el disco. Sin un índice, MySQL debe escanear cada fila para encontrar las que coincidan con un predicado: un “escaneo completo de la tabla”. Los índices permiten MySQL Saltar directamente a las filas coincidentes, lo que transforma el plan de consulta de O(n) a aproximadamente O(log n) para búsquedas en árboles B.

Sintaxis: Crear índice

Un índice puede definirse en dos lugares:

  1. En el momento de la creación de la tabla.
  2. Después de que la tabla ya existe.

Ejemplo: Crear un índice en línea con CREATE TABLE

Para el myflixdb base de datos, esperamos muchas búsquedas en la columna de nombre completo. El script a continuación crea una nueva members_indexed tabla con un índice en la full_names columna.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Ejecutar el script en MySQL Banco de trabajo contra el myflixdb base de datos.

tabla members_indexed en MySQL Banco de trabajo

Refrescar myflixdb para ver el nuevo members_indexed mesa. La full_names La columna ahora aparece debajo de la Índices nodo.

A medida que crece la membresía, las consultas de búsqueda en members_indexed que utilizan WHERE y ORDER BY contra full_names son mucho más rápidos que las mismas consultas en el original. members tabla sin índice.

ARTÍCULOS RELACIONADOS

Agregue un índice después de que la tabla ya exista.

A menudo descubrirá que una tabla existente necesita un índice: las consultas de búsqueda son lentas y un plan EXPLAIN muestra un escaneo completo de la tabla en una columna que aparece en WHERE. CREATE INDEX Esta instrucción agrega un índice sin recrear la tabla.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Ejemplo concreto: acelerar las búsquedas en el title columna de la movies mesa:

CREATE INDEX `title_index` ON `movies` (`title`);

Cada consulta que filtra por movies.title Ahora cuenta con el respaldo del nuevo índice. Las consultas que filtran por otras columnas siguen escaneando la tabla a menos que tengan su propio índice.

Nota: Puedes crear un índice compuesto que abarque varias columnas cuando tus consultas siempre filtran u ordenan según la misma combinación. El orden importa: la primera columna determina si se puede usar el índice.

Listar índices en una tabla

Usar SHOW INDEXES para ver todos los índices definidos en una tabla.

SHOW INDEXES FROM `table_name`;

Ejemplo: listar índices en el movies mesa:

SHOW INDEXES FROM `movies`;

Ejecuta la instrucción en MySQL Banco de trabajo en contra myflixdb para ver los índices existentes y las columnas que abarcan.

Nota: Las claves primarias y foráneas se indexan automáticamente por MySQLCada índice tiene un nombre único y enumera la(s) columna(s) que abarca.

Sintaxis: Drop Index

Usar DROP INDEX Eliminar un índice existente de una tabla. Esto resulta útil cuando una tabla con muchas operaciones de escritura se ve ralentizada por un índice que ya no resulta útil en el lado de lectura.

DROP INDEX `index_id` ON `table_name`;

Ejemplo concreto: dejar caer el full_names índice de members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Tipos de MySQL Índices

MySQL Admite varios tipos de índices, cada uno adecuado para una carga de trabajo diferente.

Tipo Propósito
CLAVE PRIMARIA Identificador único de fila; agrupado con los datos de la tabla en InnoDB.
UNIQUE Garantiza la unicidad al tiempo que funciona como índice.
ÍNDICE (árbol B) Índice secundario predeterminado utilizado para consultas de rango y búsquedas de igualdad.
TEXTO COMPLETO Optimizado para la búsqueda de texto en lenguaje natural con COINCIDIR … CONTRA.
ESPACIAL Índice R-tree para tipos de datos SIG como PUNTO y POLÍGONO.
Hachís Búsquedas de igualdad en tiempo constante; utilizadas por el motor de almacenamiento MEMORY.
Compuesto (de varias columnas) Combina varias columnas en un solo índice; respeta la regla del prefijo más a la izquierda.

Mejores prácticas para MySQL Índices

Los siguientes hábitos contribuyen a que los índices sean útiles y evitan que se conviertan en una carga innecesaria.

  • Índice del patrón de consulta, no del nombre de la columna: Agregue índices que coincidan con las cláusulas WHERE, JOIN y ORDER BY reales, no con "todas las columnas que parezcan importantes".
  • Orden del índice compuesto de la observación: La columna principal debe aparecer en la consulta para que se pueda utilizar el índice.
  • Evite los índices duplicados: Un prefijo principal de un índice compuesto ya cubre las búsquedas de una sola columna en ese prefijo.
  • Inspeccione con EXPLICAR: Confirma que el planificador está seleccionando realmente el nuevo índice.
  • Eliminar índices no utilizados: use sys.schema_unused_indexes in MySQL 5.7+ para encontrar índices que nada lee.
  • Coincidencia de tipos de datos: Si una cláusula WHERE compara una columna VARCHAR con un número, el índice no se puede utilizar debido a una conversión implícita.

Preguntas Frecuentes

Una clave primaria identifica de forma única cada fila y siempre está indexada. Un índice general acelera las búsquedas, pero permite valores duplicados. Toda clave primaria es un índice, pero no todo índice es una clave primaria.

Evite los índices en tablas muy pequeñas, en columnas con muy pocos valores distintos (baja cardinalidad) y en tablas que se escriben con mucha más frecuencia de la que se leen. Cada índice adicional ralentiza cada operación de inserción, actualización y eliminación.

Un índice compuesto (de varias columnas) abarca más de una columna en un solo índice. Respeta la regla del prefijo más a la izquierda, por lo que puede atender consultas que filtren por la primera columna, las dos primeras columnas, etc., pero no por la segunda columna sola.

Ejecutar EXPLAIN delante de la instrucción SELECT. El clave La columna muestra qué índice seleccionó el optimizador, mientras que tipo y filas Te indicará si la ruta de acceso es eficiente.

Un índice de cobertura contiene todas las columnas que necesita la consulta, por lo que el motor responde a la consulta solo con el índice, sin leer la tabla. EXPLAIN informa "Usando índice" cuando esto sucede.

Las razones comunes incluyen envolverping la columna en una función (WHERE YEAR(col) = …), conversiones de tipo implícitas, cardinalidad muy baja y estadísticas obsoletas. Ejecutar ANALYZE TABLE para actualizar las estadísticas e inspeccionar EXPLAIN por la verdadera razón.

Los asistentes de IA procesan los registros de consultas lentas, clasifican los patrones más costosos, sugieren índices de una sola columna o compuestos y explican los planes de EXPLAIN en lenguaje sencillo. Reducen el tiempo de optimización de horas a minutos para cargas de trabajo rutinarias.

Sí. Las herramientas de IA convierten una solicitud como "acelerar las búsquedas de clientes por correo electrónico y fecha de registro" en una instrucción CREATE INDEX funcional, recomiendan el orden de las columnas y explican el impacto previsto en el rendimiento de lectura y escritura.

Resumir este post con: