SQL Server Performance 分析 – 之三

上一篇《SQL Server Performance 分析 – 之二》提到了执行计划缓存和表分区对 SQL 的性能影响。这一节我们谈一谈索引的影响。

1.索引的使用限制，就是在什么情况下，索引能被引用到，什么情况下，索引失效。索引能被引用的地方，这还得分单表查询与多表查询，单键与多建的情况。

单表查询与多表查询对索引的限制，其实就是在where子句或者join子句中，被索引字段不能有函数，（相比较oracle的函数索引就不同）。第二点就是like子句中，通配符%不能在整个字符串之前。举两个不走索引的例子：

假设 dbo.sales有orderMonth这个字段的索引，字段是整型，那么convert(varchar(6),orderMonth)=’201601’是不会走索引的，orderMonth like ‘%05’ 也是走不了索引的。

单键与多键的情况就较复杂了。首先单键与多键的情况也必须满足单表与多表的限制条件才能走索引。其次多键的情况下，还要考虑被索引字段的排列顺序。比如我们把整个数据库的表结构作一张表，按照object_id与column_id做一个符合索引。那么当我们按照object_Id,column_Id作索引查询的时候，分别互换object_id,column_Id，会不会都走索引呢？

有两种情况，是会走索引的：

a) 两个等式都是用全等比较，比如 object_id = 101 , column_id = 111, 此时顺序可以不严格按照索引创建时候的来；

b) 当 object_id = 101并且排在第一位时，column_id无论用什么等式，都可以走seek 索引。

2. Columnstore index 列式索引也是一大特色，从SQL SERVER 2012起，支持列式索引了。我们要谈一谈列式索引的概念，用法，比较下与row-based index的区别，特别是列式索引的存储。据说列式索引采用了独特的压缩方式。这种压缩方式叫xVelocity(前称VertiPaq)，专门用于 Analysis Service和Power Pivot的数据存储，现将其移到relational database storage engine中来。

1） ColumnStore index data Structure: 从物理存储上来说，ColumnStore index 在page之上加了一层抽象，segment。一个segment就是一列索引的字段。如果我们新创建一个ColumnStore Index，就会有两个segment。每个segment会有一个存储的上限，每一个segment都可以包含很多数据页（data page）。一个columnstore index的所有segment，按照从上到下一一对应排序。也就是说，如果我们新建一个2列的columnstore index，第一个segment的第一个行，和第二个segment的第一个行，组成了堆表里面的第一行。

2）按照列来存储，有3个好处：一来存储的都是同质化的（homogenous）数据，压缩采用的函数比较高效；二来针对重复值比较多的列，可以采用 dictionary的方式存储，key部分存储在索引上，value部分放在dictionary 里面,省下很多空间，查询产生的IO就更小了；再一个因为每一个segment存储了单一的值，减少了一些大字段的占用空间，很多预读的数据页就极大减少了不必要字段，IO更有效率。

3）Batch Mode Processing：SQL Server 有三种处理数据集的方式， 一种是 row-based, 一行一行处理，一种是 Batch mode, 一个batch包含了1000条数据，每一个列在这个batch里面被称之为vector，基于vector的处理方法，叫做batch processing。当然我们可以把row-based, batch mode合并起来应用，这是第三种方式。

针对 colunmstore index，借用Robert Sheldon的一张图，可以获得清晰的存储认识：

每一个ROW GROUP都存储了相同数目的行，并且都按相同的行位置排列。针对上面的列式索引，Make + Model + Color, 假设表里第一行的数据是， Audi + S + Red, 那么 Make Segment第一行存储的是Audi, Model Segment第一行存储的就是S，Color Segment第一行存储的就是Red。

如果我们对一张堆表做全表扫描，可以看到I/O Cost的标示

我们对一张表加 columnstore index， 可以看到默认的，执行计划就选择了columnstore index。

create nonclustered columnstore index

idx_colstr_sts

on siebeldbTableSchema(

object_id

,column_id

,column_name

)

go

Estimated Operator Cost 从1.46 降到了0.27， 而Estimated I/O Cost 从1.25降到了0.06。 I/O这种重型处理一般在分析系统，BI或者数据仓库中大量存在，所以针对fact table或者大数据量的dimension table会比较适用。

综上，Index 对性能的影响，也是一本书都讲不完的，Johnnason Lewis编写的《Cost-Based Oracle Fundamentals》，Michael Leach编写的《Relational Database Index Design and the Optimizers》都是讲的索引攻略。我这里只能粗的不能再粗的浅谈辄止。

如果说索引是 SQL 性能的外因，那么 query engine, 即查询引擎就是内因了。一切优化的 SQL 都需要出自他之手。下一篇我们将讨论这一主题，比如熟知的 SQL 执行顺序：

Select top (X)|Distinct

t1.Field1

,t2.Field4

,sum(t2.Field6) as Filed6 

From dbo.tableone t1

Left|right|full Join dbo.tabletwo t2 

    on t1.Filed2 = t2.Field1 

Where t1.Field3 = ‘XXX’
Order by t2.Field5