我的编程空间,编程开发者的网络收藏夹
学习永远不晚

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

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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

本文转载自微信公众号「数仓宝贝库」,作者叶桦 等 。转载本文请联系数仓宝贝库公众号。

案例:一条很简单的SQL语句明明选择了索引扫描,但效率还是很低,SQL语句比较简单,是对单张表进行查询,示例代码如下:

  1. SQL> set autot trace 
  2.  
  3. SQL> SELECT REQUISITION_ID PARAM1, '1' PARAM2,  '1' PARAM3 
  4.  
  5.   2    FROM dbo.LIS_REQUISITION_INFO 
  6.  
  7.   3   WHERE PRINT_TIME >= 
  8.  
  9.   4         TO_DATE('2019-01-01 00:00:00''YYYY-MM-DD HH24:MI:SS'
  10.  
  11.   5     AND PRINT_TIME < SYSDATE 
  12.  
  13.   6     and length(requisition_id) = 12 
  14.  
  15.   7     AND (TAT1_STATE = '' OR TAT1_STATE IS NULL
  16.  
  17.   8     AND ROWNUM < 800; 
  18.  
  19.  
  20.  
  21. Execution Plan 
  22.  
  23. ---------------------------------------------------------- 
  24.  
  25. Plan hash value: 1151136383 
  26.  
  27. ------------------------------------------------------------------------------------------ 
  28.  
  29. | Id  | Operation            |Name                |Rows  | Bytes | Cost (%CPU)| Time     | 
  30.  
  31. ------------------------------------------------------------------------------------------ 
  32.  
  33. |   0 | SELECT STATEMENT     |                    |  799 | 18377 |   160K  (1)| 00:32:03 | 
  34.  
  35. |*  1 |  COUNT STOPKEY       |                    |      |       |            |          | 
  36.  
  37. |*  2 |   FILTER             |                    |      |       |            |          | 
  38.  
  39. |*  3 |    TABLE ACCESS BY  
  40.  
  41.                 INDEX ROWID  |LIS_REQUISITION_INFO|  800 | 18400 |  160K   (1)| 00:32:03 | 
  42.  
  43. |*  4 |     INDEX RANGE SCAN |I_PRINT_TIME        |      |       |  3799   (1)| 00:00:46 | 
  44.  
  45. ------------------------------------------------------------------------------------------ 
  46.  
  47. Predicate Information (identified by operation id): 
  48.  
  49. --------------------------------------------------- 
  50.  
  51.    1 - filter(ROWNUM<800) 
  52.  
  53.    2 - filter(SYSDATE@!>TO_DATE(' 2019-01-01 00:00:00''syyyy-mm-dd hh24:mi:ss')) 
  54.  
  55.    3 - filter("TAT1_STATE" IS NULL AND LENGTH("REQUISITION_ID")=12) 
  56.  
  57.    4 - access("PRINT_TIME">=TO_DATE(' 2019-01-01 00:00:00''syyyy-mm-dd hh24:mi:ss'AND 
  58.  
  59.               "PRINT_TIME"
  60.  
  61. Statistics 
  62.  
  63. ---------------------------------------------------------- 
  64.  
  65.           1  recursive calls 
  66.  
  67.           0  db block gets 
  68.  
  69.     1204017  consistent gets 
  70.  
  71.      161836  physical reads 
  72.  
  73.       19984  redo size 
  74.  
  75.         761  bytes sent via SQL*Net to client 
  76.  
  77.         520  bytes received via SQL*Net from client 
  78.  
  79.           2  SQL*Net roundtrips to/from client 
  80.  
  81.           0  sorts (memory) 
  82.  
  83.           0  sorts (disk) 
  84.  
  85.           3  rows processed 

从上述代码的执行计划可以看出,Id=4的dbo.LIS_REQUISITION_INFO表选择的索引是I_PRINT_TIME,PRINT_TIME为时间字段,逻辑读高达1204017,下面我们看下该列的选择性,命令如下:

  1. SQL> select  
  2.  
  3.  b.owner, 
  4.  
  5.  b.table_name, 
  6.  
  7.  a.column_name, 
  8.  
  9.  b.num_rows, 
  10.  
  11.  a.num_distinct Cardinality, 
  12.  
  13.  ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity 
  14.  
  15.   from dba_tab_col_statistics a, dba_tables b 
  16.  
  17.  where a.owner = b.owner 
  18.  
  19.    and a.table_name = b.table_name 
  20.  
  21.    and a.owner = 'DBO' 
  22.  
  23.    and a.table_name = 'LIS_REQUISITION_INFO' 
  24.  
  25.    and a.column_name = 'PRINT_TIME'
  26.  
  27.  
  28.  
  29. OWNER   TABLE_NAME             COLUMN_NAME  NUM_ROWS  CARDINALITY  SELECTIVITY 
  30.  
  31. ------- ---------------------  -----------  --------  -----------  ----------- 
  32.  
  33. DBO     LIS_REQUISITION_INFO   PRINT_TIME   6933600   2226944      32.1 

LIS_REQUISITION_INFO的数据量为6 933 600条,PRINT_TIME列的不同值为2 226 944个,选择性高达32.1%,PRINT_TIME给定了条件时间范围,目前从执行计划来看,

LIS_REQUISITION_INFO表的访问先通过I_PRINT_TIME索引进行范围扫描,符合条件的记录回表之后再过滤,产生了大量的单块读。虽然PRINT_TIME的选择性很高,且符合索引扫描的要求,但因为其给定的条件范围太大,导致该字段并不是一个很好的索引选择。

除了PRINT_TIME,该SQL还有requisition_id、TAT1_STATE和ROWNUM,下面就来看下它们的选择性,命令如下:

  1. SQL> select  
  2.  
  3.  b.owner, 
  4.  
  5.  b.table_name, 
  6.  
  7.  a.column_name, 
  8.  
  9.  b.num_rows, 
  10.  
  11.  a.num_distinct Cardinality, 
  12.  
  13.  ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity 
  14.  
  15.   from dba_tab_col_statistics a, dba_tables b 
  16.  
  17.  where a.owner = b.owner 
  18.  
  19.    and a.table_name = b.table_name 
  20.  
  21.    and a.owner = 'DBO' 
  22.  
  23.    and a.table_name = 'LIS_REQUISITION_INFO' 
  24.  
  25.    and a.column_name in ('PRINT_TIME''REQUISITION_ID''TAT1_STATE'); 
  26.  
  27. OWNER   TABLE_NAME            COLUMN_NAME       NUM_ROWS CARDINALITY SELECTIVITY 
  28.  
  29. ------- --------------------- -------------------------- ----------- ----------- 
  30.  
  31. DBO     LIS_REQUISITION_INFO  TAT1_STATE         6933600           2           0 
  32.  
  33. DBO     LIS_REQUISITION_INFO  REQUISITION_ID     6933600     6933600         100 
  34.  
  35. DBO     LIS_REQUISITION_INFO  PRINT_TIME         6933600     2226944        32.1 
  36.  
  37.  
  38.  
  39. SQL> select count(*), 
  40.  
  41.   from dbo.LIS_REQUISITION_INFO 
  42.  
  43.  where length(requisition_id) = 12 
  44.  
  45. COUNT(*) 
  46.  
  47. ------- 
  48.  
  49. 6968919 
  50.  
  51.  
  52.  
  53. SQL> select TAT1_STATE, count(*) 
  54.  
  55.   from dbo.LIS_REQUISITION_INFO 
  56.  
  57.  group by TAT1_STATE; 
  58.  
  59. TAT1_STAT   COUNT(*) 
  60.  
  61. ----------  -------- 
  62.  
  63.             1242217 
  64.  
  65. 1           5355366 
  66.  
  67. 2            371401 

REQUISITION_ID为主键的选择性很高,但几乎所有的记录值都符合length (requisition_id) = 12,TAT1_STATE的数据分布存在倾斜,条件中的TAT1_STATE = '' OR TAT1_STATE IS NULL属于第一种情况,占总数据量的1/3。该字段为固定取值(TAT1_STATE = '' OR TAT1_STATE IS NULL)。如果 PRINT_TIME和TAT1_STATE组合创建联合索引,那么效果又将如何呢?命令如下:

  1. SQL> create index dbo.idx_LIS_REQUISITION_INFO_com1 on dbo.LIS_REQUISITION_INFO 
  2.  
  3.     (PRINT_TIME,TAT1_STATE) online; 
  4.  
  5.  
  6.  
  7. SQL> SELECT  
  8.  
  9.  REQUISITION_ID PARAM1, '1' PARAM2,  '1' PARAM3 
  10.  
  11.   FROM dbo.LIS_REQUISITION_INFO 
  12.  
  13.  WHERE PRINT_TIME >= 
  14.  
  15.        TO_DATE('2019-01-01 00:00:00''YYYY-MM-DD HH24:MI:SS'
  16.  
  17.    AND PRINT_TIME < SYSDATE 
  18.  
  19.    and length(requisition_id) = 12 
  20.  
  21.    AND (TAT1_STATE = '' OR TAT1_STATE IS NULL
  22.  
  23.    AND ROWNUM < 800; 
  24.  
  25. Execution Plan 
  26.  
  27. ---------------------------------------------------------- 
  28.  
  29. Plan hash value: 1406522876 
  30.  
  31. ----------------------------------------------------------------------------------------------------- 
  32.  
  33. | Id  | Operation            | Name                        |Starts|E-Rows|A-Rows|   A-Time  |Buffers| 
  34.  
  35. ----------------------------------------------------------------------------------------------------- 
  36.  
  37. |   0 | SELECT STATEMENT     |                             |    1 |      |    6 |00:00:00.27|  8146 | 
  38.  
  39. |*  1 |  COUNT STOPKEY       |                             |    1 |      |    6 |00:00:00.27|  8146 | 
  40.  
  41. |*  2 |   FILTER             |                             |    1 |      |    6 |00:00:00.27|  8146 | 
  42.  
  43. |*  3 |    TABLE ACCESS BY  
  44.  
  45.                INDEX ROWID   |LIS_REQUISITION_INFO         |    1 |  144 |    6 |00:00:00.27|  8146 | 
  46.  
  47. |*  4 |     INDEX RANGE SCAN |IDX_LIS_REQUISITION_INFO_COM1|    1 |14398 |    8 |00:00:00.27|  8140 | 
  48.  
  49. ----------------------------------------------------------------------------------------------------- 
  50.  
  51. Predicate Information (identified by operation id): 
  52.  
  53. --------------------------------------------------- 
  54.  
  55.    1 - filter(ROWNUM<800) 
  56.  
  57.    2 - filter(SYSDATE@!>TO_DATE(' 2019-01-01 00:00:00''syyyy-mm-dd hh24:mi:ss')) 
  58.  
  59.    3 - filter(LENGTH("REQUISITION_ID")=12) 
  60.  
  61.    4 - access("PRINT_TIME">=TO_DATE(' 2019-01-01 00:00:00''syyyy-mm-dd hh24:mi:ss'AND "TAT1_STATE" 
  62.  
  63.               IS NULL AND "PRINT_TIME"
  64.  
  65.        filter("TAT1_STATE" IS NULL
  66.  
  67. Statistics 
  68.  
  69. ---------------------------------------------------------- 
  70.  
  71.           1  recursive calls 
  72.  
  73.           0  db block gets 
  74.  
  75.        8008  consistent gets 
  76.  
  77.        8014  physical reads 
  78.  
  79.           0  redo size 
  80.  
  81.         471  bytes sent via SQL*Net to client 
  82.  
  83.         508  bytes received via SQL*Net from client 
  84.  
  85.           1  SQL*Net roundtrips to/from client 
  86.  
  87.           0  sorts (memory) 
  88.  
  89.           0  sorts (disk) 
  90.  
  91.           0  rows processed 

创建索引之后,SQL性能有了明显的提升,逻辑读从原来的1204017降到8008,执行时间也从原来的32分钟降至27秒。

上述案例介绍了简单的复合索引优化,很多情况下,虽然改写SQL能够更好地解决问题,但我们往往很难让开发商去做出修改,因此索引优化变得尤为重要。当表上存在多个过滤条件时,字段在表中的选择性只能作为参考而不能成为最终依据,在实际工作中,我们应该根据业务特点对多个字段进行组合分析。在很多情况下,单个字段的选择性比较低,多个字段的选择性会成倍增长。

 

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

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

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

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

LIS_REQUISITION_INFO表的访问先通过I_PRINT_TIME索引进行范围扫描,符合条件的记录回表之后再过滤,产生了大量的单块读。
SQL策略优化2024-12-03

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

SQL优化是优化工作中经常会涉及的问题,由于早期的开发人员往往只关注于SQL功能的实现,而忽略了性能。特别是复杂的SQL,上线之后很少修改,一旦出现问题,即使是当初的开发人员自己也很难理清其中的业务逻辑,需要花费大量的时间去理解代码之间的
SQL索引性能2024-12-03

MySQL高性能索引策略和查询性能优化

前缀索引和索引选择性有时候需要索引很长的字符,这会让索引变得大且慢。一个策略是模拟哈希索引。通常可以索引开始的部分字符,这样可以大大解约索引空间,提高索引效率。但这样会降低索引的选择性。索引的选择性:不重复的索引值(也成为基数)和数据表的记录总数比值。索引的选
MySQL高性能索引策略和查询性能优化
2017-05-17

MongoDB索引策略与优化的方法是什么

MongoDB的索引策略和优化方法包括以下几个方面:选择合适的字段建立索引:根据查询需求和数据访问模式选择需要建立索引的字段,可以是单个字段或者组合字段。避免创建过多的索引:过多的索引会增加写入操作的开销,并占用额外的存储空间,同时也会增加
MongoDB索引策略与优化的方法是什么
2024-05-07

索引在Oracle中优化聚合查询的策略

在Oracle中优化聚合查询的一个有效策略是通过创建适当的索引来加速查询性能。以下是一些与索引优化聚合查询相关的策略:在聚合函数的列上创建索引:如果聚合函数的列没有索引,Oracle在执行聚合查询时可能需要进行全表扫描。为了加速查询性能,可
索引在Oracle中优化聚合查询的策略
2024-08-15

Mysql性能优化之索引下推

索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给
2022-05-19

PHP与MySQL索引的查询语句优化和索引返回的性能优化策略及其对性能的影响

数据库是现代应用开发中不可或缺的一部分,而针对数据库的查询语句优化和索引返回的性能优化是开发人员应该重点关注的问题。索引是一种用于提高数据库查询效率的重要数据结构,它通过在表中创建特定字段的索引来加速数据的查找和排序过程。本文将重点讨论PH
2023-10-21

SQL语句性能优化的策略有哪些

这篇文章主要介绍“SQL语句性能优化的策略有哪些”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“SQL语句性能优化的策略有哪些”文章能帮助大家解决问题。52 条 SQL 语句性能优化策略:1、对查询进
2023-06-28

优化网站性能的有效方法和策略

随着互联网的普及和发展,网站已经成为企业宣传和营销的重要渠道之一。然而,随着网站访问量的不断增加,网站性能的问题也逐渐暴露出来。网站打开速度慢、页面加载时间长等问题不仅影响用户体验,也会导致流量流失和转化率下降。为了提升网站性能,吸引用户,
优化网站性能的有效方法和策略
2024-02-02

Vite性能优化之分包策略的实现

本文主要介绍了Vite性能优化之分包策略的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
2023-05-19

MySQL红黑树与索引分裂合并的优化策略

MySQL中的红黑树和索引分裂合并是两种不同的优化策略,它们分别用于解决不同的问题。下面我将分别介绍这两种策略及其优化方法:红黑树优化策略:红黑树是一种自平衡的二叉搜索树,它能够在数据的插入和删除过程中保持树的平衡状态,从而保证查询的高效性
MySQL红黑树与索引分裂合并的优化策略
2024-10-07

PHP接口性能优化之并发处理策略(PHP接口中并发处理的性能优化方法)

PHP接口性能优化之并发处理策略PHP接口中可通过并发处理提高性能,采用非阻塞异步处理、多进程、多线程、协程、消息队列等策略。优化网络请求、使用负载均衡,并进行性能监控和分析,可进一步提升表现。实施这些策略能显著降低响应时间、提高吞吐量。
PHP接口性能优化之并发处理策略(PHP接口中并发处理的性能优化方法)
2024-04-02

PHP接口性能优化之网络协议优化(PHP接口中网络协议的性能优化策略)

本文重点介绍PHP接口中网络协议性能优化策略,通过选择高效协议(HTTP/2、QUIC)、优化协议参数(TCP缓冲区、MTU、Keep-Alive)、使用GZIP压缩、减少DNS查找、减少重定向、优化证书验证、利用CDN等方法,以及异步请求、监控网络性能、优化服务器端响应时间等技巧,帮助提升接口性能。
PHP接口性能优化之网络协议优化(PHP接口中网络协议的性能优化策略)
2024-04-02

MongoDB和SQL语句的性能比较及优化策略?

MongoDB和SQL语句的性能比较及优化策略随着大数据时代的来临,数据存储和处理变得尤为重要。在数据库领域中,MongoDB和SQL是两种常见的解决方案。不同的数据库在性能方面存在一定的差异,因此优化查询语句是提高系统性能的关键。本文将比
MongoDB和SQL语句的性能比较及优化策略?
2023-12-18

PHP与MySQL索引的数据读取和查询优化的性能优化策略及其对性能的影响

摘要:在PHP与MySQL开发中,高效的数据读取和查询优化对于提升应用程序的性能至关重要。本文将介绍一些性能优化策略,并通过具体的代码示例来说明其对性能的影响。索引的基本概念索引是一种数据结构,用于加速数据库的数据检索。在MySQL中,常见
2023-10-21

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录