1

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.

  • From your EXPLAIN, (key), no index is updated though your created index is possible. What portion of the table does the data range indicated correspond to? Do the range of consume_item values in the query represent the entire set? If no appending consume_item to the index may help. Include SHOW CREATE TABLE wallet_consume_record in your question. An instantaneous answer to looking at 1M rows may be asking too much. Your query looks like its trying to format the answer too much rather than just deliver the sums. – danblack Jun 30 at 3:38
  • You may try to add another nesting if explain shows that the COUNTS are evaluated twice: SELECT consumeNum, transinNum , CASE WHEN transinUserNum < 0 THEN 0 ELSE transinUserNum END AS transinUserNum , ... FROM ( SELECT SUM(consume_num) AS consumeNum , SUM(transin_num) AS transinNum , COUNT(DISTINCT transin_user_num) - 1 as transinUserNum .... – Lennart Jun 30 at 10:34
1

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.

| improve this answer | |
  • I don't know why the consume_num should add a index. it is the user spent money value,it does not in the where clause. @RolandoMySQLDBA – Dolphin Jul 1 at 1:54
  • 1
    Hey @Dolphin The extra columns (consume_num,consume_item,user_id) should be in the index so that mysqld does not need to consult the table for data. It will already be available when accessing the index. Good covering indexes can go beyond satisfying WHERE, GROUP BY and ORDER BY clauses. – RolandoMySQLDBA Jul 1 at 14:21
0

Simplifications

CASE 
    WHEN COUNT(DISTINCT transin_user_num) - 1 < 0 THEN 0
    ELSE COUNT(DISTINCT transin_user_num) - 1
END AS transinUserNum

can be simplified to

GREATEST(COUNT(DISTINCT transin_user_num) - 1, 0)  AS transinUserNum

and...

CASE 
    WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN 1
    ELSE 0
END AS bumped_num

to

consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF')  AS bumped_num

The second one is because true and false is represented as 1 and 0.

Missing millisecond

WHERE created_time < 1593359999999  -- use <=

Better INDEX

WHERE created_time <= 1593359999999
AND created_time   >= 1593273600000
AND app_id = 1
AND tenant_id = 1

needs

INDEX(tenant_id, app_id, created_time)

At the same time toss index_tenant_id because it will be redundant.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.