开源MySQL高效数据仓库解决方案：Infobright详细介绍

infobright是一款基于独特的专利知识网格技术的列式数据库。infobright是开源的mysql数据仓库解决方案，引入了列存储方案，高强度的数据压缩，优化的统计计算(类似sum/avg/group by之类)，infobright 是基于mysql的，但不装mysql亦可，因为它本身就自带了一个。mysql可以粗分为逻辑层和物理存储引擎，infobright主要实现的就是一个存储引擎，但因为它自身存储逻辑跟关系型数据库根本不同，所以，它不能像innodb那样直接作为插件挂接到mysql，它的逻辑层是mysql的逻辑层加上它自身的优化器。

infobright特征

优点：

1. 大数据量查询性能强劲、稳定：百万、千万、亿级记录数条件下，同等的select查询语句，速度比myisam、innodb等普通的mysql存储引擎快5～60倍。高效查询主要依赖特殊设计的存储结构对查询的优化，但这里优化的效果还取决于数据库结构和查询语句的设计。
2. 存储数据量大：tb级数据大小，几十亿条记录。数据量存储主要依赖自己提供的高速数据加载工具（百g/小时）和高数据压缩比（>10:1）
3. 高数据压缩比：号称平均能够达到 10:1 以上的数据压缩率。甚至可以达到40:1，极大地节省了数据存储空间。高数据压缩比主要依赖列式存储和 patent-pending 的灵活压缩算法.
4. 基于列存储：无需建索引，无需分区。即使数据量十分巨大，查询速度也很快。用于数据仓库，处理海量数据没一套可不行。不需要建索引，就避免了维护索引及索引随着数据膨胀的问题。把每列数据分块压缩存放，每块有知识网格节点记录块内的统计信息，代替索引，加速搜 索。
5. 快速响应复杂的聚合类查询：适合复杂的分析性sql查询，如sum, count, avg, group by

infobright的价值

1. 节约设计开销。没有复杂的数据仓库模型设计要求(比如星状模型、雪花模型)，无需要物化视图、数据分区、索引建立
2. 节省存储资源。高压缩比率通常是10:1，某些应用可能达到40:1
3. 集成利用广泛。和众多的bi套件相容，比如pentaho、cognos、jaspersof
4. 降低运维成本。随着数据库的逐渐增大，查询和装载性能持续保持稳定，实施和管理简单，需要极少的管理
5. 商业保证。第一个商业支持的开源仓储分析数据库，是oracle/mysql 官方推荐的仓储集成架构

infobright的适用场景

1. 大数据量的分析应用。网页/在线分析、移动分析、客户行为分析、分析营销和广告
2. 日志/事件管理系统。电信详单分析和报告、系统/网络 安全认证记录
3. 数据集市。企事业单位特定数据仓库、为中小企业提供数据仓库
4. 嵌入式分析。为独立软件供应商/ saas供应商提供嵌入式分析应用

限制：

1. 不支持数据更新：社区版infobright只能使用“load data infile”的方式导入数据，不支持insert、update、delete。这使对数据的修改变得很困难，这样就限制了它作为实时数据服务的数据仓库来使用。
2. 不支持高并发：只能支持10多个并发查询，虽然单库 10 多个并发对一般的应用来说也足够了，但较低的机器利用率对投资者来说总是一件不爽的事情，特别是在并发小请求较多的情况下。
3. 没有提供主从备份和横向扩展的功能。如果没有主从备份，想做备份的话，也可以主从同时加载数据，但只能校验最终的数据一致性，使得从机在数据加载时停服务的时间较长；横向扩展方面，它本身就不是分布式的存储系统。

与mysql对比

