数据库第五天

第八章 MySQL事务和SQL优化
8.1 什么是事务
Transaction，一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成

以银行转账为例：账户转账是一个完整的业务，最小的单元，不可再分——也就是说银行账 户转账是一个事务：

update t_act set balance=balance - 400 where actno=1;
update t_act set balance=balance + 400 where actno=2;

以上两台DML语句必须同时成功或者同时失败。最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务。

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；

要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

8.2 事务的特性
事务是由一组SQL语句 组成的逻辑处理单元，它的ACID特性如下：

**原子性（Atomicity）：**原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

**一致性（Consistency）：**事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

**隔离性（Isolation）：**事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

**持久性（Durability）：**持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

8.3 MySQL使用事务
MySQL开启事务、回滚事务、提交事务命令。`

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

自动提交策略：

MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交。`

db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;


注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

(1)
set autocommit=0;   

(2)
vim /etc/my.cnf
autocommit=0  

隐式提交语句

用于隐式提交的 SQL 语句：
begin 
a
b
begin
SET AUTOCOMMIT = 1

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

开始事务流程：

1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
#个别版本不支持
show variables like 'autocommit';

2、开启事务,并结束事务

BEGIN;
DELETE FROM student WHERE NAME='alexsb';
UPDATE student SET NAME='alexsb' WHERE NAME='alex';
ROLLBACK;

BEGIN;
DELETE FROM student WHERE NAME='alexsb';
UPDATE student SET NAME='alexsb' WHERE NAME='alex';
COMMIT;

8.4 InnoDB 事务的ACID如何保证
8.4.1 基本概念

redo log ---> 重做日志 ib_logfile0~1   50M   , 轮询使用

redo log buffer ---> redo内存区域

ibd     ----> 存储 数据行和索引 

buffer pool --->缓冲区池,数据和索引的缓冲

LSN : 日志序列号 

磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
ACSR（Auto Crash Safey Recovery）自动的故障安全恢复

8.4.2 redo log
Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。

作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

redo日志位置
redo的日志文件：iblogfile0 iblogfile1

redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号

LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog

redo的刷新策略

commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

MySQL CSR——前滚`

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一:
我们做了一个事务,begin;update;commit.
1. 在begin ,会立即分配一个TXID=tx_01.
2. update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3. DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4. LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6. 假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7. MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是
LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正常启动

以上的工作过程,我们把它称之为基于REDO的"前滚操作"

8.4.3 undo 回滚日志
undo是什么？

undo,顾名思义“回滚日志”
1
作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含 3 个隐藏列

db_row_id：隐藏的行 ID。在没有自定义主键也没有 Unique 键的情况下，会使用该隐藏列作为主键。

db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。

db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。Undo Log 中存储了回滚需要的数据

8.5 事务的隔离级别
多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。

如果不考虑隔离性，可能会引发如下问题：

1、幻想读

2、不可重复读取

3、脏读

8.5.1 脏读
指一个事务读取了另外一个事务未提交的数据。

这是非常危险的，假设Ｂ向Ａ转帐100元，对应sql语句如下所示
1. update account set money=money+100 where name=‘a’;    
2. update account set money=money-100 where name=‘b’;
————————————————
版权声明：本文为CSDN博主「WHearTBeat」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/WHearTBeat/article/details/127576100

解决 : 串行化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3JH2spou-1666950266200)(D:\微服务\Java课程\鸥鹏标准化\鸥鹏MySql数据库标准化\mysql-images\2022-10-25_173507.png)]

8.5.2 不可重复读
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B3iHabcj-1666950266200)(D:\微服务\Java课程\鸥鹏标准化\鸥鹏MySql数据库标准化\mysql-images\2022-10-25_172402.png)]

8.5.3 虚读/幻读
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

如：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。

幻读和不可重复读的区别：
不可重复读：是同一条SQL查询的内容不同（被修改了）

幻读是：查询的结果条数不同（增加了、或者删除了记录）

幻读的影响
会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。

产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过binlog恢复的数据是会将所有符合条件的目标行都进行变更的。

幻读产生的原因
行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

如何解决幻读
将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。
8.5.4 事务的隔离级别
为了处理这些问题，SQL标准定义了以下几种事务隔离级别
数据库共定义了四种隔离级别：

Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）

Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）

Read committed：可避免脏读情况发生（读已提交）。

Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

可以通过命令 set transaction命令设置事务隔离级别：

语法 : set transaction isolation level 设置事务隔离级别

select @@tx_isolation   查询当前事务隔离级别

SET  TRANSACTION ISOLATION LEVEL SERIALIZABLE;

select @@tx_isolation

SET  TRANSACTION ISOLATION LEVEL REPEATABLE READ;

select @@tx_isolation

8.6 MySQL优化方式

系统优化：硬件、架构

服务优化
应用优化
影响性能的因素

应用程序

查询

事务管理

数据库设计

数据分布

网络

操作系统

硬件

谁参与优化`

数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员

1、系统优化

1、硬件优化
cpu 64位 一台机器8-16颗CPU
内存 96-128G 3-4个实例
硬盘：数量越多越好 性能：ssd（高并发业务） > sas （普通业务）>sata（线下业务）
raid(独立磁盘冗余阵列) 性能 raid0 > raid10 > raid5 > raid1
网卡：多块网卡bond
2、软件优化
操作系统：使用64位系统
软件：MySQL 编译优化

SATA
SATA的全称是Serial Advanced Technology Attachment，是由Intel、IBM、Dell、APT、Maxtor和Seagate公司共同提出的硬盘接口规范。

SAS
从接口标准上而言，SATA是SAS的一个子标准，因此SAS控制器可以直接操控SATA硬盘，但是SAS却不能直接使用在SATA的环境中，因为SATA控制器并不能对SAS硬盘进行控制

SSD
SSD是摒弃传统磁介质，采用电子存储介质进行数据存储和读取的一种技术，突破了传统机械硬盘的性能瓶颈，拥有极高的存储性能，被认为是存储技术发展的未来新星。

RAID是英文Redundant Array of IndependentDisks的缩写，中文简称为。RAID就是一种由多块硬盘构成的冗余阵列。虽然RAID包含多块硬盘，但是在操作系统下是作为一个独立的大型存储设备出现。RAID4即带奇偶校验码的独立磁盘结构

2、服务优化

MySQL配置原则
配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用
针对 MyISAM 或 InnoDB 不同引擎进行不同定制性配置
针对不同的应用情况进行合理配置
针对 my.cnf 进行配置，后面设置是针对内存为2G的服务器进行的合理设置