寻求替代DISTINCT与REGEXP的更快MySQL查询方案

原查询的性能瓶颈在于REGEXP或LIKE这类子字符串匹配操作——它们无法利用索引，再加上两次子查询的重复计算，直接导致速度极慢。下面提供两种优化思路，分别适用于无法修改表结构和可以调整表结构的场景：

方案一：不修改表结构，用CTE拆分组合位置（MySQL 8.0+） #

利用数字辅助表拆分combined_locations为单独位置条目，结合用户的偏好集合做匹配，替代低效的正则匹配：

WITH user_prefs AS (
    -- 一次性获取用户1的Yes/No位置集合
    SELECT
        GROUP_CONCAT(CASE WHEN user_selection = 'Yes' THEN user_locations END) AS yes_locs,
        GROUP_CONCAT(CASE WHEN user_selection = 'No' THEN user_locations END) AS no_locs
    FROM users
    WHERE user_id = 1
),
split_combined AS (
    -- 拆分每个组合位置为单独条目
    SELECT
        l.combined_locations,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(l.combined_locations, ',', n.num), ',', -1)) AS single_loc
    FROM locations l
    -- 数字辅助表，根据实际组合位置的最大数量调整（比如最多5个就加到5）
    CROSS JOIN (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n
    WHERE n.num <= 1 + LENGTH(l.combined_locations) - LENGTH(REPLACE(l.combined_locations, ',', ''))
)
SELECT DISTINCT sc.combined_locations
FROM split_combined sc
JOIN user_prefs up ON 1=1
WHERE
    -- 确保组合中所有位置都在用户的Yes列表里
    NOT EXISTS (
        SELECT 1
        FROM split_combined sc2
        WHERE sc2.combined_locations = sc.combined_locations
        AND FIND_IN_SET(sc2.single_loc, up.yes_locs) = 0
    )
    -- 确保组合中没有位置在用户的No列表里
    AND NOT EXISTS (
        SELECT 1
        FROM split_combined sc2
        WHERE sc2.combined_locations = sc.combined_locations
        AND FIND_IN_SET(sc2.single_loc, up.no_locs) > 0
    );

优势： #

• 仅查询一次users表获取偏好，避免重复计算
• 用FIND_IN_SET替代REGEXP，匹配效率显著提升
• 通过拆分后检查所有子位置，逻辑更清晰，避免原查询中两次子查询的冗余

方案二：调整表结构（最优性能方案） #

原表将多个位置存储在单个字符串中，是性能问题的根源。建议拆分为关联表，彻底解决字符串匹配的性能瓶颈：

1. 创建拆分后的表结构 #

-- 存储组合位置的主表
CREATE TABLE location_groups (
    group_id INT PRIMARY KEY AUTO_INCREMENT,
    group_name VARCHAR(255) NOT NULL UNIQUE COMMENT '比如"Arizona, California"'
);

-- 存储组合与单个位置的关联关系
CREATE TABLE group_locations (
    group_id INT NOT NULL,
    location_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (group_id, location_name),
    FOREIGN KEY (group_id) REFERENCES location_groups(group_id),
    INDEX idx_location (location_name) -- 给单个位置加索引，加速匹配
);

2. 导入数据（一次性操作） #

用类似方案一的CTE拆分原locations表的数据，插入到新表中。

3. 高效查询 #

SELECT lg.group_name
FROM location_groups lg
JOIN group_locations gl ON lg.group_id = gl.group_id
-- 关联用户标记为Yes的位置
JOIN users u_yes ON gl.location_name = u_yes.user_locations
    AND u_yes.user_id = 1
    AND u_yes.user_selection = 'Yes'
-- 左关联用户标记为No的位置，排除存在No的组合
LEFT JOIN users u_no ON gl.location_name = u_no.user_locations
    AND u_no.user_id = 1
    AND u_no.user_selection = 'No'
WHERE u_no.user_id IS NULL
-- 分组确保组合的所有位置都匹配到Yes（避免部分匹配）
GROUP BY lg.group_id, lg.group_name
HAVING COUNT(DISTINCT gl.location_name) = (
    SELECT COUNT(DISTINCT location_name)
    FROM group_locations
    WHERE group_id = lg.group_id
);

优势： #

• 所有匹配都是等值匹配，完全利用索引，性能比字符串匹配提升几个数量级
• 数据结构更规范，后续扩展或维护更方便

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