MySQL架构原理(详解)

一.MySQL整体逻辑架构

我们先下图看看MySQL整体逻辑架构(MySQL’s Logical Architecture)

图1

第一层：连接层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。

第二层：核心服务层 。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视 图等。

第三层：存储引擎层。通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事 物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。

第四层：数据存储层。主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

二.MySQL逻辑模块组成

虽然从上图1看起来 MySQL 架构非常的简单，就是简单的两部分而已，但实际上每一层 中都含有各自的很多小模块，尤其是第二层 SQL Layer ，结构相当复杂的。下面我们就分别 针对 SQL Layer 和 Storage Engine Layer 做一个简单的分析。我们看下图体系结构：

图2

1.Connectors

指的是不同语言中与SQL的交互，如php、java等。

2 Management Serveices & Utilities：

系统管理和控制工具

3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信， 接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。 主要功能： a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。 b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

他使用的是“选取-投影-联接”策略进行查询。 用一个例子就可以理解： select uid,name from user where gender = 1; 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤 这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤 将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer： 查询缓存。

Cache：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

buffer与cache的区别？ 缓存那里实际上有buffer和cache两个，那它们之间是否有什么不同呢？简单的说就是，buffer是写缓存，cache是读缓存。

8 、存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。 从图2还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。 注意：存储引擎是基于表的，而不是数据库。

三.SQL SELECT语句执行过程

MySQL 整个查询执行过程，总的来说分为 6 个步骤 :

SQL执行步骤：请求、缓存、SQL解析、优化SQL查询、调用引擎执行，返回结果 1、连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。 2、缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段 3、解析：服务器进行SQL解析(词法语法)、预处理。 4、优化：再由优化器生成对应的执行计划。 5、执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。 6、结果：将结果返回给客户端，同时缓存查询结果。

首先程序的请求会通过mysql的connectors与其进行交互，请求到处后，会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。

SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果；否则，需要完整的走一趟流程：

1)由SQL接口丢给后面的解释器（Parser），上面已经说到，解释器会判断SQL语句正确与否，若正确则将其转化为数据结构。

2)解释器处理完，便来到后面的优化器（Optimizer），它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。

3)确定最优执行计划后，SQL语句此时便可以交由存储引擎（Engine）处理，存储引擎将会到后端的存储设备中取得相应的数据，并原路返回给程序。

第1步：Connectors ：客户端/服务端通信协议

MySQL客户端/服务端通信协议 是 “半双工” 的，在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet参数，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一

具体建立连接说明：

建立与 MySQL 的连接，这就是由连接器Connectors来完成的。连接器Connectors负责跟客户端建立连接、获取权限、维持和管理连接。连接命令为： mysql -hlocalhost -P3306 -uuser -ppasswd

验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。

系统的连接：

客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数 wait_timeout 控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。

长连接和短连接

• 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢？可以考虑以下两种方案：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

