MYSQL如何执行查询指定的SQL语句？

运行以下 SQL。我们看到的只是输入一条语句，返回一个结果，但是我们不知道这条语句在MySQL中的执行过程。

select * from where id ='1';

上图展示了MySQL的基本架构图，从中可以清晰地看到MySQL各个功能模块中SQL语句的执行过程。一般来说，MySQL可以分为两部分：服务器层和存储引擎层。

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

存储引擎层负责存储和检索数据。其架构模型是插件式的，支持InnoDB、MyISAM、Memory等多种存储引擎。现在使用最广泛的存储引擎是InnoDB，从MySQL 5.5.5版本开始它已经成为标准存储引擎。您还可以通过指定存储引擎类型来选择不同的引擎。例如，在建表语句中使用engine=memory来指定创建表时使用的内存引擎。

一条SQL查询的完整执行流程如上图所示。

服务器服务层

连接

与数据库的连接必须是一开始的连接。连接器负责与客户端建立连接、获取权限、维护和管理连接。连接命令通常这样写：

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

输入命令后，必须在交互对话框中输入密码。虽然也可以在命令行中的 -p 之后直接输入密码，但这可能会导致您的密码泄露。如果您连接到生产服务器，我们强烈建议不要这样做。 connect命令中的

mysql是一个客户端实用程序，用于与服务器建立连接。完成经典的 TCP 握手后，链接将开始使用您输入的用户名和密码验证您的身份。

• 如果用户名或密码不正确，您将收到“用户访问被拒绝”错误，然后客户端程序将终止执行。
• 如果用户名和密码验证成功，链接将检查权限表以确定您拥有哪些权限。之后这方面的权限评估逻辑就会依赖于当时读取到的权限。

这意味着用户创建连接后，即使使用管理员帐户更改用户的权限，也不会影响现有连接的权限。更改完成后，只有新连接才会使用新的权限设置。

连接完成后，如果没有后续操作，连接将处于空闲状态，可以在show process list命令中看到。文中图片是显示进程列表的结果。命令列显示“Sleep”行，这意味着系统中存在非活动连接。

如果客户端长时间不活动，链接会自动断开连接。这个时间由wait_timeout参数控制，默认值为8小时。

如果客户端在连接断开后重新发送请求，会收到错误提醒：在查询过程中丢失与MySQL服务器的连接。如果此时想继续，则必须重新连接，然后执行请求。

在数据库中，长连接是指连接成功后，如果客户端继续发送请求，将一直使用同一个连接。短连接是指执行几次查询后连接会断开，并为下一个查询重新建立新的连接。

建立连接的过程通常比较复杂，所以我建议您在使用过程中尽量减少建立连接的动作，即尽量使用长连接。但是，当所有长连接都使用后，你可能会发现MySQL占用的内存增长得非常快。这是因为MySQL在运行时临时使用的内存是在连接对象中管理的。当连接断开时，这些资源将会被释放。因此，如果长连接积累起来，可能会占用过多的内存而被系统强行杀死（OOM）。从现象来看，MySQL异常重启。

如何解决这个问题？您可以考虑以下两种选择。

1. 定期断开长连接。使用一段时间后，或者程序检测到执行了大内存消耗的查询后，断开连接，然后声明该查询，然后重新连接。
2. 如果您使用的是 MySQL 5.7 或更高版本，您可以在每次主要操作后执行 mysql_reset_connection 来重新初始化连接资源。此过程不需要重新连接和权限验证，而是会将连接恢复到刚刚创建时的状态。

查询缓存

连接建立后，就可以执行select语句了。执行逻辑将进入第二阶段：查询缓冲区。

MySQL收到查询请求后，首先会去查询缓冲区看这条语句之前是否执行过。之前执行的语句及其结果可以以键值对的形式直接缓存在内存中。键是查询短语，值是搜索结果。如果您的查询可以直接在此缓存中找到键，则该值将直接返回给客户端。

如果该语句不在查询缓冲区中，则后续执行阶段将继续。执行完成后，执行结果将存储在查询缓冲区中。可以看到，如果查询命中了缓存，MySQL就可以直接返回结果，不需要进行后续的复杂操作，效率非常高。

但在大多数情况下我建议你不要使用查询缓存，为什么？因为搜索缓存往往弊大于利。

查询缓冲区经常过期。只要表有更新，该表上的所有查询缓存都会被刷新。因此，您可能费心保存结果，但在使用它们之前，它们已被更新删除。对于更新压力较大的数据库，查询缓冲区的命中率会很低。除非你的企业有一个静态表，很长一段时间才会更新一次。例如，如果有一个系统配置表，则该表中的查询适合查询缓存。

