千萬數據 group by,union,group by能否做到秒查?

背景是這樣:

有一張大表,只會增加,不會修改刪除,需要從中選擇多個數據集進行碰撞,找出相同用戶並且出現最多的

表的格式類似:

id, userid, data_time, provin

表的數據量:

50億+

選擇的數據集個數:

2-10

數據集的量級:

200W+

類似的SQL:

SELECT
userid,
COUNT(userid) match_num
FROM (
SELECT
userid
FROM
report
WHERE
provin = "guangdong"
AND data_time &>= "2016-12-01 19:15:16"
AND data_time &<= "2016-12-15 19:15:21" GROUP BY userid UNION ( SELECT userid FROM report WHERE r.provin = "xinjiang" AND data_time &>= "2016-12-01 19:15:16"
AND data_time &<= "2016-12-15 19:15:21" GROUP BY userid ) UNION ( SELECT userid FROM report WHERE provin = "fujian" AND data_time &>= "2016-11-22 19:15:16"
AND data_time &<= "2016-11-30 19:15:21" GROUP BY userid ) ) GROUP BY userid ORDER BY match_num DESC limit 100

想請教:

1,現有的環境是oracle,但查詢一次基本上都需要幾分鐘,是否可以優化,需要做到秒回或者5秒以內返回

2,如果需要換框架,什麼樣的解決方案比較合適,現在的主要壓力是:a,類似的分析,有三四個相似的場景;b,總體統計和唯一統計(自己實現了預計算,查詢效果良好,但維護麻煩,不能良好擴展)

各種大神不吝賜教~~~


問 SQL 查詢優化,至少也請提供下 DDL 和 EXPLAIN 結果吧……

Disclaimer:從來沒有用過 Oracle,套用 Spark SQL 上的一些經驗試分析一下。幹活干累了刷知乎,權當練手。能有多少優化效果完全不敢說。也請 Oracle 的專家看看是不是靠譜。

查詢優化,無非是削計算和 IO。

邏輯層面上,題主的 query 可以改寫簡化為以下形式(後記:跟題主在評論中討論的時候才發現我審題不清,原 query 中其實涉及到了兩個不同的時間段,而我以為 UNION 起來的三個 subquery 針對的都是同一個時間段。所以下述的簡化版本其實是錯的… 不過優化思路還是有效的,大家湊合著看吧…):

SELECT userid, count(1) AS match_num
FROM (
SELECT userid
FROM report
WHERE
provin IN ("xinjiang", "guangdong", "fujian")
AND data_time &>= "2016-12-01 19:15:16"
AND data_time &<= "2016-12-15 19:15:21" GROUP BY userid, provin ) subq GROUP BY userid ORDER BY match_num DESC LIMIT 100;

改寫依據:

  • UNION 起來的三個 subquery 高度近似,僅在 provin 欄位的 WHERE 條件上有差異,可以考慮把三個 subquery 合併,將 UNION 改寫為三個 provin 判斷條件的 OR,並進一步改寫為 IN。
  • 然而 GROUP BY 使得我們不能直接這樣合併,否則會改變 query 語義。原 query 需要的是三個省份內各自 userid 去重後的並集,那麼把 provin 一併加入 GROUP BY 就好了。

其實還可以改寫為更簡單的版本:

SELECT userid, count(DISTINCT provin) AS match_num
FROM report
WHERE
provin IN ("xinjiang", "guangdong", "fujian")
AND data_time &>= "2016-12-01 19:15:16"
AND data_time &<= "2016-12-15 19:15:21" GROUP BY userid, provin ORDER BY match_num DESC LIMIT 100

沒有採用這個版本的原因是:

  • 不太確定 Oracle 對 distinct aggregation 的優化策略。感覺應該也是跟 Spark SQL 一樣展開成兩重 aggregation 來做,那麼性能上跟上面的版本應該是差不多的(因為 SELECT 里引用了 provin 欄位,可能會多一些中間 IO 開銷)。
  • 上面那個略複雜點的版本解釋起來比較直觀。

手頭沒有 Oracle 可用,無法 EXPLAIN。邏輯層面的這一步改寫能有多少效果很難說:

  • 說不定 Oracle 的優化已經足夠聰明,可以自動完成這個程度的 query rewriting,那麼改寫與否對性能顯然沒有影響。
  • 如果 Oracle 無法自動改寫,那麼減少了兩次全表掃描或全索引掃描(視是否建立了合適的索引而定),性能提升應該比較明顯。

