Mysql底层原理

一、mysql架构

1.连接层

客户端连接器，例如java开发的JDBC；

2.服务层

连接池

管理连接、权限验证；

通过show processlist;查询连接状态。用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为8小时，超过时间后会被断开。

1
2
3
4

-- 查询连接状态
show processlist;
-- 查询等待超时时间（28800秒）
show global variables like 'wait_timeout' ;

缓存

当接受到查询请求时，会现在查询缓存中查询（key/value保存），是否执行过。key是查询的语句，value是查询的结果。没有的话，再走正常的执行流程。

但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时，缓存就会被清空。所以适用于静态表。在 MySQL8.0 后，查询缓存被废除。

解析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

做完了词法分析以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

如果我们在拼写SQL时候，少了或者写错了某个字母，就会收到“You have an error in your SQL syntax”的错误提醒。

优化器

优化器主要是在我们的数据库表中，如果存在多个多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序 。

执行器

首先，肯定是要判断权限，就是有没有权限执行这条SQL。工作中可能会对某些客户端进行权限控制。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

3.存储引擎层

在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(Table Type，即存储和操作此表的类型)。

同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

• MySQL5.5版本(mysql 版本 < 5.5版本) 以前，默认使用的存储引擎是MyISAM 。
• MySQL5.5版本(mysql 版本 >= 5.5版本) 以后，默认使用的存储引擎是InnoDB

4.文件系统层

系统文件存储层主要是负责将数据库的数据和日志存储在系统的文件中，同时完成与存储引擎的之间的打交道，是文件的物理存储层。

比如：数据文件、日志文件、pid文件、配置文件等。

数据文件

「db.opt文件」：记录这个数据库的默认使用的字符集和校验规则。

「frm文件」：存储于边相关的元数据信息，包含表结构的定义信息等，每一张表都会有一个frm文件与之对应。

「MYD文件」：MyISAM存储引擎专用的文件，存储MyISAM表的数据信息，每一张MyISAM表都有有一个.MYD文件。

「MYI文件」：也是MyISAM存储引擎专用的文件，存放MyISAM表的索引相关信息，每一张MyISAM表都有对应的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独立表空间和共享表空间。

• 独享表空间使用ibd文件来存放数据，并且每一张InnoDB表存在与之对应的.ibd文件。
• 共享表空间使用ibdata文件，所有表共同使用一个或者多个.ibdata文件。

「ibdata1文件」：系统表空间数据文件，存储表元数据、Undo日志等。

「ib_logfile0、ib_logfile1文件」：Redo log日志文件。

日志文件（错误日志，binglog和慢查询日志）

1. 错误日志：默认是开启状态，可以通过命令查看：

1

show variables like '%log_error%';

2. 二进制日志binary log：

   记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行耗时；但是不记录查询select、show等不修改数据的SQL。主要用于数据库恢复和数据库主从复制。也是大家常说的binlog日志。

1
2
3
4
5
6

-- 查看是否开启binlog日志记录。
show variables like '%log_bin%';
-- 查看参数
show variables like '%binlog%';
-- 查看日志文件
show binary logs;

如果log_bin为OFF，则需要手动修改配置文件打开日志；

1
2
3

[mysqld]
log-bin=mysql-bin
server_id=2

3. 慢查询日志：记录查询数据库超时的所有SQL，默认是10秒。

1
2
3
4

-- 查看是否开启慢查询日志记录。
show variables like '%slow_query%';
-- 查看时长
show variables like '%long_query_time%';

如果slow_query_log为OFF，则需要手动修改配置文件打开日志；

1
2

[mysqld]
slow_query_log=1

4. 通用查询日志：记录一般查询语句；

1

show variables like '%general%';

二、SQL执行流程

1.查询流程

客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回

2.更新流程

1. 取数据行: 执行器先找引擎取 ID=2 这一行（ID 是主键，引擎直接通过B+树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回)。
2. 写入undo log：获取ID=2 这一行的数据写入到对应的undo log buffer。
3. 更新数据： 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
4. 更新内存：引擎将这行新数据更新到内存buffer poll中。
5. 更新 redo log：同时将这个更新操作记录到 redo log buffer里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
6. 写入bin log：执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
7. 提交事务：执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，触发redo log刷盘，然后事务标记为提交成功。

三、核心日志文件

1.redo log

概述

redo log又称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。是存储引擎层(InnoDB)生成的日志，记录“物理级别”上的页修改操作，比如：页号xxx、偏移量xx写入了‘zzzz’数据。

• 事务的隔离性由锁机制实现。
• 而事务的原子性、一致性和持久性由事务的 Redo 日志和 Undo 日志来保证。

