数据库学习笔记
Postgresql
为什么选择 Postgresql?
首先如今的 Postgresql 在处理复杂查询性能相对更出色，利于构建高并发服务应用；其次 Postgresql 更加接近SQL标准，采用了MVCC提供了更全面的 ACID (原子性、一致性、隔离性、持久性)支持；再而，Postgresql 支持复杂的数据结构存储，比如我们可以使用 jsonb 存储一个复杂的键值对象，实现类似于 MangoDB 的键值存储和查询，利于根据业务的扩展数据结构
Postgres 和 MySQL 的区别，如何选择？
区别：
- 从设计哲学来说，Postgres 偏向于“学院派”，追求功能完备，而MySQL 偏向于“实用派”，追求简单高效；
- Postgres 采用多进程架构，而 MySQL 则采用多线程架构，Postgres 采用多进程可以更好的进行操作隔离，提升系统稳定性；
- Postgres 通过表空间和不同的存储方式来优化存储和访问性能，比如采用TOAST存储大数据，而MySQL则通过根据需求选择合适的存储引擎进行;
- 在分区表声明中，Postgres 原生支持，而 MySQL 需要使用额外的分区引擎实现；
- 在对JSON对象数据处理中，Postgres 提供了 jsonb 类型来优化json数据的存储，同时还提供了各种函数对 json 进行操作，而 MySQL 虽然也支持存储 json 数据，当功能就相对简单，性能和灵活性不如 Postgres
- 同时 Postgres 支持用户自定义数据类型、函数、操作符等，拥有更强的扩展性，而MySQL虽然也有较多的可扩展性，当灵活性和易用性还是稍逊色些
选择：
- 在需要复杂的数据分析、严格的数据一致性和追求自定义扩展时，我们可以选择使用 Postgres
- 在需要快速部署、只需要满足简单的CRUD时，我们可以选择使用 MySQL
Postgres MVCC 机制
在开启事务进行读取数据时，Postgres 会创建一个当前数据库状态的快照，只能查询获取到的之前已提交的数据版本，确保事务的数据视图一致性；在开启事务进行写操作时，Postgres 会创建一个新的数据版本，修改只会被引用到新版本中，而不会影响到旧版本，更不会影响到其他事务的数据版本，确保了数据操作隔离性，在写操作提交时，这个新版本的数据才会被其他事务可见，如果出现了数据冲突，Postgres 将采用锁或者乐观并发控制机制来解决，确保数据一致性
Postgres 大对象存储
Postgres 采用 TOAST 机制，当插入的数据超过一定的阈值时，Postgres 会尝试对其进行压缩，如果压缩后数据仍然很大，则会将其分割成更小的块，并存储在一个专门的TOAST表中，主表只保留一个引用
Postgres VACUUM 命令和作用
Postgres 中的 VACUUM 命令主要用于清理数据库中在执行插入、更新和删除操作后产生的不再需要的记录，回收存储空间，减少空间浪费，其次这个命令还可以防止事务ID达到最大值从而导致系统崩溃；Postgres 提供了自动执行 VACUUM 清理的机制，当然我们也可以手动执行，在执行标准 VACUUM 时，只会回收存储空间，期间不会锁表，在执行 VACUUM FULL 时，则会彻底回收空间，并且还会重新整理数据表，这期间可能会加锁
Postgres 备份
逻辑备份采用 pg_dump(不会造成阻塞) 内置工具，恢复采用 pg_restore
物理备份(PITR, point-in-time recovery)采用 pg_basebackup 工具，通过配置 postgresql.conf 恢复指令执行拷贝WAL(Write Ahead Logging)归档日志(restore_command)和指定的目标时间节点(recover_target_time)来完成恢复
SQL 性能调优
一是可以通过合理地使用索引进行优化
二是对查询进行重构，对 SQL 进行简化，减少多表连接
三是使用数据库缓存
同时 JOIN 多张表，会带来什么影响
一来是对性能有所影响，同时 JOIN 多张表会导致复杂的查询计划，同时还可能会带来更多表的锁定，降低查询效率
二来是会增加维护成本，JOIN 多表会使得 SQL 变得更加难以理解
优化方式：
- 拆分 SQL，将一条复杂的多表 JOIN SQL 拆成多个简单的 SQL
- 将 SQL 转成视图
索引相关
索引是什么
索引是用于提高数据库查询效率的一种特殊的数据结构
索引的类型
按数据结构类型分的话，有 BTree 索引、哈希索引、位图索引等
按物理存储方式分的话，有集簇索引和非集簇索引
按字段特性分的话，有主键索引、唯一索引、复合索引、普通索引和全文索引
创建索引时应该注意什么
一是要选择合适的列进行创建索引，优先考虑用于查询条件、排序或是连接的列
二是要注意避免在可能会频繁更新的列上加索引
三是避免创建过多的索引
最后是选择合适的索引类型
使用 BTree 索引的好处
使用 BTree 的数据结构最主要的好处就是可以大幅减少磁盘的访问，提高查询效率
如何分析索引是否失效以及什么情况下会发生索引失效
可以通过 EXAPLAIN 进行分析索引是否失效，如果SQL的分析结果是采用的是全表扫描，那么索引就是失效了；
当索引的结构被破坏时，索引会失效，比如将索引列作为参数传入到函数中、对索引列强制进行类型转换或者是索引列参与了计算
违反了索引的使用规则也会导致索引失效，比如查询条件不符合最左匹配规则、使用 LIKE 时最左边使用了通配符或者是使用 OR 同时拼接了索引列和非索引列
还有一种情况就是优化器自己主动放弃使用索引，比如在表的总行数比较小时，优化器会优先进行全表扫描
集簇索引和非集簇索引的区别
从存储方式上说，集簇索引的叶子节点上存储的是数据行，并且索引顺序和数据行顺序一致，而非集簇索引的叶子节点上存储的是数据指针，并且索引顺序和数据行顺序不一致，
从空间利用上说，集簇索引占用更少，因为数据和索引是一起存储的，而非集簇索引需要额外的空间去存储指针
从可创建个数上说，集簇索引通常只能创建一个，而非集簇索引可以创建多个
从更新代价上说，集簇索引相比于非集簇索引来说更大，因为可能会导致索引重排
慢查询优化
加索引
根据业务实际需要选择合适的索引类型，为合适的列添加索引
重构查询SQL
- 避免出现
Select *，按需获取 - 消除”N+1”查询，用 JOIN 代替子查询
- 使用
LIMIT减少数据获取量 - 合理使用索引，避免索引失效
- 减少排序和聚合开销
- 为排序字段添加索引，索引是本身就是有序的
- 使用
Where进行数据过滤，减少聚合数据量
- 拆分复杂查询，对含有大量的JOIN和复杂聚合的SQL进行拆分成更小的查询，减少执行计划的开销
核心要点:
- 数据量获取最小化
- 索引利用最大化
- 计算最简化
系统配置优化
根据实际的服务器配置和业务需求修改 Postgresql 系统配置为推荐值
如:
work_mem: 单个查询执行排序、哈希连接等操作时可使用的内存。可根据实际查询数据量进行配置合适的大小
shared_buffers: PostgreSQL 服务器进程共享的内存缓冲区，用于缓存数据页，减少磁盘 IO。可配置为推荐值：系统内存的 25%
锁
表级锁
冲突的锁模式
从弱到强，“X”表示不可同时持有，锁之间存在冲突
| 请求的锁模式 | 触发的场景 | ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|---|---|---|
ACCESS SHARE(访问共享) | 执行SELECT读取表内容而不修改表时 | X | |||||||
ROW SHARE(行共享) | SELECT命令在所有指定了FOR UPDATE、 FOR NO KEY UPDATE、FOR SHARE或 FOR KEY SHARE选项的表上时 | X | X | ||||||
ROW EXCLUSIVE(行排他) | 执行UPDATE、DELETE、INSERT和 MERGE 时 | X | X | X | X | ||||
SHARE UPDATE EXCLUSIVE | 执行VACUUM（不使用FULL）， ANALYZE，CREATE INDEX CONCURRENTLY， CREATE STATISTICS，COMMENT ON， REINDEX CONCURRENTLY， 以及某些ALTER INDEX 和ALTER TABLE变体时 | X | X | X | X | X | |||
SHARE(共享锁) | 执行CREATE INDEX（不带CONCURRENTLY）时 | X | X | X | X | X | |||
SHARE ROW EXCLUSIVE | 执行CREATE TRIGGER和某些形式的 ALTER TABLE | X | X | X | X | X | X | ||
EXCLUSIVE(排他锁) | 执行REFRESH MATERIALIZED VIEW CONCURRENTLY时 | X | X | X | X | X | X | X | |
ACCESS EXCLUSIVE | 执行ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW（不带CONCURRENTLY），以及很多形式的ALTER INDEX和ALTER TABLE也在这个层面上获得锁（见ALTER TABLE）时，同时这也是未显式指定模式的LOCK TABLE命令的默认锁模式 | X | X | X | X | X | X | X | X |
行级锁
| 要求的锁模式 | FOR KEY SHARE | FOR SHARE | FOR NO KEY UPDATE | FOR UPDATE |
|---|---|---|---|---|
FOR KEY SHARE(键共享锁) | X | |||
FOR SHARE(共享锁) | X | X | ||
FOR NO KEY UPDATE | X | X | X | |
FOR UPDATE | X | X | X | X |
页级锁
页面级别的共享/排他锁被用来控制对共享缓冲池中表页面的读/写。 这些锁在行被抓取或者更新后马上被释放。应用开发者通常不需要关心页级锁。
咨询锁
PostgreSQL提供了一种方法创建由应用定义其含义的锁
函数文档: 9.28. 系统管理函数
| 类型 | 释放时机 | 相关函数 | 适用场景 |
|---|---|---|---|
| 会话级 | 会话结束 / 手动释放 | pg_advisory_lock(获取排他锁)/pg_advisory_unlock(释放一个排他锁) | 跨事务的互斥（如分布式锁、任务防重） |
| 事务级 | 事务结束自动释放 | pg_advisory_xact_lock(获取排他锁)/ | 单次事务内的互斥（如避免重复插入） |
死锁
死锁发生的必要条件：
- 互斥条件；
- 请求保持条件；
- 不剥夺条件
- 环路等待条件。
隔离级别
并发问题描述
| 并发问题 | 原因 |
|---|---|
| 脏读 | 一个事务读取了由另一个并发未提交事务写入的数据 |
| 不可重复读 | 一个事务重新读取了之前读取过的数据，发现数据已被另一个事务修改（自初始读取以来已提交) —- 读取的数据内容前后不一致 |
| 幻读 | 一个事务重新执行一个返回满足搜索条件的一组行的查询，并发现由于另一个最近提交的事务而导致满足条件的行集发生了变化 —- 读取的行数前后不一致 |
| 序列化异常 | 成功提交一组事务的结果与依次运行这些事务的所有可能顺序不一致 |
级别描述
| 隔离级别 | 行为描述 | 脏读 | 不可重复读 | 幻读 | 序列化异常 |
|---|---|---|---|---|---|
| 读已提交 | 默认级别，当一个事务使用此隔离级别时，SELECT查询（没有 FOR UPDATE/SHARE子句）仅会看到查询开始之前提交的数据； 它不会看到未提交的数据或在查询执行期间由并发事务提交的更改 | 不可能 | 可能 | 可能 | 可能 |
| 读未提交 | 行为与“读已提交”相同 | 允许，但不在 PG 中 | 可能 | 可能 | 可能 |
| 可重复读 | 仅能看到事务开始之前提交的数据； 它从不看到未提交的数据或在事务执行期间由并发事务提交的更改 | 不可能 | 不可能 | 允许，但不在 PG 中 | 可能 |
| 可序列化 | 最严格的事务隔离。 该级别模拟了所有已提交事务的串行事务执行； 就好像事务是依次串行执行而不是并发执行一样 | 不可能 | 不可能 | 不可能 | 不可能 |
**PS: **
- PostgreSQL内部只实现了三种不同的隔离级别，即 PostgreSQL 的读未提交模式的行为和读已提交相同
- PostgreSQL的可重复读实现不允许幻读，读未提交不允许脏读
Redis
Redis 为什么快 / 为什么 Redis 采用单线程还可以这么快
首先，Redis 大部分操作都在内存中操作
- 其次，Redis 采用了单线程模型，避免了多线程之间的资源竞争，线程的切换需要CPU操作去保存上下文
- 还有一个重要的点，Redis 采用了 I/O 多路复用的方式处理请求
Redis I/O 多路复用
Redis 采用的是 epoll 多路复用的机制，在初始化时会调用 epoll_create 创建一个epoll 对象，同时调用 socket 函数创建服务器socket，接着进行绑定端口，监听服务器socket，每当客户端成功和Redis建立连接, 都会将客户端的socket注册到epoll中，然后epoll会监听客户端是否有事件需要处理，如果有，则会将事件分发到Redis中相应的处理器进行处理，通过I/O多路复用可以避免频繁的进程/线程切换带来的性能损失，使得 CPU 不再是性能瓶颈，这也是 Redis 之所以采用单线程的原因
Redis 是如何做持久化的
Redis采用AOF日志、RDB快照和混合持久化三种方式。
首先AOF日志方式是通过在每次执行命令时进行记录到日志文件中，Redis会专门fork一个子进程去完成，同时AOF还会采取重写的机制去压缩日志文件，降低恢复时间，但这个方式也很难避免大文件日志的出现，导致Redis恢复时间过长;
那么 RDB 快照 方式，这个方式是通过记录某一时刻下的内存数据，在恢复的时候直接读取并应用这个内存数据就行了，恢复效率比AOF高些，就解决了恢复时间过长的问题，但是如果高频去执行RDB生成快照，就会导致性能下降；
接下来的混合方式，就融合了前面说的两个方式的优点，同时有降低了这两个方式的缺点带来的影响，混合方式首先会采用RDB的方式记录一个快照写入日志文件，然后再通过AOF的方式去记录指令，这样混合方式的缺点也很明显，AOF文件可读性就变差很多，同时兼容性也比较差，在过往版本就不能使用新版本生产的AOF文件进行恢复
Redis 集群高可用实现
Redis 采用主从复制、哨兵模式和切片集群模式的方式进行实现；
首先先说说主从复制，现在假设我们需要从原先一台Redis服务器扩展到多台Redis服务器来降低负载，原先的服务器就是定为主服务器，其他的就定为从服务器，主服务器上可以进行读写数据，但其他的从服务器只能进行读数据，在主服务器上完成写数据后就会自动将写操作同步到其他从服务器，但是同步写操作的过程是异步进行的，主服务器并不会等待从服务器完成同步后才返回响应给客户端，如果从服务器没有执行写操作同步就会出现数据不一致的问题，要应对这个问题，一来是尽量保证主从服务器在同一机房下，确保网络连接状态良好，二来是我们可以编写一个外部程序去监控主从复制的进度，通过 INFO replication 命令获取主节点的写命令进度和从节点复制写命令的进度，然后通过判断这两个的进度差值是否大于我们预设的阈值来判断是否让客户端去从指定的从节点读数据，以减少读到数据不一致的情况
接下来再说说哨兵模式，Redis 的哨兵模式实现了监控主从节点的服务器状态，哨兵会每隔1秒给所有主从节点发送PING命令，如果在指定时间内没有接收到响应，则会将该节点标记为下线；同时Redis的哨兵模式还提供了主从故障转移的功能，当主节点服务器挂掉了，就会重新在从节点中选出一个新的主节点，以避免出现没有主服务器处理写请求的情况；这里可能会出现脑裂的问题，解决这个问题的方式是通过配置主节点至少连接节点的数量和主从复制的延迟描述，只有当主节点连接的从节点大于指定数量并且主从复制的延迟在指定时间范围内，主节点才接收和处理写请求
最后再说下切片集群模式，这个模式主要应对Redis的缓存数据量大到一定程度后无法缓存的情况，切片集群采用哈希槽的方式处理数据和节点的映射关系，首先Redis 会对键值对的Key进行CRC16算法算出一个16位的值，然后进行取模的操作得到实际放入的槽位；
Redis 是如何处理过期的数据
Redis 采取惰性删除结合定时删除的策略配合使用的方案，只有在范围键值时判断该数据是否过期，如果过期了就直接删除；同时每隔一段时间随机抽取指定数量的键值对，删除其中的过期键值对，当过期的键值对比例小于指定值时停止本轮定时检查，然后等待下一轮检查
针对于持久化，在快照持久化方式中生成RDB文件时，会对key进行过期检查，过期的键不会保存到新文件中，在主服务器载入RDB文件时会再检查数据是否过期，过期的键不会加载到数据库中，但在从服务器中则不论键是否过期都直接加载到数据库中，因为在主服务器进行数据同步时，从服务器的数据会被清空；在AOF持久化方式中，在写入日志时会将已过期的键以追加 DEL 命令的方式显示删除该键，在重写日志时则会进行检查，已过期的键不会写入到新日志中
针对于主从模式，从库不会进行过期扫描，不论键值是否过期都将会像正常的键值一样进行返回，从库的过期处理由主库服务器进行控制，当主库中的key到期时，会将DEL命令同步到所有从库
Redis 是如何进行内存淘汰的
Redis 默认的策略是不进行淘汰，当写入的缓存达到预设的最大值时，之后的任何请求都将直接返回错误; 当然我们也可以配置 Redis 采用根据过期时间优先淘汰最近未使用的键值，或者淘汰最少使用的键值
什么是缓存雪崩、缓存击穿、缓存穿透？如何解决这些问题？
首先缓存雪崩是指大量缓存在同一时间过期或失效，导致大量的请求同一时刻都没有命中缓存直接进行数据库访问，导致数据库服务器负载骤增，严重的将导致数据库宕机，导致一系列的连锁反应，造成系统崩溃的问题；我们可以采取打散缓存的过期时间或设置缓存不过期通过后台服务更新的方式去解决这个问题
其次缓存击穿是指同时有大量的请求访问某一个刚好过期的数据，导致无法命中缓存，直接访问数据库，导致数据库容易被高并发请求冲垮的问题；我们可以采取不给热点数据设置过期时间或者通过互斥锁的方案去解决这个问题
最后的缓存穿透是指某一时刻突然有大量请求访问既不在缓存中也不在数据库中的数据，导致数据库压力骤增的问题；我们可以采取对非法请求进行限制、设置空值或默认值直接返回给应用或者是使用布隆过滤器快速判断数据是否有效的方案去解决这个问题
Redis 分布式锁
应用场景
当多个应用共享一个资源时，确保同一时刻只有一个应用进行访问和使用
原理
使用SET命令，在设置 key 时，添加参数NX，来实现 key 不存在时才插入 的效果，即当
- key 存在时，显示插入失败，表示加锁失败
- key 不存在时，显示插入成功，表示加锁成功
加锁操作:
执行命令
1
SET lock_key lock_unique_value NX PX 10000
描述:
lock_key为 key 键lock_unique_value为客户端生成的唯一标识，用于区分不同的客户端的锁操作NX参数为设定只有在 key 不存在时才插入PX参数为设定过期时间，以避免客户端因发生异常导致锁无法被释放，PX 10000设定过期时间为 10s
释放操作:
通过执行一个 Lua 脚本来保证解锁操作的原子性，在 Redis 中，可以以原子的方式执行 Lua 脚本
1
2
3
4
5
if redis.call("get", KEYS[1]) == ARGV[1] then
return redis.call("del", KEYS[1])
else
return 0
end
描述:
- 先判断当前存储的唯一标识是否与当前客户端的标识一致，确保“谁加的锁就谁释放”，避免锁的误释放
- 然后再执行删除 key 操作
数据库和缓存一致性
采取策略: 先更新数据库数据， 再删除缓存
理由: 数据库写入所需时间 > 缓存更新所需时间，大部分情况下不会出现先执行的数据库更新请求比后执行数据库更新的请求还晚执行缓存更新