Tabelas MyISAM

MyISAM é o tipo de tabela padrão no MySQL Versão 3.23. Ela é baseada no código ISAM e possui várias extensões úteis.

O índice é armazenado em um arquivo com extensão .MYI (MYIndex), e os dados são armazenados em um arquivo com a extensão .MYD (MYData). Você pode verificar/reparar tabelas MyISAM com o utilitário myisamchk. Você pode compactar tabelas MyISAM com myisampack para utilizar menos espaço. O itens seguintes são novos no MyISAM:

  • Existe um parâmetro no arquivo MyISAM que indica se a tabela foi fechada corretamente. Se o mysqld é iniciado com --myisam-recover, tabelas MyISAM serão automaticamente verificadas e/ou reparadas na abertura se a tabela não foi fechada apropriadamente.
  • Você pode INSERIR novas linhas em uma tabela que não tenha blocos livres no meio do arquivo de dados, na mesma hora outras threadas são lidas da tabela (inserção concorrente). Um bloco livre pode vir de uma atualização de uma linha de tamanho dinâmico com muitos dados para uma linha com menos dados ou ao deletarmos linhas. Quando todos os blocos livres são usados, todas as inserções futurs serão concorrentes de novo.
  • Suporte a grandes arquivos (63-bit) em sistema de arquivos/sistemas operacionais que suportam grandes arquivos.
  • Todo dado é armazenado com byte mais baixo primeiro. Isto torna a máquina e SO independentes. A única exigência para a portabilidade do arquivo binário é que a a máquina utilize inteiros com sinais em complemento de dois (como toda a máquina nos últimos 20 anos tem) e formato de pontos flutuante IEEE (também totalmente dominante entre máquinas mainstream). A única área de máquinas que não podem suportar compatibilidade binária são sistemas embutidos (porque eles, algumas vezes, tem processadores peculiares).

Não há uma grande perda de velocidade em armazenar o byte mais baixo de dados primeiro; os bytes em um registro de tabela estão normalmente desalinhados e isto não dá muito poder de leitura do byte desalinhado em outra ordem além da ordem reversa. O código atual busca-valor-coluna também não é crítico em relação ao tempo comparado a outro código.

  • Todas as chaves numéricas estão armazendas com o byte mais alto em primeiro para conseguir melhor compactação do índice.
  • Tratamento interno de uma coluna AUTO_INCREMENT. MyISAM irá atualizá-lo automaticamenteem um INSERT/UPDATE. O valor AUTO_INCREMENT pode ser zerado com myisamchk. Ele fará colunas AUTO_INCREMENT mais rápidas (pelo menos 10%) e números natigos não irão reutilizar como no antigo ISAM. Note que quando um AUTO_INCREMENT é definido no fim de uma chave multi-parte o comportamento antigo ainda está presente.
  • Ao inserir ordenandamente (como quando se utiliza colunas AUTO_INCREMENT) a árvore chave será separada de forma que o nodo mais alto contenha apenas uma chave. Isto irá aumentar a utilização de espaço na árvore de chaves.
  • Colunas BLOB e TEXT podem ser indexados.
  • Valores NULL são perimitidos em colunas indexadas. Isto gasta 0-1 bytes/chave.
  • O tamanho máximo da chave é de 500 bytes por padrão (pode ser alterado recomopilando). No caso de chaves maiores que 250 bytes, um tamanho de bloco de chave maior que o padrão de 1024 bytes é usado para esta chave.
  • Número máximo de chaves/tabelas é 32 por padrão. Isto pode ser aumentado para 64 sem ser necessário recompilar myisamchk.
  • myisamchk marcará as tabelas como verificadas se alguém executá-las sem --update-state. myisamchk --fast só verificará aquelas tabelas que não tenham esta marca.
  • myisamchk -a armazena estatísticas para partes de chaves(e não apenas para toda a chave como no ISAM).
  • Linhas de tamanho dinâmico serão agora muito menos fragmentados quando misturar deleções com atualizações e inserções. Isto é feito combinando automaticamente blocos deletados adjacentes e extendendo blocos se o próximo bloco é deletado.
  • myisampack pode empacotar colunas BLOB e VARCHAR.
  • Você pode colocar arquivos de dados e índices em diretórios diferentes para obter maior velocidade (com a opção DATA/INDEX DIRECTORY="caminho" para CREATE TABLE).

MyISAM também suporta os seguintes itens, os quais o MySQL estará apto a utilizar em um futuro próximo:

  • Suporte a tipos VARCHAR reais; uma coluna VARCHAR inicia com um tamanho armazenado em 2 bytes.
  • Tabelas com VARCHAR podem ter um registro de tamanho fixo ou dinâmico.
  • VARCHAR e CHAR podem ser maior que 64K. Todos os segmentos de chaves têm a sua própria definição de linguagem. Isto habilitará o MySQL para ter diferentes definições de linguagens por coluna.
  • Um índice computado em hash pode ser usado para UNIQUE. Isto lhe permitirá ter UNIQUE em qualquer combinação de colunas na tabela. (Você não pode procurar em um em um índice computado UNIQUE, de qualquer forma.)

