引言

MySQL大批量IN查詢優(yōu)化是一個(gè)非常經(jīng)典且棘手的高并發(fā)、大數(shù)據(jù)量場(chǎng)景下的問題。直接使用 WHERE id IN (十萬(wàn)個(gè)ID) 是絕對(duì)的下策，會(huì)導(dǎo)致性能急劇下降。

本文我將與大家探討最有效、最常用的優(yōu)化方案，并按推薦順序排列。

核心思路

根本問題在于，將一個(gè)巨大的列表（10萬(wàn)個(gè)參數(shù)）傳遞給SQL語(yǔ)句，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)解析SQL的耗時(shí)極長(zhǎng)（語(yǔ)法分析、優(yōu)化）、網(wǎng)絡(luò)傳輸壓力大，并且很可能無法有效利用索引。

最佳思路是：將“內(nèi)存計(jì)算”轉(zhuǎn)化為“集合聯(lián)接查詢”。

首選舉薦方案：使用臨時(shí)表

這是處理此類問題最標(biāo)準(zhǔn)、兼容性最好且效果最顯著的方法。其本質(zhì)是將程序中的ID列表持久化到數(shù)據(jù)庫(kù)的一張臨時(shí)表中，然后通過高效的表聯(lián)接（JOIN） 來代替低效的 IN 操作。

操作步驟：

創(chuàng)建臨時(shí)表：在數(shù)據(jù)庫(kù)中創(chuàng)建一張臨時(shí)表，通常只包含一個(gè)主鍵字段 id (根據(jù)實(shí)際情況選擇類型，如 BIGINT UNSIGNED)。使用 CREATE TEMPORARY TABLE 可以避免沖突且會(huì)話結(jié)束后自動(dòng)清理。

CREATE TEMPORARY TABLE temp_ids (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=Memory;

ENGINE=Memory：建議使用內(nèi)存引擎，數(shù)據(jù)完全存儲(chǔ)在內(nèi)存中，速度極快。如果ID量極大（遠(yuǎn)超10萬(wàn)），可改用InnoDB并添加索引。

批量插入數(shù)據(jù)：使用批量插入（Batch Insert）的方式，將你的10萬(wàn)個(gè)ID分批次插入到臨時(shí)表中。這是性能關(guān)鍵點(diǎn)，絕對(duì)不要用10萬(wàn)條獨(dú)立的INSERT語(yǔ)句。

Java (JDBC) 示例:

String sql = "INSERT INTO temp_ids (id) VALUES (?)";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (Long id : hugeIdList) { // hugeIdList 是你的10萬(wàn)個(gè)ID的集合
    pstmt.setLong(1, id);
    pstmt.addBatch(); // 加入批量操作
    
    // 每1000條或一定數(shù)量執(zhí)行一次，避免批量過大
    if (i % 1000 == 0) {
        pstmt.executeBatch();
    }
}
pstmt.executeBatch(); // 插入最后一批

• 其他語(yǔ)言：同理，找到對(duì)應(yīng)的批量操作方式。

使用JOIN代替IN：改寫原來的SQL語(yǔ)句，用 JOIN 關(guān)聯(lián)臨時(shí)表。

原SQL：

SELECT * FROM your_table 
WHERE your_id IN (1, 2, 3, ..., 100000);

優(yōu)化后的SQL：

SELECT t.* 
FROM your_table t 
INNER JOIN temp_ids tmp ON t.your_id = tmp.id;

• 如果原表 your_table 的 your_id 字段有索引，這個(gè) JOIN 操作會(huì)非?？欤?yàn)樗举|(zhì)上是兩個(gè)集合的哈希聯(lián)接或索引查找，數(shù)據(jù)庫(kù)優(yōu)化器可以高效處理。

優(yōu)點(diǎn)：

• 性能飛躍：避免了超長(zhǎng)SQL的解析，利用了索引和高效的集合操作。
• 通用性強(qiáng)：適用于所有版本的MySQL，是標(biāo)準(zhǔn)的SQL用法。
• 資源可控：臨時(shí)表（尤其是內(nèi)存臨時(shí)表）對(duì)系統(tǒng)影響較小。

備選方案：使用內(nèi)聯(lián)值表（MySQL 8.0+ 專屬）

如果你的MySQL版本是8.0或更高，可以使用 JSON_TABLE 或 VALUES 語(yǔ)句來構(gòu)造一個(gè)內(nèi)聯(lián)的表結(jié)構(gòu)。

示例 (使用 JSON_TABLE)：

SELECT t.*
FROM your_table t
JOIN JSON_TABLE(
  '[1,2,3,...,100000]', -- 這里替換為你的JSON數(shù)組字符串
  '$[*]' COLUMNS(id BIGINT PATH '$')
) AS tmp
ON t.your_id = tmp.id;

操作步驟：

1. 在應(yīng)用程序?qū)?，?0萬(wàn)個(gè)ID的列表序列化為一個(gè)JSON數(shù)組字符串，例如 "[1,2,3,4,5]"。
2. 將上述字符串填充到SQL中的 '[1,2,3,...,100000]' 位置。
3. 執(zhí)行該SQL。

優(yōu)點(diǎn)：

• 無需創(chuàng)建臨時(shí)表，一步到位。

缺點(diǎn)：

• 僅限MySQL 8.0+。
• SQL語(yǔ)句本身仍然會(huì)非常長(zhǎng)（一個(gè)包含10萬(wàn)個(gè)數(shù)字的JSON字符串），雖然解析器對(duì)JSON的解析可能比解析10萬(wàn)個(gè)逗號(hào)分隔的數(shù)字更快，但仍然有網(wǎng)絡(luò)傳輸和內(nèi)存消耗的壓力。通常不如臨時(shí)表方案穩(wěn)定可靠。

堅(jiān)決避免的方案

• 拆分多次查詢（如 WHERE id IN (1,2,3...1000)，查100次）：

缺點(diǎn)：網(wǎng)絡(luò)往返次數(shù)（RT）暴增，總耗時(shí)可能更長(zhǎng)，對(duì)應(yīng)用和數(shù)據(jù)庫(kù)都是負(fù)擔(dān)。

• 手動(dòng)拼接10萬(wàn)個(gè)參數(shù)的SQL字符串：

缺點(diǎn)：這是問題的根源。數(shù)據(jù)庫(kù)解析SQL的CPU消耗巨大，而且可能達(dá)到 max_allowed_packet 限制導(dǎo)致失敗。

總結(jié)與選擇

給你的最終建議：

毫不猶豫地選擇【臨時(shí)表】方案。 這是經(jīng)過無數(shù)生產(chǎn)環(huán)境驗(yàn)證的、最有效的處理大批量IN查詢的方法。雖然需要3步操作（建表、批量插入、JOIN查詢），但其整體的性能、穩(wěn)定性和資源消耗遠(yuǎn)勝于其他任何方法。

到此這篇關(guān)于MySQL大批量IN查詢的優(yōu)化方案的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化大批量IN查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論