oracle数据如何获取游标中动态字段_宜信的105条数据库军规

作为一家金融企业，宜信的大量业务重度依赖数据库。如何提高公司整体数据库应用水平，是对DBA的一大挑战，也非常具有现实意义。笔者在宜信的多年工作中，与团队一起总结并整理了针对传统关系型数据库使用规则，并借助自研的数据库审核平台落地。借此帮助研发团队评估数据库开发质量，并达到尽早发现问题、解决问题之目的。下图正是这一系统的简单原理图。

如上图所见，针对规则部分又可细分为如下分类(部分)。简单描述如下，后面将逐一详细说明。

1. Oracle规则(对象)

★ 表、分区

【规则1】

• 规则说明：超过指定规模且没有分区的表

• 规则阈值：2GB(物理大小超过指定阀值)

• 规则描述

  表的规模过大，将影响表的访问效率、增加维护成本等。常见的解决方案就是使用分区表，将大表转换为分区表。

【规则2】

• 规则说明：单表或单分区记录数量过大

• 规则阈值：1000000(单表或单分区记录数超过指定阀值)

• 规则描述

  控制单个表或单个分区的数据规模，提高单一对象的访问效率。如记录数过多，应考虑分库、分表、分区等策略。

【规则3】

• 规则说明：大表过多

• 规则阈值：自定义(超过2G的表的数量过多)

• 规则描述

  大表在所有表中所占比例超过20%(OLTP)或95%(OLAP)。

【规则4】

• 规则说明：单表分区数量过多

• 规则阈值：500(单表分区数量超过指定阀值)

• 规则描述

  分区表中分区数量过多，将导致整体维护成本过高，可调整分区粒度。

【规则5】

• 规则说明：分区表数量过多

• 规则阈值：2000(分区表数量超过指定阀值)

• 规则描述

  分区表过多，常见原因是大表较多。因根据需求，考虑进行垂直拆分，减小单库规模。

【规则6】

• 规则说明：复合分区数量过多

• 规则阈值：5(复合分区数量超过指定阀值)

• 规则描述

  同上面分区表数量过多理由类似(含有复合分区表的数量)。

【规则7】

• 规则说明：存在启用并行属性的表

• 规则阈值：1(表degree属性不为1)

• 规则描述

  一般情况下不建议对表设置并行属性。

★ 索引

【规则8】

• 规则说明：外键没有索引的表

• 规则描述

  外键没有索引会导致主子表关联查询时，关联效率很低。

【规则9】

• 规则说明：组合索引数量过多或没有索引

• 规则描述

  组合索引过多，将导致空间消耗较大、索引维护成本较高。应考虑构建战略性索引结构，不要针对每个需求都通过创建索引解决。

【规则10】

• 规则说明：单表索引数量过多

• 规则阈值：3(单表索引数量超过指定阀值)

• 规则描述

  索引可以提高访问速度，但数量过多将导致空间消耗过大，且索引维护成本较高，影响DML效率等问题。应控制索引数量。

【规则11】

• 规则说明：存在7天内没有使用的索引

• 规则描述

  在数据库一段时间内，该索引没有被任何SQL语句使用。请评估此索引的有效性。

【规则12】

• 规则说明：字段重复索引

• 规则描述

  一个字段被多个索引引用，请考虑构建策略。删除不必要的索引。

【规则13】

• 规则说明：存在全局分区索引

• 规则描述

  全局分区索引，存在维护成本较高问题。当分区发生变化是，需要维护全局索引的有效性。

【规则14】

• 规则说明：失效索引

• 规则描述

  索引状态为INVALID、UNUSABLE。

【规则15】

• 规则说明：索引高度超过指定高度

• 规则阈值：4

• 规则描述

  索引高度过高导致增加IO成本。

【规则16】

• 规则说明：存在位图索引

• 规则描述

  OLTP环境中不建议使用位图索引，如果表对象经查做DML操作，会在一定程度上阻塞相关操作。

【规则17】

• 规则说明：存在函数索引

【规则18】

• 规则说明：存在启用并行属性的索引

• 规则阈值：1(索引degree属性不为1)

• 规则描述

  一般情况下不建议对索引设置并行属性。

【规则19】

• 规则说明：存在聚簇因子过大的索引

• 规则阈值：自定义

• 规则描述

  聚簇因子过大的索引，应该考虑优化。

★ 约束

【规则20】

• 规则类别：约束

