¿Cómo funcionan los índices de 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):

Supongamos que tiene un libro sobre bases de datos y desea obtener información sobre, por ejemplo, almacenamiento. Sin un índice (suponiendo que no haya otra ayuda, como una tabla de contenido), tendría que recorrer las páginas una a una, hasta que encuentre el tema (es una full table scan ). Por otro lado, un índice tiene una lista de palabras clave, por lo que consultaría el índice y vería que el storage se menciona en las páginas 113-120, 231 y 354. Luego, podría pasar a esas páginas directamente, sin buscar (es una búsqueda con un índice, algo más rápido).

Por supuesto, cuán útil será el índice, depende de muchas cosas, algunos ejemplos, usando el símil anterior:

  • si tuviera un libro en 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 sea ​​más rápido ir por las páginas una a una (en una base de datos, esto es “selectividad pobre”).
  • Para un libro de 10 páginas, no tiene sentido hacer un índice, ya que puede terminar con un libro de 10 páginas con el prefijo de 5 páginas, lo cual es una tontería: simplemente escanee las 10 páginas y termine con esto. .
  • El índice también debe ser útil; generalmente no tiene sentido indexar, por ejemplo, la frecuencia de la letra “L” por página.

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.

Existen 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 B + Tree, 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 sea mucho más rápida.

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, por ejemplo, apellido y primer nombre, el orden en que consulte 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 está consultando primero la columna first_name 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á comparando la parte más a la derecha del campo situado más a la derecha en el índice.

El índice hash

Este es un tipo de índice diferente que, lamentablemente, solo admite el backend de memoria. Es increíblemente rápido, pero solo es útil para búsquedas completas, lo que significa que no puedes usarlo para operaciones como > , < o LIKE .

Como solo funciona para el backend de memoria, probablemente no lo use con mucha frecuencia. El caso principal en el que puedo pensar ahora 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 usando índices de hash.

Si tiene un gran campo VARCHAR , puede "emular" el uso de un índice hash al usar un B-Tree, creando otra columna y guardando un hash del gran valor en él. 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 copiar 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 un hash bastante pequeño, terminará 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 cosas incluso si el número de colisión es alto porque solo realizarás la segunda comparación (la cadena uno) contra los hashes repetidos.

Desafortunadamente, al usar esta técnica, aún necesita golpear la mesa para comparar el campo de la url .

Envolver

Algunos hechos que puede considerar cada vez que quiera hablar sobre la 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 haga más rápido, no más lento. Se puede ilustrar 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 B + Tree uno es el más utilizado y el hash es bueno saberlo, pero puede encontrar los otros en la documentación de MySQL .

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

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

1: Ir al centro de la lista: ¿es más alto o más bajo que lo que estoy buscando?

2: Si es más alto, vaya a la mitad del punto entre el centro y el fondo, si es inferior, medio y superior

3: ¿Es más alto o más bajo? Salta al punto medio otra vez, etc.

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

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

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

Un índice de base de datos es similar a un índice de un libro. Si desea buscar un tema, primero busca en el índice y luego abre la página que tiene el tema sin escanear todo el libro.

Se recomienda encarecidamente que cree un índice en las columnas de la tabla desde la que suele consultar los datos. Tenga en cuenta que todas las columnas de clave primaria están en el índice principal de la tabla de forma automática.

Si el índice ayuda a acelerar los datos de consulta, ¿por qué no utilizamos índices para todas las columnas? Si crea un índice para cada columna, MySQL debe comstackr y mantener la tabla de índices. Cada vez que se realiza un cambio en los registros de la tabla, MySQL debe reconstruir el índice, lo que lleva tiempo y disminuye el rendimiento del servidor de la base de datos. Creando el índice MySQL

A menudo creas índices cuando creas tablas. MySQL agrega automáticamente cualquier columna declarada como PRIMARY KEY, KEY, UNIQUE o INDEX al índice. Además, puede agregar índices a las tablas que ya tienen datos.

Para crear índices, usa la instrucción CREATE INDEX. A continuación, se muestra la syntax de la sentencia CREATE INDEX: 1 2 3

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

Primero, especifica el índice según el tipo de tabla o el motor de almacenamiento:

ÚNICO significa que MySQL creará una restricción de que todos los valores en el índice deben ser únicos. Se permite duplicar el valor NULL en todos los motores de almacenamiento excepto en BDB. El motor FULLTEXT 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. El índice SPATIAL admite columnas espaciales y está disponible en el motor de almacenamiento MyISAM. Además, el valor de la columna no debe ser NULL.

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

Estos son 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 MEMORY / HEAP HASH, BTREE NDB HASH

Tercero, declaras el nombre de la tabla y una lista de columnas que deseas agregar al índice. Ejemplo de creación de índice en MySQL

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

CREATE INDEX officeCode ON employees(officeCode)

Eliminar Índices

Además de crear índice, también puede eliminar el índice utilizando la instrucción DROP INDEX. Curiosamente, la statement DROP INDEX también se asigna a la instrucción ALTER TABLE. La siguiente es la syntax para eliminar el índice: 1

DROP INDEX index_name ON table_name

Por ejemplo, si desea eliminar el índice de officeCode de la tabla de empleados, que hemos creado anteriormente, puede ejecutar la siguiente consulta: 1

DROP INDEX officeCode ON employees

Vea en estos videos para obtener más información 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 syntax 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 UNIQUE de la consulta para crear un índice simple. El í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) 

Eche 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 cubrir en una publicación SO.

Esta es una de las mejores explicaciones de los índices que he visto. Desafortunadamente es para SQL Server y no para MySQL. No estoy seguro de qué tan similares son los dos …