mybatis的多表联查注意点

MyBatis 多表联查是一个非常重要的功能，在使用时需要注意多个关键点。下面我详细讲解多表联查的注意事项、实现方式和最佳实践。

核心注意事项
1. 结果映射（ResultMap）的配置
一对一关联（association）
xml
<resultMap id="OrderDetailMap" type="Order">
    <id property="id" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="createTime" column="create_time"/>
    
    <!-- 一对一关联用户信息 -->
    <association property="user" javaType="User">
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
    </association>
</resultMap>
注意事项：

确保 column 属性与 SQL 查询的列名一致

javaType 可以省略，MyBatis 会自动推断

使用 id 标签标记主键字段，提高性能

一对多关联（collection）
xml
<resultMap id="UserOrdersMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    
    <!-- 一对多关联订单 -->
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>
注意事项：

ofType 指定集合中元素的类型

一对多查询可能产生笛卡尔积，需要考虑分页问题

2. SQL 编写注意事项
列名冲突解决
sql
<!-- 错误的写法 - 列名冲突 -->
SELECT * 
FROM users u 
JOIN orders o ON u.id = o.user_id

<!-- 正确的写法 - 明确指定列别名 -->
SELECT 
    u.id as user_id,
    u.username as user_username,
    u.email as user_email,
    o.id as order_id,
    o.order_no as order_no,
    o.amount as order_amount
FROM users u 
JOIN orders o ON u.id = o.user_id
使用列别名规范
sql
SELECT 
    u.id as user_id,
    u.name as user_name,
    u.email as user_email,
    o.id as order_id,
    o.order_no as order_no,
    o.create_time as order_create_time,
    p.id as product_id,
    p.name as product_name,
    p.price as product_price
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
3. N+1 查询问题
问题示例
xml
<!-- 方式1：会产生N+1查询（不推荐） -->
<resultMap id="UserWithOrdersMap" type="User">
    <collection property="orders" column="id" 
                select="selectOrdersByUserId"/>
</resultMap>

<select id="selectUserWithOrders" resultMap="UserWithOrdersMap">
    SELECT * FROM users WHERE id = #{id}
</select>

<select id="selectOrdersByUserId" resultType="Order">
    SELECT * FROM orders WHERE user_id = #{userId}
</select>
解决方案：使用 JOIN 查询
xml
<!-- 方式2：使用JOIN一次性查询（推荐） -->
<resultMap id="UserWithOrdersJoinMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>

<select id="selectUserWithOrdersJoin" resultMap="UserWithOrdersJoinMap">
    SELECT 
        u.id as user_id,
        u.username,
        o.id as order_id,
        o.order_no,
        o.amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>
4. 分页查询的特殊处理
分页时的注意事项
java
// 在Service层处理分页
public PageInfo<UserVO> getUsersWithOrders(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);
    
    // 查询主表数据
    List<User> users = userMapper.selectUsersWithOrders();
    
    // 手动处理关联数据（避免分页不准）
    return new PageInfo<>(users);
}
xml
<!-- 分页查询时建议先查询主表，再查询关联表 -->
<select id="selectUsersWithOrders" resultMap="UserBaseMap">
    SELECT id, username, email FROM users
</select>

<select id="selectOrdersByUserIds" resultType="Order">
    SELECT * FROM orders 
    WHERE user_id IN
    <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
</select>
5. 复杂关联查询示例
多层级关联
xml
<resultMap id="OrderDetailResultMap" type="Order">
    <id property="id" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="amount" column="amount"/>
    
    <!-- 一对一：用户信息 -->
    <association property="user" javaType="User">
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="phone" column="phone"/>
    </association>
    
    <!-- 一对多：订单项 -->
    <collection property="orderItems" ofType="OrderItem">
        <id property="id" column="item_id"/>
        <result property="quantity" column="quantity"/>
        <result property="price" column="price"/>
        
        <!-- 订单项关联商品 -->
        <association property="product" javaType="Product">
            <id property="id" column="product_id"/>
            <result property="name" column="product_name"/>
            <result property="category" column="category"/>
        </association>
    </collection>
    
    <!-- 一对一：收货地址 -->
    <association property="address" javaType="Address">
        <id property="id" column="address_id"/>
        <result property="province" column="province"/>
        <result property="city" column="city"/>
        <result property="detail" column="detail"/>
    </association>
</resultMap>

<select id="selectOrderDetail" resultMap="OrderDetailResultMap">
    SELECT 
        o.id as order_id,
        o.order_no,
        o.amount,
        
        u.id as user_id,
        u.username,
        u.phone,
        
        oi.id as item_id,
        oi.quantity,
        oi.price,
        
        p.id as product_id,
        p.name as product_name,
        p.category,
        
        a.id as address_id,
        a.province,
        a.city,
        a.detail
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.id
    LEFT JOIN addresses a ON o.address_id = a.id
    WHERE o.id = #{orderId}
</select>
6. 性能优化建议
延迟加载配置
xml
<!-- 在mybatis-config.xml中配置 -->
<settings>
    <!-- 开启延迟加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

<!-- 在Mapper中指定延迟加载 -->
<resultMap id="UserLazyMap" type="User">
    <collection property="orders" column="id" 
                select="selectOrdersByUserId"
                fetchType="lazy"/>
</resultMap>
使用二级缓存
xml
<!-- 在Mapper XML中开启缓存 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>

<!-- 在关联查询中指定使用缓存 -->
<association property="user" column="user_id" 
             javaType="User" select="selectUserById"
             fetchType="lazy"/>
7. 代码结构最佳实践
VO对象设计
java
// 为复杂查询创建专门的VO对象
@Data
public class OrderDetailVO {
    private Long orderId;
    private String orderNo;
    private BigDecimal amount;
    
    // 用户信息
    private Long userId;
    private String username;
    private String phone;
    
    // 商品信息
    private List<OrderItemVO> orderItems;
    
    // 地址信息
    private String province;
    private String city;
    private String detail;
    
    @Data
    public static class OrderItemVO {
        private Long productId;
        private String productName;
        private Integer quantity;
        private BigDecimal price;
    }
}
对应的简化ResultMap
xml
<resultMap id="OrderDetailVOMap" type="OrderDetailVO">
    <id property="orderId" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="amount" column="amount"/>
    <result property="userId" column="user_id"/>
    <result property="username" column="username"/>
    <result property="phone" column="phone"/>
    <result property="province" column="province"/>
    <result property="city" column="city"/>
    <result property="detail" column="detail"/>
    
    <collection property="orderItems" ofType="OrderDetailVO$OrderItemVO">
        <result property="productId" column="product_id"/>
        <result property="productName" column="product_name"/>
        <result property="quantity" column="quantity"/>
        <result property="price" column="price"/>
    </collection>
</resultMap>
总结
多表联查的关键注意事项：

列名别名：务必为所有可能冲突的列设置明确的别名

结果映射：合理使用 association 和 collection

避免N+1：优先使用 JOIN 查询而非嵌套查询

分页处理：复杂关联查询时注意分页准确性

性能优化：合理使用延迟加载和缓存

代码结构：为复杂查询创建专门的VO对象

SQL可读性：保持SQL语句的清晰和可维护性