Revisão de banco de dados

Este capítulo reúne o conteúdo de banco de dados para revisão: os erros mais comuns, o checklist antes de uma manutenção, as diferenças das versões antigas, o glossário, as referências e perguntas que misturam vários assuntos.

Erros comuns

• Confundir a pasta compartilhada com o acesso ao banco. A pasta usa a porta 445, e o MySQL usa, por padrão, a porta 3306, com conta e permissões próprias.
• Consultar o servidor ou o banco errado. Uma ferramenta conectada a outro servidor mostra outro estado, e o comando SELECT @@hostname, DATABASE(); confirma o destino.
• Comparar uma coluna de texto com um número. O MySQL converte o texto de cada linha em número, pode trazer códigos diferentes, como 00123 e 123, e deixa de usar o índice de forma eficiente.
• Procurar o NULL com o sinal de igual. A comparação com NULL nunca é verdadeira, e a forma certa é IS NULL.
• Esquecer o último dia em um BETWEEN de datas. Em uma coluna com horário, a data sem horário vale a meia-noite do início do dia.
• Somar valores depois de uma junção. A junção repete cada venda uma vez por item, e a soma do total das vendas fica multiplicada.
• Contar no banco sem a condição da marca de exclusão. A contagem inclui as linhas que o sistema esconde.
• Comparar o Rows do SHOW TABLE STATUS com uma contagem exata. No InnoDB, o Rows é uma estimativa e, a partir do MySQL 8, pode vir de um valor guardado pelo servidor horas antes.
• Deixar uma transação aberta na ferramenta de consulta. Em uma tabela InnoDB, a alteração fica invisível para as outras estações e segura os bloqueios até o COMMIT ou o ROLLBACK.
• Contar com o ROLLBACK em uma tabela MyISAM. A alteração vale assim que o comando termina, e só uma cópia do estado anterior permite voltar atrás.
• Encerrar uma gravação em uma tabela MyISAM sem conferir os dados depois. O KILL não desfaz nada, e as linhas que o comando já tinha alterado continuam alteradas.
• Corrigir uma coluna de origem sem as colunas derivadas. A busca passa a usar um valor antigo, com os arquivos íntegros.
• Corrigir na loja um dado que vem da matriz. A próxima sincronização devolve o valor da matriz, ou a loja passa a divergir dela.
• Tratar a lentidão como prova de dano. A lentidão pode vir de um índice ausente, de um plano ruim, de uma espera por bloqueio ou do volume de dados.
• Reconstruir uma tabela para resolver uma espera por bloqueio. A espera termina quando a transação ou o comando que bloqueia termina, e não com uma manutenção.
• Usar o REPAIR TABLE em uma tabela InnoDB. A nota de falta de suporte não é um reparo.
• Ler o OK do CHECK TABLE como prova de integridade completa. A verificação tem alcance limitado e não confere as regras da aplicação.
• Reparar sem preservar os arquivos e sem anotar as medidas. Sem a comparação, as linhas descartadas pelo reparo passam despercebidas.
• Confiar no status OK de um reparo MyISAM. O aviso Number of rows changed mostra linhas descartadas, mesmo com o OK no fim.
• Usar o USE_FRM sem o valor anotado do contador. O contador volta para o maior identificador existente mais um, e os números de linhas já excluídas podem voltar a ser gerados.
• Copiar os arquivos do banco com o serviço em execução. A cópia pode ficar inconsistente e falhar justamente na restauração.
• Considerar uma exportação completa só porque o arquivo existe. O arquivo completo termina com a linha Dump completed, e a prova final é a restauração de teste.
• Restaurar por cima dos dados atuais sem preservá-los. As alterações feitas depois da cópia desaparecem, e a restauração deixa de ter volta.
• Recriar uma tabela com CREATE TABLE ... SELECT e esquecer os índices. A tabela nasce sem índices, sem chave primária e sem a numeração automática, e as telas ficam lentas.
• Deixar a recuperação forçada do InnoDB na configuração. O InnoDB recusa as alterações de dados e deixa de executar partes da recuperação normal.
• Usar o myisamchk com o serviço em funcionamento. O servidor e a ferramenta podem alterar os mesmos arquivos ao mesmo tempo.
• Deduzir o engine de uma tabela pela versão do servidor. O padrão pode ter sido alterado no arquivo de configuração, e o engine de cada tabela aparece no SHOW CREATE TABLE.
• Supor que um servidor antigo se comporta como um novo. No MySQL 5.1, o log binário vem desligado, o utf8mb4 e vários comandos de diagnóstico não existem, e alguns procedimentos mudam.

Checklist antes de uma manutenção

O checklist não substitui o raciocínio. O checklist serve para que as verificações básicas não sejam esquecidas.

Identificação

