MySQL: como auditar índices não usados em topologias com réplicas

A view sys.schema_unused_indexes só enxerga o servidor consultado. Uma ferramenta da Percona cruza o resultado entre primary e réplicas antes de sugerir qualquer DDL de remoção.

A view sys.schema_unused_indexes só enxerga o servidor consultado. Uma ferramenta da Percona cruza o resultado entre primary e réplicas antes de sugerir qualquer DDL de remoção.

O problema que o sys schema esconde

Todo schema MySQL↳MySQL8 conteúdosMySQL + Adminer + Docker Compose: montando rapidamente um ambiente para usoData · abr 2019Banco de dados MYSQL no PHPStormDev (Back & Front) · jul 2019Criando um ambiente de desenvolvimento PHP mínimo com Docker – Parte 3Dev (Back & Front) · out 2019Ver tudo em Data → com alguns anos de estrada acumula índice que ninguém lembra de ter criado. Veio de um relatório aposentado, de uma migração de ORM, de um "vai que precisa" que nunca precisou. Cada um desses índices custa: escrita extra em todo INSERT e UPDATE, mais pressão no buffer pool, backup maior, DDL mais lento.

Desde o MySQL 5.7, o sys schema traz a view sys.schema_unused_indexes, construída sobre os contadores de I/O por índice do Performance Schema. Uma query lista tudo com zero leituras desde que o servidor subiu. É tentador transformar essa lista direto em DROP INDEX.

O problema é que esses contadores são locais ao servidor consultado. Eles não sabem nada sobre o que as réplicas estão fazendo. Na maioria das topologias, o primary recebe as escritas e o OLTP leve, enquanto a leitura pesada vai para outro lugar: uma réplica de relatório que roda fechamento mensal, um reader de Aurora que atende o dashboard, um nó de Galera ou Percona XtraDB Cluster que o load balancer escolhe mais para um tipo de consulta.

Um índice pode aparecer com zero leituras no primary e ser a única coisa que mantém a query de relatório de uma réplica abaixo de um segundo. Derruba no primary, o DDL replica, e a réplica cai para table scan completo. Há ainda um segundo detalhe traiçoeiro: os contadores zeram a cada restart. Um servidor que recebeu patch na terça só viu alguns dias de tráfego, e um job semanal ou relatório trimestral ainda nem rodou.

Como o check_unused_indexes resolve isso

Arunjith Aravindan publicou em 1º de novembro de 2026, no blog da Percona, o check_unused_indexes, um script Python↳Python56 conteúdosVSCode + Python + Alexa: Desenvolva e teste skills para alexa localmente com pythonDev (Back & Front) · out 2025Dominando decoradores em Python: um guia completo com exemplosDev (Back & Front) · jan 2025Desenvolvimento de software: diferenças entre Python, JavaScript e JavaGestão Dev & TI · nov 2024Ver tudo em Dev (Back & Front) → que faz a mesma pergunta em todo servidor de uma topologia de replicação e só reporta um índice quando a resposta é "não usado" em todos eles. O projeto roda em três etapas, com dois pontos de interrupção segura.

Na etapa 1, o script descobre a topologia. Ele se conecta a cada servidor do inventário e lê SHOW REPLICA STATUS (ou SHOW SLAVE STATUS em versões mais antigas), @@wsrep_cluster_name para identificar membros de Galera e PXC, information_schema.replica_host_status para Aurora, e a variável Uptime. A partir daí ele caminha a partir do primary: uma réplica cujo source bate com o endereço do primary entra na lista, depois as réplicas dessa réplica, e assim por diante, incluindo replicação encadeada ou circular e peers de cluster Galera.

Na etapa 2, o script consulta sys.schema_unused_indexes em cada nó resolvido. Dois filtros valem sempre: schemas de sistema (sys, mysql, performance_schema, information_schema) ficam de fora, e qualquer índice cuja primeira coluna participe de uma foreign key é excluído, porque o MySQL exige índice nessas colunas e não deixaria derrubar mesmo que você quisesse. Dá para restringir mais com --include-schema e --include-table, ou ignorar schemas extras com --ignore-schema.

Na etapa 3, o script faz a interseção: parte da lista do primary e remove tudo que não aparece também na lista de cada outro nó. O que sobra é o conjunto de índices com zero leitura em todo lugar verificado, impresso como tabela seguida do DDL para rodar no primary.

Instalação e primeira execução

O script é um único arquivo. Precisa de Python 3.6 ou mais recente e da biblioteca pymysql.

git clone https://github.com/Percona-Lab/check_unused_indexes.git
cd check_unused_indexes
pip3 install --user pymysql

As credenciais vêm de um arquivo de opções do MySQL, por padrão ~/.my.cnf (ou outro, apontado com -c). A conta precisa de leitura em sys.schema_unused_indexes e information_schema, e permissão para rodar SHOW REPLICA STATUS. O script só lê, nunca altera nada nos servidores.

Para um teste rápido, dá para listar os servidores direto na linha de comando. Para qualquer coisa maior, prefira um arquivo de inventário, um alias=host:porta por linha:

# inventory.txt
db-primary=10.0.0.10:3306
db-replica-1=10.0.0.11:3306
db-replica-2=10.0.0.12:3306
db-analytics=10.0.0.13:3306

