Quando uma consulta apresenta lentidão ou consome memória excessiva, o plano de execução indica exatamente onde o tempo e os recursos são gastos. O comando EXPLAIN exibe o caminho planejado pelo otimizador para uma consulta antes da execução. Já o EXPLAIN ANALYZE executa a consulta e captura os custos reais da execução distribuída — durações por operador, pico de uso de memória e contagem de linhas de entrada/saída — para que você possa comparar as estimativas com a realidade e identificar gargalos.
Pré-requisitos
Antes de começar, verifique se:
Seu cluster AnalyticDB for MySQL está na versão V3.1.3 ou posterior. Para verificar a versão, consulte How can I visualize the version of an AnalyticDB for MySQL cluster?. Para atualizar, envie um ticket.
EXPLAIN
O comando EXPLAIN avalia o caminho de execução planejado para uma consulta SQL. A saída é uma estimativa do otimizador e pode não corresponder aos resultados reais da execução.
Sintaxe
EXPLAIN (format text) <SELECT statement>;Adicione (format text) em consultas não complexas para melhorar a legibilidade da hierarquia em árvore na saída.
Exemplo
EXPLAIN (format text)
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';A saída é uma árvore em que cada nó representa um operador. O campo Outputs em cada nó mostra quais colunas o operador produz, enquanto Estimates apresenta a previsão do otimizador sobre quantidade de linhas e volume de dados. O otimizador usa essas informações para definir a ordem de junção e a estratégia de distribuição de dados.
Output[count(*)]
│ Outputs: [count:bigint]
│ Estimates: {rows: 1 (8B)}
│ count(*) := count
└─ Aggregate(FINAL)
│ Outputs: [count:bigint]
│ Estimates: {rows: 1 (8B)}
│ count := count(`count_1`)
└─ LocalExchange[SINGLE] ()
│ Outputs: [count_0_1:bigint]
│ Estimates: {rows: 1 (8B)}
└─ RemoteExchange[GATHER]
│ Outputs: [count_0_2:bigint]
│ Estimates: {rows: 1 (8B)}
└─ Aggregate(PARTIAL)
│ Outputs: [count_0_4:bigint]
│ Estimates: {rows: 1 (8B)}
│ count_4 := count(*)
└─ InnerJoin[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_6]
│ Outputs: []
│ Estimates: {rows: 302035 (4.61MB)}
│ Distribution: REPLICATED
├─ Project[]
│ │ Outputs: [c_nationkey:integer, $hashvalue:bigint]
│ │ Estimates: {rows: 1500000 (5.72MB)}
│ │ $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
│ └─ RuntimeFilter
│ │ Outputs: [c_nationkey:integer]
│ │ Estimates: {rows: 1500000 (5.72MB)}
│ ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
│ │ Outputs: [c_nationkey:integer]
│ │ Estimates: {rows: 1500000 (5.72MB)}
│ │ c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
│ └─ RuntimeCollect
│ │ Outputs: [n_nationkey:integer]
│ │ Estimates: {rows: 5 (60B)}
│ └─ LocalExchange[ROUND_ROBIN] ()
│ │ Outputs: [n_nationkey:integer]
│ │ Estimates: {rows: 5 (60B)}
│ └─ RuntimeScan
│ Outputs: [n_nationkey:integer]
│ Estimates: {rows: 5 (60B)}
└─ LocalExchange[HASH][$hashvalue_0_6] ("n_nationkey")
│ Outputs: [n_nationkey:integer, $hashvalue_0_6:bigint]
│ Estimates: {rows: 5 (60B)}
└─ Project[]
│ Outputs: [n_nationkey:integer, $hashvalue_0_10:bigint]
│ Estimates: {rows: 5 (60B)}
│ $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
└─ RemoteExchange[REPLICATE]
│ Outputs: [n_nationkey:integer]
│ Estimates: {rows: 5 (60B)}
└─ InnerJoin[(`n_regionkey` = `r_regionkey`)][$hashvalue_0_7, $hashvalue_0_8]
│ Outputs: [n_nationkey:integer]
│ Estimates: {rows: 5 (60B)}
│ Distribution: REPLICATED
├─ Project[]
│ │ Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue_0_7:bigint]
│ │ Estimates: {rows: 25 (200B)}
│ │ $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
│ └─ RuntimeFilter
│ │ Outputs: [n_nationkey:integer, n_regionkey:integer]
│ │ Estimates: {rows: 25 (200B)}
│ ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
│ │ Outputs: [n_nationkey:integer, n_regionkey:integer]
│ │ Estimates: {rows: 25 (200B)}
│ │ n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
│ │ n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
│ └─ RuntimeCollect
│ │ Outputs: [r_regionkey:integer]
│ │ Estimates: {rows: 1 (4B)}
│ └─ LocalExchange[ROUND_ROBIN] ()
│ │ Outputs: [r_regionkey:integer]
│ │ Estimates: {rows: 1 (4B)}
│ └─ RuntimeScan
│ Outputs: [r_regionkey:integer]
│ Estimates: {rows: 1 (4B)}
└─ LocalExchange[HASH][$hashvalue_0_8] ("r_regionkey")
│ Outputs: [r_regionkey:integer, $hashvalue_0_8:bigint]
│ Estimates: {rows: 1 (4B)}
└─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
Outputs: [r_regionkey:integer, $hashvalue_0_9:bigint]
Estimates: {rows: 1 (4B)}/{rows: 1 (B)}
$hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}|
Parâmetro |
Descrição |
|
|
Coluna de saída e tipo de dado de cada operador. |
|
|
Estimativa do otimizador para quantidade de linhas e volume de dados por operador. O otimizador utiliza esses valores para determinar a ordem de junção e a estratégia de distribuição de dados. |
EXPLAIN ANALYZE
O comando EXPLAIN ANALYZE executa a consulta e retorna os custos reais da execução distribuída — durações de execução, pico de uso de memória e contagem de linhas de entrada/saída para cada operador e fragmento.
Sintaxe
EXPLAIN ANALYZE <SELECT statement>;Exemplo
EXPLAIN ANALYZE
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';A saída é organizada por fragmento, e cada fragmento corresponde a uma etapa do plano de execução distribuída. Para cada operador, visualize a contagem real de linhas, o uso de memória e o tempo decorrido, além das estimativas do otimizador. Compare Estimates com Output para identificar onde o modelo do otimizador diverge da execução real. Utilize PeakMemory e WallTime para localizar os operadores mais custosos.
Fragment 1 [SINGLE]
Output: 1 row (9B), PeakMemory: 178KB, WallTime: 1.00ns, Input: 32 rows (288B); per task: avg.: 32.00 std.dev.: 0.00
Output layout: [count]
Output partitioning: SINGLE []
Aggregate(FINAL)
│ Outputs: [count:bigint]
│ Estimates: {rows: 1 (8B)}
│ Output: 2 rows (18B), PeakMemory: 24B (0.00%), WallTime: 70.39us (0.03%)
│ count := count(`count_1`)
└─ LocalExchange[SINGLE] ()
│ Outputs: [count1:bigint]
│ Estimates: {rows: ? (?)}
│ Output: 64 rows (576B), PeakMemory: 8KB (0.07%), WallTime: 238.69us (0.10%)
└─ RemoteSource[2]
Outputs: [count2:bigint]
Estimates:
Output: 32 rows (288B), PeakMemory: 32KB (0.27%), WallTime: 182.82us (0.08%)
Input avg.: 4.00 rows, Input std.dev.: 264.58%
Fragment 2 [adb:AdbPartitioningHandle{schema=tpch, tableName=customer, dimTable=false, shards=32, tableEngineType=Cstore, partitionColumns=c_custkey, prunedBuckets= empty}]
Output: 32 rows (288B), PeakMemory: 6MB, WallTime: 164.00ns, Input: 1500015 rows (20.03MB); per task: avg.: 500005.00 std.dev.: 21941.36
Output layout: [count4]
Output partitioning: SINGLE []
Aggregate(PARTIAL)
│ Outputs: [count4:bigint]
│ Estimates: {rows: 1 (8B)}
│ Output: 64 rows (576B), PeakMemory: 336B (0.00%), WallTime: 1.01ms (0.42%)
│ count_4 := count(*)
└─ INNER Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue6]
│ Outputs: []
│ Estimates: {rows: 302035 (4.61MB)}
│ Output: 300285 rows (210B), PeakMemory: 641KB (5.29%), WallTime: 99.08ms (41.45%)
│ Left (probe) Input avg.: 46875.00 rows, Input std.dev.: 311.24%
│ Right (build) Input avg.: 0.63 rows, Input std.dev.: 264.58%
│ Distribution: REPLICATED
├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
│ Outputs: [c_nationkey:integer, $hashvalue:bigint]
│ Estimates: {rows: 1500000 (5.72MB)}/{rows: 1500000 (5.72MB)}
│ Output: 1500000 rows (20.03MB), PeakMemory: 5MB (44.38%), WallTime: 68.29ms (28.57%)
│ $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
│ c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
│ Input: 1500000 rows (7.15MB), Filtered: 0.00%
└─ LocalExchange[HASH][$hashvalue6] ("n_nationkey")
│ Outputs: [n_nationkey:integer, $hashvalue6:bigint]
│ Estimates: {rows: 5 (60B)}
│ Output: 30 rows (420B), PeakMemory: 394KB (3.26%), WallTime: 455.03us (0.19%)
└─ Project[]
│ Outputs: [n_nationkey:integer, $hashvalue10:bigint]
│ Estimates: {rows: 5 (60B)}
│ Output: 15 rows (210B), PeakMemory: 24KB (0.20%), WallTime: 83.61us (0.03%)
│ Input avg.: 0.63 rows, Input std.dev.: 264.58%
│ $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
└─ RemoteSource[3]
Outputs: [n_nationkey:integer]
Estimates:
Output: 15 rows (75B), PeakMemory: 24KB (0.20%), WallTime: 45.97us (0.02%)
Input avg.: 0.63 rows, Input std.dev.: 264.58%
Fragment 3 [adb:AdbPartitioningHandle{schema=tpch, tableName=nation, dimTable=true, shards=32, tableEngineType=Cstore, partitionColumns=, prunedBuckets= empty}]
Output: 5 rows (25B), PeakMemory: 185KB, WallTime: 1.00ns, Input: 26 rows (489B); per task: avg.: 26.00 std.dev.: 0.00
Output layout: [n_nationkey]
Output partitioning: BROADCAST []
INNER Join[(`n_regionkey` = `r_regionkey`)][$hashvalue7, $hashvalue8]
│ Outputs: [n_nationkey:integer]
│ Estimates: {rows: 5 (60B)}
│ Output: 11 rows (64B), PeakMemory: 152KB (1.26%), WallTime: 255.86us (0.11%)
│ Left (probe) Input avg.: 25.00 rows, Input std.dev.: 0.00%
│ Right (build) Input avg.: 0.13 rows, Input std.dev.: 264.58%
│ Distribution: REPLICATED
├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
│ Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue7:bigint]
│ Estimates: {rows: 25 (200B)}/{rows: 25 (200B)}
│ Output: 25 rows (475B), PeakMemory: 16KB (0.13%), WallTime: 178.81us (0.07%)
│ $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
│ n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
│ n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
│ Input: 25 rows (250B), Filtered: 0.00%
└─ LocalExchange[HASH][$hashvalue8] ("r_regionkey")
│ Outputs: [r_regionkey:integer, $hashvalue8:bigint]
│ Estimates: {rows: 1 (4B)}
│ Output: 2 rows (28B), PeakMemory: 34KB (0.29%), WallTime: 57.41us (0.02%)
└─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
Outputs: [r_regionkey:integer, $hashvalue9:bigint]
Estimates: {rows: 1 (4B)}/{rows: 1 (4B)}
Output: 1 row (14B), PeakMemory: 8KB (0.07%), WallTime: 308.99us (0.13%)
$hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}
Input: 1 row (5B), Filtered: 0.00%|
Parâmetro |
O que indica |
|
|
Coluna de saída e tipo de dado de cada operador. |
|
|
Contagem estimada de linhas e volume de dados pelo otimizador. Compare com |
|
|
Pico de memória utilizado pelo operador ou fragmento. Use este valor para identificar o principal consumidor de memória ao diagnosticar gargalos. |
|
|
Duração total de execução dos operadores. Devido à computação paralela, essa duração não corresponde necessariamente ao tempo real de execução. Use-a para analisar as causas de gargalos durante operações de computação. |
|
|
Quantidade de linhas e bytes lidos como entrada. |
|
|
Média de linhas de entrada por tarefa e desvio padrão entre tarefas. Um desvio padrão alto indica skew de dados — algumas tarefas processam muito mais dados que outras, o que limita a eficiência do paralelismo. |
|
|
Quantidade de linhas e bytes produzidos como saída. |
Diagnosticar problemas comuns
Filtro não aplicado na camada de armazenamento
Quando um filtro não pode ser aplicado diretamente na camada de armazenamento, a consulta varre a tabela inteira e aplica o filtro em memória, aumentando significativamente o uso de I/O e memória.
As duas consultas a seguir ilustram essa diferença:
-
SQL 1 — filtro sobre o valor bruto da coluna (pode ser aplicado na camada de armazenamento):
SELECT count(*) FROM test WHERE string_test = 'a'; -
SQL 2 — filtro envolvido em uma função (não pode ser aplicado na camada de armazenamento):
SELECT count(*) FROM test WHERE length(string_test) = 1;
Execute EXPLAIN ANALYZE em ambas as consultas e compare o Fragment 2.
Saída do SQL 1 (filtro aplicado na camada de armazenamento): O fragmento utiliza o operador TableScan e Input avg. é 0.00 rows, indicando que a camada de armazenamento filtrou todas as linhas antes de retornar os dados.
Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
Output: 4 rows (36B), PeakMemory: 0B, WallTime: 6.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
Output layout: [count_0_1]
Output partitioning: SINGLE []
Aggregate(PARTIAL)
│ Outputs: [count_0_1:bigint]
│ Estimates: {rows: 1 (8B)}
│ Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 212.92us (3.99%)
│ count_0_1 := count(*)
└─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}]
Outputs: []
Estimates: {rows: 4 (0B)}
Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 4.76ms (89.12%)
Input avg.: 0.00 rows, Input std.dev.: ?%Saída do SQL 2 (filtro não aplicado na camada de armazenamento): O fragmento usa ScanFilterProject em vez de TableScan, e Input exibe 9999 rows. O campo filterPredicate confirma que o filtro é aplicado após a varredura, e não na camada de armazenamento.
Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
Output: 4 rows (36B), PeakMemory: 0B, WallTime: 102.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
Output layout: [count_0_1]
Output partitioning: SINGLE []
Aggregate(PARTIAL)
│ Outputs: [count_0_1:bigint]
│ Estimates: {rows: 1 (8B)}
│ Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 252.23us (0.12%)
│ count_0_1 := count(*)
└─ ScanFilterProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}, filterPredicate = (`test4dmp`.`length`(`string_test`) = BIGINT '1')]
Outputs: []
Estimates: {rows: 9999 (312.47kB)}/{rows: 9999 (312.47kB)}/{rows: ? (?)}
Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 101.31ms (49.84%)
string_test := AdbColumnHandle{columnName=string_test, type=13, isIndexed=true}
Input: 9999 rows (110.32kB), Filtered: 100.00%Para diagnosticar esse padrão, procure por ScanFilterProject na saída. Se Input mostrar uma contagem elevada de linhas e Filtered estiver próximo de 100%, a consulta está varrendo muito mais dados do que o necessário. Reescreva o filtro usando uma forma aplicável na camada de armazenamento ou adicione condições que restrinjam a varredura.
Alto uso de memória
Use PeakMemory no nível de fragmento para identificar qual etapa consome mais memória. As causas mais comuns de PeakMemory elevado incluem:
Broadcast joins não intencionais que resultam em alto consumo de memória
Resultados intermediários de junção grandes devido a excesso de dados
Tabela grande no lado probe (esquerdo) de uma junção
TableScanlendo um volume elevado de dados
Após identificar o fragmento, use PeakMemory por operador para localizar o operador específico responsável pelo consumo. Adicione condições de filtragem conforme os requisitos de negócio para limitar o volume de dados ou reestruture a consulta para reduzir o tamanho dos resultados intermediários.
Consultas lentas em colunas sem chave de distribuição
Quando uma consulta filtra por colunas que não são a chave de distribuição, o otimizador não consegue eliminar partições e precisa varrer todos os shards, resultando em uma varredura completa da tabela. Na saída do EXPLAIN ANALYZE, isso aparece como um operador TableScan com alta contagem de linhas em Input e uma porcentagem elevada de WallTime.
Para otimizar esse tipo de consulta:
Defina colunas de consulta de igualdade de alta frequência como chave clusterizada (
CLUSTERED KEY) para controlar o layout físico dos dados e acelerar varreduras por intervalo.Evite
SELECT *; consulte apenas as colunas necessárias para reduzir a sobrecarga de I/O e memória.Execute
ANALYZE TABLE <table_name>para atualizar as estatísticas da tabela e ajudar o otimizador a gerar um plano de execução mais eficiente.