mysql查询执行顺序与优化

最新推荐文章于 2024-08-15 23:27:38 发布

runtoweb3

最新推荐文章于 2024-08-15 23:27:38 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： sql优化

本文链接：https://blog.csdn.net/littlexiaoshuishui/article/details/102551116

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

一条sql语句交到mysql数据库

连接器

client 首先要与 MySQL 建立连接，这就需要一个连接器，负责与 client 建立连接、权限验证、管理连接。

分析器

client 和 server 连接完成了，向 server 发送 sql 请求，连接器不会直接处理，会转给分析器，对这条 sql 进行词法分析，例如识别出来“select”关键字，知道这是一个查询语句，识别出表明、字段名等，词法分析之后就是语法分析，判断是否满足 mysql 语法。

优化器

执行器（server层和innodb的交互）

在执行器的阶段,此时会调用存储引擎的API，每个表由一个handle实例表示，在优化器阶段已经为每个表创建了handle实例。server端与存储通过不同的存储引擎接口连接起来，公共接口定义在handler API中。 server层和存储引擎层的交互是以记录为单位的。

但是什么操作是在server层做的，什么操作是在存储引擎层做的大家可能有些迷糊。

例如：SELECT * FROM hero WHERE name < 's孙权' AND country = '蜀';

1.server层第一次开始执行查询，把条件name < 's孙权'交给存储引擎，让存储引擎定位符合条件的第一条记录。

2.存储引擎在二级索引idx_name中定位name < 's孙权'的第一条记录，然后拿着该二级索引记录中的主键值去回表，把完整的用户记录都取到之后返回给server层（也就是说得到一条二级索引记录后立即去回表，而不是把所有的二级索引记录都拿到后统一去回表）。

3.意味着server层在接收到存储引擎层返回的记录之后，接着就要判断其余的WHERE条件是否成立（就是再判断一下country = '蜀'是否成立）。如果成立的话，就直接发送给客户端。

4.接着server层向存储引擎层要求继续读刚才那条记录的下一条记录。

mysql联表查询的算法

https://www.cnblogs.com/zuochanzi/p/10409752.html

Nested-Loops Join（嵌套循环联接）

首先确定驱动表，就是第一个查询的表，比如左连接的话，左边的表就是驱动表，内连接的话mysql会选择过滤条件大的作为驱动表，查出驱动表的数据，然后循环每一条驱动表的数据，根据on的条件去查询被驱动表的数据，相当于对被驱动表进行单表查询，然后把结果放到临时表中。也就是说联表查询中，驱动表只要查询一次，得到n条记录，然后需要查询被驱动表n次。

例如表t1,t2联表查询，on 条件为t1.mid = t2.nid, 查询t1表数据有两条

t1.mid = 1时，对被驱动表t2的查询条件为 t2.nid = 1,查出t2所有符合条件的数据

t1.mid = 2时，对被驱动表t2的查询条件为 t2.nid = 2,查出t2所有符合条件的数据

所以说联表查询优化：

首先是要尽量减少驱动表的查询结果数

然后就是被驱动表的on条件字段要加上索引

MySQL数据库根据不同的使用场合又衍生出改进的嵌套算法：

Simple Nested-Loops Join（NLJ）算法

直接扫描被驱动表数据，来匹配每一个驱动表的行

Index Nested-Loops Join（INLJ，基于索引的嵌套循环联接）

如果被驱动表的on字段用了索引，则直接查找索引就可以对比，只扫描被驱动表的索引，查找符合的主键索引值，再回表查询被驱动表的数据。缺点就是：如果查找的是辅助索引，并且是范围查找（有很多个符合的主键索引值），因为这些主键索引是离散的，回表的时候就需要大量的随机I/O操作。为此，MySQL 5.6（MariaDB 5.3）开始支持Batched Key Access Join算法（简称BKA），该算法通过常见的空间换时间，随机I/O转顺序I/O，以此来极大的提升Join的性能（随机I/O需要花费昂贵的磁头旋转和定位来查找，因此、顺序IO访问的速度远远快于随机IO）。

回表：innodb二级索引叶子节点存的是主键值，通过二级索引寻找到主键值，再通过一级索引找到对应的行数据。为什么不直接在二级索引存行指针呢？因为数据会修改产生页分裂等操作，所以行的地址其实是会变化的，如果存行指针，这样产生修改操作的时候就要修改所有的二级索引，所以只存主键值的话，数据修改，只修改一级索引即可。

Block Nested-Loops Join（BNL，基于块的嵌套循环联接）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。这也就是Block Nested-Loop Join算法的思想

也就是说在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，因为那个算法太粗暴，不忍直视。数据量大些的复杂SQL估计几年都可能跑不出结果。而Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

其实在这种算法从运算角度上，循环的次数并没有变，但是可以显著减少被驱动表的查询次数。如果驱动表的结果比较小，join buffer可以完全放的下，那被驱动表只需要查一次即可，所以联表查询最好不用*,而是查询需要的字段即可。

但是这种算法也有一个缺点，就是判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源。

MySQL数据库使用Join Buffer的原则如下：

* 系统变量Join_buffer_size决定了Join Buffer的大小。

* Join Buffer可被用于联接是ALL、index、和range的类型。

