Problemas de desempenho SQL decorrentes de “trabalho desnecessário, mas obrigatório”

Nível de conhecimento necessário para compreender o artigo: conhecimento geral de bancos de dados e SQL, alguma experiência prática com SGBD. Provavelmente, a coisa mais importante que você pode aprender para escrever consultas SQL eficazes é a indexação. No entanto, em segundo lugar, muito atrás, está o conhecimento de que muitos clientes SQL exigem que o banco de dados faça muitos “trabalhos desnecessários, mas necessários ” . Repita depois de mim: O que é “trabalho desnecessário, mas obrigatório”? Como nos diz o Capitão Óbvio, ela:

Desnecessário

Deixe nosso aplicativo cliente precisar dos seguintes dados: Nada incomum. Estamos trabalhando com um banco de dados de filmes (como o banco de dados Sakila ) e queremos exibir o título e a classificação de todos os filmes aos usuários. A consulta a seguir pode fornecer o resultado que precisamos:

SELECT title, rating
FROM film

No entanto, nosso aplicativo (ou nosso ORM) executa esta consulta:

SELECT *
FROM film

O que obtemos como resultado? Adivinhar. Recebemos muitas informações inúteis: À direita você pode até ver algum JSON complexo sendo carregado:

• do disco
• armazenar em cache
• Por fio
• na memória do cliente
• e finalmente jogado fora [como desnecessário]

Sim, jogamos fora a maior parte dessas informações. Todas as ações tomadas para extrair essas informações revelaram-se completamente inúteis. É verdade? É verdade.

Obrigatório

E agora – a pior parte. Embora os otimizadores possam fazer muito agora, essas ações são obrigatórias para o banco de dados. O banco de dados não tem como saber que o aplicativo cliente não precisa de 95% desses dados. E este é apenas o exemplo mais simples. Imagine conectar várias tabelas... E daí, você diz, mas os bancos de dados são rápidos? Deixe-me esclarecê-lo sobre algumas coisas que você provavelmente não pensou. É claro que o tempo de execução de uma solicitação individual não afeta realmente nada. Ok, funcionou uma vez e meia mais devagar, mas vamos superar isso, certo? Por conveniência? Às vezes isso é verdade. Mas se você sempre sacrificar o desempenho pela conveniência , essas pequenas coisas começarão a fazer sentido. Não falaremos mais sobre desempenho (velocidade de execução de solicitações individuais), mas sobre throughput (tempo de resposta do sistema), e então começarão problemas sérios, que não são tão fáceis de resolver. É quando você perde escalabilidade. Vejamos os planos de execução, neste caso, o SGBD Oracle:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

comparado com:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Executando uma consulta SELECT * em vez de SELECT title, a classificação usa 8 vezes mais memória no banco de dados. Nada inesperado, certo? Sabíamos que isso iria acontecer. Mas ainda concordamos com isso para muitas das nossas solicitações nas quais simplesmente não precisamos de todos esses dados. Criamos trabalho desnecessário, mas obrigatório para o banco de dados , que vai se acumulando e se acumulando. Usamos 8 vezes mais memória do que o necessário (o multiplicador mudará, é claro). Enquanto isso, em todos os outros estágios (E/S de disco, transferência de dados pela rede, consumo de memória pelo cliente) os problemas são exatamente os mesmos, mas vou ignorá-los e olhar em vez disso...

Usando índices

