数据库索引优化实战（B-Tree、覆盖索引、选择性与回表）

数据库索引优化实战（B-Tree、覆盖索引、选择性与回表）概述索引是 OLTP 查询性能的核心。本文从 B-Tree 原理出发，讨论覆盖索引、选择性、回表的实际影响，并给出可验证的优化步骤。关键参数与实践B-Tree：多数关系型数据库（MySQL/InnoDB、PostgreSQL）默认使用 B-Tree 索引，适合范围与精确查询。选择性（Selectivity）：不同值数量/总行数。选择性越高，过滤效果越好，索引更有效。覆盖索引：查询字段完全包含在同一索引中，避免回表读取数据页，提升性能。回表：索引定位后再读取主记录（MySQL InnoDB 使用聚簇主键回表）。复合索引列顺序：遵循“高选择性在前、查询/排序条件优先”。优化示例（MySQL）-- 创建复合覆盖索引 CREATE INDEX idx_user_status_created_at ON orders (user_id, status, created_at); -- 使用 EXPLAIN 验证 EXPLAIN ANALYZE SELECT user_id, status, created_at FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50; 预期现象：`Using index`（覆盖），`Using where`，`range/const` 访问类型，回表次数降低。验证方法`EXPLAIN`/`EXPLAIN ANALYZE` 观察访问类型、行数估算与回表行为。`SHOW INDEX`/`pg_stat_statements` 评估索引命中与查询分布。比较有/无覆盖索引下的延迟、扫描行数与 I/O 指标。注意事项过多索引影响写入与维护成本；使用真实工作负载评估收益。前缀索引可能降低选择性与排序能力；谨慎用于模糊匹配。复合索引的列顺序应与主要查询、排序模式一致。