资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/优化案例：不合理的复合索引导致大表访问低效

优化案例：不合理的复合索引导致大表访问低效

IT那活儿 / 2197人阅读
优化案例：不合理的复合索引导致大表访问低效

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

事件描述

前段时间一个27日的晚上，某生产库进行割接，28日08:30至09:00，监测到第5个节点DPR事件高达130多个。
INST_ID EVENT# EVENT WAIT_CLASS COUNT(*)
 ---------- ---------- ---------------------------------------- --------------- ----------
          1        447 SQL*Net message from dblink Network 2
          2        172 log file sync                            Commit 2
          2        161 log file parallel write                 System I/O 1
          2        186 gc current request Cluster 1
          2        860 Redo Transport MISC Other 1
          3        447 SQL*Net message from dblink Network 1
          5        246 direct path read                         User I/O 131
          5        183 db file async I/O submit System I/O 13
          5        179 db file sequential read                  User I/O 10
          5        184 db file parallel read                    User I/O 8
          5        172 log file sync                            Commit 8
          5        182 db file parallel write                   System I/O 7
          5        860 Redo Transport MISC Other 1
          5        447 SQL*Net message from dblink Network 1
          5        848 SYNC Remote Write Other 1
检查 I/O 读写情况，发现 30 分钟内 I/O 读取高达 1.4 TB，响应时间上升至 7 毫秒。
Start - End               5_R         5_W     5_T
 -------------------------- -------- ----------- -------
 07/28 06:30 - 07/28 07:00     59.29        3.90    0.45
 07/28 07:00 - 07/28 07:30      4.97        2.64    0.55
 07/28 07:30 - 07/28 08:00     64.17      107.45    0.66
 07/28 08:00 - 07/28 08:30     49.35      119.04    0.49
 07/28 08:30 - 07/28 09:00  1,410.74       61.31    7.11
二

分析过程

分析此时段数据库所有等待事件，DPR 位置榜首，说明很可能存在扫描大表的 SQL 语句。
Top Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
 --- ---------------------------------------- ------------ --------------------- ---------------- --------- --------------
   1 direct path read 799,755 60,444.8 75.579 19.2 User I/O
   2 log file sync 16,373,135 30,794.3 1.881 9.8 Commit
   3 log file switch (checkpoint incomplete) 4,761              12,784.5        2,685.246       4.1 Configuration
   4 db file sequential read                       107,176               6,753.6           63.014       2.1 User I/O
   5 DB CPU 5,841.9                        1.9
   6 db file parallel read                          23,852               4,916.0          206.103       1.6 User I/O
   7 buffer busy waits 29,536               2,097.8           71.024       0.7 Concurrency
   8 log file switch completion 1,906               1,488.0          780.686       0.5 Configuration
   9 row cache lock                                320,632               1,111.9            3.468       0.4 Concurrency
  10 read by other session                             105                 220.0        2,095.040       0.1 User I/O
