第18期吐槽:都走索引了为什么还要回表访问?原来是索引里缺少了“灵魂”

文中参考文档点击阅读原文打开, 同时推荐2个学习环境: 

1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》

2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、最佳实践等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

第18期吐槽:PG 索引中没有存储tuple版本信息

1、产品的问题点

• PG 索引中没有存储tuple版本信息, 即使仅查询索引包含的字段内容也可能需要回表进行判断.

2、问题点背后涉及的技术原理

• PG 的索引没有TUPLE版本信息, 所以必须回表才能判断索引指向的tuple是否为dead tuple、是否对当前事务可见. 回表访问就意味着离散IO, 而且还占用更多的shared buffer资源.

• 为了解决这个问题, PG引入VM file, 记录每个data block的状态, 当datablock为clean状态时, 则不需要回表, 但是也要访问VM才能判断datablock为clean状态, 所以离散IO次数是没有节省下来的, 节省的是IO覆盖范围, 毕竟VM里面2bit就表示1个data block(默认8KB).

3、这个问题将影响哪些行业以及业务场景

• 对IO敏感的场景, 例如:

• 多对象的时序、时空场景. 对某个对象, 范围扫描大量记录, 需要回表, 产生大量离散IO. 例如: 查询某个车辆的轨迹数据, 将需要访问上万条离散记录. 查询某个用户在某个时间段的行为, 例如用户消费记录、话费、短视频浏览历史记录、视频历史观看记录、音频历史记录等等.

4、会导致什么问题?

• 即使采用 include index , 叶子节点包含需要查询的所有数据, 如果需要回表判断, 离散IO依旧是问题(从索引到table或VM文件都是离散IO).

• 额外对table数据文件的回访, 导致浪费shared buffer.

• 在删除大量数据后 或者 更新大量数据后, 如果索引的垃圾版本未及时进行垃圾回收, 将导致命中这部分索引的Query性能急剧下降.

5、业务上应该如何避免这个坑

• 尽量不要使用更新, 并且使用include index, 使得大部分数据块内的记录都是对所有会话可见, 所以不需要回表查询, 减少IO.

• 尽量不要使用更新, 并且使用多值类型存储多条点的聚合数据, 例如array或json, 使得单一对象的多条记录聚合为1条, 密集存储在少量block里面, 可以降低IO的数量.

• 按对象ID分区, 使用BRIN索引代替BTREE索引, 减少时序类数据的范围搜索IO.

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• 管理复杂度、程序设计的复杂度均有增加

7、数据库未来产品迭代如何修复这个坑

• 内核层解决, 降低回表次数, 除了vm file, 是否可以考虑引入索引支持版本更新? 因为本身写入、删除、更新时就需要更新对应的索引tuple, 索引支持记录多版本信息并没有增加更多的IO操作, 只是需要增加存储空间.

• 或者使用undo这样的存储引擎, 因为是in-place update, 不存在索引多版本的问题.

本期彩蛋-招商中...

文章中的参考文档请点击阅读原文获得. 

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  

近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: