高性能SQL

多数的性能优化都是讲索引和SQL优化，实际上从数据库设计到业务应用到数据库部署等各个方面都对数据库性能有影响，本文从表设计和SQL优化等多个角度介绍数据库性能优化的方法

1.表设计聚集索引把最常用于Where条件和Join条件的字段设置为聚集索引，而不总是主键一个表只能有一个聚集索引，数据在磁盘上的排练顺序与聚集索引一致，根据业务仔细设定聚集索引，值递增的不可修改的字段才能设置聚集索引： 自增Id，递增的单号，创建日期等<创建时间>字段唯一，递增，不可修改，经常用于where条件中进行范围检索，非常适合做聚集索引应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为普通索引。主键一个表主键经常被其他表引用，也就经常出现在连接条件和Where条件中，合适的主键对于SQL性能起到了关键的作用如果业务上已经有唯一的递增的不可修改的，关键业务字段，优先考虑使用业务字段做主键，例如订单的单号，单号符合 唯一，递增，不可修改，长度固定，关键业务，这些特性，非常适合做主键，考虑自增ID或雪花ID， 不要用GUID ，正常的雪花算法生成的ID是一个64bit的十进制整数，其长度为19位十进制数。JavaScript的Number类型最大值是53bit的，为: 9007199254740991 ，是一个16位的十进制数要兼容JavaScript，我们要使用简化后的雪花算法，产生的ID的长度小于等于16位，最高位小于9，也就是不能超过: 九千兆(九千万亿), 简化后的雪花算法可以在1毫秒内产生16个Id主键默认是聚集索引，如果要单独建聚集索引, 在建表时要先设置聚集索引，再设置主键，才不会被主键默认占用聚集索引非聚集索引

就是普通的索引，可准确预估到会高频出现在where条件和连接条件中的字段可以设置索引，否则不要设置，待出现性能瓶颈后，分析sql后再设置

null

在where条件中出现null值判断(IsNull函数)会破坏索引的使用，所以在表设计时就尽量把列设置为not null ，空字符串列可以存储 '' 来表示null ，其他类型没有通用的替代方案，但是尽量可以结合业务把列调整为not null ， 例如 枚举可以用-1代表空值

慎用varchar(max)、nvarchar(max)类型

正常来讲SQL在处理varchar(max)这种大值类型运算的时候性能会比varchar(N)慢 50%左右，涉及到表关联、更新时性能还是很有问题，建议开发人员在定义这种max类型时一定要跟程序实际业务数据结合一起考量，避免空间浪费和性能问题。

外键

不要设置外键!!

不要设置外键!!

不要设置外键!!

外键只作为业务概念存在，不做数据库约束，否则会带来运维灾难, 例如删除, 修改 都可能出现意想不到的问题

仅在外键字段上增加索引， 例如子表里面的主表id应建索引

建表的例子

--表说明xxxxx

IF OBJECT_ID ('SysUser'， 'U') IS NULL

BEGIN

CREATE TABLE [dbo].[SysUser]

(

[Id] BIGINT IDENTITY (1， 1) NOT NULL，

[Name] NVARCHAR (32) NOT NULL，

[PhoneNumber] NVARCHAR (32) NOT NULL，

[Password] NVARCHAR (128) NOT NULL，

[UserName] NVARCHAR (256) NOT NULL，

[Remark] NVARCHAR (512) NOT NULL，

[CreationTime] DATETIME NOT NULL，

[CreatorUserId] BIGINT NULL，

[LastLoginTime] DATETIME NULL，

[LastModificationTime] DATETIME NULL，

[LastModifierUserId] BIGINT NULL，

[IsActive] BIT NOT NULL

);

--添加聚集索引，根据业务仔细设定，值递增的字段才能设置聚集索引

CREATE CLUSTERED INDEX IX_SysUser_CreationTime

ON SysUser (CreationTime);

--添加非聚集索引

] CREATE NONCLUSTERED INDEX IX_SysUser_CreatorUserId

ON SysUser (CreatorUserId);

--添加默认值

ALTER TABLE SysUser ADD CONSTRAINT DF_SysUser_CreationTime DEFAULT GetDate() FOR CreationTime

--添加主键

ALTER TABLE SysUser ADD CONSTRAINT PK_SysUser PRIMARY KEY (Id);

END

GO

2.索引

出现性能瓶颈的时候，先优化SQL语句，再考虑在 where 及 order by 涉及的列上建立索引

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 、 update 及 delete 的效率，所以索引不是越多越好

数据量大的表，维护索引的开销也大，建索引要慎重数据量小的表用不着建索引索引列存在大量重复数据时效果不明显，此时不应该建索引，例如sex列3.Where

不要在 where 子句中的列上进行函数、算术运算、类型转换或其他表达式运算，否则系统将可能无法正确使用索引

函数

尽量避免在where子句中对字段进行函数操作，否则将导致全表扫描。

