SQL调优

SQL Server 查询

查询的时候应该尽量按照复合索引中的顺序来做条件查询；（比如IXC中spInterActiveInstance_GetByIDToStat条件and ProcessState<>99的位置）；
如果在程序中有For或者是Foreach，在存储过程中又有IF Exists，那就要看是否可以在表中加入复合索引了，IF Not Exists可以转换为IF Exists来使用索引；
在查询中尽量少使用*或者全表字段来查询数据。第一，返回的数据集比较大；第二，产生更多的IO操作；第三，会使用到Bookmark lookup的查询计划，性能有所下降；如果能使用到覆盖索引来查询是最理想的；
下面关于查询的性能比较
返回行数较多：索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
返回行数较少：索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描
比较难理解的可能是：堆集的非聚集索引>聚集的非聚集索引。下面是个人见解，堆表就是没有聚集索引的表，实质上就是在一个没有聚集索引的表中加入非聚集索引和一个在聚集表中加入同一字段的非聚集索引；（聚集）非聚集索引-定位器-聚集索引-Bookmark lookup-返回指定字段值；（堆集）非聚集索引-定位器-对应的数据行的指针-返回指定字段值；
不要在Where字句中的列名加函数；如果有对列值进行判断的，尽量把操作符（比如+、-、*等）放到=号的右边；
 
索引 By Viajar

复合索引（where A And B）如果没有对A和B做单一索引，查询的时间为a；如果对A做单一索引，查询时间为b；如果对B做单一索引，查询时间为c；如果对A、B做复合索引，查询的时间为d，那时间的比较就应该是a>b=c>d；（比如spMsgReader_Distribute中使用WHERE InfoID=@infoID AND UserID=@userID，插入7000次的时候就很明显地看到性能了；（8秒比1：50秒）注意升序和降序的区别？
当高选择性的非聚集索引达到5%的选择性时，该索引是非常有用的；
关于复合索引的属性列位置问题，应该把高选择性的列放到最左边（已前就忽略了这个高选择性的位置），那个通过IP和UrlID的SARG中我们可以创建一个IP和UrlID的复合索引，通过业务来说，我们测试的时候可能是IP的重复量比较大，但是在生成环境中，应该是UrlID的重复量会比较大，所以就IP放到复合索引的最左边；
对EasyURL跳转功能中，需要通过输入地址来查询目标地址，这个数据库查询可以使用覆盖索引，查询的速度是最快的；
当返回一个聚集索引列和一个非聚集索引列，并且是使用非聚集索引属性列作为SARG，那么这也是一个索引覆盖查询，因为在非聚集索引中包括聚集索引，所以直接在B-Tree就返回了数据，不用查询数据页；
在查询Select语句中用Where字句限制返回的行数和列数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。
对长字符列的索引，我们可以使用哈希索引，也就是CHECKSUM函数，具体用法可以看帮助文档；通常情况下我们都没有对长字符列建立索引的，而且我们的业务逻辑中对长字符作为内容的搜索也是不常见的，但是如果有需要，可以考虑哈希索引，有些同学可能会说为什么不用全文索引呢？因为毕竟全文搜索是要花费很多磁盘空间和IO操作的；
 
数据库设计

如果某表经常出现死锁，那就要做对象职责分离，就是把插入、更新、删除等分离；
在设计或创建表的时候，我们往往会把Id字段设置为聚集索引，但是我们这样的习惯可能不是最优的，因为聚集索引和非聚集索引的区别就是一个是物理存储的，所以在查询的时候如果使用聚集索引的话，可能会比非聚集索引要开，因为关系到数据页的分布，因此我们有时可以根据我们的需要来设置聚集索引，比如待办待阅一般都是按照时间来倒排的，那我们可以让日期字段来做聚集索引；
当表的数据量比较大，而且一些基本的优化也不能满足到需求，那就可以考虑：纵向、横向分割表，减少表的尺寸(sp_spaceuse)；
表字段如果不为空的，就不应该设置允许空，第一：这里可以确保数据的完整性；第二：依稀记得跟性能有关，但是记不起，欢迎补充；
在表中设计索引属性的时候，我们都会看到填充因子，通常情况下我们都是不去理会它的，如果我们去了解它，也许我们会有不错的回报。（以下是个人言论，有可能存在BUG，不久将来会进一步确认是否正确。）
填充因子的意思就是当数据页的数据达到多少百分比的时候就换页，相当以在数据页中预留多少空间来应对数据的增长，比如聚集索引，如果我们选择了100%或者0%，意思就是我们要把整个数据页都填充完整；
如果该表有一个以ID自增为聚集索引，而且我们设置的这个比值比较小，就会有可能造成比较多的磁盘碎片，查询时的IO消耗比较大，这是因为聚集索引在数据页的表现是物理存储，索引每个数据页都会留下比较多的数据页空间，而且是永远都不会给使用到的；
只有当索引被创建或者是重建时才会重新设置数据页的百分比分布，当做插入数据后，改数据页的比例就会小于这个百分比了；