幸运的是，MySQL也提供了这种“按需使用”的方法。您可以将 query_cache_type 参数设置为 DEMAND，以便查询缓存不用于标准 SQL 语句。对于确定会使用查询缓存的语句，可以使用SQL_CACHE显式指定，比如下面的语句：

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

需要注意的是，MySQL 8.0版本直接删除了整个查询缓存功能，这意味着从 8.0 开始该功能将不再可用。

分析器

如果查询缓冲区未命中，则将开始实际执行语句。首先，MySQL需要知道你想要做什么，因此它需要解析SQL语句。

分析器首先会进行“词法分析”。您输入的是由多个字符串和空格组成的 SQL 语句。 MySQL需要识别其中的字符串是什么以及它们代表什么。

MySQL 从您输入的“select”关键字识别出这是一条查询语句。它还将字符串“T”识别为“表名 T”，将字符串“ID”识别为“列 ID”。

完成这些识别后，我们需要做“语法分析”。语法分析器根据词法分析的结果，根据语法规则判断你输入的SQL语句是否满足MySQL语法。

如果您的语句不正确，您将收到错误提醒“您的SQL语法有错误”。例如，在下面的句子中，缺少首字母“s”。

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

一般情况下，语法错误都会指出错误发生的第一个地方，所以要注意“在附近使用”之后紧接的内容。

优化器

通过分析器后，MySQL就知道你要做什么。在执行开始之前，必须经过优化器的处理。

当表中有多个索引时，优化器决定使用哪个索引；或者当一条语句有多个表连接（joins）时，它决定每个表的连接顺序。一般来说，两种执行方式的逻辑结果是相同的，但是执行效率会有所不同，而优化器的作用就是决定采用哪种方案。优化阶段完成后，确定该语句的执行计划，然后进入执行阶段。

执行SQL查询时，优化器主要执行以下任务：

1. 选择最合适的索引；
2. 选择表扫描或索引；
3. 选择表连接顺序；
4. 优化where子句；
5. 排除管理中无用的表；
6. 决定order by和group by是否应该建立索引；
7. 尝试用内接头代替外接头；
8. 简化子查询并确定结果缓存；

MySQL查询优化 服务器有几个目标，但最重要的是尽可能多地使用索引，并且使用尽可能严格的索引来消除尽可能多的数据行。

优化器尝试排除数据行的原因是，它排除数据行的速度越快，找到与条件匹配的数据行的速度就越快。如果首先执行最严格的测试，则可以更快地执行查询。

执行器

MySQL通过分析器知道你要做什么，通过优化器知道怎么做，于是就进入执行阶段，开始执行语句。
开始执行时，首先要判断是否有权限对该表T执行查询，如果没有，会返回无权限错误，如下所示（项目实现中，如果查询缓冲区被命中，当查询缓冲区返回结果时，会进行权限验证（查询也会在优化器之前调用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并不完全相同。

储能电机

通过显示电机查看电机类型；可以看到只有InnoDB引擎类型可以用于事务。

InnoDB 存储引擎

InnoDB 是 MySQL 默认的事务引擎，也是最重要、使用最广泛的存储引擎，具有行级锁定和外键约束。它旨在处理大量短期交易。大多数情况下，短期事务都会正常提交，很少会回滚。 InnoDB 的性能和自动崩溃恢复功能使其在非事务性存储需求方面广受欢迎。除非有非常具体的原因需要使用不同的存储引擎，否则应首选 InnoDB 引擎。

InnoDB 目前的场景/特性包括：

• 频繁更新的表适合处理多个并发的更新请求。
• 支持交易。
• 可以从灾难中恢复（通过二进制日志等）。
• 外键限制。只有他支持外键。
• 支持列属性auto_increment自动递增。

MyISAM存储引擎

MyISAM提供了大量的功能包括全文搜索、压缩等，但不支持事务和行级锁，但支持表级锁。对于只读数据，或者表较小且能承受修复操作的场景，仍然可以使用MyISAM。

目前MyISAM的场景/特性包括：

• 不支持事务性设计，但这并不意味着有事务性操作的项目不能使用MyISAM存储引擎。这个可以根据自己的业务在程序层面进行。需要相应的控制。
• 不支持带有外键的表设计。
• 查询速度非常高，如果数据库有很多插入和更新操作，比较适合。
• 全天锁定时钟的场景。
• MyISAM 非常重视快速读取操作。
• MyIASM 存储的是表中的行数，因此当 SELECT COUNT(*) FROM TABLE 时，只需直接读取存储的值，无需进行全表扫描。如果表的读操作远多于写操作并且不需要数据库事务的支持，MyIASM 也是一个不错的选择。

