MySQL知识点

事务

事务的特性

1. 原子性：所有操作要么都做要么都不做
2. 一致性：数据在事务操作前后都满足业务规则的约束
3. 隔离性：数据库允许多个并发事务同时对数据进行读写和修改的能力
4. 持久性：对数据的修改是永久的

truncate与delete

区别：

• truncate是DDL语句操作，delete是DML语句操作
• 简而言之(DDL是建库/建表/等语句;DML是增删改查语句)

1. truncate不能回滚，delete可以回滚
2. truncate清空表的自增id属性，从1重新开始记录，delete则不会

并发带来的问题

• 脏读：当一个事务对一个数据修改后还没提交数据库中时，另一个事务也访问了这个数据
• 丢失修改：一个事务修改数据时，另一个事务也修改了这个数据
• 不可重复读：一个事务多次读取同一个数据时 还没结束时，另一个数据页访问此是数据，导致两次读取的数据不一样
• 幻读：第一事务读取了几行数据，接着另一个事务插入了几行数据，，随后第一事务就会发现多了一些原本不存在的数据

隔离级别

• READ-UNCOMMITTED(读取未提交)：允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
• REPEATABLE-READ(可重复读)：对同⼀字段的多次读取结果都是⼀致的，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
• SERIALIZABLE(可串⾏化)：完全服从ACID，可以防⽌脏读、不可重复读以及幻读。

innoDB锁类型

行锁

id name score
1   aa    60

• 在会话A事务中更新字段score=60时名字设为aaa
• 在会话B事务中，更新会话A同一行数据，将name设为a，会出现锁等待超时现象

间隙锁(Gap lock)

• 在RR隔离级别下，为了避免幻读，引入了Gap lock
• 但它只锁定行记录数据的范围，不包含本身，即不允许在此范围内插入任何数据
  在RR级别下

id  name  score
1   aa      60
2   bb      70  
3   cc      80

• 会话A查询score<80的记录，在上面加了一个共享锁
• 会话B往表中插入score=75的数据，但没插入成功，出现锁超时现象
• 在scor<80的这个区间内，不允许有任何数据插入

池化思想(或好处)

• 抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销
• 比如去食堂打饭，每次阿姨都打好了，去了直接吃

InnoDB处理死锁

• 检测到死锁的循环依赖，立即返回一个错误
• 目前的处理办法是：将持有最少行级排它锁的事务进行回滚

MVCC

• 多版本并发控制(可以认为MVCC是行级锁的一个变种)，在很多情况下避免了加锁的操作，因此开销很低
• 实现：通过保存数据在某个时间点的快照来实现的，换句话说不管需要执行行多长时间，每个事物看到的数据都是一致的。

MVCC分类

• 悲观并发控制和乐观并发控制
• 只在repeatable read(rr)和read committed(rc)两个隔离级别下工作

InnoDB

• 采用MVCC来支持高并发，默认级别是RR(可重复读),并且通过间隙锁策略防止幻读的出现
• 使用行锁，并且支持事务

MyISAM

• 对整张表进行加锁(表锁)

索引

• 排好序的快速查找数据结构
• 降低了数据库排序的成本
• 降低了数据库的IO成本
• 创建索引会占用空间

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 主键索引：定为主键后数据库会自动建立索引，innodb为聚簇索引
• 复合索引：即一个索引包含多个列

B+和B树

B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键

字。从这个角度看 B树的性能好像要比 B+树好，而在实际应用中却是 B+树的性能要好些。
因为 B+树的非叶子节点不存放实际的数据， 这样每个节点可容纳的元素个数比 B树多，树高比 B树小，
这样带来的好处是减少磁盘访问次数。

---

Binlog -- 二进制文件

• Binlog 是逻辑日记，用于记录数据库执行的写入操作（查询不记录）信息，Server 层记录和引擎层无关，并且是以追加方式进行写入
• 可以通过参数 max_binlog_size 设置每个 Binlog 文件的大小，文件大小达到设定值时会生成新的文件来保存日记。
  作用:
• 主从复制场景：在 Master 主端开启 Binlog，将 Binlog 发生到各个 Slave 从端，Slave 从端重放 Binlog 从而达到主从数据一致
• 数据恢复场景：通过使用 mysqlbinlog 工具来恢复数据

Undo log -- 事务日记

Undo log是 逻辑日记 、回滚日记。比如一条修改 +3 的逻辑语句，Undo log 会记录对应一条 -3 的逻辑日记，一条插入语句则会记录一条删除语句，这样发生错误时，根据执行 Undo log 就可以回滚到事务之前的数据状态。
作用:

• 回滚数据：当程序发生异常错误时等，根据执行 Undo log 就可以回滚到事务之前的数据状态，保证原子性，要么成功要么失败。
• MVCC 一致性视图：通过 Undo log 找到对应的数据版本号，是保证 MVCC 视图的一致性的必要条件。