Guía del analista de datos para la indexación SQL: Corregir Consultas Lentas

¿El tiempo de respuesta de sus consultas SQL deja que desear? O quizás no sabes si tus consultas podrían ser más rápidas. En este artículo, le explicaremos cómo puede ayudarle la indexación SQL.

Sumergirse en SQL es como desbloquear un superpoder. Se trata de dominar el arte de consultar eficazmente la base de datos para poder recuperar la información que necesitas de forma rápida y sencilla. Pero, ¿qué ocurre cuando se hace una pregunta lo suficientemente compleja como para que el tiempo de respuesta pase de segundos a minutos, o incluso más? En ese momento es cuando hay que decir hola a la indexación SQL.

En este artículo, compartiré contigo todo lo que necesitas saber sobre la indexación SQL y por qué debería ser el siguiente paso en la optimización de tus consultas SQL. ¡Entremos en materia!

SQL y las consultas a bases de datos

SQL es un lenguaje para consultar y mantener bases de datos. Permite almacenar y visualizar datos, así como actualizarlos. La gestión de datos es crucial para el análisis de datos y la inteligencia empresarial. Necesitamos gestionar los datos subyacentes para poder analizarlos y extraer conclusiones de ellos: ¿Qué productos se venden bien? ¿Qué clientes compran mucho? ¿Qué clientes no pagan a tiempo?

Cuando tengamos los datos, podremos responder a este tipo de preguntas. Pero antes tenemos que "hacer preguntas" o "peticiones" sobre nuestros datos. Estas peticiones se denominan consultas: por ejemplo, algo como "Para las ventas del mes pasado, cuente el número de veces que se vendió cada producto y facilíteme la suma de las ventas".

Por supuesto, a medida que formulamos preguntas más complejas, nuestras consultas se vuelven más complejas. Puede que necesitemos combinar (o JOIN) información de múltiples fuentes para poder extraer la información que necesitamos.

Por ejemplo, es útil saber que el producto ID 2123876123 fue el más vendido durante el trimestre anterior, pero es aún más útil saber que ese producto es una sudadera azul. A medida que aumenta la complejidad de nuestras consultas, el sistema de base de datos tiene más dificultades y tarda más en responder. Necesitamos acelerar nuestras consultas SQL, pero ¿cómo?

Aquí es donde empieza la diversión. Entender cuándo una consulta responde con lentitud y cómo mejorar su rendimiento es un arte sutil. Podríamos intentar modificar nuestra consulta y hacerla más eficiente, pero el ajuste del rendimiento de SQL sólo nos llevará hasta cierto punto. En algún momento tendremos que optimizar la consulta. Esto facilita al sistema de base de datos la recuperación de los resultados que buscamos, lo que mejora el rendimiento de la consulta.

Aquí es donde entran en juego los índices.

¿Qué es un índice SQL?

Al igual que el índice de un libro, los índices SQL preparan el sistema de base de datos para una recuperación de datos más eficaz. La creación de índices es sencilla; tenemos un curso completo sobre índices que le enseñará todo lo que necesita saber. Esto incluye cómo se crean dentro de la base de datos, la sintaxis SQL para crear un índice y cuándo crear un índice. Hay más de 50 ejercicios y un estimado de 10 horas de aprendizaje.

Si quieres una introducción a la parte teórica de los índices, también tenemos artículos adicionales para ti sobre fundamentos de indexación SQL y ¿Qué es un índice de base de datos?

Para nuestros propósitos, no nos preocuparemos por los detalles de cómo la base de datos construye un índice o su estructura de árbol B subyacente. En su lugar, nos centraremos en cómo la base de datos utiliza un índice. Baste decir que un índice de árbol B (árbol equilibrado) nos permite acceder a cualquier fila de la base de datos en el mismo tiempo.

La indexación de bases de datos acelera la recuperación de datos. Volvamos a nuestra analogía: es más fácil encontrar la página que hace referencia a Abraham Lincoln buscando "Lincoln, Abraham" en el índice del libro. (Compare esto con buscar en cada página una mención al Sr. Lincoln y se hará una idea). Utilizando un índice, encontrar la referencia a cualquier persona concreta en el libro llevaría el mismo tiempo.

