1. 问题分析
次问题为宋宇提交的慢sql优化问题，分析发现，2个子查询 大表，查询后在inner join ,分析器提示缺少索引，查询比较慢需25秒，
2.解决方案
A.优化后的sql ，将INNER JOIN 改为 EXISTS， 测试查询从25秒提高到1秒不到 ，已改造完成
B.此类问题可以参考将INNER JOIN 改为 EXISTS的方案，提高查询速度
--select count(0) from [RB_Product_Image] nolock --40416416
--select count(0) from [RB_Product] nolock --2190570
SELECT TOP 20 [Id] AS [Id],
[CustomerID] AS [CustomerId],
[SKU] AS [Sku],
[ImageUrl] AS [Url],
[ImageType] AS [Type],
[FileSize] AS [FileSize],
[Status] AS [Status],
[IsCover] AS [IsMain],
[IsSpareCover] AS [IsSpareMain],
[IsWhiteBackGround] AS [IsWhiteBackground],
[IsFigure] AS [IsAttach],
[SortBy] AS [Sort],
[AddTime] AS [AddTime]
FROM [RB_Product_Image] i
WHERE EXISTS (SELECT 1
FROM [RB_Product] p
WHERE i.sku = p.sku
AND p.[ImageUpdateTime] BETWEEN '2022-01-17 13:45:03' AND '2022-05-17 15:45:03')
3.sql
SELECT
top 20
*
FROM
(
SELECT
[i].[Id] AS [Id],
[i].[CustomerId] AS [CustomerId],
[i].[Sku] AS [Sku],
[i].[Url] AS [Url],
[i].[Type] AS [Type],
[i].[FileSize] AS [FileSize],
[i].[Status] AS [Status],
[i].[IsMain] AS [IsMain],
[i].[IsSpareMain] AS [IsSpareMain],
[i].[IsWhiteBackground] AS [IsWhiteBackground],
[i].[IsAttach] AS [IsAttach],
[i].[Sort] AS [Sort],
[i].[AddTime] AS [AddTime],
[p].[UpdateTime] AS [UpdateTime]
FROM
(
SELECT
[Id] AS [Id],
[CustomerID] AS [CustomerId],
[SKU] AS [Sku],
[ImageUrl] AS [Url],
[ImageType] AS [Type],
[FileSize] AS [FileSize],
[Status] AS [Status],
[IsCover] AS [IsMain],
[IsSpareCover] AS [IsSpareMain],
[IsWhiteBackGround] AS [IsWhiteBackground],
[IsFigure] AS [IsAttach],
[SortBy] AS [Sort],
[AddTime] AS [AddTime]
FROM
[RB_Product_Image] WITH ( NOLOCK )
WHERE
( [CustomerID] = 1 )
) i
INNER JOIN (
SELECT
[CustomerID] AS [CustomerID],
[SKU] AS [SKU],
[UpdateTime] AS [UpdateTime]
FROM
[RB_Product] WITH ( NOLOCK )
WHERE
( [CustomerID] = 1 )
AND ( [ImageUpdateTime] BETWEEN '2022-05-17 13:45:03' AND '2022-05-17 15:45:03' )
) p ON ( ( [i].[CustomerId] = [p].[CustomerID] ) AND ( [i].[Sku] = [p].[SKU] ) )
) MergeTable
ORDER BY
Id ASC;
--select count(0) from [RB_Product_Image] nolock --40416416
--select count(0) from [RB_Product] nolock --2190570