Otimizações SQL interessantes que não dependem do modelo de custo. Parte 2

Otimizações SQL interessantes que não dependem do modelo de custo. Parte 1

4. Eliminação de predicados “sem sentido”

Igualmente sem sentido são os predicados que são (quase) sempre verdadeiros. Como você pode imaginar, se você estiver perguntando:

SELECT * FROM actor WHERE 1 = 1;

... então os bancos de dados não irão realmente executá-lo, mas simplesmente irão ignorá-lo. Certa vez respondi uma pergunta sobre isso no Stack Overflow e foi por isso que decidi escrever este artigo. Deixarei testar isso como um exercício para o leitor, mas o que acontece se o predicado for um pouco menos “sem sentido”? Por exemplo:

SELECT * FROM film WHERE release_year = release_year;

Você realmente precisa comparar o valor consigo mesmo para cada linha? Não, não existe nenhum valor para o qual esse predicado seria FALSE , certo? Mas ainda precisamos verificar isso. Embora o predicado não possa ser igual a FALSE , ele pode muito bem ser igual a NULL em todos os lugares , novamente devido à lógica de três valores. A coluna RELEASE_YEAR é anulável e se alguma das linhas tiver RELEASE_YEAR IS NULL , então NULL = NULL resulta em NULL e a linha deve ser eliminada. Então a solicitação passa a ser a seguinte:

SELECT * FROM film WHERE release_year IS NOT NULL;

Quais bancos de dados fazem isso?

DB2

Sim!

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

É uma pena, mas o MySQL, novamente, não mapeia predicados em planos de execução, então descobrir se o MySQL implementa essa otimização específica é um pouco complicado. Você pode realizar uma avaliação de desempenho e ver se alguma comparação em grande escala está sendo feita. Ou você pode adicionar um índice:

CREATE INDEX i_release_year ON film (release_year);

E receba em troca planos para as seguintes solicitações:

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

Se a otimização funcionar, os planos de ambas as consultas deverão ser aproximadamente os mesmos. Mas neste caso este não é o caso:

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 você pode ver, nossas duas consultas diferem significativamente nos valores das colunas POSSIBLE_KEYS e FILTERED . Então, eu arriscaria um palpite razoável de que o MySQL não otimiza isso.

Oráculo

Sim!

----------------------------------------------------
| 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

Infelizmente não!

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

Os planos e custos variam. Ou seja, observe a avaliação da cardinalidade, que não é absolutamente boa, enquanto este predicado:

SELECT * FROM film WHERE release_year IS NOT NULL;

dá resultados muito melhores:

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

Desapontamento!

servidor SQL

Curiosamente, o SQL Server também não parece fazer isso:

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

Porém, com base na aparência do plano, a avaliação da cardinalidade está correta, assim como o custo. Mas na minha experiência com o SQL Server, eu diria que, neste caso, nenhuma otimização ocorre, pois o SQL Server exibiria o predicado realmente executado no plano (para ver o porquê, dê uma olhada nos exemplos de restrição CHECK abaixo). E quanto aos predicados "sem sentido" nas colunas NOT NULL ? A conversão acima só foi necessária porque RELEASE_YEAR pode ser indefinido. O que acontece se você executar a mesma consulta sem sentido, por exemplo, na coluna FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Agora, não corresponde a nenhum predicado? Ou pelo menos é assim que deveria ser. Mas é isso?

DB2

Sim!

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

Nenhum predicado é aplicado e selecionamos todos os filmes.

MySQL

Sim! (Novamente, palpite fundamentado)

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

Observe que a coluna EXTRA agora está vazia, como se não tivéssemos nenhuma cláusula WHERE!

Oráculo

Sim!

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

Novamente, nenhum predicado se aplica.

PostgreSQL

Uau, não de novo!

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

O filtro é aplicado e a pontuação de cardinalidade ainda é 5. Que chatice!

servidor SQL

E aqui novamente não!

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

Resumo

Parece uma otimização simples, mas não é usada em todos os SGBDs; em particular, por incrível que pareça, não é usada no SQL Server!

5. Projeções em subconsultas EXISTS

Curiosamente, sou constantemente questionado sobre eles na minha master class, onde defendo o ponto de vista de que SELECT * geralmente não leva a nada de bom. A questão é: é possível usar SELECT * em uma subconsulta EXISTS ? Por exemplo, se precisarmos encontrar atores que atuaram em filmes...

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
)

E a resposta é... sim. Pode. O asterisco não afeta a solicitação. Como você pode ter certeza disso? Considere a seguinte 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);

Todos esses bancos de dados relatam um erro de divisão por zero. Observe um fato interessante: no MySQL, quando dividimos por zero, obtemos NULL em vez de erro, então temos que fazer outra coisa que não é permitida. Agora, o que acontece se executarmos, em vez das consultas acima, as seguintes 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));

Agora nenhum dos bancos de dados retorna um erro. Todos eles retornam TRUE ou 1 . Isso significa que nenhum de nossos bancos de dados realmente avalia a projeção (ou seja, a cláusula SELECT ) da subconsulta EXISTS . O SQL Server, por exemplo, mostra o seguinte plano:

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

Como você pode ver, a expressão CASE foi convertida em uma constante e a subconsulta foi eliminada. Outros bancos de dados armazenam a subconsulta no plano e não mencionam nada sobre a projeção, então vamos dar uma olhada no plano de consulta original no Oracle:

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

O plano de consulta acima é assim:

------------------------------------------------------------------
| 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 informações sobre a projeção em Id=3 . Na verdade, nem acessamos a tabela FILM_ACTOR porque não precisamos. O predicado EXISTS pode ser executado usando um índice de chave estrangeira em uma única coluna ACTOR_ID - tudo o que é necessário para esta consulta - mesmo que tenhamos escrito SELECT * .

Resumo

Felizmente, todos os nossos bancos de dados removem a projeção das subconsultas EXISTS : Fique ligado na Parte 3 , onde discutiremos outras otimizações interessantes de SQL.