MySQL阅读网上MySQL文章有感的杂记

前言，本篇文章将会记录各大MySQL文章的一些有意思的内容摘取，以及一些问题的提问，并且持续更新。

并且MySQL专栏将会记录MySQL常考的场景题等实战。

问题归类：

1.MySQL从加锁范围上分为哪三类?
2.全局锁加锁方法的执行命令是什么?主要的应用场景是什么?
3.做整库备份时为什么要加全局锁?
4.MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?
5.不建议使用set global readonly = true的方法加全局锁有哪两点原因?
6.表级锁有哪两种类型? 各自的使用场景是什么?
7.MDL中读写锁之间的互斥关系怎样的?
8.如何安全的给小表增加字段?

摘取：

既然session C blocked，拿不到写锁，那么session D为什么会被blocked呢？
回复: 如果说设计初衷，是为了防饿死吧

Online DDL的过程是这样的：

1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个
表可以正常读写数据，是因此称为“online ”
问题
如果第四步升级为MDL写锁的时候，这个表的MDL锁有其他事务占着，那么这个事务会阻塞，
等到可以拿到MDL写锁是吧
回复: 
对
而且如果不幸一直拿不到，最后锁超时了，就只好回滚这个DD了操作

摘取

sessionC（DDL操作）被前面的sessionA和B（查询操作，获取MDL 读锁）所阻塞。这里sessio
nC的DDL操作任务肯定是处于等待的，后续来的sessionD（查询操作）为什么会被sessionC所
阻塞？
我理解的是sessionC现在都还没有进行DDL操作，没有获取到MDL写锁，为什么sessionD会被
C阻塞？难道mysql Server端对于sessionC，D有一个 队列 来决定谁先执行？
回复:

“难道”正确😄
 

问题

1.两阶段锁的概念是什么? 对事务使用有什么帮助?
2.死锁的概念是什么? 举例说明出现死锁的情况.
3.死锁的处理策略有哪两种?
4.等待超时处理死锁的机制什么?有什么局限?
5.死锁检测处理死锁的机制是什么? 有什么局限?
6.有哪些思路可以解决热点更新导致的并发问题?

总结：

两阶段锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释
放， 而是要等到事务结束时才释放。
建议：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往
后放。
死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，
就会导致这几个线程都进入无限等待的状态。
解决方案：
1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默
认值是50s。
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执
行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。
如何解决热点行更新导致的性能问题？
1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量
的死锁检测工作了。
3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。
innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

摘取

1.innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)
2.事务在启动时会拍一个快照,这个快照是基于整个库的.
基于整个库的意思就是说一个事务内,整个库的修改对于该事务都是不可见的(对于快照读的情况)
如果在事务内select t表,另外的事务执行了DDL t表,根据发生时间,要嘛锁住要嘛报错(参考第六
章)
3.事务是如何实现的MVCC呢?
(1)每个事务都有一个事务ID,叫做transaction id(严格递增)
(2)事务在启动时,找到已提交的最大事务ID记为up_limit_id。
(3)事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo lo
g里,
并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
(4)再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transacti
on id做比对,
如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo lo
g里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数
据
4.什么是当前读,由于当前读都是先读后写,只能读当前的值,所以为当前读.会更新事务内的up_l
imit_id为该事务的transaction id
5.为什么rr能实现可重复读而rc不能,分两种情况
(1)快照读的情况下,rr不能更新事务内的up_limit_id,
而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复
读
(2)当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读

问题

把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
1.mysql如何判断一个查询的扫描行数?
2.索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?
3.可以重新统计索引信息的命令是什么?
4.如何定位索引选择异常这样的问题?
5.索引选择异常的问题可以有哪几种处理方式?

摘取

