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

豪AI冰

已于 2024-09-07 11:06:56 修改

阅读量1.2k

点赞数 16

分类专栏： SQL优化与索引设计文章标签： sql mysql 数据库

于 2024-09-06 10:02:59 首次发布

本文链接：https://blog.csdn.net/qq_42687692/article/details/141951487

版权

SQL优化与索引设计专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Author:Jason豪

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

两条问题SQL均涉及“日级数据上传表”，cm_settle_partner_day_data，该表目前的生产库数据量是1550926，每日新增数据量在4000左右，目前除主键外已有四个单列索引。

建表语句如下：

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`)
) ENGINE=InnoDB AUTO_INCREMENT=1041177 DEFAULT CHARSET=utf8 COMMENT='日级数据上传表'

二、两条sql的具体优化思路与方案

1.第一条SQL是一个单表查询，未优化前执行计划走了全表扫描，该SQL在生产库执行时间是0.5秒，线上3秒。最先考虑可以建立一个三字段的联合索引，该三字段均在该SQL中的where条件中，从索引中取出目标数据行主键ID后，在回表取出查询数据。
具体SQL如下：

SELECT
	sum( cash_value )* 100 AS expectSettleAmount,
	agent_id AS agentId 
	from cm_settle_partner_day_data 
WHERE
	STATUS = 1 
	AND dimension_value IN ( 1, 3 ) 
	AND date_format( business_date, '%Y-%m' ) = '2020-07' 
GROUP BY
	agent_id;

更多考量后，该表是“日级数据上传表”，每天除了查询外，每日还有4000左右数据量的INSERT操作。如果不考虑INSERT操作，建立该查询SQL中涉及的五个字段的联合索引形成覆盖索引会是最优解，这样就可以直接从二级索引（新建的五个字段的覆盖索引）中查找出所有数据，省去回表操作，大大提升SQL执行效率，但是代价是五个字段的联合索引会很大（比三个字段的联合索引要大），除了日常MySQL数据库的维护，还会极大拖累INSERT与Update操作（因为每INSERT一条新数据到表时同时也会修改索引B+TREE数据结构），需要在这两个方案中做个权衡。SQL调优和索引使用的本质就是在查询与修改数据之间的权衡。
建立索引语句：
建立where条件的三列索引：

ALTER table cm_settle_partner_day_data add INDEX three_sdb_index  (`status `,`dimension_value`,`business_date`);

建立where条件与查询数据的全索引：

ALTER table fpd_icash_hk_dev_a.cm_settle_partner_day_data 
add INDEX five_sdbac_index (`status`,`dimension_value`,`business_date`,`agent_id`,`cash_value`);

注意：建立联合索引时，要遵循联合索引的最左匹配使用原则，为了最大提升联合索引的使用效率与频率，将where条件中的字段优先放在左边（SQL的执行会先过滤where条件然后拿数据，sql语句的执行顺序from-where-groupby-having-select-orderby-limit），然后再将查询select字段依次放入。
将最频繁使用的字段放在联合索引建立时的最左边开始处，然后依据字段使用频率依次安排。
安插知识点：单列索引和联合索引的数据结构分别是什么样的？B+TREE数据结构在查询数据时如何增大查询效率？

2.第二条SQL是一个多表级联查询SQL，涉及到5张表的多表级联查询，均使用INNER JOIN级联，cm_settle_partner_day_data（1550926），cm_channel_admittance_form(923),cm_channel（1528），cm_sub_category（50），cm_category（21），目前生产库执行时间是1.7秒左右，线上执行时间是5秒左右。除主表外，各个级联表中也有常用单列索引。
原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,
	ch.channel_name channel_name  FROM  cm_settle_partner_day_data p
	INNER JOIN cm_channel_admittance_form a ON ( a.channel_id = p.channel_id AND a.agent_id = p.agent_id )
	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 
	AND p.agent_id = 296 
	AND '2020-07' = date_format( business_date, '%Y-%m' ) 
	AND p.STATUS = 1 
	AND p.dimension_value IN ( 1, 3 ) 
GROUP BY
	p.channel_id,
	ch.channel_name;

最初该SQL的执行计划显示查询均有走索引，但是都走的单列索引，因为该SQL涉及的字段较多，很多查询结果与where条件中的字段均没有索引可走，导致SQL执行计划与实际SQL执行效果不符，SQL很慢。最初执行计划截图如下：
在这里插入图片描述

多次sql语法改写尝试，发现除去where条件后的前三个字段条件后

p.agent_id = 296 
	AND '2020-07' = date_format( business_date, '%Y-%m' ) 
	AND p.STATUS = 1

SQL的查询效率会大幅提升（从1.7秒上升到0.6秒），随即决定，将主表外的其余四张级联表单独组合级联成一张临时表，并把where条件中的前三个条件归入临时表的查询中，完成SQL改写后，执行SQL，发现速度并没有快，查看SQL执行计划，发现主表仍走了效率极差的单列索引idx_channel_id，这是问题所在。（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 
	,
	(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;

在这里插入图片描述

因为此时改写后的SQL的where条件中，均使用了主表cm_settle_partner_day_data的字段，考虑到该SQL查询使用的列较多，不易建联合索引（这样的联合索引会特别大，随着数据量的增多，一定会导致问题），所以考虑新建一个where条件中涉及到字段的五列索引，又考虑到此次SQL优化涉及两条线上业务sql，索引建议建立第一条sql中的全列优化索引，一条联合索引优化两条SQL。

ALTER table fpd_icash_hk_dev_a.cm_settle_partner_day_data 
add INDEX five_sdbac_index (`status`,`dimension_value`,`business_date`,`agent_id`,`cash_value`);

建立好索引并测试后，sql查询速度并没有提升，在看SQL的执行计划，主表仍然走了idx_channel_id单列索引，这是MySQL的CBO判断错误导致为SQL选择了一条错误的执行计划，从而导致sql执行效率慢。此处，我又使用了
FORCE INDEX (five_test_index) 语法，强制该条sql走新建的五列联合索引，再次执行sql后，从1.7秒提升到了0.015秒，再次查看改写后的sql执行计划，最新改写的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 (five_test_index)
	,
	(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;

在这里插入图片描述

三、最终结果

在生产库建立五列联合索引，并引用SQL的新语法后，第二条级联SQL从原来的线上5秒生产库2秒，查询速度提升到0.03秒。而第一条SQL也从线上3秒提升到了0.02秒。查看两条SQL的执行计划，均走新建的联合索引。

四、后记

在最终上线的索引版本时，有了更多业务上的考量，第二条SQL的查询效率高低远比第一条重要，所以最终上线的SQL索引版本中的最后字段改为了channel_id，最大方便第二条SQL中where条件的索引使用，放弃了第一条SQL的全列覆盖索引最优解的使用。

ALTER table fpd_icash_hk_dev_a.cm_settle_partner_day_data 
add INDEX five_sdbac_index (`status`,`dimension_value`,`business_date`,`agent_id`,` channel_id `);

在本次SQL优化过程中，曾考虑将business_date放在联合索引的最左边（因为该字段比较常用），但导致SQL一直走全表扫描，不再走索引即使加了force index的语法也无济于事，分析原因，大概率是business_date的值比较多，以该字段为首列建立联合索引，会导致二级B+Tree索引过大CBO会判断走全表扫描会更快（事实也确实如此, 线上每条business_date值都不同，有150w条左右）。

建立联合索引时，字段的位置很重要，要详细了解索引底层各类B+Tree数据结构的实现与使用以及SQL的执行机制，即可做出精准判断。