Saltar para o conteúdo 
Com o «optimizer trace» no MariaDB, consigo compreender, passo a passo, por que razão o otimizador escolhe um determinado plano e quais as variantes que rejeita. Este registo JSON mostra-me Decisões sobre custos, ordens de junção e filtros, para que eu possa adaptar as consultas SQL de forma específica.
Pontos centrais
- Transparência: Um registo baseado em JSON explica as reescritas, os custos e os planos rejeitados.
- Foco: as funções `join_preparation` e `join_optimization` fornecem as informações mais importantes.
- Sistema de controlo: As variáveis de sessão limitam a sobrecarga e a memória.
- Fluxo de trabalho: EXPLAIN/ANALYZE para o plano, Trace para o „porquê“.
- Vantagens práticas: Ajustar de forma fundamentada os índices, as estatísticas e as ordens de junção.
O que é o MariaDB Optimizer Trace?
Desde a versão 10.4 que o MariaDB implementa um Optimizador Trace, que documenta em formato JSON cada fase de otimização mais significativa de uma instrução SELECT, UPDATE ou DELETE. Nele, consigo ver como o motor expande as consultas, normaliza as condições e, por fim, determina a ordem das junções, incluindo os acessos aos índices. Esta visão é significativamente mais aprofundada do que o EXPLAIN, que mostra principalmente o plano final, e revela alternativas rejeitadas com as respetivas justificações. O rastreio fica na memória por cada ligação e está disponível através de information_schema.OPTIMIZER_TRACE pronto. Assim, obtenho uma explicação completa e legível por máquina sobre o Passos, que conduziram a um plano de execução.
Ativar e ler o Optimizer Trace
Ativo a função especificamente por sessão, para poder realizar diagnósticos sem sobrecarga global e ter controlo total sobre Memória tenho. Normalmente, utilizo SET SESSION optimizer_trace = 'enabled=on'; e, se necessário SET SESSION optimizer_trace_max_mem_size = 1048576; ou superior, se o rastreio for extenso. Depois, executo a consulta suspeita e leio o rastreio com SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Importante: a tabela guarda apenas a última consulta da ligação ativa, e tenho em conta campos como MISSING_BYTES_BEYOND_MAX_MEM_SIZE ou INSUFFICIENT_PRIVILEGES para obter indicações de diagnóstico. Esta forma de trabalhar mantém o ambiente de produção simplificado e facilita a análise exato.
| Variável/Campo | Objetivo | Exemplo de valor |
|---|---|---|
optimizer_trace | Ativa o registo por sessão | 'enabled=on' |
optimizer_trace_max_mem_size | Capacidade máxima de armazenamento por trace | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Instrução SQL original | SELECT ... |
OPTIMIZER_TRACE.TRACE | Documento JSON da otimização | Texto JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Bytes cortados quando o trace é demasiado grande | 0 ou quantidade |
INSUFFICIENT_PRIVILEGES | A autorização de leitura é suficiente? | 0 ou 1 |
Estrutura JSON: join_preparation e join_optimization
A estrutura JSON divide-se nos seguintes blocos preparação_de_junção e otimização de junções, que são as primeiras que consulto, porque são as mais importantes Notas fornecer. Na secção preparação_de_junção reconheço a consulta alargada (expanded_query) e vejo se e como o motor transformou as condições ou as projeções. O segundo bloco otimização de junções regista estimativas de linhas, planos analisados, a ordem de junção selecionada e a adição de partes seletivas da condição WHERE às tabelas. Os subárvores são particularmente úteis rows_estimation, planos_de_execução_considerados e atribuir_condições_a_tabelas, porque remetem diretamente para os pressupostos de custos e para as posições dos filtros. Desta forma, consigo identificar rapidamente onde existem avaliações erradas ou situações desfavoráveis Índices levam a planos que não são os melhores possíveis.
Comparação com EXPLAIN e ANALYZE
Para uma avaliação completa, combino o EXPLAIN, o ANALYZE e o Traço num procedimento fixo. Primeiro, utilizo EXPLICAR ou EXPLAIN FORMAT=JSON, para ver o plano selecionado e os percursos-chave. Depois, defino EXPLAIN ANALYZE para obter dados reais sobre o tempo de execução e valores de contagem, como loops e linhas filtradas. Se ainda houver dúvidas, ativo o Optimizer Trace e verifico quais variantes o Optimizer analisou e rejeitou. Este artigo oferece-me uma introdução sucinta à interpretação de Compreender o EXPLAIN ANALYZE, ao qual recorro como complemento, se necessário.
Compreender as decisões de planeamento: custos, cardinalidades, filtros
A lógica de decisão baseia-se em cardinalidades, modelos de custos e na localização de Filtro ao longo do plano. No Trace, vejo, para cada ordem de junção analisada, quais os conjuntos de linhas que o motor espera e como calcula a partir daí o custo total. Verifico se estatísticas desatualizadas ou correlações desfavoráveis levam a que as varreduras de intervalo sejam subestimadas e as varreduras completas sejam preferidas. Além disso, verifico se o motor aplica as condições WHERE à tabela mais seletiva com antecedência suficiente, de modo a reduzir etapas de junção dispendiosas. Desta forma, consigo chegar a conclusões sólidas sobre o motivo pelo qual um plano foi escolhido e como posso melhorá-lo com Índices, reescritas ou atualização de estatísticas.
Exemplo prático: Rastreio de uma consulta de filtro simples
Em SELECT * FROM t1 WHERE a < 10 verifico em preparação_de_junção, se o motor ampliou a projeção e, eventualmente, consolidou condições, o que me deu uma primeira Indicadores apresenta. Depois disso, vejo no bloco rows_estimation, quantas linhas o motor utiliza para o Range-Scan em a em comparação com o Full-Table-Scan. Se forem encontrados valores irrealistas, interpreto isso frequentemente como um sinal de estatísticas desatualizadas ou da falta de histogramas. Na secção planos_de_execução_considerados Percebo, em seguida, se o acesso ao índice foi realmente mais económico do que a análise completa. Por fim, mostra atribuir_condições_a_tabelas, se a condição seletiva se aplica a a começa a surtir efeito mais cedo do que previsto, o que reduz significativamente o tempo de execução baixa.
Funções JSON: extrair trechos específicos
Como o trace está no formato JSON, filtro especificamente subárvores com JSON_EXTRACT e crio pequenas análises para dados recorrentes Amostra. Por exemplo, analiso apenas a lista dos planos considerados para verificar se determinadas sequências de junções falham sistematicamente. Da mesma forma, extraio os campos de custos dos principais candidatos e comparo-os com os dados do ANALYZE, para detetar suposições erradas. Através de vistas simples ou procedimentos armazenados, automatizo estas verificações para as minhas sessões de diagnóstico. Desta forma, construo uma ferramenta leve Monitorização para as decisões do otimizador, sem ativar o rastreio permanente.
Casos de utilização típicos e vantagens
Recorro ao Trace quando o EXPLAIN indica uma varredura completa inesperada e quero descobrir a razão pela qual um Índice quero saber. Da mesma forma, o trace fornece-me, no caso de muitas tabelas, a justificação para a ordem de junção escolhida, o que me indica o caminho para planos alternativos. Ao mudar de versão, guardo os registos antes e depois da atualização, para avaliar as alterações no comportamento do otimizador. No que diz respeito a questões estratégicas de ajuste, esta visão geral ajuda-me a mecanismos internos do otimizador, que associo aos resultados do Trace. É assim que decido, de forma estruturada, se devo ajustar os índices, as estatísticas ou a formulação das consultas parafuso de ajuste coloque.
Boas práticas para a produção
Ativo o Trace de forma sistemática como Sessão-Encerro o diagnóstico de forma adequada assim que tiver dados suficientes. Para registos de grande dimensão, aumentei optimizer_trace_max_mem_size apenas a curto prazo e, depois, volto a definir o valor para um nível mais baixo. Antes de partilhar ficheiros JSON, oculto constantes sensíveis, textos de comentários ou indicadores empresariais. Utilizo o Trace especificamente como ferramenta de diagnóstico, enquanto, para monitorização contínua, prefiro registos de consultas lentas, vistas de desempenho ou profiler externos. Esta disciplina mantém os sistemas otimizados e evita Despesas gerais na atividade quotidiana.
Rastreio do Optimizer no conjunto de ferramentas
Para um ajuste integral, represento a cadeia composta pela compreensão do plano, pela análise das causas e pela medição do sistema, e interligo as Conclusões. O EXPLAIN mostra-me o plano, o ANALYZE confirma os custos reais e o trace fornece os pormenores por trás da decisão. Paralelamente, analiso conceitos relacionados com o plano de execução de consultas, para identificar padrões na seleção de chaves, cardinalidades e estratégias de junção. Um bom complemento a esta perspetiva é a visão geral concisa sobre Planos de execução de consultas, ao qual recorro quando tenho dúvidas sobre arquitetura. A partir daí, deduzo conclusões sólidas Prioridades para o trabalho de indexação, reescritas e parâmetros.
Aprofundar o tema: análise de intervalo e escolha de chaves
Muitas vezes, o «trace» contém um bloco análise_de_intervalo para cada tabela, onde consigo identificar quais os índices que podiam ser utilizados para acessos do tipo Range, Ref ou EQ-Ref. O otimizador compara aí alternativas como „range no idx_a“, „range no idx_b“ ou „full scan“, atribui-lhes custos e o número de linhas esperado e destaca a opção vencedora. Se verificar que um índice adequado foi rejeitado devido a custos elevados, analiso, em seguida, as seletividades e estatísticas subjacentes. Se as suposições não estiverem corretas, pode ser necessário um ANALISAR TABELA (se for o caso, com estatísticas persistentes) ou a criação de um Índice de cobertura anular a decisão.
Também é útil analisar as divisões dos índices compostos: o trace documenta se a condição utiliza apenas a primeira coluna do índice ou se existem predicados adicionais que podem ser aplicados, tornando outras colunas-chave efetivas. A partir daí, deduzo se devo reformular os predicados (por exemplo, evitando funções) ou alargar o índice de forma a abranger os filtros e ordenações típicos.
Juntas em pormenor: semijuntas, BKA/MRR e buffer de juntas
Nas consultas com várias tabelas, as secções de rastreio mostram se foi considerada alguma estratégia de semijoin e, em caso afirmativo, qual (por exemplo, FirstMatch, DuplicateWeedout, LooseScan ou Materialization). Consigo perceber ali por que razão uma variante foi rejeitada – por exemplo, devido a custos de materialização elevados ou a uma seletividade demasiado baixa. Além disso, Acesso por chave em lote (BKA) e Leitura multi-intervalo (MRR) aparecem no trace, caso estejam ativadas. Estas técnicas agrupam as pesquisas de chaves e melhoram a localidade da cache. Se o BKA/MRR não aparecerem no trace, verifico optimizer_switch e parâmetros como join_cache_level. Em cargas de trabalho com muitas pesquisas aleatórias de chaves, é possível acelerar significativamente a fase de junção, o que pode ser verificado com o comando EXPLAIN ANALYZE.
Outro fator decisivo é o tamanho e o tipo do buffer de junção: o trace revela se foram executadas variantes de Nested Loop com ou sem buffer e em que ponto os filtros entram em ação. Avalio se a criação de índices adicionais nas chaves de junção ou uma reescrita para reduzir os resultados intermédios constitui a opção mais eficiente do que aumentar as dimensões dos buffers.
Subconsultas, tabelas derivadas e vistas
Em preparação_de_junção Na minha opinião, se as subconsultas na forma EXISTS/IN em Semijoins foram transformados (in_to_exists), se as tabelas derivadas foram fundidas (derived_merge) ou se concretizaram e se Condição Flexão até às tabelas derivadas. Estes passos são decisivos, porque a ausência de uma fusão pode levar a uma materialização dispendiosa. Se, no trace, observar repetidamente decisões de materialização com custos elevados, verifico se existe uma STRAIGHT_JOIN, uma sugestão ou uma reorganização da consulta (por exemplo, Common Table Expressions com filtros específicos) leva o motor a adotar uma estratégia mais eficiente. No caso das vistas, verifico se o otimizador resolve adequadamente o conteúdo da vista ou se faltam índices adicionais na tabela subjacente.
Particionamento e poda
No caso de tabelas particionadas, o rastreio mostra quais as partições que foram excluídas com base nas chaves de partição e nos predicados (Poda de partições). Se a poda esperada não ocorrer, isso é um sinal de que os filtros devem ser formulados mais cedo e de forma mais seletiva com base na chave de partição. Presto também atenção à interação entre o particionamento e os índices: se faltarem índices locais ou globais, o motor pode verificar um número excessivo de linhas, apesar da poda, o que se torna visível no rastreio através de custos de varredura elevados.
Verificar de forma específica as sugestões, as especificações de índice e o optimizer_switch
Utilizo o Trace para verificar o efeito das sugestões e dos comutadores de parâmetros ocupar. Se eu colocar, por exemplo,. ÍNDICE DE FORÇA ou uma sugestão do otimizador, consigo ver no registo se a alternativa foi realmente imposta e como foi avaliada. Através de optimizer_switch posso ativar ou desativar temporariamente estratégias (por exemplo, para decisões de semijoin, index_merge ou derived_merge). O registo serve-me então como prova para verificar se o motor aceitou as especificações ou se outras restrições (por exemplo, cardinalidades) continuam a prevalecer. Opcionalmente, utilizo sinalizadores de formatação como one_line ou end_markers em optimizer_trace-String, para adaptar a legibilidade à minha ferramenta de análise.
Update/DELETE e percursos de gravação
O Optimizer Trace não se limita às instruções SELECT. Também nas instruções UPDATE e DELETE consigo ver como são escolhidos os métodos de acesso e se os filtros são aplicados com antecedência suficiente para manter baixo o número de linhas afetadas. Verifico se um filtro WHERE não é sargável ou se a ausência de um índice leva a uma fase de varredura abrangente antes de a alteração propriamente dita ser executada. A partir do Trace, deduzo se um índice compacto (por exemplo, apenas as colunas necessárias) evita acessos desnecessários de ida e volta e, assim, reduz os bloqueios e o volume de registo.
Segurança, privilégios e instruções preparadas
Para poder ler a faixa na íntegra, preciso de privilégios de objeto suficientes – se estes não existirem, o campo indica que INSUFFICIENT_PRIVILEGES Restrições. Por isso, em cenários próximos da produção, utilizo os mesmos dados de acesso que a aplicação ou uma conta de diagnóstico com autorizações específicas. No caso das instruções preparadas, o rastreio apresenta normalmente a forma otimizada com os parâmetros já vinculados, o que me permite avaliar as seletividades sem revelar constantes sensíveis. Se tiver de partilhar rastreios, oculto os valores dos parâmetros ou substituo-os por intervalos representativos, a fim de cumprir os requisitos de proteção de dados.
Automatização: registar, diferenciar e documentar os traços
Para garantir a reprodutibilidade das análises, guardo traces de forma aleatória numa tabela de diagnóstico e atribuo-lhes metadados, tais como esquema, versão, variáveis de sessão e carimbo de data/hora. Desta forma, posso, antes e depois de alterações no índice ou atualizações de versão, diffen, quais as decisões que foram adiadas. É prático organizar os blocos planos_de_execução_considerados e rows_estimation armazenar separadamente, para comparar rapidamente as alterações nos custos. Pequenas consultas auxiliares extraem-me a ordem de junção selecionada e os custos calculados – por exemplo, com JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – e guardam o resultado juntamente com os resultados dos comandos EXPLAIN e ANALYZE. Desta forma, obtém-se uma documentação fiável para cada etapa do ajuste.
Limitações, particularidades de cada versão e comparação com o MySQL
As estruturas-chave do trace seguem o modelo do MySQL; no entanto, os detalhes e os nomes dos campos podem variar ligeiramente, dependendo da versão do MariaDB. Por isso, vou centrar a minha atenção na semânticos Secções (Rewrites, Rows-Estimation, planos considerados, Condition-Attachments), em vez de me deixar irritar por diferenças meramente cosméticas. Importante: No MariaDB, o foco recai sobre a última instrução da ligação ativa. Quem analisa muitas instruções consecutivas deve, por isso, ler os dados imediatamente após a execução ou de forma automatizada através de um hook, para que nenhum rasto relevante seja sobrescrito. No caso de ficheiros JSON muito grandes, tenho em conta as necessidades de memória e compreendo MISSING_BYTES_BEYOND_MAX_MEM_SIZE como um convite para aumentar temporariamente o limite e voltar a executar a análise.
Extrações concretas de JSON para o dia-a-dia
Para terminar, eis alguns excertos concisos que utilizo frequentemente na prática para ir direto ao essencial:
- Ordem de junção selecionada e listas de candidatos: extraio os prefixos do plano e a tabela associada a cada um, para poder compreender a sequência de decisões.
- Alternativas de intervalos e custos: extraio a lista dos índices avaliados para as tabelas mais seletivas, a fim de avaliar com precisão as reescritas ou novos índices.
- Filtros aplicados desde cedo: Estou a ler o
atribuir_condições_a_tabelas-secções, para garantir que os predicados fortes fiquem o mais próximo possível da fonte de dados.
Com poucas visualizações destas extrações, tenho uma „lente de leitura“ simplificada para as decisões do otimizador, que ativo quando necessário nas sessões de diagnóstico e desativo novamente a seguir.
Obstáculos frequentes e resolução de problemas
Se faltarem histogramas ou se as estatísticas estiverem desatualizadas, as estimativas ficam erradas e geram Planos com análises completas desnecessárias. Se observar cardinalidades muito divergentes no trace, atualizo as estatísticas, defino índices adequados ou reformulo os filtros de forma a torná-los mais eficientes. Identifico traces demasiado sucintos através de MISSING_BYTES_BEYOND_MAX_MEM_SIZE e respondo com um limite temporariamente mais elevado. Se o ANALYZE apresentar melhores tempos de execução para um caminho alternativo, verifico no trace qual o fator de custo que favoreceu a variante escolhida. Assim, vou colmatando, passo a passo, as lacunas de conhecimento e consigo Clareza sobre a lógica de decisão.
Brevemente resumido
O MariaDB Optimizer Trace explica-me, num documento JSON, como o motor reformula as consultas, estima o número de linhas, compara planos e, por fim, uma Sequência seleciona. Ativo-o por sessão, leio a faixa, verifico preparação_de_junção e otimização de junções e associo essas conclusões ao EXPLAIN/ANALYZE. A partir das razões para a rejeição de índices, filtros tardios ou estimativas erradas, deduzo medidas concretas: melhores índices, estatísticas mais atualizadas e formulações claras das consultas. Com funções JSON, extraio trechos, identifico padrões e documento as decisões de forma reproduzível. Desta forma, consigo também processar cargas de trabalho SQL extensas de forma fiável Desempenho e asseguro que as decisões relativas ao tuning sejam compreensíveis.