第2步：查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表 (不要理解成table，可以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，再比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义

MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿，而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外 : 1、 任何的查询语句在开始之前都必须经过检查，即使这条 SQL语句 永远不会命中缓存 2、如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

3、两个SQL语句，只要相差哪怕是一个字符（例如 大小写不一样：多一个空格等），那么两个SQL将使用不同的cache。

基于此，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查询缓存，并在数据库设计上做一些优化 : 1、用多个小表代替一个大表，注意不要过度设计 2、批量插入代替循环单条插入 3、合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适 4、可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存 注 : SQL_NO_CACHE 是禁止缓存查询结果，但并不意味着 cache 不作为结果返回给 query，之前的缓存结果之后也可以查询到

代码语言：javascript复制

mysql> SELECT SQL_CACHE COUNT(*) FROM a;
 ---------- 
| COUNT(*) |
 ---------- 
|    98304 |
 ---------- 
1 row in set, 1 warning (0.01 sec)mysql> SELECT SQL_NO_CACHE COUNT(*) FROM a;
 ---------- 
| COUNT(*) |
 ---------- 
|    98304 |
 ---------- 
1 row in set, 1 warning (0.02 sec)

可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，都可以指定不进行查询缓存，使用 SQL_NO_CACHE 选项。对于那些变化不频繁的表，查询操作很固定，可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用 SQL_CACHE 选项

查看开启缓存的情况，可以知道query_cache_size的设置是否合理 mysql> SHOW VARIABLES LIKE '%query_cache%';

查询服务器关于query_cache的配置 query_cache_limit：超出此大小的查询将不被缓存 query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。 query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。） query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下： 0：OFF 相当于禁用了 1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存 2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。 query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。

对于查询缓存的一些操作 FLUSH QUERY CACHE : 清理查询缓存内存碎片 RESET QUERY CACHE : 从查询缓存中移出所有查询 FLUSH TABLES : 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100% 查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多

查询缓存命中率：Qcache_hits/(Qcache_hits Qcache_inserts)*100%

Query Cache的限制 a）所有子查询中的外部查询SQL 不能被Cache： b）在p'rocedure，function以及trigger中的Query不能被Cache c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache

Tip: MySQL 8.0 版本将查询缓存的功能删除了。

第3步：Analyzer分析器

如果查询缓存未命中，就要开始执行语句了。首先，MySQL 需要对 SQL 语句进行解析。

1、词法分析：

SQL语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“user_info”识别成“表名 user_info”， 把字符串“id ”识别成“列 id ”

2、语法分析：

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这SQL语句是否满足 MySQL 语法。

如果你 SQL 语句不对，就会收到 You have an error in your SQL syntax 的错误提醒，比如下面这个语句 from 写成了 form。

mysql> select * form user_info where id = 1; 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 'form user_info where id = 1' at line 1

一般语法错误会提示第一个出现错误的位置，所以要关注的是紧接 use near 的内容。

第4步：Optimizer优化器：查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果,优化器的作用就是找到这其中最好的执行计划.

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本 mysql> SELECT * FROM p_product_fee WHERE total_price BETWEEN 580000 AND 680000; mysql> SHOW STATUS LIKE 'last_query_cost'; # 显示要做多少页的随机查询才能得到最后一查询结果，这个结果是根据一些列的统计信息计算得来的，这些统计信息包括 : 每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等 有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样 (我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短) 等等

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划 :

1、在表里面有多个索引的时候，决定使用哪个索引； 2、重新定义表的关联顺序 (多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序) 3、优化 MIN() 和 MAX()函数 (找某列的最小值，如果该列有索引，只需要查找 B Tree索引 最左端，反之则可以找到最大值) 4、 提前终止查询 (比如 : 使用 Limit 时，查找到满足数量的结果集后会立即终止查询) 5、 优化排序 (在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)

比如你执行下面这样的语句，这个语句是执行两个表的 join：

代码语言：javascript复制

mysql> SELECT * FROM order_master JOIN order_detail USING (order_id) WHERE order_master.pay_status = 0 AND order_detail.detail_id = 1558963262141624521;

既可以先从表 order_master 里面取出 pay_status = 0 的记录的 order_id 值，再根据 order_id 值关联到表 order_detail，再判断 order_detail 里面 detail_id 的值是否等于 1558963262141624521。

也可以先从表 order_detail 里面取出 detail_id = 1558963262141624521 的记录的 order_id 值，再根据 order_id 值关联到 order_master，再判断 order_master 里面 pay_status 的值是否等于 0。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

第5步：查询执行引擎Actuator

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个 handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作

开始执行SQL语句:mysql> select * from user_info where id = 1;

1）、判断是否有查询权限有就继续执行没有就返回权限错误。

例如判断当前连接对这个表 user_info 有没有执行查询的权限，如果没有，就会返回没有权限的错误。错误如下（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

代码语言：javascript复制

ERROR 1142 (42000): SELECT command denied to user 'appusser'@'localhost' for table 'user_info'

2）、执行器根据表的引擎定义去掉用引擎接口

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

对于没有有索引的表使用全表扫描API：比如我们这个例子中的表 user_info 中，id 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

全表扫描接口： //初始化全表扫描 virtual int rnd_init (bool scan); //从表中读取下一行 virtual int rnd_next (byte* buf);

对于有索引的表，使用索引相关接口：

1、第一次调用读取索引第一条内容接口（ha_index_first）。

2、之后循环取满足索引条件的下一行接口（ha_index_next）。

通过索引访问table内容： //使用索引前调用该方法 int ha_foo::index_init(uint keynr, bool sorted) //使用索引后调用该方法 int ha_foo::index_end(uint keynr, bool sorted) //读取索引第一条内容 int ha_index_first(uchar * buf); //读取索引下一条内容 int ha_index_next(uchar * buf); //读取索引前一条内容 int ha_index_prev(uchar * buf); //读取索引最后一条内容 int ha_index_last(uchar * buf); //给定一个key基于索引读取内容 int index_read(uchar * buf, const uchar * key, uint key_len, enum ha_rkey_function find_flag)

数据库的慢查询日志中有 rows_examined 字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

第6步 返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过 TCP协议 进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送

查询系统性能 SHOW STATUS LIKE 'value'; value参数的几个统计参数如下 : Connections : 连接 MySQL 服务器的次数 Uptime : MySQL 服务器的上线时间 Slow_queries : 慢查询次数 Com_Select : 查询操作的次数 Com_insert : 插入操作的次数 Com_update : 更新操作的次数 Com_delete : 删除操作的次数

四.SQL更新机制和日志记录

4.1、基本流程：

查询语句的流程，更新语句也会同样的走一遍。sql= update T set c=c 1 where id=2

1、客户端向 MySQL 服务器发送一条更新请求 2、清除表查询缓存，跟这个有关的查询缓存会失效。这就是一般不建议使用查询缓存的原因。 3、分析器进行 SQL解析（词法和语法分析），分析这是一条更新语句和。 4、优化器生成对应的执行计划，优化器决定使用ID这个索引； 5、执行器负责更新，找到这一行，然后进行更新:

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

4.2、日志类型

与查询流程不同的是，更新流程涉及两个重要日志模块：redo log（重做日志）和 binlog（归档日志）。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

redo log在数据库重启恢复的时候被使用，因为其属于物理日志的特性，恢复速度远快于逻辑日志。而binlog和undo log属于的逻辑日志。

4.3、redo log(重做日志)：

redo log 是InnoDB引擎特有的物理日志，记录的是数据页的物理修改，即用于记录事务操作的变化，不管事务是否提交都会记录下来。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，InnoDB存储引擎会使用redo log恢复到掉宕机前的时刻，以此来保证数据的完整性。这个能力称为crash-safe。

在一条更新SQL语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中,并更新内存，这时更新完成。同时InnoDB引擎在适当的时候，将这个操作记录更新到磁盘里。

在MySQL中，每次的更新操作都需要写进磁盘，然后磁盘也要找到对应记录，进行更新，整个过程的IO成本、查找成本都很高。可使WAL(Write-Ahead-Logging)技术，他的关键点是先写日志，再写磁盘。

4.3.1 redo log的实施流程：

提交事务 -> 日志写入relog log buffer -> os buffer -> 写入磁盘的log file -> 根据checkpoint更新磁盘中的数据

为了确保每次日志都能写入到事务日志文件中，Redo log写入磁盘时，必须进行一次操作系统fsync操作 (即fsync()系统调用, MySQL是工作在用户空间的，Redo Log buffer也就处于用户空间的内存中)，防止redo log只是写入操作系统磁盘缓存中。参数innodb_flush_log_at_trx_commit可以控制redo log日志刷新到磁盘策略。

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。

I/O Master线程这是InnoDB一个在后台运行的主线程。它做的主要工作包括但不限于：刷新日志缓冲，合并插入缓冲，刷新脏页等。Master线程大致分为每秒运行一次的操作和每10秒运行一次的操作。master thread中刷新数据，属于checkpoint的一种。所以如果在master thread在刷新日志的间隙，DB出现故障那么将丢失掉这部分数据。

4.3.2 redo log参数

以下是通过mysql命令行查看参数：show variables like '%innodb_log%';

• innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。
  • 0表示每秒将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
  • 1表示每事务提交都将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
  • 2表示每事务提交都将"log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。
• innodb_log_buffer_size：# log buffer的大小，默认8M
• innodb_log_file_size：#事务日志的大小，默认5M
• innodb_log_files_group =2：# 事务日志组中的事务日志文件个数，默认2个
• innodb_log_group_home_dir =./：# 事务日志组路径，当前目录表示数据目录
• innodb_mirrored_log_groups =1：# 指定事务日志组的镜像组个数，但镜像功能好像是强制关闭的，所以只有一个log group。在MySQL5.7中该变量已经移除

innodb-log-files-in-group表示的是redo log group，一个组内由多个大小完全相同的redo log file组成。组内redo log file的数量由变量 innodb_log_files_group 决定，默认值为2，即两个redo log file。这个组是一个逻辑的概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file都放在这个目录下，默认是在datadir下。

可以看到在默认的数据目录下，有两个ib_logfile开头的文件，它们就是log group中的redo log file，而且它们的大小完全一致且等于变量 innodb_log_file_size 定义的值。第一个文件ibdata1是在没有开启 innodb_file_per_table 时的共享表空间文件，对应于开启 innodb_file_per_table 时的.ibd文件。

4.3.3 Redo log机制：

InnoDB的redo log 是固定大小，即记录满了以后就从头循环写。

图中展示了一组 4 个文件的 redo log 日志，checkpoint 是当前要擦除的位置，擦除记录前需要先把对应的数据落盘（更新内存页，等待刷脏页）。write pos 到 checkpoint 之间的部分可以用来记录新的操作，如果 write pos 和 checkpoint 相遇，说明 redolog 已满，这个时候数据库停止进行数据库更新语句的执行，转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘（先更新内存页，然后等待刷脏页）。

若可配置2个redo log日志文件. 每个文件的大小是256M，总共记录就是512M；参数如下:

innodb-log-files-in-group = 2 innodb-log-file-size = 256M

在innodb将log buffer中的redo log block刷到这些log file中时，会以追加写入的方式循环轮训写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。

redo log file的大小对innodb的性能影响非常大，设置的太大，恢复的时候就会时间较长，设置的太小，就会导致在写redo log的时候循环切换redo log file。

4.4、binlog (归档日志)

binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的.

binlog主要作用：复制（Master-Slave 主从同步）、恢复和审计。

最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

1、binlog是Server层实现的，意味着所有引擎都可以使用binlog日志 2、binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

4.4.1 binglog参数配置：

show variables like '%binlog%';

sync_binlog

sync_binlog=0 的时候，表示每次提交事务都只写page cache ,不会持久化到硬盘

sync_binlog=1 的时候，表示每日提交事务之后都会写page cache，并且持久化到硬盘 ,保证MySQL异常重启后 binlog不丢失；

sync_binlog=N 的时候，表示当积累N次事务之后就会一次性写入硬盘

1，如果不是什么特别重要的数据，且并发比较高的时候可以把snc_binlog设置成100-1000中的某个值，这样可以提高性能 2，如果是对特别重要的数据，例如订单数据则建议将sync_binlog的值设置为1，这样能够保证哪怕数据库挂了，也可以保证不丢数据 3，当sync_binglog设置为0的时候，mysql会根据操作系统自动进行写入且mysql默认这个值就是0

[mysqld]

binlog_format = mixed #设置日志格式

log-bin = /data/mysql/logs/mysql-bin.log #设置日志路径，注意路经需要mysql用户有权限写

expire_logs_days = 7#设置binlog清理时间

max_binlog_size = 100m#binlog每个日志文件大小

binlog_cache_size = 4m#binlog缓存大小

max_binlog_cache_size = 512m#最大binlog缓存大小

4.3.2 Binlog 格式

binlog的格式也有三种：STATEMENT、ROW、MIXED 。 1、STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。 优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。 缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题) 2、ROW: 基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。 3、MIXED混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

