SQL Server 性能优化实战：16个关键技巧提升查询速度

基础语法与查询结构优化

第1步：优化FROM子句中的表名顺序

目的：利用基于规则的优化器（CBO/RBO），选择记录数最少的表作为基础表（驱动表）进行最先处理，从而减少排序和合并的操作量。
操作：在FROM子句中，将行数最少的表写在最后（因为解析器从右向左处理）。若有多个表连接，优先选择被其他表引用的交叉表作为基础表。
示例：当TAB1有16,384条记录，TAB2只有5条时，应确保引擎优先处理TAB2。

第2步：调整WHERE子句中的连接顺序

目的：让过滤掉最多记录的条件尽早执行，减少后续处理的行数。
原理：SQL Server自下而上解析WHERE子句。应将能最大程度限制结果集的条件放在最后，或将表连接条件写在普通过滤条件之前。
效果：某案例中，调整顺序后执行时间从156.3秒降至10.6秒。

-- 低效：复杂子查询过滤放在前面
SELECT * 
FROM EMP E 
WHERE SAL > 50000 
AND JOB = 'MANAGER' 
AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);

-- 高效：将筛选基数大的条件前置或调整顺序
SELECT * 
FROM EMP E 
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) 
AND SAL > 50000 
AND JOB = 'MANAGER';

第3步：避免在SELECT中使用'*'

原因：SQL Server在解析时会查询数据字典，将'*'转换为所有列名，增加开销。
建议：明确列出所需字段，如SELECT col1, col2而非SELECT *。

第4步：合并查询以减少数据库访问次数

目的：每条SQL语句都涉及解析、绑定、读块等内部工作，减少调用次数可降低服务器负载。
操作：将多次单行检索合并为一次多行或多表连接查询。
对比：

• 最低效：分别执行两次SELECT查询。
• 中等：使用游标（Cursor）循环处理。
• 高效：通过自连接一次性获取数据。

-- 高效：通过自连接一次性检索
SELECT A.EMP_NAME, A.SALARY, A.GRADE, 
       B.EMP_NAME, B.SALARY, B.GRADE 
FROM EMP A, EMP B 
WHERE A.EMP_NO = 342 
AND B.EMP_NO = 291;

第5步：使用DECODE函数聚合数据

目的：避免对同一张表进行多次扫描或连接，通过DECODE函数在一次查询中完成不同类别的统计。
应用：可用于GROUP BY和ORDER BY子句中。
示例：

-- 高效：单次扫描完成多个部门的统计
SELECT 
  COUNT(DECODE(DEPT_NO, '0020', 'X', NULL)) D0020_COUNT,
  COUNT(DECODE(DEPT_NO, '0030', 'X', NULL)) D0030_COUNT,
  SUM(DECODE(DEPT_NO, '0020', SAL, NULL)) D0020_SAL,
  SUM(DECODE(DEPT_NO, '0030', SAL, NULL)) D0030_SAL 
FROM EMP 
WHERE ENAME LIKE 'SMITH%';

第6步：用WHERE子句替代HAVING子句

注意：原文此处描述有误导性。HAVING用于对分组后的数据进行过滤，而WHERE用于在分组前过滤行。如果过滤条件不涉及聚合函数，应尽量移至WHERE子句，以提前减少参与分组的数据量。
错误修正：原草稿中示例存在逻辑混淆。正确做法是：
- HAVING：必须保留在GROUP BY之后，用于过滤聚合结果（如HAVING COUNT(*) > 5）。 - WHERE：用于常规列值的过滤（如WHERE REGION != 'SYDNEY'）。

-- 推荐：先将非聚合条件放入WHERE
SELECT REGION, AVG(LOG_SIZE) 
FROM LOCATION 
WHERE REGION != 'SYDNEY' AND REGION != 'PERTH' 
GROUP BY REGION;

第7步：简化子查询与更新操作

目的：减少重复的子查询执行次数。
操作：

• Select：将多个独立子查询合并为一个元组比较。
  WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM ...)
• Update：同时更新多个列时，使用列表赋值而非单独赋值。
  UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE) FROM ...)

第8步：使用表别名并限定列名

作用：在多表连接中，使用简短别名（如E代替EMP）并前缀列名，可减少解析时间并消除歧义报错。

