深入解析：DB2 V10.5新特性列式存储表的优点与缺点

1.什么是列组织表

列组织表技术是 DB10.5 BLU 引入的新技术，按列组织的表，更加方便了复杂的查询，如果将这种表格式与星型模式数据集市配合，那么可以简化设计和调优，从而显著改进存储性能、查询性能和易用性

2.列组织表的管理方式

可以看到行组织模式的表是都是存放在一起的，而列组织模式的表是被分开保存的。

3.列存储使用环境

列存储主要适合 OLAP 类型应用，比如数据仓库系统，数据挖掘系统，决策支持系统等。这些系统有一些共同特征：数据量大，查询语句比重大，复杂的查询多。

4.如何启用列存储

官方文档上描述的是将 DB2_WORKLOAD 注册表变量设置为 ANALYTICS，更改后重启便会生效。这样做有助于配置内存、表组织、页大小和扩展数据块大小，并且会启用工作负载管理。

4.1更改环境变量

[db2inst1@enmodb2 ~]$ db2set DB2_WORKLOAD=ANALYTICS[db2inst1@enmodb2 ~]$ db2set -all[i] DB2_WORKLOAD=ANALYTICS[g] DB2_COMPATIBILITY_VECTOR=MYS[g] DB2SYSTEM=enmodb2[g] DB2INSTDEF=db2inst1

4.2创建列组织测试表

[db2inst1@enmodb2 ~]$ db2 "create table  testinfo (empno char(6),lastname varchar(15),hirdate date,salary decimal(9),comm decimal(9)) organize by column"DB20000I  The SQL command completed successfully.

用户在没有指定 organize by 的情况下可以将数据库参数 dft_table_org 设置为 COLUMN，缺省就是创建列组织表。

4.3添加数据
有选择的导出 employee 的数据。

[db2inst1@enmodb2 ~]$ db2 "export to '/home/db2inst1/export/employee.del' of del messages exp_employee.msg select empno,lastname,hiredate,salary,comm from employee"

Number of rows exported: 42

将数据导入 testinfo 中。

4.4查询数据
然后对数据进行查询。

同样的查询在行组织表与列组织表的区别，在进行全表扫描，或者几个列全扫描的时候，列组织表可以发挥出很大的优势，在列式存储中同类型的数据存放在同一个block里面，压缩性能比较好。而且在列式存储中，任何列都可以作为索引。

5.列式存储优点

5.1自动回收空间
当 DB2_WORKLOAD 设置成 ANALYTICS 的时候，对于列组织表默认会开启 reorg，这个时候 DB2 的列组织表就会处于一个自动维护的状态。DBA 也不用再对一些表进行定期的 reorg 操作，而且存储空间支持在线释放，不会影响系统的正常运行。

[db2inst1@enmodb2 ~]$ db2 update db cfg using AUTO_REORG onDB20000I  The UPDATE DATA  CONFIGURATION command completed successfully.[db2inst1@enmodb2 ~]$ db2 update db cfg using auto_maint onDB20000I  The UPDATE DATA  CONFIGURATION command completed successfully.[db2inst1@enmodb2 ~]$ db2 update db cfg using auto_tbl_maint onDB20000I  The UPDATE DATA  CONFIGURATION command completed successfully.[db2inst1@enmodb2 ~]$ db2 restart db testinfoDB20000I  The RESTART DATA  command completed successfully.Automatic maintenance                      (AUTO_MAINT) = ONAutomatic table maintenance          (AUTO_TBL_MAINT) = ONAutomatic reorganization               (AUTO_REORG) = ON

测试自动回收空间

[db2inst1@enmodb2 ~]$ db2 get db cfg |grep reorg     Automatic reorganization               (AUTO_REORG) = ON[db2inst1@enmodb2 ~]$ db2 "select count(0) from testinfo"1          -----------   16252928[db2inst1@enmodb2 ~]$ db2 "delete from db2inst1.testinfo where salary<500000"DB20000I  The SQL command completed successfully.[db2inst1@enmodb2 ~]$ db2 runstats on table DB2INST1.TESTINFODB20000I  The RUNSTATS command completed successfully.[db2inst1@enmodb2 ~]$ db2 "select RECLAIMABLE_SPACE from> Table(SYSPROC.ADMIN_GET_TAB_INFO('DB2INST1','TESTINFO'))"RECLAIMABLE_SPACE   --------------------                   0

