Mysql之执行计划【第六篇】

大纲：

        一、MySQL中SQL语句查询执行过程

    MySql中sql的执行过程 图例：

 

  简单描述MySQL中sql的执行过程：

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

查询缓存 流程图：

附录一：查询缓存
        MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。 查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生了变化，那么和这个表相关的所有缓存数据都将失效。 MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。 当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。 当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会缓存。包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。

        有一点需要注意，MySQL并不是会因为查询中包含一个不确定的函数而不检查查询缓存，因为检查查询缓存之前，MySQL不会解析查询语句，所以也无法知道语句中是否有不确定的函数。 事实则是，如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存，因为查询缓存中也无法找到对应的缓存结果。

        有关查询缓存的配置如下所示：

1）querycachetype：是否打开查询缓存。可以设置为OFF、ON和DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才会放入查询缓存。
2）querycachesize：查询缓存使用的总内存空间。
3）querycacheminresunit：在查询缓存中分配内存块时的最小单元。较小的该值可以减少碎片导致的内存空间浪费，但是会导致更频繁的内存块操作。
4）querycachelimit：MySQL能够查询的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以当结果全部返回后，MySQL才知道查询结果是否超出限制。超出之后，才会将结果从查询缓存中删除。

附录二：解析和预处理
        解析器通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进行进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

附录三：查询优化器
        查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。 生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。
        MySQL使用基于成本的查询优化器(Cost-Based Optimizer，CBO)。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。 优化器会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。

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

附录五：返回结果给客户端
        如果查询可以被缓存，那么MySQL在这个阶段页会将结果存放到查询缓存中。 MySQL将结果集返回给客户端是一个增量、逐步返回的过程。在查询生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

查询优化器：

---

     二、MySQL优化器及分类 

     传统关系型数据库里面的优化器分为CBO和RBO两种。
   1）RBO--- Rule_Based Potimizer 基于规则的优化器:

      RBO：RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如：在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO）

      RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了。

   2）CBO---Cost_Based Potimizer 基于成本的优化器:

      CBO :CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。(计算过程比较复杂)

---

  三、执行计划 - Explain
 

    1、什么是执行计划？

     使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。

     语法: Explain + sql

   2、执行计划的作用

     1) 表的读取顺序
     2) 数据读取操作的操作类型
     3) 哪些索引可以使用
     4) 哪些索引被实际使用
     5) 表之间的引用
     6) 每张表有多少行被优化器查

   3、执行计划包含的信息

  3.1 id - 获取select子句的操作表顺序，有几种情况

示例一：explain
explain select * from t_user;

     1) id 相同的情况下执行顺序是由上到下。

#示例二：id
#1）id相同，从上往下一次执行
#左外联
 explain select * from t_user u1 left join t_user u2 on u1.id=u2.id;
#右外联
 explain select * from t_user u2 left join t_user u1 on u2.id=u1.id;

 explain select * from t_user u1 right join t_user u2 on u2.id=u1.id;

注：通过left join 和 right join验证；id一样（注意执行计划的table列），

left join 先扫描a表，再扫描b表；right join 先扫描b表，再扫描a表

效果显而易见（主表先执行）  -----重点关注table 

 

     2) id 越大优先级越高，如果是子查询，ID 序列号会递增，id值越大，优先级越高，越先执行。

#2）id越大优先级越高
#先子查询
 explain select * from t_users where roleid=(select roleid from t_role where rolename='高级用户');

由图可知：t_role 比 t_user 先执行 

    3) id 相同又有不相同的，序列号大的会先执行，然后相同的从上到下执行。

---

示例：

     注意：在联表查询之前，并非执行外连接和内连接，而是执行的交叉查询；
                简单来说：在所有的内连接和外连接之前，先是执行的交叉查询

#示例三：select_type
#1）SIMPLE(简单SELECT,不使用UNION或子查询等)
 explain select * from t_user;

#2）PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
#   SUBQUERY(子查询中的第一个SELECT)
 explain select * from t_user where roleid=(select roleid from t_role where rolename='高级用户');

