您当前的位置:首页 > 电脑百科 > 数据库 > SQL Server

SQL Server 百万数据查询优化技巧三十则

时间:2023-11-28 14:05:00  来源:微信公众号  作者:数据库干货铺

互联网时代的进程越走越深,使用MySQL的人也越来越多,关于MySQL的数据库优化指南很多,而关于SQL SERVER的T-SQL优化指南看上去比较少,近期有学习SQLSERVER的同学问到SQL SERVER数据库有哪些优化建议?本文列举了部分常见的优化建议,具体内容如下:

1、优化建议

索引优化:

eg:考虑一个订单表 Orders,其中有列 OrderDate 和 CustomerID。如果经常需要按订单日期范围和顾客ID进行查询,可以在这两列上建立复合索引,以提高查询性能。

NULL 值判断避免全表扫描:

eg:对于包含 status 列的用户表 Users,避免使用 SELECT * FROM Users WHERE status IS NULL,可以在设计表时设置 status 默认值,确保所有用户都有一个状态,然后使用 SELECT * FROM Users WHERE status = 0 进行查询。

!= 或 <> 操作符避免全表扫描:

eg:考虑一个产品表 Products,如果要查询所有不属于某个特定类别的产品,避免使用 SELECT * FROM Products WHERE CategoryID != 5,而是使用 SELECT * FROM Products WHERE CategoryID <> 5。

OR 连接条件避免全表扫描:

eg:对于一个学生成绩表 Grades,如果需要查询得分为 A 或 B 的记录,避免使用 SELECT * FROM Grades WHERE Grade = 'A' OR Grade = 'B',而是使用 SELECT * FROM Grades WHERE Grade = 'A' UNION ALL SELECT * FROM Grades WHERE Grade = 'B'。

IN 和 NOT IN 避免全表扫描:

eg:考虑一个员工表 Employees,如果需要查询属于某个特定部门的员工,避免使用 SELECT * FROM Employees WHERE DepartmentID IN (1, 2, 3),而是使用 SELECT * FROM Employees WHERE DepartmentID BETWEEN 1 AND 3。

LIKE 查询优化:

eg:在一个文章表 Articles 中,如果需要模糊查询标题包含关键词的文章,避免使用 SELECT * FROM Articles WHERE Title LIKE '%SQL%',可以考虑全文检索或者其他优化方式。

参数使用避免全表扫描:

eg:在一个订单表 Orders 中,如果需要根据输入的订单号查询订单信息,避免使用 SELECT * FROM Orders WHERE OrderID = @OrderID,可以使用强制索引的方式,如 SELECT * FROM Orders WITH(INDEX(OrderID_Index)) WHERE OrderID = @OrderID。

字段表达式操作避免全表扫描:

eg:在一个商品表 Products 中,如果需要查询价格除以2等于100的商品,避免使用 SELECT * FROM Products WHERE Price/2 = 100,可以改为 SELECT * FROM Products WHERE Price = 100*2。

字段函数操作避免全表扫描:

eg:在一个员工表 Employees 中,如果需要查询名字以"Smith"开头的员工,避免使用 SELECT * FROM Employees WHERE LEFT(LastName, 5) = 'Smith',可以改为 SELECT * FROM Employees WHERE LastName LIKE 'Smith%'。

不要在“=”左边进行函数、算术运算:

eg:在一个库存表 Inventory 中,避免使用 SELECT * FROM Inventory WHERE YEAR(StockDate) = 2023,而是使用 SELECT * FROM Inventory WHERE StockDate >= '2023-01-01' AND StockDate < '2024-01-01'。

索引字段顺序使用避免全表扫描:

eg:在一个订单表 Orders 中,如果有复合索引 (CustomerID, OrderDate),查询时应该先使用 CustomerID,如 SELECT * FROM Orders WHERE CustomerID = @CustomerID AND OrderDate BETWEEN @StartDate AND @EndDate。

避免写没有意义的查询:

eg:不建议使用 SELECT col1, col2 INTO #t FROM t WHERE 1 = 0,可以改为明确创建表结构并使用 CREATE TABLE #t (...)。

