O mundo da teoria de banco de dados relacionais é dominado por tabelas e conjuntos, e operações em tabelas e conjuntos. Um banco de dados é um conjunto de tabelas, e uma tabela é um conjunto de linhas e colunas. Quando emitimos uma declaração SELECT para recuperar linhas de uma tabela, recebemos outro conjunto de linhas e colunas – isto é, outra tabela. Estas são noções abstratas que não fazem nenhuma referência quanto à representação subjacente que um de sistema de banco de dados usa para operar nos dados e nas tabelas. Outra abstração consiste em que operações em tabelas acontecem todas de uma vez. As consultas são conceituadas como operações de conjuntos e não existe nenhum conceito de tempo na teoria de conjuntos.
O mundo real, é claro, é bastante diferente. Sistemas de gerenciamento de banco de dados implementam conceitos abstratos, mas o fazem em sistemas de hardware reais limitados por restrições físicas reais. Como resultado, as consultas tomam tempo - às vezes um longo e irritante período de tempo. Como somos criaturas impacientes, não gostamos de esperar, deixamos o mundo abstrato de operações matemáticas instantâneas em conjuntos e procuramos por modos de acelerar nossas consultas. Felizmente, existem várias técnicas para atingir este objetivo:
- indexar tabelas para permitir que o servidor de banco de dados pesquise as linhas mais rapidamente;
- considerar como escrever consultas para tirar proveito desses índices extensivamente, e usar a declaração EXPLAIN para conferir se o servidor MySQL realmente o está fazendo;
- escrever consultas para influir no mecanismo de escalonamento do servidor (server's scheduling mechanism) de forma que consultas efetuadas por múltiplos clientes cooperem melhor entre si;
- modificar os parâmetros de operação do servidor para atingir um desempenho mais eficiente. Pensar no que acontece com o hardware subjacente e como contornar suas restrições físicas para obter melhor desempenho.
Estes são os tipos de assuntos que iremos focalizar neste artigo. O objetivo é auxiliá-lo no aperfeiçoamento do desempenho do sistema de banco de dados, de forma que venha a processar as consultas o mais rápido possível.O SGBD que utilizaremos será o MySQL.
Indexação
Indexação é a ferramenta mais importante que temos para acelerar consultas. Temos outras técnicas a nossa disposição, mas geralmente o que faz realmente a diferença é o uso de índices. Na lista de e-mail do MySQL, as pessoas pedem freqüentemente ajuda para fazer com que uma consulta rode mais rapidamente. Em um número surpreendentemente de casos, não existe nenhum índice para as tabelas em consulta, e geralmente, a adição de índices resolve o problema imediatamente. Não é sempre que trabalho assim, porque nem sempre a otimização é tão simples de se obter. Entretanto, se não usarmos índices, em muitos casos estaremos desperdiçando nosso tempo na tentativa de melhorar o desempenho através de outros meios. Usamos a indexação para melhorar o desempenho e então vemos que outras técnicas poderão ser úteis neste sentido.
Este artigo descreve o que é um índice e como a indexação melhora o desempenho das consultas. Também discutiremos as circunstâncias sob as quais os índices poderiam degradar o desempenho e como fornecer diretrizes para a mais sabia escolha de índices para a tabela em consulta.
Benefícios da indexação
Veremos como os índices trabalham começando com uma tabela que não tem nenhum índice. Uma tabela não indexada é simplesmente uma coleção desordenada de linhas. Por exemplo, a Figura 1 mostra a tabela “ad”. Não há nenhum índice nesta tabela, portanto, para pesquisar as linhas para uma companhia em particular, é necessário examinar cada linha da tabela e ver se casa com o valor desejado. Isto envolve a varredura completa de uma tabela, o que é bem lento além de tremendamente ineficiente, caso a tabela for grande e contiver apenas alguns registros que casam com os critérios de pesquisa.
A Figura 2 mostra a mesma tabela acrescida de um índice na coluna company_num. O índice contém uma entrada para cada linha da tabela, mas as entradas de índice são ordenadas pelo valor de company_num. Agora, em vez de procurar linha a linha por itens casados, podemos usar o índice. Vamos supor que estamos procurando todas as linhas para a companhia 13. Começamos a varrer o índice e achamos três valores para esta companhia. Então atingimos o valor de índice para a companhia 14, que é maior do que estamos procurando. Os valores de índice estão ordenados, assim, quando lemos o registro de índice que contém 14, sabemos que não acharemos nenhum outro casamento e podemos deixar de procurar. Assim, o ganho de eficiência obtido com o uso do índice consiste em poder determinar precisamente onde as linhas casadas acabam e ignorarmos o resto das linhas. Outro ganho de eficiência é obtido pelo uso de algoritmos de posicionamento para achar a primeira entrada casada, sem ter que fazer uma varredura linear desde o início do índice (por exemplo, uma procura binária é muito mais rápido que uma varredura). Deste modo, podemos posicionar rapidamente no primeiro valor casado e eliminar muito tempo de procura. Bancos de dados usam várias técnicas para o posicionamento rápido por valores de índices, mas neste artigo não nos aprofundaremos nessas técnicas. O importante é que elas funcionam e que a indexação é uma coisa boa.
Figura 1. Tabela ad não indexada.
Figura 2. Tabela ad indexada.
Poderíamos querer saber por que não ordenamos as linhas de dados e dispensamos o índice. Não iremos produzir o mesmo tipo de melhoria em velocidade de pesquisa? Sim, isto seria verdade caso a tabela tivesse um único índice. Mas poderíamos querer acrescentar um segundo índice e, neste caso, não conseguiríamos ordenar os dados das linhas de dois modos diferentes ao mesmo tempo (por exemplo, poderíamos querer um índice por nome do cliente e outro por numero de ID do cliente ou pelo número de telefone). Usar índices como entidades separadas das linhas de dados resolve o problema e permite criar índices múltiplos. Além disso, as linhas de índice são geralmente mais curtas do que as linhas de dados. Quando inserimos ou apagamos novos valores, é mais fácil movermos valores mais curtos de índice para manter a ordenação do que mudar as linhas de dados, que são mais longas.
Os detalhes particulares de implementações de índice variam no MySQL para diferentes storage engines. Por exemplo, para uma tabela MyISAM, as linhas de dados da tabela são mantidas em um único arquivo de dados, e valores de índice são mantidos em um único arquivo de índice. Podemos ter mais de um índice em uma tabela, mas serão todos armazenados no mesmo arquivo de índices. Cada índice no arquivo de índices consiste em um arranjo ordenado de chaves de registro que são usados para acesso rápido ao arquivo de dados.
Por outro lado, os storage engines do BDB e do InnoDB não separam linhas de dados e valores de índice da mesma maneira, embora ambos mantenham índices como conjuntos de valores ordenados. Por padrão, o motor de armazenamento do BDB usa um único arquivo por tabela para armazenar dados e valores de índice. O motor do InnoDB usa um único tablespace dentro do qual gerencia o armazenamento de dados e índices para todas as tabelas. O InnoDB pode ser configurado para criar cada tabela com seu próprio tablespace, mas mesmo assim, os dados e índices são armazenados em uma tabela no mesmo arquivo tablespace.
Esta análise descreve os benefícios conseguidos com um índice no contexto de consultas a tabela única, onde o uso de um índice acelera significativamente as procuras eliminando a necessidade de varredura completa da tabela. Índices são realmente mais valiosos quando há consultas envolvendo junções com múltiplas tabelas. Em uma consulta a uma tabela única, o número de valores que precisamos examinar por coluna corresponde ao número de linhas na tabela. Em uma consulta de múltiplas tabelas, o número de possíveis combinações cresce em grandes proporções (representado pelo produto do número de linhas de cada tabela).
Supondo que temos três tabelas não indexadas, t1, t2, e t3, cada uma contendo uma coluna, i1, i2 e i3 respectivamente, e cada uma contendo 1.000 linhas com os números 1 até 1000. Uma consulta para achar todas as combinações de linhas de tabela nas quais os valores sejam iguais, terá a seguinte forma:
SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;
O resultado desta consulta deveria ser 1.000 linhas, cada uma contendo três valores iguais. Se processarmos a consulta na ausência de índices, não teremos nenhuma idéia sobre quais linhas contêm quais valores sem fazer uma varredura total. Como conseqüência, temos que tentar todas as combinações para achar aquelas que casam com a cláusula WHERE. O número de possíveis combinações é 1.000 x 1.000 x 1.000 (um bilhão!) que equivale a um milhão de vezes mais do que o número de casamentos retornados. Isto representa muito esforço perdido. O exemplo ilustra como à medida que as tabelas crescem, o tempo para processar junções com essas tabelas cresce ainda mais se nenhum índice for usado, conduzindo a um desempenho muito fraco. Podemos acelerar as coisas consideravelmente indexando as tabelas porque os índices nos permitem processar a consulta deste modo:
- selecionar a primeira linha da tabela t1 e ver o valor contido na linha.
- usar o índice da tabela t2 e ir diretamente para a linha que casa com valor da tabela t1. Da mesma forma, usar o índice da tabela t3 e ir diretamente para a linha que casa com o valor da tabela t2.
- continuar com a próxima linha da tabela t1 e repetir o procedimento anterior. Repetir estes procedimentos até que todas as linhas da tabela t1 sejam examinadas.
Neste caso, ainda executamos uma varredura completa na tabela t1, mas podemos fazer procuras indexadas nas tabelas t2 e t3 para extrair diretamente as linhas dessas tabelas. A consulta roda aproximadamente um milhão de vezes mais rápido deste modo, literalmente. Este exemplo foi construído com a finalidade de fazer uma observação, é claro. Entretanto, os problemas que o mesmo ilustra são reais, e acrescentar índices a tabelas que não têm nenhum, freqüentemente resulta em consideráveis ganhos de desempenho.
O MySQL usa índices de vários modos:
- como foi descrito há pouco, índices são usados para acelerar procuras por linhas que casam com condições de uma cláusula WHERE ou por linhas que casam com linhas de outras tabelas, quando uma junção é executada;
- para consultas que usam as funções MIN () ou MAX (), o valor menor ou maior em uma coluna pode ser achado rapidamente sem necessidade de examinar todas as linhas;
- MySQL pode usar freqüentemente índices para executar rapidamente operações de ordenação e de agrupamento para cláusulas ORDER By e GROUP BY;
- às vezes, o MySQL pode usar um índice para ler toda a informação necessária para uma consulta. Suponha que estamos selecionando valores de uma coluna numérica indexada em uma tabela MyISAM, e não estamos selecionando outras colunas da tabela. Neste caso, quando o MySQL for ler um valor de índice do arquivo de índices, obterá o mesmo valor que seria obtido ao ler o arquivo de dados. Não há nenhuma razão para ler os valores duas vezes, assim o arquivo de dados não precisa ser consultado.
Custos da indexação
Podemos ver que estamos pintando um quadro “cor de rosa" quanto aos benefícios da indexação. Há desvantagens? Sim, há. Há custos envolvidos de tempo e de espaço. Na prática, estas desvantagens tendem a ser superadas pelas vantagens, mas devemos saber de que custos estamos falando.
Em primeiro lugar, índices aumentam a velocidade de recuperação, porém reduzem a velocidade de inserções e exclusões, assim como atualizações de valores em colunas indexadas. Isto quer dizer que índices tornam mais lentas a maioria das operações que envolvem escrita. Isto acontece porque a gravação de um registro não só requer escrita das linhas de dados, mas também implica em mudanças nos índices. Quanto mais índices uma tabela tem, mais alterações serão realizadas e maior será a degradação média de desempenho.
Em segundo lugar, um índice ocupa espaço em disco. Isto poderia fazer com que o limite de tamanho de tabela seja alcançado mais rapidamente do que se não houver nenhum índice:
- indexar pesadamente uma tabela MyISAM pode fazer com que o arquivo de índices alcance o tamanho máximo mais rapidamente que o arquivo de dados;
- para tabelas BDB que armazenam dados e valores de índice no mesmo arquivo, o acréscimo de índices também pode fazer com que o arquivo de índice alcance o tamanho máximo mais rapidamente;
- todas as tabelas InnoDB que estão situadas dentro do tablespace compartilhado, competem pelo mesmo espaço comum, e o acréscimo de índices esgota a área de armazenamento mais rapidamente dentro deste tablespace. Porém, diferentemente dos arquivos usados pelo MyISAM e pelas tabelas BDB, no InnoDB o tablespace compartilhado não é restringido pelo limite de tamanho do arquivo do sistema operacional, porque pode ser configurado para usar arquivos múltiplos. Contanto que haja espaço em disco adicional, o tablespace pode ser ampliado pelo acréscimo de novos componentes. Tabelas InnoDB que usam tablespaces individuais têm restrições do mesmo modo que tabelas BDB porque dados e valores de índice são armazenados juntos em um único arquivo.
A implicação prática destes fatores é que se não houver necessidade de um índice particular, então não devemos criá-lo.
Escolhendo índices
O conhecimento da sintaxe de criação de índices e tabelas não é suficiente para ajudar a determinar como as tabelas deveriam ser indexadas. Isto requer alguma análise sobre como as tabelas são usadas. Esta seção dá algumas diretrizes sobre como identificar colunas candidatas para índices e a melhor forma de montar estes índices.
Indexe colunas usadas para procurar, ordenar ou agrupar, e não colunas que serão apenas exibidas como saída.
Em outras palavras, as melhores colunas candidatas para índices são aquelas que usam a cláusula WHERE, colunas referenciadas em cláusulas de junção ou colunas referenciadas em cláusulas ORDER BY e GROUP BY. Colunas que só são referenciadas na lista de colunas de saída, após palavra chave SELECT, não são boas candidatas:
SELECT
col_a <- não é candidata
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- candidata
WHERE
col_d = expr;<- a candidate
Dessa forma, colunas que são referenciadas em cláusulas de junção ou em expressões da forma col1 = col2 em cláusulas WHERE são candidatas especialmente boas para indexação. Na consulta mostrada, col_b e col_c são exemplos desta situação. Se o MySQL puder aperfeiçoar uma consulta que usa colunas de junção, reduzirá bastante as combinações potenciais de tabela / linha, eliminando a varredura completa da tabela.
Considere a cardinalidade da coluna
A cardinalidade de uma coluna é o número de valores distintos que contém. Por exemplo, uma coluna que contém os valores 1, 3, 7, 4, 7 e 3, tem cardinalidade quatro. Índices funcionam melhor para colunas que têm uma cardinalidade relativamente alta em relação ao número de linhas da tabela (quer dizer, colunas que têm muitos valores distintos e poucos valores duplicados). Se uma coluna contiver muitos valores de idade diferentes, um índice diferenciará linhas prontamente. Por outro lado, um índice não será de ajuda para uma coluna que é usada para registrar sexo e só contém os valores 'M' e 'F'. Se os valores ocorrem aproximadamente com a mesma freqüência, obteremos aproximadamente metade das linhas qualquer que seja o valor procurado. Dadas estas circunstâncias, o índice poderia nunca ser usado, porque o otimizador de consulta geralmente ignora um índice em favor de uma varredura completa da tabela quando determinar que certo valor aconteça em uma porcentagem grande das linhas de uma tabela. O valor convencional para esta porcentagem era de 30%. Hoje em dia o otimizador é mais complexo e leva em conta outros fatores, assim a porcentagem não é a única determinante para que o MySQL prefira fazer uma varredura em lugar de usar um índice.
...