A maioria dos bancos de dados hoje já apreciou o conceito de cobertura de índices . Um índice de cobertura não é em si um tipo especial de índice. Mas pode acabar sendo um "índice especial" para uma consulta específica, seja "por acidente" ou porque era essa a intenção. Considere a seguinte consulta:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Não há nada inesperado em termos de sua implementação. Este é um pedido simples. Visualize o intervalo por índice, acesse a tabela – e pronto:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Bom plano, não é? Bem, se realmente precisávamos disso, então não: Obviamente estamos desperdiçando memória, etc. Vamos considerar esta consulta como uma alternativa:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Seu plano é este:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Conseguimos eliminar completamente o acesso à tabela, graças à presença de um índice que satisfaz todas as necessidades da nossa consulta... um índice de cobertura. É importante? E como! Essa abordagem permite acelerar algumas consultas em uma ordem de grandeza (ou desacelerá-las em uma ordem de grandeza quando o índice não estiver mais cobrindo após algumas alterações). Os índices de cobertura nem sempre podem ser usados. Você tem que pagar pelos índices e não deve adicionar muitos deles. Mas neste caso tudo é óbvio. Vamos avaliar o desempenho:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный request, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный request: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Observe que a tabela de atores possui apenas 4 colunas, portanto a diferença de desempenho entre as instruções 1 e 2 não é tão grande, mas ainda é significativa. Observarei também que usei dicas do otimizador Oracle para que o otimizador selecionasse um ou outro índice específico para a consulta. O Operador 3 é o vencedor indiscutível da nossa corrida. Seu desempenho é muito melhor e estamos falando de uma consulta extremamente simples. Novamente, quando escrevemos SELECT *, criamos um trabalho desnecessário, mas obrigatório para o banco de dados que ele não pode otimizar. Ela não selecionará o índice de cobertura porque ele tem uma sobrecarga um pouco maior que o índice LAST_NAME que ela escolheu e, entre outras coisas, ela ainda precisa acessar a tabela para recuperar uma coluna LAST_UPDATE inútil, por exemplo. Mas quanto mais profundamente analisamos SELECT *, piores as coisas ficam. Vamos falar sobre...

Conversões SQL

Os otimizadores funcionam muito bem porque transformam as consultas SQL ( falei sobre como isso funciona em minha palestra recente no Voxxed Days em Zurique ). Por exemplo, existe uma transformação de "exceção JOIN" extremamente poderosa. Considere a seguinte visualização auxiliar que tivemos que criar para evitar a junção manual de todas essas tabelas todas as vezes:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Essa visualização simplesmente faz todas as junções "...-to-one" entre a tabela CUSTOMER customer e diversas tabelas para partes de seu endereço. Obrigado, normalização. Imagine que depois de trabalhar um pouco com essa visão, nos acostumamos e esquecemos das tabelas subjacentes. E agora executamos a seguinte consulta:

SELECT *
FROM v_customer

Como resultado, obtemos um plano muito impressionante:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Bem, claro. O banco de dados está fazendo todas essas junções e varreduras completas de tabelas porque foi isso que pedimos para ele fazer - buscar todos esses dados. Agora, novamente, imagine que tudo o que realmente precisávamos era isto: O que, sério, certo? Agora você está começando a entender do que estou falando. Mas imagine que aprendemos algo com os erros do passado e execute esta consulta mais ideal:

SELECT first_name, last_name
FROM v_customer

Agora vamos verificar o que aconteceu!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Mudanças drásticas para melhor em termos de execução. As junções foram eliminadas porque o otimizador agora pode ver que elas são inúteis e, se puder ver isso (e você não tornou esse trabalho obrigatório selecionando *), então ele simplesmente não poderá fazer todo esse trabalho. Por que isso acontece neste caso? A chave estrangeira CUSTOMER.ADDRESS_ID para a chave primária ADDRESS.ADDRESS_ID garante exatamente um valor desta última, o que significa que a operação JOIN será uma junção "...-to-one" que não aumenta ou diminui o número de linhas . E como não selecionamos ou solicitamos nenhuma linha, não faz sentido carregá-las. A remoção do JOIN provavelmente não afetará em nada o resultado da consulta. Os bancos de dados fazem isso o tempo todo. Você pode executar a seguinte consulta em praticamente qualquer banco de dados:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

Nesse caso, você pode esperar que uma exceção aritmética seja lançada, como ao executar a seguinte consulta:

SELECT 1 / 0 FROM dual

Mas isso não acontece. O otimizador (ou mesmo o analisador) pode garantir que nenhum elemento da lista de seleção no predicado EXISTS (SELECT ..) alterará o resultado da consulta, portanto não há necessidade de executá-la. Assim!

Enquanto isso...

