V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
kisshere
V2EX  ›  程序员

一亿行的 MySQL 表,主键 ID 不连续,怎样随机取 30 行速度最快?

  •  
  •   kisshere · 60 天前 · 4230 次点击
    这是一个创建于 60 天前的主题,其中的信息可能已经有所发展或是发生改变。

    ORDER BY rand()速度太慢,主键又不连续(类似于 1,6,7,33,78,93 这种递增),要不然都可以用 php 生成 30 个随机数 id 去 query 了

    46 条回复    2021-02-20 23:08:56 +08:00
    huabalance
        1
    huabalance   60 天前 via Android
    不断 rand 直到取慢三十个 可能还要去重
    huabalance
        2
    huabalance   60 天前 via Android
    我是说 php 生成随机数。。。
    ahhui
        3
    ahhui   60 天前
    随机生成 30 个数字,WHERE ID >= 你的某个随机数字 LIMIT 1,执行 30 次查询,这样呢?
    honeycomb
        4
    honeycomb   60 天前 via Android
    另外建一个索引表,从那个表里取随机值?
    leeyuzhe
        5
    leeyuzhe   60 天前
    @ahhui 要去重啊,跟等于实际上是一样的,不断去取然后去重直到取满
    caola
        6
    caola   60 天前
    ```
    SELECT * FROM XXX
    WHERE id >= ((SELECT MAX(id) FROM XXX)-(SELECT MIN(id) FROM XXX)) * RAND() + (SELECT MIN(id) FROM XXX)
    limit 30;
    ```

    在网上抄来的
    Rocketer
        7
    Rocketer   60 天前 via iPhone
    @caola 你这不是随机 30 个,而是自然排序取连续 30 个,只是起点随机而已
    codingadog
        8
    codingadog   60 天前 via Android
    我觉得只要 sql 里有 rand 就会慢。
    还是维护个 set,保存生成的随机数,然后生成满 30 个随机数每次取一个最快。
    bthulu
        9
    bthulu   60 天前
    每次随机三十个数, 数字间大小至少相差十万万, 比如[5001, 235555, 1053052, ...], 然后查询 id>5001 limit 1 union id>23555 limit1 union id>1053052 limit1 ...
    dafsic
        10
    dafsic   60 天前   ❤️ 1
    随机生成 100 个数,然后 query,结果中大概率会有 30 个
    caola
        11
    caola   60 天前   ❤️ 1
    @Rocketer #7 如果一定要很随机的话,可以把所有的 ID 保存到 redis 并维护这个 ID 列表, 然后 srandmember 命令去取
    rahuahua
        12
    rahuahua   60 天前
    @leeyuzhe 这个方式去重不是问题,问题是要读取三十次数据库
    siweipancc
        13
    siweipancc   60 天前 via iPhone
    不上 redis 还有个折衷的方法,额外生成一列 hash,生成索引,取 30 行列值>=随机生成的 hash 的就行了
    shyling
        14
    shyling   60 天前
    增加一个存随机数的列 a 加 index,查询的时候再生成一个随机数 b,找 a 在 b 附近的 30 条
    Amayadream
        15
    Amayadream   60 天前
    一般这种不符合常理的需求都不是原始需求,可以说一下你的原始需求是什么。
    aeli
        16
    aeli   60 天前
    取最大最小 id,然后计算出差值,再在这个差值中,取 60 个随机数后排序,select * from id in( ( a1> and < a2 )or( a3 > < a4) ....)... 大概的思路吧,取两个值之间的一个。
    lovecy
        17
    lovecy   60 天前
    假设只有 100 条数据
    ```sql
    SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n1
    UNION
    SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n2
    UNION
    SELECT * FROM (SELECT * FROM xxx WHERE id > FLOOR(RAND()*100) LIMIT 1) AS n3
    #写 30 遍,查出来有重复的再查一次
    ```
    FLOOR(RAND()*100),这一步可以先在代码里算出来,整个方法前提是要知道 id 的最大范围
    laminux29
        18
    laminux29   60 天前
    这种问题明显应该用空间去换时间,比如在数据录入时,就概率性地取出某些行,直接作为最终数据。
    xxxyh
        19
    xxxyh   60 天前
    order by rand 全表扫描,还排序,如果对随机性要求不是很高的话,可以在取最大和最小 id,在应用层随机取 30 个 id,然后 select * from table where id > (随机出来的 id) limit 1,重复 30 次
    xxxyh
        20
    xxxyh   60 天前
    如果一定要非常精确的随机的话,可以 select count(*) from table,然后在应用层随机出来 30 个 id,把这 30 个 id 排序,从最小的 [email protected] 开始,select * from table limit [email protected],1,从第二条开始 select * from table limit ([email protected] - [email protected]),1
    xxxyh
        21
    xxxyh   60 天前
    如果一定要非常精确的随机的话,可以 select count(*) from table,然后在应用层随机出来 30 个 id,把这 30 个 id 排序,从最小的 [email protected] 开始,select * from table limit [email protected],1,从第二条开始 select * from table where id > (第一次取出来的 id) limit ([email protected] - [email protected]),1
    eastphoton
        22
    eastphoton   60 天前
    既然主键 ID 没有更多规律,总得查一下或者干脆拿到所有 ID 才能得知具体某个 ID 存不存在这个信息了。。

    所有 ID 单独维护一份感觉比较合适,上 redis 或者自己维护个 set 。

    要么就用一些不那么随机的方案。
    hannibalm
        23
    hannibalm   60 天前
    再建一个临时表,一列现有主键,一列从 1 开始的序号。然后随机抽 30 个。
    GTim
        24
    GTim   60 天前
    @dafsic 这个目前是最优,不过 2 倍左右就可以了,如果不够再随机 2 倍去取
    bertonzh
        25
    bertonzh   60 天前
    先加一个自增字段进去...
    zlowly
        26
    zlowly   60 天前
    mariadb.com 的 Knowledge Base 知识库上有一篇专门讲这个问题的文章,里面提到一些较快但没那么随机的方法,你可以搜索来参考下
    data-sampling-techniques-for-efficiently-finding-a-random-row
    stevenkang
        27
    stevenkang   60 天前
    数据库不是用来存取数据的吗,用来做这些复杂的活干嘛。

    直接程序随机生成 ID 呗,如 #3 所说取满 30 个就行了。
    yixiugegegege
        28
    yixiugegegege   60 天前
    SELECT *
    FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
    WHERE t1.id >= t2.id
    ORDER BY t1.id LIMIT 30;
    Still4
        29
    Still4   60 天前
    我觉得直接说业务吧,啥需求要一亿行里面去随机啊,上面说加自增字段也好,加表也好都是空间换时间,根源上如果需求可以变通的话根本就不需要这么做
    ivanMeng
        30
    ivanMeng   60 天前
    布隆过滤器吧,可以快速判断 key 是否存在,id 乱序的 mysql 聚族索引也没发挥出,又是随机的记录地址完全不相邻,更加缓慢,还有这问题本身就是问题 。1 亿的表 是否有读写分离?上层是否有缓存冷热数据分离?
    mosliu
        31
    mosliu   60 天前
    看你的数据 id 有多稀疏
    不是太稀疏的话
    先获取 3*稀疏倍数*2 个随机数 记为 idlist1
    然后按 id in idlist1 limit 30 取 架设取出 n 个
    然后不够的 再按缺少数量 取随机数 rand 循环( 30-n )次 {取 id>rand and not in (已取) limit 1}
    akira
        32
    akira   60 天前
    同意 25 楼的, 加个连续的自增字段,立马简单起来了
    love2020
        33
    love2020   60 天前
    众所周知,随机数是一个难点
    fox0001
        34
    fox0001   60 天前 via Android
    @bertonzh #25
    @akira #32
    如果是静态数据,这是最简单的方法。然而楼主丢下问题就没有再回复了
    xcstream
        35
    xcstream   60 天前
    最快当然不用 mysql 直接写程序用偏移量读取数据文件
    ipwx
        36
    ipwx   60 天前
    说实话,如果原始 id 不是均匀分布的,那么 id >= RAND() 这种操作去取数字,得出来的结果也不是均匀分布的。

    很简单,举个例子:原始 id 是 1, 5, 6, 7 。那么用 id >= min(id) + rand() * (max(id) - min(id)) limit 1 取数字,每个数字被选取的概率为:

    1 = 4/7
    5 = 1/7
    6 = 1/7
    7 = 1/7
    ipwx
        37
    ipwx   60 天前
    生成第二列连续的 id 然后去取,就不会产生这种不均匀分布的问题了。
    ipwx
        38
    ipwx   60 天前
    1 亿 id 的 int 数组在内存中也就几个 gb 的事情。说实话,单独维护一个索引文件大概都容易搞得定。
    stevefan1999
        39
    stevefan1999   60 天前
    你要先定義什麼是隨機
    譬如我可以定義 generateRandomNumber() { return 4 }
    然後按照這個 random number 做 seed 開一個 LNG 然後生成一個次序...
    因爲隨機不代表不能重複啊 不過如果要去重那更加要命了
    xumng123
        40
    xumng123   60 天前 via iPhone
    将 id 放在一个数组里,随机取 39 个后,然后去查一下数据库。
    akira
        41
    akira   60 天前
    @fox0001 就算是动态数据,只要不是动态的太厉害,定时,例如每天,处理一下自增字段 应该没太大问题的
    renmu123
        42
    renmu123   59 天前 via Android
    搞点伪随机,数据量大了之后,谁也不知道你是真随机还是假随机了
    bthulu
        43
    bthulu   59 天前
    @akira 连续的自增字段怎么加, 你如何保证严格的连续性, 删除数据, 插入未提交等造成的不连续你怎么处理
    ckdxc
        44
    ckdxc   59 天前
    随机行号,行不? 感觉不需要随机 id, round(count), 30 个行号必命中
    wakzz
        45
    wakzz   59 天前
    先看看业务,是不是必须要真随机。有很多业务实际上压根不需要真随机,搞个差一点的伪随机就够用了
    akira
        46
    akira   58 天前
    @bthulu 不需要严格保证的连续性呀
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2453 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 14:31 · PVG 22:31 · LAX 07:31 · JFK 10:31
    ♥ Do have faith in what you're doing.