一、数据库的基础架构：一条SQL查询语句是如何执行的

这一篇讲解的是MySQL的基础架构，做一件事千万不要直接陷入到细节里，你应该鸟瞰全貌，这样能够帮助你从高纬度理解问题。同样，对于MySQL学习也是这样，平常我们使用数据库，看到的通常是一个整体，比如，你有个最简单的表，表里面只有一个ID字段，在执行下面这个查询语句时：

select * from T where ID=10；

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

现在把MySQL拆解一下，看看里面有哪些零件，希望通过拆解，可以更加深入的理解MySQL.当碰到MySQL的一些异常或者问题时，能够直戳本质，更加快速的解决问题。

从MySQL结构示意图中可以清晰的看到语句在各个功能模块中的执行过程。

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

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

存储引擎负责的是数据的存储和提取，其架构是插件式的，支持Innodb、MylSAM、Memory等多个存储引擎，现在常用的存储引擎是Innodb,从mysql5.5.5版本开始成为默认的存储引擎。在create table不指定存储引擎时使用的是Innodb，也可以使用engine=Innodb制定使用哪一个引擎。不同的存储引擎表数据存取方式不同，支持的功能也是不一样的。

连接器：

当你连接数据库时，首先接待你的就是连接器，连接器负责跟客户端建立连接，获取权限、维持和管理连接。连接命令：

mysql -h$ip -P$port -u$user -p

 

虽然密码可以写在 -p后面，但是这样会造成密码泄露。不建议这样操作。

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

如果用户名或密码不对，你会收到“access denied for user”的错误，然后客户端程序结束执行。

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

如果管理员对这个用户的权限进行修改，也不会影响已经存在的连接权限，修改完成后，只有再新建才会使用新的权限设置。

连接完成后，如果没有后续操作，这个连接处于空闲操作，你可以在show processlist 命令中看到它， 稳重的图是show provessList 的结果，其中Command列显示的为‘sleep’的这一行，就表示现在系统里面有一个空闲连接

客户端如果太长时间没有动静，连接器就会自动断开它，这个参数有wait_timeout控制的，默认为8小时。

如果断开连接后，客户端再次请求，就会有：lost connection to mysql server during query.这时候如果你要继续，就需要重连，然后再执行请求。

长连接：

连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接：每次执行完很少的几次查询之后就断开连接，下次需要查询时再重新连接。

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

可以考虑以下两种方案：

1、定期断开长连接，使用一段时间，或者程序判断执行过一个占用内存的大查询后，断开重连，之后要查询在重连。

2、如果你使用mysql5.7或更新的版本，可以每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和进行权限校验，但是会将连接恢复到刚刚穿件完时的状态。

查询缓存：

连接建立之后，你就可以执行select语句了，执行语句就会来到第二步：查询缓存。

mysql拿到一个查询请求后，会先到缓存看看，之前是不是执行过这条语句，之前执行过的语句及其结果会以key-value的

形式存在，被直接缓存在内存中。ke是查询语句，value是查询结果，如果你的查询能在缓存中找到key，那么这个value就会直接返回结果，这个效率会很高。

大多数情况下不要使用查询缓存，因为利大于弊。

查询缓存失效特别频繁，一个更新语句就会将表上所有的查询缓存清空。因此，你费劲地把结果存起来，还没有用就被一个更新清空了。对于更新压力大的表，查询缓存的命中率非常低，除非你的业务就是一张静态表，很长时间才会更新一次。比如系统配置表。

好在mysql提供了这种按需使用的方式，你可以将参数query_cache_type设置成DEMAND.这样对于默认的sql语句都不使用查询缓存，对于你确定使用的查询缓存可以使用SQL_CACHE来显示指定。

mysql> select SQL_CACHE * from T where ID=10；

mysql 8.0版本直接将查询缓存的整块功能删除掉了，也就是在8.0开始就没有查询缓存的功能了。

分析器：

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

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

mysql从你输入的select这个关键字识别出来，这是一个查询语句，他需要把T识别为表名，把id识别成列名。（mysql不区分字母的大小写）。

2、之后分析器做语法分析，根据语法分析的结果，语法分析器会根据语法规则，判断你输入的sqi语句是否maysql语法。

如果你的语法有错误，会有"you have an error in your syntax"的错误提示。

mysql> elect * from t 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 t where ID=1' at line 1

 

一般语法错误会提示第一个出现错误的位置，所以你要关注的是“use near” 的内容。

优化器：

经过分析器，mysql知道了你要做什么，在执行之前，还要先经过优化器的处理。

优化器在表里面有多个索引的时候，决定使用哪个索引，或者一个语句有多表关联（join）的时候，决定各个表的顺序，下面表执行两个表的join：

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

 

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

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

两种执行方法的逻辑是一样的，但是执行的效率是不同的，而优化器决定的是使用哪一种方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入了执行阶段。

执行器：

mysql通过执行器开始执行语句了。

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

mysql> select * from T where ID=10;

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

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

比如例子中表T，ID字段没有索引，那么执行器的执行流程如下：

1、调用InnoDB引擎接口取这个表额第一行，判断ID是不是10，如果不是就跳过，如果是将这行存在结果集中；

2、调用引擎取下一行，重复相同的判断逻辑，直到这个表的最后一行。

3、执行器将上述便利结果中所用满足的条件组成记录集作为结果集返回给客户端。

至此，这个语句就执行结束。

对于有索引的表，第一次调用的是取满足条件的第一行这个接口，之后再循环满足条件的下一行这个接口，这些接口在引擎中都定义好了。

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

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