1. infobright适用于数据仓库场合：即非事务、非实时、非多并发；分析为主；存放既定的事实，例如日志，或汇总的大量的数据。所以它并不适合于应对来自网站用户的请求。实际上它取一条记录比mysql要慢很多，但它取100w条记录会比mysql快。
2. mysql的总数据文件占用空间通常会比实际数据多，因为它还有索引。infobright的压缩能力很强大，按列按不同类型的数据来压缩。
3. 服务形式与接口跟mysql一致，可以用类似mysql的方式启用infobright服务，然后原来连接mysql的应用程序都可以以类似的方式连接与查询infobright。这对熟练mysql者来说是个福音，学习成本基本为0。

infobright有两个发布版：开源的ice及闭源商用的iee。ice提供了足够用的功能，但不能 insert，delete，update，只能load data infile。iee除提供更充分的功能外，据说查询速度也要更快。

社区ice版，国内各大企业均有测试，投入生成系统的较少，主要有以下原因：

1. 对dml、alter语句限制
2. 需定时增量load导出导入
3. 自带的myisam难以支持高并发，若想充分利用服务器资源，需开启另外的mysql实例
4. 对中文等多字节文字支持不好
5. 仅支持单核调度
6. 缺少原厂的支持

ice与iee版本区别

iee包含针对大多数企业工作需求的附加特性，如：更好的查询性能、dml语句支持、分布式导入等。另外，iee版本还包含了一定级别的infobright原厂或代理商的支持救援服务、产品培训等。

1. 明显的查询性能差异。虽然iee和ice版本均具有明显超出例如oracle、sql server、mysql等行式数据库的查询性能，但iee还要比ice版本快50-500%。这个明显差距来自于iee核心引擎中特有的——多线程调度模块(自iee3.5引入).而在ice中，一个独立的查询只能使用单个cpu核心，其他的查询进程只能使用其他核心。对于需要筛选和区分大量数据的复杂查询，使用iee多线程调度模块可以显著地节约查询时间。
2. 支持dml语句。iee支持标准的sql 数据操作语言，使用insert、update、delete操控数据。而ice只支持load data infile进行数据导入，任何数据的变化都需要重新导入全部数据。dml语句的使用会降低数据查询性能，随次数递增。
3. 支持ddl语句。包括alter table rename,add column,drop column（但是列操作只能对最后列生效）
4. 支持hadoop接口（通过dlp）
5. 高级复制和高可用。iee版本包含主从功能，基于sql statement
6. 更简易的导入和更快的导入速度。iee支持分布式导入工具-dlp；且包含标准的mysql原生loader，用于处理一些复杂数据的导入，另一方面也说明ibloader的容错性较差
7. load或dml同时的一致性查询
8. 支持临时表
9. 其他商业授权，售后支持等

架构

基于mysql的内部架构 – infobright采取与mysql相似的内部架构，下面是infobright的架构图：

灰色部分是mysql原有的模块，白色与蓝色部分则是 infobright自身的。

infobright跟mysql一样的两层结构：

• 逻辑层：处理查询逻辑（服务及应用管理）,逻辑层右端的loader与unloader是infobright的数据导入导出模块，也即处理sql语句里load data infile … 与select … into file任务，由于infobright面向的是海量数据环境，所以这个数据导入导出模块是一个独立的服务，并非直接使用mysql的模块。逻辑层的infobright优化器包在mysql查询优化器的外面，如下面将会提到的，因为它的存储层有一些特殊结构，所以查询优化方式也跟 mysql有很大差异。
• 存储引擎：infobright的默认存储引擎是brighthouse，但是infobright还可以支持其他的存储引擎，比如myisam、mrg_myisam、memory、csv。infobright通过三层来组织数据，分别是dp(data pack)、dpn（data pack node）、kn（knowledge node）。而在这三层之上就是无比强大的知识网络（knowledge grid）。

infobright的模块

1. optimizer优化器。最小化的解压缩数据，有效提高执行计划。
2. knowledge grid知识网格。存储元数据、列信息、表关系，数据块分布状态统计信息，同等查询状态缓存信息
3. data pack数据块。真实数据压缩存放位置，按照数据存储块保存

data pack（数据块）压缩层

