MyISAM: vantagens e desvantagens.

O mecanismo de armazenamento MyISAM, padrão no MySQL até a versão 5.5, é o mais usado na WEB, em armazéns ou depósitos de dados e vários outros tipos de aplicações.
Neste texto, vou mostrar algumas de suas características e as situações onde o seu uso é indicado e quando não é.
Escolher o mecanismo de armazenamento (ou storage engine) mais adequado pras suas aplicações é crucial e uma decisão errada tomada nesta escolha pode ser de difícil reversão.
Antes do MySQL 5.5.5, ao criar uma nova tabela, o padrão escolhido pelo sistema será o MyISAM, a menos que você especifique outro. A partir desta versão, o padrão será o InnoDB.

SAIBA MAIS:
O mecanismo de armazenamento MyISAM é derivado de um outro, mais antigo, chamado ISAM — Indexed Sequential Access Method, originalmente desenvolvido pela IBM, para ser usado em mainframes. Ainda dentro da IBM, o ISAM evoluiu pro VSAM — Virtual Storage Access Method.
Atualmente, a IBM promove o uso do DB2.
Era possível usar o mecanismo de armazenamento ISAM até o MySQL 3.23. A partir de então, ele se tornou indisponível, embora seu código ainda estivesse incluído até a versão 4.1.
Conheça outros mecanismos de armazenamento para MySQL.

As vantagens de usar o MyISAM

Atualmente, muitas distros Linux oferecem o MySQL em seus repositórios e, no Ubuntu 14.04 é a versão 5.5 que ainda estará lá, para ser baixada — onde o mecanismo de armazenamento padrão é o MyISAM. A Canonical tem planos de mudar do MySQL para MariaDB em um futuro próximo.
Eu posso citar, pelo menos, 4 boas razões para usar o MyISAM storage engine.

1 – a simplicidade

As tabelas MyISAM são simples. Se você é novato no assunto “bancos de dados” ou no que concerne ao MySQL, é recomendado começar a usar o MyISAM, antes de sair experimentando os outros.
Segue um exemplo de como criar um novo banco de dados e uma tabela no MySQL.
(Se você ainda não tem o MySQL instalado, dê uma olhada no artigo Como instalar o MySQL).
Para criar um banco de dados, usamos a declaração CREATE DATABASE. Em seguida, o selecionamos para uso, com USE. Veja como:

CREATE DATABASE teste;
USE teste;

Uma vez criado o banco e selecionado para uso, vamos criar uma nova tabela dentro dele:

CREATE TABLE teste.meuslivros (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
titulo TEXT NOT NULL;
autor VARCHAR(30)
genero VARCHAR(20),
PRIMARY KEY(id))
ENGINE = MyISAM;

O exemplo, acima, mostra como é simples e rápida a criação de uma nova tabela no MySQL. Se você tiver interesse, o artigo – como criar tabelas no MySQL –, mostra o processo com mais exemplos e detalhes.

2 – otimização e base de conhecimento

Eu poderia dizer que o MyISAM é muito rápido – mais rápido que muitos outros storage engines, mas os benchmarks feitos por várias empresas mostram resultados muito diferentes entre si. Esta variação se deve às tarefas executadas, às configurações dos servidores e do mysqld, entre outros fatores. A melhor medição, quem faz, é você, dentro do seu ambiente de trabalho. Ainda assim, é notória a performance do mecanismo de armazenamento MyISAM, nos testes.
Em função do tempo em que o MyISAM tem estado “na estrada”, há muitos sistemas plenamente otimizados para usa-lo.
Se você sente que o seu sistema não está perfeitamente otimizado para uso do MyISAM, há uma extensa base de conhecimento disponível na Internet para ajudá-la(o) a resolver isto.

3 – indexação FULLTEXT e busca dentro do banco de dados

Considerando a tabela que criamos, imagine que precisamos realizar uma pesquisa, percorrendo títulos e gêneros dos livros.
Uma solução simples, possibilitada pelo MyISAM é adicionar um índice FULLTEXT. Veja o exemplo:

ALTER TABLE teste.livros ADD FULLTEXT alltext (autor, genero);

Agora, fica fácil encontrar todos os livros que contenham as palavras “mauro vasconcelos” e “infanto-juvenil”, dentro da tabela:

SELECT * FROM teste.livros WHERE MATCH(autor, genero) AGAINST ('mauro vasconcelos infanto-juvenil');

