Geniales optimizaciones de SQL que no dependen del modelo de costos. Parte 1

Cinco optimizaciones simples que se pueden implementar basándose únicamente en metadatos (es decir, restricciones) y la consulta misma. Le ofrecemos una adaptación del artículo de Lukas Eder, diseñada para aquellos que tienen conocimientos generales de bases de datos y SQL, así como cierta experiencia práctica con DBMS. . La optimización de costos es en realidad una forma estándar de optimizar las consultas SQL en bases de datos modernas. Por eso es tan difícil escribir manualmente un algoritmo complejo en 3GL (lenguajes de programación de tercera generación) cuyo rendimiento superaría el plan de ejecución calculado dinámicamente y generado por un optimizador moderno. Hoy no discutiremos la optimización de costos, es decir, la optimización basada en el modelo de costos de la base de datos. Veremos optimizaciones mucho más simples. Aquellos que se pueden implementar basándose únicamente en metadatos (es decir, restricciones) y la solicitud en sí. Normalmente su implementación para una base de datos no es un binomio de Newton, ya que, en este caso, cualquier optimización conducirá a un mejor plan de ejecución, independientemente de la presencia de índices, volúmenes de datos y asimetría en la distribución de los datos. "No es un binomio de Newton" no se refiere a lo fácil que es implementar la optimización, sino a si se debe hacer o no. Estas optimizaciones eliminan el trabajo adicional innecesario [para la base de datos] ( a diferencia del trabajo requerido e innecesario, sobre el cual ya escribí ).

¿Para qué se utilizan estas optimizaciones?

La mayoría de ellos se utilizan para:

• correcciones de errores en consultas;
• permitiendo que las vistas se reutilicen sin que la base de datos ejecute realmente la lógica de la vista.

En el primer caso, se podría decir: "Y qué, simplemente sigue adelante y soluciona esta estúpida consulta SQL". Pero que me tire primero la piedra el que nunca se ha equivocado. El segundo caso es especialmente interesante: nos brinda la capacidad de crear bibliotecas complejas de vistas y funciones de tablas que se pueden reutilizar en múltiples capas.

Bases de datos utilizadas

En este artículo compararemos 10 optimizaciones de SQL en los cinco DBMS más utilizados ( según la clasificación de las bases de datos ):

• Oráculo 12.2;
• MySQL 8.0.2;
• Servidor SQL 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Otra calificación casi se hace eco de ello. Como es habitual, en este artículo consultaré la base de datos de Sakila . Aquí hay una lista de estos diez tipos de optimizaciones:

1. clausura transitiva;
2. predicados imposibles y llamadas a tablas innecesarias;
3. eliminando UNIRSE;
4. eliminación de predicados "sin sentido";
5. proyecciones en subconsultas EXISTS;
6. fusión de predicados;
7. conjuntos demostrablemente vacíos;
8. restricciones VERIFICAR;
9. conexiones reflexivas innecesarias;
10. Predicados pushdown

Hoy discutiremos las pp. 1-3, en la segunda parte - 4 y 5, y en la parte 3 - 6-10.

1. Cierre transitivo

Comencemos con algo más simple: cierre transitivo . Este es un concepto trivial que se aplica a muchas operaciones matemáticas, como el operador de igualdad. Se puede formular en este caso de la siguiente manera: si A = B y B = C, entonces A = C.

No es difícil, ¿verdad? Pero esto tiene algunas implicaciones interesantes para los optimizadores de SQL. Veamos un ejemplo. Extraigamos todas las películas con ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

El resultado es el siguiente:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

Veamos ahora el plan para ejecutar esta consulta en el caso del DBMS de Oracle:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

La sección sobre predicados es aquí especialmente interesante. El predicado ACTOR_ID = 1, debido al cierre transitivo, se aplica tanto a la tabla ACTOR como a la tabla FILM_ACTOR. Si:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

Para consultas más complejas, esto produce muy buenos resultados. En particular, la precisión de las estimaciones de cardinalidad aumenta significativamente, ya que es posible seleccionar estimaciones basadas en un valor constante específico del predicado, y no, por ejemplo, en el número promedio de películas por actores, como en la siguiente consulta (devolviendo el mismo resultado):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

Su plan:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

Como puede ver, el número de filas en la tabla FILM_ACTOR está sobreestimado, mientras que el NESTED LOOP está subestimado. Aquí hay un par de valores interesantes:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Resultado:

19
27.315

De aquí provienen las estimaciones. Si la base de datos sabe que estamos hablando de ACTOR_ID = 1, entonces puede recopilar estadísticas sobre la cantidad de películas de este actor en particular . Si no es así (dado que el mecanismo estándar de recopilación de estadísticas no correlaciona FIRST_NAME/LAST_NAME con ACTOR_ID), obtendremos el número promedio de películas para todos los actores . Un error simple y sin importancia en este caso particular, pero en una consulta compleja puede propagarse más, acumularse y conducir más adelante en la consulta (más arriba en el plan) a una opción JOIN incorrecta. Entonces, siempre que puedas, diseña tus uniones y predicados simples para aprovechar el cierre transitivo. ¿Qué otras bases de datos admiten esta función?

DB2

¡Sí!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

