2021-10-08
innodb存储引擎
插入缓存
将更新操作放入到insert buffer,然后以一定的频率刷将insert buffer和辅助索引的叶子结点数据进行merge
1、适用于非聚簇索引和唯一索引、应该会涉及到唯一值的校验。
2、适用于写多读少的系统,因为read会触发系统page merge。
3、如果全部都是insert,在crash恢复时恢复时间也会比较长。
change buffer 主要节省的则是随机读磁盘的 IO 消耗。
将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。
在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。merge 的执行流程是这样的:从磁盘读入数据页到内存(老版本的数据页);从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版数据页;写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
因为 merge 的时候是真正进行数据更新的时刻,而 change buffer 的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。因此对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以,对于这种业务模式来说,change buffer 反而起到了副作用。
所以,如果要简单地对比这两个机制在提升更新性能上的收益的话,redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
double-write
16K的page 仅仅写入了4K,发生了损坏
double write分为2个部分,内存中2MB,磁盘2MB,2个区,128个页。
先将脏页复制到内存中的buffer->buffer将脏页分为2次,每次1MB写入到磁盘的共享表空间->调用fsync同步到磁盘避免缓冲写失效->再将buffer中的脏页写入到各个表空间
异步IO
单个SQL需要访问多个page,在查询时不需要等待其返回结果可先处理其余逻辑,此时可将多个io操作合并提升磁盘的iops。
刷新邻接页
在刷脏时候会检测是否有属于同一区的脏页,主要目的是为了提升磁盘的IOPS,会存在刷脏了,但是又脏了的问题,如果是在ssd硬件的条件下iops不再受限可将其关闭。
事务相关:
隔离级别
原子性、持久性是通过redo实现,一致性是通过undo实现,隔离性是通过
读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。
读提交是指,一个事务提交之后,它做的变更才会被其他事务看到。
可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
串行化,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。
begin/commit 第一种启动方式,一致性视图是在执行第一个快照读语句时创建的;
第二种启动方式,一致性视图是在执行 start transaction with consistent snapshot 时创建的。
commit work and chain 提交事务并开启下一个事务(少执行一个begin语句)
长事务
1.通过information_schema.innodb_trx表监控事务的持续时间
2.增加undo表空间
3.通过配置参数max_execution_time指定事务执行的最长时间
4.利用pt工具监控长事务
索引相关
索引设计
常见的三种索引模型分哈希表、有序数组和搜索树。
索引设计的原则
1. 查询谓词都能够通过index进行扫描
2. 排序谓词都能够利用index的有序性
3. index包含了查询所需要的所有字段
这就是传说中的Three-star index。
《Wiley,.Relational.Database.Index.Design.and.the.Optimizers》
索引下推
select * from tuser where name like '张%' and age=10 and ismale=1;
在 (name,age) 索引里面我特意去掉了 age 的值,这个过程 InnoDB 并不会去看 age 的值,仅按顺序把“name 第一个字是’张’”的记录一条条取出来回表。
因此,需要回表 4 次。
图 4 跟图 3 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。
在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。
唯一索引和普通索引的选择
这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响,所以尽量选择普通索引。
如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。而在其他情况下,change buffer 都能提升更新性能。
使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。
索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少(B+树的高度越高),搜索的效率也就会越低。
死锁问题
电影票在线交易业务,顾客 A 要在影院 B 购买电影票,我们简化一点,这个业务需要涉及到以下操作:
从顾客 A 账户余额中扣除电影票价->给影院 B 的账户余额增加这张电影票价->记录一条交易日志。
两阶段锁协议:在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。
每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。
每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行,那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的 CPU 资源。因此,你就会看到 CPU 利用率很高,但是每秒却执行不了几个事务。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。
备份MDL锁的问题
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
如果在 Q4 语句执行之前到达,现象:没有影响,备份拿到的是 DDL 后的表结构。
如果在“时刻 2”到达,则表结构被改过,Q5 执行的时候,报 Table definition has changed, please retry transaction,现象:mysqldump 终止;
如果在“时刻 2”和“时刻 3”之间到达,mysqldump 占着 t1 的 MDL 读锁,binlog 被阻塞,现象:主从延迟,直到 Q6 执行完成。
从“时刻 4”开始,mysqldump 释放了 MDL 读锁,现象:没有影响,备份拿到的是 DDL 前的表结构。
killed状态原因分析
这些“kill 不掉”的情况,其实是因为发送 kill 命令的客户端,并没有强行停止目标线程的执行,而只是设置了个状态,并唤醒对应的线程。
而被 kill 的线程,需要执行到判断状态的“埋点”,才会开始进入终止逻辑阶段。并且,终止逻辑本身也是需要耗费时间的。
超大事务执行期间被 kill:这时候,回滚操作需要对事务执行期间生成的所有新数据版本做回收操作,耗时很长。
大查询回滚:如果查询过程中生成了比较大的临时文件,加上此时文件系统压力大,删除临时文件可能需要等待 IO 资源,导致耗时较长。
DDL 命令执行到最后阶段,如果被 kill,需要删除中间过程的临时文件,也可能受 IO 资源影响耗时较久。
解决方案:(可延展到innodb_thread_concurrency内部并发线程数)
innodb_thread_concurrency 这个参数的默认值是 0,表示不限制并发线程数量。
但是,不限制并发线程数肯定是不行的。因为,一个机器的 CPU 核数有限,线程全冲进来,上下文切换的成本就会太高。
如果一个事务被 kill 之后,持续处于回滚状态,从恢复速度的角度看,你是应该重启等它执行结束,还是应该强行重启整个 MySQL 进程。
因为重启之后该做的回滚动作还是不能少的,所以从恢复速度的角度来说,应该让它自己结束。
当然,如果这个语句可能会占用别的锁,或者由于占用 IO 资源过多,从而影响到了别的语句执行的话,就需要先做主备切换,切到新主库提供服务。
切换之后别的线程都断开了连接,自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的,减少系统压力,加速终止逻辑。
双1配置
sync_binlog :
sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。
innodb_flush_log_at_trx_commit :
设置为 0 的时候,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中
设置为 1 的时候,表示每次事务提交时都将 redo log 直接持久化到磁盘
设置为 2 的时候,表示每次事务提交时都只是把 redo log 写到 page cache
并行复制
不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个 worker 中。
同一个事务不能被拆开,必须放到同一个 worker 中。
不用等到 commit 阶段,只要能够到达 redo log prepare 阶段,就表示事务已经通过锁冲突的检验了
MySQL 5.7 并行复制策略的思想是:
同时处于 prepare 状态的事务,在备库执行时是可以并行的;
处于 prepare 状态的事务,与处于 commit 状态的事务之间,在备库执行时也是可以并行的。
binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync
也就是说,这两个参数,既可以“故意”让主库提交得慢些,又可以让备库执行得快些。
在 MySQL 5.7 处理备库延迟的时候,可以考虑调整这两个参数值,来达到提升备库复制并发度的目的。
binlog_transaction_dependency_tracking,用来控制是否启用这个新策略。
这个参数的可选值有以下三种。
COMMIT_ORDER,表示的就是前面介绍的,根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
WRITESET,表示的是对于事务涉及更新的每一行,计算出这一行的 hash 值,组成集合 writeset。如果两个事务没有操作相同的行,也就是说它们的 writeset 没有交集,就可以并行。
WRITESET_SESSION,是在 WRITESET 的基础上多了一个约束,即在主库上同一个线程先后执行的两个事务,在备库执行的时候,要保证相同的先后顺序。
GTID
GTID=server_uuid:transaction_id
Auto_Position=1 ,表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set,是备库收到的所有日志的 GTID 集合;
Executed_Gtid_Set,是备库所有已经执行完成的 GTID 集合。
set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;commit;
set gtid_next=automatic;
start slave;
GTID跳错:
stop slave;
set gtid_next='';
begin;commit;
set gtid_next='automatic';
start slave;
GTID主从切换:
stop slave;
change master to master_host='',master_port='',master_auto_position=1;
start slave;
主从架构延迟方案解决
强制走主库方案
sleep 方案
主库更新后,读从库之前先 sleep 一下。具体的方案就是,类似于执行一条 select sleep(1) 命令。
判断主备无延迟方案;
1、每次从库执行查询请求前,先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ,那就必须等到这个参数变为 0 才能执行查询请求。
2、Master_Log_File 和 Read_Master_Log_Pos,表示的是读到的主库的最新位点;Relay_Master_Log_File 和 Exec_Master_Log_Pos,表示的是备库执行的最新位点。
3、Retrieved_Gtid_Set,是备库收到的所有日志的 GTID 集合;Executed_Gtid_Set,是备库所有已经执行完成的 GTID 集合。
上面判断主备无延迟的逻辑,是“备库收到的日志都执行完成了”。从 binlog 在主备之间状态的分析中,还有一部分日志,处于客户端已经收到提交确认,而备库还没收到日志的状态。
判断同步位点的方案还有另外潜在的问题,即:如果在业务更新的高峰期,主库的位点或者 GTID 集合更新很快,那么上面的两个位点等值判断就会一直不成立,很可能出现从库上迟迟无法响应查询请求的情况。
配合 semi-sync 方案
事务提交的时候,主库把 binlog 发给从库。
从库收到 binlog 以后,发回给主库一个 ack,表示收到了。
主库收到这个 ack 以后,才能给客户端返回“事务完成”的确认。
semi-sync+ 位点判断的方案,只对一主一备的场景是成立的。在一主多从场景中,主库只要等到一个从库的 ack,就开始给客户端返回确认。
这时,在从库上执行查询请求,就有两种情况:如果查询是落在这个响应了 ack 的从库上,是能够确保读到最新数据;但如果是查询落到其他从库上,它们可能还没有收到最新的日志,就会产生过期读的问题。
增强半同步
rpl_semi_sync_master_wait_point
1.InnoDB Redo File Write
2.binlog File Flush & Sync
3.InnoDB Redo File Commit(同时释放事务持有的锁)
4.Send binlog to Slave。
等主库位点方案
select master_pos_wait(file, pos[, timeout]);
它是在从库执行的;参数 file 和 pos 指的是主库上的文件名和位置;timeout 可选,设置为正整数 N 表示这个函数最多等待 N 秒。
等 GTID 方案
trx1 事务更新完成后,从返回包直接获取这个事务的 GTID,记为 gtid1;
选定一个从库执行查询语句;
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
如果返回值是 0,则在这个从库执行查询语句;
否则,到主库执行查询语句。
大表查询
与“Sending to client”长相很类似的一个状态是“Sending data”,这是一个经常被误会的问题。
“Sending to client”,就表示服务器端的网络栈写满了。
“Sending data”并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。
授权相关
全局权限
磁盘上,将 mysql.user 表里,用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’;
内存里,从数组 acl_users 中找到这个用户对应的对象,将 access 值(权限位)修改为二进制的“全 1”。
db 权限
磁盘上,往 mysql.db 表中插入了一行记录,所有权限位字段设置为“Y”;
内存里,增加一个对象到数组 acl_dbs 中,这个对象的权限位为“全 1”。
表权限和列权限
表权限定义存放在表 mysql.tables_priv 中,列权限定义存放在表 mysql.columns_priv 中。
这两类权限,组合起来存放在内存的 hash 结构 column_priv_hash 中。
这两个权限每次 grant 的时候都会修改数据表,也会同步修改内存中的 hash 结构。因此,对这两类权限的操作,也会马上影响到已经存在的连接。
正常情况grant/revoke都是可以立即生效,将表数据和内存权限数组维护一致的。仅在使用DML操作系统表会导致不一致的情况。
注意:
1、acl_dbs是一个全局数组,所有线程判断db权限都用这个数组。 但是如果当前会话已经处于某一个db中,之前use这个库的时候拿到的库权限会保存在绘画变量中,直到切换出这个库。
2、super 是全局权限,这个权限信息在线程对象中,而 revoke 操作影响不到这个线程对象。
LSN(log sequence number 日志序列号)
1、重做日志写入的数量
2、checkpoint的位置
3、页的版本,存在每个页的头部记录当前的版本号
InnoDB的数据恢复是一个很复杂的过程,在其恢复过程中,需要redo log、binlog、undo log等参与,这里把InnoDB的恢复过程主要划分为两个阶段。
第一阶段主要依赖于redo log的恢复
第二阶段,恰恰需要binlog和undo log的共同参与
第一阶段:
数据库启动后,InnoDB会通过redo log找到最近一次checkpoint的位置,然后根据checkpoint相对应的LSN开始获取需要重做的日志,
接着解析获取的日志并且保存到一个哈希表中,最后通过遍历哈希表中的redo log信息,读取相关页进行恢复。
redo log全部被解析并且apply完成,整个InnoDB recovery的第一阶段也就结束了,
在该阶段中,所有已经被记录到redo log但是没有完成数据刷盘的记录都被重新落盘。
然而InnoDB单靠redo log的恢复是不够的,这样还是有可能会丢失数据(或者说造成主从数据不一致)
第二阶段:
第一部分,扫面最后一个binlog获取所有可能没有提交事务的xid列表;
Tips:MySQL为何只需要扫描最后一个Binlog文件呢 ?
原因是每次在rotate到新的Binlog文件时,总是保证没有正在提交的事务,然后fsync一次InnoDB的redo log。
这样就可以保证老的Binlog文件中的事务在InnoDB总是提交的。
第二部分,根据undo中的信息构造所有未提交事务链表,最后通过上面两部分协调判断事务是否可以提交。
InnoDB当前版本有128个回滚段,每个回滚段中保存了undo log的位置指针,通过扫描undo日志,
我们可以构造出还未被提交的事务链表(存在于insert_undo_list和update_undo_lsit中的事务都是未被提交的),
所以通过起始页(0,5)下的solt信息可以定位到回滚段,然后根据回滚段下的undo的slot定位到undo页,把所有的undo信息构建一个undo_list,
然后通过undo_list再创建未提交事务链表trx_sys->trx_list。
MySQL在第二阶段恢复的时候,先会去读取最后一个binlog文件的所有event信息,然后把xid保存到一个列表中,然后进行第二部分的恢复。
MVCC:
定义:
即多版本并发控制。MVCC 是一种并发控制的方法,即同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)
MVCC只在RC和RR两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读,
它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
快照读
像不加锁的 select 操作就是快照读,即不加锁的非阻塞读;之所以出现快照读的情况,是基于提高并发性能的考虑,快照读的实现是基于多版本并发控制。
即快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本。
实现
MVCC 就是为了实现读-写冲突不加锁,而这个读指的就是快照读, 而非当前读,当前读实际上是一种加锁的操作,是悲观锁的实现
MVCC 的目的就是多版本并发控制,在数据库中的实现是为了解决读写冲突,它的实现原理主要是依赖记录中的 3个隐式字段,undo日志 ,Read View 来实现的。
隐藏字段
DB_TRX_ID(这条记录/最后一次修改该记录的事务 ID)
DB_ROLL_PTR(回滚指针指向这条记录的上一个版本)
DB_ROW_ID(隐含的自增 ID(隐藏主键)
undo日志
insert undo log:事务提交后可以被立即丢弃
update undo log:对应的日志才会被 purge 线程统一清除
同事务或者相同事务的对同一记录的修改,会导致该记录的undo log成为一条记录版本线性表,既链表,undo log的链首就是最新的旧记录,链尾就是最早的旧记录
Read-View 读视图
就是事务进行快照读操作的时候生产的读视图 (Read View),在该事务执行的快照读的那一刻,会生成数据库系统当前的一个快照,
记录并维护系统当前活跃事务的 ID (当每个事务开启时,都会被分配一个 ID , 这个 ID 是递增的,所以最新的事务,ID 值越大)
RC与RR的区别:
总之在 RC 隔离级别下,是每个快照读都会生成并获取最新的 Read View;
而在 RR 隔离级别下,则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View。
为什么RR能实现可重复读而RC不能,分两种情况
快照读的情况下,rr(可重复读)不能更新事务内的up_limit_id,而rc(读提交)每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc(读提交)不能可重复读
当前读的情况下,rr(可重复读)是利用record lock+gap lock来实现的,而rc(读提交)没有gap,所以rc不能可重复读
在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。
在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候重分配事务。
通常一个视图中包含创建视图的事务ID,以及在创建视图时活跃的事务ID数组。
例如,当开启一个视图时当前事务的事务ID为5, 事务链表上活跃事务ID为{2,5,6,9,12},那么就会把{2,6,9,12}存储到当前的视图中(5是当前事务的ID,不记录到视图中),
{2,6,9,12}对应的事务所做的修改对当前事务而言都是不可见的,小于2的事务ID对当前事务都是可见的,大于12的事务ID对当前事务是不可见的。
那么如何判断可见性呢? 对于聚集索引,每次修改记录时,都会在记录中保存当前的事务ID,同时旧版本记录存储在UNDO中;
对于二级索引,则在二级索引页中存储了更新当前页的最大事务ID,如果该事务ID大于readview->up_limit_id(对于上例,up_limit_id值为2),
那么就需要回聚集索引判断记录可见性;如果小于2, 那么总是可见的可以直接读取。
MHA切换流程
Phase 1: Configuration Check Phase completed.
1、HealthCheck
2、Alive Server check
Phase 2: Dead Master Shutdown Phase..
1、shutdown_script master server
Phase 3: Master Recovery Phase..
Phase 3.1: Getting Latest Slaves Phase.
Phase 3.2: Change Writer Domain Phase..
Phase 3.3: Determining New Master Phase..
Phase 4: Slaves Recovery Phase..
Phase 4.1: Starting Slaves in parallel..
Phase 5: New master cleanup phase..
Bufferpool\redo参数设置:
1、如果redo设置太大会导致crash-recovery时间过长
2、如果redo太小buffer太大会导致频check point;如果redo太大、buffer change会经常淘汰,增加IO
一般buffer:redo=10~5:1
Xtrabackup备份:
xtrabackup是基于innodb自身的崩溃恢复来实现,1个线程负责拷贝idb数据、1个线程负责扫描redo。
拷贝innodb文件->备份锁/FTWRL(lock tables for backup)->拷贝非innodb表->获取binlog点位(lock binlog for backup)->释放锁备份完成
间隙锁:
幻读:幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
间隙锁是为了解决幻读而引入的锁,间隙锁之间并不互斥,也就是说在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。
为什么binlog不是row模式会导致数据不一致?
1、索引不确定性
2、binlog_format和隔离级别会存在时许问题
MySQL加锁规则:包含了两个“原则”、两个“优化”和一个“bug”。
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。