优化多关联同表的SQL查询：获取特定用户活动最新记录

原查询及问题 #

原查询（简化版，实际含7个类似关联）运行卡顿超10分钟，未报错但效率极低：

SELECT 
    `s`.`id`, `s`.`mobile_number`, MAX(`s`.`row_number`), `s`.`campaign_name`, `s`.`createdate`, `s`.`moddate`, 
    `se1`.`column_value` AS `first_name`, 
    `se2`.`column_value` AS `last_name`
FROM `kcms_shopper` `s`
LEFT JOIN `kcms_shopper_extend` `se1` 
    ON `s`.`mobile_number` = `se1`.`mobile_number` 
    AND `s`.`campaign_name` = `se1`.`campaign_name`
    AND `s`.`row_number` = `se1`.`row_number`
LEFT JOIN `kcms_shopper_extend` `se2` 
    ON `s`.`mobile_number` = `se2`.`mobile_number` 
    AND `s`.`campaign_name` = `se2`.`campaign_name`
    AND `s`.`row_number` = `se1`.`row_number` -- 此处错误，应为s.row_number
WHERE `s`.`row_number` = (
    SELECT MAX(`row_number`) 
    FROM `kcms_shopper_extend` sx 
    WHERE `s`.`mobile_number` = `sx`.`mobile_number`
    AND `s`.`campaign_name` = `sx`.`campaign_name`
)
AND `se1`.`column_name` = "first_name"
AND `se2`.`column_name` = "last_name"
GROUP BY `s`.`mobile_number`, `s`.`row_number`
ORDER BY `s`.`mobile_number` ASC

表结构 #

kcms_shopper表 #

