如何写出高效能TSQL – 关于索引不可不知道的事

原文地址:http://blogs.technet.com/b/technet_taiwan/archive/2015/01/23/tsql-series-0123.aspx 本文将分成五大单元,分别带您了解: 索引简介 索引基本知识 索引类型介绍 索引设计注意事项 进阶推荐   简介 TSQL是查询SQL Server的核心,而索引则是提高查询效能的主角,如要写出高效能TSQL则无可避免需搭配正确索引,因为SQL Server需透过正确索引才可以快速有效地找到与索引键值相关数据,有了正确索引SQL Server就不需要扫描数据页(data page)上每一笔数据,而在众多查询效能调校技术中,透过建立并设计正确索引算是最基本的手法 (通常来说也是最有效、最快能看到效果的),所以了解索引观念和特性,可以帮助我们在开发阶段设计规划正确索引,而且我们认为不管是开发人员或DBA都应该了解索引该如何设计,因为都有可能接收用户最直接的感受,说白话一点,每一句查询TSQL都要以最短时间响应给用户。但由于索引主题范围庞大,所以一开始我们会介绍索引基本知识、B-tree 结构等,后面介绍索引类型、案例分享及设计须注意的方向,主要是希望透过本文可以让大家快速建立正确索引。   索引基本知识 SQL Server 如何使用索引 我们都知道索引可以提高查询效能,但相对也增加新增(Insert)、删除(Delete)和更新(Update)数据处理成本,所以对整体效能来说找一个合适平衡点相当重要。 当一个数据表没有索引时,数据存放的顺序绝不是依照数据新增顺序,这是因为SQL Server Database Engine会自我处理数据储存位置,所以基本上,我们无法事先预测数据储存在数据页上是否都连续且都在同一区段中,而当一句Select送给SQL Server时,因为没有索引,这时SQL Server必须扫描整个数据表,以及该数据表的所有数据页和数据页上的每一笔数据,最后才返回用户最终所需要的数据结果集,这样的操作就称为表扫描(Full Table Scan)。 当数据表上有索引时 (假设索引设计正确),这时数据表上的数据一定会经过排序,所以SQL Server将基于该索引键值和结构来定位 (透过指针) 数据位置,简单来说只搜寻必要的数据页,而这些数据页已经包含用户最终所需要的数据结果集,这样的操作就称为索引搜寻(Index Seek)。 B-tree 索引结构 SQL Server所有索引基本上都采用B-tree结构,除了xml索引、全文检索索引(full-text)、数据行存放区索引(columnstore index)和内存优化索引(Memory-optimized indexes)不用B-tree。 xml 索引是存放在 SQL Server 底层数据表,全文检索索引是利用自己引擎来处理查德询和管理全文检索目录 (full-text catalogs),数据行存放区索引则是使用 in-memory 技术,内存优化索引则是使用 BW-tree 结构。 一个标准的B-tree结构 (图1)是由根结点(root)开始的页面,下面有一或多个中继层节点及一或多个分叶(Leaf)节点构成。…

1