Índices en bases de datos: cómo aceleran las consultas

Un índice en una base de datos es una estructura de datos auxiliar que la base de datos mantiene aparte de la tabla y que le permite encontrar filas rápidamente sin tener que recorrer toda la tabla. La analogía más clara y útil es la del índice de un libro: cuando se quiere localizar dónde se trata un tema concreto en un libro, no se lee el libro entero página por página, sino que se acude al índice del final, se busca el término, que está ordenado alfabéticamente, y este indica directamente la página donde aparece, permitiendo saltar a ella de inmediato. Un índice de base de datos hace exactamente lo mismo con las filas de una tabla: es una estructura ordenada que relaciona los valores de una columna con la ubicación de las filas que los contienen, de modo que cuando se busca un valor concreto, la base de datos consulta el índice y salta directamente a la fila correspondiente en lugar de examinar todas las filas una por una. La diferencia de rendimiento es enorme, especialmente en tablas grandes: sin índice, la base de datos realiza lo que se llama un recorrido completo de la tabla, revisando fila por fila hasta encontrar lo buscado, lo que en una tabla de millones de filas resulta muy lento; con un índice adecuado, la misma búsqueda se resuelve casi instantáneamente. Por eso los índices son la principal herramienta para mantener rápidas las consultas a medida que las tablas crecen, y entender cuándo y cómo usarlos es fundamental para el rendimiento de cualquier aplicación que dependa de una base de datos.

Conviene indexar las columnas que las consultas utilizan efectivamente para buscar, filtrar, unir u ordenar, ya que son en esas operaciones donde un índice marca la diferencia. En primer lugar, las candidatas más claras son las columnas que aparecen con frecuencia en las condiciones de filtrado de las consultas, es decir, en las cláusulas que restringen qué filas se quieren, como buscar por un correo electrónico, por un estado o por un rango de fechas; indexar esas columnas convierte esas búsquedas frecuentes en operaciones muy rápidas. En segundo lugar, es muy recomendable indexar las columnas que se usan para combinar tablas, que típicamente son las claves foráneas, porque las operaciones de unión entre tablas son mucho más eficientes cuando la columna por la que se unen está indexada, y esto suele tener un impacto muy notable en el rendimiento. En tercer lugar, conviene considerar indexar las columnas por las que se ordenan habitualmente los resultados, ya que un índice, al estar ordenado, puede permitir a la base de datos devolver los resultados ya ordenados sin tener que realizar el costoso trabajo de ordenarlos. Hay que tener en cuenta que la clave primaria de cada tabla ya lleva su propio índice de forma automática, por lo que no es necesario crear uno adicional para ella. Para tomar buenas decisiones sobre qué indexar, la mejor práctica no es adivinar, sino apoyarse en las herramientas de análisis de consultas que ofrecen las bases de datos, que muestran cómo se resuelve realmente una consulta y si está aprovechando un índice o recurriendo a un recorrido completo, permitiendo así indexar en respuesta a necesidades reales y medidas.

No conviene indexar todas las columnas porque los índices, a pesar de acelerar las lecturas, tienen costes reales que hacen que un exceso de ellos perjudique el rendimiento en lugar de mejorarlo. El coste más importante es que cada índice ralentiza las operaciones de escritura. Un índice es una estructura que la base de datos debe mantener siempre actualizada y coherente con los datos de la tabla, de modo que cada vez que se inserta una fila nueva, se modifica una existente o se borra una, la base de datos no solo tiene que actualizar la tabla, sino también todos los índices asociados a ella, para reflejar el cambio. Cuantos más índices tenga una tabla, más trabajo adicional supone cada escritura, lo que puede penalizar apreciablemente el rendimiento en tablas que reciben muchas inserciones, actualizaciones o borrados. El segundo coste es el espacio: cada índice ocupa almacenamiento adicional en disco, que en tablas grandes o con muchos índices puede llegar a ser considerable. Por eso indexar todas las columnas por si acaso es contraproducente: muchos de esos índices nunca se usarían en las consultas, pero todos ellos ralentizarían las escrituras y consumirían espacio. La estrategia correcta consiste en encontrar un equilibrio entre la velocidad de lectura y la de escritura, adecuado al perfil de uso de cada tabla: una tabla que se consulta mucho y se modifica poco tolera y agradece más índices, mientras que una tabla sometida a escrituras constantes conviene mantenerla con los índices estrictamente necesarios. La regla práctica es indexar solo lo que las consultas reales necesitan, guiándose por la medición y no por la suposición.

