大家在学习SQL的时候，首先要学习select查询语句，比如下面的查询语句：//在product表中，查询id=1的记录select*fromproductwhereid=1;但是你有没有想过在MySQL中执行一条select查询语句的过程中会发生什么？带着这个问题，我们就可以对MySQL的内部架构有个很好的了解。那么，这次小林就带大家拆解一下MySQL的内部结构，看看内部每个“部分”都负责什么。MySQL的执行流程是怎样的？先从一张上帝视角图说起。下面是MySQL执行一条SQL查询语句的过程。从图中也可以看出MySQL内部架构中的各个功能模块。从查询语句执行过程可以看出，MySQL架构分为两层：服务器层和存储引擎层。Server层负责建立连接、分析和执行SQL。MySQL的大部分核心功能模块都在这里实现，主要包括连接器、查询缓存、解析器、优化器和执行器。此外，所有内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎函数（如存储过程、触发器、视图等）都在Server层实现。存储引擎层负责数据的存储和检索。支持InnoDB、MyISAM、Memory等多种存储引擎，不同的存储引擎共享一个server层。现在最常用的存储引擎是InnoDB。从MySQL5.5开始，InnoDB成为了MySQL默认的存储引擎。我们常说的索引数据结构是由存储引擎层实现的，不同的存储引擎支持的索引类型是不同的。比如InnoDB支持的索引类型是B+树，默认使用，也就是说在数据表中创建。主键索引和二级索引默认使用B+树索引。好了，现在我们对Server层和存储引擎层有了一个简单的了解。下面详细说说一条SQL查询语句的执行过程，依次看看各个功能模块的作用。第一步：连接器如果你想在Linux操作系统中使用MySQL，你的第一步必须是在执行SQL语句之前连接到MySQL服务。一般我们使用如下命令连接：#-h指定MySQL服务的IP地址。如果是连接本地的MySQL服务，则不需要该参数；#-u指定用户名，管理员角色名称为root；#-p指定密码。如果命令行没有填写密码（为了密码安全，建议不要在命令行写密码），需要在交互对话框中输入密码mysql-h$ip-u$user-p连接过程需要先经过TCP三次握手，因为MySQL是基于TCP协议传输的，如果MySQL服务没有启动，会收到如下错误：如果MySQL服务运行正常，TCP连接建立完成后，连接器会开始验证你的用户名和密码。如果用户名或密码不正确，您将收到“拒绝用户访问”错误，然后客户端程序结束执行。如果用户密码没有问题，连接器会获取用户权限并保存。用户在本次连接中的任何后续操作都将根据连接开始时读取的权限进行判断。因此，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已有连接的权限。修改完成后，只有新连接才会使用新的权限设置。如何查看有多少客户端连接到MySQL服务？如果想知道当前有多少客户端连接到MySQL服务，可以执行showprocesslist命令查看。例如上图，有两个username为root的用户连接了MySQL服务，id为6的用户的Command列状态为Sleep，表示该用户没有执行任何操作连接到MySQL服务后的命令。命令，表示这是一个空闲连接，空闲时间为736秒（时间栏）。空闲连接会一直被占用吗？当然不是，MySQL定义了一个空闲连接的最大空闲时间，由wait_timeout参数控制。默认值为8小时（28880秒）。如果空闲连接超过这个时间，连接器会自动断开连接。mysql>showvariableslike'wait_timeout';+----------------+------+|变量名|值|+-----------------+--------+|等待超时|28800|+----------------+--------+1rowinset(0.00sec)当然我们也可以使用kill手动断开空闲连接连接+id命令。mysql>killconnection+6;QueryOK,0rowsaffected(0.00sec)空闲连接被服务器主动断开后，客户端不会马上知道，直到客户端发起下一次请求，才会收到这样的错误“ERROR2013(HY000)：查询期间与MySQL服务器失去连接”。MySQL连接数有限制吗？MySQL服务支持的最大连接数由max_connections参数控制。比如我的MySQL服务默认是151，如果超过这个值，系统会拒绝下一次连接请求，并报错“连接太多”。mysql>showvariableslike'max_connections';+----------------+------+|变量名|值|+------------------+--------+|最大连接|151|+----------------+--------+1rowinset(0.00sec)MySQL连接和HTTP一样，有一个概念短连接和长连接，它们的区别如下：//短连接连接mysql服务（TCP三次握手）执行sql断开mysql服务（TCP四次挥手）//长连接连接mysql服务（TCP三次握手）-wayhandshake)executesqlexecutesqlexecutesql...disconnectmysql服务（TCP挥手四次）可以看到使用长连接的好处是可以减少连接建立的次数和断开连接的过程，所以它一般推荐使用长连接。但是使用长连接可能会占用更多的内存，因为MySQL在查询执行过程中会临时使用内存来管理连接对象，而这些连接对象资源只有在连接断开时才会被释放。如果长连接很多，MySQL服务会占用过多内存，可能会被系统强行杀掉，导致MySQL服务异常重启。如何解决长连接占用内存的问题？有两种解决方法：第一种是定期断开长连接。由于连接断开后连接占用的内存资源会被释放，所以我们可以定时断开长连接。第二，客户端主动重置连接。MySQL5.7版本实现了mysql_reset_connection()函数的接口。请注意，这是一个接口函数而不是命令。然后，当客户端进行大操作时，在代码中调用mysql_reset_connection函数，重置连接，释放内存。这个过程不需要重新连接和重新认证，但是会将连接恢复到刚刚创建的状态。至此，连接器的工作已经完成，简单总结一下：通过TCP三次握手与客户端建立连接；验证客户端的用户名和密码，如果用户名或密码不正确，会报错；如果用户名和密码正确，它会读取该用户的权限，然后下面的权限逻辑判断都是根据此时读取的权限；第二步：querycacheconnector工作完成后，客户端可以向MySQL服务发送SQL语句，MySQL服务收到SQL语句后，会解析出SQL语句的第一个字段，看是什么类型的声明是。如果SQL是一条查询语句（select语句），MySQL会先在查询缓存（QueryCache）中查找缓存的数据，看看这条命令之前是否执行过。查询缓存以key-value的形式存储在内存中，key是SQL查询语句，value是SQL语句的查询结果。如果查询语句命中查询缓存，则直接将值返回给客户端。如果查询语句没有命中查询缓存，就会继续执行。执行后，查询结果将存储在查询缓存中。从这一点来看，查询缓存还是挺有用的，但实际上，查询缓存是挺鸡肋的。对于更新频繁的表，查询缓存的命中率是很低的，因为只要更新一个表，这个表的查询缓存就会被清空。如果一个查询结果比较大的数据刚刚被缓存，还没有被使用，那么查询缓冲区会随着表的更新而被清空，相当于一个孤独的缓存。因此，MySQL8.0直接删除了querycache，也就是说从MySQL8.0开始，执行一条SQL查询语句不会进入querycache阶段。对于MySQL8.0之前的版本，如果我们想关闭querycache，可以将参数query_cache_type设置为DEMAND。第三步：在解析器正式执行SQL查询语句之前，MySQL会先解析SQL语句。这项工作将由解析器来完成。解析器将做以下两件事。第一件事，词法分析。MySQL会根据你输入的字符串来识别关键字，构建SQL语法树，让aspect后面的模块可以获取到SQL类型、表名、字段名、where条件等。第二件事，语法分析。根据词法分析的结果，解析器会根据语法规则判断你输入的SQL语句是否符合MySQL语法。如果我们输入的SQL语句语法不正确，或者数据表或字段不存在，就会在parser阶段报错。比如下面的查询语句，如果我写fromasform，那么MySQL解析器就会报错。比如下面的查询语句中，test表不存在，MySQL解析器就会报错。mysql>select*fromtest;ERROR1146(42S02):Table'mysql.test'doesn'texist第四步：优化器通过解析器后，会执行SQL查询语句，但在真正执行之前，它会检查用户是否有访问数据库表的权限，如果没有，直接报错。如果有权限，就会进入SQL查询语句的执行阶段。在SQL查询语句真正执行之前，需要制定一个执行计划。这项工作是由“优化器”完成的。优化器主要负责确定SQL查询语句的执行计划。例如，当表中有多个索引时，优化器会根据查询成本的考虑来决定使用哪个索引。当然我们这次的查询语句（select*fromproductwhereid=1）很简单，就是选择使用主键索引。要知道优化器选择了哪个索引，我们可以在查询语句的最前面加上一个explain命令，这样就会输出这条SQL语句的执行计划，然后执行计划中的key表示使用了哪个索引在执行过程中，比如下图中的key是PRIMARY，则使用主键索引。如果查询语句的执行计划中的key为null，说明没有使用索引，那么会扫描全表（type=ALL）。这种查询扫描方式效率最低，如下图所示：这张product表只有一个索引，是主键，现在我在表中设置name为普通索引（二级索引）。此时product表有一个主键索引（id）和一个公共索引（name）。假设执行了这条查询语句：selectidfromproductwhereid>1andnamelike'i%';这条查询语句的结果可以使用主键索引也可以使用普通索引，只是执行效率会有所不同。这时候需要优化器来决定使用哪个索引。很明显，这个查询语句是一个覆盖索引，可以直接在二级索引中找到结果（因为二级索引的B+树的叶子节点的数据存储的是主键值），不需要查找在主键索引中。因为查询主键索引的B+树的成本高于查询二级索引的B+树的成本，所以优化器会基于查询成本的考虑，选择一个查询成本低的普通索引。在下图的执行计划中，我们可以看到执行时使用的是普通索引（名称），而Exta是Usingindex，也就是说使用了覆盖索引优化。第五步：执行器经过优化器后，确定了执行计划，然后MySQL才真正开始执行语句。这项工作由“执行者”完成。在执行过程中，执行者会与存储引擎进行交互，交互是以记录为单位的。接下来用三种方法执行流程，给大家讲一下执行器和存储引擎的交互过程（PS：为了写好这部分，特意看了下MySQL源码，也是第一次接触）拜读哈哈）。主键索引查询全表扫描索引吓跑主键索引查询以本文开头的查询语句为例，看看执行器是如何工作的。从id=1的产品中选择*；该查询语句的查询条件使用主键索引，是一个等价查询。同时主键id是唯一的，不会有相同id的记录，所以优化器决定选择访问类型为const进行查询，即使用主键索引查询一条记录，那么执行器和存储引擎的执行流程如下：执行器第一次查询时，会调用read_first_record函数指针指向的函数，因为优化器选择的访问类型是const，这个函数指针指向作为InnoDB引擎索引查询的接口，给存储引擎条件id=1，让存储引擎定位到第一条满足条件的记录。存储引擎通过主键索引的B+树结构定位到id=1的第一条记录。如果该记录不存在，则向执行者报找不到该记录的错误，然后查询结束。如果记录存在，则将记录返回给执行者；执行器从存储引擎中读取到记录后，再判断记录是否满足查询条件，满足则发送给客户端，不满足则跳过该记录。executor的查询过程是一个while循环，所以会再次检查，但是这次因为不是第一次查询，所以会调用read_record函数指针指向的函数，因为优化器选择的访问类型是const，这个函数指针指向的是一个总是返回-1的函数，所以当这个函数被调用时，执行者退出循环，从而结束查询。至此，这条语句执行完毕。全表扫描以全表扫描为例：select*fromproductwherename='iphone';这条查询语句的查询条件没有使用索引，所以优化器决定使用访问类型ALL进行查询，即全表扫描方式查询，那么执行器和存储引擎的执行流程如下：executor的第一次查询会调用read_first_record函数指针指向的函数，因为优化器选择的访问类型是all，这个函数指针是指向InnoDB引擎全扫描的接口，让存储引擎读取表中的第一条记录；执行器会判断读取的记录名称是否为iphone，如果不是则跳过；如果是，则将记录发送给客户端（是的，服务器层每次从存储引擎读取一条记录，都会发送给客户端。客户端之所以直接显示所有记录，是因为客户端等待查询查询完成，将显示所有记录）。executor的查询过程是一个while循环，所以会再次检查，会调用read_record函数指针指向的函数，因为优化器选择的访问类型是all，read_record函数指针仍然指向到InnoDB引擎全量扫描的接口，so然后要求存储引擎层继续读取上一条记录的下一条记录。存储引擎取出下一条记录后，返回给执行器（Server层），执行器继续判断条件。如果不满足查询条件，则跳过，否则，将记录发送给客户端；重复上述过程，直到存储引擎读取表中的所有记录，然后将读取的信息返回给执行器（Server层）；执行器收到存储引擎上报的信息后，退出循环，停止查询。至此，这条语句执行完毕。索引下推这部分很适合讲索引下推（MySQL5.7推出的查询优化策略），让大家清楚的知道“下推”的动作是在什么地方下推。索引下推可以减少查询时向表返回二级索引的操作，提高查询效率，因为它将服务器层的责任转移到了存储引擎层进行处理。举个具体的例子方便大家理解，这里有一张user表如下，我为age和reword字段建立了联合索引（age,reword）：现在有如下查询语句：select*fromt_userwhereage>20和奖励=100000；联合索引遇到范围查询（>,<,between,like）会停止匹配，即a字段可以使用联合索引，reward字段不能使用索引。具体原因可以看这篇文章：关于指标的面试常见问题。那么，在不使用索引下推（MySQL5.7之前）的情况下，执行器和存储引擎的执行过程如下：服务器层首先调用存储引擎的接口，定位到第一个满足的二级索引记录查询条件，即定位到age>20的第一条记录；存储使得二级索引的B+树快速定位到这条记录，获取主键值，然后进行回表操作，将完整的记录返回给server层；服务器层在判断记录的奖励是否等于100000，如果为真，则发送给客户端；否则，记录将被跳过；然后，继续向存储引擎询问下一条记录，存储引擎在二级索引定位到这条记录后得到主键值，然后回表将完整的记录返回给server层；它一直持续到存储引擎读取了表中的所有记录。可以看出，在没有索引下推的情况下，每查询一条二级索引记录，都会进行回表操作，然后将记录返回给服务端，然后服务端判断该条记录的奖励是否为等于100000。索引下推后，判断记录的奖励是否等于100000的工作交给了存储引擎层。流程如下：server层首先调用存储引擎的接口，定位到第一个满足查询条件的二级索引记录，即Locatethefirstrecordwithage>20；存储引擎定位到二级索引后，不执行回表操作，而是先判断索引包含的列（reward列）的条件（reward是否等于100000）是否成立。如果条件不成立，直接跳过二级索引。如果成立，则执行回表操作，将完成的记录返回给Server层。server层在判断其他查询条件（本次查询没有其他条件）是否为真，如果为真，则发送给客户端；否则，跳过记录并向存储引擎请求下一条记录。如此往复，直到存储引擎读取了表中的所有记录。可以看出，使用索引下推后，reward列虽然不能在联合索引中使用，但是因为包含在联合索引(age,reward)中，存储引擎过滤掉满足条件的记录后才可以使用reward=100000进行回表操作，获取整条记录。与不使用索引下推相比，省去了很多回表操作。当你发现执行计划的Extr部分显示“Usingindexcondition”时，说明使用了索引下推。
