1 MySQL查询
MySQL 作为互联网行业使用最多的关系型数据库之一，与其免费、开源的特性是密不可分的。然而，很多小伙伴工作了很多年，只知道使用 MySQL 进行 CRUD 操作，这也导致很多小伙伴工作多年后，想跳槽进入大厂，却在面试的时候屡屡碰壁。
问个简单的问题：select 语句是如何在 MySQL 中执行的？ 这也是很多面试官喜欢问的问题，如果你连这个简单的问题都不能回答的话，那就要好好规划下自己的职业生涯了。
好了，今天我们就一起来聊聊 select 语句是如何在 MySQL 中执行的
1.1 sql语句的执行顺序
FROM
<left_table>
ON
<join_condition>
<join_type>
JOIN
<right_table>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
SELECT
DISTINCT
<select_list>
ORDER BY
<order_by_condition>
LIMIT
<limit_number>
1.2 频繁使用的select语句
为了更好地贯穿全文，这里先来列举一个最简单的 select 查询语句，例如：查询 user 表中 id 为 1001 的用户信息，使用下面的 SQL 语句进行查询。
select * from user where user_id = 1001;
当我们在 MySQL 的命令行中输入上述 SQL 语句时，这条 SQL 语句到底在 MySQL 中是如何执行的呢？接下来，我们就以这条 SQL 语句为例，说说 select 语句是如何在 MySQL 中执行的。
1.3 MySQL 逻辑架构
在介绍 select 语句在 MySQL 中的执行流程之前，我们先来看看 MySQL 的逻辑架构，因为任何 SQL 语句的执行都离不开 MySQL 逻辑架构的支撑。也就是说， SQL 语句在 MySQL 中的执行流程与 MySQL 的逻辑架构是密不可分的
大致图：
详细图：
在上图中，我们简单地画了下 MySQL 的逻辑架构图，并且给出了逻辑分层和每层中各部分的功能。从逻辑上，我们可以将 MySQL粗略地分成三层：Server 层、存储引擎层和系统文件层，而 Server 层中又可以分成网络连接层（连接器）和数据服务层（Server 层）
Server 层中包含了连接器、查询缓存、分析器、优化器和执行器等 MySQL 的核心组成部分，另外，在 Server 层中还包含了所有的内置函数（比如：日期时间函数、加解密函数、聚合函数、数学函数等），存储引擎、触发器、视图等等。
存储引擎层主要负责和系统文件层进行交互，存储引擎层本身是插件式的架构设计，支持 InnoDB、MyISAM、Archive、Memory 等存储引擎。在 MySQL 5.5.5 及以后的版本中，MySQL 的默认存储引擎是 InnoDB。
系统文件层主要负责存储实际的数据，将数据以文件的形式存储到服务器的磁盘上。
Server 层，它包括连接器、查询缓存、分析器、优化器、执行器等。比如存储过程，触发器，视图都是在这一层实现的。
-
连接器Connection Manager）：负责处理客户端与服务器之间的连接。它接受来自客户端的请求，并进行身份验证和权限检查，建立和管理连接。 -
查询缓存（Query Cache）：在旧版MySQL中有，但在较新的版本中已不推荐使用。它能够缓存查询和对应的结果，以提高查询性能。然而，在高并发和大型数据库中，它反而可能成为性能瓶颈，因为它在某些情况下会引起锁和不必要的开销。 -
分析器（Parser）：负责分析 SQL 查询语句，验证其语法和语义，确保查询的正确性。它将 SQL 语句转换成内部数据结构供优化器和执行器使用。 -
优化器（Optimizer）：接收来自分析器的查询请求，并决定如何最有效地执行查询。优化器的目标是找到最佳的执行路径，选择合适的索引、连接顺序和访问方法，以提高查询性能。 -
执行器（Executor）：负责执行优化器生成的执行计划，获取存储引擎返回的数据，并处理客户端请求。它与存储引擎交互，执行查询并返回结果给用户。 -
存储引擎层：它负责数据的存储和提取。Mysql支持InnoDB、MyISAM、Memory 等多个存储引擎。我们日常开发中，一般用的存储引擎就是InnoDB。从 MySQL 5.5 版本开始，InnoDB 就成为了默认的存储引擎。
1.4 连接器是如何授权的
首先，我们先来看看在服务器命令行输入连接 MySQL 的命令时，MySQL 的连接器是如何进行验证的。比如，我们在服务器的命令行输入了如下命令
mysql -ubinghe -p
执行“回车”后，输入 binghe 账户的密码，与 MySQL 进行连接。此时，连接的过程需要完成经典的 TCP 握手操作。之后，连接器就开始认证连接的身份是否合法，最直接的就是验证用户名和密码是否正确。
如果用户名或者密码错误，MySQL 会提示 Access denied for user。如果用户名和密码正确，则连接器会到 MySQL 的权限表中查询当前连接拥有的权限。查询到权限之后，只要这个连接没有断开，则这个连接涉及到的权限操作都会依赖此时查询到的权限。
换句话说，一个用户登录 MySQL 并成功连接 MySQL 后，哪怕是管理员对当前用户的权限进行了修改操作，此时只要这个用户没有断开 MySQL 的连接，就不会受到管理修改权限的影响。管理员修改权限后，只有对新建的连接起作用。
如果客户端连接 MySQL 后，长时间没有执行任何操作，则连接器会自动断开与这个客户端的连接。具体多长时间断开是由 MySQL 的参数wait_timeout控制的，这个值默认是 8小时。我们可以根据实际业务需要，自行调整这个参数的值，以使 MySQL 能够满足我们的实际业务场景。
由于客户端与 MySQL 的连接是比较复杂的，这个过程也是比较耗时的，它会涉及 TCP 的握手操作，还会查询当前连接的权限信息等。往往在实际的工作过程中，我们会使用数据库连接池的方式，将数据库的连接缓存起来，这就意味着我们是使用长连接与 MySQL 进行交互的。
但是使用长连接连接 MySQL 也会有一个问题：那就是有时候会发现 MySQL 占用的内存涨得特别快，这是因为 MySQL 在执行的过程中，使用的临时内存是在连接对象里面进行管理的。这些占用的资源只有在连接断开的时候，才会被释放。如果连接长时间不释放，就会出现大量的临时内存占用内存空间。如果时间久了，可能会导致占用过多的内存，从而被操作系统“消灭”了，给人的感觉就是 MySQL 意外重启了。
我们可以使用如下的方案来解决这个问题：
- 定期或者执行过一个比较占内存的查询操作后，断开连接，以后再重新建立和
MySQL的连接 - 如果使用
MySQL 5.7或更新的MySQL版本，可以通过执行mysql_reset_connection重新初始化 MySQL 的资源。重新初始化的过程不会重新连接 MySQL，也不会重新做权限的验证操作。
1.5 查询缓存的作用是什么
登录 MySQL 后，客户端就会与 MySQL 建立连接，此时执行 select 语句时，首先会到查询缓存中查询是否执行过当前 select 语句。如果之前执行过相应的 select 语句，则执行过的 select 语句和查询结果会以 key-value 的形式存放在查询缓存中，其中，key 是查询语句，value 是查询的结果数据。
如果在查询缓存中没有找到相应的数据，则会继续执行后续的查询阶段。执行完成后，会将结果缓存到查询缓存中。后续的查询如果命中缓存，则直接返回查询缓存中的数据，性能还是挺高的。
但是，大多数时候我不太建议小伙伴们开启查询缓存，为啥？原因很简单：查询缓存失效的频率是非常频繁的，只要对一个表进行更新操作，则这张表上所有的查询缓存都会被清空。 而且在 MySQL 8.0 中，直接删除了查询缓存的功能。因为在高并发和大型数据库环境下，查询缓存可能导致性能问题，并且在实际测试中发现，禁用查询缓存可能会提高整体性能和可伸缩性。
1.6 逻辑架构中各种分析器
1.6.1 分析器
分析器主要是对 select 语句进行 词法分析和语法分析 操作
如果 select 语句没有命中缓存，则首先会由分析器对其进行词法分析操作，此时，MySQL 会识别 select 语句中的每个字符串代表什么含义
例如，MySQL 会通过select关键字识别出这是一个查询语句，也会把user识别为数据表名 user，把id识别成字段名 id。接下来，就要进行“语法分析了”，根据语法规则，判断 select语句是否满足 MySQL的语法。如果判断出输入的 SQL 语句不满足语法规则，则 MySQL 会提示相应的错误信息。
语法分析主要就是判断SQL是否满足MySQL的语法。
1.6.2 优化器
对 select 语句进行了词法分析和语法分析后，还要经过优化器的优化处理才能执行。比如，我们的 select 语句中如果使用了多个索引，则优化器会决定使用哪个索引来查询数据；再比如，在 select 语句中，有多表关联的操作，优化器会决定各表的连接顺序，数据表的连接顺序不同，对于执行的效率会大不相同，优化器往往会选择使用查询效率高的连接顺序。
如果 select 语句经过优化器的优化之后，就会进入执行阶段了。
对于简单的插入SQL语句，优化器并不会执行复杂的查询计划生成工作。
优化器会处理索引的选择与维护，但并不涉及复杂的查询优化。如果表中存在主键或索引，优化器会确保索引的更新以保证数据一致性，并在插入数据时检查约束条件。
INSERT语句也会生成执行计划，它详细描述了数据库如何访问数据、使用哪些索引、以及数据的处理顺序等
1.6.3 执行器
1.6.3.1 select 操作
进入执行阶段的 select 语句，首先，执行器会对当前连接进行权限检查，最直接的方式就是检查当前连接是否对数据表 user 具有查询权限。如果当前连接对数据表 user 没有查询权限，就会返回没有权限的错误。例如，会返回如下错误。
ERROR 1142 (42000): SELECT command denied to user 'binghe'@'localhost' for table 'user'
如果当前连接具有对数据表 user 的查询权限，则会继续执行。首先会进行打开数据表的操作，此时优化器会根据创建表时使用的存储引擎，使用相应存储引擎的接口执行查询操作。这里，我们举一个例子：
假设，我们在 id 字段上没有建立索引，执行器执行的流程大致如下所示。
- 通过存储引擎读取数据表
user的第一行数据，判断当前行的 id 值是否等于 1001，如果不等于 1001，则继续读取下一行数据；如果等于 1001，则将当前行放入结果集中。 - 继续通过存储引擎读取下一行数据，执行与（1）相同的逻辑判断，直到处理完 user 表中的所有数据。
- 处理完所有的数据后，执行器就会将结果集中的数据返回给客户端。
如果在 id 字段上有索引的话，执行的整体逻辑与 id 字段上没有索引大体一致。
如果开启了慢查询的话，执行 select 语句时，会在慢查询日志中输出一个 rows_examined 字段，这个字段表示 select 语句在执行的过程中扫描了数据表中的多少行数据。不过在有些场景下，执行器调用一次，存储引擎内部会会扫描多行，这就导致存储引擎扫描的行数与 rows_examined 字段标识的行数并不完全相同
转载于：https://mp.weixin.qq.com/s/bxVOPXU_ftZEVTxmW_6NAw
1.6.3.2 insert 操作
执行器负责执行具体的 插入SQL 操作，是数据库系统的核心执行模块。对于INSERT语句，执行器会负责实际的数据写入过程。
-
确定插入位置：根据优化器的执行计划，执行器会决定将数据插入表的具体位置，比如根据主键或唯一索引找到插入点。 -
加载数据页：如果要插入的数据页在内存（Buffer Pool）中，则直接使用；如果不在内存中，则需要从磁盘加载对应的数据页到内存。 -
更新索引：如果表中有索引（如主键、唯一索引或其他索引），执行器也会相应更新这些索引。
其他相关：
- Buffer Pool
Buffer Pool是MySQL InnoDB存储引擎中的一块内存区域，专门用来缓存数据库表的数据页、索引页等内容。它的主要目的是提高数据读写性能，减少磁盘 I/O 操作.
数据写入内存中的数据页：执行器将新数据插入到Buffer Pool中的相应数据页。这是一个内存操作，而不是直接修改磁盘上的文件。 - undo log
生成Undo Log在真正插入数据之前，InnoDB会生成undo log。对于插入操作，undo log记录的是如何删除当前插入的记录（这用于事务回滚时撤销插入操作）。
在事务回滚时，MySQL需要撤销未提交的操作。通过undo log，MySQL 能够删除已经插入但未提交的记录，确保事务的原子性。 - Redo Log
执行器在插入数据后，立即将这个操作记录在redo log中
写入redo log，为了确保数据的可靠性，MySQL采用了预写日志（Write-Ahead Logging, WAL）机制。在数据真正写入磁盘前，首先会将这个操作记录在redo log中。
MySQL首先将操作写入redo log，并标记为预提交（prepare）状态。这意味着如果崩溃，MySQL可以通过redo log将操作重做，从而恢复数据。 - 写入 Binlog
在写入redo log的同时，MySQL还会将这次操作写入binlog，用于数据库复制和灾难恢复。
binlog是MySQL的逻辑日志，记录了SQL操作细节，比如（INSERT INTO）。不同于redo log的物理日志。 - 事务提交（两阶段提交）
在两阶段提交机制下，MySQL会在事务提交时更新redo log为commit状态。
为什么需要两阶段提交？
确保binlog和redo log的一致性。如果系统崩溃，MySQL可以通过redo log重做操作，并通过binlog进行恢复。 - 数据刷入磁盘
执行器不会立即将内存中的脏页同步到磁盘。后台线程会根据一定的策略（如定时刷新等），异步地将Buffer Pool中的脏页刷到磁盘上的表空间文件中。这样可以避免频繁的磁盘 I/O 提高性能。
1.7 查询问题分析
1.7.1 limit耗时分析
1.7.2 查询分组后前三条数据
1.7.3 SQL模式匹配
1.7.3.1 SQL模式
SQL的模式匹配允许你使用_匹配任何单个字符，而%匹配任意数目字符（包括零个字符）。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或！=；而使用LIKE或NOT LIKE比较操作符。
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
其中关于条件，SQL提供了四种匹配模式：
-
%：表示任意个或多个字符。可匹配任意类型和长度的字符。
比如SELECT * FROM ［user］ WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件SELECT * FROM ［user］ WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’ -
_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：（可以代表一个中文字符）
比如SELECT * FROM ［user］ WHERE u_name LIKE ‘_三_’
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
再比如SELECT * FROM ［user］ WHERE u_name LIKE ‘三__’;
只找出“三脚猫”这样name为三个字且第一个字是“三”的;
1.7.3.2 正则模式
由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符（或RLIKE和NOT RLIKE，它们是同义词）。
扩展正则表达式的一些字符是：
-
.匹配任何单个的字符。（单字节字符）
一个字符类［…］匹配在方括号内的任何字符。例如，［abc］匹配“a”、“b”或“c”。为了命名字符的一个范围，使用一个-。［a-z］匹配任何小写字母，而［0-9］匹配任何数字。 -
*匹配零个或多个在它前面的东西。例如，x*匹配任何数量的x字符，［0-9］*匹配的任何数量的数字，而.*匹配任何数量的任何东西。
正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“［aA］”匹配小写或大写的“a”而“［a-zA-Z］”匹配两种写法的任何字母。如果它出现在被测试值的任何地方，模式就匹配（只要他们匹配整个值，SQL模式匹配）。
为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用^或在模式的结尾用$。
为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写：
为了找出以三开头的名字，使用^匹配名字的开始。
FROM ［user］ WHERE u_name REGEXP ‘^三’;将会把u_name为 “三脚猫”等等以“三”开头的记录全找出来。
为了找出以三结尾的名字，使用$匹配名字的结尾。
FROM ［user］ WHERE u_name REGEXP ‘三$’;将会把u_name为“张三”，“张猫三”等等以“三”结尾的记录全找出来。
也可以使用{n}“重复n次”操作符重写先前的查询：FROM ［user］ WHERE u_name REGEXP ‘b{2}$’;
