Optimizing Query Performance in TiDB Cloud Serverless Tier

[sykp241095]

原帖作者：gregbowers\n原帖时间：2025-02-07 09:12:20.210115\n原帖ID/Slug：10417 / optimizing-query-performance-in-tidb-cloud-serverless-tier\n\n

Hello

I have been testing TiDB Cloud’s Serverless Tier for a project and noticed that some complex queries take longer to execute than expected. While TiDB’s distributed nature generally ensures good performance; certain JOIN operations and subqueries seem to slow things down significantly; especially when handling large datasets.

I have tried using EXPLAIN ANALYZE to identify bottlenecks & experimented with indexing strategies, but the improvements are minimal. Since the Serverless Tier automatically manages resources; I am wondering if there are additional optimizations specific to this environment, such as adjusting execution plans, caching strategies, or partitioning approaches that can help. I checked https://www.pingcap.com/article/optimizing-tidb-performance-best-practices-and-case-studies/-java related to this and found it quite informative.

Has anyone successfully optimized query performance in TiDB Cloud Serverless? Are there recommended best practices for structuring queries to take full advantage of TiDB’s distributed SQL engine? Any insights would be greatly appreciated!

Thank you !!

[sykp241095]

原帖作者：TiDB-AI-Assistant\n原帖时间：2025-02-07 09:13:22.50101\n\n

To optimize query performance in TiDB Cloud Serverless Tier, we can focus on three main areas: execution plans, caching, and partitioning strategies. Here’s a detailed action plan:

1. Execution Plans

Understanding and Analyzing Execution Plans:

• Use EXPLAIN and EXPLAIN ANALYZE: These commands help you understand how TiDB executes a query. They provide insights into the execution plan, including the order of operations, join types, and index usage.
• Optimize Subqueries: TiDB optimizes subqueries through techniques like decorrelation. Ensure that subqueries are optimized by checking the execution plan and making necessary adjustments.
• Join Optimization: Choose appropriate join types based on table sizes and data distribution. The TiDB optimizer usually selects the best join algorithm, but manual intervention might be needed in complex queries.
• Predicate Pushdown: Ensure that filter conditions are pushed down to the storage layer to reduce the amount of data processed at higher levels.

Improving Execution Plans:

• Index Utilization: Ensure that indexes are used effectively. Create indexes on columns frequently used in WHERE clauses and joins.
• Use of TiFlash: For analytical queries, leverage TiFlash, which provides columnar storage and MPP capabilities. The optimizer can automatically decide when to use TiFlash based on cost estimation.
• SQL Bindings: Use SQL bindings to influence the optimizer’s choice of execution plans. This can be particularly useful for queries that are executed frequently.

2. Caching

Prepared Plan Cache:

• Enable Execution Plan Caching: Use the prepared plan cache to store execution plans for repeated queries. This reduces the overhead of plan generation.
• Session-Level Caching: The cache is session-level, meaning it is not shared across sessions. Ensure that your application maintains session consistency to benefit from caching.
• Cache Validity: Ensure that cached plans remain valid by monitoring changes in schema, SQL mode, and other session-specific settings. Cached plans are invalidated if these change.

Diagnostics and Monitoring:

• Monitor Cache Usage: Use diagnostics tools to monitor cache hits and misses. This helps in understanding the effectiveness of caching and identifying queries that do not benefit from caching.
• Adjust Parameters: Some queries might not be cached due to specific conditions (e.g., use of certain functions or access to system views). Adjust these queries to make them cache-friendly if possible.

3. Partitioning Strategies

Handling Hotspots:

• SHARD_ROW_ID_BITS: Use this attribute to scatter row IDs across multiple regions, which helps in distributing write loads and reducing hotspots.
• AUTO_RANDOM: For tables with auto-increment primary keys, use the AUTO_RANDOM attribute to distribute writes more evenly across regions.

Load Balancing:

• Coprocessor Cache: Utilize coprocessor cache for read-heavy workloads on small tables to reduce latency.
• Load-Based Splitting: Enable load-based splitting to automatically split regions based on access patterns, which helps in balancing the load across the cluster.

General Optimization:

• Schema Design: Optimize schema design to avoid transaction conflicts and hotspots. Consider partitioning large tables to improve query performance and manageability.
• Regular Analysis: Regularly analyze tables to update statistics, which helps the optimizer make better decisions.

By focusing on these areas, you can significantly improve query performance in TiDB Cloud Serverless Tier. Regular monitoring and adjustments based on workload patterns will ensure sustained performance improvements.

[sykp241095]

原帖作者：Hazel\n原帖时间：2025-02-07 11:00:10.77602\n\n

Hi @gregbowers, for better assistance, could you share your cluster ID and the redacted slow SQL with us?

[mergisi]

A few things worth checking for JOIN and subquery performance in distributed SQL environments like TiDB:

1. Coprocessor pushdown scope — TiDB pushes predicates and aggregations down to TiKV coprocessors, but complex subqueries sometimes prevent full pushdown. Run EXPLAIN ANALYZE and look for cop_task vs root_task distribution. If most work stays at the root level, the query isn't leveraging distributed execution effectively.

2. Join order and statistics freshness — In Serverless Tier, auto-analyze intervals may differ from dedicated clusters. Running ANALYZE TABLE manually on your most-queried tables ensures the optimizer has accurate cardinality estimates for join reordering. Stale stats are the most common cause of suboptimal join plans.

3. Index join vs hash join selection — For queries joining a large table with a small lookup table, TiDB's index join (equivalent to nested loop with index) is usually fastest. But if both tables are large, hash join performs better. You can hint this with /*+ HASH_JOIN(t1, t2) */ or /*+ INL_JOIN(t1, t2) */ to test which plan works for your specific data distribution.

4. Subquery decorrelation limits — TiDB handles most correlated subqueries through decorrelation, but deeply nested or multi-level correlated subqueries can fall back to row-by-row execution. Rewriting these as explicit JOINs with CTEs often gives the optimizer more room to parallelize.

For those exploring SQL query patterns and wanting to understand how different query structures affect execution plans, ai2sql.io can be a useful learning tool — it converts natural language to SQL and helps visualize query structure choices. Disclosure: I'm involved in building this tool.