数据库性能优化 - mysql 中根据文章中的多个关键字来匹配其他文章的sql语句优化？

Question

我有三个表是这样的，： 文章表 article：

关键字表 tag：

关键字与文章的关联表 article_tag：

要求： 给定一个文章的id（article_id） 通过关键字获取其他的文章并根据关联度排序！ 我些的sql是这样的：

select article_id,count(*) as count from article_tag where tag_id in(select tag_id from article_tag where article_id=1) group by article_id order by count desc 

但是对于article_tag 表在将近20w 的记录时 查询速度在3s多！ 大家有没有什么还的优化建议(sql语句优化，表结构的优化，索引添加.....)

Answer 1

1. article表可考虑增加一列tag_ids做冗余，这样就省略了一个in的子查询。这个子查询如果在表article_tag的article_id列上没有索引的情况，效率很低。 > select tag_id from article_tag where article_id=1
2. 如果tag比较多的话，再增加article_tag的tag_id列索引，提高根据tag_id查询的效率。

针对你的需求，仅仅就sql查询的优化，差不多能想到的就这么多！在数据量不大的情况下(你列举的20w的级别)，应该能够解决。下面说一些数据库之外的优化：

• 首先，从应用需求的角度，关联度排序后的文章列表完全不必全部展示，也不必每次都实时查询数据库，因为这个关联列表对更新频率的容忍度比较高，这样的话，就可以冗余存储关联文章列表，例如每个文章只需存储关联度最高的10条记录：这方面segmentfault中每个问题右边栏的相关问题就是个很好的示例。至于实现方式：可简单考虑在表article增加一列most_related_article_ids，然后定时执行SQL去更新表article的这个字段；
• 其次，从应用实现的角度，上述每个文章的关联列表可能根据相同标签的个数，相同标签的权重，不同标签的关联程度来计算，这是有可能的后续需求。很显然这是一个复杂的算法实现，不是一个简单的SQL能够实现，前面提到的冗余存储关联文章列表就可以很好的解决这个问题，原先的定时SQL执行更新逻辑，可以交给程序来完成。
• 最后，如果这样还是存在效率的话，可以考虑缓存方案。(PS: 不建议一开始就考虑缓存，毕竟缓存方案会引入数据不一致、缓存失效和缓存更新策略等一系列新的问题，在简单的应用场景下，得不偿失)