主页 / dba / 问题 / 56168
Accepted
dotancohen
Asked: 2014-01-06 02:25:37 +0800 CST

选择每第 n 行，不要拉整个表

  • 772

根据关于允许 dba.SE 上的基本 SQL 问题的 Meta 讨论，我提出了一个我现在遇到的问题，对于 Stack Overflow 上的答案是不充分且幼稚的。我希望有比 SO 上提出的问题更好的解决方案（因为我目前在应用程序中面临这个问题），并且 dba.SE 似乎是找到更好答案的理想场所。

这是 Stack Overflow 上的原始问题：如何从 mysql 中选择每 n 行？

这是公认的答案：

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1

接受答案的关键问题是它需要将整个表拉到一个临时表中。因此，我已经在这个问题的标题中解决了这个问题。

还要考虑该表可能已删除行，因此仅对WHERE MOD主键进行测试的替代查询也不是一个好的解决方案。Id est，不能相信主键是连续的。

有没有更好的方法来表达一个查询，该查询将返回每第二、第十或任意第 n 行，它不需要将整个表拉入内存但也考虑已删除的行？

每第 n 行可以这样定义：

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

我的目标数据库是 MySQL 5.5，它运行在一个常见的 Debian 派生 Linux 发行版上。

编辑：回应托马斯的回答：

建议的解决方案不会产生预期的结果，见下文：

mysql> SELECT 
    ->     @i:=@i+1 AS iterator 
    ->     , t.name
    -> FROM 
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)
mysql

2 个回答

  1. Best Answer

    对于测试数据events

    id  txtcol
--  ------
 1  event0
 2  event1
 4  event2
 5  event3
 6  event4
 8  event5
 9  event6

    按升序检索主键值

    SELECT id FROM events ORDER BY id

    将其包装在查询中以分配从零开始的排名

    set @row:=-1;
SELECT @row:=@row+1 AS rownum, id 
FROM
    (
        SELECT id FROM events ORDER BY id
    ) AS sorted

    将其包装在查询中以选择第一行和此后每隔三行

    set @row:=-1;
SELECT id
FROM
    (
        SELECT @row:=@row+1 AS rownum, id 
        FROM
            (
                SELECT id FROM events ORDER BY id
            ) AS sorted
    ) as ranked
WHERE rownum % 3 = 0

    最后，将其包装在查询中以检索其他列

    set @row:=-1;
SELECT events.*
FROM
    events
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id 
                FROM
                    (
                        SELECT id FROM events ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 3 = 0
    ) AS subset
        ON subset.id = events.id

    返回

    id  txtcol
--  ------
 1  event0
 5  event3
 9  event6
    • 5

  2. 要随机抽样每 n 行，我会在键上使用哈希函数。

    例如，如果你想要第 10 行，你可以这样表达你的查询：

    SELECT foo, bar 
FROM MyTable 
WHERE CRC32(key) % 10 = 0

    假设您选择了具有良好传播的散列函数（如 CRC32），这应该防止key由于删除而出现的漏洞。

    严格来说，这不能保证准确地采样表的 1/n。但是，如果表足够大以至于您关心它的性能，我会假设该解决方案让您足够接近。

    重要的是要意识到关系数据库中没有“行号”这样的东西。表只是一组无序的元组。表上的索引可能会按特定顺序存储元组，但这仍然不会为数据添加任何形式的行编号。这意味着行号仅在存在 ORDER BY 子句时才有意义。

    换句话说，如果你的意思是你想要关于某种形式的表排序的第 N 行，那么你必须在 SELECT 中用 ORDER BY 来表达一些东西。例如，这将执行以下操作：

    SELECT 
    @i:=@i+1 AS iterator 
    , t.foo
    , t.bar
FROM 
    MySql AS t,
    (SELECT @i:=0) AS dummy
WHERE @i % 10 = 0
ORDER BY somecolumn

    如果somecolumn是主索引，则此查询应在没有排序或临时表的情况下运行。但是您仍然可以访问每一行以获得结果。

    • 1

相关问题