mysql查询底层原理及join的底层分析

拼搏现实的明天。 2023-02-11 07:16 143阅读 0赞

为了弄清楚mysql查询原理，我们得先了解mysql基础架构，然后再分析原理，最后根据具体例子分析

#  一、  **mysql基础架构** #

# **   **![format_png][] #

**1、连接器管理**  
      首先是数据库连接器，主要负责和客户端建立连接、权限获取、管理连接等，由于整个建连的过程比较复杂，所以尽量使用长连接。如果数据库发生异常后为了快速恢复，可重启系统重新建立连接。  
**2、Mysql缓存**  
     mysql请求首先看缓存数据，key为sql语句value为查询的结果，如果存在则直接返回。如果没有则直接往下走。  
注意：mysql缓存对于一些静态数据比较适合，对于实时性高的数据最好不要使用。  
**3、分析器**  
    对你执行的sql语句进行解析，首先是词法分析包括一些关键字识别，然后语法分析，查看这条语句是否符合mysql语句  
**4、优化器**  
    通过你的语句分析，发现那些查询命中索引，还有表之间的连接顺序等  
**5、执行器**  
     通过上面一系列的验证，使用引擎提供的接口。经过不断的执行将查询的结果存放在结果集中，通过explain可以看到执行器具体扫描了多少行。

#    **二、sql语句的执行流程** #

首先要清楚redo log和binlog两个日志模块  
      1、redo log（InnoDB特有的日志模块） 重做日志文件，用于记录事务操作的变化，记录修改后的值，不管事务是否提交。保证数据的完整性。其中redo log是固定大小的，是从头开始写，写到末尾在从头开始。同时会有两个指针，一个记录写入的位置，一个标记，当前擦除的位置，不断的循环。整个过程称为crash-safe。即时数据库异常，也会有记录  
      2、binlog 归档日志文件，用于记录对mysql数据库执行更改的所有操作。binlog是追加写，不会覆盖之前的。  
      接下来介绍一下mysql更新一条语句的流程。  
     `update tb_area SET area_name = "beijing" WHERE area_id = 1`  
    1） 首先执行器通过id查到这条记录（搜索树或者查找数据页） ，并加载到内存中。  
    2）然后对这条记录的area\_name调用引擎写入接口，进行修改。  
    3）修改内存中的值，同时更新redolog告知执行器完成写入（状态置为prepare），可以提交事务，执行器将这条操作记录记录在binlog，写入磁盘  
    4）完成上述一系列的操作，执行器调用事务提交接口（redolog状态置为commit），完成更新操作。

**注意：**Mysql的redolog模块写入拆成2步走，prepare和commit，**称为两阶段提交。** 整个过程为1、redolog的prepare状态 2、binlog的写入 3、redolog的commit状态，保证Mysql的可靠性。  
     如果binlog没有写入并没有提交事务回滚  
     如果binlog写入事务没提交，数据库回复后自动完成commit

# 三、MySQL查询过程 #

当向MySQL发送一个请求的时候：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70][]

1.客户端/服务端通信协议  
       MySQL客户端/服务端通信协议是“半双工”的：在任意时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接受完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max\_allowed\_packet参数。但是需要的注意的是，如果查询实在是太大，服务端会拒绝接受更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接受整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT \* 以及加上LIMIT限制的原因之一。

2.查询缓存  
       在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT\_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT\_USER或者CONNECION\_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

3.缓存失效  
       MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1.任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

2.如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化：比如：

1.用多个小表代替一个大表，注意不要过度设计

2.批量插入代替循环单条插入

3.合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

4.可以通过SQL\_CACHE和SQL\_NO\_CACHE来控制某个查询语句是否需要进行缓存

不要轻易打开查询缓存，特别是写密集型应用。如果实在是忍不住，可以将query\_cache\_type 设置为DEMAND，这时只有加入SQL\_CACH的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

4.语法解析和预处理  
       MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

5.查询优化  
      语法树被认为是合法之后，并且有优化器将其转化成查询计划，多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果，优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last\_query\_cost的值来得到其计算当前查询的成本。（show status like 'last\_query\_cost'）

show status like 'last\_query\_cost';  
\+-----------------+-------------+| Variable\_name | Value |+-----------------+-------------+| Last\_query\_cost | 6391.799000 |+-----------------+-------------+  
       示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

1.重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2.优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）

3.提前终止查询（使用Limit时，查找到满足数量的结果集后会立即终止查询）

4.优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序）

6.查询执行引擎  
       在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示，实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像塔积木一样完成了一次查询的大部分操作。

7.返回结果给客户端  
      查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

总结下mysql整个查询流程如下

1.客户端向MySQL服务器发送一条查询请求

2.服务器首先先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一级段

3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划

4.MySQL根据执行计划，调用存储引擎的API来执行查询

5.将结果返回给客户端，同时缓存查询结果

#    四、Join的实现原理 #

#    #

5.5 版本之前，MySQL本身只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

mysql底层join实现只支持一种算法：嵌套循环连接（Nested-Loop Join）,nested-Loop-Join有三种变种：

Simple Nested-Loop Join 简单嵌套循环连接

Index Nested-Loop Join 索引嵌套循环连接

Block Nested-Loop Join  块索引嵌套连接

1.Simple Nested-Loop Join：  
如图，A为驱动表，B为匹配表。 从A中取出数据1，遍历B，将匹配到的数据放到result.. 以此类推，每条A表数据都会轮询B表。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 1][]

2.Index Nested-Loop Join（索引嵌套）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 2][]  
这个需要查询时，关联非驱动表（也就是匹配表）的索引，通过索引来减少比较，加速查询。

在查询时驱动表会根据关联字段的索引 到非驱动表查找数据，找到对应的值，此时分为两种情况：

如果索引不是主键索引的话，需要进行回表查询（根据索引携带的主键信息查询数据） 不是主键时，要进行多次回表查询，先关联索引，再根据主键ID查询，性能上要慢很多。

如果关联字段是非驱动表的主键时，性能会非常高，直接就能定位到数据。

这里不懂的可以看下我的这个博客中的Inoodb引擎下的索引与主键存储图：

3.Block Nested-Loop Join（块嵌套）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 3][]  
如果关联的是非驱动表的索引会走索引嵌套，但如果join的列不是索引，就会采用Block Nested-Loop Join。  首先将驱动表的结果集中 所有与join相关的列都先缓存到join buffer中（这样当查找完成时，就可以将匹配到的记录从内存与非驱动表放到result返回），然后批量与匹配表进行匹配，将第一种中的多次比较合并为一次，降低了非驱动表的访问频率。 默认情况下join\_buffer\_size=256K（可以通过show variables like 'join\_%' 查看大小）。

BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.  
举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.

当有两个表以上join时：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 4][]

两个表以上join时也会用到join buffer，会将前两个表的结果集缓存下来，然后与第三个表比较，再返回result。建议把join buffer开大点，因为当join buffer不够用时会对数据进行分段（例如将后一千条数据放入硬盘）将内存存不下的数据放入硬盘，这样读写会产生IO从而减缓速度。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85MTgyNjQxLThmOGZhYmVmYTIyMjJhZGEuanBn?x-oss-process=image/format,png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200527112003642.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020052316123730.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200523161304557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200523161443965.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020052316152689.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3liNTQ2ODIyNjEy,size_16,color_FFFFFF,t_70