KSQL分页查询Order条件后耗时严重

garsonzhang

查询KSQL如下：

1. select sr_t_abnormalData,sr_t_abnormalData.fAPPTime as fAPPTime,SR_T_MonitorItem.fModule as fModule,sr_t_abnormalData.fTCode as fTCode,SR_T_MonitorItem.fTCodeName as fTCodeName,SR_T_MonitorItem.fContent as fContent,sr_t_abnormalDataUserSum.fPrincipalType as fPrincipalType,sr_t_abnormalDataUserSum.fScore as fScore,SR_T_MonitorItem.fMethod as fMethod
   
2.     from sr_t_abnormalData sr_t_abnormalData
   
3.      join sr_t_abnormalDataUserSum sr_t_abnormalDataUserSum on sr_t_abnormalData = sr_t_abnormalDataUserSum.fDataID
   
4.      join SR_T_MonitorItem SR_T_MonitorItem on sr_t_abnormalData.fTCode = SR_T_MonitorItem.fTCode
   
5.     where sr_t_abnormalDataUserSum.fUserID = 'KfMNXPllMIHU5SR0Dfd'

复制代码

测试执行结果：

1. 2017-10-18 10:59:27 KSQL 执行时间：1101ms, ksql: select sr_t_abnormalData,sr_t_abnormalData.fAPPTime as fAPPTime,SR_T_MonitorItem.fModule as fModule,sr_t_abnormalData.fTCode as fTCode,SR_T_MonitorItem.fTCodeName as fTCodeName,SR_T_MonitorItem.fContent as fContent,sr_t_abnormalDataUserSum.fPrincipalType as fPrincipalType,sr_t_abnormalDataUserSum.fScore as fScore,SR_T_MonitorItem.fMethod as fMethod     from sr_t_abnormalData sr_t_abnormalData       join sr_t_abnormalDataUserSum sr_t_abnormalDataUserSum on sr_t_abnormalData = sr_t_abnormalDataUserSum.fDataID      join SR_T_MonitorItem SR_T_MonitorItem on sr_t_abnormalData.fTCode = SR_T_MonitorItem.fTCode     where sr_t_abnormalDataUserSum.fUserID = 'KfMNXPllMIHU5SR0Dfd' LIMIT 0, 50

复制代码

当加上order后

1. 2017-10-18 11:00:24 KSQL 执行时间：12854ms, ksql: select sr_t_abnormalData,sr_t_abnormalData.fAPPTime as fAPPTime,SR_T_MonitorItem.fModule as fModule,sr_t_abnormalData.fTCode as fTCode,SR_T_MonitorItem.fTCodeName as fTCodeName,SR_T_MonitorItem.fContent as fContent,sr_t_abnormalDataUserSum.fPrincipalType as fPrincipalType,sr_t_abnormalDataUserSum.fScore as fScore,SR_T_MonitorItem.fMethod as fMethod     from sr_t_abnormalData sr_t_abnormalData       join sr_t_abnormalDataUserSum sr_t_abnormalDataUserSum on sr_t_abnormalData = sr_t_abnormalDataUserSum.fDataID      join SR_T_MonitorItem SR_T_MonitorItem on sr_t_abnormalData.fTCode = SR_T_MonitorItem.fTCode     where sr_t_abnormalDataUserSum.fUserID = 'KfMNXPllMIHU5SR0Dfd'     order by sr_t_abnormalData.fAPPTime desc LIMIT 0, 50
   

复制代码

时间从1秒到12秒之差，有点大，数据库为SQLServer，索引也设置了fAPPTime
望指点！！

jishuang

把sql的执行时间打出来，看sql的执行时间

garsonzhang

原因查出来了，还是数据库端缺少相关索引引起的，谢谢回复，可以结帖了，
解决办法是：打开sql实际执行时间（ctrl+m）,然后执行sql，根据提示创建相关索引