使用 EXISTS 代替 IN:

eg:在一个产品表 Products 中,避免使用 SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts),可以改为 SELECT * FROM Products WHERE EXISTS (SELECT 1 FROM DiscontinuedProducts WHERE ProductID = Products.ProductID)。

索引不一定对所有查询有效:

eg:在一个性别字段 Gender 几乎均匀分布的表中,对 Gender 建立索引可能不会提高查询效率。

索引数量谨慎选择:

eg:在一个订单表 Orders 中,不宜过多地在每个列上建立索引,需要根据查询和更新的具体需求进行权衡。

更新 clustered 索引数据列谨慎操作:

eg:在一个用户表 Users 中,如果频繁更新用户姓名,考虑是否将姓名列设为非聚集索引,以避免整个表记录顺序调整。

使用数字型字段:

eg:在一个学生成绩表 Grades 中,如果考试成绩以整数形式表示,使用整数型字段而非字符型字段。

使用 VARCHAR/NVARCHAR:

eg:在一个文章表 Articles 中,如果存储文章内容,使用 VARCHAR(MAX) 而非 TEXT。

避免使用 SELECT *:

eg:在一个员工表 Employees 中,避免使用 SELECT * FROM Employees,而是明确指定需要的列,如 SELECT EmployeeID, FirstName, LastName FROM Employees。

使用表变量代替临时表:

eg:在一个小型数据集的情况下,可以使用表变量而不是创建临时表来存储中间结果。例如,使用表变量替代以下的临时表:

-- 不推荐
CREATE TABLE #TempResults (
    ID INT,
    Name VARCHAR(255),
    ...
-- 推荐
DECLARE @TempResults TABLE (
    ID INT,
    Name VARCHAR(255),
    ...
);

避免频繁创建和删除临时表:

eg:在一个存储过程中,如果需要多次使用相同的临时表,不要在每次使用时都创建和删除,而是在存储过程的开头创建一次,最后删除。

合理使用临时表:

eg:在一个复杂的查询中,如果需要多次引用中间结果,可以考虑使用临时表。但应注意不要滥用,确保临时表的使用是必要的。

选择合适的临时表创建方式:

eg:在需要一次性插入大量数据的情况下,可以使用 SELECT INTO 替代 CREATE TABLE 和 INSERT 的两步操作,以减少日志记录。

-- 不推荐
CREATE TABLE #TempTable (
    ID INT,
    Name VARCHAR(255),
    ...
);
INSERT INTO #TempTable
SELECT ID, Name, ...
FROM SomeTable;

-- 推荐
SELECT ID, Name, ...
INTO #TempTable
FROM SomeTable;

显式删除临时表:

eg:在存储过程或脚本的最后,确保显式删除所有创建的临时表,以释放系统表资源。

-- 不推荐
DROP TABLE #TempTable;
-- 推荐
TRUNCATE TABLE #TempTable;
DROP TABLE #TempTable;

避免使用游标:

eg:在一个订单表 Orders 中,避免使用游标来逐行处理数据,可以考虑使用集合操作或者其他优化方法。

基于集的方法替代游标或临时表:

eg:在需要对大量数据进行操作时,尽量寻找基于集的解决方案,以避免使用游标或临时表。例如,使用窗口函数或联接来处理数据。

存储过程中使用 SET NOCOUNT ON/OFF:

eg:在存储过程中使用 SET NOCOUNT ON 和 SET NOCOUNT OFF,以减少向客户端发送 DONE_IN_PROC 消息,提高性能。

-- 存储过程开头
SET NOCOUNT ON;

-- 存储过程结尾
SET NOCOUNT OFF;

避免大事务操作:

eg:在一个银行交易表 Transactions 中,避免在一个事务中处理过多的交易记录,以提高系统并发能力。

避免向客户端返回大数据量:

eg:在一个日志表 Logs 中,如果查询可能返回大量的日志记录,应该审查客户端是否真的需要这么多数据,考虑分页或其他方式减少返回的数据量。

  • SQL Server执行计划掌握:

使用EXPLAIN或Show Execution Plan分析查询执行计划,发现潜在问题。

