mysql45讲学习笔记
写个博客记录我的学习过程,我也会相对容易坚持下去,不至于半途而废
强烈建议自己去看这个课,我这里只记录知识点,方便自己回顾和复习,看我这不行的,人家的都有例子和引导,让你理解知识点变得容易且印象深刻,不想花钱也行,私戳我要账号,就是博客私戳不一定秒回,建议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语句在引擎中执行流程(是个事务)
这边的流程变成这样是由于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
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。
如果主库永远启动不了,那么实际上在主库已经成功提交的事务,在从库上是找不到的,也就是数据丢失了。
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 :
在after_sync模式下解决了after_commit模式带来的数据不一致的问题,因为主库没有提交事务。但也会有个问题,当主库在binlog flush并且binlog同步到了备库之后,binlog sync之前发生了abort,那么很明显这个事务在主库上是未提交成功的(由于abort之前binlog未sync完成,主库恢复后事务会被回滚掉),但由于从库已经收到了这些Binlog,并且执行成功,相当于在从库上多出了数据,从而可能造成“数据不一致”。
此外,MySQL半同步复制架构中,主库在等待备库ack时候,如果超时会退化为异步后,也可能导致“数据不一致”。
还没有评论,来说两句吧...