O Otimizador de Consultas do MariaDB explicado em pormenor: noções básicas, planos e aplicação prática

Vou explicar o Otimizador do MariaDB Na prática: como ele elabora planos, estima custos e por que razão, por vezes, se engana. Assim, aprenderás a interpretar o plano de execução SQL de forma objetiva, a utilizar índices de forma sensata e a orientar o otimizador com base em factos, em vez de no instinto.

Pontos centrais

Para começar, vou resumir brevemente os elementos mais importantes, para que possas contextualizar as secções seguintes de forma específica e o Visão geral manténs.

• Fases: A análise, a preparação, a otimização e a execução constituem o ciclo de vida de cada consulta.
• Modelo de custos: Os valores baseados no tempo, na ordem dos microssegundos, controlam a seleção de índices, as varreduras e as sequências de junções.
• Estatísticas: A cardinalidade e os histogramas determinam a estimativa da seletividade.
• Transparência: EXPLAIN, EXPLAIN ANALYZE e Optimizer Trace abrem a «caixa preta».
• Afinação: Os índices, a reescrita de consultas, o comando ANALYZE TABLE e os parâmetros de custo aumentam a velocidade.

Ciclo de vida de uma consulta no MariaDB

Antes de se concretizar um plano, uma consulta passa por quatro etapas, que eu verifico de forma específica no dia a dia, para Causas para identificar lentidão. Durante a análise, o MariaDB converte o SQL numa estrutura interna; é aqui que os erros de sintaxe são detetados. Na fase de preparação, o motor verifica tabelas, colunas e índices potenciais e realiza transformações simples. Segue-se então a otimização, na qual são calculados planos candidatos e avaliados com base num modelo de custos. Na execução, o servidor implementa o plano selecionado passo a passo: ler, unir, filtrar, devolver.

Distingo claramente os erros de análise por fase, porque assim os diagnósticos surtem efeito mais rapidamente e Medidas agir de forma direcionada. Na maioria das vezes, os problemas de desempenho têm origem na otimização: estimativas erradas, índices em falta ou sequências de junções desfavoráveis. Os erros de análise são triviais, mas a fase de preparação já pode envolver técnicas como a resolução de vistas ou a reformulação de subconsultas. Na fase de execução, as ineficiências tornam-se então evidentes, caso tenha sido previamente selecionada uma varredura completa. Por isso, começo sempre a minha análise com uma revisão estruturada de todas as quatro etapas.

Como o Optimizer toma as suas decisões internamente

O MariaDB funciona com base nos custos e avalia execuções alternativas através de uma Função de custo. Para cada variante, o servidor estima o número de linhas lidas, a seletividade das condições WHERE/ON, os tipos de acesso, como Table Scan, Index Scan e Range Scan, bem como o tempo necessário para cada operação. Internamente, o servidor distingue entre `join_preparation` e `join_optimization`. Em `join_preparation`, são executadas reescritas de consultas, simplificações de condições, transformações de subconsultas e resoluções de vistas. O `join_optimization` calcula as ordens de junção, verifica candidatos a índices através do `ref_optimizer_key_uses`, estima o número de linhas através de varreduras de intervalo e atribui as condições a tabelas específicas o mais cedo possível.

Este mecanismo explica por que razão um pequeno filtro colocado no local errado pode causar danos dispendiosos Consequências tem. Se o `attaching_conditions_to_tables` ocorrer tarde, o plano arrasta desnecessariamente muitas linhas pelas junções. Se as estatísticas estiverem desatualizadas, o `rows_estimation` e a `Selectivity` apresentam resultados errados; o otimizador recorre então a caminhos de acesso aparentemente favoráveis, mas que, na realidade, são lentos. É precisamente nestes pontos que intervenho: estatísticas melhores, predicados mais claros, índices compostos bem organizados. Depois disso, a escolha do plano muda frequentemente de forma notável.

Modelo de custos a partir do MariaDB 11.0

As versões atuais já não avaliam o trabalho de forma aproximada com base em pesos, mas sim com Microssegundos para operações de armazenamento específicas. Parâmetros como optimizer_disk_read_cost, optimizer_disk_read_ratio e optimizer_where_cost aproximam o modelo dos tempos de execução reais. Assim, o otimizador compara a varredura de intervalo de índice com a varredura completa com base em pressupostos de tempo reais. O LAST_QUERY_COST apresenta o custo total estimado e, frequentemente, correlaciona-se significativamente melhor com a realidade do que anteriormente. Para sistemas com grande volume de dados, esta abordagem mais refinada compensa imediatamente.

