SQL Server on Linux列存储索引

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: # 问题引入 “鸟儿啊,听说微软至SQL Server 2012以来,推出了一种全新的基于列式存储的索引,你去研究看看SQL Server on Linux对这个功能的支持度如何,效率有多大的提升?”。老鸟又迫不及待的开始给菜鸟分配任务。 # 分析问题 的确如老鸟所说,从SQL Server 2012开始推出了列存储索引,这个版本限制颇多,但是它对统计查询的效率提升又是实实在在的。所以,让我

问题引入

“鸟儿啊,听说微软至SQL Server 2012以来,推出了一种全新的基于列式存储的索引,你去研究看看SQL Server on Linux对这个功能的支持度如何,效率有多大的提升?”。老鸟又迫不及待的开始给菜鸟分配任务。

分析问题

的确如老鸟所说,从SQL Server 2012开始推出了列存储索引,这个版本限制颇多,但是它对统计查询的效率提升又是实实在在的。所以,让我们来看看SQL Server on Linux列存储索引对统计查询的效率提升情况如何。
这里也顺便提一下SQL Server 2012 列存储索引的限制,比如:
非聚集列存储索引是只读的,换句话说,基表会变成Read-Only
仅支持非聚集列存储索引
只能通过删除及创建索引的方式重建索引,而不可使用ALTER INDEX命令
对应的表不可包含唯一性约束、主键约束或外键约束
......

解决问题

这一小节,我们以一组对比测试来看看列存储索引相对于B-Tree索引对统计查询的效率提升,真正是强大到没有敌人。

创建测试对象

测试之前,我们需要创建测试表对象,B-Tree索引和列存储索引,并且初始化500万条记录数据,做为测试的基础数据。

use tempdb
GO
IF OBJECT_ID('dbo.Table_with_5M_rows','U') IS NOT NULL
    DROP TABLE dbo.Table_with_5M_rows
GO

CREATE TABLE [dbo].[Table_with_5M_rows](
    [OrderItemId] [bigint] NULL,
    [OrderId] [int] NULL,
    [Price] [int] NULL,
    [ProductName] [varchar](240) NULL
) ON [PRIMARY]

GO
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
)
INSERT INTO Table_with_5M_rows
SELECT TOP(5000000)
    OrderItemId = ROW_NUMBER() OVER (ORDER BY a.a)
    ,OrderId = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
    ,Price = a.a * 10
    ,ProductName = cast(a.a as varchar)  + cast(b.a as varchar)  + cast(c.a as varchar)  + cast(d.a as varchar)  + cast(e.a as varchar)  + cast(f.a as varchar)  + cast(g.a as varchar)  + cast(h.a as varchar)
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;
GO

--Create regular index
CREATE NONCLUSTERED INDEX IX_OrderId_@price
ON dbo.Table_with_5M_rows(OrderId)
INCLUDE(price) WITH(ONLINE =ON)
GO

--create columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CSIX_Table_with_5M_rows ON dbo.Table_with_5M_rows;
GO
AI 代码解读

对象创建完毕后,截图如下:
01.png

执行测试查询

首先,我们来测试使用B-Tree常规索引的查询效率,业务场景是统计每一个订单的消费总额和平均每单价格。这里,我们强制查询语句使用索引IX_OrderId_@price,需要注意的地方是,在执行查询语句之前,我们需要清空缓存来避免缓存对执行结果的影响。查询语句如下:

--clear data cache
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
GO

--open statistics
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
GO

--Testing using B-tree index
SELECT 
    OrderId
    ,totalAmount = sum(price)
    ,avgPrice = avg(price)
FROM Table_with_5M_rows WITH(NOLOCK, INDEX=IX_OrderId_@price)
GROUP BY OrderId
GO
AI 代码解读

同样的道理,在对比组查询测试最开始,我们需要清空SQL Server缓存,然后强制使用列存储索引CSIX_Table_with_5M_rows,语句如下:

--clear data cache
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
GO

--Testing using Column store index
SELECT 
    OrderId
    ,totalAmount = sum(price)
    ,avgPrice = avg(price)
FROM Table_with_5M_rows WITH(NOLOCK, INDEX=CSIX_Table_with_5M_rows)
GROUP BY OrderId
GO
AI 代码解读

对比测试结果

两组查询测试语句执行完毕,以下我通过统计信息和执行计划两个方面来对比测试结果。
B-Tree索引查询统计信息:

Table 'Table_with_5M_rows'. Scan count 1, logical reads 16136, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1295 ms,  elapsed time = 1313 ms.
AI 代码解读

列存储索引查询统计信息:

Table 'Table_with_5M_rows'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 73, lob physical reads 7, lob read-ahead reads 0.
Table 'Table_with_5M_rows'. Segment reads 6, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 5 ms,  elapsed time = 15 ms.
AI 代码解读

从查询执行的统计信息输出来看,基于B-Tree索引的查询逻辑读IO为16136,CPU消耗为1295毫秒,执行时间为1313毫秒,而基于列存储索引的查询逻辑读IO为0,CPU消耗为5毫秒,执行时间15毫秒。CPU和执行时间上有259倍和87倍的性能提升
B-Tree索引查询执行计划截图:
02.png
列存储索引查询执行计划截图:
03.png
从实际的执行计划对比来看,IO消耗从11.912降低到0.003125,大大节约了IO的性能开销,这也是为什么性能提升非常显著的原因。

写在最后

SQL Server on Linux对列存储索引的支持这点非常强大,对于统计查询效率的提升尤其是IO的提升相当明显。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
52 1
南大通用GBase 8a MPP Cluster Linux端SQL进程监控工具
南大通用GBase 8a MPP Cluster Linux端SQL进程监控工具
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
1268 5
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
3天前
|
linux命令详细说明以及案例
本文介绍了常用的 Linux 命令及其详细说明和示例,包括:`ls`(列出目录内容)、`cd`(更改目录)、`rm` 和 `mv`(删除与移动文件)、`grep`(搜索文本)、`cat`(显示文件内容)以及 `chmod`(更改文件权限)。每个命令均配有功能描述、选项说明及实际案例,帮助用户更好地掌握 Linux 命令的使用方法。
82 56
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等