Com uma alteração na declaração acima, é possível obter o mesmo resultado, só que ordenado por relevância. Veja:

SELECT *, MATCH(autor, genero) AGAINST ('mauro vasconcelos infanto-juvenil') FROM teste.livros ORDER BY rel DESC;

4 – uso de recursos em ambientes limitados

Uma das vantagens de qualquer sistema que tem longa estrada percorrida é a estabilidade conquistada pelos anos de uso, pessoas envolvidas no projeto e seu contínuo desenvolvimento.
O MyISAM é o mais indicado em sistemas de poucos recursos, em servidores de menor capacidade de processamento e/ou com pouco espaço de armazenamento em disco ou memória RAM.
Nesta arena, ele se mostra imbatível nos benchmarks de que falamos no item anterior, mesmo que você não dedique algum tempo a otimizar o hardware e o mysqld.

As desvantagens do mecanismo de armazenamento de tabelas MyISAM

Se há situações em que o MyISAM é o mais indicado para uso, há várias outras em que ele é superado por outros mecanismos.
Aqui, vou mostrar alguns dos problemas que administradores e programadores enfrentam ao usar o MyISAM, no MySQL.

1 – integridade de dados

O MyISAM não tem suporte a transações ou a restrições de chaves estrangeiras.
Leve em conta uma aplicação bancária, onde ocorre uma transferência monetária – o que envolveria duas declarações SQL UPDATE: uma para debitar o valor de uma conta; outra para creditar o mesmo valor à outra conta.
Se houvesse uma falha no servidor, exatamente neste momento, você poderia acabar com o valor nas duas contas ou em nenhuma delas. O recurso de “transações”, presente no InnoDB, é o que evita este tipo de situação.

2 – recuperação de acidentes

O MySQL é estável e confiável. Contudo, as tabelas MyISAM podem voltar corrompidas, após uma falha.
O problema pode ser resolvido com o uso de um REPAIR TABLE – o que é um trabalho administrativo a mais, na verdade.

3 – travamento de tabelas

Ao adicionar ou atualizar um registro em uma tabela MyISAM, todas as outras mudanças são impedidas pelo travamento, até que aquela operação seja completada.
É difícil demonstrar ou provar que este comportamento diminui a performance ou causa problemas à sua aplicação web, mas há um consenso entre especialistas de que o mecanismo mais adequado para você não é o MyISAM, se a sua aplicação realiza grandes quantidades de inserções e atualizações à tabela.

LEIA MAIS:
  • Busca FULLTEXT — leia mais sobre pesquisas via FULLTEXT Index no MySQL.
  • Storage engines — conheça os mecanismos de armazenamento suportados pelo MySQL.
  • Tipos de dados — conheça os tipos de dados suportados dentro das tabelas MySQL
  • mysqlcheck — veja como restaurar uma tabela corrompida.
  • Bancos de dados — veja como criar novos bancos de dados no MySQL.
  • Tabelas — aprenda, através de exemplos, como criar tabelas no MySQL.

Conclusão: devo usar o MyISAM?

Há uma série de situações em que o MyISAM tende a ser a opção mais indicada, de acordo com a relação de vantagens e desvantagens apresentada, até agora. Nas situações, abaixo, a resposta é sim:

  • Você é iniciante no MySQL;
  • Sua aplicação web é simples e não precisa de transações;
  • Você precisa de velocidade;
  • Deseja usar buscas FULLTEXT;
  • Tem recursos de hardware limitados.

Já, se precisa fazer uso de “transactions” e a integridade dos dados é prioritária e crítica, outras opções devem ser consideradas.
Note que é comum se usar mais de um tipo de mecanismo de armazenamento dentro de um mesmo banco de dados. Algumas tabelas precisam usar o InnoDB, outras o MyISAM e outras podem usar o CSV – e todo mundo convive bem, sem problema algum.
Os tipos de aplicações mais indicados para usar o MyISAM são:

  • CMS – Content Management Systems, ou sistemas de gestão de contúdo;
  • Ferramentas de marcação de páginas favoritas online;
  • Leitores RSS;
  • Mecanismos de busca na web etc.

Espero que este texto tenha lhe sido útil e, se este for o caso, compartilhe com os seus amigos, nas redes sociais. Esta é sempre a melhor forma de agradecer e incentivar.

Como criar tabelas no MySQL

