Otimização de performance é, segundo Craig Mullins, a “otimização no uso de recursos para aumentar o throughput e minimizar a contenção, possibilitando que uma maior carga de trabalho seja processada”. Ela deve ser realizado tendo em vista o sistema como um todo. Não podemos, por exemplo, incluir uma nova coluna no início de um índice já existente apenas para otimizar o acesso de uma aplicação. Este índice pode estar sendo utilizado por outras aplicações ou pelo próprio SGBDR para garantir a integridade referencial. Estaríamos resolvendo um problema e criando outros.

São vários fatores que influenciam a otimização de um sistema que utiliza um banco de dados relacional. Alguns dos mais importantes são:

  • Sistema Operacional – O ajuste de parâmetros do sistema operacional pode influenciar diretamente na performance. Um erro de parametrização nele pode invalidar qualquer esforço de melhoria de performance nos outros níveis.
  • Sistema Gerenciador de Banco de Dados Relacional (SGBDR) – Existem dezenas de parâmetros de configuração como áreas de buffers, organização do logging, alocação de arquivos do sistema, entre outros.
  • Rede – Em sistemas on-line, a rede pode ter impacto na performance do sistema. Independente da arquitetura da aplicação (client-server, internet/intranet/extranet, etc), existem formas de concentrar o processamento no lado servidor ou cliente, para minimizar o impacto gerado pelo tráfego de dados na rede.
  • Modelo Físico de Dados – define as estruturas de tabelas e índices que contêm os dados de um determinado sistema.
  • Programa da Aplicação – A lógica desenvolvida pelo programador composta pela linguagem hospedeira (Java, Cobol, C, etc) mais os comandos SQL de acesso ao SGBDR.

Este artigo abordará a otimização de performance envolvendo apenas o “Modelo de Dados Físico”. Entretanto, as considerações feitas aqui se aplicam à maioria dos sistemas que utilizam SQL e são independentes de plataforma, gerenciador ou arquitetura de rede envolvida.

De quem é a culpa pelos problemas de Performance?

A maior parte dos problemas envolvendo a performance de aplicações que utilizam SQL está relacionada a erros no Modelo de Dados Físico (25%) e a própria aplicação (65%). Confira os números do gráfico da Figura 1.

Origem de Problemas de Performance
Figura 1. Origem de Problemas de Performance.

Fonte: DB2 Developer’s Guide, Craig Mullins, 4ª edição.

Ficou surpreso? Noventa por cento dos problemas envolvem diretamente a equipe de desenvolvimento! Problemas no “Modelo de Dados Físico”, apesar de ocorrer com freqüência inferior aos da “Aplicação”, podem ser muito mais difíceis de serem corrigidos. Pode ser necessário, por exemplo, alterar vários programas para estes se adequarem a uma única alteração do modelo físico.

Otimizando o Modelo de Dados Físico

Neste contexto, o fator que mais influencia na performance de aplicações SQL é a criação dos índices. A questão é: quais colunas que serão utilizadas em índices e que índices devem ser criados? O único índice obrigatório é a chave primária. Em alguns SGBDs, o índice único é criado implicitamente ao definir-se a chave primária.

Mas antes de responder completamente essa questão, é preciso ter consciência que índice representa custo. Não adianta sair criando índices sem utilizar critérios. O principal custo do índice refere-se à sua atualização. Qualquer operação de inclusão (comando INSERT ou utilitário LOAD/IMPORT) fará com que todos os índices da tabela sejam atualizados com os ponteiros para as novas linhas. Qualquer operação de deleção (Comando DELETE) também afetará todos os índices da tabela. Já na atualização (Comando UPDATE), apenas os índices que possuem a coluna que está sendo atualizada sofrerão alteração. Por estes motivos vale a pena pensar duas vezes antes de criar um índice em uma coluna que é constantemente atualizada.

Assim, existe uma série de fatores que influenciam na melhora de desempenho em modelos de dados físicos e portanto, não podemos definir “regras de ouro”. Sempre existirá alguma situação em que uma determinada regra não se aplica. Por isso se diz que a resposta padrão para qualquer pergunta de performance é “depende”. No entanto, contrariando este princípio, apresentarei abaixo algumas regras que podem ser consideradas no trabalho de definição do modelo de dados físico.

Regra 1. Crie um índice para cada chave estrangeira