1：MySQL选错索引，啥意思？
我们认为使用K索引检索的速度会更快的，但是MySQL没有使用，决定使用什么索引是由Serv
er层的优化器来决定的，她也是想选择最佳的方案来检索数据的，不过他也是人写的程序也是
存在bug的。
2：MySQL为啥会选错索引？
优化器认为使用那个索引检索数据的速度比较快是一个需要各种因素综合评估的事情，比如：
是否使用临时表、是否排序、扫描的行数多少、回表的次数等，文中的例子优化器判断失误的
主要原因是扫描行数的判断存在误差，因为这个信息是采样评估得到的。索引的创建是非常的
耗时的，因为需要真正的建索引的过程，但是删除索引却不需要耗费太多时间，因为是标记删
除，这个是以空间换时间的思路。优化器采用采样评估出现误差的原因也在于，索引的标记删
除影响的。
3：MySQL选错索引怎么破？
3-1：强制指定使用某个索引，不常用不推荐用
3-2：调整SQL语句，使优化器选择的和我们想的一样，不具有通用性
3-3：新建更合适的索引或者删除不合适的索引，是一个思路
3-4：使用analyze table可以解决索引统计信息不准确导致的索引选错的问题
这篇刷新了认知，以前从没有思考过这个问题，觉得MySQL很牛逼没有什么bug，即使有我也
发现不了，如果使用是有问题也是自己不会使用。
现在还存在这个问题嘛？
通过改变使用方式就解决了，好像也不算是bug了？

摘取

我用存储过程插入100000条数据特别慢，后来我set autocommit=0,每1000条才co mmit，这样就快了。我想不出来这是为什么，求解惑

回复: Redo log 和 binlog刷盘次数少了，你把100000个事务变成了100个事务。

原谅我偷懒的想法，一个学校每年预估2万新生，50年才100万记录，能节省多少空间，直接

全字段索引。省去了开发转换及局限性风险，碰到超大量迫不得已再用后两种办法
回复: 从业务量预估优化和收益，这个意识很好呢

首先排除全部索引，占空间，其次排除前缀索引，区分度不高，再排除倒序索引，区分度还没
前缀索引高。
最后hash索引适合，而且只是登录检验，不需要范围查询。

总结

总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
1.
采用整个字符串作为索引. 在查询操作时, 执行顺序是怎样的?
2.
采用部分字段做索引, 在查询操作时, 执行顺序是怎样的?
3.
使用什么语句可以分析不同长度前缀的索引区分度是多少?
4.

前缀索引相比较整个字段做索引有什么优势和劣势?
5.
采用反序存储和hash字段作为索引有什么异同点?
 

删的时候，由于有未提交事务开启的一致性视图read-view，所以导致了存在两个数据版本的
数据，貌似优化器在"看"二级索引的时候，"看到"了多个历史版本的数据，错误以为有很多数
据
而主键索引数量由于确认机制不同，数量没有变，综合考虑，优化器选择了主键索引

摘取

问题的回答，“索引 a 上的数据其实就有两份”，是不是这样理解，
其中一份是已经被标记为deleted的数据，另一份是新插入的数据，对索引数据的预估把已经
被标记为deleted的数据也算上去了？MySQL对索引数据的预估为什么不去check 被标记为dele
ted的数据？
这种场景一旦发生，就会导致预估索引数据不准确，有什么好的方法去避免和解决？
作者回复: 理解对的，
方法就是避免长事务（session A 就是模拟一个长事务）

怎么给字符串字段加索引？
1：直接创建完整索引，这样可能比较占用空间；
这种方式最简单，如果性能没问题，我会这么创建，简单直接且存储空间的费用越来越低
2：创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
这种方式需要判断出前缀的长度多少合适，需要根据自己的业务来定，主要是看区分度多少合
适
3：倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
这种方式用于前缀区分度不高后缀区分度高的场景，目的还是要提高索引的区分度，使用这种
方式不适合范围检索
4：创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都
不支持范围扫描。
终于不太烧脑啦😄
学习这个专栏感觉速度好慢，需要反复听，另外，我喜欢看评论，这个评论太多了，好像买一
赠一。