4.5 redo log和binlog区别

• redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
• redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
• redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
• binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

五.配置文件详解：SHOW Variables

---

mysqld服务器维护两种变量:

1)、全局变量影响服务器的全局操作：

服务器启动时，将所有全局变量初始化为默认值。可以在配置文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量，必须具有SUPER权限。

2)、会话变量影响具体客户端连接相关操作。

服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET SESSION var_name语句来更改动态会话变量。设置会话变量不需要特殊权限，但客户可以只更改自己的会话变量，而不更改其它客户的会话变量。

任何访问全局变量的客户端都可以看见对全局变量的更改。但是，它只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。它不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。

当使用启动选项设置变量时，变量值可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes。例如，下面的命令启动服务器时的键值缓冲区大小为16 megabytes：

key_buffer_size=16M #后缀的大小写不敏感;16M和16m是同样的。

运行时，使用SET语句来设置系统变量。此时，不能使用后缀，但值可以采取下列表达式：

SET sort_buffer_size = 10 * 1024 * 1024;

要想显式指定是否设置全局或会话变量，使用GLOBAL或SESSION选项：

mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;

mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;

两个选项均没有，则语句设置会话变量。

可以通过SHOW VARIABLES语句查看系统变量及其值。

mysql> SHOW VARIABLES;

