提高SQL查詢性能的最佳方法是什么？

目前我有以下兩個表（此處顯示有些簡化）：

用戶：（用戶ID，用戶名，電子郵件，姓名）權限：（ID，用戶ID，權限）

例如，權限欄位可能包含諸如 canRead、canWrite、canDelete 等內容。每個用戶可以擁有任意數量的權限。

我現在已經撰寫了一個查詢來顯示所有用戶并將每個用戶的權限作為一個額外的布爾列。例如，對于在系統中注冊的兩個用戶，SQL 腳本的結果應該是這樣的：

userId: 1, username: "testuser", email: "[email protected]", name: "testname", canRead: 1, canWrite: 0, canDelete: 0
userId: 2, username: "anotheruser", email: "[email protected]", name: "anothername", canRead: 1, canWrite: 1, canDelete: 1

我當前的查詢如下所示：

SELECT users.userId, users.username, users.email, users.name,
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canRead' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canRead',
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canWrite' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canWrite',
CASE WHEN(
  SELECT DISTINCT permissions.permission
  FROM permissions
  WHERE permissions.permission = 'canDelete' AND users.userId = permissions.userId
) IS NULL THEN 0 ELSE 1 END AS 'canDelete',
FROM users
LEFT JOIN permissions
ON permissions.userId = users.userId
GROUP BY users.userId

我添加的權限越多，查詢就越慢。如何以更簡化且特別高效的方式撰寫此查詢？

uj5u.com熱心網友回復：

您可以通過單個 JOIN 和一些資料操作來完成此操作：

SELECT 
    users.userId, users.username, users.email, users.name,
    COUNT(IF(permissions.permission = 'canRead', 1, null)) > 0 'canRead',
    COUNT(IF(permissions.permission = 'canWrite', 1, null)) > 0 'canWrite',
    COUNT(IF(permissions.permission = 'canDelete', 1, null)) > 0 'canDelete'
FROM users
LEFT JOIN permissions ON users.userId = permissions.userId
GROUP BY users.userId, users.username, users.email, users.name;

MySQL JOIN 小提琴

uj5u.com熱心網友回復：

簡化它。最多。

SELECT u.userId, u.username, u.email, u.name
, MAX(CASE WHEN p.permission = 'canRead' THEN 1 ELSE 0 END) AS canRead
, MAX(CASE WHEN p.permission = 'canWrite' THEN 1 ELSE 0 END) AS canWrite
, MAX(CASE WHEN p.permission = 'canDelete' THEN 1 ELSE 0 END) AS canDelete
FROM users u
LEFT JOIN permissions p
  ON p.userId = u.userId
GROUP BY u.userId, u.username, u.email, u.name;

uj5u.com熱心網友回復：

• 洗掉這個（它似乎沒有用，但需要時間）：

    LEFT JOIN  permissions  ON permissions.userId = users.userId
  

• 這個權限索引： INDEX(userId, permission)可能有幫助。（請參閱下面的警告。）

• 沒有LEFT JOIN， 就GROUP BY變得不必要了；擺脫它。

• 切換到EXISTS。例如

    (
        SELECT  DISTINCT permissions.permission
            FROM  permissions
            WHERE  permissions.permission = 'canRead'
              AND  users.userId = permissions.userId 
    ) IS NULL THEN 0 ELSE 1 END AS 'canRead',
  

-->

    EXISTS ( SELECT 1 FROM permissions
            WHERE  permissions.permission = 'canRead'
              AND  users.userId = permissions.userId 
    ) AS 'canRead',

Exists() 可能比其他 Answers 更快，因為EXISTS它是“半連接”，這意味著它在發現匹配行時停止。其他人必須檢查所有相關行，這需要更長的時間。

• “用戶：（用戶 ID，用戶名，電子郵件，姓名）權限：（ID，用戶 ID，權限）”意味著您在users? 完全id需要嗎？如果你擺脫id做出userId的PRIMARY KEY，那么我推薦指數上面變得多余。

• 如需進一步討論，請提供SHOW CREATE TABLE每張表。

標籤：mysql 加入 通过...分组

上一篇：550.游戲分析IV-為什么我不能從兩個表中選擇？

下一篇：使用jQuery從URL中選擇要上傳的影像