Un índice es aquella estructura de una base de datos que permite acceder a la información de las tablas de manera más rápida y eficiente. Un índice siempre estará asociado a una tabla y se necesita un espacio adicional para su almacenamiento y tiempo para su mantenimiento.
Un índice hace más rápida las consultas (SELECT)
Una tabla puede -no es obligatorio- uno o más índices, y el objetivo de tenerlos es que la búsqueda de registros sea más rápida. De igual manera que los libros tienen un índice (al inicio o al final) para poder ubicar rápidamente la página de un determinado tema o capítulo, en las tablas sucede lo mismo: se crea un índice (asociado a una tabla) para relacionar rápidamente un término (como los capítulos de un libro) con su ubicación (la página donde empieza el capítulo). De esa manera, si se busca un término, se busca ubica primero en el índice, y el índice indica donde empieza el registro que se está buscando.
Como en los libros, que pueden tener varios índices (por capítulos, por tablas, por imágenes, por palabras) en una tabla podemos tener varios índices (por nombre del alumno, por dirección, por boleta, por sexo) de tal forma que si se quiere buscar por alguno de los términos que conforman los índices (nombre, dirección, boleta o sexo) el motor de bases de datos es lo suficientemente eficiente como para buscar primero en el índice y luego en los datos.
Un ejemplo del plan de ejecución de dos querys sobre dos tablas casi idénticas, la primera no tiene índices y la segunda tiene un índice por el cual se hacen búsquedas.
Como puede verse, el segundo query (el SELECT sobre la tabla que tiene un índice) es mucho más rápido que el primero (SELECT sobre una tabla que no tiene índices) por que se utiliza o no un índice.
A primer vista suena ideal el tener índices («mientras más mejor» diría algún desarrollador, «para que las consultas sean más rápidas») pero la rapidez en las consultas tiene un costo, precisamente en mantener actualizados los índices.
Un índice hace más lentas las actualizaciones (INSERT, UPDATE, DELETE)
Si el autor de un libro se le ocurre añadir un párrafo en cualquier parte del libro ¿qué sucede con los índices? Provoca que quizá todos los capítulos se muevan de su página inicial y se recorran una página posterior. ¿Y si se elimina un párrado? Igual, quizá los índices se tienen que actualizar para reflejar la posición de los temas considerando que algunos se hayan movido de lugar. Lo mismo sucede con una tabla que tiene índices, cada vez que se añade un registro, se borra un registro o se cambia el valor de un campo que forma parte de un índice, los índices afectados tienen que ser recalculados para que siempre estén actualizados.
Esto hace que la actualización de una tabla sea más lenta cuando se tienen índices que cuando no se tienen índices. Los índices además, ocupan espacio adicional, por lo que también es un tema a considerar: su estructura y el espacio que ocupa.
Estructura de un índice
Un índice, como en los libros, ocupa un espacio adicional al contenido principal; son esas hojas al inicio o al final del libro que si bien hacen más rápidas las búsquedas, ocupan más hojas y hacen la libro más grande. Mientras más índices más hojas se necesitarán, y cada índice ocupa varias hojas. En los índices de tablas sucede lo mismo, se ocupa espacio -en el disco- que es adicional a los registros de la tabla y que son estructuras que apuntan a los registros de las tablas.
Si bien no se guardan como un árbol binario, si se puede utilizar este concepto para ejemplificar la estructura y la forma de trabajar de un índice.
Un árbol binario ordena un conjunto de datos de tal manera que no importa el tamaño del conjunto de datos, el árbol siempre representará los datos en una estructura ordenada. Suponiendo que se tienen los datos 32, 72, 99, 11, 29, 50, 91, 20, 65 y 41, la representación de los datos en un árbol binario sería como sigue
Si los datos representaran, por ejemplificar, el número de boleta de los alumnos, tendríamos los 10 alumnos ordenados (independientemente de la ubicación de los datos en el disco). Si dentro de cada nodo, además del numero de boleta, se tuviera la ubicación física donde empieza el registro de cada alumno, lo único que se tiene que hacer para buscar un alumno en específico es tener su número de boleta, ubicarlo en el árbol binario, y tomar de ahí la ubicación física del registro para leer todo el registro.
Cada vez que se añade un registro -un alumno- se tiene que grabar el registro, y actualizar el índice.
Si por un lado los índices hacen rápidas las consultas, pero por otro las hacen costosas (en espacio y en tiempo) ¿entonces cuándo es recomendable utilizar índices?
¿Cuándo considerar añadir índices?
La pertinencia o necesidad de crearlos dependerá de los requerimientos de las aplicaciones. Salvo el índice de la llave principal, todos los demás índices deberán ser acordados por el DBA y el área de desarrollo, sin embargo, hay algunas consideraciones generales de cuando si y cuando no usar índices
1. Usar índices cuando el tiempo de respuesta a las consultas es prioritario
Existen situaciones o ambientes donde una consulta, un reporte o un tablero de información requiere de un tiempo de respuesta mínimo. En estos ambientes será necesario añadir los índices necesarios para que dichas pantallas, reportes o consultas respondan de la manera esperada.
Una búsqueda de un producto en una tienda en línea, la consulta de infracciones proporcionando el número de placa de un auto, o la consulta de saldo en un cajero automático son ejemplos de ambientes donde la respuesta o consulta debe ser rápida.
En lo ejemplos anteriores se conocer los datos por los cuales se harán las consultas, (se conoce la placa, se conoce el nombre del producto o se conoce el número de cuenta en el cajero automático), es decir, se conoce la llave por la cual se hará la búsqueda. Es diferente a cuando no se conoce la llave y se requiere hacer la búsqueda por un criterio muy general o incompleto (por algunas letras intermedias en el nombre, por una terminación de una placa de automóvil, por ejemplo).
2. Usar índices en tablas con muchos registros
Si se requiere hacer una búsqueda en una tabla de unos cuantos cientos de registros no vale la pena el añadir índices; un índice será útil cuando la búsqueda con un índice sea mucho más rápida y eficiente que sobre la tabla sin índices. Si se utiliza el ejemplo del libro, la búsqueda de un capítulo en un pequeño folleto de 20 páginas, pero en uno de 800 páginas si representa una ventaja.
3. No es recomendable índices cuando hay muchas transacciones
Cuando existe una tabla que tendrá una carga muy grande de transacciones (altas, bajas o modificaciones de registros) comparada con las consultas, entonces quizá sea recomendable no añadir índices innecesarios. Lo recomendable en esos casos es comenzar la operación de las aplicaciones con pocos índices y solo añadir nuevos índices conforme se encuentren cuellos de botella o tiempos de espera superiores a lo deseado.
4. No se recomienda tener índices activos en tablas con cargas masivas de información
En ocasiones hay tablas que tienen transacciones focalizadas en el tiempo, es decir, hay cargas masivas de registros en momentos concretos (un proveedor envía su catálogo de productos por las noches, se actualizan los inventarios por las noches, o se elaboran los cortes mensuales al inicio de cada mes) en esos casos el tener muchos índices tendrá un efecto negativo en el tiempo de procesamiento de estas cargas masivas o cargas en batch. En esas situaciones será recomendable, en todo caso, eliminar los índices momentaneamente -mientras se realiza la carga- y luego se crean nuevamente los índices al finalizar la carga en batch
5. No son útiles cuando se hacen transformaciones a los campos llave
A veces las búsquedas no se realizan por un campo en su totalidad, si no que se hace solo por una fracción de él (en lugar de buscar por fecha de nacimiento -considerando que haya un índice por fecha- se busque solo por el mes, o cuando se requiere buscar por el segundo nombre de pila de un alumno y no por el nombre completo) y en esas situaciones un índice no ayuda a que la búsqueda se más eficiente, ya que se necesitan hacer transformaciones durante la consulta.
Los campos llave que forman el índice de una tabla
Una vez que se haya determinado que por temas de performance o rendimiento es recomendable añadir un índice a una tabla, el DBA deberá entonces crear dicho índice tomando en consideración la llave o los campos que formarán los índices. Una vez definido el o los campos que formarán el índice, se deberá crear dicho índice por medio de un comando de SQL (DDL específicamente) que se llama CREATE INDEX, mismo que será revisado en un artículo independiente para revisar sus sintáxis y revisar los tipos de índices dependiendo de cada motor de bases de datos.
Consideraciones de uso de índices
La administración de los índices es un tema complejo, que si se hace correctamente puede ayudar enormemente al rendimiento de las bases de datos y de las aplicaciones, y considera aspectos como
- Estadísticas
- Tipos de índices
- Ubicación de los índices
- Fill factor
- Regeneración de índices
- Tamaño
Todos estos temas serán abordados, pero por lo extenso de cada tema, será recomendable hacerlo en artículos independientes, incluyendo algunos consejos útiles para usar o no usar índices en tablas.