原SQL语句(所有相关敏感信息已做脱敏处理)
x
explain select t.PROJECT_CODE AS "projectCode", t.ACTIVITY_CODE AS "activityCode", t.ACTIVITY_NAME AS "activityName", t.ACTIVITY_PICTURE AS "activityPicture", t.ACTIVITY_NOTE AS "activityNote", t.ACTIVITY_STATUS AS "activityStatus", t.ACTIVITY_TYPE AS "activityType", t.AGREEMENT_CODE AS "agreementCode", t.PARENT_TYPE AS "parentType", t.PARENT_CODE AS "parentCode", t.PARENT_ACTIVITY_CODE AS "parentActivityCode", t.SUBSIDIARY_FLAG AS "subsidiaryFlag", date_format(t.ACTIVITY_WARMUP_START_TIME,'%Y-%m-%d %H:%i:%s') AS "activityWarmupStartTime", date_format(t.ACTIVITY_WARMUP_END_TIME,'%Y-%m-%d %H:%i:%s') AS "activityWarmupEndTime", date_format(t.ACTIVITY_START_TIME,'%Y-%m-%d %H:%i:%s') AS "activityStartTime", date_format(t.ACTIVITY_END_TIME,'%Y-%m-%d %H:%i:%s') AS "activityEndTime", date_format(t.REGISTOR_START_TIME,'%Y-%m-%d %H:%i:%s') AS "registorStartTime", date_format(t.REGISTOR_END_TIME,'%Y-%m-%d %H:%i:%s') AS "registorEndTime", date_format(t.AUDIT_END_TIME,'%Y-%m-%d %H:%i:%s') AS "auditEndTime", t.USER_CODE AS "userCode", t.USER_NAME AS "userName", t.PUBLISH_TIME AS "publishTime", ( select count(1) from table_xxx_registry l where l.ACTIVITY_CODE = t.ACTIVITY_CODE and SUPPLIER_CODE = '00000000' ) as signUpCount from table_xxx_activity t where 1 = 1 and t.ACTIVITY_STATUS in ('02','03') and t.BUSSINESS_TYPE IN ('2', '3') and t.REGISTOR_START_TIME <= CURRENT_TIMESTAMP and t.PARENT_TYPE in ('00') and t.ACTIVITY_TYPE in ('46','47','48','49','52') and t.PROJECT_CODE = 'PJ190906192410000007' and EXISTS ( select 1 from table_xxx_registry l where l.ACTIVITY_CODE = t.ACTIVITY_CODE ) AND EXISTS ( SELECT 1 FROM table_xxx_activity_areastore taa WHERE taa.ACTIVITY_CODE = t.ACTIVITY_CODE AND taa.AREA_CODE = '10001' ) order by t.PUBLISH_TIME DESC
sql解析顺序分析
1.from条件后的 table_xxx_activity表（只有5万条数据）字段查询条件过滤，根据目前的生产业务情况，最多不超过（小）几百条数据，每个项目下最多只建了几百个活动
没有覆盖索引，走辅助索引后，回表用Using where过滤
2.遍历1中的结果分别执行exist语句的子查询判断当条记录是否保留
exist语句1可以走索引PK_tablexxxregistry_37、38
exist语句2可以走索引table_xxx_registry_store_idx1、table_xxx_activity_areastore，但是没有覆盖索引需要走Using where
已知table_xxx_registry表数据60万，table_xxx_activity_areastore表数据400万
3.执行select部分，选择查询的字段和signUpCount字段的子查询
signUpCount字段的子查询可以走辅助索引PK_tablexxxregistry_38
4.排序用到PUBLISH_TIME字段，重新走Using filesort排序
5.limit截断
【时间估算大致情况】
查询1的时间 + 查询1的结果条数 * （exist语句1的时间 + exist语句2的时间） + exist判断后结果2的条数 *（ select字段获取的时间 + signUpCount字段的子查询的时间）+ PUBLISH_TIME字段排序的时间
explain解析
初步分析
该语句为一个分页查询语句，慢查中报出的count语句为.queryForListPage(方法自动封装的语句
语句中嵌套了3个子查询
1.signUpCount字段
2.exist语句1
xxxxxxxxxxselect 1 from table_xxx_registry l where l.ACTIVITY_CODE = t.ACTIVITY_CODE3.exist语句2
xxxxxxxxxxSELECT 1 FROM table_xxx_activity_areastore taa WHERE taa.ACTIVITY_CODE = t.ACTIVITY_CODE AND taa.AREA_CODE = '10001'explain结果中出现了Using filesort排序，因为最终的order by t.PUBLISH_TIME DESC不在索引中出现
解决方案
1.原java代码中
xxxxxxxxxxpublic Page<?> queryXXSignupActivityList(Map<String, Object> paramMap){ return dalClientMW.queryForListPage(NAMESPACE + ".queryXXSignupActivityList", paramMap);}在现有sql语句结果集上，添加count（1）自动封装了获取总条数的sql语句，而根据分析，count计算不需要通过order by，且原来的查询字段中所有字段均不参与最终条数计算，包括signUpCount子查询，可以使用新的
xxxxxxxxxxqueryForListPage(String sqlId, String countSqlId,Object param, Class<T> requiredType)代替原来的方法，并去除掉对应的字段signUpCount子查询（对应key_len为390，id为2的那一步查询），排序（对应id为1中的Using filesort排序）等语句作为新的countSqlId进行总数量查询
2.signUpCount字段可以另外写逻辑单独查询，而具体展示数据查询的时候，因为有limit条件的存在，所以相对关联到的子查询数量会比较少
3.exist语句1、2 作为前置查询条件，先查询出结果集，再代入到本sql中作为新的in查询条件，或者做一个join查询，需要预先判断这个结果集的数量多少
方案2 子查询2表table_xxx_activity_areastore构建联合索引。ACTIVITY_CODE & AREA_CODE
5.排序字段order by t.PUBLISH_TIME DESC，可以看业务情况考虑是否使用id倒序排列，减少排序资源消耗
6.目前本表table_xxx_activity建立的索引已经非常多了，不便于重新新增新的联合索引
优化发布后验证结果，查询时间降低到700毫秒以下
发表评论