深入Mysql执行流程

mysql体系结构图

Mysql是由connection pool,SQL接口，解析器，优化器，缓存，存储引擎组成的

  connection pool: 负责与所有客户端的连接，并管理所有的连接。

   SQL接口：类似于控制器，接收sql语句，并交给解析器解析，返回用户需要查询的结果。

   解析器：解析sql语句，解析的结果转换为解析树。

   优化器：对解析树做优化处理，得到一个最佳执行计划。

   存储引擎：mysql中插拔式的存储引擎，针对于表，一个数据库可以有多个存储引擎，负责与文件系统交互，获取数据。

 

sql的执行路径

简单来说可以分为

 1.客户端与数据库服务通信
 2.查询缓存
 3.查询优化处理
 4.调用存储引擎
 5.返回客户端

1.客户端与数据库服务通信：两者之间以半双工的方式进行通信(半双工：同一时间，只能有一端发送数据)，客户端一旦开始发送请求 另一端要完全接受后才能响应，客户端一旦开始接受数据没法停下来发送指令。连接有不同的状态。

可以使用show processlist命令查看与客户端连接的状态，有5种连接状态。

  sleep：线程正在等待客户端发数据
  query：连接线程正在执行查询
  locked：线程正在等待表锁的释放
  sorting result：线程正在对结果进行排序
  sending data：向请求端返回数据

可以在服务端用 kill id 命令关掉连接。

  2.查询缓存：数据库缓存一般默认为关闭状态，也有说数据库的缓存是一个比较鸡肋的东西，缓存查询的sql语句必须完全一致，表中的数据如果发生修改的话，有关表的缓存会失效。

查看有关缓存的命令 show variables like 'query_cache%'

query_cache_type  缓存是否开启的状态

如果想开启缓存 可以在配置文件中修改 修改配置文件：query_cache_type= 0 1 2（0 不开启 1  开启 2 按需开启）

开启缓存后 可以show status like "Qcache" 命令来查看缓存的情况

Qcache_inserts  是否有新的数据添加   有添加value+1

Qcache_hits 查询语句是否命中缓存 命中value+1

Qcache_free_memory 缓存空闲大小

数据库缓存的淘汰策略是先进先出

数据库缓存适用于查询远大于修改的情况下， 一般缓存不使用数据库缓存，可以使用redis或者其他做缓存。

3.查询优化处理：解析器解析sql语句，将sql语句解析成解析树，预处理检查sql语句的合法性，看有没有语法或者其它的错误，若有错误，向客户端返回错误信息，没有交给查询优化器，查询优化器会对sql语句做优化，得到最佳执行计划。

   常见的优化：

    等价变化规则：  字段顺序  5=5 and 5 >5 优化成 a>5 
      优化count max函数
      覆盖索引扫描
      子查询优化
      终止查询
      in的优化  （in会条件重排序  折半查找 效率比or高）

 对于许多数据库服务器而言，IN()列表不过是多个OR语句的同义词而已，因为IN和OR在逻辑上是等同的。不仅是在MySQL数据库服务器，对于许多其他的数据库服务器使用到IN查询时，都是按照如下方式处理的:
[1] 对IN列表中的数值进行排序。
[2] 对于查询的匹配，每次使用二分查找去匹配IN列表的数值。
所以对于第[2]步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。

在使用索引的情况下 in和or的效率相差不大。

explain关键字 模拟查询优化器得到sql语句的执行计划。如何我们想看写的sql语句的效率，可以使用explain。

  id：执行顺序的序列号 相同 执行顺序由上到， 不同 id越大 优先级越高，越先被执行。
  select_type：simple 简单查询
                       primary:包含子查询中 最外层查询
                       subquery 子查询
                       union
  table：查询的表 或者子查询的临时表
  type: system:表只有一行记录 等同于系统表 const的特例
           const：表示通过索引 一次就找到了 代表主键或者唯一性索引
           eq_ref：唯一索引扫描，对于每一个索引值 表里只有一条记录与之匹配
            ref：非唯一性索引，匹配某个单独值的所有行
            range：只检索给定范围的行 使用一个索引来选择行
            index：索引全表扫描 把索引从头到尾扫一遍
            all：遍历全表找到匹配的行
        
   possible_keys:查询过程中有可能用到的索引
   key：实际使用的索引
   rows：估算找到记录所需要读取的行数
   filtered：返回结果的行和需要读取的行数比  值越大越好
   extra额外信息：
           using  filesort：对数据使用的文件内容进行排序
           using  temporary:使用临时表保存中间结果
           using  index：使用了覆盖索引
           using  where ：使用了where 过滤条件
           select tables optimized away：查询执行计划生成的阶段已经完成了优化

4.调用存储引擎：存储引擎负责与文件系统交互，读取数据。

5.返回数据给客户端：若开启缓存，则现在缓存中保存数据，增量返回：生成第一条结果的时候，MySQL就已经向客户端返回数据。好处：无需保存过多的数据，而造成浪费空间，响应高。

 

只是简单的总结，详细的地方以后在修改