这里有一个慢 SQL 查询等你来优化

林老师带你学编程

发布于 2020-06-24 11:13:11

5460

发布于 2020-06-24 11:13:11

文章被收录于专栏：强仔仔

对于大部分系统，慢SQL优化都是一个必不可少的环节，一般慢SQL都是因为多表联查造成的，如果加索引能解决问题，那当然是最好的，但是大多数情况下，并不是索引的问题，比如下面老师要给童靴们分析的场景。

慢SQL

SELECT
	t1.Id,
	t1.AsinId,
	t2.Img AS 'ImagePath',
	t1.Asin,
	t1.Sku,
	t1.Site,
	t2.Title,
	t2.FromUrl,
	t2.Brand,
	t1.AccountId,
	t2.DspStatus,
	t1.AccountName,
	t2.BsrRank,
	t2.BsrCategory,
	t2.Review AS 'ReviewScore',
	t2.ReviewCount,
	t2.VpReviewCount,
	t2.VpReviewScore,
	t2.ActualReviewCount,
	t2.AsinReviewScore,
	t1.Price,
	t1.PriceUsd,
	t1.AvailableDays,
	t2.IsAddOn,
	t2.IsReviewMonitor,
	t1.ShipType AS 'IsFba',
	t1.Inbound,
	t1.WorkInbound,
	t1.ShipInbound,
	t1.Available,
	t1.Unvailable,
	t1.ProductGroup,
	t2.VariantType,
	t1.Reserved,
	t1.ReservedTransfersQty,
	t1.ReservedProcessingQty,
	t1.CustomerOrdersReservedQty,
	t2.KeywordCount,
	t5.Impressions,
	t5.Clicks,
	t5.TotalSpend,
	t5.TotalSpendUsd,
	t5.AdSales,
	t5.AdSalesUsd,
	ROUND( t5.TotalSpend * 100 / t5.AdSales, 2 ) AS 'Acos',
	ROUND( t5.AdSales * 100 / t5.Sales, 2 ) AS 'AsoAs',
	ROUND( t5.TotalSpend * 100 / t5.Sales, 2 ) AS 'ACoAS',
	t5.Sales,
	t5.SalesUsd,
	t5.Quantity,
	t5.Orders,
	t5.CostsUsd,
	t5.ProfitsUsd,
	t5.Costs,
	t5.Profits,
	t5.ProfitsRate,
	t1.HasAd AS 'IsSetAds',
	t5.TotalIncome,
	t5.TotalIncomeUsd 
FROM
	t_amadb_product t1
	LEFT JOIN t_amz_product_info t2 ON t1.AsinId = t2.Id
	LEFT JOIN (
SELECT
	ProductId,
	SUM( Orders ) AS 'Orders',
	SUM( Sales ) AS 'Sales',
	SUM( SalesUsd ) AS 'SalesUsd',
	SUM( Quantities ) AS 'Quantity',
	sum( Costs ) AS 'Costs',
	sum( CostsUsd ) AS 'CostsUsd',
	sum( Profits ) AS 'Profits',
	sum( ProfitsUsd ) AS 'ProfitsUsd',
	ROUND( sum( Profits ) * 100 / ( SUM( Sales ) ), 2 ) AS 'ProfitsRate',
	SUM( Impressions ) AS 'Impressions',
	SUM( Clicks ) AS 'Clicks',
	SUM( Spend ) AS 'TotalSpend',
	SUM( SpendUsd ) AS 'TotalSpendUsd',
	SUM( AdSales ) AS 'AdSales',
	SUM( AdSalesUsd ) AS 'AdSalesUsd',
	sum( TotalIncome ) AS 'TotalIncome',
	sum( TotalIncomeUsd ) AS 'TotalIncomeUsd' 
FROM
	t_amzdb_product_day_sales 
WHERE
	UserId = 100000 
	AND AccountId IN ( 1, 2, 3, 4 ) 
	AND Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
	AND BelongDate >= '2020-06-15' 
	AND BelongDate < '2020-06-16' 
GROUP BY
	ProductId 
	) t5 ON t1.Id = t5.ProductId 
WHERE
	t1.UserId = 100000 
	AND t1.AccountId IN ( 1, 2, 3, 4 ) 
	AND t1.Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
	AND ( t1.Available > 0 OR t1.Reserved > 0 OR t1.NewOutOrderDate >= '2020-06-09' ) 
ORDER BY
	t5.Quantity DESC,
	t1.Available DESC 
	LIMIT 0,10

