V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
dolee
V2EX  ›  MySQL

在高并发下,怎样能给每个请求返回 100 条不重复的记录呢?

  •  1
     
  •   dolee · 2017-01-05 21:03:21 +08:00 · 8845 次点击
    这是一个创建于 2864 天前的主题,其中的信息可能已经有所发展或是发生改变。

    mysql 初学者,有个问题请教各位老师

    现在有个表里有 1000 万条记录,需要全部读取出来执行业务一遍。

    (每次读取 100 条,每条记录只能被读取一次,查询过的记录修改 State 为 1 )

    ================

    现在我用的是乐观锁,先用 php 从 mysql 读取出没执行过的最后 100 条记录

    SELECT * FROM list WHERE State = '0' LIMIT 100

    然后一条一条修改 State 改成 1 ,修改成功的,则是有效可用的,否则就是被其他线程抢先修改了

    UPDATE list SET State = '1' WHERE Id='1' AND State = '0'

    这样子,在并发低的情况下是挺好有的,但是在高并发下就不行了

    因为同一时间查询的线程都是读取到的记录都是相同的,

    通过乐观锁过滤掉重复的记录后,最后每个线程剩下的可用记录就少得可怜……

    ================

    请问各位老师,怎样能在高并发访问的情况下,怎样能给每个请求返回 100 条不重复的记录呢?

    (不一定能全返回 100 条,尽可能多就行)

    37 条回复    2017-01-09 21:18:45 +08:00
    mifly
        1
    mifly  
       2017-01-05 21:13:02 +08:00 via iPhone   ❤️ 1
    表增加一个字段 seq,每个线程先获取一个全局唯一序列,用这个序列,先 update seq 值为序列,然后再用这个序列作为条件查询

    update 语句是行锁,能够防并发


    或者你根据总记录,然后根据 id 大小分段给各个线程
    akira
        2
    akira  
       2017-01-05 21:16:31 +08:00
    先吧 State = '0' 的对应 id 全部读取出来,然后自己程序分配到多个线程里面去处理?
    est
        3
    est  
       2017-01-05 21:26:03 +08:00
    改从 db pull 的模式为 db 主动向请求给 push 。
    dolee
        4
    dolee  
    OP
       2017-01-05 21:27:57 +08:00
    @akira 每次处理需要的时间很多,而且总记录数量还会增加,有没有办法在 读取记录的方法 上实现?
    winnie2012
        5
    winnie2012  
       2017-01-05 21:29:17 +08:00
    队列经典场景
    dolee
        6
    dolee  
    OP
       2017-01-05 21:33:49 +08:00
    @winnie2012 求大神指导
    fyibmsd
        7
    fyibmsd  
       2017-01-05 21:37:14 +08:00
    redis 分布式锁
    dolee
        8
    dolee  
    OP
       2017-01-05 21:37:28 +08:00
    @mifly 总记录的数量还会增加,而且线程数量也不固定。
    InnoDB 能不能锁定查询到的 100 条记录,不给后面的再查询到这 100 条记录呢?
    gouchaoer
        9
    gouchaoer  
       2017-01-05 21:50:08 +08:00 via Android
    这个太简单了。。。 redis 里一个主键 int 初始值为 0 ,所有 php 进程 incr 那个 redis 的 int 值,拿这个值去数据库找到主键相等的记录处理,处理完了下一个。。。。不太建议用多线程, php-cli 下开多进程方法很多, linux 下最简单 pcntl , windows 下异步开 proc_open 吧
    gouchaoer
        10
    gouchaoer  
       2017-01-05 21:52:01 +08:00 via Android   ❤️ 1
    这么说吧,遇到并发数据竞争第一个想到的应该是 redis ,因为 redis 天生单线程无竞争
    stabc
        11
    stabc  
       2017-01-05 22:11:42 +08:00
    可以考虑每次提取数据时用 limit 100 offset $offset, 这里的$offset 从 0-1000 随机。 1000 这个数值可以根据测试结果调整。
    ddou
        12
    ddou  
       2017-01-05 22:27:30 +08:00 via iPhone
    把业务场景讲讲清楚吧 大家看有没有更合理的解决方案
    shibingsw
        13
    shibingsw  
       2017-01-05 22:47:33 +08:00
    用悲观锁呢?比如先查询
    select * from list where state = 0 for update limit 100;

    然后再把 state 更新为 1.

    如果同时有两个 process 同时查的话,只能有有个一人先返回,因为有锁。接下来先返回的人更新完,提交事物,由于那一百条的 state 已经变了,刚才等待的 process 就能拿到下面的 100 条。
    realpg
        14
    realpg  
       2017-01-05 22:51:43 +08:00
    SELECT FOR UPDATE

    先 UPDATE 至中间值

    方法多了去了

    楼主的 MYSQL 根本没达到入门水平
    Lax
        15
    Lax  
       2017-01-05 22:55:56 +08:00 via iPad
    改下 sql ,貌似不需要多线程:
    update table set state = 1 where stat = 0
    性能跟表结构还有关系,而这里看不到你的索引情况。

    或者继续用多线程: 1000w 条记录, N 个线程,把数据分成 N 个区,每个分区给且只给一个线程去处理。
    Lax
        16
    Lax  
       2017-01-05 22:59:35 +08:00 via iPad
    理解有误。继续多线程按 id 分区吧。
    kimwang
        17
    kimwang  
       2017-01-05 23:07:14 +08:00
    搭车问一下,直接 SELECT 会不会效率低?好像很多框架或者案例都是把增查改删语句实例化,然后再查询?
    我也是学习 PHP+MYSQL 中。
    dolee
        18
    dolee  
    OP
       2017-01-05 23:29:11 +08:00
    谢谢大家!
    看到大家的热心解答,受益良多!

    现在正在学 redis ,打算用 redis 解决,比较长远又效率

    在 segmentfault 也收获另一个不错的解决方案,具体如下:
    加个字段 pid , UPDATE 的时候,顺便把这 100 条数据打上进程的标记:
    'UPDATE `list` SET `State` = '1', `pid` = ' . getmypid() . ' WHERE `State` = '0' LIMIT 100;'
    锁定了之后,再:
    'SELECT * FROM `list` WHERE `pid` = ' . getmypid() . ' LIMIT 100'
    来拿到这 100 条数据
    ihuotui
        19
    ihuotui  
       2017-01-05 23:36:28 +08:00
    其实思考方向先分区在处理,在多线程情况下分区执行才是效率最高的,像 currenthashmap 那样,没有冲突的多线程编程才是性能最好的,而且分区后就不用处理 sql 冲突问题, sql 执行冲突解决这么麻烦。在单机情况下可以用原子类或者 volitaion 类同步信息的,然后分布式情况下就是控制每台机器的段,达到每台机器都是平均段落, A , 0 到 4999 , B , 5000 到 9999 ,依次这样达到并发最大效率。把复杂问题简单化,而且达到切到好处。
    ETiV
        20
    ETiV  
       2017-01-06 00:28:31 +08:00
    每条数据只输出一次,那就提前把 1000 万条数据分好组(每组 100 条),然后每组对应一个自增 id 。

    请求进来后,先去 redis 拿一个自增的编号,再去数据库拿跟编号对应的分组 id 的那组。
    yidinghe
        21
    yidinghe  
       2017-01-06 00:40:42 +08:00
    我觉得这等同于 10 万条记录每次取一条,不重复。
    Miy4mori
        22
    Miy4mori  
       2017-01-06 02:11:57 +08:00
    @kimwang 你觉得你的程序可以不通过 SQL 直接查询数据库的数据吗,包装一层 API 再用生成 SQL 查询效率高还是直接使用手动优化过的 SQL 效率高?当然都没有存储过程效率高。
    Miy4mori
        23
    Miy4mori  
       2017-01-06 02:17:32 +08:00   ❤️ 1
    都在考虑即时的方案,我来给你讲个非主流的,在插入数据的时候预先分组或者定期对没有分组的数据分组,然后查询的时候直接通过分组表和你的数据表关联查询,标记业务完成状态也标记分组表。简单粗暴,就是实时性不高,不过你这种定期统计类似的需求貌似也不需要太高的实时性。还有一个问题就是事务控制要稍微麻烦点。
    shiny
        24
    shiny  
       2017-01-06 02:55:16 +08:00 via iPhone
    楼上分组的思路好。预先分组,每次查询自增一个分组 ID ,性能绝对好。
    cnwtex
        25
    cnwtex  
       2017-01-06 03:01:27 +08:00 via iPhone
    MySQL 有 select random ( 100 )
    chuhemiao
        26
    chuhemiao  
       2017-01-06 10:22:28 +08:00
    @cnwtex 不是说用函数效率会很低?
    hdlove
        27
    hdlove  
       2017-01-06 10:39:17 +08:00
    使用 redis 分布式锁,先取出 100 条,进行循环 ,设置传入单独标示,单独的 key,加 数据库一个不重复的值,再设置过期时间,可以上网上搜下类似相关的文档,一套你就明白了
    xpresslink
        28
    xpresslink  
       2017-01-06 11:17:31 +08:00   ❤️ 1
    这个明显要用 producer 和 consumer 模式,一个线程专门做 producer 从数据库里取数据,每次批量一 100 条放到队列里。其它开 N 个 consumer 线程分别从队列里取每次取 100 条云处理。
    jianzhiyao020
        29
    jianzhiyao020  
       2017-01-06 11:18:46 +08:00
    1.ID 分组,调起不同线程的时候,确定线程负责的 ID 范围
    2.利用生成者生成 ID 范围的队列,消费者直接根据获得的 ID 范围自己撸
    realpg
        30
    realpg  
       2017-01-06 12:13:04 +08:00
    @dolee
    你 18 楼这个 UPDATE 就是我说的中间值法的变种
    用 redis 啥的徒增一层逻辑没必要
    sbugzu
        31
    sbugzu  
       2017-01-06 14:52:48 +08:00
    如果 ID 是自增的或者有序的,直接通过 ID range 对每 100 个分组从数据库取数( a 可以用一个集中的分组程序或者 Redis 一类来实现分布式锁。 b 再简单点按线程数不同线程使用不同的步长),上面直接 limit 的同学数据量大了必然出现深翻页的性能问题。
    dolee
        32
    dolee  
    OP
       2017-01-09 02:45:21 +08:00
    @realpg
    用这个方法测试了两天,效率提高了很多,但是还是不够……
    并发量太大, MYSQL 压力还是很大,很容易挂掉……

    另外数据量不是固定 1000W 的,每天还会增加 100W 左右
    如果用 Redis 效率应该会更高吧^_^
    dolee
        33
    dolee  
    OP
       2017-01-09 02:46:15 +08:00
    感谢大家的热心解答~
    现在已经有解决的思路,谢谢大家
    dolee
        34
    dolee  
    OP
       2017-01-09 02:48:11 +08:00
    @cnwtex 之前也用了随机数,但性能很不好呢,高并发下查询一次基本要数十秒,后面就舍弃了
    realpg
        35
    realpg  
       2017-01-09 09:58:00 +08:00
    @dolee
    主要是你们 DBA 的问题
    查询没有统一规划吧
    锁问题,内存问题都没有统一协调

    我说一下我的场景
    一个巨大的票务系统,每一个座位就是一条记录,峰值时候一天增加 300GB 新席位数据(不是同一个表,分表)同时删除 300GB 过期数据(热查询数据库删除,备份数据不删除)

    当你在网站上订 N 张票时候,随机将 N 个符合筛选条件的席位的 state 从 0 改为 1[占用]( update 至中间变量获取占用数,因为有可能要 3 张票只有 2 张需要获取数量,然后再修改 state 为 1 )

    在我这个规模的数据下,单纯使用特殊布置的四 MYSQL 负载均衡系统, MYSQL 并不会造成挂掉(很庞大的在线用户量的大系统)
    beny
        36
    beny  
       2017-01-09 11:42:27 +08:00
    @akira 你这样把机器搞挂了
    akira
        37
    akira  
       2017-01-09 21:18:45 +08:00
    @beny 怎么说
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   976 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 22:21 · PVG 06:21 · LAX 14:21 · JFK 17:21
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.