分类： Mysql

原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

xxxxxxxxxx
select 1 from table_xxx_registry l where l.ACTIVITY_CODE = t.ACTIVITY_CODE

3.exist语句2

xxxxxxxxxx
SELECT 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代码中

xxxxxxxxxx
public Page<?> queryXXSignupActivityList(Map<String, Object> paramMap){
   return dalClientMW.queryForListPage(NAMESPACE + ".queryXXSignupActivityList", paramMap);
}

在现有sql语句结果集上，添加count（1）自动封装了获取总条数的sql语句，而根据分析，count计算不需要通过order by，且原来的查询字段中所有字段均不参与最终条数计算，包括signUpCount子查询，可以使用新的

xxxxxxxxxx
queryForListPage(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毫秒以下