国外 图片网站,深圳国内网站设计公司,营销单页网站模板,ysl免费网站建设原文地址 常见的字段类型选择 #xff11;.字符类型建议采用varchar/nvarchar数据类型 #xff12;.金额货币建议采用money数据类型 #xff13;.科学计数建议采用numeric数据类型 #xff14;.自增长标识建议采用bigint数据类型 (数据量一大#xff… 原文地址 常见的字段类型选择 .字符类型建议采用varchar/nvarchar数据类型 .金额货币建议采用money数据类型 .科学计数建议采用numeric数据类型 .自增长标识建议采用bigint数据类型 (数据量一大用int类型就装不下那以后改造就麻烦了) .时间类型建议采用为datetime数据类型 .禁止使用text、ntext、image老的数据类型 .禁止使用xml数据类型、varchar(max)、nvarchar(max) 约束与索引 每张表必须有主键 1.每张表必须有主键用于强制实体完整性 2.单表只能有一个主键不允许为空及重复数据 3.尽量使用单字段主键 每张表必须有主键 1.外键增加了表结构变更及数据迁移的复杂性 2.外键对插入更新的性能有影响需要检查主外键约束 3.数据完整性由程序控制 每张表必须有主键 1.新加的表所有字段禁止NULL 新表示为什么不允许NULL 允许NULL值会增加应用程序的复杂性。你必须得增加特定的逻辑代码以防止出现各种意外的bug 三值逻辑所有等号“”的查询都必须增加isnull的判断 NullNull、Null!Null、not(NullNull)、not(Null!Null)都为unknown不为true。 举例来说明一下 你想来找查找除了name等于aa的所有数据然后你就不经意间用了SELECT * FROM NULLTEST WHERE NAME’aa’ 结果发现与预期不一样事实上它只查出了namebb而没有查找出nameNULL的数据记录 那我们如何查找除了name等于aa的所有数据只能用ISNULL函数了 SELECT * FROM NULLTEST WHERE ISNULL(NAME,1)’aa’ 但是大家可能不知道ISNULL会引起很严重的性能瓶颈 ,所以很多时候最好是在应用层面限制用户的输入确保用户输入有效的数据再进行查询。 旧表新加字段需要允许为NULL避免全表数据更新 长期持锁导致阻塞这个主要是考虑之前表的改造问题 索引设计准则 1.应该对 WHERE 子句中经常使用的列创建索引 2.应该对经常用于连接表的列创建索引 3.应该对 ORDER BY 子句中经常使用的列创建索引 4.不应该对小型的表仅使用几个页的表创建索引这是因为完全表扫描操作可能比使用索引执行的查询快 5.单表索引数不超过6个 6.不要给选择性低的字段建单列索引 7.充分利用唯一约束 8.索引包含的字段不超过5个包括include列 不要给选择性低的字段创建单列索引 1.SQL SERVER对索引字段的选择性有要求如果选择性太低SQL SERVER会放弃使用 2.不适合创建索引的字段性别、0/1、TRUE/FALSE 3.适合创建索引的字段ORDERID、UID等 充分利用唯一索引 唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息当查询分析器通过唯一索引查找到一条记录则会立刻退出不会继续查找索引。 表索引数不超过6个这个规则只是携程DBA经过试验之后制定的。。。 1.索引加快了查询速度但是却会影响写入性能 2.一个表的索引应该结合这个表相关的所有SQL综合创建尽量合并 3.组合索引的原则是过滤性越好的字段越靠前 4.索引过多不仅会增加编译时间也会影响数据库选择最佳执行计划 SQL查询 1.禁止在数据库做复杂运算 2.禁止使用SELECT * 3.禁止在索引列上使用函数或计算 4.禁止使用游标 5.禁止使用触发器 6.禁止在查询里指定索引 7.变量/参数/关联字段类型必须与字段类型一致 8.参数化查询 9.限制JOIN个数 10.限制SQL语句长度及IN子句个数 11.尽量避免大事务操作 12.关闭影响的行计数信息返回 13.除非必要SELECT语句都必须加上NOLOCK 14.使用UNION ALL替换UNION 15.查询大量数据使用分页或TOP 16.递归查询层级限制 17.NOT EXISTS替代NOT IN 18.临时表与表变量 19.使用本地变量选择中庸执行计划 20.尽量避免使用OR运算符 21.增加事务异常处理机制 22.输出列使用二段式命名格式 禁止在数据库做复杂运算 1.XML解析 2.字符串相似性比较 3.字符串搜索Charindex 4.复杂运算在程序端完成 禁止使用SELECT * 1.减少内存消耗和网络带宽 2.给查询优化器有机会从索引读取所需要的列 3.表结构变化时容易引起查询出错 禁止在索引列上使用函数或计算 在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。 假设在字段Col1上建有一个索引则下列场景将无法使用到索引 ABS[Col1]1 [Col1]19 再举例说明一下 像上面这样的查询将无法用到O_OrderProcess表上的PrintTime索引所以我们应用使用如下所示的查询SQL 禁止在索引列上使用函数或计算 假设在字段Col1上建有一个索引则下列场景将可以使用到索引 [Col1]3.14 [Col1]100 [Col1] BETWEEN 0 AND 99 [Col1] LIKE ‘abc%’ [Col1] IN(2,3,5,7) LIKE查询的索引问题 1.[Col1] like “abc%” –index seek 这个就用到了索引查询 2.[Col1] like “%abc%” –index scan 而这个就并未用到索引查询 3.[Col1] like “%abc” –index scan 这个也并未用到索引查询 我想从上而三个例子中大家应该明白最好不要在LIKE条件前面用模糊匹配否则就用不到索引查询。 禁止使用游标 关系数据库适合集合操作也就是对由WHERE子句和选择列确定的结果集作集合操作游标是提供的一个非集合操作的途径。一般情况下游标实现的功能往往相当于客户端的一个循环实现的功能。 游标是把结果集放在服务器内存并通过循环一条一条处理记录对数据库资源特别是内存和锁资源的消耗是非常大的。再加上游标真心比较复杂挺不好用的尽量少用吧。 禁止使用触发器 触发器对应用不透明应用层面都不知道会什么时候触发触发器发生也也不知道感觉莫名……。 禁止在查询里指定索引 With(indexXXX) 在查询里我们指定索引一般都用With(indexXXX) 1.随着数据的变化查询语句指定的索引性能可能并不最佳 2.索引对应用应是透明的如指定的索引被删除将会导致查询报错不利于排障 3.新建的索引无法被应用立即使用必须通过发布代码才能生效 变量/参数/关联字段类型必须与字段类型一致这是我之前不太关注的 避免类型转换额外消耗的CPU引起的大表scan尤为严重. 看了上面这两个图我想我不用解释说明大家都应该已经清楚了吧。 如果数据库字段类型为VARCHAR在应用里面最好类型指定为AnsiString并明确指定其长度 如果数据库字段类型为CHAR在应用里面最好类型指定为AnsiStringFixedLength并明确指定其长度 如果数据库字段类型为NVARCHAR在应用里面最好类型指定为String并明确指定其长度。 参数化查询 以下方式可以对查询SQL进行参数化 sp_executesql Prepared Queries Stored procedures 用图来说明一下哈哈。 限制JOIN个数 1.单个SQL语句的表JOIN个数不能超过5个 2.过多的JOIN个数会导致查询分析器走错执行计划 3.过多JOIN在编译执行计划时消耗很大 限制IN子句中条件个数 在 IN 子句中包括数量非常多的值数以千计可能会消耗资源并返回错误 8623 或 8632要求IN子句中条件个数限制在100个以内。 尽量避免大事务操作 1.只在数据需要更新时开始事务减少资源锁持有时间 2.增加事务异常捕获预处理机制 3.禁止使用数据库上的分布式事务 用图来说明一下 也就是说我们不应该在1000行数据都更新完成之后再commit tran,你想想你在更新这一千行数据的时候是不是独占资源导致其它事务无法处理。 关闭影响的行计数信息返回 在SQL语句中显示设置Set Nocount On取消影响的行计数信息返回减少网络流量 除非必要SELECT语句都必须加上NOLOCK 关闭影响的行计数信息返回 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据其他事务设 置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错向用户显示从未提交过的数据或者导致用户两次看到记录或根本看不到记录。 使用UNION ALL替换UNION UNION会对SQL结果集去重排序增加CPU、内存等消耗。 查询大量数据使用分页或TOP 合理限制记录返回数避免IO、网络带宽出现瓶颈 递归查询层次限制 使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环 临时表与表变量 使用本地变量选择中庸执行计划 在存储过程或查询中访问了一张数据分布很不平均的表格这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上造成High CPU及大量IO Read等问题使用本地变量防止走错执行计划。 采用本地变量的方式SQL在编译的时候是不知道这个本地变量的值这时候SQL会根据表格里数据的一般分布“猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少生成的计划都是一样的。这样的计划一般会比较中庸一些不一定是最优的计划但一般也不会是最差的计划 如果查询中本地变量使用了不等式运算符查询分析器使用了一个简单的 30% 的算式来预估 Estimated Rows (Total Rows * 30)/100 如果查询中本地变量使用了等式运算符则查询分析器使用精确度 * 表记录总数来预估 Estimated Rows Density * Total Rows 尽量避免使用OR运算符 对于OR运算符通常会使用全表扫描考虑分解成多个查询用UNION/UNION ALL来实现这里要确认查询能走到索引并返回较少的结果集 增加事务异常处理机制 应用程序做好意外处理及时做Rollback。 设置连接属性 “set xact_abort on” 输出列使用二段式命名格式 二段式命名格式表名.字段名 有JOIN关系的TSQL字段必须指明字段是属于哪个表的否则未来表结构变更后有可能发生Ambiguous column name的程序兼容错误 架构设计 1.读写分离 2.schema解耦 3.数据生命周期 读写分离 1.设计之初就考虑读写分离哪怕读写同一个库有利于快速扩容 2.按照读特征把读分为实时读和可延迟读分别对应到写库和读库 3.读写分离应该考虑在读不可用情况下自动切换到写端 Schema解耦 禁止跨库JOIN 数据生命周期 根据数据的使用频繁度对大表定期分库归档 主库/归档库物理分离 日志类型的表应分区或分表 对于大的表格要进行分区分区操作将表和索引分在多个分区通过分区切换能够快速实现新旧分区替换加快数据清理速度大幅减少IO资源消耗 频繁写入的表需要分区或分表 自增长与Latch Lock 闩锁是sql Server自己内部申请和控制用户没有办法来干预用来保证内存里面数据结构的一致性锁级别是页级锁
