[已解决]请问5000W的MYSQL大数据集日志分页,如何解决查询速度?

目前日志表每天都能产生5000W左右的数据,已经采用分表处理。

但是新业务需要对日志进行分析,请问用mysql如何提高查询效率,或者还有其它改动成本低提升效率的方案吗?!

下面语句的均命中索引,比如查询总数 :

SELECT count(id) FROM log where uid = 1; // 5000w数据即使有索引速度也慢。
select id, user_id, pid, SUM(tax) as total_tax, SUM(tax1) as total_tax1 from `log_20240130` group by IF(LENGTH(pid)=0, id, pid), id order by `id` desc limit 25  // 需要分组操作,经测试2w条数据,效率就0.5s了,如果5000w直接当机了!

经过权衡,最后还是使用了doris来做专门的数据分析。使用canal同步MYSQL的数据到Doris。分析效率得到解决,非常感谢各位的热心解答!

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
最佳答案

直接使用doris呀,秒出结果,最近刚刚优化了一个项目,直接mysql数据表太大,亿级别,卡死,换了doris存储,直接秒出结果

3周前 评论
Xiaoxiaoww (楼主) 2周前
YeRic 5天前
yefy (作者) 5天前
讨论数量: 50

elk ?

数据库直接就死掉了

3周前 评论
Xiaoxiaoww (楼主) 3周前
kis龍 (作者) 3周前
Xiaoxiaoww (楼主) 3周前
kis龍 (作者) 3周前
Xiaoxiaoww (楼主) 3周前
sanders

强制加个日期筛选条件就好了 :stuck_out_tongue_winking_eye:

3周前 评论
sanders (作者) 3周前
Xiaoxiaoww (楼主) 3周前

Mysql Innodb引擎对这种大数据汇总统计并不太友好,分页查询中,仅一个count查询就要较长时间,可能比后面数据分页更耗费时间;理论上这种大数据统计上应该换数据库去做,比如clickhouse;如果非要用mysql实现,建议设计业务统计表去汇总,数据允许时延可以按时间段计划任务实现,无时延只能用数据库钩子或存储过程实现,查看数据明细要指定时间间隔,按时间分区或分表即可,理论上MyIsam引擎处理这种分页查询会比Innodb稍微有优势,至少count查询不会太慢。

3周前 评论
Xiaoxiaoww (楼主) 3周前
Xiaoxiaoww (楼主) 3周前
lovewei (作者) 3周前
Xiaoxiaoww (楼主) 3周前
lovewei (作者) 3周前
Xiaoxiaoww (楼主) 3周前
lovewei (作者) 3周前
Xiaoxiaoww (楼主) 3周前
lovewei (作者) 3周前
Xiaoxiaoww (楼主) 3周前

如果最多只有一个user_id的筛选的话,可以试试把原始数据全部拿到本地,用语言本身去处理数据分页,不过确实耗内存

3周前 评论
Xiaoxiaoww (楼主) 3周前
小猪蹄子 (作者) 3周前
小猪蹄子 (作者) 3周前

每天都能产生 5000W 左右的数据,低成本改动很难优化。 看你给的sql有大量的聚合查询。要么做个统计功能,统计新业务所需数据。要么换架构。

3周前 评论
Xiaoxiaoww (楼主) 3周前
Buffett-Cai

“下面语句的均命中索引”,命中索引不代表效率奇高,索引的用途是提高数据的定位速度,你的条件用了索引但是定位的数据量很多那就是等于白用了。比如ID有索引,where ID > 0那还是等于全表扫描。表结构、哪些字段有索引,查询条件这些都藏着掖着那肯定只能是安抚一下解决不了问题

3周前 评论
Xiaoxiaoww (楼主) 3周前

每天五千万还存mysql?

3周前 评论
dangdangdang 3周前
轻描淡写 (作者) 3周前

可不可以再优化一些:

例如一些热点的非敏感的信息,存入redis缓存,这样能减少数据库的io;

有的日志信息可不可以加入到redis或者mongodb里;

做统计表,例如每天凌晨定时产生统计数据报表,例如一个用户以日、周、月等时间为单位,汇总成统计表,这样的话,相当于用户每次查询需要耗费大量资源去做的计算,已经在统计表里汇总好了,只需要查询就行了。

另外,数据量超大的话,可不可以从存储开始优化,有的数据并不需要长期存储,可以把插入操作改为更新(例如设备当前的状态,以及设备主动上报的信息,其他时候存入的只有批量获取的信息)。

3周前 评论
Xiaoxiaoww (楼主) 3周前

日增5000w还用啥mysql,直接elk,或者用云日志服务比如阿里云的sls :neutral_face:

3周前 评论
Xiaoxiaoww (楼主) 3周前
xiyangyang (作者) 3周前
Xiaoxiaoww (楼主) 3周前

每天5000W除了更换架构和存储类型完,聚合的数据如果实时性要求没那么高,可以定时生成统计,不用每次实时统计~

3周前 评论
Xiaoxiaoww (楼主) 3周前
Imuyu (作者) 3周前
巅峰互联

大数据 你还 groupby 你是考研硬件的 内存还是运算。 变相统计。统计让专业的人。比如第三方。

3周前 评论
  1. flink 实时计算需要的数据
  2. OLAP 的数据库(clickhose,tidb,Doris)
3周前 评论
yangweijie

看你最后的group by 里用了 函数是不会有索引生效的,最好 分开group 后union 后当个虚拟表 再结合pid 来取舍结果集 然后 再分页。 你可以先估一下 分组后 数据有多少条,如果字段少的话 前端分页也不是不行。而且你这种数据 按天分表的话,昨天 之前的数据应该都是冷数据了 ,按天查询应该查一次都是缓存数据了,不去数据库里查询。

3周前 评论

直接使用doris呀,秒出结果,最近刚刚优化了一个项目,直接mysql数据表太大,亿级别,卡死,换了doris存储,直接秒出结果

3周前 评论
Xiaoxiaoww (楼主) 2周前
YeRic 5天前
yefy (作者) 5天前

如果只是统计一些数据 可以根据业务逻辑来实时(异步队列)来直接做统计 而不是后期去聚合统计 这样感觉会好一点

3周前 评论

我有用到tidb方面,目前tidb的遇到你这种数量级别也不行,建议换别的方式。

3周前 评论

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