(後記:事後在 Oracle Live SQL 上試了一下以下 SQL 片段:

CREATE TABLE IF NOT EXISTS t(a INT, b INT, c INT, PRIMARY KEY (a));

EXPLAIN PLAN
SET STATEMENT_ID = "union"
FOR
SELECT a FROM t WHERE b &> 0
UNION ALL
SELECT a FROM t WHERE b &< 0; SELECT lpad(operation, 2 * (level - 1), ".") AS operation, options, object_name FROM plan_table;

結果 Oracle 並沒有如我期待的那樣把兩個 UNION 合併,而是妥妥地給出了兩個 full table scan:

SELECT STATEMENT
..UNION-ALL
....TABLE ACCESS
....TABLE ACCESS

略失望。)

物理層面上,可以通過調整表的物理布局來削:

  • 看到 data_time 欄位,加上題主說該表只追加、不刪改,那多半是按時間累積的數據。條件反射想到 partitioned table(只用過 Hive 和 Spark SQL 的 partitioned table,掃了一下 Oracle 文檔,也有 partitioned table,不過機制更複雜靈活):
    • 根據 data_time 按日期建 range partitions
    • provin 欄位只有少量固定取值,拿來建二級 hash / list sub-partitions
  • 從前面改寫完畢的 query 中可以觀察到內側 aggregation 的 key 是 (userid, provin)。針對 (userid, provin) 建二級索引應該可以有效加速內側的 aggregation。同時,按照上述的 partitioning 策略,將二級索引建為 local partitioned index,與各個 partition 對齊。

調整完畢後,由於 partition pruning 的作用,整個 query 只會接觸 15 天 3 個省份的數據和索引,免去了全表 5B+ 行的掃描。在 2M 行的 dataset 上做兩層 aggregation,感覺 Oracle 應該秒級能搞定?

如果 Oracle 的優化器不夠強,沒法自動削掉內層的三次全表或全索引掃描,那麼上述調整後 query 掃描的行數由 3 x 5B 降為 2M,低了三個數量級,秒級應該沒有問題。當然也不排除題主其實已經建了索引和 partitioning,那麼秒級就比較難了。

Oracle partitioned table / index 的內容參考的是 Google 出來的第一條:Partitioning Concepts


看不出來為啥要用union,多次掃描,而外的group by有必要嗎?表有索引嗎?有分區嗎?分區和索引都避免full scan,另外看看查詢計劃,哪裡有優化的空間。

另外預計算為啥不好擴展?


只會增加不會變化的表,多數是要根據你需要的列分表的,沒必要放一起


這種情況,搭建個Drill集群,用IndexR存儲格式,5台機器差不多了。我這麼肯定,是因為我手頭剛好有個數據集的量差不多,最大表60億(tpc-h數據集,-s 1000)。

參考這篇文章https://zhuanlan.zhihu.com/p/27223007

剛好順手,就直接給你跑一下把!(辣雞知乎編輯器,一貼SQL就出錯,只能貼截圖了)

這裡的性能和你的需求相差不多吧!如果想要更快,也很簡單,加機器,或者換更好的硬體,我們用的測試硬體一般般: [Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz] x 2, RAM 64G(實際使用約12G), HDD STATA with 7200RPM。

這裡的where條件和題主的不太一樣,是因為沒有找到合適的欄位不過效果應該是差不多的。還有題主應該是要加個 ALL 吧?

這裡的表schema參考:

CREATE EXTERNAL TABLE IF NOT EXISTS lineitem_v8_1000 (
`L_ORDERKEY` int,
`L_PARTKEY` int,
`L_SUPPKEY` int,
`L_LINENUMBER` int,
`L_QUANTITY` double,
`L_EXTENDEDPRICE` double,
`L_DISCOUNT` double,
`L_TAX` double,
`L_RETURNFLAG` string,
`L_LINESTATUS` string,
`L_SHIPDATE` date,
`L_COMMITDATE` date,
`L_RECEIPTDATE` date,
`L_SHIPINSTRUCT` string,
`L_SHIPMODE` string,
`L_COMMENT` string
)
ROW FORMAT SERDE "io.indexr.hive.IndexRSerde"
STORED AS INPUTFORMAT "io.indexr.hive.IndexRInputFormat"
OUTPUTFORMAT "io.indexr.hive.IndexROutputFormat"
LOCATION "/indexr/segment/lineitem_v8_1000"
TBLPROPERTIES (
"indexr.segment.mode"="vlt",
"indexr.agg.grouping"="false",
"indexr.agg.dims"="l_shipdate,l_commitdate,l_receiptdate"
);

題主如果真的想秒查(&<1s),並且有錢的話,考慮用gpu的方案,MapD的方案看起來很厲害(我沒試過,羨慕ing)!關於一些答主建議的Druid,我想放一個(微笑臉)的表情。

IndexR已經開源版本,地址:https://github.com/shunfei/indexr 。我這裡的測試用的是開源 0.5.0 版本。

利益相關:本人是IndexR開發者,並且我們有提供IndexREE企業版,提供諮詢服務。


