MySQL 杂谈

1、MySQL的运行流程是怎样的？

连接器：建立连接，管理连接，校验用户身份
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
到解析器：进行语法分析和词法分析，判断SQL语句是否有错误，如果没有则构建语法书，如果有返回错误给客户端
预处理器：检查SQL语句中的表或者字段是否存在，另外就是将select*中的 *符号，扩展为表上的所有列
优化器：确定SQL查询语句的执行方案，是否使用索引，使用了哪些索引等
执行器：根据执行计划执行SQL语句，从存储引擎读取数据及，返回给客户端

2、MySQL的数据存放在哪个文件？

MySQL的存储行为由存储引擎是吸纳，MySQL支持多种存储引擎，不同的存储引擎保存的文件也不同。

MySQL的数据文件存放在/var/lib/mysql，包含三个文件

db.opt：存储当前数据库的默认字符集和字符检验规则
t_tablename.frm：保存每个标的元数据信息，主要包含表结构定义
t_tablename.ibd：保存表数据

3、表空间文件的结构是怎么样的？

表空间由段、区、页、行组成。

行：数据库表中的记录都是按行进行存放的，每行记录根据不同的行格式有不同的存储结构
页：记录是按照行来存储，读取是按照页为单位来读写。每页的大小为16KB，当需要读取一条记录时，以页为单位，将其整体读入内存
区：B+树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，就会非常慢。所以在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位来分配，而是按照区为单位来分配。每个区大小为1MB，对于16KB的页来说，连续的64个页会被划为一个区，这样链表中相邻的页的物理位置也相邻，就可以使用顺序I/O了
段：表空间是由各个段组成的，段是由各个区组成的，段一般分为索引段（存放B+叔的非叶子节点的区的集合），数据段（存放B+树的叶子节点的区的集合），回滚段（存放回滚数据的区的集合）

4、行溢出后，MySQL怎么处理？

MySQL中磁盘和内存交互的基本单位是页，一个页的大小是16KB，也就是16384字节，而一个varchar类型最多可以存储65532字节，一些大对象列入TEXT可能存储更多的数据，这时一个页存不了一条记录，会发生行溢出，在记录的真实数据处只会保存该列的一部分数据，而把多余的数据放在溢出页中，然后真实数据处用20字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

5、varchar最多可以存储多少字节？

最多可以存储65532个字节，因为一般一个页就是16KB，也就是65532个字节，然后NULL值列表占用1个字节，变长字段长度列表占用2个字节，剩下的就是65532个字节

6、MySQL中的NULL值是怎么存放的？

MySQL的compact行格式会用NULL值列表来标记值为NULL的列，所以NULL值并不会存储在行格式中的真实数据部分

7、MySQL怎么知道varchar实际占用数据的大小？

MySQL的compact行格式会用变长字段长度列表存储变长字段实际占用的数据大小

8、什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说索引就是数据的目录，索引和数据位于存储引擎中

9、索引的分类？

按数据结构分类：B+Tree索引，Hash索引，Full-text索引
按物理存储分类：聚簇索引（主键索引），二级索引（辅助索引）
按字段特性分类：主键索引，唯一索引，普通索引，前缀索引
按字段个数分类：单列索引，联合索引

10、为什么MySQL InnoDB选择B+Tree作为索引的数据结构？

B+Tree VS B Tree

B+Tree只在叶子节点存储数据，而B Tree的非叶子阶段也要存储数据，所以B+Tree的单个节点的数据量更小，在相同的磁盘I/O次数下，能查询到更多的节点。另外B+Tree叶子结点采用双链表链接，适合MySQL中常见的基于范围的顺序查找，而B Tree无法做到这一点

B+Tree VS 二叉树

