MySQL核心深入剖析
2021-03-30 18:58:34 26 举报AI智能生成
MySQL核心剖析
作者其他创作
大纲/内容
并发和事务
事务基础
事务的定义
事务的特点
事务的基本操作
设置手动提交事务
并发事务问题
脏读
不可重复读
幻读
事务隔离级别
查看事务隔离级别
读未提交
读已提交
可重复读
串行化
MySQL的锁机制
表级锁
读锁(read lock)
写锁(write lock)
加锁和释放锁
查看表锁使用情况
元数据锁
查询语句的时候会自动添加元数据读锁
对于MDL写锁,在执行完成之后,会自动释放,和当前的事务没有关系
行级锁
共享锁(S)
排它锁(X)
加锁和释放锁
行锁的算法
临键锁
对于加锁的基本单位就是临键锁next key lock, 其中临键锁的规则是前开后闭
对于在执行过程中扫描到的记录都会加锁
对于索引上等值查询,如果查询条件是唯一索引(包括主键索引), 那么临键锁会退化为记录锁(Record Lock)
对于索引上的等值查询, 会继续扫描到第一个不符合条件的记录值, 转换为间隙锁(GAP Lock)
唯一索引上的范围查询,会访问到不满足条件的第一个值为止
间隙锁
记录锁
意向锁
MVCC并发原理剖析
隐藏字段
Undo Log
一致性视图(Read View)
案例分析
InnoDB存储架构
Buffer Pool
磁盘预读
局部性原理
数据淘汰算法
Change Buffer
Log Buffer
课程说明
课程目标
掌握MySQL常见的存储引擎
掌握MySQL的数据存储结构B+树
掌握MySQL的索引优化
掌握MySQL的事务和锁
掌握MVCC原理和实现
掌握binlog,undolog,redolog日志系统
掌握回表, 索引覆盖,索引下推, 最左前缀等高级概念
掌握InnoDB内存架构
课程定位
有MySQL的基本使用经验
有一定的开发经验, 并且对数据结构和算法有基本的认识
参考资料
官方文档
丁奇-MySQL实战45讲
MySQL技术内幕(InnoDB存储引擎)第2版
公众号架构师之路
MySQL数据库架构
MySQL的基本认识
MySQL数据库架构
常见的存储引擎
InnoDB
MyISAM
MEMORY
Archive
NDB
常见命令
查看默认的存储引擎
查看存储引擎
指定存储引擎
查看文件存储路径
区别
索引
索引基础
什么是索引
为什么需要使用索引
设置初始数据
测试查询性能
索引分类
主键索引
唯一索引
普通索引
查看所有信息
基本操作
创建索引
主键索引
唯一索引
普通索引
添加索引
删除索引
复合索引
索引结构
Hash
Hash结构模拟
Hash算法基本流程
根据id 进行hash运算, 获取到对应的hash值22
进行取模运算 22%7=1, 找到对应的位置
判断位置上是否有值, 如果有, 则插入链表尾部, 如果没有, 插入对应的数据
Hash算法的优缺点
单值查询非常快
hash索引范围查询比较麻烦
hash索引结构没有顺序, 对数据排序需要重新进行排序
hash索引, 不能使用部分索引, 比如前缀索引, 以及像 like 'xxx%'模糊查询
如果大量的hash值相同, 会出现hash碰撞, 也会影响性能
B-Tree
常见的B树
3阶B树
4阶B树
5阶B树
5阶B树特点
一个节点可以存放多个数据(值) , 其中最多可以是4个数据
这是一个多叉树, 一个节点可以有多个子节点, 其中最多是5个子节点
数据是有顺序的, 查找速度比较快
整个树是处于 又胖又矮 的一个状态
树比较平衡, 每个节点的所有的子树高度比较一致
B树索引结构图
B+Tree
常见的B+树
3阶B+树
4阶B+树
5阶B+树
特点
在叶子节点存储了所有的节点数据
叶子节点直接通过链表的方式连接
B+树索引结构图
索引架构
MyISAM
主键索引
辅助索引
InnoDB
主键索引
辅助索引
聚镞索引
索引的选择
好处
提高表数据的检索效率
如果排序的列是索引列，大大降低排序成本
在分组操作中如果分组条件是索引列，也会提高效率
不足
增加数据的维护成本, 新增, 删除, 修改的时候, 需要对索引的结构进行修改维护
如何选取索引
较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件, 比如性别
更新非常频繁的字段不适合创建索引
不会出现在WHERE 子句中的字段不该创建索引
索引分析工具
Explain
基本使用
核心字段
id
select_type
union
table
type
possible_keys
key
key_len
ref
rows
Extra列
Profiling
开启
监控SQL语句
trace
开启trace
跟踪分析SQL
索引高级
索引的选择性
回表
索引覆盖
索引下推ICP
前缀索引
索引优化
前缀索引优化
最左前缀匹配
复合索引优化
最左前缀字符串匹配
范围查询
函数转换
Join优化
连接查询的基本流程
Index Nested-Loop Join
Simple Nested-Loop Join
Block Nested-Loop Join
优化原则
小结果集驱动大结果集
尽量使用Index Nested-Loop Join, 给匹配的条件添加索引
给定 join buffer size 设置适当的大小, 增大内存区域以后, 缓存的数据量就会越多, 就会减少被驱动表的扫描次数, 我们可以通过参数join_buffer_size进行调整
减少不必要的字段的查询
排序优化
排序原理
排序算法
单路排序
双路排序
排序的几点思考
排序的优化
尽量保证使用单路排序
去掉不必要的返回字段
适当增加sort_buffer的大小
SQL优化的常用原则
创建的索引的选择性需要比较高, 有比较的区分度, 对于性别等字段不适合创建索引
强烈建议不要使用select * , 如果确实需要所有的字段, 建议全部写出来
对于常用的查询尽可能多用复合索引, 避免创建多个单个索引
推荐使用自增的和业务无关的主键
查询尽量使用索引, 包括索引覆盖, 索引下推等特性, 减少回表的次数
对于where 判断条件, order 排序字段, join 关联字段推荐创建索引
索引的个数不用太多
对于频繁更新的字段, 不适合创建索
对于Join连接尽量使用Index Nested-Loop Join方式, 尽量避免使用Block Nested-Loop Join
对于Join连接,使用小结果集驱动大结果集
对于Join连接可以适当的调整join_buffer_size
对于Join连接减少不必要的查询字段
对于order by 排序的时候, 尽量使用索引列, 并且使用limit限制查询数量
对于order by 查询的时候, 尽量使用单路排序
适当的设置InnoDB的buffer大小innodb_buffer_pool_size, 一般设置为内存的60%-80%
优化案例
filesort 优化
连接优化
复合索引_前缀索引
排序优化
收藏
收藏
0 条评论
下一页
