mySQL 查詢優化 — 多個連接或選擇… where not in (select distinct…)？

背景

我有一個訪問大型使用者數據庫（約 200k 行）的 Drupal 安裝，我的“人員查找器”功能需要訪問所有這些行（以隨機順序）。我似乎無法在 Drupal 的 UI 中使用LIMIT和使用OFFSET（而且我在 Drupal 視圖中的大型數據集的慢速查詢中對 Drupal.SE 有一個稍微不同的 Drupal 特定問題- 更好地在 SQL 或 PHP 中處理？，哪個解決了這個問題，以及這個問題的一部分），但我的 mySQL 特定問題如下：

我需要根據另一個表中的數據排除一些行（“包括角色 A 中不屬於角色 B、C 或 D 的所有使用者）。Drupal 生成的查詢有效

SELECT
   users.uid AS uid,
   /* some columns */,
   RAND() AS random_field
FROM
   users users
   INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
   LEFT JOIN users_roles users_roles2
       ON users.uid = users_roles2.uid
       AND (users_roles2.rid = :views_join_condition_0
         OR users_roles2.rid = :views_join_condition_1
         OR users_roles2.rid = :views_join_condition_2)
WHERE
   (( (users.status <> :db_condition_placeholder_3)      -- Active users only
   AND (users_roles.rid = :db_condition_placeholder_4)   -- Must be in rôle A
   AND (users_roles2.rid IS NULL)                        -- Must not be in rôles B, C, D
   AND (users.uid != :users_uid OR users.uid IS NULL) )) -- Must not be current user
ORDER BY random_field ASC

（對 的引用users.uid IS NULL是一個紅鯡魚；絕不應該是這種情況，並且與此查詢無關。）

題

現在讓我感到震驚的是，手動滾動 Drupal 的過濾條件（在 Drupal 的 UI 的約束範圍內）可能會有所幫助——我可以對幾乎所有的:db_condition_placeholders 進行硬編碼——但我不確定以下兩個選項之間是否存在顯著的性能差異：

1. FROM將子句更改為

FROM users INNER JOIN users_roles
   ON users.uid = users_roles.uid AND users_roles.rid NOT IN (6, 8, 9)

（然後WHERE users_roles.rid = 5像以前一樣做，只是刪除users_roles2引用）；要麼 2. 完全刪除並將子句JOIN更改為：WHERE

WHERE users.status = 1                                             -- Active users only
   AND users.uid IN
      (SELECT DISTINCT uid FROM users_roles WHERE rid = 5)        -- Must be in rôle A
   AND users.uid NOT IN
      (SELECT DISTINCT uid FROM users_roles WHERE rid IN (6,8,9)) -- Not rôles B, C, D
   AND users.uid != :users_uid                                    -- Not current user

額外的資訊

如果有幫助，mySQL 版本號是5.1.50-enterprise-gpl-pro，所有表都使用 InnoDB 儲存引擎，並且該表users_roles已經在兩列中具有聚集主鍵：

mysql> describe users_roles;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
| rid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

我看到的實際性能問題是雙重的——伺服器在 RAM 上達到極限，而我在這裡討論的查詢需要 2 秒以上的時間才能執行。我猜如果不查看LIMITand就無法解決 RAM 問題OFFSET，但是加快此查詢的速度肯定是一個好的開始。

根據要求提供更多額外資訊

mysql> describe users;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| uid              | int(10) unsigned | NO   | PRI | 0       |       |
| name             | varchar(60)      | NO   | UNI |         |       |
| pass             | varchar(128)     | NO   |     |         |       |
| mail             | varchar(254)     | YES  | MUL |         |       |
| theme            | varchar(255)     | NO   |     |         |       |
| signature        | varchar(255)     | NO   |     |         |       |
| signature_format | varchar(255)     | YES  |     | NULL    |       |
| created          | int(11)          | NO   | MUL | 0       |       |
| access           | int(11)          | NO   | MUL | 0       |       |
| login            | int(11)          | NO   |     | 0       |       |
| status           | tinyint(4)       | NO   |     | 0       |       |
| timezone         | varchar(32)      | YES  |     | NULL    |       |
| language         | varchar(12)      | NO   |     |         |       |
| picture          | int(11)          | NO   |     | 0       |       |
| init             | varchar(254)     | YES  |     |         |       |
| data             | longblob         | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT
   users.uid AS uid,
   /* some columns */,
   RAND() AS random_field
FROM
   users users
   INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
   LEFT JOIN users_roles users_roles2
       ON users.uid = users_roles2.uid
       AND (users_roles2.rid = 6 OR users_roles2.rid = 8 OR users_roles2.rid = 9)
WHERE
   (( (users.status <> 0)                           -- Active users only
   AND (users_roles.rid = 5)                        -- Must be in rôle A
   AND (users_roles2.rid IS NULL)                   -- Not in rôles B, C, D
   AND (users.uid != 35635 OR users.uid IS NULL) )) -- Not (random valid UID)
ORDER BY random_field ASC
+----+-------------+--------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                    | rows  | Extra                                                     |
+----+-------------+--------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | users_roles  | ref    | PRIMARY,rid   | rid     | 4       | const                  | 69985 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | users        | eq_ref | PRIMARY       | PRIMARY | 4       | dbname.users_roles.uid |     1 | Using where                                               |
|  1 | SIMPLE      | users_roles2 | ref    | PRIMARY,rid   | PRIMARY | 4       | dbname.users.uid       |     1 | Using where; Using index; Not exists                      |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------+-------+-----------------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

• 我會在users_roles (rid, uid). 在具有兩列的多對多表中(a,b)，您幾乎總是需要兩個索引：(a,b)並且(b,a)在一個查詢或另一個查詢中。我認為這個索引將有助於這個查詢。
• 嘗試對查詢及其產生的各種重寫EXPLAIN EXTENDED。
• 關於您的建議，第一個是不正確的（它不會顯示相同的結果）。對於第二個建議：

WHERE users.status = 1                                           -- Active users only

是的，這比users.status <> 0. 如果有索引，這種更改可能會產生更好的效果users (status)（如果沒有很多活躍使用者，效果會更好）。使用 B 樹優化帶有布爾列（或充當布爾列）的查詢並不容易。

 AND users.uid IN
    (SELECT DISTINCT uid FROM users_roles WHERE rid = 5)        -- Must be in rôle A

不。眾所周知，MySQL 存在問題column IN (SELECT ...)，尤其是在外部表很大的情況下（而您的表是 200K 列，所以不，不好）。

 AND users.uid NOT IN
    (SELECT DISTINCT uid FROM users_roles WHERE rid IN (6,8,9)) -- Not rôles B, C, D

是的，這是重寫的一種方式。這DISTINCT是多餘的。

 AND users.uid <> :users_uid                                    -- Not current user

是的，刪除users.uid IS NOT NULL可能會有所幫助，但不會改變結果。

• 您可以嘗試的其他事情：

將rid = 5條件移至ON子句：

INNER JOIN users_roles users_roles 
 ON  users.uid = users_roles.uid
 AND users_roles.rid = 5

(rewrite) toNOT IN也可以寫成NOT EXISTS：

 AND NOT EXISTS 
     ( SELECT * 
       FROM users_roles ur 
       WHERE ur.uid = users.uid 
         AND ur.rid IN (6,8,9)
     )

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