1. 问题分析
智能刊登gaussdb 慢sql 排名第4，98次/小时 平均时间：16秒
In 子查询元素大于10
2.解决方案
语句优化建议：
发现IN表达式包含元素超过10，请慎用，因为元素过多会导致全表扫描。
建议按《sql优化军规》改为临时表后join
3.原sql
SELECT *
FROM (
SELECT SiteCode, SKU, CategoryId, ROW_NUMBER() OVER (PARTITION BY SiteCode, SKU ORDER BY LastModificationTime DESC) AS rno
FROM M_Ebay_Listing
WHERE IsAutoPublish = ?
AND SiteCode = ?
AND SKU IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
AND LENGTH(CategoryId) > ?
AND CategoryId NOT IN (
SELECT CategoryId
FROM bd_prohibitionsale_category
WHERE SiteCode = ?
AND MarketId = ?
)
) a
WHERE rno = ?;
