db3库高并发SQL全表扫描表分析及优化

1、背景：xxx.39数据库cpu一直高居不下，定时分析高并发，慢SQL反馈业务进行优化，前期已从90%降至60左右。

2、查询xxx0.39 cpu高进程是 3337端口引起。

 3、原因分析：
3.1 查询瞬时执行SQL，发现如下SQL执行并发非常高;瞬时可以高达17笔。1秒可能执行上100次。
SELECT   WORK_ITEM_ID AS workItemId,   WF_WORK_ITEM_ID AS wfWorkItemId,   PRIORITY AS priority,   CRM_ORDER_ID AS crmOrderId,   OSS_ORDER_ID AS ossOrderId,   CONTACT_ID AS contactId,   SERVICE_NO AS serviceNo,   LOGIN_NO AS loginNo,   GROUP_ID AS groupId,   ID_NO AS idNo,   STEP_ID AS stepId,   WORKORDER_ID AS workorderId,   SVC_ID AS svcId,   ACTION_ID AS actionId,   DEAL_SEQ AS dealSeq,   WORK_STATION_ID AS workStationId,   PROPERTY_CHAR AS propertyChar,   SVC_RESERVE_DATE AS svcReserveDate,   CRM_RESERVE_DATE AS crmReserveDate,   RESERVE_FLAG AS reserveFlag,   STATUS AS status,   OPERATE_TIME AS operateTime,   ORDER_CREATE_TIME AS orderCreateTime,   CREATE_TIME AS createTime,   PRE_WARNING AS preWarning,   WARNING_TIME AS warningTime,   SEND_TIME AS sendTime,   SEND_AGAIN_TIME AS sendAgainTime,   SEND_COUNT AS sendCount,   COMPLETE_TIME AS completeTime,   RET_CODE AS retCode,   RET_MSG AS retMsg,   WI_SAVE_CODE AS wiSaveCode,   ODR_SAVE_CODE AS odrSaveCode,   REMARKS AS remarks,   BACK_FLAG AS backFlag,   TENANT_ID AS tenantId,   REQUEST_NO AS requestNo,   CALL_ID AS callId,   OSS_DEAL_COUNT AS ossDealCount,   OSS_DEAL_DATE AS ossDealDate,   OSS_DEAL_MSG AS ossDealMsg,   OSS_DEAL_FLAG AS ossDealFlag  FROM SO_WORKITEM_RD_000 where status ='X'          ORDER BY DEAL_SEQ,CREATE_TIME ASC    limit                    0,2000；

 3.2SQL分析发现全表扫描

 3.3 单条预计执行时间在20-50ms之间：

 3.4分析耗时时间在那一步：主要在创建 排序索引，同时耗费系统cpu耗费2.6%，如果瞬时17笔，预计耗费cpu 44%

 3.5 考虑给排序字段加索引（DEAL_SEQ,CREATE_TIME），发现执行效率变低2-3倍：虽然把文件排序的问题解决，但是数据发送时间长，此方法不可行

 3.6、分析 where条件加索引，先看看字段区分度，发现status这个字段一共才3个不一样的值，理论上不建议加索引

 3.7但是分析出我们的SQL都是status='X'一个固定值，而且所有语句都是这个条件。根据索引原理，针对这种固定值且查询数据只有几条或者是空的情况下是可以加索引的。尝试在备节点加索引：

 

 3.8查询效率检查执行都是在0.00s：增加 create index idx_status on SO_WORKITEM_RD_000(status);索引后，查询效率至少快了5-20倍

 3.9查看每个节点的耗时：耗时节点和cpu消耗明显减少

 总结：
1、数据库xxdb3 的SO_WORKITEM_RD_xxx表类，因并发要求，需创建status字段索引。 create index idx_status on SO_WORKITEM_RD_xxx(status);
2、建议节后进行调整