InnoDB存储引擎是以页为单位来管理存储空间。在真正访问数据之前需要先把磁盘上的页缓存到内存中的Buffer Pool之后才能访问。所有的变更需要先更新缓存池中的数据，然后把缓存池中的脏页以一定频率刷新入磁盘(checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的鸿沟，这样可以保证整体的性能不会下降太快。

好处、特点

好处：

• 降低了刷盘频率；
• 占用空间小，只存储空间ID、页号、偏移量以及需要更新的值；

特点：

• 日志是顺序写入的：执行事务过程中，没执行一条语句都会产生若干Redo日志，这些日志是按照产生的顺序写入磁盘的；
• 事务过程中，日志不断产生：Redo Log是存储引擎层产生的，会一直产生日志，而bing log直到事务提交后才会希尔bing log日志文件中；

Redo的组成

Redo Log可以简单分为以下两个部分：

• 重做日志的缓冲 (Redo Log Buffer)
  保存在内存中，是易失的。
  在服务器启动时就向操作系统申请了一大片称之为Redo Log Buffer的连续内存空间，翻译成中文就是Redo日志缓冲区。这片内存空间被划分成若干个连续的Redo Log Block。一个Redo Log Block占用512字节大小。

• 参数设置innodb_log_buffer_size：

Redo Log Buffer 的大小，默认为 16M ，最大值是4096M，最小值为 1M 。

1
2
3
4
5
6

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

• 重做日志文件(Redo Log File)，保存在硬盘中，是持久的。

REDO日志文件如图所示，其中的ib_logfile0和ib_logfile1即为Redo Log日志。

• innodb_log_group_home_dir ：指定 Redo Log 文件组所在的路径，默认值为./，表示在数据库的数据目录下。MySQL的默认数据目录（ /var/lib/mysql ）下默认有两个名为 ib_logfile0 和ib_logfile1 的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。此Redo日志文件位置还可以修改。
• innodb_log_files_in_group：指明Redo Log File的个数，命名方式如：ib_logfile0，iblogfile1…iblogfilen。默认2个，最大100个。

1
2
3
4
5
6
7
8

mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
-- ib_logfile0
-- ib_logfile1

• innodb_log_file_size：单个 Redo Log 文件设置大小，默认值为 48M 。最大值为512G，注意最大值指的是整个 Redo Log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。

1
2
3
4
5
6

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

InnoDB的redo log 是固定大小，即记录满了以后就从头循环写。采用循环使用的方式向Redo日志文件组里写数据的话，会导致后写入的Redo日志覆盖掉前边写的Redo日志？当然！所以InnoDB的设计者提出了checkpoint的概念。

为什么需要Redo日志

1. 缓存池可以帮助我们消除CPU和磁盘之间的鸿沟，checkPoint机制可以保证数据的最终落盘，然而由于checkpoint并不是每次变更的时候就触发的，而是master线程隔一段时间去处理的。所以最坏的情况就是事物提交后，数据刷新到缓存池后，数据库宏机，那么数据就会丢失；
2. 事物包含持久性的特性，如果一个已经提交的事物，提交后系统发生故障，那么这个事物对数据库中所做的更改也不能丢失；
3. 如果为了保证持久性，在事物提交之前把事物所有修改的页都刷新到磁盘，也会存在问题；
   • 修改量与刷新磁盘工作量严重不成比例；
   • 随机IO刷新较慢；
4. 所以采用WAL技术，优先记录redo日志，以保证持久性；

WAL技术(Write-Ahead Logging)

InnoDB引擎的事务采用了WAL技术(Write-Ahead Logging)，这种技术的思想是先写日志，再写磁盘。只有日志写入成功了才算事务提交成功，这里的日志指的是Redo Log。当发生宏机的时候数据如果没有刷新到磁盘，可以通过Redo Log来恢复。保证的事务一致性；

Redo的整体流程

1. 将原始数据从磁盘读取到内存缓存池，修改缓存池中的数据；
2. 生成Redo Log并且写入Redo Log Buffer，记录的是数据修改后的值；
3. 当提交事务时，将Redo Log Buffer中的数据刷新到Redo Log File中，对文件采用追加的写入方式；
4. 定期将内存中修改刷新到磁盘中；

Redo Log的刷盘策略

Redo Log不会直接写入磁盘，而是优先写入Redo Log Buffer，然后以一定频率写入文件。
什么时候触发redo log写文件：

• Redo Log Buffer空间不足；
• 事务提交；
• 后台master线程；
• 做checkpoint；
• 实例shutdown；
• binlog切换；

刷盘配置：

Redo Buffer Log刷盘到Redo Log File的过程可能不是真正的刷新的磁盘中去，可能只是刷新到文件系统缓存(page cache)中，真正的写入会交给系统自己决定(比如page cache足够大)。所以还是可能会出现系统宏机，数据丢失的情况。

文件系统缓存：是现代操作系统为了提高文件写入效率做的一个优化。

针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit 提交事务时，如何将 Redo Log Buffer 中的日志刷新到 Redo Log File 中。它支持三种策略：

• 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步），事务提交不会触发redo写操作，而是留给后台线程每秒一次的刷盘操作，因此实例crash将最多丢失1秒钟内的事务。
• 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）,每次事务提交都要做一次fsync，这是最安全的配置，即使宕机也不会丢失事务；
• 设置为2 ：表示每次事务提交时都只把 Redo Log Buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件,则在事务提交时只做write操作，只保证写到系统的page cache，因此实例crash不会丢失事务，但宕机则可能丢失事务；

另外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把 Redo Log Buffer 中的内容写到文件系统缓存( page cache )，然后调用刷盘操作。

也就是说，一个没有提交事务的Redo Log记录，也可能会刷盘。因为在事务执行过程Redo Log记录是会写入Redo Log Buffer 中，这些Redo Log记录会被后台线程刷盘。
除了后台线程每秒1次的轮询操作，还有一种情况，当Redo Log Buffer占用的空间即将达到innodb_log_buffer_size(这个参数默认是16M)的一半的时候，后台线程会主动刷盘。

InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务COMMIT时，必须先将该事务的所有日志都写入到redo log文件进行持久化之后，COMMIT操作才算完成。

当事务的各种SQL操作执行时，即会在缓冲区中修改数据，也会将对应的redo log写入它所属的缓存。当事务执行COMMIT时，与该事务相关的redo log缓冲必须都全部刷新到磁盘中之后COMMIT才算执行成功。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，InnoDB存储引擎会使用redo log恢复到掉宕机前的时刻，以此来保证数据的完整性。

写入Redo Log Buffer 过程

1.Mini-Transaction

MySQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction。一个所谓的mtr可以包含一组Redo日志，在进行崩溃恢复时这一组Redo日志作为一个不可分割的整体。

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条Redo日志，画个图表示它们的关系就是这样：

2.Redo 日志写入Log Buffer

向log buffer中写入Redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。当我们想往log buffer中写入Redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB的设计者特意提供了一个称之为buf_free的全局变量，该变量指明后续写入的Redo日志应该写入到log buffer中的哪个位置，如图所示:

一个 mtr 执行过程中可能产生若干条Redo日志，这些Redo日志是一个不可分割的组，所以其实并不是每生成一条Redo日志，就将其插入到log buffer中，而是每个 mtr 运行过程中产生的日志先暂时存到一个地方，当该 mtr 结束的时候，将过程中产生的一组Redo日志再全部复制到log buffer中。我们现在假设有两个名为T1、T2的事务，每个事务都包含2个 mtr ，我们给这几个 mtr 命名一下:

• 事务T1的两个 mtr 分别称为mtr_T1_1和mtr_T1_2。
• 事务T2的两个 mtr 分别称为mtr_T2_1和mtr_T2_2。

每个 mtr 都会产生一组Redo日志，用示意图来描述一下这些 mtr 产生的日志情况：

不同的事务可能是 并发 执行的，所以 T1 、 T2 之间的 mtr 可能是 交替执行 的。每当一个mtr执行完成时，伴随该mtr生成的一组Redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的，我们画个示意图(为了美观，我们把一个mtr中产生的所有的Redo日志当作一个整体来画):

有的mtr产生的Redo日志量非常大，比如mtr_t1_2产生的Redo日志占用空间比较大，占用了3个block来存储。

3. Redo Log Block的结构图

一个Redo Log Block是由日志头、日志体、日志尾组成。日志头占用12字节，日志尾占用4字节，所以一个block真正能存储的数据就是512-12-4=496字节。

• 为什么一个block设计成512字节?
  这个和磁盘的扇区有关，机械磁盘默认的扇区就是512字节，如果你要写入的数据大于512字节，那么要写入的扇区肯定不止一个，这时就要涉及到盘片的转动，找到下一个扇区，假设现在需要写入两个扇区A和B，如果扇区A写入成功，而扇区B写入失败，那么就会出现非原子性的写入，而如果每次只写入和扇区的大小一样的512字节，那么每次的写入都是原子性的。

真正的Redo日志都是存储到占用496字节大小的log block body中，图中的log block header和logblock trailer存储的是一些管理信息。我们来看看这些所谓的管理信息都有什么。

• log block header的属分别如下：
• • LOG_BLOCK_HDR_NO:log buffer是由log block组成，在内部log buffer就好似一个数组，因此LOG_BLOCK_HDR_NO用来标记这个数组中的位置。其是递增并且循环使用的，占用4个字节，但是由于第一位用来判断是否是flush bit,所以最大的值为2G。
  • LOG_BLOCK_HDR_DATA_LEN:表示block中已经使用了多少字节，初始值为12(因为log block body从第12个字节处开始）。随着往block中写入的Redo日志越来也多，本属性值也跟着增长。如果log block body已经被全部写满，那么本属性的值被设置为512。
  • LOG_BLOCK_FIRST_REC_GROUP:一条Redo日志也可以称之为一条Redo日志记录（Redo Log Record),一个 mtr 会生产多条Redo日志记录，这些Redo日志记录被称之为一个Redo日志记录组（Redo Log Recordgroup)。LOG_BLOCK_FIRST_REC_GROUP就代表该block中第一个mtr生成的Redo日志记录组的偏移量（其实也就是这个block里第一个mtr生成的第一条Redo日志的偏移量）。如果该值的大小和LOG_BLOCK_HDR_DATA_LEN相同，则表示当前log block不包含新的日志。
  • LOG_BLOCK_CHECKPOINT_NO:占用4字节，表示该log block最后被写入时的checkpoint。
