传统项目消息中心的设计注意的地方

迷南。 2022-05-31 05:18 61阅读 0赞

今天有一条SQL占用系统负载很高，IO负载占用了30%。功能是用户登陆之后，显示未读消息的数量。由于只要点系统的功能都会查这条SQL。  
导致上午4个小时调用了10万次。   
SELECT COUNT(1)  
  FROM WORKBENCH\_MES T1, WORKBENCH\_MES\_REL T2  
 WHERE T1.MES\_ID = T2.MES\_ID  
   AND T2.RECIPIENT\_ID = '83DB7DD7505B4C90831717AB18881C69'  
   AND T2.IS\_READ = 'N'   
   AND T1.SEND\_DATE >= TO\_DATE('2017-08-04', 'YYYY-MM-DD')

AND T1.SEND\_DATE < TO\_DATE('2018-02-04', 'YYYY-MM-DD') + 1;

就在昨天，这条SQL执行计划改变了。由于之前消耗小，虽然执行次数多，问题也不大，执行计划从索引变成全表之后问题就来了。

\-------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |  
\-------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                      |                           |       |       |   258 (100)|          |  
|   1 |  SORT AGGREGATE                       |                           |     1 |   113 |            |          |  
|\*  2 |   FILTER                              |                           |       |       |            |          |  
|   3 |    NESTED LOOPS                       |                           |   100 | 11300 |   258   (0)| 00:00:04 |  
|   4 |     NESTED LOOPS                      |                           |   100 | 11300 |   258   (0)| 00:00:04 |  
|\*  5 |      TABLE ACCESS BY INDEX ROWID      | WORKBENCH\_MES\_REL         |   100 |  6800 |    58   (0)| 00:00:01 |  
|\*  6 |       INDEX RANGE SCAN                | IND\_WMR\_RECIPIENT\_ID\_0905 |   128 |       |     4   (0)| 00:00:01 |  
|\*  7 |      INDEX UNIQUE SCAN                | PK\_WORKBENCH\_MES10        |     1 |       |     1   (0)| 00:00:01 |  
|\*  8 |     TABLE ACCESS BY GLOBAL INDEX ROWID| WORKBENCH\_MES             |     1 |    45 |     2   (0)| 00:00:01 |

\-------------------------------------------------------------------------------------------------------------------

\-------------------------------------------------------------------------------------------------------------  
| Id  | Operation                   | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
\-------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |                       |       |       |       |   161K(100)|          |  
|   1 |  SORT AGGREGATE             |                       |     1 |   113 |       |            |          |  
|\*  2 |   FILTER                    |                       |       |       |       |            |          |  
|\*  3 |    HASH JOIN                |                       |   122K|    13M|  9544K|   161K  (1)| 00:32:17 |  
|\*  4 |     TABLE ACCESS FULL       | WORKBENCH\_MES\_REL     |   122K|  8111K|       |  46750  (1)| 00:09:21 |  
|   5 |     PARTITION RANGE ITERATOR|                       |  2614K|   112M|       |   107K  (1)| 00:21:25 |  
|\*  6 |      TABLE ACCESS FULL      | WORKBENCH\_MES         |  2614K|   112M|       |   107K  (1)| 00:21:25 |

\-------------------------------------------------------------------------------------------------------------

**开始诊断：**  
1.第一感觉就是两张表的索引是不是丢了。因为RECIPIENT\_ID选择性还是不错的。检查了表，索引都在。  
2.select count(1) from WORKBENCH\_MES\_REL where  RECIPIENT\_ID = '83DB7DD7505B4C90831717AB18881C69';  
  select count(1) from WORKBENCH\_MES\_REL where  RECIPIENT\_ID = '83DB7DD7505B4C90831717AB18881C69' and IS\_READ = 'N';  
  查询这两条SQL结果是一样的，说明用户基本上是不读消息的。  
3.问题找到了。是用户基本上不看未读消息，导致数据越来越多，在选择走索引还是全表的评估上，CBO评估之前数据量少就走了索引，  
随着数据量增大，CBO更倾向于走全表。  
  
**临时解决方案：**  
把一个月之前未读的消息都设置为已读，接收人和消息是否已读两个字段加索引，并生成直方图。  
create index IND\_WMR\_RID\_ISREAD on WORKBENCH\_MES\_REL(RECIPIENT\_ID,IS\_READ) nologging;  
exec dbms\_stats.gather\_table\_stats(user,'WORKBENCH\_MES\_REL',cascade => true,degree => 8,method\_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (RECIPIENT\_ID,IS\_READ)',no\_invalidate=>FALSE);  
  
**深层次的思考：**  
此功能虽然做了，显然用户没有使用，有大量的用户没有查看未读消息的习惯。请问你会读5年前未读的消息吗？  
设计上需要优化的：  
1.只保留最近1年的消息，因为5年时间已经累计了几千万的消息，查这个表没有加条件，很容易出性能问题。  
2.一个月之前未读的消息，默认标记它为已读，因为失去了实效性。  
3.用户点菜单就会查询这条SQL,改为把这个查询结果放到用户的session里面，会话有效期间不用再去查数据库，当然这也牺牲了实效性。这么做的原因是绝大部分用户没有看未读消息的习惯，从分析数据所得。