前两天策划反馈某项活动中,理论上大概千分之一中奖率的一个奖品,连续四次被同一个玩家得到了。他之前已经找了几个程序复查过代码,都说没啥问题,但是从概率上讲,肯定是有问题的,所以希望我“为程序员正名”。经过一上午的奋战,我也算不辱使命,找出了这个潜伏了5年的bug,也了解了关于mysql的一个潜规则。
整个业务场景大概是这样的(下面代码仅为示意,不可直接运行):
首先,每个玩家报名的时候,向数据库中插入一行,记录下活动id(activity_id),玩家uuid(player_uuid),申请序号(apply_no)以及一些其他的玩家相关信息。
同时,在内存中维护一个整数,记录当前活动的申请总人数apply_total_num。同时使用这个整数来获得下一个插入行的apply_no。
第二,由策划填写的数据表导成数据结构表示奖励结构,即每个等级的奖励各有多少份,如下所示:
1 2 3 4 5 6 |
awards = { 1: 1, 2: 15, 3: 50, ... } |
第三,当活动报名完毕时,根据当前的apply_total_num和总奖励份数sum(awards.values()),生成一个长度为总奖励份数的随机的apply_no序列。比如有10000人报名,总奖励分数是1000,那么我们有可能生成如下的一个包含1000个apply_no的序列:
1 |
lucky_apply_nos = [234, 123, 1356, 8765, 12, ...] |
第四,从MySQL数据库中取出lucky_apply_nos对应的uuid:
1 |
lucky_uuids = SELECT player_uuid FROM world_lottery WHERE apply_no IN lucky_apply_nos; |
拿到lucky_uuids之后,根据awards对应分配奖励:lucky_uuids[0]对应1等奖,lucky_uuids[1]~lucky_uuids[15]对应2等奖,依此类推。
看上去没有什么问题吧。其实问题就出现在SELECT语句这里了。在这段流程中有一个想当然的推论,就是lucky_uuids的顺序是和lucky_apply_nos中的顺序一致的。但是,其实这里踩了MySQL的一个潜规则坑:
对于没有ORDER BY子句的SELECT语句,其返回顺序是和所使用的引擎有关:
对于MyISAM引擎来说,其返回顺序是其物理存储顺序;
对于InnoDB引擎来说,其返回顺序是按照主键排序的。
也就是说,无论哪种情况,都不会按照IN子句里的列表lucky_apply_nos的顺序返回……
具体到我们这里的情况来说,引擎使用的是InnoDB,主键是player_uuid,也就是说返回的lucky_uuids是依据palyer_uuid排过序的。
那这样会导致什么问题呢?
如果某个玩家的uuid特别小,那么只要这个玩家进入了奖励大名单(lucky_apply_nos),那么他就一定会获得价值最高的1等奖。这样这个玩家连中四次一等奖的概率就是0.1^4,而不是0.0001^4。
这个玩法如果在奖励大名单的中奖概率和各个奖项的中奖概率相差不大的时候,bug体现的就不太明显。但是在这次活动中,策划加大了奖励大名单的数量(主要是通过增加低价值奖励的数量),这样就比较容易出现某位玩家连中几次高价值奖励的情况了。
解决方法也很简单,拿到数据库的返回结果lucky_uuids之后,再进行一次shuffle即可。
总结
MySQL对于无ORDER BY子句的SELECT的语句的返回结果有潜规则:
- 对于MyISAM引擎来说,其返回顺序是其物理存储顺序;
- 对于InnoDB引擎来说,其返回顺序是按照主键排序的。
打赏支持我写出更多好文章,谢谢!
打赏作者
打赏支持我写出更多好文章,谢谢!
任选一种支付方式
最新评论
不错,长知识了
赞
谢谢鼓励,共勉~
赞
怪不得没有中奖过,肯定是我的主键太大了
赞
我比较好奇的是,您的解决思路,怎么会想到是mysql引擎背的锅?
赞
我自己有去数据库上运行那句select语句,偶然发现每次返回的顺序都一样,但是却不是IN子句里的顺序,所以对这块逻辑产生了疑点。后来又知道最近的这次活动中确实增大了低等级奖励的数量,也印证了我的猜测^^
赞
摘录本文发布在「数据库开发」(微信号:DBDevs)后的一些评论:
逝水fox:
什么都说得好,就是总结的太胡扯了。[尴尬]结果顺序和具体引擎对数据结构的使用有关。举个例子,innodb使用辅助索引进行筛选的结果,有无启用mrr优化,返回记录顺序是可能不同的,不一定就是主键序。其实,遇到过最奇怪的顺序问题,不是MySQL,而是Oracle,即使用了order by,数据记录的顺序仍然可能是不一定的
王永新
潜规则不是规则,事实上,没有order by的话innodb不提供任何顺序上的保证。。很容易的,where字句中使用一个有索引的列,能出现查询结果不按照主键顺序排列的结果
爱吹牛的天线宝宝。
不是潜规则,这个是MySQL 默认的排序方式,是你自己想当然认为MySQL 应该是这样排序导致的。建议作者还是看一看MySQL 的文档,了解一下MySQL 的基本操作,防止下次再次出现想当然的潜规则
李磊
BUG是发现了,但总结的不正确。
Oscar
除了MySQL,其他数据库依然不保证顺序,只是你这这查询语句就有问题
Roy/快乐来来
这个哪能叫bug呢?数据库返回纪录的顺序只有order by 可以保证,其他任何假设都不是必然成立的。这是对数据库理解不到位导致的常见问题之一
术士
这个是不了解数据库原理造成的,跟MySQL潜规则没啥关系,所有的数据库都是这样的
赞
呵呵,明明是自己的业务逻辑设计的有BUG。
1 赞
1.这段业务是我后来被委托查bug时接手的,并不是我设计的。设计这段业务的程序员早已不在项目组了。
2.就我的观点来看,这段代码的设计并无bug,只是踩了一个mysql的坑,或者说是实现上有bug。
3.前面也有老司机@逝水fox指出了我内容中不对的地方,这里确实我对Mysql理解的不够。不过我也不打算修正原文了,后来人先看有错误的文章再看评论的指正,估计会更有收获。
赞
非标准的默认实现不能作为设计的一部分这是铁律。
1 赞
select id from table where id in(63262,63261,63269) order by field(id,63262,63261,63269); 这样可以解决啊
赞