SQL数据量才32W条，查询就已经达到将近10S，请问如何优化？

qq_33646435 2018-08-08 10:42:42
1、分页查询（每次查询10条数据，平均耗时6S）
SELECT *
FROM (
SELECT i.id, i.Url, i.ServicePath, p.ProductName, s.Name AS sName
, p.Brand, p.ASIN, p.Country, p.Price
, (ROW_NUMBER() OVER (ORDER BY i.Id) - 1) / 10 AS pagenum
FROM Product_Images i
LEFT JOIN Product p ON i.ProductGuid = p.ProductGuid
LEFT JOIN Store s ON p.StoreGuid = s.StoreGuid
WHERE 1 = 1
) t
WHERE pagenum = 1 - 1

2、查询全部（32.4W条数据，耗时23S）
select i.id, i.Url, i.ServicePath, p.ProductName, s.Name AS sName
, p.Brand, p.ASIN, p.Country, p.Price FROM Product_Images i
LEFT JOIN Product p ON i.ProductGuid = p.ProductGuid
LEFT JOIN Store s ON p.StoreGuid = s.StoreGuid

3、索引使用情况

...全文
277 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复 
SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

这条需要几秒？
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 4 楼 yenange 的回复:
--删除无用的索引。因为主键本身也是索引，没必要再建立

DROP INDEX IX_Product_Images ON Product_Images

DROP INDEX IX_Product ON Product



--连接字段增加索引

CREATE INDEX IX_Product_Images_ProductGuid ON  Product_Images(ProductGuid);

CREATE INDEX IX_Product_ProductGuid ON  Product(ProductGuid);

CREATE INDEX IX_Product_StoreGuid ON  Product(StoreGuid);

CREATE INDEX IX_Store_StoreGuid ON  Store(StoreGuid);


做完这些， 再看分页是否快了？

尴尬了，还是跟以前速度一样的
吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复 
--删除无用的索引。因为主键本身也是索引，没必要再建立

DROP INDEX IX_Product_Images ON Product_Images

DROP INDEX IX_Product ON Product



--连接字段增加索引

CREATE INDEX IX_Product_Images_ProductGuid ON  Product_Images(ProductGuid);

CREATE INDEX IX_Product_ProductGuid ON  Product(ProductGuid);

CREATE INDEX IX_Product_StoreGuid ON  Product(StoreGuid);

CREATE INDEX IX_Store_StoreGuid ON  Store(StoreGuid);


做完这些， 再看分页是否快了？
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 2 楼 yenange 的回复:
1. 贴出下面脚本的执行结果的截图。
EXEC sp_spaceused 'Product_Images'

EXEC sp_spaceused 'Product'

EXEC sp_spaceused 'Store'

EXEC sp_helpindex 'Product_Images'

EXEC sp_helpindex 'Product'

EXEC sp_helpindex 'Store'


2. 贴一下你分页的执行计划的截图.


吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复
1. 贴出下面脚本的执行结果的截图。
EXEC sp_spaceused 'Product_Images'

EXEC sp_spaceused 'Product'

EXEC sp_spaceused 'Store'

EXEC sp_helpindex 'Product_Images'

EXEC sp_helpindex 'Product'

EXEC sp_helpindex 'Store'


2. 贴一下你分页的执行计划的截图.
丰云 2018-08-08
  • 打赏
  • 举报
 回复
分页？？？？？

分页在那里？？？？？？
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 12 楼 yenange 的回复:
[quote=引用 11 楼 qq_33646435 的回复:]
[quote=引用 10 楼 yenange 的回复:]
DECLARE @pageIndex INT,@pageSize INT

SET @pageIndex=1

SET @pageSize=10



SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET (@pageIndex-1)*@pageSize ROWS

FETCH NEXT @pageSize ROWS ONLY;

很感谢你！！！[/quote]
没事就结贴吧， [/quote]
结贴了！分数给你了，很感谢你！
吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复
引用 11 楼 qq_33646435 的回复:
[quote=引用 10 楼 yenange 的回复:]
DECLARE @pageIndex INT,@pageSize INT

SET @pageIndex=1

SET @pageSize=10



SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET (@pageIndex-1)*@pageSize ROWS

FETCH NEXT @pageSize ROWS ONLY;

很感谢你！！！[/quote]
没事就结贴吧，
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 10 楼 yenange 的回复:
DECLARE @pageIndex INT,@pageSize INT

SET @pageIndex=1

SET @pageSize=10



SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET (@pageIndex-1)*@pageSize ROWS

FETCH NEXT @pageSize ROWS ONLY;

很感谢你！！！
吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复 
DECLARE @pageIndex INT,@pageSize INT

SET @pageIndex=1

SET @pageSize=10



SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET (@pageIndex-1)*@pageSize ROWS

FETCH NEXT @pageSize ROWS ONLY;
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 8 楼 yenange 的回复:
[quote=引用 7 楼 qq_33646435 的回复:]
[quote=引用 6 楼 yenange 的回复:]
SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

这条需要几秒？
这条不需要时间，很快执行。0秒[/quote]
这种是新式的分页， 就按这种来吧。

