网站制作中需要注意的地方,网站管理系统源码,seo搜索优化是什么意思,网站内链建设简介首先介紹列存储的概念#xff1a; 传统的数据库存储是行存储。对于SQL Server来说#xff0c;每个page是8K#xff1b;往page里面塞数据#xff0c;假设该表每条数据长度是500字节#xff0c;那么这个page 先塞第一条数据#xff0c;然后再塞第二条数据#xff0c;大…简介首先介紹列存储的概念 传统的数据库存储是行存储。对于SQL Server来说每个page是8K往page里面塞数据假设该表每条数据长度是500字节那么这个page 先塞第一条数据然后再塞第二条数据大概能塞 8K/50016条数据。注意这里每一条数据都是包括所有字段(column)的。如图所示下面是若干个page每个page塞满了一行一行的数据。行存储示意图。接下来介绍列存储是一个column一个column塞而且在SQL Server里是向row group或者segment塞。列存储示意图如图该表有5个字段(column)每个红色柱体是一个Segment每5个segment组成一个row group。Segment只包含一个column的数据而row group包含所有column的数据。每个Segment最多包含1百万条该column的数据从性能上说1百万条能达到性能最优(数量越大压缩比越大)数量越少该segment的性能越差。列存储对比行存储有什么好处 行存储适合OLTP系统就是多用户使用很多update/insert/deleteSQL 语句处理的对象都是几条数据或者几百条数据(数据量不大)列存储适合数据仓库用户数很少数据量巨大数据变化少(除了ETL)。在SQL Server中ColumnStore index能把大量的数据压缩到1/10从而减少IOCPU和memory的使用从而带来性能的飞跃ColumnStore 除了压缩还使用了Batch modesegment eliminate等技术对性能有很大提升。2. ColumnStore Index 适用的场景和其他技术一样不能适合所有场景如果选用的场景不适合反而会带来性能的急剧下降。(1) 用星性/雪花模型建模的数据仓库(2) 该表(或者分区)的记录数要大于1百万(3) 大部分SQL 语句是报表类的语句就是range scan 而不是 seek。(4) 该表的数据很少进行update/delete大量的insert是可以的。(5) 该表不能有varchar(max), nvarchar(max), or varbinary(max) 数据类型(6) 对于OLTP数据库在某些特定的场景下也可以使用columnstore index real-time operational analytics3. Column Store的物理结构ColumnStore Index 除了Row group 还包括 DeltaStore。假设该表有1105万条记录每个rowgroup容纳100万条那么总共有11个row group还有5万记录放在Deltastore里面。DeltaStore是用来存放不够数量(这里是100万)的数据行存储没有压缩而rowgroup都是列存储而且压缩了。随着insert 数据增多Deltastore的数量增加如果数量增加到100万该Deltastore 会停止接收数据变成row group也就是 列存储压缩如果还有数据insert会生成新的Deltastore。Columnstore Index 的组成部分除了 Row GroupDelta store还有 Delted Bitmap.Columnstore index 删除记录并不是物理删除而是逻辑删除在 Delted Bitmap加一个标记 Delted Bitmap会记录整个表被删除的记录SQL Server对该表做query的时候除了查询row groupDelta store(row store)还要查询 Delted Bitmap把三者的结果Union才是最后的结果。那么ColumnStore Index什么时候做物理删除 对index 进行rebuild或者reorganize的时候。对Columnstore index进行update并不是物理update而是delete该条记录然后insert一条新的记录。ColumnStore Index 结构小结(1) 包括Row Group(compressed列存储)Delta Store (也叫Delta Row group行存储)Deleted Bitmap (存储被删除的记录的信息)。(2) 从SQL Server2016开始一个表创建了ColumnStore index后还可以创建传统的行存储的索引----non clustered index(NCI)。(3)ColumnStore index本身不排序的所以查询某一条记录都需要 全表扫描(full table scan)不排序这个特性对于insert是利好performance很好对于delete/update不好特别表比较大的时候。 因为delete/update某一条记录需要先找到它而查找的代价对于ColumnStore Index 比较大。(4) 对于上面第三点要快速的找到某一条或几条数据可以在ColumnStore index基础上再创建传统的行存储的索引----non clustered index(NCI)。(5) 在ColumnStore index基础上再创建传统的行存储的索引----non clustered index(NCI)好处不仅是performance还能给这个表加上 唯一性约束、主键约束和外键约束等约束。当然这些都只能在SQL2016或之后的版本才能实现。4. 如何发现某些表适合创建ColumnStore index。首先它比较适合于数据仓库但数据仓库的每个表都能创建ColumnStore Index吗另外OLTP环境可以使用CCI吗另外可以用 DMV sys.dm_db_index_operational_stats来查看某个表的过往操作(1) 如果50%以上的操作是range scan而不是seek(2) update/delete的操作少于10%那么这个表很适合创建cluster columnstore index(CCI)当然还有一个前提该表足够大至少1百万条记录越大越好。如何估计某个表创建clustered columnstore index 之后的压缩率 在SQL 2019中可以使用sp_estimate_data_compression_savings 来预估压缩率。该sp在数据库中执行以下操作•创建临时表 T•把该表的数据进行采样载入一部分数据到T•查看T的大小•对T 进行列存储压缩查看压缩后的大小5. 快速的装载数据到已经创建 CCI的表中(1) 装载外部文件bulk insert tableA FROM c:\temp\fileA.csvcsv文件的数据并行的装载到多个Row Group和多个Delta Store 中超过102400条记录的数据进入Row Group列压缩没有超过102400条记录的数据进入 Delta Store (Delta Row group)。因为Row Group提供了高的压缩比所以装载数据产生的日志也会少很多SQL Server会自动的使用并行操作同时向多个Row Group装载数据。(2) 从其他表装载数据Insert into select * from 与“装载外部文件”很类似超过100k条记录的数据进入Row Group列压缩没有超过100k条记录的数据进入 Delta Store (Delta Row group)。不过SQL Server不会自动的使用并行操作要使用tablock才能触发并行。insert into ccitest with (TABLOCK) select * from dbo.FactResellerSalesXL (能并行)(3) 使用SSIS 来装载数据从SQL2016开始有一个新的参数 AutoAdjustBufferSize它能根据batchsize来自动调整 buffer size对性能有极大提升。6. CCI 的性能CCI 除了压缩比大带来的CPU/memory/IO的减少还使用了 Batch modesegment eliminate和 并行来提升性能。(1) Batch ModeBatch mode 从字面上就是批处理就是一次处理几百条数据而不是一条一条处理数据比较适合于大数据量的数据仓库。Batch mode 是从SQL Server 2012开始和ColumnStore Index 一起使用的在SQL 2019之前Batch mode只能在列存储中使用从SQL 2019开始batch mode也能在row store使用。对于 SQL 语句 selectProductKey,OrderQuantityfromFactResellerSalesXL_CCI where OrderQuantity3batch mode会在内存中 占用64K大小的内存形成上图中的vector先scan 该表把64k的数据放到vector中然后用predictte来过滤(OrderQuantity3)。符合过滤条件的会在memory当中的bitmap打上标记bitmap在上图的最左边。每次处理64k大小的数据。在SQL 2016之前很多函数不支持 batch mode包括sumavgmin,rank等还有distinctleft join,group by, order by等也不被支持。 总之一句话要用Columnstore index 要用batch mode不要选SQL 2012/2014要选SQL 2016/2017/2019(2) Segment elimination and column eliminationSegment elimination 也叫 Rowgroup elimination从上图可知如果表SalesTable创建了ColumnStore IndexSQL Server会自动把不需要的字段(column)不需要的rowgroup过滤掉。CCI如何过滤Row group/segment请看下面的元数据SELECT segment_id, object_name(p.object_id), s.column_id, s.min_data_id, s.max_data_id FROM sys.column_store_segments s, sys.partitions pwhere p.object_id object_id(FactResellerSalesXL_CCI) andp.hobt_id s.hobt_id and column_id 2从上图可以看到该表的CCI的第二个字段(column)有12个segment每个segment都记录了最大值和最小值这样sql语句查询的时候很容易过滤不需要的segment。另外执行 下面的语句set statistics IO ONset statistics TIME ONSELECT Productkey, OrderQuantity as curqty,Sum (OrderQuantity) OVER (ORDER BY ProductKey) AS TotalQuantityFROM FactResellerSalesXL_CCI WHERE orderdatekey in ( 20060301,20060401)正因为第二个字段(orderdatekey)的元数据存储了最大值/最小值所以上面的SQL 直接skip了 7个segment只在另外的5个segment中查找数据(3) Aggregate Pushdown从SQL2016 开始对于Select SUM(sales) from 这样的语句当表非常大的时候Aggregate Pushdown 特性可以极大提高性能。简单的来说对于sum/avg/group by/max/count这样·的函数表有数以亿计的记录SQL Server 会自动的把处理大量数据的工作放在执行计划的第一步也就是最接近存储的地方这样传送给执行计划的下一步的数据会大大减少。该特性自动进行不用任何调整。7. CCI 的维护。和其他传统的row store index一样CCI也会有碎片(fragment)。有两类碎片1.对于CCI只有一个Delta Store是正常的如果有多个Delta Store(超过10个以上)那就是碎片化需要进行维护2. 对于列存储的Row Group删除数据只是逻辑删除没有物理删除如果删除的数据占该RowGroup中超过10%那就是碎片化需要进行维护。从SQL 2016开始使用 Alter Index on (1) self merge当Row group中的逻辑删除记录数占到10%以上就会使用self merge物理删除这些记录(2)merge两个Row group的记录数加起来都不到1百万那么merge操作会把这两个RG 合并成一个8. Columnstore 和 In-Memory OLTP的结合首先解释什么是 Real-time Operational Analytics。之前介绍的传统的数据仓库适合使用 CCI对比传统的数据仓库现在有一些混合型的应用场景就是既有OLTP也有数据仓库的查询。这种场景就是直接在OLTP的数据库上跑一些报表的大SQL好处如下因为没有专门的数据仓库节约了硬件没有经过ETLOLTP的数据一般是最新的而传统的数据仓库往往经过ETL数据往往不是最新的。因为没有ETLStage 数据库的硬件ETL的维护、软件的成本都节省了。缺点也很明显不能像传统数据仓库有多个数据源没有经过ETL数据的结构不能像传统数据库那样实现星形/雪花建模同时在一个数据库上跑OLTP和报表SQL互相影响性能前面介绍了Real-time Operational Analytics可以使用disk based table(磁盘表)也可以使用in memory table;如果使用前者就可以使用noclustered columnstore index如果使用in memory table,必须使用 clustered columnstore index。下面介绍后者首先该表是 in-memory OLTP table,表上可以创建hash index 或range index这些都是传统的row store(行存储)只是把这些都搬到内存当中图中最下面是columnstore index(列存储)也是放着memory当中。上图告诉我们数据实现了冗余in memory table存储了数据而内存中的columnstore index也存储了数据而且是数据、索引放在一起。每当有数据insert先对in memory table的hot 部分(尾部)进行insert当这一部分的记录数达到1百万这些数据会转移到columnstore index 当中。当SQL 语句是OLTP类型in memory table 可以很好的处理效率非常高具体原理要参考in memory OLTP特性。当SQL 语句是报表类语句columnstore index可以高效处理。从上图看该场景需要比较多的memory。
