Metadados da aplicação e índices do banco

A reconstrução de um índice pode terminar sem nenhum erro e deixar a busca do sistema exatamente como estava. Em sistemas que mantêm as ordens de navegação na aplicação, a mesma ordem existe em três lugares diferentes, e cada manutenção corrige apenas o lugar que alcança. Separar esses lugares indica qual correção resolve cada sintoma.

A ordem da aplicação

Uma ordem é uma forma de percorrer uma tabela, identificada por um nome. A definição de uma ordem reúne três partes:

• Nome: identifica a ordem para a aplicação, como PRODUTOS_DESC, a ordem de descrição dos produtos.
• Expressão: os valores usados para ordenar e buscar, como a descrição de busca do produto.
• Condição: quando existe, define quais linhas pertencem à ordem, como apenas os produtos ativos.

Uma tabela pode ter várias ordens. A troca da ordem ativa muda a forma de buscar e de percorrer os registros, sem mudar os dados.

Três lugares para a mesma ordem

• Tabela de controle: uma tabela do banco em que a aplicação registra as ordens de cada tabela, com o nome, a expressão, a condição e o nome do índice correspondente no MySQL. Esses dados, que descrevem outros objetos, se chamam metadados. Para o MySQL, a tabela de controle é uma tabela comum.
• Índice do banco: o objeto do MySQL, com nome e colunas próprios, mantido pelo engine.
• Ordem em memória: a representação que a camada de tradução monta na estação, a partir da tabela de controle, ao abrir a tabela. É ela que orienta as condições e a ordem das consultas enviadas.

No cenário, a tabela de controle se chama controle_ordens e tem as colunas tabela, ordem, expressao, condicao e indice. A ordem PRODUTOS_DESC aponta para o índice produtos_desc, que tem as colunas descricao_busca e id. A coluna id entra no índice como desempate, e por isso o índice tem uma coluna a mais do que a expressão da ordem.

Cada banco usado pela aplicação tem a própria tabela de controle. Em muitos sistemas, a camada de tradução cria essa tabela sozinha, na primeira conexão a um banco, e uma cópia ou restauração do banco precisa levar a tabela de controle junto com as tabelas de dados.

O nome do índice registrado na tabela de controle liga os dois primeiros lugares. A coincidência de nomes é necessária, mas não comprova que as colunas, a expressão, a condição e os valores derivados estão corretos.

O que cada manutenção alcança

• Comandos de manutenção do MySQL: verificam ou reconstroem o índice do banco conforme a definição dele. Esses comandos não leem a tabela de controle e não conhecem a regra da aplicação.
• Edição da tabela de controle: muda a descrição da ordem sem criar nem ajustar o índice do banco.
• Rotina de recriação da aplicação: coordena a tabela de controle, as colunas derivadas e o índice do banco.

Se o índice tem as colunas certas, mas a tabela de controle registra uma condição errada, a reconstrução do índice não corrige a condição. Se uma coluna derivada está desatualizada, refazer o índice com os mesmos valores não recalcula a expressão.

Quatro situações de desacordo

• Ordem registrada e índice ausente: o índice foi apagado por fora da aplicação, a tabela foi recriada com CREATE TABLE ... SELECT, ou a tabela e a tabela de controle vieram de cópias de momentos diferentes. A aplicação continua pedindo a ordem, e o MySQL responde por outro caminho, com lentidão e sem erro.
• Ordem registrada e índice danificado: o índice existe, mas a estrutura foi danificada por uma falha de gravação, por um problema de disco ou pela restauração de arquivos copiados com o serviço em execução. O efeito pode ser uma mensagem de erro ou um resultado errado, como uma busca que não encontra um registro existente.
• Índice presente e ordem sem registro: uma recriação interrompida pode deixar o índice sem a linha correspondente na tabela de controle. A aplicação não reconhece a ordem, mesmo com o índice no banco. Uma tabela de controle perdida ou recriada vazia, por exemplo depois de uma restauração parcial, produz o mesmo efeito em todas as tabelas do banco de uma só vez.
• Índice íntegro e valor derivado errado: o índice está fisicamente correto, mas organiza os valores desatualizados de uma coluna derivada. A verificação física não encontra nenhum erro.

Comparar a tabela de controle com o banco

Duas consultas mostram o desacordo entre a tabela de controle e os índices do banco:

• SELECT o.ordem, o.indice, s.INDEX_NAME, GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) AS colunas FROM controle_ordens o LEFT JOIN information_schema.STATISTICS s ON s.TABLE_SCHEMA = 'loja' AND s.TABLE_NAME = o.tabela AND s.INDEX_NAME = o.indice WHERE o.tabela = 'produtos' GROUP BY o.ordem, o.indice, s.INDEX_NAME;: mostra cada ordem registrada, o índice correspondente e as colunas dele;
• SELECT DISTINCT s.INDEX_NAME FROM information_schema.STATISTICS s LEFT JOIN controle_ordens o ON o.tabela = s.TABLE_NAME AND o.indice = s.INDEX_NAME WHERE s.TABLE_SCHEMA = 'loja' AND s.TABLE_NAME = 'produtos' AND s.INDEX_NAME <> 'PRIMARY' AND o.indice IS NULL;: mostra os índices do banco que não têm registro na tabela de controle.

Na primeira consulta, a coluna INDEX_NAME em NULL indica uma ordem registrada sem o índice correspondente. A lista de colunas, porém, não se compara como texto com a expressão registrada. A expressão pode usar uma função, para a qual a aplicação criou uma coluna derivada, e o índice pode ter a coluna de desempate no fim. Uma diferença de escrita não é, sozinha, uma diferença de significado. Em um índice funcional do MySQL 8, a coluna COLUMN_NAME vem vazia, e a expressão aparece em outra coluna dos metadados.

Na segunda consulta, um índice sem registro nem sempre é um erro, porque alguns índices servem a consultas que não dependem das ordens da aplicação.

A recriação pela aplicação

A correção de um desacordo passa pela rotina de recriação da aplicação, porque só ela conhece a expressão e a condição de cada ordem. Em termos gerais, a recriação percorre estas etapas:

1. retira o registro antigo da tabela de controle;
2. apaga ou substitui o índice antigo no banco;
3. prepara as colunas derivadas, quando a ordem precisa delas, e cria o índice novo;
4. registra a definição da ordem na tabela de controle.

Essas etapas não formam uma operação única. Uma falha no meio, por falta de permissão, de espaço em disco ou por um defeito na tabela, pode deixar os três lugares em desacordo, e a ordem exata das etapas depende da versão da aplicação.

Uma tabela com um defeito físico que impede a leitura dos dados precisa ser tratada antes, porque a recriação lê a tabela inteira para montar o índice. Depois da recriação, a tabela precisa ser reaberta na aplicação, para que a ordem em memória seja montada de novo, e a busca que apresentava o problema precisa ser testada.

Rotinas com nomes parecidos podem fazer trabalhos diferentes. Uma rotina que apenas reconstrói os índices existentes não recria as ordens nem recalcula as colunas derivadas, e o procedimento de manutenção do sistema indica qual rotina usar em cada caso. A edição manual da tabela de controle pode deixar uma ordem registrada sem o índice ou sem as colunas derivadas de que ela precisa.