SQL 统计用户 登录次数、浏览商品次数、红包数

数据库版本 MySql 5.6.33

业务背景

用户列表查询需要

数据量

• ty_user_info 2W
• ty_user_login_record 30W
• ty_user_browse_record 80W
• ty_user_coupon 15W

同时 3张表都建好了 user_id 的索引列并且生效

sql 实现

第一种

注意这里没有去重，因为去重查询是在太慢了。(这种方式也是极其不推荐的，因为 left join + group by 数据量已经极其庞大了)

SELECT
	ui.user_id,
	ui.nickname,
	COUNT(login.user_info_id) login_count,
	COUNT(browse.sku_id) browse_sku_count,
	COUNT(coupon.user_coupon_id) coupon_count
FROM
	ty_user_info ui
	LEFT JOIN ty_user_login_record login ON login.user_info_id = ui.user_id 
	LEFT JOIN ty_user_browse_record browse On browse.user_info_id = ui.user_id
	LEFT JOIN ty_user_coupon coupon ON coupon.user_id = ui.user_id
GROUP BY
	ui.user_id 
ORDER BY
	ui.user_id 
	LIMIT 10;
	
# Handler_read_key 12 
# Handler_read_next 6978 

# Handler_read_key 6982  
# Handler_read_next 10436787

# Handler_read_key 10436792 
# Handler_read_next 119232422

第二种(SELECT + SUBQUERY)

如果业务没有将聚合值(登录数排序、过滤等)作为查询条件，推荐这种。

SELECT
	ui.user_id,
	ui.nickname,
	( SELECT COUNT( 1 ) FROM ty_user_login_record WHERE user_info_id = ui.user_id ORDER BY user_info_id) login_count,
	( SELECT COUNT( 1 ) FROM ty_user_browse_record WHERE user_info_id = ui.user_id ORDER BY user_info_id) browse_sku_count,
	( SELECT COUNT( 1 ) FROM ty_user_coupon WHERE user_id = ui.user_id ORDER BY user_id) coupon_count
FROM
	ty_user_info ui 
ORDER BY
	ui.user_id 
	LIMIT 10;
	
# Handler_read_key 11 
# Handler_read_next 6977

# Handler_read_key 21 
# Handler_read_next 14347

# Handler_read_key 31
# Handler_read_next 14398

第三种(提前 GROUP BY + LEFT JOIN)

可以实现根据聚合值过滤查询。(但是由于是先将表数据聚合 然后再 LEFT JOIN 其实在 ty_user_info 量提起来后也会非常糟糕)

SELECT
	ui.user_id,
	ui.nickname,
	login.login_count,
	browse.browse_count,
	coupon.coupon_count
FROM
	ty_user_info ui
	LEFT JOIN ( SELECT user_info_id, COUNT( user_info_id ) login_count FROM ty_user_login_record GROUP BY user_info_id ) login ON login.user_info_id = ui.user_id 
	LEFT JOIN ( SELECT user_info_id, COUNT( sku_id ) browse_count FROM ty_user_browse_record GROUP BY user_info_id) browse ON browse.user_info_id = ui.user_id
	LEFT JOIN ( SELECT user_id, COUNT( user_id ) coupon_count FROM ty_user_coupon GROUP BY user_id) coupon ON coupon.user_id = ui.user_id
ORDER BY
	ui.user_id 
	LIMIT 10;

# Handler_read_key 12	 
# Handler_read_next 281607

# Handler_read_key 23	 
# Handler_read_next 1060953

# Handler_read_key 34	 
# Handler_read_next 1213963

总结

这三种方式比较

• LEFT JOIN + GROUP BY 效率极低，因为LEFT JOIN 是 ref 触发了范围查询，GROUP BY 又强行进行全表级别的查询，虽然是走了索引执行计划也非常完美但是实际执行效果最差。
• SELECT + SUBQUERY 效率极高，虽然还是 ref 的范围查询因为限制了查询行数所以效果非常棒，但是计算总页数的sql 要重新写。
• 提前 GROUP BY + LEFT JOIN 效率取决于 ty_user_info 的数据量，同时因为是提前GROUP BY 还可以将聚合值作为条件过滤，项目前期赶进度完全可以使用这种方式(注意仅适用于项目前期，哎相当于给屎山加一坨的操作)。

单纯依靠MySql 正确做法应该是 定时任务+宽表 的方式双写数据，然后查询宽表得到结果。如果是新项目或时间充裕还好，但是实际工作中太多时候接手就是个烂摊子，为了在DEADLINE前完成功能只能用一些恶心自己的路数先完成功能。

总结：MySql 是非常不适合用sql 各种组装然后查询这种数据的。实际这种都是先把数据同步到数据仓库，然后在查询。