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

Cinco otimizações simples que podem ser implementadas com base apenas em metadados (ou seja, restrições) e na própria consulta. Oferecemos uma adaptação do artigo de Lukas Eder, projetada para quem tem um conhecimento geral de bancos de dados e SQL, bem como alguma experiência prática com SGBD . A otimização de custos é, na verdade, uma forma padrão de otimizar consultas SQL em bancos de dados modernos. É por isso que é tão difícil escrever manualmente um algoritmo complexo em 3GL (linguagens de programação de terceira geração) cujo desempenho excederia o plano de execução calculado dinamicamente gerado por um otimizador moderno. Hoje não discutiremos otimização de custos, ou seja, otimização baseada no modelo de custos do banco de dados. Veremos otimizações muito mais simples. Aqueles que podem ser implementados com base apenas em metadados (ou seja, restrições) e na própria solicitação. Normalmente sua implementação para um banco de dados não é um binômio de Newton, pois, neste caso, qualquer otimização levará a um melhor plano de execução, independente da presença de índices, volumes de dados e assimetria na distribuição dos dados. "Não é um binômio de Newton" não no sentido de quão fácil é implementar a otimização, mas se ela deve ser feita. Essas otimizações eliminam trabalho extra desnecessário [para o banco de dados] ( em oposição ao trabalho desnecessário e necessário, sobre o qual já escrevi ).

Para que servem essas otimizações?

A maioria deles é usada para:

• correções de bugs em consultas;
• permitindo que as visualizações sejam reutilizadas sem que o banco de dados realmente execute a lógica da visualização.

No primeiro caso, pode-se dizer: “E daí, vá em frente e corrija essa consulta SQL estúpida”. Mas que quem nunca errou atire uma pedra em mim primeiro. O segundo caso é especialmente interessante: nos dá a capacidade de criar bibliotecas complexas de visualizações e funções de tabela que podem ser reutilizadas em múltiplas camadas.

Bancos de dados usados

Neste artigo iremos comparar 10 otimizações SQL nos cinco SGBDs mais utilizados ( de acordo com rankings de banco de dados ):

• Oráculo 12.2;
• MySQL 8.0.2;
• Servidor SQL 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Outra classificação quase ecoa isso. Como de costume, neste artigo irei consultar o banco de dados Sakila . Aqui está uma lista desses dez tipos de otimizações:

1. fechamento transitivo;
2. predicados impossíveis e chamadas de mesa desnecessárias;
3. eliminando JOIN;
4. eliminação de predicados “sem sentido”;
5. projeções em subconsultas EXISTS;
6. fusão de predicados;
7. conjuntos provavelmente vazios;
8. restrições VERIFICAR;
9. conexões reflexivas desnecessárias;
10. Predicados de pushdown

Hoje discutiremos as pp. 1-3, na segunda parte - 4 e 5, e na parte 3 - 6-10.

1. Fechamento transitivo

Vamos começar com algo mais simples: fechamento transitivo . Este é um conceito trivial que se aplica a muitas operações matemáticas, como o operador de igualdade. Pode ser formulado neste caso da seguinte forma: se A = B e B = C, então A = C.

Não é difícil, certo? Mas isso tem algumas implicações interessantes para otimizadores SQL. Vejamos um exemplo. Vamos extrair todos os filmes com ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

O resultado é o seguinte:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

Vamos agora dar uma olhada no plano de execução desta consulta no caso do SGBD Oracle:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

A seção sobre predicados é especialmente interessante aqui. O predicado ACTOR_ID = 1, devido ao fechamento transitivo, aplica-se tanto à tabela ACTOR quanto à tabela FILM_ACTOR. Se:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

Para consultas mais complexas, isso produz alguns resultados muito bons. Em particular, a precisão das estimativas de cardinalidade aumenta significativamente, uma vez que se torna possível selecionar estimativas com base em um valor constante específico do predicado, e não, por exemplo, no número médio de filmes por atores, como na consulta a seguir (retornando o mesmo resultado):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

Seu plano:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

Como você pode ver, o número de linhas na tabela FILM_ACTOR está superestimado, enquanto o NESTED LOOP está subestimado. Aqui estão alguns valores interessantes:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Resultado:

19
27.315

É daí que vêm as estimativas. Se o banco de dados souber que estamos falando de ACTOR_ID = 1, ele poderá coletar estatísticas sobre o número de filmes desse ator específico . Caso contrário (já que o mecanismo padrão de coleta de estatísticas não correlaciona FIRST_NAME/LAST_NAME com ACTOR_ID), obteremos o número médio de filmes para todos os atores . Um erro simples e sem importância neste caso específico, mas em uma consulta complexa ele pode se propagar ainda mais, acumular-se e levar mais adiante na consulta (mais acima no plano) a uma escolha incorreta de JOIN. Portanto, sempre que puder, projete suas junções e predicados simples para aproveitar as vantagens do fechamento transitivo. Quais outros bancos de dados oferecem suporte a esse recurso?

DB2

Sim!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

Aliás, se você gosta de planos de execução legais como esse, dê uma olhada no script de Markus Winand .

