Otimizações para MySQL - parte 1

Normalmente esse assunto já está batido em listas de discussão especializadas, mas eu encontrei certa dificuldade em encontrar tudo que eu vou colocar aqui em um único lugar e de forma simples.

Nessa parte 1 vou tratar de tipos de tabela.

Tipos de tabela - Segurança x Velocidade:

No MySQL Versão 3.23.6, você pode escolher entre 3 formatos de tabelas básicos (ISAM, HEAP e MyISAM). Versões mais novas do MySQL suportam tipos de tabelas adicionais (InnoDB ou BDB), dependendo de como o BD foi compilado. Básicamente você pode dividir esses tipos em duas categorias: Tabelas de transção segura(TST) e Tabelas de transação “não segura”(NTST).

Vantagens de tabelas de transção segura(TST):

• Mais segura. Mesmo se o MySQL falhar ou se você tiver problemas com hardware, você é capaz de recupera-los através de recuperação automática ou de um backup + o log de transação.
• Você pode combinar muitas instruções e aceitar todas de uma vez com o comando COMMIT.
• Você pode executar um ROLLBACK para ignorar suas mudanças (se você não estiver rodando em modo auto-commit).
• Se uma atualização falhar, todas as suas mudanças serão restauradas. (Com tabelas NTST todas as mudanças que tiverem sido feitas são permanentes).
• Pode fornecer melhor concorrência se a tabela tiver muitas atualizações concorrentes com leituras.

Vantagens de tabelas de transação “não segura”(NTST):

• Muito mais rápida e não há nenhuma sobrecarga de transação.
• Usará menos spaço em disco já que não há nenhuma sobrecarga de transação.
• Usará menos memória para as atualizações.

Vale lembrar que num mesmo banco você pode utilizar tabelas de transação segura e de transação não segura para obter melhor performance e que caso você tente criar uma tabela com tipo não válido a tabela será criada automaticamente como o tipo myISAM.

Dadas essas explicações vagas e rotineiras, vamos por a mão na massa e entender melhor a diferença entre esses tipos de tabelas:

PRIMARY KEY / UNIQUE

Normalmente quando você tenta executar uma transação que viola uma chave primária você obtém um erro certo? ERRADO! Isso depende. Se a transação ocorrer em uma (ou mais) tabela(s) de transação não segura (ISAM, HEAP e MyISAM) isso está correto. Caso contrário o mysql fará o rollback da sua transação (sim, de TODA a transação!), no caso das transações não seguras o mysql irá para o registro errado e deixará os demais rodando, o que pode vir a causar várias inconsistências no seu sistema.

No caso de tabelas de transação segura você terá que utilizar o show warnings | errors, ou mysql_info() para versões mais antigas, para conseguir debugar suas querys.
No caso de transações não seguras você pode usar a diretiva IGNORE para não receber mensagens de erro (INSERT IGNORE …), isso fará com que o mysql ignore a transação que causa o erro e pule para as próximas. Por isso é uma diretiva que inspira cuidado para ser utilizada!

NOT NULL

Em tabelas NTST todos os campos no MySQL têm valores padrão. Se você tentar armazenar NULL em uma coluna que não aceita valores NULL, o MySQL Server armazenará 0 ou ” (strig vazia) nela(comportamento que pode ser alterado com a opção de compilação -DDONT_USE_DEFAULT_FIELDS). Isso faria com que as instruções INSERT gerassem um erro a menos que você colocasse valores específicos para TODAS as colunas que exigem um valor diferente de NULL.

Se você inserir um valor ‘errado’ em uma coluna como um NULL em uma coluna NOT NULL ou um valor numérico muito grande em um campo INT, o MySQL irá atribuir a coluna o ‘melhor valor possível’ em vez de dar uma mensagem de erro. Para strings este valor é uma string vazia ou a maior string possível que possa estar na coluna. Isso porque “não podemos verificar estas condições antes da consulta começar a executar. Se encontrarmos um problema depois de atualizar algumas linahs, não podemos fazer um rollback já que o tipo de tabela não suporta isto. A opção de parar não é tão boa como no caso em que a atualização esteja feita pela metade que é provavelmente o pior cenário possível. Neste caso é melhor ‘fazer o possível’ e então continuar como se nada tivesse acontecido.” (retirado do MYSQL MANUAL).
Isso tudo só mostra que devemos fazer TODAS as validações no software ANTES de executar a query :D.

ENUM e SET

Se você inserir um valor errado em um campo ENUM, ele será configurado com uma string vazia em um contexto string.
(ENTENDA ENUM)
Se você inserir uma opção errada em um campo SET, o valor errado será ignorado.
(ENTENDA SET)

Por enquanto é tudo pessoal, pretendo em um post futuro tratar a fundo as peculiaridades de cada um dos tipos de tabela. Por enquanto vale essa separeção simplista de transação não segura e transação segura. Caso você esteja preparando um banco de dados para uma aplicação e queira informações mais detalhadas, vale lembrar que o ALTER TABLE consegue alterar o tipo da tabela sem nenhum grande problema, só gerará complicações caso você deseje mudar a tabela de um tipo TST para um NTST, afinal essa segurança extra também vai exigir um bocado de mudança nas validções do seu software!

Abraço e até a próxima!