Guía SQL: Cómo escribir mejor las consultas (Parte 2)

Continuación del artículo Guía de SQL: Cómo escribir mejor las consultas (Parte 1)

De solicitud a planes de ejecución

Saber que los antipatrones no son estáticos y evolucionan a medida que crece como desarrollador de SQL, y el hecho de que hay muchas cosas a tener en cuenta al pensar en alternativas también significa que evitar antipatrones y reescribir consultas puede ser bastante difícil tarea Cualquier ayuda puede ser útil, por lo que un enfoque más estructurado para la optimización de consultas utilizando algunas herramientas puede ser más efectivo.

También debe tenerse en cuenta que algunos de los antipatrones mencionados en la última sección tienen sus raíces en problemas de rendimiento, como los operadores AND , OR y NOT y su ausencia al usar índices. Pensar en el rendimiento requiere no solo un enfoque más estructurado, sino también más profundo.

Sin embargo, este enfoque estructurado y profundo se basará principalmente en el plan de consulta, que, como recordará, es el resultado de una consulta analizada primero en un "árbol de análisis" o "árbol de análisis" y determina exactamente qué algoritmo utilizado para cada operación y cómo se coordina su ejecución.

Optimización de consultas

Como leyó en la introducción, es posible que deba verificar y configurar planes que el optimizador compila manualmente. En tales casos, deberá analizar su solicitud nuevamente mirando el plan de solicitud.

Para acceder a este plan, debe utilizar las herramientas proporcionadas por el sistema de gestión de bases de datos. Las siguientes herramientas pueden estar a su disposición:

• Algunos paquetes contienen herramientas que generan una representación gráfica del plan de consulta. Considere el siguiente ejemplo:
  
  
  
• Otras herramientas proporcionarán una descripción textual del plan de consulta. Un ejemplo es la declaración EXPLAIN PLAN en Oracle, pero el nombre de la instrucción depende del DBMS con el que esté trabajando. En otro lugar puede encontrar EXPLAIN (MySQL, PostgreSQL) o EXPLAIN QUERY PLAN (SQLite).

Tenga en cuenta que cuando trabaje con PostgreSQL, puede hacer una distinción entre EXPLAIN , donde simplemente obtiene una descripción que le dice cómo el planificador intenta ejecutar la consulta sin ejecutarla, mientras que EXPLAIN ANALYZE realmente ejecuta la consulta y le devuelve el análisis. Planes de solicitud esperados y reales. En términos generales, un plan de ejecución real es un plan en el que una solicitud se ejecuta realmente, mientras que un plan de ejecución de evaluación determina lo que hará sin cumplir la solicitud. Aunque esto es lógicamente equivalente, el plan de ejecución real es mucho más útil ya que contiene información adicional y estadísticas sobre lo que realmente sucedió cuando se ejecutó la solicitud.

En el resto de esta sección, aprenderá más sobre EXPLAIN y ANALYZE , así como también cómo usarlos para obtener más información sobre el plan de consulta y su posible rendimiento. Para hacer esto, comience con algunos ejemplos en los que trabajará con dos tablas: one_million y half_million .

Puede obtener la información actual de la tabla one_million usando EXPLAIN ; Asegúrese de colocarlo directamente encima de la solicitud y, después de ejecutarlo, le devolverá el plan de consulta:

 EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18584.82 rows=1025082 width=36) (1 row) 

En este caso, verá que el costo de la solicitud es 0.00..18584.82 , y el número de filas es 1025082 . El ancho del número de columnas es 36 .

Además, puede actualizar las estadísticas usando ANALYZE .

 ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row) 

Además de EXPLAIN y ANALYZE , también puede obtener el tiempo de ejecución real con EXPLAIN ANALYZE :

 EXPLAIN ANALYZE SELECT * FROM one_million; QUERY PLAN ___________________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.015..1207.019 rows=1000000 loops=1) Total runtime: 2320.146 ms (2 rows) 

La desventaja de usar EXPLAIN ANALYZE es que la consulta se ejecuta realmente, ¡así que tenga cuidado con esto!