Note que os arquivos de índice são muito menores com MyISAM que com ISAM. Isto significa que MyISAM usará normalmente menos recursos do sistema que ISAM, mas precisará de mais tempo de CPU quando inserir dados em um índice compactado.

As seguintes opções para mysqld podem ser usadas para alterar o comportamento de tabelas MyISAM.

Opção Descrição
--myisam-recover=# Recuperação automática de tabelas com falhas.
-O myisam_sort_buffer_size=# Buffer utilizado ao recuperar tabelas.
--delay-key-write=ALL Não desarrega buffers de chaves entre escritas para qualquer tabela MyISAM
-O myisam_max_extra_sort_file_size=# Usada paa ajudar o MySQL a decidir quando utilzar o método lento, mas seguro, de criação de índices de cache de chaves. Note este parâmetro é dado em megabytes antes da versão 4.0.3 e em bytes a partir desta versão.
-O myisam_max_sort_file_size=# Não utilzia o método rápido de ordenação de índice para criar índices se o arquivo temporário se tornasse maior que o valor dado. Note que este parâmetro é dado em megabytes antes da versão 4.0.3 e em bytes a partir desta versão.
-O bulk_insert_buffer_size=# Tamanho da arvore cache utilizado na otimização de inserções em bloco. Note que este é um limite por thread!

A recuperação automática é ativada se você iniciar o mysqld com --myisam-recover=#. Na abertura, é verificado se a tabela está marcada como quebrada ou se a variavel de contagem de abertura para esta tabela não é 0 e você a está executando com --skip-external-locking. Se nenhuma das verificações acima forem verdadeiras o seguinte ocorre.

  • Verifica-se se a tabela possui erros.
  • Se encontrarmos um erro, tente fazer um reparação rápida (com ordenação e sem recriar o arquivo de dados) da tabela.
  • Se o reparação falhar devido a um erro no arquivo de dados (por exemplo um erro de chave duplicada), é feita uma nova tentativa, mas desta vez o arquivo de dados é recriado.
  • Se a reparação falhar, tente mais uma vez com o antigo método de opção de reparação (escrever linha a linha sem ordenação) o qual deve estar apto a reparar qualquer tipo de erros com pequenas exigências de disco.

Se a recuperação não estiver apta a recuperar todas as linhas de uma instrução completada previamente e você não especificou FORCE como uma opção para myisam-recover, então a reparação automática abortará com uma mensagem de erro no arquivo de erros:

Error: Couldn't repair table: test.g00pages

Caso você tenha utilizado a opção FORCE, você irá obter um aviso no arquivo de erro:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note que se você executar uma recuperação automática com a opção BACKUP, você deve ter um script cron que mova automaticamente arquivos com nome como tablename-datetime.BAK do diretório de banco de dados para uma media de backup.

Espaço Necessário para Chaves

O MySQL pode suportar diversos tipos de índices, mas o tipo normal é ISAM ou MyISAM. Eles utilizam um índice de árvore-B, e você pode calcular aproximadamente o tamanho do arquivo de índice como (key_length+4)/0.67, somado sobre todas as chaves. (Isto é para o pior caso, quando todas as chaves são inseridas ordenadamente e nós não temos nenhuma chave compactada.)

Índices string são compactados em espaços. Se a primeira parte do índice é uma string, ele também será compactado em prefixo. Compactação em espaço torna o arquivo de índice menor que o indicado acima se a coluna string tem muitos espaços no fim ou é uma coluna VARCHAR não usada em sua totalidade. Compactação de prefixo é usado em chaves que iniciam com uma string. A Compactação de prefixo ajuda se existirem muitas strings com o prefixo idêntico.

Em tabelas MyISAM, você também pode utilizar prefixos em números comprimidos especificando PACK_KEYS=1 quando você cria a tabela. Isto ajuda quando você tem muitas chaves inteiras que têm prefixo idêntico quando o número é armazenado com o byte mais alto primeiro.

Formatos de Tabelas MyISAM

MyISAM suporta 3 tipos diferentes de tabelas. Dois deles são escolhidos automaticamente dependendo do tipo das colunas que você está usando. O terceiro, tabelas compactadas, só pode ser criado com a ferramenta myisampack.

