mysql left join 多对多 分页记录数不对的问题

一对多时的参考

情景阐述

event、group、work 三者的关系如下ER图，它们的主要属性为name，

在物理模型上表现为：event、group、work、event_group 表；以下是DDL

DROP TABLE IF EXISTS `event` ;

CREATE TABLE `event` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '',
  `work_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `event_group` ;

CREATE TABLE `event_group` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(11) unsigned DEFAULT NULL,
  `group_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `group` ;

CREATE TABLE `group` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `work` ;

CREATE TABLE `work` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('1','事件1','1');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('2','event-2','1');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('3','event-3','1');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('4','event-4','3');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('5','event-5','4');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('6','event-6','5');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('7','event-7','5');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('8','event-8','5');
INSERT INTO `event` (`id`, `name`, `work_id`) VALUES ('9','event-9','5');

INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('1','1','1');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('2','2','1');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('3','2','3');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('4','9','1');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('5','2','4');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('6','1','4');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('7','3','6');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('8','2','2');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('9','4','2');
INSERT INTO `event_group` (`id`, `event_id`, `group_id`) VALUES ('10','7','2');

INSERT INTO `group` (`id`, `name`) VALUES ('1','group-1');
INSERT INTO `group` (`id`, `name`) VALUES ('2','group-2');
INSERT INTO `group` (`id`, `name`) VALUES ('3','group-3');
INSERT INTO `group` (`id`, `name`) VALUES ('4','group-4');
INSERT INTO `group` (`id`, `name`) VALUES ('5','group-5');

INSERT INTO `work` (`id`, `name`) VALUES ('1','work-1');
INSERT INTO `work` (`id`, `name`) VALUES ('2','work-2');
INSERT INTO `work` (`id`, `name`) VALUES ('3','work-3');
INSERT INTO `work` (`id`, `name`) VALUES ('4','work-4');
INSERT INTO `work` (`id`, `name`) VALUES ('5','work-5');
INSERT INTO `work` (`id`, `name`) VALUES ('6','work-6');
INSERT INTO `work` (`id`, `name`) VALUES ('7','work-7');
INSERT INTO `work` (`id`, `name`) VALUES ('8','work-8');
INSERT INTO `work` (`id`, `name`) VALUES ('9','work-9');
INSERT INTO `work` (`id`, `name`) VALUES ('10','work-10');

需求

从event 表中查出在指定group中的记录（需要包含workName、groupName），并分页。（为了效率，使用Mybatis的ResultMapper中的collection标签，来解决N+1查询问题）
N+1 Selects Problem：
• 你执行了一个单独的 SQL 语句来获取结果的一个列表（就是“+1”）。
• 对列表返回的每条记录，你执行一个 select 查询语句来为每条记录加载详细信息（就是“N”）。

出现现象

错误示范：直接使用left join ，可以发现查出的记录中，存在多条重复记录。（以下使用 w.name like '%work%' 是为了模拟还需要筛选的其他条件）

select e.*,w.name as workName,eg.group_id from event as e 

left join work as w
on e.work_id=w.id

left join event_group as eg
on e.id=eg.event_id  

where eg.group_id in (1,2,3,4)
and w.name like '%work%'

order by e.id desc
limit 0,7

粗略解决(只考虑正确性的情况下)

1. 先查出筛选满足条件的 主表（上例中是event）的记录，并使用limit 来分页

select e.id from event as e 

left join work as w
on e.work_id=w.id

where e.id in (select distinct(ieg.event_id) from event_group ieg where ieg.group_id in (1,2,3,4))
and w.name like '%work%'

order by e.id desc
limit 0,5

2. 联表，查出需要的信息。在不考虑性能的情况下，综合SQL如下。查看结果，发现对于主表event而言，是有5条数据的但是总结果数>5，这是因为event关联多个group，这需要通过Mybatis的collection，即可正确映射1:n关系，即Event实体中，存在List。使用教程请参考：mybatis使用collection

select ew.*,egg.name as groupName from (

    select e.*,w.name as workName from event as e 

    left join work as w
    on e.work_id=w.id

    where e.id in (select distinct(ieg.event_id) from event_group ieg where ieg.group_id in (1,2,3,4))
    and w.name like '%work%'

    order by e.id desc
    limit 0,5
)as ew

left join(
    select g.*,eg.event_id from event_group as eg  join `group` as g 
    on g.id = eg.group_id
)as egg
on ew.id=egg.event_id  

推荐做法：使用exists来包装条件

此种方式的思路与上面的无异，但可以发现将条件放在exists中，更清晰，且性能更好（当然要orderby、join用到的列上，有创建索引）

select fe.*,w.name as workName,egg.name as groupName from (

    select e.id from event as e 

    where exists(
        select 1 from event ie 

        left join work as w
            on ie.work_id=w.id
        
        left join(
            select g.name,eg.* from event_group as eg  join `group` as g 
            on g.id = eg.group_id
        )as egg
        on ie.id=egg.event_id  

        where ie.id=e.id
        
        and egg.group_id in(1,2,3,4)
        and w.name like '%work%'
    )

    order by e.id desc
    limit 0,5
)as e,event fe

left join work w 
on fe.work_id=w.id

left join(
    select g.*,eg.event_id from event_group as eg  join `group` as g 
    on g.id = eg.group_id
)as egg
on fe.id=egg.event_id  


where e.id=fe.id

order by e.id desc