Um dos problemas mais irritantes com ORMs é que eles são muito fáceis de escrever consultas SELECT *. Na verdade, por exemplo, em HQL/JPQL eles geralmente são usados ​​por padrão. Podemos omitir totalmente a cláusula SELECT, pois iremos recuperar a entidade inteira, certo? Por exemplo:

FROM v_customer

Por exemplo, Vlad Mihalcea, especialista e defensor do desenvolvimento com Hibernate , recomenda usar consultas [qualificadas] quase sempre quando tiver certeza de que não deseja salvar nenhuma alteração após a finalização da compra. Os ORMs facilitam muito a solução do problema de persistência de grafos de objetos. Nota: Persistência. As tarefas de modificar gráficos de objetos e salvar alterações estão inextricavelmente ligadas. Mas se você não vai fazer isso, então por que se preocupar em extrair a essência? Por que não escrever um pedido [refinado]? Sejamos claros: do ponto de vista do desempenho, escrever uma consulta especificamente adaptada ao seu caso de uso específico é obviamente melhor do que qualquer outra opção. Você pode não se importar, pois seu conjunto de dados é pequeno e isso não importa. Ótimo. Mas quando você eventualmente precisar de escalabilidade, redesenhar seus aplicativos para usar consultas em vez da travessia imperativa do gráfico de entidade será bastante desafiador. E você terá algo para fazer sem isso.

Contando linhas para descobrir se algo está presente

Um dos piores desperdícios de recursos é executar consultas COUNT(*) apenas para ver se algo está no banco de dados. Por exemplo, precisamos descobrir se um determinado usuário tem algum pedido. E executamos a solicitação:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Elementar. Se COUNT = 0, então não há pedidos. Caso contrário, sim. O desempenho não será tão ruim já que provavelmente temos um índice na coluna ORDERS.USER_ID. Mas o que você acha que será o desempenho da consulta acima comparado com a seguinte opção:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Não é preciso ser um cientista espacial para descobrir que um predicado de existência verdadeira irá parar de procurar por strings adicionais assim que encontrar a primeira . Portanto, se o resultado for “sem pedidos”, a velocidade será comparável. Se, no entanto, o resultado for “sim, há pedidos”, então, no caso em que não seja necessário contar a quantidade exata, a resposta será recebida muito mais rapidamente. Afinal, não estamos interessados ​​no número exato. Porém, pedimos ao banco de dados para calculá-lo ( trabalho desnecessário ) e o banco de dados não sabe que estamos ignorando todos os resultados maiores que 1 ( trabalho necessário ). Claro, seria muito pior se chamássemos list.size() em uma coleção apoiada por JPA para obter os mesmos resultados. Já escrevi sobre isso em meu blog anteriormente e realizei testes comparativos de ambas as opções em...

Conclusão

Este artigo afirma o óbvio. Não force o banco de dados a realizar trabalhos desnecessários, mas obrigatórios . É desnecessário porque, dadas as exigências, você sabe que algum trabalho específico não precisa ser feito. No entanto, você diz ao banco de dados para fazer isso. É necessário porque não há como o banco de dados garantir que esse trabalho seja desnecessário . Essas informações estão disponíveis apenas para o cliente e não para o servidor. Portanto, o banco de dados precisa executá-lo. O artigo focou em SELECT *, principalmente porque é um objeto muito conveniente de se olhar. Mas isso não se aplica apenas aos bancos de dados. Isto se aplica a todos os algoritmos distribuídos nos quais o cliente diz ao servidor para fazer um trabalho desnecessário, mas necessário . Quantas tarefas N+1 existem em seu aplicativo AngularJS médio, onde a UI percorre o resultado do serviço A, chamando o serviço B várias vezes, em vez de agrupar todas as chamadas para B em uma única chamada? Este é um fenômeno muito comum. A solução é sempre a mesma. Quanto mais informações você fornecer à entidade que executa seus comandos, mais rápido ela (teoricamente) executará esses comandos. Escreva consultas ideais. Sempre. Todo o seu sistema agradecerá por isso. Artigo original