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

时间：2022-04-10 13:21:56

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"<SYSDATE@!) 
  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 /*+ NO_MERGE LEADING(a b) */ 
  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 /*+ NO_MERGE LEADING(a b) */ 
  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 /*+ index(LIS_REQUISITION_INFO dbo.idx_LIS_REQUISITION_INFO_com1) */ 
  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"<SYSDATE@!) 
  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能够更好地解决问题，但我们往往很难让开发商去做出修改，因此索引优化变得尤为重要。当表上存在多个过滤条件时，字段在表中的选择性只能作为参考而不能成为最终依据，在实际工作中，我们应该根据业务特点对多个字段进行组合分析。在很多情况下，单个字段的选择性比较低，多个字段的选择性会成倍增长。

原文链接：https://mp.weixin.qq.com/s/7AnemZ5RilCfrjNf58zZKg

标签：

相关文章