MySQL/PHP - 基於具有 ORDER 和 LIMIT 的另一列選擇 DISTINCT 列

我有一個名為messages的表，如下所示：

+-----+-----------+-------------+-----+
| id  | sender_id | receiver_id | ... |
+-----+-----------+-------------+-----+
|   . |         . |           . | ... |
|   . |         . |           . | ... |
| 120 |        19 |          11 | ... |
| 121 |         1 |           3 | ... |
| 122 |         9 |          18 | ... |
| 123 |         2 |           1 | ... |
| 124 |         1 |          24 | ... |
| 125 |         3 |           1 | ... |
| 126 |         7 |           5 | ... |
| 127 |        24 |           1 | ... |
| 128 |        25 |           1 | ... |
| 129 |         1 |          25 | ... |
| 130 |         7 |           3 | ... |
| 131 |         3 |           5 | ... |
+-----+-----------+-------------+-----+

如何選擇與使用者 1 通信的最後 3 個唯一使用者？換句話說，我該如何選擇：

• 不同的 sender_ids，其接收器 ID 為 1或不同的接收器 ID，其 sender_id 為 1
• 按降序排列並限制為 3 個？

結果將如下所示：

+-----+-----------+-------------+-----+
| id  | sender_id | receiver_id | ... |
+-----+-----------+-------------+-----+
| 129 |         1 |          25 | ... |
| 127 |        24 |           1 | ... |
| 125 |         3 |           1 | ... |
+-----+-----------+-------------+-----+

或者簡單地說：

+----+
| 25 |
| 24 |
|  3 |
+----+

測試：

SELECT CASE WHEN sender_id = 1 THEN receiver_id ELSE sender_id END user_id
FROM messages 
WHERE 1 IN (sender_id, receiver_id)
GROUP BY user_id
ORDER BY MAX(id) DESC LIMIT 3

1 IN (a,b)不會縮放。它將進行全表掃描。這將使用索引：

( SELECT id, receiver_id FROM messages WHERE sender_id = 1   LIMIT 3 )
UNION ALL
( SELECT id, sender_id   FROM messages WHERE receiver_id = 1 LIMIT 3 )
LIMIT 3 

與兩者一起

INDEX(sender_id, receiver_id, id),
INDEX(receiver_id, sender_id, id)

現在，假設您想要 3 行的其餘列：

SELECT m.*
   FROM ( ... ) AS x    -- put the above UNION inside here
   JOIN messages USING(id)

你還沒有說你的“降序”是什麼。也許id？也許一個datetime？ id會容易一些，所以這裡是：

SELECT m.*
   FROM (
           ( SELECT id, receiver_id FROM messages WHERE sender_id = 1
                 ORDER BY id DESC  LIMIT 3 )
           UNION ALL
           ( SELECT id, sender_id   FROM messages WHERE receiver_id = 1
                 ORDER BY id DESC  LIMIT 3 )
           ORDER BY id DESC  LIMIT 3   -- yes, repeated
        ) AS x
   JOIN messages USING(id)
   ORDER BY id DESC    -- yes, ORDER BY is repeated again

討論：

• 的每一側都UNION可以使用索引來快速找到請求的行。
• id被假定為PRIMARY KEY的messages。
• 每個INDEX都是“覆蓋” - 中所需的所有列SELECT都存在於INDEX.
• 從 order by 更改id為 datetime 會破壞“覆蓋”，但可以補救。
• 從每個子查詢中獲取 3 行，然後再切到 3 行——這對於考慮誰給誰發電子郵件以及何時發送電子郵件的各種情況是必要的。
• 最終JOIN只需要在數據的 BTree 中查看 3 次——這是“覆蓋”和初始“派生”表（帶有 的表UNION）的目標。
• 僅對於數千行，不需要這種複雜性。但是如果你有數百萬行，這是可取的。對於數十億行，複雜性至關重要。
• OFFSET變得更棘手。這裡的討論：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#or
• UNION ALL比在這種情況下更快UNION DISTINCT並且似乎是合適的。（從使用者到他自己的 A 將顯示兩次。）message``ALL

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