MySQL LEFT JOIN+CONCAT_WS搜索性能缓慢问题求助

问题原因 #

1. 关联后数据量膨胀：当articledata与oem_articlecodes是一对多关联时，LEFT JOIN会生成远多于单表的记录数。原来单表查询只需处理少量行，现在每行都要执行CONCAT_WS和LIKE匹配，计算量直接翻倍。
2. 函数与通配符导致索引失效：即便给oem_articlecode加了索引，SQL里的LOWER(oem_articlecode)会破坏索引可用性，再加上%关键词%这种前后通配的模糊查询，数据库完全无法利用索引，只能做全表扫描+关联后的逐行匹配。
3. 跨表拼接的额外开销：把两张表的字段拼接后再做模糊匹配，数据库需要先完成JOIN操作生成临时表，再对临时表的每一行执行字符串拼接和模糊匹配，双重开销叠加导致耗时剧增。

解决办法 #

1. 拆分查询条件，避免跨表拼接 #

把原来的单条件拼接查询拆成多个独立的OR条件，让数据库可以针对每个字段单独优化：

strSQL = "select articledata.*, oem_articlecodes.oem_articlecode FROM articledata LEFT JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid WHERE (articledata.pid LIKE '%" & Replace(Lcase(x), "'", "''") & "%' OR lower(articledata.articlecode) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' OR lower(oem_articlecodes.oem_articlecode) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' OR lower(articledata.brand) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' OR lower(articledata.name) LIKE '%" & Replace(Lcase(x), "'", "''") & "%' OR lower(articledata.description) LIKE '%" & Replace(Lcase(x), "'", "''") & "%') ORDER BY articledata.articlecode ASC;"

如果数据库使用不区分大小写的字符集（如utf8mb4_general_ci），可以去掉lower()函数，进一步提升性能，同时让字段索引有机会被利用。

2. 改用全文索引替代模糊LIKE #

全文索引是MySQL专门为文本搜索优化的索引类型，性能远高于LIKE模糊查询：

• 先给需要搜索的字段创建全文索引：

ALTER TABLE articledata ADD FULLTEXT INDEX ft_article_search (articlecode, brand, name, description);
ALTER TABLE oem_articlecodes ADD FULLTEXT INDEX ft_oem_search (oem_articlecode);

• 修改查询语句为全文搜索：

strSQL = "select articledata.*, oem_articlecodes.oem_articlecode FROM articledata LEFT JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid WHERE (MATCH(articledata.articlecode, articledata.brand, articledata.name, articledata.description) AGAINST('" & Replace(Lcase(x), "'", "''") & "') OR MATCH(oem_articlecodes.oem_articlecode) AGAINST('" & Replace(Lcase(x), "'", "''") & "')) ORDER BY articledata.articlecode ASC;"

注意：全文索引默认不支持4字符以下的关键词，若需要搜索短词，可调整MySQL配置ft_min_word_len；中文搜索需确保字符集为utf8mb4。

3. 优化关联逻辑，减少无效数据 #

如果业务允许，将LEFT JOIN改为INNER JOIN，过滤掉没有匹配oem_articlecode的记录，减少需要处理的数据量。若必须保留LEFT JOIN的结果，可以拆分查询：

-- 查匹配oem_articlecode的记录
SELECT articledata.*, oem_articlecodes.oem_articlecode 
FROM articledata 
INNER JOIN oem_articlecodes ON articledata.pid = oem_articlecodes.pid 
WHERE ... 
UNION
-- 查不匹配oem_articlecode但其他字段匹配的记录
SELECT articledata.*, NULL AS oem_articlecode 
FROM articledata 
WHERE NOT EXISTS (SELECT 1 FROM oem_articlecodes WHERE articledata.pid = oem_articlecodes.pid) AND ...

4. 其他优化点 #

• 参数化查询：替换当前的字符串拼接方式，改用VBscript的参数化查询，既避免SQL注入风险，也能让数据库更好地缓存查询计划。
• 分页限制：如果查询结果量很大，添加LIMIT子句分页，减少单次查询的计算和返回数据量。

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