[数据库]MySql系统架构

参考资料
[1] MySQL体系架构简介
[2] MySQL 整体架构一览

1. 路径

路径	解释	备注
/var/lib/mysql/	mysql数据库文件的存放路径	/var/lib/mysql/LAPTOP-L6PSTN0S.pid
/usr/share/mysql	配置文件目录	mysql.server命令及其配置文件
/usr/bin	相关命令目录	mysqldump等命令
/etc/init.d/mysql	启停相关脚本

2. 文件

• frm文件：存放表结构
• myd文件：存放表数据
• myi文件：存放表索引

3. 配置

• auto.cnf: 配置了MySQL Server的UUID
• my.cnf: MySQL的配置文件

4. 逻辑系统架构

分为3层:

• 应用层
• MySQL服务层
• 存储引擎层
  

4.1 应用层

应用层是MySQL体系架构的最上层，它可其他client-server架构一样，主要包含如下内容:

• 连接处理
• 用户鉴权
• 安全管理

1. 连接处理当一个客户端向服务端发送连接请求后，MySQL server会从线程池中分配一个线程来和客户端进行连接，以后该客户端的请求都会被分配到该线程上。MySQL Server为了提高性能，提供了线程池，减少了创建线程和释放线程所花费的时间。
   负责和客户端建立连接，获取用户权限以及维持和管理连接。
   通过 show processlist; 来查询连接的状态。在用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为 8 小时，超过时间后将会被断开。

简单说下长连接：
优势：在连接时间内，客户端一直使用同一连接，避免多次连接的资源消耗。
劣势：在 MySQL 执行时，使用的内存被连接对象管理，由于长时间没有被释放，会导致系统内存溢出，被系统kill. 所以需要定期断开长连接，或执行大查询后，断开连接。MySQL 5.7 后，可以通过 mysql_rest_connection 初始化连接资源，不需要重连或者做权限验证.

2. 用户鉴权 当客户端向MySQL服务端发起连接请求后，MySQL server会对发起连接的用户进行鉴权处理，MySQL鉴权依据是: 用户名，客户端主机地址和用户密码

3. 安全管理 当客户连接到MySQL server后，MySQL server会根据用户的权限来判断用户具体可执行哪些操作。MySQL 提供的部分权限的如下:

mysql> show privileges \G;
*************************** 1. row ***************************
Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
  Context: Databases,Tables,Indexes
  Comment: To create new databases and tables

4.2 MySQL 服务层

该层是MySQL Server的核心层，提供了MySQL Server数据库系统的所有逻辑功能，该层可以分为如下不同的组件:

• MySQL Management Server & utilities(系统管理)
• SQL Interface(SQL 接口)
• SQL Parser(SQL 解析器)
• Optimizer (查询优化器)
• Caches & buffers(缓存)

1. MySQL Management Server & utilities(系统管理) 提供了丰富的数据库管理功能，具体如下:
   – 数据库备份和恢复
   – 数据库安全管理，如用户及权限管理
   – 数据库复制管理
   – 数据库集群管理
   – 数据库分区，分库，分表管理
   – 数据库元数据管理
2. SQL Interface(SQL 接口) SQL接口，接收用户的SQL命令并进行处理，得到用户所需要的结果，具体处理功能如下:
   – Data Manipulation Language (DML).
   – Data Definition Language (DDL).
   – 存储过程
   – 视图
   – 触发器
3. SQL Parser(SQL 解析器) 解析器的作用主要是解析查询语句，最终生成语法树。首先解析器会对查询语句进行语法分析，如果语句语法有错误，则返回相应的错误信息。语法检查通过后，解析器会查询缓存，如果缓存中有对应的语句，就直接返回结果不进行接下来的优化执行操作。
   词法分析：如识别 select，表名，列名，判断其是否存在等。
   语法分析：判断语句是否符合 MySQL 语法。

注：读者会疑问，从缓存中查出来的数据会不会被修改，与真实的数据不一致，这里读者大可放心，因为缓存中数据被修改，会被清出缓存。

4. Optimizer(查询优化器) 优化器的作用主要是对查询语句进行优化，包括选择合适的索引，数据的读取方式.
   确定索引的使用，join 表的连接顺序等，选择最优化的方案。
5. Caches & buffers(缓存) 包括全局和引擎特定的缓存，提高查询的效率。如果查询缓存中有命中的查询结果，则查询语句就可以从缓存中取数据，无须再通过解析和执行。这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等。当接受到查询请求时，会现在查询缓存中查询（key/value保存），是否执行过。没有的话，再走正常的执行流程。但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时，缓存就会被清空。所以适用于静态表。在 MySQL8.0 后，查询缓存被废除。
6. 执行器
   在具体执行语句前，会先进行权限的检查，通过后使用数据引擎提供的接口，进行查询。如果设置了慢查询，会在对应日志中看到 rows_examined 来表示扫描的行数。在一些场景下（索引），执行器调用一次，但在数据引擎中扫描了多行，所以引擎扫描的行数和 rows_examined 并不完全相同。

4.3 存储引擎层

1. 存储引擎 存储引擎是MySQL中具体与文件打交道的子系统，也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎）。

2. 物理文件 物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等

5. 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 -u$user-p$passwd

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

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

系统的连接：

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

长连接和短连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

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

• 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
• 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，之前的缓存结果之后也可以查询到

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）在procedure，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：

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 验证权限)。

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

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

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

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

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

//初始化全表扫描
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 : 删除操作的次数