[client]

#MySQL客户端配置######################################### port = 3306 # MySQL客户端默认端口号

socket = /data/mysql/my3306/mysql.sock # 用于本地连接的Unix套接字文件存放路径

default-character-set = utf8mb4# MySQL客户端默认字符集

[mysql]

# MySQL命令行配置######################################### auto-rehash # 开启tab补齐功能

socket = /data/mysql/my3306/mysql.sock # 用于本地连接的Unix套接字文件存放路径

default-character-set = utf8mb4 # MySQL客户端默认字符集

max_allowed_packet = 256M # 指定在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。

[mysqld]#MySQL服务端配置

#General ########################################

port = 3306 # MySQL服务端默认监听的TCP/IP端口

socket = /data/mysql/my3306/mysql.sock # 用于本地连接的Unix套接字文件存放路径

pid_file = /data/mysql/my3306/mysql.pid # 进程ID文件存放路径

basedir = /app/mysql # MySQL软件安装路径

datadir = /data/mysql/my3306 # MySQL数据文件存放路径

tmpdir = /data/mysql/my3306 # MySQL临时文件存放路径

character_set_server = utf8mb4 # MySQL服务端字符集

collation_server = utf8mb4_general_ci # MySQL服务端校对规则

default-storage-engine = InnoDB # 设置默认存储引擎为InnoDB

autocommit = OFF # 默认为ON，设置为OFF，关闭事务自动提交

transaction_isolation = READ-COMMITTED # MySQL支持4种事务隔离级别，他们分别是： # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. # 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

event_scheduler = ON # 开启事件调度器event_scheduler

#explicit_defaults_for_timestamp = ON # 控制TIMESTAMP数据类型的特性，默认OFF，设置为ON，update 时timestamp列关闭自动更新。（将来会被废弃）

lower_case_table_names = 1 # 库名、表名是否区分大小写。默认为0，设置1，不区分大小写，创建的表、数据库都以小写形式存放磁盘。

#Network & Connection #########################################

max_connections = 1000 # MySQL允许的最大并发连接数，默认值151，如果经常出现Too Many Connections的错误提示，则需要增大此值。

max_user_connections = 1000 # 每个数据库用户的最大连接，（同一个账号能够同时连接到mysql服务的最大连接数），默认为0，表示不限制。

back_log = 500 # MySQL监听TCP端口时设置的积压请求栈大小，默认50 (max_connections/5)，最大不超过900

max_connect_errors = 10000 # 每个主机的连接请求异常中断的最大次数。对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

interactive_timeout = 28800 # 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）

wait_timeout = 28800 # 服务器关闭非交互连接之前等待活动的秒数。默认值：28800秒（8小时） # 指定一个请求的最大连接时间，当MySQL连接闲置超过一定时间后将会被强行关闭。对于4GB左右内存的服务器来说，可以将其设置为5~10。 # 如果经常出现Too Many Connections的错误提示，或者show processlist命令发现有大量sleep进程，则需要同时减小interactive_timeout和wait_timeout值。

connect_timeout = 28800 # 在获取连接时，等待握手的超时秒数，只在登录时生效。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。

open_files_limit = 65535 # mysqld能打开文件的最大个数，默认最小1024，如果出现too mant open files之类的就需要增大该值。

max_allowed_packet = 256M # 指定在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。

# Thread & Buffer#########################################

sort_buffer_size = 2M # 排序缓冲区大小，connection级参数，默认大小为2MB。如果想要增加ORDER BY/GROUP BY的速度，首先看是否可以让MySQL使用索引，其次可以尝试增大该值。 在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。sort_buffer_size并不是越大越好，由于是connection级的参数，过大的设置 高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G

read_buffer_size = 160M # 顺序读缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。对表进行顺序扫描的请求将分配一个读入缓冲区。

read_rnd_buffer_size = 160M # 随机读缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。默认值256KB，最大值4GB。当按任意顺序读取行时，将分配一个随机读缓存区。

join_buffer_size = 320M # 联合查询缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。

bulk_insert_buffer_size = 64M # 批量插入数据缓存大小，可以有效提高插入效率，默认为8M

thread_cache_size = 8 # 服务器线程缓冲池中存放的最大连接线程数。默认值是8，断开连接时如果缓存中还有空间，客户端的线程将被放到缓存中，当线程重新被请求，将先从缓存中读取，（前提是缓存数未到达上限）。 # 根据物理内存设置规则如下：1G —> 8，2G —> 16，3G —> 32，大于3G —> 64

增加这个值可以改善系统性能，通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。

thread_stack = 256K # 每个连接被创建时,mysql分配给它的内存。默认192KB，已满足大部分场景，除非必要否则不要动它，可设置范围128KB~4GB。

query_cache_type = 0 # 关闭查询缓存

query_cache_size = 0 # 查询缓存大小，在高并发，写入量大的系统，建议把该功能禁掉。

通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理

Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。 注：当一个表被更新后，和他相关的cache block将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。

Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了。

Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。

Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存 应用到的比较少，效果也就不理想。

Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。

Qcache_queries_in_cache：当前Query Cache 中cache的Query数量 Qcache_total_blocks：当前Query Cache中block的数量

query_cache_limit = 4M # 指定单个查询能够使用的缓冲区大小，缺省为1M

