上一篇【第18篇】EXPLAIN执行计划完全解读——读懂查询的“心电图“
下一篇【第20篇】PostgreSQL中的各种JOIN操作——连接查询的终极指南
标签：PostgreSQL、索引、B-Tree、唯一索引、多列索引、索引失效、索引维护、索引膨胀
摘要：索引是数据库性能优化最核心的手段之一。本文从 B-Tree 索引的底层结构讲起，涵盖唯一索引、多列索引、表达式索引、部分索引的创建和使用，并深入分析索引失效的常见场景和索引维护策略。
一、开篇引言
一个经典的面试问题："为什么加了索引，查询还是慢？"答案可能有很多——索引列上用了函数、条件用了 OR、数据分布不均匀、统计信息过时……但根本原因只有一个：你没有真正理解索引的工作原理。
索引的本质是什么？一本书的目录。通过目录（索引），你可以直接翻到需要的页码（数据行），而不需要从头到尾翻整本书（全表扫描）。但如果目录的编排方式和你查找的方式不匹配（比如你想按拼音找，但目录是按笔画排的），索引就发挥不了作用。
本文将从原理到实践，帮你建立完整的索引知识体系。
二、B-Tree 索引原理
2.1 B-Tree 结构
PostgreSQL 默认的索引类型是 B-Tree（Balanced Tree，平衡树）。它的结构类似一棵倒过来的树：
[根节点] / \ [分支节点] [分支节点] / \ / \ [叶子] [叶子] [叶子] [叶子] | 10 | 25 | 40 | 55 | | 15 | 30 | 42 | 60 | | 20 | 35 | 48 | 70 |特点：
- 所有叶子节点在同一层（树是平衡的）
- 每个叶子节点存储有序的键值和指向表行的指针（ctid）
- 查找时间复杂度 O(log N)，与表大小呈对数关系
2.2 B-Tree 索引查找过程
-- 假设有索引 CREATE INDEX idx_amount ON orders(amount);SELECT*FROMordersWHEREamount=299.99;-- 查找过程：-- 1. 从根节点开始，299.99 落在某个区间-- 2. 进入对应分支节点，继续缩小范围-- 3. 到达叶子节点，通过二分查找定位到具体的键值-- 4. 通过 ctid 指针到表中取出完整数据行2.3 PostgreSQL 页面结构
PostgreSQL 的默认页面大小是 8KB。一个 B-Tree 页面可以存储几百个索引条目，三层 B-Tree 就能索引上百万行数据。
三、创建索引
3.1 基础语法
-- 单列索引CREATEINDEXidx_orders_amountONorders(amount);-- 唯一索引CREATEUNIQUEINDEXidx_orders_order_noONorders(order_no);-- 多列索引（复合索引）CREATEINDEXidx_orders_date_amountONorders(order_date,amount);-- 表达式索引CREATEINDEXidx_orders_lower_statusONorders(LOWER(status));-- 部分索引（只索引满足条件的行）CREATEINDEXidx_orders_pendingONorders(order_date)WHEREstatus='pending';-- 并发创建索引（不锁表！）CREATEINDEXCONCURRENTLY idx_orders_customerONorders(customer_id);3.2 并发创建索引的重要性
-- 普通创建：锁表，阻塞所有写操作（生产环境慎用）CREATEINDEXidx_amountONorders(amount);-- 在大表上可能需要几分钟到几小时-- 并发创建：不锁表，允许正常的读写操作CREATEINDEXCONCURRENTLY idx_amountONorders(amount);-- 耗时更长，但不影响在线业务-- 查看创建进度SELECTphase,blocks_done,blocks_totalFROMpg_stat_progress_create_index;3.3 查看索引信息
-- 查看表上的所有索引\d+orders-- 通过系统表查看SELECTindexname,indexdefFROMpg_indexesWHEREtablename='orders';-- 查看索引大小SELECTindexrelnameASindex_name,pg_size_pretty(pg_relation_size(indexrelid))ASindex_sizeFROMpg_stat_user_indexesWHEREschemaname='public'ORDERBYpg_relation_size(indexrelid)DESC;-- 查看索引使用情况（哪些索引被使用）SELECTschemaname,relnameAStable_name,indexrelnameASindex_name,idx_scanASindex_scans,pg_size_pretty(pg_relation_size(indexrelid))ASsizeFROMpg_stat_user_indexesORDERBYidx_scan;-- idx_scan = 0 的索引可能未被使用，考虑删除四、索引类型详解
4.1 唯一索引
-- 唯一索引保证列值不重复CREATEUNIQUEINDEXuk_users_emailONusers(email);-- 唯一索引允许多个 NULLINSERTINTOusers(email)VALUES(NULL);-- OKINSERTINTOusers(email)VALUES(NULL);-- 也OK，NULL != NULL-- 创建唯一索引时如果已有重复值会报错-- 先处理重复数据，再创建索引SELECTemail,COUNT(*)FROMusersGROUPBYemailHAVINGCOUNT(*)>1;4.2 多列索引（复合索引）
-- 多列索引的列顺序很重要！CREATEINDEXidx_orders_date_statusONorders(order_date,status);-- 最左前缀原则：-- ✅ WHERE order_date = '2024-01-15' -- 走索引-- ✅ WHERE order_date = '2024-01-15' AND status = 'pending' -- 走索引-- ❌ WHERE status = 'pending' -- 不走索引！多列索引的列顺序原则：
- 区分度高的列放前面（如 status 区分度比 date 高）
- 经常单独作为查询条件的列放前面
- 范围查询的列放后面
- 如果不确定，把最常用的查询条件放前面
4.3 表达式索引
-- 常见场景：大小写不敏感查询CREATEINDEXidx_users_lower_emailONusers(LOWER(email));-- 使用索引的查询SELECT*FROMusersWHERELOWER(email)='test@example.com';-- 而不是这样（索引不生效）：-- SELECT * FROM users WHERE email = LOWER('TEST@EXAMPLE.COM');-- 其他表达式索引示例CREATEINDEXidx_orders_monthONorders(DATE_TRUNC('month',order_date));CREATEINDEXidx_orders_lengthONorders(LENGTH(note));4.4 部分索引
-- 只索引活跃订单（大幅减少索引大小）CREATEINDEXidx_active_ordersONorders(customer_id,order_date)WHEREstatusIN('pending','processing');-- 只索引高价值订单CREATEINDEXidx_high_value_ordersONorders(order_date)WHEREamount>10000;-- 只索引最近的数据CREATEINDEXidx_recent_ordersONorders(customer_id)WHEREorder_date>=CURRENT_DATE-INTERVAL'30 days';部分索引的优势：
- 索引更小，维护成本更低
- 查询时索引更紧凑，效率更高
- 特别适合数据分布不均匀的场景
五、索引失效的常见场景
5.1 在索引列上使用函数
-- ❌ 索引失效SELECT*FROMordersWHEREDATE(order_date)='2024-01-15';-- ✅ 改写为范围查询SELECT*FROMordersWHEREorder_date>='2024-01-15'ANDorder_date<'2024-01-16';-- 或创建表达式索引CREATEINDEXidx_orders_date_truncONorders(DATE(order_date));5.2 使用 OR 连接不同列
-- ❌ 可能不走索引（PostgreSQL 优化器会评估）SELECT*FROMordersWHEREcustomer_id=1ORamount>1000;-- ✅ 用 UNION 替代SELECT*FROMordersWHEREcustomer_id=1UNIONALLSELECT*FROMordersWHEREamount>1000;5.3 LIKE 的前缀通配符
-- ✅ 前缀匹配，走索引SELECT*FROMusersWHEREnameLIKE'张%';-- ❌ 前缀通配符，不走索引SELECT*FROMusersWHEREnameLIKE'%张';SELECT*FROMusersWHEREnameLIKE'%张%';5.4 隐式类型转换
-- 假设 phone 列是 VARCHAR 类型-- ❌ 隐式转换，索引可能失效SELECT*FROMusersWHEREphone=13812345678;-- PostgreSQL 需要把 integer 转为 varchar 来比较-- ✅ 显式类型匹配SELECT*FROMusersWHEREphone='13812345678';5.5 不等于操作
-- ❌ != 和 <> 通常不走索引SELECT*FROMordersWHEREstatus!='cancelled';-- ✅ 用 IN 替代（某些场景下）SELECT*FROMordersWHEREstatusIN('pending','processing','completed');5.6 索引失效速查表
| 操作 | 是否走索引 | 替代方案 |
|---|---|---|
col = value | ✅ | - |
col > value | ✅ | - |
col IN (...) | ✅ | - |
col LIKE 'prefix%' | ✅ | - |
col IS NULL | ✅ | - |
col != value | ❌ | 用 IN 或 UNION |
col LIKE '%xxx%' | ❌ | 使用全文检索 |
func(col) = value | ❌ | 表达式索引 |
col + 1 = value | ❌ | 改写为col = value - 1 |
OR不同列条件 | ❌ | UNION |
| 类型不匹配 | ❌ | 确保类型一致 |
六、索引维护
6.1 索引膨胀
PostgreSQL 的 MVCC 机制导致索引也可能产生"死条目"，造成索引膨胀：
-- 检测索引膨胀SELECTschemaname||'.'||relnameAStable_name,indexrelnameASindex_name,pg_size_pretty(pg_relation_size(indexrelid))ASindex_size,idx_scanASscans,idx_tup_readAStuples_read,idx_tup_fetchAStuples_fetchedFROMpg_stat_user_indexesORDERBYpg_relation_size(indexrelid)DESC;-- 重建索引（在线重建）REINDEXINDEXCONCURRENTLY idx_orders_amount;-- 重建表上的所有索引REINDEXTABLECONCURRENTLY orders;6.2 删除无用索引
-- 查找从未被使用的索引SELECTschemaname||'.'||relnameAStable_name,indexrelnameASindex_name,pg_size_pretty(pg_relation_size(indexrelid))ASsize,idx_scanASscansFROMpg_stat_user_indexesWHEREidx_scan=0ORDERBYpg_relation_size(indexrelid)DESC;-- 删除无用索引DROPINDEXIFEXISTSunused_index_name;七、总结与下篇预告
本文全面讲解了 PostgreSQL 索引的核心知识：
- B-Tree 索引是最常用的索引类型，查找时间复杂度 O(log N)
- 唯一索引保证不重复，注意 NULL 值的特殊处理
- 多列索引遵循最左前缀原则，列顺序选择至关重要
- 表达式索引解决函数调用导致索引失效的问题
- 部分索引通过条件过滤减少索引大小，提升效率
- 索引失效的常见原因：函数调用、前缀通配符、类型不匹配、OR 条件
- 定期检查索引膨胀和使用率，及时清理无用索引
下篇预告：第 20 篇将介绍JOIN 操作的深度解析——嵌套循环、哈希连接、合并连接的原理和适用场景。通过分析不同 JOIN 方式的执行计划，帮助你在实际查询中选择最优的连接策略。
上一篇【第18篇】EXPLAIN执行计划完全解读——读懂查询的“心电图“
下一篇【第20篇】PostgreSQL中的各种JOIN操作——连接查询的终极指南
