付费解决mysql sum 多字段统计慢的问题

最近遇见一个sum 统计特别慢的问题。

大概有13个字段需要sum 和一个count

数据五六万就要去到5秒

微ID amu-ajin 欢迎大佬联系我

佣金：500起


CREATE TABLE "order_orders" (
  "id" bigint unsigned NOT NULL AUTO_INCREMENT,
  "oid" char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "user_id" bigint unsigned NOT NULL,
  "username" varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "nickname" varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  "quantity" bigint unsigned NOT NULL DEFAULT '1',
  "amount" decimal(20,4) NOT NULL,
  "consumption" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "pay_amount" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "discount" decimal(20,4) NOT NULL DEFAULT '0.0000',
  "order_at_day" date NOT NULL,
  "created_at" timestamp NULL DEFAULT NULL,
  "updated_at" timestamp NULL DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "order_orders_oid_index" ("oid"),
  KEY "order_orders_user_id_index" ("user_id"),
  KEY "order_orders_order_at_day_index" ("order_at_day")
);

DB::table('order_orders')
    ->select(
        [
            'username',
            'nickname',
            DB::raw('count(quantity) as quantity'),
            DB::raw('sum(amount) as amount'),
            DB::raw('sum(consumption) as consumption'),
            DB::raw('sum(pay_amount) as pay_amount'),
            DB::raw('sum(discount) as discount'),

        ]
    )
    ->whereIn('user_id', [1, 2, 3, 5, 8, 11])
    ->whereBetween('order_at_day', $this->request->dateRange)
    ->groupBy(
                'username',
                'nickname',
        )
    ->cursor();

Ryan

SELECT ANY_VALUE(username), ANY_VALUE(nickname), count(quantity) as quantity, sum(amount) as amount, sum(consumption) as consumption, sum(pay_amount) as pay_amount, sum(discount) as discount FROM order_orders WHERE user_id IN (1, 2, 3, 5, 8, 11) AND order_at_day BETWEEN 'xxx' AND 'yyy' GROUP BY user_id;

EXPLAIN SELECT `username`, `nickname`, count(quantity) AS quantity, sum(amount) AS amount, sum(consumption) AS consumption, sum(pay_amount) AS pay_amount, sum(discount) AS discount FROM `order_orders` WHERE `user_id` IN (1, 2, 3, 5, 8, 11) AND `order_at_day` BETWEEN '2000-01-01' AND '2022-09-26' GROUP BY `username`, `nickname`

讨论数量: 25

wxf666

68 声望

我是讨论 MySQL 的

数据五六万，是指 order_orders 表的数量嘛？还是 user_id IN (1, 2, 3, 5, 8, 11) 的数量。。

order_orders_user_id_index 索引改成 (user_id, order_at_day) 应该能提速？（这个 order_at_day 在哪儿。。）
为嘛不能 GROUP BY user_id 呢？

2年前评论

Mumujin （楼主）

感谢老哥回复，order_orders表的数据大概是五六万。user_id IN的数量最多不超过50，第二个问题是需要直接显示username 和 nikename

wxf666 （作者）

@Mumujin 第二个问题，根据官方文档所说：

Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.
You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

你可以禁用 ONLY_FULL_GROUP_BY 后，使用如下 SQL：

SELECT username, username, ...
  FROM order_orders
 WHERE ...
 GROUP BY user_id

或者，直接使用如下 SQL 解决：

SELECT ANY_VALUE(username), ANY_VALUE(username), ...
  FROM order_orders
 WHERE ...
 GROUP BY user_id

@Mumujin ~~第一个问题，那个 order_at_day 咋来的。。~~ 噢，你修改表结构了

我还是继续原来的想法：

将 (user_id) 索引改成 (user_id, order_at_day)
然后，尝试这个 SQL？

@wxf666 感谢老哥，提升不明显。改使用 GROUP BY user_id

order_at_day 在order_orders 表的

@Mumujin 我刚才修改了评论。你尝试修改索引后，执行上面所说的那个 SQL 试试？

@wxf666 感谢老哥，效果不明显。我也有试过去掉name和nickname 所有的where条件直接userid 。但效果还是不满意

@Mumujin 你 EXPLAIN 一下？其实我感觉，这个数据量，就算扫全表，也不会太慢才对啊

@wxf666 是的，老哥。我也挺郁闷的呢。

@Mumujin 试试将表导到其他数据库里（如 PostgreSQL、SQLite），再执行那个 SQL？如果其他数据库速度很快，就是 MySQL 出问题了呗

@wxf666 好的，谢谢大哥

@wxf666 直接在工具里执行sql语句就没问题，在程序里就变慢了。

@Mumujin 瞅瞅你程序实际执行的 SQL 呗

码农先森

338 声望

explain 分析，贴出下索引的命中情况。

sql 1    SIMPLE  order_orders    NULL    index   b0a75686a91d86dfe7c53c49e6b5762c    b0a75686a91d86dfe7c53c49e6b5762c    11  NULL    86867   89.82   Using where

no_pain

课程读者 4 声望

把cursor改成get试试多久🌚

wikimo

课程读者 10 声望

用云服务么，可以用云服务来解这种题，另外OLTP、OLAP混用了么，分析类建议走OLAP就很好解了，就是稍微花点钱，不用那么折腾。

说实在，就五六万行的表，我觉得 SQLite 都很快。。

wikimo （作者）

@wxf666 理论上是的，数据量很小

huangYX

课程读者 72 声望

group by治标不治本，修改代码吧！新增一张表按维度聚合。

Yoann

见习助教 4 声望

为啥我的expain结果跟你的不一样

ysxpark

课程读者 11 声望

做个缓存,定时每分钟更新一下就行了,统计数据没必要实时,或者同步个列存储库,统计数据从列存储走,也算是近实时

半人间

课程读者 186 声望

建多一个表来存统计数据。定时同步过来

jamesZhao

见习助教 20 声望

先in 查出来在自己foreach 处理数据试试呢？

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

帮助

付费解决mysql sum 多字段统计慢的问题

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

付费解决mysql sum 多字段统计慢的问题

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录