1. 从“书”到“目录”为什么我们需要B树索引如果你用过任何一本大部头的纸质书比如一本上千页的技术手册你肯定有过这样的体验想找一个特定的知识点比如“事务隔离级别”如果一页一页翻那简直是噩梦。但如果你先翻到书最后的“索引”部分找到“事务”这个词条后面跟着一串页码你就能直接翻到对应的几页效率提升百倍。数据库里的表就是那本“大部头书”里面存储着海量的数据行。当我们需要根据某个条件比如WHERE user_id 10086快速找到一行或几行数据时如果让数据库引擎去“一页一页翻”也就是全表扫描在数据量大的情况下性能会急剧下降响应时间可能从毫秒级变成分钟甚至小时级。这时候我们就需要为这本“书”建立一个高效的“目录”这个目录就是索引。在PostgreSQL中最常用、最核心的索引类型就是基于B树BTree实现的。你可能会问为什么是B树不是哈希表查找更快或者二叉搜索树结构更简单这就涉及到数据库索引需要解决的几个核心矛盾海量数据与有限内存的矛盾表的数据量可能远超服务器内存容量索引结构必须能高效地利用磁盘I/O减少磁盘寻道次数。等值查询与范围查询的矛盾我们既要能快速找到user_id 10086这一行等值查询也要能高效地找出user_id BETWEEN 10000 AND 20000的所有行范围查询。查询效率与维护成本的矛盾索引需要加速查询但数据的插入、更新、删除也会引起索引的变动。这个维护过程不能太耗时否则会影响写入性能。B树正是在这些约束下的一个绝佳平衡点。它是一种多路平衡搜索树能保持很矮的“身高”层数少这样从根节点找到叶子节点所需的磁盘I/O次数就很少。同时它的所有数据都存储在叶子节点并且叶子节点之间通过指针相连这使得它特别擅长范围查询。而哈希索引虽然等值查询是O(1)但对范围查询无能为力普通的二叉搜索树在数据有序插入时会退化成链表性能灾难。所以当你为一张表创建了一个默认的索引CREATE INDEX ON users (user_id)PostgreSQL为你构建的就是一棵B树。理解这棵树的基本结构是理解数据库查询性能、执行计划乃至进行深度优化的基石。接下来我们就一层层剥开这棵“树”的细节。2. B树的核心设计哲学一切为了磁盘要理解B树的结构首先要忘掉内存中那些花哨的数据结构。B树是为磁盘这类块设备Block Device量身定做的。磁盘读写的基本单位是“块”在PostgreSQL中称为“页”Page默认8KB。一次磁盘I/O即使只读1个字节的成本远高于内存中成千上万次计算。因此B树设计的首要目标就是尽量减少查找过程中需要的磁盘I/O次数。2.1 多路平衡把树压扁一棵二叉树每个节点最多有两个孩子。在十亿条数据中查找最坏情况可能需要比较30次2^30 ≈ 10亿。如果每次比较都引发一次磁盘I/O那就是30次I/O太慢了。B树是一个“多路”树意味着一个节点可以拥有很多个孩子通常是几百个。这样树的“扇出”Fan-out就非常大层数就能压得非常低。一个典型的B树即使索引着数十亿行数据高度也往往只有3到4层。从根节点到叶子节点最多只需要3-4次磁盘I/O性能提升是指数级的。为什么能做到一个节点有很多孩子因为一个磁盘页8KB可以存放很多个“索引条目”。每个条目包含一个“键”Key即你索引的列的值和一个指向子节点或实际数据的指针。把尽可能多的条目塞进一个页里这个节点就能指向更多的子节点树就更“矮胖”。2.2 所有数据都在叶子节点范围查询的利器这是B树与它的前身B树的一个关键区别。在B树中非叶子节点也会存储部分数据记录或指向记录的指针。而在B树中非叶子节点内部节点只存储“键”和指向下一层子节点的指针。它们的作用纯粹是“路由”像一本书的章节目录告诉你目标内容在哪一章节。叶子节点存储所有的“键”以及指向表中实际数据行在PostgreSQL中是行的TIDTuple ID由数据块号和行偏移量组成的指针。此外所有叶子节点通过双向链表连接起来。这个设计带来了两大好处更快的范围扫描因为叶子节点是链表连接的一旦找到了范围查询的起点就可以沿着链表顺序读取下一个叶子节点直到终点。这几乎完全是顺序I/O速度极快。在B树中做范围查询可能需要在树的不同层之间来回跳跃效率低下。更稳定的查询性能任何一次等值或范围查询都必须走到叶子节点。因此查询路径长度树的高度是固定的查询性能稳定。B树中如果数据在较高的节点就被找到查询会提前结束但这反而导致了性能的不确定性。2.3 键的有序性与节点分裂/合并B树的所有节点无论是内部节点还是叶子节点中的“键”都是严格有序排列的。这是它能进行二分查找的基础。当你插入一条新数据时对应的新键值也要插入到B树中。它会根据大小被路由到某个叶子节点。如果这个叶子节点已经满了即一个8KB的页装不下更多条目了这个节点就会发生分裂Split大约一半的条目会移动到新申请的一个空页中形成一个新节点。同时需要在父节点中插入一个新的“键”来指向这个新节点。如果父节点也因此满了分裂会向上递归直到根节点。如果根节点分裂树的高度就会增加一层。反之当删除数据导致某个节点过于“空”低于某个填充因子阈值时它可能会和兄弟节点合并Merge或者从兄弟节点那里“借”一些条目过来再平衡以避免空间浪费和树的不平衡。这些分裂与合并操作保证了B树始终是平衡的——从根节点到任何一个叶子节点的路径长度都相同。维护平衡虽然带来了一些写入时的开销但换来了查询时稳定且高效的O(log n)复杂度。3. 深入PostgreSQL的B树索引页解剖一个微观世界现在我们把视角聚焦到PostgreSQL中一个具体的B树索引页8KB里看看里面到底装了些什么。理解这个对于后续分析索引膨胀、查询计划至关重要。一个索引页在物理上被划分为几个部分页头Page Header存储元信息如页面的LSN日志序列号用于崩溃恢复、指向同一层级左右兄弟页面的指针实现叶子节点双向链表的关键、页面内的特殊空间起始位置等。行指针数组Line Pointer Array这是一个从页面尾部开始向前增长的数组。每个数组元素是一个“行指针”ItemId它本身很小4字节存储着一条“索引元组”在本页面内的偏移量Offset和长度Length。你可以把它理解为页内目录。通过这个数组可以快速定位到页内的任何一条索引记录而无需遍历整个页。索引元组Index Tuples这是页面的主体部分存储着实际的索引条目。它们从页面头部页头之后开始向后顺序存放。每个索引元组至少包含索引键值Key Value就是你创建索引的列或列组合的值。对于多列索引这些值会按定义顺序拼接在一起。TIDTuple ID指向堆表Heap Table中实际数据行的指针由块号Block Number和行偏移量Offset Number组成。这就是索引最终要找到的东西。一些标志位例如是否为空、是否有变长属性等。一个关键机制HOTHeap-Only Tuple更新当更新UPDATE一行数据时PostgreSQL的MVCC机制会在堆表中插入一个新版本的行。如果这个更新没有修改任何被索引的列并且旧版本数据所在的数据页有足够的空间那么PostgreSQL可能会采用HOT更新。对于HOT更新索引条目中的TID不需要改变它仍然指向旧的行版本旧TID。通过旧行版本上的指针可以找到新的行版本。这避免了更新所有相关索引的成本对于频繁更新非索引列的场景是巨大的性能优化。理解这一点你就明白为什么“避免对频繁更新的列建索引”或者“尽可能使用窄索引”是重要的建议了。3.1 索引扫描如何工作假设我们执行SELECT * FROM users WHERE user_id 10086并且user_id上有B树索引。从根到叶优化器选择使用索引扫描。执行器从索引的根页面开始根页的位置是固定的存储在系统表中。页内二分查找在根页面内利用行指针数组和有序的索引元组进行快速的二分查找找到最后一个小于或等于10086的键值并获取其指向的子页面的指针。逐层下降沿着指针进入下一层内部节点重复页内二分查找的过程继续路由直到到达叶子节点层。叶子节点定位在叶子节点页面中通过二分查找定位到键值等于10086或第一个大于10086的索引元组。获取TID访问堆表从该索引元组中取出TID比如(block123, offset5)。然后执行器需要根据这个TID去访问堆表users表的第123个数据块读取该块内偏移量为5的行数据。这一步称为回表Index Scan Fetch。如果是范围查询比如WHERE user_id BETWEEN 10000 AND 20000。那么在找到第一个大于等于10000的叶子节点条目后不是回表一次就结束而是沿着叶子节点的双向链表向后顺序扫描依次获取每一个符合条件的键值对应的TID并回表直到遇到键值大于20000的条目为止。这个过程非常高效因为叶子节点的顺序扫描几乎是连续的磁盘I/O。4. 从B树到PostgreSQL的B-Tree实现上的细微差别你可能注意到PostgreSQL的官方文档和源码中将其默认索引类型称为“B-Tree”而不是“BTree”。这其实是一个历史命名习惯。实际上PostgreSQL实现的索引结构更符合B树的定义所有数据在叶子节点叶子节点链表连接。社区通常也认同这一点称之为B树。但PostgreSQL的B-Tree实现有一些自己的特点和优化唯一索引与重复键处理创建唯一索引CREATE UNIQUE INDEX时B树会严格拒绝重复键的插入。对于非唯一索引PostgreSQL实际上会在索引键后面自动追加表的TID作为排序的一部分。因为TID是唯一的这就保证了索引中每一个条目在逻辑上都是唯一的简化了树的操作逻辑。所以当你查询user_id 10086时即使表中有多行user_id都为10086它们也会以不同的条目区别在于TID存在于索引的叶子节点上并且彼此相邻。索引的“真空清理”VACUUM由于MVCC被删除或更新后过期了的行版本在堆表中可能仍然存在直到被VACUUM清理。对应的索引中指向这些过期行版本的条目就成了“死元组”。普通的VACUUM会标记这些索引条目为可重用空间但不会立即归还给操作系统。VACUUM FULL或REINDEX可以重建索引彻底回收空间解决索引膨胀问题。监控pg_stat_all_indexes视图中的idx_scan和idx_tup_fetch等指标结合pg_stat_user_tables的n_dead_tup可以判断索引是否有清理价值。部分索引与表达式索引PostgreSQL的B-Tree索引支持非常灵活的定义。你可以创建部分索引WHERE condition只对满足条件的行建立索引大大减小索引体积。你也可以创建表达式索引CREATE INDEX ON tab ((lower(name)))索引的键是表达式计算后的结果这对于优化特定模式的查询如大小写不敏感搜索极其有用。在内部它们依然是一棵B树只不过键值的计算和比较规则根据索引定义有所不同。4.1 一个实战思考题索引为什么失效理解了结构就能解释很多“索引失效”的现象。比如最经典的“前导通配符LIKE查询”-- 假设在 name 列上有B树索引 SELECT * FROM users WHERE name LIKE %小明%; -- 索引很可能失效 SELECT * FROM users WHERE name LIKE 小明%; -- 索引有效原因在于B树叶子节点中键值‘张三’ ‘李四’ ‘王五’…是按字典序排序的。对于‘小明%’优化器知道可以快速定位到以‘小明’开头的第一个叶子节点位置然后沿着链表向后扫描即可所以索引有效。而对于‘%小明%’由于前缀不确定无法利用键值的有序性进行快速定位只能退回到全表扫描逐行用LIKE匹配。这时更合适的工具可能是pg_trgm模块提供的GIN索引。另一个例子是对索引列进行函数操作-- 假设在 create_time (timestamp类型) 列上有索引 SELECT * FROM logs WHERE DATE(create_time) 2023-10-01; -- 索引失效 SELECT * FROM logs WHERE create_time 2023-10-01 AND create_time 2023-10-02; -- 索引有效DATE(create_time)是一个函数索引中存储的是原始的timestamp值而不是日期部分。查询条件无法直接与索引键比较因此失效。第二种写法等价于第一种但直接使用了列本身进行范围比较完美契合了B树索引的能力。5. 监控与维护让B树索引保持健康创建了索引并非一劳永逸。你需要像照顾盆栽一样偶尔检查一下你的B树是否“健康”。查看索引大小与膨胀-- 使用 pg_stat_user_indexes 和 pg_stat_user_tables 结合查询 SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, idx_scan as index_scans FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE NOT indisunique -- 非唯一索引 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;这个查询帮你找出最大的那些索引。结合idx_scan索引被扫描的次数你可以判断一个很少被使用的大索引是否值得保留。使用pgstattuple扩展分析索引细节CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstatindex(your_index_name);这个函数会返回非常详细的信息包括索引的层级数、叶子页数、内部页数、空页数、平均叶子页填充率等。填充率过低是索引膨胀的一个明显标志意味着很多空间被浪费了。如果发现索引体积远大于实际数据体积且填充率很低就该考虑REINDEX了。重建索引REINDEXREINDEX INDEX your_index_name;或REINDEX TABLE your_table_name;重建索引会创建一个全新的、紧凑的B树消除碎片和死空间。这是一个阻塞操作在重建期间会锁表在生产环境需要谨慎通常在维护窗口进行。PostgreSQL 12 提供了CONCURRENTLY选项REINDEX INDEX CONCURRENTLY可以在不阻塞读写的情况下重建索引但耗时更长且需要额外空间。定期执行 ANALYZEANALYZE your_table_name;这个命令会更新表的统计信息比如每个索引的不同值数量、数据分布直方图等。优化器依靠这些统计信息来决定是否使用索引、使用哪个索引。如果统计信息过时优化器可能会做出错误的判断导致该走索引的查询走了全表扫描。理解B树索引的基本结构是进行有效的数据库性能调优的第一步。它让你不再把索引当作一个黑盒而是能清晰地预判一个查询能否利用索引、如何利用索引以及在索引效率不佳时应该从哪个方向去分析和解决问题。当你下次看到执行计划中的Index Scan或Index Only Scan时你脑海中浮现的应该就是那棵从根到叶、叶子相连的、高效运转的B树。 PostgreSQL B+树索引原理：从磁盘优化到查询加速的深度解析
1. 从“书”到“目录”为什么我们需要B树索引如果你用过任何一本大部头的纸质书比如一本上千页的技术手册你肯定有过这样的体验想找一个特定的知识点比如“事务隔离级别”如果一页一页翻那简直是噩梦。但如果你先翻到书最后的“索引”部分找到“事务”这个词条后面跟着一串页码你就能直接翻到对应的几页效率提升百倍。数据库里的表就是那本“大部头书”里面存储着海量的数据行。当我们需要根据某个条件比如WHERE user_id 10086快速找到一行或几行数据时如果让数据库引擎去“一页一页翻”也就是全表扫描在数据量大的情况下性能会急剧下降响应时间可能从毫秒级变成分钟甚至小时级。这时候我们就需要为这本“书”建立一个高效的“目录”这个目录就是索引。在PostgreSQL中最常用、最核心的索引类型就是基于B树BTree实现的。你可能会问为什么是B树不是哈希表查找更快或者二叉搜索树结构更简单这就涉及到数据库索引需要解决的几个核心矛盾海量数据与有限内存的矛盾表的数据量可能远超服务器内存容量索引结构必须能高效地利用磁盘I/O减少磁盘寻道次数。等值查询与范围查询的矛盾我们既要能快速找到user_id 10086这一行等值查询也要能高效地找出user_id BETWEEN 10000 AND 20000的所有行范围查询。查询效率与维护成本的矛盾索引需要加速查询但数据的插入、更新、删除也会引起索引的变动。这个维护过程不能太耗时否则会影响写入性能。B树正是在这些约束下的一个绝佳平衡点。它是一种多路平衡搜索树能保持很矮的“身高”层数少这样从根节点找到叶子节点所需的磁盘I/O次数就很少。同时它的所有数据都存储在叶子节点并且叶子节点之间通过指针相连这使得它特别擅长范围查询。而哈希索引虽然等值查询是O(1)但对范围查询无能为力普通的二叉搜索树在数据有序插入时会退化成链表性能灾难。所以当你为一张表创建了一个默认的索引CREATE INDEX ON users (user_id)PostgreSQL为你构建的就是一棵B树。理解这棵树的基本结构是理解数据库查询性能、执行计划乃至进行深度优化的基石。接下来我们就一层层剥开这棵“树”的细节。2. B树的核心设计哲学一切为了磁盘要理解B树的结构首先要忘掉内存中那些花哨的数据结构。B树是为磁盘这类块设备Block Device量身定做的。磁盘读写的基本单位是“块”在PostgreSQL中称为“页”Page默认8KB。一次磁盘I/O即使只读1个字节的成本远高于内存中成千上万次计算。因此B树设计的首要目标就是尽量减少查找过程中需要的磁盘I/O次数。2.1 多路平衡把树压扁一棵二叉树每个节点最多有两个孩子。在十亿条数据中查找最坏情况可能需要比较30次2^30 ≈ 10亿。如果每次比较都引发一次磁盘I/O那就是30次I/O太慢了。B树是一个“多路”树意味着一个节点可以拥有很多个孩子通常是几百个。这样树的“扇出”Fan-out就非常大层数就能压得非常低。一个典型的B树即使索引着数十亿行数据高度也往往只有3到4层。从根节点到叶子节点最多只需要3-4次磁盘I/O性能提升是指数级的。为什么能做到一个节点有很多孩子因为一个磁盘页8KB可以存放很多个“索引条目”。每个条目包含一个“键”Key即你索引的列的值和一个指向子节点或实际数据的指针。把尽可能多的条目塞进一个页里这个节点就能指向更多的子节点树就更“矮胖”。2.2 所有数据都在叶子节点范围查询的利器这是B树与它的前身B树的一个关键区别。在B树中非叶子节点也会存储部分数据记录或指向记录的指针。而在B树中非叶子节点内部节点只存储“键”和指向下一层子节点的指针。它们的作用纯粹是“路由”像一本书的章节目录告诉你目标内容在哪一章节。叶子节点存储所有的“键”以及指向表中实际数据行在PostgreSQL中是行的TIDTuple ID由数据块号和行偏移量组成的指针。此外所有叶子节点通过双向链表连接起来。这个设计带来了两大好处更快的范围扫描因为叶子节点是链表连接的一旦找到了范围查询的起点就可以沿着链表顺序读取下一个叶子节点直到终点。这几乎完全是顺序I/O速度极快。在B树中做范围查询可能需要在树的不同层之间来回跳跃效率低下。更稳定的查询性能任何一次等值或范围查询都必须走到叶子节点。因此查询路径长度树的高度是固定的查询性能稳定。B树中如果数据在较高的节点就被找到查询会提前结束但这反而导致了性能的不确定性。2.3 键的有序性与节点分裂/合并B树的所有节点无论是内部节点还是叶子节点中的“键”都是严格有序排列的。这是它能进行二分查找的基础。当你插入一条新数据时对应的新键值也要插入到B树中。它会根据大小被路由到某个叶子节点。如果这个叶子节点已经满了即一个8KB的页装不下更多条目了这个节点就会发生分裂Split大约一半的条目会移动到新申请的一个空页中形成一个新节点。同时需要在父节点中插入一个新的“键”来指向这个新节点。如果父节点也因此满了分裂会向上递归直到根节点。如果根节点分裂树的高度就会增加一层。反之当删除数据导致某个节点过于“空”低于某个填充因子阈值时它可能会和兄弟节点合并Merge或者从兄弟节点那里“借”一些条目过来再平衡以避免空间浪费和树的不平衡。这些分裂与合并操作保证了B树始终是平衡的——从根节点到任何一个叶子节点的路径长度都相同。维护平衡虽然带来了一些写入时的开销但换来了查询时稳定且高效的O(log n)复杂度。3. 深入PostgreSQL的B树索引页解剖一个微观世界现在我们把视角聚焦到PostgreSQL中一个具体的B树索引页8KB里看看里面到底装了些什么。理解这个对于后续分析索引膨胀、查询计划至关重要。一个索引页在物理上被划分为几个部分页头Page Header存储元信息如页面的LSN日志序列号用于崩溃恢复、指向同一层级左右兄弟页面的指针实现叶子节点双向链表的关键、页面内的特殊空间起始位置等。行指针数组Line Pointer Array这是一个从页面尾部开始向前增长的数组。每个数组元素是一个“行指针”ItemId它本身很小4字节存储着一条“索引元组”在本页面内的偏移量Offset和长度Length。你可以把它理解为页内目录。通过这个数组可以快速定位到页内的任何一条索引记录而无需遍历整个页。索引元组Index Tuples这是页面的主体部分存储着实际的索引条目。它们从页面头部页头之后开始向后顺序存放。每个索引元组至少包含索引键值Key Value就是你创建索引的列或列组合的值。对于多列索引这些值会按定义顺序拼接在一起。TIDTuple ID指向堆表Heap Table中实际数据行的指针由块号Block Number和行偏移量Offset Number组成。这就是索引最终要找到的东西。一些标志位例如是否为空、是否有变长属性等。一个关键机制HOTHeap-Only Tuple更新当更新UPDATE一行数据时PostgreSQL的MVCC机制会在堆表中插入一个新版本的行。如果这个更新没有修改任何被索引的列并且旧版本数据所在的数据页有足够的空间那么PostgreSQL可能会采用HOT更新。对于HOT更新索引条目中的TID不需要改变它仍然指向旧的行版本旧TID。通过旧行版本上的指针可以找到新的行版本。这避免了更新所有相关索引的成本对于频繁更新非索引列的场景是巨大的性能优化。理解这一点你就明白为什么“避免对频繁更新的列建索引”或者“尽可能使用窄索引”是重要的建议了。3.1 索引扫描如何工作假设我们执行SELECT * FROM users WHERE user_id 10086并且user_id上有B树索引。从根到叶优化器选择使用索引扫描。执行器从索引的根页面开始根页的位置是固定的存储在系统表中。页内二分查找在根页面内利用行指针数组和有序的索引元组进行快速的二分查找找到最后一个小于或等于10086的键值并获取其指向的子页面的指针。逐层下降沿着指针进入下一层内部节点重复页内二分查找的过程继续路由直到到达叶子节点层。叶子节点定位在叶子节点页面中通过二分查找定位到键值等于10086或第一个大于10086的索引元组。获取TID访问堆表从该索引元组中取出TID比如(block123, offset5)。然后执行器需要根据这个TID去访问堆表users表的第123个数据块读取该块内偏移量为5的行数据。这一步称为回表Index Scan Fetch。如果是范围查询比如WHERE user_id BETWEEN 10000 AND 20000。那么在找到第一个大于等于10000的叶子节点条目后不是回表一次就结束而是沿着叶子节点的双向链表向后顺序扫描依次获取每一个符合条件的键值对应的TID并回表直到遇到键值大于20000的条目为止。这个过程非常高效因为叶子节点的顺序扫描几乎是连续的磁盘I/O。4. 从B树到PostgreSQL的B-Tree实现上的细微差别你可能注意到PostgreSQL的官方文档和源码中将其默认索引类型称为“B-Tree”而不是“BTree”。这其实是一个历史命名习惯。实际上PostgreSQL实现的索引结构更符合B树的定义所有数据在叶子节点叶子节点链表连接。社区通常也认同这一点称之为B树。但PostgreSQL的B-Tree实现有一些自己的特点和优化唯一索引与重复键处理创建唯一索引CREATE UNIQUE INDEX时B树会严格拒绝重复键的插入。对于非唯一索引PostgreSQL实际上会在索引键后面自动追加表的TID作为排序的一部分。因为TID是唯一的这就保证了索引中每一个条目在逻辑上都是唯一的简化了树的操作逻辑。所以当你查询user_id 10086时即使表中有多行user_id都为10086它们也会以不同的条目区别在于TID存在于索引的叶子节点上并且彼此相邻。索引的“真空清理”VACUUM由于MVCC被删除或更新后过期了的行版本在堆表中可能仍然存在直到被VACUUM清理。对应的索引中指向这些过期行版本的条目就成了“死元组”。普通的VACUUM会标记这些索引条目为可重用空间但不会立即归还给操作系统。VACUUM FULL或REINDEX可以重建索引彻底回收空间解决索引膨胀问题。监控pg_stat_all_indexes视图中的idx_scan和idx_tup_fetch等指标结合pg_stat_user_tables的n_dead_tup可以判断索引是否有清理价值。部分索引与表达式索引PostgreSQL的B-Tree索引支持非常灵活的定义。你可以创建部分索引WHERE condition只对满足条件的行建立索引大大减小索引体积。你也可以创建表达式索引CREATE INDEX ON tab ((lower(name)))索引的键是表达式计算后的结果这对于优化特定模式的查询如大小写不敏感搜索极其有用。在内部它们依然是一棵B树只不过键值的计算和比较规则根据索引定义有所不同。4.1 一个实战思考题索引为什么失效理解了结构就能解释很多“索引失效”的现象。比如最经典的“前导通配符LIKE查询”-- 假设在 name 列上有B树索引 SELECT * FROM users WHERE name LIKE %小明%; -- 索引很可能失效 SELECT * FROM users WHERE name LIKE 小明%; -- 索引有效原因在于B树叶子节点中键值‘张三’ ‘李四’ ‘王五’…是按字典序排序的。对于‘小明%’优化器知道可以快速定位到以‘小明’开头的第一个叶子节点位置然后沿着链表向后扫描即可所以索引有效。而对于‘%小明%’由于前缀不确定无法利用键值的有序性进行快速定位只能退回到全表扫描逐行用LIKE匹配。这时更合适的工具可能是pg_trgm模块提供的GIN索引。另一个例子是对索引列进行函数操作-- 假设在 create_time (timestamp类型) 列上有索引 SELECT * FROM logs WHERE DATE(create_time) 2023-10-01; -- 索引失效 SELECT * FROM logs WHERE create_time 2023-10-01 AND create_time 2023-10-02; -- 索引有效DATE(create_time)是一个函数索引中存储的是原始的timestamp值而不是日期部分。查询条件无法直接与索引键比较因此失效。第二种写法等价于第一种但直接使用了列本身进行范围比较完美契合了B树索引的能力。5. 监控与维护让B树索引保持健康创建了索引并非一劳永逸。你需要像照顾盆栽一样偶尔检查一下你的B树是否“健康”。查看索引大小与膨胀-- 使用 pg_stat_user_indexes 和 pg_stat_user_tables 结合查询 SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, idx_scan as index_scans FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE NOT indisunique -- 非唯一索引 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;这个查询帮你找出最大的那些索引。结合idx_scan索引被扫描的次数你可以判断一个很少被使用的大索引是否值得保留。使用pgstattuple扩展分析索引细节CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstatindex(your_index_name);这个函数会返回非常详细的信息包括索引的层级数、叶子页数、内部页数、空页数、平均叶子页填充率等。填充率过低是索引膨胀的一个明显标志意味着很多空间被浪费了。如果发现索引体积远大于实际数据体积且填充率很低就该考虑REINDEX了。重建索引REINDEXREINDEX INDEX your_index_name;或REINDEX TABLE your_table_name;重建索引会创建一个全新的、紧凑的B树消除碎片和死空间。这是一个阻塞操作在重建期间会锁表在生产环境需要谨慎通常在维护窗口进行。PostgreSQL 12 提供了CONCURRENTLY选项REINDEX INDEX CONCURRENTLY可以在不阻塞读写的情况下重建索引但耗时更长且需要额外空间。定期执行 ANALYZEANALYZE your_table_name;这个命令会更新表的统计信息比如每个索引的不同值数量、数据分布直方图等。优化器依靠这些统计信息来决定是否使用索引、使用哪个索引。如果统计信息过时优化器可能会做出错误的判断导致该走索引的查询走了全表扫描。理解B树索引的基本结构是进行有效的数据库性能调优的第一步。它让你不再把索引当作一个黑盒而是能清晰地预判一个查询能否利用索引、如何利用索引以及在索引效率不佳时应该从哪个方向去分析和解决问题。当你下次看到执行计划中的Index Scan或Index Only Scan时你脑海中浮现的应该就是那棵从根到叶、叶子相连的、高效运转的B树。