mysql45讲学习笔记

左手的ㄟ右手 2022-08-29 10:29 186阅读 0赞

写个博客记录我的学习过程，我也会相对容易坚持下去，不至于半途而废

强烈建议自己去看这个课，我这里只记录知识点，方便自己回顾和复习，看我这不行的，人家的都有例子和引导，让你理解知识点变得容易且印象深刻，不想花钱也行，私戳我要账号，就是博客私戳不一定秒回，建议QQ私戳

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

第一讲

首先我们来认识一条sql查询语句的执行过程

![b918a2e971fcf686a97f4fb7edae7f17.png][]

首先是用户登录，tcp三次握手请求连接，得到权限

连接分为长连接和短连接

短连接：查询几次后就断开，后续还需要查询需要重连

长连接：连接完成后，客户端持续请求，就会一直保持连接

建立连接的过程较为复杂，所以建议使用长连接

但是连接过程中临时使用的内存存在连接对象中，也就是说一直长连接+请求后内存会涨的 飞快，结果被系统强行kill，也就是mysql异常重启

如何解决？

1.定期断开长连接

2.执行 mysql\_reset\_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限 验证，但是会将连接恢复到刚刚创建完时的状态。（适用于mysql5.7以后版本）

然后会在缓存中查询对应数据，缓存一般在内存中，key-value存储键值对的方式，key是sql语句，value是查询结果，但是更新数据库就会清空缓存，对于增删改较少操作的静态表比较适合使用查询缓存（8.0版本后数据库删除了该功能）

**分析器**：（判断语法正确性+分解为各个Token字段）

**优化器**：选择哪个索引？以及多表链接决定表的链接顺序，以课上内容举例好理解

select \* from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

如何决定方案？如何选择索引？（他说后续讲）

**执行器**：先查询是否对这些表有访问权限，然后去调用相关引擎接口去查询符合条件的数据并返回给客户端

第一讲end

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

第二讲

表的更新语句执行过程

和查询的流程一样，只是返回结果那一步改为修改数据

会涉及到两个日志模块redo log（重做日志）和 binlog（归档日志）

这两种日志有以下三点不同。

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以 使用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录 的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文 件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

WAL技术：Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

出现更新操作时，先记录进日志中，当数据库不忙时，将更新数据写进磁盘中，如果数据库一直很忙，日志就会写满，那么数据库就得腾出手先把日志持久化进磁盘中

redo log固定大小，采用双指针操作，一个记录更新的位置write pos，一个记录写进磁盘的位置checkpoint，当更新记录指针到文件末尾就再次重头开始，如果追上了checkpoint，就发生了日志写满的情况，需要先写一部分日志到磁盘中推进下checkpoint

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

redolog是InnoDB特有日志，binlog是mysql自带的日志

updata语句在引擎中执行流程（是个事务）

![6d64f057aefdb587fed49a4d5b6d0dba.png][]

这边的流程变成这样是由于redolog只有InnoDB含有，又为了保存两个日志的一致性，目前看来只有redolog就可以更新了，但是binlog的意义在于，无限大小，于是就可以复现很久前的数据库，redolog不行

隔离：略

索引：略

写了半天的mysql如何通过版本号实现可重读+一致性读的，还有可重读实现时你又一个当前状态的视图，但是复制整个数据库显然不现实，通过版本号+链表就可以搞定

我TM一不小心给删了，我想吐

每行数据的更新有一个版本号用链表记录每次更新的版本，你在别的事务中查询该行数据，就在链表中找小于事务版本号的最大的版本状态读入，可重读就是这么实现的

一致性读，就是你的事务+1已经提交了，我之后读取的数，应该是你提交的还是没提交的呢，如果自己先updata该行+1，然后查询，那就是+2后的结果，如果在updata前查询就是+0的结果

这是因为updata要在最新状态下更新，不然会丢失其中的更新

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

优化器的逻辑：

优化器是如何选择索引的？

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

选择条件：区分度，就是去重后数据的数目，越多区分度越好，采样统计得到数目

优化器能预估出select语句扫描行数，根据扫描行数去判断走索引与否或是走哪条索引，但结果不一定准确，课上就举了两个例子

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

数据库的删除不是立即删除，原有的位置会被标记，定义为无效数据，之后随机插入数据会优先在无效数据中替换（复用），所以删除数据并不会使磁盘文件变小，你可以选择重构表来使数据库文件变小，但是重构的时候无法查找，所以要选择合适的场景，重构后的数据库会预留一部分空间来处理后续可能的插入

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

为什么count(\*)这么慢

