¿Cómo funcionan los índices MySQL?

Lo primero que debes saber es que los índices son una forma de evitar escanear la tabla completa para obtener el resultado que estás buscando.

Hay diferentes tipos de índices y están implementados en la capa de almacenamiento, por lo que no hay un estándar entre ellos y también dependen del motor de almacenamiento que esté utilizando.

InnoDB y el índice de árbol B +

Para InnoDB, el tipo de índice más común es el índice basado en árbol B+ , que almacena los elementos en un orden orden. Además, no tiene que acceder a la tabla real para obtener los valores indexados, lo que hace que su consulta regrese mucho más rápido.

El "problema" sobre este tipo de índice es que debe consultar el valor más a la izquierda para usar el índice. Por lo tanto, si su índice tiene dos columnas, digamos last_name y first_name, el orden en el que consulta estos campos importa mucho.

Así que, dada la siguiente tabla:

CREATE TABLE person (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

Esta consulta aprovecharía la índice:

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

Pero el siguiente no{[18]]}

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

Porque estás consultando la columna first_name primero y no es la columna más a la izquierda del índice.

Este último ejemplo es aún peor:

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

Porque ahora, estás comparando la parte más a la derecha del campo más a la derecha en el índice.

El índice hash

Este es un tipo de índice diferente que, desafortunadamente, solo admite el motor de memoria. Es muy rápido, pero solo es útil para búsquedas, lo que significa que no se puede utilizar para operaciones como >, < o LIKE.

Dado que solo funciona para el motor de memoria, probablemente no lo use muy a menudo. El caso principal que puedo pensar en este momento es el que crea una tabla temporal en la memoria con un conjunto de resultados de otra selección y realiza muchas otras selecciones en esta tabla temporal utilizando índices hash.

Si tiene un gran campo VARCHAR, puede "emular" el uso de un índice hash al usar un árbol B, creando otra columna y guardando un hash del gran valor en ella. Digamos que estás almacenando una url en un campo y los valores son bastante grandes. También puede crear un campo entero llamado url_hash y usar una función hash como CRC32 o cualquier otra función hash para hash la url al insertarla. Y luego, cuando necesite consultar este valor, puede hacer algo como esto:

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

El problema con el ejemplo anterior es que dado que la función CRC32 genera una hash, terminarás con muchas colisiones en los valores hash. Si necesita valores exactos, puede solucionar este problema haciendo lo siguiente:

SELECT url FROM url_table 
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

Todavía vale la pena hacer hash incluso si el número de colisión es alto porque solo realizará la segunda comparación (la cadena uno) contra los hashes repetidos.

Desafortunadamente, usando esta técnica, todavía necesita golpear la tabla para comparar el campo url.

Terminar

Algunos hechos que usted puede considere cada vez que quiera hablar de optimización:

1. La comparación de enteros es mucho más rápida que la comparación de cadenas. Se puede ilustrar con el ejemplo sobre la emulación del índice hash en InnoDB.

2. Tal vez, agregar pasos adicionales en un proceso lo hace más rápido, no más lento. Puede ilustrarse por el hecho de que puede optimizar un SELECT dividiéndolo en dos pasos, haciendo que el primero almacene valores en una tabla en memoria recién creada, y luego ejecute las consultas más pesadas en esta segunda tabla.

MySQL también tiene otros índices, pero creo que el B+Tree es el más utilizado y el hash es bueno saberlo, pero puedes encontrar los otros en la documentación MySQL.

Le recomiendo encarecidamente que lea el libro "High Performance MySQL", la respuesta anterior se basó definitivamente en su capítulo sobre índices.

Básicamente un índice es un mapa de todas las claves que se ordena en orden. Con una lista en orden, entonces en lugar de verificar cada clave, puede hacer algo como esto:

1: Ir a la mitad de la lista - es mayor o menor que lo que estoy buscando?

2: Si es más alto, vaya al punto medio entre medio e inferior, si es más bajo, medio y superior

3: ¿Es mayor o menor? Saltar al punto medio de nuevo, etc.

Usando esa lógica, puede encontrar un elemento en una lista ordenada en aproximadamente 7 pasos, en lugar de comprobar cada elemento.

Obviamente hay complejidades, pero eso te da la idea básica.

