Dando continuidade à série de artigos sobre Otimização de Consultas MySQL, abordaremos hoje dois assuntos: a escolha de tipos de dados e a eficiência de consultas, finalizando a série.

Escolha de tipos de dados e eficiência de consultas

Veja algumas diretrizes sobre como a escolha de tipos de dados pode ajudar a execução de consultas mais rapidamente.

01. Não use colunas grandes com dados pequenos. Se você está usando colunas de tamanho fixo, como CHAR, não especifique um tamanho grande desnecessariamente. Se o valor mais longo de um dado numa determinada coluna é de 40 caracteres, não defina como CHAR(255). Defina como CHAR(40). Isto faz a tabela ocupar menos espaço em disco e os valores são processados mais rapidamente. Além disso, se esta coluna é indexada, procure utilizar índice parcial, quando possível, pois a quantidade de registros em memória cache aumentará, diminuindo o acesso à memória principal.

02. Escolha um tipo de dado que seja adequado ao tipo de tabela que será usado. Para tabelas MyISAM, use colunas de tamanho fixo ao invés de colunas de tamanho variável, ou seja, prefira CHAR à VARCHAR. Isto ocupará mais espaço em disco, mas se você dispõe desse espaço extra, colunas de tamanho fixo são processadas mais rapidamente, especialmente para tabelas que recebem muitas modificações.

- com colunas de tamanho variável, você terá mais fragmentação da tabela com um número excessivo de atualizações e exclusões. Isso fará com que o comando OPTIMIZE TABLE seja necessário periodicamente.

- com colunas de tamanho fixo, a reconstrução da tabela é mais fácil em caso de falha. Isto acontece porque a posição das linhas é localizada pelos múltiplos do tamanho do registro. Isto trás rapidez nos processos de reparo.

Embora a conversão de tabelas MyISAM para colunas de tamanho fixo possa trazer performance, é preciso considerar alguns fatores:

- colunas de tamanho fixo são mais rápidas, mas ocupam mais espaço. Escolha de acordo com a sua prioridade. Se você prioriza velocidade e tem espaço em disco, escolha colunas de tamanho fixo. Se sua prioridade é ocupar menos espaço em disco, prefira as colunas de tamanho variável. Para aplicações críticas, o ideal é testar a performance da tabela com cada um dos tipos.

- algumas vezes você não pode usar colunas de tamanho fixo. Não há colunas de tamanho fixo para strings de mais de 255 caracteres, por exemplo.

Tabelas do tipo MEMORY normalmente são armazenadas usando registros de tamanho fixo, então é indiferente o uso de CHAR ou VARCHAR. Para tabelas InnoDB, o formato interno para armazenamento de registros não trata colunas de tamanho fixo e variável diferentemente (todos os registros usam um cabeçalho contendo ponteiros para os valores das colunas), então o uso de colunas CHAR não é internamente mais simples do que colunas VARCHAR. Conseqüentemente, o fator prioritário é o total de armazenamento usado pelos registros, fazendo com que a escolha recaia sobre colunas de tamanho variável. Para tabelas tipo BDB, normalmente não faz diferença se você usa colunas de tamanho fixo ou variável.

03. Defina colunas como NOT NULL. Isto traz rapidez de processamento e requer menos armazenamento. Isto também pode simplificar consultas porque não é necessário checar por valores nulos.

04. Considere o uso de colunas ENUM. Se você tem uma coluna string que terá baixa cardinalidade (número pequeno de valores distintos), considere o uso de colunas tipo ENUM. Colunas ENUM são processadas mais rapidamente, pois são representadas como numéricas, internamente.

05. Use a função PROCEDURE ANALYSE(). Execute a função PROCEDURE ANALYSE() para verificar indicações de tipos de dados para as colunas de uma tabela.

SELECT * FROM tabela PROCEDURE ANALYSE();

Serão sugeridos tipos de dados para cada coluna da tabela. Baseado na saída da função, você pode perceber que sua tabela precisa ser modificada para tirar proveito de tipos de dados mais eficientes.

06. Use a função OPTIMIZE TABLE para tabelas que são sujeitas à fragmentação. Tabelas que são modificadas com grande freqüência, especialmente aquelas que possuem colunas de tamanho variável, são sujeitas à fragmentação. OPTIMIZE TABLE pode ser usada apenas em tabelas MyISAM e BDB, mas desfragmentam apenas as tabelas MyISAM. O método de desfragmentação que funciona com qualquer tipo de tabela é fazer um dump da tabela com mysqldump e excluir e recriar as tabelas.

mysqldump opt nome_do_bd nome_da_tabela > tabela.sql

mysql nome_do_db < tabela.sql