Calibro o modelo com cuidado quando as características do hardware contrariam os pressupostos padrão e, consequentemente, a Escolha do plano distorcer. Os SSDs NVMe, as memórias distribuídas ou as caches específicas podem alterar significativamente a relação de disco e os tempos de leitura. Pequenos ajustes nos «optimizer_costs» fazem com que o MariaDB dê preferência a percursos mais adequados. Documento cada alteração e, em seguida, verifico o EXPLAIN ANALYZE para medir o impacto. Sem medição, o ajuste torna-se uma lotaria.

Seletividade, estatísticas e histogramas

Boas estimativas começam com dados precisos cardinalidade e de uma seletividade fiável. O MariaDB mantém estatísticas sobre os diferentes valores de cada coluna e pode, opcionalmente, utilizar histogramas para as distribuições. São precisamente os dados irregulares — pontos de concentração, distribuições de Zipf, padrões sazonais — que beneficiam dos histogramas. Após grandes alterações nos dados, executo o comando ANALYZE TABLE para que a otimização volte a basear-se em dados reais. Quem se esquecer disso corre o risco de realizar varreduras completas que são objetivamente incorretas.

Vou programar o ANALYZE como uma tarefa regular, de acordo com Alterações no volume de dados e em tabelas críticas. Em distribuições de colunas fortemente distorcidas, os histogramas ajudam a avaliar de forma realista a seletividade de valores singulares. Isto reduz as avaliações erradas em varreduras de intervalo e estratégias de fusão. Em combinação com índices compostos adequados, a precisão dos resultados melhora drasticamente. Resultado: tempos de execução mais curtos e menos E/S.

EXPLAIN e como ler os planos de execução

Para tornar as decisões visíveis, utilizo o EXPLAIN, o EXPLAIN EXTENDED e FORMATO=JSON. As colunas clássicas oferecem uma visão geral rápida: id, select_type, table, type, possible_keys, key, key_len, ref, rows e, se for o caso, filtered. Um «type=ALL» indica uma varredura completa, o que raramente é desejável. «FORMAT=JSON» mostra em detalhe como as condições foram reorganizadas e quais os caminhos que o otimizador avaliou. No contexto da hospedagem, recomendo o guia sobre Planos de execução no alojamento, para associar informações do plano aos efeitos na infraestrutura.

Para uma interpretação rápida, recorro a uma pequena tabela que resume os valores típicos e, assim, Interpretações incorrectas impedido.

Campo EXPLAIN	Valor típico	Significado na prática
tipo	ALL, range, ref, eq_ref, const	Quanto mais à direita, mais seletivo; «ALL» indica uma verificação completa.
possible_keys	Lista de índices	Índices que, em teoria, se encaixam; se faltam candidatos aqui, falta estrutura.
tecla	Nome do índice	Índice efetivamente utilizado; se estiver em branco, significa que não se utiliza o índice.
linhas	Número	Estimativa de linhas lidas; grande discrepância em relação à realidade = estatística incorreta.
filtrado	Por cento	Qual a quantidade que passa pelo filtro; muitas vezes, uma quantidade reduzida é o ideal.

Por que é que o Optimizer, por vezes, se engana

Nenhum modelo de custos se adapta a todas as situações, por isso vou corrigir Erros de forma direcionada. Estatísticas desatualizadas levam a estimativas erradas de «rows» e a sequências de junções desfavoráveis. Índices compostos mal estruturados impedem a utilização do índice em filtros com várias colunas. Subconsultas muito aninhadas dificultam reescritas eficazes e bloqueiam a materialização. Filtros ausentes ou enganosos obrigam o motor a mover muitas linhas antes que os predicados úteis sejam aplicados.

Em primeiro lugar, verifico se a formulação da consulta está de acordo com o Índice O que realmente funciona: regra do prefixo à esquerda, ordem de ordenação adequada, evitar funções nas colunas na cláusula WHERE. Depois, verifico no EXPLAIN ANALYZE se a realidade confirma a estimativa. Se não for o caso, segue-se o ANALYZE TABLE e, se necessário, uma reescrita. Só no final é que recorro ao FORCE INDEX ou ao hinting, porque isso pode limitar otimizações futuras.

Utilizar o Optimizer Trace de forma específica

