system var mysql_mysql性能优化（一）

1.2 查询缓存

mysql内部自带一个缓存模块。mysql的缓存默认情况下是关闭的

SQL 语句必须一模一样；第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。所以缓存这一块，我们还是交给 ORM 框架(比如 MyBatis 默认开启了一级缓存)，或者独立的缓存服务，比如 Redis 来处理更合适。在 MySQL 8.0 中，查询缓存已经被移除了。

1.3. 语法解析和预处理

为什么我的一条 SQL 语句能够被识别呢？假如我随便执行一个字符串 penyuyan，服务器报了一个 1064 的错：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the

right syntax to use near 'penyuyan' at line 1

它是怎么知道我输入的内容是错误的？这个就是 MySQL 的解析器和 预处理模块。这一步主要做的事情是对语句基于 SQL 语法进行词法和语法分析和语义的解析。

词法和语法分析：词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。然后就是进行语法分析，语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树。

预处理器;解析 SQL 的环节里面有个预处理器,它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树

1.4. 查询优化与查询执行计划

得到解析树后，就进入了查询优化器模块了，查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里面使用的是基于开销的优化器，那种执行计划开销最小，就用哪种。可以使用这个命令查看查询的开销：

show status like 'Last_query_cost';

如果我们想知道优化器是怎么工作的，在优化器工作时生成了多少种执行计划，每个执行计划的开销是多少的话，我们可以启动优化器追踪来进行观察(在默认下是关闭的，因为追踪很消耗系统性能)

接着我们执行一个 SQL 语句，优化器会生成执行计划：

上面语句运行完成后优化器分析的过程已经记录到系统表里面了，我们通过下面语句查询：

查询的结果是一个JSON 类型的数据，主要分成三个部分，准备阶段、优化阶段和执行阶段。expanded_query 是优化后的 SQL 语句。considered_execution_plans 里面列出了所有的执行计划。分析完记得关掉它：

set optimizer_trace="enabled=off";

优化完成之后，我们会得到一个查询执行计划，查询执行计划是一个数据结构，但是，我们拿到的执行计划不一定是最优的执行计划，因为数据库也有可以覆盖不了所有的执行计划。所以说我们怎么查看MYSQL的执行计划，在表查询中语句用了哪些索引，我们可以在SQL的查询语句前面加上EXPLAIN来查看执行计划信息

执行计划生成后，执行引擎会拿着执行计划去操作存储引擎的API完成操作。

2.一条更新SQL是如何执行的

前面说完了查询流程，现在我们就说下更新、插入、删除流程。在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery()和 doUpdate()的方法，没有 doDelete()和 doInsert()。

其实更新流程和查询流程也差不多，它也要经过解析器，优化器，执行器的过程；唯一的不同在于拿到符合条件的数据后的操作。

2.1 缓冲池(Buffer Pool)

首先，在InnoDB中的数据是放在磁盘中的，InnoDB操作数据的最小逻辑单位叫做页(页包括索引页和数据页)。我们在操作数据时不是每次都直接操作磁盘的，因为磁盘的速度太慢了，InnoDB中引进了一种缓冲池的技术，把磁盘读到的页放到一块内存区域里缓存起来；这样我们在下一次读取相同的页时，先判断是不是在缓冲池中，如果缓冲池中有就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。Buffer Pool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域。这里我们可以到官网来认识一下 InnoDB 的内存结构和磁盘结构。

2.2 InnoDB的内存结构和磁盘结构

2.3.1 内存结构

Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive HashIndex，另外还有一个(redo)log buffer。这部分内容官网上解析的很清楚，如果想要深入了解可以直接去官网看文档，这里面我只是简单的说下。

1.Buffer Pool

Buffer Pool缓存的是页面信息，包括数据页和索引页，可以通过SHOW STATUS LIKE '%innodb_buffer_pool%';查看服务器状态，里面有很多跟Buffer Pool相关的信息。

查询出的每个变量的含义可以在官网上查看https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html;可以通过 SHOW VARIABLES like '%innodb_buffer_pool%';查看系统变量；变量含义可以通过https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html;查看；数据既然是写在内存中就有写满的时候，在InnoDB 中它有自己的算法来管理缓冲池(链表实现，不是传统的 LRU，分成了 young 和 old)，经过淘汰的数据就是热点数据；

2.Change Buffer

Change Buffer我们又叫做写缓冲，在我们需要更新一条数据时，如果数据页在 Buffer Pool 中存在，那么就直接更新Buffer Pool中的数据就可以了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，这样的操作过程比较消耗性能；为了解决这个问题，我们引进了Buffer Pool,如果这个数据页不是唯一索引，不存在数据重复的情况，也就是说在我们修改或者插入后我们不用去磁盘去确定这个数据是不是唯一的。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句(Insert、Delete、Update)的执行速度。

最后把 Change Buffer同步到磁盘文件也就是记录到数据页里面，这个操作叫做 merge。什么时候发生 merge？有几种情况：在访问这个数据页的时候如果我们的数据页还在Change Buffer的话它会发生merge，或者通过后台线程、或者数据库正常关机的时候、redo log 写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer(写缓冲)。写多读少的业务，调大这个值：