• O servidor, o banco e a versão do MySQL foram confirmados?
• O engine de cada tabela envolvida foi conferido?
• A contagem exata, o maior identificador e o próximo valor do contador foram anotados?
• O defeito físico ou lógico foi sustentado por evidências?

Preservação

• O log de erros do MySQL e o registro de erros da aplicação foram guardados?
• Existe uma cópia consistente, adequada ao engine e guardada fora do servidor?
• A cópia foi testada em um destino isolado?
• A tabela de controle das ordens e as definições das tabelas foram guardadas junto com os dados?

Execução

• A janela de manutenção está combinada com a loja?
• Há espaço em disco para a cópia nova da tabela e para os arquivos temporários?
• Nenhuma transação aberta segura a tabela?
• A forma de manter as colunas derivadas e a tabela de controle está definida?

Validação

• As medidas de antes foram repetidas com as mesmas consultas?
• O resultado do comando foi lido por inteiro, inclusive os avisos?
• As buscas e as telas afetadas foram testadas na aplicação?

As diferenças das versões antigas

As versões antigas do MySQL, como a 5.1, continuam em uso em muitas instalações. As principais diferenças em relação às versões atuais formam uma lista de conferência para esses servidores:

• o engine padrão do servidor é o MyISAM, mas o instalador do Windows costuma definir o InnoDB como padrão no arquivo de configuração;
• o InnoDB pode estar desligado na configuração, com a opção skip-innodb, e então o SHOW ENGINE INNODB STATUS falha com o erro 1286, e a criação de uma tabela InnoDB falha com o mesmo erro ou cai no engine padrão, conforme a opção NO_ENGINE_SUBSTITUTION;
• a variável do engine padrão se chama storage_engine;
• o InnoDB guarda as tabelas no arquivo compartilhado ibdata1, a menos que a opção de um arquivo por tabela tenha sido ligada, e esse arquivo nunca diminui;
• o contador AUTO_INCREMENT do InnoDB é recalculado a partir do maior identificador depois de cada reinício;
• o conjunto de caracteres padrão é o latin1, e o utf8mb4 não existe;
• o modo estrito vem desligado no padrão do servidor, e o instalador do Windows costuma ligá-lo no arquivo de configuração;
• o log binário vem desligado, e a restauração de uma cópia não alcança as alterações posteriores a ela;
• os bloqueios de metadados, o performance_schema e o esquema sys não existem;
• a espera por um bloqueio de tabela aparece na lista de conexões com o estado Locked;
• as tabelas INNODB_TRX e INNODB_LOCK_WAITS só existem com o InnoDB Plugin, e a seção TRANSACTIONS do SHOW ENGINE INNODB STATUS mostra as transações abertas;
• o EXPLAIN funciona só com SELECT, e a coluna filtered aparece só no EXPLAIN EXTENDED;
• o InnoDB não aceita índices de texto completo, e uma tabela MyISAM com esse tipo de índice não se converte sem mudanças;
• a reconstrução de uma tabela InnoDB impede as gravações durante toda a operação;
• a forma do FLUSH TABLES que bloqueia só as tabelas indicadas não existe, e a cópia dos arquivos MyISAM se faz com o serviço parado;
• uma mudança no tamanho dos arquivos de registro do InnoDB, feita só na configuração, impede a inicialização do InnoDB;
• o reparo automático das tabelas MyISAM, desligado por padrão, usa a opção myisam-recover do arquivo de configuração;
• uma conta com a senha no formato anterior ao MySQL 4.1 é recusada pelos programas atuais, com o erro 2049;
• as ferramentas da mesma versão do servidor, como o mysqldump, evitam incompatibilidades.

Glossário

