MySQL-事务、日志

事务

特性

原子性

是指事务开始后，必须成功执行完所有的操作才会结束，否则会回滚到事务刚开始前。

拿转账来说，一个成功的 A向B转账100元的过程 会涉及如下过程：

A：从数据库读取A的余额；A的余额-100；将A修改后的余额更新到数据库里

B：从数据库读取B的余额；B的余额+100；将B修改后的余额更新到数据库里

 若这个步骤任何一个没有执行成功，A向B转账100就无法成功，A的余额与B的余额都不会变化。

一致性

是指事务操作前后，数据库保持一致状态。

比如,用户A和用户B的账户分别有800元和600元，总共1400，用户A向用户B转账100元，分为两个步骤，从A账户扣除100元，B账户增加100元。一致性要求转账操作完成后，两个人账户总额依然是1400。

拿 A向B转账100元来说，执行完后，A的余额会减100，B的余额会加100，不会出现A的余额不变化等错误。

持久性

事务的改变是持久的，事务处理结束后，对数据的修改时永久的，即便系统故障也不会丢失。

隔离性

数据库允许多个并发事务同时对其数据进行读写和修改，隔离性很好的防止了多个事务并发执行而导致数据不一致的情况。每个事务都有一个完整的数据空间，对其他并发事务是隔离的，不会相互干扰；一个事务对另一个事务是否可见以及可见的程度。

购买商品来举例，消费者购买商品这个事务，是不影响其他消费者购买的。

并行事务回引发哪些问题

脏读

如果一个 事务 读到 另一个未提交事务 修改过的数据，就意味着发生脏读现象。

举例：如果一个B事务读到了另一个 未提交的A事务 修改过的数据，会发生脏读现象，因为A事务还未提交，随时可能发生回滚操作，那么B事务刚才读到的数据就是过期的数据。

不可重复读：

在一个事务内多次读取同一个数据，会出现前后两次读到的数据不一样的情况，就意味着发生了不可重复读现象。

举例：事务A从数据库中读取余额，读到的数据是100，然后继续执行代码逻辑，此时，B更新了余额（设置余额为200），并提交了事务，那么当A事务再次读取数据时，就会发现前后两次读到的数据不一致，这种现象被称为不可重复读。

幻读

在一个事务内，多次查询某个符合查询条件的记录数量，如果出现前后两次查询的记录数量不一样的情况，就意味着发生了幻读现象。

举例：事务B在查询账户余额大于100元的账户数量，查询到5条记录，接下来，事务A插入了一条余额超过100元的账户，并提交了事务，此时数据库超过100的账户数量为6，然后B事务再次查询账户余额大于100元的记录，此时查到的记录数量为6，发现和前一次读到的记录数量不一样，就感觉发生了幻觉，这种现象被称为幻读。

事务隔离

当多个事务并发执行肯可能会遇到脏读、不可重复读、幻读现象，如何规避呢？

• 脏读：B事务读到了A事务修改后未提交的数据
• 不可重复读：同一个事务内，前后读取的数据不一致
• 幻读：事务读取数据库中的前后记录数量不一样

解决脏读              :用读已提交、可重复读、串行化；

解决不可重复读   :用可重复读、串行化

解决幻读              :用串行化

四种隔离级别读未提交、串行化、读已提交、可重复读

四种隔离级别是如何实现的

读未提交

定义：指一个事务还没提交时，它做的变更就被其他事务看到；

实现：因为可以读到未提交事务的修改，所以直接读取最新的数据；

可能发生"脏读、不可重复读、幻读"问题：

• 脏读：B事务读到了A事务修改后未提交的数据
• 不可重复读：同一个事务内，前后读取的数据不一致
• 幻读：事务读取数据库中的前后记录数量不一样

产生问题举例——读未提交产生脏读

上图中，事务A与事务B开启后，事务B将name改为“关羽”，事务未提交，但是如果事务A读到了name为“关羽”，而sessionB未提交随时可能发生回滚，那么事务A相当于读到了一个不存在的数据，即脏读。读到了一条数据。

