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

客官°小女子只卖身不卖艺 2023-02-20 02:59 51阅读 0赞

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

![image][]

慢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执行计划，结果如下所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg_size_16_color_FFFFFF_t_70 1][]

例子的中的慢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

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

总结

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

**想要更多干货、技术猛料的孩子，快点拿起手机扫码关注我，我在这里等你哦~**

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zdTUxaWJiaWF5dnhzZEkwODVpY1JNWWljTDVFaWIwMVkxZ0drcTVNdWxMdXJ6T0QzU251V2oydzA5cjgwSnBTdm5xSTJtV2g0QUJ5RmhJTnZ1bHhwS2I2b1JBLzY0MD93eF9mbXQ9anBlZw][]

**林老师带你学编程**：[https://wolzq.com][https_wolzq.com]

[image]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly93d3cud29senEuY29tLzIwMjAwNjIxLzEwMDAucG5n?x-oss-process=image/format,png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200622190037421.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200622190101821.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoaXFpYW5nMDMxNg==,size_16,color_FFFFFF,t_70
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zdTUxaWJiaWF5dnhzZEkwODVpY1JNWWljTDVFaWIwMVkxZ0drcTVNdWxMdXJ6T0QzU251V2oydzA5cjgwSnBTdm5xSTJtV2g0QUJ5RmhJTnZ1bHhwS2I2b1JBLzY0MD93eF9mbXQ9anBlZw]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zdTUxaWJiaWF5dnhzZEkwODVpY1JNWWljTDVFaWIwMVkxZ0drcTVNdWxMdXJ6T0QzU251V2oydzA5cjgwSnBTdm5xSTJtV2g0QUJ5RmhJTnZ1bHhwS2I2b1JBLzY0MD93eF9mbXQ9anBlZw
[https_wolzq.com]: https://wolzq.com/