查询优化处理

查询优化的3个阶段

解析器:将sql变成解析树

预处理器:检查解析树的语法是否正确如表或字段是否存在

查询优化器:找出最优的执行计划

查询优化器如何找出最有的执行计划

基于成本计算原则,尝试各种执行计划,然后通过数据抽样来试验

1 使用等价变化

    1 a < b and a = 5变成 b > 5 and a = 5

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

2 优化max,min等查询函数

    1查询min,只需要查询索引的最左边

    2查询max,只需要查询索引的最右边

3 覆盖索引扫描:

4 子查询的优化:

        select * from users where id  = (select userID  from user_address WHERE id= 1);

优化成select * from users where id = 1;

5 提前终止扫描:使用不存在的条件或者用limit

6 IN的优化:先进行排序,再用二分法查找,OR则采用一个一个比较(那扫描的记录和OR里面的值一个一个比较)

执行计划

1 执行计划id

Id相同,从上到下(id相同可以认为是一组);id不同,id越大则优先执行

如EXPLAIN select * from users where id  in  (select userID  from user_address WHERE addr= "湖南长沙麓谷");

2 执行计划select_type

用于区分简单查询,子查询和联合查询

Simple：简单查询

EXPLAIN SELECT userID  FROM user_address WHERE addr= "湖南长沙麓谷";

子查询:

子查询外部:PRIMARY

不带in的子查询:SUBQUERY

EXPLAIN select * from users where id  =  (select userID  from user_address WHERE addr= "湖南长沙麓谷");

带in的子查询:MATERIALIZED

EXPLAIN select * from users where id  in  (select userID  from user_address WHERE addr= "湖南长沙麓谷");

UNION

UNION:第二个select

UNION RESULT:使用union后的结果

EXPLAIN

select * from users where id  = (select userID  from user_address WHERE id= 2)

UNION

select * from users where id  = (select userID  from user_address WHERE addr= "湖南长沙麓谷");

3 执行计划--表 查询所涉及的表

具体的表如user_address

子查询:<subquery2>

联合查询:<union1,3>

4 执行计划--Type访问类型

执行效率从高到低是system --> const --> eq_ref --> ref --> range-->index -->all

system：const的特例,基本不会出现

主键(primary key和unique索引)

Const:单表查询

explain select * from users where id=1;

eq_ref:多表查询

EXPLAIN select * from users where id  in  (select userID  from user_address WHERE addr= "湖南长沙麓谷");

非唯一性索引:ref

explain select * from users where uname = '李二狗';

Range:索引列的范围扫描如大于,小于,%,IN,Between等

explain select * from users where uname like '李二%';

全表扫描

Index：索引全表扫描:

explain select uname from users;

ALL:全表扫描

explain select * from users;

两者的区别:Index:只遍历索引树

总结

索引:

主键或者唯一性索引:单表:Const 多表:eq_ref

非唯一性索引:精确查询:ref 范围查询:range 所有:index

其他: all

5 执行计划:possible_keys,key,rows,Filtered

索引:

possible_keys:可能用到的索引

Key:实际用到的索引

Rows:找到需要的记录所读的行数

Filtered：返回的结果集/所读的行数

6 执行计划:Extra

Using filesort:使用外部文件排序,而不是使用索引排序

Using temporary:使用临时表,常用于order by和group by,对查询结果进行排序

explain select max(lastUpdate) from users group by lastUpdate;

Using index:使用覆盖索引

Using where:使用where过滤

select tables optimized away:查询表优化掉如执行max,min,count等操作不需要查询表

explain select max(id) from users;

查询执行引擎

执行查询计划

返回结果

缓存:若有,则先缓存

增量返回结果:生成一条结果则返回一条结果