• log block trailer 中属性的意思如下：
• • LOG_BLOCK_CHECKSUM:表示block的校验值，用于正确性校验（其值和LOG_BLOCK_HDR_NO相同）,我们暂时不关心它。

checkpoint

作用：

• 缩短数据库的恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用是，刷新脏页

在整个日志文件组中还有两个重要的属性，分别是write pos、checkpoint

• write pos是当前记录的位置，一边写一边后移
• checkpoint是当前要擦除的位置，也是往后推移

每次刷盘Redo Log记录到日志文件组中，write pos位置就会后移更新。每次MySQL加载日志文件组恢复数据时，会清空加载过的Redo Log记录，并把 checkpoint后移更新。write pos和checkpoint之间的还空着的部分可以用来写入新的Redo Log记录。

如果 write pos 追上 checkpoint ，表示 日志文件组 满了，这时候不能再写入新的 Redo Log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

对于innodb存储引擎而言，其实通过LSN(Log Sequence Number)来标记版本的，而LSN是8字节的数字，其单位是字节，每个页都有LSN，重做日志中也有LSN，checkpoint也有LSN。可以通过show engine innodb status来观察：

1
2
3
4
5
6
7
8
9
10

mysql> show engine innodb status;
+--------+------+------------------------------
LOG
---
Log sequence number 58063900
Log flushed up to   58063900
Pages flushed up to 58063900
Last checkpoint at  58063891
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second

• Log sequence number:表示当前redo log(in buffer)中的LSN
• Log flushed up to : 表示刷到redo log file on disk中的LSN
• Pages flushed up to :表示已经刷新到磁盘数据页上的LSN
• Last checkpoint at :上一次检查点所在的位置LSN

在innodb存储引擎中，有两种checkpoint，分别是：

• Sharp checkpoint：发生在关闭数据库时，将所有脏页刷回磁盘。
• Fuzzy checkpoint：进行部分脏页的刷新，有效循环利用Redo日志。
  1、Master Thread Checkpoint；
  2、FLUSH_LRU_LIST Checkpoint；
  3、Async/Sync Flush Checkpoint；
  4、Dirty Page too much Checkpoint

fuzzy checkpoint发生的4个条件

1、master thread checkpoint

　　差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，不会阻塞用户查询。

• 周期性，读取flush list，找到脏页，写入磁盘
• 写入的量比较小
• 异步，不影响业务

1
2
3
4
5
6
7
8

mysql> show variables like '%io_cap%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.01 sec)

• 通过capacity能力告知进行刷盘控制

  通过innodb的io能力告知控制对flush list刷脏页数量，io_capacity越高，每次刷盘写入脏页数越多；
  如果脏页数量过多，刷盘速度很慢，在io能力允许的情况下，调高innodb_io_capacity值，让多刷脏页。

2、flush_lru_list checkpoint

　　MySQL会保证，保证里面有多少可用的空闲页，在innodb 1.1.x版本之前，需要检查在用户查询线程中是否有足够的可用空间(差不多100个空闲页)，显然这会阻塞用户线程，如果没有100个可用空闲页，那么innodbhi将lru列表尾端的页移除，如果这些页中有脏页，那么需要进行checkpoint。Innodb 1.2(5.6)之后把他单独放到一个线程page cleaner中进行，用户可以通过参数innodb_lru_scan_depth控制lru列表中可用页的数量，默认是1024。

　　读取lru list，找到脏页，写入磁盘。

1
2
3
4
5
6
7

mysql> show variables like '%lru%depth';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+
1 row in set (0.01 sec)

　　此情况下触发，默认扫描1024个lru冷端数据页，将脏页写入磁盘(有10个就刷10，有100个就刷100个……)

3、async/sync flush checkpoint

　　log file快满了，会批量的触发数据页回写，这个事件触发的时候又分为异步和同步，不可被覆盖的redolog占log file的比值：75%—>异步、90%—>同步。

　　当这两个事件中的任何一个发生的时候，都会记录到errlog中，一旦errlog出现这种日志提示，一定需要加大logfile。

　　Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始——也就是MySQL 5.6版本，这部分的刷新操作同样放入到了单独的Page Cleaner Thread中，故不会阻塞用户查询线程。

4、dirty page too much checkpoint

　　很明显，脏页太多检查点，为了保证buffer pool的空间可用性的一个检查点。

　　1、脏页监控，关注点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> show global status like 'Innodb_buffer_pool_pages%t%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_data  | 2964  |
| Innodb_buffer_pool_pages_dirty | 0     |
| Innodb_buffer_pool_pages_total | 8191  |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like '%wait_free';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0     |
+------------------------------+-------+
1 row in set (0.00 sec)

