
mysql高级篇总结
mysql
1. SQL执行顺序
1、FROM:选择FROM后面跟的表,产生虚拟表1。
2、ON:ON是JOIN的连接条件,符合连接条件的行会被记录在虚拟表2中。
3、JOIN:如果指定了LEFT JOIN,那么保留表中未匹配的行就会作为外部行添加到虚拟表2中,产生虚拟表3。如果有多个JOIN链接,会重复执行步骤1~3,直到处理完所有表。
4、WHERE:对虚拟表3进行WHERE条件过滤,符合条件的记录会被插入到虚拟表4中。
5、GROUP BY:根据GROUP BY子句中的列,对虚拟表4中的记录进行分组操作,产生虚拟表5。
6、AGG_FUNC:常用的 Aggregate 函数包涵以下几种:(AVG:返回平均值)、(COUNT:返回行数)、(FIRST:返回第一个记录的值)、(LAST:返回最后一个记录的值)、(MAX: 返回最大值)、(MIN:返回最小值)、(SUM: 返回总和)。
7、WITH 对虚拟表5应用ROLLUP或CUBE选项,生成虚拟表 6。
8、HAVING:对虚拟表6进行HAVING过滤,符合条件的记录会被插入到虚拟表7中。
9、SELECT:SELECT到一步才执行,选择指定的列,插入到虚拟表8中。
10、UNION:UNION连接的两个SELECT查询语句,会重复执行步骤1~9,产生两个虚拟表9,UNION会将这些记录合并到虚拟表10中。
11、DISTINCT 将重复的行从虚拟表10中移除,产生虚拟表 11。DISTINCT用来删除重复行,只保留唯一的。
12、ORDER BY: 将虚拟表11中的记录进行排序,虚拟表12。
13、LIMIT:取出指定行的记录,返回结果集。
2. 基础概念概述
客户端,服务端(tcp/ip连接),查询优化器,存储引擎,系统变量(会话级别全局级别),字符编码(utf8mb3 mb4 一个占3字节,一个4,emoji,varchar存汉字大小除以3-4,2w多字(null值列表是否删除都占空间),排序规则,文件目录结构(表空间,数据存储的文件机构),这边也略过了。
配置文件(永久)和启动配置项(临时):配置项优先级高
数据库 表字符编码,存储引擎建议一致
创建数据库,表,可指定存储引擎,行格式,字符编码,字符排序规则
3. 存储引擎
myisam-不支持事务,数据和索引分开,索引存的地址偏移量,回表速度快(但是要行格式定长下),只有表锁
innodb存储结构-默认使用,支持事务,B+树索引,行锁表锁间隙,插入意向锁等,事务MVCC,
4. InnoDB存储结构
记录 行格式
基础compact,5.7默认dynamic(行溢出时不存部分数据而是所有数据都甩出去,指针引用),一行记录指针在中间,往右是隐藏列和真实字段数据,往左是记录头,null值列表,变长字段列表(逆序)。
-
隐藏列:
- row_id,没主键或唯一字段,加一个row_id
- 事务id,标识是哪个事务在用
- 回滚指针:事务回滚用
-
记录头:
- 标记是否被删除,被删除数据放到垃圾链表,其他数据新增时覆盖
- 非叶子节点最小记录,非叶节点索引指向用
- 记录数,统计,页目录分组用
- 堆位置信息
- 记录类型,0普通记录,1索引记录,2最小3最大,区别数据是非叶节点还是叶子节点
- 下一条记录位置,记录之间形成单链表
-
行溢出:一个行数据过大,行溢出,多余数据放到别的页,指针引用指向
页(基本存储单位,一页16kb)
-
文件头:描述文件信息,校验文件
-
校验和
-
当前页号
-
上一页号
-
下一页号
-
日志LSN码(log sequeceNumber)
-
页的类型
-
文件至少被刷新到了的LSN位置(系统表空间)
-
页属于哪个表空间
-
-
页头,数据页专有信息
-
页目录槽数量
-
空闲空间地址
-
记录数(全部)
-
删除数据链表头
-
已删数据占用空间
-
最后插入记录地址
-
记录插入方向
-
连续同向插入的记录数
-
存活记录数量(不计入虚拟记录和被删除记录)
-
修改当前页的最大事务id
-
索引id
-
叶子字段头信息(Root)
-
非叶字段头信息(Root)
-
-
最大记录、最小记录,虚拟记录,指向实际最大最小记录
-
用户记录,存放记录
- 记录之间经过排序,用行格式文件头信息的next_record形成单链表
-
空闲记录,空闲空间
-
页目录,对记录分组,根据记录的头信息的n_owned字段对记录分组,便于二分
-
文件尾:与文件头对应,校验文件完整性
- 前4字节:校验和(与文件头对应,写入完成时两者一致说明写成功)
- 后4字节:最后修改时的LSN(配合文件头的LSN,双重校验磁盘IO完整性)
页内查找过程(两步走)
- 二分查找定位槽:通过页目录的槽(slot)进行二分查找,快速定位到目标记录所在的分组
- 链表遍历:通过记录的next_record属性,在分组内遍历找到目标记录
页目录分组规则(n_owned)
- 最小记录所在分组:只能有 1 条记录
- 最大记录所在分组:1~8 条记录
- 其他普通分组:4~8 条记录
文件头串联多页
多个数据页通过文件头的「上一页号」和「下一页号」组成双向链表,这是B+树同层节点遍历的基础。非叶节点之间、叶子节点之间各自形成独立的双向链表。
表空间
当前版本,一个表有表级独立表空间,也有系统表空间
段
对区中的叶子节点,非叶节点分开存储,便于遍历,提高查询效率
区
顺序的一片空间(64个页,1m大小)256个区(256M)一组,避免创建数据页时随机io,保证顺序io
碎片区
以及其他段,独属表空间,属于系统表空间,用于放零碎数据(避免空间浪费)
5. 索引原理
索引
空间换时间,提高查询效率,新增删除需要维护数据。
聚簇索引(数据即索引,存全部数据,根据主键排序)
核心思想:B+树本质上是页结构的逐层分组扩展。
页通过文件头标识类型,大致分根节点,非叶节点(记录页),叶子节点(数据页)
-
根节点独有,第一个数据先创建根节点,根节点不变
-
根据二分实现插入时排序,依赖文件头的插入方向,连续插入位置等
-
数据扩容形成叶子节点(数据页),每页记录由链表存储,页目录对记录分组,又一次二分
-
叶子节点之间形成双向链表(基于文件头的上下页号,遍历用)
-
叶子节点过多,不好遍历时,扩容形成非叶节点(记录页)
- 不存数据,存kv值,k是最小记录主键值,v是页号
- 同理,记录页的页目录维护的也是主键值--页号这样的kv,对其分组
- 同理,记录页之间也形成双向链表(遍历用)
-
非叶节点过多,再扩展一层,索引非叶节点。
非叶节点与叶子节点使用相同的页结构,区别在于:
- 非叶节点的记录 record_type=1(叶子节点 record_type=0)
- 非叶节点只存储「主键 + 页号」,不存真实数据
- 非叶节点的页目录分组的也是「主键 → 页号」这样的kv对
B+树形成过程(从页结构理解):
- 一个数据页内,记录通过页目录分组 → 页内二分查找
- 数据页多了,需要非叶节点把多个页的主键摘出来再分组 → 页间二分查找
- 非叶节点也多了,再加一层 → 多层B+树
本质上就是:分组 → 再分组 → 再再分组(套娃),每一层都是一次二分。
B树:平衡多路搜索树,叶子和非叶节点都存储数据
- 插入时为了维护数据要左旋右旋,经常变更结构
- 只适合随机检索,不适合顺序检索
- 查询时间复杂度O(n),树高影响
B+树:在B树基础上,非叶节点只存索引,不存数据,且同级叶子非叶节点通过双向链表链接
- 不须B树那么频繁改变树结构(也要维护)
- 适合随机和顺序检索,范围检索(因为只存索引及双向链表,顺序遍历更方便)
- 查询时间复杂度O(log(n)),相对稳定,树的高度稳定(扁长)
总结:innodb中的索引就是数据,默认一个聚簇索引,B+树结构(复用页结构),依据主键对数据实现了排序,B+树树高稳定,查询稳定,有双向链表的原因,顺序和范围遍历方便,每页的记录之间又形成单链表,又由页目录分组,又进行一次二分,进一步提高效率。
核心:B+树,二分查找,双向链表,进一步二分,单链表
在B+树中查找一条记录的完整过程:
- 从根节点开始,在非叶节点的页目录中二分查找 → 定位到某个子节点(页号)
- 进入子节点,继续在页目录中二分查找 → 定位到分组
- 在分组内通过next_record链表遍历 → 找到目标记录
- 如果子节点还是非叶节点,重复步骤1-2,直到叶子节点
二级索引
显式创建的索引,只存放部分数据,记录中只有主键-部分数据,用到二级索引提高对特定字段的检索速度
二级索引除了全文索引外都要回表,(回到聚簇索引中取其他字段的值),
联合索引
对多个字段按顺序排序的索引,使用该索引要注意最左前缀,否则失效或者只能用一部分
全值匹配
搜索条件(select中)使用到的字段全是联合索引的字段,不用回表。
最左前缀
查询条件(where后边,包括group和order)字段顺序必须要和联合索引的顺序一致。从左到右依次,否则失效或只能用部分,或者干脆走全表扫描
匹配列前缀
字符串like '%abc%'时,左边带通配符%无法使用索引,只有确定前缀顺序abc%才走
匹配范围值
索引天然有序,所以范围查询是很快的,
- 联合索引只有对最左边的索引范围查找才生效
- 联合索引可以先对左边的索引等值查询再对右边的值范围查找。例如name="aaa" and birthday between ‘1980-01-01’ and '2000-12-31'
排序
索引有序,根据索引排序很快
-
联合索引需要排序字段顺序和索引顺序一致才能使用
-
同上,匹配联合索引最左边的列就可以生效右边的列
-
不能asc desc混用
-
where字句出现非索引字段,失效
-
排序列包含非索引字段,失效
-
排序列使用复杂表达式,失效
分组
和排序一个道理一个规则
回表
二级索引拿到匹配值的id回到聚簇索引去获取全部数据(回表时是随机io,效率慢)
回表记录数越多,代价越大,导致可能直接去走全表扫描
覆盖索引
要查询的字段全是索引中的字段,这样就不用回表去聚簇索引找数据了
索引注意事项
- 只为where,group,order中的字段建立索引
- 考虑列的基数,基数小没必要建索引,例如性别就不适合索引,二分也只能分出来男女
- 索引类型尽量小,省空间,同时减少遍历和比对的时间(例如比对varchar)
- 字符串最左前缀 abc%
- 主键用顺序自增插入,随机id插入时要改变索引结构,消耗太大(- 这就是为什么建议用自增或有序的雪花算法id 不用uuid
- 索引列不要用表达式或函数(索引无法确定未知的结果,也就无法拿到确定的结果去索引里找
- 不要建过多或重复冗余的索引,例如name和name_age
还有更多的细节注意项,例如
- 字段强制类型转换,varchar类型的字段条件查询必需加''号
- not 、!=
- or 改成in,o是O(n) in 是 O(log(n))
- 字段默认值null值
- join时两边字段类型要相同
- join被驱动表的连接列加上索引
其他sql优化这里略过
6. 查询优化
Explain
使用explain查看执行计划,根据字段分析查询优化器采用什么路线执行查询,例如是否走索引,可能走哪些索引,访问方法,排序使用索引还是文件排序等
访问方法
单点区间
const:常数级,等值精确查询,where id = 2 确定只返回一条记录
ref:等值范围,where name = '张三' 确定返回了3天记录
ref_or_null:等值范围或null,等值范围可能出现null值的情况
范围区间
range:范围匹配,where age in (16,17,18,19) or (age >35 and age < 40);返回一个范围值
index:直接遍历二级索引且不用回表,例如
#有对part1,part2,part3的联合索引,索引覆盖了,不用回表,条件也不是最左前缀,就直接在二级索引上遍历了
select part1,part2,part3 from t_part where part3 like 'cc%';
all:全表扫描,聚簇索引上一条一条遍历
index_merge:索引合并,多个索引取交集,快于all
- intersection,多个二级索引取交集(建议用联合索引替代intersection)
- 索引列要等值匹配
- 主键可以范围匹配
- union
- 并集,条件or的情况
- sort-union
- 取并集前排序
阿里规范sql至少达到range级别。
explain format=json ,可以让执行计划以json形式输出,可以看到查询优化器预估的执行成本
执行完后可以show warnings \g 来查看警告,code为1003时的message,是优化器重写后的sql
完整的使用 optimizer trace 功能的步骤总结如下:
# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
# 2. 这里输入你自己的查询语句
SELECT ...;
# 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
# 4. 可能你还要观察其他语句执行的优化过程,重复上边的第2、3步
...
# 5. 当你停止查看语句的优化过程时,把optimizer trace功能关闭
SET optimizer_trace="enabled=off";
join连接原理
内连接的on 和 where等价
外连接on是连接条件 where过滤条件,一个是连接时筛选,应该是连接后过滤
2.1 Index Nested-Loop Join(索引嵌套循环连接)
驱动表访问一次,一条一条去被驱动表找,避免笛卡尔积
Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。
2.2 Block Nested-Loop Join(块嵌套循环连接)
驱动表访问一次,一块一块去被驱动表找,数据暂存join buffer pool
如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录再加载到内存匹配,这样周而复始,大大增加了IO的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。
2.3 Hash Join
计算散列值等值连接匹配。
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
- Nested Loop:对于被连接的数据子集较小的情况下,Nested Loop是个较好的选择。
- Hash Join是做
大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列值,然后扫描较大的表并探测散列值,找出与Hash表匹配的行。- 这种方式适用于较小的表完全可以放入内存中的情况,这样总成本就是访问两个表的成本之和。
- 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成
若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高I/O的性能。 - 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。Hash Join只能应用于等值连接,这是由Hash的特点决定的。
关于优化
慢查询,开启慢查询日志,慢查询工具
避免用子查询,用join代替
排序,分组同索引那块,排序分组出来的数据尽量不超过1000条,排序分组到程序做
分页:
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;
7. BufferPool缓冲区
-
磁盘太慢,用内存作为缓存很有必要。
-
Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间,可以通过
innodb_buffer_pool_size 来调整它的大小。
-
Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成,每个控制块和缓存页都是一一对应的,在
填充足够多的控制块和缓存页的组合后, Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页,
这部分空间不能被使用,也被称为 碎片 。
-
InnoDB 使用了许多 链表 来管理 Buffer Pool 。5. free链表 中每一个节点都代表一个空闲的缓存页,在将磁盘中的页加载到 Buffer Pool 时,会从 free链
表 中寻找空闲的缓存页。
-
为了快速定位某个页是否被加载到 Buffer Pool ,使用 表空间号 + 页号 作为 key ,缓存页作为 value ,
建立哈希表。
-
在 Buffer Pool 中被修改的页称为 脏页 ,脏页并不是立即刷新,而是被加入到 flush链表 中,待之后的某
个时刻同步到磁盘上。
-
LRU链表 分为 young 和 old 两个区域,可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首
次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部,在 innodb_old_blocks_time 间隔时间内访
问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时,会首先淘汰掉 old 区
域的一些页。
-
我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数,每个 Buffer Pool 实
例中都有各自独立的链表,互不干扰。
-
自 MySQL 5.7.5 版本之后,可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若
干个 chunk 组成,每个 chunk 的大小可以在服务器启动时通过启动参数调整。
-
可以用下边的命令查看 Buffer Pool 的状态信息:
SHOW ENGINE INNODB STATUS\G
8. 事务
四个特性
- 原子性:操作原子,要么都成功要么都失败(undo)
- 一致性:执行前后的数据保持一致(undo)
- 隔离性:多个事务之间隔离,不能相互影响(mvcc,锁机制)
- 持久性:事务执行后的影响永久存在(redo保证)
五个状态
- 活动的:事务处于执行状态
- 部分提交的:事务执行完毕数据还没刷盘
- 失败的:事务执行过程出现错误
- 终止的:事务执行回滚,撤销
- 提交的:事务提交成功
开启事务
显示
begin | start transaction
commit | rollback | rollback to [savepoint]
- savepoint 保存点名称
- release savepoint 保存点名称
隐式
开启系统变量auto_commit = on
ddl语音
-
隐式修改或使用mysql的表
-
事务控制语句,例如lock tables手动上锁等
-
加载数据的语句,mysql复制语句或其他语句
关闭事务:
- 系统变量 auto_commit = off
- 显式开启一个事务
数据并发问题
两个事务,Session A 和 Session B
| 问题 | 描述 |
|---|---|
| 脏写 | A修改了B修改没提交的数据 |
| 脏读 | A读取到了B修改没提交的数据(B此时回滚,A读得脏数据无效) |
| 不可重复读 | A在两次查询期间,B修改并提交了数据,A前后的数据不一致 |
| 幻读 | A在两次查询期间,B对查询的表插入了数据,且没提交,A两次查询的数目不一致(查询到了B插入没提交的数据) |
- A两次查询事务期间,B删除一些数据导致A第二次查询少了某些数据,属于不可重复读。(不可重复强调前后数据不一致,幻读强调读到之前没有的记录)
事务隔离级别
| 类型 | 描述 |
|---|---|
| 读未提交 | 解决脏写,事务之间可看见相互的未提交事务的结果 |
| 读已提交 | (大部分sql默认):解决脏写,脏读,事务之间只能看见已经提交事务的结果 |
| 可重复读(mysql默认) | 解决脏写,脏读,不可重复读,MVCC机制保证事务间不可重复读数据不一致的问题 |
| 串行化 | 解决脏写,脏读,幻读,不可重复读, |
隔离级别越高,效率越低
使用:
set [global|session] transaction_isolation = 'read-uncommitted | read-committed | repeatable-read | serializable'
或
set [global | session] transaction isolation level read uncommitted | read committed | repeatable read | serializable
global 只对后续的会话生效
session只对当前会话后续事务生效
启动项设置:
transaction-isolation=xxx
9. 事务日志
Redo Log
保证持久性,用于宕机恢复
-
min-transaction mtr:每个事务由若干sql语句组成,每个语句由若干mtr组成(新增时可能分配数据页,页分裂,申请资源等)
-
redolog记录修改过的值的偏移量,有一个type记录区分各个大小的修改数据,类型为1时代表只有一条redolog。
-
当redolog需要刷盘时,先把多个redolog按照mtr分成一组redolog buffer中,然后才刷新到磁盘中。
- 事务提交时,数据不立马刷盘,生成redolog,redolog按照mtr分组被刷到redolog buffer中
- redolog buffer是一片连续的空间,用于作缓存,redolog buffer把数据追加写到redolog file中
- redolog file可有多个,写满时从头开始追加。(日志文件组)
- redolog file有checkpoint机制,分别有几个变量记录新追加的位置,刷盘成功的数据位置(可以被擦除了),当前记录的位置,(二者中间就是还未刷盘的数据)
- 当需要追加的位置干到了checkpoint时,就需要停下等他写入,checkpoint后移才能继续追加。
- redo log刷盘时会把修改过的页追加到flush链表,更新buffer pool的缓存。
redo log 刷盘策略
- 0:每次提交不立马同步redolog,完全交给后台线程做,(系统默认1秒同步一次page cache)
- 1:每次事务提交时都把redolog同步到磁盘,(默认值)
- 2:只把redo log buffer 同步到page cache,不落盘,系统决定何时落盘,(系统挂了数据丢失)
Undo log
保证原子性,用于事务回滚,MVCC
逻辑上的日志,本质是多个undo链表,记录要撤销的信息,利用隐藏列的trc_id,依次对数据进行回滚操作
每个undo链表都在一个undo段中,undo链表通过记录该记录的前后节点指向来确定要撤销记录的信息。
不同事务执行的undolog分配到不同的undo链表中(为了提高并发)
不同的undo链表可以重用(节省资源)
undo链表可分为insert undo链表 和update undo链表
- insert undo链表,提交后旧数据没用,可直接覆盖以重用。
- update undo链表,为保证mvcc机制,不能直接覆盖,再同一个页面写入多组undo日志。
回滚段:
方便管理多个undo链表,对其分组,有多个回滚段,默认128个
- redolog是物理日志,确保事务的数据最终落盘。持久性。(追加写文件,刷盘策略,redobuffer)
- undolog是逻辑日志,确保错误或事务回滚时数据修改能撤销。一致性。(undo链表,trc_id,回滚段,insert_ update链表复用,mvcc)
10. MVCC
mvcc(mutil version concurrent control),多版本并发控制,为了解决事务中的幻读问题。
本质是基于记录里的隐藏列trx_id和rollbackpoint,undolog链表,和readview读视图,共同形成一个版本链。
-
trx_id:当对数据进行改动时,把该事务的事务id赋值给它
-
roll_point:当数据改动时,把undo操作写入undo日志中,把对应的point记录到该值,记录该次改动的版本。
-
readview:事务启动时生成当前事务的快照。
- 快照读:在进行数据读取时读取的是快照,不保证是最新的数据,基于mvcc的readview,不用加锁,提高性能
- 当前读:进行数据读取时永远读取的最新数据,通过锁机制实现,加锁的select,增删改操作都会加锁。
readview执行流程:
- 获取自己的版本号
- 获取readview
- 查询得到的数据与readview事务版本号比较
- 如果不符合readview规则,就从undo log中获取数据
- 返回符合规则的数据
read_uncommited:其他事务读取时直接读取最新版本记录,不需要mvcc。(事务之间的读取操作受影响)
read_commited:该隔离级别下,readview在每次读取数据前都生成一个readview,readview在读取时会进行校验,判断是否返回对应版本的历史记录
repeated-read:该隔离级别下,readview只有在第一次读取数据前才会生成一个readview,当后续读取时都只读到事务前的数据,也就保证了可重复读。不完全解决幻读。
读已提交和可重复读的本质区别就是readview生成的时机不同来保证。
mysql中如何在可重复读级别下解决不可重复读?
解决不可重复读有两种方式
- 读写加锁
- 读走mvcc,写加锁
不可重复读是同一个事物在两处查询中,别的事物对其修改了导致数据变化了,而mvcc在可重复读级别下,第一次查询前生产一个readview,之后的每次查询都会复用该readview,就避免了不可重复读。
mysql怎么解决不同隔离级别下的并发问题
脏写:写操作加锁
脏读:mvcc,每次读取前生成readview读视图,确保可以读取到已经提交的数据,或者读取数据前加锁,防止期间其他事务修改数据
不可重复读:mvcc,查询是读取第一次生成的readview读视图,之后的每次查询都是复用该视图,别人修改新增数据不影响
幻读:分为当前读和快照读
- 当前读:采用间隙锁和行锁的方法,select for update,新增时加间隙锁,解决幻读
- 快照读:mvcc,读的快照,保证事务期间看不见新增数据
特别注意:对于修改的语句,有时还是会出现幻读。
- 例:事务1查询age为18的用户,事务2新增了一个age为18的用户,事务1此时修改age为18的用户昵称都叫暴龙战士,再查询,此时会把事务2新增的用户查出来,也就出现了幻读。
其实幻读解决到这个层面上对业务影响不大了。
11. 锁
实现事务各个隔离级别,解决最基本的脏写。
并发情况:
读-读情况:并发事务相继进行读操作,不会影响记录
写-写情况:对相同的数据进行写操作,需要加锁排队执行
读-写情况:一个事务在读取时,另一个事务同时对它读取的数据进行写操作,此时会出现脏读,不可重复读,幻读问题。
锁的分类:
按数据类型:
读锁(共享锁)S锁:多个请求可以同时读,但是不能同时修改
写锁(排他锁)X锁:一个资源获取到锁,只有该资源可以进行写入,其他资源获取时阻塞
select 。。。lock in share mode | for share (查询加读锁)
select。。。for update(查询加写锁)
5.7之前:x锁会阻塞等待到timeout
8.0可在for update之后指定 no wait 和skip locked
- no wait 阻塞时不等待直接报错返回
- skip locked 只返回没加锁的行,跳过锁住的记录
按锁粒度:
表级锁
不依赖存储引擎,锁住整个表,锁粒度大,竞争大,并发冲突大,开销最小
-
当执行ddl(alter drop)时其他事务并发执行增删改查语句时,会发生阻塞,反之同理
-
server层的元数据锁实现
-
MyISAM查询时默认是表级读锁,增删改是写锁
-
一般情况下不会用到
- lock 表名 t read;
- lock 表名 t write;
-
意向锁:向更大一级粒度标注该表是否有其他事务使用,避免一个一个判断各页或各行是否加锁。
-
存储引擎自己维护
-
自增锁:自增时会加上特殊的锁
-
简单插入:事先确定了要插入的行数
-
批量插入:事先不知道要插入的行数
-
混合模式插入:在插入时需要上自增锁,其他事务无法执行新增操作,innodb_autoinc_lock_mode
innodb_autoinc_lock_mode:
- 0:传统模式:所有insert语句都会获得一个表级锁,多个insert会争夺锁
- 1:连续锁定模式:批量插入时用自增锁,简单插入时用轻量锁的控制下获取等量的自增值
- 2:交错锁定模式:mysql8.0默认,所有insert语句都不使用表级自增锁,在binlog日志恢复时可能会出现问题,主键值不连续。
-
-
元数据锁(MDL,meta data lock):保证数据正确性的表级锁,增删改查加读锁,ddl操作加写锁。
页级锁
- 页的粒度上加的锁,开销介于表锁和行锁之间,并发效率一般。会出现死锁
行锁
存储引擎层面实现,行级别的锁,粒度为记录
-锁的粒度小,锁的冲突概率低,并发效率高
-锁的开销大,加锁慢,容易出现死锁
-
记录锁
单条记录级别的锁,分S锁和X锁
-
间隙锁(gap锁)
锁住某个范围,专门用于解决幻读(第一种方式是mvcc,第二种方式则是间隙锁)
-
临键锁(innodb默认)
gap锁的一种,锁住某条记录,以及其之前的记录,作用是在插入时避免别人再他前面的间隙插入新记录
-
插入意向锁
在插入一条记录前,有其他的gap锁,此时该条记录会产生一条插入意向锁,代表该条记录想要插入,但是前边有gap锁阻塞
12. 其他日志
- 慢查询日志(slow query log):开启慢查询,超过指定时间的sql标记为慢查询,用于分析sql,sql优化
- 通用查询日志(general query log):作sql的统计,所有连接的起始时间和终止时间
- 错误日志(error log):记录mysql启动运行或者停止时出现的问题
- 二进制日志(bin log):所有更改数据的语句,可用于主从同步(中间件mycat),(分布式事务也是基于此),数据备份和故障恢复
- 中继日志(delay log):主从架构,主从同步时中继主库的binlog,从库读取delay log来同步主库操作
- 数据定义语句日志(DDL log):记录数据定义语句执行的元数据操作
bin log 日志
是一个二进制文件,记录所有的数据更改信息,可用于主从同步
刷盘:
事务执行过程中,数据放入bin log cache,事务提交时,再把binlog cache写入bin log file中(事务要确保一次性写入)
刷盘时机:
sync_binlog:
- 0(默认),每次提交事务只写入,系统决定何时刷盘(宕机丢失)
- 1,每次提交事务都刷盘
- N,每N个事务提交后刷盘
两阶段提交
事务提交时,binlog和redolog都会写入,二者刷盘时机不一样,(binlog一次性,redolog边写边擦),为了保证二者数据同步,使用两阶段提交的方式
redolog -》prepare
binlog -》写入
redolog -》commitmysql
1. SQL执行顺序
1、FROM:选择FROM后面跟的表,产生虚拟表1。
2、ON:ON是JOIN的连接条件,符合连接条件的行会被记录在虚拟表2中。
3、JOIN:如果指定了LEFT JOIN,那么保留表中未匹配的行就会作为外部行添加到虚拟表2中,产生虚拟表3。如果有多个JOIN链接,会重复执行步骤1~3,直到处理完所有表。
4、WHERE:对虚拟表3进行WHERE条件过滤,符合条件的记录会被插入到虚拟表4中。
5、GROUP BY:根据GROUP BY子句中的列,对虚拟表4中的记录进行分组操作,产生虚拟表5。
6、AGG_FUNC:常用的 Aggregate 函数包涵以下几种:(AVG:返回平均值)、(COUNT:返回行数)、(FIRST:返回第一个记录的值)、(LAST:返回最后一个记录的值)、(MAX: 返回最大值)、(MIN:返回最小值)、(SUM: 返回总和)。
7、WITH 对虚拟表5应用ROLLUP或CUBE选项,生成虚拟表 6。
8、HAVING:对虚拟表6进行HAVING过滤,符合条件的记录会被插入到虚拟表7中。
9、SELECT:SELECT到一步才执行,选择指定的列,插入到虚拟表8中。
10、UNION:UNION连接的两个SELECT查询语句,会重复执行步骤1~9,产生两个虚拟表9,UNION会将这些记录合并到虚拟表10中。
11、DISTINCT 将重复的行从虚拟表10中移除,产生虚拟表 11。DISTINCT用来删除重复行,只保留唯一的。
12、ORDER BY: 将虚拟表11中的记录进行排序,虚拟表12。
13、LIMIT:取出指定行的记录,返回结果集。
2. 基础概念概述
客户端,服务端(tcp/ip连接),查询优化器,存储引擎,系统变量(会话级别全局级别),字符编码(utf8mb3 mb4 一个占3字节,一个4,emoji,varchar存汉字大小除以3-4,2w多字(null值列表是否删除都占空间),排序规则,文件目录结构(表空间,数据存储的文件机构),这边也略过了。
配置文件(永久)和启动配置项(临时):配置项优先级高
数据库 表字符编码,存储引擎建议一致
创建数据库,表,可指定存储引擎,行格式,字符编码,字符排序规则
3. 存储引擎
myisam-不支持事务,数据和索引分开,索引存的地址偏移量,回表速度快(但是要行格式定长下),只有表锁
innodb存储结构-默认使用,支持事务,B+树索引,行锁表锁间隙,插入意向锁等,事务MVCC,
4. InnoDB存储结构
记录 行格式
基础compact,5.7默认dynamic(行溢出时不存部分数据而是所有数据都甩出去,指针引用),一行记录指针在中间,往右是隐藏列和真实字段数据,往左是记录头,null值列表,变长字段列表(逆序)。
-
隐藏列:
-
row_id,没主键或唯一字段,加一个row_id
-
事务id,标识是哪个事务在用
-
回滚指针:事务回滚用
-
-
记录头:
-
标记是否被删除,被删除数据放到垃圾链表,其他数据新增时覆盖
-
非叶子节点最小记录,非叶节点索引指向用
-
记录数,统计,页目录分组用
-
堆位置信息
-
记录类型,0普通记录,1索引记录,2最小3最大,区别数据是非叶节点还是叶子节点
-
下一条记录位置,记录之间形成单链表
-
-
行溢出:一个行数据过大,行溢出,多余数据放到别的页,指针引用指向
页(基本存储单位,一页16kb)
-
文件头:描述文件信息,校验文件
-
校验和
-
当前页号
-
上一页号
-
下一页号
-
日志LSN码(log sequeceNumber)
-
页的类型
-
文件至少被刷新到了的LSN位置(系统表空间)
-
页属于哪个表空间
-
-
页头,数据页专有信息
-
页目录槽数量
-
空闲空间地址
-
记录数(全部)
-
删除数据链表头
-
已删数据占用空间
-
最后插入记录地址
-
记录插入方向
-
连续同向插入的记录数
-
存活记录数量(不计入虚拟记录和被删除记录)
-
修改当前页的最大事务id
-
索引id
-
叶子字段头信息(Root)
-
非叶字段头信息(Root)
-
-
最大记录、最小记录,虚拟记录,指向实际最大最小记录
-
用户记录,存放记录
- 记录之间经过排序,用行格式文件头信息的next_record形成单链表
-
空闲记录,空闲空间
-
页目录,对记录分组,根据记录的头信息的n_owned字段对记录分组,便于二分
-
文件尾:与文件头对应,校验文件完整性
-
前4字节:校验和(与文件头对应,写入完成时两者一致说明写成功)
-
后4字节:最后修改时的LSN(配合文件头的LSN,双重校验磁盘IO完整性)
-
页内查找过程(两步走)
-
二分查找定位槽:通过页目录的槽(slot)进行二分查找,快速定位到目标记录所在的分组
-
链表遍历:通过记录的next_record属性,在分组内遍历找到目标记录
页目录分组规则(n_owned)
-
最小记录所在分组:只能有 1 条记录
-
最大记录所在分组:1~8 条记录
-
其他普通分组:4~8 条记录
文件头串联多页
多个数据页通过文件头的「上一页号」和「下一页号」组成双向链表,这是B+树同层节点遍历的基础。非叶节点之间、叶子节点之间各自形成独立的双向链表。
表空间
当前版本,一个表有表级独立表空间,也有系统表空间
段
对区中的叶子节点,非叶节点分开存储,便于遍历,提高查询效率
区
顺序的一片空间(64个页,1m大小)256个区(256M)一组,避免创建数据页时随机io,保证顺序io
碎片区
以及其他段,独属表空间,属于系统表空间,用于放零碎数据(避免空间浪费)
5. 索引原理
索引
空间换时间,提高查询效率,新增删除需要维护数据。
聚簇索引(数据即索引,存全部数据,根据主键排序)
核心思想:B+树本质上是页结构的逐层分组扩展。
页通过文件头标识类型,大致分根节点,非叶节点(记录页),叶子节点(数据页)
-
根节点独有,第一个数据先创建根节点,根节点不变
-
根据二分实现插入时排序,依赖文件头的插入方向,连续插入位置等
-
数据扩容形成叶子节点(数据页),每页记录由链表存储,页目录对记录分组,又一次二分
-
叶子节点之间形成双向链表(基于文件头的上下页号,遍历用)
-
叶子节点过多,不好遍历时,扩容形成非叶节点(记录页)
-
不存数据,存kv值,k是最小记录主键值,v是页号
-
同理,记录页的页目录维护的也是主键值--页号这样的kv,对其分组
-
同理,记录页之间也形成双向链表(遍历用)
-
-
非叶节点过多,再扩展一层,索引非叶节点。
非叶节点与叶子节点使用相同的页结构,区别在于:
-
非叶节点的记录 record_type=1(叶子节点 record_type=0)
-
非叶节点只存储「主键 + 页号」,不存真实数据
-
非叶节点的页目录分组的也是「主键 → 页号」这样的kv对
B+树形成过程(从页结构理解):
-
一个数据页内,记录通过页目录分组 → 页内二分查找
-
数据页多了,需要非叶节点把多个页的主键摘出来再分组 → 页间二分查找
-
非叶节点也多了,再加一层 → 多层B+树
本质上就是:分组 → 再分组 → 再再分组(套娃),每一层都是一次二分。
B树:平衡多路搜索树,叶子和非叶节点都存储数据
-
插入时为了维护数据要左旋右旋,经常变更结构
-
只适合随机检索,不适合顺序检索
-
查询时间复杂度O(n),树高影响
B+树:在B树基础上,非叶节点只存索引,不存数据,且同级叶子非叶节点通过双向链表链接
-
不须B树那么频繁改变树结构(也要维护)
-
适合随机和顺序检索,范围检索(因为只存索引及双向链表,顺序遍历更方便)
-
查询时间复杂度O(log(n)),相对稳定,树的高度稳定(扁长)
总结:innodb中的索引就是数据,默认一个聚簇索引,B+树结构(复用页结构),依据主键对数据实现了排序,B+树树高稳定,查询稳定,有双向链表的原因,顺序和范围遍历方便,每页的记录之间又形成单链表,又由页目录分组,又进行一次二分,进一步提高效率。
核心:B+树,二分查找,双向链表,进一步二分,单链表
在B+树中查找一条记录的完整过程:
-
从根节点开始,在非叶节点的页目录中二分查找 → 定位到某个子节点(页号)
-
进入子节点,继续在页目录中二分查找 → 定位到分组
-
在分组内通过next_record链表遍历 → 找到目标记录
-
如果子节点还是非叶节点,重复步骤1-2,直到叶子节点
二级索引
显式创建的索引,只存放部分数据,记录中只有主键-部分数据,用到二级索引提高对特定字段的检索速度
二级索引除了全文索引外都要回表,(回到聚簇索引中取其他字段的值),
联合索引
对多个字段按顺序排序的索引,使用该索引要注意最左前缀,否则失效或者只能用一部分
全值匹配
搜索条件(select中)使用到的字段全是联合索引的字段,不用回表。
最左前缀
查询条件(where后边,包括group和order)字段顺序必须要和联合索引的顺序一致。从左到右依次,否则失效或只能用部分,或者干脆走全表扫描
匹配列前缀
字符串like '%abc%'时,左边带通配符%无法使用索引,只有确定前缀顺序abc%才走
匹配范围值
索引天然有序,所以范围查询是很快的,
-
联合索引只有对最左边的索引范围查找才生效
-
联合索引可以先对左边的索引等值查询再对右边的值范围查找。例如name="aaa" and birthday between ‘1980-01-01’ and '2000-12-31'
排序
索引有序,根据索引排序很快
-
联合索引需要排序字段顺序和索引顺序一致才能使用
-
同上,匹配联合索引最左边的列就可以生效右边的列
-
不能asc desc混用
-
where字句出现非索引字段,失效
-
排序列包含非索引字段,失效
-
排序列使用复杂表达式,失效
分组
和排序一个道理一个规则
回表
二级索引拿到匹配值的id回到聚簇索引去获取全部数据(回表时是随机io,效率慢)
回表记录数越多,代价越大,导致可能直接去走全表扫描
覆盖索引
要查询的字段全是索引中的字段,这样就不用回表去聚簇索引找数据了
索引注意事项
-
只为where,group,order中的字段建立索引
-
考虑列的基数,基数小没必要建索引,例如性别就不适合索引,二分也只能分出来男女
-
索引类型尽量小,省空间,同时减少遍历和比对的时间(例如比对varchar)
-
字符串最左前缀 abc%
-
主键用顺序自增插入,随机id插入时要改变索引结构,消耗太大(- 这就是为什么建议用自增或有序的雪花算法id 不用uuid
-
索引列不要用表达式或函数(索引无法确定未知的结果,也就无法拿到确定的结果去索引里找
-
不要建过多或重复冗余的索引,例如name和name_age
还有更多的细节注意项,例如
-
字段强制类型转换,varchar类型的字段条件查询必需加''号
-
not 、!=
-
or 改成in,o是O(n) in 是 O(log(n))
-
字段默认值null值
-
join时两边字段类型要相同
-
join被驱动表的连接列加上索引
其他sql优化这里略过
6. 查询优化
Explain
使用explain查看执行计划,根据字段分析查询优化器采用什么路线执行查询,例如是否走索引,可能走哪些索引,访问方法,排序使用索引还是文件排序等
访问方法
单点区间
const:常数级,等值精确查询,where id = 2 确定只返回一条记录
ref:等值范围,where name = '张三' 确定返回了3天记录
ref_or_null:等值范围或null,等值范围可能出现null值的情况
范围区间
range:范围匹配,where age in (16,17,18,19) or (age >35 and age < 40);返回一个范围值
index:直接遍历二级索引且不用回表,例如
#有对part1,part2,part3的联合索引,索引覆盖了,不用回表,条件也不是最左前缀,就直接在二级索引上遍历了
select part1,part2,part3 from t_part where part3 like 'cc%';
all:全表扫描,聚簇索引上一条一条遍历
index_merge:索引合并,多个索引取交集,快于all
-
intersection,多个二级索引取交集(建议用联合索引替代intersection)
-
索引列要等值匹配
-
主键可以范围匹配
-
-
union
- 并集,条件or的情况
-
sort-union
- 取并集前排序
阿里规范sql至少达到range级别。
explain format=json ,可以让执行计划以json形式输出,可以看到查询优化器预估的执行成本
执行完后可以show warnings \g 来查看警告,code为1003时的message,是优化器重写后的sql
完整的使用 optimizer trace 功能的步骤总结如下:
1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
2. 这里输入你自己的查询语句
SELECT ...;
3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
4. 可能你还要观察其他语句执行的优化过程,重复上边的第2、3步
...
5. 当你停止查看语句的优化过程时,把optimizer trace功能关闭
SET optimizer_trace="enabled=off";
join连接原理
内连接的on 和 where等价
外连接on是连接条件 where过滤条件,一个是连接时筛选,应该是连接后过滤
2.1 Index Nested-Loop Join(索引嵌套循环连接)
驱动表访问一次,一条一条去被驱动表找,避免笛卡尔积
Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。
2.2 Block Nested-Loop Join(块嵌套循环连接)
驱动表访问一次,一块一块去被驱动表找,数据暂存join buffer pool
如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录再加载到内存匹配,这样周而复始,大大增加了IO的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。
2.3 Hash Join
计算散列值等值连接匹配。
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
-
Nested Loop:对于被连接的数据子集较小的情况下,Nested Loop是个较好的选择。
-
Hash Join是做
大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列值,然后扫描较大的表并探测散列值,找出与Hash表匹配的行。-
这种方式适用于较小的表完全可以放入内存中的情况,这样总成本就是访问两个表的成本之和。
-
在表很大的情况下并不能完全放入内存,这时优化器会将它分割成
若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高I/O的性能。 -
它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。Hash Join只能应用于等值连接,这是由Hash的特点决定的。
-
关于优化
慢查询,开启慢查询日志,慢查询工具
避免用子查询,用join代替
排序,分组同索引那块,排序分组出来的数据尽量不超过1000条,排序分组到程序做
分页:
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;
7. BufferPool缓冲区
-
磁盘太慢,用内存作为缓存很有必要。
-
Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间,可以通过
innodb_buffer_pool_size 来调整它的大小。
-
Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成,每个控制块和缓存页都是一一对应的,在
填充足够多的控制块和缓存页的组合后, Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页,
这部分空间不能被使用,也被称为 碎片 。
-
InnoDB 使用了许多 链表 来管理 Buffer Pool 。5. free链表 中每一个节点都代表一个空闲的缓存页,在将磁盘中的页加载到 Buffer Pool 时,会从 free链
表 中寻找空闲的缓存页。
-
为了快速定位某个页是否被加载到 Buffer Pool ,使用 表空间号 + 页号 作为 key ,缓存页作为 value ,
建立哈希表。
-
在 Buffer Pool 中被修改的页称为 脏页 ,脏页并不是立即刷新,而是被加入到 flush链表 中,待之后的某
个时刻同步到磁盘上。
-
LRU链表 分为 young 和 old 两个区域,可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首
次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部,在 innodb_old_blocks_time 间隔时间内访
问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时,会首先淘汰掉 old 区
域的一些页。
-
我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数,每个 Buffer Pool 实
例中都有各自独立的链表,互不干扰。
-
自 MySQL 5.7.5 版本之后,可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若
干个 chunk 组成,每个 chunk 的大小可以在服务器启动时通过启动参数调整。
-
可以用下边的命令查看 Buffer Pool 的状态信息:
SHOW ENGINE INNODB STATUS\G
8. 事务
四个特性
-
原子性:操作原子,要么都成功要么都失败(undo)
-
一致性:执行前后的数据保持一致(undo)
-
隔离性:多个事务之间隔离,不能相互影响(mvcc,锁机制)
-
持久性:事务执行后的影响永久存在(redo保证)
五个状态
-
活动的:事务处于执行状态
-
部分提交的:事务执行完毕数据还没刷盘
-
失败的:事务执行过程出现错误
-
终止的:事务执行回滚,撤销
-
提交的:事务提交成功
开启事务
显示
begin | start transaction
commit | rollback | rollback to [savepoint]
-
savepoint 保存点名称
-
release savepoint 保存点名称
隐式
开启系统变量auto_commit = on
ddl语音
-
隐式修改或使用mysql的表
-
事务控制语句,例如lock tables手动上锁等
-
加载数据的语句,mysql复制语句或其他语句
关闭事务:
-
系统变量 auto_commit = off
-
显式开启一个事务
数据并发问题
两个事务,Session A 和 Session B
| 问题 | 描述 |
|---|---|
| 脏写 | A修改了B修改没提交的数据 |
| 脏读 | A读取到了B修改没提交的数据(B此时回滚,A读得脏数据无效) |
| 不可重复读 | A在两次查询期间,B修改并提交了数据,A前后的数据不一致 |
| 幻读 | A在两次查询期间,B对查询的表插入了数据,且没提交,A两次查询的数目不一致(查询到了B插入没提交的数据) |
- A两次查询事务期间,B删除一些数据导致A第二次查询少了某些数据,属于不可重复读。(不可重复强调前后数据不一致,幻读强调读到之前没有的记录)
事务隔离级别
| 类型 | 描述 |
|---|---|
| 读未提交 | 解决脏写,事务之间可看见相互的未提交事务的结果 |
| 读已提交 | (大部分sql默认):解决脏写,脏读,事务之间只能看见已经提交事务的结果 |
| 可重复读(mysql默认) | 解决脏写,脏读,不可重复读,MVCC机制保证事务间不可重复读数据不一致的问题 |
| 串行化 | 解决脏写,脏读,幻读,不可重复读, |
隔离级别越高,效率越低
使用:
set [global|session] transaction_isolation = 'read-uncommitted | read-committed | repeatable-read | serializable'
或
set [global | session] transaction isolation level read uncommitted | read committed | repeatable read | serializable
global 只对后续的会话生效
session只对当前会话后续事务生效
启动项设置:
transaction-isolation=xxx
9. 事务日志
Redo Log
保证持久性,用于宕机恢复
-
min-transaction mtr:每个事务由若干sql语句组成,每个语句由若干mtr组成(新增时可能分配数据页,页分裂,申请资源等)
-
redolog记录修改过的值的偏移量,有一个type记录区分各个大小的修改数据,类型为1时代表只有一条redolog。
-
当redolog需要刷盘时,先把多个redolog按照mtr分成一组redolog buffer中,然后才刷新到磁盘中。
-
事务提交时,数据不立马刷盘,生成redolog,redolog按照mtr分组被刷到redolog buffer中
-
redolog buffer是一片连续的空间,用于作缓存,redolog buffer把数据追加写到redolog file中
-
redolog file可有多个,写满时从头开始追加。(日志文件组)
-
redolog file有checkpoint机制,分别有几个变量记录新追加的位置,刷盘成功的数据位置(可以被擦除了),当前记录的位置,(二者中间就是还未刷盘的数据)
-
当需要追加的位置干到了checkpoint时,就需要停下等他写入,checkpoint后移才能继续追加。
-
redo log刷盘时会把修改过的页追加到flush链表,更新buffer pool的缓存。
redo log 刷盘策略
-
0:每次提交不立马同步redolog,完全交给后台线程做,(系统默认1秒同步一次page cache)
-
1:每次事务提交时都把redolog同步到磁盘,(默认值)
-
2:只把redo log buffer 同步到page cache,不落盘,系统决定何时落盘,(系统挂了数据丢失)
Undo log
保证原子性,用于事务回滚,MVCC
逻辑上的日志,本质是多个undo链表,记录要撤销的信息,利用隐藏列的trc_id,依次对数据进行回滚操作
每个undo链表都在一个undo段中,undo链表通过记录该记录的前后节点指向来确定要撤销记录的信息。
不同事务执行的undolog分配到不同的undo链表中(为了提高并发)
不同的undo链表可以重用(节省资源)
undo链表可分为insert undo链表 和update undo链表
-
insert undo链表,提交后旧数据没用,可直接覆盖以重用。
-
update undo链表,为保证mvcc机制,不能直接覆盖,再同一个页面写入多组undo日志。
回滚段:
方便管理多个undo链表,对其分组,有多个回滚段,默认128个
-
redolog是物理日志,确保事务的数据最终落盘。持久性。(追加写文件,刷盘策略,redobuffer)
-
undolog是逻辑日志,确保错误或事务回滚时数据修改能撤销。一致性。(undo链表,trc_id,回滚段,insert_ update链表复用,mvcc)
10. MVCC
mvcc(mutil version concurrent control),多版本并发控制,为了解决事务中的幻读问题。
本质是基于记录里的隐藏列trx_id和rollbackpoint,undolog链表,和readview读视图,共同形成一个版本链。
-
trx_id:当对数据进行改动时,把该事务的事务id赋值给它
-
roll_point:当数据改动时,把undo操作写入undo日志中,把对应的point记录到该值,记录该次改动的版本。
-
readview:事务启动时生成当前事务的快照。
-
快照读:在进行数据读取时读取的是快照,不保证是最新的数据,基于mvcc的readview,不用加锁,提高性能
-
当前读:进行数据读取时永远读取的最新数据,通过锁机制实现,加锁的select,增删改操作都会加锁。
readview执行流程:
-
获取自己的版本号
-
获取readview
-
查询得到的数据与readview事务版本号比较
-
如果不符合readview规则,就从undo log中获取数据
-
返回符合规则的数据
read_uncommited:其他事务读取时直接读取最新版本记录,不需要mvcc。(事务之间的读取操作受影响)
read_commited:该隔离级别下,readview在每次读取数据前都生成一个readview,readview在读取时会进行校验,判断是否返回对应版本的历史记录
repeated-read:该隔离级别下,readview只有在第一次读取数据前才会生成一个readview,当后续读取时都只读到事务前的数据,也就保证了可重复读。不完全解决幻读。
读已提交和可重复读的本质区别就是readview生成的时机不同来保证。
mysql中如何在可重复读级别下解决不可重复读?
解决不可重复读有两种方式
-
读写加锁
-
读走mvcc,写加锁
不可重复读是同一个事物在两处查询中,别的事物对其修改了导致数据变化了,而mvcc在可重复读级别下,第一次查询前生产一个readview,之后的每次查询都会复用该readview,就避免了不可重复读。
mysql怎么解决不同隔离级别下的并发问题
脏写:写操作加锁
脏读:mvcc,每次读取前生成readview读视图,确保可以读取到已经提交的数据,或者读取数据前加锁,防止期间其他事务修改数据
不可重复读:mvcc,查询是读取第一次生成的readview读视图,之后的每次查询都是复用该视图,别人修改新增数据不影响
幻读:分为当前读和快照读
-
当前读:采用间隙锁和行锁的方法,select for update,新增时加间隙锁,解决幻读
-
快照读:mvcc,读的快照,保证事务期间看不见新增数据
特别注意:对于修改的语句,有时还是会出现幻读。
- 例:事务1查询age为18的用户,事务2新增了一个age为18的用户,事务1此时修改age为18的用户昵称都叫暴龙战士,再查询,此时会把事务2新增的用户查出来,也就出现了幻读。
其实幻读解决到这个层面上对业务影响不大了。
11. 锁
实现事务各个隔离级别,解决最基本的脏写。
并发情况:
读-读情况:并发事务相继进行读操作,不会影响记录
写-写情况:对相同的数据进行写操作,需要加锁排队执行
读-写情况:一个事务在读取时,另一个事务同时对它读取的数据进行写操作,此时会出现脏读,不可重复读,幻读问题。
锁的分类:
按数据类型:
读锁(共享锁)S锁:多个请求可以同时读,但是不能同时修改
写锁(排他锁)X锁:一个资源获取到锁,只有该资源可以进行写入,其他资源获取时阻塞
select 。。。lock in share mode | for share (查询加读锁)
select。。。for update(查询加写锁)
5.7之前:x锁会阻塞等待到timeout
8.0可在for update之后指定 no wait 和skip locked
-
no wait 阻塞时不等待直接报错返回
-
skip locked 只返回没加锁的行,跳过锁住的记录
按锁粒度:
表级锁
不依赖存储引擎,锁住整个表,锁粒度大,竞争大,并发冲突大,开销最小
-
当执行ddl(alter drop)时其他事务并发执行增删改查语句时,会发生阻塞,反之同理
-
server层的元数据锁实现
-
MyISAM查询时默认是表级读锁,增删改是写锁
-
一般情况下不会用到
-
lock 表名 t read;
-
lock 表名 t write;
-
-
意向锁:向更大一级粒度标注该表是否有其他事务使用,避免一个一个判断各页或各行是否加锁。
-
存储引擎自己维护
-
自增锁:自增时会加上特殊的锁
-
简单插入:事先确定了要插入的行数
-
批量插入:事先不知道要插入的行数
-
混合模式插入:在插入时需要上自增锁,其他事务无法执行新增操作,innodb_autoinc_lock_mode
innodb_autoinc_lock_mode:
-
0:传统模式:所有insert语句都会获得一个表级锁,多个insert会争夺锁
-
1:连续锁定模式:批量插入时用自增锁,简单插入时用轻量锁的控制下获取等量的自增值
-
2:交错锁定模式:mysql8.0默认,所有insert语句都不使用表级自增锁,在binlog日志恢复时可能会出现问题,主键值不连续。
-
-
-
元数据锁(MDL,meta data lock):保证数据正确性的表级锁,增删改查加读锁,ddl操作加写锁。
页级锁
- 页的粒度上加的锁,开销介于表锁和行锁之间,并发效率一般。会出现死锁
行锁
存储引擎层面实现,行级别的锁,粒度为记录
-锁的粒度小,锁的冲突概率低,并发效率高
-锁的开销大,加锁慢,容易出现死锁
-
记录锁
单条记录级别的锁,分S锁和X锁
-
间隙锁(gap锁)
锁住某个范围,专门用于解决幻读(第一种方式是mvcc,第二种方式则是间隙锁)
-
临键锁(innodb默认)
gap锁的一种,锁住某条记录,以及其之前的记录,作用是在插入时避免别人再他前面的间隙插入新记录
-
插入意向锁
在插入一条记录前,有其他的gap锁,此时该条记录会产生一条插入意向锁,代表该条记录想要插入,但是前边有gap锁阻塞
12. 其他日志
-
慢查询日志(slow query log):开启慢查询,超过指定时间的sql标记为慢查询,用于分析sql,sql优化
-
通用查询日志(general query log):作sql的统计,所有连接的起始时间和终止时间
-
错误日志(error log):记录mysql启动运行或者停止时出现的问题
-
二进制日志(bin log):所有更改数据的语句,可用于主从同步(中间件mycat),(分布式事务也是基于此),数据备份和故障恢复
-
中继日志(delay log):主从架构,主从同步时中继主库的binlog,从库读取delay log来同步主库操作
-
数据定义语句日志(DDL log):记录数据定义语句执行的元数据操作
bin log 日志
是一个二进制文件,记录所有的数据更改信息,可用于主从同步
刷盘:
事务执行过程中,数据放入bin log cache,事务提交时,再把binlog cache写入bin log file中(事务要确保一次性写入)
刷盘时机:
sync_binlog:
-
0(默认),每次提交事务只写入,系统决定何时刷盘(宕机丢失)
-
1,每次提交事务都刷盘
-
N,每N个事务提交后刷盘
两阶段提交
事务提交时,binlog和redolog都会写入,二者刷盘时机不一样,(binlog一次性,redolog边写边擦),为了保证二者数据同步,使用两阶段提交的方式
redolog -》prepare
binlog -》写入
redolog -》commit