SQL优化与索引设计 经典实战案例+原理思路详解

Author:Jason豪

前言： SQL优化与索引使用是后端开发的重要技能之一，也是区分后端开发能力强弱的关键技能之一，但其涉及的知识理论之广之深，任拿其中一点都足以成书。正如培养一个飞行员，不单单要有理论和地面模拟试驾，更要有足够的驾驶战机飞行时间，在能充分掌握SQL优化和索引的大部分理论后，充足丰富的SQL优化经验对于该项能力的磨练十分关键，纸上得来终觉浅，觉知此事要躬行。本文分享了近期工作中遇到的两个经典case，在解决过程中运用实践了大量理论，愿在文中的相处，能为你带来工作与生活中的灵感。

You must find the right balance to achieve fast queries using the optimal set of indexes.

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

1.出现慢SQL的表为cm_fr_detail表，目前线上数据量为1546340，测试环境为100w+，数据量每日仍在增长。建表语句如下：

CREATE TABLE `cm_fr_detail` (
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `created` timestamp NOT NULL DEFAULT '0000-00-00 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',
  `fr_detail_id` bigint(20) NOT NULL COMMENT 'fr id',
  `fr_batch_id` bigint(20) NOT NULL COMMENT '所属批次的fr id',
  `media_id` bigint(20) NOT NULL COMMENT '所属媒体的id',
  `fr_type` tinyint(4) NOT NULL COMMENT 'FR类型',
  `fr_code` varchar(64) NOT NULL DEFAULT '' COMMENT 'FR码',
  `ch_id` bigint(20) NOT NULL COMMENT '风控CH码',
  `agent_id` bigint(20) NOT NULL COMMENT '所属企业的id',
  `mode` varchar(64) NOT NULL DEFAULT '' COMMENT '投放方式',
  `product_code` varchar(64) NOT NULL DEFAULT '' COMMENT '产品线',
  `cl` varchar(64) NOT NULL DEFAULT '' COMMENT '小程序产品参数',
  `position` varchar(64) NOT NULL DEFAULT '' COMMENT '投放位置',
  `page_id` varchar(255) NOT NULL DEFAULT '' COMMENT '页面id',
  `active_id` varchar(255) NOT NULL DEFAULT '' COMMENT '活动id',
  `account_id` bigint(20) NOT NULL COMMENT '广告主id',
  `is_charge` tinyint(4) NOT NULL COMMENT 'FR是否计费',
  `fr_link` varchar(1024) NOT NULL DEFAULT '' COMMENT 'FR生成链接',
  `material_id` bigint(20) NOT NULL DEFAULT '-1' COMMENT '物料报备id',
  `status` tinyint(4) NOT NULL COMMENT '推广状态',
  `version` int(11) NOT NULL COMMENT '版本号',
  `throw_app` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '投放APP ',
  `activity_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'activityname（活动名称）',
  `pre_url` varchar(1024) NOT NULL DEFAULT '' COMMENT '投放URL',
  `charge_status` tinyint(2) NOT NULL DEFAULT '-1' COMMENT '计费状态',
  PRIMARY KEY (`id`),
  KEY `idx_fr_detail_id` (`fr_detail_id`),
  KEY `idx_agent_id` (`agent_id`),
  KEY `idx_fr_code` (`fr_code`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_fr_batch_id` (`fr_batch_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=6666780025 DEFAULT CHARSET=utf8 COMMENT='维度表'

2. 线上更新出现死锁的表为cm_cost_day”成本表“，目前线上数据为1463696，测试环境数据为40w+。建表语句如下：

CREATE TABLE `cm_cost_day` (
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `created` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `biz_id` bigint(20) NOT NULL COMMENT '业务唯一id',
  `biz_day` date NOT NULL DEFAULT '1971-01-01' COMMENT '日期',
  `agent_id` bigint(20) NOT NULL COMMENT '企业id',
  `category_id` bigint(20) NOT NULL COMMENT '渠道分类id',
  `sub_category_id` bigint(20) NOT NULL COMMENT '渠道子类id',
  `channel_id` bigint(20) NOT NULL COMMENT '渠道id',
  `dimension` tinyint(2) NOT NULL COMMENT '粒度',
  `dimension_value` varchar(255) NOT NULL DEFAULT '' COMMENT '粒度值',
  `settle_point` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '结算点',
  `expect_settle_amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '预计结算金额',
  PRIMARY KEY (`id`),
  KEY `idx_agent_id` (`agent_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_sub_category_id` (`sub_category_id`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_day` (`biz_day`)
) ENGINE=InnoDB AUTO_INCREMENT=815892 DEFAULT CHARSET=utf8 COMMENT='成本表'

二、“维度表”慢SQL的具体分析，优化思路与方案

1.问题慢SQL如下：

select distinct creator from cm_fr_detail WHERE
agent_id in
(1,2,3,4,5,6,7,8,9,10,........)约有500+值
and media_id in
(1,2,3,4,5,2121,50,5415..........)约有1000+值
order by creator asc;

(in值太多，因篇幅有限，无法将where条件中in的所有值写到文章中)

线上执行速度在2s 左右

2.慢SQL原因分析

在未优化前该SQL的线上执行计划如下：

观察该执行计划，该SQL慢的原因大体如下：

1. CBO判断走了全表扫描（线上数据150W+）。
2. 并且，在将全表数据分批