串行化

定义：会对事务加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

实现：通过加读写锁的方式来避免并行访问；

读已提交

定义：指一个事务提交后，它做的变更才能被其他事务看到；

实现：通过Read View实现，是在每个语句执行前都会重新生成Read View;同一个事务中两次相同的读取操作可能会看到不同的数据，因为其他事务可能在两次读取之间提交了数据。

过程：

1. 去找除本身事务之外的其他活跃的事务ID（read view）
2. 遍历undo log

 Read View:

确定哪些版本的数据对当前事务可见，维护了除本身事务之外的其他活跃的事务ID，看到的是未提交（活跃）的事务；

• 事务ID列表：包括了在创建 read view 时，所有未提交的事务ID
• 最小事务ID:在创建 read view 时，系统中已经提交的最小事务ID。这个ID之前的所有事务的修改对当前事务都是可见的。
• 当前事务ID:创建 read view 的事务的ID。

可能发生不可重复读和幻读的问题：

• 不可重复读：同一个事务内，前后读取的数据不一致
• 幻读：事务读取数据库中的前后记录数量不一样

 产生问题举例——读已提交产生不可重复读

在图，SessionA启动后，Session第一次读到name值是“刘备”；SesssionB自动启动后，修改name为“关羽”，并自动提交了事务，SessionA第二次读到name值是“关羽”，两次读到的数据不一样，发生了不可重复读；SessionB再次更新name,为“张飞”，并自动提交了事务，SessionA第三次读到的name值为“张飞”，对于同一个name，SessionA三次读到的值都不一样，发生了不可重复读问题。

 产生问题举例——读已提交产生幻读

 在图中，SessionA启动后，查到了name为“刘备”的记录，之后SessionB插入了name为“曹操”的记录，并自动提交了事务，session再次查找记录，在与第一次相同的条件（number>0）下，读到了name为“刘备”和“曹操”两条记录。

可重复读

定义：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的；

实现：启动事务时生成一个Read View，然后整个事务期间都在用这个Read View，之后读取操作都会使用这个read view,保证了在同一个事务中多次读取同一数据时，看到相同的数据版本。

过程：

1. 去找除本身事务之外的其他活跃的事务ID（read view）
2. 遍历undo log

 Read View

确定哪些版本的数据对当前事务可见，维护了除本身事务之外的其他活跃的事务ID，看到的是未提交（活跃）的事务；

• 事务ID列表：包括了在创建 read view 时，所有未提交的事务ID
• 最小事务ID:在创建 read view 时，系统中已经提交的最小事务ID。这个ID之前的所有事务的修改对当前事务都是可见的。
• 当前事务ID:创建 read view 的事务的ID。

可能发生“幻读”问题：

• 幻读：事务读取数据库中的前后记录数量不一样

 Read View

确定哪些版本的数据对当前事务可见，维护了除本身事务之外的其他活跃的事务ID，看到的是未提交（活跃）的事务；

• 事务ID列表：包括了在创建 read view 时，所有未提交的事务ID
• 最小事务ID:在创建 read view 时，系统中已经提交的最小事务ID。这个ID之前的所有事务的修改对当前事务都是可见的。
• 当前事务ID:创建 read view 的事务的ID。

可重复读与已提交的工作过程

1. 去找除本身事务之外的其他活跃的事务ID（read view）
2. 遍历undo log

举例

读已提交：每次都重复找

可重复读：延用第一次的

 日志

undo log回滚日志

是innodb存储引擎层生成的日志，实现了事务的原子性，主要用于事务回滚；记录了数据被修改之前的状态，以便在事务回滚或者需要读取旧版本数据时能够恢复到之前的状态；发生回滚时，就读取undo log里的数据，然后做原先相反的操作。

每次操作产生的undo log格式都有roll_pointer指针可以将这些undo log串成一个链表，trx_id知道该记录时被哪个事务修改的。

 插入操作：在插入一条记录时，把这条记录的主键值记到undo log中，这样回滚时只需把这个主键值对应的记录删掉就好。