#3）UNION(UNION中的第二个或后面的SELECT语句)
#   UNION RESULT: 从 union 表获取结果的 SELECT
 explain select * from t_user where username='李四' union 
 select * from t_user where roleid=2;

#4）DERIVED(派生/衍生表的SELECT, FROM子句的子查询)
 explain select * from(
 select * from t_user where username='李四' union 
 select * from t_user where roleid=2) temp;

3)  possible_keys：存在可能要走的索引

key：实际走的索引 useranme

UNION RESULT：从 union 表获取结果的 select

---

3.2 select_type - 查询的类别，主要用于区别普通查询，联合查询，子查询等的复杂查询

     1) simple: 简单的select 查询，不包含子查询或者 union
     2) primary: 查询中包含任何复杂的子部分，最外层查询则被标记
     3) subquery: 在 select 或者 where 列表中包含了子查询
     4) derived: 在from 列表中包含子查询被标记为 derived Mysql 会递归执行这些子查询，把结果放到临时表里
     5) union: 若在第二个 select 中出现 union之后，则被标记为 union 若union包含在 from 子句的子查询中，外层 select 将被标记为 derived
     6) union result: 从 union 表获取结果的 SELECT 

3.3 table - 显示这一行的数据是关于那个表的

3.4 partitions -可以实现表的分区，但这种分区是局限在单个数据库范围里的，它不能跨越服务器的限制

3.5 type - 显示的是访问类型

     type是较为重要的一个指标，结果值从最好到最坏依次是：
     system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery 
     > index_subquery > range > index > all

     注：一般来说，得保证查询至少达到range级别，最好能达到ref

     1) system:  表中只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个可以忽略不计;
     2) const:  表示通过索引一次就找到了，const用于比较primary key或者unqiue索引，因为只匹配一条数据，所以很快，如将主键置于where条件中，Mysql 就能将该查询转换一个常量;
     3) eq_ref:  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见与主键或唯一索引扫描;
     4) ref:  非唯一索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索查询，组合索引查询;
     5) range:  只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，
       一般就是在你的 where 语句中出现了 between ,<,>,in 等查询这种范围扫描比全表扫描要好，因为它只需要开始与索引的某一点，而结束与另一点，不用扫描全部索引。
     6) ALL： 全表扫描;
     7) index： 扫描全部索引树;
     8) NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引;

3.6 possible_keys

     指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。 

     简而言之：可能使用的key（索引）

 3.7 key 

     实际上使用的索引，如果没用索引，则为NULL，查询中若使用了覆盖索引，则该索引和查询的select 字段重叠。

 3.8 key_len

     显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
  
 3.9 ref -显示哪个字段或常数与key一起被使用

 3.10 rows

     这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的。
      
 3.11 Extra

     包含不合适在其他列中显示但十分重要的额外信息：

    1) Using index    此值表示mysql将使用覆盖索引，以避免访问表。
     2) Using where    mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
     3) Using temporary    mysql 对查询结果排序时会使用临时表。
     4) Using filesort    mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。

---

示例：

#示例四：正确使用索引
#1）使用 like 语句时，%在右边才会使用索引。
#有效
 explain select * from t_user where username like '李%';
#无效
 explain select * from t_user where username like '%李%';

#2）or条件中有未建立索引的列才索引失效
#无效
 explain select * from t_user where username='李四' or roleid=2;
#有效
 explain select * from t_user where username='李四' and roleid=2;

#3）条件的类型不一致
#无效
explain select * from t_user where username=1024;
#有效
explain select * from t_user where username='李四';

#4）!= 号（例外：如果是主键，则会走索引）
#无效
explain select * from t_user where username!='李四';
explain select * from t_user where username<>'李四';

#6）order by
explain select * from t_user order by id;
explain select * from t_user order by username;

#7）组合索引（遵循最左前缀）
#有效
explain select * from t_user where username='李四'and roleid=2;
#无效
explain select * from t_user where idcard='430104200111134321';