MYSQl 优化带评论的博客文章表
我正在制作一个 mysql 表，其中将保存我的博客文章及其一些信息。我希望能够在博客文章中添加评论并将它们保存在 mysql 中。我的问题是，我应该创建一个名为 comments 的表，并让所有评论都有博客文章 id，所以我只会选择相应的评论，如下所示：
select from comments where id = blogpostid
或者我的另一个想法是将所有评论放入一个数组中，然后保存它们位于我的博客文章表中的长文本字段中，因此每一行（博客文章）都将包含其评论。
我会接受任何其他想法，但对我来说速度是第一位的。
Im making a mysql table which will be holding my blog posts and some of their information. I want to be able to add comments the the blog posts and have them saved in mysql. My question is weather I should make a table called comments and have all the comments there have the blog post id, so I would only select the corresponding comments like this:
select from comments where id = blogpostid
Or my other idea was to put all the comments in an array, and save them in a longtext field in my blog posts table, so each row(blog post) will contain its comments.
I will accept any other ideas, but for me speed comes first.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
拥有一个良好标准化的、单独的评论表是正确的事情。
有了适当的索引，它就会很好地工作。我怀疑是否会出现那种可以证明为了速度而对结构进行非规范化的合理性的流量。
Having a nicely normalized, separate comments table is the right thing.
With the proper indexes, it will work fine. I doubt whether there'll ever be the kind of traffic that would justify de-normalizing the structure for speed.
请阅读有关数据库规范化的信息，例如 (http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/)
确实，非规范化（添加冗余数据或对其进行分组）可以提高数据库的性能，但也会增加数据库的大小。
当涉及到充当博客后端的数据库时，数据完整性应该是您首要考虑的问题。
您应该将博客评论表与博客文章唯一 ID 作为主键分开，除非您需要更复杂的东西（例如多态关联等）
Please read about database normalization, for example (http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/)
It's true that denormalization (adding redundant data or group it) can improve the performance of your database, but it's also increases the size of your database.
Data integrity should be your primary concern, when it's comes to database acting as a blog back-end.
You should keep your blog comments table separate with a blog post unique-id as a primary key, unless you need something more complicated (like Polymorphic Associations, etc)
来获得完全规范化的表结构
我们可以通过两种类型的表type1 table
存储所有帖子 ID --- （所有帖子的索引）
type2 表
类型 1 表中的行数 = 类型 2 表中的行数
每个 type2 表均以帖子 id 命名
类型 2 表由其各自帖子 ID 的评论组成，
表的维护肯定会出现问题
we can have a completely normalized table structure by having 2 types of tables
type1 table
that stores all the post id --- (index for all post)
type2 table
no of rows in type1 table = number of type2 table
each type2 table is named after the post id
the type 2 table consist of the comments of its respective post id
there will surely be issues with the maintains of the tables
这是我们可以通过 xml 技术的用户解决此问题的另一种方法，
将每个帖子评论线程保存在索引 xml 文件中，
因为 xml 促进树结构 IT 有利于部分评论和重新评论
额外的好处
可以更好地管理并减少数据库查询问题的数量
使用此解决方案
未针对搜索索引进行优化
they're is an another way we can solve this problem by the user of xml technology
by saving each post comment thread in a indexed xml files
as xml promotes tree structure IT is good for part comment and 're comment
additional benefits
better management and reduces the number ou db query
problem with this solution
not optimized for search index