Otimização de Consultas MySQL - Parte 01

[Mário Monteiro 179]

Otimização de Consultas MySQL - Parte 01

 

Bom, galerinha, esta é a primeira parte de um conjunto de artigos sobre otimização de banco de dados MySQL. É grande, mas acho que a leitura vale a pena! As técnicas descritas podem ser usadas em outros SGBDs (Sistema Gerenciador de Banco de Dados), respeitando-se as peculiaridades de cada um. Existem várias técnicas para trazer ganho de performance ao banco de dados, mas vou me ater à otimização de queries. E na otimização de queries vou abordar as seguintes técnicas:

 

* indexação de tabelas, para permitir ao SGBD localizar dados mais rapidamente;

* levar em consideração a forma de escrita das consultas para tirar o máximo proveito dos índices criados e usar o comando EXPLAIN para verificar se o servidor está executando da forma como deveria;

 

É importante ressaltar que o MySQL já é bastante rápido, mas pode se tornar ainda mais se ele for ajudado a realizar suas operações. Neste primeiro artigo, falarei sobre indexação.

 

Uso de Índices

 

Indexação é a ferramenta mais importante para ter aumento de performance em consultas. Quando uma consulta demora a ser concluída, normalmente as tabelas envolvidas não possuem índices ou estes são mal criados. A adequação ou criação dos índices necessários resolve o problema na grande maioria das vezes. Claro que a criação de índices nem sempre resolve, pois a otimização de banco de dados nem sempre é simples. Entretanto, se você não usar índices, em muitos casos, estará desperdiçando seu tempo tentando aumentar a performance por outros meios. Use indexação como primeira alternativa para ter um ganho de performance e depois avalie que outras técnicas podem ser úteis.

 

Benefício dos Índices

 

Quando uma tabela não tem índices, os seus registros são desordenados e uma consulta terá que percorrer todos os registros. Se adicionarmos um índice, uma nova tabela é gerada. A quantidade de registros da tabela de índices é a mesma para a tabela original, com a diferença que os registros são ordenados. Isso implica que uma consulta "varre" a tabela para encontrar os registros que casem com a condição da consulta e a busca é cessada quando um valor imediatamente maior é encontrado.

 

E se eu ordenar a tabela, ao invés de criar índice? Dá certo? A resposta é sim, mas a performance dos índices é melhor porque o tamanho dos registros da tabela de índices é menor do que na tabela original.

 

O uso de índices pode ainda ser mais valioso em consultas envolvendo joins ou múltiplas tabelas. Numa consulta em uma tabela, o número de valores que precisa ser examinado por coluna é o número de registros da tabela. Em consultas em múltiplas tabelas, o número de possíveis combinações cresce meteoricamente, já que resulta do produto do número de registros das tabelas.

 

Suponha que existam três tabelas sem índices, t1, t2 e t3, cada uma contendo uma coluna, c1, c2 e c3, respectivamente, e cada coluna contendo 1.000 registros com dados de 1 a 1.000. A consulta para encontrar todas as combinações de registros que tenham o mesmo valor pode ser representada desta forma:

 

SELECT t1.c1, t2.c2, t3.c3

FROM t1, t2, t3

WHERE t1.c1 = t2.c2 AND t2.c2 = t3.c3;

 

O resultado dessa consulta deve ser uma tabela de 1.000 registros, cada um contendo três valores iguais. Se a consulta for executada sem o uso de índices, não teremos idéias de quais registros contêm quais dados sem percorrer todos eles. Conseqüentemente, o banco tenta todas as combinações possíveis para encontrar os registros que combinam com a condição da cláusula WHERE. O número de possíveis combinações é 1.000 x 1.000 x 1.000 = 1.000.000.000 (um bilhão!), que é um milhão de vezes maior do que o número de registros que combinaram com a condição. Ou seja, foi muito esforço desperdiçado. Com o uso de índices nas tabelas, melhora-se consideravelmente a velocidade, porque os índices permitem que consultas sejam processadas como segue:

 

1. Selecione o primeiro registro da tabela t1 e veja o valor que ele contém.

2. Usando o índice da tabela t2, vá diretamente para o registro que combine com o valor de t1. Da mesma forma, use o índice da tabela t3 para ir diretamente para o registro que combine com o valor de t2.

