如何写出高效能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

如何写出高效能TSQL -深入浅出SQL Server Relational Engine (含 SQL 2014 in-memory Engine)

原文地址:http://blogs.technet.com/b/technet_taiwan/archive/2015/01/16/tsql-series-0116.aspx 简介 良好的TSQL和正确索引是大幅提高查询效能最快的快捷方式,同时TSQL也是使用SQL Server的核心,任何应用程序想要和SQL Server沟通,都无法避免撰写TSQL,所以各种效能调校方法中,我们认为查询调校是最省成本、最快能感受到效果的方法 (如下图),这一系列文章将为大家介绍如何写出高效能TSQL,以及开发人员必须了解SQL Server相关基本知识和观念,这样才能活用相关技术,并发挥SQL Server无限潜能。     SQL Server 关系型引擎 SQL Server数据库引擎主要有两部分,这我们里不讨论储存引擎(storage engine)架构,而将会着重在关系型引擎。 由于SQL Server处理查德询过程步骤相当繁琐,当一句TSQL送给SQL Server时,我们须了解关系型引擎是如何工作的,因为每一句TSQL都必须透过关系型引擎分析处理,最后才会透过储存引擎执行并返回用户所需的数据结果集,理解关系型引擎不仅可以帮我们预先避开效能陷阱,同时也有助于我们减少查询调校和除错时间,下面我将说明几个处理关键步骤。 图一:SQL Server处理查询简单示意 图二:SQL Server处理查询关键步骤流程图   分析和绑定 一开始查询优化器会先确认语法正确性,如有错误将会立即返回错误讯息给用户,如果没有错误就会建立分析树并进行对象绑定 (如数据表字段是否存在、数据型别是否正确、函式是否异常..等),主要是因为 TSQL 并非程序性语言,它无法告知数据库应该用什么样正确步骤来撷取数据,所以这阶段会帮你处理基本语法优化、数据型别转换,简单来说就是会改写 TSQL,如把 between 转换为 >= and <=,型别比较不一致时,自动增加转换函数来确保数据正确性 (这就可能会大大影响查询效能), group by 位置..等,最后将输出逻辑 (操作) 查询树,后面将会依照所输出逻辑查询树步骤来一一执行。   查询优化 查询优化几乎可说是最复杂且耗时的阶段 (所以大家要有一点想象力),一开始会先确认计划快取区是否有适当的执行计划,如果没有找到适当执行计划,就会进行一般优化 (如果可以的话),然后再次从计划快取区寻找是否有适当的一般计划,如果存在就会分配相关内存并执行,这是因为不必为只有一种可能执行方法的TSQL进行完整优化,如此一来可以省下编译和建立执行计划时间,因为我们知道查询效能一部分取决于建立执行计划效率 (执行查询时间是产生执行计划时间 + 本身数据查询时间),所以查询优化器并不会寻找最佳执行计划,而是尽可能寻找最低成本 (以 CBO 为基础的 CPU、I/O 成本) 及传回结果速度最快的执行计划,如果该TSQL有达到一般计划条件的话,那么查询优化器将可不必进行完整优化而浪费不必要时间,下面我简单列出建立一般计划的情况…