MySQL调优(二)-MySQL底层架构及日志类型

本文将从以下几个方面和大家一起学习MySQL的实现原理：

1. MySQL的基本架构图
2. MySQL的日志类型
3. MySQL的数据更新流程

一、MySQL的基本架构图

1.1 连接器
连接器负责跟客户端建立连接，获取权限、维持和管理连接；
连接分为长连接和短连接两种：长连接：推荐使用，但是要周期性的断开长连接。

1.2 查询缓存
当执行查询语句的时候，会先去查询缓存中查看结果，之前执行 过的sql语句及其结果可能以key-value的形式存储在缓存中，如果能找到则直接返回，如果找不到，就继续执行后续的阶段。
但是，不推荐使用查询缓存：

• 查询缓存的失效比较频繁，只要表更新，缓存就会清空
• 缓存对应新更新的数据命中率比较低

1.3 分析器
词法分析：Mysql需要把输入的字符串进行识别每个部分代表什么意思

• 把字符串 T 识别成 表名 T
• 把字符串 ID 识别成 列ID

语法分析： 根据语法规则判断这个sql语句是否满足mysql的语法，如果不符 合就会报错“You have an error in your SQL synta”
1.4 优化器
1.在具体执行SQL语句之前，要先经过优化器的处理 ：

• 当表中有多个索引的时候，决定用哪个索引
• 当sql语句需要做多表关联的时候，决定表的连接顺序
• 等等

2.不同的执行方式对SQL语句的执行效率影响很大
RBO（ Rule_Based Potimizer）:基于规则的优化
RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO。
RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了。
CBO（Cost_Based Potimizer）:基于成本的优化
CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。(计算过程比较复杂)

1.5 执行器
SQL语句的实际执行组件，负责操作引擎，返回结果数据；
sql语句在sql层的流程：
用户传入sql
----->查询缓存（命中缓存可直接返回结果）
----->解析器（生成sql解析树）
----->预处理器（可能sql等价改写）
----->查询优化器（生成sql执行计划）
----->查询执行引擎
----->结果返回给用户。

二、MySQL的日志类型

MySQL常用日志类型：

• 错误日志（error-log）：记录mysql在启动、运行和停止时出现的问题诊断分析。
• 常规日志（general_log）：记录所有mysql客户端发向mysql服务器的请求。包括连接请求、数据库操作请求以及一些管理命令，无论请求是否成功都会记录在这个日志中，可想如果是一个繁忙的业务系统场景，这个日志会很大，所以开启这个日志会对数据库服务器性能会有一定的影响，在业务系统中用完一定要记得关掉和清理这个日志中的内容。
• 慢查询日志(slow_query_log)：记录符合条件的查询。
• 二进制日志（binary_log）：记录全部有效的数据修改日志，是server层的日志，又称为归档日志，属于逻辑日志；作为恢复数据和主从复制。
• 中继日志（relay_log）：用于主从复制，临时存储从主库同步的二进制日志。
• 重做日志（redo log）：属于innodb事务日志，通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
• 回滚日志（undo log）：属于innodb事务日志，用来回滚行记录到某个版本；一般是逻辑日志，根据每行记录进行记录。

各种日志的配置和使用场景：
1.错误日志（error-log）：a.分析排查mysql运行错误；b.记录未经授权的访问。
常用配置参数：

log_error=$mysql/sql_log/mysql-error.log
log_error_verbosity=[1,2,3]   //5.7新增  错误日志中记录错误信息的级别;
>select @@log_error_verbosity；
//解释：
1：Error messages
2:Error and warning messages
3:Error,waring and note message

log_error_services=[日志服务组件；日志服务组件] 8.0新增
常用错误日志的服务组件：
常用配置参数：

log_filter_internal: //默认的日志过滤组件，依赖log_error_verbosity
log_sink_internal:   //默认的日志输出组件，依赖log_error
log_sink_json:       //将错误日志输出到json文件
log_sink_syseventlog://将错误日志输出到系统日志文件

2.常规日志（general_log）：分析客户端发送到mysql的实际请求
常用配置参数：

general_log = [ON|OFF]/[1/0]
general_log_file = $mysql/sql_log/general.log
log_output = [file|table|none]
show variables like 'general_log';

3.慢查日志(slow_query_log)：a.将执行成功并符合条件的查询记录到日志中,b.找到需要优化的sql
常用配置参数：

