论文部分内容阅读
摘要：针对当前较为常见的Oracle数据库应用，尤其是数据体量越来越大的现状下，文章提出了几种切合实际应用的优化方式，在一定程度上提高了SQL语句的执行效率，极大地加快了数据的查询检索等操作，充分实现了优化的目的。
关键词：oracle；数据库；SQL语句；查询；优化
中图分类号：TP311.13 文献标识码：A 文章编号：1009-3044（2018）08-0012-02
随着大数据、物联网等概念的兴起，关于数据的分析应用逐步从后台走向了前台，生活中各类的软件开发、终端应用都离不开背后支撑整个环境的数据本身，动辄上亿甚至百千亿级别的数据亦不罕见，面对如此庞大的数据量，效率“二字”也被提升上了一个空前的高度之上。作为目前使用最为广泛应用的SQL数据库语句，如何对其做性能优化，提升工作效率，去促成更强的决策能力、洞察力与最优化处理，成为信息产业所有人聚焦的核心。本文从Oracle常用的SQL入手，结合工作教学，指出了几种常见的性能优化方式。
1引言
就目前而言，Oracle先天存在的跨平台、可伸缩并行、分布式计算等特性都良好的适配了当前大数据的背景，其应用范围也在逐渐扩大，对应的数据体量和复杂度也不断增加，提高数据库的处理性能只能依靠硬件资源的升级换代和SQL语句的优化，相比之下，SQL的语句优化贯穿持续整个软件运行周期，其必然性不言而喻。
2 SQL语句优化的原则
从程序开发角度来看，对于Oracle数据的操作是通过SQL语句来实现的，其数据的精炼程度，直接决定了交互的效率，是提高整个数据库性能的最立竿见影且成本最低的方式。优化的原则就是从成本人手，减少数据访问量、减少交互次数，从而返回更少的数据，达到减少CPU和内存开销，最终增加可用资源，即漏斗原则。
3 Oracle数据库中常见的优化策略
1）合理的创建和使用索引
根据表的大小和使用率来创建索引，作为数据库管理员来说，索引一样使用系统开销，并非所有的表都需要创建索引，为合适的表创建索引而非为所有的表创建索引。一般来说不需要为比较小的表创建索引，因为即使创建了索引，其性能并没有得到改善，反而要付出建立索引的开销和维护成本，得不偿失。而对于较大的表，要首先分析表中需要查询的数据量，若经常需要查询的数据不超过15%，则创建索引的必要性就需要斟酌了，15%只是实际的一个经验数据，也可以测试一下全表查询的时间，和建立索引相对比时间是否缩短，来评估索引是否建立的必要性。
按照经验来说，索引可以提高Oracle数据库的查询效率，但数据库进行更新时，包括增加、删除、改写等，都会对索引进行更新，当索引越多时，占用资源的开销就会越大，需要在两个之间寻求一个平衡点。原则上当表的记录更新为主时，不要见太多索引，当表数据需要频繁调用时，则需要比较多的索引。
2）大数据量查询记录数优化
在目前大数据的背景下，以亿为单位数据时常遇到，一般数据库操作人员习惯使用Count（*）来统计表的记录数，Count命令执行的是Table Full Scan，也就是全表扫描，会占用极大地资源开销。实际上，无论是否建有索引，都应该使用sysindexes来进行查询，可以很快的返回结果。
3）尽量避免通配符首位出现
当通配符（%）在搜索词的首位出现时，Oracle将不适用数据表的索引，虽然很多情况下我们无法避免此类情况，但要慎重执行，通配符首位出现时会降低查询的效率。例如我們在sample表中查询包含“子”的人，通常采用：
Select*from sample where sample_name hke‘%子%’；
此时索引无法被利用，但当通配符出现在其他位置时，就可以利用索引，例如：
Select*from sample where sample_name hke‘李%’；
4）尽量使用UNION ALL代替UNION命令
UNION执行的是表连接并去重复的操作，包含了多表的连接、结果集的排序、重复数据的剔除，而在实际的运用场景下，尤其是已经确定了主键的表，并不会产生重复的数据，应尽可能地使用UNION ALL代替UNION来进行操作，直接对多表进行合并操作，节约不必要的资源开销，尤其是大数据量的操作时。
5）用MERGE改写UPDATE子查询
MERGE命令是Oracle9i及以后版本新增的命令，其字面上的意思就是合并、兼并，用来合并UPDATE和INSERT语法。通过MERGE命令，根据一张表或子查询的连接条件对另外一张表进行查询，当连接条件匹配的时候进行数据更新，执行UP-DATE操作，无法匹配时为这个表新增一条数据，执行INSERT操作。这个命的执行效率远高于INSERT UPDATE，仅进行一次全表扫描就完成了所有工作。例如：
MERGE INTO Target AS A
USING Source AS B
ON A.aac002=B.aac002
WHEN MATCHED——-当aac002匹配时，目标表进行数据更新
THEN UPDATE SET A.aac001=B.aac001
WHEN NOT MATCHED——-当目标表未查询到，在原表有的则进行插入
THEN INSERT VALUES（A.aac002，B.aac001）
WHEN WHEN NOT MATCHED BY SOURCE——目标表存在，而原表不存在时进行查询
THEN DELETE
即简洁完整的执行了一系列更新、插入、删除的操作，又相比传统的UPDATE和INSERT极大地提高了效率。 6）习惯使用COMMIT命令
当执行数据操纵语言之后，尽可能地多使用COMMIT命令进行提交操作，对占用的rollback回滚记录进行释放，以释放资源提高整体性能，即释放以下开销：
①回滚段上用于恢复数据的记录信息；
②被程序语句获得的锁；
③redo log buffer中的占用的空间；
④ORACLE为管理上述3种资源占用的内部开销。
7）尽量使用WHERE代替HAVING语句
尽可能地减少HAVING语句的使用范围，在一个完整的SELECT语句中ON最先执行，其次是WHERE，再次是聚合函数计算，最后才是HAVING和排序操作等，如果能在HAVING的前置环节限制检索的数据量，就可以减少之后的分组运算开销，即在分组之前过滤数据。综合来看，一类是WHERE语句和HAVING语句同时存在的SQL语言，通过WHERE字句限制数据记录的数量，减少后续聚合运算、分组筛选、排序操作的资源开销。另一类是WHERE字句和HAVING字句都可以实现的SQL语句，应该直接使用WHERE字句代替HAVING字句，此时在筛选排序之前就先行进行了过滤，效率远高于HAVING字句，例如同样两个语句：
8）增加执行命令的重复使用率
SQL语句执行完毕后会驻留在高速缓存中，多数时候命令的执行具有重复性，要尽量保证同一功能SQL语句的一致性，尽量确保重复执行时直接调用驻留在高速缓存中的执行计划。在某些大数据平台上，一些基础的SQL语句每秒钟会重复执行上千次，重复调用执行计划可以大大的提高资源使用效率，建议一是使用对象（如表和视图）的完全合法一致的名称，二是在应用程序中规范使用变量，最大限度保持全局一致，提高重復使用率。
4借助优化软件或者购买服务进行SQL优化
无论人工优化还是机器软件优化，其目的都是为了更高的提高效率，相比较人工而言，高速发展的人工智能可以无疲劳、不间断的进行各种语句性能比较，找到最优的路径，未来的SQL优化也将逐步实现智能化的优化方式。
例如SQL Turning、SQLExpert更优化软件，以及Tkprof等跟踪软件，都是为了采用机器或者量化的方式进行语句的优化，使得原本枯燥的语句分析变得更加直观。而对于一些实力略显不足的开发者而言，购买第三方服务也不失为一个选择，甚至随着分工的精细化，在大数据的产业链中，会衍生围绕优化的服务产业并蓬勃发展。
5结束语
其实关于Oracle的SQL语句优化是一个复杂的过程，上述的一些心得仅仅是应用层的一些表现，关于数据库的运维还涉及底层的资源分配、网络层的流量控制和操作系统的构架，甚至相同的平台的不同类型数据，其优化方式的原则都要随之改变，而不是教条式的简单修改，作为我们使用者而言，应该多加探索，借助优化软件、跟踪分析，加以人工辨别，找到一个适合自己的行之有效优化方式。
关键词：oracle；数据库；SQL语句；查询；优化
中图分类号：TP311.13 文献标识码：A 文章编号：1009-3044（2018）08-0012-02
随着大数据、物联网等概念的兴起，关于数据的分析应用逐步从后台走向了前台，生活中各类的软件开发、终端应用都离不开背后支撑整个环境的数据本身，动辄上亿甚至百千亿级别的数据亦不罕见，面对如此庞大的数据量，效率“二字”也被提升上了一个空前的高度之上。作为目前使用最为广泛应用的SQL数据库语句，如何对其做性能优化，提升工作效率，去促成更强的决策能力、洞察力与最优化处理，成为信息产业所有人聚焦的核心。本文从Oracle常用的SQL入手，结合工作教学，指出了几种常见的性能优化方式。
1引言
就目前而言，Oracle先天存在的跨平台、可伸缩并行、分布式计算等特性都良好的适配了当前大数据的背景，其应用范围也在逐渐扩大，对应的数据体量和复杂度也不断增加，提高数据库的处理性能只能依靠硬件资源的升级换代和SQL语句的优化，相比之下，SQL的语句优化贯穿持续整个软件运行周期，其必然性不言而喻。
2 SQL语句优化的原则
从程序开发角度来看，对于Oracle数据的操作是通过SQL语句来实现的，其数据的精炼程度，直接决定了交互的效率，是提高整个数据库性能的最立竿见影且成本最低的方式。优化的原则就是从成本人手，减少数据访问量、减少交互次数，从而返回更少的数据，达到减少CPU和内存开销，最终增加可用资源，即漏斗原则。
3 Oracle数据库中常见的优化策略
1）合理的创建和使用索引
根据表的大小和使用率来创建索引，作为数据库管理员来说，索引一样使用系统开销，并非所有的表都需要创建索引，为合适的表创建索引而非为所有的表创建索引。一般来说不需要为比较小的表创建索引，因为即使创建了索引，其性能并没有得到改善，反而要付出建立索引的开销和维护成本，得不偿失。而对于较大的表，要首先分析表中需要查询的数据量，若经常需要查询的数据不超过15%，则创建索引的必要性就需要斟酌了，15%只是实际的一个经验数据，也可以测试一下全表查询的时间，和建立索引相对比时间是否缩短，来评估索引是否建立的必要性。
按照经验来说，索引可以提高Oracle数据库的查询效率，但数据库进行更新时，包括增加、删除、改写等，都会对索引进行更新，当索引越多时，占用资源的开销就会越大，需要在两个之间寻求一个平衡点。原则上当表的记录更新为主时，不要见太多索引，当表数据需要频繁调用时，则需要比较多的索引。
2）大数据量查询记录数优化
在目前大数据的背景下，以亿为单位数据时常遇到，一般数据库操作人员习惯使用Count（*）来统计表的记录数，Count命令执行的是Table Full Scan，也就是全表扫描，会占用极大地资源开销。实际上，无论是否建有索引，都应该使用sysindexes来进行查询，可以很快的返回结果。
3）尽量避免通配符首位出现
当通配符（%）在搜索词的首位出现时，Oracle将不适用数据表的索引，虽然很多情况下我们无法避免此类情况，但要慎重执行，通配符首位出现时会降低查询的效率。例如我們在sample表中查询包含“子”的人，通常采用：
Select*from sample where sample_name hke‘%子%’；
此时索引无法被利用，但当通配符出现在其他位置时，就可以利用索引，例如：
Select*from sample where sample_name hke‘李%’；
4）尽量使用UNION ALL代替UNION命令
UNION执行的是表连接并去重复的操作，包含了多表的连接、结果集的排序、重复数据的剔除，而在实际的运用场景下，尤其是已经确定了主键的表，并不会产生重复的数据，应尽可能地使用UNION ALL代替UNION来进行操作，直接对多表进行合并操作，节约不必要的资源开销，尤其是大数据量的操作时。
5）用MERGE改写UPDATE子查询
MERGE命令是Oracle9i及以后版本新增的命令，其字面上的意思就是合并、兼并，用来合并UPDATE和INSERT语法。通过MERGE命令，根据一张表或子查询的连接条件对另外一张表进行查询，当连接条件匹配的时候进行数据更新，执行UP-DATE操作，无法匹配时为这个表新增一条数据，执行INSERT操作。这个命的执行效率远高于INSERT UPDATE，仅进行一次全表扫描就完成了所有工作。例如：
MERGE INTO Target AS A
USING Source AS B
ON A.aac002=B.aac002
WHEN MATCHED——-当aac002匹配时，目标表进行数据更新
THEN UPDATE SET A.aac001=B.aac001
WHEN NOT MATCHED——-当目标表未查询到，在原表有的则进行插入
THEN INSERT VALUES（A.aac002，B.aac001）
WHEN WHEN NOT MATCHED BY SOURCE——目标表存在，而原表不存在时进行查询
THEN DELETE
即简洁完整的执行了一系列更新、插入、删除的操作，又相比传统的UPDATE和INSERT极大地提高了效率。 6）习惯使用COMMIT命令
当执行数据操纵语言之后，尽可能地多使用COMMIT命令进行提交操作，对占用的rollback回滚记录进行释放，以释放资源提高整体性能，即释放以下开销：
①回滚段上用于恢复数据的记录信息；
②被程序语句获得的锁；
③redo log buffer中的占用的空间；
④ORACLE为管理上述3种资源占用的内部开销。
7）尽量使用WHERE代替HAVING语句
尽可能地减少HAVING语句的使用范围，在一个完整的SELECT语句中ON最先执行，其次是WHERE，再次是聚合函数计算，最后才是HAVING和排序操作等，如果能在HAVING的前置环节限制检索的数据量，就可以减少之后的分组运算开销，即在分组之前过滤数据。综合来看，一类是WHERE语句和HAVING语句同时存在的SQL语言，通过WHERE字句限制数据记录的数量，减少后续聚合运算、分组筛选、排序操作的资源开销。另一类是WHERE字句和HAVING字句都可以实现的SQL语句，应该直接使用WHERE字句代替HAVING字句，此时在筛选排序之前就先行进行了过滤，效率远高于HAVING字句，例如同样两个语句：
8）增加执行命令的重复使用率
SQL语句执行完毕后会驻留在高速缓存中，多数时候命令的执行具有重复性，要尽量保证同一功能SQL语句的一致性，尽量确保重复执行时直接调用驻留在高速缓存中的执行计划。在某些大数据平台上，一些基础的SQL语句每秒钟会重复执行上千次，重复调用执行计划可以大大的提高资源使用效率，建议一是使用对象（如表和视图）的完全合法一致的名称，二是在应用程序中规范使用变量，最大限度保持全局一致，提高重復使用率。
4借助优化软件或者购买服务进行SQL优化
无论人工优化还是机器软件优化，其目的都是为了更高的提高效率，相比较人工而言，高速发展的人工智能可以无疲劳、不间断的进行各种语句性能比较，找到最优的路径，未来的SQL优化也将逐步实现智能化的优化方式。
例如SQL Turning、SQLExpert更优化软件，以及Tkprof等跟踪软件，都是为了采用机器或者量化的方式进行语句的优化，使得原本枯燥的语句分析变得更加直观。而对于一些实力略显不足的开发者而言，购买第三方服务也不失为一个选择，甚至随着分工的精细化，在大数据的产业链中，会衍生围绕优化的服务产业并蓬勃发展。
5结束语
其实关于Oracle的SQL语句优化是一个复杂的过程，上述的一些心得仅仅是应用层的一些表现，关于数据库的运维还涉及底层的资源分配、网络层的流量控制和操作系统的构架，甚至相同的平台的不同类型数据，其优化方式的原则都要随之改变，而不是教条式的简单修改，作为我们使用者而言，应该多加探索，借助优化软件、跟踪分析，加以人工辨别，找到一个适合自己的行之有效优化方式。