MySQL 事务院里与优化
2023-01-17 16:31:26 10 举报AI智能生成
MySQL 事务院里与优化
作者其他创作
大纲/内容
锁机制
锁介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制（保证系统数据访问的一致性）
表锁
InnoDB和MyISAM都支持表锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行锁
InnoDB支持行锁，MyISAM不支持行锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
InnoDB
支持事务（ACID）
原子性（Actomicity）：一个事务是一个原子操作单元，要么全部执行成功，要么执行失败（由undo_log保证）
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（最终共同保证数据的一致性）
隔离性（Isolation）：数据库系统提供了一定的隔离机制，保证事务不受外界并发操作的影响“独立”执行（由锁机制保证）
持久性（Durable）：事务完成之后，对于数据的修改是永久性的，即使出现系统故障也不会发生改变（由redo_log保证）
并发事务
问题
脏读（Dirty Read）：A事务在执行过程中，B事务读取了A事务修改后的数据；但是由于某些原因，A事务进行了RollBack，则B事务所读取的数据就是脏数据
不可重复读（non-repeatable Read）：B事务读取了两次数据，在这两次读取过程中A事务修改了数据，造成B事务两次读取的数据不一样，即不可重复读
幻读：B事务读取了两次数据，在这两次读取过程中A事务添加了数据，造成B事务这两次读取出来的集合不一样，即幻读
问题分析
上述问题都是有关于数据库读一致性的问题，可以通过事务的隔离机制来保证；
数据库的事务隔离级别越高，并发副作用就越小，但付出的代价也就越大
因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别
隔离级别
read uncommitted（读未提交）：允许一个事务读取另一个事务未提交的数据，可能会发生脏读，幻读，不可重复读（不建议使用）
read committed（读已提交）：一个事务只能读取另一个事务已经提交的数据，可以避免脏读；可能发生幻读、不可重复读
repeatable read（可重复读）：一个事务可以多次执行某一查询操作，并且每次的返回结果相同；可以避免脏读、不可重复读，可能发生幻读
serializable（串行化）：每个事务都有序的进行，事务之间互相不干扰，可以防止脏读，幻读，不可重复读，但是会影响系统的效率。
锁
mysql InnoDB引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁
可以使用select …for update（加排他锁），使用select … lock in share mode（加共享锁）；所以加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制
共享锁（S）
又称读锁；获取行数据的共享锁的事务可以进行行数据的读取，但是不能修改；其他事物也可以获取该行数据的共享锁，但是不能获取排他锁
事务a对数据对象data加了S锁，则事务a可以读data但不能修改data；其他事务只能再对data加S锁，而不能加X锁，直到事务a释放data上的S锁；这保证了其他事务可以读data，但在事务a释放data上的S锁之前不能对data做任何修改
排他锁（X）
又称写锁；允许获取排他锁的事务进行读写数据，阻止其他事务获取相同数据集的读锁和写锁
若事务a对数据对象data加上X锁，事务a可以读data也可以修改data；其他事务不能再对data加任何锁，直到a释放data上的锁为止
InnoDB的行锁是通过给索引项加锁实现的，这种行锁的实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁
案例一
1、创建无索引的表
create table tab1(id int,name varchar(10)) engine=innodb;
insert into tab1 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
insert into tab1 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
2、关闭自动提交事务
set autocommit=0;
3、两个线程操作
①不加锁
线程a： select * from tab1 where id = 1; -- 查询成功
线程b：select * from tab1 where id = 2; -- 查询成功
②加写锁
线程a： select * from tab1 where id = 1 for update; -- 查询成功
线程b： select * from tab1 where id = 2 for update; -- 查询堵塞
b线程查询id=2被阻塞，说明无索引加的表锁，a线程提交事务b才能返回结果
4、给tab1的id列加索引
alter table tab1 add index idx_id(id);
5、两个线程操作
加写锁
线程a： select * from tab1 where id = 1 for update; -- 查询成功
线程b： select * from tab1 where id = 2 for update; -- 查询成功
a，b线程分别查询不同记录加锁，查询成功，说明有索引加的行锁
结论：查询列带索引，加行锁；不带锁，加表锁。
案例二
①读锁
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
线程b：select * from tab1 where id=1 lock in share mode; --操作成功
某一线程持有一数据行的读锁，其他线程也可以对该数据行加读锁
②写锁
线程a：select * from tab1 where id=1 for update; --操作成功
线程b
select * from tab1 where id=1 for update; --阻塞等待
select * from tab1 where id=1 lock in share mode; --阻塞等待
select * from tab1 where id=2 for update; --操作成功
某一线程持有一数据行的写锁，其他线程对该数据行加锁（读锁，写锁）都会进行阻塞等待，可以操作其他数据
③对索引加锁再理解
插入相同id不同name的数据（id列带索引）：insert into tab1 values(1,'hhhh'),(1,'kkkk');
线程a
select * from tab1 where id=1 and name='hhhh' for update; --操作成功
线程b
select * from tab1 where id=1 and name='kkkk' for update; --阻塞等待
虽然查询的不是同一数据，但是查询使用的是同一索引，对索引加锁，所以线程b会阻塞等待
④同一线程加锁
先加读锁，再加写锁
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
线程a：select * from tab1 where id=1 for update; --操作成功
先加写锁，再加读锁
线程a：select * from tab1 where id=1 for update; --操作成功
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
同一线程可以对同一数据反复加锁（读锁、写锁）
配置
innodb_buffer_pool_size
指定大小的内存来缓存数据和索引，最大可以设置为物理内存的80%
innodb_flush_log_at_trx_commit
主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2
innodb_thread_concurrency
设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
innodb_log_buffer_size
此参数确定日志文件所用的内存大小，以M为单位
innodb_log_file_size
此参数确定数据日志文件的大小，以M为单位
innodb_log_files_in_group
以循环方式将日志文件写到多个文件中
read_buffer_size
mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
mysql随机读的缓冲区大小
innodb_file_per_table
此参数确定为每张表分配一个新的文件
innodb_page_size
mysql数据页的大小（16K）
MVCC
认识MVCC
MVCC介绍
Multi-Version Concurrency Control，多版本并发控制
MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存
MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发能力，用更好的方式去处理读写冲突，在有读写冲突时，即使不加锁也能进行非阻塞的并发读操作
说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
当前读与快照读
当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读
为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC；
快照读可能读到的数据并不一定是最新版本的，有可能是之前的历史版本
快照读可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；
与MVCC的关系
准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这一个概念；这仅仅是一个理想概念
在MySQL中，快照读就是MySQL实现MVCC理想模型的一个具体非阻塞读功能的落地实现
快照读本身也是一个抽象概念，MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的
数据库并发场景
读-读
不存在问题，不需要进行并发控制
读-写
存在并发安全问题，会产生数据一致性问题，发生脏读、幻读、不可重复读
写-写
存在并发安全问题，会产生数据一致性问题，数据更新丢失
MVCC带来的好处
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照数据
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决数据更新丢失问题
实现原理
MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的
三个字段
DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
DB_ROLL_PTR
7byte，回滚指针，用于配合undo日志，指向上一个旧版本（存储于rollback segment里）
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了
undo log
insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
purge
为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。
为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;
如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
Read View
Read View（读视图）是事务进行快照读操作的时候产生的读视图(Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
Read View主要是用来做可见性判断的, 即当某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它作为条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
日志
undo log
undo log是为了实现事务的原子性，是innodb存储引擎级别的日志，用undo Log来实现多版本并发控制(MVCC)
在写数据之前，先将数据备份到undo log中，然后再进行写操作，如果出现了错误或者执行了ROLLBACK，系统可以利用undo log将数据恢复到事务开始之前的状态
可以理解为：当delete时，undo log中会记录对应的insert；当insert时，undo log中会记录对应的delete；当update时，undo log中记录对应相反的update
redo log
redo log是为了实现事务的持久性，是innodb存储引擎级别的日志
当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录更新到磁盘中
redo log是固定大小的，是循环写的过程； 有了redo log之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe
redo log的保存分为两个阶段（prepare和commit）
binlog
binlog是MySQL server服务端的日志级别，主要记录mysql功能层面的日志
与redo log的区别
redo log是innodb独有的，binlog是所有引擎都可以使用的
redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑
redo log是循环写的，会覆盖旧的信息，空间会用完；binlog是可以追加写的，不会覆盖之前的日志信息
binlog中会记录所有的逻辑，并且采用追加写的方式，一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置
恢复数据的过程：找到最近一次的全量备份数据；从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻
数据更新的流程
1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
2、执行器拿到数据之后会先修改数据，然后调用引擎接口重新吸入数据
3、引擎将数据更新到内存，同时写数据到redo log中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
4、执行器生成这个操作的binlog
5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo log改成commit状态，更新完成
若在commit没有发生，redo log和binlog都没有提交成功，保证了数据恢复时的一致性
0 条评论
下一页
为你推荐
查看更多