Neste post vou tentar mostrar objetivamente como criar tabelas em um banco de dados através da linha de comando do cliente MySQL.
Dada a grande quantidade de parâmetros aceitos, a declaração CREATE TABLE é uma das mais complexas no MySQL. Por isto vou procurar simplificar através de exemplos práticos — já pressupondo que você tenha privilégios para criar novas tabelas e saiba como criar bancos de dados. Ao final do texto, vou mostrar como criar tabelas em um script PHP.
Comece por selecionar o banco de dados dentro do qual uma nova tabela será criada:

USE nome_do_banco;

Sintaxe de CREATE TABLE

De acordo com a documentação da Oracle, a sintaxe mais simplificada é esta aqui:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)

As partes da declaração que se encontram entre [] (colchetes) são opcionais:

  • TEMPORARY – indica que a tabela criada será temporária, o que significa que ela expira assim que a sua sessão no MySQL terminar. Use-a sempre que estiver fazendo testes.
  • IF NOT EXISTS – verifica a prévia existência da tabela e evita uma interrupção do script, causada por erro.

Os nomes das tabelas são sensíveis à caixa (case sensitive). Ou seja, tbl_name e Tbl_Name são dois nomes totalmente diferentes pro MySQL.
Uma tabela é composta por uma ou mais colunas, cada qual com suas definições. Vamos ver, daqui pra frente, algumas formas de definir este item durante a criação da tabela

Exemplos de criação de tabelas no MySQL

Vou começar pela criação de uma agenda telefônica, como esta que você usa no celular e é armazenável no cartão SIM, que contenha apenas strings (cadeias de caracteres) curtas – nome e telefone:

CREATE TABLE contatos (
nome VARCHAR(50) NOT NULL,
telefone VARCHAR(25) NOT NULL
)ENGINE=CSV;

Note que a engine escolhida foi a CSV. Leia mais sobre mecanismos de armazenamento (storage engines), para saber entender mais sobre o assunto.
Verifique se a tabela foi criada:

SHOW TABLES;

Mysql tutorial - Create Table - Show Tables
Podemos melhorar um pouco mais a tabela contatos, ao acrescentar mais alguns campos, como sobrenome dos contatos, DDD, data de nascimento e email. Antes de criar uma nova tabela, com o mesmo nome, vamos remover a anterior:

DROP TABLE contatos;

Agora, vamos criar a nova tabela:

CREATE TABLE IF NOT EXISTS contatos (
nome VARCHAR(20),
sobrenome VARCHAR(30),
ddd INT(2),
telefone VARCHAR(9),
data_nasc DATE,
email VARCHAR(30)
)ENGINE=MyISAM;

Note que acrescentamos alguns outros itens a esta declaração:

  • Usar IF NOT EXISTS é uma boa prática dentro de scripts (veremos mais sobre isto, mais afrente);
  • Ao final do código, optamos por um outro mecanismo de armazenamento

Saiba mais:

Campos vazios, numeração automática e chave primária

Vou fazer uma apresentação rápida de alguns parâmetros que podem ser usados para melhorar a usabilidade e, mesmo, a segurança das tabelas criadas.

NOT NULL

Comumente, o desenvolvedor, ao projetar o banco de dados, não deseja que certos campos sejam deixados em branco, pelo usuário. Para evitar que isto ocorra, usa-se o parâmetro NOT NULL – o sistema irá retornar um erro, caso o usuário não preencha os campos configurados com este parâmetro.

AUTO_INCREMENT

Quando se quer aplicar uma ação a um determinado registro, na tabela, é importante que se encontre exatamente aquele que se quer, mesmo que haja 10 pessoas registradas com o mesmo nome e sobrenome. Apagar um registro errado, por exemplo, pode ser fonte para uma grande dor de cabeça.
Ter um campo de identificação única para cada registro pode prevenir este tipo de erro. É aí que entra o parâmetro AUTO_INCREMENT que pode ser usado para criar um campo numérico com uma identificação única e automática dentro da tabela.

PRIMARY KEY

O parâmetro PRIMARY KEY é usado para definir uma coluna como chave primária e ajudar a identificar um registro em relação aos outros dentro da tabela. Os valores de uma chave primária têm que ser únicos e não podem ser nulos. Pense na palavra inglesa primary com o significado de principal.
Vamos ver estes conceitos aplicados ao exemplo da tabela de contatos:

