1 select语句的执行顺序

  1. from子句组装来自不同数据源的数据；
  2. where子句基于指定的条件对记录行进行筛选；
  3. group by子句将数据划分为多个分组；
  4. 使用聚集函数进行计算；
  5. 使用having子句筛选分组；
  6. 计算所有的表达式；
  7. 使用order by对结果集进行排序。

2 不要使用 select *

select * … 不要使用*，而是使用具体的列名称。

2.1 原因

对电脑来说最终查询的还是具体的列，写*只是为了人的方便，最终*还是要被解析成具体的列名。因此书写sql时如果直接写具体的列名称，可以节省掉解析的时间。

2.2 例子

太简单，不在赘述。

3 尽量使用数字类型

3.1 原因

能使用数字类型的字段尽量使用数字类型，这将提高查询和连接的性能，并减少存储开销。引擎在处理查询和连接时会逐个比较字符串中的每个字符，对于数字类型只需要比较一次。

3.2 例子

4 对于多列索引，在条件中使用时必须从索引中最左侧的列开始

4.1 原因

对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

4.2 例子

alter table dept add index my_ind (dname,loc); //  dname左边的列,loc就是右边的列
explain select * from dept where dname='aaa'\G //会使用到索引
explain select * from dept where dname='aaa' and loc=’bbb’\G //会使用到索引
explain select * from dept where loc='aaa'\G //不会使用到索引

5 当where条件语句由多个and构成时，如果希望使用某个索引，那么让此索引能够过滤最多的行。

5.1 原因

当where条件语句由多个and构成时，并且有多个索引可用时，会选择能够过滤最多行的索引。

5.2 例子

EXPLAIN SELECT * FROM aaa WHERE NAME LIKE 'G%'; //name索引能够得到155行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2'; //hiredate索引能够得到156行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2' AND NAME LIKE 'G%';//因为name索引能够过滤更多的行，因此使用name索引
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2' AND NAME LIKE 'G%' ORDER BY NAME; //因为使用了name索引，因此无需再另行排序。因此extra栏目中没有using filesort

EXPLAIN SELECT * FROM aaa WHERE NAME LIKE 'G%'; //name索引能够得到155行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5'; //hiredate索引能够得到154行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5' AND NAME LIKE 'G%'; //因为hiredate能够过滤更多的行，因此使用hredate索引
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5' AND NAME LIKE 'G%' ORDER BY NAME; //因为使用hiredate索引，因此需要另行排序。因此extra栏目有using filesort

6 尽量不要使用or，使用union替代

6.1 原因

如果条件中有or，只有使用的所有字段，都建立索引,条件所在的语句才会使用索引。

6.2 例子

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

7 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来

7.1 原因

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来。

7.2 例子

select * from dept where dname=111;//不会使用索引
select * from dept where dname=’111’; //使用索引

8 如果可能，使用>=、<=代替>、<

8.1 原因

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段 A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因 为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

8.2 例子

9 使用like时不要使用【%关键字%】

9.1 原因

不会使用索引。【关键字%】则可以使用。

9.2 例子

太简单，不在赘述。

10 尽量避免对索引字段进行计算操作

10.1 原因

无法使用索引。

10.2 例子

......
where trunc(create_date)=trunc(:date1) //虽然已对create_date 字段建了索引，但由于加了TRUNC，使得索引无法用上。此处正确的写法应该是
where create_date>=trunc(:date1) and create_date

11 不要使用between （没有经过测试，还无法确定其正确性）

11.1 原因

11.2 例子

12 不要使用 not null

12.1 原因

不能够使用索引

12.2 例子

EXPLAIN SELECT * FROM dept WHERE loc IS NOT NULL

13 使用exists来代替in

13.1 原因

当主查询数据非常大，子查询数据非常少的时候，可以选择使用in，使用exists也可以性能不会差多少。
当主查询数据非常少，子查询数据非常多的时候，使用exists。
保险些， 使用exists来代替in 。