• AUTO_INCREMENT: a numeração automática de uma coluna, com lacunas normais na sequência.
• Autocommit: a confirmação automática de cada comando, ligada por padrão no MySQL.
• B-tree: a estrutura em árvore dos índices comuns do MySQL.
• Backup físico: a cópia dos arquivos do banco, feita com o serviço parado ou com uma ferramenta própria.
• Backup lógico: a exportação da estrutura e dos dados em comandos SQL, como a feita pelo mysqldump.
• Banco: um conjunto de tabelas com nome próprio, também chamado de schema.
• Bloqueio: a reserva de uma linha ou de uma tabela por uma transação ou por um comando.
• Bloqueio de metadados: o bloqueio que protege a definição de uma tabela, existente a partir do MySQL 5.5.
• Camada de acesso a dados: a parte da aplicação que transforma os pedidos das telas em comandos SQL.
• Camada de tradução: a camada de acesso a dados que converte a navegação registro por registro em consultas SQL.
• Cardinalidade: a estimativa da quantidade de valores diferentes em um índice.
• Chave estrangeira: a regra declarada que liga a coluna de uma tabela filha à chave de uma tabela pai.
• Chave primária: a coluna ou o conjunto de colunas que identifica cada linha de forma única.
• Collation: as regras de comparação e de ordenação dos textos.
• Coluna derivada: a coluna cujo valor a aplicação calcula a partir de outras colunas.
• COMMIT: o comando que confirma uma transação.
• Conjunto de caracteres: a correspondência entre os caracteres e os bytes guardados.
• Contador: o próximo valor que o AUTO_INCREMENT de uma tabela vai gerar.
• Deadlock: o ciclo de esperas entre transações, que o InnoDB desfaz cancelando uma delas.
• Dicionário de dados: o lugar em que o MySQL 8 guarda as definições das tabelas.
• Engine: o mecanismo de armazenamento de uma tabela, como o InnoDB e o MyISAM.
• Estatísticas: as estimativas sobre os dados que o otimizador usa para escolher o plano.
• Exclusão lógica: a marca que esconde uma linha sem removê-la da tabela.
• frm: o arquivo de definição das tabelas, até o MySQL 5.7.
• ibd: o arquivo de dados e de índices de uma tabela InnoDB, no arranjo de um arquivo por tabela.
• ibdata1: o arquivo compartilhado do InnoDB, que nunca diminui e, nas instalações antigas, guarda também as tabelas InnoDB.
• Índice: a estrutura ordenada que leva o servidor direto às linhas.
• Índice composto: o índice com várias colunas, em que a ordem das colunas importa.
• Índice de cobertura: o índice que contém todas as colunas de que uma consulta precisa.
• Índice secundário: no InnoDB, qualquer índice que não seja o da chave primária.
• information_schema: o banco especial que descreve os bancos, as tabelas, as colunas e os índices.
• InnoDB: o engine com transações, bloqueio por linha e recuperação automática depois de uma queda, padrão do servidor a partir do MySQL 5.5.
• InnoDB Plugin: a versão mais nova do InnoDB oferecida como opção no MySQL 5.1 e ativada pelo arquivo de configuração.
• ISAM: o modelo de acesso sequencial indexado, em que a aplicação percorre um registro de cada vez.
• Isolamento: a regra que define o que uma transação enxerga das alterações das outras.
• Janela de manutenção: o período combinado para uma operação que afeta o uso do sistema.
• Junção: a combinação das linhas de duas tabelas por uma condição.
• Latência: o tempo de ida e volta de um pedido pela rede.
• Locked: o estado, no MySQL 5.1, de um comando que espera o bloqueio de uma tabela, chamado de Waiting for table level lock nas versões seguintes.
• Log binário: o registro das alterações confirmadas no servidor, usado na recuperação até um horário.
• Log de consultas lentas: o registro das consultas que passam de um tempo limite.
• Log de erros: o arquivo em que o MySQL registra as falhas e as mensagens do serviço.
• Metadados: os dados que descrevem outros objetos, como a tabela de controle das ordens.
• Modo estrito: o modo em que o MySQL recusa valores inválidos, em vez de ajustá-los com um aviso.
• MYD: o arquivo de dados de uma tabela MyISAM.
• MYI: o arquivo de índices e de estado de uma tabela MyISAM.
• MyISAM: o engine sem transações, com bloqueio por tabela e arquivos separados para os dados e os índices, comum nas instalações antigas.
• myisamchk: a ferramenta que verifica e repara os arquivos MyISAM com o serviço parado.
• mysqlcheck: a ferramenta que envia comandos de verificação e de reparo ao servidor.
• mysqldump: a ferramenta de exportação lógica de bancos e de tabelas.
• NULL: a marca de ausência de valor em uma coluna.
• Ordem: a forma de percorrer uma tabela, com nome, expressão e condição, definida pela aplicação.
• Otimizador: a parte do MySQL que escolhe o caminho de cada consulta.
• Plano de consulta: o caminho escolhido pelo otimizador, mostrado pelo EXPLAIN.
• Prefixo à esquerda: a regra de que um índice composto serve às buscas que usam as suas primeiras colunas.
• Recuperação forçada: o modo de emergência do InnoDB, ligado pela opção innodb_force_recovery, para exportar dados danificados.
• Registro órfão: a linha que aponta para outra que não existe mais.
• ROLLBACK: o comando que desfaz as alterações ainda não confirmadas de uma transação.
• Schema: outro nome do banco no MySQL.
• sdi: o arquivo com a definição de uma tabela MyISAM no MySQL 8.
• Sleep: o estado de uma conexão aberta sem nenhum comando em execução, que ainda pode ter uma transação aberta.
• Tabela de controle: a tabela em que a aplicação registra as ordens e os índices correspondentes.
• Transação: o conjunto de alterações que valem juntas, ou não valem.
• Varredura completa: a leitura de todas as linhas de uma tabela.

Para continuar estudando

