基于Oracle的SQL优化.pptx

基于Oracle的SQL优化数据库调优的四个层级调优的四个层级优化物理设计、优化SQL优化对锁、缓存等资源的争用优化内存以最小化物理IO优化物理IO本主题仅讨论第一个层级性能不良的SQL既可能是设计欠佳的数据库对象，也可能是编写差劲的SQL数据库物理设计表的规范化表的反规范化索引设计模式设计参数设计表分区物化视图表的规范化原则上满足第三范式（3NF）或第四范式（BCNF）选择稳定的列作为主键尽量利用约束保护数据完整性非空值约束数据检查约束唯一性约束参照完整性约束好的规范化带来的益处去除冗余数据，降低存储成本和维护成本利用约束和依赖关系，保证数据完整性表的反规范化要解决的问题应用有时频繁地进行多个表的连接操作以获取数据，代价较高规范化程度越高，表数量也越多，发起的表连接操作也越多反规范化的手段增加冗余列、虚拟列表的合并 – 将关系密切的两张表合并为一张表概要表 – 存储汇总或聚集数据，实时或定时更新垂直分区 – 将表中较少访问的占用空间较多的列存储在另一张表中带来的问题冗余数据的存在增大维护成本增大破坏数据一致性的风险索引设计常见的索引类型B树索引位图索引函数索引建议不允许索引使用应用表空间组合索引使用选择性大的列作为前导列为子表的外键建立索引，能防止父表在主键更新时锁住整张子表由于锁住的记录量大，位图索引通常不适合OLTP应用审慎考虑是否有必要使用函数索引单张表的索引不超过5条索引压缩能提高前导列有重复值的组合索引的性能前导列选择性较差时，索引跳跃扫描效果相对较好模式设计表空间不使用系统表空间分离索引表空间与应用表空间为大对象（如CLOB）指定与其所在表不同的表空间历史数据表与非历史数据表使用不同的表空间表以读为主要操作的表可考虑使用表压缩考虑设置表的并行度启用数据缓存化使经常访问的热表常驻内存列定长字符使用CHAR类型，不定长字符使用VARCHAR2类型使用NUMBER类型时必须指定长度不使用LONG类型尽量将经常访问的列放在前部（非硬性要求）空列放在后部，可减少单行的数据长度，提高全表扫描的效率参数设计?大量单笔查询的表，不改变表默认的DEGREE参数更新频繁的表设置较大的PCFREE参数，减少发生行迁移的可能，但过大的PCFREE会引起空间浪费，降低全表扫描性能，参考值：10PCFREE 30并行DML事务较多的表，应设置较大的INITRANS参数，防止资源竞争导致并发性能下降，参考值：5 INITRANS 20设置DB_FILE_MULTIPLE_READ_COUNT参数增大一次I/O操作读取的数据块数，提高全表扫描的效率数据量小且查询频繁的静态参数表，添加STORAGE(BUFFER_POOL KEEP)，让数据常驻内存表分区适用场景千万级记录或大于4GB的表定期删除过期数据的表希望提高并行处理能力的表带来的益处分区排除技术使查询只读取相关分区的数据通过删除分区来高效删除过期数据提高并行处理能力，降低锁争用选择分区类型哈希分区范围分区列表分区组合分区注意事项将分区表设置为允许行迁移，否则更新分区键时会出错物化视图作用保存表连接或聚集等耗时较多的操作的返回结果适用场景其他手段都无法进一步优化查询时不修改SQL又希望提高查询性能时逻辑读数量与返回记录数量之间的比率非常高时注意事项数据仓库是其主要的应用场景，对OLTP应用下对于查询频繁但更新相对较少的表也可以考虑使用更新频率不能过高，否则严重影响性能SQL优化基于成本计算的Oracle优化器SQL是一种非过程化语言，优化器基于成本计算来确定执行SQL的最优方法成本计算的影响因素对象统计信息（如表的大小、各列中数据的分布）数据库参数与配置系统统计信息（如CPU、IO）确保收集统计信息的全面和准确为重要的表和索引制定统计信息收集策略使用DBMS_STATS而不使用ANALYZE收集统计信息重新收集数据量更新超过10%的表及索引的统计信息收集统计信息最好选择在维护时间窗口SQL优化 – 执行计划执行计划描述了Oracle优化器为执行SQL语句而进行的操作收集的统计信息是优化器决定执行计划的基础，但通过一些技术能重写或优化执行计划提示 - INDEX、FULL、ORDERED等存储提纲 – 固化执行计划以提供计划稳定度SQL概要 – 收集特定SQL执行的信息，创建更全面深入的统计信息SQL基线 – 只有当新的计划被证明更好时才允许计划改变SQL优化优化技术数据访问优化表连接优化并行DML优化应用设计绑定变量批量操作存储过程锁策略SQL优化技术 – 数据访问优化数据访问方式只访问少量记录时使用索引，几乎所有记录都需要访问时使用全表扫描，中间状况时具体分析判断优化技术避免阻碍索引使用的情形不等条件（!=、、NOT）空值查询（IS NULL、IS NOT NULL）对索引列使用函数LI