SQL Plan Binding in TiDB: Keep the Good Execution Plan Without Changing Your Application

Database performance problems are not always caused by a bad SQL query.

In many production environments, a query runs smoothly for months. Then, without warning, query execution time spikes. The application code did not change. The SQL statement did not change. The table schema and indexes did not change.

Yet, the query is crawling.

When this happens, the root cause is almost always an execution plan regression. The database optimizer re-evaluated the execution alternatives and chose a different, suboptimal path.

TiDB offers an integrated mechanism to handle this problem: SQL Plan Management (SPM). Within SPM, SQL Plan Binding gives database administrators the ability to direct TiDB:

"For this specific SQL statement, we already know which execution path works well. Continue using that path."

The best part is that you can apply this fix immediately on the database side without modifying, testing, and deploying application code. This makes SQL Plan Binding an indispensable tool during high-pressure production incidents.

What is an execution plan?

When an application issues a query like this:

SELECT *
FROM users
WHERE name LIKE 'ro%'
ORDER BY status, name, uid
LIMIT 501 OFFSET 500;

TiDB does not simply start reading raw data from TiKV storage. The TiDB cost-based optimizer first generates and evaluates multiple potential execution paths.

The optimizer calculates the estimated cost of each candidate plan using table statistics, data distribution, predicate selectivity, estimated cardinality, ORDER BY operations, and LIMIT clauses. It picks the plan with the lowest estimated cost.

Most of the time, this works reliably. However, when statistics become stale, data distributions shift, or complex queries make cardinality estimation difficult, the optimizer may choose a plan that looks cheaper on paper but runs significantly worse in production.

This is where SQL Plan Binding comes into play.

What is SQL Plan Binding?

An SQL binding attaches one or more TiDB optimizer hints to an incoming SQL statement transparently.

Suppose an application runs:

SELECT *
FROM users
WHERE name LIKE 'ro%';

From diagnostic testing, you identify that an existing index idx_users_name delivers the required performance. Normally, taking advantage of that index requires modifying the application SQL directly:

SELECT /*+ USE_INDEX(users, idx_users_name) */ *
FROM users
WHERE name LIKE 'ro%';

In a real enterprise environment, pushing that single change requires a code review, QA testing, deployment coordination, and a rollback plan. During an active incident, you cannot afford to wait for that lifecycle.

With TiDB SQL Plan Binding, you create the binding directly on the cluster:

CREATE GLOBAL BINDING FOR
SELECT *
FROM users
WHERE name LIKE 'ro%'
USING
SELECT /*+ USE_INDEX(users, idx_users_name) */ *
FROM users
WHERE name LIKE 'ro%';

Once applied, the application continues to issue the clean, unhinted SQL query:

SELECT *
FROM users
WHERE name LIKE 'ro%';

Behind the scenes, TiDB matches the query, attaches the bound hint, and produces the desired execution plan:

TiDB supports bindings on SELECT, DELETE, UPDATE, and INSERT/REPLACE statements containing SELECT subqueries.

Step 1: Always Test the Hint First

Never bind a query without verifying that the hint produces the intended plan and operates without warnings.

Run the hinted statement directly using EXPLAIN ANALYZE:

EXPLAIN ANALYZE 
SELECT /*+ USE_INDEX(users, idx_users_name) */ *
FROM users
WHERE name LIKE 'ro%';

Immediately after running the query, check for warnings:

SHOW WARNINGS;

If the hint contains a typo, references an invalid index, or conflicts with the query structure, TiDB outputs a warning here. If the optimizer cannot apply the hint, creating a binding for it will not resolve the problem.

Step 2: Creating a Global Binding

Bindings should be created within the explicit database context of the query.

Assuming the target table belongs to the employee database:

USE employee;

CREATE GLOBAL BINDING FOR
SELECT *
FROM users
WHERE name LIKE 'ro%'
USING
SELECT /*+ USE_INDEX(users, idx_users_name) */ *
FROM users
WHERE name LIKE 'ro%';

Now the application continues sending:

Now, any client connection running that query will automatically have the execution plan stabilized.

Common Misconception: Does "GLOBAL" Mean All Databases?

This is one of the most frequent points of confusion with TiDB bindings.

No. GLOBAL does not mean the binding matches a table named users across every database in the cluster.

Instead, GLOBAL defines the lifetime and scope of the binding across cluster nodes (cluster-wide vs. session-level). A global binding applies across all TiDB server instances rather than terminating when your current terminal session closes.

When you create a standard global binding, TiDB resolves and records the default database context. During normalization, TiDB qualifies the table names with their respective database (e.g., employee.users).

How TiDB Recognizes Bound Queries

TiDB does not match incoming queries through a rigid character-by-character string comparison. It relies on SQL Normalization.

Consider this query:

SELECT *
FROM users
WHERE balance > 100;

During normalization, TiDB strips literal constants, resolves object namespaces, and removes irregular whitespace. Conceptually, it normalizes to:

SELECT *
FROM employee.users
WHERE balance > ?;

If the application subsequently runs:

SELECT *
FROM users
WHERE balance > 5000;

TiDB recognizes that it shares the exact same normalized structure and SQL digest. As a result, one single binding covers varying parameter values and literals.