2、结语

 熟悉其他数据库的同学应该也能对比出,很多数据库的优化经验是相通的,所以在学习其他数据库的时候可以借鉴已掌握的经验去对比学习,这样学习起来也会事半功倍。



Tags:SQL Server   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
使用 Python 连接 SQL Server 数据库并实时读取数据?
实时读取SQL Server数据库表并进行处理是一个常见的需求。在Python中,可以使用pyodbc库来连接SQL Server数据库,并使用pandas库来进行数据处理。下面是一个实战示例,演示如何实...【详细内容】
2023-12-28  Search: SQL Server  点击:(90)  评论:(0)  加入收藏
数据库迁移:从 SQL Server 到 PostgreSQL
作者 | 何蔚一、背景在这个数字化时代,企业的复杂业务逻辑运转需要依赖复杂的业务服务来完成。这些业务服务通常会经历变更、拆分、合并和上云等过程,最终与一些商业软件和云...【详细内容】
2023-11-29  Search: SQL Server  点击:(234)  评论:(0)  加入收藏
SQL Server 百万数据查询优化技巧三十则
互联网时代的进程越走越深,使用MySQL的人也越来越多,关于MySQL的数据库优化指南很多,而关于SQL SERVER的T-SQL优化指南看上去比较少,近期有学习SQLSERVER的同学问到SQL SERVER数...【详细内容】
2023-11-28  Search: SQL Server  点击:(337)  评论:(0)  加入收藏
如何解决SQL Server数据库备份过程中检测到的日志损坏问题
译者 | 李睿日志损坏问题通常发生在对事务日志进行备份的时候。其错误是这样描述的:“消息26019,级别16,状态1,第1行BACKUP在数据库日志中检测到损坏。有关详细信息,请查看错误日...【详细内容】
2023-11-08  Search: SQL Server  点击:(288)  评论:(0)  加入收藏
如何在 SQL Server 中备份和恢复数据库
在SQL Server中,数据库可以存储在不同的文件和文件组中。如果您的数据库较小(100 MB 或更少),则无需过多担心文件和文件组。但如果您有一个大型数据库(数 GB 或 TB),将数据分离到不...【详细内容】
2023-09-05  Search: SQL Server  点击:(267)  评论:(0)  加入收藏
linux上SQL Server 配置管理器的使用
概述我们知道Windows平台上的SQL Server 配置管理器是一个图形工具,用于管理与 SQL Server 关联的服务、配置 SQL Server 使用的网络协议以及管理 SQL Server 客户端计算机的...【详细内容】
2023-08-24  Search: SQL Server  点击:(306)  评论:(0)  加入收藏
Linux 上 SQL Server 配置管理器的使用
概述我们知道Windows平台上的SQL Server 配置管理器是一个图形工具,用于管理与 SQL Server 关联的服务、配置 SQL Server 使用的网络协议以及管理 SQL Server 客户端计算机的...【详细内容】
2023-08-21  Search: SQL Server  点击:(124)  评论:(0)  加入收藏
python如何操作SQL Server数据库?
当使用Python与SQL Server进行交互时,可以使用不同的库和模块。以下是25个示例代码,用于演示如何使用Python与SQL Server进行连接、查询、插入、更新和删除等操作:使用pyodbc库...【详细内容】
2023-08-15  Search: SQL Server  点击:(296)  评论:(0)  加入收藏
SQL Server关系数据库在Linux系统上安装及配置
SQL Server是由Microsoft开发的关系型数据库管理系统(RDBMS)。它是一种可靠、安全且高性能的数据库解决方案,用于存储、管理和处理大量结构化数据。SQL Server支持标准的SQL查...【详细内容】
2023-08-15  Search: SQL Server  点击:(242)  评论:(0)  加入收藏
SQL Server数据库常见操作及命令
创建数据库:CREATE DATABASE database_name;删除数据库:DROP DATABASE database_name;创建表:CREATE TABLE table_name ( column1 datatype, column2 datatype, ...);删除...【详细内容】
2023-08-15  Search: SQL Server  点击:(228)  评论:(0)  加入收藏
▌简易百科推荐
SQL Server 百万数据查询优化技巧三十则
互联网时代的进程越走越深,使用MySQL的人也越来越多,关于MySQL的数据库优化指南很多,而关于SQL SERVER的T-SQL优化指南看上去比较少,近期有学习SQLSERVER的同学问到SQL SERVER数...【详细内容】
2023-11-28  数据库干货铺  微信公众号  Tags:SQL Server   点击:(337)  评论:(0)  加入收藏
如何解决SQL Server数据库备份过程中检测到的日志损坏问题
译者 | 李睿日志损坏问题通常发生在对事务日志进行备份的时候。其错误是这样描述的:“消息26019,级别16,状态1,第1行BACKUP在数据库日志中检测到损坏。有关详细信息,请查看错误日...【详细内容】
2023-11-08    51CTO  Tags:SQL Server   点击:(288)  评论:(0)  加入收藏
聊聊SQL中的排名问题
今天给大家介绍一下SQL Server排名中经常用到的ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()这四个好兄弟。 我们先创建一个测试数据表ScoresWITH t AS(SELECT 1 StuID,70 ScoreU...【详细内容】
2023-10-26  SQL数据库开发  微信公众号  Tags:SQL   点击:(176)  评论:(0)  加入收藏
数据库批量插入数据方法
SQL Server 批量插入数据常用方法:一、普通的Insert插入二、拼接BatchInsert插入三、SqlBulkCopy插入 以上3种方法插入数据的速度比较:(ms:毫秒,1000 毫秒 = 1秒)由于电脑硬件情况...【详细内容】
2023-10-17      Tags:数据库   点击:(252)  评论:(0)  加入收藏
如何在 SQL Server 中备份和恢复数据库
在SQL Server中,数据库可以存储在不同的文件和文件组中。如果您的数据库较小(100 MB 或更少),则无需过多担心文件和文件组。但如果您有一个大型数据库(数 GB 或 TB),将数据分离到不...【详细内容】
2023-09-05  祺印说信安  微信公众号  Tags:SQL Server   点击:(267)  评论:(0)  加入收藏
linux上SQL Server 配置管理器的使用
概述我们知道Windows平台上的SQL Server 配置管理器是一个图形工具,用于管理与 SQL Server 关联的服务、配置 SQL Server 使用的网络协议以及管理 SQL Server 客户端计算机的...【详细内容】
2023-08-24    雪竹频道  Tags:SQL Server   点击:(306)  评论:(0)  加入收藏
Linux 上 SQL Server 配置管理器的使用
概述我们知道Windows平台上的SQL Server 配置管理器是一个图形工具,用于管理与 SQL Server 关联的服务、配置 SQL Server 使用的网络协议以及管理 SQL Server 客户端计算机的...【详细内容】
2023-08-21  雪竹聊技术  今日头条  Tags:SQL Server   点击:(124)  评论:(0)  加入收藏
python如何操作SQL Server数据库?
当使用Python与SQL Server进行交互时,可以使用不同的库和模块。以下是25个示例代码,用于演示如何使用Python与SQL Server进行连接、查询、插入、更新和删除等操作:使用pyodbc库...【详细内容】
2023-08-15  运维开发木子李  今日头条  Tags:SQL Server   点击:(296)  评论:(0)  加入收藏
SQL Server关系数据库在Linux系统上安装及配置
SQL Server是由Microsoft开发的关系型数据库管理系统(RDBMS)。它是一种可靠、安全且高性能的数据库解决方案,用于存储、管理和处理大量结构化数据。SQL Server支持标准的SQL查...【详细内容】
2023-08-15  运维开发木子李  今日头条  Tags:SQL Server   点击:(242)  评论:(0)  加入收藏
SQL Server数据库常见操作及命令
创建数据库:CREATE DATABASE database_name;删除数据库:DROP DATABASE database_name;创建表:CREATE TABLE table_name ( column1 datatype, column2 datatype, ...);删除...【详细内容】
2023-08-15  运维开发木子李  今日头条  Tags:SQL Server   点击:(228)  评论:(0)  加入收藏
站内最新
站内热门
站内头条