Geniales optimizaciones de SQL que no dependen del modelo de costos. parte 5

Geniales optimizaciones de SQL que no dependen del modelo de costos. Parte 1 Optimizaciones geniales de SQL que no dependen del modelo de costos. Parte 2 Optimizaciones geniales de SQL que no dependen del modelo de costos. Parte 3 Optimizaciones geniales de SQL que no dependen del modelo de costos. parte 4

10. Impulsar predicados

Esta optimización no es del todo apropiada en este caso, porque no se puede decir que no se base en absoluto en el modelo de costes. Pero como no puedo pensar en una sola razón por la cual el optimizador no debería insertar predicados en tablas derivadas, la enumeraré aquí, junto con el resto de las optimizaciones sin costo. Considere la solicitud:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

La tabla derivada en esta consulta no tiene ningún sentido y debe eliminarse reduciendo el número de niveles de anidamiento de consultas. Pero ignoremos eso por ahora. Puede esperar que la base de datos ejecute la siguiente consulta en lugar de la anterior:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

Y luego, nuevamente, tal vez eliminar la solicitud externa. Un ejemplo más complejo se obtiene usando UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

El resultado de esta consulta:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Ahora, sería fantástico si el optimizador de la base de datos ejecutara una consulta como esta:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Es decir, para que inserte el predicado en la tabla derivada, y desde allí en dos subconsultas UNION ALL , ya que, después de todo, tenemos un índice tanto en la columna ACTOR.LAST_NAME como en la columna CUSTOMER.LAST_NAME . Nuevamente, esta conversión probablemente se base en estimaciones de costos en la mayoría de las bases de datos, pero sigo pensando que es una obviedad ya que, con cualquier algoritmo, casi siempre es mejor reducir la cantidad de tuplas procesadas lo antes posible. Si conoce algún caso en el que dicha transformación resulte una mala idea, ¡estaré encantado de escuchar sus comentarios! Me interesaría mucho. Entonces, ¿cuál de nuestras bases de datos puede hacer esto? (Y por favor, es tan simple y tan importante, que la respuesta sea: todo)

DB2

Tabla derivada simple Sí

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Tabla derivada con UNION También si:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Además, en ambos casos, la tabla derivada (vista) se excluyó del plan porque en realidad no era necesaria.

mysql

Tabla derivada simple Sí

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Se utiliza el acceso habitual a la clave principal por valor constante. Tabla derivada con UNION Vaya, no.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Resultados de la conversión manual en el plan:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

¡Este es un problema grave cuando se utilizan consultas anidadas complejas en MySQL!

Oráculo

Tabla derivada simple Sí, funciona.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

Y se ha reducido el número de niveles de anidación. La tabla derivada con UNION también funciona:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Sin embargo, sin reducir el número de niveles de anidación. Id=1 "Ver" muestra que la tabla derivada todavía está allí. En este caso no supone un gran problema, sólo quizás un pequeño coste adicional.

PostgreSQL

Tabla derivada simple Sí, funciona:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Sin embargo, tenga en cuenta que PostgreSQL a veces ni siquiera usa la clave principal para buscar una sola fila, sino que escanea toda la tabla. En este caso, 200 filas × 25 bytes por fila ("ancho") caben en un bloque, entonces, ¿cuál es el punto de molestarse con las lecturas de índice además de generar operaciones de E/S innecesarias para acceder a una tabla tan pequeña? Tabla derivada con UNION Sí, también funciona:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Nuevamente, no se usa un índice en la columna ACTOR.LAST_NAME , pero se usa un índice en la columna CUSTOMER.LAST_NAME porque la tabla CLIENTE es mucho más grande.

servidor SQL

Tabla derivada simple Sí, funciona

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

La tabla derivada con UNION también funciona.

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Resumen

Mis esperanzas no se hicieron realidad. MySQL 8.0.2 aún no es totalmente compatible con esta simple optimización. Todos los demás, sin embargo, lo apoyan.

Conclusión

La lista presentada aquí está lejos de ser completa. Hay muchas otras transformaciones SQL simples que no son (o no deberían ser) difíciles de implementar para las bases de datos, incluso antes de que intervenga un optimizador de costos. Eliminan el trabajo adicional innecesario [para la base de datos] ( a diferencia del trabajo requerido e innecesario , sobre el cual ya escribí ). Estas son herramientas importantes para:

1. Los errores estúpidos [del desarrollador] no tuvieron ningún impacto en el rendimiento. Los errores son inevitables y, a medida que un proyecto crece y las consultas SQL se vuelven más complejas, estos errores pueden acumularse, con suerte sin ningún efecto.


2. Proporciona la capacidad de reutilizar bloques complejos, como vistas y funciones de tabla, que pueden incrustarse en consultas SQL principales, transformarse, eliminarse o reescribirse parcialmente.

Estas capacidades son fundamentales para el punto 2. Sin ellos, sería muy difícil crear consultas SQL de 4000 filas con un rendimiento normal basadas en una biblioteca de componentes SQL reutilizables. Para decepción de los usuarios de PostgreSQL y MySQL, estas dos populares bases de datos de código abierto todavía tienen un largo camino por recorrer en comparación con sus competidores comerciales DB2, Oracle y SQL Server, de los cuales DB2 ha tenido el mejor desempeño, con Oracle y SQL Server prácticamente empatados. cuello un poco atrás.