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

12-31

背景是這樣：
有一張大表，只會增加，不會修改刪除，需要從中選擇多個數據集進行碰撞，找出相同用戶並且出現最多的
表的格式類似：
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的方式，不能精確去重，不確定是不是符合需求。

用量子計算機，或者用別人提前算好的數據：）