总结篇7——数据库

数据库是如何工作的

数据库设计理论

函数依赖

异常

不符合范式的关系，会产生很多异常

1. 数据冗余
2. 修改异常
3. 删除异常
4. 插入异常

范式

1. 第一范式：属性不可分
2. 第二范式：每个非主属性完全函数依赖于主键——可以通过分解来满足
3. 第三范式：非主属性不传递函数依赖于主键

数据库核心知识

事务 ACID

事务是一组不可分割的SQL语句，要么都执行成功，要么都执行失败。可以通过commit提交一个事务，也可以使用rollback进行事务回滚。

1. 原子性：事务被视为不可分割的最小单元，要么都成功，要么都失败。
   回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这个修改操作就可以。
2. 一致性：事务执行前后，数据库的状态保持一致，也就是所有事物对一个数据的读取结果都是相同的。
3. 隔离性：一个事物所做的修改在最终提交之前面对其他的事务都是不可见的。
4. 持久性：事务一旦提交，对数据库的更改时永久的。机试系统发生崩溃，事务的执行结果也不能丢失。但是可以通过备份来恢复。

在MySQL中，是默认采取自动提交的模式，如果不使用开始事务语句来开启一个事务，那么每个查询都被当作一个事务自动提交。

并发一致性问题

在并发的环境下，事务的隔离性是很难保证的，因此会出现一些并发一致性问题。

1. 丢失修改：如果有两个事务一起对一个数据进行修改，一个事务先修改，随之另一个事务也跟着修改，则会倒是第一个修改的数据丢失。
2. 读脏数据：当前事务读取到了其他事务更新但还没有提交的数据。
3. 不可重复读：在一个事务内多次读取同一数据，但结果不一致。
4. 幻读：事务1读取某个范围内的数据，事务2在这范围内插入新的数据，而事务一再次读取的时候，结果出现不一致。
   产生不一致问题主要原因时破坏了事务的隔离性，解决办法时通过并发控制来保证隔离性，并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户更容易解决并发一致性问题。

事务的隔离级别

1. 读未提交
2. 读已提交
3. 可重复读
4. 串行化

多版本并发控制MVCC是MySQL的InnoDB存储引擎实现隔离界别的一种具体方式，适用于实现读已提交和可重复读这两种隔离界别。而读未提交隔离界别总是读取最新的数据，无需使用MVCC。可串行化隔离级别需要对多有读物的行都加锁，单纯使用MVCC无法实现。

数据库优化

优化数据访问：

1. . 减少数据的请求量
   • 不要使用select *
   • 尽量使用 limit
   • 缓存重复查询的数据
2. . 减少服务端扫描的行数
   • 使用索引来覆盖查询

重构查询方式：

1. 切分大查询：一个大查询如果依次执行的话，可能会一次性锁住很多数据，而沾满整个事务日志，阻塞很多很小但很重要的查询。【limit 10000】
2. 分解大连接查询：将一个大莲姐查询分解成对每一个表进行一次单表查询，然后将结果保存在应用程序中进行关联。

SQL优化

MYSQL

存储引擎

主从复制

主要设计三个线程：binlog、I/O、SQL

1. binlog线程：负责将主服务器上的数据更改写入二进制日志中
2. I/O线程：负责从主服务器读取二进制日志，并写入从服务器的中继日志中。
3. SQL线程：负责读取中继日志并重放其中的SQL语句

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。