Mysql – How to optimize big result to sum in MySQL 5.7

I have a user consume history record table. Sometimes, the data is very big (> 1 million / day).

Now I have to calculate the table to get report data.

Here is the SQL:

    SELECT SUM(consume_num) AS consumeNum, SUM(transin_num) AS transinNum
    , CASE 
        WHEN COUNT(DISTINCT transin_user_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT transin_user_num) - 1
    END AS transinUserNum, SUM(transin_count) AS transinCount, SUM(transout_num) AS transoutNum
    , CASE 
        WHEN COUNT(DISTINCT transout_user_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT transout_user_num) - 1
    END AS transoutUserNum, SUM(transout_count) AS transoutCount, SUM(send_amount) AS sendAmount
    , CASE 
        WHEN COUNT(DISTINCT sender_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT sender_num) - 1
    END AS senderNum, SUM(send_num) AS sendNum, SUM(picked_amount) AS pickedAmount
    , CASE 
        WHEN COUNT(DISTINCT picker_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT picker_num) - 1
    END AS pickerNum, SUM(picked_num) AS pickedNum, SUM(bumped_amount) AS bumpedAmount
    , CASE 
        WHEN COUNT(DISTINCT bumper_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT bumper_num) - 1
    END AS bumperNum, SUM(bumped_num) AS bumpedNum, SUM(back_money_amount) AS backMoneyAmount
FROM (
    SELECT CASE 
            WHEN consume_item IN (
                'SEND_RED_ENVELOPE', 
                'BUMPED_SELF', 
                'LOCKED', 
                'HAPPY_RAIN_SEND', 
                'HAPPY_CARD_MATCH', 
                'HAPPY_RAIN_BUMPED_SELF', 
                'HAPPY_BOMB_SETTLEMENT_UNLOCK'
            ) THEN ABS(consume_num)
            ELSE 0
        END AS consume_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN ABS(consume_num)
            ELSE 0
        END AS transin_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN user_id
            ELSE -1
        END AS transin_user_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN 1
            ELSE 0
        END AS transin_count
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN ABS(consume_num)
            ELSE 0
        END AS transout_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN user_id
            ELSE -1
        END AS transout_user_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN 1
            ELSE 0
        END AS transout_count
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN ABS(consume_num)
            ELSE 0
        END AS send_amount
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN user_id
            ELSE -1
        END AS sender_num
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN 1
            ELSE 0
        END AS send_num
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN ABS(consume_num)
            ELSE 0
        END AS picked_amount
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN user_id
            ELSE -1
        END AS picker_num
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN 1
            ELSE 0
        END AS picked_num
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN ABS(consume_num)
            ELSE 0
        END AS bumped_amount
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN user_id
            ELSE -1
        END AS bumper_num
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN 1
            ELSE 0
        END AS bumped_num
        , CASE 
            WHEN consume_item IN (
                'RED_ENVELOPE_BACK', 
                'PICK_RED_ENVELOPE', 
                'BUMPED_OTHERS', 
                'TICK_WIN', 
                'REVOLVER_ABANDON_WIN', 
                'REVOLVER_GET_SHOT_WIN', 
                'REVOLVER_NOT_GET_SHOT_WIN', 
                'UNLOCKED', 
                'HAPPY_RAIN_BACK', 
                'HAPPY_CARD_SCRAPE'
            ) THEN ABS(consume_num)
            ELSE 0
        END AS back_money_amount
    FROM wallet_consume_record
    WHERE created_time < 1593359999999
    AND created_time >= 1593273600000
    AND app_id = 1
    AND tenant_id = 1
) a;

and I have already created an index on the created_time column.

This is the EXPLAIN plan:

+------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------+
|   id | select_type   | table                 |   partitions | type   | possible_keys                   |    key |   key_len |    ref |     rows |   filtered | Extra       |
|------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------|
|    1 | SIMPLE        | wallet_consume_record |       <null> | ALL    | wallet_consume_created_time_idx | <null> |    <null> | <null> | 15624863 |         23 | Using where |
+------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------+
1 row in set
Time: 0.031s

Here is the dataset result:

mysql root@10.244.5.27:meow_report_pro> select count(*)
                                     -> from wallet_consume_record
                                     -> where created_time < 1593359999999
                                     -> AND created_time >= 1593273600000;
+------------+
|   count(*) |
|------------|
|    1876234 |
+------------+
1 row in set
Time: 0.403s

Now, the SQL takes 300s+ seconds to completely execute and always times out in my application query. is it possible to make the SQL better to get high performance?

This is my table structure:

