【MySQL】运行原理（一）：查询sql的执行过程及MySQL架构分析

MySQL的发展历史和版本分支：

时间	里程碑
1996 年	MySQL1.0 发布。它的历史可以追溯到 1979 年，作者 Monty 用 BASIC 设计的一个报表工具。
1996 年 10 月	3.11.1 发布。MySQL 没有 2.x 版本。
2000 年	ISAM 升级成 MyISAM 引擎。MySQL 开源。
2003 年	MySQL4.0 发布，集成 InnoDB 存储引擎
2005 年	MySQL5.0 版本发布，提供了视图、存储过程等功能。
2008 年	MySQLAB 公司被 Sun 公司收购，进入 SunMySQL 时代。
2009 年	Oracle 收购 Sun 公司，进入 OracleMySQL 时代。
2010 年	MySQL5.5 发布，InnoDB 成为默认的存储引擎。
2016 年	MySQL 发布 8.0.0 版本。为什么没有 6、7？5.6 可以当成 6.x，5.7 可以当 成 7.x。

因为MySQL是开源的（也有收费版本），所以在MySQL稳定版本的基础上也发展出来了很多的分支，就像Linux一样，有Ubuntu、RedHat、CentOS、 Fedora 、Debian等等。大家最熟悉的应该是MariaDB，因为CentOS7里面自带了一个MariaDB。它是怎么来的呢？Oracle收购MySQL之后，MySQL创始人之一Monty担心MySQL数据库发展的未来（开发缓慢，封闭，可能会被闭源），就创建了一个分支MariaDB，默认使用全新的Maria存储引擎，它是原MyISAM存储引擎的升级版本。

其他流行分支：

• Percona Server 是MySQL重要的分支之一，它基于InnoDB存储引擎的基础上，提升了性能和易管理性，最后形成了增强版的XtraDB引擎，可以用来更好地发挥服务器硬件上的性能。
• 国内也有一些MySQL的分支或者自研的存储引擎，比如网易的InnoSQL，极数云舟的ArkDB。

我们操作数据库有各种各样的方式，比如Linux 系统中的命令行，比如数据库工具Navicat，比如程序，例如Java语言的JDBC API或者ORM框架。但大家有没有思考过，当我们的工具或者程序连接到数据库之后，实际上发生了什么事情？它的内部是怎么工作的？

---

下面以数据查询为例，来看下MySQL的工作流程是什么样的：

我们的程序或者工具要操作数据库，第一步要做什么事情？跟数据库建立连接。

1.与数据库建立连接

首先，MySQL必须要运行一个服务，监听默认的3306端口。在我们开发系统跟第三方对接的时候，必须要弄清楚的有两件事。

• 第一个就是通信协议，比如我们是用HTTP还是WebService还是TCP？
• 第二个是消息格式，比如我们用XML格式，还是JSON格式，还是定长格式？报文头长度多少，包含什么内容，每个字段的详细含义？

1.1 通信类型和连接方式

MySQL是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。这里我们拆分来看。

通信类型：同步或者异步

• 同步通信的特点：

  • 同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回
  • 一般只能做到一对一，很难做到一对多的通信

• 异步跟同步相反：

  • 异步可以避免应用阻塞等待，但是不能节省SQL执行的时间
  • 如果异步存在并发，每一个SQL的执行都要单独建立一个连接，避免数据混乱。但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量CPU资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接

  一般来说我们连接数据库都是同步连接。

连接方式：长连接或者短连接

MySQL既支持短连接，也支持长连接。短连接就是操作完毕以后，马上close 掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。

保持长连接会消耗内存。长时间不活动的连接，MySQL服务器会断开。那这个超时时间怎么查看呢？

show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序 
show global variables like' interactive_timeout'; -- 交互式超时时间，如数据库工具

执行结果如下图。默认都是28800秒，8小时

---

我们怎么查看MySQL当前有多少个连接？可以用show status命令：

show global status like 'Thread%';

• Threads_cached：缓存中的线程连接数
• Threads_connected：当前打开的连接数
• Threads_created：为处理连接创建的线程数
• Threads_running：非睡眠状态的连接数，通常指并发连接数

---

有了连接数，怎么知道当前连接的状态？可以使用show processlist命令，（root用户）查看SQL的执行状态。

