面試官：MySQL JOIN 表太多，你有哪些優(yōu)化思路？
多張表的 join 很可能會帶來問題，引發(fā)生產(chǎn)事故，增加后期維護成本。一個新系統(tǒng)上線時可能測不出問題，但隨著數(shù)據(jù)量的增加，問題就會逐漸暴露出來了。
阿里開發(fā)手冊中明確規(guī)定禁止三個表禁止 join。
圖片
那對于 MySQL 中 join 表多的 SQL，一般該怎么優(yōu)化呢？
多個表使用 join 語句的根本原因是業(yè)務(wù)代碼需要整合多張表里面的字段才能完成處理。那具體怎樣優(yōu)化呢？先來模擬一個多表 join 的 SQL，這里我們創(chuàng)建 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'創(chuàng)建時間',
`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 語句，在應(yīng)用代碼中進行組合。比如拆解成 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;在業(yè)務(wù)代碼中對兩個 SQL 結(jié)果進行組合。
2.使用臨時表
在上面的優(yōu)化中，我們使用了 SQL 拆分的方式。如果 test3 表的數(shù)據(jù)量比較大，比如有 100萬。但 test3 表使用到的結(jié)果集只有 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 表中更新過去。
改造需要注意兩點，一個是評估更新字段的開銷，第二個是要注意數(shù)據(jù)一致性，每次更新 test4 表中的 d 字段時也需要同步更新 test1 表中的 t4c 字段。
4.用好索引
join 語句對索引的使用非常重要，我們要注意下面幾點：
- 驅(qū)動表（MySQL 會選擇 where 語句篩選出記錄少的表作為驅(qū)動表）和被驅(qū)動表的 join 列都應(yīng)該有索引；
- 如果 join 語句涉及表的多個列，可以考慮為這些列建一個復(fù)合索引，比如下面 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;- 避免索引失效，比如 = 兩端數(shù)據(jù)類型不同、使用函數(shù)、表達式等情況要避免；
- 優(yōu)化 join 順序，如果我們能確定哪個表做驅(qū)動表更合適，這時我們可以考慮使用 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 使用到的列盡量加上索引；
- 通過執(zhí)行計劃查看索引使用情況。
5.修改查詢語句
如果某一個 join 表只是判斷數(shù)據(jù)行是否存在，不需要使用表里面的字段時，我們可以考慮使用 exists 或 in 語句進行優(yōu)化。對于下面這個 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;可以優(yōu)化成如下 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.減少結(jié)果集
減少結(jié)果集，也是一種優(yōu)化手段：
- 通過增加 where 條件來讓驅(qū)動表結(jié)果集降到最小；
- 限制返回給應(yīng)用的數(shù)據(jù)量，比如對返回結(jié)果做分頁；
- 對于返回結(jié)果的列，如果不用則去掉，這樣對 join_buffer 的使用也會有好處。
7.修改數(shù)據(jù)庫配置
當然，也可以修改數(shù)據(jù)庫一些配置，比如 join_buffer_size、tmp_table_size，增加 join_buffer 和臨時表大小，但是數(shù)據(jù)庫參數(shù)的修改影響范圍太大了，尤其是對于老系統(tǒng)，坑很多，不好做影響分析，所以不建議使用。
8.引入大數(shù)據(jù)工具
如果 join 表的數(shù)據(jù)量都很大，我們也可以考慮引入大數(shù)據(jù)工具，比如 ETL、數(shù)據(jù)湖，將表數(shù)據(jù)抽取到數(shù)據(jù)倉庫（比如 ClickHouse）中進行加工后把數(shù)據(jù)結(jié)果提供出來。當然，這樣存在的問題是數(shù)據(jù)時效性低。
9.匯總表
如果查詢時效性要求不高，可以通過定時任務(wù)把查詢結(jié)果放到一張匯總表，查詢的時候直接查詢這張匯總表。也可以把結(jié)果放到緩存，從緩存中查詢。
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'創(chuàng)建時間',
`update_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARY KEY (`id`)
) ENGINE=INNODBDEFAULTCHARSET=utf8
--定時任務(wù)執(zhí)行下面 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;最后，對于新系統(tǒng)、新代碼，使用多表 join 的情況比較少，因為開發(fā)規(guī)范一般不允許這樣做。但是老系統(tǒng)或者做過數(shù)據(jù)庫遷移的系統(tǒng)，可能會遇到這種情況。要多個因素綜合考慮再下手優(yōu)化。