　　　　1、Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total：表示脏页在buffer 的占比

　　　　2、Innodb_buffer_pool_wait_free：如果>0，说明出现性能负载，buffer pool中没有干净可用块

　　2、脏页控制参数

1
2
3
4
5
6
7
8

mysql> show variables like '%dirty%pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000  |
+--------------------------------+-----------+
2 rows in set (0.01 sec)

　　　　1、默认是脏页占比75%的时候，就会触发刷盘，将脏页写入磁盘，腾出内存空间。建议不调，调太低的话，io压力就会很大，但是崩溃恢复就很快；

　　　　2、lwm：low water mark低水位线，刷盘到该低水位线就不写脏页了，0也就是不限制。

2.undo log

什么是Undo Log？

Undo:意为撤销或取消，以撤销操作为目的，返回某个状态的操作。

Undo Log:数据库事务开始之前，会将要修改的记录放到Undo日志里，当事务回滚时或者数据库崩溃时，可以利用UndoLog撤销未提交事务对数据库产生的影响。

Undo Log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个Undo Log

如何理解Undo Log

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

• 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。

• 情况二：DBA可以在事务执行过程中手动输入ROLLBACK语句结束当前事务的执行。 以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚。

每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、UPDATE)，都需要”留一手”——把回滚时所需的东西记下来。比如:

• 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。(对于每个INSERT, InnoDB存储引擎会完成一个DELETE)

• 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。(对于每个DELETE,InnoDB存储引擎会执行一个INSERT)

• 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。(对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去)

MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志(即Undo Log)。注意，由于查询操作(SELECT）并不会修改任何用户记录，所以在杳询操作行时，并不需要记录相应的Undo日志

此外，Undo Log会产生Redo Log，也就是Undo Log的产生会伴随着Redo Log的产生，这是因为Undo Log也需要持久性的保护。

Undo Log的功能

• 提供数据回滚-原子性
  当事务回滚时或者数据库崩溃时，可以利用Undo Log来进行数据回滚。

• 多个行版本控制(MVCC)-隔离性
  即在InnoDB存储引擎中MVCC的实现是通过Undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过Undo读取之前的行版本信息，以此实现非锁定读取。

  Undo Log的存储结构

1. 回滚段与undo页

InnoDB对Undo Log的管理采用段的方式，也就是回滚段（rollback segment）。每个回滚段记录了1024个Undo Log segment，而在每个Undo Log segment段中进行Undo页的申请。

在InnoDB1.1版本之前（不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为1024。虽然对绝大多数的应用来说都已经够用。

从1.1版本开始InnoDB支持最大128个rollback segment，故其支持同时在线的事务限制提高到了128*1024。

• slot 0 ，预留给系统表空间；
• slot 1- 32，预留给临时表空间，每次数据库重启的时候，都会重建临时表空间；
• slot33-127，如果有独立表空间，则预留给UNDO独立表空间；如果没有，则预留给系统表空间；
• 回滚段中除去32个提供给临时表事务使用，剩下的 128-32=96个回滚段，可执行 96*1024 个并发事务操作，每个事务占用一个 undo segment slot，注意，如果事务中有临时表事务，还会在临时表空间中的 undo segment slot 再占用一个 undo segment slot，即占用2个undo segment slot。如果错误日志中有：Cannot find a free slot for an undo log。则说明并发的事务太多了，需要考虑下是否要分流业务。

回滚段（rollback segment ）采用 轮询调度的方式来分配使用，如果设置了独立表空间，那么就不会使用系统表空间回滚段中undo segment，而是使用独立表空间的，同时，如果回滚段正在 Truncate操作，则不分配。

1
2
3
4
5
6

mysql> show variables like 'innodb_undo_logs';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_undo_logs | 128   |
+------------------+-------+

虽然InnoDB1.1版本支持了128个rollback segment，但是这些rollback segment都存储于共享表空间ibdata中。从lnnoDB1.2版本开始，可通过参数对rollback segment做进一步的设置。这些参数包括:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> show variables like '%innodb_undo_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_truncate | OFF   |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 0     |
+--------------------------+-------+
4 rows in set (0.00 sec)

mysql> show variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
| innodb_undo_log_truncate             | OFF   |
+--------------------------------------+-------+
2 rows in set (0.01 sec)

mysql> show variables like '%innodb_max_undo_log_size%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+
1 row in set (0.00 sec)

• innodb_undo_directory:设置rollback segment文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为“./”，表示当前InnoDB存储引擎的目录。

• innodb_undo_logs:设置rollback segment的个数，默认值为128。在InnoDB1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments。

• innodb_undo_tablespaces:设置构成rollback segment文件的数量，这样rollback segment可以较为平均地分布在多个文件中。默认值是0，默认记录到ibdata中。在install db初始化后，就再也不能被改动(否则可能会导致innodb无法初始化)。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件，该文件就代表rollback segment文件，每个文件默认10M。

• innodb_undo_log_truncate：设置是否开启truncate功能(在线回收、收缩undo log日志文件)。

• innodb_purge_rseg_truncate_frequency：设置truncate频率，默认128。undo表空间一般不能直接truncate，需要在所有回滚段释放完后，才能 truncate，purge system没128次释放一次回滚段。调小可以加快频率。

• innodb_max_undo_log_size：设置truncate阀值(默认1G)，超过则会触发truncate，空间缩小到10M。

2. 回滚段与事务

1. 每个事务只会使用一个回滚段（rollback segment），一个回滚段在同一时刻可能会服务于多个事务。

2. 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。

3. 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用。

4. 回滚段存在于Undo表空间中，在数据库中可以存在多个Undo表空间，但同一时刻只能使用一个Undo表空间。

5. 当事务提交时，InnoDB存储引擎会做以下两件事情：
   1.将Undo Log放入列表中，以供之后的purge线程(清洗、清除)操作
   2.判断Undo Log所在的页是否可以重用(低于3/4可以重用)，若可以分配给下个事务使用

3. 回滚段中的数据分类

• 未提交的回滚数据(uncommitted undo information)：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖。

• 已经提交但未过期的回滚数据(committed undo information)：该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响。

• 事务已经提交并过期的数据(expired undo information)：事务已经提交，而且数据保存时间已经超过undo retention参数指定的时间，属于已经过期的数据。当回滚段满了之后，会优先覆盖”事务已经提交并过期的数据”。

4. Undo页的重用

当我们开启一个事务需要写Undo log的时候，就得先去Undo Log segment中去找到一个空闲的位置，当有空位的时候，就去申请Undo页，在这个申请到的Undo页中进行Undo Log的写入。我们知道MySQL默认一页的大小是16k。

为每一个事务分配一个页，是非常浪费的(除非你的事务非常长)，假设你的应用的TPS(每秒处理的事务数目)为1000，那么1s就需要1000个页，大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MySQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而且很多空间都是浪费的。

于是Undo页就被设计的可以重用了，当事务提交时，并不会立刻删除Undo页。因为重用，所以这个Undo页可能混杂着其他事务的Undo Log。Undo Log在commit后，会被放到一个链表中，然后判断Undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的Undo页可以被重用，那么它就不会被回收，其他事务的Undo Log可以记录在当前Undo页的后面。由于Undo Log是离散的，所以清理对应的磁盘空间时，效率不高。

5. 存储结构

undo log主要分为两种：

• insert undo log ：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log ：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除；delete时只是对数据打上删除标识，实际上没有删除，需要被purge线程统一清除；

四、ChangeBuffer

1、ChangeBuffer概述

ChangeBuffer是InnoDB缓存区的一种特殊的数据结构，当用户执行SQL对非唯一索引进行更改时，如果索引对应的数据页不在缓存中时，InnoDB不会直接加载磁盘数据到缓存数据页中，而是缓存对这些更改操作。这些更改操作可能由插入、更新或删除操作(DML)触发。缓存区的更改操作会在磁盘数据被其它读操作加载到缓存中时合并到对应的缓存数据页中。

1. ChangeBuffer用于存储SQL变更操作，比如Insert/Update/Delete等SQL语句；
2. ChangeBuffer中的每个变更操作都有其对应的数据页，并且该数据页未加载到缓存中；
3. 当ChangeBufferd中变更操作对应的数据页加载到缓存中后，InnoDB会把变更操作Merge到数据页上；
4. InnoDB会定期加载ChangeBuffer中操作对应的数据页到缓存中，并Merge变更操作；

2、ChangeBuffer的作用

我们知道InnoDB推荐使用自增主键，插入时主键值时递增的，可以顺序访问。与聚簇索引不同，二级索引通常是不是唯一的，并且以相对随机的顺序插入。类似的，二级索引的更新和删除经常也会影响索引树中不相邻的二级索引数据页。

对于二级索引数据变更引起的随机访问，如果每次都进行磁盘IO显然会影响数据库的性能。因此InnoDB不会立即执行数据页不在缓存中的二级索引的变更操作，而是先将变更操作缓存起来，在某个时刻再将某一个数据页上面的所有变更操作合并到该数据页上，通过变更操作缓存(ChangeBuffer)可合并同一个数据页上的大量随机访问I/O。

3、ChangeBuffer工作流程

1）、变更操作什么时候放入ChangeBuffer

并不是数据库中的所有操作都会进入ChangeBuffer，满足以下条件的数据库语句，在执行阶段不会修改数据页，而是会进入ChangeBuffer，

1. SQL会修改数据库中的数据；
2. SQL语句不涉及唯一键的校验；
3. SQL语句不需要返回变更后的数据；
4. 涉及的数据页不在缓存中；

2）、ChangeBuffer合并到原数据页

我们知道，ChangeBuffer中缓存了变更操作，这些操作最终需要合并到数据库的数据页，合并过程称为Merge，那么在什么场景下会触发ChangeBuffer的Merge操作呢？

1. 访问变更操作对应的数据页；
2. InnoDB后台定期Merge；
3. 数据库BufferPool空间不足；
4. 数据库正常关闭时；
5. RedoLog写满时；

4、为什么ChangeBuffer只缓存非唯一索引数据

ChangeBuffer仅仅适用于变更的数据未为非唯一索引的情况，如果变更操作修改的数据为唯一索引或者主键数据，那么InnoDB无法把变更操作缓存到ChangeBuffer，这是为什么呢？

1）、非唯一索引更新

假设我们使用SQL语句update user_info set age=6 where id=1修改ID=1的用户的年龄为6（age非唯一索引），该操作会同时修改年龄索引以及行数据中的年龄，更新步骤如下：

• 如果需要更改的年龄索引页和行数据页在缓存中，直接更新缓存中的数据，并把数据页标记为脏页；
• 如果需要更改的年龄索引页和行数据页不在缓存中，直接把SQL语句update user_info set age=6 where id=1存储到ChangeBuffer；

2）、唯一索引更新

假设我们使用SQL语句update user_info set id=2 where id=1修改ID=1的用户的ID为2，该操作会同时修改聚簇索引和行数据，更新步骤如下：

• 如果需要更改的聚簇索引和行数据页在缓存中，直接更新缓存中的数据，并把数据页标记为脏页；
• 如果需要更改的聚簇索引页和行数据页不在缓存中，需要把对应的数据页加载到缓存中，判断修改之后ID是不是符合唯一键约束，然后修改缓存中的数据；

可以看到，由于唯一索引需要进行唯一性校验，所以对唯一索引进行更新时必须将对应的数据页加载到缓存中进行校验，从而导致ChangeBuffer失效。

3）、普通索引还是唯一索引

通过以上分析，我们知道唯一索引无法使用ChangeBuffer，那么我们实际使用过程中应该使用普通索引还是唯一索引呢？

从等值查询性能角度来看：

• 普通索引在查找到第一个满足条件的数据之后，需要继续向后查找满足条件的数据；

• 唯一索引在查找到第一个满足条件的数据之后，不需要再次向后查找，因为索引具有唯一性；

  二者之间只相差一条记录，这个一条记录会带来多大的性能差距呢？答案是，微乎其微。因为 InnoDB引擎是以页为单位读取数据的，读取一条数据时，往往会将临近的数据也读到内存，所以多向后查询几条数据带来的性能差别微乎其微。

从索引修改角度来看：

由于唯一索引无法使用ChangeBuffer，对索引的修改会引起大量的磁盘IO，影响数据库性能。

综上可知，如果不是业务中要求数据库对某个字段做唯一性检查，我们最好使用普通索引而不是唯一索引。

5、ChangeBuffer适用场景

什么情况下ChangeBuffer会有较大的性能提升呢？

1. 数据库大部分索引是非唯一索引；
2. 业务是写多读少，或者不是写后立刻读取；

不适合使用ChangeBuffer的场景与之对应：

先说什么时候不适合，如上文分析，当：

1. 数据库都是唯一索引；
2. 写入数据后，会立刻读取；

6、ChangeBuffer相关参数

1
2
3
4
5
6
7
8

mysql> show variables like '%change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.00 sec)

• innodb_change_buffer_max_size: 配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。
  写多读少的业务，才需要调大这个值。
• innodb_change_buffering: 配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等。

五、索引下推

索引下推（index condition pushdown ）简称ICP，通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数 和 MySQL 服务层访问存储引擎的次数。

索引下推的前提

• 当需要访问整个表行时，ICP 用于 range、 ref、 eq_ref 和 ref_or_null。
• ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。
• 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。
• 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。
• 引用子查询的条件不能下推。
• 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。
• 触发条件不能下推。
• 不能将条件下推到包含对系统变量的引用的派生表。（MySQL 8.0.30 及更高版本)