• 规则说明：没有主键的表

• 规则描述

  主键是关系型数据库中唯一确定一条记录的依据，没有任何理由不定义主键。

【规则21】

• 规则类别：约束

• 规则说明：使用外键的表

• 规则描述

  不建议使用外键约束，数据一致性通过应用端解决。

★ 字段

【规则22】

• 规则说明：表字段过多

• 规则阈值：100(字段数量超过指定阀值)

• 规则描述

  字段过多，会导致记录长度过大。单个数据存储单元将保存的记录数过少，影响访问效率。

【规则23】

• 规则说明：包含有大字段类型的表

• 规则描述

  大对象字段是关系型数据库中应尽量避免的。如有需要，可考虑在外部进行存储。

【规则24】

• 规则说明：记录长度定义过长

• 规则描述

  记录定义长度与实际存储长度差异过大，请考虑字段类型定义是否合理，个别字段过长是否可分表存储。

【规则25】

• 规则说明：不包含时间戳字段的表

• 规则描述

  时间戳，是获取增量数据的一种方法。建议在表内增加创建时间、更新时间的时间戳字段。命名方式为CREATE_TIME、UPDATE_TIME。

【规则26】

• 规则说明：表字段类型不匹配

• 规则描述

  此规则会抽样部分数据，分析其定义类型与存储类型是否相符。常见问题如用数字、文本保存日期等。

★ 其他对象

【规则27】

• 规则说明：缓存过小的序列

• 规则阈值：100(序列cache值小于指定阀值)

• 规则描述

  系统默认会缓存20，如过小将导致频繁查询数据字典，影响并发能力。

【规则28】

• 规则说明：存在存储过程及函数度

• 规则阈值：20(存储过程和函数的数量超过指定阀值)

• 规则描述

  存储过程及函数，将影响数据库的异构迁移能力，并存在代码维护性较差等原因。

【规则29】

• 规则说明：存在触发器

• 规则阈值：20(触发器数量超过指定阀值)

• 规则描述

  触发器，将影响数据库的异构迁移能力。如有数据一致性维护需求，请从应用端给予考虑。

【规则30】

• 规则说明：存在DBLINK

• 规则描述

  不建议在一个数据库中访问其他数据库，请考虑在应用端解决。

2. Oracle规则(执行计划)

★ 绑定变量

【规则31】

• 规则说明：未使用绑定变量

• 规则阈值：自定义(执行次数)

• 规则描述

  执行次数超过一定阀值的语句，谓词右侧存在常量值。

【规则32】

• 规则说明：绑定变量的数量过多

• 规则阈值：自定义(绑定变量的个数)

• 规则描述

  绑定变量数量过多会增加变量替换时间，在一定程度上增加sql执行时间。

★ 表间关联

【规则33】

• 规则说明：笛卡尔积

• 规则描述

  缺少连接条件，导致表间关联使用了笛卡尔积的连接方式，执行计划中包含"CARTESIAN|"字样。

【规则34】

• 规则说明：嵌套循环层次过深

• 规则阈值：自定义(层次数)

• 规则描述

  嵌套循环层次过深，超过指定阀值。执行计划中嵌套多层"NESTED LOOP"或"FILTER"字样。

【规则35】

• 规则说明：嵌套循环内层表访问方式为全表扫描

• 规则描述

  嵌套循环的内层表访问方式为全表扫描，效率很低。

【规则36】

• 规则说明：排序合并连接中存在大结果集排序

• 规则描述

  排序合并中两个结果集都要排序，应调整为其他连接方式。

【规则37】

• 规则说明：多表关联

• 规则阈值：自定义(表个数)

• 规则描述

  过多的表关联，影响性能。

★ 访问路径

【规则38】

• 规则说明：大表全表扫描

• 规则阈值：自定义(表大小，单位GB)

• 规则描述

  对大表执行了全表扫描操作，执行计划中包含"TABLE ACCESS FULL"字样。

【规则39】

• 规则说明：大索引全扫描

• 规则阈值：自定义(索引大小，单位GB)

• 规则描述

  对大索引执行了索引全扫描操作，执行计划中包含"INDEX FULL SCAN"字样。

【规则40】

• 规则说明：大索引快速全扫描

• 规则阈值：自定义(索引大小，单位GB)

• 规则描述

  对大索引执行了索引快速全扫描操作，执行计划中包含"INDEX FAST FULL SCAN"字样。

