1. 理论篇
1.1. 1.基础架构
1.2. 2.日志系统
-
redo log
-
功能:当记录进行更新操作的时候,InnoDB先写入redo log(内存中的log buffer,减少IO),并将修改更新到内存,等到以后系统空闲时后台将log刷到磁盘(commit和abort刷盘时间由参数决定)。
-
具体刷盘时间
-
首先要明白数据库有个Log Buffer,可以先刷到操作系统OS Buffer中,然后再刷到磁盘上
-
0延迟写
- 提交事务后,不会立即刷到OS Buffer中,而是等一秒后刷新到OS Buffer并调用fsync()写入Redo Log FIle,可能会丢失一秒钟的数据。
-
1实时写
- 每次提交事务,都会刷新到OS Buffer并调用fsync()写到Redo Log FIle,性能较差
-
2延迟刷新
- 每次提交事务只刷新到OS Buffer,一秒后再调用fsync()写入Redo Log FIle。
-
-
-
通过redo log实现crash safe,即服务崩溃后仍能把事务的变更持久化到磁盘文件上
-
InnoDB引擎特有
-
物理日志,记录的是某个数据页上做了什么
-
循环写,空间固定会用完(write pos和checkpoint之间即为空闲空间,checkpoint记录的是写入到了磁盘的位置,write pos是当前正在写的位置)
-
-
bin log
-
作用:数据备份和主从同步
-
Server层实现的
-
逻辑日志
-
追加
-
-
undo log
-
undo log记录的是相反操作的逻辑日志
-
作用是用来实现事务的回滚操作,以及实现MVCC(复用undo log的trx id和历史record内容,具体见事务隔离章节)
-
-
两阶段提交
-
涉及到redo log和undo log和提交事务
-
为什么要两阶段提交?
- 一条记录更新操作必须要同时有bin log和redo log,如果少了其中的一个都要么在crash恢复或者备份恢复阶段少了其中一个记录的更新。但是如果不二阶段提交,总可能少其中一个(只是用在提交事务语句上)
-
做法:
- 先写入redo log,设为prepare状态,等到bin log也写入之后再把redo log中的prepare状态改为commit状态。当故障恢复的时候,判断redo log是否是commmit状态或者是否是prepare状态且完整
-
1.3. 3.事务隔离
-
三种问题
-
脏读
- 事务A、B,B更新a的值,A读取到,但是B没有提交而是回滚了。则A读到了一个不存在的值。
-
不可重复读
- 事务A、B,B更新a的值,提交了,A读到了。B再次更新a的值,提交了,A又读到了。造成这种问题的原因就是实现方式是每次select都开启一个Read View视图导致的。
-
幻读
-
-
四种隔离级别
-
读未提交(脏读)
-
还没有提交的事务的修改对其他事务是可见的
-
实现方式
- 直接读取版本中最新的记录
-
-
读已提交(不可重复读)
-
已提交的事务的修改对其他未提交的事务是可见的
-
图示
-
实现方式
- 使用版本链的方式
-
-
可重复读(幻读)
-
读到已提交事务的更改
-
图示
-
实现方式
-
select查询用得到是快照读
-
使用MVCC一定程度上避免了幻读
-
出现幻读的情况
- 本质原因:事务B对该记录的修改的trix id是事务B的id所以事务A是看不到的,但是事务A执行了一个更新操作以后trix id就变成事务A的id了,就可以可见了
-
-
select … for update用的是当前读
-
使用间隙锁+记录锁一定程度上避免了幻读
-
出现幻读的情况
-
T1 时刻:事务 A 先执行「快照读语句」:select * from t_test where id > 100 得到了 3 条记录。
-
T2 时刻:事务 B 往插入一个 id= 200 的记录并提交;
-
T3 时刻:事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录,此时也发生了幻读现象。
-
-
-
总结
-
一种是MVCC机制本身的幻读,一种是没加临键锁的快照读和当前读引起的
-
网址
- 美团面试官:MySQL可重复读如何解决幻读问题? - 知乎 (zhihu.com)
-
-
-
-
可串行化
-
实现方式
- 使用加锁互斥的方式实现
-
-
-
MVCC
-
三个隐藏的字段
-
RowID
-
自动创建的id(若没有主键才存在,MVCC不使用)
- 隐藏的自增ID,当建表没有指定主键,InnoDB会使用该RowID创建一个聚簇索引。
-
-
DB_TRX_ID
-
事务id
- 最近修改(更新/删除/插入)该记录的事务ID。
-
-
DB_ROLL_PTR
-
回滚指针
- 回滚指针,指向这条记录的上一个版本。
-
-
-
Read View
-
up_trix_id
- 最先开始的事务,该SQL启动时,当前事务链表中最小的事务id编号,也就是当前系统中创建最早但还未提交的事务
-
low_trix_id
- 最后开始的事务,该SQL启动时,当前事务链表中最大的事务id编号,也就是最近创建的除自身以外最大事务编号
-
m_ids
- 当前活跃事务ID列表,所有事务链表中事务的id集合
-
creator_trix_id(是否存在?)
- 当前创建 ReadView 事务的 ID
-
-
可见性判断
-
1.4. 4.索引
-
回表查询
-
联合索引
-
为什么要使用联合索引
-
减少开销
- 建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
-
覆盖索引
- 对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。
-
效率高
-
索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知
- 解释一下:使用单列索引时,查询计划使用的
-
-
-
-
覆盖索引
- 可以使得不需要回表查询
-
最左匹配原则
1.5. 5.锁机制
-
加锁粒度
-
全局锁
-
使用
-
flush tables with read lock
- 对数据的增删改和表的修改都被阻塞
-
unlock tables
-
-
作用
- 全库逻辑备份
-
备注
- InnoDB存储引擎默认是可重复读的隔离级别,备份的时候可以不用全局锁。MylSAM不支持可重复读隔离级别,备份的时候需要加全局锁
-
-
表级锁
-
表锁
-
使用
-
lock tables t_student read 共享读锁
-
lock tables t_student write 共享写锁
-
unlock tables
-
-
作用
- 表锁可以锁住表的数据
-
-
元数据锁(MDL)
-
作用
-
元数据锁可以锁住表的元数据
-
对表结构的修改(例如给某个表删除某个列)会被阻塞
-
-
使用
-
当对表进行操作时,自动给表加上MDL。DML锁一旦加上则在整个事务期间都持有,直到事务结束才释放
-
对一张表CRUD,加的是DML读锁
- 当有线程在执行CRUD(加DML读锁),其他线程如果要对表结构进行变更,会被阻塞
-
对一张表的结构CRUD,加的是DML写锁
- 当有线程进行表结构的更改(加DML写锁),其他线程如果要进行CRUD操作,将会被阻塞
-
-
申请方式
-
锁请求队列,该队列有优先级,写锁请求阻塞读写请求
- 一旦有一个写锁请求被阻塞,后面无法再进行表的CRUD操作
-
-
备注
- 在所有事务不对表进行结构改变的情况下,加的都是DML读锁,可以说互不影响,察觉不到DML锁的存在
-
-
意向锁
-
使用
-
在使用InnoDB引擎的表里对某些记录加上共享行级锁之前,需要先给表加上表级意向共享锁
- select … lock in share mode;
-
在使用InnoDB引擎的表里对某些记录加上共享独占锁之前,需要先给表加上表级意向独占锁
- select … for update;
-
-
使用注意
-
只有InnoDB才支持行级锁,所以只有InnoDB才有意向锁
-
默认的select语句使用的是MVCC实现事务隔离,是没有行级锁的
-
意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(lock tables … read)和独占表锁(lock tables … write)发生冲突。
-
-
作用
-
快速判断表里是否有记录被加锁。
-
eg:表加了意向互斥,就不能再遍历了
-
-
-
AUTO-INC锁
-
使用
- 表锁,插入数据语句执行时自动加锁,插入语句执行完后(将AUTO_INCRE递增后)立即释放
-
注意
-
AUTO-INC锁是重量级锁,插入大量数据会影响性能
-
MySQL 5.1.22之后,InnoDB使用一种轻量级锁来实现自增
- 插入数据的时候,会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁,然后给该字段赋值一个自增的值,就把这个轻量级锁释放了,而不需要等待整个插入语句执行完后才释放锁。
-
innodb_autoinc_lock_mode 的系统变量,是用来控制选择用 AUTO-INC 锁,还是轻量级的锁。
-
-
-
-
行锁(InnoDB特有)
-
记录锁(Record Lock)
-
共享锁S
-
互斥锁X
-
-
间隙锁(Gap Lock)
-
使用
- 只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
-
-
临建锁(Next-key Lock)
-
使用
-
同间隙锁,是为了在可重复隔离级别下实现幻读(第二种幻读)的
-
使用即select语句的where条件中的范围,后面for update就是给指定范围加临键锁
-
-
注意
- 一个间隙和间隙后面紧跟的key
-
-
InnoDB三种锁的选择规则
-
无索引的情况
- select…for update、insert、update、delete都会给全表加next-key lock
-
唯一索引等值查询
-
记录存在
-
加表记X意向锁
-
加的是针对该记录的X锁
-
-
记录不存在
-
表级X意向锁
-
加的是间隙锁
- id = 3
-
-
-
唯一索引范围查询
-
每扫描到一个索引加next-key锁,遇到下面的情况会退化为记录锁或者间隙锁
-
情况一:大于等于的范围查询
-
存在等值查询,如果等值查询的记录是存在于表中,next-key锁退化为记录锁
- 也就是说等于的那个值前面的间隙是不需要的
-
-
情况二:针对小于或者小于等于的范围查询
-
条件值的记录存在于表中
-
小于:退化为间隙锁
-
小于等于:next-key锁
-
-
条件值的记录不存在于表中
-
不管是小于还是小于等于都会退化为间隙锁
- 都只保留间隙,而不取key
-
-
-
-
非唯一索引等值查询
-
当我们用非唯一索引进行等值查询的时候,因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有满足查询条件的记录才会对它们的主键索引加锁。
-
情况一:查询的记录存在时
-
直到扫描到第一个不符合条件的二级索引记录就停止扫描,然后在扫描的过程中,对扫描到的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
- 解析:二级索引加锁是很好想的(第一个不符合的变为间隙锁,除去该第一个值,保留前面的间隙),一级索引加的只是记录锁。
-
-
情况二:查询的记录不存在时
- 二级索引同不存在的情况,找到第一个不符合的退化间隙锁,但是主键索引不需要加锁
-
-
非唯一索引范围查询
- 相比于等值查询,二级索引全是加next-key锁
-
-
二阶段锁规则以及性能优化
-
二阶段锁
-
所有事务都是在用到的时候加上锁,但是在事务commit的时候提交事务
-
让冲突的行尽量放在事务的后面(保证事务正确的前提下),这样可以减少锁住的时间,提高并发度。(参考45讲06的例子)
-
-
死锁检测
-
两种手段
-
等待直到超时
- 这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
-
死锁检测
- 发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑。
-
-
业务上的问题(热点行更新)
-
innodb_lock_wait_timeout的默认值是50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过50s才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。
-
主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的CPU资源。因此,你就会看到CPU利用率很高,但是每秒却执行不了几个事务。
-
-
解决思路
-
控制并发度
-
中间件实现
-
修改MySQL源码
-
表的一行拆分成逻辑的多行
-
-
-
-
-
-
2. 实战篇
2.1. 普通索引和唯一索引如何选择
-
ChangeBuffer
-
Buffer Pool内存结构图
-
工作原理
-
要修改的索引页在内存中
- 直接在内存中更新索引页
-
要修改的索引页不在内存中
-
将对索引页的修改写到change buffer中
-
下次查询索引页时,会将索引页读入Buffer Pool,然后将change buffer中的操作应用到对应的缓存页,得到最新结果,这个过程称为merge。
-
-
-
使用条件(索引)
-
记录在内存中
-
对于唯一索引来说,找到对应的位置,判断到没有冲突,插入这个值,语句执行结束;
-
对于普通索引来说,找到对应的位置,插入这个值,语句执行结束。
-
-
记录不在内存中
-
对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
-
对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了。
-
-
结论
- 对于记录在内存中,区别差不多。对于记录不在内存中,普通索引要减少随机IO次数,所以普通索引才适合使用ChangeBuffer
-
-
业务场景
-
写多读少类业务(是账单类、日志类)
- 适合changeBuffer和普通索引配合使用
-
写入后立马查询的业务
- 不适合
-
-
与redo log的工作流程
-
- Page 1在内存中,直接更新内存;
-
- Page 2没有在内存中,就在内存的change buffer区域,记录下“我要往Page 2插入一行”这个信息
-
- 将上述两个动作记入redo log中(图中3和4)。
-
-