MySQL查询优化和执行计划

查询优化处理分为三个阶段

 解析sql

    通过lex词法分析，yacc语法分析将sql解析成解析书

预处理阶段

 根据mysql的语言的规则进一步检查解析树的合法性，如：检查数据表和列是否存在，解析名字和别名的设置。还会进行权限的验证

查询优化器

 优化器的主要作用就是找到最优的执行计划

查询优化器如何找到最优执行计划？

使用等价变化规则

5 =5 and a>5 改成 a>5

a < b and a = 5 改成 b < 5 and a = 5

基于联合索引，调整条件位置等

优化count、min、max等函数

min函数只需要找索引最左边

max函数只需要找索引最右边

myisam引擎count(*)

覆盖索引扫描

子查询优化

提前终止查询

 用limit关键字或者使用不存在的条件

IN优化

 先进行排序，再采用二分查找的方式

 

MySQL 查询优化 --- 执行计划

EXPLAIN select * from table where id = 1

执行计划  id

select查询序号，标识执行顺序

 1 id相同执行顺序由上到下

 2 id不同 如果时子查询，id的序号会递增，id的值越大优先级越高，越先被执行

 3 id相同又不同 即两种情况都存在，id如果相同，可以认为时同一组，从上往下顺序执行；再所有组中，id值越大，优先级越高，越先执行

执行计划  select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等
SIMPLE : 简单的select查询，查询中不包含子查询或者union

PRIMARY: 查询中包含子部分，最外层查询则被标记为primary

SUBQUERY/MATERIALIZED:  SUBQUERY表示在select或where列表中包含了子查询

                                                   MATERIALIZED表示where后面in条件的子查询

UNION：若第二个select出现在union之后则被标记为union

UNION_RESULT: 从union表获取结果的select

执行计划  table

查询涉及到的表

 直接显示表名或者表的别名

 <unionM，N> 由id为M,N查询union产生结果

<subqueryN>由ID为N查询生产的结果

执行计划  type

访问类型 sql查询优化中一个很重要的指标，结果值从好到坏依次是

system > const > set > eq_ref > ref > range > index > ALL

system : 表只有一行记录（等于系统），const类型的特例，基本不会出现，可以忽略不=不计

const : 表示通过索引一次就找到了，const用与比较primary Key 或者unique索引

eq_ref : 唯一索引扫描，对每个索引键，表中只有一条记录与之匹配，常见与主键或唯一索引扫描

ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问

range ： 只检索给定范围的行，使用索引来选择行

index : Full Index Scan 索引全表扫描，把索引从头到尾扫描

ALL ： 遍历全表以找到匹配的行

执行计划 possible_keys、key、rows、filtered

possible_keys  查询过程中有可能用到的索引

key  实际使用的索引，如果为NULL,则没有使用索引

rows  根据表统计信息或者索引选用的情况，大致估算出找到所需的记录所需要读取的行数

filtered: 他指返回结果的行所需读到的行的百分比 越大越好

执行计划  EXtra

1 using filesort     mysql对数据使用一个外部文件内容进行了排序，而不是按照表内索引进行排序读取

2 Using temporary 使用临时表保存中间结果

3 Using index 表示相应的select操作使用覆盖索引

4 Using where  使用where过滤条件

5 select tables optimized away 基于索引优化MIN/MAX操作或者MyISMA存储引擎优化count(*) 不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成