MySQL内置的其他存储引擎

MySQL还有一些特殊的存储引擎，在一些特殊场景下使用起来非常好。在新版本的 MySQL 中，某些功能可能会因某种原因不再受支持，而另一些功能将继续受支持，但必须显式启用才能使用。

归档存储引擎

归档引擎仅支持插入和选择操作，在MySQL 5.1之前甚至不支持索引。

归档引擎会缓存所有写入，并使用zlib来压缩插入的行，因此它比MyISAM引擎需要更少的磁盘I/O。但每次选定的搜索都需要全表扫描，因此Archive更适合日志记录和数据采集应用，而此类应用在进行数据分析时往往需要全表扫描。

归档引擎支持行级锁和专用缓冲区，从而可以实现高并发插入。在开始查询直到返回表中的所有行之前，存档引擎会阻止其他选定的运行以实现一致的读取。此外，这还使得批处理帖子在读取操作完成之前不可见。

Blackhole 存储引擎

Blackhole 引擎没有实现任何存储机制，如果没有存储，它将丢失所有插入的数据。奇怪了，不是没什么用吗？不过，服务器会记录Blackhole日志，因此可以用来将数据复制到备库，或者干脆将其记录在日志中。这个特定的存储引擎可能在特定的复制架构和日志审计中发挥作用。

但是这个存储引擎的存在还是有点难以理解。

CSV 存储引擎

CSV 引擎可以将常规 CSV 文件作为 MySQL 表处理，但此类表不支持索引。

CSV 引擎可以在数据库运行时将文件复制入或出。它可以将Excel等电子表格软件中的数据保存为CSV文件，然后将其复制到MySQL数据目录中，然后在MySQL中打开并使用。同样，如果将数据写入 CSV 引擎表，其他外部程序可以立即从表的数据文件中读取 CSV 格式的数据。

因此，CSV 引擎作为一种数据交换机制非常有用。

内存存储引擎

如果您需要快速访问数据，并且重启后数据不会被更改或丢失，那么使用内存引擎非常有用。内存引擎至少比MyISAM引擎快一个数量级，因为所有数据都存储在内存中，不需要磁盘I/O。重启后内存引擎的表结构会保留，但数据会丢失。

内存引擎可以在很多场景中发挥很好的作用：

• 用于查找或映射表，例如将邮箱映射到状态名称的表。
• 用于缓存数据周期性聚合的结果。
• 用于存储数据分析过程中产生的中间数据。

内存引擎支持哈希索引，因此查找速度非常快。虽然内存非常快，但它无法取代传统的基于磁盘的表。内存引擎使用表级锁，并发访问性能较低。

如果MySQL在查询执行过程中需要使用临时表来存储中间结果，则所使用的临时表是内存引擎内部的。如果中间结果太大超过内存限制，或者包含BLOB或TEXT字段，临时表将转换为MyISAM引擎。

看完上面的解释，大家经常会混淆Memory和临时表。临时表是指使用 CREATE TEMPORARY TABLE 语句创建的表。它可以使用任何存储引擎，所以它与Memory不一样。临时表仅在单个连接内可见，并且当连接断开时将不再存在。

有关临时表和内存引擎的信息，请参阅MySQL・引擎特性・临时表。

MySQL存储引擎和第三方存储引擎有很多，这里就不一一介绍了。如果有必要，我们稍后会详细讨论它们。

如何选择合适的存储引擎

这么多的存储引擎真是让人眼花缭乱，我们应该如何选择呢？

大多数情况下都会选择默认的存储引擎——InnoDB，这也是最正确的选择，所以Oracle最终将InnoDB作为MySQL 5.5中的默认存储引擎。

如何选择合适的存储引擎可以用一句话来概括：“除非你需要使用一些InnoDB没有的功能，并且没有其他替代品，否则你应该优先考虑InnoDB引擎”。

例如，如果要使用全文检索，建议优先考虑InnoDB加Sphinx的组合，而不是使用支持全文检索的MyISAM。当然，如果你不需要InnoDB的特性，而其他引擎的特性可能更能满足你的需求，那么你可以考虑其他存储引擎。
除非绝对必要，否则建议不要混合使用多个存储引擎，否则可能会导致许多复杂的问题，以及一些潜在的错误和边界问题。

如果需要使用不同的存储引擎，建议考虑以下因素。

• 事务
• 备份
• 恢复
• 独特功能

其他搜索引擎 SQL

显示以下标准存储引擎。

mysql> show variables like '%storage_engine%';
--查看表的存储引擎
show table status like "table_name" ;

本文分享自华为云社区《一条查询 SQL 是如何执行的》，作者：一起喝一杯吧。