有order by 分页的 大数量表关联查询SQL如何优化？

nuaaguang

wlovely 发表于 2017-1-18 14:02
member 表的ID是主键，
除了主键之外，id上还有一个组合索引
CREATE INDEX IDX_MEMBER_RD ON MEMBER(i ...

  LEFT JOIN member_expert me ON am.id = me.member_id     主键和member_expert 关联？member_expert 只有40+数据？
  LEFT JOIN organize mor ON am.org_id = mor.org_id   org_id应该11对应吧

Naldonado

我正要说逆序索引。但是我觉得只要一个单列的应该就ok了吧。

demonat

Naldonado 发表于 2017-1-18 16:28
我正要说逆序索引。但是我觉得只要一个单列的应该就ok了吧。

单列的得看列上有没有not null

wlovely

demonat 发表于 2017-1-18 17:21
单列的得看列上有没有not null

这条SQL order by的字段是有空值，但是其他的SQL上类似查询的SQL即使order by上的列没有空值，就算有索引也是不能消除执行计划中的order by，要用组合索引才能消除

bell6248

[font=宋体]
测试一下如下的语句的性能如何？

select a.id,
       a.customerid,
       a.customername,
       a.sex,
       a.age,
       TO_CHAR(a.birthday, 'YYYY-MM-DD') birthday,
       a.nativeplace,
       a.nation,
       a.province proCode,
       a.city citCode,
       a.district disCode,
       a.address,
       a.telephone,
       a.email,
       TO_CHAR(a.register_date, 'YYYY-MM-DD HH24:MI:SS') register_date,
       a.mobilephone,
       a.associatcode,
       a.relation,
       a.organizecode,
       a.remarks,
       a.integral,
       a.idcard,
       a.logincount,
       a.salt,
       a.member_type,
       a.status,
       a.org_id,
       c.org_code orgCode,
       c.org_name orgName,
       b.RANK expertRank,
       b.reference_no expertReferenceNo,
       b.doctor_no expertDoctorNo,
       b.product_line expertProductLine,
       b.recommend expertRecommend,
       b.flag expertFlag,
       b.intro expertIntro,
       a.backcontact,
       a.backcontact_phone
  from member a LEFT JOIN member_expert b ON a.id = b.member_id
                LEFT JOIN organize c ON a.org_id = c.org_id
where a.rowid in
       (SELECT a_rid
          FROM (SELECT a_rid,
                       ROWNUM AS rn
                  FROM (SELECT am.rowid a_rid
                          FROM member am LEFT JOIN member_expert me ON am.id = me.member_id
                                         LEFT JOIN organize mor ON am.org_id = mor.org_id
                         ORDER BY am.register_date DESC)
                 where rownum <= 100)
         WHERE rn >= 1)

wlovely

bell6248 发表于 2017-1-20 15:06
测试一下如下的语句的性能如何？

select a.id,

没有建组合索引("REGISTER_DATE" DESC, ID, ORG_ID)之前的执行计划：

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |     1 |   345 |       |   546K  (1)| 01:49:18 |
|   1 |  NESTED LOOPS OUTER             |                         |     1 |   345 |       |   546K  (1)| 01:49:18 |
|   2 |   NESTED LOOPS OUTER            |                         |     1 |   260 |       |   546K  (1)| 01:49:18 |
|   3 |    NESTED LOOPS                 |                         |     1 |   194 |       |   546K  (1)| 01:49:18 |
|   4 |     VIEW                        | VW_NSO_1                |   100 |  1200 |       |   546K  (1)| 01:49:18 |
|   5 |      HASH UNIQUE                |                         |     1 |  2500 |       |            |          |
|*  6 |       VIEW                      |                         |   100 |  2500 |       |   546K  (1)| 01:49:18 |
|*  7 |        COUNT STOPKEY            |                         |       |       |       |            |          |
|   8 |         VIEW                    |                         |    14M|   166M|       |   546K  (1)| 01:49:18 |
|*  9 |          SORT ORDER BY STOPKEY  |                         |    14M|  1886M|  2029M|   546K  (1)| 01:49:18 |
|* 10 |           HASH JOIN RIGHT OUTER |                         |    14M|  1886M|       |   106K  (1)| 00:21:19 |
|  11 |            INDEX FULL SCAN      | IDX_MEMBER_EXPERT_ID |    43 |  1419 |       |     1   (0)| 00:00:01 |
|* 12 |            HASH JOIN RIGHT OUTER|                         |    14M|  1428M|       |   106K  (1)| 00:21:19 |
|  13 |             INDEX FULL SCAN     | IDX_ORGANIZE_ORGID    |    13 |   429 |       |     1   (0)| 00:00:01 |
|  14 |             TABLE ACCESS FULL   | MEMBER               |    14M|   971M|       |   106K  (1)| 00:21:18 |
|  15 |     TABLE ACCESS BY USER ROWID  | MEMBER               |     1 |   182 |       |     1   (0)| 00:00:01 |
|  16 |    TABLE ACCESS BY INDEX ROWID  | ORGANIZE              |     1 |    66 |       |     1   (0)| 00:00:01 |
|* 17 |     INDEX RANGE SCAN            | IDX_ORGANIZE_ORGID    |     1 |       |       |     0   (0)| 00:00:01 |
|  18 |   TABLE ACCESS BY INDEX ROWID   | MEMBER_EXPERT        |     1 |    85 |       |     1   (0)| 00:00:01 |
|* 19 |    INDEX RANGE SCAN             | IDX_MEMBER_EXPERT_ID |     1 |       |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

   6 - filter("RN">=1)
   7 - filter(ROWNUM<=100)
   9 - filter(ROWNUM<=100)
  10 - access("AM"."ID"="ME"."MEMBER_ID"(+))
  12 - access("AM"."ORG_ID"="MOR"."ORG_ID"(+))
  17 - access("A"."ORG_ID"="C"."ORG_ID"(+))
  19 - access("A"."ID"="B"."MEMBER_ID"(+))



建组合索引("REGISTER_DATE" DESC, ID, ORG_ID)之后的执行计划：

Plan hash value: 374442192

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |     1 |   345 |     7  (15)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER           |                         |     1 |   345 |     7  (15)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                         |     1 |   260 |     6  (17)| 00:00:01 |
|   3 |    NESTED LOOPS               |                         |     1 |   194 |     5  (20)| 00:00:01 |
|   4 |     VIEW                      | VW_NSO_1                |   100 |  1200 |     3   (0)| 00:00:01 |
|   5 |      HASH UNIQUE              |                         |     1 |  2500 |            |          |
|*  6 |       VIEW                    |                         |   100 |  2500 |     3   (0)| 00:00:01 |
|*  7 |        COUNT STOPKEY          |                         |       |       |            |          |
|   8 |         VIEW                  |                         |   100 |  1200 |     3   (0)| 00:00:01 |
|   9 |          NESTED LOOPS OUTER   |                         |   100 | 13600 |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS OUTER  |                         |   100 | 10300 |     3   (0)| 00:00:01 |
|  11 |            INDEX FULL SCAN    | IDX_MEMBER_RD           |    14M|   971M|     3   (0)| 00:00:01 |
|* 12 |            INDEX RANGE SCAN   | IDX_ORGANIZE_ORGID    |     1 |    33 |     0   (0)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN    | IDX_MEMBER_EXPERT_ID |     1 |    33 |     0   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY USER ROWID| MEMBER               |     1 |   182 |     1   (0)| 00:00:01 |
|  15 |    TABLE ACCESS BY INDEX ROWID| ORGANIZE              |     1 |    66 |     1   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN          | IDX_ORGANIZE_ORGID    |     1 |       |     0   (0)| 00:00:01 |
|  17 |   TABLE ACCESS BY INDEX ROWID | MEMBER_EXPERT        |     1 |    85 |     1   (0)| 00:00:01 |
|* 18 |    INDEX RANGE SCAN           | IDX_MEMBER_EXPERT_ID |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   6 - filter("RN">=1)
   7 - filter(ROWNUM<=100)
  12 - access("AM"."ORG_ID"="MOR"."ORG_ID"(+))
  13 - access("AM"."ID"="ME"."MEMBER_ID"(+))
  16 - access("A"."ORG_ID"="C"."ORG_ID"(+))
  18 - access("A"."ID"="B"."MEMBER_ID"(+))

bell6248

wlovely 发表于 2017-1-20 17:30
没有建组合索引("REGISTER_DATE" DESC, ID, ORG_ID)之前的执行计划：

--------------------------- ...

这个组合索引你不是已经说明了已经是有的，如果根本没有建合理和必要的索引，这属于设计本身的问题

demonat

wlovely 发表于 2017-1-20 14:24
这条SQL order by的字段是有空值，但是其他的SQL上类似查询的SQL即使order by上的列没有空值，就算有索引 ...

列上有没有空值和列上有没有not null约束是两回事，你这个组合索引能消除order by是因为 id上有not null 所以这个组合索引上的列不会全为空，这样才能利用到索引，单列上有not null约束同样能消除order by