主页 / dba / 问题 / 301907
Accepted
Pablo
Asked: 2021-10-30 15:42:14 +0800 CST

为索引列高效生成分位数

  • 772

我正在尝试为分布式处理系统构建查询分区器。该系统可以与支持 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. 有没有其他方法可以使用分区列为给定表找到类似大小的范围？
sql-standard index

2 个回答

  1. Best Answer

    我有坏消息要告诉你。你想做的事情将比你预期的要困难得多。

    大多数数据库都实现了 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 的需求，那就太好了，但其他数据库平台的解决方案可能看起来与它有很大的不同。

    • 3

  2. 尚不清楚为什么要以这种方式对数据进行分区。似乎您只是“为了并行化查询”而这样做，但大多数 DBMS 都有更好的并行化查询方法。

    无论哪种方式，如果您可以将代码自定义到特定的 DBMS，您就可以非常有效地查询直方图数据，而不是对巨大的表进行大规模扫描。

    例如，在 SQL Server 中，您可以执行以下操作：

    SELECT
  range_high_key,
  Centile = RunningRows * 100.0 / TotalRows
FROM (
    SELECT 
      RunningRows = SUM(range_rows) OVER (ORDER BY CAST(range_high_key AS int) ROWS UNBOUNDED PRECEDING),
      TotalRows = SUM(range_rows) OVER (),
      range_high_key = CAST(range_high_key AS int)
    FROM sys.tables t
    JOIN sys.stats s ON s.object_id = t.object_id
    CROSS APPLY sys.dm_db_stats_histogram(t.object_id, 1) h
    WHERE t.name = 't'
) h;

    db<>小提琴

    • 1

相关问题