Índices SQL explicados, parte. 1

Publicado: 2022-03-11

Usado apropiadamente, un índice de base de datos SQL puede ser tan efectivo que puede parecer mágico. Pero la siguiente serie de ejercicios mostrará que, en el fondo, la lógica de la mayoría de los índices SQL, y manejarlos correctamente, es bastante sencilla.

En esta serie, Explicación de los índices de SQL , analizaremos las motivaciones para usar índices para acceder a datos y para diseñar índices de la forma en que lo hacen todos los RDBMS modernos. Luego veremos los algoritmos utilizados para devolver datos para patrones de consulta específicos.

No tiene que saber mucho acerca de los índices para poder seguir los índices SQL explicados . Solo hay dos condiciones previas:

  • Conocimientos básicos de SQL
  • Conocimientos básicos de cualquier lenguaje de programación.

Los temas principales en los que se explicarán los índices SQL son:

  • Por qué necesitamos índices de bases de datos SQL; visualización de planes de ejecución mediante índices
  • Diseño de índices: qué índices hacen que una consulta sea rápida y eficiente
  • Cómo podemos escribir una consulta para usar índices de manera efectiva
  • El impacto del uso de índices en SQL en la eficiencia de lectura/escritura
  • Cubriendo índices
  • Particionamiento, su impacto en la lectura y la escritura, y cuándo usarlo

Este no es solo un tutorial de índice SQL, es una inmersión profunda en la comprensión de la mecánica subyacente de los índices.

Vamos a descubrir cómo un RDBMS usa índices haciendo ejercicios y analizando nuestros métodos de resolución de problemas. Nuestro material de ejercicios consta de Hojas de cálculo de Google de solo lectura. Para hacer un ejercicio, puede copiar la Hoja de Google ( Archivo → Hacer una copia ) o copiar su contenido en su propia Hoja de Google.

En cada ejercicio, mostraremos una consulta SQL que utiliza la sintaxis de Oracle. Para las fechas, utilizaremos el formato ISO 8601, YYYY-MM-DD .

Ejercicio 1: Todas las Reservas de un Cliente

La primera tarea, no la haga todavía, es encontrar todas las filas de la hoja de cálculo de Reserva para un cliente específico de un sistema de reservas de hotel y copiarlas en su propia hoja de cálculo, simulando la ejecución de la siguiente consulta:

 SELECT * FROM Reservations WHERE ClientID = 12;

Pero queremos seguir un método particular.

Enfoque 1: sin clasificación, sin filtrado

Para el primer intento, no utilice ninguna función de clasificación o filtrado. Por favor, registre el tiempo empleado. La hoja resultante debe contener 73 filas.

Este pseudocódigo ilustra el algoritmo para realizar la tarea sin clasificar:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

En este caso, tuvimos que verificar las 841 filas para regresar y copiar 73 filas que cumplieran la condición.

Enfoque 2: Ordenar solo

Para el segundo intento, ordene la hoja según el valor de la columna ClientID . No use filtros. Registre el tiempo y compárelo con el tiempo que tomó completar la tarea sin ordenar los datos.

Después de ordenar, el enfoque se ve así:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Esta vez, tuvimos que verificar "solo" 780 filas. Si de alguna manera pudiéramos saltar a la primera fila, tomaría aún menos tiempo.

Pero si tuviéramos que desarrollar un programa para la tarea, esta solución sería aún más lenta que la primera. Esto se debe a que primero tendríamos que ordenar todos los datos, lo que significa que se tendría que acceder a cada fila al menos una vez. Este enfoque es bueno solo si la hoja ya está ordenada en el orden deseado.

Ejercicio 2: El número de reservas a partir de una fecha determinada

Ahora la tarea es contar el número de registros el 16 de agosto de 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Use la hoja de cálculo del Ejercicio 1. Mida y compare el tiempo dedicado a completar la tarea con y sin clasificar. La cuenta correcta es 91.

Para el enfoque sin clasificación, el algoritmo es básicamente el mismo que el del Ejercicio 1.

El enfoque de clasificación también es similar al del ejercicio anterior. Simplemente dividiremos el bucle en dos partes:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Ejercicio 3: Investigación Criminal

El inspector de policía solicita ver una lista de huéspedes que llegaron al hotel los días 13 y 14 de agosto de 2020.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Enfoque 1: Ordenado solo por fecha

El inspector quiere la lista rápido. Ya sabemos que mejor ordenamos la tabla/hoja de cálculo según la fecha de llegada. Si acabamos de terminar el ejercicio 2, tenemos suerte de que la tabla ya esté ordenada. Entonces, aplicamos un enfoque similar al del Ejercicio 2.

