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

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

8. VERIFIQUE as restrições

Ah, isso é legal! Nosso banco de dados Sakila possui uma restrição CHECK na coluna FILM.RATING :

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

Sério, use restrições CHECK para garantir a integridade dos dados. O custo para adicioná-los é extremamente baixo - muito menor que outras restrições, por exemplo, PRIMARY , UNIQUE ou FOREIGN KEY , pois não necessitam de índice para funcionar, então você os obtém praticamente "de graça". Mas há uma nuance interessante relacionada à otimização! Considere as seguintes consultas:

Predicados impossíveis

Já encontramos predicados impossíveis , até mesmo restrições NOT NULL (que na verdade são um tipo especial de restrição CHECK ), mas esta é ainda mais legal:

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

Não existe tal filme, e não pode existir, pois a restrição CHECK impede sua inserção (ou atualização). Novamente, isso deve ser traduzido em um comando para não fazer nada. E esse pedido?

CREATE INDEX idx_film_rating ON film (rating);

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

Graças ao índice acima, provavelmente basta fazer uma rápida varredura no índice e contar todos os filmes com rating = 'NC-17' , já que essa é a única classificação restante. Portanto, a consulta deve ser reescrita assim:

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

Este deve ser o caso independentemente do índice, porque comparar uma coluna com um valor é mais rápido do que comparar com 4. Então, quais bancos de dados podem fazer isso?

DB2

Predicado impossível (classificação = 'N/A') Legal!

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

Predicate Information
 2 - RESID (1 = 0)

Predicado reverso (classificação = 'NC-17') Não...

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

Embora a etapa ID=3 utilize um índice, e embora as cardinalidades estejam corretas, ocorre uma varredura completa porque o plano não possui um predicado de intervalo, apenas o predicado "SARG". Veja a análise de Marcus Wynand para obter detalhes . Você também pode demonstrar isso invertendo manualmente o predicado e obtendo:

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

Agora temos o predicado de intervalo desejado.

MySQL

O MySQL suporta a sintaxe de restrição CHECK , mas por algum motivo não as aplica. Experimente isto:

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

e você obterá:

A
-
0

Zero pontos para MySQL (realmente, por que não apenas suportar restrições CHECK ?)

Oráculo

Predicado impossível (classificação = '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')

Novamente, um filtro muito estranho NULL IS NOT NULL , cortando FULL TABLE SCAN , que poderia facilmente ser removido completamente do plano. Mas pelo menos funciona! Predicado reverso (classificação = 'NC-17') Ops:

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

O predicado não pode ser invertido, a avaliação da cardinalidade é muito fraca, além disso obtemos INDEX FAST FULL SCAN em vez de INDEX RANGE SCAN e o predicado de filtro em vez do predicado de acesso . Mas é isso que deveríamos obter, por exemplo, invertendo manualmente o 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')

Desapontamento!

PostgreSQL

Observe que a versão PostgreSQL do banco de dados Sakila usa o tipo ENUM em vez de restrições CHECK na coluna RATING . Dupliquei a tabela usando uma restrição CHECK . Predicado impossível (classificação = 'N/A') Não funciona:

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

O predicado reverso (rating = 'NC-17') também não 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[]))

Sinto muito! Nota: Como David Rowley gentilmente nos apontou nos comentários , esse recurso pode ser habilitado definindo o parâmetro:

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

Predicado reverso (classificação = 'NC-17') Sim também!

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

Resumo

9. Conexões reflexivas desnecessárias.

À medida que suas consultas se tornam mais complexas, você pode acabar precisando realizar uma junção reflexiva em uma tabela com base em sua chave primária. Acredite, essa é uma prática muito comum ao construir visualizações complexas e conectá-las entre si, portanto, garantir que o banco de dados preste atenção a isso é uma parte crítica da otimização de códigos SQL complexos. Não vou demonstrar um exemplo complexo, bastará um simples, por exemplo:

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

Isso pode ser visto como um caso especial de eliminação de JOIN , já que na verdade não precisamos de uma junção para A2 , podemos fazer tudo o que precisamos apenas com a tabela A1 . Em seguida, a eliminação de INNER JOIN só funciona corretamente se houver um FOREIGN KEY , que não temos aqui. Mas graças à chave primária de ACTOR_ID , podemos provar que de fato A1 = A2 . Em certo sentido, este é novamente um fechamento transitivo . Você pode ir ainda mais longe e usar colunas das tabelas A1 e A2 :

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

No caso clássico de eliminação do JOIN , não seria mais possível eliminá-lo já que ambas as tabelas são projetadas. Mas como já provamos que A1 = A2 , então eles são intercambiáveis, então podemos esperar que a consulta seja convertida para:

SELECT first_name, last_name
FROM actor;

Qual SGBD pode fazer isso?

DB2

Projeção somente da tabela A1 Sim:

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

Projeção das tabelas A1 e A2 ... também sim:

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

MySQL

Projeção apenas da tabela A1 No.

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

Projeção das tabelas A1 e A2 ... também não

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

Decepção total...

Oráculo

Projeção somente da tabela A1 Sim

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

Projeção das tabelas A1 e A2 Sim novamente

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

PostgreSQL

Projeção apenas da tabela A1 Não:

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)

Projeção das tabelas A1 e A2 E novamente não:

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

Projeção apenas da tabela A1. Curiosamente, não! (Mas lembre-se de que estou usando o SQL Server 2014, versões mais recentes podem ter isso corrigido. Eu definitivamente precisaria de uma atualização!)

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

Projeção das tabelas A1 e A2 De novo não, e o plano até mudou para pior:

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

Resumo

Falando francamente, eu esperava que essa otimização fosse realizada em todos os bancos de dados, mas infelizmente me enganei muito. Além de eliminar JOIN , esta é uma das otimizações mais importantes, permitindo criar enormes consultas SQL a partir de partes reutilizáveis, como visualizações e funções de tabela. Infelizmente, não é compatível com 3 dos 5 bancos de dados mais comuns.