MySQL架构

发表于 更新于 分类于 阅读次数： Waline： 本文字数： 11k 阅读时长 ≈ 10 分钟

问大家一个问题。SELECT * FROM t WHERE id = 1 ; 这个语句是怎么执行的。

我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在MySQL内部的执行过程。

今天我想和你一起把 MySQL 拆解一下，看看里面都有哪些“零件”

下面是 MySQL 的基本架构示意图
MySQL逻辑架构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等。

存储引擎层，负责数据的存储和提取。

(1) MySQL基本架构

MySQL架构里主要包含 连接器、查询缓存、分析器、优化器、执行器、存储引擎等模块。

(1.1) 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
mysql -h 127.0.0.1 -P 3306 -u admin -p

(1.2) 查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

(1.3) 分析器

执行语句时，分析器先会做“词法分析”、语法分析、句法分析
select * form t where id=1;

(1.4) 优化器

select * from t where username = ‘test123’ and phone = ‘18812341234’ and age = 18 and sex = 1 ;
假设username、phone、age、sex都有索引，走区分度大的索引

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
小表驱动大表

(1.5) 执行器

鉴权+执行语句
select * from t where id =10;
ERROR 1142 (42000): SELECT command denied to user ‘b‘@’localhost’ for table ‘t’

假设id字段没有索引，执行器的执行流程是这样的：

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

(2)　连接器模块详解

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接命令： mysql -h $ip -P $port -u $user -p

[weikeqin@weikeqin ~ ]$mysql -h 127.0.0.1 -P 3306 -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

连接命令中的mysql是客户端工具，用来跟服务端建立连接。
在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

(2.1) 连接时密码错误

[weikeqin@weikeqin ~ ]$mysql -h 127.0.0.1 -P 3306 -u admin -p
Enter password:
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
[weikeqin@weikeqin ~ ]$

如果用户名或密码不对，你就会收到一个ERROR 1045 (28000): Access denied for user的错误，然后客户端程序结束执行。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

(2.2) 连接状态

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

mysql> show processlist ;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host            | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 15 | root        | localhost:49914 | NULL | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.00 sec)

(2.3) 连接失效

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
在用的时候一般会在程序的配置文件里配置 validationQuery=SELECT 1 就是因为这个原因。

mysql>   show databases;
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Enter password:
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: NO)
ERROR:
Can't connect to the server

mysql>

如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：ERROR 4031 (HY000): The client was disconnected by the server because of inactivity.。这时候如果你要继续，就需要重连，然后再执行请求了。

(2.4) 长连接内存问题

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

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

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

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

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

(3) 查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

(3.1) 查询缓存更新策略及命中率

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。

比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

(3.2) 手动指定使用查询缓存

好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

(4) 分析器

MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”。

mysql>  elect * from test_user where id = 1 ;
ERROR 1064 (42000): 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 'elect * from test_user where id = 1' at line 1
mysql>

(5) 优化器

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

(5.1) 多个索引-使用区分度大的索引

(5.2) 多表关联-小表驱动大表

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

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

(6) 执行器

鉴权+执行语句

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

mysql> select * from T where ID=10;

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

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

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

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此 引擎扫描行数跟 rows_examined 并不是完全相同的。

(7) 总结-各个模块的功能

1，连接
连接管理模块，接收请求；连接进程和用户模块，通过，连接线程和客户端对接
2，查询
查询缓存 Query Cache
分析器，内建解析树，对其语法检查，先from，再on，再join，再where……；检查权限，生成新的解析树，语义检查（没有字段k在这里）等
优化器，将前面解析树转换成执行计划，并进行评估最优
执行器，获取锁，打开表，通过meta数据，获取数据
3，返回结果
返回给连接进程和用户模块，然后清理，重新等待新的请求。

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

存储引擎层，负责数据的存储和提取。每个存储引擎都有它的优势和劣势。
服务器通过API与存储引擎层通信。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

(8) 牛刀小试

(8.1) 面试题

  1. MySQL的框架有几个组件, 各是什么作用?
  2. Server层和存储引擎层各是什么作用?
  3. you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?
  4. 对于表的操作权限验证在哪里进行?
  5. 执行器的执行查询语句的流程是什么样的?

(8.2) 如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答案是 分析器

办法：

安装完MySQL之后,使用Debug模式启动
mysqld --debug --console &后，
mysql> create database wxb;
Query OK, 1 row affected (0.01 sec)

mysql> use wxb;
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t where k=1;
ERROR 1054 (42S22): Unknown column 'k' in 'where clause'

T@4: | | | | | | | | | error: error: 1054 message: 'Unknown column 'k' in 'where clause''

Complete optimizer trace:
答案就很清楚了

(8.4) 动画描述各个模块的功能

连接器：门卫，想进请出示准入凭证（工牌、邀请证明一类）。“你好，你是普通员工，只能进入办公大厅，不能到高管区域”此为权限查询。
分析器：“您需要在公司里面找一张头发是黑色的桌子？桌子没有头发啊！臣妾做不到”
优化器：“要我在A B两个办公室找张三和李四啊？那我应该先去B办公室找李四，然后请李四帮我去A办公室找张三，因为B办公室比较近且李四知道张三具体工位在哪”
执行器：“好了，找人的计划方案定了，开始行动吧，走你！糟糕，刚门卫大哥说了，我没有权限进B办公室”

(8.5) 为什么对权限的检查不在优化器之前做？

有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的

(8.6) 在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

(8.7) 假如要插入10万条数据，怎么写插入语句比较好

