Performance em queries SQL usando IS NULL


Hoje, depois de o que? Uns 2 anos de hiato? Volto com uma crônica.

Prólogo

Estava hoje eu analisando a performance de uma aplicação Java no trabalho e me deparei com algo que me surpreendeu.

O Profiler do VisualVM estava me mostrando um método que estava demorando demais. Esse método era muito simples, sendo que apenas executava uma consulta no banco de dados (relacional), algo parecido com:

SELECT * FROM Produto p WHERE p.categoriaId IS NULL

Digo parecido porque na realidade era uma query em HQL sobre outra entidade, mas a ideia é a mesma

Muito intrigado abri meu Squirrel  (cliente SQL), joguei a query lá para olhar o seu plano de execução.

Ato 1 – Full Table Scan

Para minha surpresa o banco estava fazendo um full table scan na tabela de Produtos (que é enorme). Ou seja, ele estava passando por cada um dos registros olhando se a tal categoriaId era nula.

Logo pensei:

– Ah! Problema resolvido! Falta um índice na categoriaId.

Não, não faltava… Para minha surpresa o banco não estava indexando o NULL…

Ato 2 – Indexando o Null

Nem o Oracle, nem o Postgres (< 8.3) indexam NULL, apenas valores de verdade (não sei se outras marcas de BD indexam, consulte o manual). E agora? O que fazer?

Uma forma de resolver essa questão é justamente transformar o NULL em um valor de verdade, digamos 0 ou “Null”, dependendo do tipo da coluna que você vai indexar, de modo que ele faça parte do índice.

Para fazer isso podemos usar a função COALESCE(), que existe nos bancos relacionais mais conhecidos.

A função COALESCE(value, …) recebe um ou mais parâmetros e devolve o primeiro parâmetro não nulo, se houver.

Assim, ao invés de criar um índice para a coluna, vamos criar um índice para uma função sobre aquela coluna.

CREATE INDEX in_produto_categoriaid ON produto( COALESCE(categoriaid, 0) )

Aqui usamos COALESCE(categoriaid, 0) porque nossa categoriaid é uma coluna numérica. Se fosse uma coluna de tipo texto poderíamos usar COALESCE(coluna_texto, ‘Nulo’). O importante é usar um valor default que com certeza nunca será inserido nessa coluna.

Ato 3 – Adaptando as sua Queries

A desvantagem dessa solução é que precisamos mudar nossa consulta para fazer restrições sobre a função da coluna ao invés da coluna em si.

No nosso exemplo teríamos que mudar a consulta original de:

SELECT * FROM Produto p WHERE p.categoriaId IS NULL

Para:

SELECT * FROM Produto p WHERE COALESCE(p.categoriaId, 0) = 0

Assim, com certeza o banco usará o índice e você evitará um full table scan!

Epílogo

Cuidado com consultas que restringem apenas por IS NULL ou IS NOT NULL, especialmente em tabelas com muitos registros. Em alguns bancos de dados, os valores nulos não são indexados, e a sua consulta vai ficar pesadíssima.

A solução apresentada aqui é bem satisfatória, mas tem o porém de precisar de uma alteração na query em si, o que pode ser problemático, dependendo da sua aplicação.

Por fim, algumas observações:

  • Se você usa Hibernate ele tem suporte para a função COALESCE, o que é bom
  • O Oracle 11g supostamente tem uma opção para indexar valores nulos. Criar o índice dessa forma não surtiu efeito nos meus testes de fato funciona!
  • Parece que existe um comportamente estranho (bug?) no Hibernate + HSQLDB + COALESCE. Então, se você usa HSQLDB fique esperto com isso.

Fin

Errata

Inicialmente eu havia dito acima que criar o índice da forma mostrada no link acima, usando uma constante para fazer um índice composto, não funcionava:

CREATE INDEX in_produto_categoriaid ON produto( categoriaid, 1) )

Mas na verdade eu estava enganado nos meus testes. Esse workaround funciona e faz com que as colunas com NULL sejam de fato indexadas.

Vou explicar onde errei:

Primeiramente eu analisei o plano de execução da busca com IS NULL e vi que o Oracle fazia um TABLE ACCESS FULL. Logo concluí, precipitadamente, que o índice não estava sendo usado por que o NULL não estava indexado.

Mas acontece que havia muitos registros com categoriaid null, e se os predicados de restrição usados pela sua busca restringir uma quantidade de registros maior que cerca de 20% do total de registros, o banco em geral vai dar preferência por fazer um full table scan por ser menos custoso que encontrar todos esses 20% de registros a partir do índice.

Isso porque o banco é otimizado para fazer esses tipos de acesso sequencial aos dados.

Anúncios

4 comentários sobre “Performance em queries SQL usando IS NULL

  1. Kama,

    Primeiro, queria dizer que este é um post muito bom.
    Segundo, acrescentando algumas informações, só lembrando que alguns data types de bancos diferentes tem comportamentos diferentes, a exemplo do varchar e do char no DB2 e do varchar e do varchar2 do Oracle.
    Terceiro, esse é mais um recado, desenvolvedores, se forem usar algum valor diferente de nulo para representar que não há valor lembrem de documentar no modelo ER.

    Kurata

    • Isso ae Kurata!

      Concordo em documentar o “valor que representa a ausência de valor”. Só daria preferência a outras formas de documentar, como na própria classe da entidade (se houver) ou em testes unitários.

      • Kama,

        A minha sugestão do diagrama de entidade relacional se da porquê caso alguém queira plugar um ETL no banco, ele vai pedir e o diagrama lógico do banco de dados.

Os comentários estão desativados.