多表查询N+1/1次查询 N+1次查询（查询非集合/集合类型）

本文链接：https://blog.youkuaiyun.com/qq_41078128/article/details/130600517

本文探讨了数据库查询中的N+1查询问题，特别是在Java环境中处理Customer和Address类的关联查询。通过CustomerMapper和AddressMapper的XML配置，展示了如何引发N+1查询以及如何优化为1次查询。优化方法包括利用关联映射减少查询次数，以及在业务层通过代码装配数据，将地址按customer_id归集，避免多次单独查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

客户类Customer id,name,username,password,addressLists

地址类Address id,province,city,address,customer

客户表tb_customer id,name,username,password

地址表tb_address id,province,city,address,cutomer_id

=========================================================================

n+1查询查询全部地址，同时查询地址相应的客户数据

（N）根据主键（外键）查询客户

CustomerMapper.xml

<mapper namespace="com.demo.mapper.CustomerMapper">
    <select id="selectById" resultType="Customer">
        select id,name,username,password from tb_customer where id = #{id}
    </select>
</mapper>

（1）查询地址在查询对应客户

n+1查询，先查询地址（1），在根据地址中外键查询对应客户（N）

AddressMapper.xml <association>中column对应的是CustomerMapper.xml中的占位变量#{id}

<mapper namespace="com.demo.mapper.AddressMapper">

    <resultMap id="addr" type="Address">
        <id column="id" property="id"></id>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="address" property="address"/>
        <association property="customer" javaType="Customer"
                    select = "com.demo.mapper.CustomerMapper.selectById"
                    column="{id = customer_id}" fetchType="eager"></association>
    </resultMap>

    <select id="selectAllAddr" resultMap="addr">
            select id,province,city,address,customer_id from tb_address
    </select>
</mapper>

1次查询查询全部地址数据，同时查询地址对应的客户的数据

     <resultMap id="AddrMap2" type="Address">
        <id column="id" property="id"/>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="address" property="address"/>
        <association property="customer" javaType="Customer">
            <id column="cus_id" property="id"/>
            <result column="name" property="name"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>

    <select id="selectAllAddr2" resultType="AddrMap2">
        select a.id,a.province,a.city,a.address,c.id cus_id,c.name,c.username,c.password                 
        from tb_address a  left join
             tb_customer c on a.customer_id = c.id
    </select>

N+1次查询查询全部客户数据，再查询客户对应的地址

（1）次查询查客户

<mapper namespace="com.demo.mapper.CustomerMapper">
    <resultMap id="CusMap" type="Customer">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="addressList" javaType="java.util.List"
                ofType="Address" select="com.demo.mapper.AddressMapper.selectById"
                column="{customer_id = id}"></collection>
    </resultMap>
    
    <select id="selectAllCustomer" resultMap="CusMap">
        select id,name,username,password from tb_customer
    </select>
</mapper>

（N）次查询查对应地址

<select id="selectById" resultType="Address">
        select id,province,city,address,customer_id from tb_address 
        where customer_id = #{customer_id}
</select>

1次查询查询全部客户数据，再查询客户对应的地址


    <resultMap id="cusMap" type="Customer">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>

        <collection property="addressList" javaType="java.util.List"
            ofType="Address" column="{id = customer_id}">
            <id column="add_id" property="id"/>
            <result column="province" property="province"/>
            <result column="city" property="city"/>
            <result column="address" property="address"/>
        </collection>
    </resultMap>

    <select id="selectAllCustomer2" resultMap="cusMap">
        select c.id,c.name,c.username,c.password,t.id add_id,t.province,t.city,t.address             
         from tb_customer c left join
	         tb_address t on c.id = t.customer_id
    </select>

业务装配使用代码，把数据直接的关系维护起来

地址集合按照key是customer_id，value是地址对象保存在一个map中，

客户集合相同的key将map中value存在客户中地址集合，同时遍历地址集合将对象放在响应地址中