SHOW PROCESSLIST; 

从上面的Threads_connected可以看到当前有4个连接，所以这里的显示了4个连接状态。那Command这一列是什么意思呢？一些常见的状态：

状态	含义
Sleep	线程正在等待客户端，以向它发送一个新语句
Query	线程正在执行查询或往客户端发送数据
Locked	该查询被其它查询锁定
Copying to tmp tableondisk	临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改 变为磁盘模式，以节约存储器
Sendingdata	线程正在为 SELECT 语句处理行，同时正在向客户端发送数据
Sortingforgroup	线程正在进行分类，以满足 GROUPBY 要求
Sortingfororder	线程正在进行分类，以满足 ORDERBY 要求

---

还有个问题，MySQL服务允许的最大连接数是多少呢？

show variables like 'max_connections';

在5.7版本中默认是151个，最大可以设置成16384（2^14）。

set global max_connections=1000;

1.2 MySQL支持哪些通信协议

第一种是Unix Socket。比如我们在Linux服务器上，如果没有指定-h参数，它就用socket方式登录。如下图（省略
了-S /var/lib/mysql/mysql.sock）

它不用通过网络协议，也可以连接到MySQL的服务器，它需要用到服务器上的一个物理文件（/var/lib/mysql/mysql.sock）

select @@socket;

---

如果有参数-h指定主机，就会用第二种方式，TCP/IP协议。

mysql -h192.168.8.211 -uroot -p123456

我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如 mysql-connector-java-x.x.xx.jar。

1.3 MySQL采用什么通信方式

通信方式分为以下三种：

• 单工：在两台计算机通信的时候，数据的传输是单向的。比如遥控器
• 半双工：在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发的话，也必须等我发给你完了之后才能给我发。比如对讲机
• 全双工：数据的传输是双向的，并且可以同时传输。比如打电话

那MySQL应该采用哪种通信方式呢？半双工的通信方式，因为客户端与服务端肯定是双向通信的，而且要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。

客户端发送SQL语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的SQL语句有多大，都是一次性发送。比如我们用MyBatis动态SQL生成了一个批量插入的语句， 插入10万条数据， values后面跟了一长串的内容，或者where条件in里面的值太多，会出现问题。这个时候我们必须要调整MySQL服务器配置 max_allowed_packet 参数的值（默认是4M），把它调大，否则就会报错。

另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗。所以，我们一定要在程序里面避免不带limit 的这种操作，比如一次把所有满足条件的数据全部查出来，一定要先count一下。如果数据量的话，可以分批查询。

执行一条查询语句，客户端跟服务端建立连接之后呢？下一步要做什么？

2.查询缓存

MySQL内部自带了一个缓存模块。缓存的作用我们应该很清楚了，把数据以KV的形式放到内存里面，可以加快数据的读取速度，也可以减少服务器处理的时间。但是MySQL的缓存我们好像比较陌生，从来没有去配置过，也不知道它什么时候生效？假如 t_user 表有500万行数据，没有索引。我们在没有索引的字段上执行同样的查询，大家觉得第二次会快吗？

可以看到MySQL缓存是默认关闭的。默认关闭的意思就是不推荐使用，为什么MySQL不推荐使用它自带的缓存呢？

主要是因为MySQL自带的缓存的应用场景有限，需要满足以下两个要求：

• 它要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。
• 表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

所以缓存这一块，我们还是交给ORM框架（比如MyBatis默认开启了一级缓存），或者独立的缓存服务，比如Redis来处理更合适。在MySQL 8.0中，查询缓存已经被移除了。

我们没有使用缓存的话，就会跳过缓存的模块，下一步我们要做什么呢？

3. 语法解析和预处理

这里我会有一个疑问，为什么我的一条SQL语句能够被识别呢？假如我随便执行一个字符串penyuyan，服务器报了一个1064的错：

它是怎么知道我输入的内容是错误的？这个就是MySQL的Parser解析器和Preprocessor预处理模块。这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。

3.1 词法分析

词法分析就是把一个完整的SQL语句打碎成一个个的单词。比如一个简单的SQL语句：

select username from t_user where id = 1;

它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。

3.2 语法分析

第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合，然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。

