[高并发问题] MySQL 分区的情况下不能使用自增 ID,若使用 UUID 与时间做联合主键,数据量特大时,查询库效率如何?
业务背景:
每天会有1000~3000万条数据入库
每条数据入库后会有多次查询
每条数据入库时需要检查用户余额,并扣除余额
热数据只有10分钟,也就是这些数据入库后10分钟内会重复多次使用,然后每天凌晨会对前一天的数据作一次数据统计
我现在的准备使用的方式是:
使用时间字段入库表进行分区,每天建一个新的分区,旧分区保存7天(使用MYSQL 事件操作)
因为MYSQL分区之后,没有自增ID,于是我使用UUID的方式做ID,又因为需要使用时间来分区的关系,所以我的联合主键是 (时间,UUID)。
用户信息保存到redis,验证用户余额时直接判断缓存余额够不够。若缓存够,直接update money = money - cost 。会有一个余额扣负仍然可用的问题。缓存1分钟更新。
查询结果也保存到了缓存,先查缓存
存数据的时只有2条写库SQL语句 1条插入数据、1条改用户余额
查询的时候,也加了缓存
现在的问题是:
1.当这个表超大时,或达到7天时间,极限可能会有2亿多数据,此时根据UUID去查一条记录的时候会不会非常慢?有什么优化方法?
2.因为UUID是字符串,前后可能不连续,所以它没办法使用2分查找,索引可能效果也不是特别好?在数据量特别大的情况下UUID是否不适合做主键?
自己思考:如果我再额外建立int类型的索引字段,在高并发的情况下,我没法保证效率和唯一性,若每次都去查一下数据库找到最大值(可以放到缓存),再入库?感觉这个方法也可能会有风险。
3.update money = money - cost 这种方式扣用户余额,在高并发情况下有问题吗?
4.MYSQL事件删除旧分区时,因为数量很大,会不会导致数据库很慢?
5.最后一个问题,这样的业务环境,使用MYSQL分区好,还是用程序每天建一个新表?(建新表方式开发成本较高,但可以使用自增ID,不用UUID)
大神们若有合适的方案还请不吝赐教
用户余额放 redis 不妥,按你们这个数据量读写缓存频率很高,redis 又是单进程的容易成为瓶颈。
使用
update money = money - cost where uid = xx and money >= cost
这条 sql 可以保证不出现余额为负的情况,并且通过 affected rows 来判断是否扣款成功,这样就免去查余额的步骤了。@leo 我是把整个User对象序列化后放到了缓存,因为用户登录的频率也会很高,而且用户获取结果的频率会非常之高(一个上传请求对应50个查询请求,如果直接查库,肯定不行),若redis有这个瓶颈限制,是否还有其它好的解决方案。
我当前在测试中还遇到一个问题,也没找到原因:
以上测试只是采用手工刷新页面
若用ab测试,并发100个连接,100000次请求,再手工请求的时候,耗时都是在700ms~1s~3s左右了
初步感觉可能是docker的瓶颈,还没有重新架环境测试
一个比较有意思的现象是用AB压测的时候,CPU占用还非常高....
最后xhprof工具可以检查到各种性能瓶颈的地方。目前的结论是不能使用UUID来做主键,会非常慢。
目前我尝试过使用twitter的https://github.com/Sxdd/php_snowflake/来生成唯一ID,但这玩意生成的数字太长,
保存到数据库里没问题,但显示到页面就被LARAVEL转成int类型越界了
mysql自带有uuid_sort()函数可以生成唯一ID,但通过DB::table()->create 或Model::create() 均无法得到这个ID
所以也比较郁闷,相当于我插入了一条数据,但我并不知道它的ID,这是我的业务逻辑所不允许的。如果有人知道在插入数据的同时获取到由MYSQL生成的非自增ID的话,请告诉我,谢谢。
最后通过近几天的测试,如果实在不行,只有考虑放弃使用MYSQL分区表的方式了,计划用脚本每天建立新表,然后 将旧数据移到新表中来并删除旧数据库中的数据,采用MYSQL的自增ID来做
最终解决方式,将snowflake生成的ID存到了2个字段中,我将原来的snowflake生成ID改由32位改了26位,其中前13位是时间戳微秒,后13位是机器码线程码和序列码,刚好分成2个bigint类型的字段。同时,我使用前13位的时间戳数字进行分区,最终效果还是非常不错的
@leo 使用laravel Eloquent Model 去
$num = $model->update(['money' => 1])
这个$num
并不是affected rows, 你知道Eloquent的update如何获取到affected rows吗@LinGod
Model::getQuery()->where('id', $id)->update(['money' => 1])
,这里的getQuery
是获取 database 的 query builder,database query builder 的update
方法返回的就是 affected rows@leo 感谢, 困扰了挺久, 刚用laravel不久,
$model->update(['money' => 1])
一直返回的是true or false, 百度也没有相关方法膜拜大神ing
这么大的表为什么不考虑分表和引入数据库中间件呢
@zhengzean 因为当前实际业务量并没有这么大,只是按这个规格来进行前期设计。不借助中间件的分表与分区在底层性能上一样的,但分表会让代码层面更加复杂,倒不如使用MYSQL自身的分区了,而借助中间件的的分布式架构分表,目前用不到。热数据只有10多分钟,之后就是冷数据,在业务量起来后,考虑将它们放到缓存的