组合索引优化一条SQL

最新推荐文章于 2022-09-16 11:10:06 发布

wadekobe9

最新推荐文章于 2022-09-16 11:10:06 发布

阅读量964

点赞数

分类专栏： oracle SQL优化文章标签：优化 access statistics join table filter

本文链接：https://blog.csdn.net/wadekobe9/article/details/7711217

版权

oracle SQL优化专栏收录该内容

15 篇文章 0 订阅

订阅专栏

原始SQL,出来的数据为零行，这不是重点，统计信息没有过期，这里不考虑统计信息的问题
分别看下设计到表的行数
select count(*) from t_ho_room_price --3254605
select count(*) from t_ho_order_info --2034996
select count(*) from t_ho_order_statistics --2034996
Select count(*) from t_hp_hotel_belong --6
select count(*) from t_hp_hotel --18191
select count(*) from t_sd_province --41
select count(*) from t_hd_hotel_grade_type --12
select count(*) from t_hp_chkpymt_rule --15581
关注点在大表身上
SQL,运行1分28秒,出来的结果为0行（这个无所谓，月底结束后就没了），0行跑了1分28秒，先看看执行计划再说

SELECT   p.province_name, h.hotel_id, h.hotel_name, g.hotel_grade_type_desc,
         SUM (op.rmdy_stay) AS rmdy_all,
         SUM (op.comm_total_confirm) AS comm_total_all,
         SUM (CASE
                 WHEN o.order_flow_id BETWEEN 700 AND 720
                    THEN op.rmdy_confirm
                 ELSE 0
              END
             ) AS rmdy,
         SUM
            (CASE
                WHEN o.order_flow_id BETWEEN 700 AND 720
                   THEN op.comm_total_confirm
                ELSE 0
             END
            ) AS comm_total
    FROM t_ho_room_price op 
         INNER JOIN t_ho_order_info o ON o.order_id = op.order_id
         INNER JOIN t_hp_hotel_belong hb ON hb.hotel_belong = o.hotel_belong
         INNER JOIN t_hp_hotel h ON h.hotel_id = o.hotel_id
         INNER JOIN t_sd_province p ON p.province_id = h.province_id
         INNER JOIN t_hd_hotel_grade_type g ON g.hotel_grade_type_id = h.hotel_grade_type_id
         INNER JOIN t_hp_chkpymt_rule r ON r.hotel_id = h.hotel_id AND r.rmdy_sep_date = 0
         INNER JOIN t_ho_order_statistics s ON s.order_id = op.order_id
         AND s.check_month <= '2012-03'  AND s.is_issue_hotelbill = 0
   WHERE o.order_status_id <= 700 AND hb.finance_type = 1 AND h.city_id = 110101
   GROUP BY p.province_name, h.hotel_id, h.hotel_name, g.hotel_grade_type_desc;
