SQL 优化是优化工作中经常会涉及的问题, 由于早期的开发人员往往只关注于 SQL 功能的实现, 而忽略了性能. 特别是复杂的 SQL, 上线之后很少修改, 一旦出现问题, 即使是当初的开发人员自己也很难理清其中的业务逻辑, 需要花费大量的时间去理解代码之间的关系, 最终可能还是感觉无从下手. 因此开发人员前期应做好代码注释, 避免编写过于复杂的 SQL 语句. 本文为大家介绍一些生产环境中真实的常用索引优化方法.
遇到问题 SQL 时, 大家可以根据各自的习惯使用不同的工具 (PL/SQL,TOAD 等) 对 SQL 进行格式化, 我们需要重点关注的是 FROM 后面的表, 以及包含 WHERE 语句的条件, 然后通过 awrsqrpt 或 dbms_xplan 获取 SQL 的详细执行计划和资源消耗信息, 业务案例中的 SQL 语句如下:
- SQL> select sum(cggzl) cggzl, sum(qbgzl) qbgzl
- from (select case
- when zlxm_mc like '%2ê3?3£1??ì2é%' then
- gzl
- else
- 0
- end cggzl,
- case
- when zlxm_mc like '%?3±í?÷1ù%' then
- gzl
- else
- 0
- end qbgzl
- from dictmanage.dict_zl_pro b,
- his.pat_inpat_order_info c,
- pat_inpat_order_cost d
- where d.sfxm_id = b.zlxm_id
- and c.yzjl_id = d.dyzy_yzjl_id
- and zlxm_mc like '%2???%'
- and c.yz_zxrq >= to_date(sysdate)
- and c.yz_zxrq < to_date(sysdate + 1)
- and d.fy_status in ('1', '2')
- and sfxm_je > 0
- and c.yz_zfrq is null
- and c.zylsh = :in_zylsh)
SQL 的详细执行计划如图 1 所示.
图 1 SQL 执行计划
AWR 报告中的资源消耗信息如图 2 所示.
图 2 AWR 报告中的资源消耗信息
上述代码所示的业务 SQL 语句通过三张表进行关联, 最终返回的行数为个位数, 从执行计划中我们可以看出, Id=0,CBO 计算总的 COST 为 123K, 其中绝大部分的 COST 是由 Id=10 的表 pat_inpat_order_cost 全表扫描所产生的. 此时, 我们需要重点关注 pat_inpat_order_cost 与其他两张表格的关联情况, where 条件中, pat_inpat_order_cost 的 sfxm_id 和 dyzy_yzjl_id 除了与其他两张表的字段相关联之外, 只有 fy_status 一个过滤条件, 下面我们就来看下该列的选择性, 代码如下:
- SQL> select /*+ NO_MERGE LEADING(a b) */
- b.owner,
- b.table_name,
- a.column_name,
- b.num_rows,
- a.num_distinct Cardinality,
- ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity
- from dba_tab_col_statistics a, dba_tables b
- where a.owner = b.owner
- and a.table_name = b.table_name
- and a.owner = upper('his')
- and a.table_name = upper('pat_inpat_order_cost')
- and a.column_name = upper('fy_status');
pat_inpat_order_cost 表的字段信息如图 3 所示.
图 3 pat_inpat_order_cost 表的字段信息
- SQL> select count(*), FY_STATUS
- from his.pat_inpat_order_cost c
- group by FY_STATUS;
fy_status 字段列的选择性如图 4 所示.
图 4 fy_status 字段列的选择性
由图 4 可知, fy_status 的选择性并不好, 而且存在严重倾斜, 语句中的固定写法 d.fy_status in ('1', '2')几乎包含了所有记录, 因此其并不是一个很好的过滤条件. where 条件中的大部分过滤条件均来自于 C 表 pat_inpat_order_info, 而且 C 表与 D 表 pat_inpat_order_cost 的 sfxm_id 字段相关联.
整个 SQL 语句最终返回的行数为个位数, C 表通过 YZ_ZXRQ_IDX 索引范围扫描再回表进行过滤, 获取绑定变量值, 之后再进一步确认 C 表返回的行数, 代码如下:
- SQL> select sql_Id, name, datatype_string, last_captured, value_string
- from v$sql_bind_capture
- where sql_id = '18rwad2bgcxfa';
SQL 绑定变量值获取情况如图 5 所示.
图 5 SQL 绑定变量值获取情况
- SQL> select count(*)
- from his.pat_inpat_order_info c
- where c.yz_zxrq >= to_date(sysdate)
- and c.yz_zxrq < to_date(sysdate + 1)
- and c.yz_zfrq is null
- and c.zylsh = 72706;
带入绑定变量我们可以发现, 这个查询返回的行数都保持在个位数, 如果 C 表和 D 表采用嵌套连接的方式, C 表能作为驱动表与 D 表 pat_inpat_order_cost 相关联, 被驱动表只需要在关联列上创建索引, 即可大幅提升整个查询的效率, 做法其实很简单, 只需要在 sfxm_id 字段上创建索引即可, 命令如下:
- SQL> create index IDX_SFXM_ID on PAT_INPAT_ORDER_COST (SFXM_ID);
- Plan hash value: 408580053
- ------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 12 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 68 | | |
- |* 2 | FILTER | | | | | |
- | 3 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 |
- | 4 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 |
- | 5 | NESTED LOOPS | | 1 | 39 | 11 (0)| 00:00:01 |
- |* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID
- | PAT_INPAT_ORDER_INFO | 1 | 21 | 5 (0)| 00:00:01 |
- |* 7 | INDEX RANGE SCAN | YZ_ZXRQ_IDX | 4 | | 3 (0)| 00:00:01 |
- |* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID
- | PAT_INPAT_ORDER_COST | 6 | 108 | 6 (0)| 00:00:01 |
- |* 9 | INDEX RANGE SCAN | IDX_DYZY_YZJL_ID | 6 | | 2 (0)| 00:00:01 |
- |* 10 | INDEX UNIQUE SCAN | DICT_ZL_PRO_PK | 1 | | 0 (0)| |
- |* 11 | TABLE ACCESS BY INDEX ROWID | DICT_ZL_PRO | 1 | 29 | 1 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(TO_DATE(TO_CHAR(SYSDATE@!+1))>TO_DATE(TO_CHAR(SYSDATE@!)))
- 6 - filter(("C"."ZYLSH"=TO_NUMBER(:IN_ZYLSH) AND "C"."YZ_ZFRQ" IS NULL))
- 7 - access("C"."YZ_ZXRQ">=TO_DATE(TO_CHAR(SYSDATE@!)) AND "C"."YZ_ZXRQ"
- (SYSDATE@!+1)))
- 8 - filter(("SFXM_JE">0 AND INTERNAL_FUNCTION("D"."FY_STATUS")))
- 9 - access("C"."YZJL_ID"="D"."DYZY_YZJL_ID")
- 10 - access("D"."SFXM_ID"="B"."ZLXM_ID")
- 11 - filter("ZLXM_MC" LIKE '% 部位 %')
创建索引之后, 整个执行计划按照我们设想的方式进行, SQL 执行时间也从原来的 24 分钟缩短到 1 秒, 速度提升了上千倍.
上述案例介绍了一种最简单的 SQL 优化方式, 在大多数情况下, 我们很难让开发商修改应用, 因此索引的优化在 SQL 优化工作中显得尤为重要.
本文摘编于《DBA 攻坚指南: 左手 Oracle, 右手 MySQL》, 经出版方授权发布.
来源: http://database.51cto.com/art/202107/672654.htm