共计 7659 个字符，预计需要花费 20 分钟才能阅读完成。
学习 SQL 的时候，大家必定第一个先学到的就是 select 查问语句了，比方上面这句查问语句：
// 在 product 表中，查问 id = 1 的记录 select * from product where id = 1;然而有没有想过，执行一条 select 查问语句，在 MySQL 中期间产生了什么？
带着这个问题，咱们能够很好的理解 MySQL 外部的架构。
所以，这次小林就带大家拆解一下 MySQL 外部的构造，看看外部里的每一个“整机”具体是负责做什么的。
MySQL 执行流程是怎么的？
先来一个上帝视角图，上面就是 MySQL 执行一条 SQL 查问语句的流程，也从图中能够看到 MySQL 外部架构里的各个功能模块。
能够看到，MySQL 的架构共分为两层：Server 层和存储引擎层，
- Server 层负责建设连贯、剖析和执行 SQL。MySQL 大多数的外围功能模块都在这实现，次要包含连接器，查问缓存、解析器、优化器、执行器等。另外，所有的内置函数（如日期、工夫、数学和加密函数等）和所有跨存储引擎的性能（如存储过程、触发器、视图等。）都在 Server 层实现。
- 存储引擎层负责数据的存储和提取。反对 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。当初最罕用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎。咱们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎反对的索引类型也不雷同，比方 InnoDB 反对索引类型是 B+ 树，且是默认应用，也就是说在数据表中创立的主键索引和二级索引默认应用的是 B+ 树索引。
好了，当初咱们对 Server 层和存储引擎层有了一个简略意识，接下来，就具体说一条 SQL 查问语句的执行流程，顺次看看每一个功能模块的作用。
第一步：连接器
如果你在 Linux 操作系统里要应用 MySQL，那你第一步必定是要先连贯 MySQL 服务，而后能力执行 SQL 语句，广泛咱们都是应用上面这条命令进行连贯：
# -h 指定 MySQL 服务得 IP 地址，如果是连贯本地的 MySQL 服务，能够不必这个参数；# -u 指定用户名，管理员角色名为 root；# -p 指定明码，如果命令行中不填写明码（为了明码平安，倡议不要在命令行写明码），就须要在交互对话外面输出明码 mysql -h$ip -u$user -p连贯的过程须要先通过 TCP 三次握手，因为 MySQL 是基于 TCP 协定进行传输的，如果 MySQL 服务并没有启动，则会收到如下的报错：
如果 MySQL 服务失常运行，实现 TCP 连贯的建设后，连接器就要开始验证你的用户名和明码，如果用户名或明码不对，就收到一个 ”Access denied for user” 的谬误，而后客户端程序完结执行。
如果用户明码都没有问题，连接器就会获取该用户的权限，而后保存起来，后续该用户在此连贯里的任何操作，都会基于连贯开始时读到的权限进行权限逻辑的判断。
所以，如果一个用户曾经建设了连贯，即便管理员中途批改了该用户的权限，也不会影响曾经存在连贯的权限。批改实现后，只有再新建的连贯才会应用新的权限设置。
如何查看 MySQL 服务被多少个客户端连贯了？
如果你想晓得以后 MySQL 服务被多少个客户端连贯了，你能够执行 show processlist命令进行查看。
比方上图的显示后果，共有两个用户名为 root 的用户连贯了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 Sleep，这意味着该用户连贯完 MySQL 服务就没有再执行过任何命令，也就是说这是一个闲暇的连贯，并且闲暇的时长是 736 秒（Time 列）。
闲暇连贯会始终占用着吗？
当然不是了，MySQL 定义了闲暇连贯的最大闲暇时长，由 wait_timeout 参数管制的，默认值是 8 小时（28880 秒），如果闲暇连贯超过了这个工夫，连接器就会主动将它断开。
mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout | 28800 |+---------------+-------+1 row in set (0.00 sec)当然，咱们本人也能够手动断开闲暇的连贯，应用的是 kill connection + id 的命令。
mysql> kill connection +6;Query OK, 0 rows affected (0.00 sec)一个处于闲暇状态的连贯被服务端被动断开后，这个客户端并不会马上晓得，等到客户端在发动下一个申请的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
MySQL 的连接数有限度吗？
MySQL 服务反对的最大连接数由 max_connections 参数管制，比方我的 MySQL 服务默认是 151 个, 超过这个值，零碎就会回绝接下来的连贯申请，并报错提醒“Too many connections”。
mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in set (0.00 sec)MySQL 的连贯也跟 HTTP 一样，有短连贯和长连贯的概念，它们的区别如下：
// 短连贯连贯 mysql 服务（TCP 三次握手）执行 sql 断开 mysql 服务（TCP 四次挥手）// 长连贯连贯 mysql 服务（TCP 三次握手）执行 sql 执行 sql 执行 sql.... 断开 mysql 服务（TCP 四次挥手）能够看到，应用长连贯的益处就是能够缩小建设连贯和断开连接的过程，所以个别是举荐应用长连贯。
然而，应用长连贯后可能会占用内存增多，因为 MySQL 在执行查问过程中长期应用内存治理连贯对象，这些连贯对象资源只有在连贯断开时才会开释。如果长连贯累计很多，将导致 MySQL 服务占用内存太大，有可能会被零碎强制杀掉，这样会产生 MySQL 服务异样重启的景象。
怎么解决长连贯占用内存的问题？
有两种解决形式。
第一种，定期断开长连贯。既然断开连接后就会开释连贯占用的内存资源，那么咱们能够定期断开长连贯。
第二种，客户端被动重置连贯。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，留神这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连贯，达到开释内存的成果。这个过程不须要重连和从新做权限验证，然而会将连贯复原到刚刚创立完时的状态。
至此，连接器的工作做完了，简略总结一下：
- 与客户端进行 TCP 三次握手建设连贯；
- 校验客户端的用户名和明码，如果用户名或明码不对，则会报错；
- 如果用户名和明码都对了，会读取该用户的权限，而后前面的权限逻辑判断都基于此时读取到的权限；
第二步：查问缓存
连接器得工作实现后，客户端就能够向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。
如果 SQL 是查问语句（select 语句），MySQL 就会先去查问缓存（Query Cache）里查找缓存数据，看看之前有没有执行过这一条命令，这个查问缓存是以 key-value 模式保留在内存中的，key 为 SQL 查问语句，value 为 SQL 语句查问的后果。
如果查问的语句命中查问缓存，那么就会间接返回 value 给客户端。如果查问的语句没有命中查问缓存中，那么就要往下继续执行，等执行完后，查问的后果就会被存入查问缓存中。
这么看，查问缓存还挺有用，然而其实 查问缓存挺鸡肋 的。
对于更新比拟频繁的表，查问缓存的命中率很低的，因为只有一个表有更新操作，那么这个表的查问缓存就会被清空。如果刚缓存了一个查问后果很大的数据，还没被应用的时候，刚好这个表有更新操作，查问缓冲就被清空了，相当于缓存了个寂寞。
所以，MySQL 8.0 版本间接将查问缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查问语句，不会再走到查问缓存这个阶段了。
对于 MySQL 8.0 之前的版本，如果想敞开查问缓存，咱们能够通过将参数 query_cache_type 设置成 DEMAND。
第三步：解析器
在正式执行 SQL 查问语句之前，MySQL 会先对 SQL 语句做解析，这个工作交由由解析器来实现，解析器会做如下两件事件。
第一件事件，词法剖析。MySQL 会依据你输出的字符串辨认出关键字进去，构建出 SQL 语法树，这样方面前面模块获取 SQL 类型、表名、字段名、where 条件等等。
第二件事件，语法分析。依据词法剖析的后果，语法解析器会依据语法规定，判断你输出的这个 SQL 语句是否满足 MySQL 语法。
如果咱们输出的 SQL 语句语法不对，或者数据表或者字段不存在，都会在解析器这个阶段报错。
比方，我上面这条查问语句，把 from 写成了 form，这时 MySQL 解析器就会给报错。
比方，我上面这条查问语句，test 这张表是不存在的，这时 MySQL 解析器就会给报错。
mysql> select * from test;ERROR 1146 (42S02): Table 'mysql.test' doesn't exist第四步：优化器
通过解析器后，接着就要执行 SQL 查问语句了，然而在真正执行之前，会检查用户是否有拜访该数据库表的权限，如果没有就间接报错了。
如果有权限，就进入 SQL 查问语句的执行阶段，而 SQL 查问语句真正执行之前须要先制订一个执行打算，这个工作交由「优化器」来实现的。
优化器次要负责将 SQL 查问语句的执行计划确定下来，比方在表外面有多个索引的时候，优化器会基于查问老本的思考，来决定抉择应用哪个索引。
当然，咱们本次的查问语句（select * from product where id = 1）很简略，就是抉择应用主键索引。
要想晓得优化器抉择了哪个索引，咱们能够在查问语句最后面加个 explain 命令，这样就会输入这条 SQL 语句的执行打算，而后执行打算中的 key 就示意执行过程中应用了哪个索引，比方下图的 key 为 PRIMARY 就是应用了主键索引。
如果查问语句的执行打算里的 key 为 null 阐明没有应用索引，那就会全表扫描（type = ALL），这种查问扫描的形式是效率最低品位的，如下图：
这张 product 表只有一个索引就是主键，当初我在表中将 name 设置为一般索引（二级索引）。
这时 product 表就有主键索引（id）和一般索引（name）。假如执行了这条查问语句：
select id from product where id > 1 and name like 'i%';这条查问语句的后果既能够应用主键索引，也能够应用一般索引，然而执行的效率会不同。这时，就须要优化器来决定应用哪个索引了。
很显然这条查问语句是 笼罩索引，间接在二级索引就能查找到后果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查问主键索引的 B+ 树的老本会比查问二级索引的 B+ 的老本大，优化器基于查问老本的思考，会抉择查问代价小的一般索引。
在下图中执行打算，咱们能够看到，执行过程中应用了一般索引（name），Exta 为 Using index，这就是表明应用了笼罩索引优化。
第五步：执行器
经验完优化器后，就确定了执行计划，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」实现的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。
接下来，用三种形式执行过程，跟大家说一下执行器和存储引擎的交互过程（PS：为了写好这一部分，特地去看 MySQL 源码，也是第一次看哈哈）。
- 主键索引查问
- 全表扫描
- 索引吓退
主键索引查问
以本文结尾查问语句为例，看看执行器是怎么工作的。
select * from product where id = 1;这条查问语句的查问条件用到了主键索引，而且是等值查问，同时主键 id 是惟一，不会有 id 雷同的记录，所以优化器决定选用拜访类型为 const 进行查问，也就是应用主键索引查问一条记录，那么执行器与存储引擎的执行流程是这样的：
- 执行器第一次查问，会调用 read_first_record 函数指针指向的函数，因为优化器抉择的拜访类型为 const，这个函数指针被指向为 InnoDB 引擎索引查问的接口，把条件
id = 1交给存储引擎，让存储引擎定位符合条件的第一条记录。 - 存储引擎通过主键索引的 B+ 树结构定位到 id = 1 的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的谬误，而后查问完结。如果记录是存在的，就会将记录返回给执行器；
- 执行器从存储引擎读到记录后，接着判断记录是否合乎查问条件，如果合乎则发送给客户端，如果不合乎则跳过该记录。
- 执行器查问的过程是一个 while 循环，所以还会再查一次，然而这次因为不是第一次查问了，所以会调用 read_record 函数指针指向的函数，因为优化器抉择的拜访类型为 const，这个函数指针被指向为一个永远返回 – 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是完结查问了。
至此，这个语句就执行实现了。
全表扫描
举个全表扫描的例子：
select * from product where name = 'iphone';这条查问语句的查问条件没有用到索引，所以优化器决定选用拜访类型为 ALL 进行查问，也就是全表扫描的形式查问，那么这时执行器与存储引擎的执行流程是这样的：
- 执行器第一次查问，会调用 read_first_record 函数指针指向的函数，因为优化器抉择的拜访类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
- 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是间接显示所有记录的，是因为客户端是等查问语句查问实现后，才会显示出所有的记录）。
- 执行器查问的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器抉择的拜访类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求持续读方才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server 层），执行器持续判断条件，不合乎查问条件即跳过该记录，否则发送到客户端；
- 始终反复上述过程，直到存储引擎把表中的所有记录读完，而后向执行器（Server 层）返回了读取结束的信息；
- 执行器收到存储引擎报告的查问结束的信息，退出循环，进行查问。
至此，这个语句就执行实现了。
索引下推
在这部分非常适合讲索引下推（MySQL 5.7 推出的查问优化策略），这样大家能分明的晓得，「下推」这个动作，下推到了哪里。
索引下推可能缩小 二级索引 在查问时的回表操作，进步查问的效率，因为它将 Server 层局部负责的事件，交给存储引擎层去解决了。
举一个具体的例子，不便大家了解，这里一张用户表如下，我对 age 和 reword 字段建设了联结索引（age，reword）：
当初有上面这条查问语句：
select * from t_user where age > 20 and reward = 100000;联结索引当遇到范畴查问 (>、<、between、like) 就会进行匹配，也就是 a 字段能用到联结索引，然而 reward 字段则无奈利用到索引。具体起因这里能够看这篇：索引常见面试题
那么，不应用索引下推（MySQL 5.7 之前的版本）时，执行器与存储引擎的执行流程是这样的：
- Server 层首先调用存储引擎的接口定位到满足查问条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
- 存储引起依据二级索引的 B+ 树疾速定位到这条记录后，获取主键值，而后 进行回表操作，将残缺的记录返回给 Server 层；
- Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
- 接着，持续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，而后回表操作，将残缺的记录返回给 Server 层；
- 如此往返，直到存储引擎把表中的所有记录读完。
能够看到，没有索引下推的时候，每查问到一条二级索引记录，都要进行回表操作，而后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。
而应用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下：
- Server 层首先调用存储引擎的接口定位到满足查问条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
- 存储引擎定位到二级索引后，先不执行回表 操作，而是先判断一下该索引中蕴含的列（reward 列）的条件（reward 是否等于 100000）是否成立。如果 条件不成立 ，则间接 跳过该二级索引 。如果 成立 ，则 执行回表 操作，将实现记录返回给 Server 层。
- Server 层在判断其余的查问条件（本次查问没有其余条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，而后向存储引擎索要下一条记录。
- 如此往返，直到存储引擎把表中的所有记录读完。
能够看到，应用了索引下推后，尽管 reward 列无奈应用到联结索引，然而因为它蕴含在联结索引（age，reward）里，所以间接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有应用索引下推，节俭了很多回表操作。
当你发现执行打算里的 Extr 局部显示了“Using index condition”，阐明应用了索引下推。
总结
执行一条 SQL 查问语句，期间产生了什么？
- 连接器：建设连贯，治理连贯、校验用户身份；
- 查问缓存：查问语句如果命中查问缓存则间接返回，否则持续往下执行。MySQL 8.0 已删除该模块；
- 解析器，对 SQL 查问语句进行词法剖析、语法分析，而后构建语法树，不便后续模块读取表名、字段、语句类型；
- 优化器：基于查问老本的思考，抉择查问老本最小的执行打算；
- 执行器：依据执行打算执行 SQL 查问语句，从存储引擎读取记录，返回给客户端；
