SQL审核与优化ppt

PPT 内容
PPT 图集

1. SQL审核与优化
2. DevOps 势在必行：辅助开发SQL审核基于前端开发的SQL审核服务 – 实现优化前置 SQL是一项专业的技能 – Oracle SQL Language Reference ~ 2000 页由于开发人员的技能差异、变化频繁，很多SQL隐患在开发环节被埋入系统； SQL审核通过专业的工具和SQL专家服务，守住上线关卡，实现规范落地；提前发现及预防因SQL编写不合理而带来的性能隐患300%SQL审计通过优化SQL语句直接提升系统性能和用户感受 SQL执行效率大幅提升应用系统效率大幅提升系统资源充分利用全面审计SQL的结构、编写标准、执行计划、性能等内容，降低低效SQL造成的压力和提升系统开发质量SQL中子查询使用的是否合理表连接方式及顺序是否合理执行计划是否有效SQL编写是否符合统一标准索引设计不合理，缺乏战略索引设计(以表单位)。以表为单位，搜集SQL的Access Path。通过对其进行综合性的分析，设计出最佳索引。实现以最少的索引个数满足最多读取要求的目标。SQL审核SQL开发培训线上SQL优化
3. 索引与索引扫描1表连接23生产库审核与优化案例真正读懂SQL执行计划4
4. 索引索引类型： B*-tree索引单列及组合索引唯一及非唯一索引函数索引索引组织表位图索引特殊的属性: 压缩正序，反序反向键值索引
5. 正常的 B*-tree 索引存储结构Index entry headerKey column lengthKey column valuerowidRootBranchLeafIndex entry通过rowid获取数据表的记录
6. 索引扫描索引扫描类型: 唯一范围扫描全扫描快速全扫描跳跃扫描B-Tree indexBBBBBBTableB : block
7. 索引唯一扫描（INDEX UNIQUE SCAN )create unique index ind_object_id on test_objects(object_id); SQL> select object_id from test_objects where object_id=275257; Execution Plan ---------------------------------------------------------- Plan hash value: 3714264582 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | |* 1 | INDEX UNIQUE SCAN| IND_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=275257)
8. 索引范围扫描（INDEX RANGE SCAN）SQL> select object_id from test_objects where object_id>275200; 43 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2038338801 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21 | 105 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_OBJECT_ID | 21 | 105 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID">275200)
9. 索引快速全扫描(INDEX FAST FULL SCAN)LEGEND: SH=segment header R=root block B=branch block L=leaf blockL...RBBLLLLLSHmultiblock readdb_file_multiblock_read_count = 4multiblock readSQL> select object_id from test_objects where object_id is not null; 101467 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2799590446 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 101K| 495K| 59 (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| IND_OBJECT_ID | 101K| 495K| 59 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NOT NULL)
10. 索引快速全扫描(INDEX FAST FULL SCAN)SQL> select * from test_objects where object_id is not null; 101467 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3570092908 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 101K| 9413K| 398 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 101K| 9413K| 398 (1)| 00:00:05 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NOT NULL)
11. 索引全扫描(INDEX FULL SCAN)SQL> select object_id from test_objects where object_id is not null order by 1; 101467 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3454935350 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 101K| 495K| 213 (1)| 00:00:03 | |* 1 | INDEX FULL SCAN | IND_OBJECT_ID | 101K| 495K| 213 (1)| 00:00:03 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NOT NULL)
12. 索引跳跃扫描(INDEX SKIP SCAN)create index ind_com on city(country_id,city_id); SQL> select * from city where city_id='1'; Execution Plan ---------------------------------------------------------- Plan hash value: 1118414011 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 13 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| CITY | 1 | 21 | 13 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | IND_COM | 1 | | 12 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CITY_ID"='1') filter("CITY_ID"='1')
13. 索引跳跃扫描(INDEX SKIP SCAN)SQL> select /*+ no_index(city ind_com) */ * from city where city_id='1'; Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 3973142328 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 996 (1)| 00:00:12 | |* 1 | TABLE ACCESS FULL| CITY | 1 | 21 | 996 (1)| 00:00:12 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CITY_ID"='1')
14. 位图索引（BITMAP INDEX）<Blue, 10.0.3, 12.8.3, 100010000 010000000 010010100><Green, 10.0.3, 12.8.3, 000101000 000000000 100100000><Red, 10.0.3, 12.8.3, 010000000 001100000 000001001><Yellow, 10.0.3, 12.8.3, 001000000 100000000 001000010>KeyStart ROWIDEnd ROWIDBitmapTableIndexBlock 10Block 11Block 12File 3
15. 位图索引SELECT * FROM PERF_TEAM WHERE country in('FR','DE');ORSELECT * FROM EMEA_PERF_TEAM T WHERE country='FR' and gender='M';FR001111000000M111011010111AND
16. 如何考虑索引的使用怎么选择索引：要不要建？根据需求哪个字段上建？Where条件中惟一值较多的字段建什么类型的？单列 / 组合唯一 / 非唯一分区 / 非分区 B-tree / 位图函数索引存储位置与表分开，分散在多个表空间上存储参数区大小 / 块空间参数 / Logging属性怎么管理索引：重建16
17. 使用索引如果程序或者一条SQL中只用到某个表的少量数据，应该尽量使用索引。查看唯一值个数 Select count(*),count(distinct colname) from table_name; 查看数据分布 Select count(*), colname from table_name Group by colname order by 1;
18. 组合索引的考虑前导字段的选择：最常在where子句中出现唯一值最多
19. 优化器没有选择索引的原因缺乏适当索引列条件上使用了运算符条件上发生隐式转换条件对应索引列不在复合索引第一位条件对应索引列选择度不够高统计信息不准确
20. 避免索引滥用索引消耗大量空间索引影响DML 效率不当索引导致错误执行计划无用索引与重复索引
21. DML 操作对索引的影响插入操作导致在适当的块中插入索引项删除行只导致逻辑删除索引项，删除的行所占用的空间难以用于新项，直到删除块中的所有项 PCTused 对索引没有影响因为删除并没有真正的清理数据，只是标识 PCTfree在索引中表示的含义与表不同此时是为了未来insert保留数据，而不在是为了未来更新预留空间索引的Initrans比表的高因为索引记录比表记录小，所以块包含的记录会更多
22. 索引可能降低查询性能若查询数据比例占表数据百分比过大则降低性能影响百分比大小的因素 IO能力索引字段在整行所占的百分比数据有序度（DBA_INDEXES的CLUSTERING_FACTOR 字段）数据块的顺序与索引块顺序的相似度有序度越高，聚簇因子越小，索引代价越小
23. 索引与索引扫描1表连接23生产库审核与优化案例真正读懂SQL执行计划4
24. HASH JOIN散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和；在两个表的数据量差别很大的时候；等值连接。用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。
25. SQL> select city_name,country_name from city,country where city.country_id=country.country_id; 1010000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 114462077 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1010K| 25M| 1003 (2)| 00:00:13 | |* 1 | HASH JOIN | | 1010K| 25M| 1003 (2)| 00:00:13 | | 2 | TABLE ACCESS FULL| COUNTRY | 10 | 120 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| CITY | 1010K| 13M| 996 (1)| 00:00:12 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")
26. SORT MERGE JOINMerge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。这种方式适用在没有索引，并且数据已经排序的情况；非等值连接。用USE_MERGE(table_name1 table_name2)提示来强制使用散列连接。
27. SQL> select city_name,country_name from city,country where city.country_id<country.country_id; 4545000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 662350140 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4545K| 112M| | 6000 (1)| 00:01:13 | | 1 | MERGE JOIN | | 4545K| 112M| | 6000 (1)| 00:01:13 | | 2 | SORT JOIN | | 10 | 120 | | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| COUNTRY | 10 | 120 | | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 1010K| 13M| 46M| 5985 (1)| 00:01:12 | | 5 | TABLE ACCESS FULL| CITY | 1010K| 13M| | 996 (1)| 00:00:12 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(INTERNAL_FUNCTION("CITY"."COUNTRY_ID")<INTERNAL_FUNCTION("COUNTRY "."COUNTRY_ID")) filter(INTERNAL_FUNCTION("CITY"."COUNTRY_ID")<INTERNAL_FUNCTION("COUNTRY "."COUNTRY_ID"))
28. NESTED LOOPS在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表。 Nested loop一般用在被连接的数据子集较小，连接的表中有索引，并且索引选择性较好的时候。使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。
29. SQL> select city_name,country_name from city,country where city.country_id<>country.country_id; 9090000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3145739091 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 9090K| 225M| 9948 (1)| 00:02:00 | | 1 | NESTED LOOPS | | 9090K| 225M| 9948 (1)| 00:02:00 | | 2 | TABLE ACCESS FULL| COUNTRY | 10 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| CITY | 909K| 12M| 994 (1)| 00:00:12 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CITY"."COUNTRY_ID"<>"COUNTRY"."COUNTRY_ID")
30. 索引与索引扫描1表连接23生产库审核与优化案例真正读懂SQL执行计划4
31. 如何查看执行计划正确的执行计划执行顺序通过示例来实践正确阅读执行计划的方法读懂执行计划有什么用？执行计划贯穿Oracle调优始终了解执行计划的真实执行过程，有助于优化更加深入理解Oracle原理读懂执行计划，SQL调优的第一步
32. SQL是声明型语言，她只说我要去哪里，但很少告诉你到底如何去？ RDBMS所要做的是基于算法和现有统计信息计算最佳路径： Access Path访问路径分析：访问数据是用tablescan还是index 对返回的结果集做例如Join的进一步处理，以便返回给客户端 SQL语句的执行最终会落实为Oracle执行步骤的组合，即SQL执行计划。什么是SQL EXECUTION PLAN执行计划？取出磁盘存储加工結果oracle database
33. 1. Explain Plan For SQL 不实际执行SQL诧句，生成的计划未必是真实执行的计划必须要有plan_table 2. SQLPLUS AUTOTRACE 除set autotrace traceonly explain外均实际执行SQL，但仍未必是真实计划必须要有plan_table 3. SQL TRACE 需要启用10046戒者SQL_TRACE 一般用tkprof看的更清楚些，当然10046里本身也有执行计划信息 4. V$SQL和V$SQL_PLAN 可以查询到多个子游标的计划信息了，但是看起来比较费劲 5. Enterprise Manager 可以图形化显示执行计划，但并非所有环境有EM可用 6. 其他第三方工具注意 PL/SQL developer之类工具F5看到的执行计划未必是真实的
34. 查看执行计划的方法:更靠谱的方法SQL> select * from test_objects where object_name='OBJ$'; SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 4z3850hja9dkx, child number 0 ------------------------------------- select * from test_objects where object_name='OBJ$' Plan hash value: 3570092908 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | | 4 |00:00:00.06 | 5716 | |* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 4 | 2 | 4 |00:00:00.06 | 5716 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='OBJ$') 18 rows selected.
35. Id 分配给执行计划中每一个步骤的一个数字，每个步骤（执行计划中的行，或树中的节点）代表行源 (row source)。 Operation 该步骤实施的内部操作名 id=0的operation一般是 SELECT/INSERT/UPDATE/DELETE Statement Name 该步骤操作的表或者索引名 Rows CBO基于统计信息估计该操作将返回的行数 Bytes CBO基于统计信息估计该操作将返回的字节数
36. Cost 在默认启用CPU Costing的环境中 Cost = IO Cost + CPU Cost %CPU 代表CPU Cost占总的Cost的比例， ( Cost – Io Cost) / Cost ，对于Table Access FULL而言一般%CPU 很低 Time CBO评估该操作将要消耗的时间，单位为秒与CBO相关的参数Cost、Rows、Bytes、Time等当使用RBO优化器时全部为NULL Pstart 访问多个分区时的起始分区 Pstop 访问多个分区时的停止分区
37. access谓词多用于使用索引访问的场景。所谓access即是不遍历全量的数据，而利用对应的查询条件或者约束来驱动访问索引。 filter谓词多用于无法使用索引访问的场景，例如步骤3是在对整张表全表扫描的过程中，对于每一条记录做识别看是否符合过滤相关的条件；但是主要filter过滤并不会真的是物理读一个块然后就对里面的记录做过滤，仍会一次物理读取多个块，之后逻辑读这些块并做逻辑过滤。Filter一般没有驱动作用。Predicate Information (identified by operation id): --------------------------------------------------- - access("S"."TIME_ID"="T"."TIME_ID") - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02') - access("S"."CUST_ID"="C"."CUST_ID") - filter("C"."CUST_STATE_PROVINCE"='FL') 10 - filter("CH"."CHANNEL_DESC"='Direct Sales') 13 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
38. 错误的执行顺序说法国内对于执行计划执行顺序有一种错误的说法：最右最上最先执行的原则我们来验证这种说法，按照最右最上原则第一步该执行TABLE ACCESS FULL TIMES,因为它是唯一缩进最大Depth=6的节点
39. 错误的执行顺序说法我们可以通过10046 trace来跟踪Oracle，如果首先读取的是Times表则最右最上原则是正确的，否则显然不正确通过上述验证我们可以知道最右最上最先执行的说法是错误的！ Top RightMost不是指最右最上，这个错误的观念可能来源于对文档的误读
40. 正确的计划树形图解析顺序树形图的解析过程：从顶部开始。在树中向左下移，直至到达左节点(没有子节点的节点)。首先执行此节点。查看此行源的同级行源。接下来执行这些行源。执行子行源后，接着执行父行源。完成此父行源及其子行源后，在树中向上退一级，查看相应父行源的同级行源和父行源。按前述方式执行。在树中不断上移，直至用完所有行源为止。如右图例：执行顺序 3 6 8 7 5 9 4 2 左下移动3节点没有子节点，优先执行3 遍历3的同级别行源4，左下移动，执行6 之后遍历6的同级行源7，执行8 执行7 执行5 执行9 执行4 执行2 执行10123 FULL SCAN CUSTOMERS456 FULL SCAN CHANNELS78 FULL SCAN TIMES9 FULL SCAN SALES3 FULL SCAN CUSTOMERS
41. 读懂Oracle执行计划，示例alter session set statistics_level=ALL; select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname from emp,dept where dept.deptno = emp.deptno and not exists (select * from salgrade where emp.sal between losal and hisal); set linesize 200 pagesize 1400 select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST')); SQL_ID 4xb2say20cqq8, child number 0 ------------------------------------- select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname from emp,dept where dept.deptno = emp.deptno and not exists (select * from salgrade where emp.sal between losal and hisal) Plan hash value: 1175760222 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------|0 | SELECT STATEMENT||1 |0 |00:00:00.01 |59 ||* || FILTER | NESTED LOOPS| || |1 | 1 |0 |00:00:00.01 | 14 |00:00:00.01 |59 | 23 ||3 |TABLE ACCESS FULL | EMP|1 |14 |00:00:00.01 |7 ||4 |TABLE ACCESS BY INDEX ROWID| DEPT|14 |14 |00:00:00.01 |16 ||* 5 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 14 |00:00:00.01 | 2 | |* 6 | TABLE ACCESS FULL | SALGRADE | 12 | 12 |00:00:00.01 | 36 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NULL) - access("DEPT"."DEPTNO"="EMP"."DEPTNO") - filter(("HISAL">=:B1 AND "LOSAL"<=:B2))"A-Rows"对应于相应行源生成的行数。 "Buffers"对应于行源执行的一致读取数。 "Starts"指示处理相应操作的次数。系统获取 EMP 表中每一行的 ENAME、SAL、JOB 和 DEPTNO。此后，系统通过 DEPT 表的唯一索引 (PK_DEPT) 访问该表，以便使用来自上一结果集的 DEPTNO 获得 DNAME。仔细观察统计信息将发现，EMP 表上的 TABLE ACCESS FULL 操作 (ID=3) 启动了一次。但是，ID5 和ID4 操作启动了 14 次；对每个 EMP 行执行一次。在 ID=2 的步骤中，系统获得了所有的 ENAME、SAL、 JOB 和 DNAME。此时，系统必须过滤出其薪金不在薪金等级表中的薪金范围内的雇员。为此，系统针对来自 ID2 的每一行，使用 FULL TABLE SCAN 操作访问 SALGRADE 表，以便检查雇员的薪金是否不在薪金范围内。在本例中，由于在运行时系统检查每个不同的薪金，而 EMP 表中有 12 个不同的薪金，因此此操作只需执行 12 次。
42. Q&A

下载需要 3 香币 [ 香币充值 ]

亲，您也可以通过分享原创文档来获得香币奖励！

服务器/托管费、人工审核、技术维护等都需要很多费用，请您支持香当网的发展

下载PPT

该用户的其他文档

趣味运动会活动方案

SQL审核与优化ppt

该用户的其他文档

相关PPT

相关文档