SQL 优化总结

SQL 优化总结

（一）SQL Server 关键的内置表、视图

　　1. sysobjects

        SELECT name as '函数名称',xtype as XType  FROM  sysobjects  where xtype in ('V','FN','IF','TF');  -- 查找数据中全部的视图、标量函数、内联表函数、表函数

列名	数据类型	描述
name	sysname	对象名,常用列
id	int	对象标识号
xtype	char(2)	对象类型。常用列。xtype可以是下列对象类型中的一种：  C = CHECK 约束　　D = 默认值或 DEFAULT 约束　　F = FOREIGN KEY 约束　　L = 日志　　FN = 标量函数  IF = 内嵌表函数 　　P = 存储过程 　　PK = PRIMARY KEY 约束（类型是 K） 　　RF = 复制筛选存储过程   S = 系统表 　　TF = 表函数 　　TR = 触发器 　　U = 用户表 　　UQ = UNIQUE 约束（类型是 K）  V = 视图 　　X = 扩展存储过程
uid	smallint	所有者用户对象编号
info	smallint	保留。仅限内部使用
status	int	保留。仅限内部使用
base_schema_ ver	int	保留。仅限内部使用
replinfo	int	保留。供复制使用
parent_obj	int	父对象的对象标识号（例如，对于触发器或约束，该标识号为表 ID）。
crdate	datetime	对象的创建日期。
ftcatid	smallint	为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为 0
schema_ver	int	版本号，该版本号在每次表的架构更改时都增加。
stats_schema_ ver	int	保留。仅限内部使用。
type	char(2)	对象类型。可以是下列值之一：   C = CHECK 约束   D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束   FN = 标量函数 IF = 内嵌表函数  K = PRIMARY KEY 或 UNIQUE 约束   L = 日志 P = 存储过程 R = 规则  RF = 复制筛选存储过程  S = 系统表  TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程
userstat	smallint	保留。
sysstat	smallint	内部状态信息
indexdel	smallint	保留
refdate	datetime	留用
version	int	保留
deltrig	int	保留
instrig	int	保留
updtrig	int	保留
seltrig	int	保留
category	int	用于发布、约束和标识
cache	smallint	保留

　　 2. syscolumns

SELECT name FieldName ,xtype XType,number Number  from syscolumns where id=object_id('User');--根据对象名称获取 此对象的列或参数信息

name sysname 列名或过程参数的名称。   
  id int 该列所属的表对象   ID，或与该参数关联的存储过程   ID。   
  xtype tinyint systypes   中的物理存储类型。   
  typestat tinyint 仅限内部使用。   
  xusertype smallint 扩展的用户定义数据类型   ID。   
  length smallint systypes   中的最大物理存储长度。   
  xprec tinyint 仅限内部使用。   
  xscale tinyint 仅限内部使用。   
  colid smallint 列或参数   ID。   
  xoffset smallint 仅限内部使用。   
  bITpos tinyint 仅限内部使用。   
  reserved tinyint 仅限内部使用。   
  colstat smallint 仅限内部使用。   
  cdefault int 该列的默认值   ID。   
  domain int 该列的规则或   CHECK   约束   ID。   
  number smallint 过程分组时（0   表示非过程项）的子过程号。   
  colorder smallint 仅限内部使用。   
  autoval varbinary(255) 仅限内部使用。   
  offset smallint 该列所在行的偏移量；如果为负，表示可变长度行。   
  status tinyint 用于描述列或参数属性的位图：   
    
  0x08   =   列允许空值。   
  0x10   =   当添加   varchar   或   varbinary   列时，ANSI   填充生效。保留   varchar   列的尾随空格，保留   varbinary   列的尾随零。   
  0x40   =   参数为   OUTPUT   参数。   
  0x80   =   列为标识列。   
    
  type tinyint systypes   中的物理存储类型。   
  usertype smallint systypes   中的用户定义数据类型   ID。   
  printfmt varchar(255) 仅限内部使用。   
  prec smallint 该列的精度级别。   
  scale int 该列的小数位数。   
  iscomputed int 表示是否已计算该列的标志：   
  0   =   未计算。   
  1   =   已计算。   
  isoutparam int 表示该过程参数是否是输出参数：

1   =   真。   
  0   =   假。   
  isnullable int 表示该列是否允许空值：   
  1   =   真。   
  0   =   假。

   3 Sql server中根据存储过程中的部分信息查找存储过程名称的方法【视图和Function】

　　

