Mysql
2022-02-23 10:00:26 1 举报AI智能生成
登录查看完整内容
Mysql高级知识点总结
作者其他创作
大纲/内容
输入： mysql -h$ip -p$port -u$user -p
回车以后再输入密码
案例：mysql -h127.0.0.1 -uroot -p;
方法一
输入：mysql -h$ip -p$port -u$user -p$password
案例：mysql -h127.0.0.1 -uroot -p123456；
方法二
方法二为明文输入，有暴露密码的风险
两种方法比较
连接指令
输入连接指令
抛出\"Access denied for user\"错误
账户密码错误
建立连接
当用户登录成功以后，mysql会到权限表查询用户具有的权限
当用户登录成功获取权限以后，之后的查询都会依赖此时查询的结果集。如果修改了权限，对之前的用户也不会产生影响，只对新连接产生影响。也可以手动刷新权限
注意事项
管理权限
连接创建完成以后就处于空闲状态
wait_timeout
默认8小时
mysql空闲时间设置参数
不推荐
将wait_timeout时间延长
客户端每隔一段时间发送一次查询语句维持连接
数据库连接池会创建指定数量的连接，并且维护在链表中
数据库连接池每隔6小时自动检测空闲连接
如果存在空闲连接则关闭
如果剩余连接不够指定数量
则创建的新的连接
原理
采用数据库连接池
解决方案
如果客户端空闲时间太长，mysql会断开连接
维持和管理连接
连接器
以sql语句作为key去查询缓存
验证失败，不能查询
precheck 验证权限
命中缓存则返回查询结果（效率极高）
未命中缓存则继续向下执行
执行流程
只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
sql语句发生更改，则查不到对应的缓存
查询缓存的失效非常频繁
缺陷
查询缓存
MySQL 需要识别出sql语句里面的字符串分别是什么，代表什么。
”select“识别成查询语句
“T”识别成“表名 T”
把字符串“ID”识别成“列 ID”。
例如
词法解析
根据sql语句生成语法树
检测关键字是否缺失，表明和列名是否存在
判断你输入的这个 SQL 语句是否满足 MySQL 语法
You have an error in your SQL syntax
sql语句错误
语法解析
解析器
有多个索引的时候，决定使用哪个索引
语句有多表关联（join）的时候，决定各个表的连接顺序
优化器
执行sql语句
返回没有权限错误
无权限
判断当前连接是否具有权限
调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
无索引的情况
第一次调用的是“取满足条件的第一行”这个接口
之后循环取“满足条件的下一行”这个接口
有索引的情况
查看表的存储引擎，调用引擎的接口查询数据
执行器
Mysql Server层
支持行锁
支持事务
InnoDB
支持表锁
不支持事务
Myisam
Memory
引擎类型
create table 语句中使用 engine=memory
指定引擎
存储引擎
基础组件
mysql查询执行原理
基础组件模块从上往下执行即为查询的执行流程
查询执行原理
undo log 日志
数据库发生异常重启，之前提交的记录都不会丢失
crash-safe
作用
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB
一开始write pos在四个文件的最开始
当写入redo log 日志时，write pos就往后开始移动
write pos
一开始check point在文件最末尾的位置
当触发刷盘操作时（redo log 日志写入到磁盘中），check point指针朝有记录的方向开始移动
check point
redo log 日志具有两个指针，write pos 和 check point。
MySQL redo log 存储状态
图片
存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；（速度很快）
写到磁盘 write（速度很快），但是没有持久化fsync，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
持久化到磁盘fsync（速度很慢），对应的是 hard disk，也就是图中的绿色部分。
状态：
redo log三种状态
InnoDB 提供了 innodb_flush_log_at_trx_commit 参数
控制参数
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；（redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。）
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
参数取值
redo log写入策略
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。
redo log 写入磁盘的场景
LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。
日志逻辑序列号（log sequence number，LSN）
图片：redo log 组提交
trx1 是第一个到达的，会被选为这组的 leader；
等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
这时候 trx2 和 trx3 就可以直接返回了。（所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好）
步骤
组提交过程
图片：两阶段提交细化
redo log fsync 在binlog执行write之后，拖延fsync的时间，让更多成员进入该组
binlog fsync也延迟到redo log fsync后，这样binlog也可以组提交，不过，因为redo log fsync的速度很快，所以，binlog日志的组提交效果较差
Mysql优化组提交机制
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
binlog组提交优化
组提交机制（group commit）
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
redo log日志（重做日志）
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
binlog cache
binlog 写盘状态(每个线程有自己 binlog cache，但是共用同一份 binlog 文件。)
事务执行过程中，先把日志写到 binlog cache
sync_binlog=0 的时候，表示每次提交事务都只文件系统的page cache中（速度很快）
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync（将数据持久化到硬盘，效率较低）；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志
事务提交的时候，再把 binlog cache 写到 binlog 文件中
清空binlog cache里面的内容
binlog 的写入机制
statement 格式 binlog 示例
BEGIN和COMMIT表示中间开启了事务；xid是mysql表隐式的字段，用于redo log和binlog日志之间的对应
use `test`；表示选择某个数据库，这样在同步数据的时候能找到对应的数据库。
statement格式下存储了完整的sql语句，包括备注
案例展示
在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。导致主备数据不一致
delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
因为 delete 带 limit，很可能会出现主备数据不一致的情况。
如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。
案例
statement格式存在的问题
statement
row 格式 binlog 示例
BEGIN和COMMIT表示中间开启了事务；xid是mysql表隐式的字段，用于redo log和binlog日志之间的对应
Table_map event，用于说明接下来要操作的表是 test 库的表 t;
Delete_rows event，用于定义删除的行为。
row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。
insert
row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
delete
binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。
update
row
因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。比如我们这个例子，设置为 mixed 后，就会记录为 row 格式；而如果执行的语句去掉 limit 1，就会记录为 statement 格式。
为什么会有 mixed 格式的 binlog？
mixed格式下采用的是statement格式存储
如果隔了一段时间存储，对now函数是没有影响的，因为在写日志的时候将时间戳也写进去了，不管备库多久执行，写入的都会是同一个时间，但是，如果运维人员直接将binlog日志拿去执行，则会产生数据不一致的问题、
用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。
用 binlog 来恢复数据的标准做法
mixed
binlog日志格式
一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入
binlog写入时的注意事项
binlog日志
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo_log和binlog日志的区别
保证redo log 日志和binlog日志逻辑保持一致
在进行数据恢复时，使用binlog日志恢复就差了一条数据
写完redo log后发生crash，binlog日志还没写入
在进行数据恢复时，使用redo log 日志恢复便差了一条数据
先写binlog日志，发生crash，redo log 日志还没写入
由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash）
例子
先写入redo_log日志，redo_log状态为prepare
生成binlog并写入磁盘
将redo_log状态更改为commit
两段提交协议
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果是，则提交事务；
否则，回滚事务。
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
崩溃恢复时的判断规则
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
redo log 和 binlog 是怎么关联起来的
statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event。
一个事务的 binlog 是有完整格式的：
在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。
MySQL 怎么知道 binlog 是完整的?
redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。
所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。
redo log 一般设置多大？
日志
日志系统
全称：Write-Ahead Logging
作用：先写日志，再写磁盘
WAL 技术
执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
对于insert操作，undo_log日志记录其主键id，恢复时按照id删除即可。
对于delete/update操作，undo_log日志记录原来的row数据，回滚时直接恢复
记录undo_log日志
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
外框
更新执行原理
一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Atomicity（原子性）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Consistency（一致性）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Isolation（隔离性）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
Durability（持久性）
Mysql特性
解决脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题
读到其他事务未提交的数据；
脏读
前后读取的记录内容不一致；
不可重复读
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。
说明
必须是当前读，不能是快照读（reason：可重复读级别下，普通查询为快照读，根据undo log可见性规则，是看不到其他事务插入的数据）
产生的条件
图 1 假设只在 id=5 这一行加行锁（for update会使查询语句从快照读变成当前读）
描述
可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。
Q1 只返回 id=5 这一行；
在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
分析流程
幻读案例一
假设只在 id=5 这一行加行锁 -- 数据一致性问题
从案例中分析，sessionA中是要将d=5的行都加上行锁，但是sessionB和sessionC后面又更新或插入其他行使d=5，但没有被锁住，这样语义就破坏了
语义被破坏
正常情况下只有sessionA的数据会被修改为100，但，出现幻读的情况下，sessionB和sessionC的数据也会被改成100
数据一致性的问题
幻读导致的问题
幻读案例二
间隙锁与next-key lock
方案一
读提交隔离级别（rc） + binlog日志格式为row
方案二
幻读解决方案
两条记录之间的间隙，使用间隙锁锁住以后，往这个间隙里面插入记录都会阻塞
定义
间隙锁和行锁合称
next-key lock
间隙锁导致的死锁
产生死锁问题
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
间隙锁与next-key lock带来的问题
原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
加锁规则
案例表结构与数据
等值查询的间隙锁
案例一
只加在非唯一索引上的锁
案例二
对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？
mysql> select * from t where id=10 for update;mysql> select * from t where id>=10 and id<11 for update;
主键索引上范围查询的锁
案例三
非唯一索引范围锁
案例四
唯一索引范围锁的 bug
案例五
非唯一索引上存在\"等值\"的例子
delete 示例
加锁效果示例
案例六
limit 语句加锁，表 t 里 c=10 的记录有两条
这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。
案例七
操作序列
案例八
间隙锁
幻读
产生的原因
一个事务还没提交时，它做的变更就能被别的事务看到。
读未提交（read uncommitted）
一个事务提交之后，它做的变更才会被其他事务看到。
读提交（read committed）
一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
可重复读（repeatable read）
“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
串行化（serializable ）
全称：Multi-Version Concurrency Control
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
读的时候需要加锁
当前读
不加锁的非阻塞读
快照读
技术概念提前引入
span style=\
DB_TRX_ID
7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
DB_ROLL_PTR
6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
DB_ROW_ID
隐式字段
记录的是insert记录的主键id
insert undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除
记录的是
update undo log
undo log日志种类
在事务 1修改该行(记录)数据时，数据库会先对该行加排他锁
然后把该行数据拷贝到 undo log 中，作为旧记录，既在 undo log 中有当前行的拷贝副本
事务提交后，释放锁
现在来了一个事务 1对该记录的 name 做出了修改，改为 Tom
在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面
事务提交，释放锁
又来了个事务 2修改person 表的同一个记录，将age修改为 30 岁
undo log 日志存储原理
undo log日志
当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
当前执行快照读的事务能否看见最后一次事务修改的数据，如果不能，则去找最新版本或者历史版本
可见性判断
一个数值列表
用于维护 Read View 生成时刻系统 正活跃的事务 ID 列表，即begin以后，还没有commit和rollback的事务
trx_list
是 trx_list活跃事务列表中最小的 ID
up_limit_id（lower water remark）
ReadView 生成时刻系统尚未分配的下一个事务 ID ，也就是 目前已出现过的事务 ID 的最大值 + 1
low_limit_id（hight water mark）
具有的属性
判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains (DB_TRX_ID)，如果在，则代表我 Read View 生成时刻，你这个事务还在活跃，还没有 Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在 Read View 生成之前就已经 Commit 了，你修改的结果，我当前事务是能看见的
MVCC原理
Read View 读视图
实现原理
MVCC多版本并发控制1
一致性视图是在第一个select快照读语句时创建
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动
一致性视图在执行语句时创建
start transaction with consistent snapshot命令，马上启动一个事务
没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。
事务的启动时机
是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的
事务 ID（transaction id）
每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID
row trx_id
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
活跃事务列表
数组里面事务 ID 的最小值记为低水位
低水位
当前系统里面已经创建过的事务 ID 的最大值加 1
高水位
属性
事务的执行流程图
undo_log日志记录图
假设当前存在活跃事务，事务ID为90，事务A的ID为100， 事务B的ID为101，事务C的ID为102
事务的执行结果为：事务A查询到的值为1，事务B查询到的值为3
在事务C执行之前，数据版本为（1，1）90
事务C（102）没有显示的开启事务，所以在执行update语句时，加行锁，开启事务，将数据修改为（1，2）102
事务B在事务C之前开启事务，但行锁的是在执行update语句时加上的，并且，在执行update语句时，会执行当前读（读取数据的最新值，如果不读取最新的结果，则k=1，事务B的执行结果为（1，2），这样事务C的更新就丢失了），即事务C更新后的值为2，所以，事务B执行的结果为3，在查询时，读取到的也是自己修改的结果，所以k=3，当前数据版本为（1，3）101
在事务A开启时，创建一致性视图，此时的活跃事务列表为【90， 100】，低水位为90， 高水位为101，由于事务B执行了更新操作，当前row trx_id = 101 >= 101（高水位），所以该版本的修改对当前事务不可见，则沿着undo_log日志找上一个版本（1，2）row_trx>=101(高水位)，该版本也对当前事务不可见，再往前走为：（1，1）90，rox_trx_id 大于90(低水位)，并且90在活跃事务列表中，可以查看该版本
事务B查询的值为3详解
数据版本可见性规则
版本未提交，不可见；数据版本的事务ID
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。
执行快照读之前，如果该事务执行了修改操作（update），该事务之后读取到的都是最新结果
事务可见性判断
事务B的更新逻辑
事务C开启了事务，创建了当前数据版本，但是没有提交事务
事务B在进行更新时发现id=1的行已经被其他事务上锁，就进行等待，直到事务C执行结束，事务B才能继续执行当前读获取最新的（1，2）数据
事务的更新逻辑
MVCC多版本并发控制2（极客时间版）
事务的隔离级别
事务
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)
效率排序
InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
count(主键 id)
InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
count(1)
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count(字段)
不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
count(*)
count(*)优化
表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
Using filesort
表示的就是使用了覆盖索引，性能上会快很多。
Using index
表示的是需要使用临时表
Using temporary
explain 命令来看看这个语句的执行情况
初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。
索引为city的情况下，进行查询和排序
全字段排序过程
部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
在sort buffer大小不足时，会采用外部文件辅助排序
初始化 sort_buffer，确定放入两个字段，即 name 和 id；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
rowid排序过程
将需要的条件字段字段和排序字段放入sort buffer中进行排序，排序完成以后，其他字段再从主键索引树中获取
rowid排序原理
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
缺点
SET max_length_for_sort_data = 16;
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
如何让mysql使用该算法
rowid 排序
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
全字段排序 VS rowid排序
索引包含查询条件和排序条件
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
将联合索引升级成覆盖索引，这样不用回表查询，效率更高
覆盖索引需要维护的索引文件更多
联合索引优化
联合索引排序
order by 优化
join优化
假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。
mysql> select count(*) from tradelog where month(t_modified)=7;
走全索引扫描的方案
mysql> select count(*) from tradelog where -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
优化方案
需求1
对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。
优化器“偷懒”行为
select * from tradelog where id = 10000 -1
需求2
条件函数优化
mysql> select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
mysql> select * from tradelog where tradeid='110717';
优化
“将字符串转成数字”
以上面的案例为例，Mysql实际执行为：mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;这里还是走了条件函数的坑，导致不能走索引搜索
换一种方式的情况下，就能执行该情况下，id是数值类型select * from tradelog where id=\"83126\";
Mysql字符串与数字转换规则
隐式类型转换优化
该语句执行情况为：tradelog表走了主键索引查询，而trade_detail走了全表扫描，因为两张表的tradeid字段的字符集不一致，前者为utf8，后者为uft8mb4（该字符集为uft8的超集）。在次情况下，作比较的时候，会将上述语句进行转换
sql语句
将两张表的字符集修改为相同的，或者用后表做驱动表，让条件函数转换发生在值这边
隐式字符编码转换
sql优化
提高数据库查询的效率
目的
等值查询速度快
优点
不支持做范围查询
Hash索引
查询效率高，支持等值查询和范围查询
更新数据的时候，后面所有的数据都需要移动位置，效率太低
有序数组
支持等值查询和范围查询
极端情况下，二叉树会退化成为链表，查询效率极低
二叉树
能够自动平衡，查询效率还可以
每个节点只能存储一条数据，数据很多的时候，树会特别高，导致查询效率降低
红黑树
每个节点能够存储多个数据
查询效率较高
索引和数据没有分开，导致树还是比较高
B树
索引和数据分开，非叶子节点只存储索引，非叶子节点存储数据
树高很低，查询效率极高
B+树
可以实现的索引结构
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
主键索引
非主键索引的叶子节点内容是主键的值
非主键索引
主键索引和非主键索引图
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
主键索引和非主键索引查询的区别
索引类别
先搜索非主键索引树，获取到主键ID，再到主键索引树中查询到相应的记录的过程称为回表
分析select * from T where k between 3 and 5的执行流程
InnoDB 的索引组织结构
在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。
该搜索过程即是回表的过程
采用覆盖索引查询
如何避免回表操作
回表
索引字段覆盖查询字段，在非主键索引树中查询到结果即可返回，避免回表
如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
覆盖索引可以减少树的搜索次数，显著提升查询性能
覆盖索引
如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
在依据索引查询时，如果没有按照索引顺序查询，会导致索引失效
使用越频繁的索引，放在最前面，复用性更高
描述：该案例讲解最左前缀原则
查询语句为 select * from person where name =\"张\
查询语句为 select * from person where age =\"10\
查询语句为 select * from person where name = \"张\" and age =\"10\
查询语句为 select * from person where id_card like '5105%' and name = \"张\" and age =\"10\
查询语句为 select * from person where id_card like '5105%' and name = \"张\
最左前缀原则
顺序优化
空间优化
Mysql5.6之前无索引下推优化
Mysql5.6以后有索引下推优化
出现版本
无索引下推优化图
有索引下推优化图
索引下推
索引创建优化原则
对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
普通索引
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
唯一索引
普通索引相比于唯一索引，会多执行查找下一个记录的操作，但是，将数据读入内存是按照页读取，查找下一个记录都在内存中进行，效率很高
查询比较
查询过程
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，再执行merge操作进行落盘。通过这种方式就能保证这个数据逻辑的正确性。
这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
innodb_change_buffer_max_size
大小设置
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
效率提高的原因
写多读少的业务
使用场景
写完数据立马就要查询，该情况下应该关闭change buffer
不适合的场景
适用的索引
change buffer
redo_log日志是将写入磁盘时的随机IO优化为顺序写入IO（性能提升）
change buffer减少的是磁盘读取IO（此处指的是写入数据时，通常需要先读取数据，再进行写入，而此处时直接写入change buffer中即可。这样减少了写入时的磁盘IO）
redo_log与change buffer的优点
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge
数据库正常关闭（shutdown）的过程中，也会执行 merge 操作
触发merege的方式
merge
知识补充
找到 3 和 5 之间的位置
判断到没有冲突，插入这个值
语句执行结束；
找到 3 和 5 之间的位置，插入这个值
语句执行结束。
目标页在内存中
需要将数据页读入内存
将更新记录在 change buffer
语句执行就结束；
目标页不在内存中
内存
redo log（ib_log_fileX）
数据表空间（t.ibd）
系统表空间（ibdata1）
四个部分
change buffer 的更新过程
Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
后台线程会将数据merge到磁盘中（图中的两个虚线箭头，是后台操作，不影响更新的响应时间。）
change buffer的更新过程
数据的查询过程
要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。
change buffer 查询过程
更新过程
索引
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
InnoDB等支持事务的引擎，可以将事务的隔离级别设置成可重复读，即可备份数据，如果不支持事务的引擎，备份数据就只能使用全局读锁
更好的备份方式
全局锁
lock tables … read
lock tables … write
加锁
unlock tables
客户端断开连接也能解锁
解锁
语法
锁粒度太大，导致并发度不高
表锁
MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
场景
表t
修改表结构由MDL锁引发的问题
首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
非热点表
这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
ALTER TABLE tbl_name NOWAIT add column ...ALTER TABLE tbl_name WAIT N add column ...
热点表
如何安全地给小表加字段
元数据锁（meta data lock，MDL)
表锁由Mysql实现
行锁就是针对数据表中行记录的锁。
比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
MySQL 的行锁是在引擎层由各个引擎自己实现的
支持行锁的引擎
MyISAM
不支持行锁的引擎
实现
锁粒度更细，业务并发度更高
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
多个线程更新记录图
两阶段锁
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
电影院案例
行锁优化
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁
数据库死锁图
死锁
直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。
问题：关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题
如果你有中间件，可以考虑在中间件实现；
你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。
控制并发度
如果对同一行数据有大量线程进行更新也会降低并发度，每一个更新的线程都需要做死锁检测，消耗大量的资源
死锁解决方案
行锁
子主题
锁
主要做写入数据的节点，同时，会将数据通过binlog日志同步给备节点和从节点
主节点
做主备切换的节点，当主节点故障宕机，备节点主动切换为主节点
备节点
主要做查询的节点
从节点
前导知识
MySQL 主备切换流程（在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。）
主备切换流程
有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
可以用 readonly 状态，来判断节点的角色。
将备库设置成readonly=ture的原因
readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。
备库设置成只读了，还怎么跟主库保持同步更新呢？
主备切换
图片：主备流程图
在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。（建立的是长连接）
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，如果这个文件现在还在 page cache中，直接读走，发给 B。如果不在page cache里，就只好去磁盘读取以后，再发送给B
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）
sql_thread 读取中转日志，解析出日志里的命令，并执行。（由于多线程复制方案的引入，sql_thread 演化成为了多个线程）
同步过程
图片：MySQL 主备切换流程 -- 双 M 结构
节点 A 和 B 之间总是互为主备关系，这样在切换的时候就不用再修改主备关系。业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。然后再同步给节点A，造成循环复制
问题描述
MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id
规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。
解决方案案例
循环复制问题
主备数据同步
主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
备库 B 执行完成这个事务，我们把这个时刻记为 T3。
所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。
同步延迟
你可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。这个值的时间精度是秒。
查看主备延迟时间
每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。
seconds_behind_master 的计算方法
其实不会的。因为，备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。
如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？
第一种情况：有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
第二种情况：即备库的压力大。
因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。
不要一次性地用 delete 语句删除太多数据
大事务
处理方案就是，计划内的 DDL，建议使用 gh-ost 方案
经典大事务场景：大表 DDL
第三种情况：
第四种情况：备库的并行复制能力
主备延迟的来源
主库做大量的dml操作，引起延迟
对myisam存储引擎的表做dml操作，从库会有延迟。
工作中主备延迟的场景
图片：MySQL 可靠性优先主备切换流程（图中的 SBM，是 seconds_behind_master 参数的简写。）
判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
把主库 A 改成只读状态，即把 readonly 设置为 true；
判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
把业务请求切到备库 B。
如不理解，请看备注
流程
图片：可靠性优先策略，主库不可用
异常情况
可靠性优先策略
图片：可用性优先策略，且 binlog_format=mixed（id是主键，且能自增）假设，现在主库上其他的数据表有大量的更新，导致主备延迟达到 5 秒。在插入一条 c=4 的语句后，发起了主备切换。
步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用“插入 c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。
binlog日志为mixed格式下的切换流程
图片：可用性优先策略，且 binlog_format=row
步骤5中，主库A插入4.5这条数据时，主键重复报错，备库B插入relay log中的4，4数据，也会出现主键重复报错。
binlog日志为row格式下的切换流程
可用性优先策略
使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。
可靠性优先策略与可用性优先策略对比
有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过 binlog 来修补，而这个短暂的不一致也不会引发业务问题。
同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。
场景描述
让业务逻辑不要依赖于这类日志的写入。也就是说，日志写入这个逻辑模块应该可以降级，比如写到本地文件，或者写到另外一个临时库里面。
这种场景就又可以使用可靠性优先策略了。
可用性优先策略场景
当我们把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：
基于位点的主备切换
GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。
GTID=server_uuid:gno
格式
需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on
启动GTID模式
如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。a. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;b. 把这个 GTID 加入本实例的 GTID 集合。
如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。
GTID生成方式
每一个Mysql都会维护一个GTID列表current_gtid
如果是以默认方式生成的，则添加到当前Mysql实例的current_gtid列表中，再同步给其他从节点、
如果是以指定的GTID生成的话，需要验证当前Mysql实力的current_gtid列表中是否含有该GTID，如果有的情况下，该GTID直接抛弃，对应的事务也不会执行，没有则进行添加
每一个事务都会生成一个GTID
GTID使用方式
图 4 初始化数据的 binlog
GTID使用案例
使用案例
基于 GTID 的主备切换
GTID模式主备切换
主备切换方式
主备切换策略（解决主备延迟问题）
主备延迟
写入数据都在主库写入，主库写入数据以后，再通过binlog日志同步到从库
查询数据都在从库查询
什么是读写分离
读写分离架构图
在主库写入数据以后，还未同步到从库，就开始查询，这样会导致在从库查询不到数据
过期读
读写分离会导致什么问题
对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。
对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。
将请求做分类
有可能请求量太大，把主库打挂
存在问题
强制走主库方案；
读从库之前先 sleep 一下，具体的方案就是，类似于执行一条 select sleep(1) 命令。
如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；
如果延迟超过 1 秒，还是会出现过期读。
sleep 方案；
每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。
判断主备无延迟方案；
下面两种方案的使用图解
Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
elay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。
处理流程
等主库位点方案；
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
图 4 备库还没收到 trx3
trx1 和 trx2 已经传到从库，并且已经执行完成了；
trx3 在主库执行完成，并且已经回复给客户端，但是还没有传到从库中。
等主库位点方案；等 GTID 方案。共同存在的存在问题
等 GTID 方案。
事务提交的时候，主库把 binlog 发给从库；
从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。
配合 semi-sync 方案；
读写分离的解决方案
读写分离导致的问题
不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
同一个事务不能被拆开，必须放到同一个 worker 中。
coordinator 在分发的时候，需要满足以下这两个基本要求
并行复制策略减少主备延迟
监控日志是否出现抛错，死锁等问题
从业务代码出发
连接是否被占用满了，之后查询都失败
是否存在慢查询
是否存在事务没有释放
从mysql服务出发
服务器是否挂了
磁盘IO速度异常
磁盘是否写满了
从机器状态出发
如何判断Mysql机器是否正常
主从同步
字符串字段做索引，只取前面的n个字符前缀用来作索引
占用的空间少
可能会增加额外的记录扫描次数
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
因为区分度越高（90%以上即可），意味着重复的键值越少
具体计算公式
区分度
使用了前缀索引，覆盖索引就失效了
前缀索引对覆盖索引的影响
前缀索引
将字符串反转以后再存入数据库
在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
采用hash字段
前缀相同的字符串添加索引
字符串字段添加索引
当内存数据页跟磁盘数据页内容不一致
脏页
内存和磁盘上的数据页的内容一致
干净页
将脏页的内容写入到磁盘
数据库flush过程
前导知识点
InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。checkpoint从cp移动到cp'，需要将中间日志记录的内容从内存flush到磁盘，write pos 到cp'之间的位置才是又可以写入的内容
对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
MySQL 认为系统“空闲”的时候
MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
触发数据库flush过程的场景
redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页
如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。
数据flush到磁盘的过程
一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。
影响Mysql性能的因素
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
磁盘的 IOPS 可以通过 fio 这个工具来测试
策略一
刷脏页的速度控制
InnoDB 刷脏页的控制策略
Mysql抖动问题（0写入）
Mysql实践
Mysql
0 条评论
回复 删除
下一页
