Oracle Sql 调优的一些建议

Oracle Sql 调优的一些建议

• • 最好的优化是降低调用频率，可以在业务上调整或使用缓存降低频率。
  • 小表一般不会存在性能问题，查询不频繁不超过十万行的表一般不需要建立索引。sql优化是针对大表的，小表一般不需要，因为对于小表来说全表扫描是效率最高的。
  • 索引不要创建很多，会导致表空间增大，dml的负担也会变大，更新很频繁的列建索引要小心。小表一般不需要建立索引。

• 尽量不要使用标量子查询，标量子查询驱动表固定为主表，除非主表返回的行数很少，不然会嵌套调用子查询（并且子查询的连接列要建索引），可以使用外连接，或者sql拆分来替代

• 如果sql比较简单，条件列和排序列都为同一个的话，可以通过建立正序或倒序索引来去除order by 排序操作（前提是列不能位null，或者有过滤null的条件）(有待商榷)

• 创建组合索引，列排列顺序按照等值过滤列、排序列、非等值过滤的顺序创建联合索引；没有等值过滤的列按照排序列，非等职过滤列的顺序创建。另外单独索引用的多的地方可以把列放在联合索引的前缀上，性能最好。

• 对于组合索引

  • 在等值查询的情况下组合索引无论哪一列在前性能都一样
  • 一列是范围查询，一列是等值查询的时候，等值查询在前范围查询在后的组合索引才高效
  • 如果单列查询和组合索引的前置列一样，则单列可以不建索引，直接利用组合索引来进行检索数据

• 尽量减少表返回的字段，返回的字段越多，回表越多，即使条件列有索引也一样。可以通过创建联合索引来减少回表。

• 对索引列进行函数运算会导致索引失效，尽量避免这样的情况（小表不影响，因为小表一般都会走全表扫描），实在无法避免，如果sql的调用频率很高的话可以创建函数索引来提高效率，不过函数索引的效率不如一般索引。

• 创建索引时要考虑索引列的重复度，重复度不高才适合创建普通索引；如果重复度很高，如性别、状态等，不适合创建普通索引，即使创建普通索引也不会生效，依旧会走全表扫描；

• 在列重复度很高，表数据量很大的时候（比如超过百万行），并且查询很多的情况，可以考虑建立位图索引。

• 位图索引的建立一定要慎重，位图索引只适合重复度很高的场景，并且使用位图索引后更新某一类被索引的列会导致这一类值都被锁住，如果索引列修改很频繁的话不适合建立位图索引。

• 建立普通索引的时候，要注意列有没有空值，如果含有大量空值不适合建立索引，如果一定要建索引可以考虑在数据建立的时候给个默认值，或者建立索引的时候给个用不到的常量。

• distinct 关键字会导致排序，最好在数据建立的时候就过滤调重复数据，不行的话可以考虑对distinct列创建索引来消除排序。

• 做两个索引列之间的关系比较时，需要注意两个列的字段属性是否一致，不一致oracle执行计划会隐式转换，会导致索引失效。

• 建索引是表级锁，对大表建立索引的时候一定要在更新次数少的时候进行。

• 如果对索引列有!=、 not 、is null 的情况一般是不会走索引的。

• 使用group by的时候应该在where条件中过滤尽可能多的数据。

• 尽量减少从大表中返回很多数据的场景，即使无法减少，也应该考虑减少返回数据和添加缓存。

• 通过mybatis查询很多数据量的时候，比如年限范围很大的走势图，可以尝试加入fetchSize来提高数据返回速度，如：

  • <select id="findResourcesByRoleIds" resultType="string" parameterType="map" fetchSize="50">

• mybatis 中最好使用# 来获取参数，体现在oracle中就是使用绑定变量，绑定变量可以减少解析，提高共享池的命中率，降低cpu使用率。当用在字段名和表名的时候只能使用$取值。

• 在建表生成数据的时候最好给需要用到的列或者用到索引的列给个默认值，尽量不要为null。像是日期之类的字段最好处理成和前端需要的格式入库，减少sql中函数的使用。

• 尽量使用union all 代替union ，因为union 需要排序，即使加了索引也无法消除排序

• 对于有大量插入和更新操作的中间表可以使用全局临时表空间来减少日志的生产，一个会话结束就会高效的删除中间表。

• delete 无法释放表空间，truncate可以释放表空间但是无法加条件，但是可以配合分区表实现高效清理数据

• 对于回表和排序可以创建索引组织表和有序散列聚簇表，但是仅限于更新和插入操作很少的情况

• 允许的话尽量使用in来替代范围查询

• 对列有进行函数运算会导致索引失效，最好在数据落地的时候就处理好或者尽量不使用函数，实在需要的话可以创建函数索引，性能介于普通索引和全表扫描之间。

• 关于表连接

  • 嵌套循环连接，驱动表返回多少条记录，被驱动表就会被访问多少次。不排序。连接方式不受限制。适合在两表关联返回的记录不多，或者遇到不等值查询导致其它连接无法使用的场景。建议在驱动表的条件列和被驱动表的条件列和限制列中都加索引。
  • 散列连接，驱动表和被驱动表都只会被访问0次或者1次。不排序，消耗内存是用于建立散列列表。散列不支持不等值连接<>、不支持>和<的连接、不支持LIKE的连接。仅需要考虑限制条件列上的索引，不需要考虑连接条件上的索引，因为不会使用到。
  • 排序合并连接，只会访问0次或者1次，没有驱动表和被驱动表的概念。会排序，会消耗PGA的空间，过大会导致排序在磁盘进行。不支持不等值<>连接，不支持LIKE连接，支持>和<连接。连接条件中的所有没有检索的作用，但是却有消除排序的所用。