MySQL - Indexación

Indexación

Los índices se utilizan para buscar filas con valores de columna específicos rápidamente. Sin un índice, MySQL leerá toda la tabla para encontrar las filas relevantes. Cuanto más grande es la tabla mayor será el tiempo de ejecución. Si la tabla tiene un índice para las columnas en cuestión, MySQL puede determinar rápidamente la posición a buscar en medio del archivo de datos, sin tener que mirar todos los datos. Esto es mucho más rápido que leer cada fila secuencialmente.

La mayoría de los índices de MySQL (PRIMARY KEY, UNIQUE, INDEX y FULLTEXT) se almacenan en árboles-B. a excepción de los tipos de datos espaciales que utilizan árboles-R.

En general, MySQL emplea los índices para las siguientes acciones:

Encontrar las filas que cumplen la condición WHERE de la consulta cuyas columnas están indexadas.
Para recuperar las filas de otras tablas cuando se emplean operaciones de tipo JOIN. Para ello, es importante que los índices sean del mismo tipo y tamaño ya que aumentará la eficiencia de la búsqueda.
Disminuir el tiempo de ejecución de las consultas con ordenación (ORDER BY) o agrupamiento (GROUP BY) si todas las columnas presentes en los criterios forman parte de un índice.
Si la consulta emplea una condición simple cuya columna de la condición está indexada, las filas serán recuperadas directamente a partir del índice, sin pasar a consultar la tabla.

Tipos de índices

INDEX (NON-UNIQUE): Este tipo de índice se refiere a un índice normal, no único. Esto implica que admite valores duplicados para la columna (o columnas) que componen el índice. No aplica ninguna restricción especial a los datos de la columna (o columnas) que componen el índice, sino que se emplea simplemente para mejorar el tiempo de ejecución de las consultas.

UNIQUE: Este tipo de índice se refiere a un índice en el que todas las columnas deben tener un valor único. Esto implica que no admite valores duplicados para la columna (o columnas) que componen el índice. Aplica la restricción de que los datos de la columna (o columnas) deben tener un valor único.

PRIMARY: Este tipo de índice se refiere a un índice en el que todas las columnas deben tener un valor único (al igual que en el caso del índice UNIQUE) pero con la limitación de que sólo puede existir un índice PRIMARY en cada una de las tablas. Aplica la restricción de que los datos de la columna (o columnas) deben tener un valor único.

FULLTEXT: Estos índices se emplean para realizar búsquedas sobre texto (CHAR, VARCHAR y TEXT). Estos índices se componen por todas las palabras que están contenidas en la columna (o columnas) que contienen el índice. No aplica ninguna restricción especial a los datos de la columna (o columnas) que componen el índice, sino que se emplea simplemente para mejorar el tiempo de ejecución de las consultas. Este tipo de índices sólo están soportados por InnoDB y MyISAM en MySQL 5.7.

SPATIAL: Estos índices se emplean para realizar búsquedas sobre datos que componen formas geométricas representadas en el espacio. Este tipo de índices sólo están soportados por InnoDB y MyISAM en MySQL 5.7.

Por ejemplo:


                create table test (
                    id int unsigned auto_increment not null,
                    nombre varchar(255) not null,
                    
                    primary key (id),
                    unique (id),
                    index (id, nombre),
                    fulltext (nombre)
                );