1. T-SQL优化常识从基础到实战作为一名与SQL Server打了十年交道的DBA我处理过太多因T-SQL编写不当导致的性能问题。今天要分享的这些优化常识都是我在生产环境中用血泪教训换来的经验。不同于教科书式的理论这里每一条建议都经过实际业务场景验证。T-SQL优化本质上是在解决三个核心矛盾数据处理效率与资源占用的平衡、开发便捷性与执行性能的权衡、即时查询速度与长期维护成本的取舍。当你的查询开始出现超时、阻塞或CPU飙升时往往意味着这三组平衡被打破了。2. 查询设计层面的优化策略2.1 SELECT语句的精简原则我见过最典型的反例是SELECT *的滥用。在一次性能审计中某个报表查询从200万行表中提取所有字段但前端实际只显示其中3列。优化为只查询必要字段后执行时间从8秒降至0.3秒。更隐蔽的问题是隐式类型转换。上周处理的一个案例-- 低效写法PostCode是varchar但用了数字比较 SELECT OrderID FROM Orders WHERE PostCode 100001 -- 优化后 SELECT OrderID FROM Orders WHERE PostCode 100001这个简单的类型不匹配导致索引失效全表扫描消耗了15秒。正确的做法是始终保证比较运算符两侧的数据类型一致。2.2 WHERE条件的优化技巧WHERE子句是查询优化的主战场。这里有三个黄金法则优先使用等值条件过滤最大数据量避免在索引列上使用函数或计算范围查询BETWEEN, , 放在条件列表最后一个真实案例某电商平台的产品搜索接口原来使用SELECT * FROM Products WHERE CONVERT(varchar, CreateTime, 112) 20230501 AND CategoryID 12改为以下写法后性能提升20倍SELECT * FROM Products WHERE CategoryID 12 AND CreateTime 2023-05-01 AND CreateTime 2023-05-022.3 JOIN操作的性能陷阱多表关联是性能问题的重灾区。关键点在于永远用小表驱动大表SQL Server的查询优化器并不总是能正确选择确保JOIN字段有索引且数据类型一致避免超过4个表的复杂关联考虑预计算或拆解查询最近优化的一个库存查询-- 原写法5表关联 SELECT p.ProductName, s.StockQty FROM Products p JOIN Inventory i ON p.ProductID i.ProductID JOIN Stores s ON i.StoreID s.StoreID JOIN Regions r ON s.RegionID r.RegionID JOIN Warehouses w ON i.WarehouseID w.WarehouseID WHERE r.RegionName 华东 -- 优化后拆分为两个查询应用层处理 -- 查询1获取区域下的门店 DECLARE StoreIDs TABLE(ID int) INSERT INTO StoreIDs SELECT StoreID FROM Stores WHERE RegionID IN ( SELECT RegionID FROM Regions WHERE RegionName 华东 ) -- 查询2只关联必要表 SELECT p.ProductName, i.StockQty FROM Products p JOIN Inventory i ON p.ProductID i.ProductID WHERE i.StoreID IN (SELECT ID FROM StoreIDs)3. 索引使用的核心要点3.1 索引选择性的重要性索引选择性是指索引列唯一值的比例。一个经验公式选择性 唯一值数量 / 总行数当选择性 0.9时适合建单列索引0.1-0.9考虑复合索引0.1通常索引无效。上周优化的用户表查询-- Status只有0/1/2三个值选择性差 CREATE INDEX IX_User_Status ON Users(Status) -- 无效索引 -- 改为包含高选择性列的复合索引 CREATE INDEX IX_User_Status_Region ON Users(Status, RegionID)3.2 索引覆盖的妙用当查询的所有列都包含在索引中时可以避免昂贵的键查找操作。去年优化的一个报表-- 原查询 SELECT UserID, UserName FROM Users WHERE DeptID 5 -- 原有索引 CREATE INDEX IX_Users_DeptID ON Users(DeptID) -- 优化为覆盖索引 CREATE INDEX IX_Users_DeptID_INCL ON Users(DeptID) INCLUDE (UserID, UserName)执行计划从Index Seek Key Lookup变为纯Index Seek性能提升8倍。3.3 索引碎片化的监控即使设计再好的索引也会随时间性能下降。建议每月检查SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ips JOIN sys.indexes i ON ips.object_id i.object_id AND ips.index_id i.index_id WHERE ips.avg_fragmentation_in_percent 30碎片率30%应考虑重建索引REBUILD10-30%可重组REORGANIZE。4. 高级优化技术实战4.1 参数嗅探问题处理参数嗅探Parameter Sniffing是存储过程性能不稳定的常见原因。处理方案使用本地变量屏蔽参数CREATE PROC GetOrders(StartDate datetime) AS BEGIN DECLARE LocalStartDate datetime StartDate SELECT * FROM Orders WHERE OrderDate LocalStartDate END使用OPTIMIZE FOR提示CREATE PROC GetOrders(StartDate datetime) AS BEGIN SELECT * FROM Orders WHERE OrderDate StartDate OPTION (OPTIMIZE FOR (StartDate 2023-01-01)) END禁用参数嗅探慎用CREATE PROC GetOrders(StartDate datetime) AS BEGIN SELECT * FROM Orders WHERE OrderDate StartDate OPTION (RECOMPILE) END4.2 临时表的正确用法临时表在复杂查询中有奇效但使用不当会适得其反。对比三种临时表类型作用域是否日志适用场景#Temp当前会话部分中间结果集会话内复用##GlobalTemp所有会话部分极少使用TableVariable当前批处理无小数据量(1000行)快速操作一个分页查询的优化案例-- 低效写法直接分页大表 SELECT * FROM BigTable ORDER BY CreateTime OFFSET 10000 ROWS FETCH NEXT 50 ROWS ONLY -- 高效写法先用窄条件筛选到临时表 DECLARE IDs TABLE(ID int PRIMARY KEY) INSERT INTO IDs SELECT TOP 10050 ID FROM BigTable WHERE CreateTime 2023-01-01 ORDER BY CreateTime -- 然后关联获取完整数据 SELECT b.* FROM BigTable b JOIN (SELECT TOP 50 ID FROM IDs ORDER BY ID OFFSET 10000 ROWS) t ON b.ID t.ID4.3 执行计划强制技术当SQL Server优化器选择次优计划时可以考虑计划引导Plan Guide。操作步骤捕获问题查询的实际执行计划分析并确定更优的执行计划创建计划引导强制使用优化计划示例强制使用索引查找而非扫描EXEC sp_create_plan_guide name NForceIndexGuide, stmt NSELECT * FROM Orders WHERE OrderDate Date, type NSQL, module_or_batch NULL, params NDate datetime, hints NOPTION (TABLE HINT(Orders, INDEX(IX_Orders_Date)))5. 日常维护与监控5.1 性能基线建立建议每周收集以下关键指标作为基准-- 查询统计 SELECT qs.execution_count, qs.total_logical_reads/qs.execution_count AS avg_logical_reads, qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time, SUBSTRING(qt.text, (qs.statement_start_offset/2)1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)1) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE qt.dbid DB_ID() ORDER BY avg_logical_reads DESC5.2 阻塞问题排查使用以下脚本实时监控阻塞链SELECT blocking.session_id AS blocking_session, blocked.session_id AS blocked_session, wait.wait_type AS wait_resource, blocking.text AS blocking_text, blocked.text AS blocked_text FROM sys.dm_exec_connections AS blocking JOIN sys.dm_exec_requests AS blocked ON blocking.session_id blocked.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) AS blocking OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked JOIN sys.dm_os_waiting_tasks AS wait ON blocked.session_id wait.session_id5.3 自动优化建议SQL Server内置的查询存储(Query Store)能自动识别退化查询-- 启用查询存储 ALTER DATABASE YourDB SET QUERY_STORE ON GO -- 查看回归查询 SELECT qsq.query_id, qsqt.query_sql_text, qsp.plan_id, qsrs.avg_duration/1000 AS avg_duration_ms FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id qsrs.plan_id ORDER BY qsrs.last_execution_time DESC6. 真实案例电商大促前的优化实战去年双十一前我们对一个关键订单查询进行了深度优化。原查询SELECT o.OrderID, o.OrderDate, c.CustomerName, p.ProductName, od.Quantity, od.UnitPrice FROM Orders o JOIN Customers c ON o.CustomerID c.CustomerID JOIN OrderDetails od ON o.OrderID od.OrderID JOIN Products p ON od.ProductID p.ProductID WHERE o.OrderDate BETWEEN Start AND End AND o.Status IN (1, 2, 3) ORDER BY o.OrderDate DESC优化步骤创建覆盖索引CREATE INDEX IX_Orders_DateStatus ON Orders(OrderDate DESC, Status) INCLUDE (CustomerID)将IN列表改为临时表StatusTable TABLE(StatusID int PRIMARY KEY)使用批处理模式处理OPTION (HASH JOIN, FAST 1000)添加查询提示OPTION (OPTIMIZE FOR UNKNOWN)最终性能从平均1200ms降至180ms峰值期间CPU使用率下降40%。 T-SQL性能优化实战：从查询设计到索引策略
1. T-SQL优化常识从基础到实战作为一名与SQL Server打了十年交道的DBA我处理过太多因T-SQL编写不当导致的性能问题。今天要分享的这些优化常识都是我在生产环境中用血泪教训换来的经验。不同于教科书式的理论这里每一条建议都经过实际业务场景验证。T-SQL优化本质上是在解决三个核心矛盾数据处理效率与资源占用的平衡、开发便捷性与执行性能的权衡、即时查询速度与长期维护成本的取舍。当你的查询开始出现超时、阻塞或CPU飙升时往往意味着这三组平衡被打破了。2. 查询设计层面的优化策略2.1 SELECT语句的精简原则我见过最典型的反例是SELECT *的滥用。在一次性能审计中某个报表查询从200万行表中提取所有字段但前端实际只显示其中3列。优化为只查询必要字段后执行时间从8秒降至0.3秒。更隐蔽的问题是隐式类型转换。上周处理的一个案例-- 低效写法PostCode是varchar但用了数字比较 SELECT OrderID FROM Orders WHERE PostCode 100001 -- 优化后 SELECT OrderID FROM Orders WHERE PostCode 100001这个简单的类型不匹配导致索引失效全表扫描消耗了15秒。正确的做法是始终保证比较运算符两侧的数据类型一致。2.2 WHERE条件的优化技巧WHERE子句是查询优化的主战场。这里有三个黄金法则优先使用等值条件过滤最大数据量避免在索引列上使用函数或计算范围查询BETWEEN, , 放在条件列表最后一个真实案例某电商平台的产品搜索接口原来使用SELECT * FROM Products WHERE CONVERT(varchar, CreateTime, 112) 20230501 AND CategoryID 12改为以下写法后性能提升20倍SELECT * FROM Products WHERE CategoryID 12 AND CreateTime 2023-05-01 AND CreateTime 2023-05-022.3 JOIN操作的性能陷阱多表关联是性能问题的重灾区。关键点在于永远用小表驱动大表SQL Server的查询优化器并不总是能正确选择确保JOIN字段有索引且数据类型一致避免超过4个表的复杂关联考虑预计算或拆解查询最近优化的一个库存查询-- 原写法5表关联 SELECT p.ProductName, s.StockQty FROM Products p JOIN Inventory i ON p.ProductID i.ProductID JOIN Stores s ON i.StoreID s.StoreID JOIN Regions r ON s.RegionID r.RegionID JOIN Warehouses w ON i.WarehouseID w.WarehouseID WHERE r.RegionName 华东 -- 优化后拆分为两个查询应用层处理 -- 查询1获取区域下的门店 DECLARE StoreIDs TABLE(ID int) INSERT INTO StoreIDs SELECT StoreID FROM Stores WHERE RegionID IN ( SELECT RegionID FROM Regions WHERE RegionName 华东 ) -- 查询2只关联必要表 SELECT p.ProductName, i.StockQty FROM Products p JOIN Inventory i ON p.ProductID i.ProductID WHERE i.StoreID IN (SELECT ID FROM StoreIDs)3. 索引使用的核心要点3.1 索引选择性的重要性索引选择性是指索引列唯一值的比例。一个经验公式选择性 唯一值数量 / 总行数当选择性 0.9时适合建单列索引0.1-0.9考虑复合索引0.1通常索引无效。上周优化的用户表查询-- Status只有0/1/2三个值选择性差 CREATE INDEX IX_User_Status ON Users(Status) -- 无效索引 -- 改为包含高选择性列的复合索引 CREATE INDEX IX_User_Status_Region ON Users(Status, RegionID)3.2 索引覆盖的妙用当查询的所有列都包含在索引中时可以避免昂贵的键查找操作。去年优化的一个报表-- 原查询 SELECT UserID, UserName FROM Users WHERE DeptID 5 -- 原有索引 CREATE INDEX IX_Users_DeptID ON Users(DeptID) -- 优化为覆盖索引 CREATE INDEX IX_Users_DeptID_INCL ON Users(DeptID) INCLUDE (UserID, UserName)执行计划从Index Seek Key Lookup变为纯Index Seek性能提升8倍。3.3 索引碎片化的监控即使设计再好的索引也会随时间性能下降。建议每月检查SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ips JOIN sys.indexes i ON ips.object_id i.object_id AND ips.index_id i.index_id WHERE ips.avg_fragmentation_in_percent 30碎片率30%应考虑重建索引REBUILD10-30%可重组REORGANIZE。4. 高级优化技术实战4.1 参数嗅探问题处理参数嗅探Parameter Sniffing是存储过程性能不稳定的常见原因。处理方案使用本地变量屏蔽参数CREATE PROC GetOrders(StartDate datetime) AS BEGIN DECLARE LocalStartDate datetime StartDate SELECT * FROM Orders WHERE OrderDate LocalStartDate END使用OPTIMIZE FOR提示CREATE PROC GetOrders(StartDate datetime) AS BEGIN SELECT * FROM Orders WHERE OrderDate StartDate OPTION (OPTIMIZE FOR (StartDate 2023-01-01)) END禁用参数嗅探慎用CREATE PROC GetOrders(StartDate datetime) AS BEGIN SELECT * FROM Orders WHERE OrderDate StartDate OPTION (RECOMPILE) END4.2 临时表的正确用法临时表在复杂查询中有奇效但使用不当会适得其反。对比三种临时表类型作用域是否日志适用场景#Temp当前会话部分中间结果集会话内复用##GlobalTemp所有会话部分极少使用TableVariable当前批处理无小数据量(1000行)快速操作一个分页查询的优化案例-- 低效写法直接分页大表 SELECT * FROM BigTable ORDER BY CreateTime OFFSET 10000 ROWS FETCH NEXT 50 ROWS ONLY -- 高效写法先用窄条件筛选到临时表 DECLARE IDs TABLE(ID int PRIMARY KEY) INSERT INTO IDs SELECT TOP 10050 ID FROM BigTable WHERE CreateTime 2023-01-01 ORDER BY CreateTime -- 然后关联获取完整数据 SELECT b.* FROM BigTable b JOIN (SELECT TOP 50 ID FROM IDs ORDER BY ID OFFSET 10000 ROWS) t ON b.ID t.ID4.3 执行计划强制技术当SQL Server优化器选择次优计划时可以考虑计划引导Plan Guide。操作步骤捕获问题查询的实际执行计划分析并确定更优的执行计划创建计划引导强制使用优化计划示例强制使用索引查找而非扫描EXEC sp_create_plan_guide name NForceIndexGuide, stmt NSELECT * FROM Orders WHERE OrderDate Date, type NSQL, module_or_batch NULL, params NDate datetime, hints NOPTION (TABLE HINT(Orders, INDEX(IX_Orders_Date)))5. 日常维护与监控5.1 性能基线建立建议每周收集以下关键指标作为基准-- 查询统计 SELECT qs.execution_count, qs.total_logical_reads/qs.execution_count AS avg_logical_reads, qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time, SUBSTRING(qt.text, (qs.statement_start_offset/2)1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)1) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE qt.dbid DB_ID() ORDER BY avg_logical_reads DESC5.2 阻塞问题排查使用以下脚本实时监控阻塞链SELECT blocking.session_id AS blocking_session, blocked.session_id AS blocked_session, wait.wait_type AS wait_resource, blocking.text AS blocking_text, blocked.text AS blocked_text FROM sys.dm_exec_connections AS blocking JOIN sys.dm_exec_requests AS blocked ON blocking.session_id blocked.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) AS blocking OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked JOIN sys.dm_os_waiting_tasks AS wait ON blocked.session_id wait.session_id5.3 自动优化建议SQL Server内置的查询存储(Query Store)能自动识别退化查询-- 启用查询存储 ALTER DATABASE YourDB SET QUERY_STORE ON GO -- 查看回归查询 SELECT qsq.query_id, qsqt.query_sql_text, qsp.plan_id, qsrs.avg_duration/1000 AS avg_duration_ms FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id qsrs.plan_id ORDER BY qsrs.last_execution_time DESC6. 真实案例电商大促前的优化实战去年双十一前我们对一个关键订单查询进行了深度优化。原查询SELECT o.OrderID, o.OrderDate, c.CustomerName, p.ProductName, od.Quantity, od.UnitPrice FROM Orders o JOIN Customers c ON o.CustomerID c.CustomerID JOIN OrderDetails od ON o.OrderID od.OrderID JOIN Products p ON od.ProductID p.ProductID WHERE o.OrderDate BETWEEN Start AND End AND o.Status IN (1, 2, 3) ORDER BY o.OrderDate DESC优化步骤创建覆盖索引CREATE INDEX IX_Orders_DateStatus ON Orders(OrderDate DESC, Status) INCLUDE (CustomerID)将IN列表改为临时表StatusTable TABLE(StatusID int PRIMARY KEY)使用批处理模式处理OPTION (HASH JOIN, FAST 1000)添加查询提示OPTION (OPTIMIZE FOR UNKNOWN)最终性能从平均1200ms降至180ms峰值期间CPU使用率下降40%。