¿Cómo funcionan los índices MySQL?
Estoy realmente interesado en cómo funcionan los índices MySQL, más específicamente, ¿cómo pueden devolver los datos solicitados sin escanear toda la tabla?
Está fuera de tema, lo sé, pero si hay alguien que pueda explicarme esto en detalle, estaría muy, muy agradecido.
Básicamente, un índice en una tabla funciona como un índice en un libro (de ahí viene el nombre):
Digamos que tiene un libro sobre bases de datos y desea encontrar información sobre, por ejemplo, almacenamiento. Sin un índice (suponiendo que no haya otra ayuda, como una tabla de contenido), tendría que revisar las páginas una por una, hasta encontrar el tema (eso es un full table scan). Por otro lado, un índice tiene una lista de palabras clave, por lo que consultarías el índice y verías que se storagemenciona en las páginas 113-120, 231 y 354. Luego podrías pasar a esas páginas directamente, sin buscar (esa es una búsqueda con un índice, algo más rápido).
Por supuesto, la utilidad del índice depende de muchas cosas: algunos ejemplos, usando el símil anterior:
- Si tuviera un libro sobre bases de datos e indexara la palabra "base de datos", vería que se menciona en las páginas 1-59,61-290 y 292 a 400. En tal caso, el índice no es de mucha ayuda y podría ser útil. Será más rápido revisar las páginas una por una (en una base de datos, esto es "mala selectividad").
- Para un libro de 10 páginas, no tiene sentido hacer un índice, ya que puedes terminar con un libro de 10 páginas precedido por un índice de 5 páginas, lo cual es simplemente una tontería: simplemente escanea las 10 páginas y listo. .
- El índice también debe ser útil; generalmente no tiene sentido indexar, por ejemplo, la frecuencia de la letra "L" por página.
Aug 25 '2010 16:08
Lo primero que debes saber es que los índices son una forma de evitar escanear la tabla completa para obtener el resultado que buscas.
Hay diferentes tipos de índices y se implementan en la capa de almacenamiento, por lo que no existe un estándar entre ellos y también dependen del motor de almacenamiento que esté utilizando.
InnoDB y el índice B+Tree
Para InnoDB, el tipo de índice más común es el índice basado en árbol B+, que almacena los elementos en un orden ordenado. Además, no es necesario acceder a la tabla real para obtener los valores indexados, lo que hace que su consulta regrese mucho más rápido.
El "problema" de este tipo de índice es que hay que consultar el valor más a la izquierda para utilizar el índice. Entonces, si su índice tiene dos columnas, digamos apellido y nombre, el orden en que consulta estos campos es muy importante .
Entonces, 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 el índice:
SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"
Pero el siguiente no
SELECT last_name, first_name FROM person WHERE first_name = "Constantine"
Porque primero estás consultando la first_namecolumna 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 la memoria backend. Es increíblemente rápido, pero sólo es útil para búsquedas completas, lo que significa que no puedes usarlo para operaciones como >, <o LIKE.
Dado que solo funciona para la memoria backend, probablemente no lo usará con mucha frecuencia. El caso principal que se me ocurre ahora es aquel en el que se crea una tabla temporal en la memoria con un conjunto de resultados de otra selección y se realizan muchas otras selecciones en esta tabla temporal usando índices hash.
Si tiene un VARCHARcampo grande, puede "emular" el uso de un índice hash cuando usa un árbol B, creando otra columna y guardando un hash del valor grande en ella. Digamos que estás almacenando una URL en un campo y los valores son bastante grandes. También puede crear un campo de número entero llamado url_hashy usar una función hash como CRC32cualquier otra función hash para codificar la URL al insertarla. Y luego, cuando necesites consultar este valor, puedes 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 CRC32función genera un hash bastante pequeño, 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 aplicar hash incluso si el número de colisión es alto porque solo realizará la segunda comparación (la de cadena) con los hashes repetidos.
Desafortunadamente, al utilizar esta técnica, aún es necesario golpear la mesa para comparar el urlcampo.
Envolver
Algunos datos que puedes considerar cada vez que quieras hablar de optimización:
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.Quizás agregar pasos adicionales en un proceso lo haga más rápido, no más lento. Puede ilustrarse por el hecho de que puede optimizar a
SELECTdividié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 es bueno saber el hash, pero puedes encontrar los otros en la documentación de MySQL .
Le recomiendo encarecidamente que lea el libro "MySQL de alto rendimiento", la respuesta anterior definitivamente se basó en su capítulo sobre índices.
