SQL优化之not in-SQL优化之not in(雅居乐10案例)-存储引擎相关文章

当前位置: 数据库>sqlserver

本页文章导读:

▪SQL优化之not in 阅读目录案例语句分析手段如何优化一直从事运维的工作，免不了优化一些SQL语句，因为本人比较懒的原因，很多经典的案例没有记录下来，深表遗憾。案例语句某大型房地产公司，巡检日期.........

▪SQL优化之not in(雅居乐10案例) 阅读目录案例语句分析手段如何优化一直从事运维的工作，免不了优化一些SQL语句，因为本人比较懒的原因，很多经典的案例没有记录下来，深表遗憾。案例语句某大型房地产公司，巡检日期.........

▪存储引擎相关文章分享大师的blog，并且把主要内容写出来，不敢翻译，以备看了之后忘记可以温习，也推广一下大师的博客Importance of choosing the right LOB storage technique本文大意： N/CHAR：当数据长度都是.........

[1]SQL优化之not in

来源: 发布时间: 2013-10-29

阅读目录

案例语句

分析手段

如何优化

一直从事运维的工作，免不了优化一些SQL语句，因为本人比较懒的原因，很多经典的案例没有记录下来，深表遗憾。

案例语句

某大型房地产公司，巡检日期2013-04-22，问题语句

 1  SELECT COUNT(*)
 2  FROM   ( SELECT    temp.Application ,
 3                     COUNT(*) AS UserCount
 4           FROM      ( SELECT    f.Application
 5                       FROM      myUserRights ur
 6                                 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode
 7                                 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID
 8                       WHERE     u.IsAdmin = 0
 9                                 AND ( IsDisabeld = 0
10                                       OR IsDisabeld IS NULL
11                                     )
12                                 AND ur.UserGUID NOT IN (
13                                 SELECT  UserGUID
14                                 FROM    myUserRoles
15                                 WHERE   RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78' )
16                       GROUP BY  f.Application ,
17                                 ur.UserGUID
18                     ) AS temp
19           GROUP BY  temp.Application
20         ) AS temp2
21         RIGHT   JOIN myApplication a ON a.Application = temp2.Application
22  WHERE  a.Level = 1
23         AND a.Application IN (
24         SELECT  Application
25         FROM    myFunction
26         WHERE   FunctionCode IN ( '', '01010102', '01010104', '01010105',
27                                   '01010111', '01010106', '01010107',
28                                   '01010110', '01010118', '01010604',
29                                   '01010603', '01010202', '01010203',
30                                   '01010205', '01010206', '01010207',
31                                   '01010301', '01010302', '01010303',
32                                   '01010304', '01010308', '01010305',
33                                   '01010306', '01010310', '01010309',
34                                   '01010407', '

[2]SQL优化之not in(雅居乐10案例)

来源: 发布时间: 2013-10-29

阅读目录

案例语句

分析手段

如何优化

一直从事运维的工作，免不了优化一些SQL语句，因为本人比较懒的原因，很多经典的案例没有记录下来，深表遗憾。

案例语句

某大型房地产公司，巡检日期2013-04-22，问题语句

 1  SELECT COUNT(*)
 2  FROM   ( SELECT    temp.Application ,
 3                     COUNT(*) AS UserCount
 4           FROM      ( SELECT    f.Application
 5                       FROM      myUserRights ur
 6                                 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode
 7                                 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID
 8                       WHERE     u.IsAdmin = 0
 9                                 AND ( IsDisabeld = 0
10                                       OR IsDisabeld IS NULL
11                                     )
12                                 AND ur.UserGUID NOT IN (
13                                 SELECT  UserGUID
14                                 FROM    myUserRoles
15                                 WHERE   RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78' )
16                       GROUP BY  f.Application ,
17                                 ur.UserGUID
18                     ) AS temp
19           GROUP BY  temp.Application
20         ) AS temp2
21         RIGHT   JOIN myApplication a ON a.Application = temp2.Application
22  WHERE  a.Level = 1
23         AND a.Application IN (
24         SELECT  Application
25         FROM    myFunction
26         WHERE   FunctionCode IN ( '', '01010102', '01010104', '01010105',
27                                   '01010111', '01010106', '01010107',
28                                   '01010110', '01010118', '01010604',
29                                   '01010603', '01010202', '01010203',
30                                   '01010205', '01010206', '01010207',
31                                   '01010301', '01010302', '01010303',
32                                   '01010304', '01010308', '01010305',
33                                   '01010306', '01010310', '01010309',
34                                   '01010407', '

[3]存储引擎相关文章

来源: 发布时间: 2013-10-29

分享大师的blog，并且把主要内容写出来，不敢翻译，以备看了之后忘记可以温习，也推广一下大师的博客

Importance of choosing the right LOB storage technique

本文大意：

N/CHAR：当数据长度都是固定的比较好用，并且可以用来限制列的大小，避免太长而分页导致碎片生产，缺点应用程序比较难辨认，如果数据长度比列定义的小，那么就可能会造成磁盘空间浪费，io量变大，内存空间浪费

N/VARCHAR (1-8000)：对于varchar小于8000个字节，那么已经避免了浪费空间的缺点，但是如果大小变化会造成分页导致碎片，在sql server2005以后，一个表的列字节和可以超过8060个字节，超过的部分会被保存溢出页中，那么当读取这个数据的时候，就会多一次io，也会导致判断是在数据页中还是在溢出页中造成困难，若这个数据比较大，将近8000个字节，那么一个页中只能保存很少的页，如果这个数据不太常用，那么就不太高效了。

N/VARCHAR (MAX) column in-row：这个类型基本和上面的差不多，多了一个好处就是可以超过8000个字节，当大于8000个字节后自动保存在溢出页，访问它需要额外的io，和FILESTREAM比这个字节上限为2GB，如果表中有LOB数据列，那么聚集索引重建不能再online模式下。

N/VARCHAR (MAX) column out-of-row：这个类型的缺点是访问需要额外的io，当对这一列排序是就会出现大量io。如果不是很常用放在溢出页是比较好的选择，但是任然有24字节大小的指针，还有一个好处是通过TEXTIMAGE_ON可以把溢出页放到另外一个文件组中，这样2边的io就可以做到不影响。

N/TEXT column in-row：已经被放弃使用，效果和 N/VARCHAR (MAX)非溢出页一样

N/TEXT column out-of-row：已经放弃使用和上面一样

使用分开的表，需要的时候使用join获取：这个方法对于不太使用到的很有好处，但是需要更多的前端设计和复杂的sql，另外一个好处是主表可以使用online索引创建

FILESTREAM column：这个类型当列数据超过1mb时使用FILESTREAM，从文件系统中获取数据比从buffer pool中获取要快，详细可以看白皮书

Inside the Storage Engine: Ghost cleanup in depth

本文大意：

ghost记录清理进程是后台运行的，主要是清理在聚集表下，记录被删除后形成的ghost记录。当在聚集索引下删除记录，并没有真正的删除只是标记为ghost记录，这样删除会变得更快，回滚只需要撤销标记。当事务提交后后台有ghost清理程序来清理这些被标记为ghost记录。并且会保留最后一个数据页的最有一条ghost记录以免页没释放。当记录被删除会在pfs上标记ghost，在数据页头上也会标记。但是pfs上的标记并不会通知处理程序处理。只有到下一次扫描到这个页时才会处理，或者等到每5秒一次的清理进程激活，清理进程就会扫描pfs页，并对ghost页处理。若没有需要清理的了就跳入下一个数据库。若这次扫描没有发现有ghost记录，那么会设置上一个标记，下5秒唤醒就跳过这个数据库。

Ghost cleanup redux

本文大意：

作者使用一个例子说明当启动快照隔离级别是heap表的删除或产生ghost记录。当删除heap中的记录，这条记录会被标记为ghost并且长度变为14个字节（6（xsn）+8（tempdb中文件，页，行的实际指针））。之后很快就会被当成ghost数据清理（稍微详细的也可以看sql server 技术内幕系列）

Turning off the ghost cleanup task for a performance gain

本文大意：

（sql server 2008中ghost清理工具没10秒执行）可以使用tf661来关闭ghost清理工具的运行，这样会减少因为清理需要把页保存在buffer pool，生产日志，造成物理io。如果对于delete量比较大的数据库可以启用tf661，这样ghost页就不会被清理。

Do changes to index keys really do in-place updates?

本文大意：

指出了sql server2008 internals 里面的关于in-place update的错误，但是我没找到，里面指出对于索引key的修改不会有使用 in-place update 应该是 out-place update。作者做了一个测试说明对key修改不会发生in-place update，而是使用out-place update（也就是先delete 再insert）。当对一个慢的页进行out-place update时，会发生分页现象。从事务日志的角度分析全过程：

0.把更新的记录设置为ghost

1.分配一个新页a(将作为root页或索引页) a，修改IAM页。

2.格式化这个页（也就是格式化96位的头等等）

3.把原数据页的索引插入到页a中

4.把也a设置为root页或者索引页

5.分配一个新页b（讲作为叶子节点）

6.讲第二行移动到页b中

7.把修改的记录插入到页b中

作者说随后就会把原先页中的ghost清理，但是我的测试中始终都没有被清理

本文链接