MySQL 多表聯合查詢並按時間排序：UNION 實戰技巧

MySQL 多表聯合查詢並按時間排序：UNION 實戰技巧

業務常見場景：資金流水表（t_money_logs）與金幣流水表（t_gold_logs）結構一致，需合並查詢並按創建時間倒序展示。MySQL UNION 操作符是解決此類「同構表合並」的利器。

核心 SQL

SELECT * FROM (
    SELECT * FROM t_money_logs
    UNION
    SELECT * FROM t_gold_logs
) AS c
ORDER BY create_time DESC;

關鍵點解析

1. UNION vs UNION ALL：
   • UNION：去重（排序+唯一），性能較低
   • UNION ALL：直接合並，不去重，性能高 （業務允許重複時首選）
2. 字段對齊要求：兩表 SELECT * 須保證列數、列順序、數據類型完全一致。生產環境建議顯式列出字段：SELECT id, user_id, amount, create_time ...
3. 排序位置：ORDER BY 必須放在外層查詢，不能直接寫在子查詢中（MySQL 8.0+ 支持子查詢排序但無意義）
4. 別名必要性：派生表 AS c 必須指定別名，否則報錯 Every derived table must have its own alias

性能優化建議

1. 利用索引避免文件排序

兩表 create_time 單獨建立索引，或建立聯合索引 (create_time, 其他高頻查詢字段)。若數據量極大，考慮分頁查詢時的「延遲關聯」優化：

-- 先按時間取 ID，再回表查詳情
SELECT t.* FROM (
    SELECT id FROM t_money_logs ORDER BY create_time DESC LIMIT 10000, 20
    UNION ALL
    SELECT id FROM t_gold_logs ORDER BY create_time DESC LIMIT 10000, 20
) AS ids
JOIN (
    SELECT * FROM t_money_logs
    UNION ALL
    SELECT * FROM t_gold_logs
) AS t ON t.id = ids.id
ORDER BY t.create_time DESC;

2. 分區表替代多表 UNION

若業務邏輯允許，將流水表設計為按月/周分區表（PARTITION BY RANGE (TO_DAYS(create_time))），單表查詢自動分區剪枝，完全避免 UNION 開銷。

3. 緩存熱點合並結果

首頁/個人中心流水列表，可將最近 7-30 天合並結果緩存至 Redis（Key: user_flow:{uid}:{page}），定時/增量更新，降低 MySQL 壓力。

常見坑點

• LIMIT 陷阱：UNION 子查詢中加 LIMIT 會改變語義（先限制再合並），外層 LIMIT 才是全局分頁
• 字符集不一致：兩表字符集不同導致 Illegal mix of collations，統一為 utf8mb4_general_ci
• 數據類型隱式轉換：一表 DECIMAL(10,2)，另表 VARCHAR 存金額，導致索引失效、精度丟失

總結

UNION [ALL] + 外層 ORDER BY 是多同構表合並排序的標準模式。生產環境務必：顯式列字段、選擇 ALL 避免去重、建立時間索引、評估分區表替代方案。對於「流水賬」類業務，分區表 + 緩存是終極演進路徑。