

首页  思维导图  详情



MySQL 事务院里与优化

2023-01-17 16:31:26   11  举报





AI智能生成

MySQL 事务院里与优化

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');

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都没有提交成功，保证了数据恢复时的一致性

 Collect

Get Started

MySQL事务大纲

 Collect

Get Started

MYSQL(初级)

 Collect

Get Started

摘要优化

 Collect

Get Started

mysql 优化导图





0 条评论

下一页