tmp_table_size = 1024M # MySQL的heap（堆积）表缓冲大小，也即内存临时表，默认大小是 32M。如果超过该值，则会将临时表写入磁盘。在频繁做很多高级 GROUP BY 查询的DW环境，增大该值。 # 实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。

max_heap_table_size = 1024M # 用户可以创建的内存表(memory table)的大小，这个值用来计算内存表的最大行数值。

table_definition_cache = 400 # 表定义缓存区，缓存frm文件。表定义(global)是全局的，可以被所有连接有效的共享。

table_open_cache = 1000 # 所有SQL线程可以打开表缓存的数量，缓存ibd/MYI/MYD文件。 打开的表(session级别)是每个线程，每个表使用。

table_open_cache_instances = 4 # 对table cache 能拆成的分区数，用于减少锁竞争，最大值64.

# Safety #######################################

#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER # MySQL支持的SQL语法模式，与其他异构数据库之间进行数据迁移时，SQL Mode组合模式会有帮助。

local_infile = OFF # 禁用LOAD DATA LOCAL命令

plugin-load = validate_password.so # 加密认证插件，强制mysql设置复杂密码

skip-external-locking #skip-locking # 避免MySQL的外部锁定，减少出错几率，增强稳定性。

skip-name-resolve # 禁止MySQL对外部连接进行DNS解析，消除MySQL进行DNS解析。如果开启该选项，所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

#skip-networking # 不允许CP/IP连接，只能通过命名管道（Named Pipes）、共享内存（Shared Memory）或Unix套接字（Socket）文件连接。 # 如果Web服务器以远程连接方式访问MySQL数据库服务器，则不要开启该选项，否则无法正常连接！ # 适合应用和数据库共用一台服务器的情况，其他客户端无法通过网络远程访问数据库

# Logs #########################################

################### General Log ###################### general_log = OFF # 关闭通用查询日志

general_log_file = /data/mysql/my3306/general.log # 通用查询日志存放路径

################### Slow Log ###################### slow_query_log = ON # 开启慢查询日志

slow_query_log_file = /data/mysql/my3306/slow.log # 慢查询日志存放路径

long_query_time = 10 # 超过10秒的查询，记录到慢查询日志，默认值10

log_queries_not_using_indexes = ON # 没有使用索引的查询，记录到慢查询日志，可能引起慢查询日志快速增长

log_slow_admin_statements = ON # 执行缓慢的管理语句，记录到慢查询日志 # 例如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

################### Error Log #################### log_error = /data/mysql/my3306/error.log # 错误日志存放路径

log_error_verbosity = 2 # 全局动态变量，默认3，范围：1～3 # 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息

#Replication ########################################

################### Bin Log ###################### server_id = 6 # 数据库服务器ID

log_bin = /data/mysql/my3306/binlog # 二进制日志存放路径

log_bin_index = /data/mysql/my3306/binlog.index # 同binlog，定义binlog的位置和名称

binlog_format = row # binlog格式，复制有3种模式STATEMENT，ROW，MIXED

expire_logs_days = 10 # 只保留最近10天的binlog日志

max_binlog_size = 50M # 每个binlog日志文件的最大容量

binlog_cache_size = 2M # 每个session分配的binlog缓存大小 # 事务提交前产生的日志，记录到Cache中；事务提交后，则把日志持久化到磁盘

log_slave_updates = ON # 开启log_slave_updates，从库的更新操作记录进binlog日志

sync_binlog = 1 # sync_binlog=0（默认），事务提交后MySQL不刷新binlog_cache到磁盘，而让Filesystem自行决定，或者cache满了才同步。 # sync_binlog=n，每进行n次事务提交之后，MySQL将binlog_cache中的数据强制写入磁盘。

binlog_rows_query_log_events = ON # 将row模式下的sql语句，记录到binlog日志，默认是0(off)

################### Relay Log ###################### relay_log = /data/mysql/my3306/relaylog # 中继日志存放路径

relay_log_index = /data/mysql/my3306/relaylog.index # 同relay_log，定义relay_log的位置和名称

#binlog_checksum = CRC32 # Session-Thread把Event写到Binlog时，生成checksum。默认为（NONE），兼容旧版本mysql。

master_verify_checksum = ON # Dump-Thread读Binlog中的Event时，验证checksum

slave_sql_verify_checksum = ON # 从库的I/O-Thread把Event写入Relaylog时，生成checksum；从库的SQL-Thread从Relaylog读Event时，验证checksum

master_info_repository = TABLE relay_log_info_repository = TABLE # 将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议改为Innodb引擎，防止表损坏后自行修复。

relay_log_purge = ON relay_log_recovery = ON # 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。

skip_slave_start = OFF # 重启数据库，复制进程默认不启动

slave_net_timeout = 5 # 当master和slave之间的网络中断，slave的I/O-Thread等待5秒，重连master

sync_master_info = 10000 # slave更新mysql.slave_master_info表的时间间隔

sync_relay_log = 10000 sync_relay_log_info = 10000 # slave更新mysql.slave_relay_log_info表的时间间隔

gtid_mode = ON enforce_gtid_consistency = ON # GTID即全局事务ID（global transaction identifier），GTID由UUID TID组成的。 # UUID是一个MySQL实例的唯一标识，TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。 # GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式： # 4e659069-3cd8-11e5-9a49-001c4270714e:1-77

auto_increment_offset = 1 # 双主复制中，2台服务器的自增长字段初值分别配置为1和2，取值范围是1 .. 65535

auto_increment_increment = 2 # 双主复制中，2台服务器的自增长字段的每次递增值都配置为2，其默认值是1，取值范围是1 .. 65535

# InnoDB ########################################