Hasta ahora, todos los algoritmos que ha visto son Seq Scan (Sequential Scan) o Full Table Scan: este es un escaneo realizado en una base de datos donde cada fila de la tabla escaneada se lee en orden serial y las columnas encontradas se verifican para cumplimiento de la condición o no. En términos de rendimiento, los escaneos secuenciales definitivamente no son el mejor plan de ejecución porque todavía está haciendo un escaneo completo de la tabla. Sin embargo, esto no es tan malo cuando la tabla no cabe en la memoria: las lecturas secuenciales son bastante rápidas incluso en discos lentos.

Aprenderá más sobre esto más adelante cuando hablemos sobre el escaneo de índice.

Sin embargo, hay otros algoritmos. Tome, por ejemplo, este plan de consulta para una conexión:

 EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN _________________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms 

¡Ves que el optimizador de consultas eligió Hash Join aquí! Recuerde esta operación, ya que la necesitará para evaluar la complejidad temporal de su solicitud. Por ahora, tenga en cuenta que no hay índice en half_million.counter , que agregamos en el siguiente ejemplo:

 CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ________________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows) 

Verá que al crear el índice, el optimizador de consultas ahora ha decidido utilizar la Merge join al escanear el Index Scan índice.

Observe la diferencia entre los escaneos de índice y los escaneos de tabla completa o los escaneos secuenciales: el primero, también llamado "escaneos de tabla", escanea los datos o las páginas de índice para encontrar los registros correspondientes, mientras que el segundo escanea cada fila de la tabla.

Verá que el tiempo de ejecución general ha disminuido y el rendimiento debería ser mejor, pero hay dos escaneos de índice, lo que hace que la memoria sea más importante aquí, especialmente si la tabla no encaja en ella. En tales casos, primero debe realizar una exploración de índice completa, que se realiza mediante lecturas secuenciales rápidas y no es un problema, pero luego tiene muchas operaciones de lectura aleatoria para seleccionar filas por valor de índice. Estas son operaciones de lectura aleatorias que suelen ser varios órdenes de magnitud más lentas que las secuenciales. En estos casos, un escaneo completo de la tabla es más rápido que un escaneo de índice completo.

Sugerencia: Si desea obtener más información sobre EXPLICAR o considerar ejemplos con más detalle, considere leer Explicación de comprensión de Guillaume Lelarge.

Complejidad del tiempo y Big O

Ahora que ha revisado brevemente el plan de consulta, puede comenzar a profundizar y pensar en el rendimiento en términos más formales utilizando la teoría de la complejidad computacional. Este es un campo de la informática teórica, que, entre otras cosas, se centra en la clasificación de problemas computacionales según su complejidad; Estos problemas computacionales pueden ser algoritmos, pero también consultas.

Sin embargo, para las consultas, no se clasifican necesariamente según su complejidad, sino que dependen del tiempo requerido para completarlas y obtener resultados. Esto se llama complejidad de tiempo, y puede usar la notación O grande para formular o medir este tipo de complejidad.

Con la designación O grande, expresa el tiempo de ejecución en términos de qué tan rápido crece en relación con la entrada, ya que la entrada se vuelve arbitrariamente grande. La notación O grande excluye los coeficientes y los miembros de un orden inferior, por lo que puede centrarse en la parte importante del tiempo de ejecución de su consulta: su tasa de crecimiento. Cuando se expresan de esta manera, descartando los coeficientes y términos de un orden inferior, dicen que la complejidad del tiempo se describe asintóticamente. Esto significa que el tamaño de entrada va al infinito.

En un lenguaje de base de datos, la complejidad determina cuánto tiempo lleva completar una consulta a medida que aumenta el tamaño de las tablas de datos y, por lo tanto, la base de datos crece.

Tenga en cuenta que el tamaño de su base de datos aumenta no solo por el aumento en la cantidad de datos en las tablas, sino que el hecho de que haya índices también juega un papel importante en el tamaño.

Estimando la complejidad de tiempo de su plan de consulta

