Posts

多列索引在PostgreSQL中用于优化包含多列条件的查询，支持B-tree、GiST、GIN和BRIN四种索引类型。B-tree索引遵循左前缀原则，适合等值或等值+范围查询；GiST索引首列选择性影响效率，适合空间数据；GIN索引无左前缀限制，适合多值类型查询；BRIN索引适合大表范围查询。覆盖索引通过INCLUDE子句包含查询所需列，避免回表，提升查询效率。最佳实践包括优先使用INCLUDE、避免冗余列和注意可见性映射。

部分索引是建立在表子集上的索引，通过条件表达式定义，仅包含满足条件的行。其优势在于体积小、维护成本低、查询匹配精准。典型应用包括排除常见值、优化高频查询、实现部分唯一约束及修正错误查询计划。表达式索引则基于函数或标量表达式的结果，预存常用计算逻辑，加快查询速度，适用于查询频率远高于更新频率的场景。两者均能显著提升数据库性能，但需注意避免滥用部分索引替代分区表。

B-tree索引是PostgreSQL默认的索引类型，通过分层结构（根节点、分支节点、叶子节点）快速定位数据，时间复杂度为O(log n)。它支持比较操作符（如=、>）、范围查询（如BETWEEN）、空值判断（如IS NULL）以及锚定开头的模式匹配（如LIKE 'foo%'）。适用场景包括加速“等于”查询（如用户登录）、范围查询（如订单统计）和空值查询（如查找未激活用户）。不适用于结尾模糊匹配（如LIKE '%phone'）、非排序类操作（如!=）和低基数列（如性别）。

pg_stat_statements是PostgreSQL的核心性能监控模块，用于跟踪SQL语句的计划与执行统计信息，帮助定位性能瓶颈。通过修改配置文件并重启数据库，可以启用该模块。核心配置参数包括跟踪SQL的最大数量、跟踪范围等。pg_stat_statements视图提供SQL的详细统计信息，如执行次数、总执行时间、缓存命中率等。pg_stat_monitor是增强版模块，支持按时间窗口统计和响应时间直方图，适合持续监控。结合这两个模块，可以持续优化数据库性能，通过定位瓶颈SQL、分析执行计划、优化索引等步骤提升效率。

MVCC（多版本并发控制）是PostgreSQL实现高并发的核心机制，通过为每个事务提供数据快照，解决读-写冲突，实现无锁并发。PostgreSQL的锁机制包括表级锁、行级锁等，行级锁粒度小，并发高。锁等待和死锁是常见问题，死锁由循环等待引起。优化实践包括减少事务长度、合理选择锁级别、使用乐观锁、统一资源顺序、利用NOWAIT和SKIP LOCKED避免等待，以及监控锁状态。通过优化事务处理顺序和缩短锁持有时间，可以有效避免锁等待和死锁。

PostgreSQL性能调优涉及内存、IO和并发参数的合理配置。内存调优中，shared_buffers建议设置为系统内存的25%，work_mem和maintenance_work_mem分别用于查询和维护操作的内存上限。IO调优通过背景写入器和异步IO参数减少磁盘访问延迟。并发调优则通过max_worker_processes和max_parallel_workers_per_gather提升并行处理能力。合理配置这些参数能显著提升数据库性能，减少磁盘IO和内存竞争。