TB級大表秒級任意次元分析 - 采樣估值滿足高效TOP N等分析需求

postgresql , 采樣 , sample , top n , 統計分析

估值計算是統計學的常用手段。因為資料量龐大，求精确數值需要耗費巨大的資源，而統計分析并不要求完全精确的資料，是以估值計算是一種折中的方法，廣泛應用于統計分析場景。

postgresql是一個功能強大的資料庫，在估值統計方面，提供了很多方法。

1、postgresql中，求估計的uv，增量uv等(即count distinct)，可以通過hll插件來實作。

<a href="https://github.com/digoal/blog/blob/master/201608/20160825_02.md">《greenplum 最佳實踐 - 估值插件hll的使用(以及hll分式聚合函數優化)》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130228_01.md">《postgresql hll (hyperloglog) extension for "state of the art cardinality estimation algorithm" - 3》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130227_01.md">《postgresql hll (hyperloglog) extension for "state of the art cardinality estimation algorithm" - 2》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130226_01.md">《postgresql hll (hyperloglog) extension for "state of the art cardinality estimation algorithm" - 1》</a>

2、求任意字段的top value(包括數組字段的top 元素)，以及count，可以通過統計資訊柱狀圖得到。

<a href="https://github.com/digoal/blog/blob/master/201308/20130811_01.md">《postgresql pg_stats used to estimate top n freps values and explain rows》</a>

3、求全表記錄數可以通過pg_class.reltuples得到。

4、求任意sql的傳回記錄數(例如求分頁)，或者求<code>count(*)</code>的估值(将sql轉換為select 1 from ...即可)，可以通過explain的估值得到，例子如下。

<a href="https://github.com/digoal/blog/blob/master/201605/20160506_01.md">《論count與offset使用不當的罪名 和 分頁的優化》</a>

5、求多個字段的唯一值個數，可以通過定義自定義統計資訊得到非常準确的估值。

<a href="https://github.com/digoal/blog/blob/master/201709/20170902_02.md">《postgresql 10 黑科技 - 自定義統計資訊》</a>

6、求帶條件的查詢的估值，比如某個省的top n電影明星，我們可以通過先采樣，然後在采樣中進行計算的方法得到。

<a href="https://github.com/digoal/blog/blob/master/201609/20160929_01.md">《postgresql 巧妙的資料采樣方法》</a>

<a href="https://github.com/digoal/blog/blob/master/201706/20170602_02.md">《postgresql 資料采樣與脫敏》</a>

本文将介紹采樣估值的方法。

之前寫過一個場景，是泛内容網站的透視分析，資料量比較龐大，計算全量需要掃描的資料量較大。看看采樣的方法是否滿足需求？

<a href="https://github.com/digoal/blog/blob/master/201708/20170827_01.md">《音視圖(泛内容)網站透視分析 db設計 - 阿裡雲(rds、hybriddb) for postgresql最佳實踐》</a>

1、表結構

2、生成随機值函數

3、寫入測試資料

資料樣式如下

求任意條件下的tag1的top n元素。

4、分析表，生成柱狀圖。

表大小 16 gb。

5、求某個條件下的精确top n元素，實際上有1000個熱點id，是以傳回top 10的count結果非常近似，後面在使用估值時，得到的top 10可能就沒這麼準了，但是一定是在1000個id以内的。

6、求同樣條件下的采樣top n

采樣算法參考文章末尾，postgresql内置了2種采樣方法，同時支援擴充采樣方法，其中有兩個内置的擴充采樣方法，實際上内置總共有4種采樣方法。

使用塊級采樣（目前采樣不支援并行）。

采樣的方法，得到的top n是很準确的，因為例子用了1000個随機值，并且每個随機值的機率是一樣的，如果傳回top 1000，那就準确無疑了。

重新設計熱點，總共寫入40億測試資料：

一級熱點，1，5億

二級熱點，2-4，5億

三級熱點，5-10，5億

四級熱點，11-30，5億

普通資料，1-100000，20億

1、表結構設計

2、寫入測試資料

3、分析表

表大小，254 gb。

4、精确top 30

5、采樣top 30

ok，采樣千分之一的時候（僅需約掃描254mb資料），隻花了不到1秒，就算出了準确的top 30，而且準确度相當的高。

如果在greenplum中支援這個功能，會很爽，一萬億的資料，秒級任意次元鑽取透視不是夢。

1、采樣與精确查詢耗時對比

1.1、求數組元素top n

查詢

表大小

記錄數

求top n耗時

精确，32并行

16gb

1.5億

23秒

精确，非并行

155秒

采樣5%

7秒

采樣2%

3秒

1.2、求scalar類型top n

254gb

40億

21秒

471秒

32秒

13秒

采樣0.1%

0.86秒

2、采樣計算達到了很高的精确度，同時耗費資源很少。雖然并行計算也非常快，但是需要消耗更多的cpu和io資源，并行度就會大打折扣，除非有足夠的資源給你折騰，否則能采用估值計算的時候，還是建議估值計算。

3、估值計算的效率評估：

由于目前估值計算不能采用多核并行，處理速度約每秒254mb，那麼要達到1秒内的響應，對于254gb的表，采樣設定為0.1%，對于1tb的表，可以将采樣設定為0.025%）。那麼tb級的表，也能實作任意次元秒級估算。

4、采樣方法

a tablesample clause after a table_name indicates that the specified sampling_method should be used to retrieve a subset of the rows in that table. this sampling precedes the application of any other filters such as where clauses. the standard postgresql distribution includes two sampling methods, bernoulli and system, and other sampling methods can be installed in the database via extensions.

the bernoulli and system sampling methods each accept a single argument which is the fraction of the table to sample, expressed as a percentage between 0 and 100. this argument can be any real-valued expression. (other sampling methods might accept more or different arguments.) these two methods each return a randomly-chosen sample of the table that will contain approximately the specified percentage of the table's rows. the bernoulli method scans the whole table and selects or ignores individual rows independently with the specified probability. the system method does block-level sampling with each block having the specified chance of being selected; all rows in each selected block are returned. the system method is significantly faster than the bernoulli method when small sampling percentages are specified, but it may return a less-random sample of the table as a result of clustering effects.

the optional repeatable clause specifies a seed number or expression to use for generating random numbers within the sampling method. the seed value can be any non-null floating-point value. two queries that specify the same seed and argument values will select the same sample of the table, if the table has not been changed meanwhile. but different seed values will usually produce different samples. if repeatable is not given then a new random sample is selected for each query, based upon a system-generated seed. note that some add-on sampling methods do not accept repeatable, and will always produce new samples on each use.

5、postgresql中還支援很多其他的估值方法，請參考本文開頭部分的介紹。