SELECT优化 数据量过大 不要用IN

场景

两天SQL,第二条SQL需要用到第一条SQL查询出来的ID,并需要用SELECT 中的IN

相关代码

String sql = "select rnn.number_id NUMBER_ID from rm_nbrspace rn,rr_nbrspace_number rnn where rn.id = rnn.nbrspace_id and rn.spec_id = ? " +
    " and rn.sharding_id = ? and rn.id = ? ";
String field = "NUMBER_ID";
Object[] params = new Object[]{TypeUtils.castToBigDecimal(SPEC_ID), TypeUtils.castToBigDecimal(currentShardingId), TypeUtils.castToBigDecimal(numnerId)};
this.commonDataQuery(currentShardingId, page, queryCriteria, sql, field, params);

@Override
public void commonDataQuery(String currentShardingId, Page<Map<String, Object>> page, QueryCriteria queryCriteria, String sql, String field, Object[] params) throws Exception {
    List<Object> dataList = new ArrayList();
    List<Map<String, Object>> retList = new ArrayList<>();
    List<Map<String, Object>> dataMap = metaService.query(sql, params);
    for (Map map : dataMap
        ) {
        dataList.add(map.get(field));
    }
    if (dataList.size() > 0 && CommonUtil.isNotNull(dataList.get(0))) {
        queryCriteria.addExpression(QueryExpressionUtil.in("ID", dataList));
        queryCriteria.addExpression(QueryExpressionUtil.equals("SHARDING_ID", currentShardingId));
        MetaObjectQueryResult result = pageSupport.queryMetaDatasetByPage(page, queryCriteria);
        if (result != null) {
            retList = super.metaObjectListToMapList(result.getList());
            page.setEntities(retList);
            page.setEntityCount((int) result.getTotal());
        }
    }
}

优化后

String sql = "EXISTS (\n" +
"    SELECT 1\n" +
"    FROM rm_nbrspace rn\n" +
"    JOIN rr_nbrspace_number rnn ON rn.id = rnn.nbrspace_id\n" +
"    WHERE rn.spec_id = "+ SPEC_ID +"\n" +
"      AND rn.sharding_id = "+ currentShardingId +"\n" +
"      AND rn.id = "+ numnerId +"\n" +
"      AND rnn.number_id = C.ID\n" +
"  )";
queryCriteria.addExpression(QueryExpressionUtil.sql(sql, new String[]{"ID"}));
queryCriteria.addExpression(QueryExpressionUtil.equals("SHARDING_ID", currentShardingId));
MetaObjectQueryResult result = pageSupport.queryMetaDatasetByPage(page, queryCriteria);
if (result != null) {
List<Map<String, Object>> retList = super.metaObjectListToMapList(result.getList());
page.setEntities(retList);
page.setEntityCount((int) result.getTotal());

SQL相关代码

-- 码号池关联查询码号
select rnn.number_id NUMBER_ID
from rm_nbrspace rn,
     rr_nbrspace_number rnn
where rn.id = rnn.nbrspace_id
  and rn.spec_id = 2010200006
  and rn.sharding_id = 554
  and rn.id = 340012200000000000444098;

SELECT *
FROM RM_NUMBER C,
     RE_NUMBER_PHYSIC P2
WHERE P2.NUMBER_ID = C.ID
  AND '1747793998799572992-2066855581' = '1747793998799572992-2066855581'
  AND C.ID IN (340010300000000075459415, 340010300000000075459416, 340010300000000075459417, 340010300000000075459418,
               340010300000000075459419, 340010300000000075459420, 340010300000000075459421, 340010300000000075459422,
               340010300000000075459423, 340010300000000075459424)
  AND C.SHARDING_ID = 554
  AND C.SPEC_ID = 2020400001;

主要问题是第一条SQL查出来的ID数据量偏大,可能有几十万条,用in无法实现相关功能,直接报错 Cannot connect to already running IDE instance. CannotActivateException:Process 16,232 is still running

优化后

SELECT *
FROM RM_NUMBER C,
     RE_NUMBER_PHYSIC P2
WHERE P2.NUMBER_ID = C.ID
  AND EXISTS (SELECT 1
              FROM rm_nbrspace rn
                       JOIN rr_nbrspace_number rnn ON rn.id = rnn.nbrspace_id
              WHERE rn.spec_id = 2010200006
                AND rn.sharding_id = 554
                AND rn.id = 340012200000000000444098
                AND rnn.number_id = C.ID)
  AND C.SHARDING_ID = 554
  AND C.SPEC_ID = 2020400001;

这种方案适合公司自有的queryCriteria查询,虽然时间还是比较长,但是能查询出来。下面这个可以为自定义的sql查询。

SELECT *
FROM RM_NUMBER C
INNER JOIN RE_NUMBER_PHYSIC P2 ON P2.NUMBER_ID = C.ID
INNER JOIN (
    SELECT rnn.number_id
    FROM rm_nbrspace rn
    JOIN rr_nbrspace_number rnn ON rn.id = rnn.nbrspace_id
    WHERE rn.spec_id = 2010200006
      AND rn.sharding_id = 554
      AND rn.id = 340012200000000000444098
) T ON C.ID = T.number_id
WHERE  C.SHARDING_ID = 554
  AND C.SPEC_ID = 2020400001;
文章作者: Administrator
本文链接:
版权声明: 本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 欲念
喜欢就支持一下吧