删除操作：删除一条记录时，要把记录中的内容对记到undo log中，回滚时把由这些内容组成的记录（通过roll_pointer找到之前版本中delete_mask为0的记录）插入到表中就好。

更新操作：在更新一条记录时，把更新列的旧值记到undo log中，回滚时把这些列的更新为旧值（通过roll_pointer找到上一个版本的数据（旧值））就好了。每次修改时，都会生成一个新的undo log记录，并且这个指针会通过roll_pointer指针与之前的undo log记录相连，形成一个版本链,通过roll_pointer找到上一个版本的数据。

redo log重做日志：是innodb存储引擎层生成的日志，实现了事务的持久性，主要用于解决掉电等故障恢复；

binlong归档日志：是server层生成的日志，主要用于数据备份和主从复制

索引的类别

按照实现的数据结构区分

B+树索引

Hash索引

为什么不采用Hash/HashMap这种存储结构而选择B+树呢？

B+树支持按顺序存储和范围查询，而hash结构不支持范围查询，因为Hash是基于Hash函数计算的无序存储结构；B+树的内部节点和叶子节点形成有序链表，这使得在执行顺序访问时非常高效，Hash机构没有内在顺序，无法提供顺序访问性能。

按照约束区分

普通索引

唯一索引(UNIQUE修饰)

MySQL在进行插入操作时，普通索引和唯一索引哪个的效率高？

普通索引更快，因为它不需要唯一性检查。普同索引允许索引键值重复，而唯一索引不允许重复，用唯一索引插入时会检查索引键值是否重复，不重复才插入，若重复则失败。

MySQL在进行查找操作时，普通索引和唯一索引哪个的效率高？

唯一索引更快，保证数据唯一性使得查找的时候索引通过键就能定位符合条件数据行，而普通索引的索引键存在重复，会定位多个数据行，接下来还要遍历数据才能查找到符合条件的数据行。

按照索引列的数量区分

单列索引

联合索引（最左匹配原则）

最左匹配原则是按什么顺序实现的？

从小到大的顺序，如按第一列从小到大的顺序排列，若出现相同的数值，则比较相同数值的下一列，依然是按照从小到大的顺序，后面同理。

联合索引为什么要满足最左匹配原则？

如（A,B,C），从A字段开始匹配才能保证索引的有序性。对于查询，如果跳过A字段直接查询B或C字段，那么这些字段在索引中可能是无序的，从而加速查询过程。

按照存储的内容区分

聚簇索引：B+树的叶子结点存放的是实际数据，索引就是数据，以主键为索引；

非聚簇索引：B+书的叶子结点存放主键值，存储了索引列与主键，除主键外的其他字段为索引；通过非聚簇索引找到了符合条件的数据行，根据存储在索引中的主键，再去访问实际的数据行，这个过程被称为“回表”。

回表：需要检索两颗B+树，先在二级索引的B+树找到对应的叶子结点，获取主键值，然后用获取的主键值在聚簇索引中的B+树检索到对应的叶子结点，然后获取要查询的数据；

索引注意事项

1.ORDER BY子句里使用索引列，但是order by后字段的排序不一致（增减性相反索引失效）就不能使用；

2.为用于搜索、排序或分组的列创建索引；

3.列的区分度大的列创建索引，重复数据多的字段不应设为索引

4.联合索引，区分度大的放在第一位

5.只有索引列在比较表达式中单独出现才可以适用索引

6.为了尽可能少的让聚簇索引发生 页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性，让主键自增，防止页分裂（会使插入速度变慢）。

7.尽量使用覆盖索引进行查询，避免回表带来的性能损耗

8.使用IN查询，IN的数量不能太大（<=2000，若大于，则全表扫描就可以）,避免mysql走错索引

9.更新频繁的列不应设置索引（索引也需要维护）

索引失效

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。