千萬數據 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/flink4. 樓上提到的druid, es在count時候都是hyperloglog的方式,不能精確去重,不確定是不是符合需求。
用量子計算機,或者用別人提前算好的數據 :)
推薦閱讀:
※機器學習如今是否取得了「巨大」的進展?
※如何評價昨天刷屏的「滴滴大數據丨2016年最「狠」公司排行榜」中,各個公司的加班現象?
※相比於傳統 BI,基於 Hadoop 的大數據(Big Data)戰略有何不同?
※人工智慧、大數據、雲計算、物聯網,彼此之間有哪些內在聯繫?
※如何評價《Big Data:大數據時代》這本書?
TAG:SQL效率 | 大數據 | Oracle資料庫開發 | Spark | MySQLDBA |