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

  显示原文与译文双语对照的内容
0 0

下面是MySQl查询,我可以得到disired结果

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


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 Extra
1 PRIMARY users index NULL PRIMARY 4 NULL 1045612 Using where
6 DEPENDENT SUBQUERY promotion_user ALL NULL NULL NULL NULL 16159 Using where
6 DEPENDENT SUBQUERY u eq_ref PRIMARY PRIMARY 4 [1] 1 NULL
5 DEPENDENT SUBQUERY promotion_user ALL NULL NULL NULL NULL 16895 Using where
5 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 NULL
4 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
时间: 原作者:

0 0

我将尝试使用非关联的子查询。 然而,因为你只返回单个用户( 因此可能只有一行)的详细信息,这可能没有帮助。 超越了一个子查询。

类似于这样( 未测试数据定义或者数据示例)


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的索引中加入到子查询的优化方式差不多。

如果启动全名等于唯一,则可以能可以删除子查询。

...