Os índices de chave estrangeira são muito utilizados nas operações de junção de tabelas. Normalmente, os joins são realizados igualando as colunas da chave estrangeira com as colunas da chave primária. Caso o gerenciador inicie a resolução do join pela tabela pai, ao acessar a tabela filha, o índice da chave estrangeira será utilizado.

O índice de chave estrangeira também será utilizado pelo próprio gerenciador na hora de garantir a integridade referencial entre tabelas. Por exemplo, caso você esteja deletando linhas na tabela pai, o gerenciador irá verificar a existência de linhas na tabela filho para proibir a deleção (caso tenha especificado ON DELETE RESTRICT no relacionamento) ou para efetuar a deleção nas linhas filhas (caso tenha especificado ON DELETE CASCADE).

Assim, se alguém falar para você que a integridade referencial deixa o sistema lento, certamente o problema será a ausência dos índices corretos nas chaves estrangeiras.

Porem, não é necessário criar o índice de chave estrangeira nas situações em que o próprio índice da chave primária possui as colunas da chave estrangeira nas posições iniciais. Por exemplo, considere a tabela de “Empregados” que possui como chave primária a coluna “Matricula” e a tabela de “Dependentes de Empregados” com a chave primária composta pelas colunas “Matricula” e “Sequencial_Dependente”. Neste caso o índice da chave primária já está resolvendo o acesso pela chave estrangeira, não sendo necessário criar um índice adicional.

Regra 2. Em tabelas associativas, além do índice primário, crie um outro índice único invertendo a ordem das chaves das tabelas pais.

Uma tabela associativa é aquela projetada para manter relacionamentos entre duas tabelas quando a cardinalidade entre elas é de N para N. A chave primária de uma tabela associativa é formada pela composição das chaves primária das tabelas pais. Considere o exemplo abaixo:


            TAB1 (C1, C2, C3)

            TAB2 (C8, C9, C10)

            TAB3 (C1, C2, C8, C5) // Associativa
        

Neste caso, lembre-se de criar também um índice único invertido nas colunas (C8, C1, C2) da tabela associativa TAB3. Este índice será utilizado nos joins nas situações em que o filtro esteja na tabela TAB2. Veja um exemplo abaixo:


            SELECT C3, C9

            FROM TAB1 T1, TAB2 T2, TAB3 T3
     
            WHERE T1.C1 = T3.C1
     
                      AND T1.C2 = T3.C2
     
                      AND T2.C8 = T3.C8
     
                      AND T2.C10 = ‘X’
        

No comando acima, o único predicado de filtro encontra-se na tabela TAB2. Imagine que exista um índice na coluna C10. Este índice será utilizado para filtrar as linhas com valor igual a ‘X’. Na seqüência, o índice sugerido da tabela associativa (TAB3) será utilizado e, por último, o índice da chave primária da tabela TAB1. Perceba que todas as operações foram efetuadas com auxílio de índices o que melhora o desempenho da consulta.

Regra 3. Defina o índice como único para atender à sua regra de negócio.

Além do seu importante papel para a performance do sistema, o índice é o recurso utilizado pelo SGBDR para garantir a unicidade de dados. Caso exista uma regra de negócio que defina a unicidade em uma ou mais colunas, defina um índice como único para atender a esta regra de negócio. A definição do índice como único irá fazer com que o espaço alocado seja menor do que o mesmo índice declarado como permitindo duplicidade.

Sempre é melhor declarar as regras de integridade de dados no gerenciador do que deixá-las nas aplicações. Ultimamente têm aparecido alguns “fanáticos” de Applications Servers que pregam que a integridade de dados deve ser garantida pelo servidor de aplicação, ao invés da sua declaração no gerenciador de dados. Desconsidere estas sugestões.

Regra 4. Crie índice para evitar operações de SORT de muitas linhas.

Em algumas situações um índice pode evitar a ordenação de valores realizada pelo SGBD. Por exemplo, caso você codifique um SELECT com ORDER BY por uma coluna que possui índice, ao invés do SGBDR realizar um SORT, ele poderá utilizar este índice que já possui a ordenação desejada. Além do “ORDER BY”

as cláusulas “GROUP BY” e “DISTINCT” também podem disparar SORT, podendo também ser beneficiadas pela criação do índice. Considere esta recomendação em situações onde são selecionadas muitas linhas.

Regra 5. Defina um dos índices como CLUSTER.