--------------------------------------------------------------------------------------------------
SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2171370484

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       | 10180 |  1411K|       | 41412   (6)| 00:08:17 |
|   1 |  HASH GROUP BY                      |                       | 10180 |  1411K|  3096K| 41412   (6)| 00:08:17 |
|*  2 |   HASH JOIN                         |                       | 10180 |  1411K|       | 41085   (6)| 00:08:14 |
|*  3 |    TABLE ACCESS FULL                | T_HP_CHKPYMT_RULE     | 14018 |   109K|       |    58   (6)| 00:00:01 
|*  4 |    HASH JOIN                        |                       | 10180 |  1332K|       | 41025   (6)| 00:08:13 |
|   5 |     TABLE ACCESS FULL               | T_HD_HOTEL_GRADE_TYPE |    12 |   192 |       |     3   (0)| 00:00:01 |
|*  6 |     HASH JOIN                       |                       | 10180 |  1173K|       | 41022   (6)| 00:08:13 |
|   7 |      TABLE ACCESS FULL              | T_SD_PROVINCE         |    41 |   492 |       |     3   (0)| 00:00:01 |
|*  8 |      HASH JOIN                      |                       | 10198 |  1055K|  2984K| 41018   (6)| 00:08:13 |
|*  9 |       HASH JOIN                     |                       | 30194 |  2624K|  1568K| 36327   (6)| 00:07:16 |
|* 10 |        HASH JOIN                    |                       | 18595 |  1343K|       | 17453   (6)| 00:03:30 |
|* 11 |         TABLE ACCESS FULL           | T_HP_HOTEL_BELONG     |     3 |    18 |       |     3   (0)| 00:00:01 |
|* 12 |         HASH JOIN                   |                       | 30992 |  2058K|       | 17448   (6)| 00:03:30 |
|  13 |          TABLE ACCESS BY INDEX ROWID| T_HP_HOTEL            |   182 |  8554 |       |    53   (0)| 00:00:01 |
|* 14 |           INDEX RANGE SCAN          | HBASEINFO_COUNTRY_FK  |   182 |       |       |     1   (0)| 00:00:01 |
|* 15 |         TABLE ACCESS FULL          | T_HO_ORDER_INFO      |  1067K|    21M|       | 17358   (5)| 00:03:29 |
|  16 |        TABLE ACCESS FULL            | T_HO_ROOM_PRICE      |  3229K|    46M|       | 14439   (7)| 00:02:
|* 17 |       TABLE ACCESS BY INDEX ROWID   | T_HO_ORDER_STATISTICS |   671K|    10M|       |  3573   (3)|
|* 18 |        INDEX RANGE SCAN             | IND_IS_ISSUE          |   673K|       |       |   468   (4)| 00:00:06 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("R"."HOTEL_ID"="H"."HOTEL_ID")
   3 - filter("R"."RMDY_SEP_DATE"=0)
   4 - access("G"."HOTEL_GRADE_TYPE_ID"="H"."HOTEL_GRADE_TYPE_ID")
   6 - access("P"."PROVINCE_ID"="H"."PROVINCE_ID")
   8 - access("S"."ORDER_ID"="OP"."ORDER_ID")
   9 - access("OP"."ORDER_ID"="O"."ORDER_ID")
  10 - access("HB"."HOTEL_BELONG"="O"."HOTEL_BELONG")
  11 - filter("HB"."FINANCE_TYPE"=1)
  12 - access("H"."HOTEL_ID"="O"."HOTEL_ID")
  14 - access("H"."CITY_ID"=110101)
  15 - filter("O"."ORDER_STATUS_ID"<=700)
  17 - filter("S"."CHECK_MONTH"<='2012-03')
  18 - access("S"."IS_ISSUE_HOTELBILL"=0)

已选择42行。
 

统计信息
----------------------------------------------------------
       1739  recursive calls
         32  db block gets
       2253  consistent gets
         95  physical reads
          0  redo size
       3862  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         24  sorts (memory)
          0  sorts (disk)
         42  rows processed

观察逻辑读，这里是2253，不高（更为准确的算法是考虑出来的行数除以总的逻辑读，每行的逻辑读在15以内都是Ok的）
然后观察执行计划，大表都在全表扫描，虽然都是在通过hash，所以只扫一次，但是仍然不好。

开始优化：
我的第一想法是用小表去关连大表，这样尽可能的过滤掉大表的数据，并且根据等值条件通过走索引的方式
访问大表，要根据等值条件走索引，要嵌套循环才能做到，于是我通过加hints的方法做了如下几种尝试
/*+ use_nl(hb,o) */
/*+ use_nl(h,o) */
/*+ use_nl(s,op) */
/*+ use_nl(s,op) use_nl(h,o) */

结果证明非常不理想，2分钟都没有跑出结果，想了想，大表做驱动表，小表循环次数太多，因为大表上面没过滤条件
用小做驱动表，光只是对大表多进行几次index fuall scan也没多大提高，这里就不贴执行计划了，反正是2分钟内都
没跑出来。

