如何提升MySQL中strcmp比较相似姓名字符串的性能？

核心问题分析 #

你当前查询性能糟糕的根源，是对name字段的TRIM/LCASE/REPLACE函数处理导致数据库无法利用索引，每次自连接都要全表扫描计算处理后的值，数据量越大，性能损耗越明显。

具体优化步骤 #

• 新增标准化姓名字段并预计算值
  给人员表添加一个存储标准化后姓名的字段，比如normalized_name（建议设为VARCHAR类型，长度匹配处理后姓名的最大可能长度）：

  ALTER TABLE person ADD COLUMN normalized_name VARCHAR(255) COMMENT '标准化后的姓名（去空格、转小写）';
  

  批量计算现有数据的标准化值：

  UPDATE person SET normalized_name = REPLACE(LCASE(TRIM(name)), ' ', '');
  

• 创建联合索引
  针对同组判定的关联字段（比如group_id）和normalized_name创建联合索引，让数据库能快速定位同组且姓名标准化后相同的记录：

  CREATE INDEX idx_person_group_normalized ON person(group_id, normalized_name);
  

• 重构重复项查询逻辑
  把原来基于函数处理的自连接条件，替换为直接比较normalized_name，同时通过id过滤避免重复配对（比如只保留s.id < t.id的组合，防止A和B、B和A被重复识别）：

  SELECT s.id AS duplicate_id, t.id AS original_id
  FROM person s
  JOIN person t ON s.group_id = t.group_id 
                AND s.normalized_name = t.normalized_name 
                AND s.id < t.id;
  

  用这个查询替代原来的视图，性能会有质的提升。

• 维护标准化字段的自动更新
  为确保后续新增/更新数据时normalized_name始终同步，添加触发器：

  • 插入时自动生成标准化姓名：

    DELIMITER //
    CREATE TRIGGER trg_person_insert_normalize BEFORE INSERT ON person
    FOR EACH ROW
    BEGIN
        SET NEW.normalized_name = REPLACE(LCASE(TRIM(NEW.name)), ' ', '');
    END //
    DELIMITER ;
    

  • 更新姓名时同步更新标准化字段：

    DELIMITER //
    CREATE TRIGGER trg_person_update_normalize BEFORE UPDATE ON person
    FOR EACH ROW
    BEGIN
        IF NEW.name != OLD.name THEN
            SET NEW.normalized_name = REPLACE(LCASE(TRIM(NEW.name)), ' ', '');
        END IF;
    END //
    DELIMITER ;
    

额外建议 #

如果你的删除逻辑需要关联其他表，可以基于上述高效查询得到的重复ID列表，继续使用现有存储过程处理删除逻辑即可——问题核心出在查找环节而非删除环节。

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