07. Coloque colunas BLOB e TEXT numa tabela separada. Sob algumas circunstâncias, pode fazer sentido mover estas colunas para uma tabela secundária, se você pretende converter a tabela para registros de tamanho fixo nas outras colunas. Isto reduzirá a fragmentação na tabela primária e permitirá que você tire proveito dos benefícios de performance de tabelas de coluna de tamanho fixo. Isto também permite que você execute consultas SELECT * na tabela primária sem sobrecarregar o servidor com o grande tamanho dos campos BLOB ou TEXT.

Carregando dados eficientemente

Muitas vezes você provavelmente se preocupou com a otimização de queries SELECT, porque elas são o tipo mais comum de query. Entretanto, a carga de dados também é muito importante e há estratégias que você pode usar para carregar dados eficientemente. Os princípios básicos são:

- Carga de dados em volume é mais eficiente do que a carga de um simples registro porque a chave de cache não precisa ser transportada depois que cada registro é carregado. Quanto mais você reduz o transporte de chaves de cache, mais rápida será a carga dos dados.

- A carga é mais rápida quando a tabela não tem índices. Se há índices, além da adição do registro no arquivo de dados, cada índice precisa ser alterado e reorganizado.

- Comandos SQL curtos são mais rápidos do que comandos longos porque eles envolvem menos análise de sintaxe no servidor e ocupam menos largura de banda na rede.

Alguns destes fatores podem parecer secundários, mas se você está carregando um pacote de dados, cada pequena perda de eficiência faz a diferença. Partindo destes princípios, você pode chegar a muitas conclusões práticas para carga de dados mais eficiente:

- O comando LOAD DATA é mais eficiente do que comandos INSERT porque ele faz a carga em volumes. O servidor precisa analisar a sintaxe e interpretar somente um comando, não vários. Além disso, os índices serão transportados somente após todos os registros terem sido processados.

- LOAD DATA é mais eficiente sem a palavra-chave LOCAL. Sem LOCAL, o arquivo precisa estar no servidor e você não precisa ter privilégios administrativos sobre o arquivo. O servidor lerá o arquivo diretamente do disco.

- Se você precisa usar INSERT, use a forma que permite que múltiplos registros sejam especificados num único comando:

INSERT INTO tabela VALUES (....), (....), (....), ....;

Quanto mais linhas forem especificadas no comando, melhor. Isso reduz o número de análises de sintaxe e minimiza a quantidade de transporte de índices. Isto contradiz que comandos mais curtos são mais eficientes, mas não há contradição. Se você executar mysqldump numa tabela com --opt (optimize) habilitado, será gerado INSERT de múltiplos registros, o que indica que é a melhor opção.

- Se você precisa usar múltiplos comandos INSERT, agrupe-os, se possível, para reduzir o transporte de índice. Isso pode ser feito com o uso de transações, se o tipo de tabela permitir:

START TRANSACTION;

INSERT INTO tabela (....);

INSERT INTO tabela (....);

INSERT INTO tabela (....);

COMMIT;

Se o tipo de tabela não permitir transações, trave a tabela para escritas e execute os comandos INSERT:

LOCK TABLE tabela WRITE;

INSERT INTO tabela (....);

INSERT INTO tabela (....);

INSERT INTO tabela (....);

UNLOCK TABLES;

- Use o protocolo de compressão cliente/servidor para reduzir o volume de dados na rede. Para a maioria dos clientes MySQL, isto pode ser especificado usando a opção de linha de comando --compress. Em geral, isto deve ser feito apenas em redes lentas, pois a compressão requer um pouco mais de tempo de processamento.

- Permita que o MySQL insira valores DEFAULT para você. Isto é, não especifique colunas em comandos INSERT que podem ser determinadas por valor padrão. No mínimo, o tamanho dos comandos será menor e ocupará menos banda da rede. Além disso, o servidor fará menos análise sintática e conversão de valores.

Os princípios para carga de dados também se aplicam para ambientes envolvendo execução em clientes com diferentes tipos de operações. Por exemplo, você deve evitar longas consultas SELECT em tabelas que sofrem alterações constantemente. Isso compromete a performance dos comandos de escrita. Uma forma de contornar isso, se suas escritas são principalmente de comandos INSERT, é adicionar os novos registros numa tabela auxiliar e então adicionar esses registros à tabela principal periodicamente. Isso não é uma estratégia viável se você precisa estar apto a acessar novos registros imediatamente, mas se você pode deixá-los inacessíveis por um curto período de tempo, use a tabela auxiliar para ajudá-lo de duas maneiras: primeiro, ela reduz a disputa entre os comandos de escrita e as consultas SELECT que ocupam a tabela principal, fazendo com que sejam executados mais rapidamente. Em segundo lugar, leva menos tempo carregar um lote de dados da tabela auxiliar para a principal.

Uma aplicação para esta estratégia é quando você está realizando o login da sua aplicação através de registros de uma tabela MySQL. Neste caso, pode não haver grande necessidade dos novos usuários ter acesso imediatamente.

Fonte: Thiago Ferreira