高性能SQL-数据库性能优化

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

表设计

聚集索引：

a. 把最常用于 Where 条件和 Join 条件的字段设置为聚集索引，而不总是主键
b. 一个表只能有一个聚集索引，数据在磁盘上的排列顺序与聚集索引一致，根据业务仔细设定聚集索引，值递增的不可修改的字段才能设置聚集索引，例如自增 ID、递增的单号、创建日期等。
c. 创建时间字段唯一，递增，不可修改，经常用于 where 条件中进行范围检索，非常适合做聚集索引。
d. 应尽可能避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，则需要考虑是否应将该索引建为普通索引。

主键：

a. 一个表主键经常被其他表引用，也就经常出现在连接条件和 where 条件中，合适的主键对于 SQL 性能起到了关键的作用。
b. 如果业务上已经有唯一的递增的不可修改的，关键业务字段，优先考虑使用业务字段做主键，例如订单的单号，单号符合 唯一、递增、不可修改、长度固定、关键业务，这些特性，非常适合做主键。
c. 其次考虑自增 ID，检索的效率最高。
d. 主键默认是聚集索引，建表要先设置聚集索引，再设置主键，才不会被主键默认占用聚集索引。

非聚集索引：

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

null：

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

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

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

外键：

不要设置外键！不要设置外键！不要设置外键！外键只作为业务概念存在，不做数据库约束，否则会带来运维灾难。

建表的例子

--表说明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 的效率，所以索引不是越多越好
a.数据量大的表，维护索引的开销也大，建索引要慎重
b.数据量小的表用不着建索引
c.索引列存在大量重复数据时效果不明显，此时不应该建索引，例如sex列

3.Where

运算

不要在 where 子句中的列上进行函数、算术运算、类型转换或其他表达式运算，否则系统将可能无法正确使用索引
a.函数
尽量避免在where子句中对字段进行函数操作，否则将导致全表扫描。
例如： Where substring(name，1，3) = ‘abc’
应改为 Where name like ‘abc%’
另外IsNull也是经常使用的函数，要避免就要像上面讲的在表设计时规划好
b.计算
尽量避免在where子句中对字段进行表达式操作，否则会导致全表扫描。可以把字段从表达式中移除走，
例如： Where num/2 = 100
应改为 Where num = 100 *2

like/Or/In

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

不等于

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

条件顺序

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序一致。
Where条件我们应该遵循这样一个顺序原则：
[=]->[>=or<=]->[exists or in]->[not exists ]->[>or< or not in or <>]->[函数]

4.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.存储过程

合理使用临时表

a.避免频繁创建和删除临时表，会影响数据库性能
b.存储过程中某个复杂查询反复出现时，可以把此查询结果先存入临时表，提高性能
c.用多个简单语句代替一个复杂语句
如果一个复杂的语句有很多张表要连接，很多时候，根据表和表的逻辑关系，知道一张表和另外一张表如果先做连接，可能会过滤掉很多数据，得到一个小的结果集再做其他连接，会更快。为了提高性能，对这种特别复杂的语句，可以把一句话拆成两句，甚至三句话分布做完，中间结果集，可以以临时表的形式存放。对开发人员来说可能多做了一些事情，不过对SQL来说，大大简化了复杂度，很多时候对性能也会有所帮助。
d.在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定

禁用游标，慎用循环

a.禁止使用游标，游标效率低，编写复杂，用游标不如用循环
b.然而当你需要使用循环的时候，你要考虑清楚有没有办法不使用循环而达到效果，如果一定要用，请务必考虑尽量减少循环的数据量
c.发挥数据库的优势，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效

6.其他SQL优化

a.不要使用Select *，不必要的列带来额外的系统开销
b.用 >= 替代 >
c.控制嵌套调用的层数：存储过程嵌套，视图嵌套
d.尽量避免大事务操作，提高系统并发能力。当使用约束和触发器都能完成同样的功能时，优先考虑使用约束。
e.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
f.尝试用 Union All 替代 Union
Union在去重的过程中会有排序运算，性能不如 Union All
如果我们不在乎结果中是否有重复数据，或者事先知道不会有重复数据，可以使用 Union All 代替 Union
g.Order by
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式
h.用TRUNCATE替代DELETE
TRUNCATE删除全表数据，不记录日志，性能上 TRUNCATE要好过DELETE，在删除表的部分数据时可以使用DELETE，但要注意带上Where条件；全表删除优先考虑使用TRUNCATE
i.With与临时表
公用表表达式（CTE）是SQL Server 2005新推出来的一种语法对象。它的出现提高了语句的重用可维护性，比表变量的效率要高的多。
不过在日常优化过程中，常常发现用户的脚本卡在With这一段，而将它改成临时表之后就会很快过去。通过分析发现With在存放大数据量时的性能比临时表差，因此在设计程序的时候对一些应用比较频繁的脚本，特别是报表在无法预知它的With对象的数量时，建议用临时表来操作会有较好的性能表现。
h.charindex > patindex > like
where charindex(‘包子’,UserName)>0 要优于 where UserName like ‘%包子%’
所以做关键字检索的时候用charindex

7.堵塞与死锁

数据库阻塞

第一个连接占有资源没有释放，而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚，第二个连接会一直等待下去，直到第一个连接释放该资源为止。
长期堵塞严重影响系统体验，所以对数据库操作要及时地提交或者回滚

数据库死锁

第一个连接占有资源没有释放，准备获取第二个连接所占用的资源，而第二个连接占有资源没有释放，准备获取第一个连接所占用的资源。这种互相占有对方需要获取的资源的现象叫做死锁。对于死锁，数据库会牺牲其中一个，并抛出异常
死锁严重影响系统体验，发现死锁脚本，结合业务优化脚本

8.其他数据库性能优化

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