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

李培杨 云和恩墨西区交付技术顾问，有多年数据库运维经验，长期服务移动运营商行业客户，熟悉 DB2 数据库故障诊断，数据库迁移升级。

>>>

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查询数据

然后对数据进行查询。

4.5行组织表与列组织表的对比

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

>>>

5.列式存储优点

5.1自动回收空间

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

[db2inst1@enmodb2 ~]$ db2 update db cfg using AUTO_REORG on DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2inst1@enmodb2 ~]$ db2 update db cfg using auto_maint on DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2inst1@enmodb2 ~]$ db2 update db cfg using auto_tbl_maint on DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2inst1@enmodb2 ~]$ db2 restart db testinfo DB20000I The RESTART DATABASE command completed successfully. Automatic maintenance (AUTO_MAINT) = ON Automatic table maintenance (AUTO_TBL_MAINT) = ON Automatic 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.TESTINFO DB20000I 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 coco Database Connection Information Database server = DB2/LINUXX8664 10.5.9 SQL authorization ID = DB2INST1 Local database alias = COCO [db2inst1@enmodb2 ~]$ db2 get db cfg Database Configuration for Database Database configuration release level = 0x1000 Database release level = 0x1000 Database territory = cn Database code page = 1386 Database code set = gbk Database country/region code = 86 Database collating sequence = UNIQUE Alternate collating sequence (ALT_COLLATE) = Number compatibility = OFF Varchar2 compatibility = OFF Date compatibility = OFF Database 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 databases or Unicode databases with specific database collations, but the current database is not a Unicode database or is a Unicode database with unsupported database collations. SQLSTATE=560AA

6.3必须存储于自动存储表空间

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

[db2inst1@enmodb2 ~]$ db2 "create tablespace mytem02 pagesize 32K managed by database 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 DATABASE". SQLSTATE=42838

>>>

7.总结

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

作者：李培杨。

转载：意向文章下方留言。