Laravel
话题列表 社区 Wiki 优质外文 招聘求职 Laravel 实战教程 社区文档
登录
注册

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

问答 / 986 / 50 / 创建于 2个月前 / 更新于 2个月前

目前日志表每天都能产生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。分析效率得到解决，非常感谢各位的热心解答！

Xiaoxiaoww
19 声望
暂无个人描述~
《L04 微信小程序从零到发布》
《L04 微信小程序从零到发布》
从小程序个人账户申请开始，带你一步步进行开发一个微信小程序，直到提交微信控制台上线发布。
《L03 构架 API 服务器》
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程，JWT 概念及使用 和 API 开发相关的进阶知识。
yefy
课程读者 118 声望
最佳答案

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
果然如此，非常感谢。
YeRic 2个月前
能不能写篇心得分享一下？
yefy （作者） 2个月前
@YeRic 好的
1
讨论数量: 50
排序：
时间 投票
kis龍
Laravel 9.x 译者 426 声望

elk ?

数据库直接就死掉了

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
我查资料，有说用tidb之类的对改善查询没用吧？
kis龍 （作者） 2个月前
@Xiaoxiaoww 还有这个 RabbitMQ， 我只是部署过，没怎么使用，太详细的也回答不了
Xiaoxiaoww （楼主） 2个月前
@kis龍 RabbitMQ不是队列服务吗？
kis龍 （作者） 2个月前

@Xiaoxiaoww

Laravel

Xiaoxiaoww （楼主） 2个月前

@kis龍 谢谢，可是我这个也不算单纯的系统日志呢。目前的业务需求很简单，就无时延按 uid 查出用户日志数据列表 + group by（pid!=0，则按 pid 分组，还有几个 sum (tax) ）

sanders
课程读者 389 声望 / 程序员鼓励师 @ KDD

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

2个月前 评论
sanders （作者） 2个月前
@Xiaoxiaoww 哦，单表 5000w uid 有索引的话按说不至于，explain 看看哪？后面那个 goup by 确实很难优化，如果是聚合分析的话，可以考虑采用 clickhouse 或者预存聚合结果。
Xiaoxiaoww （楼主） 2个月前
已经分表了，兄弟。
lovewei
105 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
目前的业务需求很简单，就按uid查出用户日志数据列表+group by（如果pid长度大于0，则按pid分组，sum(tax) ）无时延需求是有的，请问用存储过程或者数据库钩子怎么实现呢？就是group by不知道如何优化效率，就因为这个需求就引入clickhouse或es会不会动作太大？
Xiaoxiaoww （楼主） 2个月前

我刚刚查了clickhouse，请问直接用MYSQL引擎查询数据，能不能得到CLICKHOUSE的性能？还是需要使用物化MYSQL引擎？还是插入数据到mysql时，同时插入一份到clickhouse。

lovewei （作者） 2个月前

@Xiaoxiaoww 有几点疑问：

  1. 你按天分表，有没有跨天查询的业务？如果没有，可以考虑在分表中使用分区，按照uid分区。
  2. 你给的count_sql和data_sql条件不一致，按照你data_sql的条件需要使用聚合统计，为什么count_sql没有使用聚合统计条件，这样的带来的问题就是分页数据不一致。
  3. 业务设计有问题，不应该在group by使用条件判断，条件判断应该放在where中，业务设计应该将两种结果独立化，前端根据条件区分渲染。
Xiaoxiaoww （楼主） 2个月前
@lovewei 非常感谢您的解答，1没有跨天的业务 2、count_sql实际中也需要group by，我想知道不加GROUP有没有简单的解决方案。3、目前的业务好比如商品出售列表，单独购买的正常显示一条（ID），购物车批量购买合并为一条（PID）并SUM税额。我昨晚连夜研究您所说的CK，如果CK能直读MYSQL并且能有巨大提升的话，CK确实值得引用，因为这么多的数据量，每次编写SQL相关的业务都有效率担忧。
lovewei （作者） 2个月前

@Xiaoxiaoww 按照你说的业务场景情况，你应该依据pid业务维度设计一张新的业务统计表，在原有的日志业务基础上，拆分计算用户购物车相关数据，插入到业务统计表，这样你就避免了group by操作，实现方式以下2种：

  1. 业务代码上进行数据写入，优点: 后续迭代维护方便，缺点：受制于并发操作，非事务性SQL不能保证数据一致性(几率较低)。
  2. 数据库钩子，以原日志表为基础创建after_insert钩子，在数据库钩子函数中完成业务，优点：较业务性能高，缺点：维护不方便，出错不容易定位问题。

新的业务统计表，可以考虑使用myisam引擎，详细请查阅innodb和myisam不同以及性能差异，如果涉及分区，就不建议使用myisam了，mysql8.0版本，myisam不再支持本地分区策略了。