CREATE TABLE `kcms_shopper` (
    `id` int(11) NOT NULL,
    `mobile_number` varchar(16) NOT NULL,
    `campaign_name` varchar(64) NOT NULL,
    `row_number` int(11) NOT NULL,
    `createdate` datetime NOT NULL DEFAULT current_timestamp(),
    `moddate` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `kcms_shopper`
    ADD PRIMARY KEY (`id`),
    ADD KEY `ix__mobile_number` (`mobile_number`) USING BTREE,
    ADD KEY `ix__campaign_name` (`campaign_name`);

ALTER TABLE `kcms_shopper`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

kcms_shopper_extend表 #

CREATE TABLE `kcms_shopper_extend` (
    `id` int(11) NOT NULL,
    `shopper_id` int(11) NOT NULL,
    `mobile_number` varchar(16) NOT NULL,
    `campaign_name` varchar(64) NOT NULL,
    `row_number` int(11) NOT NULL,
    `column_name` varchar(64) NOT NULL,
    `column_value` varchar(4096) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `kcms_shopper_extend`
    ADD PRIMARY KEY (`id`),
    ADD KEY `ix__column_name` (`column_name`) USING BTREE,
    ADD KEY `ix__mobile_number` (`mobile_number`),
    ADD KEY `ix__campaign_name` (`campaign_name`);

ALTER TABLE `kcms_shopper_extend`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;    

查询需求 #

• 获取用户的最新条目（最大row_number）
• 针对特定活动
• 指定手机号

问题分析 #

1. 关联子查询效率极低：WHERE子句中的子查询是关联查询，每扫描一条kcms_shopper记录就要执行一次子查询，重复计算量巨大
2. 关联条件错误：se2的关联条件用了se1.row_number而非s.row_number，会导致关联逻辑错误，还可能引发额外的数据过滤异常
3. LEFT JOIN被转为INNER JOIN：WHERE子句中添加se1.column_name = "first_name"和se2.column_name = "last_name"，会过滤掉没有对应扩展字段的用户，违背LEFT JOIN的初衷
4. 冗余分组与聚合：已经通过WHERE筛选了最大row_number，MAX(s.row_number)完全冗余；GROUP BY字段不符合标准SQL规范，可能导致数据异常
5. 缺失复合索引：现有索引都是单字段，无法覆盖关联、过滤、排序的组合查询需求，导致全表扫描

优化方案 #

1. 添加必要的复合索引 #

先创建覆盖查询场景的复合索引，大幅提升查询速度：

-- 针对kcms_shopper：按手机号、活动名、行号过滤，覆盖查询字段
ALTER TABLE `kcms_shopper` ADD INDEX `ix_mobile_campaign_row` (`mobile_number`, `campaign_name`, `row_number`);

-- 针对kcms_shopper_extend：按手机号、活动名、行号、字段名过滤，覆盖关联和查询字段
ALTER TABLE `kcms_shopper_extend` ADD INDEX `ix_mobile_campaign_row_column` (`mobile_number`, `campaign_name`, `row_number`, `column_name`, `column_value`);

2. 优化后的查询语句（MySQL 8.0+支持CTE） #

使用非关联子查询预取每个用户+活动的最大行号，避免重复计算；修正关联条件；将扩展字段的过滤移至ON子句保留LEFT JOIN特性（若不需要保留无扩展字段的用户，可改为INNER JOIN）：

-- 预获取每个用户+活动的最新row_number
WITH latest_shopper AS (
    SELECT 
        mobile_number, 
        campaign_name, 
        MAX(row_number) AS max_row
    FROM kcms_shopper
    -- 直接过滤特定活动和指定手机号，减少数据量
    WHERE campaign_name = '你的特定活动名' 
      AND mobile_number = '指定手机号'
    GROUP BY mobile_number, campaign_name
)
SELECT 
    s.id,
    s.mobile_number,
    s.row_number, -- 已筛选最大行号，无需MAX聚合
    s.campaign_name,
    s.createdate,
    s.moddate,
    se1.column_value AS first_name,
    se2.column_value AS last_name
FROM kcms_shopper s
JOIN latest_shopper ls 
    ON s.mobile_number = ls.mobile_number
    AND s.campaign_name = ls.campaign_name
    AND s.row_number = ls.max_row
LEFT JOIN kcms_shopper_extend se1
    ON s.mobile_number = se1.mobile_number
    AND s.campaign_name = se1.campaign_name
    AND s.row_number = se1.row_number
    AND se1.column_name = 'first_name' -- 将字段过滤移至ON子句，保留LEFT JOIN
LEFT JOIN kcms_shopper_extend se2
    ON s.mobile_number = se2.mobile_number
    AND s.campaign_name = se2.campaign_name
    AND s.row_number = se2.row_number -- 修正关联条件
    AND se2.column_name = 'last_name'
ORDER BY s.mobile_number ASC;

3. 替代方案（MySQL < 8.0无CTE支持） #

如果你的MySQL版本不支持CTE，可以改用子查询作为临时表：

SELECT 
    s.id,
    s.mobile_number,
    s.row_number,
    s.campaign_name,
    s.createdate,
    s.moddate,
    se1.column_value AS first_name,
    se2.column_value AS last_name
FROM kcms_shopper s
JOIN (
    SELECT 
        mobile_number, 
        campaign_name, 
        MAX(row_number) AS max_row
    FROM kcms_shopper
    WHERE campaign_name = '你的特定活动名' 
      AND mobile_number = '指定手机号'
    GROUP BY mobile_number, campaign_name
) ls 
    ON s.mobile_number = ls.mobile_number
    AND s.campaign_name = ls.campaign_name
    AND s.row_number = ls.max_row
LEFT JOIN kcms_shopper_extend se1
    ON s.mobile_number = se1.mobile_number
    AND s.campaign_name = se1.campaign_name
    AND s.row_number = se1.row_number
    AND se1.column_name = 'first_name'
LEFT JOIN kcms_shopper_extend se2
    ON s.mobile_number = se2.mobile_number
    AND s.campaign_name = se2.campaign_name
    AND s.row_number = se2.row_number
    AND se2.column_name = 'last_name'
ORDER BY s.mobile_number ASC;

优化效果说明 #

• 预取最大行号的子查询仅执行一次，而非每条记录都执行，大幅减少计算量
• 复合索引覆盖了查询的过滤、关联、排序字段，避免全表扫描
• 修正关联条件，确保逻辑正确性
• 移除冗余的聚合和分组，符合SQL规范

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