Por favor, intente registrar el tiempo, la cantidad de filas que tuvo que leer y la cantidad de elementos en la lista.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Usando este enfoque, tuvimos que leer 511 filas para compilar una lista de 46 invitados. Si pudiéramos deslizarnos hacia abajo con precisión, en realidad no tendríamos que realizar 324 lecturas del ciclo de repetición solo para ubicar la primera llegada el 13 de agosto. Sin embargo, todavía tuvimos que leer más de 100 filas para verificar si el huésped llegó al hotel con un HotelID de 3 .

El inspector esperó todo ese tiempo pero no estaba feliz: en lugar de los nombres de los invitados y otros datos relevantes, solo entregamos una lista de identificaciones sin sentido.

Volveremos a ese aspecto más adelante en la serie. Primero encontremos una manera de preparar la lista más rápido.

Enfoque 2: Ordenado por hotel, luego por fecha

Para ordenar las filas según HotelID y luego DateFrom , podemos seleccionar todas las columnas, luego usar la opción de menú de Google Sheets Data → Sort range .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Tuvimos que saltarnos las primeras 338 llegadas antes de ubicar la primera en nuestro hotel. Después de eso, revisamos 103 llegadas anteriores para ubicar la primera el 13 de agosto. Finalmente, copiamos 46 valores consecutivos de ClientID . Nos ayudó que en el tercer paso pudimos copiar un bloque de identificaciones consecutivas. Lástima que de alguna manera no pudimos saltar a la primera fila desde ese bloque.

Enfoque 3: Ordenado solo por hotel

Ahora intente el mismo ejercicio utilizando la hoja de cálculo ordenada únicamente por HotelID .

El algoritmo aplicado a la tabla ordenada solo por HotelID es menos eficiente que cuando ordenamos por HotelID y DateFrom (en ese orden):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

En este caso, tenemos que leer las 166 llegadas al hotel con un HotelID de 3 , y para cada una, verificar si DateFrom pertenece al intervalo solicitado.

Enfoque 4: ordenado por fecha, luego hotel

¿Realmente importa si ordenamos primero por HotelID y luego por DateFrom o viceversa? Averigüemos: intente ordenar primero por DateFrom , luego por HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Ubicamos la primera fila con la fecha correspondiente, luego seguimos leyendo hasta ubicar la primera llegada al hotel. Después de eso, durante varias filas, se cumplieron ambas condiciones, la fecha correcta y el hotel correcto. Sin embargo, después de las llegadas al Hotel 3, tuvimos llegadas a los hoteles 4, 5 y así sucesivamente, para la misma fecha. Después de ellos, tuvimos que volver a leer filas para el día siguiente para los hoteles 1 y 2, hasta que pudimos leer llegadas consecutivas a nuestro hotel de interés.

Ilustración del diseño de datos utilizando diferentes enfoques de clasificación, como se describe más adelante en el texto del artículo.

Como podemos ver, todos los enfoques tienen un solo bloque de datos consecutivos en medio del conjunto completo de filas, que representan datos parcialmente coincidentes. Los enfoques 2 y 4 son los únicos en los que la lógica nos permite detener el algoritmo por completo antes de llegar al final de las coincidencias parciales.

El enfoque 4 tiene datos totalmente coincidentes en dos bloques, pero el enfoque 2 es el único en el que los datos objetivo están todos en un bloque consecutivo.

Enfoque 1 Enfoque 2 Enfoque 3 Enfoque 4
Filas saltables iniciales 324 338 + 103 = 441 342 324
Filas candidatas a examinar 188 46 166 159
Filas saltables después de que el algoritmo se detenga 328 353 332 357
Total de filas saltables 652 794 674 681

Por los números, está claro que el Enfoque 2 tiene la mayor cantidad de ventajas en este caso.

Explicación de los índices SQL: conclusiones y lo que sigue

Al hacer estos ejercicios deberían quedar claros los siguientes puntos:

  1. La lectura de una tabla correctamente ordenada es más rápida.
  2. Si una tabla aún no está ordenada, la ordenación lleva más tiempo que la lectura de una tabla no ordenada.
  3. Encontrar una manera de saltar a la primera fila que coincida con una condición de búsqueda dentro de la tabla ordenada ahorraría muchas lecturas.
  4. Sería útil tener una mesa ordenada por adelantado.
  5. Sería útil mantener las copias ordenadas de la tabla para las consultas más frecuentes.

Ahora, una copia ordenada de una tabla suena casi como un índice de base de datos. El siguiente artículo de SQL Indexes Explained cubre una implementación de índice rudimentaria. ¡Gracias por leer!