A influência de chaves primárias e índices na performance das consultas SQL

Olá pessoal,

Recentemente, eu e meus colegas de equipe nos vimos em uma situação que um determinado processo de ETL estava lento, tínhamos por obrigação melhorar o desempenho do mesmo. Verificamos o processo de ETL em si e ele estava bem enxuto, então partimos para o banco de dados.

Analisando as tabelas utilizadas, notamos uma tabela que a principio iria ser temporária, mas acabou sendo promovida a definitiva e estava provocando o gargalo de processamento. Ao verificar a mesma, notamos que ela estava sem chave primária ou índice (um pecado), ela era puramente um tabelão, então providenciamos uma chave primária e alguns índices. Após tal mudança, o processo de ETL melhorou absurdamente. Esclarecendo um pouco, essa tabela é necessária para que certas ferramentas de descoberta de conhecimento possam acessar o dado de forma simples, pois os usuários que terão acesso à mesma são de marketing, finanças e etc..

Depois de contextualizar a questão, venho com mais uma dica, na verdade um passo a passo demonstrando a importância de se usar chaves primárias e índices em suas tabelas do banco de dados relacional. Não vou me ater nos conceitos aprofundados de chaves primárias e índices, deixarei isso para um futuro próximo, vou demonstrar como fazer um teste e verificar a eficácia de se ter tais recursos em suas tabelas.

O SGBD utilizado foi o Oracle 11g, então as queries utilizadas foram criadas em PL\SQL, isso quer dizer que, caso você teste as mesma em outro banco de dados, no SQL Server por exemplo, aparecerão erros.

Lembrando que, o ambiente em que for fazer o teste, pode afetar o desempenho de sua consulta. Processador, memória e disco rígido, fazem toda a diferença, resumindo, os resultados que encontrei podem não ser os mesmos que você vai encontrar.

Primeiramente, vamos criar uma tabela simples, sem nenhuma chave primária ou índice e faremos a consulta da mesma. 

Importante: O procedimento abaixo cria uma tabela por meio de uma consulta que gera dados de teste para nosso exemplo, eu criei 10 milhões de registros, o banco de dados que utilizei fica em um servidor de grande porte, então aconselho a utilizar menos registros, talvez para um notebook pessoal, um milhão de linhas seja de bom tamanho, fica a dica:

CREATE TABLE big_t AS 
  SELECT ROWNUM AS num_uniq 
       , MOD(ROWNUM,10) AS num10
       , MOD(ROWNUM,100000) AS num100k
       , RPAD(rownum,1000,'A') AS texto 
    FROM all_source, all_source 
   WHERE rownum<= 10000000;  

Este processo demorou exatamente 30,826 segundos para ser executado no meu SGBD.

Agora vou testar a tabela fazendo algumas consultas:

SELECT COUNT(1) FROM big_t;  --101,12 segundos
SELECT COUNT(1) FROM big_t;  --101,604 segundos
SELECT COUNT(1) FROM big_t;  --104,24 segundos

Fiz o teste 3 vezes para ter certeza do tempo de consulta.

Agora vou colocar um filtro nessa brincadeira e ver a performance:

SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --100,605 segundos
SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --101,760 segundos
SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --101,791 segundos

Observe que não tenho nenhuma complexidade nessas queries, imagine algo bem maior com joins e agrupamentos, não vai ficar performático né.

Agora vamos à "mágica"  das chaves primárias, segue a criação da mesma.

ALTER TABLE big_t ADD (  CONSTRAINT big_t_pk PRIMARY KEY (num_uniq));

Repetirei as consultas realizadas, veja a diferença que tive no tempo de resposta:

SELECT COUNT(1) FROM big_t;  --3,229 segundos
SELECT COUNT(1) FROM big_t;  --1,061 segundos
SELECT COUNT(1) FROM big_t;  --1,061 segundos

De 100 segundos, cai para 1 segundo, apenas definindo uma chave primária em minha tabela, imagine a diferença que isso faz para uma consulta pesada, é muita coisa.

Porém, se repetirmos a consulta com o filtro para num10, a performance não vai mudar, pois a chave primária já não vai ter o mesmo peso que teve na consulta anterior.

SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --101,51 segundos
SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --106,033 segundos

Então faço uma nova "mágica", adiciono um índice à tabela.

CREATE INDEX idx_bigt_num10 ON big_t(num10);

Feito isso, vou repetir as consultas com filtro.

SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --2,075 segundos
SELECT COUNT(1)  FROM big_t  WHERE num10 = 1;  --0,686 segundos

É impressionante o resultado que obtive, de 106 segundos gastos antes do índice, agora leva-se menos de 1 segundo, gastei décimos de segundo.

Vale lembrar que não basta apenas adicionar índices a torta e a direita, deve-se analisar quais as consultas serão feitas na tabela e quais os filtros mais utilizados, para ai sim, criar índices performáticos. Ter índices mal criados :-) é pior do que não ter, no meu exemplo, foi feito de forma simples para ser o mais didático possível.

Bom, qual a explicação para isso: quando é criada uma chave primária em sua tabela, por default também está sendo criado um índice do tipo clustered (num próximo post entro em maiores detalhes) quer dizer que, um índice desse tipo, organiza toda a sequência de gravação de registros nos arquivos físicos do SGBD. Tal organização, tem uma importância fundamental  para que se tenha uma boa performance de suas consultas, a explicação é que facilita bastante a localização física dos registros de suas tabelas.

Conclui-se que, se a tabela criada não possui nenhum tipo de chave primária e nenhum tipo de índice clustered, todos os seus registros estarão gravados em disco de forma sequencial, quer dizer, na mesma ordem em que os mesmos foram inseridos,  dificilmente tal ordem cronológica será a melhor forma de consultas em grandes tabelas.

Por hoje é só, espero que possa ter ajuda com tais dicas.

Documentação oficial Oracle:

Índices
<http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm>

Comentários