极客时间 MySQL45讲
2022-01-21 17:22:35 0 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
极客时间 MySQL45讲
作者其他创作
大纲/内容
逻辑架构图
连接器、查询缓存、分析器、优化器、执行器等
负责跟客户端建立连接、获取权限、维持和管理连接
作用
mysql -h$ip -P$port -u$user -p
认证
超时退出wait_timeout: 8h
空闲状态
Lost connection to MySQL server during query
连接被断开之后，客户端再次发送请求
如果客户端持续有请求，则一直使用同一个连接
定期断开长连接
通过执行 mysql_reset_connection 来重新初始化连接资源
解决方案
MySQL 占用内存涨得特别快
长连接
指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个
短连接
流程
连接器
以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果
拿到一个查询请求后，会先到查询缓存看看
执行结果会被存入查询缓存中
查询流程
适用于系统配置表，更新少
不适用频繁表的更新，缓存会频繁失效
优缺点
将参数 query_cache_type 设置成 DEMAND
select SQL_CACHE * from T where ID=10；
按需使用
使用方式
span style=\
查询缓存
词法分析
语法分析
分析器
决定使用哪个索引；
多表关联（join） 决定各个表的连接顺序
优化器
判断执行权限
根据表的引擎定义，使用引擎接口
调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
慢查询日志中 字段rows_examined
执行器
问题
Server 层
负责数据的存储和提取。
架构模式是插件式的，
支持 InnoDB、MyISAM、Memory 等多个存储引擎。
存储引擎层
逻辑架构
01 | 基础架构：一条SQL查询语句是如何执行的？
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存
InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面
WAL 技术
固定大小 4个文件
当前记录的位置，一边写一边后移
write pos
当前要擦除的位置
往后推移并且循环
擦除记录前要把记录更新到数据文件
checkpoint
记录新的操作
空的时候
擦掉一些记录，把 checkpoint 推进
write pos 追上 checkpoint
原理
InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失
crash-safe
redo log
InnoDB 引擎特有的
物理日志
循环写的，空间固定会用完
MySQL 的 Server 层实现的
逻辑日志
追加写入
binlog
不同点
1.执行器取数据
2.执行器拿到数据+1，写入
3.引擎更新到内存，更新记录到redo log，处于prepare状态
4。生成binlog 写入磁盘
5. 提交事务 redolog状态更新
执行器和InnoDB 引擎 Update流程
redo log 写完，binlog 还没有写完 ，崩溃恢复
binlog 缺少一条数据 恢复数据后 与原库不同
先写 redo log 后写 binlog
binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复
binlog 多了一个事务，恢复数据与原库数据不同
先写 binlog 后写 redo log
为什么
过程
为分布式事务
初衷
详解
参考文章
如何判断binlog和redolog是否达成了一致
设置成 1 ，每次事务的 redo log 都直接持久化到磁盘
innodb_flush_log_at_trx_commit
设置成 1 ，每次事务的 binlog 都持久化到磁盘
sync_binlog
参数设置
两阶段提交
02 | 日志系统：一条SQL更新语句是如何执行的？
原子性
Atomicity
一致性
Consistency
隔离性
Isolation
持久性
Durability
ACID
脏读（dirty read）
幻读（phantom read）
多个事务出现的问题
读未提交（read uncommitted）
读提交（read committed）
可重复读（repeatable read）
串行化（serializable ）
隔离级别
读未提交
读提交
可重复读
串行化
例子
数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
实现方式
MYSQL
ORACLE
默认级别
transaction-isolation
配置方式
隔离性与隔离级别
MVCC及实现原理
多版本并发控制（MVCC）
原理图
系统里没有比这个回滚日志更早的 read-view 的时候
当没有事务再需要用到回滚日志
什么时候删除回滚日志
回滚记录占用空间
拖垮整个库
占用锁资源
什么建议你尽量不要使用长事务
事务隔离的实现
start transaction
commit
rollback
显式启动
set autocommit=0
推荐set autocommit=1
事务的启动方式
采用begin或star transaction
使用commit work and chain
如何避免长事务的出现?
思考题
03 | 事务隔离：为什么你改了我还看不见？
添加快
优点
不适合做区间查询 要全部扫描
缺点
适用等值查询
适用场景
哈希表
二分法
等值查询
二分法 区间
范围查询
修改速度慢，需要挪动数据
O(log(N))
时间复杂度
适用于静态存储引擎
有序数组索引
特点
更新和搜索 O(log(N))
树高过高 查询更新慢
二叉搜索树
读写速度快
“N 叉”树
索引的常见模型
聚簇索引（clustered index）
主键索引
二级索引（secondary index）
非主键索引
索引类型
主键查询方式
普通索引查询
基于主键索引和普通索引的查询有什么区别？
B+树
InnoDB 的索引模型
递增插入
中间插入
性能受影响
空间利用率降低大概50%
页分裂
插入数据页已满
页合并
相邻的两个数据页利用率很低
性能
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
存储空间
为什么要推荐使用自增主键
只有一个索引
该索引必须是唯一索引
什么场景适合用业务字段直接做主键的呢？
自增主键场景
索引维护
04 | 深入浅出索引（上）
回到主键索引树搜索的过程
回表
只需要查 ID 的值，而 ID 的值已经在 k 索引树上
是否有必要将身份证号和名字建立联合索引？
覆盖索引
联合索引的最左 N 个字段
字符串索引的最左 M 个字符
where name like ‘张 %
如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
空间原则
在建立联合索引的时候，如何安排索引内的字段顺序
最左前缀原则
解释
MySQL 5.6 之前
5.6以后
索引下推
05 | 深入浅出索引（下）
MySQL45讲
0 条评论
回复 删除
下一页
