mysql - 使用子查询优化 mysql 查询

Question

下面是 MySQl 查询，我可以用它得到不满意的结果

但是有什么方法可以优化查询

SELECT users.*,
    (SELECT country_name FROM country WHERE country_code = users.country_code)
        AS country_name,
    (SELECT zone_name FROM timezone WHERE timezone_id = users.timezone_id)
        AS zone_name,
    (SELECT GROUP_CONCAT(list_name)
     FROM list LEFT JOIN user_list ON user_list.list_id = list.list_id
     WHERE user_list.user_id = users.user_id AND user_list.status = "active")
        AS groups,
    (SELECT GROUP_CONCAT(promotion_name)
     FROM promotion LEFT JOIN promotion_user ON promotion_user.promotion_id = promotion.promotion_id
     WHERE promotion_user.user_id = users.user_id AND promotion_user.status = "active")
         AS promotions,
    (SELECT GROUP_CONCAT(full_name)
     FROM users u LEFT JOIN promotion_user ON promotion_user.promotor_id = u.user_id
     WHERE promotion_user.user_id = users.user_id AND promotion_user.status = "active")
         AS promotors
FROM users WHERE client_id = '2' AND status != 'deleted'
ORDER BY user_id desc
LIMIT 50 OFFSET 0

解释输出是

                                                possible        key   id  select_type         table           type    _keys   key     _len    ref     rows    Extra1   PRIMARY             users           index   NULL    PRIMARY 4       NULL    1045612 Using where6   DEPENDENT SUBQUERY  promotion_user  ALL     NULL    NULL    NULL    NULL    16159   Using where6   DEPENDENT SUBQUERY  u               eq_ref  PRIMARY PRIMARY 4       [1]     1       NULL5   DEPENDENT SUBQUERY  promotion_user  ALL     NULL    NULL    NULL    NULL    16895   Using where5   DEPENDENT SUBQUERY  promotion       ALL     PRIMARY NULL    NULL    NULL    4       Using where; Using join buffer (Block Nested Loop)4   DEPENDENT SUBQUERY  list            ALL     PRIMARY NULL    NULL    NULL    1592    NULL4   DEPENDENT SUBQUERY  user_list       ALL     NULL    NULL    NULL    NULL    159852  Using where; Using join buffer (Block Nested Loop)3   DEPENDENT SUBQUERY  timezone        eq_ref  PRIMARY PRIMARY 4       [2]     1       NULL          2   DEPENDENT SUBQUERY  country         ALL     NULL    NULL    NULL    NULL    239     Using where                                                                        [1] test.promotion_user.promoter_id                                                                        [2] test.promotion_user.promoter_id

Answer 1

我会尝试使用不相关的子查询。但是，由于您只返回单个用户的详细信息(因此可能只有一行)，这可能无济于事。除了可能消除一个子查询之外。

类似这样的东西(未经测试，因为没有数据定义或数据示例)

SELECT `users`.*,
    country.country_name,
    timezone.zone_name,
    sub_groups.groups,
    sub_promotors.promotions,
    sub_promotors.promotors
FROM `users`
INNER JOIN country
ON country.country_code = users.country_code
INNER JOIN timezone
ON timezone.timezone_id = users.timezone_id
INNER JOIN 
(
    SELECT promotion_user.user_id, GROUP_CONCAT(full_name) AS promotors, GROUP_CONCAT(promotion_name) AS promotions
    FROM users u
    LEFT JOIN promotion_user ON promotion_user.promotor_id = u.user_id
    WHERE promotion_user.status = "active"
    GROUP BY promotion_user.user_id
) AS sub_promotors
ON sub_promotors.user_id = users.user_id
INNER JOIN 
(
    SELECT user_list.user_id, GROUP_CONCAT(list_name) AS groups
    FROM list
    LEFT JOIN user_list ON user_list.list_id = list.list_id
    WHERE user_list.status = "active"
    GROUP BY user_list.user_id
) AS sub_groups
ON sub_groups.user_id = users.user_id
WHERE users.client_id = '2'
AND users.status != 'deleted'
ORDER BY users.user_id 
DESC LIMIT 50 OFFSET 0

相关子查询有效地强制 MySQL 为每个返回的行执行一次。将这些更改为连接的非相关子查询意味着它们可以对所有返回的行执行一次。不利的一面是，就 MySQL 中的索引而言，连接到子查询的优化很差。

如果发起人全名等是唯一的，您可以删除子查询。