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

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

8. VERIFICAR restricciones

¡Oh, esto es algo genial! Nuestra base de datos Sakila tiene una restricción CHECK en la columna FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

En serio, utilice restricciones CHECK para garantizar la integridad de los datos. El costo de agregarlas es extremadamente bajo, mucho menor que otras restricciones, por ejemplo, PRIMARY , UNIQUE o FOREIGN KEY , porque no requieren un índice para funcionar, por lo que las obtienes prácticamente "gratis". ¡Pero hay un matiz interesante relacionado con la optimización! Considere las siguientes consultas:

Predicados imposibles

Ya hemos encontrado predicados imposibles , incluso restricciones NOT NULL (que en realidad son un tipo especial de restricción CHECK ), pero ésta es aún más interesante:

SELECT *
FROM film
WHERE rating = 'N/A';

No existe tal película, y no puede existir, ya que la restricción CHECK impide su inserción (o actualización). Nuevamente, esto debería traducirse en un comando para no hacer nada. ¿Qué pasa con esta solicitud?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Gracias al índice anterior, probablemente sea suficiente con hacer un escaneo rápido del índice y contar todas las películas con clasificación = 'NC-17' , ya que esa es la única clasificación que queda. Entonces la consulta debería reescribirse así:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Este debería ser el caso independientemente del índice, porque comparar una columna con un valor es más rápido que comparar con 4. Entonces, ¿qué bases de datos pueden hacer esto?

DB2

Predicado imposible (calificación = 'N/A') ¡Genial!

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

Predicate Information
 2 - RESID (1 = 0)

Predicado inverso (calificación = 'NC-17') No...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Aunque el paso ID=3 utiliza un índice y aunque las cardinalidades son correctas, se produce un escaneo completo porque el plan no tiene un predicado de rango, solo el predicado "SARG". Consulte la reseña de Marcus Wynand para obtener más detalles . También puedes demostrar esto invirtiendo manualmente el predicado y obteniendo:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Ahora tenemos el predicado de rango deseado.

mysql

MySQL admite la sintaxis de restricciones CHECK , pero por alguna razón no las aplica. Prueba esto:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

y obtendrás:

A
-
0

Puntos cero para MySQL (en realidad, ¿por qué no simplemente admitir restricciones CHECK ?)

Oráculo

Predicado imposible (calificación = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Nuevamente, un filtro muy extraño NULL IS NOT NULL , que corta FULL TABLE SCAN , que fácilmente podría eliminarse del plan por completo. ¡Pero al menos funciona! Predicado inverso (calificación = 'NC-17') Vaya:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

El predicado no se puede invertir, la evaluación de cardinalidad es muy poco convincente, además obtenemos INDEX FAST FULL SCAN en lugar de INDEX RANGE SCAN y el predicado de filtro en lugar del predicado de acceso . Pero esto es lo que deberíamos obtener, por ejemplo, invirtiendo manualmente el predicado:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

¡Gorrón!

PostgreSQL

Tenga en cuenta que la versión PostgreSQL de la base de datos Sakila utiliza el tipo ENUM en lugar de restricciones CHECK en la columna RATING . En su lugar, dupliqué la tabla usando una restricción CHECK . Predicado imposible (calificación = 'N/A') No funciona:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

El predicado inverso (rating = 'NC-17') tampoco funciona:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

¡Lo sentimos mucho! Nota: Como David Rowley nos señaló amablemente en los comentarios , esta función se puede habilitar configurando el parámetro:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Predicado inverso (calificación = 'NC-17') ¡ Sí también!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Resumen

9. Conexiones reflexivas innecesarias.

A medida que sus consultas se vuelven más complejas, es posible que acabe necesitando realizar una unión reflexiva en una tabla en función de su clave principal. Créame, esta es una práctica muy común al crear vistas complejas y conectarlas entre sí, por lo que asegurarse de que la base de datos preste atención a esto es una parte fundamental de la optimización del código SQL complejo. No mostraré un ejemplo complejo, bastará con uno simple, por ejemplo:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Esto puede verse como un caso especial de eliminación de JOIN , ya que en realidad no necesitamos una unión con A2 , podemos hacer todo lo que necesitamos solo con la tabla A1 . A continuación, la eliminación de INNER JOIN solo funciona correctamente si hay una CLAVE EXTRANJERA , que no tenemos aquí. Pero gracias a la clave principal de ACTOR_ID , podemos demostrar que, de hecho, A1 = A2 . En cierto sentido, esto es nuevamente un cierre transitivo . Puede ir aún más lejos y utilizar columnas de las tablas A1 y A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

En el clásico caso de eliminación de JOIN , ya no sería posible eliminarlo ya que ambas tablas están proyectadas. Pero como ya hemos demostrado que A1 = A2 , entonces son intercambiables, por lo que podemos esperar que la consulta se convierta en:

SELECT first_name, last_name
FROM actor;

¿Qué DBMS puede hacer esto?

DB2

Proyección de la tabla A1 únicamente Sí:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Proyección de las tablas A1 y A2 ... también sí:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

mysql

Proyección de la tabla A1 únicamente No.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Proyección de las tablas A1 y A2 ... tampoco

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Completa decepción...

Oráculo

Proyección de la tabla A1 únicamente Sí

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Proyección de las tablas A1 y A2 Sí nuevamente

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Proyección del cuadro A1 únicamente No:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Proyección de las tablas A1 y A2 Y nuevamente no:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

servidor SQL

Proyección de la tabla A1 únicamente. ¡ Curiosamente, no! (Pero tenga en cuenta que estoy usando SQL Server 2014; es posible que las versiones más nuevas tengan esto solucionado. ¡Definitivamente me vendría bien una actualización!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Proyección de las tablas A1 y A2 No otra vez, e incluso el plan cambió para peor:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Resumen

Francamente, esperaba que esta optimización se realizara en todas las bases de datos, pero lamentablemente estaba muy equivocado. Además de eliminar JOIN , esta es una de las optimizaciones más importantes, que le permite crear consultas SQL enormes a partir de partes reutilizables como vistas y funciones de tabla. Desafortunadamente, no es compatible con 3 de las 5 bases de datos más comunes.