MSSQL列存储索引：提高查询效率的魔法键

MSSQL 列存储索引：提高查询效率的魔法键

什么是列存储？

在理解列存储索引之前，我们需要先了解什么是列存储。在关系型数据库中，数据通常存储在行里，这样的行式存储可以方便地进行事务处理，但是对于大量读操作来说却不是那么高效。因为我们进行查询的时候，并不需要所有的行的所有列，只需要特定的列或特定的行。

列存储是指将一个表格的数据按列而非按行进行存储。具体来说，这种存储方式是将每一列的数据分别储存，各列之间是比较独立的，而不是将整行数据存在一起。同时，列存储允许我们快速地跳跃到某个列。这使得对于大规模的数据分析和 OLAP 查询的情况下，列存储的优势就比行存储更加明显。

MSSQL中的列存储索引

列存储索引就是基于列存储的基础上，对于某些数据的列建立的具有排序，查询等功能的索引。与传统的B树索引不同，列存储索引的数据是以纵向存储在磁盘中，搜索匹配的数据时，它会跳过大量不相关的数据列，大大提升了查询效率。

为什么需要MSSQL列存储索引？

从数据量越来越大的背景下，查询时需要扫描大量数据的磁盘操作，已经成为数据库应用程序性能瓶颈的一个主要原因。

例如，一个表格拥有1000万条数据，而我们需要查询其中的一列以获取所有的唯一值。使用传统的行式存储，我们必须扫描所有的行并找到需要的列，这必然需要很长的时间和大量的磁盘读写。而使用了列存储之后，我们只需要扫描这一列中的所有值，速度将会快得多。

如何实现MSSQL列存储索引？

下面我们来看一下如何在MSSQL中实现列存储索引。

创建表格，假设我们创建了一个日志表：

CREATE TABLE LogTable (  
  ID INT NOT NULL,  
  LogTime datetime NOT NULL,  
  Caller varchar(30) NOT NULL,  
  StartIP varchar(30) NOT NULL,  
  EndIP varchar(30) NOT NULL,  
  Protocol varchar(30) NOT NULL,  
  PRIMARY KEY NONCLUSTERED (ID ASC)  
);

在MSSQL中，建立列存储索引有两个注意点。首先，需要将表格设置为可支持列存储。其次，需要建立非聚集列存储索引。

--创建表格并设置为可支持列存储  
CREATE TABLE LogTable_Columnstore   
(  
      ID INT NOT NULL,  
      LogTime datetime NOT NULL,  
      Caller varchar(30) NOT NULL,  
      StartIP varchar(30) NOT NULL,  
      EndIP varchar(30) NOT NULL,  
      Protocol varchar(30) NOT NULL  
)  
WITH (  
        MEMORY_OPTIMIZED=ON,   
        DURABILITY=SCHEMA_ONLY,  
        CLUSTERED COLUMNSTORE INDEX  
);
--在表格上建立非聚集列存储索引  
CREATE NONCLUSTERED COLUMNSTORE INDEX NonClusteringColumnstoreIndex  
    ON LogTable_Columnstore (Caller,StartIP,EndIP)  
WITH (  
        DROP_EXISTING = OFF  
); 

这里建立的非聚集列存储索引包含Caller、StartIP、EndIP三列。同时，CLUSTERED COLUMNSTORE INDEX声明了表格支持列存储索引，这让它成为了一个列存储的表格。

列存储索引的测试

下面，我们来测试一下MSSQL列存储索引的效率。

首先，在我们的日志表中插入1000万条数据。

DECLARE @LogTable_Columnstore TABLE (
    ID          INT NOT NULL,
    LogTime DATETIME2 NOT NULL,
    Caller          VARCHAR(30),
    StartIP     VARCHAR(30),
    EndIP           VARCHAR(30),
    Protocol        VARCHAR(30),
    INDEX [IX_Caller_StartIP_EndIP] NONCLUSTERED COLUMNSTORE (Caller, StartIP, EndIP)
);
WITH n(n) AS
(
SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n)
),
a(n) AS
(
SELECT 1 FROM n, n AS b, n AS c, n AS d
),
b(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) FROM a
)
INSERT INTO @LogTable_Columnstore
SELECT TOP 10000000 b.n,
       dateadd(minute, b.n, '20100101'),
       'Caller_' + CAST(b.n % 10000 AS VARCHAR(10)),
       'StartIP_' + CAST(b.n % 10000 AS VARCHAR(10)),
       'EndIP_' + CAST(b.n % 10000 AS VARCHAR(10)),
       'Protocol_' + CAST(b.n % 10000 AS VARCHAR(10))
FROM b;

接下来，我们用以下代码测试查询列存储索引的效率。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT DISTINCT Caller FROM @LogTable_Columnstore;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

此时我们已经可以看到区别了。查询结果如下所示：

Table '@LogTable_Columnstore'. Scan count 3, logical reads 358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 936 ms,  elapsed time = 943 ms.

总结

在查询大数据量时，使用MSSQL列存储索引可以大大提高查询效率。与传统的B树索引不同，它在磁盘上以纵向方式存储数据，并能跳过大量不相关的数据列，提高查询效率。同时，MSSQL中通过设置表格支持列存储，并建立非聚集列存储索引的方式实现列存储索引。