Mysql Indices

Introdución

• Ya vimos que podemos activar el registro de consultar lentas para determinar cuales son las consultas que más ralentizan el Mysql.

Cuando descubramos una de esas sentencias, tendremos que analizarla para ver la razón.

Una posible causa será el no utilizar indices.

• Un índice es una estructura de datos en la cual los valores guardados son los datos de una o más columnas de una tabla de forma ordenada. Cada entrada del índice apuntará a la ubicación del registro en la tabla.

De esta forma, la búsqueda de registros por un campo indexado será mucho más rápida que si no lo está.

• Como en todo, el uso de registros lleva aparejado ciertos problemas (sino fuera así podríamos crear índices para todas las columnas de una tabla):

• Es una estructura de datos que ocupa espacio a mayores del espacio de la propia tabla.
• En caso de operaciones de insercción, borrado o modificación el índice se debe rehacer con la cosiguiente carga de trabajo para el gestor.

• Toda clave primaria debe tener un índice asociado (de hecho se crea automaticamente).

También deberían tenerlo las claves foráneas.

• Un ejemplo obtenido de la wiki:

En este ejemplo, la clave primaria tiene asociado un índice de tipo BTREE, en el que las claves están ordenadas, estando al lado izquierdo las menores y al lado derecho las mayores con respecto al valor referenciado de un nodo dado.

• ¿ Cuando voy a hacer uso de índices ?

• En cláusulas WHERE
• En cláusulas ORDER BY

Si tengo una orden SQL como:

SELECT nombre FROM ALUMNOS WHERE edad between 10 and 20

Tardará menos en ejecutarse si tengo un índice asociado a la edad que si no lo tengo.

Crear índices

• Podemos crear un índice de dos formas:

• Con la orden SQL CREATE INDEX
• Con la orden SQL ALTER TABLE y la opción ADD INDEX.

• Veamos un ejemplo sobre una supuesta tabla Alumnos sobre la que queramos indexar su nombre:

1 CREATE INDEX idx_Alumnos_nombre ON Alumnos (nombre)

Siendo Alumnos el nombre de la tabla y nombre la columna a indexar.

1 ALTER TABLE Alumnos ADD INDEX idx_Alumnos_nombre (nombre)

Siendo Alumnos el nombre de la tabla y nombre la columna a indexar.

• Algunos de los modificadores que podemos añadir a la creación de un índice:

• UNIQUE: índice formado por campos cuyo valor no puede repetirse.
• PRIMARY: Es el índice que conforma la clave primaria de una tabla. Los valores no pueden repetirse. Es recomendable que sea numérica y en caso de no existir, se debe crear una columna de tipo 'autonumérico'.
• full-text: Es un tipo de índice 'especial' que se aplica a campos de texto de gran tamaño, como el tipo TEXT y sus variantes, aunque también puede realizarse sobre char y varchar. Al crear un índice de este tipo, cada una de las palabras del texto es 'indexada' para poder hacer búsquedas en las que se busca sólo alguna de las palabras del mismo. Esto se realiza haciendo uso de la orden SQL MATCH(). A diferencia de LIKE, este comando busca por palabras y permite buscar en varias columnas, indicar si deben aparecer ciertas palabras en la columna o que no aparezcan ciertas otras,...
• SPATIAL: Son índices usados para tipos de datos espaciales como LINE o CURVE.

Modificar índices

• No es posible.

Podemos utilizar la orden SQL ALTER TABLE con la opción ALTER INDEX para hacer el índice visible o invisible.

Esto hará que el optimizador de consultas no lo use (invisible) o lo tenga en cuenta (visible).

Sólo se puede utilizar en NDB CLUSTER.

• Un ejemplo:

1 ALTER TABLE Alumnos ALTER INDEX idx_Alumnos_nombre INVISBLE

Siendo Alumnos el nombre de la tabla.

Borrar índices

• Podemos usar la orden SQL DROP INDEX.
• Un ejemplo:

1 DROP INDEX idx_Alumnos_nombre ON Alumnos

Siendo Alumnos el nombre de la tabla.

Tipos de índices

Parciales

• Como comentamos en la introducción, los índices van a ocupar espacio en disco.

Una columna de 200 caracteres de ancho (200 bytes) y con 5 millones de registros, si hacemos un índice sobre ella, ocupará 1.000.000.000 de bytes = 953MB

• Para evitarlo podemos crear un índice parcial, es decir, un índice sobre una parte del tamaño de la columna.

