数据库设计三大范式
- 1NF：数据表每一列不可拆分（原子性）
- 2NF：每一列都与主键相关（只描述一件事）
- 3NF：每张表都只与主键直接相关
Left join/Right join
使用事务保持数据一致性和完整性
Begin：
Commit
使用外键
在频繁进行排序分组的列上建立索引
事务
- 原子性：事务包含的操作要么全部成功，要不全部失败回滚
- 一致性：事务执行前后处于一致性状态
- 隔离性：当多个用户并发访问数据库时，数据库为每个用户开启的事务不被其他事务的操作所干扰，多个并发事务之间要相互隔离
- 持久性：事务一旦提交了，改变就是永久性的
MVCC（多版本并发控制）：保存数据的多个版本来实现并发控制
当需要更新某条数据时，不会立即用新的数据覆盖原始数据，而是创建该记录的一个新的版本。（MySQL是悲观锁机制）
四种隔离级别
- Serializable：串行化：不会幻读，重复度，脏读
- Repeatable：可重复读：会幻读
- Read Committed：读已提交：仅能读取到已经提交的数据（默认），会不可重复读，会幻读
- Read Uncommitted：读未提交
事务并发调度问题
- 脏读：读取未提交，使用version
- 不可重复读：读取之前提交的，更改的数据
- 幻读：读取之前提交的整批数据，新增与删除的数据
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 会 | 会 | 会 |
| 读已提交 | 不会 | 会 | 会 |
| 可重复读 | 不会 | 不会 | 会 |
| 串行化 | 不会 | 不会 | 不会 |
数据库的锁：
- 共享锁：读锁，阻止其他事务修改表数据
- 排他锁：写锁，阻止其他事务读写
- 行级锁(开销大，枷锁慢，会出现死锁，粒度小，并发度高)，表级锁(开销小，加锁块，不会出现死锁，粒度大，并发度低)，页锁
- 意向锁：先申请意向共享锁，成功之后申请行锁：判断表是否被其他事务用表锁锁定；发现表上有共享意向锁，表明表中有些行被锁定了，因此写锁会被阻塞
封锁协议
- 一级封锁协议：如果事务有写操作，在读操作之前加排他锁，事务结束后释放
- 二级封锁协议：事务读取之前加上共享锁，读完之后释放
- 三级封锁协议：事务读取数据之前加共享锁，事务完成以后释放
死锁-相互等待
- 一次封锁法
- 顺序封锁法
- 超时等待
MySQL数据库引擎
| MyISAM | InnoDB |
|---|---|
| 不支持事务 | 支持 |
| 不支持外键 | 支持 |
| 支持全文索引 | 5.6以后版本支持 |
| 只有表锁 |
MySQL主备模式
主服务器会将每次改动写入到日志中
从服务器会读取日志并执行一遍
MySQL的特点：
- 核心完全多线程，支持多处理器
- 能够工作在不同的平台上
- 通过高度优化的类库实现SQL函数库并能够快速，查询初始化以后没有内存分配和内存泄漏
索引
索引的存储类型：B树索引(Innodb,MyISAM)和哈希索引(MEMORY)
- 普通索引：不加任何条件限制的索引，可以创建在任何数据字段上
- 主键索引：根据主键构成的索引，不允许重复，不允许空值
- 唯一索引：限制索引值必须是唯一的（自动索引-完整性约束时创建的索引，设置为主键或者为唯一时和手动索引）
- 全文索引：在数据类型为CHAR，VARCHAR，TEXT上，不区分大小写，为二进制数据类型时区分大小写
- 多列索引：多个字段构成的索引，只有查询条件使用了锁关联字段中的第一个字段，多列索引才会使用(最左前缀原则)
BTree索引 度为2d，高为h
- 每个叶子节点的高度一样，等于h
- 每个非叶子节点由n-1个key和n个指针组成，d<=n<=2d，节点两端一定是key
- 叶子节点的指针都是null
- 非叶子节点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据
- 查找复杂度为hlog(n)
B+Tree索引 d为树的度数，h为树的高度
- B+Tree的非叶子节点不存储数据，只存储键值
- B+Tree的叶子节点没有指针，所有的键值都会出现在叶子节点上，且key存储的键值对应data数据的物理地址
- B+Tree的每个非叶子节点由n个键值key和point组成
对比
- 磁盘读写代价更低：尽可能的减少磁盘IO来加快读取速度
- 查询速度更稳定：B+Tree非叶子节点不存储数据，因此所有的数据都要查询至叶子节点，叶子节点的高度是相同的
MyISAM-非聚簇索引
- 非聚簇索引的主索引和辅助索引(如果查询条件不是主键)几乎一样，只是主索引不允许重复，不允许空值，叶子节点的key都存储指向键值对应的数据的物理地址
- 非聚簇索引的数据表和索引表是分开存储的
- 非聚簇索引的数据是根据数据的插入顺序保存，因此非聚簇索引更适合单个数据的查询
图
image.png
InnoDB-聚簇索引
- 聚簇索引的主索引的叶子节点存储的是键值对应的数据本身，辅助索引的叶子节点存储的是键值对应的数据的主键键值，因此主键的值长度越小越好，类型越简单越好
- 聚簇索引的数据和主键索引存储在一起
- 聚簇索引的数据是根据主键的顺序保存，因此合适按主键索引的区间查找。
- InnoDB表是基于聚簇索引建立的，辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。过长的主索引会导致辅助索引变得过大
图
image.png
image.png
char和varchar
- char定义从列的长度是固定的，右边以空格填充，检索时删去空格，不进行大小写转换(0-255)
- varchar变长(0-65535,除去起始符和结束符最大65532)，保存时只保存需要的字符数，然后一个字节来保存大小，超过255时需要两个字节
网友评论