由於表比較大,50億的數據量,你建立的這個索引應該也有40G-50G以上,這個索引存在範圍查找,使用的是b樹索引,就算整個索引全在內存中,也很難達到你想到的效果,後續的磁碟取數就更不用說了,所在在資料庫層面比較困難,推薦樓主可以嘗試使用一下es,分散式,每個欄位都有索引,不過代價也是非常大的


這種數據量情況下,不分區索引已經無效了

個人的意見是,能否秒查的前提的是,如果不group by ,返回的記錄條數是多少,我看了是200W

200w數據大概在dbf文件中大小起碼也是1-2個G.即使是ssd硬碟,實際讀取速度實際上也很難超過200m/s

所以,優化到5s內非常不現實。

以我的經驗來看,極限能優化到1分鐘左右了不起了

個人意見,這種業務要麼使用數據倉庫的方案來做,要麼使用流式資料庫方案.


給您個祖傳秘方:拆分sql拿業務層去做,如果慢就是oracle的問題了


不做 join 的話, 聽說 druid 挺適合你的場景的。 沒試過, 試了可以告訴我


clickhouse足以滿足你..

我自己測試過的,單機單表百億級數據量下可以實現adhoc秒級查詢. 集群部署的話性能還有提升.

32G內存機器.

目前已經部署到生產環境,運行穩定.

缺點:

1.不支持表別名.

2.文檔略少.


難。


sql寫得就不對,一幫人討論的熱火朝天,就沒人發現他的sql連語義都不對嗎?

以及毫無意義的union

select userid,count(distinct provin) from ... where....

然並卵,寫對了也快不到哪兒去。。。

不過題主你可以試試告訴我這個正確的sql時間是多少,我純屬好奇。。。


es吧


如果數據不需要動態修改的話,那麼應該可以使用olap之類的解決方案。具體思路就是,離線定期給(新增的)數據分片建靜態索引,將在線的overhead轉移到線下,畢竟傳統資料庫有很大的開銷是用來維護一個可動態修改的索引。對應到開源方案,就是druid之類的了。


這樣的場景,預計算是最好的解決方案。不然的話都在查詢時計算,隨著數據量與日俱增查詢性能必然下降。

可以研究一下apache kylin,正是為了這種場景研發的產品,你值得擁有


首先說明下,不知道業務場景以下緊供參考

1.設計問題,如此大表可否按時間,按年,按季度分區或分表……是否有冷熱數據區分存儲

2.欄位類型最小化,索引最小化檢查

3.拆分查詢,提前查詢統計,緩存,緩存,緩存重要事說三遍

4.檢查分析業務,是否改成其它架構,成本多大(spark,ES,HBASE……),帶來收益和擴展性是否值得

如果架構不想大改優先參考1,2,3


三個子查詢分別用時多少?


沒接觸過這麼大數據量,但是感覺列式資料庫是不是會更合適這種需求啊,尤其是數據只增加不刪改。個人接觸過的有infobright,這個估計社區版的應該達不到你的要求,得企業版的;infinidb,現在叫ColumnStore,跟infobright差不多;另外還有一個Clickhouse,俄羅斯的,自己玩過,也不錯,就是文檔不多


如果能夠接受一定程度誤差,用druid很簡單的:

{
"dataSource": "report",
"granularity": "all",
"intervals": [
"2017-01-01T00:00:00.000/2017-05-31T00:00:00.000"
],
"dimension": "userid",
"metric": "provinCount",
"threshold": 1000,
"aggregations": [
{
"type": "cardinality",
"name": "provinCount",
"fields": ["provin"]
}
],
"queryType": "topN"
}


想到的幾個點

1. userid也放進索引。如果是mysql會有幫助,o不是很熟,但這麼大數據量5秒也夠嗆。

2. 如果userid是整型可以每個分區預計算成bitmap求交集。

3. 互聯網公司這種場景(我理解是類似實時熱賣/排行榜的需求)一般會用kafka+實時計算框架,strom/spark streaming/flink

4. 樓上提到的druid, es在count時候都是hyperloglog的方式,不能精確去重,不確定是不是符合需求。


用量子計算機,或者用別人提前算好的數據 :)


推薦閱讀:

機器學習如今是否取得了「巨大」的進展?
如何評價昨天刷屏的「滴滴大數據丨2016年最「狠」公司排行榜」中,各個公司的加班現象?
相比於傳統 BI,基於 Hadoop 的大數據(Big Data)戰略有何不同?
人工智慧、大數據、雲計算、物聯網,彼此之間有哪些內在聯繫?
如何評價《Big Data:大數據時代》這本書?

TAG:SQL效率 | 大數據 | Oracle資料庫開發 | Spark | MySQLDBA |