額外的 JOIN 查詢更快

SELECT COUNT(DISTINCT User_id) FROM Session 
GROUP BY Floor_id;

SELECT COUNT(DISTINCT User_id) FROM Session 
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;

第二個查詢的執行速度比第一個查詢快 20 倍。

Floor_id是 FK 到Floor.id.

為什麼會出現這種情況，我們應該如何知道何時可以使用 JOIN 來加速查詢？

CREATE TABLE `Session` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `startTime` datetime NOT NULL,
 `endTime` datetime NOT NULL,
 `ssid` varchar(45) DEFAULT NULL,
 `AccessPoint_id` int(11) DEFAULT NULL,
 `User_id` int(11) NOT NULL,
 `Floor_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique` (`User_id`,`AccessPoint_id`,`startTime`),
 KEY `fk_session_AccessPoint1_idx` (`AccessPoint_id`),
 KEY `fk_session_User1_idx` (`User_id`),
 KEY `time` (`startTime`,`endTime`),
 KEY `fk_Session_Floor1_idx` (`Floor_id`),
 CONSTRAINT `fk_Session_Floor1` FOREIGN KEY (`Floor_id`) REFERENCES `Floor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_session_AccessPoint1` FOREIGN KEY (`AccessPoint_id`) REFERENCES `AccessPoint` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_session_User1` FOREIGN KEY (`User_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1099568396 DEFAULT CHARSET=utf8

你想要更快嗎？添加INDEX(floor_id, user_id).

如前所述，兩個查詢都給出了相同的答案。所以我也想知道它是如何工作的。

經過幾個小時的研究，我發現了一些東西，我在這裡解釋一下。

第一個查詢

• 全索引掃描 {fk_Session_Floor1}
• 分組後按欄位對整個表格進行排序
• 使用 DISTINCT User_id 應用計數函式

第二次查詢

• 全索引掃描 {fk_Session_Floor1}
• 樓層表的唯一查找{加入發生}
• Filesort 預設用於任何類型的連接，以便行已經用鍵排序
• 按排序的行分組
• 使用 DISTINCT User_id 應用計數函式

8.8.2 EXPLAIN 輸出格式，用於顯示以下內容的任何參考。

1. 不同（JSON 屬性：不同）

MySQL 正在尋找不同的值，因此它在找到第一個匹配行後停止為目前行組合搜尋更多行。

2. 使用文件排序（JSON 屬性：using_filesort）

MySQL 必須做一個額外的過程來找出如何按排序順序檢索行。排序是通過根據連接類型遍歷所有行並儲存排序鍵和指向與 WHERE 子句匹配的所有行的行的指針來完成的。然後對鍵進行排序，並按排序順序檢索行。

另見第 8.2.1.14 節

同樣我們都知道，如果記錄在分組之前已經排序，那麼 group by 不需要進行任何排序。它可以直接應用mapper、reducer或聚合函式。

所以這裡的 join 讓這有點容易，但它不會減少行數。但它也使所有行預設按鍵排序，以前沒有這樣做。

這就是為什麼您的查詢要快得多的原因。

希望這是您正在尋找的。

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