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

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 Otimizações interessantes de SQL que não dependem do modelo de custo. Parte 4

10. Empurrando predicados

Esta otimização não é inteiramente apropriada aqui, porque não se pode dizer que não se baseie de forma alguma no modelo de custo. Mas como não consigo pensar em um único motivo pelo qual o otimizador não deva enviar predicados para tabelas derivadas, vou listá-los aqui, junto com o restante das otimizações sem custo. Considere o pedido:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

A tabela derivada nesta consulta não faz sentido e deve ser eliminada reduzindo o número de níveis de aninhamento de consulta. Mas vamos ignorar isso por enquanto. Você pode esperar que o banco de dados execute a seguinte consulta em vez da consulta acima:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

E então, novamente, talvez elimine a solicitação externa. Um exemplo mais complexo é obtido usando UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

O resultado desta consulta:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Agora, seria ótimo se o otimizador de banco de dados executasse uma consulta como esta:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Ou seja, para que ele empurre o predicado para a tabela derivada, e daí para duas subconsultas UNION ALL , já que, afinal, temos um índice tanto na coluna ACTOR.LAST_NAME quanto na coluna CUSTOMER.LAST_NAME . Novamente, essa conversão é provavelmente baseada em estimativas de custo na maioria dos bancos de dados, mas ainda acho que é óbvio, pois, com qualquer algoritmo, é quase sempre melhor reduzir o número de tuplas processadas o mais cedo possível. Se você conhece algum caso em que tal transformação acabou sendo uma má ideia, ficarei feliz em ouvir seus comentários! Eu estaria muito interessado. Então, qual dos nossos bancos de dados pode fazer isso? (E por favor, é tão simples e tão importante, que a resposta seja: tudo)

DB2

Tabela derivada simples Sim

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Tabela derivada com UNION Também sim:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Além disso, em ambos os casos, a tabela derivada (visualização) foi excluída do plano porque não era realmente necessária.

MySQL

Tabela derivada simples Sim

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

É usado o acesso usual de chave primária por valor constante. Tabela derivada com UNION Ops, não.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Resultados da conversão manual no plano:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Este é um problema sério ao usar consultas aninhadas complexas no MySQL!

Oráculo

Tabela derivada simples Sim, funciona.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

E o número de níveis de aninhamento foi reduzido. A tabela derivada com UNION também funciona:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Porém, sem reduzir o número de níveis de aninhamento. Id=1 "View" mostra que a tabela derivada ainda está lá. Neste caso não é um grande problema, talvez apenas um pequeno custo adicional.

PostgreSQL

Tabela derivada simples Sim, funciona:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Observe, entretanto, que o PostgreSQL às vezes nem usa a chave primária para procurar uma única linha, mas em vez disso verifica a tabela inteira. Nesse caso, 200 linhas × 25 bytes por linha ("largura") cabem em um bloco, então qual é o sentido de se preocupar com leituras de índice além de gerar operações de E/S desnecessárias para acessar uma tabela tão pequena? Tabela derivada com UNION Sim, funciona também:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Novamente, um índice na coluna ACTOR.LAST_NAME não é usado, mas um índice na coluna CUSTOMER.LAST_NAME é usado porque a tabela CUSTOMER é muito maior.

servidor SQL

Tabela derivada simples Sim, funciona

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

Tabela derivada com UNION também funciona.

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Resumo

Minhas esperanças não se concretizaram. O MySQL 8.0.2 ainda não suporta totalmente esta otimização simples. Todos os outros, no entanto, apoiam.

Conclusão

A lista aqui apresentada está longe de estar completa. Existem muitas outras transformações SQL simples que não são (ou não deveriam ser) difíceis de serem implementadas pelos bancos de dados, mesmo antes do envolvimento de um otimizador de custos. Eles eliminam o trabalho extra desnecessário [para o banco de dados] ( em oposição ao trabalho desnecessário e obrigatório , sobre o qual já escrevi ). Estas são ferramentas importantes para:

1. Erros estúpidos do [desenvolvedor] não tiveram impacto no desempenho. Os erros são inevitáveis ​​e, à medida que um projeto cresce e as consultas SQL se tornam mais complexas, esses erros podem se acumular, sem qualquer efeito.


2. Fornece a capacidade de reutilizar blocos complexos, como visualizações e funções de tabela, que podem ser incorporados em consultas SQL pai, transformados ou parcialmente excluídos ou reescritos.

Essas capacidades são críticas para o ponto 2. Sem eles, seria muito difícil criar consultas SQL de 4.000 linhas com desempenho normal baseadas em uma biblioteca de componentes SQL reutilizáveis. Para grande decepção dos usuários do PostgreSQL e do MySQL, esses dois populares bancos de dados de código aberto ainda têm um longo caminho a percorrer em comparação com seus concorrentes comerciais DB2, Oracle e SQL Server, dos quais o DB2 teve o melhor desempenho, com o Oracle e o SQL Server praticamente empatados. pescoço, um pouco atrás.