SQL性能优化策略之索引优化方法

Karen110
• 阅读 1192

「数仓宝贝库」,带你学数据!

导读: 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所示。

SQL性能优化策略之索引优化方法

图1 SQL执行计划

AWR报告中的资源消耗信息如图2所示。

SQL性能优化策略之索引优化方法

图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所示。

SQL性能优化策略之索引优化方法

图3 pat_inpat_order_cost表的字段信息


SQL> select count(*), FY_STATUS
  from his.pat_inpat_order_cost c
 group by FY_STATUS;

fy_status字段列的选择性如图4所示。

SQL性能优化策略之索引优化方法

图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所示。

SQL性能优化策略之索引优化方法

图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"<TO_DATE(TO_CHAR
       (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》,经出版方授权发布。

SQL性能优化策略之索引优化方法

**-----**------**-----**---**** 送书 **-----**--------**-----**-****

欢迎各位大佬点击链接加入群聊【helloworld开发者社区】:https://jq.qq.com/?_wv=1027&k=mBlk6nzX进群交流IT技术热点。

本文转自 https://mp.weixin.qq.com/s/6Wq7zgKlvoxTjw4iC0tqNg,如有侵权,请联系删除。

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Karen110 Karen110
2年前
SQL性能优化策略之索引优化方法
「数仓宝贝库」,带你学数据!导读:SQL优化是优化工作中经常会涉及的问题,由于早期的开发人员往往只关注于SQL功能的实现,而忽略了性能。特别是复杂的SQL,上线之后很少修改,一旦出现问题,即使是当初的开发人员自己也很难理清其中的业务逻辑,需要花费大量的时间去理解代码之间的关系,最终可能还是感觉无从下手。因此开发人员前期应做好代码注释,避免编写过于复杂的SQL
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这