innodb_data_home_dir = /data/mysql/my3306 # innodb表的数据文件目录

innodb_file_per_table = ON # 使用独立表空间管理

innodb_data_file_path = ibdata1:1G:autoextend # InnoDB共享表空间磁盘文件，存放数据字典、和在线重做日志

innodb_log_group_home_dir = /data/mysql/my3306 # 在事务被提交并写入到表空间磁盘文件上之前，事务数据存储在InnoDB的redo日志文件里。这些日志位于innodb_log_group_home_dir变量定义的目录中

innodb_buffer_pool_size = 2G # InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典的缓冲池。该值越大，缓存命中率越高，但是过大会导致页交换。

#对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的70%~80%。

innodb_buffer_pool_instances = 8 # 开启8个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写，降低并发导致的内部缓存访问冲突。 # InnoDB缓存系统会把参数innodb_buffer_pool_size指定大小的缓存，平分为innodb_buffer_pool_instances个buffer_pool

#innodb_additional_mem_pool_size = 16M # InnoDB存储数据字典、内部数据结构的缓冲池大小，类似于Oracle的library cache

innodb_log_file_size = 256M # InnoDB redo log大小，对应于ib_logfile0文件。 # ib_logfile* 是Innodb多版本缓冲的一个保证，该日志记录redo、undo信息，即commit之前的数据，用于rollback操作。 # 官方文档的建议设置是innodb_log_file_size = innodb_buffer_pool_size/innodb_log_files_in_group

innodb_log_buffer_size = 64M #此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_files_in_group = 4 # redo日志文件数，默认值为2，日志是以顺序的方式写入。

innodb_max_dirty_pages_pct = 90 # 缓存池中脏页的最大比例，默认值是75%，如果脏页的数量达到或超过该值，InnoDB的后台线程将开始缓存刷新。 # “缓存刷新”是指InnoDB在找不到干净的可用缓存页或检查点被触发等情况下，InnoDB的后台线程就开始把“脏的缓存页”回写到磁盘文件中。

innodb_flush_log_at_trx_commit = 1

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0，1，2.

#设置为0 ，每秒 write cache & flush disk #设置为1 ，每次commit都 write cache & flush disk #设置为2 ，每次commit都 write cache，然后根据innodb_flush_log_at_timeout（默认为1s）时间 flush disk

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。

innodb_lock_wait_timeout = 10 # InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables语句或第三方事务引擎,则InnoDB无法识别死锁。 # 为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)。

innodb_sync_spin_loops = 40 # 自旋锁的轮转数，可以通过show engine innodb status来查看。 # 如果看到大量的自旋等待和自旋轮转，则它浪费了很多cpu资源。浪费cpu时间和无谓的上下文切换之间可以通过该值来平衡。

innodb_support_xa = ON # 第一，支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。 # 第二，支持内部xa事务，即支持binlog与innodb redo log之间数据一致性。

#innodb_file_format = barracuda # InnoDB文件格式，Antelope是innodb-base的文件格式，Barracude是innodb-plugin后引入的文件格式，同时Barracude也支持Antelope文件格式。

innodb_flush_method = O_DIRECT # 设置innodb数据文件及redo log的打开、刷写模式，fdatasync(默认)，O_DSYNC，O_DIRECT # 默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer # 设置为为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件 # 设置为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

innodb_strict_mode = ON # 开启InnoDB严格检查模式，在某些情况下返回errors而不是warnings，默认值是OFF

innodb_checksum_algorithm = strict_crc32 # checksum函数的算法，默认为crc32。可以设置的值有:innodb、crc32、none、strict_innodb、strict_crc32、strict_none

innodb_status_file = 1 # 启用InnoDB的status file，便于管理员查看以及监控

innodb_open_files = 3000 # 限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。

innodb_thread_concurrency = 8 # 同时在Innodb内核中处理的线程数量，建议默认值。默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。

innodb_thread_sleep_delay = 500

#innodb_file_io_threads = 16 # 文件读写I/O数，这个参数只在Windows上起作用。在LINUX上只会等于４，默认即可。

innodb_read_io_threads = 16 # 设置read thread(读线程个数，默认是4个)

innodb_write_io_threads = 16 # 设置write thread(写线程个数，默认是4个)

innodb_io_capacity = 2000 # 磁盘io的吞吐量，默认值是200.对于刷新到磁盘页的数量，会按照inodb_io_capacity的百分比来进行控制。

log_bin_trust_function_creators = 1 # 开启log-bin后可以随意创建function，存在潜在的数据安全问题。

innodb_purge_threads = 1 # 使用独立线程进行purge操作。 # 每次DML操作都会生成Undo页，系统需要定期对这些undo页进行清理，这称为purge操作。

innodb_purge_batch_size = 32 # 在进行full purge时，回收Undo页的个数，默认是20，可以适当加大。

innodb_old_blocks_pct = 75 # LRU算法，默认值是37，插入到LRU列表端的37%，差不多3/8的位置。 # innodb把midpoint之后的列表称为old列表，之前的列表称为new列表，可以理解为new列表中的页都是最为活跃的热点数据。

innodb_change_buffering = all # 用来开启各种Buffer的选项。该参数可选的值为：inserts、deletes、purges、changes、all、none。 # changes表示启用inserts和deletes，all表示启用所有，none表示都不启用。该参数默认值为all。

[mysqldump]

max_allowed_packet = 256M

quick # mysqldump导出大表时很有用，强制从服务器查询取得记录直接输出，而不是取得所有记录后将它们缓存到内存中。

[mysqlhotcopy] interactive-timeout

[mysqld_safe] #ledir = /app/mysql/bin # 包含mysqld程序的软件安装路径，用该选项来显式表示服务器位置。

