面试题：PostgreSQL执行计划与索引在高并发场景下的调优

查看执行计划：使用 EXPLAIN ANALYZE 语句查看当前复杂查询的执行计划，重点关注扫描类型（顺序扫描、索引扫描等）、成本估计、实际执行时间等信息。例如，如果发现大量的顺序扫描，可能意味着索引使用不当。
识别性能瓶颈：从执行计划中找出导致性能瓶颈的操作，如全表扫描、昂贵的连接操作等。对于高并发场景，若多个查询同时对某一表进行全表扫描，会消耗大量系统资源，导致性能下降。

锁机制影响：
- 行级锁：PostgreSQL 默认使用行级锁。在高并发写入场景下，如果索引设计不合理，可能导致锁争用。例如，频繁更新的列若没有合适的索引，可能会使多个事务锁住大量行，影响并发性能。因此，对于经常更新的列，考虑创建索引，以减少锁的粒度。
- 表级锁：某些操作（如 VACUUM）可能会获取表级锁。在高并发读写时，要尽量避免长时间持有表级锁。可以通过优化索引来减少 VACUUM 的频率和时间，例如合理设置填充因子，减少索引膨胀。
事务特性：
- 事务隔离级别：不同的事务隔离级别对并发性能有影响。例如，Serializable 隔离级别提供最高的数据一致性，但可能导致更多的并发冲突。在高并发场景下，若应用对一致性要求不是极高，可以适当降低隔离级别到 Read Committed 或 Repeatable Read，以提高并发性能。同时，索引设计要适应所选的隔离级别，确保事务在并发执行时不会因索引问题导致性能问题。
- 事务长度：长事务会占用系统资源并增加锁的持有时间。优化索引可以使查询更高效，从而缩短事务的执行时间。例如，对于包含多个查询的事务，确保每个查询都能利用索引快速执行。

创建复合索引：对于包含多个条件的复杂查询，创建复合索引。例如，如果查询经常使用 WHERE column1 = value1 AND column2 = value2，可以创建 CREATE INDEX idx_column1_column2 ON your_table(column1, column2);。复合索引的列顺序很重要，一般将选择性高的列放在前面。
覆盖索引：如果查询只涉及少数几个列，可以创建覆盖索引，即索引包含查询所需的所有列。这样查询可以直接从索引中获取数据，避免回表操作，提高查询性能。例如，CREATE INDEX idx_covering ON your_table(column1, column2, column3);，其中 column1、column2、column3 是查询中需要的列。
删除无用索引：定期检查数据库中的索引，删除那些不再被使用的索引。无用索引不仅占用存储空间，还会增加写入操作的开销，因为每次写入都需要更新相关索引。可以使用 pg_stat_activity 和 pg_stat_statements 视图来分析索引的使用情况。
索引维护：定期对索引进行 ANALYZE 和 VACUUM 操作，以更新统计信息，确保查询优化器能生成更准确的执行计划。同时，对于频繁更新的表，可以考虑适当调整填充因子，减少索引页的分裂，提高性能。

索引膨胀：频繁的更新操作可能导致索引页分裂，造成索引膨胀。解决方案是定期执行 VACUUM FULL 或 CLUSTER 操作来整理索引，但要注意这些操作可能会获取表级锁，应在低峰期执行。另外，可以通过调整填充因子（如 CREATE INDEX idx_name ON your_table(column) WITH (FILLFACTOR = 80);）来减少索引页分裂。
索引选择错误：查询优化器可能选择错误的索引，尤其是在复杂查询和多个索引存在的情况下。可以使用 FORCE INDEX 提示来强制查询使用特定索引，但这只是临时解决方案。长期来看，需要通过更新统计信息（ANALYZE）、调整索引设计等方式，让优化器做出更准确的选择。
锁争用加剧：新的索引设计可能会导致锁争用加剧。例如，创建了一个高选择性的索引，可能会使多个事务集中锁定某些索引页。解决方案是进一步优化索引设计，如采用分区表和分区索引，将数据和锁分散，或者调整事务隔离级别和事务执行顺序，减少锁冲突。

星途面试题库