El índice de la base de datos, o simplemente el índice, ayuda a acelerar la recuperación de datos de las tablas. Cuando consulta datos de una tabla, primero MySQL comprueba si los índices existen, luego MySQL usa los índices para seleccionar las filas físicas exactas correspondientes de la tabla en lugar de escanear toda la tabla.

Un índice de base de datos es similar al índice de un libro. Si desea encontrar un tema, primero busque en el índice y luego abra la página que tiene el tema sin escanear todo libro.

Es muy recomendable que cree un índice en las columnas de la tabla desde la que a menudo consulta los datos. Observe que todas las columnas de clave primaria se encuentran automáticamente en el índice principal de la tabla.

Si el índice ayuda a acelerar los datos de consulta, ¿por qué no usamos índices para todas las columnas? Si crea un índice para cada columna, MySQL tiene que construir y mantener la tabla de índices. Cada vez que se realiza un cambio en los registros de la tabla, MySQL tiene que reconstruir el índice, que toma tiempo, así como disminuye el rendimiento del servidor de base de datos. Creación de MySQL Index

A menudo se crean índices cuando se crean tablas. MySQL añade automáticamente al índice cualquier columna declarada como CLAVE PRIMARIA, CLAVE, ÚNICA o ÍNDICE. Además, puede agregar índices a las tablas que ya tienen datos.

Para crear índices, utilice la instrucción CREATE INDEX. A continuación se ilustra la sintaxis del CREATE INDEX instrucción: 1 2 3

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],...)

Primero, especifique el índice basado en el tipo de tabla o motor de almacenamiento:

UNIQUE significa que MySQL creará una restricción para que todos los valores del índice sean únicos. El valor NULL duplicado está permitido en todo el motor de almacenamiento excepto BDB. El índice de texto completo solo es compatible con el motor de almacenamiento MyISAM y solo se acepta en la columna que tiene el tipo de datos CHAR, VARCHAR o TEXT. SPATIAL index es compatible con spatial column y está disponible en MyISAM storage engine. En además, el valor de la columna no debe ser NULO.

Luego, se nombra el índice y su tipo después de la palabra clave USING como BTREE, HASH o RTREE también basado en el motor de almacenamiento de la tabla.

Aquí están los motores de almacenamiento de la tabla con los tipos de índice permitidos correspondientes: Tipos de Índice Permitidos del Motor de Almacenamiento MyISAM BTREE, RTREE InnoDB BTREE HASH DE MEMORIA / MONTÓN, BTREE NDB HASH

En tercer lugar, declara el nombre de la tabla y una lista de columnas que desea agregar a la Indice. Ejemplo de creación de índice en MySQL

En la base de datos de ejemplo, puede agregar la columna officeCode de la tabla employees al índice utilizando la instrucción CREATE INDEX de la siguiente manera: 1

CREATE INDEX officeCode ON employees(officeCode)

Eliminando índices

Además de crear index, también puede eliminar index usando la instrucción DROP INDEX. Curiosamente, la instrucción DROP INDEX también está asignada para ALTERAR la instrucción TABLE. La siguiente es la sintaxis de eliminar el Indice: 1

DROP INDEX index_name ON table_name

Por ejemplo, si desea eliminar index officeCode de la tabla employees, que hemos creado anteriormente, puede ejecutar la siguiente consulta: 1

DROP INDEX officeCode ON employees

Mira en estos videos para más detalles sobre la indexación

Indexación simple Puede crear un índice único en una tabla. Un índice único significa que dos filas no pueden tener el mismo valor de índice. Aquí está la sintaxis para crear un índice en una tabla

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

Puede usar una o más columnas para crear un índice. Por ejemplo, podemos crear un índice en tutorials_tbl usando tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

Puede crear un índice simple en una tabla. Simplemente omita la palabra clave ÚNICA de la consulta a crear índice simple. Índice Simple permite valores duplicados en una tabla.

Si desea indexar los valores en una columna en orden descendente, puede agregar la palabra reservada DESC después del nombre de la columna.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

Echa un vistazo a este enlace: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Cómo funcionan es un tema demasiado amplio para cubrirlo en una publicación de SO.

Aquí es una de las mejores explicaciones de índices que he visto. Desafortunadamente es para SQL Server y no MySQL. No estoy seguro de lo similares que son los dos...