How to analyze and optimize slow SQL statements
A single slow SQL statement can degrade performance for your entire service. Every SQL execution holds I/O resources for its duration — when one statement consumes 30% of resources for a minute, all other statements compete for the remaining 70%. As slow statements pile up, new executions queue and wait.
Resolving a slow SQL problem is a two-step process:
Locate which statements are slow — use the slow query log or console tools.
Diagnose and fix why those statements are slow — that's what this topic covers.
How SQL execution works in PolarDB-X
SQL execution in PolarDB-X spans three layers and involves two network hops: from the client to a compute node (CN), and from the CN to a data node (DN).
The gray path shows the execution flow for statements that cannot be pushed down (parsing → optimization → scheduling → execution). The light green path shows pushdown-capable statements, where the CN sends the query to DNs via a private remote procedure call (RPC) protocol, and DNs scan and compute data before returning results.
Three factors determine how long a statement takes:
Data volume — the more rows a DN must scan, the more I/O it requires. Large scans make DN I/O the bottleneck.
Data retrieval method — whether data is served from cache or disk; whether a global secondary index (GSI) enables fast addressing and predicate-based acceleration.
Data transformation method — operations like sorting, subqueries, aggregations, and joins load data into temporary tables. Large transformations consume significant CPU on the CN. The choice of join algorithm (HashJoin, SortMergeJoin, or BkaJoin) also matters.
Locate slow SQL statements
Use any of the following methods to find slow statements:
Console: Slow query log and Performance Trend show slow statements with filtering and trend views.
`show slow`: Returns the last 100 slow logical SQL statements.
`show physical_slow`: Returns the last 100 slow physical SQL statements executed on DNs.
show slow;+------------------+---------------+-----------+---------------------+--------------+------------+--------------------+
| TRACE_ID | USER | HOST | START_TIME | EXECUTE_TIME | AFFECT_ROW | SQL |
+------------------+---------------+-----------+---------------------+--------------+------------+--------------------+
| 12f812912f400000 | polardbx_root | 127.0.0.1 | 2021-08-24 12:59:38 | 6475 | 0 | show physical_slow |
| 12f8128383400000 | polardbx_root | 127.0.0.1 | 2021-08-24 12:59:19 | 1899 | -1 | show physical_slow |
+------------------+---------------+-----------+---------------------+--------------+------------+--------------------+
2 rows in set (0.01 sec)show physical_slow;+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
| GROUP_NAME | DBKEY_NAME | START_TIME | EXECUTE_TIME | SQL_EXECUTE_TIME | GETLOCK_CONNECTION_TIME | CREATE_CONNECTION_TIME | AFFECT_ROW | SQL |
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
| TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 2021-03-16 13:05:38 | 1057 | 1011 | 0 | 0 | 1 | select sleep(1) |
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
1 row in set (0.01 sec)show slowandshow physical_slowonly retain the last 100 records. For historical data, use the console slow query log.
Diagnose slow SQL statements
Slow statements fall into three root-cause categories:
Service issues — data skew, an unsuitable sharding strategy, or excessive rows returned to the client.
System issues — resource exhaustion caused by traffic spikes, or network jitter.
Execution issues — a wrong index, join type, or join order chosen by the optimizer.
The following sections walk through each category with the tools to use.
Check for service issues
High row count on a simple query
If a simple SELECT returns hundreds of thousands or millions of rows, that's expected behavior — the query is doing exactly what it asks. Decide from a business perspective whether that data volume is necessary. If not, add filters or paginate results.
Data skew
If a query is slow only for specific partition key values, the underlying data may be skewed. Run show table info from <table> to inspect shard sizes:
show table info from test_tb;+----+------------+-----------------+------------+
| ID | GROUP_NAME | TABLE_NAME | SIZE_IN_MB |
+----+------------+-----------------+------------+
| 0 | ads_000000 | test_tb_o2ud_00 | 0.01562500 |
| 1 | ads_000000 | test_tb_o2ud_01 | 0.01562500 |
| 2 | ads_000000 | test_tb_o2ud_02 | 0.01562500 |
| 3 | ads_000000 | test_tb_o2ud_03 | 0.01562500 |
| 4 | ads_000000 | test_tb_o2ud_04 | 0.01562500 |
+----+------------+-----------------+------------+Significant size differences between shards confirm data skew. Consider adjusting the sharding strategy.
Check for system issues
Traffic spike with resource exhaustion
If slow statements appear alongside a traffic increase, check Resource Monitoring in the console for CPU and memory usage at the affected time. If resources are exhausted due to load growth, the slowness is expected. Options:
Scale out the cluster.
Apply SQL throttling to protect the system during peak traffic.
Network issues
If no slow statements appear in the log but the service is affected, and you've ruled out service-side issues, check network connectivity. Confirm that the client and server are in the same VPC. If not, capture packets for further analysis.
Check for execution issues
PolarDB-X provides four diagnostic commands. Use the one that matches your situation:
| When you need to... | Use |
|---|---|
| Check whether the overall execution plan is reasonable | explain |
| Check whether a local index is used on the DN | explain execute |
| Find which operator is the bottleneck | explain analyze |
| Find whether the bottleneck is on the CN or DN | trace + show trace |
View the logical execution plan
explain shows how the optimizer plans to execute the query at the logical level — including join order, join type, and which shards are accessed.
explain select nation, o_year, sum(amount) as sum_profit
from (
select n_name as nation, extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from part, supplier, lineitem, partsupp, orders, nation
where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
and ps_partkey = l_partkey and p_partkey = l_partkey
and o_orderkey = l_orderkey and s_nationkey = n_nationkey
and p_name like '%yellow%'
) as profit
group by nation, o_year
order by nation, o_year desc
limit 1;+-----------------------------------------------------------------------------...+
| LOGICAL EXECUTIONPLAN |
+-----------------------------------------------------------------------------...+
| Limit(offset=0, fetch=?2) |
| SortAgg(group="nation,o_year", $f2="SUM(amount)") |
| Project(nation="N_NAME", o_year="EXTRACT", ...) |
| HashJoin(condition="PS_PARTKEY = L_PARTKEY ...", type="inner") |
| ... |
| LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="...") |
| HitCache:false |
| Source:PLAN_CACHE |
+-----------------------------------------------------------------------------...+shardCountin aLogicalViewis the total number of shards accessed.
Analyze the plan for these issues:
Is the right join algorithm selected (HashJoin, SortMergeJoin, BkaJoin)?
Is a global secondary index being used where it should be?
Is aggregation happening at the right layer?
If the plan is suboptimal, fix it with one of these approaches:
Refresh statistics — the optimizer relies on statistics to build plans. Stale statistics lead to poor choices. Check current statistics with
show statistic, then refresh a specific table:show statistic;+---------------+------------+-------------+-------------+ | table_name | table_rows | column_name | cardinality | +---------------+------------+-------------+-------------+ | tpch_orders | 8400 | NULL | NULL | | tpch_lineitem | 10000 | NULL | NULL | | lineitem | 8492 | NULL | NULL | | ... | ... | ... | ... | +---------------+------------+-------------+-------------+ 15 rows in set (0.01 sec)analyze table lineitem;+--------------+---------+----------+----------+ | TABLE | OP | MSG_TYPE | MSG_TEXT | +--------------+---------+----------+----------+ | ads.lineitem | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (1.66 sec)Force an index with a hint — even with accurate statistics, the optimizer's dynamic programming search may not find the optimal plan. Use
HINT force indexto override the choice manually.Add a global secondary index — if the plan is slow because the join key differs from the sharding key, add a GSI on the join key to enable pushdown on the index table.
View the physical execution plan
explain execute shows the execution plan for the pushdown SQL as it runs on the DN. Use it to confirm whether a local index is selected.
-- Full table scan (no index used)
explain execute select * from lineitem;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+-- Index scan (PRIMARY key used via covering index)
explain execute select L_LINENUMBER from lineitem;+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | lineitem | NULL | index | NULL | PRIMARY | 8 | NULL | 1 | 100 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+Identify the slow operator
explain analyze runs the query and reports actual time and row counts per operator, so you can pinpoint exactly where time is spent.
explain analyze select L_SUPPKEY, count(*) from lineitem group by L_SUPPKEY;+--------------------------------------------------------------------------------------...+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------...+
| HashAgg(...): rowcount = 1000.0, ..., actual time = 0.000 + 0.000, actual rowcount = 1000, ... |
| Gather(concurrent=true): ..., actual rowcount = 0, ... |
| LogicalView(..., shardCount=16, ...): ..., actual time = 0.004 + 0.002, actual rowcount = 4892, ... |
+--------------------------------------------------------------------------------------...+Key fields in the output:
| Field | Meaning |
|---|---|
rowcount | Estimated row count based on statistics |
actual rowcount | Actual rows produced during execution |
actual time | Time spent in this operator: startup + execution |
Look for operators where actual time is high or where actual rowcount far exceeds rowcount — those are the bottlenecks. If the issue is insufficient concurrency, adjust the query executor concurrency.
Trace where time is spent (CN vs. DN)
trace captures the actual pushdown SQL sent to each DN shard and the time cost for each. Run trace first, then show trace to see the results.
trace must be run on a MySQL client. Download one from the MySQL website if needed.trace select L_SUPPKEY, count(*) from lineitem group by L_SUPPKEY limit 1;+-----------+----------+
| L_SUPPKEY | count(*) |
+-----------+----------+
| 1 | 12 |
+-----------+----------+
1 row in set (0.21 sec)show trace;+----+----------------+-----------+-------+------------------+...+---------------+...+------+...+
| ID | NODE_IP | TIMESTAMP | TYPE | GROUP_NAME |...| TIME_COST(MS) |...| ROWS |...|
+----+----------------+-----------+-------+------------------+...+---------------+...+------+...+
| 0 | 192.168.31.105 | 0.000 | Query | ADS_000003_GROUP |...| 3 |...| 0 |...|
| 4 | 192.168.31.105 | 0.000 | Query | ADS_000001_GROUP |...| 5 |...| 746 |...|
| 9 | 192.168.31.105 | 0.000 | Query | ADS_000000_GROUP |...| 5 |...| 884 |...|
| 11 | 192.168.31.105 | 0.000 | Query | ADS_000000_GROUP |...| 6 |...| 917 |...|
| ... |...|
+----+----------------+-----------+-------+------------------+...+---------------+...+------+...+
16 rows in set (0.05 sec)The show trace output shows the actual pushdown physical SQL and its time cost for each shard. Use it to clarify whether the high time cost occurs at the CN layer or the DN layer.
Optimization strategies
Once you know the root cause, apply the matching strategy.
Push down join operations
If large-table joins dominate your workload, design the sharding key to match the join key. PolarDB-X can then push the join down to the DN and avoid cross-node data movement.
For joins between a large table and a small, infrequently updated table, convert the small table to a broadcast table. Any join with a broadcast table becomes pushdown-capable.
If changing the sharding strategy isn't an option, create a GSI on the join key. The join can then run on the GSI's index table on the DN.
Reduce data scans
Add predicate conditions (such as time-range filters) to reduce the rows scanned.
Check whether an index can be hit to avoid full table scans.
Cache frequently accessed data
If a query involves a large table and cannot be further optimized, move the result to an application-side cache or a service like Redis. This works well when the data volume is small, the data changes infrequently, and access frequency is high.
Fix the execution plan
Refresh statistics with
analyze tablewhen the optimizer uses a suboptimal plan.Use
HINT force indexto override index selection manually.Add a GSI to enable pushdown for queries that currently require cross-node operations.
Select a more reasonable data transformation algorithm in the execution plan. Join algorithms include HashJoin, SortMergeJoin, and BkaJoin. Each join algorithm is suited for specific scenarios.