Por otro lado, imagina que el libro no tiene índice y tienes que escanear cada página para encontrar un nombre. Localizar cualquier referencia concreta llevará una cantidad de tiempo desconocida y variable: las personas que aparecen en las primeras páginas se encontrarán más rápidamente que las que aparecen al final (a menos que escaneemos de atrás hacia delante).

La indexación SQL funciona del mismo modo. Se aplica un índice a una columna que facilita a la base de datos la obtención de información para una búsqueda.

¿Deberíamos crear índices para cada columna de una tabla? ¿No aceleraría esto la base de datos? No. Indexar demasiadas columnas afectará negativamente al rendimiento de la base de datos, haciendo que sea muy lento añadir y actualizar filas. La mejor práctica actual consiste en indexar únicamente las columnas que se utilizan con frecuencia para ordenar o clasificar datos.

Ejemplo 1 de indexación de bases de datos

Supongamos que necesita almacenar información sobre personas: su nombre, apellidos, calle, número, código postal, ciudad, país, número de teléfono y fecha de nacimiento.

Ahora, supongamos que tiene millones de registros almacenados en esta base de datos. ¿Es probable que realice una búsqueda (es decir, una consulta) en la base de datos basada en el apellido? Probablemente sí. ¿Consultaría la base de datos utilizando sólo el nombre? No, probablemente no; es más probable que la consulta se base en el nombre más el apellido. Pero tal vez quiera saber cuándo un nombre concreto fue el más popular en el conjunto de datos. En ese caso, se preguntaría "¿en qué año nació el mayor número de personas con este nombre de pila?".

Cada uno de estos casos requeriría un enfoque diferente de la indexación. Si buscamos sólo por apellido, crearíamos un índice en la columna last_name. Si buscamos por nombre y last , indexaríamos tanto la columna first_name como la columna last_name. En el tercer caso, crearíamos un índice sólo en la columna first_name.

En resumen, existen diferentes técnicas de indexación SQL. Aplicando estas diferentes técnicas, podemos conseguir un ajuste del rendimiento de SQL. La creación de índices depende de cómo se vayan a utilizar los datos o, en otras palabras, de cómo se vayan a consultar los datos.

Ejemplo nº 2 de indexación de bases de datos

Imaginemos que tenemos una tabla person con cuatro columnas: ssn (número de la seguridad social, que es similar a un número de identificación nacional), first_name, last_name y zip_code (código postal). Cuando tenemos millones de registros, ejecutar una consulta SQL para encontrar una fila basada en el apellido tarda casi un minuto - incluso para una tabla tan sencilla.

¿Te imaginas esperar un minuto a que la aplicación recupere la información que necesitas cada vez que la consultas? En este ejemplo, se tardaron 46 segundos en escanear 40 millones de registros. No había ningún índice en la columna last_name, por lo que el sistema de base de datos tenía que leer todos los registros de la tabla para comprobar qué registros correspondían a un apellido concreto. Necesitamos urgentemente acelerar esta consulta SQL.

En este caso, crearemos un índice en la columna apellido. Esto acelerará la consulta SQL en tres órdenes de magnitud (aproximadamente 3.000 veces más rápido) a 15 milisegundos: una enorme ganancia en el rendimiento de la consulta SQL.

Para indexar una base de datos, necesitamos definir:

1. El nombre del índice.
2. Qué columna(s) tendrá(n) el índice.
3. El nombre de la tabla que contiene estas columnas.

Recuerde que no debe crear índices para cada columna o combinación de columnas. Si lo hacemos, corremos el riesgo de matar la base de datos y crear una aplicación que no responda cuando los usuarios estén añadiendo o actualizando registros.

Veamos cómo hacerlo.

Creación de un índice SQL

La sintaxis para crear un índice es sencilla. Para crear un índice simple sobre una columna de la tabla, utilizamos la siguiente sentencia. En este caso, indexaremos la columna zip_code de nuestra tabla person tabla:

CREATE INDEX index_zip
ON person (zip_code);

No es complicado. Sin embargo, recuerde que cada índice debe actualizarse cuando se añaden nuevas filas y se modifican o eliminan filas existentes. Estas actualizaciones llevan tiempo; si utilizas demasiado los índices, éstos pueden hacer que tu base de datos y su aplicación se vuelvan más lentas o incluso demasiado lentas para los usuarios.

