#索引优化策略 - MySQL与PostgreSQL索引设计与优化指南

#引言

上周排查某电商订单系统，把高峰期查「7天未发货广州单」的耗时从 4分钟 压到 220ms —— 靠的不是升级 SSD，而是删掉3个重复索引、新增一个精准的复合覆盖索引。

索引是数据库性能的「第一生产力」，但也是一把双刃剑：它能极大加速查询，却会拖慢写入、占用额外的磁盘空间。本文帮你快速掌握 MySQL 和 PostgreSQL 两套主流relational-database的索引核心设计技巧，让索引真正为你的查询模式服务。

#1. 索引基础扫盲（快速回顾）

#1.1 核心类比

数据库索引就像图书馆的书脊分类 + 按书名/作者的检索目录，不用翻遍所有书架就能快速定位到想要的书。

#1.2 四条核心原则

先记牢这四点，后面的所有策略都是它们的延伸：

1. 优先为高选择性列建索引
   选择性越高（不同值越多），索引效果越好。用户邮箱（几乎唯一）优于订单状态（可能只有3~4种值）。
2. 复合索引必须遵循「最左前缀」
   索引键按顺序组织，就像目录先按「省份」、再按「城市」排序。跳过省份直接查城市，索引无法生效。
3. 写多读少，谨慎建索引
   每次写入、更新、删除，所有相关索引的数据都要同步修改，写压力大的表索引过多会变成灾难。
4. 定期维护索引健康
   删除不再使用的索引，重建碎片严重的索引，保持整体效率。

#2. MySQL vs PostgreSQL 核心索引对比

简单记忆：MySQL 的索引生态相对传统但扎实；PostgreSQL 则提供了更丰富、更精准的索引类型，可以应对复杂的半结构化数据和大表场景。

#3. 实用索引设计实战（重点！）

#3.1 高频查询场景

#场景1：复合条件 + 排序 + 覆盖

订单系统的高频查询：

-- 需求：根据用户ID和订单状态查询，按订单时间倒序，只取ID和金额
SELECT id, amount FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY order_date DESC
LIMIT 10;

MySQL 方案
因为没有原生的 INCLUDE 子句，我们需要将需要返回的列也包含在复合索引中，实现覆盖索引。

CREATE INDEX idx_usr_stat_date ON orders(user_id, status, order_date DESC, id, amount);

索引键按等值查询列、范围/排序列、覆盖列的顺序排列。这样的索引可以完全覆盖查询，不必回表。

PostgreSQL 方案
使用原生 INCLUDE 子句，将 id、amount 作为非键列包含在索引中，不参与索引搜索逻辑，但可避免回表。

CREATE INDEX idx_usr_stat_date_pg ON orders(user_id, status, order_date DESC)
INCLUDE (id, amount);

最左前缀验证： 两种方案都能精准利用索引，查询计划中会出现 Using index 或 Index Only Scan。

#场景2：只查询某类特定状态的小范围数据

紧抓「近30天未发货的退货审核单」这类只关心一小撮数据的查询。

MySQL 方案
MySQL 不支持原生部分索引，但可以通过函数索引缩小范围（8.0.13+），或者使用普通索引配合范围查询。

-- 利用函数索引变相缩小范围
CREATE INDEX idx_ret_pending_30d ON orders((DATE(order_date)), status);
-- 查询时必须配合相应条件
SELECT * FROM orders
WHERE status = 'return_pending'
  AND DATE(order_date) > CURDATE() - INTERVAL 30 DAY;

PostgreSQL 方案
原生部分索引只对满足 WHERE 条件的行建索引，节省大量空间，且查询自动匹配。

CREATE INDEX idx_ret_pending_30d_pg ON orders(user_id, order_date DESC)
WHERE status = 'return_pending'
  AND order_date > CURRENT_DATE - INTERVAL '30 days';

适合只有少量数据满足条件的高频查询，索引体积缩小 90% 以上。

#场景3：JSON / 数组查询

MySQL 方案
MySQL 5.7 开始提供 JSON 类型，可通过虚拟列或函数索引优化部分路径查询，但能力较局限。

-- 只能加速对 tags 数组第一个元素的查询
CREATE INDEX idx_tags_mysql ON products((JSON_EXTRACT(tags, '$[0]')));

PostgreSQL 方案
GIN 索引原生支持数组和 JSONB，查询能力强大得多。

CREATE INDEX idx_tags_gin ON products USING gin(tags);
CREATE INDEX idx_profile_gin ON user_profiles USING gin(profile_data);

查询示例：

-- 数组中包含指定元素
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'wireless'];

-- JSONB 内包含特定键值
SELECT * FROM user_profiles
WHERE profile_data->'preferences' @> '{"theme": "dark"}';

如果业务中大量使用 JSON 或数组，PostgreSQL 的优势非常明显。

#4. 索引避坑指南

#4.1 常见索引失效场景

-- ❌ 失效1：LIKE 前导通配符
SELECT * FROM users WHERE nickname LIKE '%张%';
-- ✅ 替代：全文索引（MySQL/PG 均有支持）

-- ❌ 失效2：对索引列使用函数或表达式
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- ✅ 替代：用范围查询或函数索引
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- ❌ 失效3：OR 连接不同索引列
SELECT * FROM users WHERE name = '张三' OR phone = '13800138000';
-- ✅ 替代：拆分为两个独立索引查询，用 UNION ALL 合并且避免重复

#4.2 三大反模式

• 过度索引：为每个列单独建索引，写操作极度膨胀，存储空间飞涨。
• 键顺序错位：将低选择性列（如 status）放在复合索引最前端，导致索引筛选能力弱。
• 从不维护：大表索引碎片超过 30% 仍不重建，扫描效率可能还不如全表扫描。

#5. 快速诊断工具：用 EXPLAIN 看清索引使用

#5.1 MySQL EXPLAIN

-- JSON 格式输出更直观（MySQL 5.6+）
EXPLAIN FORMAT=JSON
SELECT id, amount FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY order_date DESC
LIMIT 10;

关键信息：

• key：实际被使用的索引名
• rows：预估扫描行数，越小越好
• Extra：出现 Using index 代表覆盖索引，无需回表；Using filesort 表示需要额外排序，通常是警告信号

#5.2 PostgreSQL EXPLAIN

-- 使用 ANALYZE、BUFFERS 查看真实执行统计
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, amount FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY order_date DESC
LIMIT 10;

关键信息：

• 扫描类型优选顺序：Index Only Scan > Index Scan > Bitmap Heap Scan > Seq Scan（全表扫描）
• Heap Fetches 越少越好，过多说明回表次数高，可考虑扩展覆盖索引

#总结

1. 按场景选索引，按数据库选特性
   普通查询用 B-Tree 覆盖；JSON/数组/全文优先考虑 PG 的 GIN/GiST；时序大表用 BRIN 压缩索引空间。
2. 复合索引键顺序有讲究
   等值查询列 → 范围/排序列 → 覆盖列（或通过 INCLUDE 单独附加）
3. 定期巡检慢查询，清理无用索引
   用好 EXPLAIN，配合数据库自带的索引统计视图，及时“瘦身”。

#相关教程

• Python接入MySQL与PostgreSQL数据库
• 视图、函数和过程
• SQL详解之DQL