MySQL百万级数据下高效对比两表OEM字段（含LIKE非完全匹配）

你的查询性能极差的核心原因是LIKE '%xxx%'无法利用常规B-tree索引，导致数据库需要对两张表做全量笛卡尔积式匹配——50万行的product表和15万行的oem表组合，计算量直接拉满，自然会超时甚至返回503错误。以下是按优先级排序的优化方案：

1. 先排除完全匹配的记录，缩小处理范围 #

先筛选出product表中oem与oem表完全匹配的记录，后续只处理剩下的非匹配数据，直接减少计算量：

-- 创建临时表存储所有完全匹配的product id
CREATE TEMPORARY TABLE matched_oem_ids AS
SELECT DISTINCT p.id
FROM product p
JOIN oem ON p.oem = oem.oem;

-- 排除完全匹配的记录，再找包含oem的非规范数据
SELECT p.*
FROM product p
WHERE p.id NOT IN (SELECT id FROM matched_oem_ids)
AND EXISTS (
    SELECT 1
    FROM oem
    WHERE p.oem LIKE CONCAT('%', oem.oem, '%')
)
ORDER BY p.id ASC
LIMIT 5000;

临时表matched_oem_ids可以利用p.oem和oem.oem的索引快速生成，剩下的product行数会大幅减少，后续匹配压力骤降。

2. 改用全文索引加速包含匹配 #

MySQL的B-tree索引对%xxx%前缀模糊匹配无效，但全文索引可以高效处理包含关系，这是提升性能的关键：

-- 给两张表的oem字段创建全文索引
ALTER TABLE product ADD FULLTEXT INDEX ft_product_oem (oem);
ALTER TABLE oem ADD FULLTEXT INDEX ft_oem_oem (oem);

注意事项： #

如果你的OEM字符串长度小于MySQL默认最小词长（默认是4），需要修改配置：

• 临时生效：SET GLOBAL ft_min_word_len = 2;（需重启MySQL）
• 永久生效：在my.cnf/my.ini中添加ft_min_word_len=2，重启MySQL

然后用全文搜索替代LIKE，查询速度会提升几个量级：

SELECT p.*
FROM product p
LEFT JOIN matched_oem_ids mo ON p.id = mo.id
WHERE mo.id IS NULL
AND EXISTS (
    SELECT 1
    FROM oem
    -- 用""实现精确包含匹配，效果和LIKE '%xxx%'一致
    WHERE MATCH(p.oem) AGAINST(CONCAT('"', oem.oem, '"') IN BOOLEAN MODE)
)
ORDER BY p.id ASC
LIMIT 5000;

3. 分批次处理，避免生产环境超时 #

不要一次性查询所有数据，按id分批次拉取，避免结果集过大导致超时：

-- 第一次查询id范围1-10000
SELECT p.*
FROM product p
LEFT JOIN matched_oem_ids mo ON p.id = mo.id
WHERE mo.id IS NULL
AND EXISTS (
    SELECT 1
    FROM oem
    WHERE MATCH(p.oem) AGAINST(CONCAT('"', oem.oem, '"') IN BOOLEAN MODE)
)
AND p.id BETWEEN 1 AND 10000
ORDER BY p.id ASC;

-- 后续依次查询10001-20000、20001-30000...以此类推

4. 直接用MySQL导出CSV，比客户端导出快 #

如果要导出结果，用MySQL原生的INTO OUTFILE命令，避免客户端传输大量数据，速度更快：

SELECT p.*
INTO OUTFILE '/绝对路径/your_output.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM product p
LEFT JOIN matched_oem_ids mo ON p.id = mo.id
WHERE mo.id IS NULL
AND EXISTS (
    SELECT 1
    FROM oem
    WHERE MATCH(p.oem) AGAINST(CONCAT('"', oem.oem, '"') IN BOOLEAN MODE)
)
ORDER BY p.id ASC;

注意：需确保MySQL服务有写入该路径的权限，路径必须是绝对路径。

额外优化：过滤无效OEM值 #

如果oem表中有长度过短（比如1位）或无意义的字符串，先过滤掉减少无效匹配：

CREATE TEMPORARY TABLE valid_oems AS
SELECT DISTINCT oem
FROM oem
WHERE LENGTH(oem) >= 2; -- 根据实际情况调整最小长度

后续查询用valid_oems替代原oem表，进一步降低匹配次数。

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