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

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

4. Eliminación de predicados "sin sentido"

Tampoco tienen sentido los predicados que (casi) siempre son verdaderos. Como puedes imaginar, si estás preguntando:

SELECT * FROM actor WHERE 1 = 1;

...entonces las bases de datos en realidad no lo ejecutarán, sino que simplemente lo ignorarán. Una vez respondí una pregunta sobre esto en Stack Overflow y es por eso que decidí escribir este artículo. Dejaré la prueba de esto como ejercicio para el lector, pero ¿qué sucede si el predicado es un poco menos "sin sentido"? Por ejemplo:

SELECT * FROM film WHERE release_year = release_year;

¿Realmente necesitas comparar el valor consigo mismo para cada fila? No, no hay ningún valor para el cual este predicado sea FALSO , ¿verdad? Pero todavía tenemos que comprobarlo. Aunque el predicado no puede ser igual a FALSE , bien puede ser igual a NULL en todas partes , nuevamente debido a la lógica de tres valores. La columna RELEASE_YEAR admite valores NULL y, si alguna de las filas tiene RELEASE_YEAR IS NULL , entonces NULL = NULL da como resultado NULL y la fila debe eliminarse. Entonces la solicitud queda como sigue:

SELECT * FROM film WHERE release_year IS NOT NULL;

¿Qué bases de datos hacen esto?

DB2

¡Sí!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

mysql

Es una pena, pero MySQL, nuevamente, no asigna predicados a planes de ejecución, por lo que determinar si MySQL implementa esta optimización en particular es un poco complicado. Puede realizar una evaluación del desempeño y ver si se están realizando comparaciones a gran escala. O puedes agregar un índice:

CREATE INDEX i_release_year ON film (release_year);

Y obtenga a cambio planos para las siguientes solicitudes:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Si la optimización funciona, entonces los planes de ambas consultas deberían ser aproximadamente los mismos. Pero en este caso no es así:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Como puede ver, nuestras dos consultas difieren significativamente en los valores de las columnas POSSIBLE_KEYS y FILTERED . Así que me atrevería a suponer razonablemente que MySQL no optimiza esto.

Oráculo

¡Sí!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

¡Lamentablemente no!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Los planes y costos varían. Es decir, mire la evaluación de la cardinalidad, que no es nada buena, mientras que este predicado:

SELECT * FROM film WHERE release_year IS NOT NULL;

da resultados mucho mejores:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

¡Gorrón!

servidor SQL

Por extraño que parezca, SQL Server tampoco parece hacer esto:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Sin embargo, basándose en la apariencia del plan, la evaluación de la cardinalidad es correcta, al igual que el costo. Pero en mi experiencia con SQL Server, diría que en este caso no se produce ninguna optimización, ya que SQL Server mostraría el predicado realmente ejecutado en el plan (para ver por qué, eche un vistazo a los ejemplos de restricciones CHECK a continuación). ¿Qué pasa con los predicados "sin sentido" en columnas NO NULAS ? La conversión anterior solo fue necesaria porque RELEASE_YEAR no puede estar definido. ¿Qué sucede si ejecuta la misma consulta sin sentido, por ejemplo, en la columna FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

¿No corresponde ahora a ningún predicado? O al menos así debería ser. ¿Pero es?

DB2

¡Sí!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

No se aplica ningún predicado y seleccionamos todas las películas.

mysql

¡Sí! (Nuevamente, suposición fundamentada)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Observe que la columna EXTRA ahora está vacía, ¡como si no tuviéramos ninguna cláusula WHERE!

Oráculo

¡Sí!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Nuevamente, no se aplican predicados.

PostgreSQL

¡Guau, no otra vez!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Se aplica el filtro y la puntuación de cardinalidad sigue siendo 5. ¡Qué lástima!

servidor SQL

¡Y aquí de nuevo no!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Resumen

Parece una optimización simple, pero no se usa en todos los DBMS; en particular, curiosamente, ¡no se usa en SQL Server!

5. Proyecciones en subconsultas EXISTS

Curiosamente, me preguntan sobre ellos todo el tiempo en mi clase magistral, donde defiendo el punto de vista de que SELECT * generalmente no conduce a ningún bien. La pregunta es: ¿es posible utilizar SELECT * en una subconsulta EXISTS ? Por ejemplo, si necesitamos encontrar actores que hayan actuado en películas...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Y la respuesta es sí. Poder. El asterisco no afecta la solicitud. ¿Cómo puedes estar seguro de esto? Considere la siguiente consulta:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Todas estas bases de datos informan un error de división por cero. Tenga en cuenta un hecho interesante: en MySQL, cuando dividimos por cero, obtenemos NULL en lugar de un error, por lo que tenemos que hacer algo más que no está permitido. Ahora bien, ¿qué pasa si ejecutamos, en lugar de lo anterior, las siguientes consultas?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Ahora ninguna de las bases de datos devuelve un error. Todos devuelven VERDADERO o 1 . Esto significa que ninguna de nuestras bases de datos evalúa realmente la proyección (es decir, la cláusula SELECT ) de la subconsulta EXISTS . SQL Server, por ejemplo, muestra el siguiente plan:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Como puede ver, la expresión CASE se ha convertido en una constante y la subconsulta se ha eliminado. Otras bases de datos almacenan la subconsulta en el plan y no mencionan nada sobre la proyección, así que echemos otro vistazo al plan de consulta original en Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

El plan de consulta anterior se ve así:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Observamos información sobre la proyección en Id=3 . De hecho, ni siquiera accedemos a la tabla FILM_ACTOR porque no es necesario. El predicado EXISTS se puede realizar usando un índice de clave externa en una sola columna ACTOR_ID (todo lo que se necesita para esta consulta), aunque escribimos SELECT * .

Resumen

Afortunadamente, todas nuestras bases de datos eliminan la proyección de las subconsultas EXISTS : Estén atentos a la Parte 3 , donde analizaremos otras optimizaciones interesantes de SQL.