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

《Mysql应用开源MySQL高效数据仓库解决方案：Infobright详细介绍》要点：
本文介绍了Mysql应用开源MySQL高效数据仓库解决方案：Infobright详细介绍，希望对您有用。如果有疑问，可以联系我们。

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

MYSQL利用Infobright特征

MYSQL应用长处：

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

MYSQL应用Infobright的价值

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

MYSQL应用Infobright的适用场景

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

MYSQL应用限制：

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

MYSQL应用与MySQL对比

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

MYSQL应用infobright有两个发布版：开源的ICE及闭源商用的IEE.ICE提供了足够用的功能,但不能 INSERT,DELETE,UPDATE,只能LOAD DATA INFILE.IEE除提供更充分的功能外,据说查询速度也要更快.

MYSQL应用社区ICE版,国内各年夜企业均有测试,投入生成系统的较少,主要有以下原因：

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

MYSQL应用ICE与IEE版本区别

MYSQL应用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应用架构

MYSQL应用基于MySQL的内部架构 C Infobright采取与MySQL类似的内部架构,下面是Infobright的架构图：

MYSQL利用

MYSQL应用灰色部门是mysql原有的模块,白色与蓝色部门则是 infobright自身的.

MYSQL应用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).

MYSQL应用Infobright的模块

1. Optimizer优化器.最小化的解压缩数据,有效提高执行计划.
2. Knowledge Grid知识网格.存储元数据、列信息、表关系,数据块分布状态统计信息,同等查询状态缓存信息
3. Data Pack数据块.真实数据压缩存放位置,依照数据存储块保存

MYSQL应用Data Pack(数据块)压缩层

MYSQL应用存储引擎最底层是一个个的Data Pack(数据块).每一个Pack装着某一列的64K个元素,所有数据依照这样的形式打包存储,每一个数据块进行类型相关的压缩(即根据不同数据类型采用不同的压缩算法),压缩比很高.它上层的压缩器与解压缩器就做了这个事情.

MYSQL应用Infobright号称数据压缩比率是10:1到40:1.前面我们已经说过了Infobright的压缩是根据DP里面的数据类型,系统自动选择压缩算法,而且自适应地调节算法的参数以达到最优的压缩比.先看看在实验环境下的压缩比率,如下图所示：

MYSQL利用

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

MYSQL应用既然Infobright会根据具体的数据类型进行压缩,那我们就看看分歧的数据类型具有什么样的压缩比率.如下表所示：

MYSQL利用

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

MYSQL应用再看看act字段,act字段使用了comment lookup,比简单的char类型具有更佳的压缩比率和查询性能.comment lookup的原理其实比拟像位图索引.对于comment lookup的使用下一章节将细细讲述.在所有的字段当中date字段的压缩比率是最高的,最后数据的大小只有0.1M.varchar的压缩比率就比拟差了,所以除非必要,不然不建议使用varchar.

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

MYSQL利用Knowledge Grid(知识网格)

MYSQL应用压缩层再向上就是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数据是装载数据的时候产生的,另外一些事是查询的时候产生.

MYSQL应用

MYSQL应用Knowledge Grid可分为四部门,DPN、Histogram、CMAP、P-2-P.

MYSQL利用DPN如上所述.

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

MYSQL利用

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

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

MYSQL利用

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

MYSQL应用Pack-To-Pack是Join操作的时候产生的,它是表示join的两个DP中操作的两个列之间关系的位图,也便是二进制表示的矩阵.

MYSQL利用

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

MYSQL应用粗拙集(Rough Sets)是Infobright的核心技术之一.Infobright在执行查询的时候会根据知识网络(Knowledge Grid)把DP分成三类：

1. 相关的DP(Relevant Packs),满足查询条件限制的DP
2. 不相关的DP(Irrelevant Packs),不满足查询条件限制的DP
3. 可疑的DP(Suspect Packs),DP里面的数据部门满足查询条件的限制

MYSQL应用案例：

MYSQL利用

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

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

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

MYSQL利用comment lookup的使用

MYSQL应用前面已经阐发了Infobright的构架,简要介绍了Infobright的压缩过程和工作原理.现在来讨论查询优化的问题.

MYSQL利用

MYSQL应用1)配置环境：在Linux下面,Infobright环境的配置可以依据README里的要求,配置brighthouse.ini文件.

MYSQL利用2)选取高效的数据类型

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

• TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
• DECIMAL(尽量减少小数点位数)
• DATE,TIME

MYSQL应用效率比较低的、不保举使用的数据类型有：

• BINARY VARBINARY
• FLOAT
• DOUBLE
• VARCHAR
• TINYTEXT TEXT

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

MYSQL应用3)使用comment lookup

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

MYSQL应用comment lookup使用很简单,在创立数据库表的时候如下定义即可：

MYSQL利用?

MYSQL利用4)尽量有序地导入数据

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

MYSQL利用5)使用高效的查询语句.

MYSQL应用这里涉及的内容比拟多了,总结如下：

• 尽量不适用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
• 尽量使用系统提供的函数

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

MYSQL应用Infobright导入对象

• Insert
• MySQL 导入工具 (@bh_dataformat='mysql')
• ETL工具：http://www.infobright.org/Downloads/ContributedSoftware/
• Infobright 自身的导入工：CSV格式(@bh_dataformat='txt_variable'),二进制格式(@bh_dataformat='binary')
• DLP 散布式导入工具(1.6TB/小时)

MYSQL应用参考链接：

• infobright贸易网站：http://www.infobright.com/
• infobright社区交流网站：http://www.infobright.org/
• mysql对infobright的介绍：http://dev.mysql.com/tech-resources/articles/datawarehousing_mysql_infobright.html
• 关于infobright的介绍视频：http://www.infobright.com/Resource-Library/Webcasts-Podcasts/?infobright_product_demo

欢迎参与《Mysql应用开源MySQL高效数据仓库解决方案：Infobright详细介绍》讨论，分享您的想法，编程之家PHP学院为您提供专业教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!