数据库调优积累系列（5）：专业术语
性能调优
测试基线
测试用例，测试场景
聚集索引、非聚集索引
索引覆盖
复合索引、组合索引、单一索引、哈希索引
高选择性
计划缓存、执行计划、编译、重编译
缓存命中率
查询计划（编译-优化-执行）
查询优化器、查询执行引擎
物理操作符、逻辑操作符
行定位器
谓词
填充因子
Index Seek（索引查找）、Index Scan（索引扫描）、Bookmark Lookup（书签查找）
共享锁（S锁）、独占锁、排它锁（X锁）
独占锁（Exclusive Lock）、共享锁（Shared Lock）、更新锁（Update Lock）、乐观锁（Optimistic Lock）、悲观锁（Pessimistic Lock）
快照
分区
事件探查器
Profiler
ShowPlans
OPTION(RECOMPILE)
优化猜想（自创词汇）
 
起因
在执行计划中看到了Bookmark Lookup操作符，于是就上网去逛逛，最后发现这篇牛文；
主要内容摘要
非聚集索引有一个与聚集索引中相似的B树索引结构，但是他对数据行的顺序不起作用，其最低行包含非聚集索引的键值，每个键值项都有指针指向包含该键值的数据行。对于堆集，该指针是指向行的指针，对于聚集表，则是聚集索引键。该指针叫做行定位器；
在基于非聚集索引查找数据时，还有另外一种情形，那就是如果放回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。这种情况，叫做索引覆盖；
SQL Server在查找数据时，服务器先使用和使用聚集索引相同的查找方法找到该索引的行定位器------Bookmark，然后通过行定位器来找到所需要的数据，这种通过行定位器查找数据的方式就是Bookmark Lookup；
查询性能比较：
返回行数较多：索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
返回行数较少：索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描
一个堆集在sysindexes内有一行，其indid=0；
某个表和视图的聚集索引在sysindexes内有一行，其indid=1，root列指向聚集索引B树的顶端；
某个表或视图的非聚集索引在索引在sysindexes内也有一行，其indid值从2到250，root列指向非聚集索引B树的顶端；
SQL Server 的数据文件中有一类是IAM，即索引分配映射表，它存储有关表和索引所使用的扩展盘区信息；
Bookmark Lookup逻辑运算符和物理运算符使用书签（行 ID 或聚集键）在表或聚集索引内查找相应的行；
感想
里面的"在一个聚集表上使用非聚集索引进行查询，其性能低于在堆集上使用非聚集索引进行查询"（这句话不是完全正确的，因为当返回的字段包含了非聚集索引和聚集索引的列值，那么就会产生索引覆盖，而堆集上使用非聚集索引的返回字段只能是只身才会形成索引覆盖）
第一次看这篇文章的时候感觉是在看天书，完全不懂；随着知识的积累，偶尔回来几次看这篇文章，感觉一步步的理解了一点点内容，看来这篇文章还得继续看多几遍啊。！

起因
在EasyURL的数据库（sql2000）中使用了sp_executesql来执行存储过程，使得无法使用计划缓存，虽然最后通过新增存储过程来解决这个问题，但是一直没有得到理论上的证实，所以上网以"sp_executesql"为关键字搜索了，最后找到这篇文章；
主要内容摘要
直接拼SQL
参数化SQL
调用存储过程
这里不得不提.NET SqlClient组件的一个龌龊：如果你的参数中包含varchar或者char类型的参数，你在Parameters.Add的时候又没有指定长度，它都会根据你实际传入的字符串长度(假设是n)给你重新定义成nvarchar（n） 。如：select * from mytable where col1 = @p1，你设置@p1为'123456'，实际传到sql这边的命令是：exec sp_executesql N'select * from mytable where col1 = @p1',N'@p1 nvarchar(6)',@p1=N'123456'。这样，系统缓存中实际存储的sql是：(@p1 nvarchar(6))select * from mytable where col1 = @p1。看到了吧？如果你的输入参数变动比较多，那么看起来同样的一条语句，会被编译很多次，在缓存中存储很多份。cpu和内存都浪费了。这也是在《写有效率的SQL查询IV》中建议的使用最强类型参数匹配的原因之一。
感想
通过自己的知识和实践得到了某些东西，后来又等到理论证实，这种感觉真的很棒、很爽（主要是发现和解决问题的能力）；
虽然这是一个比较简单的问题，或者其它人也很容易的发现并解决这个问题，但是能有这么一件开心的事，我们还需要求什么呢？！
感觉它描述的比我好，主要还有例子作为解说；