千万级数据,多表(大概9个表)分组查询汇总 sql 优化 问题

1. 运行环境

lnmp

1). 当前使用的 Laravel 版本?

laravel8

2). 当前使用的 php/php-fpm 版本?

PHP 版本:8.0.3

3). 当前系统

centOS7.9

4). 业务环境

生产环境

5). 相关软件版本

mysql 8.0.1

2. 问题描述?

现在有个商品信息表大概几万数据,需要关联大概九个表,类目表(cate),有两个表(goodsFlow,goodsFlowPerson)有一千多万数据,goodsSpus,adGoods(adGoodsSum)大概百万数据,skuDaily,refundOrderGoods,refundSkuDaily 有几十万数据,现在需要商品信息表关联这些表,分组汇总所需要的字段数据,该加的索引全部加了,强制索引也用了,千万级表扫描行数还有280多万,百万级表扫描行数也有几十万行(业务砍不了,必须这么多表),按接口拆分过sql不是很理想

千万级数据,多表(大概9个表)分组查询汇总 sql 优化 问题

3. 您期望得到的结果?

这个sql 该怎么优化,或者怎么拆分能达到1秒左右

4. 您实际得到的结果?

按时间段查询大概3个月,整个sql执行完还要八九秒

《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
最佳答案

同步到其他适合大数据查询的数据库

1个月前 评论
Alone88 (作者) 1个月前
nb_xw (楼主) 1个月前
Alone88 (作者) 1个月前
nb_xw (楼主) 1个月前
nb_xw (楼主) 1个月前
讨论数量: 32

同步到其他适合大数据查询的数据库

1个月前 评论
Alone88 (作者) 1个月前
nb_xw (楼主) 1个月前
Alone88 (作者) 1个月前
nb_xw (楼主) 1个月前
nb_xw (楼主) 1个月前

得上es了

1个月前 评论
nb_xw (楼主) 1个月前

试试doris,bi专用 :grin:,合理合并数据

1个月前 评论
nb_xw (楼主) 1个月前

把你那个whereHas改一下改成join 走索引 whereHas 大量数据的时候很慢

1个月前 评论
nb_xw (楼主) 1个月前
lijizheng (作者) 1个月前
nb_xw (楼主) 1个月前
lijizheng (作者) 1个月前
lijizheng (作者) 1个月前

whereHas 改成 whereHasIn

1个月前 评论
nb_xw (楼主) 1个月前

优化个毛线,clickhouse就是专为这种业务实现的

1个月前 评论
nb_xw (楼主) 1个月前
QIN秦同学 4周前
linzhijun (作者) 4周前

在源数据表再怎么优化,也不会有太好的结果,如你文章所说,强制索引,仍然要扫描几百万数据,即便是覆盖索引,查询速度也比较慢,这还不算你的数据库聚合运算消耗的时间,如果再有分页,仅count查询就非常耗时,当然myisam引擎查询要比innodb更有优势些,根据你说的目前只有2种方案:

  1. 独立业务汇总统计表,在源数据表业务上做钩子回写统计,或者时间粒度比较低的定时任务统计汇总
  2. 换数据库,比如clickhouse去做这方面统计。
1个月前 评论
nb_xw (楼主) 1个月前

whereHas 在大数据面前不是一个好的选择

1个月前 评论
nb_xw (楼主) 1个月前

clickhouse吧,做过类似的项目,laravel也有扩展包 github.com/the-tinderbox/Clickhous... ,你要是有心思封装下,sql查询的代码都不用怎么改

1个月前 评论
nb_xw (楼主) 1个月前

前面给过方案了,换其它库,再补充一些信息。

现有库主要是OLTP,适合业务写入,分析类场景OLAP类型的分析库会更合适。比如前面提到了百度云产品,也可以看下阿里云ADB,用DTS将业务库OLTP 同步到 OLAP ADB库,在ADB库里面完成查询,就很方便了。唯一缺点可能是需要花点钱,但这个钱花的收益还是比较大的。

仅供参考!

1个月前 评论
nb_xw (楼主) 4周前

这sql写的最后查询的正不正确自己都不敢保证

4周前 评论
nb_xw (楼主) 4周前

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