mysql root@10.244.5.27:meow_report_pro> SHOW CREATE TABLE wallet_consume_record;
+-----------------------+----------------+
| Table                 | Create Table   |
|-----------------------+----------------|
| wallet_consume_record | CREATE TABLE `wallet_consume_record` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `room_play_id` bigint(11) NOT NULL DEFAULT '-1' COMMENT '<E6><88><BF><E9><97><B4><E6><A0><87><E8><AF><86>',
  `consume_num` int(11) NOT NULL COMMENT '<E6><B6><88><E8><B4><B9><E9><87><91><E9><A2><9D>',
  `consume_item` varchar(512) NOT NULL DEFAULT '' COMMENT '<E6><B6><88><E8><B4><B9><E9><A1><B9><E7><9B><AE>',
  `user_id` bigint(11) NOT NULL COMMENT '<E7><94><A8><E6><88><B7><E6><A0><87><E8><AF><86>',
  `consume_time` bigint(20) NOT NULL COMMENT '<E6><B6><88><E8><B4><B9><E6><97><B6><E9><97><B4>',
  `description` varchar(2048) NOT NULL DEFAULT '' COMMENT '<E6><B6><88><E8><B4><B9><E6><8F><8F><E8><BF><B0>',
  `trans_no` bigint(64) NOT NULL COMMENT '<E4><BA><A4><E6><98><93><E6><B5><81><E6><B0><B4><E5><8F><B7>',
  `remain_num` bigint(11) NOT NULL COMMENT '<E4><BA><A4><E6><98><93><E5><90><8E><E5><89><A9><E4><BD><99><E9><87><91><E9><A2><9D>',
  `current_num` bigint(11) NOT NULL COMMENT '<E4><BA><A4><E6><98><93><E5><89><8D><E8><B4><A6><E6><88><B7><E4><BD><99><E9><A2><9D>',
  `current_status` int(11) NOT NULL COMMENT '<E5><BD><93><E5><89><8D><E4><BA><A4><E6><98><93><E7><8A><B6><E6><80><81><EF><BC><88>1<E3><80><81><E4><BA><A4><E6><98><93><E5><AE><8C><E6><88><90><EF><BC><89>',
  `pay_type` int(11) NOT NULL DEFAULT '1' COMMENT '<E6><94><AF><E4><BB><98><E6><96><B9><E5><BC><8F>(1.<E5><B9><B3><E5><8F><B0><E5><85><85><E5><80><BC><E6><94><AF><E4><BB><98>)',
  `app_mark` varchar(128) DEFAULT 'UNKNOWN' COMMENT '<E5><BA><94><E7><94><A8><E6><9E><9A><E4><B8><BE><E5><90><8D><E7><A7><B0>',
  `tenant_id` bigint(20) NOT NULL,
  `created_time` bigint(20) NOT NULL COMMENT '<E5><88><9B><E5><BB><BA><E6><97><B6><E9><97><B4>',
  `updated_time` bigint(20) NOT NULL COMMENT '<E6><9B><B4><E6><96><B0><E6><97><B6><E9><97><B4>',
  `device` int(11) NOT NULL DEFAULT '-1' COMMENT '<E8><AE><BE><E5><A4><87><E7><B1><BB><E5><9E><8B>',
  `app_id` bigint(20) DEFAULT '-1' COMMENT '<E5><BA><94><E7><94><A8><E6><A0><87><E8><AF><86>',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `trans_no` (`trans_no`),
  KEY `index_consume_num` (`consume_num`) USING BTREE,
  KEY `index_consume_time` (`consume_time`) USING BTREE,
  KEY `index_user_id` (`user_id`) USING BTREE,
  KEY `index_tenant_id` (`tenant_id`) USING BTREE,
  KEY `wallet_consume_created_time_idx` (`created_time`)
) ENGINE=InnoDB AUTO_INCREMENT=18925265 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='<E9><87><91><E9><A2><9D><E6><B6><88><E8><B4><B9><E6><98><8E><E7><BB><86>'                |
+-----------------------+----------------+
1 row in set
Time: 0.003s

by the way, the app_id only have 6 value: 1,2,3,4,5 and tenant_id only have 3 value 1,2,3 and consume_item have 10 value of string.

Best Answer

What you should do is narrow the search even further by making a decent covering index

Please create the following index

ALTER TABLE wallet_consume_record
    ADD INDEX everything_and_the_kitchen_sink_ndx
    (app_id,tenant_id,created_time,consume_num,consume_item,user_id)
;

This index will contain all the columns needed in the subquery. That way, all the needed data comes from the index and the table is never touched. Also note that I placed the columns app_id and tenant_id in the front of the index to narrow the search, which will minimize the range of created_time.

So, proceed to create this index, generate the EXPLAIN plan, and see if things improve.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Related Question