unico - ¿Cómo funcionan los índices de MySQL?

tipos de datos y los tipos de índices de mysql (6)

Básicamente, un índice en una tabla funciona como un índice en un libro (de ahí proviene el nombre):

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

Por supuesto, la utilidad del índice dependerá de muchas cosas, algunos ejemplos, utilizando 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 más rápido para recorrer 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 puede terminar con un libro de 10 páginas con el prefijo de un índice de 5 páginas, lo cual es una tontería, simplemente escanee las 10 páginas y listo. .
El índice también debe ser útil: generalmente no hay ningún punto para indexar, por ejemplo, la frecuencia de la letra "L" por página.

Estoy realmente interesado en cómo funcionan los índices de 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 me pueda explicar esto en detalle, estaría muy, muy agradecido.

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

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

2: Si es más alto, vaya al punto intermedio entre la parte media e inferior, si es la parte baja, media y superior

3: es más alto o más bajo? Salta de nuevo al punto medio, etc.

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

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

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

La forma en que funcionan es demasiado amplio de un tema para cubrir en una publicación SO.

Here está una de las mejores explicaciones de los índices que he visto. Desafortunadamente es para SQL Server y no para MySQL. No estoy seguro de cuán similares son los dos ...

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 existen los índices, 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 a un índice de un libro. Si desea buscar un tema, primero busque en el índice y luego abra 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 a menudo consulta los datos. Observe que todas las columnas de clave primaria están en el índice primario de la tabla automáticamente.

Si el índice ayuda a acelerar los datos de consulta, ¿por qué no usamos índices para todas las columnas? Si creas 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 debe reconstruir el índice, lo que lleva tiempo y disminuye el rendimiento del servidor de la base de datos. Creando MySQL Index

A menudo creas índices cuando creas tablas. MySQL agrega automáticamente cualquier columna que se declara como CLAVE PRINCIPAL, CLAVE, ÚNICA o ÍNDICE al índice. Además, puede agregar índices a las tablas que ya tienen datos.

Para crear índices, use la sentencia CREATE INDEX. Lo siguiente ilustra la sintaxis de la sentencia CREATE INDEX: 1 2 3

Primero, especifique el índice basado en 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 el valor NULL duplicado en todos los motores de almacenamiento excepto BDB. El índice FULLTEXT solo es compatible con el motor de almacenamiento MyISAM y solo se acepta en la columna que tiene un tipo de datos como 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, debe nombrar el índice y su tipo después de la palabra clave USING, como BTREE, HASH o RTREE, también en función del 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 admisibles del motor de almacenamiento MyISAM BTREE, RTREE InnoDB BTREE MEMORY / HEAP HASH, BTREE NDB HASH

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

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

CREATE INDEX officeCode ON employees(officeCode)

Eliminando Índices

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

DROP INDEX index_name ON table_name

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

DROP INDEX officeCode ON employees

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

Existen diferentes tipos de índices y se implementan 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 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 tiene que acceder a la tabla real para obtener los valores indexados, lo que hace que su consulta sea más rápida.

El "problema" de este tipo de índice es que debe consultar el valor que se encuentra más a la izquierda para usar el índice. Por lo tanto, si su índice tiene dos columnas, diga last_name y first_name, 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 lo haría.

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

Porque estás consultando primero la columna de primer nombre y no es la columna más a la izquierda en el í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 de hash

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

Ya que solo funciona para el backend de memoria, probablemente no lo uses muy a menudo. El caso principal en el que puedo pensar ahora es en el que creas una tabla temporal en la memoria con un conjunto de resultados de otra selección y realizas muchas otras selecciones en esta tabla temporal utilizando índices hash.

Si tiene un campo VARCHAR grande, puede "emular" el uso de un índice hash cuando usa un árbol B, creando otra columna y guardando un valor hash del valor grande en él. Digamos que está 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 de hash como CRC32 o cualquier otra función de hash para hash la url al insertarla. Y luego, cuando necesita 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 de 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ás la segunda comparación (la cadena uno) contra los hashes repetidos.

Desafortunadamente, al usar esta técnica, todavía debes golpear la tabla para comparar el campo de url .

Envolver

Algunos datos que puede considerar cada vez que quiera hablar sobre optimización:

La comparación de enteros es mucho más rápida que la comparación de cadenas. Puede ilustrarse con el ejemplo sobre la emulación del índice hash en InnoDB .
Tal vez, agregar pasos adicionales en un proceso lo hace 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 los primeros valores de almacenamiento en una tabla en memoria recién creada, y luego ejecutar las consultas más pesadas en esta segunda tabla.

MySQL también tiene otros índices, pero creo que B + Tree one es el más usado y el hash uno es bueno saberlo, pero puedes encontrar los otros en la dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html .

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

Toma en this videos para más detalles sobre la indexación

Indizació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 utilizar 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 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)