目录
1 记录数据的基本格式............................................................................................................................................................... 3
1.1 数据页的基础知识 ...................................................................................................................................................... 3
1.2 SQL Server 2005 有以下几种页类型： .................................................................................................................... 3
1.3 数据页（ Data 类型页）的结构示意图： ............................................................................................................. 4
1.4 对大型行的支持 .......................................................................................................................................................... 4
1.5 SQL Server 的数据页缓存 .......................................................................................................................................... 5
1.6 缓冲区管理的工作原理 .............................................................................................................................................. 5
1.7 实验 ............................................................................................................................................................................... 5
2 聚集索引，非聚集索引 .......................................................................................................................................................... 7
2.1 B+ 树的结构图: .......................................................................................................................................................... 8
2.2 聚集索引（ Clustered Index） ................................................................................................................................ 8
2.3 非聚集索引 （ Unclustered Index） ................................................................................................................... 10
2.4 什么是 Bookmark Lookup ..................................................................................................................................... 12
3 测试中一些常看的指标和清除缓存的方法 ....................................................................................................................... 16
3.1 如何获得索引的一些信息........................................................................................................................................ 16
3.2 如何查看磁盘 I/O 操作信息 ................................................................................................................................... 17
3.3 使用 SQL Server Management Studio Standard Reports ....................................................................................... 18
3.4 测试中，释放缓存的一些方法 ............................................................................................................................... 18
4 主键与聚集索引 ..................................................................................................................................................................... 18
4.1 主键（ PRIMARY KEY ）........................................................................................................................................ 19
4.2 聚集索引 ..................................................................................................................................................................... 19
4.3 两者的比较................................................................................................................................................................. 19
5 理解 newid()和 newsequentialid() ........................................................................................................................................ 20
5.1 The insert algorithm for B+ Trees ......................................................................................................................... 21
6 索引的代价，使用场景 ........................................................................................................................................................ 22
6.1 使用索引的意义 ........................................................................................................................................................ 22
6.2 使用索引的代价 ........................................................................................................................................................ 22
6.3 创建索引的列 ............................................................................................................................................................ 23
6.4 不创建索引的列 ........................................................................................................................................................ 23
6.5 Heaps 是 staging data 的很好选择，当它没有任何 Index 时 .............................................................................. 23
6.6 何时创建聚集索引？ ................................................................................................................................................ 23
6.7 聚集索引唯一性（独特型的问题） ....................................................................................................................... 23
6.8 聚集索引持续向上增长的需求 ............................................................................................................................... 24
6.9 非聚集索引提高性能的方法 ................................................................................................................................... 24
6.10 参考资料................................................................................................................................................................. 25
7 Indexing for AND ..................................................................................................................................................................... 26
7.1 总结知识点： ............................................................................................................................................................ 28
8 数据基本格式补充 ................................................................................................................................................................. 28
9 Indexing for OR ........................................................................................................................................................................ 29
10 Join 时的三种算法简介.................................................................................................................................................... 31
10.1 Hash Join （哈希联结） .......................................................................................................................................... 32
10.2 Nested Loop Join （嵌套循环联结） ..................................................................................................................... 33
10.3 Merge Join （合并联结） ........................................................................................................................................ 34
10.4 分别使用这三种 Join 的例子：......................................................................................................................... 35
11 附录 ..................................................................................................................................................................................... 36
