mysql - 选择从 Mysql 获取帖子评论数的最佳方式

Question

我有一个关于在数据库中选择表设计的偏好的问题。
首先，我有三个表，分别用于用户、帖子、评论
此架构显示表格:
此用户架构:

    CREATE TABLE `Users` (
      `id` int(11) NOT NULL,
      `fullname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
      `profileImg` varchar(255) DEFAULT NULL,
      `appPackage` varchar(50) DEFAULT NULL,
      `email` varchar(255) NOT NULL,
      `token` text NOT NULL,
      `gender` enum('male','female') DEFAULT NULL,
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  ALTER TABLE `Users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

此帖子架构:

    CREATE TABLE `Posts` (
      `id` int(11) NOT NULL,
      `userId` int(11) NOT NULL,
      `text` varchar(255) COLLATE utf8mb4_bin NOT NULL,
      `background` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
      `img` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `country` varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
      `countryCode` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
      `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `CommentsCount` int(11) NOT NULL,
      `likes` int(11) NOT NULL DEFAULT '0',
      `views` int(11) NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

   ALTER TABLE `Posts`
     ADD PRIMARY KEY (`id`),
     ADD KEY `users_post_userId_fk` (`userId`);

此评论架构:

CREATE TABLE `Comments` (
  `id` int(11) NOT NULL,
  `postId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `commentText` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `users_comments_userId_fk` (`userId`),
  ADD KEY `users_comments_postId_fk` (`postId`);

我想查询 Posts 表，每个查询都会带来 10 行，其中包含每个帖子的评论数。
哪个是最好的选择？
通过连接查询 Posts 表以获取评论数量，如下所示:

SELECT * ,
       (SELECT COUNT(*)
        FROM Comments
        WHERE Comments.postId = Posts.id) AS commentsCount
FROM Posts
ORDER BY Posts.id DESC
LIMIT 0,10

或者在 posts 表中创建一个列 commentsCount，其中包含每个帖子的评论数。

并创建一个链接到评论表的触发器，其中插入新评论时，commentsCount 中的数字会增加一个数字，而当删除评论时，commentsCount 中的数字会减少一个数字。

该查询仅变成一个帖子表来获取帖子和评论计数:

SELECT * FROM Posts ORDER BY Posts.id DESC LIMIT 0,10

提前致谢!

Answer 1

我建议不要将派生信息存储在 post 表中。这将违背标准化最佳实践(并且维护起来会很痛苦)。

有关评论的信息属于其自己的表。每当您需要计算每个帖子的评论数时，您可以按照您的建议使用相关子查询:

SELECT 
    p.* ,
       (SELECT COUNT(*) FROM Comments c WHERE Comments.postId = Posts.id) AS commentsCount
FROM Posts p
ORDER BY p.id DESC
LIMIT 0,10

但是，如果您正在处理大量帖子，这可能会变得效率低下，因此您还可以通过对 Comments 进行聚合查询来加入 Posts 表 像这样:

SELECT p.*, COALESCE(c.commentsCount, 0) commentsCount
FROM Posts p
LEFT JOIN (
    SELECT postId, COUNT(*) commentsCount
    FROM Comments
    GROUP BY postId
) c ON c.postId = p.id
ORDER BY p.id DESC
LIMIT 0,10