常用的SQL优化技巧

常见的字段类型选择 •字符类型建议采用varchar/nvarchar数据类型 •金额货币建议采用money数据类型 •科学计数建议采用numeric数据类型 •自增长标识建议采用bigint数据类型   (数据量一大,用int类型就装不下,那以后改造就麻烦了) •时间类型建议采用为datetime数据类型 •禁止使用text、ntext、image老的数据类型 •禁止使用xml数据类型、varchar(max)、nvarchar(max) 约束与索引 每张表必须有主键 •每张表必须有主键,用于强制实体完整性 •单表只能有一个主键(不允许为空及重复数据) •尽量使用单字段主键 不允许使用外键 •外键增加了表结构变更及数据迁移的复杂性 •外键对插入,更新的性能有影响,需要检查主外键约束 •数据完整性由程序控制 索引设计准则 •应该对 WHERE 子句中经常使用的列创建索引 •应该对经常用于连接表的列创建索引 •应该对 ORDER BY 子句中经常使用的列创建索引 •不应该对小型的表(仅使用几个页的表)创建索引,这是因为完全表扫描操作可能比使用索引执行的查询快 •单表索引数不超过6个 •不要给选择性低的字段建单列索引 •充分利用唯一约束 •索引包含的字段不超过5个(包括include列) 不要给选择性低的字段创建单列索引 •SQL SERVER对索引字段的选择性有要求,如果选择性太低SQL SERVER会放弃使用• •不适合创建索引的字段:性别、0/1、TRUE/FALSE •适合创建索引的字段:ORDERID、UID等 充分利用唯一索引 唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息,当查询分析器通过唯一索引查找到一条记录则会立刻退出,不会继续查找索引 •表索引数不超过6个, 表索引数不超过6个(这个规则只是携程DBA经过试验之后制定的。。。) •索引加快了查询速度,但是却会影响写入性能 •一个表的索引应该结合这个表相关的所有SQL综合创建,尽量合并 •组合索引的原则是,过滤性越好的字段越靠前 •索引过多不仅会增加编译时间,也会影响数据库选择最佳执行计划 SQL查询 •禁止在数据库做复杂运算 •禁止使用SELECT * •禁止在索引列上使用函数或计算 •禁止使用游标 •禁止使用触发器… Continue reading 常用的SQL优化技巧

统计信息, SQLServer自动更新和自动创建统计信息选项

统计信息是如何提高SQLServer查询性能的?统计直方图用作在查询执行计划中查询优化器的选择依据。如果一个查询谓词包含统计信息的列,那么查询优 化器不需要预测该查询中影响行数,因此,查询优化器有足够的信息去创建执行计划。SQLServer创建执行计划有一下几种不同的方式: 统计信息会在每个新创建的索引中自动创建统计信息。 如果数据库中AUTO_CREATE_STATISTICS被设置为ON,SQLServer将会自动对查询中用到的,且没有索引的列自动创建统计信息。 AUTO_CREATE_STATISTICS选项: 当把该选项设为ON时,查询优化器会对在谓词中使用的到列,如果这些列的统计信息不可用,则会单独对每列创建统计信息。这些统计信息对创建一个查询 计划非常必要。它们创建于那些现有统计对象中不存在直方图的列上,名字包括列名和对象ID的十六进制格 式:_WA_Sys_<column_name>_<XXXX>。这些统计信息用于查询优化器决定使用何种优化后的执行计划。 可以通过以下语句启用自动统计信息创建功能: ALTER DATABASE[你的库名] SET AUTO_CREATE_STATISTICS ON Auto Update Statistics选项: 统计信息会在查询编译或者执行缓存执行计划前被检查。当在以下情况下,统计信息会被认为过期: 1、  在一个空表中有数据的改动。 2、  当统计信息创建时,表的行数只有500或以下,且后来统计对象中的引导列的更改次数大于500. 3、  当表的统计信息收集时,超过了500行,且统计对象的引导列后来更改次数超过500+表总行数的20%时。 4、  在Tempdb中的表,少于6行且最少有6行被更改。