La indexación es la principal herramienta para optimizar el rendimiento general de cualquier base de datos. Es también la más conocida por los usuarios de servidores MySQL y, paradójicamente, su no utilización es una de las principales causas de bajo rendimiento en servidores de bases de datos.

Muchos administradores y diseñadores simplemente parecen olvidar usar índices para optimizar los accesos a las bases de datos. Por otro lado, algunas personas tienden a indexar todo, esperando que de esta manera el servidor acelere cualquier tipo de consulta que se le solicite. En realidad, esta práctica puede causar una disminución en el rendimiento, sobre todo en lo que respecta a inserciones y modificaciones.

Para ver las ventajas de utilizar índices, analizaremos en primer término una simple búsqueda en una tabla sin índice alguno:

•    El constante acceso de escritura de una tabla la mantiene desordenada.

•    La ordenación de una tabla es una operación costosa: el servidor tendría que detenerse un tiempo considerable para ordenar sus tablas.

•    Muchas tablas tienen más de un criterio de ordenación: ordenar según una columna implica desordenar otra.

•    La inserción y eliminación de datos sin alterar el orden en una tabla es costosa: la inserción de un registro en una tabla grande implicaría una larga espera en la actualización de la misma.

•    Si se opta por mantener la tabla desordenada (que es la opción más viable), una búsqueda implicaría forzosamente un recorrido secuencial (también denominado full scan), registro por registro.

El uso de índices en la ordenación de las bases de datos ofrece las ventajas siguientes:

•    Permite ordenar las tablas por varios criterios simultáneamente.

•    Es menos costoso ordenar un archivo índice, porque incluye sólo referencias a la información y no la información en sí.

•    El coste de inserción y eliminación es menor.

•    Con los registros siempre ordenados se utilizaran algoritmos mucho más eficientes que el simple recorrido secuencial en las consultas.

El uso de índices también comporta alguna desventaja:

•    Los índices ocupan espacio en disco.

•    Aún teniendo registros pequeños, el mantener en orden un índice disminuye la velocidad de las operaciones de escritura sobre la tabla.

A pesar de estos inconvenientes, la utilización de índices ofrece mayores ventajas que desventajas, sobre todo en la consulta de múltiples tablas, y el aumento de rendimiento es mayor cuanto mayor es la tabla.

Consideremos por ejemplo una consulta sobre las tablas A, B, y C, independientemente del contenido de la cláusula where, las tres tablas se deben de combinar para hacer posible posteriormente el filtrado según las condiciones dadas:

Consideremos que no son tablas grandes, que no sobrepasan los 1.000 registros. Si A tiene 500 registros, B tiene 600 y C 700, la tabla resultante de la consulta  anterior  tendrá  210  millones  de  registros.  MySQL  haría  el  producto cartesiano de las tres tablas y, posteriormente, se recorrería la relación resultante para buscar los registros que satisfacen las condiciones dadas, aunque al final el resultado incluya solamente 1.000 registros.

Si utilizamos índices MySQL los utilizaría de una forma parecida a la siguiente:

•    Tomaría cada uno de los registros de A.

•    Por cada registro de A, buscaría los registros en B que cumpliesen con la condición A.a = B.b. Como B está indexado por el atributo 'b', no necesitaría hacer el recorrido de todos los registros, simplemente accedería directamente al registro que cumpliese la condición.

•    Por cada registro de A y B encontrado en el paso anterior, buscaría los registros de C que cumpliesen la condición B.b = C.c. Es el mismo caso que en el paso anterior.

Comparando las dos alternativas de búsqueda, la segunda ocuparía cerca del 0,000005% del tiempo original. Por supuesto que sólo se trata de una aproximación teórica, pero adecuada para comprender el efecto de los índices en las consultas sobre bases de datos.

El índice ideal debería tener las siguientes características:

•    Los registros deberían ser lo más pequeños posible.
•    Sólo se debe indexar valores únicos.

Analicemos cada recomendación:
•    Cuanto más pequeños sean los registros, más rápidamente se podrán cambiar de lugar (al insertar, modificar o borrar filas), además, en un momento dado, el índice puede permanecer en memoria. Consideremos las dos definiciones posibles:

En esta tabla el índice se realiza sobre nombre, que es un campo de 30 caracteres, y se utiliza como clave para hacer los 'joins' con otras tablas.

 

Ahora considérese la siguiente alternativa:

Se agrega una columna que servirá como identificador de la empresa. Desde el punto de vista de rendimiento implica una mejora, ya que el índice se realiza sobre números enteros, por lo tanto, ocupará menos espacio y funcionará más rápido.

•    Consideremos el índice siguiente, creado para disminuir la necesidad de efectuar accesos a la tabla:

Si consideramos que un índice se crea para evitar la necesidad de recorrer la tabla, veremos que el índice creado es prácticamente inútil, ya que alguno de los valores ocurre el 50% o más de las veces: para encontrar todos los resultados hay que recorrer gran parte de la tabla. MySQL no utiliza los índices que implican un 30% de ocurrencias en una tabla.

Aun así, y exceptuando casos exagerados como este último, puede ser interesante indexar una tabla por algún atributo que no sea único, si ese atributo se utiliza para ordenar los resultados. También puede ser conveniente crear un índice por varios atributos simultáneamente si se usan todos en alguna consulta en la cláusula ORDER BY.

* Memoria intermedia de acceso rápido.

El servidor MySQL incluye la posibilidad de utilizar una cache* con los resultados de las últimas consultas para acelerar la velocidad de respuesta. Esta solución  es  útil  cuando  las  tablas  tienen  relativamente  pocos  cambios  y  se realizan los mismos tipos de consultas. El funcionamiento de la cache se basa en las premisas siguientes:

•    La primera vez que se recibe una consulta se almacena en la cache.

•    Las siguientes veces la consulta se realiza primero en la cache; si tiene éxito, el resultado se envía inmediatamente.

La cache tiene las siguientes características:

•    El servidor compara el texto de la consulta; aunque técnicamente sea igual si difiere en uso de mayúsculas-minúsculas o cualquier otro cambio, no se considera la solicitud idéntica y no será tratada por la cache.

•    Si alguna tabla incluida en alguna consulta cambia, el contenido de la consulta es eliminado de la cache.

La configuración de la cache se realiza a través de variables globales:

•    query_cache_limit. No almacena resultados que sobrepasen dicho tamaño. Por omisión es de 1M.

•    query_cache_size. Tamaño de la memoria cache expresada en bytes. Por omisión es 0; es decir, no hay cache.

•    query_cache_type. Puede tener tres valores: ON , OFF o DEMAND 

tipos de cache

Valor	Tipo	Significado
0	OFF	Cache desactivado
1	ON	Cache activado
2	DEMAND	Sólo bajo solicitud explicita

 

 

 

 

 

Cuando la cache del servidor esta en modo DEMAND, se debe solicitar explícitamente que la consulta utilice o no la cache: