開源MySQL高效數據倉庫解決方案：Infobright詳細介紹，mysqlinfobright
Infobright是一款基於獨特的專利知識網格技術的列式數據庫。Infobright是開源的MySQL數據倉庫解決方案，引入了列存儲方案，高強度的數據壓縮，優化的統計計算(類似sum/avg/group by之類)，infobright 是基於mysql的，但不裝mysql亦可，因為它本身就自帶了一個。mysql可以粗分為邏輯層和物理存儲引擎，infobright主要實現的就是一個存儲引擎，但因為它自身存儲邏輯跟關系型數據庫根本不同，所以，它不能像InnoDB那樣直接作為插件掛接到mysql，它的邏輯層是mysql的邏輯層加上它自身的優化器。
Infobright特征
優點：
- 大數據量查詢性能強勁、穩定：百萬、千萬、億級記錄數條件下，同等的SELECT查詢語句，速度比MyISAM、InnoDB等普通的MySQL存儲引擎快5～60倍。高效查詢主要依賴特殊設計的存儲結構對查詢的優化，但這裡優化的效果還取決於數據庫結構和查詢語句的設計。
- 存儲數據量大：TB級數據大小，幾十億條記錄。數據量存儲主要依賴自己提供的高速數據加載工具（百G/小時）和高數據壓縮比（>10:1）
- 高數據壓縮比：號稱平均能夠達到 10:1 以上的數據壓縮率。甚至可以達到40:1，極大地節省了數據存儲空間。高數據壓縮比主要依賴列式存儲和 patent-pending 的靈活壓縮算法.
- 基於列存儲：無需建索引，無需分區。即使數據量十分巨大，查詢速度也很快。用於數據倉庫，處理海量數據沒一套可不行。不需要建索引，就避免了維護索引及索引隨著數據膨脹的問題。把每列數據分塊壓縮存放，每塊有知識網格節點記錄塊內的統計信息，代替索引，加速搜 索。
- 快速響應復雜的聚合類查詢：適合復雜的分析性SQL查詢，如SUM, COUNT, AVG, GROUP BY
Infobright的價值
- 節約設計開銷。沒有復雜的數據倉庫模型設計要求(比如星狀模型、雪花模型)，無需要物化視圖、數據分區、索引建立
- 節省存儲資源。高壓縮比率通常是10:1，某些應用可能達到40:1
- 集成利用廣泛。和眾多的BI套件相容，比如Pentaho、Cognos、Jaspersof
- 降低運維成本。隨著數據庫的逐漸增大，查詢和裝載性能持續保持穩定，實施和管理簡單，需要極少的管理
- 商業保證。第一個商業支持的開源倉儲分析數據庫，是Oracle/MySQL 官方推薦的倉儲集成架構
Infobright的適用場景
- 大數據量的分析應用。網頁/在線分析、移動分析、客戶行為分析、分析營銷和廣告
- 日志/事件管理系統。電信詳單分析和報告、系統/網絡 安全認證記錄
- 數據集市。企事業單位特定數據倉庫、為中小企業提供數據倉庫
- 嵌入式分析。為獨立軟件供應商/ SaaS供應商提供嵌入式分析應用
限制：
- 不支持數據更新：社區版Infobright只能使用“LOAD DATA INFILE”的方式導入數據，不支持INSERT、UPDATE、DELETE。這使對數據的修改變得很困難，這樣就限制了它作為實時數據服務的數據倉庫來使用。
- 不支持高並發：只能支持10多個並發查詢，雖然單庫 10 多個並發對一般的應用來說也足夠了，但較低的機器利用率對投資者來說總是一件不爽的事情，特別是在並發小請求較多的情況下。
- 沒有提供主從備份和橫向擴展的功能。如果沒有主從備份，想做備份的話，也可以主從同時加載數據，但只能校驗最終的數據一致性，使得從機在數據加載時停服務的時間較長；橫向擴展方面，它本身就不是分布式的存儲系統。
與MySQL對比
- infobright適用於數據倉庫場合：即非事務、非實時、非多並發；分析為主；存放既定的事實，例如日志，或匯總的大量的數據。所以它並不適合於應對來自網站用戶的請求。實際上它取一條記錄比mysql要慢很多，但它取100W條記錄會比mysql快。
- mysql的總數據文件占用空間通常會比實際數據多，因為它還有索引。infobright的壓縮能力很強大，按列按不同類型的數據來壓縮。
- 服務形式與接口跟mysql一致，可以用類似mysql的方式啟用infobright服務，然後原來連接mysql的應用程序都可以以類似的方式連接與查詢infobright。這對熟練mysql者來說是個福音，學習成本基本為0。
infobright有兩個發布版：開源的ICE及閉源商用的IEE。ICE提供了足夠用的功能，但不能 INSERT，DELETE，UPDATE，只能LOAD DATA INFILE。IEE除提供更充分的功能外，據說查詢速度也要更快。
社區ICE版，國內各大企業均有測試，投入生成系統的較少，主要有以下原因：
- 對DML、alter語句限制
- 需定時增量load導出導入
- 自帶的MyISAM難以支持高並發，若想充分利用服務器資源，需開啟另外的MySQL實例
- 對中文等多字節文字支持不好
- 僅支持單核調度
- 缺少原廠的支持
ICE與IEE版本區別
IEE包含針對大多數企業工作需求的附加特性，如：更好的查詢性能、DML語句支持、分布式導入等。另外，IEE版本還包含了一定級別的Infobright原廠或代理商的支持救援服務、產品培訓等。
- 明顯的查詢性能差異。雖然IEE和ICE版本均具有明顯超出例如Oracle、SQL Server、MySQL等行式數據庫的查詢性能，但IEE還要比ICE版本快50-500%。這個明顯差距來自於IEE核心引擎中特有的——多線程調度模塊(自IEE3.5引入).而在ICE中，一個獨立的查詢只能使用單個CPU核心，其他的查詢進程只能使用其他核心。對於需要篩選和區分大量數據的復雜查詢，使用IEE多線程調度模塊可以顯著地節約查詢時間。
- 支持DML語句。IEE支持標准的SQL 數據操作語言，使用insert、update、delete操控數據。而ICE只支持Load data infile進行數據導入，任何數據的變化都需要重新導入全部數據。DML語句的使用會降低數據查詢性能，隨次數遞增。
- 支持DDL語句。包括alter table rename,add column,drop column（但是列操作只能對最後列生效）
- 支持Hadoop接口（通過DLP）
- 高級復制和高可用。IEE版本包含主從功能，基於SQL statement
- 更簡易的導入和更快的導入速度。IEE支持分布式導入工具-DLP；且包含標准的MySQL原生loader，用於處理一些復雜數據的導入，另一方面也說明IBloader的容錯性較差
- Load或DML同時的一致性查詢
- 支持臨時表
- 其他商業授權，售後支持等
架構
基於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的模塊
- Optimizer優化器。最小化的解壓縮數據，有效提高執行計劃。
- Knowledge Grid知識網格。存儲元數據、列信息、表關系，數據塊分布狀態統計信息，同等查詢狀態緩存信息
- 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高性能的重要原因。它包含兩類結點：
- Data Pack Node（數據塊節點）：Data Pack Node和Data Pack是一一對應的關系。DPN記錄著每一個DP裡面存儲和壓縮的一些統計數據，包括最大值（max）、最小值（min）、null的個數、單元總數count、sum。avg等等。至不同值的量等等；Knowledge Node則存儲了一些更高級的統計信息，以及與其它表的連接信息，這裡面的信息有些是數據載入時已經算好的，有些是隨著查詢進行而計算的，所以說是具備一 定的“智能”的。
- 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分成三類：
- 相關的DP（Relevant Packs），滿足查詢條件限制的DP
- 不相關的DP（Irrelevant Packs），不滿足查詢條件限制的DP
- 可疑的DP（Suspect Packs），DP裡面的數據部分滿足查詢條件的限制
案例：
復制代碼 代碼如下:
SELECT COUNT(*) FROM employees WHERE salary > 100000 AND age < 35 AND job = ‘IT' AND city = ‘San Mateo';
- 查找包含salary > 100000的數據包
- 查找包含age < 35的數據包
- 查找包含job = 'IT'的數據包
- 查找包含city = ‘San Mateo'的數據包
- 去除所有與檢索條件不相干的標記
- 最後在確定的數據包內解壓縮相關數據
- 執行檢索
從上面的分析可以知道，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數據類型使用的一些經驗和注意點：
- Infobright的數值類型的范圍和MySQL有點不一樣，比如Infobright的Int的最小值是-2147483647，而MySQl的Int最小值應該是-2147483648。其他的數值類型都存在這樣的問題。
- 能夠使用小數據類型就使用小數據類型，比如能夠使用SMALLINT就不適用INT，這一點上Infobright和MySQL保持一致。
- 避免效率低的數據類型，像TEXT之類能不用就不用，像FLOAT盡量用DECIMAL代替，但是需要權衡畢竟DECIMAL會損失精度。
- 盡量少用VARCHAR，在MySQL裡面動態的Varchar性能就不強，所以盡量避免VARCHAR。如果適合的話可以選擇把VARCHAR改成CHAR存儲甚至專程INTEGER類型。VARCHAR的優勢在於分配空間的長度可變，既然Infobright具有那麼優秀的壓縮性能，個人認為完全可以把VARCHAR轉成CHAR。CHAR會具有更好的查詢和壓縮性能。
- 能夠使用INT的情況盡量使用INT，很多時候甚至可以把一些CHAR類型的數據往整型轉化。比如搜索日志裡面的客戶永久id、客戶id等等數據就可以用BIGINT存儲而不用CHAR存儲。其實把時間分割成year、month、day三列存儲也是很好的選擇。在我能見到的系統裡面時間基本上是使用頻率最高的字段，提高時間字段的查詢性能顯然是非常重要的。當然這個還是要根據系統的具體情況，做數據分析時有時候很需要MySQL的那些時間函數。
- 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商業網站：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
