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

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

这是一个创建于 2878 天前的主题，其中的信息可能已经有所发展或是发生改变。

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 条，尽可能多就行）

State

记录

读取

并发

37 条回复 • 2017-01-09 21:18:45 +08:00

mifly

2017-01-05 21:13:02 +08:00 via iPhone

表增加一个字段 seq,每个线程先获取一个全局唯一序列，用这个序列，先 update seq 值为序列，然后再用这个序列作为条件查询

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

或者你根据总记录，然后根据 id 大小分段给各个线程

akira

2017-01-05 21:16:31 +08:00

先吧 State = '0' 的对应 id 全部读取出来，然后自己程序分配到多个线程里面去处理？

est

2017-01-05 21:26:03 +08:00

改从 db pull 的模式为 db 主动向请求给 push 。

dolee

2017-01-05 21:27:57 +08:00

@akira 每次处理需要的时间很多，而且总记录数量还会增加，有没有办法在读取记录的方法上实现？

winnie2012

2017-01-05 21:29:17 +08:00

队列经典场景

dolee

2017-01-05 21:33:49 +08:00

@winnie2012 求大神指导

fyibmsd

2017-01-05 21:37:14 +08:00

redis 分布式锁

dolee

2017-01-05 21:37:28 +08:00

@mifly 总记录的数量还会增加，而且线程数量也不固定。
InnoDB 能不能锁定查询到的 100 条记录，不给后面的再查询到这 100 条记录呢？

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

2017-01-05 21:52:01 +08:00 via Android

这么说吧，遇到并发数据竞争第一个想到的应该是 redis ，因为 redis 天生单线程无竞争

stabc

2017-01-05 22:11:42 +08:00

可以考虑每次提取数据时用 limit 100 offset $offset, 这里的$offset 从 0-1000 随机。 1000 这个数值可以根据测试结果调整。

ddou

2017-01-05 22:27:30 +08:00 via iPhone

把业务场景讲讲清楚吧大家看有没有更合理的解决方案

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

2017-01-05 22:51:43 +08:00

SELECT FOR UPDATE

先 UPDATE 至中间值

方法多了去了

楼主的 MYSQL 根本没达到入门水平

Lax

2017-01-05 22:55:56 +08:00 via iPad

改下 sql ，貌似不需要多线程：
update table set state = 1 where stat = 0
性能跟表结构还有关系，而这里看不到你的索引情况。

或者继续用多线程： 1000w 条记录， N 个线程，把数据分成 N 个区，每个分区给且只给一个线程去处理。

Lax

2017-01-05 22:59:35 +08:00 via iPad

理解有误。继续多线程按 id 分区吧。

kimwang

2017-01-05 23:07:14 +08:00

搭车问一下，直接 SELECT 会不会效率低？好像很多框架或者案例都是把增查改删语句实例化，然后再查询？
我也是学习 PHP+MYSQL 中。

dolee

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

2017-01-05 23:36:28 +08:00

其实思考方向先分区在处理，在多线程情况下分区执行才是效率最高的，像 currenthashmap 那样，没有冲突的多线程编程才是性能最好的，而且分区后就不用处理 sql 冲突问题， sql 执行冲突解决这么麻烦。在单机情况下可以用原子类或者 volitaion 类同步信息的，然后分布式情况下就是控制每台机器的段，达到每台机器都是平均段落， A ， 0 到 4999 ， B ， 5000 到 9999 ，依次这样达到并发最大效率。把复杂问题简单化，而且达到切到好处。

ETiV

2017-01-06 00:28:31 +08:00

每条数据只输出一次，那就提前把 1000 万条数据分好组（每组 100 条），然后每组对应一个自增 id 。

请求进来后，先去 redis 拿一个自增的编号，再去数据库拿跟编号对应的分组 id 的那组。

yidinghe

2017-01-06 00:40:42 +08:00

我觉得这等同于 10 万条记录每次取一条，不重复。

Miy4mori

2017-01-06 02:11:57 +08:00

@kimwang 你觉得你的程序可以不通过 SQL 直接查询数据库的数据吗，包装一层 API 再用生成 SQL 查询效率高还是直接使用手动优化过的 SQL 效率高？当然都没有存储过程效率高。

Miy4mori

2017-01-06 02:17:32 +08:00

都在考虑即时的方案，我来给你讲个非主流的，在插入数据的时候预先分组或者定期对没有分组的数据分组，然后查询的时候直接通过分组表和你的数据表关联查询，标记业务完成状态也标记分组表。简单粗暴，就是实时性不高，不过你这种定期统计类似的需求貌似也不需要太高的实时性。还有一个问题就是事务控制要稍微麻烦点。

shiny

2017-01-06 02:55:16 +08:00 via iPhone

楼上分组的思路好。预先分组，每次查询自增一个分组 ID ，性能绝对好。

cnwtex

2017-01-06 03:01:27 +08:00 via iPhone

MySQL 有 select random （ 100 ）

chuhemiao

2017-01-06 10:22:28 +08:00

@cnwtex 不是说用函数效率会很低？

hdlove

2017-01-06 10:39:17 +08:00

使用 redis 分布式锁，先取出 100 条，进行循环，设置传入单独标示，单独的 key,加数据库一个不重复的值，再设置过期时间，可以上网上搜下类似相关的文档，一套你就明白了

xpresslink

2017-01-06 11:17:31 +08:00

这个明显要用 producer 和 consumer 模式，一个线程专门做 producer 从数据库里取数据，每次批量一 100 条放到队列里。其它开 N 个 consumer 线程分别从队列里取每次取 100 条云处理。

jianzhiyao020

2017-01-06 11:18:46 +08:00

1.ID 分组，调起不同线程的时候，确定线程负责的 ID 范围
2.利用生成者生成 ID 范围的队列，消费者直接根据获得的 ID 范围自己撸

realpg

2017-01-06 12:13:04 +08:00

@dolee
你 18 楼这个 UPDATE 就是我说的中间值法的变种
用 redis 啥的徒增一层逻辑没必要

sbugzu

2017-01-06 14:52:48 +08:00

如果 ID 是自增的或者有序的，直接通过 ID range 对每 100 个分组从数据库取数（ a 可以用一个集中的分组程序或者 Redis 一类来实现分布式锁。 b 再简单点按线程数不同线程使用不同的步长），上面直接 limit 的同学数据量大了必然出现深翻页的性能问题。

dolee

2017-01-09 02:45:21 +08:00

@realpg
用这个方法测试了两天，效率提高了很多，但是还是不够……
并发量太大， MYSQL 压力还是很大，很容易挂掉……

另外数据量不是固定 1000W 的，每天还会增加 100W 左右
如果用 Redis 效率应该会更高吧^_^

dolee

2017-01-09 02:46:15 +08:00

感谢大家的热心解答~
现在已经有解决的思路，谢谢大家

dolee

2017-01-09 02:48:11 +08:00

@cnwtex 之前也用了随机数，但性能很不好呢，高并发下查询一次基本要数十秒，后面就舍弃了

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

2017-01-09 11:42:27 +08:00

@akira 你这样把机器搞挂了

akira

2017-01-09 21:18:45 +08:00

@beny 怎么说