Database Autonomy Service:Mecanismo de diagnóstico SQL baseado em custo

Por que a otimização de SQL é complexa

Cerca de 80% dos problemas de desempenho em bancos de dados têm origem em instruções SQL. No entanto, a otimização de SQL é uma das tarefas mais demoradas na administração de bancos de dados: o plano de execução ideal depende do volume de dados, estatísticas, combinações de índices e estrutura da consulta — fatores que mudam constantemente.

Desenvolvedores frequentemente passam horas identificando consultas lentas, analisando planos de execução e testando alterações de índices. O mecanismo de diagnóstico SQL baseado em custo automatiza esse processo, transformando um fluxo de trabalho manual e dependente de conhecimento especializado em uma operação contínua e escalável.

Como o mecanismo funciona

Este mecanismo atua como um otimizador independente do kernel do banco de dados. Ele coleta estatísticas automaticamente e calcula os custos dos planos de execução sem depender da funcionalidade nativa What-If Tool (WIT) do banco de dados — uma limitação comum em bancos de dados open source, como o MySQL.

O processamento de cada instrução SQL ocorre em quatro etapas:

1. Análise e verificação de SQL: Analise a instrução SQL, verifique a sintaxe, identifique predicados, campos de ordenação, campos de agregação e campos de consulta e construa a árvore sintática. Confirme se todas as tabelas e campos referenciados correspondem ao schema do banco de dados.

2. Geração de índices candidatos: Gere múltiplas combinações de índices a partir da árvore sintática.

3. Avaliação baseada em custo: Utilize um otimizador integrado — independente do kernel do banco de dados — para buscar estatísticas do banco de dados (armazenadas em cache no mecanismo de diagnóstico) e calcular o custo de cada índice candidato e de cada reescrita de SQL candidata. Selecione a opção com o menor custo.

4. Mesclagem de índices e seleção do índice ideal: Ao diagnosticar múltiplas instruções SQL na mesma instância, consolide as recomendações de índices. Identifique índices sobrepostos, índices com prefixos compartilhados e candidatos redundantes para produzir um conjunto de índices mínimo e eficaz.

Recomendações baseadas em custo versus baseadas em regras

Existem duas abordagens para recomendações de otimização de SQL: baseada em regras e baseada em custo. O DAS utiliza o método baseado em custo.

Método baseado em regras

Essa abordagem aplica padrões fixos para gerar índices candidatos, sem avaliar o custo. Para uma consulta como:

SELECT *
FROM t1
WHERE time_created >= '2017-11-25'
AND consuming_time > 1000
ORDER BY consuming_time DESC;

O método baseado em regras gera vários candidatos, mas não oferece como classificá-los:

IX1(time_created)
IX2(time_created, consuming_time)
IX3(consuming_time)
IX4(consuming_time, time_created)

Se a consulta envolver junções de tabelas ou subconsultas, a ambiguidade aumenta ainda mais. O método baseado em regras não consegue determinar qual índice reduz efetivamente o custo de execução.

Método baseado em custo

Já o método baseado em custo avalia cada candidato com base nas estatísticas do banco de dados e seleciona aquele com o menor custo calculado — seguindo a mesma lógica do otimizador do banco de dados. Para problemas polinomiais não determinísticos (NP), em que a avaliação exaustiva é inviável, o mecanismo analisa o maior número possível de candidatos para encontrar uma solução próxima do ideal.

Essa abordagem exige duas capacidades que bancos de dados open source, como o MySQL, não possuem nativamente:

• Capacidade WIT: Necessária para simular o custo de um índice hipotético sem criá-lo de fato. O DAS implementa essa funcionalidade independentemente do kernel do banco de dados.

• Estatísticas: Essenciais para calcular com precisão os custos dos planos de execução. O DAS coleta e armazena essas estatísticas em cache automaticamente.

Validação de capacidade

A qualidade das recomendações do mecanismo é medida continuamente em relação a uma biblioteca de casos de teste baseada em assinaturas. Cada caso de teste é descrito por sua assinatura — uma impressão digital do padrão SQL, schema, estatísticas e parâmetros de ambiente envolvidos.

Os casos de teste provêm de instruções SQL executadas em serviços internos do Alibaba Group. Todos os dados extraídos — incluindo informações estatísticas — são criptografados e mascarados por meio de um processo automatizado.

A biblioteca é avaliada em quatro dimensões:

• Cobertura de assinaturas: quais assinaturas da biblioteca já possuem casos de teste correspondentes

• Análise de lacunas: quais assinaturas ainda não foram cobertas ou falham na verificação de SQL

• Rastreamento de regressão: quais assinaturas apresentam falhas recorrentes ao longo do tempo

• Taxa de cobertura por nível de capacidade

Esse ciclo de feedback impulsiona melhorias contínuas na precisão das recomendações do mecanismo.

Modos de otimização

Antes de ser lançado no Alibaba Cloud, o mecanismo operou por anos em serviços internos do Alibaba Group, diagnosticando uma média de aproximadamente 50.000 instruções SQL por dia. O DAS disponibiliza essa capacidade em dois modos:

• Otimização personalizada: Envie instruções SQL específicas para diagnóstico. O mecanismo retorna recomendações de índices e sugestões de reescrita de SQL com a estimativa de melhoria de desempenho.

• Otimização automática: O DAS monitora as cargas de trabalho das instâncias, identifica consultas lentas, realiza o diagnóstico e gera sugestões de otimização. Após validar cada sugestão, o DAS agenda a otimização, aplica-a online e monitora o desempenho pós-alteração. Todo o processo ocorre sem intervenção manual.

Casos de rollback da otimização automática, diagnósticos com falha e problemas reportados por usuários são incorporados automaticamente à biblioteca de casos de teste para aprimorar recomendações futuras.

Mecanismos de banco de dados compatíveis

O mecanismo de diagnóstico SQL baseado em custo é compatível com os seguintes bancos de dados:

• ApsaraDB RDS for MySQL

• PolarDB for MySQL

• ApsaraDB RDS for PostgreSQL

• PolarDB for PostgreSQL (Compatible with Oracle)