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.

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

• Sólo se pueden crear sobre columnas de tipo String (cadena).

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'.

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