　　1、查询的语句：

　　select a.id,b.name,a.*,b.* from syscomments a
　　join sysobjects b on a.id=b.id
　　where b.xtype='P' and a.text like '%usp_cm%'

　　b.xtype='P'指定在什么类型的范围进行搜索

　　‘%usp_cm%’就是你能记得的存储过程中的内容。

　　（select t.name,t.text from all_source t where type = 'PROCEDURE' and text like '%没有找到相应的批次%'）

　　2、查找类型：

　　select distinct xtype from sysobjects

　　找到数据库中所有的对象类型

　　P是存储过程

　　V是视图

　　FN是function

　　U是Table

识别低效TSQL，采用最佳实践重构和应用TSQL

　　由于每个程序员的能力和习惯都不一样，他们编写的TSQL可能风格各异，部分代码可能不是最佳实现，对于水平一般的程序员可能首先想到的是编写TSQL实现需求，至于性能问题日后再说，因此在开发和测试时可能发现不了问题。

　　也有一些人知道最佳实践，但在编写代码时由于种种原因没有采用最佳实践，等到用户发飙的那天才乖乖地重新埋头思考最佳实践。

　　我觉得还是有必要介绍一下具有都有哪些最佳实践。

　　1、在查询中不要使用“select *”

　　(1)检索不必要的列会带来额外的系统开销，有句话叫做“该省的则省”;

　　(2)数据库不能利用“覆盖索引”的优点，因此查询缓慢。

　　2、在select清单中避免不必要的列，在连接条件中避免不必要的表

　　(1)在select查询中如有不必要的列，会带来额外的系统开销，特别是LOB类型的列;

　　(2)在连接条件中包含不必要的表会强制数据库引擎检索和匹配不需要的数据，增加了查询执行时间。

　　3、不要在子查询中使用count()求和执行存在性检查

　　(1)不要使用

　　　　　　SELECT column_list FROMtableWHERE0< (SELECTcount(*) FROM table2 WHERE ..)

　　使用

　　　　　　SELECT column_list FROMtableWHEREEXISTS (SELECT*FROM table2 WHERE ...)

(2)当你使用count()时，SQL Server不知道你要做的是存在性检查，它会计算所有匹配的值，要么会执行全表扫描，要么会扫描最小的非聚集索引;

　　(3)当你使用EXISTS时，SQL Server知道你要执行存在性检查，当它发现第一个匹配的值时，就会返回TRUE，并停止查询。类似的应用还有使用IN或ANY代替count()。

　　4、避免使用两个不同类型的列进行表的连接

　　(1)当连接两个不同类型的列时，其中一个列必须转换成另一个列的类型，级别低的会被转换成高级别的类型，转换操作会消耗一定的系统资源;

　　(2)如果你使用两个不同类型的列来连接表，其中一个列原本可以使用索引，但经过转换后，优化器就不会使用它的索引了。例如：　

SELECT column_list FROM small_table, large_table WHERE

　　smalltable.float_column = large_table.int_column

　　在这个例子中，SQL Server会将int列转换为float类型，因为int比float类型的级别低，large_table.int_column上的索引就不会被使用，但smalltable.float_column上的索引可以正常使用。

　　5、避免死锁

　　(1)在你的存储过程和触发器中访问同一个表时总是以相同的顺序;

　　(2)事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量;

　　(3)永远不要在事务中等待用户输入。

　　6、使用“基于规则的方法”而不是使用“程序化方法”编写TSQL

　　(1)数据库引擎专门为基于规则的SQL进行了优化，因此处理大型结果集时应尽量避免使用程序化的方法(使用游标或UDF[User Defined Functions]处理返回的结果集) ;

　　(2)如何摆脱程序化的SQL呢?有以下方法：

　　- 使用内联子查询替换用户定义函数;

　　- 使用相关联的子查询替换基于游标的代码;

　　- 如果确实需要程序化代码，至少应该使用表变量代替游标导航和处理结果集。

　　7、避免使用count(*)获得表的记录数

　　(1)为了获得表中的记录数，我们通常使用下面的SQL语句：

　SELECTCOUNT(*) FROM dbo.orders

　　这条语句会执行全表扫描才能获得行数。

　　(2)但下面的SQL语句不会执行全表扫描一样可以获得行数：

SELECT rows FROM sysindexes

　　WHERE id =OBJECT_ID('dbo.Orders') AND indid <2

