La función de ventana LAG me ha ayudado recientemente a solucionar un problema. Y no la conocía antes, así que me ha parecido conveniente compartir lo aprendido. Siempre me creo que conozco SQL y todos los años sistemáticamente descubro algo nuevo que antes no sabía. Creo que muchos desarrolladores compartimos un sesgo cognitivo: nos creemos que dominamos el suficiente SQL, pero me temo estamos equivocados.
Índice de contenidos
- 1. Introducción
- 2. Pero, ¿cómo pueden ser redundantes?
- 3. Detección de tuplas redundantes
- 4. La función de ventana LAG()
- 5. La función de ventana LEAD()
- 6. Limpieza de los redundantes
- 7. Reorganización
- 8. Nunca se sabe el suficiente SQL
- 9. Agradecimientos
- 10. Conclusiones
- 11. Referencias
1. Introducción.
Tengo mucha confianza en mis capacidades. Y me creo de verdad que domino SQL, pero la realidad viene una vez tras otra a darme un tirón de orejas y me pone en evidencia.
Una cosa que he descubierto en los últimos años es que me encanta optimizar, y me he ido especializando en el extraño arte de obtener el máximo rendimiento al menor coste. Ya sea desde una query de SQL, a optimizar un proceso complejo. Estos problemas, que con frecuencia acaban encima de mi mesa, los disfruto enormemente. Requieren encontrar la solución óptima a un problema complejo.
En este contexto, me llegó un problema hace poco, y es que un proceso no daba el rendimiento esperado. Se iba degradando. De hecho, era de los pocos microservicios que en lugar de escalar por concurrencia, escalaba por CPU. Eso ya indica un problema en el proceso. Y era de los primeros que desarrollamos hace algunos años.
Estudié el problema, pedí los datos, y me repliqué la BBDD en local. Descubrí que el ¡¡¡98% de los datos eran redundantes!!!.
2. Pero, ¿cómo pueden ser redundantes?
En un sistema orientado a eventos es muy sencillo. Si la tabla tienen claves generadas mediante una secuencia o un autoincremental, siempre que te llegue un evento se inserta una nueva tupla, que en esencia, puede ser exacatamente igual a la vigente actualmente.
Lo mismo puede pasar si la PK se trata de una clave compuesta por varias columnas. Si una de ellas es el dateTime en la que llega el evento, diferirá de la anterior por esta columna.
Eso pasa mucho con eventos que representan entidades maestras, cuando hay varios microservicios suscritos a estos eventos, pero nuestro micro sólo está interesado en ciertas propiedades que caen dentro del ámbito de su responsabilidad. El caso es que esa entidad se emite cuando cambia cualquiera de sus otras propiedades, el evento es de tipo Carrier State Transfer, y nuestro micro recibe el evento, pero las propiedades que interesan a nuestro micro NO cambian… de hecho siguen siendo exactamente iguales a las vigentes… No deberíamos actualizar ni insertar tupla nueva, ya que sería redundante.
Si esto no se tiene en cuenta, nos podemos encontrar con que el 98% de nuestra tabla sea redundante. Hay que corregir el error de desarrollo, y que si llegan datos igual a los vigentes NO los persista, pero ¿y qué hacemos con los datos ya persistidos?
3. Detección de tuplas redundantes
Nos encontramos con una situación como la siguiente.
Que tenemos una serie de filas
La PK es (a, b, event_date), pero C es igual para (a, b) en muchos casos…
Pero si nos fijamos, hay una serie de filas que son redundantes. Las que son en verde…
En mi caso en concreto representaba el 98% de las tuplas. Y es un número muy alto Pero, ¿cómo podemos detectar las tuplas redundantes con SQL?. Y peor aún, ¿cómo las limpiamos?
Las filas redundantes son aquellas que siendo la fila anterior a y b iguales, c es también igual. Del ejemplo anterior sólo las filas 1,6 y 7 son las vigentes. Las demás, sobran.
4. La función de ventana LAG()
LAG() es una función de ventana que nos dice exactamente los valores la fila anterior según una función de ventana. Es muy, muy interesante
Con la consulta
select a, b, event_date, c, lag(a) over (partition by a, b order by event_date) as prev_a, lag(b) over (partition by a, b order by event_date) as prev_b, lag(c) over (partition by a, b order by event_date) as prev_c from example
Obtenemos los valores de las columnas que nos interesan y los valores previos,
Pero sabemos que los valores redundantes son los que la tupla original es igual a los valores previos.
Luego las tuplas redundantes son:
SELECT * FROM ( select a, b, event_date, c, lag(a) over (partition by a, b order by event_date) as prev_a, lag(b) over (partition by a, b order by event_date) as prev_b, lag(c) over (partition by a, b order by event_date) as prev_c from example ) k WHERE a = prev_a AND b = prev_b AND c = prev_c
Exactamente 15 tuplas de las 18… Es correcto, ya que sólo hay 3 vigentes…
5. La función de ventana LEAD()
Mientras que LAG() obtiene el valor previo de la función de ventana, LEAD() obtiene el siguiente.
Ambas son funciones muy interesantes.
Aunque en este caso nos vamos a centrar en la función de ventana LAG()
6. Limpieza de los redundantes
¿Y como limpiamos los valores redundantes?
Habrá que obtener la PK de las tuplas redundantes y borrar por la PK. Recordemos que la PK es una clave compuesta fruto de la combinación de las columnas a, b y event_date.
DELETE FROM example WHERE (a, b, event_date) IN ( SELECT a, b, event_date FROM ( select a, b, event_date, c, lag(a) over (partition by a, b order by event_date) as prev_a, lag(b) over (partition by a, b order by event_date) as prev_b, lag(c) over (partition by a, b order by event_date) as prev_c from example ) k WHERE a = prev_a AND b = prev_b AND c = prev_c )
Quedando como tuplas vigentes sólo las tres que habíamos indicado:
7. Reorganización
Pensemos ahora en lo que hemos hecho. Hemos borrado millones de tuplas redundantes de una tabla. Imaginemos el espacio que ocupaba esa tabla en el disco duro como un rectándulo, y ahora pensemos en las tuplas que hemos borrado como huecos en ese rectángulo. En el caso que yo me encontré, quedaron muchos más huecos que tuplas útiles.
Hemos dejado la tabla físicamente como un queso de Gruyere. Llena de «agujeros».
Una de las operaciones más costosas en BBDD es ir a disco a por datos. Las operaciones de entrada salida tienen un coste. Cuando esos datos están muy dispersos sobre el disco duro, tiene mayor coste. Normalmente, cuando acceden a un dato para llevárselo a una página de la memoria, llenan la página entera, suponiendo que otros datos adyacentes le serán de interés.
Luego debemos compactar la información quitando los huecos. Eso en Db2 se hace con el comando reorg y en MySQL con optimize. Sospecho que cada gestor tendrá su comando.
Por otro lado ¿nos vale sólo con compactar? Los índices siempre están ordenados, mediante un árbol B, y son los que usaremos para cruzar en nuestras queries y para localizar las tuplas en las tablas mediante su rowId, pero si la tabla está ordenada respecto a su PK, la operación de «fetch» será mucho más eficiente. En algunos gestores, como Db2, hay un parámetro llamado clusterratio que mide en porcentaje cómo de ordenada está la tabla respecto de un índice. La tabla no puede estar ordenada igual de bien respecto de todos sus índices, pero al menos, la PK sí debería.
Esto no es algo que un desarrollador deba tener en cuenta, pero el conocimiento no suele hacer daño.
8. Nunca se sabe el suficiente SQL
Los desarrolladores abusamos de JPA. Es fácil. Pero no nos da el control sobre la capa de datos. Y creo que es un error que los desarrolladores nos mantengamos agnósticos al acceso a los datos. Debemos optimizar las consultas y saber hacer e interpretar los explain plans. Debemos saber manejar los datos que tenemos entre manos. En este sentido creo que MyBatis es nuestro amigo, y nos permite acceder mediante una serie de consultas que hemos optimizado estudiando sus explain plans. Es nuestra responsabilidad y debemos involucrarnos.
9. Adradecimientos
El ecosistema al que pertenecemos nos hace crecer. Yo bebo muchas cosas de la gente que ha tenido a bien enseñarme. Así agradeceré siempre su buena disposición y paciencia a Javier Alcolea, que me ha enseñado de BBDD lo que no está escrito.
Carlos Guzmán me puso sobre la pista del LAG() y LEAD() y de él sigo aprendiendo cada día.
¡Qué bien me lo he pasado aprendiendo de ellos!
10. Conclusiones
Los DBAs saben mucho de BBDD, pero los desarrolladores deben responsabilizarse de sus consultas, y para ello deben conocer el modelo. No deben incurrir en estos errores de rendimiento de tener el 98% de las tuplas redundantes. Hay que entender lo que se tiene entre manos, y se debería conocer SQL y saber hacer explain plans. El rendimiento de la aplicación depende de ello.
