一、 ACID
1.1 原子性(Atomicity)
原子性是指一个事务是一个不可分割的工作单位,其中的操作要么都做,要么都不做,如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。 InnoDB是靠undo log实现回滚,当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。undo log数据逻辑日志,它记录的是sql执行相关的信息。当回滚时,InnoDB会根据undo log做与之相反的操作,对于insert会执行delete,对于update会执行与之相反的update(当事务执行update时,其生成的undo log会包含被修改行的主键、修改了哪些列、修改前的值等以便于回滚)。1.2 一致性(Consistency)
一致性指的是事务不能破坏数据的完整性(实体完整性、列完整性、外键约束等)和业务的一致性(事务的一致性操作)。从数据库层面,数据库通过原子性、隔离性、持久性来保证一致性,C(一致性)是目的,A(原子性)、I(隔离性)、D(持久性)是手段,是为了保证一致性,数据库提供的手段。1.3 隔离性(Isolation)
隔离性是指事务内部的操作与事务是隔离的,并发执行的各个事务之间不能互相干扰。(一个事务)写操作对(另一个事务)写操作的影响:锁机制保证隔离性;(一个事务)写操作对(另一个事务)读操作的影响:MVCC保证隔离性。a>锁机制
锁机制的基本原理可以概况为:事务在修改数据之前,需要先获得相应的锁;获得锁之后,事务便可以修改数据;该事务操作期间,这部分数据是锁定的,其他事务如果想要修改该数据需要等当前事务提交或回滚后释放锁。b>隔离级别
读未提交(脏读)、读已提交(不可重复读)Oracle默认、可重复读(幻读)MySQL默认、串行化c>MVCC(MySQL InnoDB RR)
Read View:![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042348-2097316276.png)
- creator_trx_id:创建该Read View的事务id
- m_ids:在创建Read View时,当前数据库中活跃事务的事务id列表。其中“活跃事务”指启动了但还没有提交的事务
- min_trx_id:在创建Read View时,当前数据中活跃事务中事务id最小的事务,也就是m_ids中的最小值
- max_trx_id:在创建Read View时当前数据库中应该给下一个事务的id值,也就是全局事务中最大的事务id值+1
- trx_id:表示最近修改的事务id,每次一个事务对某条聚簇索引记录进行改动时,都会把该事务的事务id赋值给trx_id隐藏列。新增一个事务时,trx_id会递增,因此trx_id能够表示事务开始的先后顺序。
- roll_pointer:指向该行上一个版本的地址,每次对某条聚簇索引记录进行改动时,都会把旧的版本写入undo日志中,然后这个隐藏列相当于一个指针,可以通过它来找到该记录修改前的信息。
![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042178-2035756299.png)
- 如果记录的trx_id值小于Read View中的min_trx_id值,表示这个版本的记录在创建Read View前已经提交的事务生成,所以该版本的记录对当前事务可见
- 如果记录的trx_id值大于等于Read View中的max_trx_id值,表示这个版本的记录在创建Read View后才启动的事务生成,所以该版本的记录对当前事务不可见
- 如果记录的trx_id在min_trx_id和max_trx_id之间,需要判断trx_id是否在m_ids列表中:
- 如果trx_id在m_ids中,表示生成该版本记录的活跃事务依然活跃着(还没提交事务),所以该版本的记录对当前事务不可见
- 如果trx_id不在m_ids中,表示生成该版本记录的活跃事务已经被提交,所以该版本的记录对当前事务可见
- 针对快照读(普通 select 语句),是通过 MVCC 方式解决了幻读,因为可重复读隔离级别下,事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,即使中途有其他事务插入了一条数据,是查询不出来这条数据的,所以就很好了避免幻读问题。
- 针对当前读(select ... for update 等语句),是通过 next-key lock(记录锁+间隙锁)方式解决了幻读,因为当执行 select ... for update 语句的时候,会加上 next-key lock,如果有其他事务在 next-key lock 锁范围内插入了一条记录,那么这个插入语句就会被阻塞,无法成功插入,所以就很好了避免幻读问题。
1.4 持久性(Durability)
持久性是指事务一旦提交,它对数据库的改变就应该是永久的,接下来的其他操作或故障不应该对其产生影响。InnoDB是靠redo log实现持久性的。a>Buffer Pool
InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),Buffer Pool中包含了磁盘中部分数据页(默认16 KB)的映射, 作为访问数据库的缓冲。当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool中。当想数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。 Buffer Pool的使用大大提高了读写数据的效率,但是也带来了新的问题。如果MySQL宕机,而此时Buffer Pool 中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。b>redo log
redo log的出现就是为了解决InnoDB的持久性问题。当数据修改时,除了修改Buffer Pool中的数据,还会在redo log记录这次操作;当事务提交时,会调用fsync接口对redo log进行刷盘。如果MySQL宕机,重启时可以读取redo log中的数据,对数据进行恢复。redo log采用的是WAL(write ahead logging)预写日志,指的是 MySQL 的写操作并不是立刻更新到磁盘上,而是先记录在日志上,然后在合适的时间再更新到磁盘上。![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042074-1412726542.png)
二、 binlog
2.1 binlog的原理和流程
二进制日志(binlog):1.用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步;2.用于数据库的基于时间点的还原。 binlog刷盘流程:![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101041929-19568689.png)
- sync_binlog = 0 的时候,表示每次提交事务都只 write,不 fsync,后续交由操作系统决定何时将数据持久化到磁盘;
- sync_binlog = 1 的时候,表示每次提交事务都会 write,然后马上执行 fsync;
- sync_binlog =N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。
2.2 redo log 与 binlog的区别
- redo log是属于InnoDB层面,binlog属于MySQL Server层面的,这样在数据库用别的存储引擎时可以达到一致性的要求。
- redo log是物理日志,记录该数据页更新的内容;binlog是逻辑日志,记录的是这个更新语句的原始逻辑
- redo log是循环写,日志空间大小固定;binlog是追加写,是指一份写到一定大小的时候会更换下一个文件,不会覆盖。
- binlog可以作为恢复数据使用,主从复制搭建,redo log作为异常宕机或者介质故障后的数据恢复使用。
三、主从复制
3.1 主从复制的实现原理
![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042240-1839014138.png)
- MySQL主库在收到客户端提交事务的请求后,会先写入binlog,再提交事务,更新存储引擎中的数据,事务提交完成后,返回给客户端“操作成功”的响应
- 从库会创建一个专门的I/O线程,连接主库的log dump线程,来接收主库的binlog日志,再把binlog信息写入relay log的中继日志里,再返回给主库“复制成功”的响应
- 从库会创建一个用于回放日志的线程,去读relay log中继日志,然后回放更新存储引擎中的数据,最终实现主从的数据一致性。
3.2 主从复制从库是不是越多越好?
不是的。因为从库数量增加,从库连接上来的I/O线程也比较多,主库也要创建同样多的log dump线程来处理复制的请求,对主库资源消耗比较高,同时还受限于主库的网络带宽。3.3 主从复制的模型
- 同步复制:MySQL主库提交事务的线程要等待所有从库的复制成功响应,才返回客户端结果。问题:性能差,因为要复制到所有节点才返回响应;可用性差,主库和所有从库任何一个数据库除问题,都会影响业务
- 异步复制(默认模型):MySQL主库提交事务的线程并不会等待binlog同步到各从库,直接返回客户端。问题:主库宕机,数据会发生丢失
- 半同步复制:MySQL 5.7版本后增加的一种复制方式,介于二者之间,事务线程不用等待所有的从库复制成功响应,只要一部分复制成功响应即可。兼顾异步复制和同步复制的优点,即使出现主库宕机,至少还有一个从库为最新数据,不存在数据丢失的风险。
3.4 主从复制的延迟问题
- 主库DML请求频繁(TPS较大)
- 原因:主库并发写入数据,而从库SQL Thread为单线程应用日志,很容易造成relaylog堆积产生延迟。
- 思路:做sharding,打散写请求。考虑升级MySQL 5.7+,开启基于逻辑时钟的并行复制
- 主库执行大事务
- 原因:在主从库配置相近的情况下,主库花费很长时间更新一张大表,从库也需要花费几乎相同的时间更新,导致从库延迟堆积
- 思路:拆分大事务,及时提交
- 主库对大表执行DDL语句
- 原因:DDL未开始执行,被阻塞,SHOW SLAVE STATUS检查到Slave_SQL_Running_State为waiting for table metadata lock,且Exec_Master_Log_Pos不变;DDL正在执行,单线程应用导致延迟增减,Slave_SQL_Running_State为altering table,Exec_Master_Log_Pos不变。
- 思路:通过processlist或information_schema.innodb_trx来找到阻塞DDL语句的查询,干掉该查询,让DDL正常在从库执行。建议考虑:① 业务低峰期执行;②set sql_log_bin=0后,分别在主从库上手动执行DDL(此操作对于某些DDL操作会造成数据不一致,请务必严格测试)
- 主从实例配置不一致
- 原因:主库实例服务器使用SSD,而从库实例服务器使用普通SAS盘、cpu主频不一致等;如RAID卡写策略不一致,OS内核参数设置不一致,MySQL落盘策略不一致等
- 思路:尽量统一DB机器的配置(包括硬件及选项参数);甚至对于某些OLAP业务,从库实例硬件配置高于主库等
- 从库自身压力过大
- 原因:从库执行大量的select请求,或者从库正在备份等,此时可能造成cpu负载过高,io利用率过高等,导致SQL Thread应用过慢
- 思路:建立更多从库,打散读请求,降低现有从库实例的压力
- 网络延迟
- 网络通信状况也会影响主从复制(影响从库的IO线程获取主库binlog),只能升级带宽,优化网络。
四、两阶段提交
4.1 MySQL中两阶段提交的应用
redo log和binlog 都要持久化到磁盘,但是这两个独立的逻辑,可能出现半成功的状态,这样会造成两份日志之间的逻辑不一致。如果将redo log刷入磁盘后,MySQL宕机了,而binlog还没有来得及写入。MySQL重启,主库会更新而从库不会更新。如果将binlog刷入到磁盘后,MySQL宕机了,而redo log还没有来得及写入。MySQL重启,主库不会更新而从库会更新。 MySQL为了避免出现两份日志之间的逻辑不一致问题,使用了【两阶段提交】来解决。两阶段提交把单个事务的提交拆分成2个阶段,分别是【准备(Prepare)阶段】和【提交(commit)阶段】。MySQL的内部开启一个XA事务,分两阶段爱完成XA事务的提交。![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042193-1512981897.png)
- prepare阶段:将XID(内部XA事务的ID)写入到redo log,同时将redo log对应的事务状态设置为prepare,然后将redo log刷新到硬盘
- commit阶段:把XID写入到binlog,然后将binlog刷新到磁盘,接着调用InnoDB的提交事务接口,将redo log状态设置为commit(将事务设置为commit状态后,刷入到磁盘redo log文件,所以commit状态也是会刷盘的)
4.2 异常重启会出现什么现象?
- 如果 binlog 中没有当前内部 XA 事务的 XID,说明 redolog 完成刷盘,但是 binlog 还没有刷盘,则回滚事务。
- 如果 binlog 中有当前内部 XA 事务的 XID,说明 redolog 和 binlog 都已经完成了刷盘,则提交事务。
4.3 两阶段提交的问题
- 磁盘I/O次数高:对于“双1”配置( sync_binlog = 1、innodb_flush_log_at_trx_commit = 1),每个事务提交都会进行两次fsync(刷盘),一次是redo log刷盘,另一次是binlog刷盘
- 锁竞争激烈:两阶段提交虽然保证单事务两个日志的内容一致,但是在多事务的情况下,却不能保证两者的提交顺序一致,因此,在两阶段提交的流程基础上,还需要加一个锁来保证提交的原子性,从而保证在多事务的情况下两个日志的提交顺序一致。
4.4 组提交
Binlog 组提交![0](/i/l/?n=23&i=blog/1405595/202212/1405595-20221202101042273-704410821.png)
五、索引
5.1 索引的定义、数据结构和类型
5.2 索引的选择策略
#建表 CREATE TABLE `t` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `a` INT ( 11 ) NOT NULL, `b` INT ( 11 ) NOT NULL, PRIMARY KEY ( `id` ), KEY `a` ( `a` ) USING BTREE, KEY `b` ( `b` ) USING BTREE ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; #定义测试数据存储过程 delimiter; CREATE DEFINER = `root` @`10.%` PROCEDURE `idata` ( ) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 100000 DO INSERT INTO t ( id, a, b ) VALUES ( i, i, i ); SET i = i + 1; END WHILE; END delimiter; #执行存储过程,插入测试数据 call idata();
a>优化器目的
优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。优化器选择索引的依据有(或者说下面的条件都可能对选择产生影响):
- 扫描的行数(基于采样)
- 是否回表操作
- 是否使用临时表
- 是否使用排序或者文件排序
b>干预优化器
如果执行计划(explain)不是按照我们的预期执行,那么可以采用一些引导或强制措施:
- 使用 force index 强行选择一个索引。select * from t force index(索引名称) where ...
- 可以考虑修改语句,引导需要期望的结果。比如:order by b limit 1 -> order by b,a limit 1
- 在某些场景下可以新建一个更适合的索引,提供给优化器选择,或者删除可能误导的索引【与业务耦合度很高】
-- 查看执行计划,使用了字段a上的索引 mysql> explain select * from t where a between 10000 and 20000; +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | t | range | a | a | 4 | NULL | 10000 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ --由于需要进行字段b排序,虽然索引b需要扫描更多的行数,但本身是有序的,综合扫描行数和排序,优化器选择了索引b,认为代价更小 mysql> explain select * from t where (a between 10000 and 20000) and (b between 50000 and 100000) order by b limit 1; +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE index强制走索引a,纠正优化器错误的选择,不建议使用(不通用,且索引名称更变语句也需要变) mysql> explain select * from t force index (a) where (a between 10000 and 20000) and (b between 50000 and 100000) order by b limit 1; +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | t | range | a | a | 4 | NULL | 10000 | Using where; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ -- 方案2:引导 MySQL 使用我们期望的索引,按b,a排序,优化器需要考虑a排序的代价 mysql> explain select * from t where (a between 10000 and 20000) and (b between 50000 and 100000) order by b,a limit 1; +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | t | range | a,b | a | 4 | NULL | 10000 | Using where; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------------+ -- 方案3:有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引 ALTER TABLE `t` DROP INDEX `a`, DROP INDEX `b`, ADD INDEX `ab` (`a`,`b`) ;
c>采样原理
一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,称之为“基数”(cardinality)mysql> show index from t; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t | 0 | PRIMARY | 1 | id | A | 100302 | NULL | NULL | | BTREE | | | | t | 1 | a | 1 | a | A | 100302 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
从性能的角度考虑,InnoDB 使用采样统计,默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。因此,上述两个索引显示的基数只是一个近似值。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候(innodb_stats_persistent=on时M=20,N=10;off时M=8,N=16),会自动触发重新做一次索引统计。
附录:
2 万字 + 30 张图 | 细聊 MySQL undo log、redo log、binlog 有什么用?
标签:binlog,事务,log,MySQL,redo,id From: https://www.cnblogs.com/bbgs-xc/p/16943566.html