Se o EXPLAIN não for suficiente, ativo o Optimizer Trace e acompanho Decisões no registo JSON. Aí consigo ver quais os planos que foram considerados, rejeitados ou aceites. Percebo por que razão uma condição é aplicada tardiamente ou por que razão um índice não foi selecionado. O registo também mostra como as condições foram reorganizadas. Esta visão aprofunda a compreensão e fornece alavancas concretas para o próximo ajuste.

Guardo as secções relevantes do trace juntamente com o hash da consulta e Parâmetrosavaliar. Assim, mais tarde, posso comparar qual a alteração que teve que efeito. A documentação do servidor MariaDB e várias apresentações no ecossistema descrevem estes campos em pormenor (fonte: Documentação do servidor MariaDB sobre o Query Optimizer e o Optimizer Trace). Com esta ferramenta, consigo identificar suposições erradas mais rapidamente do que através do método de tentativa e erro. Poupo tempo sobretudo em junções complexas.

Na prática: Otimização de bases de dados, passo a passo

Começo cada otimização com uma Medição. Identifico as consultas relacionadas com problemas através da monitorização e do Registo de consultas lentas. Em seguida, comparo o EXPLAIN com o EXPLAIN ANALYZE, para comparar o plano com a realidade. Adapto a estratégia de indexação às cláusulas WHERE, JOIN e ORDER BY; alinho os índices compostos com os pontos de acesso mais frequentes. Só utilizo o FORCE INDEX quando o otimizador, apesar de dispor de estatísticas corretas, escolhe o candidato errado.

Cada etapa implica a manutenção da Estatísticas: ANALYZE TABLE em tabelas com grande volume de transações, histogramas para distribuições assimétricas. Simplifico subconsultas desnecessárias, materializo resultados intermédios quando necessário e elimino soluções alternativas antigas. No caso de hardware especial, verifico os optimizer_costs para garantir que o modelo de microssegundos está correto. Documento cada alteração com valores «antes» e «depois», para que o efeito permaneça verificável a longo prazo.

Problemas típicos dos otimizadores e respetivas soluções

Se o EXPLAIN mostrar type=ALL, apesar de o campo possible_keys estar preenchido, verifico primeiro Seletividade. Muitas vezes, a ordem das colunas no índice composto não é adequada ou uma função impede a utilização do índice. Nesses casos, inverto a ordem, elimino funções que causam problemas ou divido os predicados. Se a ordem das junções estiver errada, verifico se é possível aplicar uma filtragem antecipada, por exemplo, colocando a tabela mais seletiva em primeiro lugar. Sempre que faz sentido, converto subconsultas em junções ou tabelas TEMPORARY.

Também reconheço decisões erradas quando estas se afastam significativamente linhas entre o planeado e a realidade. Nesse caso, o comando ANALYZE TABLE ou um histograma na coluna em questão podem ajudar. Se mesmo as estatísticas corretas não conduzirem ao resultado pretendido, considero a utilização de hints explícitos. Antes disso, guardo uma verificação cruzada e os valores medidos, para que versões posteriores do otimizador não sejam prejudicadas por dados armazenados. A disciplina na documentação compensa neste caso.

Contexto de alojamento e aspetos operacionais

A qualidade das consultas e a infraestrutura têm de estar em sintonia; caso contrário, a aplicação desperdiça o seu potencial Potencial. SSDs rápidos, caches consistentes e uma configuração bem organizada são a base sobre a qual o Optimizer toma boas decisões. Um tráfego elevado não permite análises completas; algumas consultas mal elaboradas podem paralisar sistemas inteiros. Para ambientes MySQL/MariaDB em produção, fornecem orientações práticas como Otimizador do MySQL reflexões úteis sobre a combinação entre plano e plataforma. Quem tem em conta este aspeto evita os estrangulamentos antes que estes se agravem.

Associo sempre a análise do plano a métricas relativas a E/S, latência e concorrência. Se os valores não corresponderem ao modelo de custos previsto, verifico os parâmetros. Em seguida, analiso os tamanhos dos buffers, as cargas de trabalho paralelas e a distribuição dos hotsets. Com esta abordagem, consigo gerir as consultas e os recursos de forma harmoniosa e manter os picos de tráfego sob controlo.

Caminhos de junção e de acesso na prática

