Leia os artigos anteriores:

Otimização de Consultas MySQL – Parte 02

Otimização de Consultas MySQL – Parte 01

*

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.

Abraços,

*

Adaptação de: http://www.informit….a…377652&rl=1