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