MySQL的内部组件结构

MySQL的组件结构图

MySQL可以分为Server层和存储引擎层两部分。

Server层

Server层主要包含：连接器、查询缓存、词法分析器、优化器、执行器。涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如：日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

连接器

MySQL客户端要向MySQL发起通信必须需要与服务端建立通讯连接，而通讯连接的工作是由连接器来完成的，连接器负责和客户端建立连接、获取权限、维持和管理连接。客户端可执行下面命令来与服务端进行连接：

mysql -h host[数据库地址] -u root[用户名] -p root[密码] -P 3306

1. 如果连接用户名或密码不对，会收到一个“Access denied for user” 的错误，然后客户端程序结束执行。
2. 如果连接用户名密码认证通过，连接器会查询权限表查出连接用户拥有的权限，之后连接里面的权限判断逻辑都将依赖于此时读到的权限。 这意味着，一个用户连接成功建立之后，即使用管理员的账号对该用户的权限进行了修改，也不会影响到已经建立连接的权限，只有重新建立的连接才会拥有修改后的权限。

连接建立完成后，如果没有后续的操作动作，这个连接就处于空闲状态，可执行show processlist 命令查看，客户端如果长时间不发送command到服务段，连接器就会自动将它断开，这个时间可以通过配置参数wait_timeout控制，默认是8小时。

如果客户端在连接断开之后再次发送了请求操作，就会收到一个错误的提醒 "Lost connection to MySQL server during query"

在开发中，客户端与服务端的连接放在Pool内进行管理，长连接有时候会导致MySQL占用内存增加特别快，因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开时才释放，所以如果长连接积累下来，可能导致内存占用太大，被系统强制kill（OOM），重现象上看就是MySQL异常重启了。

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

查询缓存

连接器建立完成后，执行select查询语句，先会到查询缓存看看之前是不是执行过这条查询语句，查询缓存会以key-value对的形式存储在内存中，key是查询的语句，value是查询的结果。如果查询缓存中不存在，会进行后续的执行操作，执行完成后执行结果会被存入查询缓存中。

查询缓存往往是弊大于利，查询缓存失效非常频繁，只要有对表数据更新，这个表上的所有查询缓存都会被清空，所以查询缓存的命中率非常低。

查看当前MySQL实例是否开启缓存机制

mysql> show global variables like "%query_cache_type%";

监控查询缓存的命中率

mysql> show status like "%Qcache%";

• Qcache_free_blocks：表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。

• Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。

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

• Qcache_inserts： 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

• Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。

• Qcache_not_cached： 表示因为query_cache_type的设置而没有被缓存的查询数量。

• Qcache_queries_in_cache：当前缓存中缓存的查询数量。

• Qcache_total_blocks：当前缓存的block数量。

分析器

MySQL真正开始执行SQL语句，首先会对SQL语句进行解析（语法解析）操作，在分析器阶段，MySQL需要识别出执行的SQL语句里面的字符串分别是什么，代表什么。如：语句操作类型 select、insert、update、delete等，识别操作表名，识别操作表字段等。

词法分析器

词法分析器主要的分析步骤：

• 词法分析

• 语法分析

• 语义分析

• 构造执行树

• 生成执行计划

• 计划的执行

SQL语句的分析分为词法分析与语法分析，mysql的词法分析由MySQLLex[MySQL自己实现的]完成，语法分析由Bison生成。经过Bison语法分析之后，会生成一个这样的语法树。

优化器

优化器是在表里有多个索引的时候决定使用哪个索引，或者在一个语句有多表关联（join）的时候决定各个表的连接顺序。

执行器

优化器阶段完成后，SQL语句就进入了执行阶段，在开始执行时，首先先判断一下操作的表对连接用户有没有权限操作，如果没有就会返回权限错误，有则执行器会根据表的引擎定义，去使用这个引擎提供的接口。

引擎层

存储引擎层负责数据的存储和提取，其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎式InnoDB，它从MySQL5.5.5版本开始成为了默认的存储引擎。也就是说如果在create table时不指定表的存储引擎类型，默认会设置存储引擎为InnoDB。

bin-log 归档

SQL执行时，会将SQL语句的执行逻辑记录在bin-log文档中，bin-log是Server层实现的二进制日志，会记录cud操作。Binlog的特点有以下几个：

• Binlog在MySQL的Server实现

• Binlong为逻辑日志，记录的是一条语句的原始逻辑

• Binlong不限大小，追加写入，不会覆盖以前的日志

开启MySQL的binlong功能，修改配置文件my.cnf

#配置开启binlog
log-bin=/usr/local/mysql/data/binlog/mysql-bin
#注意5.7以及更高版本需要配置本项：
server-id=123454（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog-format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1

常用命令

#查看bin-log是否开启
mysql> show variables like '%log_bin%'; 
#会多一个最新的bin-log日志
mysql> flush logs; 
#查看最后一个bin-log日志的相关信息
mysql> show master status; 
#清空所有的bin-log日志
mysql> reset master; 
#查看binlog内容
mysql> /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 
#从bin-log恢复数据
#恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
#恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
#恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00"  --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)