1 CREATE INDEX idx_Alumnos_direccion ON Alumnos (direccion(100))

En este caso estamos creando un índice de la mitad del tamaño de la columna (100 caracteres) por lo tanto ocupará 953/2 = 476MB

• Se pueden crear sobre columnas de tipo CHAR, VARCHAR y TEXT.

En el caso de TEXT es obligatorio indicarlo.

También con los tipos de datos BINARY, VARBINARY y BLOG, pero en este caso indicamos el número de bytes. También es obligatorio.

Multicolumna

• Este tipo de índices abarca más de una columna.

Un caso típico podría ser el de columnas nombre y apellidos.

Si quiero crear un índice por apellidos y dentro de apellidos que ordene por nombre, crearía un único índice con las dos columnas, de la forma:

1 CREATE INDEX idx_Alumnos_nombre_apellidos ON Alumnos (apellidos(100),nombre)

Nota Fijarse que el orden de las columnas en el índice es importante. No es lo mismo crear un índice de 'nombre-apellidos' que de 'apellidos-nombre'.

Secundarios o clusters

• Cuando creamos una clave primaria, realmente estamos creando un índice sobre las columnas que conforman la clave primaria, un tipo de índice que impide que haya dos valores iguales.

Al crearlo, Mysql ordena físicamente los registros para que coincidan con el índice, de tal forma que índice y registro están 'unidos'. El registro se guarda junto a su clave primaria.

Así, si tengo creado como índice de clave primaria una columna id, físicamente los registros estarán:

ID-------Nombre
1--------Angel
2--------Maria
3--------Juan
4--------Ana

Este tipo de índice se denomina cluster.

• En una tabla, como vimos, podemos crear otros índices. Cuando usamos INNODB como motor de almacenamiento, éstos guardan el valor de la clave primaria para buscar el registro, a parte del valor del índice creado. Este tipo de índices se denominan índices secundarios o no clusterizados.

En el ejemplo anterior, si creamos un índice sobre la columna Nombre tendremos:

Nombre-------ID
Ana----------4
Angel--------1
Juan---------3
Maria--------2

Para recuperar un registro cuando usamos un índice secundario, necesitamos dos operaciones de disco, una para buscar el valor en la tabla del índice y otra para buscar el registro a partir de la clave primaria.

En el motor MyISAM pasa lo mismo, pero la diferencia es que los índices se guardan en archivos separados y el valor asociado al índice no es la clave primaria, sino el valor desplazamiento (offset) del registro en la tabla.

• Visto lo anterior es importante elegir bien la clave primaria:

• Debemos elegir un campo que no implique modificaciones, ya que será necesario una reordenación de los registro.
• Además es mejor que tenga un tamaño pequeño, ya que, si por ejemplo es una cadena de 100 caracteres, estamos guardando en todos los índices secundarios 100 caracteres en cada entrada del índice.

Estructura de índices

• Si miramos la sintaxis de la orden SQL CREATE INDEX, en la opción index_type podemos escoger entre BTREE o HASH

BTREE

• Es una estructura en forma de árbol-b (b-tree) balanceado (se intenta que haya una cantidad similar de nodos a un lado y a otro del árbol.

Cada nodo del árbol posee un valor del índice, ordenado. Cada nodo 'apunta' a registros del disco.

La cantidad de nodos hijos permitida es variable (si fuera dos, tendríamos un árbol binario, por ejemplo).

• Su uso es adecuado para consultas basadas en operadores de comparación (<,>, =, < >) y rangos de valores (between).

HASH

• Se basan en el uso de una función de HASH. Este tipo de función, transforma una entrada (en nuestro caso, la columna de la tabla sobre la que vamos a crear un índice) en un valor único.

Ese valor único es guarda en una tabla, y cada entrada de esa tabla tiene asociada el valor de la clave primaria para buscar el registro.

• La estructura de HASH es más rápida que la B-Tree cuando la utilizamos para comparar valores de la forma: columna = valor en la cláusula WHERE.

Presenta los siguientes inconvenientes:

• Puede haber colisiones (en el caso de dos valores, al aplicarse la función de HASH den el mismo resultado)
• En el caso de búsquedas por rango, es más lenta que en el caso de B-TREE.
• En el caso de cláusulas ORDER BY, Mysql no puede emplear índices HASH.

Más información en este enlace.

Trabajando con índices

• Indicamos en la introducción que uno de los factores por lo que una consulta puede ser lenta en su ejecución es por mal uso o falta de índices en la misma.

-- Ángel D. Fernández González -- (2017).