Deep Dive into TiDB SQL Optimizer and Execution Engine

The presentation introduces TiDB’s overall architecture, which consists of four core modules: TiDB (SQL layer), TiKV (distributed transactional key‑value store), PD (cluster meta‑service and hotspot scheduling), and TiSpark for heavy analytical workloads.

It explains how relational tables and indexes are mapped to key‑value pairs in TiKV, and describes the SQL layer components such as the protocol layer, session context, and various managers handling permissions, DDL, and garbage collection.

The query optimizer is divided into logical and physical phases. Logical optimization applies rule‑based transformations—such as column pruning, partition pruning, group‑by elimination, projection elimination, outer‑join simplification, max/min elimination, and subquery decorrelation—to produce an equivalent but more efficient logical plan. Physical optimization then selects concrete algorithms (e.g., hash join, merge join) and determines execution attributes like task placement (TiDB vs. TiKV) and data ordering, using dynamic programming or greedy strategies for join ordering.

Statistics collection is covered, highlighting histogram‑based range estimation, Min‑Sketch for point queries, and methods for gathering statistics (manual ANALYZE, automatic ANALYZE, and query feedback). Accurate statistics are crucial for cost estimation in filters, joins, and aggregations.

Future work includes improving query plan stability, enhancing the Cascades planner for better search space pruning, accelerating large‑scale ANALYZE operations, and introducing multi‑column statistics to capture inter‑column correlations for more precise cardinality estimates.