主页 / dba / 问题 / 82715
Accepted
beeks
Asked: 2014-11-15 20:23:36 +0800 CST

通过排序不佳的表格进行分页的最具成本效益的方法？

  • 772

我有一个包含三列的表：HashUID1、HashUID2、Address_Name（这是一个文本电子邮件地址，前两个哈希列是一些疯狂的创建，用于将事件参与者表链接到电子邮件地址。它很丑，几乎没有用我的控制。关注 address_name 索引）

它有 7800 万行。排序不正确。无论如何，该索引被拆分到许多快速 LUN 上并执行非常快速的索引查找。

我需要创建一系列查询以一次仅提取 20,000 个“每页行”，但要避免冲突或重复。由于没有标识列或易于排序的列，是否有一种简单的方法来选择所有内容并翻页？

我说的是否正确，如果我从 hugetablewithemails 中执行 select * from hugetablewithemails 到临时表中，然后通过 row_number 选择该表在事务期间保留在内存中，这对我来说是过多的内存资源？这似乎是首选的分页方法。我宁愿按统计百分比分页。:(

有一个索引按顺序维护 address_name 电子邮件地址，并且维护良好。在过去的一周里，我一直想通过花一些时间来研究构建一个基于窗口函数吐出范围的过程来帮助其他开发人员，该函数基于统计数据（我不擅长，但这个查询真的让我感兴趣）到提供从 1 到（变量）LEFT LIKE 字符的索引范围，满足 20,000 行——但我什至没有时间开始查询……

几个问题：

  1. 有什么建议么？不是在寻找实际代码，只是根据经验寻找一些提示或建议，也许是警告。我想避免在初始扫描后进行额外的索引扫描。

  2. 这是正确的方法吗？

  3. 我正在考虑打破所有电子邮件地址的索引总和，收集行数 (*)、/20,000，并将其用作窗口函数，根据总行数的百分比对最小/最大子字符串 (1,5) 值进行分组建立分组范围。想法？

这是针对无法修改源数据库的 ETL 过程。

我希望通过一次完整的索引扫描我可以做到：

  • 查询以根据索引使用情况（按字母顺序排序）获取直方图，并使用 min/max 将其分解（窗口化）以创建这样的范围，以便轻松查找所需的索引：

  • A-> AAAX，（例如 20k 行）AAA-Z，B->（另外 20k），B->BAAR -> BAAR-> CDEFG -> CDEFH > FAAH，等等。

我们为此 ETL 过程在这些数据库中运行读取提交。我们只是试图将它分批处理成 20k 行，因为 DBA 说我们通过抓取完整的表使用了太多的网络资源。如果数据发生变化（这是一个问题），我们会即时更新 DW 和暂存表。

我很想使用临时表，但如果我这样做了，我会溢出到 tempdb 并通过电子邮件从 DBA 那里得到关于它的抨击，而且数据库太大了。

sql-server pagination

3 个回答

  1. Best Answer

    本质上，您是在询问是否可以对整个数据执行单个有序扫描，同时不制作数据副本，并在每次调用时从完整集中返回“x”个不相交的行集。这正是适当配置的 API 游标的行为。

    例如，使用 AdventureWorks 表Person.EmailAddress返回 1,000 行的集合：

    DECLARE 
    @cur integer,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt integer = 16 | 8192 | 16384,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536, 
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT *
    FROM AdventureWorks2012.Person.EmailAddress
        WITH (INDEX([IX_EmailAddress_EmailAddress]))
    ORDER BY EmailAddress;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur, 
        2, 
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

    每个提取操作最多返回 1,000 行，并记住上次调用的扫描位置。

    • 15

  2. 如果不知道开窗背后的目的，就很难具体说明。考虑到您一次要查看两万行，我猜这是一个批处理过程，而不是供人查看。

    如果电子邮件地址上有索引，则会对其进行排序。索引是 BTree，它们在内部维护一个顺序。这将是该列排序规则的排序顺序（可能但不一定是数据库的默认排序规则）。

    临时表——#table 和@table——将存在于 tempdb 中。大的结果集也会溢出内存到 tempdb。

    如果您所说的“统计数据”是指 SQL Server 的内部统计数据，它在索引上或通过create statistics..语句维护，那么我认为这不会发生。这些统计数据只有几百个桶（刚才忘记了正确的限制），因为您需要 39,000 个“窗口”才能阅读完整的表格。如果您打算通过触发器维护自己的行到窗口映射，这是可以实现的，但开销可能很大。

    翻阅大型数据集的传统方法是记住每组中最大的键值并从那里开始读取。如果电子邮件地址列不是唯一的，即一个地址可以多次出现，您有几个选择。A) 在应用程序中逐行处理每个批次并跳过重复项或 b) 在 SQL 中过滤掉它们。“B”将需要排序，但如果按键顺序读取数据，则可以优化这种排序：

    declare @MaxKey varchar(255) = '';  -- email size

while exists (select 1 from mytable where address_name > @MyKey)
begin
    ;with NewBatch as
    (
    select top 20000  -- whatever size a "window" must be
        address_name
    from mytable
    where address_name > @MaxKey
    order by address_name
    )
    select distinct
        address_name
    from NewBatch;

    --process and then
    select @MaxKey = max(address_name) -- from this batch of rows
end

    迭代可能发生在 SQL 或您的应用程序中，具体取决于您的架构。

    如果需要许多列，而不仅仅是电子邮件地址，您可以考虑使用定义了 KEYSET 或 STATIC 关键字的游标。但是，这仍将使用 tempdb 中的资源。

    向后退一步，SSIS 专门设计用于高效处理大型行集。定义一个满足您要求的包可能是最好的长期答案。

    • 2

  3. 如果您只是关心在存在 DML 的情况下随着时间的推移排序顺序的稳定性，请考虑使用快照隔离来查询表。SNAPSHOT在完成提取页面之前，您可以让事务保持打开状态。这具有与快照隔离相关的常见缺点。

    也就是说，此技术将需要为您提取的每个页面对整个表格进行排序。那真的很昂贵（二次渐近性能）。

    考虑使用带有IDENTITY主键的临时表。这样您就可以通过范围搜索轻松提取页面。

    临时表未固定到内存中。这是一个普遍的误解。

    对于 78m 行（每 100 字节 => 7.8GB 磁盘空间），该技术应该可以正常工作。

    请注意，例如，从原始表中提取数据READ COMMITTED可能会给您一个在任何时间点都不存在的数据集（由于并发 DML）。SNAPSHOT如果可以，请使用隔离。

    您可以在自己的数据库中或在单独的 SIMPLE 模式下创建临时表，而不是备份数据库。另请注意，对整个表进行排序将暂时使用尽可能多的 tempdb 空间来存储您需要的所有列。所以也许你需要从已经存在的（唯一的）索引中导出行号（并应用大小缩减技巧）。

    另一个想法：不是将所有行缓冲到临时表，而是只写每行的键。您表示在它们的主表中查找会很快。

    或者，您只写每 20,000 行，这样您就知道从哪里开始每个分页查询。提取页面将不会按行号工作，而是使用SELECT TOP 20000 ... WHERE SomeKey >= PageStartKey ORDER BY SomeKey.

    • 1

相关问题