对于有N个叶子节点的B+Tree，其搜索复杂度为0（logdN），d表示节点允许的最大子节点个数，在实际应用中，d肯定是大约100的，这样保证即使数据达到千万级别，B+Tree的高度依旧维持在3到4层左右，也就是说一次数据查询操作，只需要做3到4次的磁盘I/O就可以查询到数据。而二叉树的每个父节点的儿子节点个数只能是2个，意味着其搜索复杂度为O(logN），这意味检索到目标数据及所经历的磁盘I/O次数要更多

B+Tree VS Hash

Hash做等值查询的时候效率很高，搜索复杂度为O（1），但是不适合做范围查询

11、什么是覆盖索引？

在查询时使用了二级索引，如果查询的数据能在二级索引里查询到，那么就不需要进行回表，这个过程就是覆盖索引

12、什么是回表？

在查询时使用了二级索引，如果查询的数据不能在二级索引里查询到，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，检索主键索引才能查询到数据，这个过程就是回表

13、什么是主键索引？

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多有一个主键索引，索引列不允许有空值

14、什么是唯一索引？

唯一索引建立在unique字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一但是允许有空值

15、什么是普通索引？

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为unique

16、什么是前缀索引？

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char，varchar，binary，varbinary的列上。使用签注索引的目的是为了减少索引占用的存储空间，提升查询效率

17、什么是联合索引？

通过将多个字段组合成一个索引，该索引就被称为联合索引，使用联合索引时存在最左匹配原则。在遇到范围查询比如><的时候就会停止匹配，也就是范围查询的字段可以使用联合索引，但是在范围查询字段的后边的字段无法使用联合索引。注意：对于>=,<=，between，like前缀匹配的范围查询，并不会停止匹配。

18、什么是索引下推？

索引下推优化可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。

19、有什么优化索引的方法？

前缀索引优化：使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度，在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小
覆盖索引优化：覆盖索引是指SQL中query的所有字段，在索引B+Tree的叶子节点都能找到那些索引，假如我们只需要查询商品的名称、价格，我们可以建立一个联合索引，即商品ID、名称、价格作为一个联合索引，如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表
主键索引最好是自增的：InnoDB创建主键索引默认是聚簇索引，数据被存放在B+Tree的叶子节点上，也就是同一个叶子节点内的各个数据是按主键顺讯存放的，因此每当有一条新的数据插入的时候，数据库会根据主键将其插入到对应的叶子节点中。如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动现有数据，当页面写满就会自动开辟一个新页面，因为每次插入一条新纪录都是追加操作，不需要重新移动现有数据，因此这种插入数据的方法效率非常高。如果我们使用非自增主键，每次插入主键的索引值都是随机的，每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其他数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常称这种情况为页分裂，页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。另外主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小，（二级索引的叶子节点存放的就是主键值），这样二级索引占用的空间也就越小。
索引最好设置为NOT NULL：索引存在NULL会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为为NULL的列会使索引、索引统计和值都比较复杂。另外NULL值是一个没意义的值，但是他会占用物理空间，如果表中存在允许为NULL的字段，那么compact行格式至少会占用1字节空间存储NULL值列表
防止索引失效：当使用左或者左右模糊匹配的时候，也就是like %xx或者like %xx%这两种方式会造成索引失效；当我们在查询条件中对索引列左计算，函数，类型转换的操作也会导致索引失效；联合索引要能正确使用需要遵循最左匹配原则；在where子句中，如果在or前的条件是索引列，而在or后的条件列不是索引列，那么索引会失效。

20、explain中返回的参数及其意义？

possible_keys：可能用到的索引
keys：实际用到的索引，如果为NULL表示没有使用索引
key_len：索引的长度
rows：扫描的数据行数
type：数据扫描类型：ALL（全表扫描），index（全索引扫描），range（索引范围扫描），ref（非唯一索引扫描），eq_ref（唯一索引扫描），const（结果只有一条的主键或唯一索引扫描）

21、count（*）和count（1）有什么区别，性能更好？

count（1）、count（*）count（主键字段）在执行的时候，如果表中存在二级索引，优化器就会选择key_len最小的二级索引进行扫描，相比于扫描主键索引效率会高一些，不要使用count（字段）来统计个数，因为他的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为NULL的记录个数，建议给这个字段建立一个二级索引。

23、InnoDB引擎通过什么技术来保证事务的四个特性？

持久性是通过redo log（重做日志）来保证
原子性是通过undo log（回滚日志）来保证
隔离性是通过MVCC（多版本并发控制）或者锁机制来保证
一致性通过持久性+原子性+隔离性来保证

24、Read View在MVCC里是如何工作的？

Read View有四个重要字段：

m_ids：指的是在创建Read View时，当前数据库中活跃事务（启动了但是还没有提交的事务）的事务id列表
min_trx_id：指的是在创建Read View时，m_ids列表中的最小值
max_trx_id：指的是创建Read VIew时当前数据库中应该给到下个事务的id值，也就是全局事务中最大的事务id值+1
creator_trx_id：指的是创建Read View的事务的事务id
聚簇索引记录的两个隐藏列：trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务id记录到trx_id隐藏列中；roll_pointer：每次对某条聚簇索引记录进行该懂事，都会把旧版本的记录写入到undo log中，然后这个隐藏列是个指针，指向每一个旧版本记录，通过它可以找到修改前的记录

当一个事务区访问记录时，除了自己的更新记录总是可见之外，还有几种情况：

如果记录的trx_id值小于Read View中的min_trx_id值，表示这个版本的记录是在创建Read View之前已经提交的事务生成的，所以该版本的记录对当前事务可见
如果记录的trx_id值大于Read View中的max_trx_id值，表示这个版本的记录是在创建Read View之后才启动的事务生成的，所以该版本的记录对当前事务不可见
如果记录的trx_id值在Read View的min_trx_id和max_trx_id之间，那么需要判断trx_id 是否在m_ids 列表中，如果存在列表中，表示生成该记录的活跃事务依旧活跃着，所以该版本的记录对当前事务不可见，如果不在表示生成该版本记录的活跃事务已经被提交了，所以该版本的记录对当前事务可见。

25、MySQL可重复读隔离级别，如何解决幻读？

MySQL InnoDB引擎的默认隔离级别虽然是可重复读，但是它很大程度上避免幻读现象（并不是完全解决），解决方案有两种：

针对快照读（普通的select语句）：通过MVCC方式解决了幻读，因为可重复读隔离级别下，在开始事务后，执行第一个查询语句时会创建一个Read View，后续的查询语句利用这个Read View可以在undo log版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的。即使中途有其他事务插入了数据，是查询不出来这条数据的，很好的避免了幻读问题
针对当前读（select … for update语句）：通过next-key lock临键锁（记录锁+间隙锁）方式解决了幻读。因为当执行select … for update语句的时候，会加上next-key lock如果有其他事务在next-key lock锁范围内修改了一条记录，那么这个修改语句就会被阻塞，无法修改成功，很好的避免了幻读问题

26、MySQL可重复读隔离级别下，如果发生幻读？

快照读：MVCC并不能完全避免幻读现象，比如当事务A更新了一条事务B插入的记录，那么事务A前后两次查询的记录条目就不一样了，所以就发生了幻读
当前读：如果事务开启后，并没有执行当前读而是先执行快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目不一样了，所以就发生了幻读

27、什么是死锁？

在MySQL中，当两个或多个事务同时请求访问同一组资源，并且每个事务都持有其他事务需要的锁时，就可能会发生死锁。比如，事务A持有资源X，事务B持有资源Y，但事务A也需要访问资源Y，而事务B也需要访问资源X，这种情况下如果两个事务都不释放当前持有的锁，那么救护陷入死锁状态，无法向前推进，直到其中一个事务放弃锁或者超时

28、如何避免死锁？

死锁的四个必要条件：互斥，占有等待，不可强占用，循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件死锁就不会成立。

设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚操作，于是锁就释放了，另一个事务就可以继续执行。InnoDB默认超时时间是50秒
开启主动死锁检测：主动死锁检测在发现死锁后，主动回滚死锁链条中的其中一个事务，让其他事务得以继续进行。默认开启

29、undo log的具体操作？

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删除就可以
在删除一条记录时，要把这条记录的内容都记下来，这样之后回滚时再把这些内容组成的记录插入到表中就可以
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就可以

流程：

开始事务：当用户开始一个事务时，MySQL会为该事务分配一个惟一的事务ID，并为该事务创建一个undo log
写入undo log：在事务执行过程中，如果对数据进行了修改，MySQL会将修改前的数据记录到undo log中，并为每个记录添加一个指针，指向对应的数据页和记录位置
提交事务：当用户提交事务时，MySQL会将所以偶的修改操作写入redo log，并将redo log写入磁盘，同时MySQL会将该事务的undo log标记为可回收
回滚事务：如果用户发起了一个回滚操作，MySQL会找到该事务的undo log，并将其中的记录应用到对应的数据页中，恢复数据到事务开始前的状态

需要注意的是，undo log的记录是在内存中进行的，而不是直接写入磁盘，因此，在MySQL发生异常关闭或者崩溃时，未提交的事务的undo log可能会丢失，从而导致数据损坏或丢失。为了避免这种情况，MySQL使用了redo log和check破in特瑞机制来保证数据的持久性和一致性

30、undo log 的作用？

实现事务回滚，保障事务的原子性：事务处理过程中，如果出现了错误或者用户执行了rollback语句，MySQL可以利用undo log中的历史数据将数据恢复到事务开始之前的状态
实现MVCC的关键因素之一：MVCC是通过Read View+undo log实现的，undo log为每条记录保存多份历史数据，MySQL在执行快照读的时候，会根据事物的Read View里的信息，顺这undo log的版本链找到满足其可见性的记录

31、redo log的具体操作？

当一条记录需要更新的时候，InnoDB引擎会先更新内存（同时标记为脏页），然后将本次对这个页的修改先写入到redo log Buffer，随后在持续化到磁盘，后续InnoDB引擎会在适当的时候，由后台线程将缓存在Buffer Pool的脏页刷新到磁盘里，这就是WAL（Write-Ahead Logging）技术。

流程：

开始事务：当用户开始一个事务时，MySQL会为该事务分配一个唯一的事务ID，并为该事务创建一个redo log buffer
写入redo log buffer：在事务执行过程中，如果对数据进行了修改，MySQL会将修改操作记录记录到redo log buffer中
刷写到磁盘：当redo log buffer被写满时，MySQL会将其中的记录写入到磁盘的redo log文件中，此时事务还未提交，但是修改操作已经被记录下来，保证事务的持久性
提交事务：当用户提交事务时，MySQL会将redo log中的所有记录应用到对应的数据页中，将修改操作落实到磁盘上，从而保证事务的一致性

32、redo log要写到磁盘，数据也要写到磁盘，为什么要多此一举？

写入redo log的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的顺序写比随机写高效的多，因此redo log写入磁盘的开销更小。

33、为什么需要redo log？

实现事务的持久性，让MySQL有了crash-safe（崩溃恢复）的能力，能够保证MySQL在任何时间段突然崩溃，重启后之前以提交的记录都不会丢失。
将写操作从随机写变成了顺序写，提升MySQL写入磁盘的性能

34、redo log什么时候刷盘？

MySQL正常关闭时
当redo log Buffer中记录的写入量大于redo log Buffer内存空间的一半时
InnoDB的后台每隔一秒，将redo log Buffer持久化到磁盘
每次事务提交时都将缓存在redo log Buffer里的redo log直接持久化到磁盘

注意：将缓存在redo log Buffer里的redo log写入到redo log文件，并不意味着写入到了磁盘，因为操作系统的文件系统有个page cache，是专门用来缓存文件数据的，所以写入redo log文件意味着是写入到了操作系统的文件缓存，所以当MySQL崩溃时，只要操作系统没有崩溃，那这部分数据也不会丢失

35、redo log写满了怎么办？

默认情况下，InnoDB存储引擎有一个重做日志文件组（redo log group），由两个redo log文件组成，分别为ib_logfile0和ib_logfile1.重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。随着系统运行，Buffer Pool的脏页刷新到了磁盘中，那么redo log对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。当redo log文件满了，MySQL也不能执行新的更新操作就会阻塞，等待将Buffer Pool中的脏页刷新到磁盘中，然后擦除旧的redo log记录腾出新空间了再继续执行新的更新操作。

36、redo log和binlog的区别？

适用对象不同

binlog是MySQL的server层实现的日志，所有存储引擎都可以用

redo log是InnoDB存储引擎实现的日志
文件格式不同

binlog有三种格式类型，分别是statement（每一条修改数据的SQL都会被记录到binlog中，主从复制总slave端再根据SQL语句重现，但是statement有动态函数问题，比如使用了UUID或者弄完这些函数，你在主库上执行的结果并不是你在从库执行的结构，这种随时在变得函数会导致复制的数据不一致）ROW（记录行数据最终被修改成什么样，虽然不会出现statement下动态函数的问题，但是每行数据的变化结果都会被记录，要是批量执行更新操作命令，就会记录非常多数据，导致binlog过大），MIXED（包含两种格式，可以根据不同的情况自动使用ROW模式或者STATEMENT模式）

redo log是物理日志，记录的是某个数据页做了什么修改
写入方式不同

binlog是追加写，写满一个文件就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量日志

redo log是循环写，日志空间大小是固定的，全部写满就擦除记录从头开始，保存未被刷入磁盘的脏页日志
用途不同

binlog用于备份恢复，主从复制

redo log用于断电等故障恢复

37、MySQL主从复制详细过程？

MySQL主库在收到客户端提交事务的请求后，会先写入binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回客户端操作成功的响应
从库会创建一个I/O线程，连接主库的log dump线程，来接收主库的binlog日志，再将binlog信息写入到relay log的中继日志里，再返回给主库复制成功的响应
从库会创建一个用于回放binlog的线程，读取relay log中继日志，然后回放binlog更新存储引擎的数据，最后实现主从的数据一致性

38、binlog什么时候刷盘？

事务执行过程中，先把日志文件写入到binlog cache（server层的cache）。事务提交的时候，再把binlog cache写入到binlog文件中，然后根据sync_binlog参数来控制数据库的binlog刷到磁盘上的频率。

39、具体一条更新记录“update user set name=‘aaa’ where id = 1”的具体流程

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取到id=1这一行数据，如果id=1这一行的数据页刚好在Buffer pool中，直接返回给执行器更新。如果不在则将数据页读入到Buffer pool中，返回记录给执行器
执行器得到聚簇索引记录后，会看一下更新前后的记录是否一样，如果一样，则不进行后续的操作，如果不一样则把个更新前后的记录当做参数传给InnoDB层，让InnoDB真正的执行更新记录的操作
开启事务，InnoDB层更新记录前会先写入相应的undo log，预防回滚
InnoDB开始更新记录，先更新内存（将数据行所在的页标记为脏页），然后将记录写到redo log中。为了减少磁盘I/O，不会立刻将脏页写入到磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘，这就是WAL技术。
至此，一条记录更新完成了
在更新语句执行完成后，开始记录该语句对应的binlog，此时记录的binlog会保存到binlog cache，并没有刷新到硬盘上的binlog文件，在事务统一提交时会将该事务运行过程中的所有binlog刷新到硬盘中
事务提交，将redo log对应的事务状态设置为prepare，然后将redo log刷新到硬盘，将binlog刷新到硬盘，接着调用引擎的提交事务接口，将redo log状态设置为commit，将redo log文件刷入到硬盘
至此，一条更新语句执行完成

40、为什么要有Buffer Pool？

InnoDB存储引擎设计缓冲池，来提高数据库的读写性能

当读取数据时，如果数据在Buffer Pool中，客户端会直接读取Buffer Pool中的护具，否则再去磁盘中读取
当修改数据时，首先修改Buffer Pool中的数据，并将数据所在页标记为脏页，最后由后台线程将脏页写入磁盘

41、如何管理空闲页？

Buffer Pool是一片连续的内存空间，当MySQL运行一段时间以后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。当我们从磁盘读取数据的时候，不可能总是遍历这一片连续的内存空间来找到空闲的缓存页，这样效率太低，所以为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的控制块作为链表的节点，这个链表成为Free链表（空闲链表）。Free链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址以及当前链表中节点的数量等信息。Free链表节点是一个一个的控制块，每个控制块包含缓存页的地址，所以相当于Free链表节点都对应一个空闲的缓存页。有了Free链表后，每当需要从磁盘加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上然后把该缓存页对应的控制块从Free链表中移除。

42、如何管理脏页？

和Free链表类似，设计Flush链表，链表的节点也是控制块，区别在于FLush链表的元素是脏页

43、什么是Buffer Pool污染？

当某一个SQL语句扫描了大量的数据时，在Buffer Pool空间有限的情况下，可能会把Buffer Pool中的所有页都替换出去，导致大量热数据被淘汰，等这些热数据再次被访问时，由于缓存为命中，就会产生大量的磁盘I/O，MySQL性能会急剧下降，这个过程被称为Buffer Pool污染。一般出现全表扫描和全索引扫描时就会出现Buffer Pool污染的情况

44、如何解决出现Buffer Pool污染而导致缓存命中率下降的问题？

LRU链表中young区域的数据就是热点数据，只要我们提高进入到young区域的门槛，就能有效保证young区域里的热点数据不会被替换掉。MySQL在进入到young区域条件增加了一个停留在old区域的时间判断。

当对某个old区域的缓存页进行第一次访问时，就在它对应的控制块中记录本次访问的时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页不会被从old区域移动到young区域的头部，如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到young区域的头部。

只有同时满足被访问与在old区域停留时间超过1秒这两个条件，才会被插入到young区域头部，这样就能解决Buffer Pool 污染的问题。

45、脏页什么时候会被刷入磁盘？

当redo log 日志满了，会主动触发脏页刷入磁盘
Buffer Pool空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要现将脏页刷入磁盘
MySQL认为空闲时，后台线程会定期将适量的脏页刷入到磁盘
MySQL正常关闭之前，会将所有的脏页刷入到磁盘

46、undo log的具体数据

------------------------
--- TRANSACTIONS
------------------------

Trx id counter 19988
Purge done for trx's n:o < 19988 undo n:o < 0 state: running
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421703546277024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421703546278016, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

------------------------
--- ROW OPERATIONS
------------------------

---TRANSACTION 421703546277024, not started
MySQL thread id 8, OS thread handle 139911965771776, query id 1448 localhost root
---TRANSACTION 421703546278016, not started
MySQL thread id 6, OS thread handle 139911967008512, query id 1446 localhost root

这个 undo log 的示例是 MySQL 的事务列表，包括了当前运行的事务 ID、正在等待锁的事务 ID、锁的状态、历史事务列表等信息。在这个示例中，可以看到两个事务正在等待锁的状态，它们的事务 ID 分别是 421703546277024 和 421703546278016。

需要注意的是，undo log 记录的内容与 redo log 有所不同，主要是用于事务回滚和 MVCC 特性的支持，因此记录了修改之前的原始数据，以便在事务回滚时可以恢复到之前的状态。在实际的 MySQL 数据库中，undo log 通常是以回滚段的形式存储在磁盘上，用于支持事务的回滚和 MVCC 特性的实现。

47、redo log的具体数据

# Time: 2022-05-06T14:37:22.000000Z
# Transaction ID: 129
# Table Name: users
# Log type: UPDATE

# Update values
# Old value: "Alice", 25
# New value: "Alice", 26

SET @@SESSION.GTID_NEXT= '7c5cf1cc-afb7-11ec-bf5e-0242ac130004:129';
BEGIN;
# at 2854

UPDATE users SET age = 26 WHERE name = 'Alice';
# at 2909

COMMIT;

这个 redo log 记录了一次对 users 表中 name 为 ‘Alice’ 的记录进行更新操作，将 age 值从 25 修改为 26。可以看到，这个 redo log 记录了更新操作所在的事务 ID、操作类型、被修改的表名、旧值和新值等重要信息，同时还记录了事务的开始和结束，以及 GTID 等信息，用于保证事务的原子性和一致性

48、binlog的具体数据

# at 30011
#210518 10:53:07 server id 1  end_log_pos 30062 CRC32 0x214d6eb4 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1621309987/*!*/;
BEGIN
/*!*/;
# at 30062
#210518 10:53:07 server id 1  end_log_pos 30126 CRC32 0xd91523b6 	Query	thread_id=4	exec_time=0	error_code=0
SET @@SESSION.GTID_NEXT= '0b14d2f7-a1ca-11eb-bb1d-0800277507f6:42'/*!*/;
# at 30126
#210518 10:53:07 server id 1  end_log_pos 30178 CRC32 0x4b1d8b20 	Query	thread_id=4	exec_time=0	error_code=0
SET STATEMENT FOR @a:=1/*!*/;
# at 30178
#210518 10:53:07 server id 1  end_log_pos 30229 CRC32 0x64d94970 	Xid = 5150
COMMIT/*!*/;
# at 30230
#210518 10:53:07 server id 1  end_log_pos 30273 CRC32 0x5e5d5d5f 	Query	thread_id=4	exec_time=0	error_code=0
SET @@SESSION.GTID_NEXT= '0b14d2f7-a1ca-11eb-bb1d-0800277507f6:43'/*!*/;
# at 30273
#210518 10:53:07 server id 1  end_log_pos 30342 CRC32 0xc8dc5c5a 	Query	thread_id=4	exec_time=0	error_code=0
SET STATEMENT FOR @a:=2/*!*/;

个 binlog 记录了两个事务，第一个事务在执行 BEGIN 语句时开启，接着执行 SET STATEMENT FOR @a:=1 语句，最后通过 COMMIT 语句提交事务；第二个事务也是通过 SET STATEMENT FOR @a:=2 语句进行的操作。在每个事务执行的过程中，binlog 记录了开始时间、执行语句、线程 ID、执行时间、错误码、GTID 等信息，用于保证事务的原子性、一致性和持久性。

需要注意的是，binlog 记录的内容主要用于主从同步和数据备份等功能，与 redo log 和 undo log 不同，不包含修改前和修改后的数据，而是记录了执行的 SQL 语句或语句的元数据，以便在备份或主从同步时可以重新执行相同的操作，保持数据的一致性。

49、MySQL 中 InnoDB 和 MyISAM 的区别？

事务支持：InnoDB 支持事务和行级锁定，而 MyISAM 不支持事务和行级锁定，这意味着如果需要使用事务或者处理高并发的应用程序，通常应该使用 InnoDB 引擎
外键支持：InnoDB 引擎支持外键，而 MyISAM 不支持。外键是一种重要的数据完整性保护机制，可以确保引用表和被引用表之间的关联关系是正确的
并发性能：InnoDB 对并发性能的支持比 MyISAM 更好。InnoDB 使用行级锁定机制，可以避免锁表现象，提高多个并发事务之间的处理能力
读写性能：MyISAM 在处理大量读操作时比 InnoDB 更快，但是在处理大量写操作时则会有较大的性能损失
数据缓存：InnoDB 支持缓存数据和索引，而 MyISAM 只能缓存索引，这意味着对于大型数据集和高并发读写的应用程序，InnoDB 更适合缓存和处理数据
数据安全：InnoDB 支持 ACID，可以确保数据的安全性和完整性，MyISAM 不支持 ACID 特性，容易发生数据丢失和数据不一致的情况

数据库 MySQL

MySQL 数据库

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

达梦数据库（DM8）安装+Golang 使用上一篇

浅谈分布式（二）下一篇