MySQL用户阻塞查询慢社会化网络设计

Question

我在一个小型的社交网络上工作，但我正在努力优化设计中的阻塞部分。我有三个桌子阻塞，饲料和用户。它工作得很好，但是当我添加阻塞时，查询从16 in或更低到超过50 in或更高，对于一些简单的东西来说，这似乎太多了。事实上，在不阻塞hedisql sql的情况下，查询时间有时报告为0ms，因为我猜它低于一定的时间。

阻塞的想法是，如果用户阻止其他人，他们将无法看到他们的提要帖子，被屏蔽的人将无法看到拦截器。

我正在努力优化的部分是：左联接阻塞blocking.blockerId = feed.userId和blocking.blockedId = '3‘OR blocking.blockerId = '3’和blocking.blockedId = feed.userId，其中blocking.blockerId为NULL，blocking.blockedId为NULL

有什么方法可以在不改变数据的情况下改进这个查询吗?或者它是否尽可能好呢？

我试图优化的查询：

SELECT users.displayName, feed.id, feed.userId, feed.message
FROM feed
INNER JOIN users ON feed.userId = users.id
LEFT JOIN blocking ON blocking.blockerId = feed.userId AND blocking.blockedId = '3' OR blocking.blockerId = '3' AND blocking.blockedId = feed.userId
WHERE blocking.blockerId IS NULL AND blocking.blockedId IS NULL
ORDER BY feed.id DESC
LIMIT 20;

阻塞表和键：

CREATE TABLE IF NOT EXISTS `blocking` (
  `blockerId` int(11) NOT NULL,
  `blockedId` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`blockedId`,`blockerId`) USING BTREE,
  KEY `userId` (`blockerId`,`blockedId`) USING BTREE,
  CONSTRAINT `blockedId` FOREIGN KEY (`blockedId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `blockerId` FOREIGN KEY (`blockerId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

饲料表：

CREATE TABLE IF NOT EXISTS `feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `timestamp` (`timestamp`),
  CONSTRAINT `userIdCas` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

用户表：

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `displayName` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
) 

解释而不阻塞：

​

用阻塞解释：

​

我正在调整的更新查询运行速度似乎要快得多：

FROM feed
FORCE INDEX (PRIMARY)
INNER JOIN users ON feed.userId = users.id
LEFT JOIN blocking ON blocking.blockerId = feed.userId AND blocking.blockedId = '3' OR blocking.blockerId = '3' AND blocking.blockedId = feed.userId
WHERE blocking.blockerId IS NULL AND blocking.blockedId IS NULL
ORDER BY feed.id DESC
LIMIT 20;

有谁能解释一下为什么这会运行得更好，为什么MySQL一开始就不使用索引？

(从注释中)我将查询更改为

SELECT  users.displayName, feed.id, feed.userId, feed.message
    FROM  feed FORCE INDEX (PRIMARY)
    INNER JOIN  users  ON feed.userId = users.id
    LEFT JOIN  blocking  ON blocking.blockerId = feed.userId
      AND  blocking.blockedId = '3'
      OR  blocking.blockerId = '3'
      AND  blocking.blockedId = feed.userId
    WHERE  blocking.blockerId IS NULL
      AND  blocking.blockedId IS NULL
    ORDER BY  feed.id DESC
    LIMIT  20;

而且它似乎运行得更好，谁能给我一个更深的理解为什么？

Answer 1

您应该在OR条件下使用()，否则就会得到不满意的结果。

SELECT users.displayName
  , feed.id
  , feed.userId
  , feed.message
FROM feed
INNER JOIN users ON feed.userId = users.id
LEFT JOIN blocking ON blocking.blockerId = feed.userId 
  AND ( blocking.blockedId = '3' OR blocking.blockerId = '3' )
  AND blocking.blockedId = feed.userId
WHERE blocking.blockerId IS NULL AND blocking.blockedId IS NULL
ORDER BY feed.id DESC
LIMIT 20;

为了获得更好的性能，您可以尝试在表上添加一个复合索引。

table  blocking columns  ( blockerId, blockedId   )

还有一个表上的综合索引

table  feed columns  (userId,  message, id )