MySQL

Infelizmente, os planos de execução do MySQL não são adequados para este tipo de análise. O próprio predicado está faltando nas informações de saída:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

Mas o fato de const ser especificado duas vezes na coluna REF mostra que ambas as tabelas estão procurando um valor constante. Ao mesmo tempo, o plano de consulta com FIRST_NAME/LAST_NAME é assim:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

E como você pode ver, o REF agora faz referência à coluna do predicado JOIN. A pontuação de cardinalidade é quase a mesma do Oracle. Então sim, o MySQL também suporta fechamentos transitivos.

PostgreSQL

Sim!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

servidor SQL

Sim!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

Resumo

Todos os nossos bancos de dados suportam fechamento transitivo. No entanto, espere pelo item 6 na próxima parte do artigo. Existem casos complexos de fechamento transitivo que nem todos os bancos de dados conseguem lidar.

2. Predicados impossíveis e chamadas de mesa desnecessárias

Esta é uma otimização completamente estúpida, mas por que não? Se os usuários escrevem predicados impossíveis, por que se preocupar em executá-los? aqui estão alguns exemplos:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

A primeira consulta obviamente nunca retornará nenhum resultado, mas a mesma afirmação é verdadeira para a segunda. Afinal, embora NULL IS NULL seja sempre TRUE, o resultado do cálculo NULL = NULL é NULL, o que, segundo a lógica de três valores , equivale a FALSE. Isso é bastante autoexplicativo, então vamos direto descobrir quais bancos de dados realizam essa otimização.

DB2

Sim!

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

Como você pode ver, o acesso à tabela ACTOR está totalmente excluído do plano. Contém apenas a operação GENROW, que gera zero linhas. Perfeito.

MySQL

Sim!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

Desta vez, o MySQL teve a gentileza de nos informar sobre a cláusula WHERE impossível. Obrigado! Isso torna a análise muito mais fácil, especialmente em comparação com outros bancos de dados.

Oráculo

Sim!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Vemos que o plano ainda menciona acesso à tabela ACTOR, e o número esperado de linhas ainda é 200, mas também existe uma operação de filtragem (FILTER) com Id=1, onde nunca haverá TRUE. Devido à antipatia da Oracle pelo tipo de dados booleano SQL padrão , a Oracle exibe NULL IS NOT NULL no plano, em vez de apenas FALSE. Ah, bem... Mas, falando sério, observe esse predicado. Tive a oportunidade de depurar planos de execução com subárvores de 1.000 linhas e valores de custo extremamente altos, apenas para descobrir depois que toda a subárvore estava sendo "cortada" pelo filtro NULL IS NOT NULL. Um pouco desanimador, eu lhe digo.

PostgreSQL

Sim!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

Já melhor. Nenhuma chamada de tabela ACTOR irritante e um pequeno predicado FALSE.

Servidor SQL?

Sim!

|--Constant Scan

O SQL Server chama isso de " varredura constante ", que é uma varredura onde nada acontece - semelhante ao DB2. Todos os nossos bancos de dados podem excluir predicados impossíveis:

3. Elimine JOINs

Na seção anterior, observamos acessos desnecessários a tabelas em consultas de tabela única. Mas o que acontece se o JOIN não exigir um dos vários acessos à tabela? Já escrevi sobre a eliminação do JOIN em um post anterior do meu blog . O mecanismo SQL é capaz de determinar, com base no tipo de consulta e na presença de chaves primárias e estrangeiras, se um determinado JOIN é realmente necessário em uma determinada consulta, ou se eliminá-lo não afetará a semântica da consulta. Em todos os próximos três exemplos, JOIN não é necessário. Uma junção interna ...-to-one pode ser eliminada com uma chave estrangeira NOT NULL. Em vez disso:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

O banco de dados pode fazer o seguinte:

SELECT first_name, last_name
FROM customer c

Um INNER JOIN do tipo "...-to-one" pode ser substituído se houver uma chave estrangeira anulável. A consulta acima funciona se a chave estrangeira estiver sujeita a uma restrição NOT NULL. Caso contrário, por exemplo, como nesta solicitação:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

então JOIN ainda pode ser eliminado, mas você terá que adicionar o predicado NOT NULL, assim:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Um OUTER JOIN do tipo "...-to-one" pode ser removido se houver uma chave exclusiva. Em vez disso:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

O banco de dados, novamente, pode fazer o seguinte:

SELECT first_name, last_name
FROM customer c

... mesmo que não haja chave estrangeira para CUSTOMER.ADDRESS_ID. A conexão externa exclusiva (DISTINCT OUTER JOIN) do tipo "...-to-many" pode ser removida. Em vez disso:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

O banco de dados pode fazer o seguinte:

SELECT DISTINCT first_name, last_name
FROM actor a

Todos esses exemplos foram estudados detalhadamente no artigo anterior, então não vou me repetir, apenas resumirei tudo o que vários bancos de dados podem eliminar: Infelizmente, nem todos os bancos de dados podem resolver todos os tipos de conexões. DB2 e SQL Server são os líderes indiscutíveis aqui! Continua