我曾踏足山巅，也曾进入低谷，二者都令我受益良多。 -----宝石骑士-塔里克

你有多了解mysql？

说到mysql，相信很多人对他都不陌生，尤其是后端开发和DBA，更是熟悉地不能再熟悉了，什么mysql是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，支持插入式存储引擎等等，这些概念也能脱口而出，但是有多少人能正确的说出mysql中一条sql的执行流程是什么？带着这个疑问，进入这篇文章的主题。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

这是mysql的一些基本资料，这些大家都知道，我也不必介绍太多，我们直接来剖析mysql的执行流程吧。

mysql的执行流程

要搞清楚mysql的执行流程之前，我们需要先搞明白几个重要的组件，把他们搞清楚了，自然就知道mysql的执行流程是怎样的了。

一共六个组件，我们一起来看看

  • 连接器：连接mysql服务器，进行权限验证
  • 缓存：保存上次查询的结果，提高性能
  • 分析器：词法与语法分析
  • 优化器：对你的查询语句做出适当的优化
  • 执行器：操作存储引擎，读写数据
  • 存储引擎：存储数据

mysql的这几个重要的组件大家应该都清楚了，那你们知道这些组件之间的关系或者说，mysql是如何利用这些组件完成一次普通的查询操作呢？我画一张图来给大家看一下。

mysql 历史执行sql mysql执行记录_mysql

所以mysql执行一个查询操作的完整流程为：客户端通过连接器建立连接，这个操作进行权限验证，通过之后会先前往缓存，根据sql作为key去查询，查到直接返回，否者前往分析器，经过分析器对sql语句的分析、解析，得到一个mysql可以理解的语法，随后进入优化器，mysql会根据你查询的条件进行适当的优化，之后在经过执行器，这就真正的开始前往存储引擎查询数据，最后将查询到的数据返回给客户端，顺便写入缓存（不一定）。

下面我们一起来看看每个组件的所负责的具体事项。

