索引聚焦如何使 SQL Server高效 -- 设计（ITPUT 讨论汇总）
本篇文章个人在北京游玩的时候突然想到的...之前就有想写几篇关于索引聚焦的笔记，所以回家到以后就奋笔疾书的写出来发表了
1、 您认为在设计SQL Server对象时，主要会斟酌哪些因素来防止出现性能问题?
讨论汇总——整体设计
l 应该根据系统架构类型或主要操作进行有针对性的设计: 如 OLTP 、OLAP 、ETL 等
l 逻辑清晰，使应用程序更便于开发。有良好的扩展性和维护性，减少数据冗余等
l 在DB管理上，得从CPU、HDD配置动手，在DB开发上，从业务流程、逻辑、功能、DB结构、SQL指令准确设计和实现目标
l 设计视图时应针对某个详细需求，不该过多斟酌其重用性
l 触发器也应该防止应用，斟酌将触发器实现的功能改写到触发语句中。对于调式，性能跟踪来讲，不必触发器也会更直观些
l 先斟酌会出现什么样的性能问题（拜访性能？插入性能？）；接着斟酌数据的变更特点（是小表还是大表？数据的变更频率如何？里头数据重复性如何？），同时也斟酌业务特点（是拜访多，还是插入多，还是即拜访多还有插入多）；然后结合SQLServer的原理和特点进行设计，比如要不要应用索引，是应用聚集索引比较好，还长短聚集索引比较好？表的数据要怎么存储才能充分发挥SQL Server的特性（如从SQL Server数据存储、page、碎片等方面斟酌。还有要不要应用读写分离？要不要从整个数据库级别也斟酌？）；最后，斟酌对设计实现后的对象，进行测试，然后寻觅最优的方案
l 逻辑数据库和表的设计。数据库的逻辑设计、包括表与表之间的关系是优化关系型数据库性能的核心。一个好的逻辑数据库设计可认为优化数据库和应用程序打下良好的基础。标准化的数据库逻辑设计包括用多的、有互相关系的窄表来取代很多列的长数据表
l 读写分离架构（如呈文数据库和交易数据库部署分离等）；存眷物理存储；文件布局(数据文件和日记文件断绝，日记文件最好应用高速设备)
l 斟酌数据库范围和重点表的范围，数据库文件组以及内部分区也须要斟酌在内，这里可以跟磁盘一同斟酌
l 硬件的二级缓存会影响，把数据、日记、索引放到不同的I/O设备上，增长读取速度。数据量（尺寸）越大，提高I/O越重要.
讨论汇总——表及索引设计
l 对表的应用场景进行分析，分为日记表、参数表和频繁更新删除表
l 公道的表数据量预算，分为大表、中表、小表，斟酌大表分区/分片
l 清晰的逻辑和物理模型设计，要准从第三范式，必要时也须要反范式（参考上面的某些场景）
l 逻辑结构上可以适当冗余，防止太多表关联；物理结构上选择性能高的磁盘策略，表分区，读写分离
l 设计表对象时首先斟酌功能，再斟酌性能。如果表中字段过多，斟酌拆分对多个小对象（小表）；如果表中有拜访频率不高，但有需较大存储空间的字段，斟酌拆分另外存储
l 斟酌好archive 机制，保证线上业务表数据量不能太大，其余的到历史表，不然SQL Server 单表到亿级别，怎么都不好使
l 根据业务需求，给各种数据选择适当的数据类型，勤俭数据空间的同时提高效率
l 用占用空间尽量小的数据类型存储数据, 如date, smalldatetime、smallint、tinyint等
l 设计索引时先斟酌查询频率和更新频率，切忌为偶尔执行的语句建立索引，建立复合索引时需斟酌列的可选择性和应用频率来肯定先后次序
l 再次对表的索引进行评估（须要应用开发人员的介入，获取其SQL），开端构建索引，从索引列的选择性、索引列的倾斜度，索引列的应用频度、索引列的应用次序、表索引的个数的综合权衡
l 索引的效率也很重要，详细要看业务会怎么样去用，设计公道的索引组合，同时须要定期跟踪
l 创建高效的索引, 应用索引的包括列、过滤索引等功能
讨论汇总——tempdb
l ,tempdb在SQL Server停掉,重启时会自动的drop,re-create. 根据model数据库会默认建立一个新的8MB(mdf file:8MB;ldf file:1MB， ecoverymodel为simple
l tempdb对IO的要求比较高，最好分配到高IO的磁盘上且与其他的数据文件分到不必的磁盘上,以提高读写效率
l 应该根据根据CPU个数来配置tempdb的文件数（有几个CPU建立几个文件）
个人弥补：tempdb 的文件初始巨细可以设置，通过ALTER DATABASE MODIFY FILE，或者界面操作直接设置文件的初始巨细，这个设置在重启SQL服务时，重建tempdb会应用这个设置值
个人弥补
l 公道分别数据库。设计开始的时候就斟酌数据库断绝，不要把什么东西都放在一个数据库里头。数据库的分别可以综合斟酌这些方面：业务功能、数据重要性、查询和读取比例、数据存储周期
l 为数据库选择合适的恢复模型（RECOVERY MODEL）。这个对数据库的写入和备份影响比较大
l 把控需求，明白应用数据库的必要性以及数据存储的详细水平
l 明白常规字段的定义（特别是在有多个开发团队的情况下），防止因为设计不同，致使在交互（包括互相查询）数据时带来的性能损失
l 肯定公道的对象应用期。有的人爱好紧密结合当前需求来设计表，结果一段时光以后，可能因为某些变更，不得不修改表结构，修改包括数据的表结构，带来的影响是比较大的；而某些人为了防止这种情况，索性设计一个非常宽松的表，结果表结构的修改频率可能长短常低了，但是性能可能会遭到比较大的影响（比如设计的一些关键字段为了满意未来须要，设计的长度很宽，致使查询性能不好）
争议问题
l 主键应用自增列
支撑应用的观念：
应用系统生成的主键，防止应用复合键，外键总是关联唯一的键字段，不要把社会保障号码（SSN）或身份证号码（ID）选作键
反对应用的观念：
如果一个表可以用自增列作主键，那么这个表的主键可有可无。例如记录网站用户登录信息的日记表。这种表可以不要主键，将登录时光设为聚集索引即可。
GUID生成时须要耗用cpu，也不在斟酌范围。当表没有合适的列设主键时，我会斟酌固定长度的流水号之类的字段当主键。
不管是自增列、GUID，还是流水号当主键，只要表本身字段有合适的字段作主键，哪怕是复合键，只要长度不太长(100字节之内可斟酌，50之内最合适)，以及字段数不太多（3个是能接受的范围，5个也可斟酌），就应该应用这些字段来当主键。理由是：在表的join以及数据查询中，真正被查询的，还是这些字段。伪主键们除了占用空间，没有一点好处。
如果将一个自增列设为主键，它的好处在哪儿？这样看起来，就是为了给表安一个主键而去create的。你可能会说，自增列当主键，极大减少page spliting，减 少碎片；聚集索引包括在每个非聚集索引的尾部，极大减少索引size等等。我却觉得：1. 一个表的主键，最好能反映出这个表的逻辑设计，意思是，我一看这个表的主键，就大致晓得这个表存的什么内容。2:就算你应用了自增列当主键，但实际上这个表的逻辑主键，你还是必须得建立这样的索引。因为数据检索时，往往还是根据这个条件来查询的。自增列的主键，也不会用来当外键，因为它的值极度不靠谱。这样你和别的表关联时，还是仍然须要应用表的逻辑主键字段来关联。所以这样的自增列主键，是多余的，没有必要的
个人的观念：
重点是根据应用场景公道选择，不是绝对的用或不必某种。
对于一个表来讲，主键不是必须的，聚焦索引也不是必须的。
从功能上来讲，主键是对表中数据的一种约束（唯一、不允许NULL值），我们通过唯一索引（或约束），并且在字段上设置 NOT NULL属性可以达到同样的效果。所以玉键是可以被取代的，这也说明它不是必需的了（当然，就算没有等同的可取代的功能，从业务上来讲，也并不是每个表都要有一个能够确保数据唯一的东东）。
在提主键的时候，会提聚焦索引，主要是因为主键默认是聚焦索引（如果表中原来没有聚焦索引，并且没有指定NONCLUSTERED的情况下），这表明主键并不是聚焦索引，只是可以把它设置为聚焦索引
所以，独自讨论自增列做主键没有任何意思，我们增长一个自增列，并且把它确认它是唯一和非NULL值的，对于我们的业务数据而言，不存在任何意思（多余和没有必要）。
须要斟酌的，是主键作为聚焦索引的情况，当主键为聚焦索引时，我们要斟酌的，除了主键的特性外，还要斟酌聚焦索引的特性。聚焦索引是和数据存储在一同的，它决议数据的存储次序，表中数据存储的非叶子层是聚焦索引值（叶子层是数据）；另外，聚焦索引键长短聚焦索引的行定位器（指向数据记录的指针，如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当须要使聚集键唯一以用于非聚集索引中时，才添加该值）。如果表中没有聚焦索引，那么非聚焦索引的行定位器是指向行的指针（由文件标识符 (ID)、页码和页上的行数生成的行 ID (RID)）。很显然，非聚焦索引不依赖聚焦索引，而数据存储我们也并不一定须要保证按照某个次序来存储，所以聚焦索引也不是必需的。但是从效率上来讲，次序读取比行ID这种无序读取更有效率，所以对于频繁查询的表，聚焦索引是须要的。
结合前面的信息我们晓得，对于可能涉及比较频繁查询的情况，是须要聚焦索引的（确保进行有效的次序读取）；同时我们也晓得，聚焦索引键值长短聚焦索引的行定位器（存储在非聚焦索引的叶子层），所以聚焦索引的宽度应该要尽可能小（每个非聚焦索引中都要存一次，当然是越小空间占用越少，I/O效率越高），确保唯一（这样就没有必要为了保证唯一去加那个附加载的值了）；另外，NULL值没有比较意思，所以最好是非NULL；数据的写入次序最好是与聚焦索引键值生成次序差不多，并且频繁的修改尽量不会损坏次序（这样减少碎片的发生）；最后，如果聚焦索引的定义发生修改，那就相当于表和索引都要重新组织存储一次，所以最好的情况是，聚焦索引列定义基本上不会被修改。结合评估这些所有的情况，如果业务数据列是没有合适的，那么自增列会是一个不错的选择。
当然，也有人可能会说，聚焦索引的检索效率是最高的，用自增列做主键，是否是浪费了这个最高效的机遇。实际上，这个有点误区，聚焦索引的叶子结点是数据，非聚焦索引的叶子结点是行定位器，这意味着，如果表中的数据不只一列的话，聚焦索引Seek所要加载的页可能比非聚焦索引多（因为它的叶子层是数据，占用的空间比非聚焦索引的行定位器要多），它的好处是SEEK到键值的时候，也就可以直接取出对应的记录了（都在同一页上）；而非聚焦索引还要通过行定位器去拿对应的记录；但是如果我们要的数据在索引中就全部包括的话，聚焦索引的效率就可能要低一些了。另外一个就是SCAN的情况，很显然，非聚焦索引涉及的PAGE比聚焦索引少，SCAN会更有优势。不是所有的查询都可以做SEEK，一般查询频繁的表也有多种常用的查询组合，所以整体来讲，业务数据列做主键并不是在所有的场景下都能体现出优势。
后续讨论话题：
2、 您认为在T-SQL编写（包括存储过程、函数和视图）上，哪些因素会影响SQL Server效率?
3、 在设计数据库操作程序上，您认为应该注意哪些事项，以确保能够有效地应用数据库?
4、 在您的SQL Server应用过程中，有哪些令您非常困惑的性能问题 ?
文章结束给大家分享下程序员的一些笑话语录：
人工智能今天的发展水平：8乘8的国际象棋盘其实是一个体现思维与创意的强大媒介。象棋里蕴含了天文数字般的变化。卡斯帕罗夫指出，国际象棋的合法棋步共有1040。在棋局里每算度八步棋，里面蕴含的变化就已经超过银河系里的繁星总数。而地球上很少有任何数量达到这个级别。在金融危机之前，全世界的财富总和大约是1014人民币，而地球人口只有1010。棋盘上，所有可能的棋局总数达到10120，这超过了宇宙里所有原子的总数！经典语录网
---------------------------------
原创文章 By
索引和聚焦
---------------------------------
