MySQL线上SQL优化实战案例（二）

Author:Jason豪

前言：此次出现问题的慢SQL共涉及到三张表，其中一张表是之前已经优化过SQL并上线过索引的一张表cm_settle_partner_day_data，同一张表再次抛出慢SQL，需要更深入的探究和检查之前SQL优化的结果和已创建的索引，并解决明晰上次SQL优化遗留的技术盲点，下文为本次的研究优化成果，在文末会总结参考过的文献与资料。

一、SQL业务背景与线上表数据环境

再次出现慢SQL的表为“日级数据上传表”，cm_settle_partner_day_data，该表目前的生产库数据量是2946064，每日新增数据量在4000左右，目前除主键外已有四个单列索引和上次新建的五列联合索引idx_status_dim_date_agent_channel。

建表语句如下：

CREATE TABLE `cm_settle_partner_day_data` (
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `created` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `modifier` varchar(64) NOT NULL DEFAULT '' COMMENT '更新人',
  `creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人',
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `settle_partner_day_id` bigint(20) NOT NULL COMMENT '业务唯一id',
  `channel_id` bigint(20) NOT NULL COMMENT '渠道id',
  `agent_id` bigint(20) NOT NULL COMMENT '企业id',
  `dimension_value` tinyint(4) NOT NULL COMMENT '维度值：1-Fr维度，2-账户维度，3-非标维度',
  `status` tinyint(4) NOT NULL COMMENT '数据状态，1-有效，2-覆盖',
  `business_date` date NOT NULL DEFAULT '1971-01-01' COMMENT '日期',
  `account` varchar(32) NOT NULL DEFAULT '' COMMENT '前台账号',
  `fr_code` varchar(32) NOT NULL DEFAULT '' COMMENT 'fr',
  `show_value` bigint(20) NOT NULL DEFAULT '0' COMMENT '展示数据',
  `click_value` bigint(20) NOT NULL DEFAULT '0' COMMENT '点击数据',
  `consume_value` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '消耗数据（元）',
  `cash_value` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '现金（元）',
  `file_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '上传的文件ID',
  PRIMARY KEY (`id`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_settle_partner_day_id` (`settle_partner_day_id`),
  KEY `idx_business_date` (`business_date`),
  KEY `idx_fr_code` (`fr_code`),
KEY `idx_status_dim_date_agent_channel` (`status`,`dimension_value`,`business_date`,`agent_id`,`channel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1041177 DEFAULT CHARSET=utf8 COMMENT='日级数据上传表'

二、“日级数据上传表”问题sql的具体优化思路与方案

1. 上次“日级数据上传表”SQL优化的瑕疵剖析和技术盲点扫除

上次优化最终版本的SQL如下（和本次优化的SQL不是同一个）：

SELECT
      p.channel_id channelId,
      sum( p.show_value ) showValue,
      sum( p.click_value ) clickValue,
      sum( p.consume_value ) consumeValue,
      sum( p.cash_value ) cashValue,
      four.channel_name channel_name 
      from cm_settle_partner_day_data p FORCE INDEX (idx_status_dim_date_agent_channel)
      ,
      (SELECT a.channel_id,a.agent_id,ch.channel_name,a.deleted,a.biz_type,ca.category_id from cm_channel_admittance_form a
      INNER JOIN cm_channel ch ON a.channel_id = ch.channel_id
      INNER JOIN cm_sub_category sc ON ch.sub_category_id = sc.sub_category_id
      INNER JOIN cm_category ca ON sc.category_id = ca.category_id
      WHERE a.deleted = 0
      AND a.biz_type = 1
      AND ca.category_id = 2) four
WHERE
p.STATUS = 1
      AND p.dimension_value IN ( 1, 3 )
      AND '2020-07' = date_format( business_date, '%Y-%m' )
      AND p.agent_id = 296
      AND p.agent_id = four.agent_id
      AND p.channel_id = four.channel_id
GROUP BY
      p.channel_id,
      four.channel_name;

上次涉及cm_settle_partner_day_data 表的SQL优化完成后，原来的级联慢SQL速度提升近百倍，线上执行0.03秒左右。但在建立联合索引时，出现将business_date字段放在联合索引首列后，SQL无法