mysql 查询处理过程

问题1： 有一条sql语句，那么是怎么返回查询结果的了？ 

数据表1：

CREATE TABLE `test1` (
  `id` int(4) NOT NULL,
  `age` int(4) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

数据表2： 

CREATE TABLE `test2` (
 `id` int(4) DEFAULT NULL, 
`t1` int(4) NOT NULL COMMENT 'test1中的外键id', 
 `name` varchar(100) NOT NULL COMMENT '名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

sql: 

select DISTINCT a.id, a.age,b.id from test1 a
left JOIN test2 b on a.id = b.t1
where a.id in(1,2,3)
GROUP BY a.id with cube
HAVING b.id < 100
order by a.age desc
limit 10,20;

这条sql语句的效率如何， 如何优化了？带着这样的问题， 我们逐渐学习sql语句的执行过程以及如果提高sql的查询效率 

一、查询处理 

1. mysql逻辑执行流程 将问题1中的sql语句抽象化可以得到如下表示：

(8)select (9)DISTINCT<select_list> 
(1)from <left_table>
(3)<join_type> JOIN <right_table>
(2)on <JOIN_condition>
(4)where <where_condition>
(5)GROUP BY<group_list>
(6)WITH<cube|ROLLUP>
(7)having<having_condition>
(10)order by<order_BY_list>
(11)limit <limit_num> 

同时将执行的顺序标记了出来，mysql总是从from表开始，每一步操作都是产生一张虚表，最后一步完成之后才会返回最终结果。 

(1)left_table 与 right_table执行笛卡尔积，产生虚表vt1， 如果left表中的记录为m, right表中的记录为n, 那么虚表中的记录数未，m×n; 

(2)通过on条件，筛选符合条件的记录到虚表vt2, 注意on条件中的null， 通常尽量不要on条件中属性中存在空值; 

(3)将on未匹配的保留表中记录添加到vt2中，生成虚表vt3， 如果from是多个表， 重复将所有的表完成join操作， 注意区分inner join 与 outer join的区别; 

(4)对vt3根据where_condition筛选出符合条件的记录生成出vt4; 注意：where条件还未分组不能使用count(), min(), max()等聚合函数，没有执行select 不能使用属性别名。 

(5)对vt4根据group的列分组生成vt5; 

(6)对vt5进行聚合操作生成vt6; 

(7)having对vt7筛选生成vt7; 

(8)select 筛选指定的列得到vt8; 

(9)vt8去重得到vt9， 此时会用临时表去处理， 增加一个唯一索引，实现去重; 

(10)对vt9根据order by指定的字段排序得到vt10， innodb 存储按照索引组织，如果order by中的列没有索引，则会进行重新扫描排序，对性能消耗比较大; 

(11)在vt10中，通过limit选出指定数量的记录数，返回给客户端， 对vt10进行一次表扫描 通过此分析流程， 大致的知道了问题中的sql的执行流程 

2.物理查询处理 

上述介绍得逻辑查询处理，但是通常数据库都会对查询进行优化操作， mysql存在两个组件Parser与Optimizer， Parser负责解析sql语句， Optimizer负责sql的优化， 很多时候，真是的查询并不一定 按照逻辑查询处理的过程，索引在查询优化中起到关键性性作用，如果没有索引，则按照逻辑查询去处理， 效率很低， 代价很大。每张虚表都在内存中实实在在的存在。 问题2：为什么建立索引之后就会提高查询效率， 一定是这样的吗？后续我们在来揭晓答案。 

二、子查询 

       mysql的子查询一直是一个被诟病的过程，性能很差，在应用中很少使用。但是有时候实现功能很方便， sql的可读性较高。

        自查的主表是全表扫描， 子表根据索引情况，命中则采用索引，否则执行笛卡尔积， 这是为什么子查询效率低的原因