SQLServer性能分析及优化

image.png

公司使用SQLServer作为数据库，工作中积累了很多SQL性能优化经验，为了自己查询和记忆方便，我的分类方式主要分为语法优化，子句优化和索引优化，可以根据自己所需直接查找到对应的优化建议。（比如使用!=好还是<>好，LIKE语句有什么优化建议&＃8230;）

需要注意的是：

1. 有些优化技巧是只能在SQLServer中使用，但更多的是标准SQL能够通用的优化技巧。
2. 任何SQL优化都必须使用实际数据，脱离数据量谈优化都是耍流氓
3. 本文所述的很多优化技巧，SQL的查询优化器也是会自己优化的，但是我们还是得学习下

先介绍SARG

SARG操作就是“一个范围内的匹配搜索操作”，就是SQL语句的WHERE条件，而且这个条件不能触发全表扫描。

可以理解为：没有触发全表扫描的搜索条件

举几个例子：

• Like语句是否属于SARG取决于所使用的通配符的类型，原因是通配符%在字符串的开头使得索引无法使用

如：name like ‘张%’ ，这就属于SARG
而：name like ‘%张’，就不属于SARG。


• 使用or会引起全表扫描

如：Name=’张三’ and 价格>5000 符号SARG，
而：Name=’张三’ or 价格>5000 则不符合SARG。


其他介绍请看后缀子句的介绍。

子句优化

SQL优化建议很多且难以分类，所以我以子句为粒度来对SQL优化建议进行分类，尽可能让我们看到子句就知道需要其对应的优化建议。这里为了理解方便，将SQL所有子句根据where子句的位置分割为前缀子句和后缀子句，where子句属于后缀子句。

前缀子句

SELECT

• 避免使用SELECT * 的语法：应该完整的写上所需要的字段，并且要按照表中字段的物理顺序书写，不但可以防止表结构改变导致的代码错误，也可以防止大数据量的提取导致SQL效率低下。

INSERT

• 避免使用INSERT INTO table VALUE(?,?,?)：需要在table后完整地写上需要插入的字段，按照字段的物理顺序书写，而不是用?代替，这样可以提高效率。

UPDATE

• 暂无

DISTINCT

• 尽量少用DISTINCT或用其他方法去重：DISTINCT解决重复数据很方便，但是他会临时产生一张工作表，通过排序来删除重复的数据，因此会增加SQL的I/O次数和时间损耗。

COUNT

• COUNT统计NULL有差异：COUNT(*)和COUNT(1)会统计NULL值，COUNT(列名)不会统计NULL值
• 尽可能使用COUNT(1)：官方文档显示1等价于*，因为大家都传言1快过*，所以官方对1做了优化，虽说等价，但还是推荐使用COUNT(1)，保证统一。
• 单列索引可以提升COUNT性能：COUNT(*)和COUNT(1)都会自动遍历寻找最小的索引，考虑在一个最短的列建立一个单列索引，会极大的提升性能。

后缀子句

OR

• 尽可能少用： 会引起全表扫描（不符合SARG），可以考虑用UNION替换。

LIKE

• [通配符](*,?,_,%)不要在字符串的开头：会导致索引失效而全表扫描（不符合SARG）

IN / NOT IN

• 尽可能少用： 会引起全表扫描（不符合SARG），类似OR，可以考虑用EXISTS替换。

EXIST

• 可以使用EXIST和NOT EXIST 可以代替 IN和NOT IN：这样可以避免SARG，但是应该需要考虑EXITS中的语句执行速度，如果EXITS中的语句执行很慢，数据有多少条就需要执行多少次，这样反而会拖累速度，不一定EXIST比IN好，应该考虑实际数据情况。

WHERE

• 将能够筛选出最多数据的条件放在最右：因为默认SQL SERVER采用自右向左的顺序解析where子句，所以应该将可以过滤掉大量记录的条件必须写在where子句的末尾。

ORDER BY

• 建议参数为索引列，且索引列不能有NULL值：参数中有任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，优化器将无法优化，所以需要先排除NULL。（SQL中很多地方存在NULL都会影响SQL执行性能，建议在不破坏业务需求的情况下习惯性排序掉NULL值）。

GROPU BY

• 建议参数为索引列，且索引列不能有NULL值：参数中有任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，优化器将无法优化，所以需要先排除NULL。（SQL中很多地方存在NULL都会影响SQL执行性能，建议在不破坏业务需求的情况下习惯性排序掉NULL值）。

• 要在group by之前先过滤数据：提高group by语句的效率，可以将不需要的记录在group by之前过滤掉

语法优化

• 避免使用子查询，使用连接查询：子查询属于笛卡尔集，嵌套过多会导致数据量指数级增长，如果不能避免希望能够先过滤尽可能多的数据。
• 数据表起别名：可以利于优化器优化。
• 不要使用负逻辑： 不满足SARG，会直接触发全表扫描。当表较大时，会严重影响系统性能，可以用别的操作来代替。
• 将逻辑运算从等号左边移到右边：比如把a*2>4　改为 a>4/2。

索引优化

• 索引不被使用的几种情况：null判断，比较，not函数
• 索引列上不要计算
• 索引列上>= 代替 >
• 避免频繁的索引重建
• 索引数量不超过列总数的40%

感谢

• 数据库性能的优化
• 高并发update的 死锁产生原因
• 快速理解聚集索引和非聚集索引
• SQL索引一步到位