存储引擎最底层是一个个的data pack（数据块）。每一个pack装着某一列的64k个元素，所有数据按照这样的形式打包存储，每一个数据块进行类型相关的压缩（即根据不同数据类型采用不同的压缩算法），压缩比很高。它上层的压缩器与解压缩器就做了这个事情。

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三列。

knowledge grid（知识网格）

压缩层再向上就是infobright最重要的概念：knowledge grid（知识网格）这也是infobright放弃索引却能应用于大量数据查询的基础。knowledge grid构架是infobright高性能的重要原因。它包含两类结点：

1. data pack node（数据块节点）：data pack node和data pack是一一对应的关系。dpn记录着每一个dp里面存储和压缩的一些统计数据，包括最大值（max）、最小值（min）、null的个数、单元总数count、sum。avg等等。至不同值的量等等；knowledge node则存储了一些更高级的统计信息，以及与其它表的连接信息，这里面的信息有些是数据载入时已经算好的，有些是随着查询进行而计算的，所以说是具备一 定的“智能”的。
2. knowledge node里面存储着指向dp之间或者列之间关系的一些元数据集合，比如值发生的范围（miin_max）、列数据之间的关联。大部分的kn数据是装载数据的时候产生的，另外一些事是查询的时候产生。

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中操作的两个列之间关系的位图，也就是二进制表示的矩阵。

• 存储在memory中，作用域在一个sission中
• 提高join查询性能，无论是新建还是复用的

粗糙集（rough sets）是infobright的核心技术之一。infobright在执行查询的时候会根据知识网络（knowledge grid）把dp分成三类：

1. 相关的dp（relevant packs），满足查询条件限制的dp
2. 不相关的dp（irrelevant packs），不满足查询条件限制的dp
3. 可疑的dp（suspect packs），dp里面的数据部分满足查询条件的限制

案例：

select count(*) from employees where salary > 100000 and age < 35 and job = ‘it' and city = ‘san mateo';

1. 查找包含salary > 100000的数据包
2. 查找包含age < 35的数据包
3. 查找包含job = 'it'的数据包
4. 查找包含city = ‘san mateo'的数据包
5. 去除所有与检索条件不相干的标记
6. 最后在确定的数据包内解压缩相关数据
7. 执行检索

从上面的分析可以知道，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会自适应地调节算法的参数以达到最优的压缩比。

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

comment lookup的使用

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

1）配置环境：在linux下面，infobright环境的配置可以根据readme里的要求，配置brighthouse.ini文件。

2）选取高效的数据类型

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能够显著地提高压缩比率和查询性能。

3）使用comment lookup

comment lookup只能显式地使用在char或者varchar上面。comment lookup可以减少存储空间，提高压缩率，对char和varchar字段采用comment lookup可以提高查询效率。comment lookup实现机制很像位图索引，实现上利用简短的数值类型替代char字段已取得更好的查询性能和压缩比率。comment lookup的使用除了对数据类型有要求，对数据也有一定的要求。一般要求数据类别的总数小于10000并且当前列的单元数量/类别数量大于10。comment lookup比较适合年龄，性别，省份这一类型的字段。

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

act   char(15)   comment 'lookup',
part  char(4) 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里面尽量剔除不要的字段。
• 避免使用select * from table
• 避免使用union all
• 尽量使用系统提供的函数

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

infobright导入工具

• insert
• mysql 导入工具 （@bh_dataformat='mysql'）
• etl工具：http://www.infobright.org/downloads/contributed‐software/
• infobright 自身的导入工：csv格式(@bh_dataformat='txt_variable'),二进制格式(@bh_dataformat='binary')
• dlp 分布式导入工具（1.6tb/小时）

参考链接：

• infobright商业网站：
• infobright社区交流网站：
• mysql对infobright的介绍：
• 关于infobright的介绍视频：http://www.infobright.com/resource-library/webcasts-podcasts/?infobright_product_demo