数据库索引优化与查询性能

概述合理的索引设计与维护是查询性能的核心。本文提供可验证的诊断流程与设计原则，覆盖 MySQL 与 PostgreSQL 常见场景。诊断与度量（已验证）MySQL：`EXPLAIN`/`EXPLAIN ANALYZE` 关注 `type`、`key`、`rows`、`filtered` 等字段；`Slow Query Log` 捕获慢 SQL。PostgreSQL：`EXPLAIN (ANALYZE, BUFFERS)` 获取真实执行时间与 I/O；结合 `pg_stat_statements` 聚合热点查询。统计信息：MySQL 使用 `ANALYZE TABLE` 更新统计；PostgreSQL 使用 `ANALYZE` 或 `VACUUM ANALYZE`。索引设计原则选择性优先：高选择性列更适合作为索引键；低选择性列慎用或与高选择性列组合。复合索引的顺序：按照过滤条件的选择性与查询最常用的前缀排序（MySQL 最左前缀原则）。覆盖索引：MySQL：`INDEX` 覆盖查询列可显著减少回表。PostgreSQL：通过可见性映射（Visibility Map）配合 `Index-Only Scan` 在统计信息良好且可见性标记充分时生效。前缀与函数索引：PostgreSQL 支持表达式索引；MySQL 可使用前缀索引但需评估选择性与回表成本。常见优化范式避免在条件列上进行函数/计算，改用可索引的等值或范围条件。为排序与分页设计合适的复合索引（例如 `(created_at, id)`），减少 `filesort` 或磁盘排序。通过物化视图（PostgreSQL）或汇总表降低复杂聚合的在线开销；设定增量刷新策略。保持统计信息新鲜：高变更表设置更高的 `autovacuum` 与 `analyze` 频率（PostgreSQL）。示例（验证通过的指引）-- MySQL：为高选择性过滤列与排序列建立复合索引 CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); -- PostgreSQL：表达式索引提升常用查询 CREATE INDEX idx_users_lower_email ON users (lower(email)); -- PostgreSQL：查看真实执行 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = $1 AND created_at > now() - interval '30 days' ORDER BY created_at DESC LIMIT 20; 维护与监控定期检查未使用索引并清理（注意变更窗口与回归验证）。捕获与优化 N+1 查询；从 ORM 层面启用预加载或批量查询。监控 `bloat`（膨胀）与磁盘占用，必要时重建索引（在线重建优先）。注意事项谨慎添加过多索引：写入开销与维护成本显著增加。变更前后使用 `EXPLAIN ANALYZE` 对比并记录基线，避免主观判断。