5.2 减少 IO
在某些环境下，对于行存储是按行存放的，在读取的时候需要将整行的数据都读出，这样无形中增加了数据的 IO。而列存储的存储方式是按列存储，任何列都可以作为索引，只读出所需访问的列，读取时冗余很少，从而减少了 I/O，提高了性能。

5.3良好的压缩比
当 UTIL_HEAP_SZ 足够大，能使得在转换成列存储表之后获取很好的压缩比。对于传统的压缩技术了来讲，可以很大程度的节省存储，某些压缩算法甚至可以不需要解压缩而实现对数据的直接操作，节省了解压缩的开销，从而降低了对 CPU 的消耗。经过优化后的数据库，很多压缩数据会存储在内存中，减少查询时消耗的内存资源。

6.列式存储缺点

6.1筛选条件限制
如果查询中有筛选条件，这样列式存储未必就能发挥相应的优势，而且相对资源消耗可能会比行式存储更高。

6.2不支持 GBK 字符集
而且在 DB2 中，列式存储只支持数据库 UTF-8 并且国家地域必须为 cn。因为unicode里面好像不支持 GBK，如果是 GBK 字符集的数据库，创建列组织表的时候直接会报错，提示该数据库的字符集不支持。

[db2inst1@enmodb2 ~]$ db2 connect to cocoData  Connection Information Data  server        = DB2/LINUXX8664 10.5.9 SQL authorization ID   = DB2INST1 Local data  alias   = COCO [db2inst1@enmodb2 ~]$ db2 get db cfg  Data  Configuration for Data   Data  configuration release level                    = 0x1000 Data  release level                                          = 0x1000 Data  territory                                                  = cn Data  code page                                              = 1386 Data  code set                                                 = gbk Data  country/region code                               = 86 Data  collating sequence                                 = UNIQUE Alternate collating sequence              (ALT_COLLATE) =  Number compatibility                                              = OFF Varchar2 compatibility                                             = OFF Date compatibility                                                    = OFF Data  page size                                                 = 32768

创建列组织表

[db2inst1@enmodb2 ~]$ db2 "create table  testinfo (empno char(6),lastname varchar(15),hirdate date,salary decimal(9),comm decimal(9)) organize by column"DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned:SQL1233N  The statement failed because the statement includes the use of functionality that is supported only in Unicode data s or Unicode data s with specific data  collations, but the current data  is not a Unicode data  or is a Unicode data  with unsupported data  collations.  SQLSTATE=560AA

6.3必须存储于自动存储表空间
必须将列存储表存储在自动存储的表空间里面。

[db2inst1@enmodb2 ~]$ db2 "create  tablespace mytem02 pagesize 32K managed by data  using (file '/home/db2inst1/tbs/mytem02' 10M) bufferpool IBMDEFAULTBP"DB20000I  The SQL command completed successfully.[db2inst1@enmodb2 ~]$ db2 "create table  testinfo3 (empno char(6),lastname varchar(15),hirdate date,salary decimal(9),comm decimal(9)) organize by column in mytem02"DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned:SQL0284N  Table creation failed because the table space "MYTEM02" that was specified in the statement after the clause "IN" is not a supported type of table space for that clause.  Table space type: "MANAGED BY DATA ".  SQLSTATE=42838>>>

7.总结

相对于列存储，行存储的好处是增加修改数据容易，适合于 OLTP 事务型应用。列存储表在进行查询的时候需要占用大量的内存，同时列存储表中的数据是按照列存储的，这样的组织方式不太适合查询单行或者几行数据，这就决定了列存储表不适合 OLTP 的系统，因此不是所有的表都适合转换成列存储表。但是在某些场景下，比如数据仓库，在对数据进行查询时可以实现数据跳读，从而进一步降低数据库的 IO，提升查询的性能。

原文发布时间为：2018-07-18
本文作者：李培杨
本文来自云栖社区合作伙伴“数据和云”，了解相关信息可以关注“数据和云”。