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 条,尽可能多就行)
1
mifly 2017-01-05 21:13:02 +08:00 via iPhone 1
表增加一个字段 seq,每个线程先获取一个全局唯一序列,用这个序列,先 update seq 值为序列,然后再用这个序列作为条件查询
update 语句是行锁,能够防并发 或者你根据总记录,然后根据 id 大小分段给各个线程 |
2
akira 2017-01-05 21:16:31 +08:00
先吧 State = '0' 的对应 id 全部读取出来,然后自己程序分配到多个线程里面去处理?
|
3
est 2017-01-05 21:26:03 +08:00
改从 db pull 的模式为 db 主动向请求给 push 。
|
5
winnie2012 2017-01-05 21:29:17 +08:00
队列经典场景
|
6
dolee OP @winnie2012 求大神指导
|
7
fyibmsd 2017-01-05 21:37:14 +08:00
redis 分布式锁
|
8
dolee OP @mifly 总记录的数量还会增加,而且线程数量也不固定。
InnoDB 能不能锁定查询到的 100 条记录,不给后面的再查询到这 100 条记录呢? |
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 吧
|
10
gouchaoer 2017-01-05 21:52:01 +08:00 via Android 1
这么说吧,遇到并发数据竞争第一个想到的应该是 redis ,因为 redis 天生单线程无竞争
|
11
stabc 2017-01-05 22:11:42 +08:00
可以考虑每次提取数据时用 limit 100 offset $offset, 这里的$offset 从 0-1000 随机。 1000 这个数值可以根据测试结果调整。
|
12
ddou 2017-01-05 22:27:30 +08:00 via iPhone
把业务场景讲讲清楚吧 大家看有没有更合理的解决方案
|
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 条。 |
14
realpg 2017-01-05 22:51:43 +08:00
SELECT FOR UPDATE
先 UPDATE 至中间值 方法多了去了 楼主的 MYSQL 根本没达到入门水平 |
15
Lax 2017-01-05 22:55:56 +08:00 via iPad
改下 sql ,貌似不需要多线程:
update table set state = 1 where stat = 0 性能跟表结构还有关系,而这里看不到你的索引情况。 或者继续用多线程: 1000w 条记录, N 个线程,把数据分成 N 个区,每个分区给且只给一个线程去处理。 |
16
Lax 2017-01-05 22:59:35 +08:00 via iPad
理解有误。继续多线程按 id 分区吧。
|
17
kimwang 2017-01-05 23:07:14 +08:00
搭车问一下,直接 SELECT 会不会效率低?好像很多框架或者案例都是把增查改删语句实例化,然后再查询?
我也是学习 PHP+MYSQL 中。 |
18
dolee OP 谢谢大家!
看到大家的热心解答,受益良多! 现在正在学 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 条数据 |
19
ihuotui 2017-01-05 23:36:28 +08:00
其实思考方向先分区在处理,在多线程情况下分区执行才是效率最高的,像 currenthashmap 那样,没有冲突的多线程编程才是性能最好的,而且分区后就不用处理 sql 冲突问题, sql 执行冲突解决这么麻烦。在单机情况下可以用原子类或者 volitaion 类同步信息的,然后分布式情况下就是控制每台机器的段,达到每台机器都是平均段落, A , 0 到 4999 , B , 5000 到 9999 ,依次这样达到并发最大效率。把复杂问题简单化,而且达到切到好处。
|
20
ETiV 2017-01-06 00:28:31 +08:00
每条数据只输出一次,那就提前把 1000 万条数据分好组(每组 100 条),然后每组对应一个自增 id 。
请求进来后,先去 redis 拿一个自增的编号,再去数据库拿跟编号对应的分组 id 的那组。 |
21
yidinghe 2017-01-06 00:40:42 +08:00
我觉得这等同于 10 万条记录每次取一条,不重复。
|
22
Miy4mori 2017-01-06 02:11:57 +08:00
@kimwang 你觉得你的程序可以不通过 SQL 直接查询数据库的数据吗,包装一层 API 再用生成 SQL 查询效率高还是直接使用手动优化过的 SQL 效率高?当然都没有存储过程效率高。
|
23
Miy4mori 2017-01-06 02:17:32 +08:00 1
都在考虑即时的方案,我来给你讲个非主流的,在插入数据的时候预先分组或者定期对没有分组的数据分组,然后查询的时候直接通过分组表和你的数据表关联查询,标记业务完成状态也标记分组表。简单粗暴,就是实时性不高,不过你这种定期统计类似的需求貌似也不需要太高的实时性。还有一个问题就是事务控制要稍微麻烦点。
|
24
shiny 2017-01-06 02:55:16 +08:00 via iPhone
楼上分组的思路好。预先分组,每次查询自增一个分组 ID ,性能绝对好。
|
25
cnwtex 2017-01-06 03:01:27 +08:00 via iPhone
MySQL 有 select random ( 100 )
|
27
hdlove 2017-01-06 10:39:17 +08:00
使用 redis 分布式锁,先取出 100 条,进行循环 ,设置传入单独标示,单独的 key,加 数据库一个不重复的值,再设置过期时间,可以上网上搜下类似相关的文档,一套你就明白了
|
28
xpresslink 2017-01-06 11:17:31 +08:00 1
这个明显要用 producer 和 consumer 模式,一个线程专门做 producer 从数据库里取数据,每次批量一 100 条放到队列里。其它开 N 个 consumer 线程分别从队列里取每次取 100 条云处理。
|
29
jianzhiyao020 2017-01-06 11:18:46 +08:00
1.ID 分组,调起不同线程的时候,确定线程负责的 ID 范围
2.利用生成者生成 ID 范围的队列,消费者直接根据获得的 ID 范围自己撸 |
31
sbugzu 2017-01-06 14:52:48 +08:00
如果 ID 是自增的或者有序的,直接通过 ID range 对每 100 个分组从数据库取数( a 可以用一个集中的分组程序或者 Redis 一类来实现分布式锁。 b 再简单点按线程数不同线程使用不同的步长),上面直接 limit 的同学数据量大了必然出现深翻页的性能问题。
|
32
dolee OP @realpg
用这个方法测试了两天,效率提高了很多,但是还是不够…… 并发量太大, MYSQL 压力还是很大,很容易挂掉…… 另外数据量不是固定 1000W 的,每天还会增加 100W 左右 如果用 Redis 效率应该会更高吧^_^ |
33
dolee OP 感谢大家的热心解答~
现在已经有解决的思路,谢谢大家 |
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 并不会造成挂掉(很庞大的在线用户量的大系统) |