ICP模式开关

1
2
3
4
5
6

-- 查看模式
mysql> show variables like '%optimizer_switch%';
+------------------+-----------------------+
| Variable_name    | Value |-------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 将 ICP 关闭
SET optimizer_switch = 'index_condition_pushdown=off';
-- 清空 status 状态
flush status;
-- 查看 handler 状态
mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 13    |  -- 回表查询次数
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             |

六、MRR（Multi-Range Read Optimization）

简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

MRR开关、参数

1
2
3
4
5
6
7

-- 总开关
mysql > set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)
-- 告诉优化器，需要考虑MRR使用成本
--    如果为off，则优化器会通通使用MRR（即使只有一条结果的情况）
mysql > set optimizer_switch='mrr_cost_based=on';
Query OK, 0 rows affected (0.06 sec)

• mrr: on/off 打开后才能使用
• mrr_cost_based: on/off 用来告诉优化器，要不要基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR。

1
2
3
4
5
6
7

mysql> show variables like '%read_rnd_buffer_size%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)

• read_rnd_buffer_size：是用来设置用于给 rowid 排序的内存的大小。这是为每个客户端分配的缓冲区，因此不应将全局变量设置为大值。

为什么要把随机读转化为顺序读？

​ 当一个表很大并且没有缓存在buffer pool中时，由于二级索引和主键的排列顺序一般情况下是不一样的，在二级索引上使用范围扫描回表读取行数据时会导致产生大量的随机I/O。
如果查询的数据分布在各个零散、顺利不一致的页上，例如第一条在第一页，第二条在第三页，第三条有出现在第一页。那么磁盘就需要先加载第一页，第三页，最后又要重新加载第一页。
然而磁盘查找数据是相对较慢的，磁盘旋转，磁头不停跳动顺序读取这些离散数据。

10,000 RPM（Revolutions Per Minute，即转每分） 的机械硬盘，每秒大概可以执行 167 次磁盘读取，所以在极端情况下，MySQL 每秒只能给你返回 167 条数据，这还不算上 CPU 排队时间。

怎么转化的？

​ 通过MRR优化，MySQL会通过索引扫描收集相关行数据的主键，将主键值的集合存储到read_rnd_buffer中，然后在buffer中对主键进行排序，最后利用排好序的主键再回表查询。

为什么顺序读就能提升读取性能？

​ 如果缓冲池不够大的话，频繁的离散读还会导致缓存中的页频繁的被替换出缓冲池，然后又不断的被读入缓冲池，若按照主键顺序进行访问的话，可以减少数据页的读取，降低数据页被频繁替换出入缓冲池的情况。

六、Read View

ReadView是事务开启时，当前所有活跃事务（还未提交的事务）的一个集合，ReadView数据结构决定了不同事务隔离级别下，数据的可见性。

MySQL官方文档说明：

Read view lists the trx ids of those transactions for which a consistent read should not see the modifications to the database.

An internal snapshot used by the MVCC mechanism of InnoDB. Certain transactions, depending on their isolation level, see the data values as they were at the time the transaction (or in some cases, the statement) started. Isolation levels that use a read view are REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

Read view 列出了那些事务的trx id，对于这些事务，一致读取不应该看到对数据库的修改。

Read view InnoDB的MVCC机制使用的内部快照。某些事务，根据其隔离级别，可以看到事务（或在某些情况下，语句）启动时的数据值。使用读取视图的隔离级别为可重复读、读已提交和读未提交。

ReadView的数据结构

• up_limit_id：最先开始的事务，该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务

• low_limit_id：最后开始的事务，该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号

• m_ids：当前活跃事务ID列表，所有事务链表中事务的id集合

  注：ID越小，事务开始的越早；ID越大，事务开始的越晚

事务链

当我们用select读取数据时，这一时刻的数据会有很多个版本（例如上图有四个版本），但我们并不知道读取哪个版本，这时就靠readview来对我们进行读取版本的限制，通过readview我们才知道自己能够读取哪个版本。

不同隔离级别处理

• read uncommitted隔离级别事务：直接读取记录的最新版本；
• serializable隔离级别事务：使用加锁的方式来访问记录；
• RC和RR隔离级别事务：需要用到版本链概念；

readview如何判断版本链中的哪个版本可用呢？（重点！）

从上到下分别为（1）（2）（3）（4），依次进行解释，trx_id表示要读取的事务id

• （1）如果要读取的事务id等于进行读操作的事务id，说明是我读取我自己创建的记录，那么为什么不可以呢。
• （2）如果要读取的事务id小于最小的活跃事务id，说明要读取的事务已经提交，那么可以读取。
• （3）max_trx_id表示生成readview时，分配给下一个事务的id，如果要读取的事务id大于max_trx_id，说明该id已经不在该readview版本链中了，故无法访问。
• （4）m_ids中存储的是活跃事务的id，如果要读取的事务id不在活跃列表，那么就可以读取，反之不行。

Mysql面试题

1.MyIsAm和InnoDB的区别

InnoDB有三大特性，分别是事务、外键、行级锁，这些都是MyIsAm不支持的，

另外InnoDB是聚簇索引，MyIAm是非聚簇索引，

InnoDB不支持全文索引，MyIAm支持

InnoDB支持自增和MVCC模式的读写，MyIAm不支持

MyIsAM的访问速度一般InnoDB快，差异在于innodb的mvcc、行锁会比较消耗性能，还可能有回表的过程（先去辅助索引中查询数据，找到数据对应的key之后，再通过key回表到聚簇索引树查找数据）

2.mysql事务特性

原子性：一个事务内的操作统一成功或失败

一致性：事务前后的数据总量不变

隔离性：事务与事务之间相互不影响

持久性：事务一旦提交发生的改变不可逆

3.事务靠什么保证

原子性：由undolog日志保证，他记录了需要回滚的日志信息，回滚时撤销已执行的sql

一致性：由其他三大特性共同保证，是事务的目的

隔离性：由MVCC保证

持久性：由redolog日志和内存保证，mysql修改数据时内存和redolog会记录操作，宕机时可恢复