在正文开始之前，我们先创建一张测试表，用户下面demo展示

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL,
  `username` varchar(255) NOT NULL,
  `age` smallint(6) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mail` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into sys_user  values (1,'zhangsan',18,1,'123@163.com');

连接器

从上面的图中我们可以看出，mysql基本分为两大组件，一个是server层、一个是存储引擎，为什么server和存储引擎会被单独分开呢？看上面的图应该也不难想象，mysql的存储引擎是可以随场景变化的，它可以是MYISAM、InnoDB、也可以是Memory，所以它被设计成了插入式。

我们现在来看看连接器，它位于server层中，主要用于连接和权限验证，校验通过之后mysql会返回当前登录用户所拥有的权限，用于后续操作。

怎么进行连接呢？如果是使用的Navicat 这种第三方工具，那么很简单，直接输入指定的参数即可

mysql 历史执行sql mysql执行记录_mysql_02

如果是使用命令连接，那么命令如下：

mysql -hip地址 -P端口 -u用户名 -p密码

不过不建议把密码直接写在 -p后面，可以写成下面这样：

mysql -h127.0.0.1 -P3306  -uroot -p

如果连接的是本地服务，ip与端口可以省略，直接

mysql  -uroot -p

回车之后再输入密码：

mysql 历史执行sql mysql执行记录_mysql 历史执行sql_03

密码输入正确之后即可建立连接

mysql 历史执行sql mysql执行记录_缓存_04

连接是通过tcp协议进行连接的，需要通过握手建立连接，完成握手之后，服务器就要开始验证你的身份信息，通过则返回当前用户所拥有的权限信息，否则抛出异常，验证的信息为你输入的用户名、密码、ip等，如果用户名或者密码错误，服务器将会抛出一个错误码，随后断开连接，错误信息如下：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

表示你输入的用户名或者密码不正确， ‘root’@'localhost’中的root表示你的用户名，localhost表示当前客户端的ip，也就是当前和服务器进行握手的客户端。

如果是创建的用户只局限于本地连接，当外部客户端强行连接的时候会发生什么呢？我们先来创建一个 testlocal 用户，只限本地登录

mysql 历史执行sql mysql执行记录_mysql 历史执行sql_05

尝试本地登录

mysql 历史执行sql mysql执行记录_mysql_06

本地很容易的连接成功，那我们切换到其他IP的客户端之后怎么样呢？请看

mysql 历史执行sql mysql执行记录_mysql 历史执行sql_07

很明显，连接失败，错误的信息与用户名或密码错误基本一致，所以同学们在连接mysql的时候一定要能区分是用户名密码错误还是ip限制，我身边很多同事都踩过这个坑，一直以为是自己密码有问题，改了许多次还是不行，完全没有往ip方向联想。

身份校验通过之后mysql就会进入到权限表中查询当前登录用户所拥有的权限，如果连接的时候使用了安全套接字（SSL），那么可以使用X.509证书进行认证，那更改的权限在何时才能生效呢？

权限变更的生效时间

在 mysql 启动时，所有权限表的内容都会被加载到内存中。

当服务器监听到授权表发生改变时，已经连接的客户端将会发生如下改变：

  • 表和列权限在客户端下一次请求时生效。
  • 数据库权限改变在下一个USE db_name命令生效。
  • 全局权限的改变和密码改变在下一次客户端连接时生效。

如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改，服务器会注意到并立即重新将授权表载入内存。

如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等)，你应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再装载授权表，否则你的更改将不会生效，除非你重启服务器。

如果你直接更改了授权表但忘记重载，重启服务器后你的更改方生效。这样可能让你迷惑为什么你的更改没有什么变化！

权限表一般在mysql库中。

user：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db：记录各个帐号在各个数据库上的操作权限。

table_priv：记录数据表级的操作权限。

columns_priv：记录数据列级的操作权限。

连接完成之后如果没有后续的动作，这个时候，mysql会将该链接设置为空闲状态，我们一起来看看我的mysql服务器一共有多少个客户端处于连接状态，输入命令

sqlshow processlist;

mysql 历史执行sql mysql执行记录_客户端_08

  • id：线程的唯一标识。
  • User：启动这个线程的用户。
  • Host：记录了发送请求的客户端IP和端口信息。
  • db：当前执行的命令发生在哪个数据库中，如果没有指定为NULL，比如我刚刚查询的sql：sqlshow processlist 并没有指定数据库，所以展示NULL。
  • Command：此刻线程正在执行的命令。
  • Time：该线程处于当前状态的时间。
  • State：线程状态，与Command对应。
  • Info：记录着线程执行的sql语句，默认展示前100个字符，如果需要查看全部，请执行 show full processlist。

我们一起来看看Command一共有哪些值

  • Binlog Dump这是复制源上的线程，用于将二进制日志内容发送到副本。
  • Change user线程正在执行更改用户操作。
  • Close stmt线程正在关闭准备好的语句。
  • Connect副本连接到其源。
  • Connect Out副本正在连接到其源。
  • Create DB线程正在执行创建数据库操作。
  • Daemon该线程在服务器内部，而不是为客户端连接提供服务的线程。
  • Debug该线程正在生成调试信息。
  • Delayed insert该线程是一个延迟的插入处理程序。
  • Drop DB线程正在执行放置数据库操作。
  • Error
  • Execute线程正在执行准备好的语句。
  • Fetch线程正在从执行准备好的语句中获取结果。
  • Field List该线程正在检索表列的信息。
  • Init DB线程正在选择默认数据库。
  • Kill该线程正在杀死另一个线程。
  • Long Data执行准备好的语句的结果是线程正在检索长数据。
  • Ping线程正在处理服务器ping请求。
  • Prepare该线程正在准备一个准备好的语句。
  • Processlist该线程正在生成有关服务器线程的信息。
  • Query线程正在执行一条语句。
  • Quit线程正在终止。
  • Refresh该线程是刷新表，日志或缓存，或者重置状态变量或复制服务器信息。
  • Register Slave线程正在注册副本服务器。
  • Reset stmt该线程正在重置准备好的语句。
  • Set option该线程正在设置或重置客户端语句执行选项。
  • Shutdown线程正在关闭服务器。
  • Sleep线程正在等待客户端向其发送新语句。
  • Statistics线程正在生成服务器状态信息。
  • Time没用过。

有点跑偏了，我们回到主题，若客户端一段时间没有使用则会被置为空闲（Sleep）状态，但如果客户端长时间没有操作，那么服务器就会自动将连接断开，默认时长 8 小时，不过可以通过 wait_timeout 参数设置。

超过时长，连接被断开之后，客户端发起请求，那么该客户端将会收到一个：Lost connection to MySQL server during query，这个时候就只能通过重新建立连接进行操作。

连接分为两种类型，一个为长连接，一个为短连接，建立连接之后，客户端发送持续请求，如果一直在同一个连接中，那么这个就是长连接，如果每个请求一个连接，则是短连接，我们知道连接会有用户信息的校验，权限的验证，比较麻烦，所以推荐使用长连接进行操作，但是长连接也不是十全十美，有利肯定就有弊，长连接过多时会导致mysql占用的内存过多，导致内存紧张，极端情况可能导致内存泄漏（OOM），那我们如何解决这个问题呢？

  1. 定时清除长连接。
  2. 通过执行 mysql_reset_connection 来重新初始化连接资源，不过要求mysql的版本在5.7或之上。

缓存

连接完成之后，下一步就会进入缓存，mysql会在缓存中检测之前是否执行过这条语句，如果被执行过，那么查询的结果将会以key-value的形式存储在缓存中，这个时候下一次的查询直接命中缓存，直接返回相对应的数据，如果缓存中不存在当前key（sql语句），就会进入下一个阶段 - 分析器。

mysql判断缓存是否命中的方式很简单，mysql将缓存存放在一个引用表中，通过hash值方式应用，hash值包括：查询的sql、查询的数据库、客户端协议版本等等，mysql在判断是否命中缓存的时候不会提前解析sql的语法，而是直接使用sql语句和客户端的基本信息（协议）等等，进行hash算法，这里需要特别注意：在编写sql的时候，需要与上一次执行的sql保持完全一致，空格、注释、编码或者有其他的任何不同的地方都会导致hash出来的结果不同，从而无法命中缓存，所以在操作时需要保持一个统一的编码规范。

除了这个还有很多情况也会导致查询的数据无法缓存，比如聚合函数、自定义函数、存储过、用户变量、临时表、mysql库中的系统表、权限表。

mysql的缓存虽然能提升查询的性能，但是也会在其他方面造成额外的消耗，具体如下：

  1. 查询之前必须先检查是否命中缓存，对于缓存中没有的sql多了一次缓存的查询。
  2. 第一次查询或者表中的数据被修改时，当前查询需要将结果写入到缓存中，带来了额外的系统消耗。
  3. mysql在写操作时会将关于当前相关缓存的数据全部清空，如果缓存的数据过大，或者缓存的sql语句过多，可能会导致很大的系统消耗。

所以，缓存的好处可以提升查询的效率，弊端可能给系统带来额外的系统消耗，尤其是在InnoDB中的事务中，所以在使用的时候需要慎重，不可为了查询效率二盲目的使用缓存，使用不当，可能适得其反。

那mysql如何开启缓存呢？只需要将参数 query_cache_type 设置成 DEMAND 即可，这样会导致整个mysql都是使用缓存，很明显，这是不被推荐的，所以还有一种方式，那就是按需指定，什么叫按需指定呢？就是在你需要缓存的sql语句加上 SQL_CACHE 指定使用缓存即可，代码如下：

select SQL_CACHE * from sys_user where id = 1;

在这里有两点需要特别注意哦，一：mysql在8.0版本直接将缓存模块删除，也就是说，mysql8.0所有的查询都不会走向缓存了，而是直接前往磁盘；二：查询缓存的返回直接也会校验权限信息的，如果没有权限，就算使用了缓存，也无法查询。

分析(解析)器

mysql在缓存中没有命中之后将会进入流程的下一步，但这里并不会直接进入解析器，而是需要先将查询的sql转换成一个执行计划，在经过这个执行计划和存储引擎进行交互，这里就包括了**：解析器、预处理、优化器、执行器**。

生成完执行计划之后，mysql会对sql的关键字进行解析，生成一棵对应的 “解析树”，在这个解析过程中，mysql解析器会使用语法规则对sql进行解析和校验，第一步做的时词法分析，mysql执行的并不是你写的sql语句，而是将你写的sql语句解析成mysql可以执行的语句。

生成“解析树“之前还需要校验你的sql语句写的是否有问题，是否满足mysql的语法，如果你输入的sql语句存在问题，这个时候程序将会抛出异常，结束查询。

select * rom sys_user here id = 1;

我们故意将 from 写成了 rom，where 携程here，如果执行这条sql，程序会抛出什么异常呢？请看：

mysql 历史执行sql mysql执行记录_缓存_09

错误提示在rom附近存在一处语法错误，这说明，执行语法分析时，如果存在语法问题，不管有多少处问题，检测到第一处时就直接返回，不会接着往下分析，所以我们看到报错的时候别以为只有这一处问题，一定要检查仔细，否则上了生产环境，可能就会酿成大错。

接下来就需要进行预处理了，mysql会根据一些规则进一步的解析树是否合法，比如：表和数据列是否存在解析名字和别名是否存在歧义，我们再来看这条sql语句：

select * from sys_user1 here 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 'id = 1' at line 1
> 时间: 0.007s

这里同样存在两处错误，第一：表名不存在，第二：关键字where写成了here，我们发现异常出现在 ”id=1“ 的附近，那么说明先分析的语法，在执行预处理的时候才校验的表名，我们现在将关键字where修改正确

select * from sys_user1 where id = 1

> 1142 - SELECT command denied to user 'testlocal'@'113.xxx.xxx.xxx' for table 'sys_user1'
> 时间: 0.006s

这个时候提示表名有问题了，说明上面的顺序，先解析sql语法，然后再预处理的时候在校验表名、字段名等是否合法。

下一步预处理还会验证权限，这里的验证一般情况下都会较快，除非权限配置相当复杂。

优化器

分析器完成之后，语法树已经是合法的了，这个时候优化器就登场了，优化器将这条语法树转化成执行计划。mysql官方也是很为我们开发人员着想，设置了这个优化器，在开发过程中，我们想要查询一条sql语句，执行的方式有很多，比如是否走索引、走哪条索引、关联查询哪张表做主表等等，这些都是可变的，而优化器的作用就是根据程序员写的sql语句找到一条它认为最好的执行计划。

举个例子，请看下面这条sql：

select id,user_id,username from math m join english e using(user_id)
where m.score = 80 and e.score = 90

这里不建表了，随便写了一个sql语句，大家能看明白就行，主要是讲解一下什么是优化器，math：数学成绩表；english：英语成绩表，score：成绩（字段），这条sql的意思是：查询数学成绩80分并且英语成绩90分的同学信息，你觉得mysql会怎么样执行这条sql呢？

  1. 正常情况是不是先往数学成绩表（math）中查询成绩等于80的 user_id，然后拿着这些 user_id到英语成绩表（english）中查询成绩等于90的学生信息？
  2. 能不能先前往英语成绩表（english）中查询成绩等于90的user_id，然后再去数学成绩表（math）中查询成绩等于80的学生呢？

如果数学成绩表（math）中一共有5位同学的成绩是80分，英语成绩表（english）中只有一个同学的成绩是90分，那么第二种方式是不是效率就会高很多呢？

mysql的优化器就是做这个的，他会根据独特的算法，找到它认为效率最高的一条路径去执行，这里就存在一个疑问了，经过优化器优化的sql，是否总是最优的执行计划呢？答案是否定的，哪些情况会导致优化器生成的执行计划效果反而更差呢？以下七点参考于《高性能mysql》。

  1. 统计信息不完整或者不准确，比如InnoDB的MVCC多版本并发控制会导致表数据行的统计不准确。
  2. 执行计划中的成本并不等同于实际执行的成本，这个时候即使统计的信息很准确，优化器给出的执行计划也有可能不是最优的。举个例子，有些时候某个执行嘉华虽然需要读取更多的页面或者数据，但是它的实际成本可能会很小，为什么呢？原因很简单，如果读取的页面都是有序的或者这些页面(数据)已经被加载到内存中了，这个时候的访问成本比执行计划估计的成本小得多，mysql并不知道哪些数据存在内存，哪些数据存在磁盘中，所以IO的次数也是未知数。
  3. mysql的最优可能和你想得不一样，你可能希望执行时间越短越好，但是mysql只是基于成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以这里我们看到的根据执行计划成本来选择执行计划并不是完美的模型。
  4. mysql从不考虑其他并发执行的查询，这可能影响到当前查询的速度。
  5. mysql并不是完全基于成本优化，有时候也会给予一些固定的规则，例如存在全文索引的match()子句，则在存在全文索引的时候就是用全文索引，即使有时候使用别的索引和where条件可以远比全文索引的方式要快很多，但是mysql也会选择使用全文索引。
  6. mysql不会考虑不受其控制的操作成本，例如存储过程或者用户自定义函数的成本。
  7. 优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

mysql的优化器是一个非常复杂的组件，算法很多优化的策略也有很多，它会通过自己的优化策略选择出优化器认为最优的一个执行计划，优化策略虽然很多，但是在大致上可以分为两种：静态优化、动态优化。

静态优化：可以直接对解析树进行分析、优化。优化器可以通过代数变换将where条件转换为另外一种等价形态，这个转换不依赖条件的具体数值，即使where条件中的数值发生改变，静态优化也仍然有效。可以理解为”编译时优化“。

动态优化：它与查询的上下文相关，影响动态优化的因素有很多，比如索引对应的数量行数、where条件中的值等等，这些都会让优化器在执行sql的时候重新进行优化。可以理解为”运行时优化“。

所以优化器对sql进行优化的时候是选择静态优化还是动态优化取决于sql语句，静态优化只需要做一次，而动态优化在每次执行的时候都需要重新评估。

执行器

分析器将我们写的sql语句解析成了语法树、优化器将语法树转成了它认为最优的一条执行计划，这个时候就需要真正的去读数据了，执行器就是那个执行者，前面的工作都是准备，分析器、优化器让mysql知道了你要干什么、通过什么方式最简单有效，执行器就是拿着这些准备好的方案实施。

假如我们现在需要一共有多少人叫 “zhangsan” 这个姓名，手写了一条SQL语句：

select * from sys_user where username = 'zhangsan';

这就是一个普通的where条件查询，不过执行器在执行查询之前还需要做一件事情，而且是非常重要的事情：权限验证，执行器根据库名、表名、操作类型等等查看当前用户是否具备权限操作，如果发现当前用户并不具备此权限，那么直接终端执行，直接结束。

权限验证通过之后，执行器就会打开进入存储引擎，打开数据表进行数据读取，执行器也是通过存储引擎提供的API进行的操作。

我们一起来看看这条sql语句的执行过程，执行器调用存储引擎api获取 sys_user 表的第一行数据判断 username 是否等于 “zhangsan”，如果是，将结果保存到返回的结果集中，如果不是，则继续读取下一行数据，直到读完整张表为止（username字段未加索引情况下），最后执行器将结果集返回给客户端。

这就是执行器大致的执行过程，那执行器调用存储引擎的的接口是什么呢？我们一般称它为 “handler API”，查询中的每一张表都有一个 handler 示例，在优化器中创建的，事无绝对，并不是所有的操作都是通过“handler” 完成，例如：表锁。

mysql会重复执行计划中的每个操作，直到执行器查询完所有的数据为止，所以，执行器没有分析器、优化器那么的复杂，它的主要功能就是执行。

存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

我们常用的存储引擎一般有一下几种：

MyISAM：拥有较高的插入，查询速度，但不支持事务

InnoDB：支持事务的存储引擎，mysql5.5以后将它设置为默认存储引擎。

BDB：事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性

Memory：基于内存的存储引擎，将所有的数据都置于内存中，查询、插入、删除效率极高，是一种空间换时间的思想，不过服务重启会导致数据丢失。

Merge：将一部分的MyISAM表联合成的一个整体，适用于大数据存储。

存储引擎五花八门，所以mysql将它设置成了插拔式结构，提高了mysql的整体灵活性。

总结

mysql有哪些基本组件？

  • 连接器
  • 缓存（8.0以前，不含8.0）
  • 分析器
  • 优化器
  • 执行器
  • 存储引擎

mysql的执行流程是什么？

客户端 ----> 连接器 ----> 缓存 ----> 分析器 ----> 优化器 ----> 执行器 ----> 存储引擎 ----> 客户端

本文仅仅只是介绍了mysql一个查询语句整个的执行流程，看着内容非常多，其实只要认真的去思考，无非就是几个组件之间的调用，每个组件都有着自己的使用，连接器负责登陆校验、权限验证；缓存为了提高查询效率；分析器将mysql语句解析成语法树，然后执行预处理，判断权限等；优化器则是mysql生成一条它认为最优的执行计划；执行器拿着优化器给出的执行计划开始调用存储引擎的 “handler API” 进行数据读取，最后成返回到客户端，整篇文章看起来每个组件都比较复杂，没必要去死记硬背，尝试着去理解它为什么会出现这些组件，自然就能看明白了。