任何数据库的中间件，比如 Mycat，Sharding-JDBC（用到了Druid Parser），都必须要有词法和语法分析功能，在市面上也有很多的开源的词法解析的工具（比如LEX，Yacc）。

3.3 预处理器

问题：如果我写了一个词法和语法都正确的SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如：

select * from penyuyan;

解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

4.查询优化得到执行计划

得到解析树之后，是不是执行SQL语句了呢？这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的SQL？

这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是MySQL的查询优化器的模块（Query Optimizer）。

4.1 什么是优化器？

查询优化器的目的就是根据解析树生成不同的执行计划（ExecutionPlan），然后选择一种最优的执行计划。MySQL里面使用的是基于开销（cost）的优化器，哪种执行计划开销最小，就用哪种。可以使用这个命令查看查询的开销：

show status like 'Last_query_cost';

mysql官网关于这里的参考，想了解更多的同学可以看看

4.2 优化器可以做什么？

MySQL的优化器能处理哪些优化类型呢？举两个简单的例子：

1. 当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表
2. 有多个索引可以使用的时候，选择哪个索引

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。如果对于优化器的细节感兴趣，可以看看《数据库查询优化器的艺术-原理解析与SQL性能优化》。

但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写SQL语句的时候还是要注意。

如果我们想知道优化器是怎么工作的，它生成了几种执行计划，每种执行计划的cost是多少，应该怎么做？

4.3 优化器得到执行计划的过程？

首先，我们要启用优化器的追踪（默认是关闭的）：

SHOW VARIABLES LIKE 'optimizer_trace'; 
set optimizer_trace = 'enabled=on';

注意，开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它（改成off）。接着我们执行一个SQL语句，优化器会生成执行计划（下面是一个两表联查）：

SELECT d.username,i.phone from user_info i,user_detail d WHERE i.id=d.user_id;

这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询

select * from information_schema.optimizer_trace\G;

如果是直接在Navicat中执行，那么得到结果只是短短一行数据，并没有完整的执行计划

所以，此处应该是在命令行中执行：

得到的优化器分析的过程是一个JSON类型的数据，主要分成三部分，准备阶段、优化阶段和执行阶段。

那具体的优化计划在哪呢？在优化阶段（“join_optimization”）中的 considered_execution_plans

最后，分析完记得关掉它：

set optimizer_trace="enabled=off"; 
SHOWVARIABLESLIKE'optimizer_trace';

优化完之后，得到一个什么东西呢？

4.4 如何查看最终执行计划？

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。

当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为MySQL也有可能覆盖不到所有的执行计划。我们怎么查看MySQL的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？

MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息。

5.存储引擎，存储数据

得到执行计划以后，SQL语句是不是终于可以执行了？问题又来了：

1. 从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面？
2. 执行计划在哪里执行？是谁去执行？

5.1 存储引擎基本介绍

我们先回答第一个问题：在关系型数据库里面，数据是放在什么结构里面的？放在表Table里面的，我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。

在MySQL里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。为什么要搞这么多存储引擎呢？一种还不够用吗？这个问题先留着。

5.2 查看存储引擎

比如我们数据库里面已经存在的表，我们怎么查看它们的存储引擎呢？

show table status from `forum`; --forum是指定数据库名

另外，还通过DDL建表语句来查看。

在MySQL里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。

我们说一张表使用的存储引擎决定我们存储数据的结构，那在服务器上它们是怎么存储的呢？我们先要找到数据库存放数据的路径：

show variables like 'datadir';

默认情况下，每个数据库有一个自己文件夹，以forum数据库为例。

任何一个存储引擎都有一个frm文件，这个是表结构定义文件。不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb 是 1 个，memory没有，myisam是两个。

这些存储引擎的差别在哪呢？

5.3 存储引擎比较

MyISAM 和InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前，默认的存储引擎是MyISAM，它是MySQL自带的。我们创建表的时候不指定存储引擎，它就会使用MyISAM作为存储引擎。MyISAM的前身是ISAM（IndexedSequentialAccessMethod：利用索引，顺序存取数据的方法）。

5.5版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。为什么要改呢？最主要的原因还是InnoDB 支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合。

这个里面又有Oracle和MySQL公司的一段恩怨情仇。