How to Verify That the Binding Exists

To view all active bindings in your cluster, query:

SHOW GLOBAL BINDINGS\G

Output:

*************************** 1. row ***************************
Original_sql: SELECT * FROM users WHERE name LIKE 'ro%'
    Bind_sql: SELECT /*+ USE_INDEX(users, idx_users_name) */ * FROM users WHERE name LIKE 'ro%'
  Default_db: employee
      Status: enabled
 Create_time: 2026-09-28 00:45:50.973
 Update_time: 2026-09-28 00:45:50.973
     Charset: utf8mb4
   Collation: utf8mb4_0900_ai_ci
      Source: manual
  Sql_digest: 982b0a6bd6bc486a192a57d934d0a778395c9238315e061409a84e0ae89867c8
 Plan_digest: 
1 row in set (0.00 sec)

Look closely at the Status column (enabled) and confirm that Default_db aligns with the execution schema.

Confirming That Queries Actually Use the Binding

Seeing a binding in SHOW GLOBAL BINDINGS does not guarantee that your application queries are successfully using it. Always test and verify.

Method 1: Check the Session Variable

Run the application query in your MySQL client session, then immediately check:

SELECT @@LAST_PLAN_FROM_BINDING;

• If the output is 1, the optimizer generated the plan using the active binding.
• If the output is 0, the binding was ignored or did not match.

mysql> SELECT @@LAST_PLAN_FROM_BINDING;
+--------------------------+
| @@LAST_PLAN_FROM_BINDING |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

Method 2: Verbose Explain Output

You can also run:

EXPLAIN FORMAT='VERBOSE' SELECT ...;
SHOW WARNINGS;

When a binding matches, TiDB outputs an informational warning indicating the specific bind_sql statement that influenced plan construction.

Can a Binding Override an Application Hint?

Yes. TiDB gives administrative SQL bindings higher priority than hints written into application SQL.

If a developer wrote a query with an embedded hint, but an administrator creates a global binding for that query, the global binding overrides the application hint.

While this allows DBAs to quickly correct misbehaving hints during emergencies, it also underscores the need for clear communication and tracking. If a binding is active, developers inspecting the codebase may not understand why their local hint changes have no effect.

Dropping a Binding

When a schema change, index addition, or version upgrade makes a binding unnecessary, drop it:

DROP GLOBAL BINDING FOR
SELECT *
FROM users
WHERE name LIKE 'ro%';

TiDB marks the global binding record as deleted and propagates the removal across all nodes in the cluster. Run SHOW GLOBAL BINDINGS; to confirm removal.

A Good Plan Today Can Become a Bad Plan Tomorrow

This is the most critical operational rule of SQL plan management:

SQL Plan Bindings provide plan stability, not permanent performance guarantees.

Consider this real-world scenario:

1. Month 1: Forcing Index A performs well because only 0.5% of rows match the filter predicate.
2. Month 6: Application data shifts. Now, 70% of rows match the filter. Scanning the index followed by individual row lookups is now dramatically slower than a simple table scan.

If you lock an execution plan indefinitely, that binding can turn into the very performance bottleneck you were trying to solve. Treat every binding as a managed operational lifecycle:

Advanced Alternative: Binding Directly from Historical Plan Digests

If a query started running poorly after 11:00 AM, but ran cleanly at 10:00 AM, you do not always need to manually construct hints from scratch.

TiDB tracks historical query executions in the Statement Summary tables. You can extract the plan_digest of the earlier, fast run and bind it directly:

CREATE GLOBAL BINDING 
FROM HISTORY 
USING PLAN DIGEST '3a886b...';

TiDB inspects the execution details tied to that historical plan digest and reconstructs the necessary hints automatically.

Note: For complex multi-table joins and subqueries, automatically reconstructed hints should still be carefully checked with EXPLAIN to make sure every access path matched your expectations.

Production Checklist

Before closing out an incident involving SQL Plan Binding, work through this checklist:

1. Compare Execution Stats: Collect EXPLAIN ANALYZE outputs for both the bad plan and the target plan. Compare execution time, scanned rows, scanned keys, memory, and Request Unit (RU) consumption.
2. Test Hints Directly: Run the hinted SQL in isolation and check SHOW WARNINGS; to ensure zero hint errors.
3. Check Context: Ensure you run USE <database>; before creating bindings so the table name is bound to the proper schema.
4. Validate Status: Confirm that Status is enabled in SHOW GLOBAL BINDINGS.
5. Verify Usage: Run the unhinted query and ensure SELECT @@LAST_PLAN_FROM_BINDING; returns 1.
6. Log and Track: Document the binding in your internal DBA change log. Include why it was added, who created it, and the date it must be reviewed.
7. Monitor Workload: Keep an eye on cluster resource metrics and RU utilization to confirm the stabilization holds under peak traffic and confirm whether the pressure is a plan problem or a capacity problem.

Optimize Your TiDB Deployments with Mydbops

Struggling with unexpected query regressions, cost optimizer hiccups, or scaling limits on your distributed database? Mydbops provides end-to-end database architecture, performance tuning, and Remote DBA support for high-throughput TiDB, MySQL, and MariaDB environments.