Quando você cria (CREATE) ou altera (ALTER uma tabela, você pode, para tabelas que não possuem BLOBs, forçar o formato da tabela para DYNAMIC ou FIXED com a opção de tabela ROW_FORMAT=#. No futuro você estará apto a compactar/descompactar tabelas especificando ROW_FORMAT=compressed | default para ALTER TABLE.

Características de Tabelas Estáticas (Tamanho Fixo)

Este é o formato padrão. É usado quando a tabela não contém colunas VARCHAR, BLOB, ou TEXT.

Este é o formato mais e simples e seguro. É também o mais rápidos dos formatos em disco. A velocidade vem da facilidade de se encontrar dados no disco. Procurar por algo com um índice no formato estático é muito simples. Apenas multiplique o número de linhas pelo seu tamanho.

Também, ao varrermos uma tabela, é muito simples ler um número contante de registros a cada leitura de disco.

A segurança é evidenciada se o seu computador falha ao escrever em um arquivo MyISAM de tamanho fixo, caso no qual o myisamchk pode facilemente descobrir onde cada linha começa e termina. Assim, geralmente pode se recuperar todos os registros, exceto os escritos parcialmente. Note que no MySQL todos os índices sempre podem ser reconstruídos.

  • Todas as colunas CHAR, NUMERIC, e DECIMAL tem espaços adicionados até o tamanho da coluna.
  • É muito rápida.
  • Facil de se colocar em cache.
  • Fácil de reconstruir depois de uma falha, pois os registros estão localizados em posições fixas.
  • Não precisa ser reorganizada (com myisamchk) a menos que um grande número de registros sejam deletados e você queira retornar espaço de diaco livre ao sistema operacional.
  • Normalmente exige mais espaço de disco que tabelas dinâmicas.

Características de Tabelas Dinâmicas

Este formato é usado se a tabela contém colunas VARCHAR, BLOB ou TEXTou se as tabelas são criadas com ROW_FORMAT=dynamic.

Este formato é um pouco mais complexo porque cada linha tem que ter um cabeçalho que diz o seu tamanho. Um registro também pode acabar em mais de um local quando fica maior em uma atualização.

Você pode utilizar OPTIMIZE tabela ou myisamchk para desfragmentar uma tabela. Se você tiver dados estáticos que você acessa/altera demias na mesma tabela, como alguma coluna VARCHAR ou BLOB, pode ser uma boa idéia mover as colunas dinâmicas para outra tabela apenas para evitar fragmentação.

  • Todas as colunas string são dinâmicas (exceto aquelas com tamanho menor que 4).
  • Cada registro é precedido por um mapa de bits indicando quais colunas estão vazias ('') para colunas string ou zero para colunas numéricas (Isto é diferente de colunas contendo valores NULL). Se uma coluna de string tem um tamanho de zero depois da remoção de espaços extras, ou uma coluna numérica tem um valor de zero, isto é marcado no mapa de bits e não é salvado em disco. Strings não vazias são salvas como um byte de tamanho mais o conteudo da string.
  • Geralmente utiliza muito menos espaço de disco que tabelas de tamanho fixo.
  • Cada registro utiliza apenas o espeço necessário. Se um registro aumenta, ele é separado em varios pedaços, de acordo com a necessidade. Isto resulta em fragmentação do registro.
  • Se você atualiza uma linha com informações que ultrapassam o seu tamanho, a linha será fragmentada. Neste caso, você pode precisar executar myisamchk -r de tempos em tempos para obter melhor performance. Use myisamchk -ei nome_tabela para algumas estatísticas.
  • Não é fácil de recontruí-la após uma falha, pois um registro pode ser fragmentado em muitos pedaços e um link (fragmento) pode ser perdido.
  • O tamanho esperado para registros de tamanho dinâmico é:
·                3
·                + (número de colunas + 7) / 8
·                + (número de colunas char)
·                + tamanho empacotado de colunas numéricas
·                + tamanho das strings
·                + (número de colunas NULL + 7) / 8

Existe uma penalidade de 6 bytes para cada link. Um registro dinâmico é ligado sempre que uma atualização causa um aumento do registro. Cada novo link terá pelo menos 20 bytes, assim o próximo aumento estará, provavelemente, no mesmo link. Se não, haverá outro link. Você pode checar quantos links existem com myisamchk -ed. Todos os links podem ser removidos com myisamchk -r.

Características de Tabelas Compactadas

Este é um tipo somente leitura que é gerado com a ferramenta opcional myisampack (pack_isam para tabelas ISAM):

  • Todas as distribuições MySQL, mesmo aquelas existentes antes do MySQL se tornar GPL, podem ler tabelas que forma compactadas com myisampack.
  • Tabelas compactadas utilizam muito pouco espaço em disco. Isto minimiza o uso de disco, o que é muito bom quando se utiliza discos lentos (com CD-ROMs).
  • Cada registro é compactado separadamente (pouca sobrecarga de acesso). O cabeçalho de um registro é fixo (1-3 bytes) dependendo do maior registro na tabela. Cada coluna é compactada diferentemente. Alguns dos tipos de compactação são:
    • Existe, geralmente, uma tabela Huffman diferente para cada coluna.
    • Compactação de espaço de sufixos.
    • Compactação de espaço de prefixos.
    • Números com valor 0 são armazenados usando 1 bit.
    • Se os valores em uma coluna inteira tem uma faixa pequena, a coluna é armazenada usando o menor tipo possível. Por exemplo, uma coluna BIGINT (8 bytes) pode ser armazenada como uma coluna TINYINT (1 byte) se todos os valores estão na faixa de 0 a 255.
    • Se uma coluna tem apenas um pequeno conjunto de valores possíveis, o tipo de coluna é convertido para ENUM.
    • Uma coluna pode usar uma combinação das compactações acima.
  • Pode tratar registros de tamanho fixo ou dinâmico.
  • Pode ser descompactada com myisamchk.

Problemas com Tabelas MyISAM

O formato do arquivo que o MySQL usa para armazenar dados tem sido testado extensivamente, mas sempre há circunstâncias que podem fazer com que tabelas de banco de dados sejam corrompidas.

Tabelas MyISAM Corrompidas

Mesmo se o formato MyISAM for muito confiável (todas as alterações na tabela são escritas antes da instrução SQL retornar), você ainda pode ter tabelas corrompidas se algum dos seguintes itens ocorrer:

  • O processo mysqld ser finalizado no meio de uma escrita.
  • Finalização inesperada do computador (por exemplo, se o computador é desligado).
  • Um erro de hardware.
  • Você estar usando um programa externo (como myisamchk) em uma tabela aberta.
  • Um bug de um software no código MySQL ou MyISAM.

Os sintomas típicos de uma tabela corrompida são:

  • Você obtem o erro Incorrect key file for table: '...'. Try to repair it enquanto seleciona dados da tabela.
  • Consultas não encontram linhas em uma tabela ou retornam dados incompletos.

Você pode verificar se uma tabela está ok com o comando CHECK TABLE.

Você pode repara um tabela corrompida com REPAIR TABLE.

Você também pode repará-la, quando o mysqld não estiver em execução com o comando myisamchk. sintaxe myisamchk.

Se a sua tabela estiver muito corrompida você deve tentar encontrar o razão!

Neste caso, a coisa mais importante de saber é se a tabela foi corrompida porque o mysqld foi finalizado (pode se verificar isto facilmente verificando se há uma linha restarted mysqld recente no arquivo de erro do mysql. Se este não é o caso, então você deve tentar fazer um caso de teste disto.

O Cliente está usando a tabela ou não a fechou de forma apropriada

Cada arquivo .MYI do MyISAM tem um contador no cabeçalho que pode ser usado para verificar se uma tabela foi fechada apropriadamente.

Se você obteve o seguinte aviso de CHECK TABLE ou myisamchk:

# clients is using or hasn't closed the table properly

isto significa que este contador eta fora de sincronia. Insto não significa que a tabela está corrompida, mas significa que você poderia pelo menos fazer uma verificação na tabeal para verificar se está ok.

O contador funciona da seguinte forma:

  • A primeira vez que a tabela é atualizada no MySQL, um contador no cabeçalho do arquivo de índice é incrementado.
  • O contador não é alterado durante outras alterações.
  • Quando a última intância da tabela é fechda (devido a um FLUSH ou porque não há espaço na cache de tabelas) o contador é decremetado se a tabela tiver sido atualizada em qualquer ponto.
  • Quando você raparar a tabela ou verificá-la e ela estiver ok, o contador é zerado.
  • Para evitar problemas com interações com outros processos que podem fazer uma verificação na tabela, o contador não é decrementado no fechamento se ele for 0.

Em outras palavras, o único modo dele ficar fora de sincronia é:

  • As tabelas MyISAM são copiadas sem um LOCK e FLUSH TABLES.
  • O MySQL ter falhado entre uma atualização e o fechamento final. (Note que a tabela pode ainda estar ok já que o MySQL sempre faz escritas de tudo entre cada instrução.)
  • Alguém ter feito um myisamchk --recover ou myisamchk --update-state em uma tabela que estava em uso por mysqld.
  • Muitos servidores mysqld estrem usando a tabela e um deles tiver feito um REPAIR ou CHECK da tabela enquanto ela estava em uso por outro servidor. Nesta configuração o CHECK é seguro de se fazer (mesmo se você obter ovisos de outros servidor), mas REPAIR deve ser evitado pois ele atualmente substitui o arquivo de dados por um novo, o qual não é mostrado para os outros servidores.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s