这条SQL总共涉及三个表的连接查询，这条SQL执行时间大概4-5s，这个时间对用户而言，已经算慢了，我们先通过EXPLAIN来分析一下这条SQL执行计划，结果如下所示：

如果对EXPLAIN语法不了的童靴，可以看下面所示的思维导图，这边老师就不做过多的解释了。

例子的中的慢SQL最核心的问题其实在ORDER BYt5.Quantity DESC上面，从执行计划中，我们可以得到慢的原因是因为：查询出来的结果又需要进行排序操作，10w多的数据进行排序，最后自然而然查询速度会很慢。

MySQL版本：

MySQL5.7
4核8000MB内存

这条SQL之所以难优化，是因为它已经用到索引了，但是因为用户对应的数据量实在太大了，导致order by排序时间过长，这个时候不管怎么加索引都是没用的。

慢SQL优化

那这种情况我们该怎么办呢？这种情况就算分库分表也没用，因为业务需要将这么多数据查询出来，然后进行order by排序。

这条SQL慢的核心是因为需要对Quantity字段进行排序，Quantity字段来自t_amzdb_product_day_sales表，通过EXPLAIN对t_amzdb_product_day_sales查询进行分析，发现t_amzdb_product_day_sales的查询速度非常快，只有0.3s不到。

1	SIMPLE	t_amzdb_product_day_sales	t_amzdb_product_day_sales_2020_06	range	account_product_date,belong_date,site,userid,productId_belongDate,uab,productId,userId_asinId,ua_belongDate	ua_belongDate	11		455	100	Using index condition; Using where; Using temporary; Using filesort

得到这个结论，我们就可以对SQL进行拆分，将原本一条SQL修改为多条SQL，比如上面案例的SQL，我们可以用多个SQL来实现。

首先我们将需要排序的字段通过如下所示的SQL查询出来

SELECT
t1.ProductId,
t2.Available,
SUM( Quantities ) AS 'Quantity'
FROM
t_amzdb_product_day_sales t1
LEFT JOIN t_amadb_product t2 ON  t2.Id = t1.ProductId
WHERE
        t1.UserId = 100000 
	    AND t1.AccountId IN ( 1, 2, 3, 4 ) 
        AND t1.Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
        AND t1.BelongDate >= '2020-06-15' 
        AND t1.BelongDate < '2020-06-16' 
GROUP BY
t1.ProductId 
ORDER BY
Quantity DESC,
t2.Available DESC 
limit 10

将查询出来ProductId，通过下面一条组合起来，如果ProductId有10条，则通过下面一条SQL就可以全部查询出来，如果ProductId不满10条，则还需要第三条SQL将后续的数据查询出来。

第二条SQL

SELECT
	t1.Id,
	t1.AsinId,
	t2.Img AS 'ImagePath',
	t1.Asin,
	t1.Sku,
	t1.Site,
	t2.Title,
	t2.FromUrl,
	t2.Brand,
	t1.AccountId,
	t2.DspStatus,
	t1.AccountName,
	t2.BsrRank,
	t2.BsrCategory,
	t2.Review AS 'ReviewScore',
	t2.ReviewCount,
	t2.VpReviewCount,
	t2.VpReviewScore,
	t2.ActualReviewCount,
	t2.AsinReviewScore,
	t1.Price,
	t1.PriceUsd,
	t1.AvailableDays,
	t2.IsAddOn,
	t2.IsReviewMonitor,
	t1.ShipType AS 'IsFba',
	t1.Inbound,
	t1.WorkInbound,
	t1.ShipInbound,
	t1.Available,
	t1.Unvailable,
	t1.ProductGroup,
	t2.VariantType,
	t1.Reserved,
	t1.ReservedTransfersQty,
	t1.ReservedProcessingQty,
	t1.CustomerOrdersReservedQty,
	t2.KeywordCount,
	t5.Impressions,
	t5.Clicks,
	t5.TotalSpend,
	t5.TotalSpendUsd,
	t5.AdSales,
	t5.AdSalesUsd,
	ROUND( t5.TotalSpend * 100 / t5.AdSales, 2 ) AS 'Acos',
	ROUND( t5.AdSales * 100 / t5.Sales, 2 ) AS 'AsoAs',
	ROUND( t5.TotalSpend * 100 / t5.Sales, 2 ) AS 'ACoAS',
	t5.Sales,
	t5.SalesUsd,
	t5.Quantity,
	t5.Orders,
	t5.CostsUsd,
	t5.ProfitsUsd,
	t5.Costs,
	t5.Profits,
	t5.ProfitsRate,
	t1.HasAd AS 'IsSetAds',
	t5.TotalIncome,
	t5.TotalIncomeUsd 
