SQL Plan Management

SQL Plan Management helps you control TiDB execution plans. It is useful when the optimizer picks a suboptimal plan.

SQL Binding

SQL Binding lets you force the use of a specific execution plan:

-- Create a binding for the query
CREATE BINDING FOR
  SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01'
USING
  SELECT /*+ USE_INDEX(users, idx_status) */ * FROM users WHERE status = 'active' AND created_at > '2024-01-01';

-- Show all bindings
SHOW BINDINGS;

-- Drop the binding
DROP BINDING FOR SELECT * FROM users WHERE status = 'active';

Optimizer Hints

Hints give the optimizer guidance on how to execute a query:

-- Use a specific index
SELECT /*+ USE_INDEX(users, idx_email) */ * FROM users WHERE email = '[email protected]';

-- Join type hint
SELECT /*+ HASH_JOIN(o, c) */ o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- Aggregation hint
SELECT /*+ HASH_AGG() */ status, COUNT(*) FROM orders GROUP BY status;

EXPLAIN Analysis

-- Show the execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Detailed analysis with statistics
EXPLAIN ANALYZE
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 500000;

SQL Plan Management (SPM)

Auto Capture

-- Enable auto capture to store plans automatically
SET GLOBAL tidb_enable_plan_replayer = ON;

Plan Baseline

-- Store a baseline plan from history
CREATE BASELINE FROM HISTORY USING PLAN DIGEST 'abc123...';

-- Show baseline plans
SELECT * FROM mysql.global_plan_replayer_status;

Cross-Database Binding

-- Create a binding that applies to all databases
CREATE GLOBAL BINDING FOR
  SELECT * FROM t WHERE a = 1
USING
  SELECT /*+ USE_INDEX(t, idx_a) */ * FROM t WHERE a = 1;

Tips

• Use EXPLAIN before optimizing a query
• Create SQL Bindings for queries whose plans change frequently
• Use hints as a temporary solution
• Monitor execution plans using the TiDB Dashboard

In the next chapter, we will look at TiDB Data CDC.