Un recorrido completo de tabla, conocido en inglés como full table scan, es la operación mediante la cual la base de datos examina todas y cada una de las filas de una tabla, una por una desde el principio hasta el final, para encontrar aquellas que cumplen la condición de una consulta. Es la forma más básica y directa de buscar, pero también la más costosa cuando la tabla es grande, porque el tiempo que tarda crece proporcionalmente al número de filas: si la tabla tiene diez filas, revisar todas es trivial, pero si tiene millones, examinarlas una por una puede tardar mucho. La base de datos recurre a un recorrido completo cuando no dispone de un índice adecuado que le permita localizar directamente las filas buscadas, de modo que no le queda más remedio que mirarlas todas para no dejarse ninguna. Aquí está precisamente el valor de los índices: cuando existe un índice sobre la columna por la que se busca, la base de datos puede evitar el recorrido completo y saltar directamente a las filas relevantes, transformando una operación lenta en una casi instantánea. Conviene matizar que un recorrido completo no siempre es malo ni evitable: en tablas pequeñas puede ser incluso más eficiente que usar un índice, y en consultas que necesitan devolver la mayoría de las filas de una tabla también puede ser lo razonable. El problema aparece cuando una consulta que debería ser selectiva, es decir, que busca unas pocas filas concretas en una tabla grande, acaba haciendo un recorrido completo por falta de un índice apropiado, y ese es justamente el tipo de situación que las herramientas de análisis de consultas ayudan a detectar para corregirla añadiendo el índice que falta.

No, la clave primaria no necesita que se le cree un índice aparte, porque las bases de datos crean automáticamente un índice sobre la clave primaria en el momento de definirla. Esto tiene todo el sentido, ya que la clave primaria es, por definición, la columna que identifica de forma única cada fila y por la que muy frecuentemente se buscan y referencian los registros, tanto directamente como a través de las relaciones con otras tablas, de modo que necesita ser rápida de localizar. Al crear ese índice de forma automática, la base de datos garantiza a la vez dos cosas: por un lado, la eficiencia en las búsquedas por la clave primaria, y por otro, el cumplimiento de la unicidad, ya que el índice le permite comprobar rápidamente que no se inserten valores duplicados en la clave primaria. Por tanto, intentar crear manualmente un índice adicional sobre la columna que ya es clave primaria sería redundante e innecesario, y solo añadiría el coste de mantener un segundo índice idéntico sin ningún beneficio. Un punto relacionado y práctico es que, aunque la clave primaria se indexa sola, las claves foráneas no siempre lo hacen automáticamente en todas las bases de datos, por lo que sí suele ser recomendable crear índices explícitos sobre las columnas de clave foránea, ya que son las que se usan para unir tablas y su indexación mejora notablemente el rendimiento de esas uniones. En resumen, la clave primaria viene indexada de fábrica y no hay que preocuparse por ella, mientras que para el resto de columnas relevantes, incluidas las claves foráneas, sí hay que decidir y crear los índices de forma consciente.

Para saber si una consulta está aprovechando un índice o si, por el contrario, está recurriendo a un recorrido completo de la tabla, las bases de datos ofrecen una herramienta de análisis del plan de ejecución, que en muchas de ellas se invoca anteponiendo una palabra clave como EXPLAIN a la consulta. Esta herramienta no ejecuta la consulta de forma normal, o al menos no solo eso, sino que muestra el plan que la base de datos ha decidido seguir para resolverla, es decir, qué pasos va a dar, en qué orden, y crucialmente si va a utilizar algún índice y cuál, o si va a examinar la tabla entera fila por fila. Interpretando ese plan, se puede detectar cuándo una consulta que debería ser rápida está en realidad haciendo un recorrido completo por falta de un índice adecuado, lo que suele ser la causa de las consultas lentas. Esta es la forma correcta y profesional de abordar los problemas de rendimiento, porque permite tomar decisiones basadas en datos concretos sobre lo que la base de datos hace realmente, en lugar de basarse en suposiciones o en la intuición. El flujo de trabajo habitual consiste en identificar las consultas lentas, analizarlas con esta herramienta para ver cómo se están resolviendo, y en caso de detectar recorridos completos innecesarios, crear los índices apropiados sobre las columnas implicadas y volver a analizar para confirmar que ahora sí se utilizan y que la consulta ha mejorado. Este enfoque de medir, ajustar y volver a medir es la base de la optimización de consultas, y evita tanto dejar consultas lentas sin resolver como crear índices inútiles que solo penalizarían las escrituras.