29 Abril 2016

SQL Performance: Índices

Como é que a performance pode ser melhorada com o uso correcto de indexes

Todos nós já nos deparamos com problemas de performance ao tentar fazer queries mais ou menos complexas. É normal que quando nos deparamos com estes problemas de performance a primeira ideia seja que faltam índices, mas será que é sempre assim? Se olharmos para a definição de índice: “lista de assuntos ou capítulos que geralmente aparece no início ou no fim de uma publicação contendo a indicação das páginas onde esses assuntos ou capítulos se iniciam”, provavelmente diríamos que sim, mas apesar de os índices em Base de Dados funcionarem da mesma forma que os índices dos livros, se não forem aplicados correctamente em vez de termos melhorias de performance podemos não ter qualquer ganhos ou mesmo diminuição de performance. Parece estranho? Vamos então perceber um pouco melhor como funcionam os índices.

Índices B-tree

Os índices B-Tree, como o próprio nome indica têm uma estrutura de árvore (balanceada), esta árvore possui uma lista de valores ordenada, dividida em intervalos, ou seja é uma excelente opção para a maior parte das queries que é efectuada, para se encontrar intervalos de valores ou até mesmo valores exactos. Este é o índice que é criado por default quando se executa o comando de “CREATE INDEX MY_INDEX_NAME ON MY_TABLE (PRIMARY_KEY)”. Podemos olhar para este tipo de índices como uma árvore invertida, composta por três elementos: raiz, ramos e folhas.

No topo da árvore fica a raiz, que está ligada a vários ramos, que por sua vez estão ligados a várias folhas. As folhas neste tipo de índice contêm o valor usado no índice, por exemplo a chave primária e o respectivo ROWID. Isto permite a localização directa da linha da tabela através do índice.

Diz-se que este tipo de índice é balanceado porque todos os blocos de folhas têm a mesma profundidade, isto faz com que para se obter qualquer registo dentro do índice o tempo será sempre aproximadamente o mesmo.

Um dos problemas da utilização destes índices, é que a alteração dos dados da tabela requer a manutenção do índice, o que é uma operação que pode ter um peso muito elevado, uma vez que este tipo de índice é ordenado, caso quiséssemos inserir uma nova entrada numa folha que já não possuísse espaço livre, teria de ser criada outra folha e redistribuir todas as entradas da folha anterior para a nova folha e criar uma nova entrada no ramo, que também poderia estar sem espaço e teria de se criar um novo ramo e actualizar todos os apontadores. Esta é uma das principais razões pelo qual se devem desabilitar todos os índices quando se estão a fazer processos de ETL, uma vez que a performance das operações de DML pode ser bastante afectada com esta manutenção dos índices. Contudo, se o índice estiver a ser aplicado numa sequência de números, como por exemplo chaves primárias, a manutenção deste tipo de índices é bastante reduzida, uma vez que os novos valores do índice serão sempre superiores aos já existentes, fazendo com que a árvore apenas cresça para a direita, não sendo necessário fazer reordenações.

Índices BITMAP

Os índices bitmaps são bastante diferentes dos índices b-tree. Estes índices criam uma estrutura bidimensional, onde cada linha representa todos os valores distintos dentro do índice e as colunas possuem todas as linhas na tabela. A seguinte imagem representa uma coluna indexada através de um índice bitmap:

Assim quando se tenta obter todos os valores “AAA”, a base de dados consegue rapidamente identificar quais são as linhas que possuem o valor e em poucos segundos retorna todos os valores pretendidos. Este tipo de índice, ao contrário do índice b-tree indexa também os valores null.

Em conclusão, este tipo de índice deve ser usado quando existe uma baixa cardinalidade na coluna que se quer indexar. Podemos avaliar a baixa cardinalidade se os valores distintos nessa coluna forem inferiores a 1% do número total de linhas que da tabela, mas se tivermos valores numa coluna que são repetidos mais de 100 vezes, também devemos avaliar se não deveremos usar um índice bitmap para essa coluna.

A actualização deste tipo de índice é muito pesada e causa grandes problemas de performance, por isso não se deve criar este tipo de índice em tabelas que estão constantemente a ser actualizadas.

Comparação b-tree vs bitmap

Para a comparação destes dois índices foi criada uma tabela “Employees” com apenas quatro colunas “EMPNO”, “ENAME”, “SAL” e “GENDER”:

Foi inserido um milhão de linhas (geradas automaticamente de forma aleatória, tendo em conta que o gender apenas tem registos “m”, “f” ou ”null”) para que se possa verificar de forma mais eficaz o efeito da utilização dos dois tipos de índices, numa tabela com poucos registos não se verificaria grandes diferenças.

O primeiro teste que vamos efectuar é a criação de um índice no campo gender. Para se conseguir fazer as comparações em paralelo foi criada uma tabela employees2 que é exactamente uma cópia da primeira, com os mesmos valores exactamente. Como foi visto anteriormente este campo é um campo com baixa cardinalidade pelo, que é um excelente candidato à criação de um índice bitmap, vamos verificar as diferenças que obtemos ao indexarmos este campo com um índice bitmap e um índice b-tree:

Vamos verificar o espaço ocupado por cada índice:

 

Como é possível verificar o índice bitmap ocupa muito menos espaço que o índice B-TREE, uma vez que este cria toda uma árvore, para indexar apenas 2 tipos de valores (sim, porque não indexa os valores null).

Vamos verificar como se comportam em termos de performance:

> Índice BITMAP:

 

> Indice B-TREE

Como é possivel verificar, conseguimos obter exactamente os mesmos resultados bastante mais rapidamente utilizando o indice bitmap, como a teoria acima mostrava. Como esta coluna tem uma reduzida cardinalidade, a informação fica mais facilmente acessivel através de uma tabela bidimensional do que de uma árvore balanceada e esta ocupa muito mais espaço a guardar a informação.

Agora vamos fazer o mesmo teste, mas utilizado a coluna ENAME.

Se verificarmos o espaço ocupado por cada indice, podemos verificar que aqui, o indice bitmap ocupa mais espaço:

 

Isto acontece devido ao facto de termos uma tabela bidimensional de 1000000 sobre 1000000 (uma vez que todos os registos na coluna ename são distintos).

Em termos de performance podemos ver que não existe grande diferença, contudo o b-tree é um pouco mais rápido nesta situação.

 

> Índice BITMAP:

> Indice B-TREE

Por último vamos testar a performance de operações de DML:

> Índice BITMAP

Foi executado um insert de 1 milhão de registos na tabela que possui o índice BITMAP e podemos verificar que demorou mais de 7 minutos para inserir todos os registos.

> Índice B-TREE

Foi efectuada a mesma inserção na tabela que possui o índice b-tree e pode-se verificar que a mesma inserção teve uma duração de 2 minutos.

Conclusão

Os índices podem melhorar a performance das queries, mas apenas quando utilizados correctamente. É importante avaliar-se sempre bem qual o tipo de índice que deve ser utilizado e fazer testes de performance para verificar se realmente existem ganhos significativos. Sem nos esquecermos que devemos também verificar a volatilidade das tabelas, uma vez que os índices provocam enorme peso nas operações de DML.

.

.

.

.

      Gonçalo Grilo
        Consultant