DB2SQL总结

一、注意事项
1.查询尽量避免使用*，*会去字典中查询一次增加开销
2.避免使用not in的情况，因为这种情况会使索引失效
3.查询操作较多的字段尽量建立索引，但是经常进行修改的字段最好不要添加索引
4.避免类型不一致导致的索引失效，字符型不要写成int型
5.数据分布不一致的索引自己通过判断决定是否使用索引
并发场景下应该注意的几点问题
1.事务不要开得太大，容易长时间占有链接，锁无法释放导致极易出现死锁
2.事务中只封装必要的操作，查询语句尽量不要放进去，缩短事务时间
3.事务中如果出现大量更新操作，判断一下这些更新的数据会不会有交集，如果有交集，尽量以一定顺序入库
4.更新语句中涉及索引字段的一定要考虑会不会和其他语句中的索引死锁（二级索引和主键索引使用不当）
update A set name = ‘’ where itme_no = ‘’;(二级索引)
update A set item_no = ‘’ where id = ‘’;(主键索引)

注：造成死锁的原因主要是上锁顺序不一致，主要两种情况第一种是事务中各自更新操作多行互相等待（比较多见）第二种是主键索引和二级索引使用不当
二、索引使用建议
1)避免对条件字段进行操作
对条件字段的操作会导致该列的索引失效，这里所谓的操作包括数据库函数、计算表达式等。
如以下3个sql，其条件列上都建有索引，但不会被使用：
SELECT CORD_NAME FROM RECORD WHERE SUBSTRB(CARDNO,1,4)= ‘5378’ --(13秒)
SELECT CORD_NAME FROM RECORD WHERE AMOUNT/30< 1000 --（11秒）
SELECT CORD_NAME FROM RECORD WHERE TO_CHAR(ACTIONTIME,‘YYYYMMDD’)=‘19991201’ --（10秒）

可改为如下形式：
SELECT CORD_NAME FROM RECORD WHERE CARDNO LIKE ‘5378%’ --（< 1秒）
SELECT CORD_NAME FROM RECORD WHERE AMOUNT < 1000*30 --（< 1秒）
SELECT CORD_NAME FROM RECORD WHERE ACTIONTIME = TO_DATE (‘19991201’ ,‘YYYYMMDD’) --（< 1秒）

2)避免隐式转换
对于条件值书写不规范的，数据库会自动对条件列进行隐式转换，以完成比较，但同时也会造成索引失效。
如将字符型数据与数值型数据比较，数据库会自动将字符型用 INTEGER() 或 TO_NUMBER()函数进行转换，从而导致全表扫描。
例：表EMPLOYEE中的列EMPNO是字符型（char)，则以下语句存在类型转换：
SELECT EMP_NAME FROM EMPLOYEE WHERE EMPNO > 000100

应该写为：
SELECT EMP_NAME FROM EMPLOYEE WHERE EMPNO > ‘000100’

3)尽量避免 “<>”
尽量去掉 “<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR"方式。
例：UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上语句由于其中包含了"<>"，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段STATE 为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引来提高效率。
修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。

4)避免Where子句中的IS NULL和IS NOT NULL
Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

5)数据分布不均匀列的索引使用
当列值的选择性不高，但查询返回值恰好是返回较少的数值，则应创建索引。
例：表ServiceInfo中数据量很大，有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：[0，1，2，3，4，5，6，7]。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低。
然而，由于该字段上数据值的分布情况非常特殊，具体如下表：
取值范围 1~5 6 7
占总数据量的百分比 1% 98% 1%
而且，常用的查询中，查询 DisposalCourseFlag<6 的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么将大大提高这种情况的查询效率。

6)屏蔽无用索引
如果了解到条件字段建有索引，但返回值较多，如果走索引的话效率更低，则可通过特殊方法屏蔽掉索引的使用。如字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：“||""”
例：select WORK_NAME from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = ‘36’；
注意：这种方法多用于SQL 调优，在生产系统中不推荐使用。

7)LIKE子句尽量前端匹配
当LIKE字句前段为通配符，会导致索引失效。
如：SELECT CITY_NO FROM CITY WHERE NAME LIKE ‘%S%’; --该语句执行全表扫描
修改为：
SELECT CITY_NO FROM CITY WHERE NAME LIKE ‘S%’ ; --会正确使用索引
8)基于函数的列选择，使用生成列（DB2）
注意： DB2 不支持基于函数创建索引。为了解决这个问题，推荐在表上新加一个生成列，然后基于这个生成列创建索引。

9)使用分区索引
在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入数据库的数据字典中。数据库可以利用这个信息来提取出那些只与SQL查询相关的数据分区。
例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。数据库会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。
但是当查询跨多个分区，涉及到比较大小时，考虑使用全局索引。

10)少数情况下用全表扫描替代索引扫描
在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：
对于原始排序的表：仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。
对于未排序的表：仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

11)查询列和排序列与索引列次序保持一致，确保使用到复合索引
三、sql性能规范
1)批量操作，选择合适的COMMIT频率
不合适的COMMIT频率会导致物理I/O增大，降低性能，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT时必须保证事务的完整性。

2)避免动态SQL，要使用绑定变量方式
为了不重复解析相同的SQL语句，在第一次解析之后，数据库将SQL语句缓存在内存中，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同，数据库就能很快获得内存中相同语句的执行计划，然后就直接执行该语句，而不需要进行解析。
如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，数据库还是需要对每条结构相同的语句重新解析一遍，同时在内存中缓存该语句，导致共享池中存放了N条结构相同的语句，达不到共享的目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。
Java代码示例：
示例1： 未使用绑定变量：
…
try{
String sSql = “select field from my_table where field=1”;
pStmt = connection.prepareStatement(sSql);
pstmt.executeUpdate(); //执行动态SQL语句
…
}

示例2：使用绑定变量：
…
try{
String sSql = “select field from my_table where field=?”;
pStmt = connection.prepareStatement(sSql);
pstmt.setString(1, sCode); //sCode为传入变量
pstmt.executeUpdate(); //执行动态SQL语句
…
}
3.避免不必要的排序
对查询结果进行排序会大大的降低系统的性能
4)用WHERE子句替换HAVING子句
HAVING子句是为解决WHERE子句无法使用聚合函数的问题。所以当不需要对聚合函数的结果做限制的时候，请不要使用HAVING子句。
例：
SELECT NAME, SUM(AGE)
FROM EMPLOYEE
GROUP BY NAME HAVING NAME != ‘ABC’

修改为以下语句效果更好：
SELECT NAME，SUM(AGE)
FROM EMPLOYEE
WHERE NAME !=‘ABC’ GROUP BY NAME
5)用“>=”替代“>”
如：在ID列上建有索引，则语句SELECT EMP_NAME FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT EMP_NAME FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录，而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。
6)在索引列上使用<>(!=)和 LIKE 将不会使用索引。
如果在索引列上使用 <>(!=) 和 LIKE，往往无法使用索引，从而导致SQL性能下降。因此，应避免在索引列上使用<>(!=) 和 LIKE。
例：
SELECT MEMBER_NO FROM STAFF_MEMBER WHERE DEPT_NO<>2012;
SELECT MEMBER_NO FROM STAFF_MEMBER WHERE FIRST_NAME LIKE '%DON’;
通常，以上语句都不会用到索引

注：LIKE语句改为 SELECT MEMBER_NO FROM STAFF_MEMBER WHERE FIRST_NAME LIKE ‘DON%’;
当前面没有%时，将使用索引，数据库内部转换为范围操作