MySQL 探秘笔记

由SQL语句的执行过程引出 MySQL 核心组件

1.客户端发送 SQL 语句给 MySQL.
2.MySQL 的 Server 层与客户端通信, 得到 SQL 语句.
3.Server 层先检查缓存, 后调用分析器.
4.分析器解析 SQL 语句得到解析树, 然后调用优化器.
5.优化器基于成本控制来寻找较优解, 如逻辑转换/选取索引/计算成本/改进计划, 最终得到查询计划; 然后调用执行器
5.执行器根据查询计划执行存储引擎并获取返回结果再返回给客户端.


# 存储引擎 InnoDB
0.先根据语句的 where 查询需要操作的列, 若单条, 单个执行, 多条(未知, 遍历执行不太可能...)
1.由于事务是自动开启的(默认设置), 因此单条语句也会自动被事务包围.
2.所以先写 undo log(默认置于共享表空间), 写这个是用于事务回滚的.
3.接着将修改写入到 Buffer Pool(即修改内存中的值).
4.二阶段提交第一阶段, 写 redo log, 此作用为防止写 binlog 时宕机造成数据不一致(前提是开了 binlog); 这里标记 redo log 为 prepare 状态.
5.接着写 binlog, 若有从库集群, 应该还要等待从库同步.
6.二阶段提交第二阶段, 写完 binlog 后, 将 redo log 标记成 commit, 代表这次提交和 binlog 保持了一致.


# PS 二阶段提交会标记 redo log 为 prepare 状态, 这样如果数据库宕机(在写 binlog 时)再重启, 读取到这个标志, 就知道提交不是完整的, 于是就要通过判断 binlog 的LSN 做一些处理了.(啥处理我也不知道, 可能是照常提交, 也可能是回滚)

PS: 选取索引只能用一个索引, 除了 union 好像会触发合并索引, 但合并后也算一个索引.

即使是 update 语句也会使用优化器寻找查询计划, 因为当带条件时, 需要先锁定记录, 再进行修改.

几个核心组件的作用

连接器: 网络编程建立端口监听, 接收客户端发送的SQL语句
分析器: 对SQL进行语法、词法上的分析。
优化器: 生成执行计划、选择索引。
- *逻辑转换: *包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；
- *优化准备: *例如索引 ref 和 range 访问方法分析、查询条件扇出值（fan out，过滤后的记录数）分析、常量表检测；
- *基于成本优化: *包括访问方法和连接顺序的选择等；
- *执行计划改进: *例如表条件下推、访问方法调整、排序避免以及索引条件下推。
执行器: 操作执行引擎，获取SQL的执行结果
存储引擎(执行引擎): 负责具体的语句执行, 查询等.

PS: 基数(值某列数据去重后剩余个数, 估算得到) 会被用于分析索引的过滤效果.

然后是存储引擎里面的一些概念

# 存储引擎中
Buffer Pool: 用于缓存表数据的改动, 有自己的落盘时机.

Redo log Buffer: 用于缓存 redo log, 有自己的落盘时机.
	commit 事务时落盘
	可配置 everysec 每秒触发落盘

Binlog Buffer: 用于缓存 binlog, 即记录每个改动, 有自己的落盘时机.
	commit 事务时
		
undo log 文件: 默认存于共享表空间中, 也可单独存放于一个表空间. 保存 undo log, 有瘦身机制.

redo log: 用于保存 Buffer Pool 发送的变动. 防止 Buffer Pool 的脏页未刷新到磁盘就宕机导致数据丢失.
binlog: 记录逻辑表的改动, 可用于集群同步数据, 审计SQL渗透, 备份/恢复数据库.
undo log: 用于保存事务中的改动, 便于事务失败触发回滚时回滚数据.

redo log vs binlog
一个(redo log)保存数据页变动, 而数据页是实际的物理空间加载到内存中的缓存, 所以记录的是物理上的改动.
一个(binlog)保存逻辑上的改动, 比如 xx 表的 id=xxx 的行的 xx 列数据修改为了 xx. 或者哪张表新增了一行, 数据是 xxx...  所以记录的是逻辑上的改动.
两者最大的区别就是 redo log 是大小是有限的, 到了一定的大小, 会将无用的数据删掉, 而 binlog 更是一种备份, 只会越来越大... 而且没法通过瘦身保持某个大小...