FROM
	t_amadb_product t1
	LEFT JOIN t_amz_product_info t2 ON t1.AsinId = t2.Id
	LEFT JOIN (
SELECT
	ProductId,
	SUM( Orders ) AS 'Orders',
	SUM( Sales ) AS 'Sales',
	SUM( SalesUsd ) AS 'SalesUsd',
	SUM( Quantities ) AS 'Quantity',
	sum( Costs ) AS 'Costs',
	sum( CostsUsd ) AS 'CostsUsd',
	sum( Profits ) AS 'Profits',
	sum( ProfitsUsd ) AS 'ProfitsUsd',
	ROUND( sum( Profits ) * 100 / ( SUM( Sales ) ), 2 ) AS 'ProfitsRate',
	SUM( Impressions ) AS 'Impressions',
	SUM( Clicks ) AS 'Clicks',
	SUM( Spend ) AS 'TotalSpend',
	SUM( SpendUsd ) AS 'TotalSpendUsd',
	SUM( AdSales ) AS 'AdSales',
	SUM( AdSalesUsd ) AS 'AdSalesUsd',
	sum( TotalIncome ) AS 'TotalIncome',
	sum( TotalIncomeUsd ) AS 'TotalIncomeUsd' 
FROM
	t_amzdb_product_day_sales 
WHERE
	UserId = 100000 
	AND AccountId IN ( 1, 2, 3, 4 ) 
	AND Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
	AND BelongDate >= '2020-06-15' 
	AND BelongDate < '2020-06-16' 
GROUP BY
	ProductId 
	) t5 ON t1.Id = t5.ProductId 
WHERE
	t1.UserId = 100000 
	AND t1.AccountId IN ( 1, 2, 3, 4 ) 
	AND t1.Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
	AND ( t1.Available > 0 OR t1.Reserved > 0 OR t1.NewOutOrderDate >= '2020-06-09' ) 
	AND t1.Id in ('上条SQL查询出来的ProductId值')
ORDER BY
	t5.Quantity DESC,
	t1.Available DESC 
	LIMIT 0,10

第三条SQL

SELECT
	t1.Id,
	t1.AsinId,
	t2.Img AS 'ImagePath',
	t1.Asin,
	t1.Sku,
	t1.Site,
	t2.Title,
	t2.FromUrl,
	t2.Brand,
	t1.AccountId,
	t2.DspStatus,
	t1.AccountName,
	t2.BsrRank,
	t2.BsrCategory,
	t2.Review AS 'ReviewScore',
	t2.ReviewCount,
	t2.VpReviewCount,
	t2.VpReviewScore,
	t2.ActualReviewCount,
	t2.AsinReviewScore,
	t1.Price,
	t1.PriceUsd,
	t1.AvailableDays,
	t2.IsAddOn,
	t2.IsReviewMonitor,
	t1.ShipType AS 'IsFba',
	t1.Inbound,
	t1.WorkInbound,
	t1.ShipInbound,
	t1.Available,
	t1.Unvailable,
	t1.ProductGroup,
	t2.VariantType,
	t1.Reserved,
	t1.ReservedTransfersQty,
	t1.ReservedProcessingQty,
	t1.CustomerOrdersReservedQty,
	t2.KeywordCount,
	t1.HasAd AS 'IsSetAds'
FROM
	t_amadb_product t1
	LEFT JOIN t_amz_product_info t2 ON t1.AsinId = t2.Id
WHERE
    t1.UserId = 100000 
	AND t1.AccountId IN ( 1, 2, 3, 4 ) 
	AND t1.Site IN ( 'ca', 'de', 'es', 'fr', 'it', 'mx', 'uk', 'us', 'jp' ) 
	AND ( t1.Available > 0 OR t1.Reserved > 0 OR t1.NewOutOrderDate >= '2020-06-09' ) 
ORDER BY
	t1.Available DESC 
	LIMIT 0,剩余个数

虽然QPS从1变成2/3，但是因为每一条的SQL执行时间都特别短，所以总体时间还是在一秒以内，在系统允许范围之内，而且相对于MySQL的cpu、io消耗上面都会小很多。

总结

遇到这种SQL，一般只能通过业务角度，将原本一条的SQL修改为多条，但是需要注意的是多条SQL的效果要等价于原本SQL，否则就是改bug给自己。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/06/22 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql