MySQL优化之事务基础，实战优化的基础知识

MySQL的逻辑架构

第一层不是mysql独有的：如连接处理、授权认证、安全，第三方服务工具等等。

第二层mysql核心功能：如包括查询解析、分析、优化、缓存以及所有的内置函数。

第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。

MySQL主键自增机制

1. MyISAM引擎的自增值保存在数据文件中

2. InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。

每次重启后，第一次打开表时，都会去找自增值的最大值max(id)，将max(id)+步长作为这个表当前的自增值

select max(ai_col) from table_name for update;

在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

这也就是添加失败，事务回滚时(主键不事务化)，主键出现断层。日志中的自增机制是只增不减。

MySQL关键词

1)幻读：事务1读取，事务2添加并提交，事务1再次读取时无法看到事务2新增的记录， 但却客观存在；
        
2)不可重复读取：事务1读取记录时，事务2更新了记录并提交，事务1再次读取时可以看到事务2修改后的记录；
          
3)脏读：事务1更新了记录，但没有提交，事务2读取了更新后的行，然后事务T1回滚，现在T2读取无效。

4)更新丢失:当两个事务选择同一行，然后更新数据，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，（你我同时读取同一行数据，进行修改，你commit之后我也commit，那么我的结果将会覆盖掉你的结果）。

什么是事务

官方解释为: 数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；

这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）

MySQL 创建事务的基础语法

隐式事务：事务没有明显的开启或者结束的标志，在mysql中，默认是开启自动提交的

查看事务：SHOW @@autocommit;

所以针对SELECT、UPDATE、DELETE、INSERT等DQL及DML语句的执行，mysql会自动提交该事务，如果关闭就需要手动提交或者回滚来完成操作。

显式事务：与隐式事务想反，有明显的开启或结束标志。
SET autocommit=0
[START TRANSACTION]  # 可选的语句
[DELETE | UPDATE | INSERT | SELECT ]   # DML DQL操作
[COMMIT | ROLLBACK];  # 提交或者回滚

显式事务：回滚点的使用(setpoint)
SET AUTOCOMMIT=0;
START TRANSACTION;
[DELETE | UPDATE | INSERT | SELECT];  #回滚时要执行提交的部分
SAVEPOINT a;  # 设置回滚点，且变量名为a
[DELETE | UPDATE | INSERT | SELECT];  #回滚时不执行提交的部分
ROLLBACK TO a;  # 回滚时与ROLLBACK TO搭配使用

一组事务必须满足四大特性

原子性(Atomicity) 一个事务中的SQL，要么全部执行，要么全部不执行；

一致性(Consistency) 一个事务执行前后，数据库中的所有约束依然满足；

隔离性(Isolation) 一个事务执行过程中，数据不受另一个事务的影响；

持久性(Durability) 一个事务执行完成后，事务对数据的修改必须持久化到数据库中。

MySQL事务的隔离级别

事务隔离级别.png

READ UNCOMMITTED（未提交读）
--事务中的修改，即使没有提交，对其他事务也都是可见

READ COMMITTED（已提交读/不可重复读）
--事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。重点修改

REPEATABLE READ（可重复读）
--同一事务中多次读取同样的记录结果是一致的，MySQL默认(会出现幻读) 重点新增或者删除

SERIALIZABLE（序列化）
--事务串行执行，这是最高的隔离级别，它强制事务串行执行，避免了前面说的幻读现象，简单来说，它会在读取的每一行数据上都加锁，所以可能会导致大量的超时和锁争用问题。

注意：隔离级别越严格，内部工作机制越复杂，较松散的隔离级别通常可以支持更高的并发。

MySQL事务机制原理与日志

事务的ACID是通过InnoDB日志和锁来保证。

隔离性是通过数据库锁的机制实现的。

持久性通过redolog（重做日志）来实现。

原子性和一致性通过Undolog来实现。

UndoLog日志，为了满足事务的原子性，在操作任何数据之前，首先将数据备份（这个存储数据备份的地方称为UndoLog）。

然后进行数据的修改，若出现错误或用户执行了rollback，系统可以利用UndoLog中的备份将数据恢复到事务开始之前的状态。

RedoLog记录的是新数据的备份。在事务提交前，只要将RedoLog持久化即可，不需要将数据持久化。

当系统崩溃时，虽然数据没有持久化，但RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

MySQL sql事务语法

show engines;
-- 查看数据库采用的存储引擎

show variables like '%storage_engine%';
-- 查看数据库默认采用的存储引擎

show create table 表名;
-- 查询某张表的存储引擎

修改表的存储引擎
1. ALTER TABLE tableName ENGINE = InnoDB
2. 导出导入，可使用mysqldump工具
3. CREATE and SELECT
    CREATE TABLE newTable LIKE oldTable;
    ALTER TABLE newTable ENGINE=InnoDB;
    INSERT INTO newTable SELECT * FROM oldTable;