SQL语句优化

推荐阅读 : 数据库SQL优化大总结之 百万级数据库优化方案

写在前面的话    自己琢磨也可以,琢磨不出就多问问前辈  很多问题他们都会有优秀的解答 .

先说说目前公司文档中提出的要求,公司的开发库为DB2

 VARCHAR与CHAR选项
对于 30 字节或更少字节的列，应避免使用 VARCHAR 数据类型，因为这种情况下，VARCHAR 类型通常会浪费空间，所以建议使用 CHAR 类型。如果数据量很大，那么空间的浪费往往会对查询时间造成影响。

索引

我们的系统，原则上所有表都需要对关键字段创建索引。如果经过实践，不创建索引可以获得更好的性能，则可以不创建索引。

当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。

 适合创建索引的列

基数较大的列很适合用来做索引。由少数窄列组成，列宽度较大的列不适合作索引。

 索引的列数

考虑到管理上的开销，应避免在索引中使用多于 5 个的列。

索引列顺序

对于多列索引，将查询中引用最多的列放在定义的前面。

 避免索引列重复

避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。

SQL语句

1.1 With ur 选项

在语句的末尾一定添加with ur选项，避免锁表异常情况发生；

Select * from tabschema.tabname with ur;

1.2 表名前要加schema

一个表名应该由schema和name组成，可以避免移植期间的许多麻烦；

Select * from tabschema.tabname with ur;

1.3 SQL语句中不要使用双引号

  建表或写SQL语句的时候不要用双引号，双引号是非常严格的SQL，区分大小写的。

如果不加双引号，会自动转成大写。

1.4 SQL语句索引的利用 

对条件字段的一些优化

1.4.1 采用函数处理的字段不能利用索引，如： 

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’

1.4.2 进行了显式或隐式的运算的字段不能进行索引，如： 

ss_df+20>50，优化处理：ss_df>30

‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’

1.4.3 条件内包括了多个本表的字段运算时不能进行索引，如： 

ys_df>cx_df，无法进行优化

qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

1.5  操作符优化

1.5.1 IN 操作符   使用exists或between或left join

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的，从执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

1.5.2 NOT IN操作符 

此操作是强列推荐不使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

1.5.3 <> 操作符（不等于） 

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如

a<>0 改为 a>0 or a<0

a<>’’ 改为 a>’’

1.5.4 IS NULL 或IS NOT NULL操作（判断字段是否为空） 

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

推荐方案：

用其它相同功能的操作运算代替，如

a is not null 改为 a>0 或a>’’等。

不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。 

1.5.5 > 及 < 操作符（大于或小于操作符） 

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有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的记录索引。

1.5.6  LIKE操作符 

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

1.5.7 UNION操作符    大部分时间用UNION ALL就可以了 

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys with ur

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys with ur

1.6  VALUS函数的使用注意事项   

在使用VALUE()函数进行字段转换时，请不要在该函数中使用列计算，如：VALUE(COL1,COL2*100)，这样会导致SQL语句效率低下，如果有类似的需要，请修改为CASE语句进行转换，如：CASE WHEN COL1 IS NULL THEN COL2*100 ELSE COL1 END。

1.7 自定义的外部函数的使用 

在对列进行转换时，尽量不要使用函数进行转换，这样会降低执行效率，最好使用CASE语法进行转换；如：

在做数据业务彩铃KPI优化过程中发现原来使用的民族和品牌转化自定义外部函数转化效率很低

 

  KPI.FUN_DIM_BRAND(BRAND_CODE)

   KPI.FUN_DIM_FOLK(FOLK_CODE)

   改成使用KPI.SP_KPI_DIM_TRANS过程进行转化对比运行时间发现效率比以前的自定义函数快很多

   call KPI.SP_KPI_DIM_TRANS('KPI_DIM_FOLK','FOLK_CODE','FOLK_CODE','TYPE_ID',V_FOLK_CODE)

   CALL KPI.SP_KPI_DIM_TRANS('KPI_DIM_BRAND','BRAND_CODE','BRAND_CODE','TYPE_ID',V_BRAND_CODE) ;

1.8 UPDATE语句的注意事项

在DB2的UPDATE语句中，更新条件的必须有明确的相关名获取表字段，否则有出现问题的可能性。

例如：

update  xjdss.dim_cell a  

set ESP_EPARCHY_CODE='97'

where  eparchy_Code ='18'  and  stat_year=2009

 and COUNTRY_AREA_ID = (select distinct AREA_ID from xjdss.dim_area_cell where eparchy_code ='18'  and  stat_year=2009 and  AREA_name like '%阿合奇%'  )  with ur

表xjdss.dim_area_cell中没有字段eparchy_code，但是这条语句还是可以执行成功，原因是eparchy_code是表xjdss.dim_cell的属性，DB2解析时，将eparchy_code判断为对表xjdss.dim_cell的限制。

为了增强程序的可读性和健壮性，大家在写类似语句的时候最好将别名都写清楚。在每个作为条件的字段，是从那个表中获取的，要有明确信息。另外子查询中用in不要用=

清空表数据

清空一张表的所有数据时，不要使用delete语句，因为delete语句不释放空间，而且性能很差。可以使用下面语句清空表数据：

　

　ALTER TABLE TABLE_SCHEMA.TABLE_NAME ACTIVE NOT LOGGED INITALLY WITH EMPTY TABLE;

或

IMPORT FROM NUL OF DEL REPLACE INTO TABLE_SCHEMA.TABLE_NAME;

这两个命令的区别是ALTER TABLE命令是DDL语句，可以在存储过程中使用，IMPORT是外部实用工具，不能在存储过程中直接引用。

上面的也只是些基础 , 还是推荐阅读位于顶部的推荐链接,总结的很好 比我这新手好多了..

sqlserver中执行顺序

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

标准sql执行顺序是：

　　1:form 组装来自不同表的数据，如 form user或者，form user as u join goodsOrder as r on u.id= r.userid

　　2:where 过滤符合查询条件的数据，如：id>1000

　　3:group by 将查询数据进行分组

　　4:使用sum等聚合函数进行计算。

　　5:使用having 进行筛选分组。

　　6:执行select语种

　　7:执行排序语句

　　如：select count(gid),gname from shopping_goods where gcid=1 group by gname having count(gid)>1 order by count(gid) desc

　　1:首页查询shopping_goods 表，得到表中的数据

　　2:执行where,过滤出gcid=1的商品。

　　3:对gname进行分组。

　　4:使用聚合函数count(),计算出商品类型为1，不同商品名称的数量.

　　5:使用having,过滤出类型为1，商品统计数量大于1的商品

　　6:执行select语句

　　7:执行order by ,按照商品数量降序排列。

一些低级错误不要犯  
 尽量不要全表扫描 , 尽量避免排序 , select * 什么的就算了吧 , join中尽量不要套join  
写sql时要把逻辑理清楚, 在哪个地方加上限制条件能最大化优化