DROP TABLE contatos;
CREATE TABLE IF NOT EXISTS contatos (
id INT(5) AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(20) NOT NULL,
sobrenome VARCHAR(30) NOT NULL,
ddd INT(2),
telefone VARCHAR(9) NOT NULL,
data_nasc DATE,
email VARCHAR(30)
)ENGINE=MyISAM;

Veja abaixo o resultado que eu obtive:

DROP TABLE contatos;
Query OK, 0 rows affected (0.14 sec)

CREATE TABLE IF NOT EXISTS contatos (
    -> id INT(5) AUTO_INCREMENT PRIMARY KEY,
    -> nome VARCHAR(20) NOT NULL,
    -> sobrenome VARCHAR(30) NOT NULL,
    -> ddd INT(2),
    -> telefone VARCHAR(9) NOT NULL,
    -> data_nasc DATE,
    -> email VARCHAR(30)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.10 sec)

show tables;
+--------------------+
| Tables_in_primeiro |
+--------------------+
| contatos           |
+--------------------+
1 row in set (0.00 sec)

Evite mensagens de erros, usando IF NOT EXISTS

Note que, ao executar este código, ele irá criar uma tabela, com o nome contatos se ela já não existir. O que significa que o script retornará uma mensagem de erro, se você recarregar a página, logo após tê-lo executado:
script php mysql criar tabela comando create table
Se você mudar a linha 14, acrescentando o parâmetro opcional IF NOT EXISTS, tal como vimos no começo deste texto, a mensagem de erro não será exibida e o script prosseguirá normalmente. Veja como:

$sqlcriatabela = "CREATE TABLE IF NOT EXISTS contatos (nome VARCHAR(50), telefone VARCHAR(25));";

Mas, neste caso, isto não seria útil.

Faça o trabalho com o PHP

Até aqui, você aprendeu a criar novas tabelas e alguns parâmetros relacionados a este comando, dentro da linha de comando do cliente MySQL. Espero ter conseguido demonstrar que, embora seja complexa (pela quantidade de recursos) a declaração CREATE TABLE não é, de modo algum, complicada.
Se estiver interessado em realizar a tarefa através da linguagem de programação PHP, leia este post — onde a construção do script em PHP é explicado com bastante detalhamento.

Mecanismos de armazenamento no MySQL ou Storage Engines

Tutorial Mysql - Storage Types - Mecanismos de armazenamentoEntenda os tipos de tabelas do MySQL ou os mecanismos de armazenamento (storage engines) e conheça os mais comuns.
O MySQL suporta diversos tipos de tabelas ou mecanismos de armazenamento. Ao entender os recursos presentes em cada tipo, você poderá criar e usar tabelas com maior eficiência e melhorar a performance de seus bancos de dados.
Neste texto, vou abordar 8 tipos de tabelas MySQL e explicar suas características, seus recursos, vantagens e desvantagens, no sentido de te ajudar a obter máxima eficiência e melhor performance do banco de dados.
Os mecanismos de armazenamento de dados são componentes de software dos bancos de dados que permitem criar, ler, atualizar e apagar dados do sistema.

MyISAM

O MyISAM estende os recursos do antigo ISAM. É otimizado para compressão, velocidade e as tabelas neste formato são portáveis entre várias plataformas e sistemas operacionais.
As tabelas neste formato, padrão até a versão 5.5, podem ter até 256 TB de tamanho – o que é um tamanho considerável. As tabelas MyISAM podem ser convertidas para tabelas comprimidas e somente leitura (read-only).
Ao iniciar, o sistema de banco de dados verifica as tabelas deste tipo e pode encontrar e corrigir setores defeituosos nas tabelas.
Este tipo é plenamente apropriado a ambientes com intensa leitura de dados e pouca escrita – melhor ainda, com nenhuma escrita. Sua principal deficiência é não ter suporte a transações e chaves estrangeiras.
Leia mais: No artigo “vantagens e desvantagens do MyISAM“, são delineados mais detalhadamente os casos em que este mecanismo de armazenamento é mais indicado ou não.

InnoDB