slow_query_log=[on|off]
slow_query_log_file=$mysql/sql_log/slowlog.log
long_query_time=XX秒 //（可以精确到微秒）查询超过这个值才会被记录到slowlog中，互联网行业中一般设置0.001s比较合适。
set global long_query_time=0.001 //只对新建立的连接起作用。设置为0 是记录所有sql.
log_queries_not_using_indexes=[on|off]
log_slow_admin_statements=[on|off]//设置为on时将会记录管理类命令比如create drop等。
log_slow_slave_statements=[on|off] //前提要开启binlog

4.二进制日志（binary_log）：a.记录所有对数据库中数据的修改;b.基于时间点的备份和恢复;c.高可用架构的基础-主从复制
常用配置参数：

log_bin =  [base_name]  //静态参数 只能在my.cnf中配置 重启服务后生效。 5.7之前一般不会启用这个参数。
binlog_format = [row/statement/mixed] //动态参数。 5.7之后默认row 主从复制中安全性比较高
binlog_row_image = [full/minimal/noblob]  minimal //只会记录被修改的值，noblob会记录除blob外的所有修改前修改后的值。full没修改的列对应值也会记录。
set session binlog_row_image = 'minimal';
binlog_rows_query_log_events = [on|off]  //会同时记录执行的sql语句
log_slave_updates = [on|off]   //默认情况下slave不会记录同步主库过来的binlog日志，有些情况比如级联复制  slave是另一个mysql实例的主，还有需要gtid复制。都需要记录从主从同步过来的binlog日志。
sync_binlog = [1|0]  //控制如何刷新二进制日志到磁盘，mysql每写一次二进制日志都会向磁盘刷新一次。
//二进制日志的清除:
expire_logs_days = days  //动态参数
purge binary logs to 'mysql-bin.010' //将指定日志前的二进制日志都删除。
purge binary logs before 'yyyy-mm-dd hh24:mi:ss';//删除指定日期前的日志。

5.中继日志（relay_log）：

relay_log = filename  //指定relay log存放位置和名称。
relay_log_purge = [on|off]   //自动清理

6.重做日志（redo log）:
确保事务的持久性,当发生数据修改的时候，innodb引擎会先将记录写到redo log中， 并更新内存，此时更新就算是完成了，同时innodb引擎会在合适 的时机将记录操作到磁盘中
▪ redolog是固定大小的，是循环写的过程
▪ 有了redolog之后，innodb就可以保证即使数据库发生异常重启， 之前的记录也不会丢失，叫做crash-safe
文件位置：
默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2
常用配置参数：

innodb_log_group_home_dir //指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
innodb_log_files_in_group //指定重做日志文件组中文件的数量，默认2
关于文件的大小和数量，由一下两个参数配置
innodb_log_file_size //重做日志文件的大小。
innodb_mirrored_log_groups //指定了日志镜像文件组的数量，默认1

疑惑：既然要避免io，为什么写redo log的时候不会造成io的问题？
redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。
也就是说，从redo log buffer写日志到磁盘的redo log file中，过程如下:

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

• 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on
  disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
• 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os
  buffer并调用fsync()写入到log file on
  disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file
  on disk。

redo log的两阶段提交

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

7.回滚日志（undo log）：

• undo log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中， 还用Undo
  Log来实现多版本并发控制(简称：MVCC)。

• 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方 称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo
  Log中的备份将数据恢复到事务开始之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

• 注意：undo log是逻辑日志，可以理解为：
  当delete一条记录时，undo log中会记录一条对应的insert记录
  当insert一条记录时，undo log中会记录一条对应的delete记录
  当update一条记录时，它记录一条对应相反的update记录

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。
常用配置参数：
关于MySQL5.7之后的独立undo 表空间配置参数如下

innodb_undo_directory = /data/undospace/ //undo独立表空间的存放目录
innodb_undo_logs = 128 //回滚段为128KB
innodb_undo_tablespaces = 4 //指定有4个undo log文件
//如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

三、MySQL的数据更新流程

执行流程：
1、执行器先从引擎中找到数据，如果在 内存中直接返回，如果不在内存中，查询后返回
2、执行器拿到数据之后会先修改数据， 然后调用引擎接口重新写入数据
3、引擎将数据更新到内存，同时写数据到redo中，此时处于prepare（准备）阶段，并通知执行器执行完成，随时可以操作
4、执行器生成这个操作的binlog
5、执行器调用引擎的事务提交接口，引 擎把刚刚写完的redo改成commit状态， 更新完成

下面我通过一条更新语句分析sql执行的顺序：

update T set c=c+1 where ID=2;

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