我們?cè)趯?nbsp;SQL 時(shí)，經(jīng)常會(huì)考慮到 in 語句的參數(shù)數(shù)量限制，比如，Oracle 的 in 語句參數(shù)數(shù)量超過 1000 個(gè)時(shí)會(huì)報(bào)下面錯(cuò)誤

ORA-01795: maximum number of expressions in a list is 1000

MySQL 雖然沒有明確的限制不能超過 1000，但是也會(huì)受系統(tǒng)參數(shù)的影響。今天來聊一聊 MySQL in 語句為什么要限制參數(shù)數(shù)量。

1.限制原因

1.1 參數(shù)限制

MySQL server 端會(huì)限制返回的數(shù)據(jù)大小，比如下面兩個(gè)參數(shù)：

• max_allowed_packet：?jiǎn)蝹€(gè)數(shù)據(jù)包能夠傳輸?shù)淖畲笞止?jié)數(shù)，如果 in 語句返回的結(jié)果超過這個(gè)值，服務(wù)端就會(huì)返回異常 Packet for query is too large；
• net_buffer_length：網(wǎng)絡(luò)緩沖區(qū)的大小。如果 in 語句返回的結(jié)果超過網(wǎng)絡(luò)緩沖區(qū)大小，可能導(dǎo)致傳輸問題。

1.2 性能考慮

MySQL server 處理 in 語句也會(huì)考慮內(nèi)存大小的影響：

• in 語句要查詢的數(shù)據(jù)量非常大，在 SQL 中完全沒有限制，比如下面的 SQL。因?yàn)?MySQL Server 要在內(nèi)存中完成處理，遇到大表的全表掃描時(shí)，會(huì)占用大量?jī)?nèi)存。如果是高并發(fā)場(chǎng)景，很容易因?yàn)楹馁M(fèi)內(nèi)存太大導(dǎo)致響應(yīng)慢；

select * from table1 where id in(select id from table2)

• 如果查詢涉及到排序，并且排序的數(shù)據(jù)量很大，導(dǎo)致 sort buffer 不夠用，就需要利用磁盤臨時(shí)文件輔助排序，性能下降。

即使 in 語句沒有影響到 Server 端內(nèi)存，in 語句中參數(shù)數(shù)量太多的話，也會(huì)增加比較次數(shù)，增加單個(gè)語句的執(zhí)行時(shí)間，降低性能。

2.優(yōu)化建議

2.1 拆分 SQL

如果 in 語句中的值太多，可以考慮在應(yīng)用代碼中進(jìn)行拆分，比如每個(gè) SQL 限制傳入 1000 個(gè)值，下面是一個(gè)偽代碼：

List<Long> allIds = table2Dao.selectAllIds();
List<Long> splitIds;
int start = 0;
while(true){
    splitIds = start + 1000 > allIds.size() ? allIds.subList(start, allIds.size())  : allIds.subList(start, start + 1000);
    List<ResultObject> batchResults = table1Dao.query(splitIds);
 if(start + 1000 > allIds.size()){
     break;
 }
    start += 1000;
}

2.2 使用臨時(shí)表

可以使用臨時(shí)表進(jìn)行優(yōu)化，把 table2 中的 id 插入到臨時(shí)表，然后使用 table1 和臨時(shí)表進(jìn)行關(guān)聯(lián)查詢。

--創(chuàng)建臨時(shí)表
CREATE TEMPORARY TABLE temp_table2_ids (
    id BIGINT PRIMARY KEY
);
--把 table2 的 id 插入臨時(shí)表
insert into temp_table2_ids select id from table2;
--使用 EXISTS 語句代替 in
SELECT * FROM table1 t1 WHERE EXISTS (SELECT * FROM temp_table2_ids t2 WHERE t1.id = t2.id);

3.總結(jié)

在 MySQL 中使用 in 語句時(shí)，要注意 MySQL Server 端的限制，同時(shí)要考慮對(duì)內(nèi)存和性能的影響?？梢允褂脴I(yè)務(wù)代碼中拆分 SQL 和使用臨時(shí)表的方法進(jìn)行優(yōu)化。