Este é, atualmente, o mecanismo padrão do MySQL. A Oracle é a mantenedora e dona do InnoDB (após ter adquirido, em 2005, a empresa responsável pelo seu desenvolvimento, a finlandesa Innobase Oy).
O banco de dados MariaDB e o Percona Server usam um fork deste mecanismo, chamado XtraDB – que costuma incorporar os novos recursos do InnoDB, tão logo eles sejam disponibilizados pela Oracle.
Entre seus recursos, cito o suporte total ao ACID, à transações, chaves estrangeiras, commit, rollback etc. Têm ótima performance e as tabelas neste mecanismo podem chegar a 64 Terabytes.
Tal como o seu antecessor, o InnoDB também é portável entre diferentes plataformas. E o MySQL também faz checagem e reparos nas tabelas, ao ser iniciado.
Resumindo, o InnoDB tem suporte a:

  • transações SQL e XA
  • tablespaces
  • chaves estrangeiras
  • indíces FULLTEXT
  • operações espaciais
  • colunas virtuais

MERGE

Trata-se de uma tabela virtual, que combina múltiplas tabelas MyISAM, que têm estruturas similares, em uma só tabela. A palavra inglesa merge pode ser usada com o significado de junção, mistura. Também é conhecido como mecanismo MRG_MyISAM. Uma vez que não tem seus próprios índices, este mecanismo usa os dos seus componentes.
Ao fazer uso deste mecanismo de armazenamento, você pode melhorar a performance da junção de várias tabelas. O MySQL só vai permitir as operações de SELECT, DELETE, UPDATE e INSERT em tabelas MERGE.
Se você executar o comando de remoção de tabela DROP TABLE, só as especificações MERGE serão removidas – as tabelas envolvidas “na mistura” não serão afetadas pela operação.

MEMORY (HEAP)

Este storage engine, que já foi conhecido como HEAP, cria tabelas com propósitos específicos, cujo conteúdo fica armazenado na memória.
Uma vez que os dados ficam muito vulneráveis a problemas de hardware, como oscilações na rede elétrica, é seguro usar este mecanismo apenas para tabelas temporárias, com restrições de escrita (que não podem ser alteradas) e com dados emprestados de outras tabelas.
A principal vantagem deste mecanismo é a agilidade com que se pode manipular os seus dados.

ARCHIVE

É otimizado para inserção ágil e rápida de grande quantidade de dados em arquivos comprimidos e não indexados, para economizar espaço.
Os registros são comprimidos ou descomprimidos sob demanda, à medida em que são inseridos ou requisitados.
CSV Storage Engine Mecanismo tipo de armazenamento

CSV

O mecanismo de armazenamento CSV (Comma Separated Values — valores separados por vírgulas)armazena os dados em arquivos de texto, separando os registros com vírgulas. Este é o mecanismo usado em listas de contatos do cartão SIM do seu celular:

"Odair José","1136789890"
"Reginaldo Rossi","8198786543"

É um padrão que ocupa pouquíssimo espaço, por sua simplicidade e tem maior compatibilidade com aplicativos tais como planilhas eletrônicas (Excel, LibreOffice etc).

FEDERATED

Ao criar uma tabela de dados com o uso de um dos mecanismos de armazenamento padrão (MyISAM, CSV ou InnoDB), o arquivo conterá uma definição de seu conteúdo e dados. Uma tabela FEDERATED consiste de uma definição dos dados que armazena – contudo, os seus dados estão fisicamente armazenados em um servidor remoto.
As tabelas deste mecanismo, consistem, portanto de 2 elementos:

  • um servidor remoto com uma tabela de banco de dados, contendo uma definição da tabela (em formato .frm) e a tabela à qual está associada. O tipo de tabela pode estar em qualquer formato que este servidor suporte;
  • um servidor local com uma tabela contendo uma definição que corresponde à da tabela no servidor remoto. Não há um arquivo de dados no servidor local – há uma conexão para a tabela no servidor remoto.

Blackhole

O mecanismo “buraco negro” aceita dados, mas não os armazena. Ao tentar recuperar os dados “armazenados”, você receberá sempre uma resposta vazia. Pode ser usado em projetos de bancos de dados distribuídos, onde estes são automaticamente replicados, mas não armazenados localmente.
É comum ser usado para rodar testes de performance.

LEIA MAIS
  • O MyISAM — Aplicabilidade, vantagens e desvantagens deste mecanismo de armazenamento.
  • Tipos de dados — Conheça os tipos que você pode usar nas tabelas.

Conclusão

Os formatos padrão, InnoDB e MyISAM, acabam por ser as escolhas mais seguras na grande maioria dos casos. Se houver necessidade de fazer transações, escolha o InnoDB (padrão nas versões atuais do MySQL). Em casos fora do comum, um destes outros mecanismos que você conheceu pode ser o mais adequado.