Xiaoxiaoww （楼主） 2个月前
@lovewei 您的意思是说把pid!=0的统计插入一条数据到新表，然后在查询的时候join统计表吗？那大数据集的表就需要加条件了 where uid=1 and pid != '' 这样会导致全表扫描吧？而且这样还是不能解决count_sql的效率吧？
lovewei （作者） 2个月前
@Xiaoxiaoww 不需要join，查询数据以新表为准，根据你的业务分为2种场景，单独购买和购物车批量购买，这两者场景都要写入新表，区别在于前者可以直接写入汇总表，后者需要根据pid聚合计算出结果写入新表，这样你的查询完全依赖新表，不需要group by。
Xiaoxiaoww （楼主） 2个月前
@lovewei 明白了，但是这样会冗余一份数据，如果订单状态发生改变，需要维护2张表。而且本来1s只需要插入1W数据的，现在需要插入2W条数据。查解决了，写和更新成了问题。
lovewei （作者） 2个月前

@Xiaoxiaoww 你这业务产品设计估计有问题，大数据的日志表，还存在回写？这还是日志表吗？这已经是业务表，如果日志数据跟随业务状态发生变化，那就不应该这么设计了。

Xiaoxiaoww （楼主） 2个月前

@lovewei 不好意思！可能误导了，日志是业务，的确是业务表，因为是需要基于表中数据的数据状态统计。

小猪蹄子
184 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
需要实时查询的呢，不是系统日志。
小猪蹄子 （作者） 2个月前
@Xiaoxiaoww 如果必定有user_id做条件，那每个user_id的数据只会占用表的一小部分，真可以尝试看看把一个user_id的数据取出来做处理，一个user_id的内存占用量，目前看你表字段其实并不会很大，如果有其他条件筛选的话，确定就比较麻烦了
小猪蹄子 （作者） 2个月前
@Xiaoxiaoww 这种是不是可以做汇总表
YIBAI
课程读者 0 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
这个不是日志统计呢，主要需求就是按用户显示日志数据列表。然后如果pid长度大于0，则按pid分组，否则按id分组。
Buffett-Cai
课程读者 43 声望 / 打工仔 @ 打工仔联盟

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
数据表结构有什么必要藏着掖着？我只是尽量把问题描述简单，不浪费大家时间。我也测试过，这种量级的数据表瓶颈不在索引，而是在group by。
轻描淡写
课程读者 309 声望

每天五千万还存mysql？

2个月前 评论
dangdangdang 2个月前
不存MySQL存什么呢？什么结构适合？
轻描淡写 （作者） 2个月前
@dangdangdang 我们的日志是存es的
她来听我的演唱会
Laravel 9.x 译者 544 声望

可不可以再优化一些：

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

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

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

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
谢谢，每个表只使用一天，这个不是日志表，而是用户操作的金额日志表，需要实时的显示列表给用户，而且我测试过把字段减到最少，但是这个量级使用group by还是不行。
xiyangyang
课程读者 0 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
我还没研究过elk，这个不是系统日志表，而是用户操作的金额日志表，主要需求就是按用户显示日志数据列表。然后如果pid长度大于0，则按pid分组，否则按id分组。如果只是这个需求的话，有必要引入elk吗？
xiyangyang （作者） 2个月前
@Xiaoxiaoww id是唯一的，这样说其实pid=0那种是不需要group by的，那要分组的就是pid>0的，可以考虑预处理，比如pid=1，有这样一条记录是pid=1的统计数据，每当有pid=1的数据写入的时候，更新一下统计数据
Xiaoxiaoww （楼主） 2个月前
@xiyangyang 目前的业务其实也不需要统计，就好比如商品出售列表，单独购买的正常显示一条（ID），购物车批量购买合并为一条（PID）并SUM税额。
Imuyu
课程读者 779 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
谢谢，但是这个是用户操作的金额日志表，需要实时的显示列表给用户呢。
Imuyu （作者） 2个月前
@Xiaoxiaoww 除了优化架构，可以考虑实时展示半年内的记录，更多的记录去另一个地方查询
巅峰互联
课程读者 19 声望 / PHP开发 @ 自由职业者

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

2个月前 评论
bbdd
见习助教 12 声望
  1. flink 实时计算需要的数据
  2. 换 OLAP 的数据库（clickhose,tidb,Doris）
2个月前 评论
yangweijie
课程读者 29 声望 / PHP工程师 @ 江苏艾克斯信息科技有限公司

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

2个月前 评论
yefy
课程读者 118 声望

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

2个月前 评论
Xiaoxiaoww （楼主） 2个月前
果然如此，非常感谢。
YeRic 2个月前
能不能写篇心得分享一下？
yefy （作者） 2个月前
@YeRic 好的
1
cccdz
课程读者 465 声望

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

2个月前 评论
FixBug
课程读者 84 声望

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

2个月前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！
支持 MD 帮助
Xiaoxiaoww 19 声望
社区赞助商
成为赞助商

关于 LearnKu

LearnKu 是终身编程者的修道场
做最专业、严肃的技术论坛
LearnKu 诞生的故事

资源推荐

  • 《社区使用指南》
  • 《文档撰写指南》
  • 《LearnKu 社区规范》
  • 《提问的智慧》

    • 服务提供商

    其他信息

  • 成为版主
  • 所有测验
  • 联系站长（反馈建议）

    • 粤ICP备18099781号-6 | 粤公网安备 44030502004330号 | 违法和不良信息举报

    由 Summer 设计和编码 ❤

    请登录

    内容举报
    匿名举报，为防止滥用，仅管理员可见举报者。

    我要举报该，理由是：

    取消