InnoDB的策略是全局扫描，累计计数，于是我们考虑用redis缓存来存储该数据，但是redis在内存中，如果不慎重启，重启期间的增删会导致数据不一致，以及事务对redis缓存正确性的影响

比如你是先让redis+1还是先插入一行呢，在这两个操作中有一个输出最近100次数据行和输出redis计数总数，会发现比如会出现数据不一致的情况

redis在数据库中新建一张表即可，前面会出现的数据不一致可以由加锁保证数据的一致性

突然就看吐了，有心情的时候再学吧！，先一目十行干完剩下的，想细细读的时候再来一遍

面试被吊起来羞辱了，我又回来学习了

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql日志

在InnoDB引擎前，mysql只有一个日志文件bin log，具体操作就是将每一行修改数据库操作记录下来存入磁盘中

binlog写入机制具体操作为，一个事务的sql修改操作，会先存入bin log cache，事务提交的时候将cache里面的内容写入binlog文件中

每个事务都有一个属于自己的bin log cache

write：写入page cache中

fsync：数据持久化到磁盘

选择那种操作是由参数sync\_binlog控制

sync\_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

sync\_binlog=1 的时候，表示每次提交事务都会执行 fsync；

sync\_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

redo log写入机制为

为了控制 redo log 的写入策略，InnoDB 提供了 innodb\_flush\_log\_at\_trx\_commit 参数，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**数据库主备一致性**

MySQL的复制分为：异步复制、半同步复制、全同步复制。

异步复制

主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致“数据不一致”。早期MySQL仅仅支持异步复制。

半同步复制

MySQL在5.5中引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log（中转日志）中，半同步复制通过rpl\_semi\_sync\_master\_wait\_point参数来控制master在哪个环节接收 slave ack，master 接收到 ack 后返回状态给客户端，此参数一共有两个选项 AFTER\_SYNC & AFTER\_COMMIT。

配置为WAIT\_AFTER\_COMMIT

![1][]

rpl\_semi\_sync\_master\_wait\_point为WAIT\_AFTER\_COMMIT时，commitTrx的调用在engine层commit之后，如上图所示。即在等待Slave ACK时候，虽然没有返回当前客户端，但事务已经提交，其他客户端会读取到已提交事务。如果Slave端还没有读到该事务的events，同时主库发生了crash，然后切换到备库。那么之前读到的事务就不见了，出现了数据不一致的问题，如下图所示。图片引自[Loss-less Semi-Synchronous Replication on MySQL 5.7.2][]。

![2][]

如果主库永远启动不了，那么实际上在主库已经成功提交的事务，在从库上是找不到的，也就是数据丢失了。  
PS：早在11年前后，阿里巴巴数据库就创新实现了在engine层commit之前等待Slave ACK的方式来解决此问题。

配置为WAIT\_AFTER\_SYNC

MySQL官方针对上述问题，在5.7.2引入了Loss-less Semi-Synchronous，在调用binlog sync之后，engine层commit之前等待Slave ACK。这样只有在确认Slave收到事务events后，事务才会提交。如下图所示，图片引自[Loss-less Semi-Synchronous Replication on MySQL 5.7.2 ][Loss-less Semi-Synchronous Replication on MySQL 5.7.2]：

![3][]

在after\_sync模式下解决了after\_commit模式带来的数据不一致的问题，因为主库没有提交事务。但也会有个问题，当主库在binlog flush并且binlog同步到了备库之后，binlog sync之前发生了abort，那么很明显这个事务在主库上是未提交成功的（由于abort之前binlog未sync完成，主库恢复后事务会被回滚掉），但由于从库已经收到了这些Binlog，并且执行成功，相当于在从库上多出了数据，从而可能造成“数据不一致”。  
  此外，MySQL半同步复制架构中，主库在等待备库ack时候，如果超时会退化为异步后，也可能导致“数据不一致”。

[b918a2e971fcf686a97f4fb7edae7f17.png]: /images/20220829/57efe73743f84870972a32bbd4cf369d.png
[6d64f057aefdb587fed49a4d5b6d0dba.png]: /images/20220829/9786f0570fc84983b88f1a53f7558c8b.png
[1]: /images/20220829/cff3070ae3604a0b95b63ab2eaf57875.png
[Loss-less Semi-Synchronous Replication on MySQL 5.7.2]: https://yq.aliyun.com/go/articleRenderRedirect?url=http%3A%2F%2Fmy-replication-life.blogspot.com%2F2013%2F09%2Floss-less-semi-synchronous-replication.html
[2]: /images/20220829/a97e09434d0f43218c1d88905bfcd3e8.png
[3]: /images/20220829/a9d7c809d2904a6992ef86e4e3e79281.png