• Replicação: a cópia contínua das alterações de um servidor para outro, com o log binário.
• Procedimentos armazenados e gatilhos: a lógica guardada no próprio banco e executada pelo servidor.
• Particionamento: a divisão de tabelas grandes em partes, por data ou por faixa de valores.
• Configuração do servidor: as variáveis de memória e de desempenho do arquivo de configuração, como o tamanho do cache do InnoDB.
• Atualização de versões: o caminho de um servidor 5.1 até as versões atuais, com as mudanças de padrão de cada etapa.

Referências

As referências a seguir reúnem a documentação oficial do MySQL sobre os principais assuntos de banco de dados. O material está em inglês e corresponde ao manual do MySQL 8.0, que indica, em muitos tópicos, as diferenças das versões anteriores. Os manuais das versões antigas, como o da 5.1, saíram do site e ficam disponíveis em PDF no arquivo de documentação do MySQL.

Conexão, contas e consultas

• MySQL, conexão pela linha de comando: as opções de servidor, porta, conta e senha na conexão;
• MySQL, mensagens de erro do servidor: os códigos de erro do servidor, como o 1045, o 1062 e o 1205;
• MySQL, mensagens de erro dos clientes: os códigos de erro dos programas clientes, como o 2003 e o 2049;
• MySQL, nomes de contas: a forma de usuário e computador de origem das contas;
• MySQL, privilégios: o que cada privilégio permite;
• MySQL, tipos de dados: os tipos numéricos, de texto e de data;
• MySQL, modos do servidor: o modo estrito e as demais opções do sql_mode;
• MySQL, comando SELECT: as cláusulas de uma consulta;
• MySQL, junções: a junção comum e a junção à esquerda;
• MySQL, valores NULL: as comparações e as contas com a ausência de valor;
• MySQL, conjuntos de caracteres e collations: a codificação e as regras de comparação dos textos;
• MySQL, tipos de data e hora: os tipos DATE, DATETIME, TIMESTAMP e TIME.

Estrutura, índices, engines e concorrência

• MySQL, AUTO_INCREMENT no InnoDB: a geração dos identificadores e o comportamento do contador em cada versão;
• MySQL, chaves estrangeiras: as regras entre tabelas e as ações na exclusão;
• MySQL, como os índices são usados: os ganhos e os custos dos índices;
• MySQL, índices de várias colunas: a ordem das colunas e o prefixo à esquerda;
• MySQL, saída do EXPLAIN: o significado de cada coluna do plano;
• MySQL, colunas geradas: as colunas calculadas pelo próprio servidor;
• MySQL, introdução ao InnoDB: as transações, o bloqueio por linha e a recuperação;
• MySQL, engine MyISAM: os arquivos, os limites e as opções do MyISAM;
• MySQL, bloqueio por tabela: o bloqueio das tabelas MyISAM e a ordem em que as leituras e as gravações são atendidas;
• MySQL, níveis de isolamento: o que cada nível enxerga das outras transações;
• MySQL, bloqueios do InnoDB: os tipos de bloqueio e o que cada comando bloqueia;
• MySQL, deadlocks no InnoDB: a detecção dos deadlocks e as formas de reduzi-los.

Manutenção, cópia e recuperação

• MySQL, CHECK TABLE: as opções, o alcance e os riscos da verificação;
• MySQL, REPAIR TABLE: os engines que aceitam o reparo e as opções de cada um;
• MySQL, OPTIMIZE TABLE: a reorganização das tabelas em cada engine;
• MySQL, reparo de tabelas MyISAM: o passo a passo do reparo com o myisamchk;
• MySQL, recuperação forçada do InnoDB: os níveis da opção innodb_force_recovery e os riscos de cada um;
• MySQL, mysqldump: as opções da exportação lógica;
• MySQL, recuperação até um horário: o uso do log binário depois da restauração de uma cópia;
• MySQL, mysqlcheck: a verificação e o reparo de várias tabelas pela linha de comando;
• MySQL, myisamchk: as opções da ferramenta de manutenção do MyISAM;
• MySQL, mysqladmin: os comandos de administração, como o ping e o status;
• MySQL, log de erros: o local e o conteúdo do log de erros;
• MySQL, SHOW PROCESSLIST: as colunas da lista de conexões;
• MySQL, estados das conexões: o significado de cada estado da lista de conexões, como a espera por bloqueio de tabela;
• MySQL, comando KILL: o encerramento de conexões e de comandos, e o que não é desfeito nas tabelas sem transações;
• MySQL, variáveis de status: o significado de cada contador do servidor;
• MySQL, log de consultas lentas: a configuração e a leitura do registro de consultas lentas.

Versões antigas

• MySQL, arquivo de documentação: os manuais das versões antigas, em PDF;
• Manual de referência do MySQL 5.1, em PDF: o manual completo da versão 5.1.