También puedes modificar los índices existentes o eliminarlos, pero esto queda fuera del ámbito de este artículo.

Existen diferentes tipos de índices:

• Los índices únicos mantienen la integridad de los datos definiendo que no puede haber dos filas en la tabla que tengan el mismo valor para el índice único.
• Los índicesprimarios son un tipo específico de índice único, pero sólo puede haber uno por tabla. El índice primario se crea cuando se crea la tabla. Como su nombre indica, se crea a partir de la clave primaria de la tabla.
• Los índicessecundarios son índices adicionales que se crean bajo demanda (mediante CREATE INDEX) y se pueden eliminar. Los índices secundarios pueden tener valores duplicados y no únicos (es decir, pueden tener más de una fila con el mismo valor).
• Los índicescompuestos (índices multicolumna) son índices que incluyen varias columnas.

Los índices son importantes, pero su uso excesivo es tan perjudicial como su uso insuficiente o su no utilización. Una estrategia de indexación bien implementada es clave. Puede evitar los índices innecesarios comprendiendo cómo se mantendrán los datos de sus tablas. Pero asegúrese de incluir índices importantes en función de cómo se accederá a sus datos y cómo se utilizarán.

Mejores prácticas de indexación SQL

Analicemos las mejores prácticas de indexación.

1. No indexe todas las tablas. Las tablas pequeñas no requieren índices, ya que un escaneo de la tabla será más eficiente que buscar en el índice y luego recuperar los datos de la tabla.
2. No indexestodas las columnas. Espero que esto resulte obvio a partir de los ejemplos anteriores. Indexar todas las columnas añade sobrecarga para mantener los índices actualizados y ralentiza otras operaciones de la base de datos. Indexe las columnas sobre las que filtre (es decir, las que utilice a menudo en las cláusulas WHERE).
3. No indexe columnas grandes. Un campo grande dentro de la tabla generará un índice grande.
4. Indexeclaves externas. Esto mejora el rendimiento de JOIN
5. Utilice índices multicolumna sólo cuando sea apropiado. Los índices multicolumna son excelentes. (Recuerde nuestro ejemplo de indexar tanto el nombre como el apellido para poder realizar consultas eficientes sobre esa combinación). Sin embargo, los índices compuestos son más complicados, ya que hay que tener en cuenta el orden de las columnas dentro del índice. ¿Debemos crear un índice sobre el nombre y el apellido o sobre el apellido y el nombre? Se trata de dos índices diferentes. ¿Cuál será más eficaz? La respuesta dependerá de las consultas SQL. Por lo general, un índice de una sola columna es suficiente (y ahorra tiempo).
6. Utilice los índices para preordenar los datos. La ordenación repetida de los datos puede evitarse si se añade un índice con el orden de ordenación (es decir, ascendente o descendente).
7. Asegúrese de que todo funciona como se espera. Use EXPLAIN PLAN para verificar que sus índices están ayudando.

Ahora que tenemos una estrategia para crear índices, consideremos cómo mantenerlos y ajustarlos. Necesitas monitorizar tus índices: comprueba las estadísticas de índice para entender el uso de los índices y comprueba el plan de consulta para analizar cómo tus consultas SQL están usando los índices.

El mantenimiento de índices SQL implica reorganizar o reconstruir índices. La reorganización de un índice es menos intensiva que una reconstrucción completa. Tanto la reorganización como la reconstrucción de un índice consumen muchos recursos y pueden o no mejorar el rendimiento. Por lo tanto, analice sus índices antes de mantenerlos. No asuma que el mantenimiento de un índice mejorará el rendimiento de la consulta SQL.

¿Qué sigue con la indexación SQL?

Ahora que hemos repasado qué son los índices y las mejores prácticas para crearlos y mantenerlos, el siguiente paso es profundizar en los índices. Nuestro curso Entendiendo los Índices explica cómo funcionan los índices y le guía a través de cómo y cuándo crear un índice. Sus ejercicios prácticos le ayudarán a comprender rápidamente lo que necesita para que sus consultas sean rápidas y eficaces. ¡Feliz aprendizaje!