在MySQL中，数据更新操作并不总是直接写入磁盘，而是先写入缓存（也就是内存），然后再按照一定的规则将缓存中的数据刷入磁盘。缓存中尚未刷入磁盘的数据被称为脏页（dirty page），而刷入磁盘的操作被称为flush。

flush操作通常由以下几种场景触发：

1. 内存池空间不足：MySQL会根据实际内存容量和配置参数来决定分配给内存池的空间大小。当内存池中的空间不足时，MySQL会尝试将脏页刷入磁盘以释放内存空间。

2. 后台线程周期性刷盘：MySQL会启动一个或多个后台线程，周期性地扫描缓存区域中的脏页，并将它们刷入磁盘。MySQL提供了一系列参数来配置后台线程的行为，如innodb_flush_log_at_trx_commit、innodb_io_capacity等。

3. 数据库关闭或备份：当MySQL关闭或备份数据库时，所有的脏页必须被刷入磁盘以保证数据的一致性。

MySQL将脏页刷入磁盘的过程比较耗时，特别是对于大量的修改操作（例如一次性插入10万行），可能会导致大量的脏页需要被刷入磁盘，从而降低系统的响应速度。因此，在平时执行很快的更新操作时，MySQL并不会立即将修改操作写入磁盘，而是将其写入内存中的缓冲池，并尽可能地等待后台线程或其他机制来进行flush操作。

当MySQL "抖" 一下的瞬间，通常就是在进行flush操作。在这个过程中，MySQL会阻塞当前进程，将缓存区域中的脏页刷入磁盘。因此，如果缓存中存在大量的脏页，flush操作可能会导致较长时间的阻塞和响应延迟。

需要注意的是，为了提高性能，MySQL提供了许多优化参数可以调整flush的策略和行为，如innodb_flush_method、innodb_max_dirty_pages_pct等。通过合理配置这些参数，可以使flush操作尽量避免影响系统的响应速度，从而提高数据库的性能和稳定性。

当内存不够用了，要将脏页写到磁盘，会有一个数据页淘汰机制（最久不使用），假设淘汰的
是脏页，则此时脏页所对应的redo log的位置是随机的，当有多个不同的脏页需要刷，则对应
的redo log可能在不同的位置，这样就需要把redo log的多个不同位置刷掉，这样对于redo log
的处理不是就会很麻烦吗？（合并间隙，移动位置？）

另外，redo log的优势在于将磁盘随机写转换成了顺序写，如果需要将redo log的不同部分刷
掉（刷脏页），不是就在redo log里随机读写了么？
回复: 好问题。
其实由于淘汰的时候，刷脏页过程不用动redo log文件的。
这个有个额外的保证，是redo log在“重放”的时候，如果一个数据页已经是刷过的，会识别出来并跳过。

问题

我观察了下公司的数据库确实发现了抖动现象，有几个问题，
1）Innodb_buffer_pool_pages_total这个值很大，百万级别的，而且数值不像是人为设置上去
的，是怎么来的呢？
2）Innodb_buffer_pool_pages_dirty达到4万多的时候就开始flush了，脏页比例是75，这肯定
是远达不到的，ssd磁盘，innodb_io_capacity是200，肯定可以提高。文章中说flush的触发条
件有2个，一个是内存不够了，一个是redo log 满了，那么我这个场景是哪种情况呢
作者回复: 1） 这个是innodb 数据页总是，过百万是正常的，16K一个，Bufree pool size 16G 就是100万了
2）你这个例子就是io_capacity设太小了...

