一次join查询优化的历程

以下是获取关注用户的已通过审核，content_type ==2 帖子的sql ;并需要根据点赞数和评论数排序

SELECT t1.*,t2.comment_total,t2.like_total,t2.collect_total,t3.nickname,t3.avater,t4.name from posts AS t1 
# 连接 帖子统计信息获取点赞，评论数
LEFT JOIN post_statistics as t2 on t1.id = t2.post_id 
# 连接 用户信息
INNER JOIN users as t3 on t3.id = t1.user_id
# 连接帖子类别
INNER JOIN post_cates as t4 on t4.id = t1.cate_id
# 连接用户关注
INNER JOIN user_followed as t5 on t5.followed_user_id = t1.user_id
# 帖子content_typ = 2, audit_status = 1
WHERE  t1.content_type = 2 AND t1.audit_status = 1 
# user_id 的关注用户的帖子
WHERE t5.user_id = :user_id AND t5.followed_type >= 1
# 根据点赞 评论排序
ORDER BY t2.likes DESC,t5.comment_total DESC
limit(0,15);

以上sql 存在5张表的连接，因笛卡尔积生成的临时表数据量巨大；我们通过以下几个点来优化

1. 剔除非必要连接
   users 与post_cates 为非必要关联，我们可以剔除
2. 通过子查询来优化

SELECT
	t2.*,t5.comment_total,t5.like_total,t5.collect_total
FROM
	( SELECT id,cate_id,user_id,content FROM posts WHERE content_type = 2 AND audit_status = 1 AND posts_status = 1 ) AS t1
	INNER JOIN 
	# 关注 用户 子查询
	( SELECT followed_user_id FROM user_followed WHERE user_id = :user_id AND followed_type >= 1 ) AS t1 ON t1.user_id = t2.followed_user_id
	LEFT JOIN
	(SELECT post_id,comment_total,likes FROM post_statistics) as t3 ON t3.post_id = t1.id
	ORDER BY t3.like_total DESC,t3.comment_total DESC
	limit(0,15);

通过子查询找到对应的关联零时表的数据少很多，同时也进行了字段筛选；

通过以上数据到具体帖子id 和user_id 用IN来查询帖子分类和用户信息(帖子id和user_id 只有分页条数，查询迅速)；

深度分页优化

其实上面的查询还存在深度分页性能问题，其原理大家可以百度，其核心思想就是减少回表次数来优化；

SELECT
	t2.*,t5.comment_total,t5.like_total,t5.collect_total
FROM
	( SELECT id,cate_id,user_id,content FROM posts WHERE content_type = 2 AND audit_status = 1 AND posts_status = 1 ) AS t1
	INNER JOIN 
	# 关注 用户 子查询
	( SELECT followed_user_id FROM user_followed WHERE user_id = :user_id AND followed_type >= 1 ) AS t1 ON t1.user_id = t2.followed_user_id
	INNER JOIN
	(SELECT post_id,comment_total,likes FROM post_statistics ORDER BY like_total DESC,collect_total DESC,comment_total DESC LIMIT 100021,15) as t3 ON t3.post_id = t1.id
	ORDER BY t3.like_total DESC,t3.comment_total DESC	

其上主要修改 LEFT JOIN -> INNER JOIN 把分页作为子查询关联

执行计划

执行查询：先通过post_statistics，查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表

未优化前查询4000条以后数据

优化后

相差近一半的时间