【规则41】

• 规则说明：索引跳跃扫描

• 规则描述

  对索引执行跳跃扫描操作，执行计划中包含"INDEX SKIP SCAN"字样。

【规则42】

• 规则说明：分区全扫描

• 规则描述

  对分区表进行了全分区扫描，执行计划中含有“PARTITION RANGE ALL”字样。

【规则43】

• 规则说明：非连续分区扫描

• 规则描述

  非连续分区扫描，执行计划中含有“PARTITION RANGE INLIST”或“PARTITION RANGE OR”字样。

【规则44】

• 规则说明：跨分区扫描

• 规则描述

  连续的分区扫描，执行计划中含有“PARTITION RANGE ITERATOR”字样。

★ 类型转换

【规则45】

• 规则说明：存在隐式转换

• 规则描述

  在条件判断中使用了隐式数据类型转换。

★ 其他执行计划

【规则46】

• 规则说明：存在大结果集排序操作

• 规则描述

  可考虑通过引入索引等操作避免排序。

【规则47】

• 规则说明：存在并行访问特征

• 规则描述

  并行很影响性能，一般情况下需要避免。

【规则48】

• 规则说明：存在视图访问

• 规则描述

  视图操作一般可以合并、解嵌套等，如都不行应该排查视图定义。

3. Oracle规则(执行特征)

★ 执行特征

【规则49】

• 规则说明：扫描块数与返回记录数比例过低

• 规则阈值：自定义(百分比)

• 规则描述

  扫描大量数据但返回记录数很少，需要从逻辑上调整SQL语句。

【规则50】

• 规则说明：子游标过多

• 规则阈值：自定义(子游标数)

• 规则描述

  子游标过多，可能存在执行计划不稳定的情况。

【规则51】

• 规则说明：elapsed_time

• 规则阈值：自定义

【规则52】

• 规则说明：cpu_time

• 规则阈值：自定义

【规则53】

• 规则说明：buffer_gets

• 规则阈值：自定义

【规则54】

• 规则说明：disk_reads

• 规则阈值：自定义

【规则55】

• 规则说明：direct_writes

• 规则阈值：自定义

【规则56】

• 规则说明：executions

• 规则阈值：自定义

4. MySQL规则(对象)

★ 表、分区

【规则57】

• 规则说明：超过指定规模且没有分区的表

• 规则阈值：自定义(表大小，GB)

• 规则描述

  表的规模过大，将影响表的访问效率、增加维护成本等。常见的解决方案就是使用分区表，将大表转换为分区表。

【规则58】

• 规则说明：单库数据表过多

• 规则阈值：自定义(表个数)

• 规则描述

  单库数据表过多，将影响整体性能。必要时，进行业务逻辑的垂直拆分。

【规则59】

• 规则说明：单表(分区)数据量过大

• 规则阈值：自定义(数据规模，记录数)

• 规则描述

  单表(分区)数据表过多，将影响整体性能。必要时，进行分库、分表或定期清理、归档数据。

★ 索引

【规则60】

• 规则说明：单表索引数量过多

• 规则阈值：自定义(索引数量)

• 规则描述

  单表索引数量过多，不仅维护成本高，而且占用更多的空间。

【规则61】

• 规则说明：存在重复索引

• 规则描述

  索引能由另一个包含该前缀的索引完全代替，是多余索引。多余的索引会浪费存储空间，并影响数据更新性能。

【规则62】

• 规则说明：索引选择率不高

• 规则阈值：自定义(选择率，百分比)

• 规则描述

  索引选择率不高，将导致索引低效，请调整索引字段。

★ 约束

【规则63】

• 规则说明：表存在外键

• 规则描述

  外键资源将消耗数据库的计算能力，建议通过应用层保证数据约束。

【规则64】

• 规则说明：表没有定义主键

• 规则描述

  没有定义主键，MySQL会自动创建主键。这不是一种好的设计方法。

★ 字段

【规则65】

• 规则说明：存在大对象字段

• 规则描述

  大对象字段将影响存取性能、耗费较多空间，建议在数据库之外存储。

【规则66】

• 规则说明：单表字段数过多

• 规则阈值：自定义(字段数)

• 规则描述

  表字段数过多，将造成记录过长，单页存储记录数减少。可考虑拆表处理。

【规则67】

• 规则说明：单表字段定义长度过长

• 规则阈值：自定义(字段长度，单位字节)

