月度归档:2018年04月

Rowstore索引和Columnstore索引方式对比分析


摘要:
列存储索引在sqlserver2012版本中开始引用的,
何时应该使用列存储、何时应使用传统的行存储索引,存在很多问题。微软对列存储的最新增强功能尤其如此。微软利用列存储索引推出一些令人印象深刻的性能提升。
例如,微软在他们的网站上指出,与传统的面向行的存储相比,列存储索引可以实现高达10倍的查询性能提升,并且在未压缩的数据大小上可以实现10倍的数据压缩率。
列存储的原始实施有一些重大限制。例如,您需要删除索引以修改基表中的数据。
另外,最初的实现并没有使用柱状格式来存储物理数据。



一、行存储和列存储区别

行存储:行存储索引是自SQL Server初始版本以来一直存在的传统风格。
Rowstore索引旨在通过使查询能够通过索引快速定位数据而不是扫描整个表来加快数据检索速度。
Rowstore数据按行和列进行逻辑组织,并物理存储在面向行的数据页中。
SQL Server通过数据页面在内部组织使用B-Tree结构的rowstore索引。
SQL Server支持聚簇和非聚簇索引。使用聚簇索引时,基表中的数据根据​​聚簇索引进行组织。
通过搜索特定值或检索小范围的值,Rowstore索引在寻求数据的查询上表现最佳。
————————————————–
列存储: 列存储索引也在逻辑上组织为包含行和列的表格,但数据物理存储为列式数据格式。
列存储索引适用于大数据集的大多数只读查询,如数据仓库工作负载。
列存储索引不太适合寻求特定单个值的查询。列通常包含相似的数据,这些数据可以高度压缩数据,从而提高内存利用率并显着减少磁盘使用量。
列存储索引可以是群集或非群集。群集和非群集列存储索引的功能相同。不同之处在于,集群式列存储索引为整个表提供主存储空间,而非聚簇索引是包含基础表中某些列副本的二级索引。
——————————————————————–
对于使用更多更新和查找操作的联机事务处理(OLTP)工作负载,行存储索引往往更好,
而对于使用更多读取操作的联机分析处理(OLAP)工作负载,列存储索引往往更好。
Rowstore索引在执行随机读取和写入时往往更好。列存储索引对于执行顺序读取和写入往往更好。



二、列存储的应用

列存储进行实时分析和OLTP应用
SQL Server列存储索引最具吸引力的地方是它们能够启用实时分析。
传统的BI和数据仓库应用程序通常不是使用实时数据构建的。
相反,它们通常是通过ETL过程构建的,ETL过程定期采用OLTP数据并将其加载到后端数据仓库中。这意味着决策数据不是最新的,
而是几个小时甚至几天。数据只与最后一次ETL数据加载时的数据相同。
列存储索引可以通过加速实时OLTP数据的查询性能来解决此问题,从而可以在没有ETL引起的延迟的情况下对实时数据执行BI和分析分析。

从SQL Server 2016开始,您还可以通过内存中OLTP表创建可更新的非群集列存储,从而可能提供查看实时分析所需的性能。非聚集列存储索引存储所选列的副本,
因此您确实需要额外的空间,但数据将被高度压缩。通过组合列存储和内存中OLTP,您可以在列存储索引上运行BI查询,并同时支持高性能的OLTP事务。
SQL Server还使您能够灵活地在列存储索引上拥有一个或多个非集群行存储索引,从而使您能够在基础列存储上执行高效的表查找。
————————————–

二、列存储的应用举例

列存储索引和行的性能比较。
下面的Microsoft ®上找到样本查询的GitHub ®使用AdventureworksDW2016_EXT数据库来说明在性能上的差异,
你可以得到使用列存储索引。


-----查询1:使用名为FactResellerSalesXL_PageCompressed的rowstore索引。
  SET STATISTICS IO和TIME操作用于记录查询所需的存储使用情况和已用时间。
USE [AdventureworksDW2016_EXT]
GO

DBCC DROPCLEANBUFFERS   ---清楚缓存
  
SET STATISTICS IO ON    ---打开属性开关
SET STATISTICS TIME ON

SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales',
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders',
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_PageCompressed f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

 
------查询2下一个查询方法相同,它使用FactResellerSalesXL_CCI列存储索引而不是行存储。两个表中的数据都是相同的。同样,统计信息用于记录查询性能。

USE [AdventureworksDW2016_EXT]
GO

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales',
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders',
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_CCI f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

 


