MySQL基础
2023-10-28 09:29:47 0 举报AI智能生成
MySQL基础相关内容，非常全
作者其他创作
大纲/内容
MVCC
视图概述
InnoDB 在实现 MVCC 时用到的一致性读视图（consistent read view）
支持两种隔离级别的实现
RC（Read Committed，读提交）
RR（Repeatable Read，可重复读）
MVCC中的“快照”
快照概述
概述
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”，注意，这个快照是基于整库的
快照实现
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id
每行数据也都是有多个版本的
示例
分支主题
图中的三个虚线箭头，就是 undo log
V1、V2、V3 并不是物理上真实存在的，而是根据当前版本和 undo log 计算出来的
一致性视图
概述
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果，但是之后，这个事务执行期间，其他事务的更新对它不可见
视图数组
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID
高水位
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位
可见性规则
这个视图数组把所有的 row trx_id 分成了几种不同的情况，对于当前事务的启动
瞬间来说，一个数据版本的 row trx_id，有以下几种可能
1、如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的
2、如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的
3、如果落在黄色部分，那就包括两种情况
若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见
若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况
1、版本未提交，不可见
2、版本已提交，但是是在视图创建后提交的，不可见
3、版本已提交，而且是在视图创建前提交的，可见
InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力
更新逻辑
更新数据都是先读后写的，并且只能读当前的值，称为“当前读”（current read）
事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读（consistent read）
事务更新数据的时候，只能用当前读，如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待
读提交和可重复读区别
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图
各类事务级别可见性
对于可重复读，查询只承认在事务启动前就已经提交完成的数据
对于读提交，查询只承认在语句启动前就已经提交完成的数据
当前读，总是读取已经提交完成的最新版本
为什么表结构不支持“可重复读”？
因为表结构没有对应的行数据，也没有 row trx_id，因此只能遵循当前读的逻辑
MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可重复读
锁
全局锁
含义
全局锁就是对整个数据库实例加锁
一个库被全局锁上以后，对里面任何一个表做加字段操作，都会被锁住
FTWRL
MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)
当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞
数据更新语句（数据的增删改）
数据定义语句（包括建表、修改表结构等）
更新类事务的提交语句
使用场景
全库逻辑备份（把整库每个表都 select 出来存成文本）
加锁存在的问题
主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟
如果不加锁
不加锁会存在的问题
备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的
解决不加锁存在的问题
在可重复读隔离级别下开启一个事务（single-transaction）
为什么还是需要FRWRL？
1、一致性读是好，但前提是引擎要支持这个隔离级别
2、因此，single-transaction 方法只适用于所有的表使用事务引擎的库
为何不使用set global readonly=true？
1、在有些系统中，readonly 的值会被用来做其他逻辑
比如用来判断一个库是主库还是备库，修改 global 变量的方式影响面更大
2、在异常处理机制上有差异
执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态
将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
表级锁
表锁
语法
lock tables … read/write
与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放
lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象
使用程度
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式
而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大
元数据锁（meta data lock - MDL）
概述
MDL 不需要显式使用，在访问一个表的时候会被自动加上
MDL 的作用是，保证读写的正确性
加锁方式
当对一个表做增删改查操作的时候，加 MDL 读锁
当要对表做结构变更操作的时候，加 MDL 写锁
申请释放时机
在语句执行开始时申请
语句结束后并不会马上释放，而会等到整个事务提交后再释放
读写锁间关系
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
案例
为什么给一个小表加个字段，导致整个库挂了？
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据（环境MySQL5.6）
session A 先启动，这时候会对表 t 加一个 MDL 读锁
由于 session B 需要的也是 MDL 读锁，因此可以正常执行
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞
宕机原因分析
1、所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了
2、并且考虑到客户端有重试机制，超时后会再起一个新 session 重新请求，这个库的线程很快就会爆满
如何安全地给小表加字段？
解决长事务，事务不提交，就会一直占着 MDL 锁
在 alter table 语句里面设定等待时间
如果在这个指定的等待时间里面能够拿到 MDL 写锁最好
拿不到也不要阻塞后面的业务语句，先放弃，之后开发人员或者 DBA 再通过重试命令重复这个过程
行锁
概述
1、行锁就是针对数据表中行记录的锁
2、MySQL 的行锁是在引擎层由各个引擎自己实现的
3、但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁
4、InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一
两阶段锁
例子引入
问题
在如图的操作序列中，事务 B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键
答案
实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行
结论
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放
启发
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，降低锁的竞争，提高并发度
业务场景
假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：
从顾客 A 账户余额中扣除电影票价；
给影院 B 的账户余额增加这张电影票价；
记录一条交易日志。
根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的
所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少，这就最大程度地减少了事务之间的锁等待，提升了并发度。
死锁和死锁检测
死锁含义
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致线程间进入无限等待状态
解决策略
1、直接进入等待，直到超时
这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置
默认是50s，一般业务是无法接受的
但是超时时间也不能设置太短，太短的话，会导致部分进行正常锁等待的线程被干掉
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行
将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑
默认是on
也是一般来说会使用的策略
死锁检测
含义
每当一个事务被锁就要查看其依赖线程是否被其他线程锁住，如此循环，最后判断是否出现循环等待（死锁）
缺点
那如果是所有事务都要更新同一行的场景呢？
每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作
假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的
虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源
因此，就会看到 CPU 利用率很高，但是每秒却执行不了几个事务的现象
解决方案
1、关闭死锁检测
2、控制并发度
3、一行改成逻辑多行
索引理论
覆盖索引
引入
一条SQL的树搜索次数&扫描行
select * from T where k between 3 and 5
分支主题
执行流程
1、在 k 索引树上找到 k=3 的记录，取得 ID = 300
2、再到 ID 索引树查到 ID=300 对应的 R3
3、在 k 索引树取下一个值 k=5，取得 ID=500
4、再回到 ID 索引树查到 ID=500 对应的 R4
5、在 k 索引树取下一个值 k=6，不满足条件，循环结束
结论
这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）
含义
将查询需要用到的字段加入索引中，从而满足查询需求，即被称为覆盖索引
优点
覆盖索引可以减少树的搜索次数，显著提升查询性能
示例
问题
在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
分析
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了
当然，索引字段的维护总是有代价的，因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了
最左前缀
引入
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录
含义
最左优先，以最左边的为起点任何连续的索引都能匹配上
同时遇到范围查询(>、<、between、like)就会停止匹配
示例
下述查询语句是如何执行的？
select * from T where name like ‘张 %’
使用最左前缀能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止
注意
不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索
这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
如何安排联合索引字段顺序？
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
如果不能少维护一个索引，则另外考虑的原则就是空间成本
索引下推
作用
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
基础架构
基本架构
注：现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎
连接器
作用
跟客户端建立连接
获取权限
维持和管理连接
查看连接情况
查看指令：show processlist
如果没有后续动作，当前连接会处于空闲状态
Command 列显示为“Sleep”表示系统中有一个空闲连接
客户端如果太长时间没后续动作，连接器就会自动将它断开
由参数 wait_timeout 控制
默认值 8 小时
连接类型
长连接&短连接
长连接
连接成功后，如果客户端持续有请求，则一直使用同一个连接
短连接
每次执行完很少的几次查询就断开连接，下次查询再重新建立一个
长短连接使用
建立连接的过程比较复杂，使用中要尽量减少建立连接的动作，即尽量使用长连接
问题
如果全部使用长连接MySQL 占用内存涨得特别快，为什么？
问题原因
MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，资源会在连接断开的时候才释放
如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启
解决方案
1、定期断开长连接
使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
2、执行mysql_reset_connection
MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后执行 mysql_reset_connection 重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
查询缓存
缓存方式
拿到一个查询请求后，会先到查询缓存看看，之前是否执行过这条语句
之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中
key 是查询的语句，value 是查询的结果
如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端
问题
尽量不要使用查询缓存，为什么呢？
因为失效非常频繁
1、只要有对一个表的更新，这个表上所有的查询缓存都会被清空
2、对于更新压力大的数据库来说，查询缓存的命中率会非常低
注意
MySQL 8.0 版本已经删除查询缓存的整块功能，8.0 开始彻底没有这个功能了
分析器
词法分析
输入的是由多个字符串和空格组成的一条 SQL 语句
MySQL 需要识别出里面的字符串分别是什么，代表什么
语法分析
根据词法分析的结果，根据语法规则，判断输入的 SQL 是否满足 MySQL 语法
比如表、字段是否存在
优化器
在表里面有多个索引的时候，决定使用哪个索引
在一个语句有多表关联（join）的时候，决定各个表的连接顺序
执行器
判断是否拥有查询表的查询执行权限
如果没有，就会返回没有权限的错误
有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
问题
一条SQL查询语句的执行流程？
select * from T where ID=10;
1、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中
2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行
3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
实战
问题
如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？
答案
分析器
日志系统
redo log（InnoDB引擎层）
背景
如果每一次的更新操作都需要写进磁盘，磁盘要找到对应的那条记录再更新，整个过程 IO 成本、查找成本都很高
WAL技术
概述
WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘
具体流程
记录更新时，InnoDB 引擎先把记录写到 redo log 里面，并更新内存，此时更新就算作完成
InnoDB 引擎会在系统比较空闲的时候，将这个操作记录更新到磁盘
如果redo log满了，则先暂停更新操作，写redo log中的更新操作执行到磁盘中
日志格式
写入方式
InnoDB 的 redo log 大小固定
从头写到末尾，然后返回开头循环写
写入位置
是否直接写磁盘可以通过参数指定，一般写磁盘
配置参数
innodb_flush_log_at_trx_commit
设置成 1 较好，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不丢失
写入示例
比如配置一组 4 个文件，每个文件的大小是 1GB，那么 redo log 总共就可以记录 4GB 的操作
分支主题
write pos
当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头
checkpoint
当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
write pos & checkpoint间关系
write pos 和 checkpoint 之间的是redo log空余的部分，可以用来记录新的操作
如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录（写入磁盘），把 checkpoint 推进一下。
crash-safe
InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe
binlog（Server层）
背景
最开始 MySQL 没有 InnoDB 引擎
MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档
InnoDB 是另一个公司以插件形式引入 MySQL
只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力
binlog日志类型
row
statement
配置参数
sync_binlog
设置成 1 较好，表示每次事务的 binlog 都持久化到磁盘，可以保证 MySQL 异常重启之后 binlog 不丢失
redo log & binlog不同
所在架构层级不同
redo log 是 InnoDB 引擎特有的
binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
日志类型不同
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
写入方式不同
redo log 是循环写的，空间固定会用完
binlog 是可以追加写入的（“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志）
一条更新语句是如何执行的？
update T set c=c+1 where ID=2;
1、执行器找引擎取满足条件的记录行
如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
否则，需要先从磁盘读入内存，然后再返回
2、执行器拿到引擎给的行数据，把这个值加上 1
比如原来是 N，现在就是 N+1
得到新的一行数据，再调用引擎接口写入这行新数据
3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面
此时 redo log 处于 prepare 状态
告知执行器执行完成了，随时可以提交事务
4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘
5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态
6、更新完成
两阶段提交
作用
为了让两份日志之间的逻辑一致
同时也是是跨系统维持数据逻辑一致性时常用的一个方案
解决问题
问题示例
需要恢复半个月内的数据
提前须知
binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式
如果能保证恢复半个月内所有数据，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份
这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备
解决流程
1、找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份
2、从这个备份恢复到临时库
3、从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻
为什么要用两阶段提交？
反证
先写 redo log 后写 binlog
假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启
redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1
但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句
因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同
先写 binlog 后写 redo log
如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0
但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志
在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同
证明
崩溃恢复时的判断规则
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整
如果是，则提交事务
否则，回滚事务
redo log 处于 prepare 阶段之后、写 binlog 之前发生崩溃（crash）
binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚
此时binlog 还没写，所以也不会传到备库
binlog 写完，redo log 还没 commit 前发生 crash
崩溃恢复过程中事务会被提交
结论
如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致
场景
误操作
误操作后需要用这个过程来恢复数据
需要扩容时
也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况
衍生问题
MySQL 怎么知道 binlog 是完整的?
一个事务的 binlog 是有完整格式的
statement 格式的 binlog，最后会有 COMMIT
row 格式的 binlog，最后会有一个 XID event
binlog-checksum 参数
用来验证 binlog 内容的正确性
binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现
redo log 和 binlog 是怎么关联起来的?
它们有一个共同的数据字段，叫 XID
崩溃恢复的时候，会按顺序扫描 redo log
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务
处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用
所以，在主库上也要提交这个事务
采用这个策略，主库和备库的数据就保证了一致性
干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？
对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）
而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了
不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？
历史原因
InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复
实现原因
只用binglog
binlog 没有能力恢复“数据页”
InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页
在图中这个位置发生崩溃的话，事务 1 也是可能丢失了的，而且是数据页级的丢失。此时，binlog 里面并没有记录数据页的更新细节，是补不回来的
优化一下 binlog 的内容，让它来记录数据页的更改可以吗？但，这其实就是又做了一个 redo log 出来
那能不能反过来，只用 redo log，不要 binlog？
只从崩溃恢复的角度来讲是可以的
可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的
正式的生产库上，binlog 都是开着的，因为 binlog 有着 redo log 无法替代的功能
归档
redo log 是循环写，写到末尾是要回到开头继续写的，这样历史日志没法保留，redo log 也就起不到归档的作用
MySQL 系统依赖于 binlog
binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制
业务消费 MySQL 的 binlog
还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了
redo log 一般设置多大？
redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了
如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧
正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
redo log 并没有记录数据页的完整数据
所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况
正常运行的实例
数据页被修改以后，跟磁盘的数据页不一致，称为脏页
最终数据落盘，就是把内存中的数据页写盘
这个过程，甚至与 redo log 毫无关系
崩溃恢复场景
InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新
会将其读到内存
让 redo log 更新内存内容
更新完成后，内存页变成脏页
总的来说就是redo log并未参与实际的数据写盘流程，只是负责把数据在内存中更新到最新版本而已
流程示意图
分支主题
redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？
redo log buffer 就是一块内存，用来先存 redo 日志的
在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志
真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的
单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成
实战问题
日志备份一天一备还是一周一备更好
一天一备RTO恢复目标时间短，但存储空间大
一周一备存储空间小，但恢复时间长
事务隔离
提前须知
事务是保证一组数据库操作，要么全部成功，要么全部失败
在 MySQL 中，事务支持是在引擎层实现的
MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务（比如MyISAM 引擎）
隔离性&隔离级别
ACID
Atomicity
原子性
Consistency
一致性
Isolation
隔离性
Durability
一致性
多事务同时执行存在问题
脏读（dirty read）
不可重复读（non-repeatable read）
幻读（phantom read）
SQL 标准事务隔离级别
读未提交（read uncommitted）
事务未提交，其变更就能被别的事务看到
读提交（read committed）
事务提交后，其变更才会被其他事务看到
可重复读（repeatable read）
事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的
串行化（serializable ）
对于同一行记录，“写”会加“写锁”，“读”会加“读锁”
当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
不同隔离级别示例
V1、V2、V3的值分别是多少？
读未提交
2
2
2
读提交
1
2
2
可重复读
1
1
2
串行化
1
1
2
隔离级别实现原理
数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的
“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念
“串行化”隔离级别下直接用加锁的方式来避免并行访问
隔离级别配置参数
transaction-isolation
将启动参数 transaction-isolation 的值设置成 READ-COMMITTED（以读提交为例）
show variables
用 show variables 来查看当前的值
show variables like 'transaction_isolation';
事务隔离具体实现原理
（以可重复读为例）
每条记录在更新的时候都会同时记录一条回滚操作
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似上面的记录。
记录上的最新值，通过回滚操作，都可以得到前一个状态的值
同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）
回滚日志删除时间
系统会判断，当系统里没有比这个回滚日志更早的 read-view 的时候，即当没有事务再需要用到这些回滚日志时，回滚日志会被删除
事务启动方式
显式启动事务语句
开始事务 begin 或 start transaction
提交语句 commit
回滚语句 rollback
自动提交事务
set autocommit=0
该命令会将线程的自动提交关掉
如果只执行一个 select 语句，事务就启动了，并且不会自动提交
该事务持续存在直到主动执行 commit 或 rollback 语句，或者断开连接。
有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令
这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务
长事务
尽量不要使用长事务的原因
长事务意味着系统里面会存在很老的事务视图
由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间
长事务还占用锁资源，也可能拖垮整个库
如何避免长事务对业务影响？
确认是否使用了 set autocommit=0，目标就是把它改成 1
确认是否有不必要的只读事务，仅有 select 语句的这种事务给去掉
业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间
索引模型
常见模型
哈希表
以键 - 值（key-value）存储数据的结构
把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置
多个 key 值经过哈希函数的换算，会出现同一个值的情况，处理这种情况的一种方法是，拉出一个链表
新增速度快，但由于不是有序的，区间查询速度会很慢
有序数组
等值查询和范围查询场景中的性能就都非常优秀
仅仅看查询效率，有序数组就是最好的数据结构
更新数据时，往中间插入一个记录就必须得挪动后面所有的记录，成本太高
二叉搜索树
每个节点的左儿子小于父节点，父节点又小于右儿子
为了维持 O(log(N)) 的查询复杂度，需要保持这棵树是平衡二叉树
因此更新的时间复杂度也是 O(log(N))
二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树
原因是，索引不止存在内存中，还要写到磁盘上
N叉树
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块
因此就不应该使用二叉树，而是要使用“N 叉”树
其中，“N 叉”树中的“N”取决于数据块的大小
在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样
InnoDB 的索引模型
索引组织表
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的，每一个索引在 InnoDB 里面对应一棵 B+ 树
索引类型
主键索引
叶子节点存储整行数据
别名：聚簇索引（clustered index）
非主键索引
非主键索引的叶子节点存储主键的值
别名：二级索引（secondary index）
两种索引查询方式的不同
如果是主键查询方式，则只需要搜索 ID 这棵 B+ 树
普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次
基于非主键索引的查询需要多扫描一棵索引树，在应用中应该尽量使用主键查询
索引维护
索引插入
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
假设当前主键ID的值如图所示
插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录
新插入的 ID 值为 400，需要逻辑上挪动后面的数据，空出位置
页分裂
含义
如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂
影响
影响性能
影响数据页利用率
原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%
页合并
当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并
合并的过程，可以认为是分裂过程的逆过程
自增主键
定义
指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值
优点
性能
自增主键的插入数据模式，正符合递增插入的场景
每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
存储空间
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
0 条评论
下一页
