优化含5k+number_card的IN()子句SQL查询性能的替代方案

1. 用临时表关联替代IN子句 #

当IN子句包含5000个值时，数据库优化器难以高效处理，容易触发大量扫描或重复查找。换成临时表关联的方式能大幅提升性能：

步骤1：创建临时表 #

CREATE TEMPORARY TABLE temp_cards (
    number_card VARCHAR(20) PRIMARY KEY
);

（根据customers_cards表中number_card的实际类型、长度调整字段定义，确保匹配）

步骤2：批量插入目标卡号 #

从PHP生成批量插入语句，把5000个卡号一次性插入临时表：

INSERT INTO temp_cards (number_card) VALUES 
('5892101311148569'),
('6104338967224862'),
('5894631571684950'),
-- 剩余卡号依次列出
;

步骤3：关联查询 #

SELECT cc.number_card
FROM customers_cards cc
JOIN customers_card_psp ccp 
    ON cc.id = ccp.card_id
    AND ccp.psp_id = '12'
    AND ccp.status = 'old_ofogh_koorosh'
JOIN temp_cards tc ON cc.number_card = tc.number_card;

临时表的主键会自动生成索引，关联时能快速匹配，避免IN子句的低效扫描逻辑。

2. 优化现有索引 #

确保以下索引存在，能进一步加速JOIN和过滤操作：

• 给customers_cards表的number_card字段建索引：

  CREATE INDEX idx_cc_number_card ON customers_cards(number_card);
  

• 给customers_card_psp表建组合索引，覆盖JOIN和过滤条件：

  CREATE INDEX idx_ccp_psp_status_card ON customers_card_psp(psp_id, status, card_id);
  

（组合索引顺序可根据数据分布调整，用EXPLAIN语句验证最优顺序）

3. 辅助优化建议 #

• PHP端用PDO预处理语句批量绑定参数插入临时表，比直接拼接SQL更安全高效。
• 如果是MySQL，可在创建临时表时指定ENGINE=MEMORY，利用内存存储进一步提速（注意字段类型限制）。

内容的提问来源于stack exchange，提问作者Martin AJ