python3 check_unused_indexes.py --inventory inventory.txt --primary db-primary

Um detalhe que vale conferir antes da primeira rodada: a descoberta de topologia compara o Source_Host de cada réplica com os endereços do inventário como strings puras. Se as réplicas foram configuradas com CHANGE REPLICATION SOURCE TO SOURCE_HOST='db-primary.internal', o inventário precisa usar esse mesmo hostname, não o IP. Topologia menor do que o esperado quase sempre tem essa causa.

O que a saída revela na prática

No exemplo do próprio projeto, com um primary e duas réplicas (a db-analytics do inventário replica de outro primary e fica de fora), a etapa 2 encontra 12 candidatos no primary, 8 na primeira réplica e 5 na segunda. A interseção final fica em 2 índices: idx_legacy_status em app_db.orders e idx_created_at em app_db.users.

Essa diferença entre 12 e 2 é todo o propósito da ferramenta. Espere uma diferença assim na maioria dos sistemas reais.

Arunjith Aravindan, autor do check_unused_indexes na PerconaThat gap between 12 and 2 is the whole point of the tool. Expect a gap like this on most real systems.

Dos 12 candidatos vistos isoladamente no primary, 10 carregavam tráfego de leitura em alguma réplica. Rodar sys.schema_unused_indexes sozinho ali teria apagado índice que sustentava consulta real em outro nó. Para quem só quer o DDL, sem a tabela, a flag -qs (--quiet-summary) resume a saída.

O que acontece quando um nó não responde

A interseção só vale o que as entradas valem. Se uma réplica cai e o script simplesmente a ignora, o resultado parece completo sem ser. A versão atual trata isso por situação:

O aviso da etapa 1 merece atenção redobrada: se uma réplica já está fora do ar quando o script começa, ele não consegue ler o status de replicação dela e não tem como saber que ela pertence àquela topologia. A única saída é listar os endereços do inventário que não respondeu. Vale ler esse aviso antes de confiar no resultado. --force existe para o caso de um nó sabidamente desativado para sempre, não para contornar uma instabilidade de rede passageira.

Do candidato ao índice removido

O script imprime DDL e para por aí: nunca executa nada sozinho. Por padrão, a sugestão é tornar o índice invisível em vez de derrubar:

ALTER TABLE app_db.users ALTER INDEX idx_created_at INVISIBLE;

Índices invisíveis existem desde o MySQL 8.0. O otimizador para de usar o índice, mas o MySQL continua mantendo-o em toda escrita. É uma mudança de metadado, então desfazer também é, o que torna o rollback questão de segundos, bem diferente de um DROP INDEX seguido de recriação, que em tabela grande pode levar horas. O rollout que faz sentido seguir:

1. Ler a lista de candidatos pessoalmente e confirmar que cada nome faz sentido.
2. Rodar os ALTER ... INVISIBLE no primary; eles replicam para todos os nós da topologia.
3. Esperar um ciclo de negócio completo, incluindo fechamento mensal e qualquer batch semanal, acompanhando slow query log e métricas.
4. Se algo piorar, rodar ALTER INDEX ... VISIBLE no índice afetado e voltar ao ponto de partida.
5. Só depois de confiante, gerar o DROP INDEX de fato com --action drop (ou --action both para as duas formas de DDL).

Antes do último passo, dá para testar uma query específica sem tornar o índice visível para a aplicação inteira, setando use_invisible_indexes=on em optimizer_switch só para a sessão atual, com SET_VAR(optimizer_switch = 'use_invisible_indexes=on').

Limites que valem registro

Nenhuma ferramenta enxerga tráfego que ainda não aconteceu, e o check_unused_indexes não foge dessa regra. Alguns pontos que o próprio autor destaca:

• Só servidores listados no inventário são verificados; uma réplica esquecida é invisível para o script, e tudo que ela atende também.
• sys.schema_unused_indexes exclui chaves primárias, mas não índices UNIQUE: um índice único pode ter zero leituras e ainda estar aplicando uma regra de negócio. Vale conferir cada candidato com SHOW CREATE TABLE antes de derrubar.
• As estatísticas começam no último restart; o aviso de uptime ajuda, mas definir o que é "tempo suficiente" é critério de quem opera o workload.
• O Performance Schema precisa estar ativo; se alguém desligou, a view não tem o que reportar.
• Readers de Aurora são identificados por server ID ou por convenção de alias (nomear o writer aurora e os readers aurora-reader-1, aurora-reader-2), e hosts são comparados como string literal, então o inventário precisa usar o mesmo nome que aparece em Source_Host.
• A ferramenta encontra índice não usado, não índice duplicado; para redundância entre índices que cobrem as mesmas colunas, o caminho continua sendo o pt-duplicate-key-checker do Percona Toolkit.

O projeto está no GitHub, em Percona-Lab/check_unused_indexes, sob licença Apache 2.0, como arquivo único fácil de ler antes de apontar para algo que importa. Group replication e réplicas multi-source ainda não têm suporte declarado; quem encontrar esse caso tem canal aberto para issue e pull request.

Fonte

1. Percona Database Blog: Multi-Node Unused Index Verification, por Arunjith Aravindan, 1º de novembro de 2026.

Fonte: Percona Database Blog