MySQL：关联两表查询每个客户对应最早日期的高效准确方案

表结构与约束 #

我有两张表：store_customers和additional_customers，约束如下：

• store_customers表中的每个store_id必须唯一
• additional_customers表中的每个store_id, customer_id组合必须唯一
• additional_customers表中的每个store_id都存在于store_customers表中

表定义与测试数据 #

CREATE TABLE store_customers (
   autoinc     int(10) unsigned NOT NULL AUTO_INCREMENT,
   store_id    varchar(50) NOT NULL,
   customer_id varchar(50) NOT NULL,
   datetime    datetime    NOT NULL,
   field_1     varchar(50) DEFAULT NULL,
   field_2     varchar(50) DEFAULT NULL,
   PRIMARY KEY (`autoinc`),
   UNIQUE KEY `store_id` (`store_id`),
   KEY `customer_id` (`customer_id`) -- 修正原表中错误的索引名
);
INSERT INTO store_customers (store_id, customer_id, datetime, field_1, field_2)
VALUES ('100', '10', '2011-01-01', 'aaa', 'bbb'),
       ('200', '20', '2012-01-01', 'ccc', 'ddd'),
       ('300', '20', '2013-01-01', 'eee', 'fff'),
       ('400', '40', '2014-01-01', 'ggg', 'hhh'),
       ('500', '50', '2015-01-01', 'iii', 'jjj'),
       ('600', '50', '2016-01-01', 'kkk', 'lll'),
       ('700', '70', '2017-01-01', 'mmm', 'nnn'),
       ('800', '70', '2018-01-01', 'ooo', 'ppp'),
       ('900', '90', '2019-01-01', 'qqq', 'rrr');


CREATE TABLE additional_customers (
   store_id    varchar(50) NOT NULL,
   customer_id varchar(50) NOT NULL,
   PRIMARY KEY (`store_id`, `customer_id`) -- 符合约束添加联合主键
);  
INSERT INTO additional_customers (store_id, customer_id)
VALUES ('400', '41'),
       ('400', '42'),
       ('500', '51'),
       ('500', '52'),
       ('700', '71'),
       ('700', '72'),
       ('800', '81'),
       ('800', '82'),
       ('900', '70');

查询需求 #

针对每个唯一的customer_id，返回对应最早datetime的store_customers记录：

• 对于store_customers中的客户：找到该customer_id在表中最早datetime的那条记录
• 对于additional_customers中的客户：关联其store_id对应的store_customers记录（因store_id唯一，直接取该store_id的记录即可）

预期结果：

store_id  customer_id  datetime     field_1  field_2
100       10           2011-01-01   aaa      bbb           
200       20           2012-01-01   ccc      ddd
400       40           2014-01-01   ggg      hhh
400       41           2014-01-01   ggg      hhh
400       42           2014-01-01   ggg      hhh
500       50           2015-01-01   iii      jjj
500       51           2015-01-01   iii      jjj
500       52           2015-01-01   iii      jjj
700       70           2017-01-01   mmm      nnn
700       71           2017-01-01   mmm      nnn
700       72           2017-01-01   mmm      nnn
800       81           2018-01-01   ooo      ppp
800       82           2018-01-01   ooo      ppp
900       90           2019-01-01   qqq      rrr

现有问题 #

当前查询语句存在随机选行（MySQL 5.7非标准GROUP BY行为）和效率低下（OR连接导致索引失效、全表扫描）的问题：

SELECT   sc.store_id,
         customers.customer_id,
         MIN(sc.datetime),
         sc.field_1,
         sc.field_2
FROM     (
           SELECT store_id, customer_id
           FROM   store_customers
           UNION
           SELECT store_id, customer_id
           FROM   additional_customers
         ) AS customers
JOIN     store_customers sc
         ON sc.store_id    = customers.store_id
         OR sc.customer_id = customers.customer_id
GROUP BY customer_id;

优化思路 #

1. 拆分逻辑：将主表客户与附加表客户的处理分开，再合并结果
2. 规避OR连接：OR会导致索引失效，改用明确的关联条件
3. 提前锁定最小日期记录：对store_customers按customer_id分组获取最早datetime，再关联回原表拿到完整字段
4. 利用唯一约束：store_id在store_customers中唯一，附加表客户直接通过store_id关联即可

索引优化 #

添加复合索引提升查询效率：

-- 快速获取每个customer_id的最早日期记录
ALTER TABLE store_customers ADD INDEX idx_customer_datetime (`customer_id`, `datetime`);

最终查询SQL #

-- 处理store_customers中的客户：获取每个customer_id最早的完整记录
SELECT sc.store_id, sc.customer_id, sc.datetime, sc.field_1, sc.field_2
FROM store_customers sc
JOIN (
    SELECT customer_id, MIN(datetime) AS min_datetime
    FROM store_customers
    GROUP BY customer_id
) AS min_dates
ON sc.customer_id = min_dates.customer_id AND sc.datetime = min_dates.min_datetime

UNION ALL

-- 处理additional_customers中的客户：关联对应store_id的主表记录
SELECT sc.store_id, ac.customer_id, sc.datetime, sc.field_1, sc.field_2
FROM additional_customers ac
JOIN store_customers sc ON ac.store_id = sc.store_id;

逻辑说明 #

• 第一部分：子查询min_dates先按customer_id分组得到每个客户的最早日期，再关联回store_customers获取完整字段，彻底避免GROUP BY随机选行的问题
• 第二部分：利用store_id的唯一性，直接将附加表客户与对应store_id的主表记录关联，逻辑清晰且高效
• UNION ALL：合并两部分结果，比UNION更高效（两类客户的customer_id无重叠，无需去重）

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