搞點多維分析，糙快猛的解決方式就是使用ROLAP(關系型OLAP)了。數據經維度建模后存儲在MySQL，ROLAP引擎(比方開源的Mondrian)負責將OLAP請求轉化為SQL語句提交給數據庫。OLAP計算分析功能導致MySQL須要進行較多復雜SQL查詢，性能調優不可缺少，本文總結了一些有用原則。

OLAP特點

OLAP的典型應用包含復雜動態報表，須要支持鉆取(上卷和下鉆)、切片、切塊和旋轉操作。下表總結了OLAP和OLTP系統的主要差別。OLAP的特點決定了SQL的查詢場景和優化方案，下文將從索引、聚合、子查詢、表連接和Pivoting等幾個方面分別介紹。

OLAP

OLTP

用戶量

分析人員用戶量相對小

高并發

數據庫設計

維度模型：星型、雪花型號

規范化

數據量

大，動輒千萬級別

小，一般不超過百萬級別

SQL讀寫場景

定期導入，一般無更新，復雜查詢每次檢索大量數據

以事務為單位每次讀寫少量數據

老生常談之索引

在權衡數據容錯恢復和性能之后，存儲引擎選擇的是Innodb。Innodb索引的特性是主鍵聚集索引和B+Tree數據結構。利用這兩個特性，可以提升數據導入和多維度組合切片的性能。

1)?????? 數據導入速度

下圖為Innodb表主鍵索引示意圖，聚集索引使表中全部數據必須依照主鍵順序存儲在主鍵索引葉子節點上。假設不依照主鍵順序導入數據，會導致額外的分頁、數據查找、移動IO操作，這樣，Innodb表的插入速度嚴重依賴于插入順序。解決方法比較簡單：主鍵使用Auto_Increment列。

2)?????? 多維度切片

多維度組合查詢、分組和匯總操作很常見，那么在多個維度字段上加入復合索引是不可缺少的，而復合索引的字段選擇和順序尤為重要。

誰排NO.1？一般遵循下面原則：

a)????????Mysql僅僅進行索引最左前綴匹配，能夠選擇最常查詢的字段排首位。特殊情況：如果少量查詢場景不存在該字段怎么處理？須要另外再建索引嗎？如果在盤古系統中，運營單位通常會出如今全部查詢中，所以會建立[運營單位，行業，產品線……]的復合索引，但某些高級別管理人員的查詢語句中，不包括運營單位，那么須要再建立[行業，產品線……]的復合索引嗎？答案是看情況，提供小技巧：應用層處理，在不包括運營單位條件的查詢SQL中增加“運營單位 in(全部運營單位)”條件

b)????????最佳性能優化原則決定索引區分度最大的字段排首位(可用count(distinct column)/count(*)計算)

還有個大家往往會忽略的問題，誰排最后呢？答案是：將可能存在范圍條件檢索的字段放最后。來個案例

……WHERE avg_csm_weekly >100 AND trade_id= 19 ORDER BY balance如果建立的復合索引為[avg_cms_weekly,trade_id, ,balance]，那么因為在avg_csm_weekly上存在范圍條件，MySQL不會使用剩余的索引。

聚合

MySQL不支持Hash聚合，僅支持流聚合。流聚合會先依據GROUP BY的字段進行排序，然后流式訪問排序好的數據，進行分組聚合。假設在explain的extra列中看到Using temporary和Using filesort，說明聚合使用了暫時表和文件排序操作，這可能導致性能低下。最佳優化目標是讓聚合操作使用Covering Index，即全然不用查詢表數據，僅僅在索引上完畢聚合查詢。

以下查詢語句會使用復合索引[trade_id,product_line_id]

select trade_id,product_line_id,count(*) from data_acct_info_weekly group bytrade_id,product_line_id

觀察查詢計劃，在extra列顯示Using index，說明該操作為Covering

Index查詢。

在OLAP分析中，時間范圍上的聚合操作很普遍。以下以賬號每日消費表為演示樣例，總結幾種常見的時間聚合查詢模板

account_id(賬戶)

stdate(數據日期)

click_pay(點擊消費)

1

2013-08-01

100

1

2013-08-02

150

2

2013-08-01

125

1)累計聚合

返回賬戶增加某度以來累計消費和平均值。

SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)

FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b

ON a.account_id=b.account_id ANDb.stdate<=a.stdate

GROUP BY a.account_id,a.stdate

ORDER BY a.account_id,a.stdate

2)滑動累計

返回賬戶固定窗體時間內累計消費和平均值

SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)

FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b

ON a.account_id=b.account_id ANDb.stdate<=a.stdate

AND b.stdate>=DATE_ADD(a.stdate,INTERVAL -30 DAY)

GROUP BY a.account_id,a.stdate

ORDER BY a.account_id,a.stdate

