[高并发问题] 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)

大神们若有合适的方案还请不吝赐教

本帖已被设为精华帖!
本帖由 Summer 于 3年前 加精
hookover
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 12
leo

用户余额放 redis 不妥,按你们这个数据量读写缓存频率很高,redis 又是单进程的容易成为瓶颈。

使用 update money = money - cost where uid = xx and money >= cost 这条 sql 可以保证不出现余额为负的情况,并且通过 affected rows 来判断是否扣款成功,这样就免去查余额的步骤了。

3年前 评论
hookover

@leo 我是把整个User对象序列化后放到了缓存,因为用户登录的频率也会很高,而且用户获取结果的频率会非常之高(一个上传请求对应50个查询请求,如果直接查库,肯定不行),若redis有这个瓶颈限制,是否还有其它好的解决方案。
我当前在测试中还遇到一个问题,也没找到原因:

环境:本地开发机器用的是DOCKER容器(nginx mariadb php7 redis) + lumen框架 + targetliu/phpredis包
表现:采用laravel开发的管理端页面没有缓存,直接查库的,访问日志表(每页15条数据展示),3条SQL语句直接查库
,再加上页面加载,总耗时18ms左右(比较稳定)
select * from `admins` where `id` = '1' limit 1
select count(*) as aggregate from `files`
select * from `files` order by `created_at` desc limit 15 offset 0

而加了redis的lumen,向result api获取结果时,只有缓存中没数据时,我才会从库中加载到缓存。
通常耗费时间是 8ms左右,但偶尔会有200ms以上 (5%概率)

上传文件api,我测试时采用的是不到100个字节的base64文件
上传api需要经过如下步骤:
验证 app key (缓存)  -> 验证文件 -> 验证类型ID(缓存)-> 验证用户及余额(缓存)->保存文件(Storage)->扣费  

表现比较奇怪:总耗时60ms左右(70%概率),耗时200~300ms(25%),耗时300ms~1s(5%)

以上测试只是采用手工刷新页面

若用ab测试,并发100个连接,100000次请求,再手工请求的时候,耗时都是在700ms~1s~3s左右了
初步感觉可能是docker的瓶颈,还没有重新架环境测试

3年前 评论
hookover

一个比较有意思的现象是用AB压测的时候,CPU占用还非常高....

3年前 评论
hookover

最后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来做

3年前 评论
hookover

最终解决方式,将snowflake生成的ID存到了2个字段中,我将原来的snowflake生成ID改由32位改了26位,其中前13位是时间戳微秒,后13位是机器码线程码和序列码,刚好分成2个bigint类型的字段。同时,我使用前13位的时间戳数字进行分区,最终效果还是非常不错的

3年前 评论

@leo 使用laravel Eloquent Model 去$num = $model->update(['money' => 1]) 这个$num并不是affected rows, 你知道Eloquent的update如何获取到affected rows吗

3年前 评论
leo

@LinGod Model::getQuery()->where('id', $id)->update(['money' => 1]),这里的getQuery是获取 database 的 query builder,database query builder 的 update 方法返回的就是 affected rows

3年前 评论

@leo 感谢, 困扰了挺久, 刚用laravel不久, $model->update(['money' => 1])一直返回的是true or false, 百度也没有相关方法

3年前 评论

膜拜大神ing

3年前 评论

这么大的表为什么不考虑分表和引入数据库中间件呢

3年前 评论
hookover

@zhengzean 因为当前实际业务量并没有这么大,只是按这个规格来进行前期设计。不借助中间件的分表与分区在底层性能上一样的,但分表会让代码层面更加复杂,倒不如使用MYSQL自身的分区了,而借助中间件的的分布式架构分表,目前用不到。热数据只有10多分钟,之后就是冷数据,在业务量起来后,考虑将它们放到缓存的

3年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!