4.事务的隔离级别

在高并发情况下，并发事务会产生脏读、不可重复读、幻读问题，这时需要用隔离级别来控制

• 读未提交： 允许一个事务读取另一个事务已提交的数据，可能出现不可重复读，幻读。

• 读提交： 只允许事务读取另一个事务没有提交的数据可能出现不可重复读，幻读。

• 取数据行: 执行器先找引擎取 ID=2 这一行（ID 是主键，引擎直接通过B+树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。)

  写入undolog：获取ID=2 这一行的数据写入到对应的undolog

  更新数据： 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

  更新内存：引擎将这行新数据更新到内存中，

  更新 redo log：同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

  写入binlog：执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

  提交事务：执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，触发redolog刷盘，然后事务标记为提交成功。

• 可重复读： 确保同一字段多次读取结果一致，可能出现欢幻读。

• 可串行化： 所有事务逐次执行，没有并发问日

Inno DB 默认隔离级别为可重复读级别，分为快照度和当前读，并且通过间隙锁解决了幻读问题。

5.什么是快照读和当前读

*快照读读取的是当前数据的可见版本，可能是会过期数据，不加锁的select就是快照都

*当前读读取的是数据的最新版本，并且当前读返回的记录都会上锁，保证其他事务不会并发修改这条记录。如update、insert、delete、select for undate（排他锁）、select lockin share mode（共享锁） 都是当前读

6.MVCC是什么

MVCC（Mutil Version Concurrency Control 多版本并发控制），为每次事务生成一个新版本数据，每个事务都由自己的版本，从而不加锁就决绝读写冲突，这种读叫做快照读。只在读已提交和可重复读中生效。

• 快照读：读取历史版本的记录

• 当前读：读取数据库的最新版本数据，是一种悲观锁操作，会对当前读取的数据进行加锁

  • select lock in share mode（共享锁）
  • select for update（排他锁）Ty
  • update（排他锁）
  • insert（排他锁）
  • delete（排他锁）

快照读实现原理由五个东西保证，他们是：

• [undo log](#2.undo log)日志：记录了数据历史版本

• [read view](#六、Read View)：事务进行快照读时动态生成产生的视图，记录了当前系统中活跃的事务id，控制哪个历史版本对当前事务可见

• 隐藏字段DB_TRX_ID： Transaction ID事物开始的时候被分配的版本号 (6字节)，最近修改记录的事务ID

• 隐藏字段DB_ROLL_PTR：Roll Pointer回滚指针( 7字节)，配合undo log指向数据的上一个版本

• 隐藏字段DB_ROW_ID：6字节，隐藏主键，如果数据表没有主键，那么innoDB会自动生成一个6字节的row_id

7.MySQL有哪些索引

主键索引：一张表只能有一个主键索引，主键索引列不能有空值和重复值

唯一索引：唯一索引不能有相同值，但允许为空

普通索引：允许出现重复值

组合索引：对多个字段建立一个联合索引，减少索引开销，遵循最左匹配原则

全文索引：myisam引擎支持，通过建立倒排索引提升检索效率，广泛用于搜索引擎

8.聚簇索引和非聚簇索引的区别

• 聚簇索引：聚簇索引的叶子节点存放的是主键值和数据行；辅助索引（在聚簇索引上创建的其它索引）的叶子节点存放的是主键值或指向数据行的指针。

优点：根据索引可以直接获取值，所以他获取数据更快；对于主键的排序查找和范围查找效率更高；

缺点：如果主键值很大的话，辅助索引也会变得很大；如果用uuid作为主键，数据存储会很稀疏；修改主键或乱序插入会让数据行移动导致页分裂；所以一般我们定义主键时尽量让主键值小，并且定义为自增和不可修改。

• 非聚簇索引（辅助索引）：叶子节点存放的是数据行地址，先根据索引找到数据地址，再根据地址去找数据

他们都是b+数结构

9.MySQL如何做慢SQL优化

可以查看执行计划分析数据的扫描类型、索引是否生效，常见的慢查询优化有：

• （1）尽量减少select的数据列，尽量使用覆盖索引

• （2）order by查找时使用索引进行排序，否则的话需要进行回表

• （3）group by查询时，同样要用索引，避免使用到临时表

• （4）分页查询时，如果limit 后面的数字太大，可以使用子查询查出主键，再limit主键后n条数据就能走覆盖索引

• （5） 使用复杂查询时，使用关联查询来代替子查询，并且最好使用内连接

• （6）使用count函数时直接使用count的话count(*)的效率最高，count(*)或count(唯一索引)或count(数字):表中总记录数，count(字段)不会统计null

• （7) 在写update语句时，where条件要使用索引，否则会锁会从行锁升级为表锁

• （8）表中数据是否太大，是不是要分库分表

10.为什么要用内连接而不用外连接？

用外连接的话连接顺序是固定死的，比如left join，他必须先对左表进行全表扫描，然后一条条到右表去匹配；而内连接的话mysql会自己根据查询优化器去判断用哪个表做驱动。

子查询的话同样也会对驱动表进行全表扫描，所以尽量用小表做驱动表。

11.MySQL整个查询的过程

（1）客户端向 MySQL 服务器发送一条查询请求
（2）服务器首先检查查询缓存，如果命中缓存，则返回存储在缓存中的结果。否则进入下一阶段
（3）服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
（4）MySQL 根据执行计划，调用存储引擎的 API 来执行查询
（5）将结果返回给客户端，同时缓存查询结果
注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了

12.执行计划中有哪些字段？

我们想看一个sql的执行计划使用的语句是explain+SQL，表中的字段包括：

1. id：select查询的优先级，id越大优先级越高，子查询的id一般会更大

2. select_type：查询的类型,是普通查询还是联合查询还是子查询，

   常见类型有：

   • SIMPLE（不包含子查询和UNION）

   • PRIMARY（标记复杂查询中最外层的查询）

   • UNION（标记primary之后子查询)

   • UNION RESULT（为合并结果)

     

     • SUBQUERY（包含在select中的子查询；不在from的子句中）

     

     • DERIVED（包含再form的子查询中，mysql会将查询结果放入一个临时表中，此临时表也叫衍生表）

       

3. table：对应行的数据是数哪张表的

4. partitions：查询将匹配记录的分区。对于非分区表，该值为NULL

5. type:扫描类型，效率从底到高为

   ALL（全表扫描)
   index(全索引扫描，我们的需要的数据在索引中可以获取)
   range(使用索引进行范围查找)
   ref(使用非唯一索引列进行了关联查询)
   eq_ref (使用唯一索引进行关联查询)
   const(使用唯一索引查询一行数据)
   system(表中只有一行数据)