6.Mysql状态详解

show status 命令 主要了解参数含义如下: aborted_clients ：客户端非法中断连接次数 aborted_connects ：连接mysql失败次数 com_xxx xxx：命令执行次数,有很多条 connections： 连接mysql的数量 Created_tmp_disk_tables ：在磁盘上创建的临时表 Created_tmp_tables ：在内存里创建的临时表 Created_tmp_files ：临时文件数 Key_read_requests The number of requests to read a key block from the cache Key_reads The number of physical reads of a key block from disk Max_used_connections： 同时使用的连接数 Open_tables ：开放的表 Open_files :开放的文件 Opened_tables :打开的表 Questions: 提交到server的查询数 Sort_merge_passes: 如果这个值很大,应该增加my.cnf中的sort_buffer值 Uptime :服务器已经工作的秒数

提升性能的建议: 1.如果opened_tables太大,应该把my.cnf中的table_cache变大 2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率 3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥索引的键的作用 4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率 5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的

状态名	作用域	详细解释
Aborted_clients	Global	由于客户端没有正确关闭连接导致客户端终止而中断的连接数
Aborted_connects	Global	试图连接到MySQL服务器而失败的连接数
Binlog_cache_disk_use	Global	使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
Binlog_cache_use	Global	使用临时二进制日志缓存的事务数量
Bytes_received	Both	从所有客户端接收到的字节数。
Bytes_sent	Both	发送给所有客户端的字节数。
com*		各种数据库操作的数量
Compression	Session	客户端与服务器之间只否启用压缩协议
Connections	Global	试图连接到(不管是否成功)MySQL服务器的连接数
Created_tmp_disk_tables	Both	服务器执行语句时在硬盘上自动创建的临时表的数量
Created_tmp_files	Global	mysqld已经创建的临时文件的数量
Created_tmp_tables	Both	服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘
Delayed_errors	Global	用INSERT DELAYED写的出现错误的行数(可能为duplicate key)。
Delayed_insert_threads	Global	使用的INSERT DELAYED处理器线程数。
Delayed_writes	Global	写入的INSERT DELAYED行数
Flush_commands	Global	执行的FLUSH语句数。
Handler_commit	Both	内部提交语句数
Handler_delete	Both	行从表中删除的次数。
Handler_discover	Both	MySQL服务器可以问NDB CLUSTER存储引擎是否知道某一名字的表。这被称作发现。Handler_discover说明通过该方法发现的次数。
Handler_prepare	Both	A counter for the prepare phase of two-phase commit operations.
Handler_read_first	Both	索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。
Handler_read_key	Both	根据键读一行的请求数。如果较高，说明查询和表的索引正确。
Handler_read_next	Both	按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev	Both	按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd	Both	根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。
Handler_read_rnd_next	Both	在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
Handler_rollback	Both	内部ROLLBACK语句的数量。
Handler_savepoint	Both	在一个存储引擎放置一个保存点的请求数量。
Handler_savepoint_rollback	Both	在一个存储引擎的要求回滚到一个保存点数目。
Handler_update	Both	在表内更新一行的请求数。
Handler_write	Both	在表内插入一行的请求数。
Innodb_buffer_pool_pages_data	Global	包含数据的页数(脏或干净)。
Innodb_buffer_pool_pages_dirty	Global	当前的脏页数。
Innodb_buffer_pool_pages_flushed	Global	要求清空的缓冲池页数
Innodb_buffer_pool_pages_free	Global	空页数。
Innodb_buffer_pool_pages_latched	Global	在InnoDB缓冲池中锁定的页数。这是当前正读或写或由于其它原因不能清空或删除的页数。
Innodb_buffer_pool_pages_misc	Global	忙的页数，因为它们已经被分配优先用作管理，例如行锁定或适用的哈希索引。该值还可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total	Global	缓冲池总大小（页数）。
Innodb_buffer_pool_read_ahead_rnd	Global	InnoDB初始化的“随机”read-aheads数。当查询以随机顺序扫描表的一大部分时发生。
Innodb_buffer_pool_read_ahead_seq	Global	InnoDB初始化的顺序read-aheads数。当InnoDB执行顺序全表扫描时发生。
Innodb_buffer_pool_read_requests	Global	InnoDB已经完成的逻辑读请求数。
Innodb_buffer_pool_reads	Global	不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
Innodb_buffer_pool_wait_free	Global	一般情况，通过后台向InnoDB缓冲池写。但是，如果需要读或创建页，并且没有干净的页可用，则它还需要先等待页面清空。该计数器对等待实例进行记数。如果已经适当设置缓冲池大小，该值应小。
Innodb_buffer_pool_write_requests	Global	向InnoDB缓冲池的写数量。
Innodb_data_fsyncs	Global	fsync()操作数。
Innodb_data_pending_fsyncs	Global	当前挂起的fsync()操作数。
Innodb_data_pending_reads	Global	当前挂起的读数。
Innodb_data_pending_writes	Global	当前挂起的写数。
Innodb_data_read	Global	至此已经读取的数据数量（字节）。
Innodb_data_reads	Global	数据读总数量。
Innodb_data_writes	Global	数据写总数量。
Innodb_data_written	Global	至此已经写入的数据量（字节）。
Innodb_dblwr_pages_written	Global	已经执行的双写操作数量
Innodb_dblwr_writes	Global	双写操作已经写好的页数
Innodb_log_waits	Global	我们必须等待的时间，因为日志缓冲区太小，我们在继续前必须先等待对它清空
原文地址
Innodb_log_write_requests	Global	日志写请求数。
Innodb_log_writes	Global	向日志文件的物理写数量。
Innodb_os_log_fsyncs	Global	向日志文件完成的fsync()写数量。
Innodb_os_log_pending_fsyncs	Global	挂起的日志文件fsync()操作数量。
Innodb_os_log_pending_writes	Global	挂起的日志文件写操作
Innodb_os_log_written	Global	写入日志文件的字节数。
Innodb_page_size	Global	编译的InnoDB页大小(默认16KB)。许多值用页来记数；页的大小很容易转换为字节。
Innodb_pages_created	Global	创建的页数。
Innodb_pages_read	Global	读取的页数。
Innodb_pages_written	Global	写入的页数。
Innodb_row_lock_current_waits	Global	当前等待的待锁定的行数。
Innodb_row_lock_time	Global	行锁定花费的总时间，单位毫秒。
Innodb_row_lock_time_avg	Global	行锁定的平均时间，单位毫秒。
Innodb_row_lock_time_max	Global	行锁定的最长时间，单位毫秒。
Innodb_row_lock_waits	Global	一行锁定必须等待的时间数。
Innodb_rows_deleted	Global	从InnoDB表删除的行数。
Innodb_rows_inserted	Global	插入到InnoDB表的行数。
Innodb_rows_read	Global	从InnoDB表读取的行数。
Innodb_rows_updated	Global	InnoDB表内更新的行数。
Key_blocks_not_flushed	Global	键缓存内已经更改但还没有清空到硬盘上的键的数据块数量。
Key_blocks_unused	Global	键缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
Key_blocks_used	Global	键缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块。
Key_read_requests	Global	从缓存读键的数据块的请求数。
Key_reads	Global	从硬盘读取键的数据块的次数。如果Key_reads较大，则Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests计算缓存损失率。
Key_write_requests	Global	将键的数据块写入缓存的请求数。
Key_writes	Global	向硬盘写入将键的数据块的物理写操作的次数。
Last_query_cost	Session	用查询优化器计算的最后编译的查询的总成本。用于对比同一查询的不同查询方案的成本。默认值0表示还没有编译查询。 默认值是0。Last_query_cost具有会话范围。
Max_used_connections	Global	服务器启动后已经同时使用的连接的最大数量。
ndb*		ndb集群相关
Not_flushed_delayed_rows	Global	等待写入INSERT DELAY队列的行数。
Open_files	Global	打开的文件的数目。
Open_streams	Global	打开的流的数量(主要用于记录)。
Open_table_definitions	Global	缓存的.frm文件数量
Open_tables	Both	当前打开的表的数量。
原文地址:http://www.sandzhang.com/blog/2010/04/07/mysql-show-status-explained-detail/
Opened_files	Global	文件打开的数量。不包括诸如套接字或管道其他类型的文件。 也不包括存储引擎用来做自己的内部功能的文件。
Opened_table_definitions	Both	已经缓存的.frm文件数量
Opened_tables	Both	已经打开的表的数量。如果Opened_tables较大，table_cache 值可能太小。
Prepared_stmt_count	Global	当前的预处理语句的数量。 (最大数为系统变量: max_prepared_stmt_count)
Qcache_free_blocks	Global	查询缓存内自由内存块的数量。
Qcache_free_memory	Global	用于查询缓存的自由内存的数量。
Qcache_hits	Global	查询缓存被访问的次数。
Qcache_inserts	Global	加入到缓存的查询数量。
Qcache_lowmem_prunes	Global	由于内存较少从缓存删除的查询数量。
Qcache_not_cached	Global	非缓存查询数(不可缓存，或由于query_cache_type设定值未缓存)。
Qcache_queries_in_cache	Global	登记到缓存内的查询的数量。
Qcache_total_blocks	Global	查询缓存内的总块数。
Queries	Both	服务器执行的请求个数，包含存储过程中的请求。
Questions	Both	已经发送给服务器的查询的个数。
Rpl_status	Global	失败安全复制状态(还未使用)。
Select_full_join	Both	没有使用索引的联接的数量。如果该值不为0,你应仔细检查表的索引
Select_full_range_join	Both	在引用的表中使用范围搜索的联接的数量。
Select_range	Both	在第一个表中使用范围的联接的数量。一般情况不是关键问题，即使该值相当大。
Select_range_check	Both	在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为0，你应仔细检查表的索引。
Select_scan	Both	对第一个表进行完全扫描的联接的数量。
Slave_heartbeat_period	Global	复制的心跳间隔
Slave_open_temp_tables	Global	从服务器打开的临时表数量
Slave_received_heartbeats	Global	从服务器心跳数
Slave_retried_transactions	Global	本次启动以来从服务器复制线程重试次数
Slave_running	Global	如果该服务器是连接到主服务器的从服务器，则该值为ON。
Slow_launch_threads	Both	创建时间超过slow_launch_time秒的线程数。
Slow_queries	Both	查询时间超过long_query_time秒的查询的个数。
Sort_merge_passes	Both	排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。
Sort_range	Both	在范围内执行的排序的数量。
Sort_rows	Both	已经排序的行数。
Sort_scan	Both	通过扫描表完成的排序的数量。
ssl＊		ssl连接相关
Table_locks_immediate	Global	立即获得的表的锁的次数。
Table_locks_waited	Global	不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
Threads_cached	Global	线程缓存内的线程的数量。
Threads_connected	Global	当前打开的连接的数量。
Threads_created	Global	创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running	Global	激活的（非睡眠状态）线程数。
Uptime	Global	服务器已经运行的时间（以秒为单位）。
Uptime_since_flush_status	Global	最近一次使用FLUSH STATUS 的时间（以秒为单位）。

sqlserver sql 数据库 云数据库SQLServer 缓存

0 人点赞