Como viste anteriormente, el plan de ejecución, entre otras cosas, determina qué algoritmo se usa para cada operación, lo que te permite expresar lógicamente cada tiempo de ejecución de consulta como una función del tamaño de la tabla incluida en el plan de consulta, que se llama función de complejidad. En otras palabras, puede usar la notación O grande y el plan de ejecución para evaluar la complejidad y el rendimiento de la consulta.

En las siguientes secciones, obtendrá una visión general de los cuatro tipos de complejidad temporal, y verá algunos ejemplos de cómo la complejidad temporal de las consultas puede variar según el contexto en el que se ejecute.

Sugerencia: ¡los índices son parte de esta historia!

Sin embargo, debe tenerse en cuenta que hay diferentes tipos de índices, diferentes planes de ejecución y diferentes implementaciones para diferentes bases de datos, por lo que las dificultades temporales que se enumeran a continuación son muy generales y pueden variar según la configuración específica.

O (1): tiempo constante

Dicen que un algoritmo funciona en tiempo constante si necesita la misma cantidad de tiempo, independientemente del tamaño de los datos de entrada. Cuando se trata de una consulta, se ejecutará en tiempo constante si se requiere la misma cantidad de tiempo independientemente del tamaño de la tabla.

Este tipo de consulta no es realmente común, pero aquí hay un ejemplo:

 SELECT TOP 1 t.* FROM t 

La complejidad del tiempo es constante, ya que se selecciona una fila arbitraria de la tabla. Por lo tanto, el período de tiempo no debe depender del tamaño de la tabla.

Tiempo lineal: O (n)

Dicen que el algoritmo funciona en tiempo lineal, si su tiempo de ejecución es directamente proporcional al tamaño de los datos de entrada, es decir, el tiempo aumenta linealmente con el tamaño de los datos de entrada. Para las bases de datos, esto significa que el tiempo de ejecución será directamente proporcional al tamaño de la tabla: a medida que aumenta el número de filas en la tabla, aumenta el tiempo de ejecución de la consulta.

Un ejemplo es una consulta con una WHERE para una columna no indexada: se requerirá un escaneo completo de la tabla o un Seq Scan , lo que conducirá a una complejidad de tiempo O (n). Esto significa que cada línea debe leerse para encontrar la línea con el identificador (ID) deseado. No tiene ninguna restricción en absoluto, por lo que debe contar cada línea, incluso si la primera línea coincide con la condición.

Considere también el siguiente ejemplo de consulta, que tendrá complejidad O (n) si no hay índice en el campo i_id :

 SELECT i_id FROM item; 

• Lo anterior también significa que otras consultas, como consultas para calcular el número de filas COUNT (*) FROM TABLE; tendrá una complejidad de tiempo O (n) , ya que se requerirá un escaneo completo de la tabla porque el número total de filas no se ha guardado para la tabla. De lo contrario, la complejidad temporal sería similar a O (1) .
  

El tiempo de ejecución lineal está estrechamente relacionado con el tiempo de ejecución de los planes que tienen uniones de tabla. Aquí hay algunos ejemplos:

• La combinación hash tiene la complejidad esperada de O (M + N). El algoritmo clásico de combinación hash para unir internamente dos tablas primero prepara la tabla hash de la tabla más pequeña. Las entradas de la tabla hash consisten en un atributo de conexión y su cadena. Se accede a la tabla hash aplicando la función hash al atributo de conexión. Una vez que se crea la tabla hash, se escanea una tabla grande y las filas correspondientes de la tabla más pequeña se encuentran buscando en la tabla hash.
• Las combinaciones de combinación generalmente tienen complejidad O (M + N), pero dependerá en gran medida de los índices de la columna de combinación y, si no hay índice, si las filas se ordenan de acuerdo con las claves utilizadas en la combinación:
  • Si ambas tablas se ordenan según las claves utilizadas en la combinación, la consulta tendrá una complejidad temporal de O (M + N).
  • Si ambas tablas tienen un índice para columnas unidas, entonces el índice ya admite estas columnas en orden y no es necesario ordenarlas. La dificultad será O (M + N).
  • Si ninguna de las tablas tiene un índice en las columnas conectadas, primero debe ordenar ambas tablas, para que la complejidad se vea como O (M log M + N log N).
  • Si solo una de las tablas tiene un índice en las columnas conectadas, solo la tabla que no tiene un índice debe clasificarse antes de que ocurra el paso de unión, de modo que la complejidad se vea como O (M + N log N).
