為索引列高效生成分位數

我正在嘗試為分佈式處理系統建構查詢分區器。該系統可以與支持 JDBC 的數據庫一起使用。

此分區器的輸入將是表的名稱和用於分區的列。讓我們假設此列上有一個索引。例如：

Table: customers
Partitioning column: customer_id

從那裡，我將建構以下查詢：

select * from customers where customer_id >= ? and customer_id < ?

然後，我想將域劃分customer_id為 10 個大小大致相同的範圍。我想發出一個快速查詢來解決這個問題。

我percentile_cont在 ANSI SQL 中找到了這個函式（很好，因為很多數據庫都支持這個）——我寫了這個查詢，它似乎有效：

SELECT 
 percentile_cont(customer_id, 0)  over(),
 percentile_cont(customer_id, 0.1)  over(),
 percentile_cont(customer_id, 0.2)  over(),
 percentile_cont(customer_id, 0.3)  over(),
 percentile_cont(customer_id, 0.4)  over(),
 percentile_cont(customer_id, 0.5)  over(),
 percentile_cont(customer_id, 0.6)  over(),
 percentile_cont(customer_id, 0.7)  over(),
 percentile_cont(customer_id, 0.8)  over(),
 percentile_cont(customer_id, 0.9)  over(),
 percentile_cont(customer_id, 1)  over()
FROM customers LIMIT 1

我的問題是：

1. 如果該列customer_id被索引 - 此查詢是否會相當快地快速生成範圍，然後使用它們來並行化 10 個查詢？
2. 有沒有其他方法可以使用分區列為給定表找到類似大小的範圍？

我有壞消息要告訴你。你想做的事情將比你預期的要困難得多。

大多數數據庫都實現了 ANSI SQL 標準的大部分內容，但它們並沒有實現它的所有部分，尤其是對於像視窗函式這樣更高級的東西。一些數據庫平台甚至不支持LIMIT您使用的構造。您需要準備為您支持的每個數據庫平台編寫不同的程式碼。

您連接的數據庫不太可能在每個表的每一列上都有索引。您將需要對正在查詢的數據庫架構進行某種控制，以獲取允許您想要執行的查詢的合理性能的架構。即使這樣，標準的 b-tree 索引也不允許快速計算百分位數。

每個數據庫平台的查詢優化器以明顯不同的方式工作。在一個平台上快速的查詢可能在另一個平台上很慢。考慮以下針對索引 ID 列的簡單查詢：

SELECT MIN(ID), MAX(ID)
FROM table;

該查詢過去在 SQL Server 上非常快，但需要在 Oracle 上進行兩次表掃描（較新版本的 Oracle 可能改進了查詢優化器，不再需要表掃描）。該查詢比您的查詢簡單得多。我不希望您在問題中使用的方法在大多數平台上都表現良好。我將您的語法更改為 SQL Server 支持的語法，但查詢在六分鐘後未完成對大約六百萬行的表。

您還應該考慮放寬問題陳述以允許近似結果。如果目標是將數據拆分為多個部分並在不同的系統上處理每個部分，那麼您可能不需要將表的 10% 準確發送到每個系統。允許近似結果將極大地提高某些方法的查詢性能，尤其是在目標表非常大的情況下。

我建議將您提出的問題更改為類似以下內容：“對於數據庫平台 X，我如何為具有 Z 隔離級別下的 Y 行的表計算快速（定義快速）分位數？” 對於這類問題，該平台的數據庫專家可能會為您提供幫助。但是，由於這些平台的限制，某些平台可能不允許像您希望的那樣快速計算答案。

為了讓您了解性能良好的解決方案是什麼樣的，我將為以下問題提供一個範例解決方案：“對於數據庫平台 SQL Server，我如何計算 SERIALIZABLE 下具有 650 萬行的表的分位數1秒內隔離級別？”

首先，我將向具有聚集列儲存索引的表添加 650 萬行：

CREATE TABLE part_test (
   ID1 BIGINT NOT NULL,
   ID2 BIGINT NOT NULL,
   ID3 VARCHAR(3) NOT NULL,
   ID4 VARCHAR(10) NOT NULL,
   ID5 DATETIME NOT NULL,
   INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO part_test WITH (TABLOCK)
SELECT RN, RN % 100000, LEFT(CAST(NEWID() AS VARCHAR(40)), 3), REPLICATE(CHAR(RN % 60 + 10), 10), DATEADD(SECOND, RN, GETDATE())
FROM
(
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

我指定 SERIALIZABLE 隔離級別的原因是它允許我將查詢分成兩部分。我發現通過使用兩個查詢來編寫快速程式碼來解決這個問題要容易得多。在查詢執行期間，沒有其他程序能夠修改表中的數據。你需要決定這是否可以接受。這是計算百分位數的一種方法：

BEGIN TRANSACTION;
DECLARE @CNT BIGINT, @MIN BIGINT, @MAX BIGINT;

SELECT @CNT = COUNT_BIG(*), @MIN = MIN(ID1), @MAX = MAX(ID1)
FROM part_test WITH (TABLOCK, SERIALIZABLE);

SELECT ID1
FROM 
(
   SELECT TOP (9) ID1
   FROM
   (
       SELECT ID1, ROW_NUMBER() OVER (ORDER BY ID1) RN
       FROM part_test WITH (TABLOCK)
   ) q
   WHERE RN % (@CNT / 10) = (@CNT / 10) - 1 -- math may not be exactly right here, but it's just a proof of concept
   ORDER BY ID1
) q

UNION ALL SELECT @MIN

UNION ALL SELECT @MAX

ORDER BY ID1;

COMMIT TRANSACTION;

在我的 8 CPU 核心桌面上，該查詢在大約 200 毫秒內完成。重要的是要了解這種方法會進行排序。隨著表獲得更多行，性能會變得更差，特別是如果無法在記憶體中執行排序。隨著表大小變大，完全不同的策略可能會執行得更好。這就是我提到允許近似結果可能對您有所幫助的原因之一。

了解我使用的方法非常特定於 SQL Server 也很重要。如果這樣的東西能滿足您對 SQL Server 的需求，那就太好了，但其他數據庫平台的解決方案可能看起來與它有很大不同。

引用自：https://dba.stackexchange.com/questions/301907