Introduction to PostgreSQL Indexes
4 months ago
- #postgresql
- #database
- #performance
- PostgreSQL索引通过减少磁盘读取来加速数据访问
- 索引用于强制主键和唯一键等约束，但仅对匹配索引列和数据类型的查询有帮助
- 当查询返回少于15-20%的表数据时索引更有效，否则顺序扫描可能更优
- PostgreSQL将表数据存储在无序堆文件中，分为8KB大小的页，行(元组)无序存储
- 索引将键值与堆中的行定位器(ctid)关联，实现快速数据检索
- 在列上创建索引可通过避免全表扫描显著减少查询执行时间
- 索引会带来成本：增加磁盘空间、写操作(INSERT/UPDATE/DELETE)开销和内存使用
- PostgreSQL提供6种默认索引类型(Btree/Hash/BRIN/GIN/GiST/SP-GiST)，更多可通过扩展获得
- Btree是最常用的索引类型，支持主键/唯一键，提供高效的O(log n)搜索
- 多列索引可提升性能，但需要谨慎设计列顺序以最大化效用
- 部分索引通过仅索引满足条件的行子集来减少大小和开销
- 覆盖索引通过包含查询所需全部列实现仅索引扫描(index-only scan)
- 表达式索引允许对转换后的数据(如小写文本)建立索引以优化查询
- Hash索引对等值查询紧凑高效，但不支持排序或多列组合使用
- BRIN索引对具有数据相关性的大型只追加数据集特别节省空间
- GIN索引擅长搜索复合数据(如数组/JSON/文本)
- GiST和SP-GiST是支持自定义索引类型的框架，适用于几何和全文搜索