WHERE on SQL with JOIN on large tables

我們在 MySQL 數據庫上有一組大表（數百萬條記錄），具有如下模式（簡化）：

T1: id, uid, rid, text1, text2, int1, int2, ...
T2: id, rid, tag_id, created_at
T3: id, owner_id, tag_name

指數：

T1: Primary(id), unique(uid,rid), index(rid), index(uid,int1)
T2: Primary(id), unique(tag_id,rid), index(tag_id), index(created_at)
T3: Primary(id), unique(owner_id,tag_name)

並且要求進行選擇，該選擇返回具有 tag_name = XX 但不是 YY 的“rids”：

SELECT t1.rid 
FROM t1 
LEFT JOIN t2 ON t1.rid = t2.rid 
LEFT JOIN t3 ON t2.tag_id = t3.id 
WHERE t1.uid = 123
AND t1.int1 = 3
AND t3.tag_name eq 'XX'
AND t3.tag_name != 'YY'
LIMIT 100

這自然是行不通的，因為WHERE它不會消除rid具有多個標籤的標籤。我們如何在考慮到大型表的性能的情況下實現這一目標？

更多關於數據：

以uid表示的使用者在T1中將有大約100,000條記錄，其中大約10％的T2記錄有10,000條（已標記的rids），而T3中的標籤少於10條。T1 中有 1000 個使用者 (uid)。

給定的擺脫可以是以下之一：

1. ) 有一個標籤 –> 一個 T2 記錄
2. ) 有多個標籤 –> 多個 t2 記錄
3. ) 沒有標籤 –> 0 T2 記錄

我們還可以更改 T2 和 T3 的表結構和索引以適應這種情況，只要我們保持過濾“標籤”和“T2”創建時間的能力。

您可以NOT EXISTS用作：

SELECT t2.rid
FROM t2
WHERE NOT EXISTS (
   SELECT 1 
   FROM t3
   WHERE t2.tag_id = t3.id
     AND t3.tag_name <> 'YY'
)
AND EXISTS (
   SELECT 1 
   FROM t3
   WHERE t2.tag_id = t3.id
     AND t3.tag_name <> 'XX'
);

您的索引 T2:index(tag_id) 已被 T2:unique(tag_id,rid) 覆蓋，因此您可以擺脫它

我不太使用 MySQL，但我得到的印像是 JOIN 通常比 EXISTS/NOT EXISTS 更受歡迎。翻譯查詢（注意 DISTINCT）：

SELECT DISTINCT t2.rid
FROM t2
JOIN t3 AS t31
   ON t2.tag_id = t31.id
  AND t31.tag_name = 'XX'
LEFT JOIN t3 AS t32
   ON t2.tag_id = t32.id
  AND t32.tag_name <> 'YY'
WHERE t32.id IS NULL;

對於完整和正確的答案，並非所有資訊都提供：

• 結構（包括索引），希望 t1.int1 - 至少有索引
• 標籤的基數（至少猜測一下）
• 目前查詢計劃

所以，只是常見的建議：

1. LIKE %xx%- 不要總是使用索引。這意味著，搜尋 T2 和 T3 - 在這種情況下始終是全掃描。可能的解決方案 - 如果它是真正的標籤 - 從列表中選擇標籤並使用 = 或按全名進行搜尋LIKE "XXX%"- 此構造開始使用 T2 和 T3 表的索引
2. 如果大小不同T1且T2/T3嚴重 - 您可以更改從最小表中選擇的查詢並與最大表連接。它繼續在小表上進行全掃描，但是對於每個從小的使用索引與大的比較一次並且比每次 MySQL 自動執行此操作，需要配置文件查詢來檢查計劃
3. 關於基數 -LIMIT 100當然減少顯示數據的時間，但與原始查詢的數據大小無關（特別是如果在最終查詢中您使用任何排序ORDER BY t1.int1）。因此，如果 1000 萬條記錄表只有 20 個標籤 - 它總是會管理大量記錄，並且記憶體可能會成為瓶頸。

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