又思考了一下，请老师帮忙看一下理解的对不对：buffer pool里维护着一个脏页列表，假设现
在redo log 的 checkpoint 记录的 LSN 为 10，现在内存中的一干净页有修改，修改后该页的L
SN为12，大于 checkpoint 的LSN，则在写redo log的同时该页也会被标记为脏页记录到脏页列
表中，现在内存不足，该页需要被淘汰掉，该页会被刷到磁盘，磁盘中该页的LSN为12，该页
也从脏页列表中移除，现在redo log 需要往前推进checkpoint，到LSN为12的这条log时，发现
内存中的脏页列表里没有该页，且磁盘上该页的LSN也已经为12，则该页已刷脏，已为干净
页，跳过。
回复: 对的。

我想问一下，innodb是如何知道一个页是不是脏页的，是有标记位还是通过redolog的c
keckpoint来确定的？
作者回复: 每个数据页头部有LSN，8字节，每次修改都会变大。
对比这个LSN跟checkpoint 的LSN，比checkpoint小的一定是干净页

flush和purge是不是还是有区别的？flush主要指刷新脏页，和clean进程相关？而pur
ge是清理不再被使用的undo信息。
回复: 对，
flush 一般是说刷脏页，
purge一般是指清undo log,
merge一般是指应用change buffer

总结

把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
1.脏页和干净页的定义是什么?
2.引发数据库flush脏页的四种典型场景各是什么?对mysql性能的影响各是怎样的?
3.缓存池中的内存页有哪三种状态? 哪两种刷脏页的情况会比较影响性能?
4.innodb_io_capacity这个参数的作用是什么,这个参数设置错误可能导致什么样的后果, 如何正
确的设置这个参数?
5.mysql如何通过innodb_io_capacity, 脏页比例(M), 当前日志序号(N)这三个指标来控制以什么
样的速度去刷脏页的?
6.innodb_flush_neighbor这个参数表示什么意思,应该如何设置?

摘取

1：为啥删除了表的一半数8据，表文文件大小没变化？
因为delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是
不会变的。也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，表
文件的大小当然是不变的啦！

2：表的数据信息存在哪里？
表数据信息可能较小也可能巨大无比，她可以存储在共享表空间里，也可以单独存储在一个
以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，老师建议总是作为一个单独的
文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应
的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。
3：表的结构信息存在哪里？
首先，表结构定义占有的存储空间比较小，在MySQL8.0之前，表结构的定义信息存在以.frm
为后缀的文件里，在MySQL8.0之后，则允许把表结构的定义信息存在系统数据表之中。
系统数据表，主要用于存储MySQL的系统数据，比如：数据字典、undo log(默认)等文件
4：如何才能删除表数据后，表文件大小就变小？
重建表，消除表因为进行大量的增删改操作而产生的空洞，使用如下命令：
1：alter table t engine=InnoDB
2：optimize table t( 等于 recreate+analyze)。
3：truntace table t (等于drop+create)
5：空洞是啥？咋产生的？
空洞就是那些被标记可复用但是还没被使用的存储空间。
使用delete命令删除数据会产生空洞，标记为可复用
插入新的数据可能引起页分裂，也可能产生空洞
修改操作，有时是一种先删后插的动作也可能产生空洞

摘取

很喜欢的MySQL，绝对干货哈哈。
这里针对空洞提下问题：
1.删除有空洞，是因为标记了已删除可复用的节点位置，不会释放。
2.随机插入有空洞，是因为数据页分裂造成。
3.但更新有空洞，有点费解，我个人理解是更新是结合删除和插入的一个合并操作。删除后产
生的空洞，在插入时不是应该就马上被复用了吗，毕竟主键是一致的。所以为什么更新会产生
空洞呢？？
回复: 可以这么想下，如果1，2，3，4，5
然后update把2 改成6， 如果原地修改，这个索引就不是“有序”的了

总结

把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
1.innodb_file_per_table参数是什么意思, 一般如何设置更合理?
2.删除数据和插入数据为什么会造成出现空间空洞的情况? 原因各是什么?
3.online DDL在重建表流程上比非online DDL的方式做了哪些流程上的优化?

4.Online和replace两个概念各是什么, 他们之间有什么样的区别?