大表上没有好的过滤条件，小表连大表的方式也行不通。后面在网上问了下落落，他给出了建组合索引的建议
命令如下：
--------------------------------------------------------------------------------------------------
Create index idx_T_SD_PROVINCE on T_SD_PROVINCE(Province_Name,PROVINCE_ID) nologging;
Create index idx_T_HD_HOTEL_GRADE_TYPE on T_HD_HOTEL_GRADE_TYPE(Hotel_Grade_Type_Desc,Hotel_Grade_Type_Id) nologging;
Create index idx_T_HO_ROOM_PRICE on T_HO_ROOM_PRICE(Rmdy_Stay,Rmdy_Confirm,Comm_Total_Confirm,ORDER_ID) nologging ;
Create index idx_T_HO_ORDER_STATISTICS on T_HO_ORDER_STATISTICS(IS_ISSUE_HOTELBILL,CHECK_MONTH,Order_Id) nologging;
create index idx_T_HO_ORDER_INFO on T_HO_ORDER_INFO(ORDER_STATUS_ID,HOTEL_ID,ORDER_ID) nologging;
Create Index idx_CHKPYMT On T_HP_CHKPYMT_RULE(RMDY_SEP_DATE,Hotel_Id) nologging ;
--------------------------------------------------------------------------------------------------

索引连接完成后，30秒左右出结果了，执行计划如下：

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3650795862

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           | 10180 |  1411K|       | 26899   (6)| 00:05:23 |
|   1 |  HASH GROUP BY                      |                           | 10180 |  1411K|  3096K| 26899   (6)| 00:05:23 |
|*  2 |   HASH JOIN                         |                           | 10180 |  1411K|       | 26571   (6)| 00:05:19 |
|*  3 |    INDEX FAST FULL SCAN             | IDX_CHKPYMT               | 14018 |   109K|       |    11  (10)| 00:00:01 |
|*  4 |    HASH JOIN                        |                           | 10180 |  1332K|       | 26558   (6)| 00:05:19 |
|   5 |     INDEX FULL SCAN                 | IDX_T_HD_HOTEL_GRADE_TYPE |    12 |   192 |       |     1   (0)| 00:00:
|*  6 |     HASH JOIN                       |                           | 10180 |  1173K|       | 26557   (6)| 00:05:19 |
|   7 |      INDEX FULL SCAN                | IDX_T_SD_PROVINCE         |    41 |   492 |       |     1   (0)| 00:00:01 |
|*  8 |      HASH JOIN                      |                           | 10198 |  1055K|  2984K| 26555   (6)| 00:05:19 |
|*  9 |       HASH JOIN                     |                           | 30194 |  2624K|  1568K| 24502   (6)| 00:04:55 |
|* 10 |        HASH JOIN                    |                           | 18595 |  1343K|       | 17453   (6)| 00:03:30 |
|* 11 |         TABLE ACCESS FULL           | T_HP_HOTEL_BELONG         |     3 |    18 |       |     3   (0)| 00:00:01 |
|* 12 |         HASH JOIN                   |                           | 30992 |  2058K|       | 17448   (6)| 00:03:30 |
|  13 |          TABLE ACCESS BY INDEX ROWID| T_HP_HOTEL                |   182 |  8554 |       |    53   (0)| 00:00:01 |
|* 14 |           INDEX RANGE SCAN          | HBASEINFO_COUNTRY_FK      |   182 |       |       |     1   (0)| 00:00:01 |
|* 15 |         TABLE ACCESS FULL          | T_HO_ORDER_INFO          |  1067K|    21M|       | 17358   (5)| 00:03:29 |
|  16 |        INDEX FAST FULL SCAN         | IDX_T_HO_ROOM_PRICE      |  3229K|    46M|       |  2614   (7)| 00:00
|* 17 |       INDEX RANGE SCAN              | IDX_T_HO_ORDER_STATISTICS |   671K|    10M|       |   935   (3)| 00:
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("R"."HOTEL_ID"="H"."HOTEL_ID")
   3 - filter("R"."RMDY_SEP_DATE"=0)
   4 - access("G"."HOTEL_GRADE_TYPE_ID"="H"."HOTEL_GRADE_TYPE_ID")
   6 - access("P"."PROVINCE_ID"="H"."PROVINCE_ID")
   8 - access("S"."ORDER_ID"="OP"."ORDER_ID")
   9 - access("OP"."ORDER_ID"="O"."ORDER_ID")
  10 - access("HB"."HOTEL_BELONG"="O"."HOTEL_BELONG")
  11 - filter("HB"."FINANCE_TYPE"=1)
  12 - access("H"."HOTEL_ID"="O"."HOTEL_ID")
  14 - access("H"."CITY_ID"=110101)
  15 - filter("O"."ORDER_STATUS_ID"<=700)
  17 - access("S"."IS_ISSUE_HOTELBILL"=0 AND "S"."CHECK_MONTH"<='2012-03')

