时间戳

首页 > 解决方案 > 使用 ORDER BY 和 INNER JOIN 优化 MySQL 查询（选择用户关注的位置）

mysql - 使用 ORDER BY 和 INNER JOIN 优化 MySQL 查询（选择用户关注的位置）

问题描述

我需要优化以下查询：

SELECT a.*
  FROM Activity AS a
  JOIN users_following AS f1 
    ON f1.userId = a.originatorId 
   AND f1.followerId = 1 
 ORDER 
    BY a.time DESC
 LIMIT 10

这个想法是让某个用户（在本例中为用户 1）正在关注的用户发起的所有活动，按时间排序。编写的这个查询非常慢（约 5 秒），但如果 a) 省略连接或 b) 省略 order by 子句，则速度非常快。

我尝试过的事情：

以下是 CREATE TABLE 和 EXPLAIN 定义。

CREATE TABLE `Activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `voteId` int(11) DEFAULT NULL,
  `commentId` int(11) DEFAULT NULL,
  `achievementId` int(11) DEFAULT NULL,
  `challengeId` int(11) DEFAULT NULL,
  `followerId` int(11) DEFAULT NULL,
  `acknowledged` int(11) NOT NULL DEFAULT '0',
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `isPrivate` int(11) NOT NULL DEFAULT '0',
  `portalId` int(11) DEFAULT NULL,
  `postId` int(11) DEFAULT NULL,
  `portalMemberId` int(11) DEFAULT NULL,
  `originatorId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_55026B0C1CC880D8` (`portalMemberId`),
  KEY `IDX_55026B0C1D79C36A` (`challengeId`),
  KEY `IDX_55026B0CE7A069D0` (`achievementId`),
  KEY `IDX_55026B0CB6FEC0EE` (`voteId`),
  KEY `IDX_55026B0C6690C3F5` (`commentId`),
  KEY `IDX_55026B0C64B64DCC` (`userId`),
  KEY `IDX_55026B0CF542AA03` (`followerId`),
  KEY `IDX_55026B0C57076B1F` (`portalId`),
  KEY `IDX_55026B0CE094D20D` (`postId`),
  KEY `IDX_55026B0C162E014D` (`originatorId`),
  KEY `activity_time_idx` (`time`),
  KEY `activity_filter_idx` (`type`,`originatorId`,`userId`,`isPrivate`),
  KEY `acknowledged_idx` (`acknowledged`),
  KEY `idx1` (`time`,`originatorId`),
  KEY `idx2` (`originatorId`,`userId`,`postId`,`challengeId`,`commentId`,`time`),
  CONSTRAINT `FK_55026B0C162E014D` FOREIGN KEY (`originatorId`) REFERENCES `ProseUser` (`id`),
  CONSTRAINT `FK_55026B0C1CC880D8` FOREIGN KEY (`portalMemberId`) REFERENCES `PortalMember` (`id`),
  CONSTRAINT `FK_55026B0C1D79C36A` FOREIGN KEY (`challengeId`) REFERENCES `Challenge` (`id`),
  CONSTRAINT `FK_55026B0C57076B1F` FOREIGN KEY (`portalId`) REFERENCES `Portal` (`id`),
  CONSTRAINT `FK_55026B0C64B64DCC` FOREIGN KEY (`userId`) REFERENCES `ProseUser` (`id`),
  CONSTRAINT `FK_55026B0C6690C3F5` FOREIGN KEY (`commentId`) REFERENCES `Comment` (`id`),
  CONSTRAINT `FK_55026B0CB6FEC0EE` FOREIGN KEY (`voteId`) REFERENCES `Vote` (`id`),
  CONSTRAINT `FK_55026B0CE094D20D` FOREIGN KEY (`postId`) REFERENCES `Post` (`id`),
  CONSTRAINT `FK_55026B0CE7A069D0` FOREIGN KEY (`achievementId`) REFERENCES `UserAchievement` (`id`),
  CONSTRAINT `FK_55026B0CF542AA03` FOREIGN KEY (`followerId`) REFERENCES `ProseUser` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4097200 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE `users_following` (
  `userId` int(11) NOT NULL,
  `followerId` int(11) NOT NULL,
  PRIMARY KEY (`userId`,`followerId`),
  KEY `IDX_17C2F70264B64DCC` (`userId`),
  KEY `IDX_17C2F702F542AA03` (`followerId`),
  KEY `idx1` (`userId`,`followerId`),
  KEY `idx2` (`followerId`,`userId`),
  CONSTRAINT `FK_17C2F70264B64DCC` FOREIGN KEY (`userId`) REFERENCES `ProseUser` (`id`),
  CONSTRAINT `FK_17C2F702F542AA03` FOREIGN KEY (`followerId`) REFERENCES `ProseUser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

解释

+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                                               | key                  | key_len | ref                       | rows | filtered | Extra                                        |
+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | f1    | NULL       | ref  | PRIMARY,IDX_17C2F70264B64DCC,IDX_17C2F702F542AA03,idx1,idx2 | IDX_17C2F702F542AA03 | 4       | const                     |  145 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | NULL       | ref  | IDX_55026B0C162E014D,idx2                                   | IDX_55026B0C162E014D | 5       | prose_2_24_2021.f1.userId |   38 |   100.00 | NULL                                         |
+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+

标签： mysqlsqldatabasejoin

解决方案

让我们举个例子：用户#1 跟随用户#2 和#3。以下是用户订购的活动，然后是时间：

+--------+-------+
| 用户 | 时间 |
+--------+-------+
| 1 | 10:00 |
| 1 | 11:00 |
| 2 | 10:00 |
| 2 | 12:00 |
| 3 | 09:00 |
| 3 | 13:00 |
| 4 | 10:00 |
+--------+-------+

我们可以快速找到关注的用户的活动，但得到的时间是无序的：

+--------+-------+
| 用户 | 时间 |
+--------+-------+
| 2 | 10:00 |
| 2 | 12:00 |
| 3 | 09:00 |
| 3 | 13:00 |
+--------+-------+

这意味着我们必须对它们进行排序才能使前 n 个排序。如果这些不是四个，而是数千或数百万个活动，那将需要很长时间。

另一方面，如果数据是按时间降序排列的，那么用户：

+--------+-------+
| 用户 | 时间 |
+--------+-------+
| 3 | 13:00 |
| 2 | 12:00 |
| 1 | 11:00 |
| 1 | 10:00 |
| 2 | 10:00 |
| 4 | 10:00 |
| 3 | 09:00 |
+--------+-------+

我们必须按顺序读取整个数据，直到找到 n 个最重要的活动。无需进一步订购。如果我们幸运的话，前 n 行是匹配的，就是这样。如果我们不走运，我们会阅读整个表格（或索引）。

所以，不能保证这么快。第一种方法可以快速获取数据，但排序可能需要很长时间。第二种方法不需要排序，但阅读可能需要很长时间。

我更喜欢第二种方法，但这当然取决于数据。用户多吗？大多数人紧随其后的是用户 #1 还是只有少数人？活动多吗？多由几个用户或每个用户很少的活动？...无论如何，我会提供这个索引：

create index idx1 on activity (time desc, originatorid);

由于索引只是对 DBMS 的提议，我们不妨为 DBMS 想要遵循其他路线的情况提供其他索引：

create index idx2 on activity (originatorid, time desc);

这就是我可能编写查询的方式：

SELECT a.*
FROM activity AS a
WHERE EXISTS
(
  SELECT NULL
  FROM users_following AS f1 
  WHERE f1.userId = a.originatorId 
  AND f1.followerId = 1 
)
ORDER BY a.time DESC
LIMIT 10;

推荐阅读