Esclareço muitos mal-entendidos explicando o Tipos de acesso ponderar de forma específica uns contra os outros. Um intervalo- ou ref-O acesso funciona quase sempre TUDO. No caso de ligações de igualdade com base em chaves únicas (eq_ref) os planos são particularmente estáveis. Além disso, verifico se um Índice de cobertura que atenda integralmente à consulta: se todas as colunas necessárias estiverem no índice, o MariaDB evita acessos dispendiosos às tabelas. Pressão de condição de índice (ICP) ajuda a verificar condições WHERE adicionais já no índice – o que reduz o número de linhas devolvidas e as operações de E/S.

Sobre Fusão de índices O MariaDB pode combinar vários índices (intersecção/união). Isto é útil no caso de predicados OR ou de várias condições de seleção, mas é frequentemente mais lento do que um índice composto bem escolhido. Além disso, estou a avaliar MRR (Leitura multialcance) e BKA (Batched Key Access). O MRR ordena as chaves primárias a ler para suavizar as E/S aleatórias; o BKA agrupa as pesquisas de junção e obtém melhores resultados, sobretudo em junções não sobrepostas. Na prática, testo o BKA/MRR através do `optimizer_switch` e verifico com o `EXPLAIN ANALYZE` se os padrões de E/S diminuem. Se, pelo contrário, o MariaDB recorrer ao Bloco de laços aninhados (BNL), na maioria das vezes compensa utilizar um buffer de junção maior (join_buffer_size) – ou uma reescrita que permita junções por índice verdadeiras.

-- Exemplo: Índice composto para junção + filtro + ordenação
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Acesso típico
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Com o índice acima referido, o otimizador pode escolher a ordem mais seletiva, avaliar os filtros numa fase inicial e, muitas vezes, efetuar a ordenação sem necessidade de uma ordenação em ficheiro adicional.

ORDER BY, GROUP BY, ordenação por ficheiros e tabelas temporárias

A ordenação e a agregação demoram tempo. Eu garanto que ORDER BY e GRUPO POR podem ser executadas na ordem do índice. Isto funciona se o prefixo e a direção corresponderem exatamente. Caso contrário, aplica-se uma Classificação de ficheiros com buffer de ordenação (sort_buffer_size) e, se necessário, uma tabela temporária. Se o conjunto de resultados incluir colunas TEXT/BLOB largas, o MariaDB torna-se mais rápido no disco Tabelas TEMP (Aria). Prevenho este problema selecionando apenas as colunas necessárias, carregando campos grandes apenas no final ou utilizando prefixos com comprimento limitado.

Nas agregações, sempre que possível, utilizo, Análise de Índice Livre (por exemplo, GROUP BY na parte principal do índice) e seleciono índices compostos ao longo do agrupamento. Quando os resultados intermédios se tornam volumosos, uma materialização com chaves adequadas escala melhor do que uma única mega-junção. Mido regularmente as métricas dos handlers e os contadores Created_tmp_* para detetar pontos críticos de ordenação e de tabelas temporárias.

Subconsultas, semi-join e materialização

Muitas subconsultas podem ser reformuladas de forma eficiente na fase de preparação. As construções IN/EXISTS podem ser substituídas por Semi-Join funcionam com estratégias como a materialização ou o LooseScan. Verifico se o otimizador é um derived_merge conseguiu executar: se uma tabela derivada (ou um WITH-CTE) for inserida no plano externo, os seus índices ficam diretamente disponíveis. Se isso não for possível, a subconsulta acaba numa tabela temporária — nesse caso, se for viável, atribuo-lhe uma chave (por exemplo, através de SELECT DISTINCT/ORDER BY nas colunas-chave), para que as junções não acabem no limbo.

