mysql查询执⾏顺序与优化
⼀条sql语句交到mysql数据库
连接器
client ⾸先要与 MySQL 建⽴连接，这就需要⼀个连接器，负责与 client 建⽴连接、权限验证、管理连接。
分析器sqliteret源文件
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联表查询的算法
Nested-Loops Join（嵌套循环联接）
英伟达canvas
⾸先确定驱动表，就是第⼀个查询的表，⽐如左连接的话，左边的表就是驱动表，内连接的话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数据库根据不同的使⽤场合⼜衍⽣出改进的嵌套算法：fierceness
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排序实现⽅式和算法
java开发工程师需要什么学历在实际业务场景中，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 也会在排序之后应⽤，所以即使需要很少的数据，临时表和需要排序的数据也⾮常⼤。
mysql语句多表查询Mysql5.6后，改进了有limit的排序，mysql会先排除掉不满⾜的数据，再进⾏排序。
排序算法：快速排序、归并排序和堆排序。
Group by 分组算法
1、使⽤松散（Loose）索引扫描实现 GROUP BY
在利⽤索引时，group by可根据索引，即可对数据分组，此时完全不⽤去访问表的数据值（索引健对应的数据）。这种实现⽅式就是利⽤松散索引。
什么情况使⽤松散索引？
2.  索引为（c1,c2）,
2.索引（c1,c2,c3）,group by c2,c3正常是⽤不到索引的，但是如果语句有where c1 = 常量， 则可以⽤到索引。
1到9的ascii码同时还要注意，只能使⽤ MAX 和 MIN 这两个聚合函数，使⽤松散索引的执⾏计划extra为using index for group by。
2.使⽤紧凑(Tight)索引扫描实现 GROUP BY
在 MySQL 中，MySQL Query Optimizer ⾸先会选择尝试通过松散索引扫描来实现 GROUP BY 操作，当发现某些情况⽆法满⾜松散索引扫描实现 GROUP BY 的要求之后，才会尝试通过紧凑索引扫描来实现。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。