MySQL 事务院里与优化
2023-01-17 16:31:26 9 举报AI智能生成
登录查看完整内容
MySQL 事务院里与优化
作者其他创作
大纲/内容
锁是计算机协调多个进程或线程并发访问某一资源的机制（保证系统数据访问的一致性）
InnoDB和MyISAM都支持表锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
表锁
InnoDB支持行锁，MyISAM不支持行锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
行锁
锁介绍
原子性（Actomicity）：一个事务是一个原子操作单元，要么全部执行成功，要么执行失败（由undo_log保证）
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（最终共同保证数据的一致性）
隔离性（Isolation）：数据库系统提供了一定的隔离机制，保证事务不受外界并发操作的影响“独立”执行（由锁机制保证）
持久性（Durable）：事务完成之后，对于数据的修改是永久性的，即使出现系统故障也不会发生改变（由redo_log保证）
支持事务（ACID）
脏读（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…查询数据，因为普通查询没有任何锁机制
又称读锁；获取行数据的共享锁的事务可以进行行数据的读取，但是不能修改；其他事物也可以获取该行数据的共享锁，但是不能获取排他锁
事务a对数据对象data加了S锁，则事务a可以读data但不能修改data；其他事务只能再对data加S锁，而不能加X锁，直到事务a释放data上的S锁；这保证了其他事务可以读data，但在事务a释放data上的S锁之前不能对data做任何修改
共享锁（S）
又称写锁；允许获取排他锁的事务进行读写数据，阻止其他事务获取相同数据集的读锁和写锁
若事务a对数据对象data加上X锁，事务a可以读data也可以修改data；其他事务不能再对data加任何锁，直到a释放data上的锁为止
排他锁（X）
\tInnoDB的行锁是通过给索引项加锁实现的，这种行锁的实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁
1、创建无索引的表
set autocommit=0;
2、关闭自动提交事务
线程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才能返回结果
②加写锁
3、两个线程操作
alter table tab1 add index idx_id(id);
4、给tab1的id列加索引
线程b： select * from tab1 where id = 2 for update; -- 查询成功
a，b线程分别查询不同记录加锁，查询成功，说明有索引加的行锁
加写锁
5、两个线程操作
结论：查询列带索引，加行锁；不带锁，加表锁。
案例一
线程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; --操作成功
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; --操作成功
线程b
某一线程持有一数据行的写锁，其他线程对该数据行加锁（读锁，写锁）都会进行阻塞等待，可以操作其他数据
②写锁
select * from tab1 where id=1 and name='hhhh' for update; --操作成功
线程a
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; --操作成功
先加读锁，再加写锁
先加写锁，再加读锁
同一线程可以对同一数据反复加锁（读锁、写锁）
④同一线程加锁
案例二
锁
指定大小的内存来缓存数据和索引，最大可以设置为物理内存的80%
innodb_buffer_pool_size
主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2
innodb_flush_log_at_trx_commit
设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
innodb_thread_concurrency
此参数确定日志文件所用的内存大小，以M为单位
innodb_log_buffer_size
此参数确定数据日志文件的大小，以M为单位
innodb_log_file_size
以循环方式将日志文件写到多个文件中
innodb_log_files_in_group
mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_buffer_size
mysql随机读的缓冲区大小
read_rnd_buffer_size
此参数确定为每张表分配一个新的文件
innodb_file_per_table
mysql数据页的大小（16K）
font color=\
配置
InnoDB
锁机制
Multi-Version Concurrency Control，多版本并发控制
MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存
MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发能力，用更好的方式去处理读写冲突，在有读写冲突时，即使不加锁也能进行非阻塞的并发读操作
MVCC介绍
为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
当前读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC；
快照读可能读到的数据并不一定是最新版本的，有可能是之前的历史版本
快照读可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；
快照读
准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这一个概念；这仅仅是一个理想概念
在MySQL中，快照读就是MySQL实现MVCC理想模型的一个具体非阻塞读功能的落地实现
快照读本身也是一个抽象概念，MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的
与MVCC的关系
当前读与快照读
不存在问题，不需要进行并发控制
读-读
存在并发安全问题，会产生数据一致性问题，发生脏读、幻读、不可重复读
读-写
存在并发安全问题，会产生数据一致性问题，数据更新丢失
写-写
数据库并发场景
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照数据
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决数据更新丢失问题
MVCC带来的好处
认识MVCC
MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_TRX_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
DB_ROW_ID
7byte，回滚指针，用于配合undo日志，指向上一个旧版本（存储于rollback segment里）
DB_ROLL_PTR
三个字段
insert undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
update undo log
为了实现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可见，那么这条记录一定是可以被安全清除的。
purge
undo log
Read View
实现原理
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是为了实现事务的持久性，是innodb存储引擎级别的日志
当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录更新到磁盘中
redo log是固定大小的，是循环写的过程； 有了redo log之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe
redo log的保存分为两个阶段（prepare和commit）
redo log
binlog是MySQL server服务端的日志级别，主要记录mysql功能层面的日志
redo log是innodb独有的，binlog是所有引擎都可以使用的
redo log是物理日志，记录的是在某个数据页上做了什么修改；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都没有提交成功，保证了数据恢复时的一致性
数据更新的流程
日志
MVCC
MySQL 事务原理与优化
0 条评论
回复 删除
下一页
