数据库面试总结

pgsql 数据库

在大数据量的查询情况下，pgsql和mysql 谁更有优势
当数据量达到一定程度时（如数亿条记录以上），PostgreSQL 在查询性能方面的优势可能会更加明显。这得益于其强大的查询优化器、丰富的索引类型、高效的并发处理和事务管理能力以及支持分区和并行查询等特性。
pgsql的索引类型
- BTree 常规索引
- Hash
- GiST 广义搜索树
- SP-GiST 空间路径广义搜索树
- GIN 全文索引
- BRIN
为什么postgresql比MySQL性能好
- PostgreSQL在处理复杂查询和大数据集方面的性能表现优于MySQL。
- PostgreSQL支持多版本并发控制（MVCC），这使得它能够高效地处理复杂的事务和并发操作
- PostgreSQL是多进程数据库，它能够充分利用CPU资源，从而获得更好的性能表现。
- PostgreSQL完全支持ACID特性，并且其事务处理机制更为强大，确保了数据的一致性和完整性
- PostgreSQL在创建索引和存储方面表现更好
什么是回表查询
简单来说，就是根据某个索引查询的时候，索引里没有查询所需要的字段，就会根据id回到表里查询，避免回表的方式:索引覆盖，把需要的字段都包括在索引里。
表膨胀以及表膨胀的危害
原理：pg旧数据存储在数据文件中，并不立刻清理，只是标记为无效。这些旧数据如果不能及时清理，业务表和数据文件会越来越大，引发表膨胀。
原因：
慢查询
游标未关闭
大事务或者DML量过大，产生死元组速度快于清理
未开启autovacuum或者禁用了track_counts参数
autovacuum过慢（例如IO问题、触发阈值不合理、执行周期不合理、配置了延迟触发，worker过于忙碌等）
长事务，包括pg_dump和pg_dumpall，默认会以可重复读级别开启事务
危害：
表和数据文件占用空间持续增长
查询表时要扫描的数据块可能增多，查询速度变慢
需要用vacuum full处理（如果不用其他插件），vacuum full会获取表的8级锁，阻塞对表的所有操作，影响业务。并且最大会占有原来磁盘空间的两倍，可能打爆磁盘空间。
长事务的危害以及如何溯源长事务
- 如果前面执行过DML语句，会锁定相关数据，阻塞后面语句
- 阻塞create index（也包括 concurrently）
- WAL无法及时清理，占用空间大
- 占用连接数
- 逻辑复制下会阻塞复制槽的创建
WAL 堆积的原因有哪些
- 主库大事务、长事务、包括pg_dump,pg_dumpall导出
- 未开启归档，或归档命令执行失败（命令报错、目录不存在等），开启归档但是归档效率低，默认单进程归档，pgbackrest可以实现多进程归档
- 复制槽失效
- max_wal_size,wal_keep_size（pg 13前为wal_keep_segment）设置过大
- 主库DML量过大，产生WAL日志过多
- 过于频繁的检查点
长连接的危害是什么
- 连接数堆积，可能超过 max_connections
- 连接数越多，进程调度和管理越复杂，pg性能会线性下降（pg 14对此进行了优化，但依然不建议过多）
- 无法及时释放内存，甚至遇到过导致oom的情况
- 空闲过长可能会被防火墙或者DB超时中断，导致应用报错
空值是如何存储的以及索引是否存储空值
BTree 索引存储空值（SQL Server也存，Oracle不存）
存储：在pg元组头数据中，有一个t_bits 的数组，用于存储空值位图。当元组中没有null值的时候，t_bits可以被认为是空的，当元组有null值的列时，t_bits使用一个bit来表示列是否为null。
索引失效的各种原因
1.
行锁是如何实现的，行锁是否会存储在共享内存中
pg采用元组级常规锁+xmax结合的方式实现行锁。不单纯用元组级常规锁，是为了避免事务修改行过多时，锁表急剧增大导致性能劣化，并且锁表在共享内存中的大小是有限的。因此，行锁也是不存储在内存中的。