例如： Where substring(name，1，3) = 'abc'

应改为 Where name like 'abc%'

另外IsNull也是经常使用的函数，要避免就要在表设计建设可null的列

计算

尽量避免在where子句中对字段进行表达式操作，否则会导致全表扫描。可以把字段从表达式中移除走，

例如： Where num/2 = 100

应改为 Where num = 100 *2

like/Or/In减少引起全表扫描的动作： or、like（'%%'）、in和not in (用左匹配(走索引)，不用右匹配，不用全匹配： Where UserName like 'abc%'很多时候用exists，between替代 in 是一个好的选择：Where num in(select num from b)应改为：Where exists(select 1 from b where num=a.num)不等于

尽量不用not 或 =! 或 <> ，将放弃索引

条件顺序

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序一致。

Where条件我们应该遵循这样一个顺序原则：

[=]->[>=或<=]->[exists 或 in]->[not exists]->[>或< 或 not in 或 <>]->[函数]

Join

何时用left join ?? 何时用 inner join ??

不确定的时候用 inner join , 发现不对再修改为left join , 如果一开始就使用left join 很难发现问题

少用左连接，多用内连接

left join 比 inner join 消耗更多的资源

满足业务要求的情况下使用内连接

连接条件的优化

on后面的连接条件只放表与表之间的连接过滤，单表的过滤条件放Where里面

...

From User a

Inner Join Role b on b.UserId = a.Id And b.Name = '管理员'

--优化为

...

From User a

Inner Join Role b on b.UserId = a.Id

Where b.Name = '管理员'

5.存储过程合理使用临时表避免频繁创建和删除临时表，会影响数据库性能存储过程中某个复杂查询反复出现时，可以把此查询结果先存入临时表，提高性能用多个简单语句代替一个复杂语句如果一个复杂的语句有很多张表要连接，很多时候，根据表和表的逻辑关系，一张表和另外一张表如果先做连接，可能会过滤掉很多数据，则把结果存入临时表, 再做其他连接，会更快。对特别复杂的语句，可以把一句SQL拆成两句SQL或三句SQL，分步做完，中间结果用临时表的形式存放。虽然多做了一些事情，但简化了复杂度，提高了可读性，很多时候也能提高性能。在存储过程的最后务必将所有的临时表显式删除，先 truncate table #temp ，然后 drop table #temp，这样可以避免系统表的长时间锁定禁用游标，慎用循环禁止使用游标，游标效率低，编写复杂，用游标不如用循环然而当你需要使用循环的时候，你要考虑清楚有没有办法不使用循环而达到效果，如果一定要用，请务必考虑尽量减少循环的数据量数据库天然具有批量数据的处理能力，发挥数据库的优势，应先寻找基于集合的解决方案来避免循环6.其他SQL优化不要使用Select *，不必要的列带来额外的系统开销用>= 替代 >控制嵌套调用的层数：存储过程嵌套，视图嵌套尽量避免大事务操作，提高系统并发能力。当使用约束和触发器都能完成同样的功能时，优先考虑使用约束。尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理Union和Union All :

Union在去重的过程中会有排序运算， 性能不如 Union All

如果我们不在乎结果中是否有重复数据，或者事先知道不会有重复数据，可以使用 Union All 代替 Union

Order by

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式

用TRUNCATE替代DELETE

TRUNCATE删除全表数据，不记录日志，性能上 TRUNCATE要好过DELETE，在删除表的部分数据时可以使用DELETE，但要注意带上Where条件；全表删除优先考虑使用TRUNCATE

With与临时表

公用表表达式（CTE）是SQL Server 2005新推出来的一种语法对象。它的出现提高了语句的重用可维护性，比表变量的效率要高的多。

不过在日常优化过程中，常常发现用户的脚本卡在With这一段，而将它改成临时表之后就会很快过去。通过分析发现With在存放大数据量时的性能要差与临时表，因此在设计程序的时候对一些应用比较频繁的脚本，特别是报表在无法预知它的With对象的数量时，建议用临时表来操作会有较好的性能表现。

7.堵塞与死锁数据库阻塞

第一个连接占有资源没有释放，而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚，第二个连接会一直等待下去，直到第一个连接释放该资源为止。

长期堵塞严重影响系统体验，所以对数据库操作要及时地提交或者回滚

一个长时间的查询也会形成堵塞，所以通过分步查询等方式优化

数据库死锁

第一个连接占有资源没有释放，准备获取第二个连接所占用的资源，而第二个连接占有资源没有释放，准备获取第一个连接所占用的资源。这种互相占有对方需要的资源的现象叫做死锁。对于死锁，数据库会牺牲其中一个，并抛出异常

死锁严重影响系统体验，发现死锁脚本，结合业务优化脚本

8.其他数据库性能优化

不存储二进制文件

独立日志库

分库分表

读写分离： 高可用，订阅与发布

推荐SqlSugar： 通过配置就可以实现读写分离， 独立日志库