[转][转]列式数据库之infobright以及架构

　　年前听过Sybase中国区副总裁的关于列式数据库的讲座之后就一直被列式数据库强大的性能吸引。最近邂逅了infobright，列式数据库的学习展开了。

　　Sysbase可以说是列式数据库的先驱，Sysbase IQ 15 就是Sybase 目前最新的列式数据库。它具有强大的功能，包括数据的快速加载、超高速的分析

性能、强大的业务智能分析、领先的数据建模能力等等。       infobright是一个基于MySQL的数据仓库系统，    共工的不周山的blog上有挺详细的介绍。同样

是列式数据库，但是infobright和Sybase IQ系列还是有很大的不同。infobright采用的Knowledge Grid来组织数据，infobright内部是没有索引，就这点就

节省了不少的空间。而Sybase IQ系列还是使用了索引，而这些索引我个人的理解就是位图索引的改进版。白皮书上说，infobright的数据压缩比可以是10：1到40：1，

个人拿庞大的日志数据库做了个小小实验，感觉压缩也没那么夸张。如果依据位图索引的思想，每列数据的相似度越高就会具有越高的压缩比。infobright应该也是满足这

一点的，但是具体Knowledge Grid内部如何实现还不清楚，有待继续考究。      

　　 infobright的优点有很多，简单列举如下：       

　　Infobright的优点：

     （1）高压缩比率

     （2）快速响应复杂的分析查询语句

     （3）随着数据库的逐渐增大，查询和装载性能基本保持稳定

     （4）没有特殊的数据仓库模型（比如星状模型、雪花模型）要求

     （5）无需要物化视图、复杂的数据分区策略、索引

     （6）实施和管理简单，需要极少的管理

     （7）和众多的BI套件相容，比如Pentaho、Cognos、Jaspersoft。

      infobright有两个版本ICE和IEE，目前ICE的版本是3.3.1，支持64位Linux和32位windows。ICE不支持DML，也就是不支持insert、update等操作。

       至于infobright的框架待改天分析。

　　Infobright的总体构架图如下:

　　如上图所示，Infobright采用了和MySQL一致的构架，分为两层。上层是服务及应用管理，下层是存储引擎。Infobright的默认存储引擎是brighthouse，但是Infobright还可以支持其他的存储引擎，比如MyISAM、MRG_MyISAM、Memory、CSV。Infobright通过三层来组织数据，分别是DP(Data Pack)、DPN（Data Pack Node）、KN（Knowledge Node）。而在这三层之上就是无比强大的知识网络（Knowledge Grid）。

　　数据块（DP）是存储的最低层，列中每64K个单元组成一个DP。DP比列更小，具有更好的压缩比率；又比单个数据单元更大，具有更好的查询性能。

　　数据块节点（DPN），DPN和DP之间是一对一的关系。DPN记录着每一个DP里面存储和压缩的一些统计数据，包括最大值、最小值、null的个数、单元总数count、sum等等。

　　KN里面存储着指向DP之间或者列之间关系的一些元数据集合，比如值发生的范围（MIin_Max）、列数据之间的关联。大部分的KN数据是装载数据的时候产生的，另外一些事是查询的时候产生。

　　在这三层之上是知识网络（Knowledge Grid），Knowledge Grid构架是Infobright高性能的重要原因。

　　Knowledge Grid可分为四部分，DPN、Histogram、CMAP、P-2-P。

　　DPN如上所述。Histogram用来提高数字类型（比如date，time，decimal）的查询的性能。Histogram是装载数据的时候就产生的。DPN中有mix、max，Histogram中把Min-Max分成1024段，如果Mix_Max范围小于1024的话，每一段就是就是一个单独的值。这个时候KN就是一个数值是否在当前段的二进制表示。

　　Histogram的作用就是快速判断当前DP是否满足查询条件。如上图所示，比如select id from customerInfo where id>50 and id<70。那么很容易就可以得到当前DP不满足条件。所以Histogram对于那种数字限定的查询能够很有效地减少查询DP的数量。

　　CMAP是针对于文本类型的查询，也是装载数据的时候就产生的。CMAP是统计当前DP内，ASCII在1-64位置出现的情况。如下图所示

　　比如上面的图说明了A在文本的第二个、第三个、第四个位置从来没有出现过。0表示没有出现，1表示出现过。查询中文本的比较归根究底还是按照字节进行比较，所以根据CMAP能够很好地提高文本查询的性能。

　　Pack-To-Pack是Join操作的时候产生的，它是表示join的两个DP中操作的两个列之间关系的位图，也就是二进制表示的矩阵。

　　Knowledge Grid还是比较复杂的，里面还有很多细节的东西，可以参考官方的白皮书和 Brighthouse: an analytic data warehouse for ad-hoc queries这篇论文。

