概念结构设计
- 通过对用户需求进行综合、归纳和抽象，形成独立于具体数据库管理系统的概念模型
- 把需求分析阶段得到的应用需求，抽象成概念模型
- 连接现实世界和信息世界的桥梁
- 好的概念结构设计
- 能真实、充分的反映现实世界
- 要易于理解
- 要易于修改
基本概念
- 实体（Entity）
客观存在并可相互区别的事物称为实体，可以是具体的人、事、物或抽象的概念 - 属性（Attribute）
实体所具有的某一特性称为属性，一个实体可以由若干属性来刻画 - 关键字（key）
唯一标识实体的属性集称为关键字（码），具有唯一性和最小性的特性 - 域（Domain）
属性的取值范围称为该属性的域 - 实体类型（Entity Type）
相同类型和性质的实体组成了实体类型，实体是实体类型的实例
E-R模型
- 比较流行的概念模型，用E-R图来描述现实世界
- 先分析实体之间的联系，再用E-R图表示出来
两个实体之间的联系
- 一对一：对于实体集A中的每个实体来说，实体集B中至多有一个实体与之联系，反之亦然，则实体A和B有一对一联系
- 一对多
- 多对多
逻辑结构设计
- 把E-R图转换为数据库系统支持的逻辑数据模型
常见的逻辑数据模型
- 层次模型
- 网状模型
- 关系模型
- 面向对象数据模型
关系模型
- 术语
- 关系（relation）：一个关系对应一张表
- 元组（tuple）：表中的行
- 属性（attribute）：表中的列
- 属性名：属性的名称
- 关键字（key）：用来唯一确认某个元组的属性或属性组，也叫码或键
- 域（domain）：一组具有相同数据类型的值的集合，属性的取值范围来自某个域
- 分量：元组中的某一个属性值
- 关系模式：是对关系的描述，表示为：关系名（属性1，属性2，属性3）
- 规范化：关系模型要求关系是规范化的。规范化的最基本要求是，关系中的每个分量必须是不可分的数据项。不符合这个要求的关系，就是非规范关系。
物理结构设计
确定数据的存储结构
- 选择用什么数据库
- 确定每个字段的数据类型
- 如何记录日志
eg：binlog、relaylog、undolog、slowlog… - 如何设计备份
- 如何确定系统配置
确定数据的存取方法
- 创建什么索引、怎样创建索引
- B+Tree索引、Hash索引、R-Tree索引
- 联合索引
- 存取时间
- 维护代价
- 空间利用率
数据库加密设计
- 敏感信息如何加密？
- 在数据库层面处理
- 在应用层面处理
- 在传输层处理
在数据库层面处理
- ENCODE()/DECODE()/DES_ENCRYPT/DES_DECRYPT…
以上函数MySQL5.7废弃，MySQL8.0删除 - AES_ENCRYPT(),AES_DECRYPT()
AES_ENCRYPT(明文，密钥)，AES_DECRYPT(密文，秘钥)
创建表格
CREATE TABLE test_aes (id INT PRIMARY KEY auto_increment,column_varbinary VARBINARY(16),column_binary BINARY(16),column_blob BLOB(16),column_varchar_utf8 VARCHAR(64),column_varchar_latin VARCHAR(64) CHAR SET latin1
)
插入数据
INSERT INTO test_aes ( column_varbinary, column_binary, column_blob, column_varchar_utf8, column_varchar_latin )
VALUES(AES_ENCRYPT( 'varbinary', 'key_pass' ),AES_ENCRYPT( 'binary', 'key_pass' ),AES_ENCRYPT( 'blob', 'key_pass' ),AES_ENCRYPT( 'varchar_utf8', 'key_pass' ),AES_ENCRYPT( 'varchar_latin', 'key_pass' ) )
结论1：添加数据操作执行失败，因为utf-8字符集的varchar字段无法存储加密后的数据
INSERT INTO test_aes ( column_varbinary, column_binary, column_blob, column_varchar_latin )
VALUES(AES_ENCRYPT( 'varbinary', 'key_pass' ),AES_ENCRYPT( 'binary', 'key_pass' ),AES_ENCRYPT( 'blob', 'key_pass' ),AES_ENCRYPT( 'varchar_latin', 'key_pass' ) )
结论2：执行成功，latin1字符集的varchar字段可以存储加密后的数据，varbinary、binary、blob可以存储加密后的数据（基于二进制存储）
utf-8的varchar怎么存储加密数据？
-
方案1：用hex()转16进制存储
INSERT INTO test_aes (column_varchar_utf8) VALUES (HEX(AES_ENCRYPT('varchar_hex','test_key')))SELECT AES_DECRYPT(UNHEX(column_varchar_utf8),'test_key') from test_aes -
方案2：转base64存储
INSERT INTO test_aes (column_varchar_utf8) VALUES (to_base64(AES_ENCRYPT('base64','key')))SELECT AES_DECRYPT(from_base64(column_varchar_utf8),'key') from test_aes
对于无法解密的数据，aes_decrypt会直接返回null
在传输层处理
- SSL
- 数据加密
- 身份验证
- 消息完整性验证
- SSL连接时参数控制
事物隔离级别
ACID
- 原子性（Atomicity）：事物是一个不可再分割的单位，其中的操作要么都发生，要么都不发生
- 一致性（Consistency）：事物在开始和结束的时候，应该满足一致性约束
- 隔离性（Isolation）：事物的执行不会被其他的事务干扰
- 持久性（Durability）：事务一旦提交，对数据库的修改就会持久的保存在数据库中，不会被回滚
隔离级别
Read Uncommitted（读未提交）
- 事务能够看到其他未提交事务的执行结果
- 实际项目中很少使用，因为隔离性较差，而且性能和其他级别相比，提升不大
Read Committed（读取提交内容）
- 一个事务只能看见其他事务已提交后的结果
- 多数数据库系统的默认隔离级别（MySQL不是）
Repeatable Read（可重读）
- 确保在同一事务中多次读取同一数据时，结果相同
- MySQL默认的隔离级别
Serializable（串行化）
- 通过强制事务排序，使之不可能发生冲突
- 隔离级别最高，性能最差，用得很少
设置隔离级别
查看隔离级别
隔离级别可能造成的问题
- 脏读：读到了其他事务尚未提交的数据
未提交的数据意味着这条数据可能会回滚，也就是说，这条数据最终并不会存到数据库中（读到不一定会存在的数据） - 不可重复读：在一个事务中对同一条数据的两次查询结果不同
- 幻读
- 当读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再去操作该范围的数据行时，发现有新的“幻影”行
- 类似于不可重复读，都是在一个事务周期内读的数据不一致，区别在于：幻读侧重于插入操作带来的影响，而不可重复读侧重于修改或删除带来的影响。
数据库锁详解
InnoDB中的锁
共享排它锁
- 共享锁（SLock）：允许持有该锁的事务读取一行数据
- 排他锁（XLock）：允许持有该锁的事务删除或更新一行数据
- 特性：
- 行级锁
- 如果一个事务持有行X的共享锁，那么其他事务也可以获得该行的共享锁
- 如果一个事务持有行X的排它锁，那么其他事务必须等待这个事务释放后才能持有
- UPDATE、DELETE语句如何加锁？
InnoDB会自动给涉及的数据集加排它锁 - SELECT语句如何加锁？
- 普通的SELECT语句，不会加锁
sql select * from table where ... 
- 普通的SELECT语句，不会加锁
意向锁（Intention Locks）
- 未来某个时刻，事务要加共享锁/排它锁了，所以先提前声明意向
- 意向共享锁（intention shared lock，简称IS）：表示事务有意向对表中的某些行加共享锁
- 意向排它锁（intention exclusive lock，简称IX）：表示事务有意向对表中的某些行加排它锁
- 特性
- 表级锁
- 事务在请求S锁和X锁前，需要先获得对应的IS、IX锁
- 意向锁并不会阻塞全表扫描以外的任何请求
- 为什么要引入意向锁？
- 提高事务的锁检测性能
- 共享排它锁，意向锁的兼容性
记录锁（Record Locks）
- 用来封锁索引记录，从而防止其他事务针对同一记录执行增删改操作
间隙锁（Gap Locks）
- 在REPEATABLE-READ（可重复读）隔离级别下的锁机制，用来封锁索引里面的间隙，解决幻读问题。
- 间隙：键值在条件范围内但不存在记录
包括：索引记录之间的间隙，第一条索引记录之前的范围，最后一条索引记录之后的范围
- 间隙锁为什么能够解决幻读问题？
- 间隙锁特性
- 要有索引
- 封锁的是索引里面的间隙，防止其他事务在间隙中插入数据
- 解决幻读问题
临键锁（Next-Key Locks）
- 记录锁和间隙锁的组合，封锁索引记录和索引中的间隙。会封锁记录本身以及索引记录之前的区间，即范围（上一条记录，记录本身]
- 如果一个事务占用了索引记录R的共享排它锁，则其他事务不能再记录R之前的区间插入新的索引记录
- InnoDB行锁默认用的就是临键锁
插入意向锁（Insert Intention Locks）
- 由insert操作再插入行之前设置的一种间隙锁
- 多个事务，在同一个索引、同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此
自增锁（AUTO-INC Locks）
- 特殊的表级锁
- 专门针对插入AUTO_INCREMENT类型的列的事务
- 最简单的情况，如果一个事务正在往表中插入记录，其他事务的插入必须等待，以便第一个事务插入的行时连续的值
- innodb_autoinc_lock_mode可用来调整该锁的算法
锁优化的几个建议
- 尽量使用较低的隔离级别（实际项目中很少调整）
- 尽量使用索引访问数据，使加锁更精确，减少锁冲突的机会
- 选择合理的事务大小，小事务发生锁冲突的几率也更少
- 除非必须，查询时不要显示加锁
阻塞与死锁
阻塞
- 一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，就形成了阻塞
- innodb_lock_wait_timeout控制等待时间，单位秒，默认50
死锁
- 是指多个进程/线程在运行过程中因争夺资源而造成的一种僵局，当进程/线程处于这种僵持状态时，若无外力作用，他们都将无法再向前推进
如何避免死锁
- 以固定的顺序访问表和行
两个更新数据的事务，事务A更新数据的顺序为1，2；事务B更新数据的顺序为2，1，这样更容易造成死锁 - 大事务拆小，大事务更倾向于死锁，无果业务允许，将大事务拆小
- 在同一个事务中，尽可能一次锁定需要的所有资源，可减少死锁概率
- 降低隔离级别（很少使用）
- 用表级锁，表级锁不存在死锁问题（很少故意这么干）
- 如果SQL没有使用索引，会自动用表级锁
- 可以用如下语法强制加表级锁
MVCC（Multi-Version Concurrency Control） 多版本并发控制
- MVCC使得支持行锁的数据库引擎，不再单纯的使用行锁来进行数据库的并发控制，而是把数据库的行锁与行的多个版本结合起来，这样只需要很小的开销，即可实现非锁定读，大大提高数据库系统的并发性能。
隐藏字段
- DB_TRX_ID（6字节）：用来记录最近一次对本记录插入或修改的事务ID
删除操作也会视为一个update操作，只是会额外修改删除标记（DELETE BIT） - DB_ROLL_PTR（7字节）：指向当前行的undo log信息，也叫回滚指针
- DB_ROW_ID（6字节）：随新行插入而单调递增的行ID
如果当前表没有聚簇索引，InnoDB会用该字段作为聚簇索引；如果已存在聚簇索引，该字段则不作为聚簇索引
Undo log
- 存储老版本数据，当一个事务需要读取行记录时，如果当前行记录不可见，则顺着undo log链找到满足其可见性条件的行记录版本
- insert undo log：事务insert新纪录时产生的undo log
只在事务回滚时需要，并且在事务提交后就可以立即丢弃 - update undo log：事务进行delete和update操作时产生的undo log
- 不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录时，对应的undo log才会被pruge线程删除
- pruge线程：专门用来处理DELETE BIG是true的记录的线程
MVCC工作机制
快照读
- 普通SELECT语句会使用快照读
- REPEATABLE-READ级别：事务begin后，第一条SELECT语句会创建一个快照，读取满足可见性的数据，之后该事务会一直用这个快照，直到事务结束
- READ-COMMITTED级别：事务begin后，每条SELECT语句都会重置快照（重新创建快照）读取当前满足可见性的数据
当前度