Por cierto, si te gustan los planes de ejecución interesantes como este, consulta el script de Markus Winand .

mysql

Desafortunadamente, los planes de ejecución de MySQL no son adecuados para este tipo de análisis. El predicado en sí no aparece en la información de salida:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

Pero el hecho de que const se especifique dos veces en la columna REF muestra que ambas tablas buscan un valor constante. Al mismo tiempo, el plan de consulta con FIRST_NAME/LAST_NAME tiene este aspecto:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

Y como puede ver, REF ahora hace referencia a la columna del predicado JOIN. La puntuación de cardinalidad es casi la misma que en Oracle. Entonces sí, MySQL también admite cierres transitivos.

PostgreSQL

¡Sí!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

servidor SQL

¡Sí!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

Resumen

Todas nuestras bases de datos admiten el cierre transitivo. Sin embargo, espere al punto 6 en la siguiente parte del artículo. Existen casos complejos de cierre transitivo que no todas las bases de datos pueden manejar.

2. Predicados imposibles y llamadas a tablas innecesarias

Esta es una optimización completamente estúpida, pero ¿por qué no? Si los usuarios escriben predicados imposibles, ¿por qué molestarse en ejecutarlos? Aquí hay unos ejemplos:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

Obviamente, la primera consulta nunca arrojará ningún resultado, pero la misma afirmación es válida para la segunda. Después de todo, aunque NULL IS NULL siempre es VERDADERO, el resultado del cálculo NULL = NULL es NULL, que, según la lógica de tres valores , equivale a FALSO. Esto se explica por sí mismo, así que pasemos directamente a descubrir qué bases de datos realizan esta optimización.

DB2

¡Sí!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Como puede ver, el acceso a la tabla ACTOR está completamente excluido del plan. Contiene sólo la operación GENROW, que genera cero filas. Perfecto.

mysql

¡Sí!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

Esta vez, MySQL tuvo la amabilidad de informarnos sobre la imposible cláusula WHERE. ¡Gracias! Esto facilita mucho el análisis, especialmente en comparación con otras bases de datos.

Oráculo

¡Sí!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Vemos que el plan todavía menciona el acceso a la tabla ACTOR, y el número esperado de filas sigue siendo 200, pero también hay una operación de filtrado (FILTRO) con Id=1, donde nunca será VERDADERO. Debido a la aversión de Oracle por el tipo de datos booleano SQL estándar , Oracle muestra NULL IS NOT NULL en el plan, en lugar de solo FALSE. Oh, bueno... Pero en serio, cuidado con ese predicado. Tuve la oportunidad de depurar planes de ejecución con subárboles de 1000 líneas y valores de costo extremadamente altos, solo para descubrir después que el filtro NULL IS NOT NULL estaba "cortando" todo el subárbol. Un poco desalentador, te lo digo.

PostgreSQL

¡Sí!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

Ya mejor. Sin molestas llamadas a la mesa ACTOR y un pequeño predicado FALSO.

¿Servidor SQL?

¡Sí!

|--Constant Scan

SQL Server llama a esto un " escaneo constante ", que es un escaneo en el que no sucede nada, similar a DB2. Todas nuestras bases de datos pueden excluir predicados imposibles:

3. Eliminar las UNIONES

En la sección anterior, observamos accesos innecesarios a tablas en consultas de una sola tabla. Pero, ¿qué sucede si JOIN no requiere uno de varios accesos a la tabla? Ya escribí sobre la eliminación de JOIN en una publicación anterior de mi blog . El motor SQL es capaz de determinar, según el tipo de consulta y la presencia de claves primarias y externas, si un JOIN particular es realmente necesario en una consulta determinada, o si eliminarlo no afectará la semántica de la consulta. En los siguientes tres ejemplos, JOIN no es necesario. Una unión interna... a uno se puede eliminar teniendo una clave externa NOT NULL. En su lugar:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

La base de datos puede hacer lo siguiente:

SELECT first_name, last_name
FROM customer c

Se puede reemplazar una INNER JOIN de tipo "...-to-one" si hay una clave externa que acepta valores NULL. La consulta anterior funciona si la clave externa está sujeta a una restricción NOT NULL. Si no, por ejemplo, como en esta solicitud:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

entonces JOIN aún se puede eliminar, pero tendrás que agregar el predicado NOT NULL, así:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Una UNIÓN EXTERNA del tipo "...-a-uno" se puede eliminar si hay una clave única. En lugar de esto:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

La base de datos, nuevamente, puede hacer lo siguiente:

SELECT first_name, last_name
FROM customer c

... incluso si no hay una clave externa para CUSTOMER.ADDRESS_ID. La conexión externa única (DISTINCT OUTER JOIN) del tipo "...-a-muchos" se puede eliminar. En lugar de esto:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

La base de datos puede hacer lo siguiente:

SELECT DISTINCT first_name, last_name
FROM actor a

Todos estos ejemplos fueron estudiados en detalle en el artículo anterior, por lo que no me repetiré, solo resumiré todo lo que varias bases de datos pueden eliminar: Desafortunadamente, no todas las bases de datos pueden resolver todos los tipos de conexiones. ¡DB2 y SQL Server son los líderes indiscutibles aquí! Continuará