MySQL8学习脑图
2025-07-07 18:34:12 35 举报
AI智能生成
MySQL8学习脑图
作者其他创作
大纲/内容
SQL执行过程
总体流程
客户端 -> 连接器 -> 分析器 -> 优化器 -> 执行器 -> 存储引擎
Update流程
1、执行器请求存储引擎读旧数据
2、存储引擎从 BufferPool 读取数据
3、若 BufferPool 不存在,则从数据库文件加载所在的页到缓存
4、存储引擎将数据存入 undo log 的版本链中,然后返回给执行器
4、执行器修改数据后提交给存储引擎,存储引擎存入 BufferPool
5、存储引擎向 redo log buffer 中写入数据,标记状态为 prepare
6、根据持久化策略,redo log buffer 将数据写入 redo log 文件
7、执行器将 Update操作 写入 binlog
8、事务提交且写入binlog成功后,将 redo log 中的 prepare 状态变更为 commit
9、Buffer Pool 中的新数据所在的页写入 Double Write Buffer 文件
10、Buffer Pool 中的新数据所在的页写入 idb 数据库磁盘文件
内部二阶段提交
过程:先写redo log,状态为prepare -> 再写 binlog -> redolog 状态变更为 commit
意义:保证 redo log 和 binlog 的一致性
索引
作用
通过数据有序存储,减少检索数据时的查找次数,从而提高检索速度
结构:B+树
特征
1、从左到右,索引值单调递增
2、一个节点的大小为一页(InnoDB Page Size,默认 16KB)
2、非叶子节点冗余存储部分的索引值;叶子节点存储全量的索引值及数据
3、叶子节点之间采用指针相连
优势
1、非叶子节点只存储索引值,所以一个节点能存储大量元素,树的高度低,有效减少检索次数,效率高
2、叶子节点之间存在相邻指针,范围查询时可利用相邻指针提升检索效率
使用方式
增删改操作
1、节点内元素增加/删除/修改
2、节点分裂/合并
3、树增高/变矮
查询操作
1、节点树向下递归
2、节点内二分查找
3、相邻指针,快速移动
类型
按叶子节点存储元素划分
聚簇索引
索引值:主键
叶子节点数据:完整列
二级索引
索引值:非主键
叶子节点数据:主键
按索引值包含的列数划分
单列索引
联合索引
查询优化
SQL语句优化
充分利用最左前缀匹配
查询字段覆盖索引,规避回表
order by、group by 尽可能利用索引
in 查询条件不要包含太多元素
避免对索引列操作导致索引失效
统计记录数使用 count(*)
分页查询
根据查询条件生成只包含主键的派生表,再与原表通过主键关联
翻页场景:把上一页最后一条记录的自增主键,放到当前页的条件中
维护额外字段,将范围查询转化为等值查询
join 不超过三张表,更多的 join 放到应用程序中
索引优化
join 在大表的连接字段加索引
使用区分度大的字段或字段组合
长字符串使用前缀索引
where 和 order by 冲突,优先 where
1-2个联合索引涵盖大部分业务场景,若干辅助索引涵盖其余场景
其他优化
在应用程序中进行复杂逻辑操作,而不是SQL中
先开发主体业务,再创建索引
大字段分表存放
开启慢SQL监测
MySQL不擅长的交给其他组件
利用trace工具判断实际执行的优化路径
技术补充
ICP 索引下推
join算法
filesort
Explain执行计划
查看方式
EXPLAIN + 原SQL语句
包含字段
id
id越大越先执行,id相同排在前面的先执行
select type
simple
简单查询
primary
复杂查询的外层语句
subquery
复杂查询 where 之前的子句
derived
复杂查询 where 或 in 之后的派生表
table
查询的表
partitions
查询的分区
type
索引使用的类型
ALL
全表扫描
NULL
无需进入执行阶段
system
使用子查询作为派生表,且子查询必然返回一条记录
const
使用唯一索引进行等值查询
eq_ref
使用唯一索引进行等值关联查询
ref
使用非唯一索引查询,或唯一索引的前缀查询
range
使用索引进行范围查询
index
查询列覆盖索引
possible_keys
可能用到的索引
key
实际使用的索引
key_len
索引的长度
ref
与索引比较的值的类型
rows
存储引擎预估的返回记录数
filtered
实际返回行数 / rows
extra
额外信息
using index
查询列覆盖索引,且 where 条件是索引前导列的精确匹配
using where; using index
查询列覆盖索引,且 where 条件是索引前导列的一个范围,或索引的非前导列。
Null
查询列未覆盖索引,且 where 条件是索引前导列的精确匹配
using index condition
查询列未覆盖索引,且 where 条件是索引前导列的一个范围,使得 ICP 索引下推技术被使用
using where
查询列未覆盖索引,且 where 条件是索引的非前导列,或非索引列。无法利用索引有序性检索
using temporary
使用临时表。无法利用索引有序性检索
using filesort
使用文件排序。无法利用索引有序性检索
事务
ACID
原子性
一个事务内的操作要么都成功,要么都不成功
通过 undo log 实现
一致性
一个事务完成后,约束状态不变
通过其他三个特性实现
隔离性
并发事务之间相互隔离,互不干扰
通过锁、MVCC实现
持久性
事务一旦提交成功,状态永久生效
通过 redo log 实现
隔离级别
读未提交
概念
一个事务可以读到其他事务未提交的内容
存在问题
脏读、脏写
读已提交(RC)
概念
一个事务可以无法读到其他事务未提交的内容,可以读到其他事务已提交的内容
解决问题
脏读、脏写
存在问题
不可重复读、幻读
可重复读(RR)
概念
如果事务A开启时,事务B尚未提交,则A无法读到B后续提交的内容
解决问题
不可重复读、幻读
串行化
概念
一个事务的读操作会阻塞其他事务的写操作,一个事务的写操作会阻塞其他事务的读写操作
解决问题
以上所有并发读写问题
存在问题
并发度低
性能优化方法
降低隔离级别
缩减事务规模
准备工作放在事务外
远程调用尽量异步、设置超时时间
事务拆分
加锁操作放在事务的靠后位置
业务代码处理异常回滚,代替事务
锁
按性能分类
悲观锁
获得锁才能修改
乐观锁
修改时比较数据是否被其他事务修改,若是,则重新获取最新数据后重试
按操作类型分类
读锁(S)
写锁(X)
意向锁(I)
按操作粒度分类
表锁
行锁
间隙锁(GAP)
只在 RR 隔离级别生效
临键锁(Next Key)
定义
行锁与间隙锁的结合
作用
解决 RR 隔离级别下,当前读的幻读问题
锁相关问题排查
查看锁等待统计情况
show status like 'innodb_row_lock%';
查看事务列表
select * from information_schema.INNODB_TRX;
查看使用中的锁
select * from performance_schema.data_locks
查看正在等待的锁
select * from `performance_schema`.data_lock_waits
查看最近一次死锁
show engine innodb status; 然后找到deadlock
查看应用程序日志
锁的优化
数据修改时,利用索引检索数据
范围查找时,减少搜索范围
控制事务大小
降低事务级别
MVCC
作用
InnoDB引擎通过MVCC机制,实现事务隔离性
关键技术
undo 版本链
为数据库中的每条记录维护一个版本链
read view
包含数据
每个事务为自己维护 read view,包含所有未提交的事务id集合 + 当前最大的事务id
更新时机
RC隔离级别:每次查询时
RR隔离级别:事务开始时
可见性算法
快照读时,根据 read view 决定当前事务对哪个事务之前的修改可见,然后从版本链中找到最近可见的版本
补充内容
事务开始时机
事务内的第一次DML或select for update 才标志真正的事务开始,此时会分配一个唯一的、递增的事务ID
关键存储空间
Buffer Pool
作用
要被读取的页从磁盘加载到 Buffer Pool,事务提交时写回磁盘,从而提升读取速度
数据结构
利用 LRU 算法构造的链表,新加入元素从中间插入,被访问时移到最顶端,最底端的数据不断被淘汰
double write buffer
作用
在页被写回数据库文件前,先顺序写一份到 double write buffer 文件。如果写入数据库文件是发生页损坏,则可以从 double write buffer 中恢复
redo log
作用
记录数据所在页的物理更改,若事务提交后,在 Buffer Pool中的数据持久化到数据库文件前崩溃,则重启后可根据 redo log 文件恢复
数据结构
一些列固定大小文件组成的环形结构,由 write_pos 和 checkpoint 指针进行标记
持久化策略
数据不会直接写入 redo log 文件,而是先写入 redo log buffer 这个内存,而后根据持久化策略写到 redo log 文件
undo log
作用
存储了一条记录的各个版本。每个事务对该记录的变更都会在 undo log 的版本链中追加一条记录。
在MVCC算法中,根据事务的可见性决定快照读的读取版本
在事务回滚时,将从 undo log 中还原 Buffer Pool 中对应的数据
binlog
关键参数
MySQL8新特性
高可用
收藏
0 条评论
下一页