-- Exemplo: EXISTS em vez de IN e tabela derivada compatível com Merge
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Derivação com chaves únicas
WITH paid_orders AS (
  SELECT DISTINCT order_id
  FROM payments
  WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;

Utilizo o comando EXPLAIN FORMAT=JSON para verificar se materializado ou subconsulta dependente foi selecionado e se existem condições (condição de pushdown) agir com a devida antecedência.

Particionamento e poda

A particionamento não substitui os índices, mas pode Volume de dados por acesso reduzir drasticamente. O Optimizer só realiza uma poda correta se o predicado Chave de partição seja inequivocamente identificada e não seja obscurecida por funções. Por isso, evito expressões como DATE(created_at) na condição WHERE em tabelas particionadas e, em vez disso, trabalho com limites de intervalo. O EXPLAIN mostra quais as partições que são lidas; intervalos muito amplos indicam uma má seleção de partições.

Um número excessivo de partições pequenas aumenta a sobrecarga de planeamento. Por isso, opto por uma granularidade adequada (por exemplo, mensal em vez de diária), mantenho as estatísticas atualizadas por partição (ANALYZE PARTITION) e verifico se os índices importantes estão disponíveis localmente nas partições. Em projetos de migração, tenho em conta o impacto na replicação e na cópia de segurança – ambos influenciam o grau de agressividade com que realizo a partição.

Sargability e padrões de reescrita

A solução mais simples continua a ser Caixão – Condições que permitem a utilização de índices. Evito funções nas colunas na cláusula WHERE, reduzo as constantes ao nível das colunas e, se necessário, separo as condições OR em UNIÃO TODOS. Para pesquisas LIKE sem âncora inicial ("%foo"), um índice BTREE não serve de nada; neste caso, pretendo utilizar a pesquisa de texto completo ou um serviço de pesquisa adequado. Para cálculos, utilizo colunas geradas com índice, para que o otimizador consiga identificar a lógica no índice.

-- Anti-padrão: função na coluna
WHERE DATE(created_at) = '2026-08-01'
-- Melhor: intervalo com valor bruto
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-padrão: o OR impede a utilização do índice
WHERE status = 'open' OR customer_id = 42
-- Melhor: duas pesquisas com UNION ALL e um índice próprio para cada uma
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

No que diz respeito aos índices compostos, considero que a regra do prefixo à esquerda Aplico rigorosamente esta regra, ordenando as colunas por seletividade e de acordo com a ordenação que será necessária posteriormente. Se precisar de um ORDER BY descendente, tenho isso em conta no layout do índice – assim evito a ordenação por ficheiros.

Opções do otimizador e ajuste fino dos custos

Antes de analisar as consultas, verifico optimizer_switch e memória tampão. Funcionalidades como mrr, batched_key_access, index_merge, semijoin, derived_merge ou condição_pushdown_para_derivados podem ser ajustados por sessão. Ativo candidatos de forma seletiva para uma sessão de teste, avalio com EXPLAIN ANALYZE e reverto as alterações se o efeito não for o esperado. O percurso da junção beneficia de uma quantidade suficiente de tamanho_de_junção; grandes variedades de tamanho do buffer de ordenação. Ao mesmo tempo, mantenho-me atento às memórias tampão em relação à concorrência, para que o servidor não entre em modo de troca de memória sob carga paralela.

No que diz respeito aos custos, ajusto, se necessário, os já mencionados custos_do_otimizador em microssegundos. O meu guia: pequenos passos reversíveis com pontos de medição documentados. Utilizo LAST_QUERY_COST para verificar a plausibilidade e repito as medições com valores de parâmetros realistas, uma vez que os planos podem depender fortemente de valores concretos.

Estabilidade do plano, regressões e fluxo de trabalho da equipa

Mesmo um bom plano pode ser afetado pelo aumento do volume de dados ou por mudanças de versão tombar. Por isso, garanto-me de ter conhecimento sobre os planos de execução: hashes de consultas, JSON do EXPLAIN, excertos do rastreio do otimizador e tempos de execução do EXPLAIN ANALYZE. As alterações aos índices e as reescritas são submetidas por mim como pull requests, acompanhadas de comprovativos do antes e do depois. Em ambientes de CI/CD, verifico automaticamente as consultas críticas em relação a conjuntos de dados representativos. É assim que começo Planos de regressão cedo.

Para casos delicados, considero que Dicas (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch por consulta) estão disponíveis como última opção, mas utilize-as com moderação e com prazo de validade. É melhor resolver as causas – estatísticas, índices, formulação. Nas equipas, um guia simples sobre escalabilidade, conceção de índices e disciplina de medição garante que novas funcionalidades não introduzam, sem que se dê por isso, problemas de desempenho.

Resumo: Do plano à execução

Quem pode utilizar o Plano Compreender é controlar o desempenho. As fases de análise (Parsing), preparação (Preparing), otimização (Optimizing) e execução (Executing) explicam onde se perde tempo. O modelo de custos baseado no tempo a partir da versão 11.0, as estatísticas atualizadas e os histogramas tornam as estimativas fiáveis. Os comandos EXPLAIN, EXPLAIN ANALYZE e o Optimizer Trace proporcionam transparência, que eu traduzo em medidas concretas. Com uma estratégia de indexação bem definida, um design claro das consultas e uma infraestrutura adequada, as consultas do MariaDB fornecem respostas sempre rápidas.