3.(redo)Log Buffer

如果 Buffer Pool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，会导致这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。为了避免这个问题，InnoDB 把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作(用它来实现事务的持久性)。这个文件就是磁盘的 redo log(叫做重做日志)，对应于/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1，每个 48M。这 种 日 志 和 磁 盘 配 合 的 整 个 过 程 ，它的关键点就是先写日志，再写磁盘;当然 redo log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域(Log Buffer)专门用来保存即将要写入日志文件的数据，默认 16M，在数据写入rodo Buffer之前其实数据是先写到Log Buffer中的，它一样可以节省磁盘 IO;在这里需要注意的是redo log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 bufferpool。redo log 写入磁盘，不是写入数据文件。那么Log Buffer的数据文件在写入到磁盘的时候会经过操作系统本身的缓存log buffer定稿磁盘的时机，由一个参数控制，我们可以通过以下SQL查询

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

这是内存结构中的第三块内容，redo log它又分成内存和磁盘两部分。redo log 的特点如下：

1、redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

2、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。

3、redo log 的大小是固定的，空间不够时前面的内容会被覆盖。

4.系统表空间(System Tablespace)

innodb系统表空间包含innodb数据字典(innodb相关对象的元数据)，同时，双写缓冲(doublewrite buffer)、改变缓冲(change buffer)和undo日志(undo logs)等也存储于系统表空间中。此外，系统表空间也包含用户在改表空间创建的表和索引等数据。由于系统表空间可以存储多张表，因此，其为一个共享表空间。系统表空间由一个或多个数据文件组成，默认情况下，其包含一个叫ibdata1的系统数据文件，位于mysql数据目录下。系统表空间数据文件的大小和数目由innodb_data_file_path启动选项控制。

5.表文件表空间(File-Per-Table Tablespaces)

表文件表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表文件表空间中。否则，innodb将被创建于系统表空间中每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表文件表空间支持动态(DYNAMIC)和压缩(commpressed)行格式。

6.通用表空间(General Tablespaces)

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

通过create table tab_name ... tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name语法将其添加与通用表空间内。

7.undo log tablespace

undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常，可以用 undo log 来实现回滚操作(保持原子性)。在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。redo Log 和 undo Log 与事务密切相关，统称为事务日志。undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。

有了前面的知识铺垫，接下来总结下一个更新操作的流程：

update user_innodb where name="张三" where id=1

1、事务开始，从内存或磁盘取到这条数据，返回给 Server 的执行器；

2、执行器修改这一行数据的值为张三;

3、记录 name="原来的值"到 undo log；

4、记录 name=张三 到 redo log；

5、调用存储引擎接口，在内存中修改 name=”张三“；

6、事务提交。

后台线程负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread，IO thread，purge thread，page cleaner thread。

master thread： 负责刷新缓存数据到磁盘并协调调度其它后台进程。

IO thread：分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、重做日志、读写请求的 IO 回调。

purge thread ：用来回收 undo 页。

page cleaner thread： 用来刷新脏页。

除了 InnoDB 架构中的日志文件，MySQL 的 Server 层也有一个日志文件，叫做binlog，它可以被所有的存储引擎使用

2.4 Binlog

上面我们聊了很多InnoDB架构中存储引擎层的很多东西，现在我们聊一下服务层的日志文件Binlog，为方便了解下面在网上截了个图片

Binlog是在我们服务层实现的，所以他可以被所有的存储引擎所共用，而我们前面说的undo log和redo log都是在InnoDB在存储引擎层实现的；binlog 的作用其实是以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是数据值，属于逻辑日志)，因为Binlog记录了我们所有的操作语句，所以可以用来做数据恢复；我们的从服务器可以请求主服务器拿到Binlog里的逻辑日志，然后把他解析出来，在从服务器上再执行一次，从而达到主从复制；

了解了Binlog后，我们再重新分析下一条更新语句执行的流程：

update user_innodb where name="张三" where id=1

1.先查询到这条数据，如果有缓存，也会用到缓存。

2.在服务层把name值改成我们要修改的值然后调用引擎的API接口，写入这一行数据的内存中(是写到Buffer Pool中)，同时记录 redo log。这时 redo log 进入 准备状态，然后告诉服务端的执行器，执行完成了，可以随时提交。

3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log为 commit状态。

4、更新完成

由上面这个图片我们可以清晰的看到记录redo log 分为两个阶段；至于为什么会分两阶段提交，我们来分析下：

1.先写 redo log 后写 binlog

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 name 的值是 张三。但是由于 binlog 没写完就 进程异常重启了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 name 的值就是 是修改前的值，与原库的值不同

2.先写 binlog 后写 redo log

如果在 binlog 写完之后进程异常重启，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 name的值不是张三。但是 binlog 里面已经记录了“把 name 改成 张三”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 name的值就是 张三，与原库的值不同。