1.1.1. 编写SQL规则
1.1.1.1. 避免使用IN语句
原因
1. IN用作子查询时，会使查询时间变长，查询效率大大降低
2. IN会使系统无法使用索引，而只能全表扫描
替代方案
1. 做子查询时，使用EXISTS代替。
例如：SELECT CREATEOR_ID FROM LS_PLAN WHERE PLAN_ID IN(SELECT PLAN_ID FROM LS_TASK)改为：SELECT CREATEOR_ID FROM LS_PLAN WHERE EXISTS (SELECT PLAN_ID FROM LS_TASK WHERE PLAN_ID=LS_PLAN.PLAN_ID)。
2. 查询连续数值，BETWEEN不用IN。
例如：SELECT TASK_ID FROM LS_TASK WHERE TASK_ID IN(01,02,03) 改为： SELECT TASK_ID FROM LS_TASK WHERE TASK_ID BETWEEN01AND03。
1.1.1.2. 避免使用DISTINCT
原因
DISTINCT排序时要排序整张表，效率非常低。
替代方案
GROUP BY 代替。
例如：SELECT DISTINCT TASK_ID FROM LS_TASK 改为：
SELECT TASK_ID FROM LS_TASK GROUP BY TASK_ID
1.1.1.3. 合理使用NOT IN和NOT EXISTS
优化说明
1. 子查询结果集较小时（个数或者百数之内），使用NOT IN会优于NOT EXISTS。
2. 子查询具有一定复杂度时（即SQL关联关系较多，例如子查询中包含多个表查询），使用NOT IN会优于NOT EXISTS。
3. 其他情况下NOT EXISTS优于NOT IN。
原因
NOT EXISTS在总体上要优于NOT IN ,但不是绝对，合理使用NOT IN和NOT EXISTS能有效的减少查询时间。
替代方案
下面是两种使用NOT IN 的情况：
1. 子查询结果集较小。
例如：SELECT A1_ID FROM A1 WHERE NOT EXISTS (SELECT A1_ID FROM A2 WHERE A1_ID=A1.A1_ID AND A2.A2_ID <100)
改为： SELECT A1_ID FROM A1 WHERE A1_ID NOT IN (SELECT A2.A1_ID FROM A2 WHERE A2.A2_ID <100)。
2. 子查询有一定复杂度。
例如：SELECT A1_ID FROM A1 WHERE NOT EXISTS (SELECT A1_ID FROM A2 WHERE A1_ID=A1.A1_ID AND A2.A2_ID IN(SELECT A2_ID FROM A3 WHERE A2_ID<100) )
改为： SELECT A1_ID FROM A1 WHERE A1_ID NOT IN (SELECT A1_ID FROM A2 WHERE EXISTS (SELECT A2_ID FROM A3 WHERE A2.A2_ID=A3.A2_ID AND A2_ID<100))。
1.1.1.4. 避免在WHERE子句中对字段进行NULL值判断
原因
WHERE子句进行NULL值判断，容易使导致引擎进行全表扫描。
替代方案
使用空字符串来代替。包括在设计时，尽量不使用空字段，所有的空值都用空字符串表示。
例如：SELECT ID FROM T1 WHERE NUM IS NULL 改为： SELECT ID FROM T1 WHERE NUM = ‘’。
1.1.1.5. 正确使用LIKE
优化说明
1. 当查询的字符串在字段的开头（例如：AB A在字段开头）时，开头不要放通配符%。
2. 当在函数或者是存储过程中，去搜索某个字段是否拥有某个字符串在里面，不用LIKE。
原因
1. % 放在开头将无法使用索引而扫描全表
2. LIKE的使用是有限制的，它不允许跟在它后面的是一个变量或者是字段
替代方案
1. 例如BC在字段开头： SELECT PLAN_ID FROM LS_PLAN WHERE ORGAN_ID LIKE'%BC%'改为： SELECT PLAN_ID FROM LS_PLAN WHERE ORGAN_ID LIKE'BC%'
2. 用locate 代替。用法：locate(arg1,arg2,<pos>):在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始找arg1第一次出现的位置（第三个参数可省略）。
例如：SELECT PLAN_ID FROM LS_PLAN WHERE ORGAN_ID LIKE'%BC%'改为：SELECT PLAN_ID FROM LS_PLAN WHERE LOCATE('BC',ORGAN_ID)>0。
1.1.1.6. 避免在 WHERE子句中使用 OR 来连接条件
原因
若用OR连接的字段中有使用索引的字段，则使用OR 时，就无法利用索引扫描，而会遍历所有记录或所有的索引。
替代方案
用UNION合并代替OR。
例如：SELECT A2_NAME FROM A2 WHERE A2_ID =2OR A2_ID =7; 若A2_ID列存在索引，则改为：SELECT A2_NAME FROM A2 WHERE A2_ID =2UNION SELECT A2_NAME FROM A2 WHERE A2_ID =7。
1.1.1.7. 避免在WHERE子句的‘=’或者‘<>’的左边对字段进行函数、 算术运算或其他表达式操作
原因
做此操作可能会使系统无法正确使用索引。
替代方案
若有运算操作，放在表达式右边。
例如：SELECT A2_ID FROM A3 WHERE A2_ID/2=3改为 SELECT A2_ID FROM A3 WHERE A2_ID=3*2。
注：若运算简单，如上例，直接改成SELECT A2_ID FROM A3 WHERE A2_ID=6效率更高。
1.1.1.8. 避免不必要的排序
原因
排序是数据库中资源消耗比较大的一种操作，所以在业务允许的情况下，尽量避免不必要的排序。
替代方案
通过索引来代替排序，在GROUP BY、ORDER BY子句涉及的列上创建索引。（注意索引的排序是升序还是降序）
1.1.1.9. 表连接
原因
多表连接时，连接的顺序和连接条件影响SQL效率。
替代方案
1. 如果SQL中存在表A、B、C三表连接，则首先应保证最先连接的两表具有较小的子集。
2. 在进行表连接时，为连接字段和查询过滤字段（where 语句后的条件）建立索引，会得到很好的性能提升。
3. 当进行表连接时，确保连接条件只有一个，尤其是大表连接。
1.1.1.10. 使用fetch first N rows only来取多行数据
优化说明
当返回的结果集较多而我们用不到这么多数据时，使用fetch first N rows only。当结果集较多时，返回所有的结果集会增大所需的执行时间。
替代方案
例如，取结果集的前5列： SELECT A2_ID FROM A3 FETCH FIRST5ROWS ONLY。
1.1.1.11. 杜绝使用SELECT * FROM…
原因
当返回结果集很多时，会有很高的fetch，占用大量资源。
替代方案
只取所需要的列，把*改为所需查询的列名。
1.1.1.12. 避免使用CHAR/NCHAR
原因
定长字段所占存储空间较大
替代方案
使用VARCHAR/NVARCHAR代替，不仅可以节省空间，而且在一个相对小的字段搜索效率更高。
1.1.1.13. 尽量使用数字型字段
原因
只含数字信息的字段（例如：电话号码、年龄等）尽量不要使用字符型。若数字信息的字段设计为字符型，会降低查询和连接的性能，并增加存储开销。
替代方案
使用数字型代替字符型。
1.1.2. 建立索引规则
索引是表的一个或多个键值的有序列表。如果表上不存在索引，那么必须对SQL查询中引用的每个表进行全面扫描。表中记录数越多，每条记录越长，全表扫描花费的时间越长，因为全表扫描需要顺序访问表中所有的行。虽然对于需要表中大多数记录的复杂查询来说，使用表扫描效率可能更高，但对于只返回表中部分记录的查询，索引扫描可以更有效的访问表中的数据。
1.1.2.1. 根据条件中谓词的选择度创建索引
优化说明
因为建立索引需要占用数据库的存储空间，所以需要在空间和时间性能之间进行权衡。很多时候，只考虑那些条件子句中有条件判断的列上建立索引也有同样的效果，可以节约空间。
优化方法
可以通过简单的select count(*) from tabname where colA=’X’ 这种方式来查看每个谓词条件过滤的总数，过滤出结果集越小，代表选择度越高，如果是建立组合索引，那么应该将该谓词放在首位。
若条件子句中的判断条件为多个，可分别查询每一个判断条件以及组合条件的选择度，若单个条件选择度和整个条件语句差不多时，可直接建立单列索引来节约空间。
例如select count(*) from tabname where colA=’X’ and colB=’Y’
select count(*) from tabname where colA=’X’
select count(*) from tabname where colB=’Y’
结果分别为：1404、1407、128700
因为前两条选择度几乎相同，那么只建立对colA建立索引即可。
1.1.2.2. 避免在建有索引的列上使用函数
优化说明
如果在建有索引的列上使用函数，由于函数单调性无法确定，所以函数的返回值和输入值可能无法一一对应，有可能存在索引中位置差别很大的多个列满足带函数的谓词条件，所以DB2优化器无法进行匹配的索引扫描，甚至可能会直接进行全表扫描。
1.1.2.3. 在需要被排序的列上建立索引
使用背景
一般用于没有条件语句的查询
优化说明
因为索引本身是有序的，在需要被排序的列上建立索引可以避免查询时再次对数据进行排序，对于没有条件语句的查询，排序会在查询中占有较大比重，因此能利用索引的排序结构进行查询优化。若需要创建联合索引，将需要被排序的列放在联合索引的第一列。这一原则对大表非常有效。
1.1.2.4. 合理使用INCLUDE关键词
使用背景
只能创建在唯一索引中
优化说明
例如：SELECT NAME FROM ABC WHERE NUM BETWEEN ‘00010’ AND ‘00015’;
在NAME和 NUM上可以建立联合索引来提高查询性能，若NUM是唯一的，就可以使用INCLUDE关键字来创建唯一索引。对于查询来说，INCLUDE的唯一索引和联合索引作用是同等的，但是对于建立索引的时间和以后维护的代价来说，INCLUDE的唯一索引要优于联合索引，它可以有效地减少fetch cost，这对于大表（通常是百万级）来说，十分有效。
优化方法
例如：SELECT NAME FROM ABC WHERE NUM BETWEEN ‘00010’ AND ‘00015’;
如此例，NUM是唯一的，可创建唯一索引：
CREATE UNIQUE INDEX ABC.NUM_1 ON ABC(NUM) INCLUDE (NAME);
1.1.2.5. 指定索引的排序属性
优化说明
当查询某一列有限定范围时，例如：SELECT MAX(ADD_DATE) FROM TEMP，此时要查询ADD_DATE的最大值时， 索引的降序要比升序的效率更快，所以创建索引时合理的使用ASC和DESC。
1.1.2.6. 索引不是越多越好，合理创建索引
优化说明
索引会提高相应的SELECT的效率，但同时也会降低UPDATE和INSERT的效率，索引过多会增加SQL语句的编译时间。所以建议，一个表上的索引最好不要超过6个，考虑不常使用的列是否有必要建索引。
1.1.2.7. 尽量不要在主键上创建索引
优化说明
主键会隐式创建索引，所以不要在主键上创建索引来浪费空间
1.1.2.8. 具有大量重复数据的列上不要创建索引
优化说明
在大量重复的列上建索引没有任何意义，例如：字段col有大量的重复数据，数据Y占90%，数据N占10%，当查询col=’Y’时，该表的索引扫描和全表扫面没有太大区别，并且建立索引会占用空间。
1.1.2.9. 组合索引
优化说明
1. 考虑到管理上的开销，避免在索引中使用多于5个的列
2. 将查询中使用最多的列放在定义的前边
1.1.3. 拆表规则
1.1.3.1. 水平拆表
使用背景
1. 数据量较大。
2. 表中的数据有独立性，比如不同地区、不同时间等。
3. 需要把历史数据和当前数据区分开。
等以上情况适合水平拆分。
优化说明
水平拆分是根据某些条件将数据放在两个或多个表中，即按照记录拆分，每个表的表结构完全相同，数据行减少。
优化方法
1. 根据时间日期拆分。例如：某公司一年销售记录较大，可将表按月拆分，每月一张表。
2. 根据地区拆分。例如：某公司各地分公司较多，就可按地区分表。
3. 若根据上述两条拆分，表依旧很大，还可根据其他业务维度分。例如：根据时间地区分表后依旧很大，还可根据产品类型等业务维度再分。
特点
优点：降低在查询时需要读的数据和索引的页数。降低索引的层数，加快了查询。
缺点：会给应用增加复杂性，当查询操作时可能需要多个表名，查询所有数据需要UNION操作。对于很多数据库应用，这种复杂性可能会超过它带来的优点。
1.1.3.2. 垂直拆表
使用背景
适用于某些列常用，某些列不常用的表。
优化说明
垂直拆分是根据表的列进行拆分，拆分后的每一张表都带有原表的主键。简单来说就是主键列和一部分列作为一张表，主键列和其他部分列作为另一张表。
优化方法
1. 把不常用的列放在一张表中。
2. 把经常查询的列放在一张表中。
3. 把text、blob(二进制大对象，是一个可以存储二进制文件的容器)拆分出来放在附表中。
特点
优点：可使行数据变小，一个数据块可以存放更多数据，减少I/O次数。可以达到最大化利用Cache的目的。
缺点：垂直拆分后，主键出现冗余，需要管理冗余列。会引起表连接JOIN操作，增加CPU开销，需要从业务上规避。
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