3. Vá para o próximo registro da tabela t1 e repita o procedimento anterior. Faça isto até que todas as linhas em t1 tenham sido examinadas.

 

Neste caso, ainda teremos uma varredura completa da tabela t1, mas nós podemos usar os índices nas tabelas t2 e t3 para encontrar registros diretamente. A consulta é executada aproximadamente um milhão de vezes mais rápido desta maneira, literalmente. Este exemplo é didático, mas os problemas que ele ilustra são reais, assim como o ganho de performance com a criação de índices.

 

MySQL usa índices de muitas maneiras:

 

* Aumentar a velocidade de pesquisa de registros que combinem com a condição da cláusula WHERE ou linhas que combinam com linhas de outras tabelas quando joins são usados.

* Para consultas que usam as funções MIN() e MAX(), o menor e o maior valores numa coluna indexada podem ser encontrados rapidamente.

* Realizar operações de ordenação e agrupamento rapidamente usando as cláusulas ORDER BY e GROUP BY.

* Ler todas as informações requeridas numa consulta. Isso acontece quando os campos retornados na consulta são os campos indexados na tabela.

 

Custos de Índices

 

Por enquanto, foram explanadas apenas aas vantagens dos índices. E as desvantagens? Sim, elas existem. Há custos de espaço e tempo. Na prática, as desvantagens tendem a ser minimizadas pelo valor das vantagens, mas devemos saber que elas existem.

 

Primeiramente, índices aumentam a velocidade de consultas, mas diminuem a velocidade de inserções, atualizações e deleções. Isto é, índices tornam mais lentas as operações de escrita. Isso ocorre porque escrever um registro requer não apenas escrevê-lo, mas também mudar a ordenação dos índices. Quanto mais índices houver numa tabela, mais mudanças de ordenação necessitam ser feitas, degradando a performance.

 

Em segundo lugar, um índice ocupa espaço de disco. Isso pode fazer a tabela exceder o seu tamanho limite mais rapidamente do que se não tivesse índices. Para entender, vamos ver o seguinte:

 

Os detalhes da implementação de índices em bancos de dados MySQL variam de acordo com os tipos de tabelas usadas. Numa tabela MyISAM, por exemplo, os dados são mantidos em um arquivo de dados e os índices são mantidos em arquivos de índices. Pode ter mais de um índice numa tabela, mas eles serão todos armazenados num mesmo arquivo de índices. Cada índice do arquivo de índices consiste de um conjunto de chaves de registros ordenados que podem ser usados para localizar dados de forma rápida.

 

Em contrapartida, os tipos de tabelas BDB e InnoDB não separam dados e índices da mesma maneira. Por padrão, o tipo BDB usa um simples arquivo por tabela para armazenar tanto dados quanto índices. O tipo InnoDb usa uma simples tablespace dentro da qual é gerenciado o armazenamento de dados e índices de todas as tabelas InnoDB. O InnoDB pode ser configurado para criar cada tabela em seu próprio tablespace, mas de qualquer maneira os dados e os índices são armazenados no mesmo tablespace.

 

Isso faz com que:

 

* Para tabelas MyISAM, o espaço ocupado por índices é ainda mais preocupante, pois faz com que o arquivo de índices alcance o seu tamanho máximo mais rapidamente do que o arquivo de dados.

* Para tabelas BDB, onde os dados são armazenados no mesmo arquivo que os índices, a tabela pode alcançar o seu tamanho máximo mais rapidamente.

* Todas as tabelas InnoDB são alocadas dentro de tablespaces compartilhadas que disputam um espaço em disco comum. Entretanto, diferentemente dos arquivos usados pelas tabelas MyISAM e BDB, os tablespaces InnoDB não são controlados pelo limite de tamanho de arquivo do sistema operacional, porque ele pode ser configurado para múltiplos arquivos. Quão longo for o espaço em disco disponível, podemos expandir as tablespaces pela adição de novos componentes a ela.

 

Tabelas InnoDB que usam tablespaces individuais funcionam da mesma maneira que as tabelas BDB.

 

A Escolha de Índices

 