—-行存储查询1结果:
(42 rows affected)
Table ‘Worktable’. Scan count 3, logical reads 7099656, physical reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0.
Table ‘FactResellerSalesXL_PageCompressed’. Scan count 1, logical reads 87246, physical reads 1.
Table ‘DimDate’. Scan count 1, logical reads 59, physical reads 0.
Table ‘DimEmployee’. Scan count 1, logical reads 31, physical reads 1.
Table ‘DimSalesTerritory’. Scan count 1, logical reads 3, physical reads 1.
—————————————————————————
SQL Server Execution Times:
CPU time = 19515 ms, elapsed time = 20831 ms.
————————————————————————–
—-列存储查询2结果:
(42 rows affected)
Table ‘FactResellerSalesXL_CCI’. Scan count 1, logical reads 0, physical reads 0.
Table ‘FactResellerSalesXL_CCI’. Segment reads 7, segment skipped 5.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0.
Table ‘DimSalesTerritory’. Scan count 1, logical reads 3, physical reads 1.
Table ‘DimEmployee’. Scan count 1, logical reads 31, physical reads 1.
Table ‘DimDate’. Scan count 1, logical reads 59, physical reads 0.
————————————————————————-
SQL Server Execution Times:
CPU time = 2297 ms, elapsed time = 2803 ms.

查询1和查询显示的行存储和列存储查询的性能和差异

对于上面的例子,我们可以看到columnstore需要更少的I / O,并且提供了比rowstore更好的性能。本文附带的视频中提供了更详细的信息。
如果您有一些缓慢的查询,您认为可能会从使用列存储索引中受益,该怎么办?试用列索引索引并查看它是否适用于您很容易。
如果您创建列存储索引并且不提供您期望的性能优势,则可以通过简单地删除列存储索引来回滚。您的里程可能因列存储索引而异,
您看到的性能将取决于您的情况,但对于某些类型的查询,它可能比行存储有优势。

mssql sqlserver 主键和外键的功能分享


摘要:
下文主要讲述sqlserver中主键及外键的功能



一、sqlserver主外键的功能

1.1 sqlserver中主键和外键都是对表数据起到约束作用,使表数据更加的完整。
1.2 如果一张表设置了外键,那么数据插入、更新时,将同步验证键值是否存在主键中,不存在,则禁止外键所在的表插入数据
1.3 主键所在的表,删除行数据时,验证是否有外键数据关联值此条数据上,如果存在数据也禁止删除主键行所在的数据
1.4 更新和删除主键数据时,可通过设置级联属性,达到同步更新和删除外键所在的行数据
1.5 主键可以确保数据的唯一性,因为主键列所需的数值必须唯一



二、sqlserver主外键的区别

主键:行数据中主键具有唯一性,不可为null
外键:外键可以重复
—————————————-
主键:保证数据的完整性和唯一性
外键:用于关联其它表,和限制关联表的数据完整性
—————————————-
主键:每张表只允许存在一个主键
外键:一张表,可以拥有多个外键



三、sqlserver主外键的应用举例

/*
例:
创建一个业务单据表
billMain 业务单主表
billDetail 业务单明细表
*/
create table billMain(billNo varchar(30) not null,info varchar(30))  
create table billDetail(billNo varchar(30) not null,infoDetail varchar(30))
go
 
---设置主表上的主键
ALTER TABLE billMain 
ADD CONSTRAINT PK_billNo   
PRIMARY KEY(billNo)
go
---设置主表上的外键
ALTER TABLE  billDetail with nocheck 
ADD CONSTRAINT FK_billNO_ref_billMain
FOREIGN KEY(billNo) REFERENCES billMain(billNo) 


--设置级联外键 -主表billMain上数据更新和删除 子表billDetail上会随着变化
ALTER TABLE billDetail 
ADD CONSTRAINT FK_billNO_ref_billMain2 
FOREIGN KEY(billNo) REFERENCES billMain(billNo)
ON UPDATE CASCADE ON DELETE CASCADE




--获取表上的主外键信息
EXEC sp_helpconstraint 'billDetail' 


---删除表上的外键
ALTER TABLE  billDetail  
drop  CONSTRAINT FK_billNO_ref_billMain


ALTER TABLE  billDetail 
drop CONSTRAINT FK_billNO_ref_billMain2

---删除表上的主键(需先清理主键所对应的外键)
ALTER TABLE billMain 
drop CONSTRAINT PK_billNo  

go
drop table billDetail 
drop table billMain

mssql sqlserver like匹配数据列是否全部由英文字母组成


摘要:
今天收到一学生提问,如何验证一张数据表中,列值全部由英文字母组成,
收到这个提问,我立即想到了 一个like的正则匹配 [a-zA-Z],
由于不知道列的数据长度,所以我们需要转变思路,匹配一个列中的值不属于英文字母的方式来验证这个列的数值是否全部为英文,
具体实现方法如下所示:


create table A(note varchar(30))
go 
insert into A values('readyGo')
insert into A values('ceshi.')
insert into A values('英文字母')
insert into A values('hundao')
insert into A values('78&*&') 
go
select * from A where note not  like '%[^a-zA-Z]%'
go
drop table A
mssql sqlserver like匹配数据列是否全部由英文字母组成

mssql sqlserver like匹配数据列是否全部由英文字母组成