从多个维度对 SQL 执行情况进行分析，发现有两条 SQL 语句 Total% 项均为最高。
Top Elapsed Time (s) Executions Elapsed Time per Exec (ms) Total% SQL_ID Schema Module
 --- ---------------- ------------ -------------------------- ------- ------------- ------ --------------------------
   1       122,861.29           20              6,143,064.500   38.94 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   2       122,580.44           22              5,571,838.182   38.85 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   3         1,949.11        6,674                    292.045    0.62 9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
   4         1,907.77            1              1,907,770.000    0.60 dmyv32kv2v407 AD MrOfeJ@mafjnh-10.lacolavon
   5         1,784.75            1              1,784,750.000    0.57 55a3n59n7duy0 AD MrOfeJ@mafjnh-10.lacolavon
 Top CPU Time (s) Executions CPU per Exec (ms) Total% Elapsed Time (s) SQL_ID Schema Module
 --- ------------- ------------ ----------------- ------- ---------------- ------------- ------ ----------------------------
   1        324.08           22         14,731.11    5.55       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2        314.10           20         15,704.77    5.38       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3         76.83      320,633              0.24    1.32         1,352.41 bsy2r0brwxdba AD emarfs-167924-1@hnjfapp55-3
   4         43.22           31          1,394.24    0.74            42.78 gx4drnh6540u5 JF SQL*Plus
   5         33.33    1,046,074              0.03    0.57           508.34 9vaj2dfcrc44v AD emarfs-175368-13@hnjfapp17-7
 Top User I/O Time (s) Executions UIO per Exec (ms) %Total Elapsed Time (s) SQL_ID Schema Module
 --- ----------------- ------------ ----------------- ------- ---------------- ------------- ------ --------------------------
   1          31546.74           22    1,433,942.7927   43.46       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2          29609.35           20    1,480,467.5470   40.79       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3           1939.27        6,674          290.5711    2.67         1,949.11 9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
   4           1194.62        6,937          172.2093    1.65         1,231.32 d5zu2b9cz25y8 AD MrOfeJ@mafjnh-15.lacolavon
   5           1032.22        3,548          290.9299    1.42         1,065.47 c9tkyagwbvj7d AD MrOfeJ@mafjnh-15.lacolavon
 Top Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) SQL_ID Schema Module
 --- ----------- ---------- --------------- ------- ---------------- ------------- ------ -----------------------------
   1 104,089,687         22    4,731,349.41   25.86       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2  98,475,484         20    4,923,774.20   24.46       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3   6,555,975         31      211,483.06    1.63            42.78 gx4drnh6540u5 JF SQL*Plus
   4   2,800,644  1,046,074            2.68    0.70           508.34 9vaj2dfcrc44v AD emarfs-175368-13@hnjfapp17-7
   5   2,599,250  1,010,897            2.57    0.65           350.97 4wjx4ubwbumz5 AD emarfs-175388-5@hnjfapp33-13
 Top Physical Reads Executions Reads per Exec %Total Elapsed Time (s) SQL_ID Schema Module
 --- -------------- ------------ --------------- ------- ---------------- ------------- ------ --------------------------
   1    103,598,396           22    4,709,018.00   52.23       122,580.44  1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2     98,039,336           20    4,901,966.80   49.43       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3      1,341,265            1    1,341,265.00    0.68         1,784.75  55a3n59n7duy0 AD MrOfeJ@mafjnh-10.lacolavon
   4      1,341,265            1    1,341,265.00    0.68         1,907.77 dmyv32kv2v407 AD MrOfeJ@mafjnh-10.lacolavon
   5         29,556        6,674            4.43    0.01         1,949.11  9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
-- dwyq6uy86w4gw
update CD.OC_DORP_173 set PROD_EXPIRE_DATE = :1  where PRODUCT_ID=:2

-- 1gxak4kjy0tj6
select t.* from CD.OC_DORP_173 t where t.PRODUCT_ID=:1
可以看到，两条 SQL 均访问表 CD.OC_DORP_173，且使用了 PRODUCT_ID 做为筛选条件，查看执行计划，发现两条 SQL 都使用了全表扫描访问。
SQL_ID dwyq6uy86w4gw, child number 0
-------------------------------------
update CD.OC_DORP_173 set PROD_EXPIRE_DATE = :1  where PRODUCT_ID=:2

Plan hash value: 2749196760
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |    TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT |             | |       | 817K(100)|          | |      | |
| 1 |  UPDATE | OC_DORP_173 |       | |            | |        | |            |
|   2 | PX COORDINATOR |             | |       | |          | |      | |
| 3 |    PX SEND QC (RANDOM)| :TQ10000 |     1 | 18 |   817K (1)| 00:00:32 |  Q1,00 | P->S | QC (RAND) |
|   4 | PX BLOCK ITERATOR |             | 1 |    18 | 817K (1)| 00:00:32 | Q1,00 | PCWC | |
|* 5 |      TABLE ACCESS FULL| OC_DORP_173 |     1 | 18 |   817K (1)| 00:00:32 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  5 - access(:Z>=:Z AND :Z<=:Z)
      filter("PRODUCT_ID"=:2)
