JOIN、WHERE、ORDER BY 組合性能不佳（MySQL）

posts目標：選擇與特定使用者相關的最近 10 行。

有兩個表：posts（~5,000,000 行）和relations（~8,000 行）。

posts列：

--------------------------------------------------------------------------------------------
|  id (int)  |  source_id (int)  |  title (varchar)  |  content (longtext)  |  date (int)  |
--------------------------------------------------------------------------------------------

relations列：

----------------------------------------------------
|  id (int)  |  source_id (int)  |  user_id (int)  |
----------------------------------------------------

我嘗試使用 JOIN檢索與特定使用者相關的****10 個最新行posts：

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        posts AS p
JOIN        relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

但是，執行它需要大約 30 秒（SDD 託管！）。我為這兩個表嘗試了很多索引組合，包括單列和多列的索引——無論如何，這些都沒有影響執行時間。有什麼方法可以加快選擇速度嗎？

因為在. user_id=1_source_id``posts

SHOW CREATE TABLE...結果：

CREATE TABLE `relations` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`source_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `source_id` (`source_id`),
KEY `source_id_2` (`source_id`,`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=7692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `posts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`source_id` bigint(20) unsigned NOT NULL,
`title` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
`date` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `source_id` (`source_id`),
KEY `date` (`date`),
KEY `date_2` (`date`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4867283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN結果：

使用您的數據集，MySQL 必須從文章中獲取這 450,000 條記錄（每個匹配的 source_id 的 1000 個小塊），對其進行排序，然後返回前 10 條。這是一項昂貴的工作。

您可以求助於使用儲存過程，並及時累積結果，例如每天或每週，循環直到獲得至少 10 條記錄，然後返回 10 條最近的記錄。你需要一個關於postsby的索引(date, source_id)。對於最近活躍的使用者，它會很快返回，但對於沒有最近文章的使用者來說，需要更長的時間。類似於以下內容：

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id int)
BEGIN
 DECLARE fd DATE;
 DECLARE d DATE;

 SELECT MIN(date), MAX(date) INTO fd, d FROM posts;

 CREATE TEMPORARY TABLE last_posts (id int);

 WHILE d > fd AND (SELECT COUNT(*) FROM last_posts) < 10 DO
   INSERT INTO last_posts (id)
   SELECT p.id
   FROM relations AS r
   JOIN posts AS p ON (p.source_id = r.source_id AND
                       p.date > date_sub(d, interval 7 day) AND p.date <= d)
   WHERE r.user_id = u_id
   ORDER BY p.date DESC
   LIMIT 10;

   SET d = date_sub(d, interval 7 day);
 END WHILE;

 SELECT p.id, p.title, p.content, r.id AS rid
 FROM posts p
 JOIN relations AS r ON (r.source_id = p.source_id)
 WHERE p.id IN (SELECT * FROM last_posts)
 ORDER BY p.date DESC
 LIMIT 10;

 DROP TABLE last_posts;
END;;
DELIMITER ;

您可以使用間隔，將它們減少到 1 天（更多周期和更快的查詢）。確保您在posts (date).

引用自：https://dba.stackexchange.com/questions/137344