优化数据访问_chuanzhi.pdf

优化数据访问 概要 ? 本章主要介绍数据库引擎如何访问数据。 – full scan table – Index 1. 找不出理想的访问路径 ? 查询优化器正确的选择执行计划后，不意 味着执行计划一定能运行良好。 ? 怎么办？ –调整SQL –调整访问结构（add index） ? 本节重点： –介绍几种检查手段，用来识别不理想的访问路 径 –找出原因并加以解决。 1.1 识别 ? 衡量访问路径的标准： – 处理数据时消耗的资源是否可接受。 ? 可以接受的标准： – 一个访问路径所使用的资源和返回行的总数成正比例。 – 所以检查应该基于返回单行所用的资源总量。 ? 关注效率，而不仅仅是速度。 – SQL语句使用越少资源，系统就会越具有扩展性、越快。 – 资源：CPU、MEMORY、磁盘和网络。 ? 易于收集的度量： – 逻辑读数量 ? 受制于CPU ? 可能导致物理读 ? 受制于串行操作，减少逻辑读有利于避免扩展性问题。 1.1 识别 ? 经验法则： – 集中精力解决每个返回行具有较多逻辑读的访问路径上 每个返回行逻辑读个数 效率 5 好 10-15 可接受 15-20 低效 1.1 识别 ? 举例： Select * from t where n1 between 6000 and 7000 and n2=19; TKPROF输出内容摘要： Rows Row Source Operation ------- --------------------------------------------------- 3 TABLE ACCESS BY INDEX ROWID T (cr=27 pr=0 pw=0 time=0 us cost=21 size=1340 card=5) 24 INDEX RANGE SCAN T_N2_I (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=20)(object id 61330) 注意： 只考虑访问路径层的数据，而不是整个SQL语句，因为SQL语句 层的数据可能会引起误导。 1.1 识别 ? 举例： select sum(n1) from t where n2246; TKPROF输出内容摘要： Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=389 pr=0 pw=0 time=0 us) 160 TABLE ACCESS FULL T (cr=389 pr=0 pw=0 time=6996 us cost=106 size=1256 card=157) ? 操作1 对操作2的结果使用了单行函数。结果，总是返回 单行而隐藏了访问路径的性能数据。 1.2 陷阱 ? 检查logical read数量时，提防两个陷阱： – 读一致性（read consistency） – 行预取 1.2.1 read consistency ? 事物的读一致性：Undo ? 基于当前块以及回滚信息，数据块相同副本应该在运行时 创建。要运行这个操作，将产生若干个逻辑读。 ? 参照实验read_consistency.sql 1.2.2 行预取 ? 这种情况较少发生，在此不做特殊讲解。 1.2.3 原因 ? 导致低效率访问路径原因： – 没有提供合适的访问结构 – 提供合适访问结构，但SQL语法不允许使用。 – 表或者索引是分区的，但由于不能pruning，因而要访问所有分区。 – Table or index 没有分区。 – 查询优化器缺少统计信息。 – 查询优化器自身的bug或者运行机制的限制。 1.2.4 解决办法 ? 最小化逻辑读为目标。 – 添加访问结构 – 调整物理分区 ? 从大体上来看： – 存在适当index，具有强选择性的操作执行更高效。 – 选择性非常弱，全表扫描更高效。 2 具有弱选择性的SQL语句 ? 全表扫描或者全分区扫描。 ? 很多场景只能用全表扫描。 – 分区是企业版功能。 – 不是所有表是分区表。 – 有限的列分区。 ? 特定场景下，全表扫描和全分区扫描可能 被全索引扫描替代，因为他们比较小。 2.1 全表扫描 ? 顺序读取高水位线以下的all blocks。log