mysql 拼接sql批量执行_MySQL的sql执行步骤及优化

简单来说MySQL的sql执行过程如下:

1. 客户端发送一条查询给服务器；
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

1、MySQL客户端和服务器通讯

MySQL客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让MySQL通信简单快速，但也限制了MySQL。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。

多数连接MySQL的库函数都可以获得全部结果并缓存到内存里，还可以逐行获取所需要的数据。默认一般是获得全部结果并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接受全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放对应的资源。

2、查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前 查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

3、查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

• 语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询；预处理器则根据一些MySQL规则进一步检查解析数是否合法。
• 查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
• 执行计划：MySQL不会生成查询字节码来执行查询，MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

4、查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

MySQL简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询，MySQL只需要重复执行计划中的各个操作，知道完成所有的数据查询。

5、返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么MySQL在这个阶段也会将结果放到查询缓存中。

MySQL将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让MySQL客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

join 算法简介

1）Nested Loop Join算法

NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。

Nested-Loop 的伪算法如下:

for each row in t1 matching range {

 for each row in t2 matching reference key {

 for each row in t3 {

 if row satisfies join conditions,

      send to client

    }

  }

 }

因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下，其扫描成本为O(Rn),若没有索引,则扫描成本为O(Rn*Sn)。如果内部表S有很多记录，则SimpleNested-Loops Join会扫描内部表很多次，执行效率非常差。

2）Index Nested-Loop Join算法

与NLJ算法类似其优化的思路 主要是为了减少内层表数据的匹配次数;简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数;从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。

3）Block Nested-Loop Join算法

BNL 算法:将外层循环的行/结果集存入joinbuffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.

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

前面描述的query, 如果使用join buffer, 那么实际join示意如下:

for each row in t1 matching range {

 for each row in t2 matching reference key {

    store used columns from t1, t2 in join buffer

 if buffer is full {

 for each row in t3 {

 for each t1, t2 combination in join buffer {

 if row satisfies join conditions,

          send to client

        }

       }

      empty buffer

    }

  }

}





if buffer is not empty {

 for each row in t3 {

 for each t1, t2 combination in join buffer {

 if row satisfies join conditions,

      send to client

     }

  }

}

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为

(S * C)/join_buffer_size + 1

扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了。

join语句的优化

1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；

2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

3. 对被驱动表的join字段上建立索引；

4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。