大家好,我是全栈小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;
解释
-
CTE(公共表表达式)定义:我们创建了一个名为
EmployeeRank
的 CTE,其中包含ROW_NUMBER()
函数来为每一行分配一个行号。排序规则是按Salary
列降序排列。 -
分页查询:在外部查询中,我们通过
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 行记录。
OFFSET
和FETCH
是 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】新手一分钟看懂在已有表基础上修改字段默认值和数据类型
总结:温故而知新,不同阶段重温知识点,会有不一样的认识和理解,博主将巩固一遍知识点,并以实践方式和大家分享,若能有所帮助和收获,这将是博主最大的创作动力和荣幸。也期待认识更多优秀新老博主。