• Para combinaciones anidadas, la complejidad suele ser O (MN). Esta combinación es efectiva cuando una o ambas tablas son extremadamente pequeñas (por ejemplo, menos de 10 registros), lo cual es una situación muy común al evaluar consultas, ya que algunas subconsultas se escriben para devolver solo una fila.

Recuerde: una combinación anidada es una combinación que compara cada registro en una tabla con cada registro en otra.

Tiempo logarítmico: O (log (n))

Se dice que un algoritmo funciona en tiempo logarítmico si su tiempo de ejecución es proporcional al logaritmo del tamaño de entrada; Para las consultas, esto significa que se ejecutarán si el tiempo de ejecución es proporcional al logaritmo del tamaño de la base de datos.

Esta complejidad de tiempo logarítmico es válida para planes de consulta en los que se escanea un Index Scan o un índice agrupado. Un índice agrupado es un índice donde el nivel de índice final contiene las filas reales de la tabla. Un índice agrupado es similar a cualquier otro índice: se define en una o más columnas. Forman una clave de índice. La clave de agrupación son las columnas clave de un índice agrupado. Escanear un índice agrupado es básicamente la operación de leer su DBMS para una fila o filas de arriba a abajo en un índice agrupado.

Considere el siguiente ejemplo de consulta, donde hay un índice para i_id y que generalmente resulta en complejidad O (log (n)):

 SELECT i_stock FROM item WHERE i_id = N; 

Tenga en cuenta que sin un índice, la complejidad del tiempo sería O (n).

Tiempo cuadrático: O (n ^ 2)

Se cree que el algoritmo se ejecuta en tiempo cuadrático, si su tiempo de ejecución es proporcional al cuadrado del tamaño de entrada. Nuevamente, para las bases de datos, esto significa que el tiempo de ejecución de la consulta es proporcional al cuadrado del tamaño de la base de datos.

Un posible ejemplo de una consulta de complejidad de tiempo cuadrático es el siguiente:

 SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

La complejidad mínima puede ser O (n log (n)), pero la complejidad máxima puede ser O (n ^ 2) según la información de índice de los atributos de conexión.

Para resumir, también puede echar un vistazo a la siguiente hoja de trucos para evaluar el rendimiento de la consulta en función de su complejidad temporal y su efectividad:

Ajuste de SQL

Dado el plan de ejecución de la consulta y la complejidad del tiempo, puede personalizar aún más su consulta SQL. Puede comenzar centrándose en los siguientes puntos:

• Reemplace los escaneos innecesarios de tablas completas con escaneos de índice;
• Asegúrese de aplicar el orden de unión óptimo.
• Asegúrese de que los índices se utilicen de manera óptima. Y
• Se utiliza el almacenamiento en caché de escaneos de texto completo de tablas pequeñas (escaneos de tabla completa de caché de tabla pequeña).

Uso adicional de SQL

Felicidades Ha llegado al final de este artículo, que acaba de darle un pequeño vistazo al rendimiento de las consultas SQL. Espero que tenga más información sobre los antipatrones, el optimizador de consultas y las herramientas que puede utilizar para analizar, evaluar e interpretar la complejidad de su plan de consultas. Sin embargo, ¡todavía tienes mucho por descubrir! Si desea saber más, lea el libro "Sistemas de gestión de bases de datos" de R. Ramakrishnan y J. Gehrke.

Finalmente, no quiero negarle StackOverflow en esta cita:

Mi antipatrón favorito no verifica tus solicitudes.

Sin embargo, es aplicable cuando:

• Su consulta proporciona más de una tabla.
• Cree que tiene el diseño óptimo para la solicitud, pero no intente verificar sus suposiciones.
• Acepta la primera solicitud de trabajo, sin saber qué tan cerca está de lo óptimo.