* 每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer。

* Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放。

* Join Buffer只存储要进行查询操作的相关列数据，而不是整行的记录。

Batched Key Access Join（BKA，批量键访问联接）

在说明BKA前，需要了解mysql5.6后的一个新特性mrr——multi range read。InnoDB由于索引组织表的特性，如果你的查询是使用辅助索引，并且有用到表中非索引列（投影非索引字段，及条件有非索引字段），因此需要回表读取数据做后续处理，过于随机的回表会伴随着大量的随机I/O。而mrr的优化在于，并不是每次通过辅助索引读取到数据就回表去取记录，范围扫描（range access）中MySQL将扫描到的数据存入由 read_rnd_buffer_size 变量定义的内存大小中，默认256K。然后对其按照Primary Key（RowID）排序，然后使用排序好的数据进行顺序回表，大大提高回表效率。

要开启mrr还有一个比较重的参数是在变量optimizer_switch中的mrr和mrr_cost_based选项。mrr选项默认为on，mrr_cost_based选项默认为off。mrr_cost_based选项表示通过基于成本的算法来确定是否需要开启mrr特性。然而，在MySQL当前版本中，基于成本的算法过于保守，导致大部分情况下优化器都不会选择mrr特性。为了确保优化器使用mrr特性

这个 BKA 算法，其实就是综合INLJ算法和 BNL算法

我们知道 INLJ 算法执行的逻辑是：从驱动表一行行地取出 join 条件值，再到被驱动表去做 join。也就是说，对于被驱动表来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。那怎么才能一次性地多传些值给被驱动表呢？方法就是，从驱动表里一次性地多拿些行出来，一起传给被驱动表。既然如此，我们就把驱动表的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。

我们知道 join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。那么，我们刚好就可以复用 join_buffer 到 BKA 算法中。INLJ 算法优化后的 BKA 算法的流程，整个过程如下所示：

如果外部表扫描的是主键，那么表中的记录访问都是比较有序的，但是如果联接的列是非主键索引，那么对于表中记录的访问可能就是非常离散的。因此对于非主键索引的联接，Batched Key Access Join算法将能极大提高SQL的执行效率。BKA算法支持内连接，外连接和半连接操作，包括嵌套外连接。

Batched Key Access Join算法的工作步骤如下：

1. 将外部表中相关的列放入Join Buffer中，查询内部表的索引得到key

2. 批量的将Key（索引键值）发送到Multi-Range Read（MRR）接口。

3. Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行回表数据的读取操作。

4. 返回结果集给客户端。

Batched Key Access Join算法的本质上来说还是Simple Nested-Loops Join算法，其发生的条件为内部表上有索引，并且该索引为非主键，并且联接需要访问内部表主键上的索引。这时Batched Key Access Join算法会调用Multi-Range Read（MRR）接口，批量的进行索引键的匹配和主键索引上获取数据的操作，以此来提高联接的执行效率，因为读取数据是以顺序磁盘IO而不是随机磁盘IO进行的。

mysql子查询in,exit的算法

我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。

select * from account where NOT EXISTS (select * from member where account.`name`=member.`name`)

exist：先执行外部查询语句，然后在执行子查询，子查询中它每次都会去执行数据库的查询，执行次数等于外查询的数据数量。查询数据库比较频繁(记住这点),如果b表再id上加了索引也会走索引.

in: 先查询 in()子查询的数据(1次)，并且将数据放进内存里(不需要多次查询),然后外部查询的表再根据查询的结果进行查询过滤,最后返回结果

mysql排序实现方式和算法

https://www.cnblogs.com/zhoujinyi/p/5437289.html

在实际业务场景中，SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort_buffer_size参数与结果集大小确定

MySQL内部排序主要有3种方式：常规排序、优化排序和优先队列排序

常规排序(单路排序)

(1).从表t1中获取满足WHERE条件的记录，只读主键和排序字段。

(2).对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer

(3).如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)

(4).若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的

(5).循环执行上述过程，直到所有满足条件的记录全部参与排序

(6).扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)

(7).将获取的结果集返回

从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对，这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO，一次是捞(id,col2),第二次是捞(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。对于第二次MySQL本身一个优化，即在捞之前首先将id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去捞记录，将随机IO转为顺序IO

优化排序(双路排序)

常规排序方式除了排序本身，还需要额外两次IO。优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数max_length_for_sort_data，只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式

优先队列排序

5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式--优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M,N 这类排序的问题，虽然仍然需要所有字段参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

问题：堆排序是一个不稳定的排序方法，会导致分页重复现象，例如第一页的最后一个记录同时也会出现在第二页的第一个记录。

关联表查询的排序

如果order by 的字段全部来自驱动表的话，那么在查询第一个表的时候就会进行排序，在explain中，可以看到using filsort.除此之外，mysql会先将关联结果放到临时表中，然后关联结束后，再进行排序。这种情况下，explain时extra会出现Using temporary; Using filesort.如果有limit, limit 也会在排序之后应用，所以即使需要很少的数据，临时表和需要排序的数据也非常大。

Mysql5.6后，改进了有limit的排序，mysql会先排除掉不满足的数据，再进行排序。

排序算法：快速排序、归并排序和堆排序。