row_number 分页按道理也不慢， 你再试下这种看看：
SELECT * FROM (

	SELECT i.id,

			i.Url,

			i.ServicePath,

			p.ProductName,

			s.Name AS sName,

			p.Brand,

			p.ASIN,

			p.Country,

			p.Price,

			ROW_NUMBER() OVER (ORDER BY id) AS rid

	FROM   Product_Images i

			LEFT JOIN Product p

				ON  i.ProductGuid = p.ProductGuid

			LEFT JOIN Store s

				ON  p.StoreGuid = s.StoreGuid

	WHERE  1 = 1

) AS t

WHERE t.rid>0 AND t.rid<=10
[/quote]
恩，谢谢你第二张也快，0S查询出来数据。但是想请教你个问题：
第一种你说的新式分页，怎么定义第几页呢？（FETCH NEXT 10 ROWS ONLY 是查询10条数）
假如我每页显示10条 想显示第三页（21~30条）的数据，要怎么修改？
吉普赛的歌 2018-08-08
  • 打赏
  • 举报
 回复
引用 7 楼 qq_33646435 的回复:
[quote=引用 6 楼 yenange 的回复:]
SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

这条需要几秒？
这条不需要时间，很快执行。0秒[/quote]
这种是新式的分页， 就按这种来吧。

row_number 分页按道理也不慢， 你再试下这种看看：
SELECT * FROM (

	SELECT i.id,

			i.Url,

			i.ServicePath,

			p.ProductName,

			s.Name AS sName,

			p.Brand,

			p.ASIN,

			p.Country,

			p.Price,

			ROW_NUMBER() OVER (ORDER BY id) AS rid

	FROM   Product_Images i

			LEFT JOIN Product p

				ON  i.ProductGuid = p.ProductGuid

			LEFT JOIN Store s

				ON  p.StoreGuid = s.StoreGuid

	WHERE  1 = 1

) AS t

WHERE t.rid>0 AND t.rid<=10
qq_33646435 2018-08-08
  • 打赏
  • 举报
 回复
引用 6 楼 yenange 的回复:
SELECT i.id,

        i.Url,

        i.ServicePath,

        p.ProductName,

        s.Name AS sName,

        p.Brand,

        p.ASIN,

        p.Country,

        p.Price

FROM   Product_Images i

        LEFT JOIN Product p

            ON  i.ProductGuid = p.ProductGuid

        LEFT JOIN Store s

            ON  p.StoreGuid = s.StoreGuid

WHERE  1 = 1

ORDER BY i.id 

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

这条需要几秒？
这条不需要时间，很快执行。0秒
MaxCompute SQL查询优化解析
MaxCompute是阿里云的大数据解决方案，它在跨数据中心的超大规模分布式系统上提供多种编程...这个分享会专注于SQL领域，介绍MaxCompute SQL近的发展，如何利用基于代价的优化器以及代码生成技术大幅提高了SQL查询效率。
mysql千万级数据分页查询性能优化
因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。本文给大家分享的是作者在使用mysql进行千万级数据量分页查询的时候进行性能优化的方法，对我们学习mysql性能优化非常有帮助。2）查询的时候建议使用覆盖索引，非必要字段不返回同样有利于查询速度提升，在写SQL的时候要合理利用索引去优化。我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。1）分页查询每页的数据量越大查询效果越差，所以分页的时候应该控制每页的数据量。.
基于mysql大数据量limit查询优化
今天一个业务场景，定时任务同步全量的历史数据。Java中用一个list去接收sql查询返回的数据。想到list的存储及sql性能瓶颈，于是就需要对数据进行分页查询。 使用得mysql数据，在查询数据库中40w数据时，发现耗时将近4min。其中230s是用在网络传输。 那么此时就需要分页查询。分页查询逻辑也有效率高低，比较好的方法 通过id进行between查询。这种局限于id为自增的表。并且它的每条数据内容都是固定的。如果中间有其实id不是连续的，那么中间这个基数就很难定义。比如我数据库最小的id
深页查询优化
针对上面的问题，看了一下发现目前的后台订单查询sql，每一次查询，不管是翻页和不翻页都会把所有的数据查询出来，但其实后台管理页面只展示10条数据，根据不需要每次查询出所有数据返回给前端，这是一个主要问题，其次查询sql也可以进行一个优化，没有使用索引，也没有限制每次查询的数据量。由于我们的是订单系统，每天产生的订单也特别的大，造成后台查询订单的时候特别慢，存在一个深页查询的问题，现在后台管理页面查询近一个月订单的时候将近10s左右。然后这里查询出来当页展示的数据之后，我们还得把数据的总量查询出来。
oracle数据库慢查询SQL
SQL语句文本不同：即使SQL语句的逻辑相同，但如果SQL语句的文本不同（比如空格、大小写等），Oracle也会将它们当作不同的SQL语句进行处理。绑定变量不同：如果SQL语句使用了绑定变量，即在SQL语句中使用了占位符，那么不同的绑定变量值会导致不同的SQL语句被加入到共享池中。不同的解析环境：在不同的解析环境下，相同的SQL语句可能会被多次解析并加载到共享池中，比如在不同的会话或者不同的数据库连接中。但是分页查询总数的sql语句执行五次，5.932s，3.78s，2.89s, 2.5s，1.9s。
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章