　　前面已经简要分析了Infobright的构架，现在来介绍Infobright的工作原理。

　　粗糙集（Rough Sets）是Infobright的核心技术之一。Infobright在执行查询的时候会根据知识网络（Knowledge Grid）把DP分成三类：

　　相关的DP（Relevant Packs），满足查询条件限制的DP

　　不相关的DP（Irrelevant Packs），不满足查询条件限制的DP

　　可疑的DP（Suspect Packs），DP里面的数据部分满足查询条件的限制

　　下面是一个案例：

　　如图所示，每一列总共有5个DP，其中限制条件是A>6。所以A1、A2、A4就是不相关的DP，A3是相关的DP，A5是可疑的DP。那么执行查询的时候只需要计算B5中满足条件的记录的和然后加上Sum（B3），Sum（B3）是已知的。此时只需要解压缩B5这个DP。从上面的分析可以知道，Infobright能够很高效地执行一些查询，而且执行的时候where语句的区分度越高越好。where区分度高可以更精确地确认是否是相关DP或者是不相关DP亦或是可以DP，尽可能减少DP的数量、减少解压缩带来的性能损耗。在做条件判断的使用，一般会用到上一章所讲到的Histogram和CMAP，它们能够有效地提高查询性能。

　　多表连接的的时候原理也是相似的。先是利用Pack-To-Pack产生join的那两列的DP之间的关系。

　　比如：SELECT MAX(X.D) FROM T JOIN X ON T.B = X.C WHERE T.A > 6。Pack-To-Pack产生T.B和X.C的DP之间的关系矩阵M。假设T.B的第一个DP和X.C的第一个DP之间有元素交叉，那么M[1,1]=1，否则M[1,1]=0。这样就有效地减少了join操作时DP的数量。

　　前面降到了解压缩，顺便提一提DP的压缩。每个DP中的64K个元素被当成是一个序列，其中所有的null的位置都会被单独存储，然后其余的non-null的数据会被压缩。数据的压缩跟数据的类型有关，infobright会根据数据的类型选择压缩算法。infobright会自适应地调节算法的参数以达到最优的压缩比。

　　Infobright里面支持所有的MySQL原有的数据类型。其中Integer类型比其他数据类型更加高效。尽可能使用以下的数据类型：

　　TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT

　　DECIMAL（尽量减少小数点位数）

　　DATE ，TIME

　　效率比较低的、不推荐使用的数据类型有：

　　BINARY VARBINARY

　　FLOAT

　　DOUBLE

　　VARCHAR

　　TINYTEXT TEXT

　　Infobright数据类型使用的一些经验和注意点：

　　（1）Infobright的数值类型的范围和MySQL有点不一样，比如Infobright的Int的最小值是-2147483647，而MySQl的Int最小值应该是-2147483648。其他的数值类型都存在这样的问题。

　　（2）能够使用小数据类型就使用小数据类型，比如能够使用SMALLINT就不适用INT，这一点上Infobright和MySQL保持一致。

　　（3）避免效率低的数据类型，像TEXT之类能不用就不用，像FLOAT尽量用DECIMAL代替，但是需要权衡毕竟DECIMAL会损失精度。

　　（4）尽量少用VARCHAR，在MySQL里面动态的Varchar性能就不强，所以尽量避免VARCHAR。如果适合的话可以选择把VARCHAR改成CHAR存储甚至专程INTEGER类型。VARCHAR的优势在于分配空间的长度可变，既然Infobright具有那么优秀的压缩性能，个人认为完全可以把VARCHAR转成CHAR。CHAR会具有更好的查询和压缩性能。

　　（5）能够使用INT的情况尽量使用INT，很多时候甚至可以把一些CHAR类型的数据往整型转化。比如搜索日志里面的客户永久id、客户id等等数据就可以用BIGINT存储而不用CHAR存储。其实把时间分割成year、month、day三列存储也是很好的选择。在我能见到的系统里面时间基本上是使用频率最高的字段，提高时间字段的查询性能显然是非常重要的。当然这个还是要根据系统的具体情况，做数据分析时有时候很需要MySQL的那些时间函数。

　　（6）varchar和char字段还可以使用comment lookup，comment lookup能够显著地提高压缩比率和查询性能。

　　Infobright号称数据压缩比率是10:1到40:1。前面我们已经说过了Infobright的压缩是根据DP里面的数据类型，系统自动选择压缩算法，并且自适应地调节算法的参数以达到最优的压缩比。

　　先看看在我的实验环境下的压缩比率，如下图所示：

