En pasadas entradas sobre MySQL os conté primero la ventaja a nivel de rendimiento que supone tener índices en las bases de datos, y posteriormente, cómo forzar a MySQL a usar un índice que no es el que MySQL estima que es el mejor. La entrada de hoy profundiza en cómo esel mecanismo de los índices en las tablas de MySQL-MariaDB, con el fin de que lo tengas presente para la optimización de consultas.
Aunque esta entrada es bastante teórica, creo que es muy interesante que prestes toda la atención a los distintos conceptos a explicar, pues la realización de consultas eficientes es altamente dependiente de los índices, sobre todo si tienes que trabajar con alguna tabla que contenga un buen número de registros. Llegado este caso, no es lo mismo recorrer la tabla entera, que sólo una fracción de la misma. También pueden ayudar las particiones, pero de esto hablaremos en otra entrada.
Empezamos con varias definiciones.
Definición de tabla
Para entender qué es una tabla, lo primero de definir qué es un diccionario:
Un diccionario es un conjunto de pares clave-valor.
En base a esta definición, definimos tabla:
Una tabla es un cojunto de diccionarios donde éstos pueden ser modificados (update) o bien seleccionados (select), y donde está permitido la agregación de nuevos elementos (insert) o la eliminación (delete).
Algunos ejemplos de diccionarios son B-Trees o Fractal Trees, que son ampliamente usados en las bases de datos.
B-Tree es un método de colocar y localizar ficheros (o registros) en las bases de datos, con el objetivo de minimizar el número de veces que se tarda en localizar un registro concreto, acelerando el proceso.
Un índice Fractal Tree es una estructura que mantiene la información ordenada y permite la búsqueda y acceso secuencial de la misma forma que B-Tree, pero donde las inserciones y eliminaciones son más rápidas que con B-Tree.
Como definición adicional, consultas de rango es una operación común de las bases de datos consistente en recuperar todos los registros donde algún valor está comprendido entre un valor máximo y uno mínimo, como por ejemplo, cuando se usa la cláusula BETWEEN.
Definición de índice
Un índice es una estructura de datos organizada para la búsqueda de registros en una tabla. Se caracteriza por estar separado de la tabla para organizar mejor la información. Otra definición:
Un índice en una tabla no es sino un diccionario (clave-valor) con las siguientes particularidades:
- La clave es un subconjunto de datos del diccionario principal (registro)
- El valor es la clave primaria de la tabla.
La clave del índice es un subconjunto de campos del diccionario principal. El valor del índice es la clave primaria de la tabla (aunque hay otras formas de definirlo, ésta es la más común).
Los tipos más comunes de índices son B_Tree, Hash, Log-structured Merge, full-text, spatial, … No profundizamos en detalles, porque no merece la pena para nuestro objetivo.
El orden de ordenación de los diccionarios queda definido por los índices.
Cuando se realiza una consulta a la base de datos basada en criterios definido en los índices (con los diccionarios odenados), la respuesta es muy rápida, frente a cuando los criterios de búsqueda no están indexados, que requieren del escaneo de la tabla completa.
El motivo por el que se crean índices en las tablas es para hacer las consultas más rápidas, pero para el diseño de los índices es fundamental conocer las consultas que se van a realizar sobre la tabla. El precio que hay que pagar por los índices pueden ser muy elevados si no se realizan correctamente, siempre con la base de las consultas a realizar. El coste se debe a que cada inserción, actualización o borrado de datos requiere también trabajo por parte de los índices, por lo que la inserción, actualización o borrado no será tan rápida.
Piensa que si al realizar una consulta sobre una tabla no existe un índice óptimo, entonces se leerá toda la tabla, y esta operación puede suponer un elevado coste en tiempo!
Las índices deben estar preparados para una serie de consultas, no para todas, por lo que es posible que consultas que no trabajen con índices sean lentas.
Tres reglas para definir los índices
Antes de indicar las reglas, es importante saber que las reglas son de aplicación independientemente del algoritmo a utilizar (tasnto B-Tree como Fractal Trees) y de la estructura de las tablas. Además, el muy importante seleccionar la consulta o consultas más frecuentes que va a tener la tabla, para adecuar los índices a dichas consultas, además del coste de mantenimiento de los índices.
Además, no hay una regla fija, sino que son:
- Recupera cuando menos datos puedas: cuanto más datos a recuperar, más trabajo, más ancho de banda, más recursos, … basada en la cláusula WHERE.
Para poder aplicar esta regla, hay que estudiar la cláusula WHERE, y lo mejor es que los campos de igualdades estén al principio del índice, lo que reduce el número de filas a procesar. Si no hay una igualdad, el orden de los campos del índice no asegura el recuperación de las menos filas posibles
- Evita consultas puntuales: el acceso secuencial es mucho más rápido que el acceso puntual.
Si bien es cierto que el coste por registro de recorrer toda una tabla es bajo, el precio por los registros buscados es alto, precísamente por tener que recorrer toda la tabla. Sin embargo, es deseable que en el índice también esté incluido campos sobre los que requerimos operaciones, de forma que los datos estén ya presentes en el índice, y no tenga que volver a la tabla a buscarlos. Es por ello, que para evitar el coste de buscar otras columnas en la tabla, los índices deberían incluir todos los campos usados en la sentencia completa, y no sólo los de la cláusula WHERE.
- Evita el ordenado, ya que los operadores GROUP BY y ORDER son aplicados una vez recuperados los datos, y por tanto, implica más trabajo.
La mejor forma de evitar el ordenado es usando esos campos en los índices, lo que ya pre-ordena esos datos, de forma que la consulta pueda ser ejecutada más rápidamente.
Aunque no se ha mencionado aún, es posible diseñar índices por varios campos, pero hay que tener en cuenta que en la consulta debe respetarse el orden de los campos del índice para que el funcionamiento del índice sea óptimo, y que el orden de los campos del índice es muy importante.
Esto es solo el principio de trabajar con índices
Cómo te he comentado al principio, esta entrada es bastante teórica y requiere de entrenamiento para la puesta en práctica de estas tres reglas, y que dependen de la tabla, de la consulta o consultas, …. por lo que espero que esta introducción a los índices te sirvan para entenderlos mejor. La teoría de los índices es bastante extensa, por lo que dominar los índices requiere tiempo, estudio, y sobre todo, ensayo-error.
Los índices requieren de mucho análisis de las consultas!
Buen día.
PD: Te recomiendo que veas la charla Understanding Indexing, donde explica todo esto y con ejemplos!