批量插入 insert into t (id, c) values (1,1), (2, 2)

(8.8) 假如要更新10万条数据，怎么写插入语句比较好

批量更新

(8.9) 详细的执行过程

1.　首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface

2.　SQL interface接到该请求后，先对该条语句进行解析，验证权限是否匹配

3.　验证通过以后，分析器会对该语句分析,是否语法有错误等

4.　接下来是优化器器生成相应的执行计划，选择最优的执行计划

5.　之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL，则等待。如果没有，则加在该表上加短暂的MDL(S)。(如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)

6.　进入到引擎层，首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息

7.　通过元数据信息,去lock info里查出是否会有相关的锁信息，并把这条update语句需要的锁信息写入到lock info里(锁这里还有待补充)

8.　然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo (如果data page里有就直接载入到undo page里，如果没有，则需要去磁盘里取出相应page的数据，载入到undo page里)

9.　在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里。由于update这个事务会涉及到多个页面的修改，所以redo log buffer里会记录多条页面的修改信息。因为group commit的原因，这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上

10.　同时修改的信息，会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,一旦commit之后，dump线程会从binlog_cache里把event主动发送给slave的I/O线程)

11.　之后把这条sql,需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge (随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)

12.　此时update语句已经完成，需要commit或者rollback。这里讨论commit的情况

13.　commit操作，由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),所以提交分为prepare阶段与commit阶段

14.　prepare阶段,将事务的xid写入，将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)

15.　commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit

16.　当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作，先把该脏页复制到doublewrite buffer里，把doublewrite buffer里的刷新到共享表空间，然后才是通过page cleaner线程把脏页写入到磁盘中

(8.10) 写redo日志也是写io（我理解也是外部存储）。同样耗费性能。怎么能做到优化呢

1.写redo日志也是写io（我理解也是外部存储）。同样耗费性能。怎么能做到优化呢
2.数据库只有redo commit 之后才会真正提交到数据库吗

  1. Redolog是顺序写，并且可以组提交，还有别的一些优化，收益最大是是这两个因素；
  2. 是这样，正常执行是要commit 才算完，但是崩溃恢复过程的话，可以接受“redolog prepare 并且binlog完整” 的情况

(8.11) redo log是为了快速响应SQL充当了粉板

　1. redo log本身也是文件，记录文件的过程其实也是写磁盘，那和文中提到的离线写磁盘操作有何区别？
　2.响应一次SQL我理解是要同时操作两个日志文件？也就是写磁盘两次？

  1. 写redo log是顺序写，不用去“找位置”，而更新数据需要找位置
  2. 其实是3次（redolog两次 binlog 1次）。不过在并发更新的时候会合并写

(8.12) binlog为什么说是逻辑日志呢？它里面有内容也会存储成物理文件，怎么说是逻辑而不是物理

这样理解

逻辑日志可以给别的数据库，别的引擎使用，已经大家都讲得通这个“逻辑”；

物理日志就只有“我”自己能用，别人没有共享我的“物理格式”

(8.13) redo log 和 bin log 是否重复

redo 是引擎提供的，binlog 是server 自带的，文中提到前者用在crash的恢复，后者用于库的恢复。两者是否在某种程度上是重复的？如果在都是追加写的情况下，是否两种日志都能用于 crash 与 库 的恢复呢？
Crash-safe是崩溃恢复，就是原地满血复活；binlog恢复是制造一个影分身（副本）出来。

(8.14)

  1. 如果把 innodb_flush_log_at_trx_commit 设置成1每次都写入到磁盘，那不就等于是掌柜的每次记账都记到账本上嘛，那还要小黑板干嘛呢？

  2. binlog是逻辑，redolog是物理，两者都能记录历史，如果发生异常情况binlog就可以恢复数据，为什么说只有redolog才能算是crash-safe了呢。

  3. Redolog是顺序写，数据文件是随机写。虽然都写盘，顺序写还是快很多的

(8.15)

1、首先数据库更新操作都是基于内存页，更新的时候不会直接更新磁盘，如果内存有存在就直接更新内存，如果内存没有存在就从磁盘读取到内存，在更新内存，并且写redo log，目的是为了更新效率更快，等空闲时间在将其redo log所做的改变更新到磁盘中，innodb_flush_log_at_trx_commit设置为1时，也可以防止服务出现异常重启，数据不会丢失

2、redo log两阶段提交，是为了保证redo log和binlog的一致性，如果redo log写入成功处于prepare阶段，写binlog失败，事务回滚，redo log会回滚到操作之前的状态

3、redo log也是写磁盘，写redo log是顺序写，update直接更新磁盘，需要找到数据，再对此数据进行更新(随机写)。

(8.16)

开启了半同步复制after_sync以后，假设一个事务在已经把binlog sync到磁盘了,在传输binlog到从库上时,主库挂了。如果这时发生了主从切换,从库是没有这个事务的.但是挂掉的主库重新启起来,由于binlog已经fsync到磁盘,虽然引擎层未commit,但是会根据binlog来恢复这个事务.这个时候就是主从不一致了,那么和after_commit那就没区别呀。请问这个事务是恢复还是回滚呢？如果是回滚,内部又是怎么判断的呢？

如果你设置来双MM，启动以后binlog还会传到新主库的。
但是如果你设置的是单向的，那就会不一致了…

References

[1] 01 | 基础架构：一条SQL查询语句是如何执行的？ - MySQL实战45讲
[2] 02 | 日志系统：一条SQL更新语句是如何执行的？ - MySQL实战45讲
[3] 《高性能MySQL》 O’REILLY