事务的实现

# 事务回滚
1.undo log: 分 insert/update, update 又分 update/delete; 但无论啥类型, 目的都是辅助事务回滚, 
	其中 insert 比较简单, 当回滚时根据记录下的主键(联合主键)通过索引找到对应行, 删除即可.
	当回滚时 update 是根据记录的主键找到记录后, 再根据记录下的修改过的列数据, 反向修改回去.
	当回滚时 delete 是根据记录的整行数据, 然后 insert 回去.
2.但一个事务往往不止一条 SQL, 因此也不止一个 undo log, 每增加一条 SQL, undo log 也随之增加, 这些 undo log 会组成一个链条.
2.若为单个事务, 当回滚时, 情况比较简单, 即只需从链条尾部, 反向遍历, 往前回滚每个 undo log 就行了.
3.若为多个事务, 与单个事务类似, 只是还需要注意链条的维护(即维持链条的连通性)

# 隔离级别
1.原理是开启事务会创建一个 ReadView, 其作用是判断 undo log 链条中哪些数据是可读的.
2.先说 ReadView, 其存有当前事务 id, 事务启动时那会未提交的事务 id 列表, 所有未提交事务中最小的事务, 下一个事务id; 这里面未提交的事务是重点.
3.RR: 可重复读, 事务一开启则创建一个 ReadView, 直到事务结束; 接着倒着遍历链条, 直到 undo log 是比自己小且不在未提交事务中的事务记录时停下, 仅获取此时的数据, 由于事务开启过程中, ReadView 不改变, 因此整个事务过程中的读取总是一致的.
4.RC: 读已提交, 和 RR 略有不同, 不同之处在于其每次 select 都会重新获取 ReadView, 这使得若有事务提交后, 再 select 数据, 则生成的 ReadView 数据会发生变化(即未提交事务 id 列表中少了刚刚提交的事务). 因此同样的逻辑进行判断, RC 能读取到已提交的事务的改动.

# PS: 居然问我 读未提交??  那不是不需要 ReadView 就能实现吗...

图为回滚对应的 undo 链; 以及隔离级别原理的 ReadView.

MySQL 速记

SQL 优化

索引原理

MySQL 索引一般选择 B+树做为数据结构存储. B+ 树的优点是, 对文件IO的访问次数控制在 3 次, 保证速度的同时, 能存储千万行数据.

索引

1
2
3

1.对常用列添加索引, 视具体情况选择单一索引或复合索引(一般为复合)
2.通过 Explain 语句分析执行计划, 将 type 提升到至少 index 级别.
3.通过 Explain 语句分析执行计划, 将 extra 中 Using filesort消除(排序列加索引), Using join buffer消除 (通过给关联表的关联列加索引), Using temporary (一般通过分组列加索引), Using where(根据最左原则对条件列加复合索引)

事务

ACID:
A: 原子性, 多个操作要么都做, 要么都不做
C: 一致性, 数据库文件的状态必须从一个一致性状态到另一个一致性状态.
I: 隔离性, 事物之间相互隔离, 互不影响.
D: 持续性, 一个事务一但提交, 则对数据库的改变是永久的.

隔离级别

1.读未提交: 可读取其他未提交事务的执行结果(如更新了某个字段), 可能会造成读取错误的数据(未提交的事务回滚了), 造成脏读.
2.读已提交: 可读取其他已提交事务的执行结果, 2次读取数据还是可能不一致(其他事务又提交了), 造成不可重复读.
3.可重复读: 确保同一事务内多次读取数据时, 会看到相同的数据. 但可能造成幻读, 如批量修改登录密码后, 另一个事务新增了一条记录, 导致新纪录未修改.
4.串行化: 事务串行化执行, 效率低.

MySQL 默认隔离级别

可重读读

数据库锁

锁原理

1 2	行锁: 分为排它锁(X) 和共享锁(S). 即写锁和读锁. 表锁: 分为元数据锁(MDL)和表锁.

锁触发方式

1
2

行锁: 隐式(条件带有索引则锁对应列, 不带索引则锁全部行, RR 总会带有 GAP 锁, RC 不会), 显式(使用 for update, lock in share mode)
表锁: 隐式(对整个表不带条件进行增删改, 或任何 DDL 操作) 显示(使用 for update, lock in share mode)