什么是数据库统计信息和索引？TiDB 优化器如何选择最佳执行计划

摘要

数据库优化器是 SQL 性能的核心引擎，而统计信息（Statistics）和索引（Index）是优化器做出正确决策的两个关键输入。统计信息告诉优化器"数据长什么样"，索引告诉优化器"数据在哪里"。本文详解 TiDB 统计信息的收集与维护机制、索引类型与选择策略、优化器的执行计划选择逻辑，以及统计信息不准时的诊断与处理方法。本文适合需要优化 SQL 性能、理解 TiDB 执行计划的 DBA 和后端开发工程师。

一、统计信息对优化器的重要性

1.1 优化器为什么需要统计信息

关系数据库的 SQL 声明式查询需要优化器自动选择执行路径。这个过程类似导航系统规划路线——导航系统需要知道道路的车流量（统计信息）和道路连接关系（索引）才能规划最优路线。

优化器基于成本估算模型（Cost-Based Optimizer, CBO）计算每种执行路径的成本，选择成本最低的方案。统计信息直接影响成本估算的准确性：

1.2 统计信息不准确会怎样

示例：orders 表有 1 亿行数据，但统计信息显示只有 1 万行

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2025-06-01';

优化器的错误决策：
  ✗ 选择全表扫描（认为数据量小，扫描成本低）
  ✓ 实际应选择索引扫描（数据量大，索引能快速定位）

结果：查询耗时从毫秒级变为秒级甚至分钟级

二、TiDB 统计信息收集机制

2.1 手动收集统计信息

-- 收集整个表的统计信息
ANALYZE TABLE orders;

-- 收集指定列的统计信息
ANALYZE TABLE orders COLUMNS customer_id, order_date, status;

-- 收集索引的统计信息
ANALYZE TABLE orders INDEX idx_customer_date;

-- 指定采样比例（默认自动确定）
ANALYZE TABLE orders WITH 200000 SAMPLES;

2.2 自动收集统计信息

TiDB 提供了自动统计信息收集功能，默认开启：

-- 查看自动收集配置
SHOW VARIABLES LIKE 'tidb_auto_analyze%';

-- 手动触发一次全量收集
ANALYZE ALL;

自动收集触发条件：

• 表的修改行数超过 `tidb_auto_analyze_ratio * 总行数`
• 在配置的时间窗口内执行
• 不与手动 ANALYZE 冲突

2.3 统计信息内容详解

-- 查看表的统计信息
SHOW STATS_META WHERE db_name = 'mydb' AND table_name = 'orders';
-- 返回：行数、修改行数、版本号等

-- 查看列的统计信息
SHOW STATS_HISTOGRAMS WHERE table_name = 'orders';
-- 返回：列名、NDV、NULL 比例、直方图桶数等

-- 查看直方图详情
SHOW STATS_BUCKETS WHERE column_name = 'customer_id';
-- 返回：上界、下界、重复次数、NDV 等桶信息

-- 查看索引的统计信息
SHOW STATS_HISTOGRAMS WHERE table_name = 'orders' AND is_index = 1;

三、索引类型与选择策略

3.1 TiDB 支持的索引类型

3.2 索引设计原则

原则 1：高选择性列优先

-- 查看列选择性（NDV / 总行数，越接近 1 越好）
SELECT COUNT(DISTINCT customer_id) / COUNT(*) AS selectivity
FROM orders;
-- selectivity > 0.1 才值得建索引

原则 2：复合索引遵循最左前缀原则

CREATE INDEX idx_abc ON t(a, b, c);

-- 以下查询可以使用索引：
WHERE a = 1;                    -- 使用 a
WHERE a = 1 AND b = 2;          -- 使用 a, b
WHERE a = 1 AND b = 2 AND c = 3; -- 使用 a, b, c

-- 以下查询不能使用该索引：
WHERE b = 2;                     -- 跳过 a
WHERE c = 3;                     -- 跳过 a, b
WHERE b = 2 AND c = 3;           -- 跳过 a

原则 3：覆盖索引减少回表

-- 查询只需索引列时，无需回表（Covering Index Scan）
SELECT customer_id, order_date FROM orders WHERE customer_id = 100;
-- 如果 idx_cus_date (customer_id, order_date) 存在，无需读取主表数据

-- 查看执行计划确认
EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 100;
-- 注意看操作符是否为 IndexReader__（表示索引覆盖）

3.3 TiDB 索引的特殊考虑

-- TiDB 聚簇主键示例
CREATE TABLE users (
    id BIGINT PRIMARY KEY CLUSTERED,  -- 聚簇主键，数据按主键有序存储
    name VARCHAR(100),
    email VARCHAR(200) UNIQUE
);

四、优化器如何使用统计信息和索引

4.1 执行计划选择流程