已选择40行。


统计信息
----------------------------------------------------------
         45  recursive calls
         22  db block gets
        832  consistent gets
          1  physical reads
          0  redo size
       3819  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         40  rows processed
---------------------------------------------------------------------------------------------------
可以看出consistent gets（逻辑读）从2253降低到832，优化SQL的时候降低逻辑读是比较重要的，逻辑一个是
消耗cpu一个就是它可能伴随着物理读。
而且下面这两张大表也走索引。
INDEX FAST FULL SCAN         | IDX_T_HO_ROOM_PRICE
INDEX RANGE SCAN             | IDX_T_HO_ORDER_STATISTICS

总结：
SQL是优化了，原因在于建立了组合索引，下面来分析一下组合索引到底是怎么起作用的

组合索引：
由多列组成的索引,应用于由and条件联合的多个SQL条件组成的限制。（or不能有效的使用组合索引）
组合索引提供的索引扫描只返回了符合全部where条件的数据行，那么filter需要过滤的数据就大大的减少了
换种说法，这里使用组合索引时访问的rowid要比使用一般索引要少

需要添加的列：
建立组合索引的时候，整个SQL里面用到这张表上面的列都要放进去，不怕放多了，即使组合索引里面组成的
列比你这条SQL中用到的列多，只要是组合索引包括了，那么就能够有效的使用组合索引。但是注意不要在
同一张表上让一个列存在于多个组合索引里面，冗余了会出现问题。

列的顺序：
当所有的索引列都在where条件后面出现的时候，索引效率和索引中列的顺序是无关的，
如果不是，那么先导列应该是最常在where后面出现的列，这里建索引的时候不单单要
考虑这个SQL,要考虑到其他所有SQL使用这个列的情况

-----------------------------------------------------------------------
下面有一个组合索引的简单例子
Select Count(Tel_Record_Id)
From t_s_Tel_Record
Where t_s_Tel_Record.Result Is Null
And t_s_Tel_Record.Employee_Id = 1063
跑40秒钟
加组合索引后不到1秒
Create Index idx_tel_zu On T_S_TEL_RECORD(Result,Employee_Id) Online
------------------------------------------------------------------------------------

A leading portion of an index is a set of one or more columns that were

specified first and consecutively in the list of columns in the CREATE

INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind
ON table1(x, y, z);
x, xy, and xyz combinations of columns are leading portions of the index
yz, y, and z combinations of columns are not leading portions of the index

wadekobe9

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
组合索引优化一条SQL

原始SQL,出来的数据为零行，这不是重点，统计信息没有过期，这里不考虑统计信息的问题分别看下设计到表的行数 select count(*) from t_ho_room_price --3254605 select count(*) from t_ho_order_info --2034996 select count(*) from t_ho_order_s
复制链接

扫一扫

专栏目录