　　相信读者可以很清楚地看到，整体的压缩比率是20.302。但是这里有一个误区，这里的压缩比率指的是数据库中的原始数据大小/压缩后的数据大小，而不是文本文件的物理数据大小/压缩后的数据大小。很明显前者会比后者大出不少。在我的实验环境下，后者是7:1左右。一般来说文本数据存入数据库之后大小会比原来的文本大不少，因为有些字段被设置了固定长度，占用了比实际更多的空间。还有就是数据库里面会有很多的统计信息数据，其中就包括索引，这些统计信息数据占据的空间绝对不小。Infobright虽然没有索引，但是它有KN数据，通常情况下KN数据大小占数据总大小的1%左右。

　　既然Infobright会根据具体的数据类型进行压缩，那我们就看看不同的数据类型具有什么样的压缩比率。如下表所示：

　　首先看看Int类型的压缩比率，结果是压缩比率上Int<mediumint<smallint。细心地读者会很容易发现tinyint的压缩比率怎么会比int还小。数据压缩比率除了和数据类型有关之外，还和数据的差异性有特别大关系，这是显而易见。posFlag只有0，1，-1三种可能，这种数据显然不可能取得很好的压缩比率。

　　再看看act字段，act字段使用了comment lookup，比简单的char类型具有更佳的压缩比率和查询性能。comment lookup的原理其实比较像位图索引。对于comment lookup的使用下一章节将细细讲述。

　　在所有的字段当中date字段的压缩比率是最高的，最后数据的大小只有0.1M。varchar的压缩比率就比较差了，所以除非必要，不然不建议使用varchar。

　　上面的数据很清楚地展示了Infobright强大的压缩性能。在此再次强调，数据的压缩不只是和数据类型有关，数据的差异程度起了特别大的作用。在选择字段数据类型的时候，个人觉得性能方面的考虑应该摆在第一位。比如上面表中一些字段的选择就可以优化，ip可以改为bigint类型，date甚至可以根据需要拆分成year/month/day三列。

　　前面的章节一直涉及到comment lookup，这里将简单介绍comment lookup的使用。

　　comment lookup只能显式地使用在char或者varchar上面。Comment Lookup可以减少存储空间，提高压缩率，对char和varchar字段采用comment lookup可以提高查询效率。

　　Comment Lookup实现机制很像位图索引，实现上利用简短的数值类型替代char字段已取得更好的查询性能和压缩比率。CommentLookup的使用除了对数据类型有要求，对数据也有一定的要求。一般要求数据类别的总数小于10000并且当前列的单元数量/类别数量大于10。Comment Lookup比较适合年龄，性别，省份这一类型的字段。

　　comment lookup使用很简单，在创建数据库表的时候如下定义即可：

　　 act   char(15)   comment 'lookup',

　　part  char(4) comment 'lookup',

　　前面已经分析了Infobright的构架，简要介绍了Infobright的压缩过程和工作原理。现在来讨论查询优化的问题。

　　（1）配置环境

　　　　在Linux下面，Infobright环境的配置可以根据README里的要求，配置brighthouse.ini文件。

　　（2） 选取高效的数据类型

　　　　参见前面章节。

　　（3）使用comment lookup

　　　　参见前面章节。

　　（4）尽量有序地导入数据

　　　　前面分析过Infobright的构架，每一列分成n个DP，每个DPN列面存储着DP的一些统计信息。有序地导入数据能够使不同的DP的DPN内的数据差异化更明显。比如按时间date顺序导入数据，那么前一个DP的max（date）<=下一个DP的min(date)，查询的时候就能够减少可疑DP，提高查询性能。换句话说，有序地导入数据就是使DP内部数据更加集中，而不再那么分散。

　　（5）使用高效的查询语句。

　　　　这里涉及的内容比较多了，总结如下：

　　      尽量不适用or，可以采用in或者union取而代之

　　　　减少IO操作，原因是infobright里面数据是压缩的，解压缩的过程要消耗很多的时间。

　　　　查询的时候尽量条件选择差异化更明显的语句

           Select中尽量使用where中出现的字段。原因是Infobright按照列处理的，每一列都是单独处理的。所以避免使用where中未出现的字段可以得到较好的性能。

           限制在结果中的表的数量，也就是限制select中出现表的数量。

          尽量使用独立的子查询和join操作代替非独立的子查询

　　　  尽量不在where里面使用MySQL函数和类型转换符

          尽量避免会使用MySQL优化器的查询操作

　　　  使用跨越Infobright表和MySQL表的查询操作

　　　　尽量不在group by 里或者子查询里面使用数学操作，如sum（a*b）。

　　　　select里面尽量剔除不要的字段。

　　Infobright执行查询语句的时候，大部分的时间都是花在优化阶段。Infobright优化器虽然已经很强大，但是编写查询语句的时候很多的细节问题还是需要程序员注意。