sql优化

 

Sql编写顺序

1. select <select_list>
2. from <table>
3. where <where_condition>
4. group by <group_by_list>
5. having <having_condition> 
6. order by <order_by_condition>

 

mysql解析器的顺序

1. from <table>
2. where <where_condition>
3. group by <group_by_list>
4. having <having_condition> 
5. select <select_list>
6. order by <order_by_condition>

SQL Select语句完整的执行顺序:

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

 

mysql优化

1、查询尽量用确定的列名，少用*号

2、尽量少嵌套子查询，这种查询会消耗大量的CPU资源

3、避免使用耗费资源的操作

1、DISTINCT

2、UNION  union all

3、ORDER BY

4、MINUS

minus”直接翻译为中文是“减”的意思.A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录 SELECT 字段1,字段2,····FROM 表1 WHERE [CONDITION] MINUS SELECT 字段1,字段2,····FROM 表2 WHERE [CONDITION]

 

5、INTERSECT

INTERSECT ：查询两张表中的交集 也就是说查询两张表共有的数据集··· 语法 ： SELECT 字段1,字段2,····FROM 表1 WHERE [CONDITION] INTERSECT SELECT 字段1,字段2,····FROM 表2 WHERE [CONDITION]

 

4、选择最有效率的表名顺序，Oracle解析器对表解析从右到左，所以记录少的表放在右边

5、对于有比较多or运算的查询，建议分成多个查询，用union all联结起来

6、合理使用索引

 

mysql执行计划

通过执行计划可以分析查询语句的效率

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以被使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化查询

 

 

type 
这是重要的列，显示查询使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

key

实际使用的索引，如果为null，则没有使用索引

 

key_len 
使用的索引的长度。在不损失精确性的情况下，长度越短越好

该值可以通过索引的列计算出来

ref 
显示索引的哪一列被使用了，如果可能的话，是一个常数

 

优化策略

1．尽量全值匹配

 

 

 

当索引多列时遵循左前缀法则

2.不在索引列上做任何的操作（计算、函数、类型转换）

 

3.范围条件后索引失效

>  in  <   等范围查询时，范围后的索引失效

4.覆盖索引尽量使用（查询列和索引列一致）

5.不等于要慎用

6.not null 对索引有影响

 

 

覆盖索引

 

7.like查询注意事项

以通配符开头索引失效

8.字符类型的引号不可以省去，会进行类型的自动转换，索引失效

9.OR改 union效率高

 

 

 

10.exists替换in

select * from T1 where T1.a in (select T2.a from T2) ;

select * from T1 where exists(select 1 from T2 where T1.a=T2.a);

T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

对外表用loop逐条查询，每次查询都会查看exists的条件语句 当 exists里的条件语句能够返回记录行时(无论记录行是多少，只要能返回)，条件就为真 , 返回当前loop到的这条记录。反之如果exists里的条件语句不能返回记录行，条件为假，则当前loop到的这条记录被丢弃。 exists的条件就像一个boolean条件，当能返回结果集则为true，不能返回结果集则为 false。

 

速记口诀

全值匹配我最爱   最左前缀要遵守

带头大哥不能死   中间兄弟不能断

索引列上少计算   范围之后全失效

Like百分写最右   覆盖索引不写*

不能空值还有or   影响索引要注意

Var引号不可丢    SQL优化有诀窍