高级查询策略：IN, EXISTS与DISTINCT

第9步：用EXISTS替代IN

场景：当主查询表大，子查询表小且满足条件频繁时，EXISTS通常更高效，因为它一旦发现匹配项即可返回真，无需构建完整集合。
示例：

-- 高效：使用EXISTS
SELECT * 
FROM EMP 
WHERE EMPNO > 0 
AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB');

第10步：用NOT EXISTS替代NOT IN

警告：NOT IN会导致全表扫描和内部排序/合并，效率极低，尤其当子查询结果包含NULL时可能产生意外结果。
替代方案：使用LEFT JOIN IS NULL或NOT EXISTS。
最高效写法：

-- 最高效：使用NOT EXISTS
SELECT * 
FROM EMP E 
WHERE NOT EXISTS (
    SELECT 'X' FROM DEPT D 
    WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = 'A'
);

第11步：适当使用表连接替代EXISTS

说明：在某些特定版本的优化器中，显式的INNER JOIN比EXISTS子句更快，因为它们可以被优化器更好地转化为哈希连接或嵌套循环。可根据实际执行计划评估。
示例：

-- 高效：直接连接
SELECT ENAME 
FROM DEPT D, EMP E 
WHERE E.DEPT_NO = D.DEPT_NO 
AND DEPT_CAT = 'A';

第12步：用EXISTS替代DISTINCT

目的：去除重复记录时，DISTINCT需要对结果集进行排序或哈希去重，而EXISTS在找到第一个匹配项后即停止搜索，速度更快。
示例：

-- 高效：避免去重开销
SELECT DEPT_NO, DEPT_NAME 
FROM DEPT D 
WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

索引使用与维护最佳实践

第13步：理解并利用索引扫描模式

索引唯一扫描 (INDEX UNIQUE SCAN)：适用于主键或唯一索引的确切值查询。效率高，若查询列均在索引中，甚至不需要回表查数据。
索引范围扫描 (INDEX RANGE SCAN)：适用于范围查询（如BETWEEN, LIKE 'A%'）或非唯一索引。效率略低于唯一扫描但仍远优于全表扫描。
注意：如果通配符在开头（如LIKE '%HANMAN'），索引失效，退化为全表扫描。

第14步：避免在索引列上进行计算或函数转换

原则：WHERE条件中若对索引列使用了函数（如TRIM, TO_DATE, 数学运算），优化器将无法使用该索引。
修正：将函数移到运算符的另一侧。
示例：

-- 低效：函数作用于索引列
SELECT ... FROM DEPT WHERE SAL * 12 > 25000;

-- 高效：常量计算移项
SELECT ... FROM DEPT WHERE SAL > 25000 / 12;

第15步：谨慎使用IS NULL和IS NOT NULL

影响：标准B-Tree索引不包含值为NULL的行。因此，在WHERE子句中对索引列使用IS NULL或IS NOT NULL会导致索引失效（除非使用位图索引或复合索引且至少一列非空）。
建议：在设计表结构时，尽量避免关键查询列出现大量NULL，或考虑创建包含固定非空值的虚拟列索引。

第16步：合理使用UNION ALL与UNION

区别：

• UNION：自动对结果进行去重排序，开销较大。
• UNION ALL：直接合并结果，不进行排序和去重，效率更高。

索引使用的边界情况

虽然索引能大幅提升查询速度，但需注意以下局限：
1. 选择性阈值：若查询需返回表中超过30%的记录，使用索引带来的随机I/O开销可能大于全表扫描的顺序I/O优势。
2. 维护成本：索引会占用存储空间，且在INSERT、UPDATE、DELETE时需要额外维护，可能略微降低写入性能。
3. 数量控制：过多的索引会使优化器选择路径变慢，并增加存储和维护负担。

简要总结

SQL Server的性能优化是一个系统工程，从书写规范的SQL代码（如少用SELECT *、正确使用WHERE过滤）到巧妙运用连接算法（EXISTS vs IN），再到合理地设计和使用索引，每一个环节都可能成为性能的瓶颈。开发者应养成查看执行计划的习惯，针对具体场景选择最优策略。以上就是SQL Server性能优化技巧的详细内容，更多关于SQL Server数据库优化资料请关注本站其它相关文章！