很多人聊数据库索引时会把下面几个概念混在一起B-tree / BTree主键索引聚簇索引回表Index Only Scan一句话先说结论PostgreSQL 的主键索引虽然是 B-tree但通常仍然需要访问表数据MySQL InnoDB 的主键索引是聚簇索引主键 BTree 叶子节点存的是整行数据所以按主键查询理论上不需要再“回表”。1. 什么是“回表”通俗理解先通过索引找到数据位置再去真正的数据表里把完整记录取出来这一步就可以理解为回表。例如查询SELECT*FROMordersWHEREid1001;如果索引里只有id和数据地址那么数据库找到id 1001后还要再去数据表里取order_no、amount、create_time等其他字段这就是“回表”。2. PostgreSQL 主键索引通常需要访问 heap 表PostgreSQL 中创建主键CREATETABLEorders(idbigintPRIMARYKEY,order_novarchar(64),amountnumeric,create_timetimestamp);PostgreSQL 会自动创建一个唯一 B-tree 索引大致可以理解为orders_pkey: id - TID这里的TID是 PostgreSQL heap 表里的物理位置指针类似(block number, tuple offset)也就是说PostgreSQL 的主键索引叶子节点里并不保存完整行数据而是保存主键值 指向 heap tuple 的位置所以执行SELECT*FROMordersWHEREid1001;通常流程是1. 通过 orders_pkey 找到 id 1001 2. 从索引项中拿到 heap TID 3. 根据 TID 访问 heap 表 4. 取出完整行所以在 PostgreSQL 里主键查询SELECT *通常仍然需要访问 heap 表。3. PostgreSQL 的“聚簇”不等于 InnoDB 的聚簇主键PostgreSQL 也有CLUSTER命令例如CLUSTER ordersUSINGorders_pkey;它的作用是按照某个索引的顺序把 heap 表数据重新物理排列一遍。但要注意它和 MySQL InnoDB 的聚簇主键不是一回事。PostgreSQL 的CLUSTER有几个特点它是一次性的表重写操作。后续新增、更新的数据不会一直自动保持这个物理顺序。索引叶子节点里仍然不是整行数据仍然主要是 key TID。查询需要完整行时仍然要访问 heap。所以可以简单理解为PostgreSQL CLUSTER 把表按某个索引顺序整理一下 InnoDB 聚簇主键 主键索引本身就是数据表这两个不是同一个概念。4. MySQL InnoDB 主键索引主键 BTree 叶子节点存整行MySQL InnoDB 的表是按主键组织的。假设有表CREATETABLEorders(idbigintPRIMARYKEY,order_novarchar(64),amountdecimal(18,2),create_timedatetime)ENGINEInnoDB;InnoDB 的主键索引可以理解为PRIMARY KEY BTree id 1001 - 完整行数据 id 1002 - 完整行数据 id 1003 - 完整行数据也就是说InnoDB 主键索引的叶子节点里直接存整行数据。所以执行SELECT*FROMordersWHEREid1001;理论上的流程是1. 通过主键 BTree 找到 id 1001 2. 叶子节点上已经有完整行 3. 直接返回数据因此InnoDB 按主键查询完整行时理论上不需要再回表。5. InnoDB 二级索引才常说“回表”例如给order_no建索引CREATEINDEXidx_orders_order_noONorders(order_no);InnoDB 的二级索引叶子节点里通常存的是order_no - 主键 id执行SELECT*FROMordersWHEREorder_noSO-001;流程大致是1. 先走 idx_orders_order_no 找到主键 id 2. 再用主键 id 去 PRIMARY KEY BTree 找完整行这一步“再去主键索引找完整行”就是 MySQL InnoDB 里常说的回表。6. PostgreSQL 什么时候可能不访问 heapPostgreSQL 有一种执行方式叫Index Only Scan它看起来像“不回表”但需要满足条件。例如SELECTidFROMordersWHEREid1001;如果只查id而id已经在主键索引里那么 PostgreSQL 有机会只扫索引。但是 PostgreSQL 有 MVCC可见性信息主要在 heap 里。为了确认这条记录对当前事务是否可见PostgreSQL 还依赖 visibility map。只有当相关 heap page 被标记为all-visible时才可能真正避免访问 heap。看执行计划时可以用EXPLAIN(ANALYZE,BUFFERS)SELECTidFROMordersWHEREid1001;如果看到Index Only Scan using orders_pkey on orders Heap Fetches: 0这才说明基本没有访问 heap。如果看到Index Scan using orders_pkey on orders通常就说明它通过索引定位后还访问了 heap 表。7. 对比总结数据库主键索引结构主键叶子节点存什么SELECT * WHERE pk ?是否需要回表PostgreSQLB-tree 索引 heap 表分离主键值 TID通常需要访问 heapPostgreSQLCLUSTER后heap 按索引顺序重排主键值 TID通常仍需要访问 heapMySQL InnoDB聚簇主键 BTree完整行数据理论上不需要回表MySQL InnoDB 二级索引二级索引 BTree二级索引值 主键值查询完整行通常需要回主键索引8. 常见误区误区一主键索引都是聚簇索引不是。MySQL InnoDB 的主键是聚簇索引但 PostgreSQL 的主键只是一个唯一 B-tree 索引。误区二PostgreSQL 有 CLUSTER所以主键查询不需要回表不对。PostgreSQL 的CLUSTER只是把 heap 表按某个索引顺序重新排列。它不会让索引叶子节点保存完整行也不会让表像 InnoDB 那样永久按主键自动组织。误区三PostgreSQL Index Only Scan 一定不访问 heap也不一定。还要看 visibility map。如果执行计划里Heap Fetches不为 0说明仍然访问了 heap。9. 最通俗的一句话可以这样记PostgreSQL 的主键索引像“目录”目录告诉你数据在 heap 表哪一页哪一行MySQL InnoDB 的主键索引像“目录 正文”通过主键找到叶子节点时整行数据已经在那里了。所以PostgreSQL 主键索引通常需要根据 TID 再访问 heap。 MySQL InnoDB 主键索引主键叶子节点就是完整数据理论上不需要回表。