MySQL数据库SQL语句执行顺序及优化

一、sql执行顺序

(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by
(11) limit

从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
第一步：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）

第二步：接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2

第三步：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3

第四步：如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。

第五步：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4，在这有个比较重要的细节不得不说一下，对于包含outer join子句的查询，就有一个让人感到困惑的问题，到底在on筛选器还是用where筛选器指定逻辑表达式呢？on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来，而where的移除的最终的。举个简单的例子，有一个学生表（班级,姓名）和一个成绩表(姓名,成绩)，我现在需要返回一个x班级的全体同学的成绩，但是这个班级有几个学生缺考，也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在on子句指定学生和成绩表的关系（学生.姓名=成绩.姓名）那么我们是否发现在执行第二步的时候，对于没有参加考试的学生记录就不会出现在vt2中，因为他们被on的逻辑表达式过滤掉了,但是我们用left outer join就可以把左表（学生）中没有参加考试的学生找回来，因为我们想返回的是x班级的所有学生，如果在on中应用学生.班级='x’的话，left outer join会把x班级的所有学生记录找回（感谢网友康钦谋__康钦苗的指正），所以只能在where筛选器中应用学生.班级=‘x’ 因为它的过滤是最终的。

第六步：group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

第七步：应用cube或者rollup选项，为vt5生成超组，生成vt6.

第八步：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

第九步：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

第十步：应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

第十一步：应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

第十二步：应用top选项。此时才返回结果给请求者即用户。

二、优化

数据库最常用的优化方式有：SQL语句和索引、数据库表结构、系统配置、硬件。

优化效果：SQL语句和索引 > 数据库表结构 > 系统配置 > 硬件，但成本从低到高。

数据库的优化方法小结：
（1）设计符合范式的数据库。

（2）选择合适的存储引擎。

（2）SQL语句优化；

（3）索引优化：高分离字段建立索引。

（4）SQL表结构、字段优化。

（5）数据库参数优化：IO参数、CPU参数。

（6）延迟加载、设置缓存与缓存参数优化。

（7）分库分表：垂直切分与水平切分。

（8）分区：将表的数据按照特定的规则放在不同的分区，提高磁盘的IO效率，提高数据库的性能。

（9）主从复制与读写分离：三个主要线程与bin-log文件、relay_log文件，主数据库负责写操作，从数据库负责读操作。

（10）负载均衡。

（11）数据库集群。

（12）硬件。

一、优化SQL语句的一般步骤：

1、通过show status 命令了解各种SQL的执行效率，

show [session | global] status;

可以根据需要加上参数来显示session级（当前连接，默认）和global级（自数据库上次启动至今）的统计结果。

show status like ‘Com_%’; —显示当前连接所有统计参数的值。

Com_xxx表示每个xxx语句执行的次数，通常需要注意的是下面几个参数：

Com_select/Com_insert/Com_update/Com_delete。

2、定位执行效率较低的SQL语句：

（1）通过show processlist命令实时查看当前SQL的执行情况；

（2）通过慢查询日志定位出现的问题。

3、通过explain 或 desc分析低效SQL的执行计划：

4、通过show profile 分析SQL：

show profile 能帮我们了解时间都耗费到哪里去了。

通过secect @have_profiling命令能够看到当前MySQL是否支持profile；

通过show profiles我们能够更清楚了解SQL执行的过程；

通过show profile for query我们能看到执行过程中线程的每个状态和消耗的时间。

5、通过trace分析优化器如何选择执行计划：

MySQL5.6提供了对SQL的跟踪trace,能帮我们了解为什么优化器选择执行A计划而不是B计划，进一步理解优化器的行为。

6、确定问题并采取相应的优化措施。

二、MySQL 常用的SQL语句优化方法：

1、写出统一的SQL语句：
对于以下两句SQL语句，很多人人认为是相同的，但是，数据库查询优化器认为是不同的。

select * from dual
select * From dual
虽然只是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

4、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

5、避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

6、前导模糊查询将导致全表扫描：

select id from t where name like ‘%c%’

下面使用索引

select id from t where name like ‘c%’

7、not in 也要慎用，否则会导致全表扫描；对于连续的数值，能用 between 就不要用 in 了，尽量使用exists代替in。

8、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

7、应尽量避免在 where 子句中对字段进行表达式与函数或其他表达式运算操作，这将导致引擎放弃使用索引而进行全表扫描。如：

（1）select id from t where num/2=100

应改为：select id from t where num=100*2

（2）select id from t where substring(name,1,3)=’abc’ –name以abc开头的id

应改为：select id from t where name like ‘abc%’

（3）select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id

应改为：select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1

8、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

9、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

10、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。如一表中有字段 se