MySQL多个子查询-慢排序

Question

知道如何优化这个查询吗？

当使用ORDER BY (~10秒)子句时，它运行得非常慢。如果删除order_by子句，则执行时间为毫秒。

问题是，我需要能够使用子查询的结果来排序结果。需要能够按：num_products，credits_ordered，credits_consumed，num_refunds，amount_refunded，last_uploaded_date和last_order_date订购。

查询：

SELECT DISTINCT `users`.`id` as `user_id`, `users`.`email` as `user_email`, 

(select count(products.id) from products where products.user_id = users.id and products.added >= '2021-09-29' and products.added < '2021-10-29') as num_products, 

(select COALESCE(sum(amount), 0) from credits_history where credits_history.user_id = users.id and credits_history.type = 1 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') as credits_ordered, 

(select COALESCE(sum(amount), 0) from credits_history where credits_history.user_id = users.id and credits_history.type = 2 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') as credits_consumed, 

COALESCE(sum((select count(id) from credits_history where credits_history.user_id = users.id and credits_history.type = 3 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0)
+
COALESCE(sum((select count(id) from credits_history where credits_history.user_id = users.id and credits_history.type = 4 and credits_history.amount > 0 and credits_history.order_id IS NULL and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') ), 0) as num_refunds, 

COALESCE(sum((select sum(amount) from credits_history where credits_history.user_id = users.id and credits_history.type = 3 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0) 
+
COALESCE(sum((select sum(amount) from credits_history where credits_history.user_id = users.id and credits_history.type = 4 and credits_history.amount > 0 and credits_history.order_id IS NULL and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0) as amount_refunded, 

(select added from products where products.user_id = users.id order by id desc limit 1) as last_uploaded_date, 
(select added from credits_history where credits_history.user_id = users.id and credits_history.type = 1 order by credits_history.id desc limit 1) as last_order_date
FROM `users`
WHERE `users`.`is_subuser` =0
GROUP BY `user_id`
ORDER BY `num_products` DESC
LIMIT 0,10

数据库模式：

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `is_subuser` tinyint(1) DEFAULT '0',
  `customer_type` tinyint(1) DEFAULT '1',
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `is_subuser` (`is_subuser`)
) ENGINE=InnoDB AUTO_INCREMENT=25006 DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `is_user_id_is_deleted` (`id`,`user_id`,`is_deleted`),
  KEY `user_id_is_deleted` (`user_id`,`is_deleted`),
  KEY `id_is_deleted` (`id`,`is_deleted`),
  KEY `added` (`added`)
) ENGINE=InnoDB AUTO_INCREMENT=196921 DEFAULT CHARSET=utf8;


CREATE TABLE `credits_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `credits_type` tinyint(1) NOT NULL,
  `order_id` bigint(11) DEFAULT NULL,
  `added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_id_credits_type_added` (`user_id`,`credits_type`,`added`),
   KEY `order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=176204 DEFAULT CHARSET=utf8;

解释：

        +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+
    | id | select_type        | table           | partitions | type  | possible_keys                                                                   | key                        | key_len | ref            | rows | filtered | Extra                                                  |
    +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+
    |  1 | PRIMARY            | users           | NULL       | range | PRIMARY,can_login,is_subuser_master_id,is_subuser,magento_customer_id,master_id | is_subuser                 | 2       | NULL           | 6720 |   100.00 | Using index condition; Using temporary; Using filesort |
    | 10 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_credits_type_added | 4       | func           |   85 |    10.00 | Using index condition; Using where; Using filesort     |
    |  9 | DEPENDENT SUBQUERY | products        | NULL       | ref   | user_id_is_deleted                                                              | user_id_is_deleted         | 4       | func           |   79 |   100.00 | Using index condition; Using filesort                  |
    |  8 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id,order_id                    | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.06 | Using index condition; Using where                     |
    |  7 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.11 | Using index condition; Using where                     |
    |  6 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id,order_id                    | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.06 | Using index condition; Using where                     |
    |  5 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.11 | Using index condition; Using where                     |
    |  4 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | func           |   90 |     0.11 | Using index condition; Using where                     |
    |  3 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | func           |   90 |     0.11 | Using index condition; Using where                     |
    |  2 | DEPENDENT SUBQUERY | products        | NULL       | ref   | user_id_is_deleted,added                                                        | user_id_is_deleted         | 4       | func           |   79 |     0.93 | Using index condition; Using where                     |
    +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+

为了简单起见，我从表模式中删除了一些字段/索引，因为表中有很多字段。

Answer 1

我怀疑是否有理由在同一个查询中同时使用DISTINCT和GROUP BY。

让我们把查询往内转。然而，这将增加复杂性--也就是说，您需要对每个不同的ORDER BYs进行不同的查询。

目前，这个查询就是我所说的“爆炸-内爆”。它会击中许多表中的许多行，但后来将结果归纳为10行。我们需要尽量减少为这10行查找ids的工作量，然后执行所有其他工作。

要旨是

SELECT ids.user_id,
       ids.num_products,
       ( ... ) AS credits_ordered,
       ( ... ) AS ...,
       ( ... ) AS ...,
       COALESCE( ... ) AS ...,
       COALESCE( ... ) AS ...
    FROM (
        SELECT users.id AS user_id
            FROM `users`, num_products
            JOIN .... (( to get num_products ))
            GROUP BY users.id
            ORDER BY `num_products` DESC
            LIMIT 0,10
         ) AS ids
    ORDER BY ids.num_products DESC

id是，可能需要触摸许多行才能获得ids子查询，但只有10行才能得到其他子查询。

另一种方法(可能更好，也可能更坏)：

其余的一切似乎都依赖于

            from  credits_history
            where  credits_history.user_id = users.id
              and  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'

(加上对type和amount的测试)

所以，试着用

   JOIN ( SELECT user_id, type, amount, credits_history
            FROM credits_history
            WHERE  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'
        ) AS ch
      ON  ch.user_id = users.id

然后将type和amount放在子查询中。

第三个想法是做类似的事情(假设你使用的是8.0或10.2)

WITH ch AS
    ( SELECT user_id, type, amount, credits_history
            FROM credits_history
            WHERE  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'
    )

同时，其中一些可能会有所帮助：

users:  INDEX(is_subuser,  id, email)
products:  INDEX(user_id, added,  id)
credits_history:  INDEX(user_id, credits_type, type, added)
credits_history:  INDEX(user_id, type, amount, order_id, credits_type, added)

清理一些索引：

  KEY `user_id` (`user_id`),  -- Drop because redundant with the following:
  KEY `user_id_credits_type_added` (`user_id`, `credits_type`, `added`),

  KEY ... (id, ...)  -- Drop because redundant with PRIMARY KEY(id)

通常，使用COUNT(*)而不是COUNT(id)。

Answer 2

您的解释计划看起来像是从credits_history表读了7遍。一般来说，如果你能帮忙的话，最好只从桌子上读一遍。多读肯定会给查询增加大量的运行时间。

我会尝试重写它，以便您只读一次从credits_history。使where子句足够通用，可以同时读取所有组中需要的所有行。然后用这些行在事实之后进行条件求和。

就像这样。(我正在使用Postgresql，因此语法可能会略为偏离)

select
  c.result_one,
  c.result_one
  ...
from 
`users` u
left join lateral (
  select
    sum(case when c.history_type = 1 then c.amount else 0 end) as result_one,
    sum(case when c.history_type = 2 and c.some_other_thing = 'foo' then c.amount else 0) as result_two,
  from `credits_history` c
  where 
    c.user_id = u.id
    ...--set the rest of the where clause
       --such that you read all credits_history rows that will be included anywhere in your dataset
) c on true
--and your other joins as they make sense results. make sure it could use indexes if appropriate.
;