关联查询与分步查询比较

有一个机构表A和商品表B，商品表B中有A的编码，但没有机构名称，现在要查询机构下的商品列表，查询条件要用机构名称做全模糊查询，是分开写2条sql,先去查询机构表A，然后用机构表B的机构编码去查商品表B快还是1条sql，AB表做关联查询快。

在大多数情况下，使用一条关联查询（JOIN）会更高效，尤其是当数据库优化器能够合理利用索引时。以下是具体分析和建议：

1. 使用一条关联查询（JOIN）的优势

SELECT B.*
FROM 机构表A A
JOIN 商品表B B ON A.编码 = B.机构编码
WHERE A.机构名称 LIKE '%条件%';

优化点：

减少网络开销：只需一次数据库交互，避免两次查询的延迟。
数据库优化器的智能优化：数据库可能先通过WHERE条件过滤机构表A，再用索引快速关联商品表B（如果B.机构编码有索引）。
避免IN子句问题：如果分步查询返回大量机构编码，使用IN可能导致性能问题，而JOIN更高效。

适用场景：

商品表B的机构编码字段有索引。
机构表A的机构名称字段即使使用模糊查询，仍能通过覆盖索引或全文索引优化（如MySQL的全文索引）。

2. 分步查询的适用场景

-- 第一步：查询机构编码
SELECT 编码 FROM 机构表A WHERE 机构名称 LIKE '%条件%';

-- 第二步：查询商品
SELECT * FROM 商品表B WHERE 机构编码 IN (...);

适用场景：

机构表A的模糊查询返回结果极少（如仅1-2条），且商品表B的机构编码无索引。
机构表A的机构名称字段无索引，且表数据量极大，全表扫描成本过高，需人工分步控制。

3. 关键优化建议

为B.机构编码添加索引：

CREATE INDEX idx_org_code ON 商品表B (机构编码);

优化模糊查询：
- 如果允许，改用后缀模糊查询（LIKE '条件%'），以便利用索引。
- 考虑使用全文索引（如MySQL的FULLTEXT）替代LIKE。
监控执行计划：
- 使用EXPLAIN分析两种方法的执行计划，观察是否走索引、扫描行数等关键指标。

优先使用一条关联查询（JOIN），并在B.机构编码上建立索引。如果性能仍不理想，再考虑分步查询并结合缓存机制（如缓存机构编码列表）。实际性能需通过EXPLAIN和实际执行时间验证。

如果考虑缓存，分为2种情况

4. 本地缓存（适合单机部署）

使用 Caffeine 作为本地缓存库，缓存机构名称与编码的映射关系，减少数据库查询频率。

4.1 添加依赖

<!-- pom.xml -->
<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>caffeine</artifactId>
    <version>3.1.8</version>
</dependency>

4.2 缓存配置

@Configuration
public class CacheConfig {

    @Bean
    public Cache<String, List<String>> orgNameCache() {
        return Caffeine.newBuilder()
                .expireAfterWrite(10, TimeUnit.MINUTES)  // 缓存10分钟
                .maximumSize(1000)                        // 最大缓存1000条
                .build();
    }
}

4.3 业务逻辑实现

@Service
public class OrgService {

    @Autowired
    private OrgMapper orgMapper;  // 假设是MyBatis或JPA的机构表DAO

    @Autowired
    private Cache<String, List<String>> orgNameCache;

    /**
     * 根据机构名称模糊查询机构编码列表（优先走缓存）
     */
    public List<String> getOrgCodesByName(String orgName) {
        // 从缓存中获取
        return orgNameCache.get(orgName, key -> {
            // 缓存未命中时，查询数据库
            List<Org> orgs = orgMapper.findByNameLike("%" + orgName + "%");
            return orgs.stream().map(Org::getCode).collect(Collectors.toList());
        });
    }
}

4.4 使用缓存查询商品

@Service
public class ProductService {

    @Autowired
    private OrgService orgService;

    @Autowired
    private ProductMapper productMapper;

    public List<Product> getProductsByOrgName(String orgName) {
        // 1. 从缓存或数据库获取机构编码列表
        List<String> orgCodes = orgService.getOrgCodesByName(orgName);
        
        // 2. 查询商品表
        if (orgCodes.isEmpty()) {
            return Collections.emptyList();
        }
        return productMapper.findByOrgCodes(orgCodes);
    }
}

5. 分布式缓存（适合集群部署）

使用 Redis 缓存机构数据，支持多服务节点共享缓存。

5.1 添加依赖

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

5.2 缓存配置

@Configuration
@EnableCaching
public class RedisConfig {

    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        template.setKeySerializer(new StringRedisSerializer());
        template.setValueSerializer(new GenericJackson2JsonRedisSerializer());
        return template;
    }
}

5.3 业务逻辑实现

@Service
public class OrgService {

    @Autowired
    private OrgMapper orgMapper;

    private static final String ORG_CACHE_PREFIX = "org:name:";

    /**
     * 根据机构名称模糊查询机构编码列表（优先走Redis缓存）
     */
    @Cacheable(value = "orgCache", key = "#orgName")
    public List<String> getOrgCodesByName(String orgName) {
        List<Org> orgs = orgMapper.findByNameLike("%" + orgName + "%");
        return orgs.stream().map(Org::getCode).collect(Collectors.toList());
    }

    /**
     * 更新缓存（当机构数据变更时调用）
     */
    @CacheEvict(value = "orgCache", key = "#orgName")
    public void evictOrgCache(String orgName) {
        // 缓存自动失效
    }
}

5.4 缓存查询商品（同本地缓存）

逻辑与本地缓存场景一致，只需替换 OrgService 的实现。

6. 缓存策略优化

6.1 缓存键设计

模糊查询缓存键：直接以查询条件（如 "机构A"）作为键，适合短条件。

全量缓存机构数据：若机构表数据量较小，可缓存全量机构列表，在应用层做过滤：

@Cacheable(value = "allOrgs")
public List<Org> getAllOrgs() {
    return orgMapper.findAll();
}

// 应用层过滤
public List<String> filterOrgCodesByName(String orgName) {
    List<Org> allOrgs = getAllOrgs();
    return allOrgs.stream()
            .filter(org -> org.getName().contains(orgName))
            .map(Org::getCode)
            .collect(Collectors.toList());
}

6.2 缓存更新

定时刷新：通过 @Scheduled 定期刷新全量缓存。
事件驱动：在机构数据变更时，通过 @CacheEvict 清理相关缓存。

7. 性能注意事项

缓存穿透：对不存在的查询条件（如 "不存在机构"），缓存空结果（空列表）并设置短过期时间。
缓存击穿：对热点数据，使用互斥锁（如 Redis 的 SETNX）防止并发重建缓存。
缓存雪崩：分散缓存过期时间（如基础值 + 随机偏移）。

总结

单机场景：优先使用 Caffeine 本地缓存，性能更高。
分布式场景：使用 Redis 缓存，确保多节点数据一致性。
模糊查询优化：若性能仍不理想，可考虑 Elasticsearch 替代数据库模糊查询。

posted on 2025-04-23 17:44 qqq9527 阅读(17) 评论(0) 收藏举报

刷新页面返回顶部