O índice CLUSTER é o que irá indicar para o SGBDR a posição onde a linha deverá ser inserida em uma tabela. No geral, define-se como CLUSTER o próprio índice primário pois ele é a principal porta de acesso em operações de junção. Acessos feitos apenas ao índice ("INDEX ONLY") ou acessos que retornam poucas linhas não se beneficiam da definição CLUSTER de um índice. A grande vantagem do acesso pelo índice CLUSTER é nas situações em que o SELECT retorna muitas linhas e é necessário acessar colunas da tabela que não fazem parte deste índice (os dados da tabela estarão organizados na mesma ordem do índice cluster).

Após algum tempo a clusterização dos dados é prejudicada pelas operações de atualização (INSERT, UPDATE, DELETE, LOAD/IMPORT). Neste caso é importante executar periodicamente os utilitários de reorganização. A definição de espaço livre da página em tabelas com muita inserção é um importante parâmetro para diminuir a freqüência de reorganização.

Regra 6. Crie chave primária com múltiplas colunas em tabelas que dependam existencialmente de outras (Entidade Fraca).

É muito comum, em tempos de “orientação a objeto”, a proliferação de colunas com incremento automático (SEQUENCE ou IDENTITY) ou manual (a aplicação de inserção obtém o último valor gerado e soma mais um antes de inserir). Em algumas situações, este tipo de coluna pode e deve ser utilizado. No entanto, é muito comum encontrar exemplos de mau uso.

Para exemplificar, vamos considerar as tabelas Contrato, Parcela (um contrato pode ter várias parcelas) e Parâmetro da Parcela (uma parcela pode ter vários parâmetros). Note que a partir da própria definição, fica clara a dependência existencial entre as entidades pai (Parcela) e filha (Parâmetro de Parcela).

As três tabelas do exemplo, utilizando a abordagem de coluna com incremento (Opção I), seriam modeladas da seguinte forma:


            Contrato (ID_Contrato, C1, C2)

            Parcela (ID_Parcela, P1, P2, FK_Contrato)
     
            Parm_Parcela (ID_ParmParcela, PP1, PP2, FK_Parcela)
        

Na Opção II, utilizando a modelagem sugerida na regra, teríamos as seguintes tabelas (note que a chave primária da tabela de Contrato utiliza a coluna de incremento).


            Contrato (ID_Contrato, C1, C2)

       Parcela (FK_Contrato, SQ_Parcela, P1, P2)

       Parm_Parcela (FK_Contrato, FK_Parcela, SQ_ParmParcela, PP1, PP2)
        

São muitas as vantagens da Opção II em relação à Opção I:

  • Na Opção II o índice da chave primária já resolve a necessidade de índice da chave estrangeira, pois a chave estrangeira é a parte inicial da chave primária. Na opção I, além do índice da chave primária é necessário criar um índice para a chave estrangeira. Esta economia de índice se traduz em menos espaço em disco e melhor performance de atualização. Em tabelas de milhões de linhas, este é um fator muito importante.
  • A Opção II minimiza a necessidade de joins. Por exemplo, para saber os parâmetros de uma parcela de um determinado contrato, na Opção I é necessário fazer uma junção de 3 tabelas, já na Opção II basta acessar uma única.
  • A clusterização dos dados das tabelas dependentes na Opção II pode seguir uma ordem bem melhor do que na Opção I. Por exemplo, após a reorganização dos dados, podemos garantir que a tabela de parâmetro de parcelas de contrato estará organizada por contratos, parcelas e parâmetros, nesta ordem. Esta organização é impossível de ser feita na Opção I (a não ser que seja feita a redundância da coluna de contrato na tabela de parâmetros de parcela – que acabaria sendo pior). A clusterização é muito importante em comandos em que é necessário acessar índice, dados e retornar muitas linhas.
  • Na Opção I existem inúmeros pontos onde é necessário criar novos números (incrementos). Na Opção II, um novo número é criado apenas para os novos contratos. Na Opção I é necessário criar o incremento também para cada parcela do contrato e para cada parâmetro de uma parcela. Note que muitos mais números são gerados na Opção I do que na Opção II. Em um ambiente com milhares de usuários on-line esta configuração da Opção I pode gerar problemas de contenção.

Conclusão

A correta definição dos índices é um fator determinante para o sucesso do projeto. Acredito que a utilização das seis regras apresentadas pode prevenir o aparecimento de muitos problemas de performance. Por fim, vale atentar para o uso de ferramentas CASE na modelagem de dados. Muitas destas geram índices desnecessários.

Revista SQL Magazine Edição 9
Clique aqui para ler todos os artigos desta edição