论坛项目MySQL分析优化

论坛项目MySQL分析优化

根据功能分类优化

• 主页展示板块，以及板块的最后修改时间的一条帖子

select a.bid,bname,parentid,total ,tid ,title , date_format(modifytime,'%Y-%m-%d %H:%I:%S') as recentmodifytime ,uname from 
 (select board.bid,bname,parentid,count(tid) as total FROM board left join topic on board.bid=topic.bid GROUP BY board.bid,bname,parentid)a 
 left join
 (select tid,title,a.modifytime,uname,a.bid from 
(select bid,tid,title,modifytime,uname from topic left join user on topic.uid=user.uid )a,
(select bid,max(modifytime) as modifytime from topic GROUP BY bid )b 
 where a.bid=b.bid and a.modifytime =b.modifytime)b on a.bid=b.bid ;

使用expain查看执行计划

可以看到问题相当多，有很多派生表，以及大量的全表查询。

//暂时不知道怎么解决，建议：

1. 参考别的论坛，只展示板块名称（可选是否将子模块同时查询出来）
2. 用redis做缓存
3. 对于展示的帖子，参考feed流？提前做好要展示哪些的准备然后一起放到feed流中

• 点板块查看贴子

下面是查看板块帖子的sql语句

explain select b.*,a.total from ( 
select replay.tid,count(*) as total from replay left join topic
on topic.tid=replay.tid group by replay.tid ) a 
right join ( 
select tid,title,content,DATE_FORMAT(publishtime,'%Y-%m-%d %H:%i') as publishtime, 
DATE_FORMAT(modifytime,'%Y-%m-%d %H:%i' ) as modifytime,t.uid,b.bid,bname, 
parentid,uname,upwd,head,DATE_FORMAT(regtime,'%Y-%m-%d %H:%i')as regtime,gender from topic t 
left join board b on t.bid=b.bid 
left join user u on u.uid=t.uid ) b 
on a.tid=b.tid
order by b.modifytime desc
limit ?,?

使用的派生表和all全表扫描特别多，于是我们考虑分成两部分来做

EXPLAIN select tid,title,content,publishtime,modifytime,  user.uid,  uname,bid
from topic
 inner join user
on topic.uid=user.uid
 where bid=?
order by modifytime desc 
 limit 0,2 ;

我们在优化前的topic 帖子表中建立了两个外键索引

而用explain 后效果是

很明显第二行用的user表主键索引，type是eq-ref（主键或者唯一索引）这个没问题。

但是我们发现第一行可以优化

主要就在于Extra字段的Using filesort(使用了文件排序)

我们考虑到项目中topic时间经常用于排序，但又主要是需要根据板块分类进行排序，不相同的板块的帖子不需要排序，于是我们把索引修改如下：

这样再使用expain的效果就为

不再进行文件排序了

上面的sql语句虽然查出了帖子和作者信息，但是没有查出评论数

有两个办法，一个用mybatis子查询完成，将tid去一个一个做一对一的查询查出评论数量

第二个是再关联查表
建立索引之前
建立索引后

一个是文件排序没有了，另外也走了索引

• 点板块查看帖子详情以及评论

我觉得和上面差不多，就不分析了。

总结

在哪些情况下要建立索引？

• 主键，外键都需要索引，因为经常要用到where语句中
• 排序字段可选择建立索引
• 统计或者分组字段
• 在单键和组合索引中做出选择（在高并发的情况下优先组合索引？？也许是为了节省io）