SQL_ID 1gxak4kjy0tj6, child number 0
-------------------------------------
select t.* from CD.OC_DORP_173 t where t.PRODUCT_ID=:1

Plan hash value: 3713006496
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |       | | 32424 (100)| |        | |            |
|   1 | PX COORDINATOR |             | |       | |          | |      | |
| 2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 | 119 | 32424   (1)| 00:00:02 |  Q1,00 | P->S | QC (RAND) |
|   3 | PX BLOCK ITERATOR |             | 1 |   119 | 32424 (1)| 00:00:02 | Q1,00 | PCWC | |
|* 4 |     TABLE ACCESS FULL| OC_DORP_173 |     1 | 119 | 32424   (1)| 00:00:02 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access(:Z>=:Z AND :Z<=:Z)
      filter("T"."PRODUCT_ID"=:1)
检查表物理结构，发现该表创建有复合索引，包含 PRODUCT_ID 列，不过并非前导列，因此以上两条 SQL 语句并未使用该索引，直接对表进行了全扫描。
TABLE_NAME COLUMN_NAME COLUMN_POSITION INDEX_NAME UNIQUENES STATUS
------------ ----------- --------------- ------------------- --------- ------
OC_DORP_173 OBJECT_ID 1 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 PRODUCT_ID 2 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 VALID_DATE 3 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 SO_NBR 4 PK_OC_DORP_173 UNIQUE VALID
为什么创建索引时 PRODUCT_ID 列没有被做为前导列呢？是选择性不好吗？我们检查下列的数据分布情况。
COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY
--------------------- ---------- ------------ ----------
PRODUCT_ID NUMBER 266993664 3.7454E-09
可以看到，PRODUCT_ID 列的选择性是最好的，进一步检查表数据，统计显示约 2.7 亿条数据，占用空间约 36 GB，但并未使用任何分区技术，因此每执行一次都会访问约 36GB 的数据，这也是 30 分钟内仅执行 42 次，I/O 读取却高达 1.4TB 的根本原因。
COUNT(1)
-------------
271,868,287

SEGMENT_NAME MB
-------------- ----------
OC_DORP_173 36,844.00

TABLE_NAME PARTITIONED
--------------- ------------
OC_DORP_173 NO
三

分析结论及建议

未合理使用复合索引，未合理利用分区技术，导致对大表的访问过于低效。

优化建议：

  • 创建合理的复合索引，确保常用筛选列为前导列。
  • 对于超过 2G 或千万条数据的大表，使用分区技术改造。




本文作者：任 崇(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

云服务器 GPU云服务器 复合索引 索引分析和优化 mongodb索引优化 重启阿里云服务器导致无法访问

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129145.html

相关文章

  • 开源|性能优化利器：数据库审核平台Themis的选型与实践

    摘要：正是存在问题，促使我们考虑引入数据库审核平台。的确，与很多互联网公司相比，数据库数十套的估摸并不是太大但与互联网类公司不同，类似宜信这类金融类公司对数据库的依赖性更大，大量的应用是重数据库类的，且其使用复杂程度也远比互联网类的复杂。 作者：韩锋 出处：DBAplus社群分享 Themis开源地址：https://github.com/CreditEaseDBA 拓展阅读：宜信开源|数...

    wenhai.he 评论0 收藏0

  • 一文看懂 MySQL 高性能优化技巧实践

    摘要：实际应用中，查询的返回结果会有条记录。修改保存文件后，重启服务。通常优化至少到级别，最好能优化到指出使用哪个索引在该表找到行记录。如果该值为，说明没有使用索引，可以建立索引提高性能显示实际使用的索引。 ...

    simon_chen 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<