Índices devem ser criados em colunas que usamos para pesquisa, ordenação ou agrupamento. Nunca em colunas que só são exibidas. Em outras palavras, as colunas candidatas são aquelas que aparecem na cláusula WHERE, joins, ORDER BY ou GROUP BY.

 

O fato de uma coluna aparecer na lista de colunas que serão exibidas num SELECT não a descarta de ser uma coluna candidata, pois ela pode estar na listagem, mas também estar na cláusula WHERE, por exemplo.

 

Colunas que aparecem em cláusulas join ou em expressões como col1 = col2 nas cláusulas WHERE são candidatas fortíssimas à criação de índice.

 

Considere a cardinalidade da coluna. A cardinalidade da coluna é o número de valores distintos que ela contém. Índices funcionam melhor em colunas com um alto número de cardinalidade relativa ao número de registros da tabela, isto é, colunas que têm muitos valores únicos e poucos duplicados. Se uma coluna contém valores muito diferentes de idade, um índice irá diferenciar os registros rapidamente. Entretanto, não irá ajudar numa coluna que é usada para armazenar registros de gênero (sexo) e contém somente os valores 'M' ou 'F'. Se os registros têm aproximadamente o mesmo número de homens e mulheres, o índice percorrerá aproximadamente metade dos registros, qualquer que seja o valor buscado.

 

Sob estas circunstâncias, o índice pode nunca ser utilizado, pois o otimizador de consultas dará prioridade à varredura completa dos registros, se ele determinar que o valor ocorre numa grande porcentagem dos registros da tabela. A porcentagem que era usada como critério é de aproximadamente 30%. Hoje em dia, o otimizador é mais complexo e leva outros fatores em consideração, então a porcentagem não é o fator determinante para a escolha de uma varredura usando índices.

 

Crie índices com valores pequenos. Use tipos de dados o menor possível. Por exemplo, não use uma coluna BIGINT se MEDIUMINT suporta os dados que serão armazenados. Não use CHAR(100) se nenhum dos valores armazenados ultrapassa 25 caracteres. Valores pequenos melhoram o processamento de índices de muitas maneiras:

 

* Podem ser comparados mais rapidamente.

* Ocupam menos espaço de disco nos arquivos de índices.

* É possível a permanência de mais registros em cache, fazendo com que o servidor tenha que acessar menos o disco rígido.

 

Para as tabelas BDB e InnoDB que usam índices agrupados, é especialmente benéfico manter as chaves primárias curtas. O índice agrupado é aquele no qual os registros de dados são armazenados juntos (ou seja, agrupados) os valores das chaves primárias.

 

Não crie índices em exceço. Não se deve indexar colunas baseado no conceito de que quanto mais, melhor. Isso é um erro. Todo índice adicional ocupa espaço em disco e diminui a performance de operações de escrita, como já foi dito. Índices devem ser atualizados e reorganizados quando modificamos o conteúdo de uma tabela e, quanto mais índices, mais demorada será a atualização e ordenação. Se temos um índice que é raramente ou nunca utilizado, estamos diminuindo a performance da tabela desnecessariamente. Além disso, índices desnecessários podem fazer o otimizador de consultas não escolher o melhor índice a ser usado. Mantendo somente os índices que realmente necessitamos, ajudamos o otimizador a fazer as escolhas certas e a cometer menos erros.

 

Índices parciais

 

Quando indexamos uma coluna que seja string, devemos especificar um tamanho de prefixo para a criação deste índice. Por exemplo, se temos uma coluna CHAR(100), não devemos indexar a coluna inteira se temos valores únicos dentro dos primeiros 10 ou 20 caracteres. Devemos criar um índice parcial de tamanho de prefixo de 20 caracteres. Além de economizar espaço em disco, tornará mais rápidas as consultas e operações de escrita. O tamanho do prefixo deve ser escolhido com bom senso. De nada adianta criar um índice parcial de tamanho 1.

 

Os índices parciais podem ser criados em colunas com os tipos CHAR, VARCHAR, BINARY, VARBINARY, BLOB e TEXT.

 

No próximo artigo vou falar sobre o Otimizador de Consultas MySQL.

 

Abraços!

 

Fonte: http://imasters.com.br/artigo/14624/mysql/otimizacao_de_consultas_mysql_parte_01/