排序，掌握主键偏移与TABLESAMPLE)
假设业务方某天丢过来一句话从线上用户表里随机抽2万条数据做线下分析表大概10亿行明天要结果。你要是想都不想就写SELECT * FROM users ORDER BY RAND() LIMIT 20000那么接下来等着你的大概率是持续几十分钟的SQL、被占满的临时表空间以及DBA在群里毫不留情的质问。随机抽样2w行本身不难难的是面对10亿行这个量级时任何一次全表排序或者回表都要三思。这篇文章从我实际处理过的类似需求出发把大表随机抽样的几种主流SQL写法和它们背后的原理、代价、坑完整盘一遍。1. 动手前先问清楚四个问题10亿行随机抽样难在“需求”而不在“SQL”1.1 抽样比例2w对10亿意味着什么先算一笔比例账。2w在10亿里的占比是0.002%也就是每10万行里才抽2行。这么低的抽样率意味着两件事一是“逐行扫描逐行判断”的方案不是不能想但你得接受扫描全部10亿行这个代价。哪怕什么都不做光把这10亿行从磁盘读一遍在普通云数据库上也不是秒级的事情。二是任何依赖“先排序再取前N行”的逻辑都要警惕。数据库优化器不会因为看到LIMIT 20000就聪明到跳过排序它必须有一种方式确认哪些行会落在前2w里而随机排序恰恰是它最不擅长优化的场景。在写任何SQL之前我习惯先确认四个问题样本能不能重复抽样结果要不要精确等于2w字段分布有没有周期性比如按用户ID奇偶、按地区分区这次抽样是离线一次性任务还是线上频繁调用这四个答案直接决定方案选型前面没想清楚后面大概率要返工。1.2 随机性等级真随机、近似随机、“看着随机”很多业务方说“随机抽样”但他们心里要的随机性级别完全不同。严格等概率行级随机每一行被抽中的概率完全相等概率2e-5。适合A/B实验分组、训练集划分等对统计性质敏感的场景。块级随机页面/块等概率数据库按物理块或索引页为单位抽样命中某个块就把块内所有行都拿进来。速度极快但样本可能存在聚集效应比如同一个用户的多条成交记录被同时抽中。近似随机比如随机选一个主键起点往后连续取2w行。分布大体均匀但不是严格随机ID空洞和局部热点都会造成偏差。确定性伪随机用哈希函数把行映射到某个桶里取特定桶。可复现、可下推但不是概率随机严格说是“按指纹均匀抽取”。不要觉得“严格等概率”才叫抽样。在我的经验里80%的业务场景根本用不到严格等概率他们只需要一份“没有明显偏置”的样本。问清随机性等级能帮你省下好几个数量级的执行时间。1.3 物理约束扫描、排序、临时文件的量级10亿行表默认就是一尊大佛。假设单行平均200字节表体量就是200GB左右假设一行更宽500GB也不奇怪。InnoDB一页16KB200GB光数据页就有约1300万页。全表顺序扫描就算磁盘能跑到每秒1GB也得200秒以上这还不算事务、锁、Buffer Pool命中率的影响。排序则更狠。对10亿行做外部排序临时文件往往能吃掉几十GB甚至上百GB的磁盘空间产生的随机IO在普通云盘上能直接把实例拖垮。所以物理约束可以浓缩成一句话10亿行级别对全表排序说“不”对全表扫描说“谨慎可以”但要有心理准备对走主键索引的点查和范围查说“欢迎”。2. 拆穿 ORDER BY RAND()为什么它是大表上的“慢性自杀”2.1 一条SQL背后的完整流程SELECT * FROM users ORDER BY RAND() LIMIT 20000这条SQL看起来人畜无害但它的执行路径是全表扫描所有10亿行 - 对每一行调用RAND()生成随机值 - 按随机值排序 - 从排序结果里取前20000行。关键点在于数据库不知道哪2w行会排在前面所以它必须给全部10亿行都生成一个随机值并且让这些随机值参与排序哪怕最终只要前2w。你写的是“只要2w”数据库执行的是“给10亿行全部洗牌然后只发前2w张牌”。优化器并不是完全没有微调手段。比如某些数据库支持Top-N堆排序也就是维护一个大小为N的最大堆每来一行就和堆顶比一比比堆顶优秀的就替换进去。这样复杂度可以从O(n log n)降为O(n log N)。但就算降下来了你仍然逃不掉两件事全表扫描所有行、给所有行生成随机数。随机数生成本身不贵但10亿次调用加上堆的比较交换耗时也是分钟级起步而且堆排序的内存占用在超大N下依然可能落盘。更麻烦的是MySQL的filesort实现。如果排序数据超过sort_buffer_sizeMySQL会把中间结果写到临时文件做归并排序。对10亿行来说这个中间结果集会非常庞大最终产生的临时文件读写量往往比原表还大。2.2 用数学算一笔账10亿行排序到底要多久假设有一种理想情况排序过程中每行只比较一次键值10亿次比较每次比较100纳秒这已经是非常乐观的估算那就是100秒。但真实排序不存在只比较一次外部归并排序需要多次读写临时文件每次读写都是IO层面的成本。我用线上实际遇到的一个案例来佐证一张3000万行的订单表字段不算宽ORDER BY RAND() LIMIT 100在普通8核云数据库上跑了大约90秒。行数翻到1亿同样写法直接奔着10分钟去了。按O(n log n)粗略推导10亿行的耗时轻松突破2小时。这还没算临时盘空间占满导致整个实例IO抖动的情况。所以我的判断标准很简单任何参与RAND()排序的行数超过百万级基本就不该考虑这条路了。你抽的是2w行没错但数据库为此处理的是10亿行。2.3 小表例外什么时候 ORDER BY RAND() 又可以用说了一堆坏话但这个写法也不是一无是处。如果参与排序的结果集很小比如经过WHERE过滤后只剩几千行、几万行ORDER BY RAND() LIMIT n就是最直接、最可读的方案性能也没问题。举个例子抽奖系统里要从当天活跃用户里抽100个中奖者当天活跃用户可能就5万过滤后直接ORDER BY RAND() LIMIT 100毫秒级返回完全不需要折腾其他方案。另一个实用技巧如果你要在一个大表里先过滤再抽样尽量把过滤条件设计得尽量窄。先缩小参与排序的数据集再想办法随机。我遇到很多性能事故本质都是过滤条件没写全导致本应只sort几万行的操作被放大成了数亿行。3. 主键 ID 偏移法大表抽样最常用的杀招3.1 连续自增ID随机ID列表按主键回表如果表有自增主键ID且ID接近连续那么最靠谱的思路是先随机生成2w个主键ID再用这些ID去主表里精确取行。主键点查走B树每次查询是O(log n)2w次点查在绝大多数数据库上都是秒级到分钟级完全可控。先取ID边界SELECT MIN(id), MAX(id) FROM users;这里注意MIN和MAX都能走索引端点几乎瞬时返回。但千万别随手加一个SELECT COUNT(*)InnoDB的COUNT(*)要扫全索引10亿行下会突然多出一块几十秒到几分钟的开销。除非你确实需要精确行数做配额计算否则用max-min1估算就行。拿到边界后生成2w个随机偏移量。MySQL里可以利用递归CTE生成序列SET SESSION cte_max_recursion_depth 22000; WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 22000 ), bounds AS ( SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM users ), rand_ids AS ( SELECT FLOOR(RAND() * (bounds.max_id - bounds.min_id 1)) bounds.min_id AS id FROM seq CROSS JOIN bounds ) SELECT u.* FROM users u JOIN rand_ids r ON u.id r.id LIMIT 20000;这里有个新手很容易踩的坑MySQL递归CTE默认迭代上限是1000次直接递归到22000会报错。必须先SET SESSION cte_max_recursion_depth 22000;否则这条SQL会莫名奇妙地失败而且报错信息不太直观。为什么要生成22000个ID而不是正好20000因为随机生成的ID之间可能重复而且如果有删除空洞JOIN会匹配不到最终返回行数会少于2w。多生成一批JOIN后加LIMIT能有效提高拿满2w行的概率。如果表空洞特别严重可以生成2倍甚至3倍的随机ID再JOIN。3.2 有空洞的 ID三种兜底策略现实中的自增ID几乎必然有空洞事务回滚、手动删除、批量导入失败都会让ID不连续。空洞不严重时上面的多生成JOIN策略够用。空洞严重时有几个替代方案方案A随机起点连续区间抽样。先在ID范围里随机选一个起点然后从该起点往后按主键顺序取2w行SELECT * FROM users WHERE id ( SELECT FLOOR(RAND() * ((SELECT MAX(id) FROM users) - (SELECT MIN(id) FROM users))) (SELECT MIN(id) FROM users) ) ORDER BY id LIMIT 20000;这个方案的本质是“块级近似随机”速度极快走主键范围扫描只需要读取2w行左右的数据。但样本不是全表等概率而是“随机起点之后的一段连续区间”。业务上如果只是看看数据长什么样这个方案是性价比之王。方案B行号映射。对有主键的表理论做法是给所有行编一个连续行号然后随机抽行号。但在10亿行上ROW_NUMBER() OVER (ORDER BY id)要先扫全表并排序代价太大。这个方案只适合几千万行以下的中型表。方案C分层配额抽样。如果表本身有分区或者可以按业务字段如user_id hash分桶可以按桶行数比例分配样本名额在每个桶内用随机起点法抽样最后UNION汇总。这样每桶内部是近似随机桶与桶之间按比例配额整体分布比单点随机起点法好很多。3.3 让抽样SQL走索引的写法细节同样是ID偏移法写法不同执行计划天差地别。最容易犯的错误是把随机数生成直接塞进WHERE条件-- 错误示范RAND() 会在每行判断时被调用结果难以预测 SELECT * FROM users WHERE id FLOOR(RAND() * 1000000000);这种写法的问题是优化器可能把这个条件当作“非常量表达式”直接放弃索引走全表扫描。正确做法是把随机ID生成放到派生表或CTE里让ID只生成一次再和主表JOIN。写完SQL后一定要看执行计划。typerange或typeconst是正常的如果出现typeALL就说明优化器选择了全表扫得回来看是不是条件里掺杂了易变函数。还有一个细节如果主键是InnoDB的聚簇索引按ID范围段取数据非常快如果主键是普通索引比如某些分布式中间件下的表取回主键ID后还要回表拿整行数据2w次回表在网络和IO上会有额外开销。这时候可以考虑先用覆盖索引把ID取出来再按需批量回表。3.4 主键法不适用的场景UUID主键、雪花ID、联合主键、无主键表这四种场景下ID偏移法的效果会打折扣。UUID主键不是单调有序的MIN和MAX的范围跨度没有业务意义随机偏移会大量落空。雪花ID虽然趋势递增但它的分布和插入顺序强相关随机偏移取出来的数据在实际分布上会有偏差。无主键或没有可靠唯一约束的表最麻烦ID偏移法完全失效。这时候要么接受全表扫描蓄水池算法要么找其他有索引的列做分段要么直接上块级采样。我在后面章节会讲无主键情况下的选择。4. 蓄水池抽样不依赖主键也能精确等概率4.1 算法原理一次遍历、逐行替换、概率证明蓄水池算法是数据流和超大文件随机抽样领域的老牌选手。它的核心逻辑是维护一个容量为k的“池子”遍历数据流前k行直接进池从第k1行开始第i行以k/i的概率被选中被选中后随机替换掉池中的一行。遍历结束时池中的k行就是整个数据集的随机样本。为什么它能保证等概率直觉解释就是“后来的行虽然概率变小但前面行的概率也会被后续行稀释最终每个位置的概率都收敛到k/n”。给一个简单的证明思路对第i行来说它进入池的概率是k/i对它后面的每一行j这一行不会被踢出去的概率是(1 - k/j)加上被选中但没替换到它的概率最终约分下来第i行留在池中的概率恰好是k/n。这个算法最大的优点是不需要知道总行数n不需要排序不需要主键只需要一次顺序遍历。这在“从一个无法预知总量的流里抽样”的场景里几乎是唯一解。生活化类比就是你有一个只能装k个球的小盒子面前有一条望不到头的传送带每个球经过时你都犹豫一下要不要把它放进盒子如果盒子满了就拿出一个旧球。最终盒子里留下哪些球等价于从整条传送带上做了一次等概率抽取。4.2 在工程中怎么落地外部程序流式读取蓄水池算法在纯SQL里很难优雅表达因为它依赖“动态概率替换”这个状态。我的做法一般是写一个外部脚本用数据库游标一行一行读同时维护池子。Node/Java/Python都行核心代码大概长这样以Python为例import random import psycopg2 conn psycopg2.connect(...) cur conn.cursor(sample_cursor) # 服务端游标避免一次性拉10亿行进内存 cur.execute(SELECT * FROM users) k 20000 pool [] for i, row in enumerate(cur, start1): if i k: pool.append(row) else: j random.randint(1, i) if j k: pool[j - 1] row for row in pool: print(row)注意一定要用服务端游标或游标式读取不要cur.execute之后直接fetchall。10亿行一次性塞进客户端内存机器直接OOM。如果表在数据库里而数据本身不在流上也可以用分页游标模拟流式读取但分页在大偏移量下有性能衰减。更稳妥的方式是直接读取导出的数据文件或者用数据库提供的流式导出接口。4.3 蓄水池 vs 主键ID法的取舍蓄水池算法的随机质量是最高的理论上有严格证明不依赖任何数据分布假设。但它的代价也很明确必须读完整张表10亿行扫描跑不掉。对比一下主键ID法返回快、走索引、资源消耗小但随机质量依赖ID连续性和实际物理分布。蓄水池法随机质量上限高、不依赖主键但整体耗时基本等同于一次全表扫描。我的选择原则是如果表有可靠的自增主键且ID空洞可控优先主键ID法如果表结构混乱、没有可用主键或者业务明确要求严格等概率那就老老实实跑蓄水池把任务放到低峰期或离线数仓执行。抽样本身是数学问题工程落地才见真章。5. 数据库方言实战MySQL/PG/SQL Server/Oracle 的推荐写法5.1 MySQL没有TABLESAMPLE老老实实ID偏移MySQL到8.0依然没有TABLESAMPLE语法原生供你使用的随机手段只有RAND()排序和主键偏移两条路。大表上就别指望RAND()排序了直接走ID偏移方案我前文给出的递归CTE脚本就是可用版本。如果ID空洞偏大还有一个变体先按主键范围分片每个分片用随机起点法取一小段最后UNION ALL汇总。比如把ID范围分成20个区间每个区间随机起点取1000行合起来就是近似2w。这种“撒胡椒面”式抽样在MySQL上重点解决了一个问题单条SQL扫描的范围被严格控制住了避免了随机起点恰好在某个ID高度稀疏区域时读很多行的情况。MySQL还有一个不高大上但很实用的技巧SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * max_id) FROM dual) ORDER BY id LIMIT 20000。它和3.2里的方案思路一致只是写法更简单。适合对随机性要求不高的临时取数。5.2 PostgreSQLTABLESAMPLE 的两种模式怎么选PostgreSQL 9.5起支持TABLESAMPLE这是大表抽样最好用的语法糖。核心语法SELECT * FROM users TABLESAMPLE BERNOULLI (0.002); SELECT * FROM users TABLESAMPLE SYSTEM (0.002);参数是百分比不是行数。0.002%的百分比在10亿行上期望返回约2w行10亿 × 0.002 / 100 2w。两种模式差别很大BERNOULLI行级采样。对每一行独立判断是否选中随机质量接近严格等概率性能上它依然要扫描绝大多数页面在10亿行上不算快。SYSTEM块级采样。以数据页为单位决定是否选中选中就取整个页面的所有行。它只读少量页面速度可能快一到两个数量级但样本有聚集性同页数据往往是物理上相邻、插入时间接近的数据会一起出现。两者都可以加REPEATABLE(seed)参数相同种子下重复执行会得到完全相同的结果。这个特性在做“可复现抽样”场景下非常有用——比如QA想针对同一批样本反复验证问题用REPEATABLE(42)就能保证每次抽到同一批数据。我的建议是对随机质量有要求的场景用BERNOULLI但不加LIMIT先接受“返回行数在1.8w到2.2w之间波动”然后看业务能不能接受对性能有硬指标的场景用SYSTEM但要明确告诉业务方样本存在块级聚集效应。5.3 SQL ServerTABLESAMPLE 是页级采样别当随机用SQL Server也有TABLESAMPLE语法SELECT * FROM users TABLESAMPLE (20000 ROWS); SELECT * FROM users TABLESAMPLE (0.002 PERCENT);但文档里写得很明白SQL Server的TABLESAMPLE是基于物理页的采样不是行级随机。命中的页面里所有行都会被返回而且由于页的物理分布和数据插入顺序强相关抽样结果可能出现明显的“分段聚集”。如果你拿它当严格随机用后面做分布对比测试时大概率会翻车。SQL Server里经常被新手使用的方法是SELECT TOP 20000 * FROM users ORDER BY NEWID()。NEWID()会给每一行生成一个唯一GUID然后按GUID排序取前2w。这个方法在小表上可用但在大表上等价于全表扫描全部排序性能和ORDER BY RAND()一个档次。所以SQL Server上的推荐方案还是主键偏移法写法参考DECLARE min_id BIGINT (SELECT MIN(id) FROM users); DECLARE max_id BIGINT (SELECT MAX(id) FROM users); DECLARE start_id BIGINT min_id CAST((max_id - min_id 1) * RAND() AS BIGINT); SELECT TOP (20000) * FROM users WHERE id start_id ORDER BY id;如果你需要在SQL Server上做更接近行级的随机抽样又不想全表排序那基本只能靠程序端蓄水池或分片随机起点法。5.4 OracleSAMPLE 子句与排序法的组合Oracle自带SAMPLE子句用法上是四种数据库里最接近“天然支持”的SELECT * FROM users SAMPLE (0.002); SELECT * FROM users SAMPLE BLOCK (0.002);不带BLOCK的SAMPLE是行级采样Oracle会在扫描路径上按0.002%的概率选行带BLOCK的SAMPLE是块级采样速度快但有聚集性。两者同样支持SEED(42)固定随机种子可复现。提个容易踩的坑SAMPLE子句和查询执行路径有耦合。如果Oracle优化器决定走某个二级索引而不是全表扫描SAMPLE可能不会按你预期的比例生效甚至可能被忽略。所以用SAMPLE之前务必用执行计划确认它确实被应用了。Oracle里不推荐用ORDER BY DBMS_RANDOM.VALUE排序抽样虽然它是Oracle社区里流传很久的旧做法但和MySQL的RAND()排序一样全表排序的代价摆在那里。10亿行就别碰了。正确的组合是先用SAMPLE把扫描范围降下来再在外层做DBMS_RANDOM排序取数SELECT * FROM ( SELECT * FROM users SAMPLE (0.05) ORDER BY DBMS_RANDOM.VALUE ) WHERE ROWNUM 20000;这里SAMPLE(0.05)的意思是大致抽0.05%的行也就是约50万行然后在这50万行里做严格随机排序取2w。既避免了10亿行全排序又拿到了比纯块级采样更“随机”的效果是一个不错的中间态方案。5.5 四种数据库方案对比数据库推荐方案随机级别扫描量级注意事项MySQL主键ID偏移 JOIN随机ID近似随机仅2w左右点查没有TABLESAMPLE递归CTE需调cte_max_recursion_depthPostgreSQLTABLESAMPLE BERNOULLI / SYSTEMBERNOULLI接近行级SYSTEM块级BERNOULLI扫全部页SYSTEM少量页REPEATABLE(seed)可复现SYSTEM有聚集效应SQL Server主键偏移 / 分片起点法近似随机少量范围扫描TABLESAMPLE是页级采样不能当严格行级随机用OracleSAMPLE(行级) / SAMPLE BLOCK(块级)行级近似 / 块级近似行级扫全部块级少量SAMPLE可能被优化器忽略需要执行计划确认6. 抽样质量的验证和工程落地不能只看SQL跑没跑完6.1 怎么判断抽出来的2w行“够随机”SQL跑完了不代表任务完成了。抽样质量不过关下游分析出来的结论全是错的。我每次抽样完都会先做三个快速验证。第一个是数量验证确定返回行数是否真的达到2w。ID偏移法因为空洞容易少行TABLESAMPLE系列因为概率波动容易多行或少行。数量不对先解决数量问题。第二个是分布对比。挑两三个关键字段比如用户表就挑地区、注册年份、活跃度分桶对比样本比例和全表比例。比例偏差在几个百分点内一般可接受如果某个分组在2w样本里完全缺失就要怀疑是不是ID空洞或者块级采样造成了系统性偏置。第三个是重复率检查。如果业务不允许重复就用主键去重后看还剩多少行。如果业务允许重复但抽样结果里大量重复那说明抽样方法本身有严重问题——比如随机起点落在一个热点ID区间导致大量行来自同一个局部。一个更高级的检查手段是用同一套SQL配不同随机种子抽5次看样本集合的重叠程度。理论上来讲5次独立抽样之间重叠不应该过高如果两次抽出的样本高度相似说明随机源头被卡在了某个局部区域需要立刻排查。6.2 提速三板斧分片并行、结果落表、从库执行第一板斧是分片并行。把10亿行按主键ID范围拆成20个区间每个区间单独用随机起点法或ID偏移法抽1000行然后并发执行。单条SQL的扫描范围一下子缩到了原来的1/20总耗时基本被压到分钟级以内。需要注意的是分片配额最好按各区间实际行数比例分配否则行数多的区间配额少了会造成比例偏置。第二板斧是结果落表。抽样结果直接INSERT INTO一张sample_result表下游所有分析都从这张表取数。别让每条下游SQL都去现场抽样一是费时二是每次抽样结果都不一样会导致上下游对不上数。落表之后整个流程就变成定时抽样更新一次 - 下游稳定消费。第三板斧是从库执行。10亿行的任何抽样SQL都不该在生产主库上跑。哪怕你自认为SQL写得再高效2w次主键点查在高峰期也会给主库增加不必要的负担。放到只读从库或者离线数仓上执行这是最基本的数据库纪律。6.3 不同用途下的方案选择同样是“从10亿行抽2w行”用途不同最优方案完全不同。数据质量抽检目标是快速定位脏数据对随机性要求不高。建议直接用块级采样PG的SYSTEM、Oracle的SAMPLE BLOCK或随机起点法速度最快能覆盖到各个物理区域就行。A/B实验分组需要确保分到两组的用户没有系统性差异。这时候必须行级随机优先蓄水池算法或BERNOULLI(0.002)如果抽到的样本有聚集性实验结论很容易被人质疑。训练集/测试集划分除了随机性还要保证类别的覆盖度。这个场景我更推荐确定性哈希分桶按user_id的哈希值取模把用户分成若干桶取某几个桶做验证集。虽然它不是“随机”但它在覆盖度和可复现性上都比纯随机好而且写起来非常简单SELECT * FROM users WHERE MOD(CAST(CRC32(user_id) AS UNSIGNED), 100) IN (0, 1, 2, 3);固定哈希函数保证了同一批用户每次抽取都会进入同一个分组这对模型评估很重要——训练集和验证集永远不会互相污染。6.4 我在线上环境最终采用的实践方案最后分享一个真实案例。我曾经处理过一张8亿行的用户行为表主键是自增ID但有大概2%的空洞业务方要求抽2w行做指标估算。我第一版方案直接用了递归CTE生成随机ID然后JOIN主表跑出来的结果是主键点查本身没问题但因为空洞第一次JOIN只返回了约1.96w行比目标少了400。补跑了两次多生成20%随机ID的版本才稳定拿满2w。后来我把方案升级成“按ID范围分片片内随机起点”每片抽1000行20片并发。单条SQL的执行时间从原来的60秒降到5秒以内整体流程也在几分钟内结束。分片之后样本在ID空间上的覆盖比纯随机点取更均匀业务方肉眼抽查数据时反而觉得“更随机”。这个案例给我的经验是在大数据量抽样这个场景里理论上的“严格等概率”往往不是第一诉求。更重要的其实是三个词可控、够快、无明显偏置。把这些想明白了再回头看各种SQL写法心里就有底了。最后再分享一个小技巧抽样SQL跑完之后把执行计划、抽样种子、随机起点都记录下来连同结果表一起存起来。这样将来有人问“这批样本当时是怎么抽的”你能拿出全套证据链。我在实际项目里靠这个习惯避免过不止一次“样本来源扯皮”的事。10亿行随机抽样从来不是一条SQL的事它是一条从需求梳理、方案选型、SQL执行到质量验证的完整链路每一环都值得认真对待。