6. possible_keys（可能的）：当前查询语句可能用到的索引，可能为null(如果用了索引但是为null有可能是表数据太少innodb认为全表扫描更快)

7. key：实际使用到的索引

8. key_len：显示mysql在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大是，mysql会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1字节去记录。

   ken_len计算规则：

   • 字符串：
     char(n)：n个数字或则字母占n个字节，汉子占3n个字节
     varchar(n)：n个数字或则字母占n个字节，汉子占3n+2个字节；+2个字节用于存储字符串长度
   • 数字类型：tinyint：1字节 smalint：2字节 int：4字节 bigint：8字节
   • 时间类型：date：3字节 timestamp：4字节 datetime：8字节

9. ref（编号）：显示key列记录的索引中，表查询值时使用到的列或常量。常见的有const、字段名

10. rows：找到了多少行数据

11. filtered：它显示的是针对表里符合某个条件（where子句或联结条件）的记录数所做的一个悲观估算的百分比

12. Extra（额外的）:mysql如何查询额外信息，常见的有：

    • Using index表示MySQL将使用覆盖索引，以避免回表查询。不要把覆盖索引和index访问类型混淆了；
    • Using where表示MySQL服务器将在存储引擎检索行后再进行过滤；
    • Using temporary表示MySQL在对查询结果排序时会使用一个临时表；
    • Using filesort表示MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL有两种文件排序算法，两种都可以在内存或磁盘上完成。但explain不会告诉你MySQL使用的是哪种，也不会告诉你排序是在内存还是磁盘上完成；
    • Range checked for each record（index map：N）表示没有好用的索引，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的；
    • Using union表示MySQL运用了索引合并策略，索引合并策略有时是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕。

13.哪些情况索引会失效

（1）where条件中有or，除非所有查询条件都有索引，否则失效
（2）like查询用%开头，索引失效
（3）索引列参与计算，索引失效
（4）违背最左匹配原则，索引失效
（5）索引字段发生类型转换，索引失效
（6）mysql觉得全表扫描更快时（数据少），索引失效

14.B和B+数的区别，为什么使用B+数

二叉树：索引字段有序，极端情况会变成链表形式；深度过大导致I/O读写频繁，进而导致查询效率地下；

AVL树（自平衡二叉查找树、高度平衡树）：每个结点的左右子树高度差（平衡因子）绝对值最多为1；树的高度不可控

B数：控制了树的高度，但是索引值和data都分布在每个具体的节点当中，若要进行范围查询，要进行多次回溯，IO开销大

B+树：非叶子节点只存储索引值，叶子节点再存储索引+具体数据，从小到大用链表连接在一起，范围查询可直接遍历不需要回溯7

15.MySQL有哪些锁

基于粒度：

• 表级锁：对整张表加锁，粒度大并发小
• 行级锁：对行加锁，粒度小并发大
• 间隙锁：间隙锁，锁住表的一个区间，间隙锁之间不会冲突只在可重复读下才生效，解决了幻读

基于属性：

• 共享锁：又称读锁，一个事务为表加了读锁，其它事务只能加读锁，不能加写锁
• 排他锁：又称写锁，一个事务加写锁之后，其他事务不能再加任何锁，避免脏读问题

16.Mysql内连接、左连接、右连接的区别

内连接取量表交集部分，左连接取左表全部右表匹部分，右连接取右表全部坐表匹部分

17.sql执行顺序

1、标准的查询语句关键字顺序不能颠倒

1

**SELECT** ... **FROM** ... **WHERE** ... **GROUP** **BY** ... **HAVING** ... **ORDER** **BY** ...

2、select语句的执行顺序，从上到下，从左到右依次执行

3、具体规则：

1

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

4、实际执行顺序：

1
2
3
4
5
6
7

SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7

18.如何设计数据库？

（1）抽取实体，如用户信息，商品信息，评论

（2）分析其中属性，如用户信息：姓名、性别…

（3）分析表与表之间的关联关系

然后可以参考三大范式进行设计，设计主键时，主键要尽量小并且定义为自增和不可修改。

19.where和having的区别？

where是约束声明，having是过滤声明，where早于having执行，并且where不可以使用聚合函数，having可以

20.三大范式

1. 第一范式：每个列都不可以再拆分。

2. 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

3. 第三范式：在第二范式的基础上，不存在传递依赖，非主键列只依赖于主键，不依赖于其他非主键。

21.char和varchar的区别

char是不可变的，最大长度为255，varchar是可变的字符串，最大长度为2^16

22.InnoDB 什么情况下会产生死锁

事务1已经获取数据A的写锁，想要去获取数据B的写锁，然后事务2获取了B的写锁，想要去获取A的写锁，相互等待形成死锁。
mysql解决死锁的机制有两个：1.等待， 直到超时 2.发起死锁检测，主动回滚一条事务
死锁检测的原理是构建一个以事务为顶点、 锁为边的有向图， 判断有向图是否存在环， 存在即有死锁。
我们平时尽量减少事务操作的资源和隔离级别

23.MySQL 删除自增 id，随后重启 MySQL 服务，再插入数据，自增 id 会从几开始？

1. innodb 引擎：
   MySQL8.0前，下次自增会取表中最大 id + 1。原理是最大id会记录在内存中，重启之后会重新读取表中最大的id
   MySQL8.0后，仍从删除数据 id 后算起。原理是它将最大id记录在redolog里了

2. myisam：
   自增的 id 都从删除数据 id 后算起。原理是它将最大id记录到数据文件里了

24.MySQL插入百万级的数据如何优化？

1. 一次sql插入多条数据，可以减少写redolog日志和binlog日志的io次数（sql是有长度限制的，但可以调整）
2. 保证数据按照索引进行有序插入
3. 可以分表后多线程插入

25.什么是覆盖索引？

覆盖索引：

• 在某个查询里面，索引K已经“覆盖了”我们的查询需求（查询的字段就是索引），称为覆盖索引。

• 覆盖索引可以减少树的检索次数，显著提升查询性能，所以覆盖索引是一个常用的性能优化手段。

26.场景索引失效的场景

• 通过索引扫描的行数超过全表的20%-30%时，引擎会认为走全表扫描更有效。

• 使用联合索引时没有遵循最左原则。

• where后面出现 or 条件 ，且没有建立单列索引会导致失效。

• 对索引使用了函数计算。

• 统计信息不真实(严重不真实)，导致执行计划错误。

• 访问小表时，更倾向于全表扫描。

• Where条件中对索引列使用左模糊或者全模糊查询。