MariaDB / Indices

Bienvenidos sean a este post, hoy hablaremos sobre los indices en nuestras tablas.

Pero que es un indice? Podemos definirlo de la siguiente manera:

Un elemento que posee la informacion ordenada y un enlace al dato original

El Tinchicus

Es decir, un indice puede ser definido en una o mas columnas y su orden es relevante, un indice que involucra columnas de cadenas puede ser definido con sus prefijos, para las columnas de tipo de TEXT o BLOB estos son mandatorios, de indices tenemos dos tipos que son los mas importantes:

• HASH
• BTREE

Tambien existe otro llamado RTREE pero esta pensado para datos geometricos, y los de tipo FULL TEXT para queries de texto-completo (full-text) pero no lo veremos por el momento, si consigo informacion interesante armare un anexo especial.

El tipo HASH puede ser usado solo para comparaciones de igualdad, es decir los operadores = o <=>(operador nulo-igual seguro), no puede ser usado para ordenar o agrupar filas.

Nota: 
Este operador tiene la particularidad de devolver 1 si ambos valores son de tipo NULL, despues trabaja de la misma forma que el operador =

El tipo BTREE en cambio puede ser usado por muchos operadores como <, <=, =, >=, >, LIKE, BETWEEN e IN, este puede ser usado para ordenar y agrupar filas, por lo tanto en la mayoria de los indices usaremos a este tipo por lo que deben asumir a este cuando no lo especifiquemos, pero entonces cual es la gracia de usar uno u otro? en el caso de HASH nos puede resultar mas util para realizar busquedas exactas de una entrada, tambien este tipo es mas rapido que BTREE pero para diferentes tipos de queries estos datos seran completamente ignorados, incluso para aquellos donde los BTREE no sufren problemas de performance, los indices de tipo BTREE son los predeterminados para la mayoria de los motores de almacenamiento, sin embargo desde que el motor MEMORY es utilizado para «cachear» datos (aquellos utilizados para la busqueda una entrada exacta) su tipo predeterminado es HASH, por lo tanto no se olvidan de establecer a BTREE para los casos donde HASH no esta disponible para sus queries, aunque InnoDB tiene un algoritmo adaptativo que convierte en BTREE todos los indices HASH cuando sea necesario.

A la hora de trabajar, mariadb utiliza la parte izquierda de un indice, por ejemplo si nosotros definimos una sola columna como indice y hacemos una busqueda de la siguiente manera:

SELECT * FROM t WHERE col1 LIKE 'inicia%';

Esta busqueda si aprovechara al indice y podra realizar una busqueda mas rapida, en cambio si utilizamos la parte central o derecha del indice este no sera utilizado, les doy un ejemplo:

SELECT * FROM t WHERE col1 LIKE '%fin';

Algo similar ocurre si tenemos multiples columnas como indices, en este caso se utiliza las columnas que estan mas del lado izquierdo, pero en este caso no influye las consultas del lado izquierdo como vimos anteriormente, vamos a suponer el siguiente escenario, tenemos en la tabla dos indices con las columnas col1 y col2, el siguiente query si toma ventaja del indice:

SELECT * FROM t WHERE col1 = 10;

En cambio el siguiente query no aprovecha al indice:

SELECT * FROM t WHERE col2 = 10;

Las opciones ORDER BY y GROUP BY pueden usar un indice inclusive si las columnas no corresponden con los ordenes antes comentados pero tiene truco, dado que si pasamos primero la columna que no este del lado izquierdo se creara una tabla temporal o archivo y aqui se ordenara, y esta simple operacion puede generar problemas de performance y deberia ser evitado, es decir si tenemos el siguiente query:

SELECT * FROM t ORDER BY col1, col2;

Este aprovechara el indice y no hara ningun operacion extra, en cambio el siguiente query:

SELECT * FROM t ORDER BY col2, col1;

Si procedera a generar una tabla temporal para ordenar correctamente la informacion generando el inconveniente antes comentado, esta es una introduccion basica a los indices como entender el concepto de como nos ayudan a evitar el exceso de trabajo en ordenar correctamente la informacion antes de entregarla, evitando el uso del log lento que vimos en este post.