3)MTD累計

返回賬戶月初以來累計消費和平均值

SELECT a.account_id,a.stdate,SUM(click_pay),AVG(click_pay)

FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b

ON a.account_id=b.account_id ANDb.stdate<=a.stdate

AND b.stdate>=DATE_FORMAT(a.stdate,”%Y-%M-01”)

GROUP BY a.account_id,a.stdate

ORDER BY a.account_id,a.stdate再探討下ROLLUP和CUBE。如果用戶須要對N個維度進行聚合操作，須要進行N次GROUP BY再將結果進行UNION，而使用ROLLUP能夠一次查詢出N次GROUP BY 操作的結果。以下的兩條語句查詢結果一致，運行計劃上卻不同，前者僅僅須要掃描一次，后者則須要掃描表四次。

語句1：

SELECT col1,col2,col3,SUM(col4) FROM table

GROUP BYcol1,col2,col3

WITH ROLLUP語句2：

SELECT col1,col2,col3,SUM(col4) FROM table

GROUP BYcol1,col2,col3

UNION

SELECT col1,col2,NULL,SUM(col4) FROM table

GROUP BYcol1,col2

UNION

SELECT col1,NULL,NULL ,SUM(col4) FROM table

GROUP BY col1

UNION

SELECT NULL,NULL,NULL,SUM(col4) FROM table與ROLLUP僅僅在同一層次上對維度進行匯總不同，CUBE對全部維度進行匯總，N個維度CUBE須要2的N次方分組操作。當前版本號的MySQL還不支持CUBE操作，但和用多個GROUP操作UNION模擬ROLLUP同理，也能夠用多個ROLLUP操作UNION模擬CUBE。

子查詢vs JOIN

復雜的需求場景導致某些子查詢場景不可避免。關于子查詢，存在不少性能陷阱和認識誤區值得關注。

1)MySQL子查詢性能差的主要原因是子查詢產生暫時表嗎？不全然正確，暫時表并不可怕，一個完整的SQL語句，FROM/JOIN/GROUP/WHERE/ORDER等操作，不考慮索引優化的情況下，都有可能產生暫時表。所以更嚴格的表述是在子查詢產生的暫時表上查詢無法利用索引導致性能低下。

2)IN子查詢往往性能不佳的真實原因是什么？是IN查詢的暫時表數據量太大，MySQL太弱，僅僅能支持極少數量的IN子查詢嗎？不一定，顯示列表IN(a,b,c)查詢的性能并不算差，IN子查詢真正的性能陷阱在于Mysql優化器往往將IN獨立子查詢優化成EXISTS相關子查詢！所以當觀察SELECT * FROM table1 WHERE table1.id IN(SELECT id FROM table2)的查詢計劃，會發現table2的查詢為DEPEDENTSUBQUERY，原因事實上是MySQL優化策略+歷史原因。

3)子查詢的性能一定弱于JOIN嗎？未必，因為Mysql不支持Semi Join(注)，所以在某些須要場景下，使用子查詢性能優于JOIN。比方A表和B表一對多關系，假設只想查詢在B表中存在相應記錄的A表記錄，假設使用JOIN，須要用DISTINCT或者GROUP操作進行去重操作。使用關聯子查詢能夠避免這部分開銷。SELECT id FROM table1 WHERE EXISTS(SELECT table2.id FROM table2WHERE table2.id=table1.id)

關于Join，Mysql使用Nested Loop算法(注)。在典型的星型維度模型中，維度表數據量遠小于事實表，JOIN操作往往是大小表連接，性能問題不大，這方面不多講。結合前面提到的Covering Index，介紹一個利用JOIN提高分頁效率的歪招：

分頁往往須要用到LIMIT OFFSET,在偏移量非常大的時候，比方LIMIT 100000,50，MySQL須要檢索100050數據，性能嚴重下降。常見的處理方式是a)添加排序輔助列，將LIMIT轉化為在輔助列上范圍查找操作b)應用層緩存機制c)需求折中，沒有人會翻到100000頁。以上皆不靈的時候，能夠選擇Covering

Index+Join。

SELECT * FROM table1 INNER JOIN

(SELECT id FROM table1 ORDER BY indexed_col limit 100000,50) AS a

ON table1.id = a.id這樣的方式效率較高，由于暫時表a僅在索引上進行操作(Innodb索引葉子節點上存儲了主鍵值)，取得所需行id之后，再和完整的表進行Join獲取其它所需列。

注：MySQL的著名分支MarioDB支持Semi

Join和Hash Join

其它

Pivoting&Unpivoting主要關注行列旋轉變化，還能夠用來對聚合數據進行格式化用于報表展現，在此不再復述

總結

以上是生活随笔為你收集整理的olap mysql_MySQL与OLAP：分析型SQL查询最佳实践探索的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。