大型电商平台跨40个数据库标题搜索的高效实现方案咨询

嘿，针对你维护40个电商数据库的搜索需求，目前靠手动拼接一堆UNION ALL的方式不仅维护起来头疼（加个新表就得改SQL），性能也会随着表数量增加越来越拉胯。这里给你几个更靠谱的优化思路：

1. 建立全局搜索索引表（最简落地方案） #

这是最容易快速见效的方式：专门建一张全局搜索索引表，把所有业务表中需要搜索的字段（比如id_no、title、mrp、store、image、offers）同步到这张表里，同时记录数据来源（比如source_db、source_table字段）。

实现方式： #

• 实时同步：给每个业务表加触发器，当原表新增/修改/删除数据时，自动同步到索引表（适合对实时性要求高的场景）。
• 定时同步：用脚本（比如Python/Shell）配合定时任务（crontab），定期拉取所有业务表的最新数据更新索引表（适合数据更新频率不高的场景，比如商品信息不会频繁变动）。

搜索示例SQL： #

SELECT id_no, offers, image, title, mrp, store 
FROM global_search_index 
WHERE MATCH(title) AGAINST(? IN BOOLEAN MODE)
LIMIT 18;

（注意用参数化查询替代直接拼$searchkey，避免SQL注入）

优点： #

• 搜索逻辑极简，只需要查一张表，性能拉满；
• 后续新增业务表，只需要同步数据到索引表即可，不用改搜索SQL；
• 可以给title单独建全文索引，比跨表UNION后再搜索高效N倍。

缺点： #

• 需要额外的存储资源；
• 实时性取决于同步方式，定时同步会有延迟。

2. 用存储过程动态生成查询（适合不想加额外表的场景） #

如果不想维护额外的索引表，可以写一个存储过程，自动遍历所有需要搜索的数据库表，动态拼接UNION ALL语句，最后执行查询并返回结果。

示例（MySQL存储过程）： #

DELIMITER //
CREATE PROCEDURE search_all_tables(IN search_key VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE db_table VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT CONCAT(db_name, '.', table_name) 
        FROM your_table_list; -- 这里需要维护一个存储所有要搜索的库表名的表
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    SET @sql = '';
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO db_table;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT(@sql, '(SELECT id_no, offers, image, title, mrp, store FROM ', db_table, ' WHERE MATCH(title) AGAINST(?)) UNION ALL ');
    END LOOP;
    
    -- 去掉最后多余的UNION ALL
    SET @sql = LEFT(@sql, LENGTH(@sql) - 10);
    -- 加上分页
    SET @sql = CONCAT(@sql, ' LIMIT 18');
    
    -- 执行动态SQL，用参数化避免注入
    PREPARE stmt FROM @sql;
    SET @key = search_key;
    EXECUTE stmt USING @key;
    DEALLOCATE PREPARE stmt;
    
    CLOSE cur;
END //
DELIMITER ;

调用时直接执行：CALL search_all_tables('你的搜索关键词');

优点： #

• 不用手动维护长长的UNION语句，新增表只需要更新your_table_list即可；
• 保留原数据结构，不用额外同步数据。

缺点： #

• 每次搜索都要动态拼接SQL，性能不如全局索引表；
• 存储过程的调试和维护有一定门槛；
• 跨40个表查询，数据库负载还是会比较高。

3. 引入全文搜索引擎（电商长远最优解） #

如果是大型电商平台，长远来看，用专门的全文搜索引擎（比如Elasticsearch）才是最优选择。把所有商品数据同步到ES中，由ES负责处理全文搜索、分页、排序、权重计算等逻辑。

为什么选ES？ #

• 原生支持海量数据的全文搜索，性能比数据库的MATCH AGAINST强几个量级；
• 支持分词（比如中文分词、同义词）、模糊搜索、按相关性排序，更符合电商用户的搜索习惯；
• 可以轻松对接40个数据源，通过Logstash或自定义脚本同步数据；
• 支持分布式部署，后续数据量增长也能轻松扩容。

简单流程： #

1. 部署ES集群；
2. 创建商品索引，定义好字段映射（比如title设为text类型，开启分词）；
3. 同步所有业务表的商品数据到ES；
4. 搜索时直接调用ES的API，返回前18条结果。

优点： #

• 搜索体验和性能碾压数据库原生方案；
• 支持复杂的搜索逻辑（比如按销量、价格排序，多条件过滤）；
• 扩展性极强，适合电商业务的长期发展。

缺点： #

• 需要额外部署和维护ES集群，有一定学习成本；
• 数据同步需要额外开发。

几个额外的小优化点 #

• 去掉重复的搜索条件：你当前SQL里同时用了MATCH(title) AGAINST(...)和title LIKE '%$searchkey%'，这完全没必要！LIKE '%xxx%'会导致索引失效，反而拖慢查询速度，只用全文索引的MATCH就足够了；
• 必须用参数化查询：直接把$searchkey拼到SQL里会有严重的SQL注入风险，一定要用预处理语句或者ORM的参数绑定；
• 避免跨表后分页：如果用UNION ALL的方式，数据库会先把所有符合条件的结果查出来再截断取前18条，数据量大的时候会非常慢，用全局索引表或ES就能避免这个问题。

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