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!

*

Artigo original: http://www.informit.com/articles/article.aspx?p=377652