InnoDB本来是InnobaseOy公司开发的， 它和MySQLAB公司合作开源了InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司（开发Java语言的Sun）收购了MySQLAB，09年Sun公司又被 Oracle 收购了，所以 MySQL，InnoDB 又是一家了。有人觉得 MySQL 越来越像Oracle，其实也是这个原因。

---

那么除了这两个我们最熟悉的存储引擎，数据库还支持其他哪些常用的存储引擎呢？我们可以用这个命令查看数据库对存储引擎的支持情况：

show engines;

其中有存储引擎的描述和对事务、XA协议和Savepoints的支持。

• XA协议用来实现分布式事务（分为本地资源管理器，事务管理器）。
• Savepoints用来实现子事务（嵌套事务）。创建了一个Savepoints之后，事务就可以回滚到这个点，不会影响到创建Savepoints之前的操作。

---

这些数据库支持的存储引擎，分别有什么特性呢？

• MyISAM（3 个文件）

  应用范围比较小。表级锁定限制了读/写的性能，因此在Web 和数据仓库配置中，它通常用于只读或以读为主的工作。

  • 支持表级别的锁（插入和更新会锁表）。不支持事务

  • 拥有较高的插入（insert）和查询（select）速度

  • 存储了表的行数（count速度更快）。（怎么快速向数据库插入100万条数据？我们有一种先用MyISAM插入数据，然后修改存储引擎为InnoDB的操作）

  • 适用：只读之类的数据分析的项目

• InnoDB（2 个文件）

  mysql5.7中的默认存储引擎。 InnoDB是一个事务安全（与ACID兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。

  • 支持事务，支持外键，因此数据的完整性、一致性更高

  • 支持行级别的锁和表级别的锁

  • 支持读写并发，写不阻塞读（MVCC）

  • 特殊的索引存放方式，可以减少IO，提升查询效率

  • 适用：经常更新的表，存在并发读写或者有事务处理的业务系统

• Memory（1 个文件）

  将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少； InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbcluster为大型分布式数据集提供了快速的键值查找。

  • 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。
  • 将表中的数据存储到内存中

• CSV（3 个文件）

  它的表实际上是带有逗号分隔值的文本文件。 csv表允许以csv格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为csv 表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入或导出阶段使用csv表。

  不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。

• Archive（2 个文件）

  这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。

  不支持索引，不支持update delete.

这是MySQL里面常见的一些存储引擎，我们看到了，不同的存储引擎提供的特性都不一样，它们有不同的存储机制、索引方式、锁定水平等功能。我们在不同的业务场景中对数据操作的要求不同，就可以选择不同的存储引擎来满足我们的需求，这个就是MySQL支持这么多存储引擎的原因。

5.4 如何选择存储引擎？

• 如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
• 如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
• 如果需要一个用于查询的临时表，可以选择Memory。
• 如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎

6.执行引擎，返回结果

OK，存储引擎分析完了，它是我们存储数据的形式，继续第二个问题，是谁使用执行计划去操作存储引擎呢？这就是我们的执行引擎，它利用存储引擎提供的相应的API来完成操作。为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的API是相同的。

最后把数据返回给客户端，即使没有结果也要返回。

==> MySQL架构

基于上面分析的流程，我们一起来梳理一下MySQL的内部模块。

1.模块详解

1. Connector：用来支持各种语言和SQL的交互，比如PHP，Python，Java的 JDBC
2. Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL复制、集群等等
3. Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等
4. SQL Interface：用来接收用户的SQL命令，返回用户需要的查询结果
5. Parser：用来解析SQL语句
6. Optimizer：查询优化器
7. CacheandBuffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key缓存，权限缓存等等
8. Pluggable Storage Engines：插件式存储引擎，它提供API给服务层使用，跟具体的文件打交道

2.架构分层

总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层（参考MyBatis：接口、核心、基础）。

1. 连接层

   我们的客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。

2. 服务层

   连接层会把SQL语句交给服务层，这里面又包含一系列的流程：比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。

   然后就是优化器，MySQL底层会根据一定的规则对我们的 SQL语句进行优化，最后再交给执行器去执行。

3. 存储引擎

   存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储引擎。再往下就是内存或者磁盘。