　　8、避免使用动态SQL

　　除非迫不得已，应尽量避免使用动态SQL，因为：

　　(1)动态SQL难以调试和故障诊断;

　　(2)如果用户向动态SQL提供了输入，那么可能存在SQL注入风险。

　　9、避免使用临时表

　　(1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;

　　(2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

　　10、使用全文搜索搜索文本数据，取代like搜索

　　全文搜索始终优于like搜索：

　　(1)全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词;

　　(2)实现全文搜索比实现like搜索更容易(特别是复杂的搜索);

　　11、使用union实现or操作

　　(1)在查询中尽量不要使用or，使用union合并两个不同的查询结果集，这样查询性能会更好;

　　(2)如果不是必须要不同的结果集，使用union all效果会更好，因为它不会对结果集排序。

　　12、为大对象使用延迟加载策略

　　(1)在不同的表中存储大对象(如VARCHAR(MAX)，Image，Text等)，然后在主表中存储这些大对象的引用;

　　(2)在查询中检索所有主表数据，如果需要载入大对象，按需从大对象表中检索大对象。

　　13、使用VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)

　　(1)在SQL Server 2000中，一行的大小不能超过800字节，这是受SQL Server内部页面大小8KB的限制造成的，为了在单列中存储更多的数据，你需要使用TEXT，NTEXT或IMAGE数据类型(BLOB);

　　(2)这些和存储在相同表中的其它数据不一样，这些页面以B-Tree结构排列，这些数据不能作为存储过程或函数中的变量，也不能用于字符串函数，如REPLACE，CHARINDEX或SUBSTRING，大多数时候你必须使用READTEXT，WRITETEXT和UPDATETEXT;

　　(3)为了解决这个问题，在SQL Server 2005中增加了VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)，这些数据类型可以容纳和BLOB相同数量的数据(2GB)，和其它数据类型使用相同的数据页;

　　(4)当MAX数据类型中的数据超过8KB时，使用溢出页(在ROW_OVERFLOW分配单元中)指向源数据页，源数据页仍然在IN_ROW分配单元中。

　　14、在用户定义函数中使用下列最佳实践

　　不要在你的存储过程，触发器，函数和批处理中重复调用函数，例如，在许多时候，你需要获得字符串变量的长度，无论如何都不要重复调用LEN函数，只调用一次即可，将结果存储在一个变量中，以后就可以直接使用了。

　　15、在存储过程中使用下列最佳实践

　　(1)不要使用SP_xxx作为命名约定，它会导致额外的搜索，增加I/O(因为系统存储过程的名字就是以SP_开头的)，同时这么做还会增加与系统存储过程名称冲突的几率;

　　(2)将Nocount设置为On避免额外的网络开销;

　　(3)当索引结构发生变化时，在EXECUTE语句中(第一次)使用WITH RECOMPILE子句，以便存储过程可以利用最新创建的索引;

　　(4)使用默认的参数值更易于调试。

　　16、在触发器中使用下列最佳实践

　　(1)最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;

　　(2)如果能够使用约束实现的，尽量不要使用触发器;

　　(3)不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器;

　　(4)不要在触发器中使用事务型代码。

　　17、在视图中使用下列最佳实践

　　(1)为重新使用复杂的TSQL块使用视图，并开启索引视图;

　　(2)如果你不想让用户意外修改表结构，使用视图时加上SCHEMABINDING选项;

　　(3)如果只从单个表中检索数据，就不需要使用视图了，如果在这种情况下使用视图反倒会增加系统开销，一般视图会涉及多个表时才有用。

　　18、在事务中使用下列最佳实践

　　(1)SQL Server 2005之前，在BEGIN TRANSACTION之后，每个子查询修改语句时，必须检查@@ERROR的值，如果值不等于0，那么最后的语句可能会导致一个错误，如果发生任何错误，事务必须回滚。从SQL Server 2005开始，Try..Catch..代码块可以处理TSQL中的事务，因此在事务型代码中最好加上Try…Catch…;

　　(2)避免使用嵌套事务，使用@@TRANCOUNT变量检查事务是否需要启动(为了避免嵌套事务);

　　(3)尽可能晚启动事务，提交和回滚事务要尽可能快，以减少资源锁定时间。

　　要完全列举最佳实践不是本文的初衷，当你了解了这些技巧后就应该拿来使用，否则了解了也没有价值。此外，你还需要评审和监视数据访问代码是否遵循下列标准和最佳实践。