開源的MySQL數據倉庫解決方案：Infobright
1. 概述
</h2>Infobright是一款基于獨特的專利知識網格技術的列式數據庫。Infobright簡單易用，快速安裝部署，使用中無需復雜操作，能大幅度減少管理工作；在應對50TB甚至更多數據量進行多并發復雜查詢時，更能夠顯示出令人驚嘆的速度。相比于MySQL，其查詢速度提升了數倍甚至數十倍，在同類產品中單機性能處于領先地位。為企業劇增的數據規模、增長的客戶需求以及較高的用戶期望提供了全面的解決方案。
2. 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
限制：
1）不支持數據更新：社區版Infobright只能使用“LOAD DATA INFILE”的方式導入數據，不支持INSERT、UPDATE、DELETE。
這使對數據的修改變得很困難，這樣就限制了它作為實時數據服務的數據倉庫來使用。用戶要么忍受數據的非實時或非精確，這樣對最（較）新數據的分析準確性就降低了許多；要么將它作為歷史庫來使用，帶來的問題是實時庫用什么？很多用戶選擇數據倉庫系統，不是因為存儲空間不夠，而是數據加載性能和查詢性能無法滿足要求。
2）不支持高并發：只能支持10多個并發查詢
雖然單庫 10 多個并發對一般的應用來說也足夠了，但較低的機器利用率對投資者來說總是一件不爽的事情，特別是在并發小請求較多的情況下。
3）. 沒有提供主從備份和橫向擴展的功能。
如果沒有主從備份，想做備份的話，也可以主從同時加載數據，但只能校驗最終的數據一致性，這會使得從機在數據加載時停服務的時間較長；橫向擴展方面，倒不是 Infobright 的錯，它本身就不是分布式的存儲系統，但如果把它搞成一個分布式的系統，應該是一件比較好玩的事情。
與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除提供更充分的功能外，據說查詢速度也要更快。
3. 架構
基于MySQL的內部架構 – Infobright采取與MySQL相似的內部架構
下面是Infobright的架構圖：
2）Knowledge Node里面存儲著指向DP之間或者列之間關系的一些元數據集合，比如值發生的范圍（MIin_Max）、列數據之間的關聯。大部分的KN數據是裝載數據的時候產生的，另外一些事是查詢的時候產生。
Knowledge Grid構架是Infobright高性能的重要原因。
</div>
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就是一個數值是否在當前段的二進制表示。
</div>
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這篇論文。
</div>4. 數據類型
5. 工作原理
粗糙集（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會自適應地調節算法的參數以達到最優的壓縮比。
6. 壓縮比例
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三列。
6. 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',
7. 查詢優化
（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優化器雖然已經很強大，但是編寫查詢語句的時候很多的細節問題還是需要程序員注意。
7. 數據導入
對于DW系統而言，龐大數據的遷移成本很高；所以導入和導出的速率及容忍性也是考量數據倉庫產品的重要標準。Infobright基于MySQL所以在數據格式上有比較成型的解決辦法，IB原廠對速率進行了優化。在4.0企業版中推出了DLP分布式導入選件，極大的減少了遷移時間，目前世界最大的光通信提供商JDSU也選用了IB產品，并以DLP為主要選件進行配置。
1、簡介
IB提供了專用的高性能loader，不同于傳統的mysql。IB loader是為了提高導入速度而設計的，所以僅支持特有的mysql loader語法，而且只支持導入格式化的變量和文本源文件.IEE版也支持mysqlloader和insert語句。infobright對txt的格式有非常嚴格的要求，格式不對是不能導入數據的。
2、默認Loader
1）ICE僅支持IB lorder
2）IEE默認使用的是是mysql loader，它能更多的容錯，但速度稍慢。為了最快的導入，使用IB loader，做以下環境的設置
導入步驟：
1）、建表：
mysql>
create table example2 (
id int not null,
textfield varchar(20) not null,
number int not null
)engine=birghthouse; 2)、建立txt/csv數據：</strong></span>
mysql> load data infile 'F:\\in2.txt' into table example2 fields terminated by ',' enclosed by '"';
Mysql>
set @bh_dataformat = ‘txt_variable’;
–使用IB loader來導入CSV格式的變量定長文本
set @bh_dataformat = ‘binary’;
–二進制文件
set @bh_dataformat = ‘mysql’;
–使用mysql loader
3，IB loader語法
IB僅支持load data infile，其他的mysql導入方式不支持
LOAD DATA INFILE ‘/full_path/file_name’
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'char']
[ENCLOSED BY 'char']
[ESCAPED BY 'char']
];
導入前關閉
set AUTOCOMMIT=0;
完成后
COMMIT;
set AUTOCOMMIT=1;
4，區域分隔符
.區域分隔符是可選的，默認設置為
CLAUSE DEFAULT VALUE
FIELDS TERMINATED BY ‘;’ (semicolon)
FIELDS ENCLOSED BY ‘”‘ (double quote)
FIELDS ESCAPED BY ” (none)
5，導入經驗
a. 當導入表格列數很多時，修改brighthouse.ini中LoaderMainHeapSize
b 使用并發導入
c 容忍性排序為txt_variables<binary<mysql
d bh_loader不支持多分隔符
e 大量數據時，DLP是必要選擇
1.妥善處理字符集，在導入和遷移時，盡量將所有%character%均改為與原庫相同的字符集2.選擇合適分隔符，infobright自己缺省默認loader為bh_loader，僅支持單個字節分隔符，不支持如’,,’ ‘||’等
3.IEE企業版還可以使用MySQL_loader，基本上和MySQL一樣，具備所有功能，使用前set @bh_dataformat=’mysql’;
4.遺留問題:
a.白發漁樵江楮上
今天在試用infobright-4.0.4版本的時候，load data 的時候出現錯誤“ERROR 1598 (HY000): Binary logging not possible. Message: Statement cannot be logged to the binary log in row-based nor statement-based format”，當然可用“SET SQL_LOG_BIN = 0”不記錄日志，但是我豈不是用不了復制了？
b.stronghearted：infobright導入數據時，選latin1，剛才選gbk，中文總是亂碼。
stronghearted：回復@W維西:導出innodb的表是gbk，如果建IB的表是gbk，導入的中文會是亂碼。選latin1就正確
W維西：Hi，做了個測試，兩邊GBK在我這邊比較正常，請看http://t.cn/akbcDH 可能還是字符集的問題，所有的變量都要改下:)
mysql數據導入到infobright中
mysql數據導入到infobright中1，在mysql中建一張表：
create table t_mis(uid mediumint not null, cid smallint not null, rating tinyint not null)engine=MyISAM; </pre><br />插入數據：
insert into t_mis(uid,cid,rating) values('70000','3600','5');
2，將數據導出csv文件：
select * from t_mis into outfile 'F:\mytable.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
3，在infobright中建一個表：
create table t_ib(uid mediumint not null, cis smallint not null, rating tinyint not null)engine=brighthouse; </pre><br />4，導入csv到表t_ib中：
load data infile 'F:\mytable.csv' into table t_ib fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
來自：http://blog.csdn.net/hguisu/article/details/11848411