面試官：MySQL JOIN 表太多，你有哪些優化思路？
多張表的 join 很可能會帶來問題，引發生產事故，增加后期維護成本。一個新系統上線時可能測不出問題，但隨著數據量的增加，問題就會逐漸暴露出來了。
阿里開發手冊中明確規定禁止三個表禁止 join。
圖片
那對于 MySQL 中 join 表多的 SQL，一般該怎么優化呢？
多個表使用 join 語句的根本原因是業務代碼需要整合多張表里面的字段才能完成處理。那具體怎樣優化呢？先來模擬一個多表 join 的 SQL，這里我們創建 5 張表：
CREATE TABLE`test1` (
`id`TINYINT(3) NOTNULLCOMMENT'主鍵ID',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(20) DEFAULTNULL,
`c`VARCHAR(200) DEFAULTNULL,
`d`TINYINT(3) DEFAULTNULL,
`create_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'創建時間',
`update_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARY KEY (`id`),
KEY`a` (`a`),
KEY`b` (`b`),
KEY`c` (`c`),
KEY`d` (`d`)
) ENGINE=INNODBDEFAULTCHARSET=utf8
CREATETABLE test2 LIKE test1;
CREATETABLE test3 LIKE test1;
CREATETABLE test3 LIKE test1;
CREATETABLE test4 LIKE test1;假如我們有這樣一個包括多個表 join 的 SQL:
SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 JOIN test4 t4 ON t1.c=t4.c;1.拆分 SQL
把多張表 join 的 SQL 拆解成多個 join 語句，在應用代碼中進行組合。比如拆解成 2 個 SQL:
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b;
SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;在業務代碼中對兩個 SQL 結果進行組合。
2.使用臨時表
在上面的優化中，我們使用了 SQL 拆分的方式。如果 test3 表的數據量比較大，比如有 100萬。但 test3 表使用到的結果集只有 1000 條，可以使用臨時表：
CREATE TEMPORARY TABLE temp_t3(id TINYINT PRIMARY KEY, b VARCHAR(20),INDEX(b))ENGINE=INNODB;
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN temp_t3 t3 ON t1.b=t3.b;
SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;3.使用冗余字段
比如我們把 test4 表的 d 字段冗余到 test1 表中，假定字段名叫 t4c，這樣就可以減少一個 join（當然，這樣違反范式了）。最后只用下面的 SQL 就可以了：
SELECT t1.id ,t1.a,t2.b,t3.c,t1.t4c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000;這樣需要先在 test1 表中增加新字段 t4c，然后把 t4c 字段的值從 test4 表中更新過去。
改造需要注意兩點，一個是評估更新字段的開銷，第二個是要注意數據一致性，每次更新 test4 表中的 d 字段時也需要同步更新 test1 表中的 t4c 字段。
4.用好索引
join 語句對索引的使用非常重要，我們要注意下面幾點：
- 驅動表（MySQL 會選擇 where 語句篩選出記錄少的表作為驅動表）和被驅動表的 join 列都應該有索引；
- 如果 join 語句涉及表的多個列，可以考慮為這些列建一個復合索引，比如下面 SQL：
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;- 避免索引失效，比如 = 兩端數據類型不同、使用函數、表達式等情況要避免；
- 優化 join 順序，如果我們能確定哪個表做驅動表更合適，這時我們可以考慮使用 straight_join；
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 straight_join test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;- order by、limit 使用到的列盡量加上索引；
- 通過執行計劃查看索引使用情況。
5.修改查詢語句
如果某一個 join 表只是判斷數據行是否存在，不需要使用表里面的字段時，我們可以考慮使用 exists 或 in 語句進行優化。對于下面這個 SQL:
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 JOIN test4 t4 ON t1.c=t4.c;可以優化成如下 SQL：
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 WHERE EXISTS(SELECT id FROM test4 t4 WHERE t4.d=t1.d);6.減少結果集
減少結果集，也是一種優化手段：
- 通過增加 where 條件來讓驅動表結果集降到最小；
- 限制返回給應用的數據量，比如對返回結果做分頁；
- 對于返回結果的列，如果不用則去掉，這樣對 join_buffer 的使用也會有好處。
7.修改數據庫配置
當然，也可以修改數據庫一些配置，比如 join_buffer_size、tmp_table_size，增加 join_buffer 和臨時表大小，但是數據庫參數的修改影響范圍太大了，尤其是對于老系統，坑很多，不好做影響分析，所以不建議使用。
8.引入大數據工具
如果 join 表的數據量都很大，我們也可以考慮引入大數據工具，比如 ETL、數據湖，將表數據抽取到數據倉庫（比如 ClickHouse）中進行加工后把數據結果提供出來。當然，這樣存在的問題是數據時效性低。
9.匯總表
如果查詢時效性要求不高，可以通過定時任務把查詢結果放到一張匯總表，查詢的時候直接查詢這張匯總表。也可以把結果放到緩存，從緩存中查詢。
CREATE TABLE`test_join_result` (
`id`TINYINT(3) NOTNULLCOMMENT'主鍵ID',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(20) DEFAULTNULL,
`c`VARCHAR(200) DEFAULTNULL,
`d`TINYINT(3) DEFAULTNULL,
`e`TINYINT(1) DEFAULTNULL,
`create_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'創建時間',
`update_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARY KEY (`id`)
) ENGINE=INNODBDEFAULTCHARSET=utf8
--定時任務執行下面 SQL
insertinto test_join_result(id,a,b,c,d) SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000JOIN test4 t4 ON t1.c=t4.c;最后，對于新系統、新代碼，使用多表 join 的情況比較少，因為開發規范一般不允許這樣做。但是老系統或者做過數據庫遷移的系統，可能會遇到這種情況。要多個因素綜合考慮再下手優化。