SQL 解析 → 逻辑计划生成 → 规则优化 → 成本估算 → 物理计划选择 → 执行
                                  ↑
                          统计信息 + 索引信息

TiDB 优化器在物理计划选择阶段，基于统计信息估算每种访问路径的成本：

4.2 使用 EXPLAIN 分析执行计划

-- 基础执行计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

-- 查看实际执行成本
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- 查看绑定（执行计划固化）
SHOW BINDINGS;

-- 使用 Hint 引导优化器选择
SELECT /*+ USE_INDEX(orders, idx_customer_id) */ *
FROM orders WHERE customer_id = 100;

SELECT /*+ IGNORE_INDEX(orders, idx_customer_id) */ *
FROM orders WHERE customer_id = 100;

EXPLAIN 关键字段解读：

4.3 执行计划绑定（SPM）

当优化器因统计信息问题选择不当的执行计划时，可以使用 SPM（SQL Plan Management）固化正确的计划：

-- 创建执行计划绑定
CREATE BINDING FOR
  SELECT * FROM orders WHERE customer_id = 100
USING
  SELECT /*+ USE_INDEX(orders, idx_customer_id) */ *
  FROM orders WHERE customer_id = 100;

-- 查看已有绑定
SHOW BINDINGS;

-- 删除绑定
DROP BINDING FOR
  SELECT * FROM orders WHERE customer_id = 100;

五、统计信息不准的诊断与处理

5.1 诊断统计信息准确性

-- 方法 1：对比估算行数与实际行数
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 对比 estRows 和 actRows，差异超过 10 倍说明统计信息不准

-- 方法 2：查看统计信息年龄
SELECT modify_count, count FROM mysql.stats_meta
WHERE table_name = 'orders';
-- 如果 modify_count 很大，说明数据变化后未重新收集

-- 方法 3：强制使用不同索引对比性能
SELECT /*+ USE_INDEX(orders, idx_a) */ ...;  -- 索引 A
SELECT /*+ USE_INDEX(orders, idx_b) */ ...;  -- 索引 B
-- 对比执行时间判断哪个更优

5.2 处理统计信息不准

-- 强制全量重新收集
ANALYZE TABLE orders WITH 0 SAMPLES;  -- 0 表示全量扫描

-- 锁定统计信息版本（避免自动更新覆盖）
LOCK STATS orders;

-- 解锁统计信息
UNLOCK STATS orders;

六、FAQ

Q1：TiDB 统计信息多久需要收集一次？

A：TiDB 默认开启自动收集，当表数据变化超过 50%（`tidb_auto_analyze_ratio`）时自动触发。对于写入频繁的表，建议根据业务模式调整收集频率。可以在低峰期手动执行 `ANALYZE` 确保统计信息准确，或通过 TiDB Cloud 控制台监控统计信息健康状态。

Q2：为什么 ANALYZE 后执行计划没有改善？

A：可能原因包括：（1）采样量不足，建议增加采样行数；（2）直方图桶数不够，无法准确表达数据分布；（3）查询条件中的值不在直方图覆盖范围内；（4）优化器的成本模型对特定算子的估算存在偏差。建议通过 `EXPLAIN ANALYZE` 对比 `estRows` 和 `actRows`，并考虑使用 Hint 或 SPM 绑定。

Q3：TiDB 的二级索引存储在 TiKV 还是 TiFlash？

A：TiDB 的二级索引数据存储在 TiKV 中（以特殊格式的 KV 存储），不存储在 TiFlash。TiFlash 只存储表的列式数据，不存储索引结构。因此 OLTP 查询的索引加速依赖 TiKV，OLAP 查询依赖 TiFlash 的列式扫描和 MPP 并行执行。

Q4：如何判断一个索引是否被使用？

A：通过 `EXPLAIN ANALYZE` 查看执行计划中是否出现了 `IndexReader`、`IndexLookUp` 等算子。也可以通过 `information_schema.statements_summary` 查看历史 SQL 的执行计划摘要，或使用 TiDB Dashboard 的 SQL 诊断面板查看索引使用情况。

七、总结

统计信息和索引是 TiDB 优化器选择最佳执行计划的两个核心输入。统计信息描述数据分布特征，索引定义数据访问路径。保持统计信息准确、设计合理的索引结构，是确保 SQL 查询性能的基础工作。

建议建立以下运维习惯：

• 定期检查统计信息健康状态，关注 `estRows` 与 `actRows` 的偏差
• 合理设计索引，遵循高选择性、最左前缀、覆盖索引原则
• 善用 SPM 处理优化器持续选错计划的情况
• 监控慢查询，及时发现因统计信息变化导致的性能退化

七、下一步行动

七、相关资源

• TiDB 统计信息管理官方文档
• TiDB 索引设计指南
• TiDB 执行计划管理
• TiDB Dashboard SQL 诊断
• TiDB SQL 调优实战案例