13.2 例子

FLUSH TABLES #清除sql缓存
################当主查询中的数据非常多，子查询中的数据非常少的时候，in的速度比exists快一些 begin######################
EXPLAIN SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);

EXPLAIN SELECT * FROM emp WHERE EXISTS (SELECT * FROM dept WHERE emp.`deptno` = dept.`deptno`);
SELECT * FROM emp WHERE EXISTS (SELECT * FROM dept WHERE emp.`deptno` = dept.`deptno`);
################当主查询中的数据非常多，子查询中的数据非常少的时候，in的速度比exists快一些 end######################

FLUSH TABLES #清除sql缓存
################当主查询中的数据非常少，子查询中的数据非常多的时候，exists的速度比in快的多 begin######################
EXPLAIN SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp);
SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp);

EXPLAIN SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE emp.`deptno` = dept.`deptno`);
SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE emp.`deptno` = dept.`deptno`);
#################当主查询中的数据非常少,子查询中的数据非常多的时候，exists的速度比in快的多 end#####################

14 不要使用not in，使用not exists

14.1 原因

not in 无法使用索引

14.2 例子

#####################不要使用not in，使用not exists begin#################################################
EXPLAIN SELECT * FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); #not in 无法使用索引
SELECT * FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
EXPLAIN SELECT * FROM dept WHERE NOT EXISTS (SELECT * FROM emp WHERE emp.`deptno` = dept.`deptno`);
SELECT * FROM dept WHERE NOT EXISTS (SELECT * FROM emp WHERE emp.`deptno` = dept.`deptno`);
#####################不要使用not in，使用not exists end#################################################

15 使用union all，不要使用union

15.1 原因

union 会对合并后的结果进行排序去重，而union all则直接返回合并后的结果。

15.2 例子

#####################使用union all 不要使用union begin#################################################
EXPLAIN SELECT * FROM emp WHERE empno<2000019 AND empno>1800000
UNION ALL
SELECT * FROM emp WHERE empno<2050019 AND empno>2000000;

SELECT * FROM emp WHERE empno<2000019 AND empno>1800000
UNION
SELECT * FROM emp WHERE empno<2050019 AND empno>2000000;

SELECT * FROM emp WHERE empno<2000019 AND empno>1800000
UNION ALL
SELECT * FROM emp WHERE empno<2050019 AND empno>2000000;
#####################使用union all 不要使用union end#################################################

16 尽量使order by 后面的列也能够使用where中使用的索引。

16.1 原因

order by是在select语句的最后对结果进行排序的操作。此操作非常耗费cpu，因此如果可能要尽量避免。避免的方法是通过让order by 后面的列也能够使用where中使用的索引。这样，因为order by中要排序的结果的顺序与索引顺序相同，就不需要另行排序了。

16.2 例子

EXPLAIN SELECT * FROM aaa WHERE NAME LIKE 'G%'; //name索引能够得到155行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2'; //hiredate索引能够得到156行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2' AND NAME LIKE 'G%';//因为name索引能够过滤更多的行，因此使用name索引
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-2' AND NAME LIKE 'G%' ORDER BY NAME; //因为where中使用了name索引，因此无需再另行排序。因此extra栏目中没有using filesort

EXPLAIN SELECT * FROM aaa WHERE NAME LIKE 'G%'; //name索引能够得到155行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5'; //hiredate索引能够得到154行
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5' AND NAME LIKE 'G%'; //因为hiredate能够过滤更多的行，因此使用hredate索引
EXPLAIN SELECT * FROM aaa WHERE hiredate > '2013-6-5' AND NAME LIKE 'G%' ORDER BY NAME; //因为where中使用hiredate索引，因此需要另行排序。因此extra栏目有using filesort

17 尽量早过滤

17.1 原因

在 SQL 编写可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。

17.2 例子