A pesar de todo lo comentado anteriormente, el que tiene la ultima palabra es el optimizador y si el considera que el indice informado no cumple las condiciones necesarias para mejorar el query procede a utilizar otro indice o de ser necesario un scaneo completo de la tabla, es decir como regla general el indice debe ayudar a mejorar las operaciones de lectura, pero que reduce las operaciones de lectura en un indice? El numero de valores unicos en un indice, esto genera una mejor cardinalidad porque queda establecida como la diferencia entre el numero de filas y valores nulos de la misma, por lo tanto si un indice tiene pocos valores (como un ENUM o un TINYINT usado como booleano) producira una muy baja cardinalidad y esto permitira tener una mejora manera de saber cuantas lineas se deben excluir si se utiliza un indice para satisfacer un modificador WHERE.

Nota: 
Cardinalidad se refiere a la cantidad de elementos que representan a un conjunto, en este caso a la columna usada como indice.

Entonces para determinar si un indice es util, el optimizador toma los siguientes factores en cuenta:

• La cardinalidad del indice
• La longitud del indice
• El numero de lineas de la tabla

Suponiendo que tenemos una tabla muy pequeña no es necesario tener indices porque el numero de lineas no justificaria al mismo dado que se puede usar el chequeo completo de la misma pero un inconveniente que se nos puede presentar con la cardinalidad es la forma que la trabaja mariadb, esto es debido a que no sabe el valor exacto sino que es un valor estimado, esto puede ocasionar que el valor de este no este relacionado a los datos reales y ocasionar que en lugar de usar un indice haga un chequeo de la tabla erroneamente, para poder re-calcular a la cardinalidad se usa a ANALYZE TABLE y para obtener el valor estimado se usa a SHOW INDEX.

Una tabla InnoDB siempre tiene un indice agrupado, este es una columna que contiene valores ordenados que identifican a cada linea, por lo tanto cada linea tiene indices secundarios con los valores indexados seguido del valor del indice agrupado de esta misma linea, dado que solo el indice agrupado tiene los apuntadores a la posicion final de la lineas en los archivos, esto permite que se realice una mejor busqueda para un valor especifico en la clave primaria (PRIMARY KEY), en este caso InnoDB utiliza a la clave primaria como indice agrupado (en caso de existir), de lo contrario utiliza una clave unica (UNIQUE), y en caso de no existir esta el motor se encarga de generar uno, este es un valor unico de 6 bytes que es invisible para el usuario.

Nota: 
Dado que estos indices agrupados bloquean mucho mas que las claves primarias, se tiene como regla crear siempre claves primarias pequeñas para evitar esto.

Tambien mencionaremos que el AUTO_INCREMENT previene que dos conexiones simultaneas trabajen con el mismo valor sobre la columna, este trabajo depende de la variable innodb_autoinc_lock_mode, la cual puede poseer los sigueintes valores:

• 0, bloquea la tabla hasta que la operacion INSERT termine
• 1, indica que el INSERT multiple y LOAD DATA INFILE necesitan de un bloque de tabla para evitar inconvenientes, esta pensado para entornos de replicacion
• 2, indica que no se utilizaran bloqueos en las tablas, puede ser peligroso pero es necesario para trabajar con Galera

Si la variable innodb_adaptive_hash_index es seteada con el valor de 1, predeterminado, nos permite convertir los indices de BTREE en HASH y viceversa, por ultimo el motor de tipo CSV no permite indices o tiene un uso muy limitado de ellos.

En resumen, hoy hemos visto indices, de una forma muy basica, como son, como nos ayudan, para que se usan, algunos de los tipos disponibles, tambien hemos visto como trabajan realmente y como el mal llamado de los mismos puede acarrear mayores inconvenientes, tambien quien es el que decide cuando usarlos, como una mala interpretacion puede hacer un mal uso, y el tema de las claves en nuestras tablas, espero les haya sido util sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.