• 规则描述

  应控制单表定义长度，避免过长记录。

【规则68】

• 规则说明：单表主键字段定义长度过长

• 规则阈值：自定义(字段长度，单位字节)

• 规则描述

  应控制主键字段长度，过长的主键字段会造成索引空间消耗过大。

【规则69】

• 规则说明：表没有定义时间戳字段

• 规则描述

  时间戳字段是获取增量数据的最佳方法，请为表定义时间戳字段。

【规则70】

• 规则说明：字段数据类型定义错误

• 规则阈值：自定义(记录数)

• 规则描述

  根据字段保存内容判断，字段类型定义异常，建议选择适合的数据类型。

★ 其他对象

【规则71】

• 规则说明：单表存在函数、存储过程、触发器

• 规则描述

  存储过程、函数、触发器等都将消耗数据库的计算能力，建议通过应用层保证数据约束。

5. MySQL规则(执行计划)

★ 访问路径

【规则72】

• 规则说明：大表全表扫描

• 规则阈值：自定义(表大小，单位GB)

• 规则描述

  对大表执行了全表扫描操作。

★ SELECT_TYPE

【规则73】

• 规则说明：DEPENDENT UNION

【规则74】

• 规则说明：SUBQUERY

【规则75】

• 规则说明：DEPENDENT SUBQUERY

【规则76】

• 规则说明：MATERIALIZED

【规则77】

• 规则说明：UNCACHEABLE SUBQUERY

【规则78】

• 规则说明：UNCACHEABLE UNION

★ ACCESS_TYPE

【规则79】

• 规则说明：fulltext

【规则80】

• 规则说明：index_merge

【规则81】

• 规则说明：unique_subquery

【规则82】

• 规则说明：all

【规则83】

• 规则说明：index range

★ 其他执行计划

【规则84】

• 规则说明：使用临时表

• 规则描述

  执行过程中使用了临时表，执行计划中包括"using temporary"。

【规则85】

• 规则说明：使用磁盘排序

• 规则描述

  执行计划中使用了磁盘排序，执行计划中包含"using filesort"字样。

6. MySQL规则(执行特征)

★ 执行特征

【规则86】

• 规则说明：index_ratio

【规则87】

• 规则说明：lock_time_sum

7. Oracle+MySQL(语句级)

★ 查询类

【规则88】

• 规则说明：select *

• 规则描述

  禁止使用select *，必须明确选择所需的列。

【规则89】

• 规则说明：重复查询子句

• 规则描述

  禁止使用重复的查询子句，应使用with as替换子句(仅限Oracle)来提升SQL执行效率。

【规则90】

• 规则说明：查询字段引用函数

• 规则描述

  禁止在查询字段中引用函数(类型转换函数、函数索引情况可忽略)。

【规则91】

• 规则说明：嵌套select子句

• 规则描述

  禁止出现select子句的嵌套子查询，避免出现性能问题。

【规则92】

• 规则说明：出现union

• 规则描述

  防止出现不必要的排序动作。

【规则93】

• 规则说明：多个过滤条件通过or连接

• 规则描述

  防止优化器出现选择异常。

【规则94】

• 规则说明：谓词条件使用like '%xxx'

• 规则描述

  无法使用索引。

【规则95】

• 规则说明：谓词中存在负向操作符

• 规则描述

  !=,<>,!,not exists,not。

【规则96】

• 规则说明：存在子查询情况

• 规则描述

  这个要区分位置(select、from、where、having等部分)。

【规则97】

• 规则说明：存在三个以上的表关联

【规则98】

• 规则说明：存在全连接或外连接

• 规则描述

  cross join或outer join情况。

★ 变更类

【规则99】

• 规则说明：update中出现order by子句

• 规则描述

  防止更新过程中出现不必要的排序。

【规则100】

• 规则说明：update中必须出现where子句

• 规则描述

  防止出现意外的全部更新动作。

【规则101】

• 规则说明：更新主键

• 规则描述

  禁止出现更新主键的情况。

【规则102】

• 规则说明：delete中出现order by子句

• 规则描述

  防止删除过程出现不必要的排序。

【规则103】

• 规则说明：delete中必须出现where子句

• 规则描述

  防止出现意外的全部删除动作。

【规则104】

• 规则说明：新增SQL文本过长规则

【规则105】

• 规则说明：新增IN List元素过多

扫码关注

有趣的灵魂在等你