记一次一头雾水的sql调优(多字段表)

最新推荐文章于 2021-06-23 17:38:57 发布

tangwanggong

最新推荐文章于 2021-06-23 17:38:57 发布

阅读量127

点赞数

分类专栏：菜鸡互啄文章标签： mysql

本文链接：https://blog.csdn.net/tangwanggong/article/details/107605868

版权

菜鸡互啄专栏收录该内容

2 篇文章 0 订阅

订阅专栏

入职新公司,接手一个旧功能,发现一个接口请求需要8s左右,定位到是sql问题,业务为根据AdId查询aCost,bCost,cCost的合值与比例,耗时7s左右,其中aCost,bCost,cCost的数据类型为decimal,原sql如下

 SELECT AdId adId, 
		SUM(aCost) aCost, 
		SUM(bCost) bCost ,
		SUM(cCost) cCost, 
		ROUND(SUM(aCost) / SUM(cCost), 4 ) ac,
		ROUND(SUM(bCost) / SUM(cCost), 4 ) bc
		FROM t_report WHERE AdId IN (
		193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 181, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 168, 167, 166, 165, 164
	) and cDate>='2010-02-29' and cDate<='2030-03-04' GROUP BY AdId

t_report这张表的数据量在50w左右,最开始认为可以查询条件进行优化,可以把AdId与cDate创建联合索引,进行优化时间,发现这个索引已经被创建了,所以问题不在这里,最后发现t_report 这张表存在80多个字段,所以想到查询时因为涉及到回表操作,所以在回表时,因为字段过多,所以会很耗时,于是创建AdId,cDate,aCost,bCost,cCost的联合索引,耗时直接到了1s以内,但是我发现t_report这张表因为很多业务在使用,这种创建联合索引的方式并不合适,不可能把每个业务都建立对应索引,是一种治标不治本的做法。于是想到利用临时表的方式先把AdId,aCost,bCost,cCost查询出,然后再进行聚合查询,具体sql如下

 SELECT AdId adId, 
		SUM(aCost) aCost, 
		SUM(bCost) bCost ,
		SUM(cCost) cCost, 
		ROUND(SUM(aCost) / SUM(cCost), 4 ) ac,
		ROUND(SUM(bCost) / SUM(cCost), 4 ) bc from(
        select AdId,aCost,bCost,cCost
		FROM t_report WHERE AdId IN (
		193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 181, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 168, 167, 166, 165, 164
	) and cDate>='2010-02-29' and cDate<='2030-03-04') tmp GROUP BY tmp.AdId

这条sql的执行时间在1s左右,在不创建索引的情况下已经达到预期。用explain查看sql的执行计划,发现临时表的查询并没有使用上索引,因为AdId与cDate已经被前人创建过联合索引,觉得非常奇怪,于是使用force index 强制使用索引,sql如下

 SELECT AdId adId, 
		SUM(aCost) aCost, 
		SUM(bCost) bCost ,
		SUM(cCost) cCost, 
		ROUND(SUM(aCost) / SUM(cCost), 4 ) ac,
		ROUND(SUM(bCost) / SUM(cCost), 4 ) bc from(
        select AdId,aCost,bCost,cCost FROM t_report 
        force index (IDX_ADID_CDATE)
        WHERE AdId IN (
		193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 181, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 168, 167, 166, 165, 164
	) and cDate>='2010-02-29' and cDate<='2030-03-04') tmp GROUP BY tmp.AdId

但sql的执行时间为3s左右,比全表查询更慢了。这正是一头雾水的地方,可能涉及到MySQL自身对sql的优化，这点查询过很多文档并没有找到答案。

整个sql的优化过程如上,问题主要为t_report字段过多,后续要对该表进行拆分操作。

后记:在正式环境运行之后,发现此次sql优化作用不大,后续发现开发版本MySQL版本5.6,正式环境为5.7,同一条sql执行计划都不相同,强制使用索引效果又不一。

路漫漫其修远兮