【Sql Server】使用row_number over方式进行表分页,数据量达到五千多条记录后,查询变慢需要20多秒的解决方案

大家好,我是全栈小5,欢迎来到《小5讲堂》。
这是《Sql Server》系列文章,每篇文章将以博主理解的角度展开讲解。
温馨提示:博主能力有限,理解水平有限,若有不对之处望指正!

在这里插入图片描述

前言

最近创建了一张表,用于保存名称相关信息。
刚开始是没有加任何索引,数据不多时查询也没什么问题。
等到了表有5千多条记录后,查询变得很慢,设置需要二十多秒。
一起来看下这个博主是如何解决的?或者你们是否有更好的解决方案呢?也欢迎评论区留言。

单字段查询

刚开始给status字段设置索引,没效果。
直接再给time字段添加索引,有效果,查询秒出。

设置索引是占用一定物理空间大小,所以用物理空间大小还速度
1)单字段索引(适合单个字段排序或查询)
2)多字段索引(适合多个字段排序或查询)

【单字段查询】

-- CREATE INDEX time_index ON 目标表 (time) -- 设置表字段索引
select count(1) from 目标表

 select * 
 from (
     select row_number() 
     over(order by t.time) as rowindex,t.* 
     from (
         select * from 目标表 where status=10
     ) t
 ) new_table
 where rowindex>((1-1)*10) and rowindex<=1*10;

温馨提示:当你的表数据很多的时候,不建议在可视化工具进行索引设置。可通过sql语句的方式

CREATE INDEX 索引名 ON 目标表 (字段1,字段2.。。)

多字段查询

【多字段查询】
支持模糊查询,字段status和name字段组合索引,查询秒出

where status=10 and name like’%张%’

 select * 
 from (
     select row_number() 
     over(order by t.time) as rowindex,t.* 
     from (
         select * from 目标表 where status=10 and name like'%张%'
     ) t
 ) new_table
 where rowindex>((1-1)*10) and rowindex<=1*10;

知识点

在 SQL Server 中,ROW_NUMBER() 函数用于为结果集中的每一行分配一个唯一的顺序号。这是一个非常有用的函数,尤其是在分页查询中。以下是有关 ROW_NUMBER() 函数的一些基本说明:

基本语法

ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression) AS row_number
  • PARTITION BY partition_expression:可选项,用于将数据分成不同的组。对于每个组,ROW_NUMBER() 函数将重新开始计数。如果不使用 PARTITION BY,则对整个结果集应用计数。
  • ORDER BY order_expression:指定排序的列,ROW_NUMBER() 函数将根据这个排序规则分配行号。

分页查询示例

假设我们有一个员工表 Employees,包含以下字段:EmployeeID, Name, 和 Salary。我们希望对这个表进行分页查询,每页显示 10 条记录,且按薪资降序排序。可以使用 ROW_NUMBER() 函数来实现这一点。

示例 SQL 查询

WITH EmployeeRank AS (
    SELECT 
        EmployeeID,
        Name,
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employees
)
SELECT 
    EmployeeID,
    Name,
    Salary
FROM EmployeeRank
WHERE RowNum BETWEEN 11 AND 20;

解释

  1. CTE(公共表表达式)定义:我们创建了一个名为 EmployeeRank 的 CTE,其中包含 ROW_NUMBER() 函数来为每一行分配一个行号。排序规则是按 Salary 列降序排列。

  2. 分页查询:在外部查询中,我们通过 WHERE RowNum BETWEEN 11 AND 20 来提取第 2 页的数据(假设每页 10 条记录)。你可以根据需要调整 BETWEEN 的范围来获取不同页的数据。

注意事项

  • 性能:使用 ROW_NUMBER() 函数可能对性能有一定影响,尤其是在处理大型数据集时。确保对排序列进行适当的索引,以优化性能。

  • 偏移量和限制:在 SQL Server 2012 及以后的版本中,可以使用 OFFSET-FETCH 子句实现分页查询,这通常更简洁,也可以提高性能。示例如下:

    SELECT 
        EmployeeID,
        Name,
        Salary
    FROM Employees
    ORDER BY Salary DESC
    OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
    

    这个查询从第 11 行开始,取接下来的 10 行记录。OFFSETFETCH 是 SQL Server 2012 引入的分页功能,更加直观且高效。

文章推荐

【Sql Server】使用row_number over方式进行表分页,数据量达到五千多条记录后,查询变慢需要20多秒的解决方案

【Sql Server】随机查询一条表记录,并重重温回顾下自定义函数的封装和使用

【Sql Server】锁表如何解锁,模拟会话事务方式锁定一个表然后进行解锁

【Sql Server】通过Sql语句批量处理数据,使用变量且遍历数据进行逻辑处理

【新星计划回顾】第六篇学习计划-通过自定义函数和存储过程模拟MD5数据

【新星计划回顾】第四篇学习计划-自定义函数、存储过程、随机值知识点

【Sql Server】Update中的From语句,以及常见更新操作方式

【Sql server】假设有三个字段a,b,c 以a和b分组,如何查询a和b唯一,但是c不同的记录

【Sql Server】新手一分钟看懂在已有表基础上修改字段默认值和数据类型

总结:温故而知新,不同阶段重温知识点,会有不一样的认识和理解,博主将巩固一遍知识点,并以实践方式和大家分享,若能有所帮助和收获,这将是博主最大的创作动力和荣幸。也期待认识更多优秀新老博主。

评论 144
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

全栈小5

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值