fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）

longz

2026-08-05 fbce3ff8d28f55fdc8e7c6e446534dcfa181f488

 ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java

@@ -26,23 +26,35 @@
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }

        String baseWhere = mapper.substring(
                mapper.indexOf("<sql id=\"enterpriseChargingBaseWhere\""),
                mapper.indexOf("<select id=\"enterpriseChargingAnalysis\""));
        String analysis = mapper.substring(
                mapper.indexOf("<select id=\"enterpriseChargingAnalysis\""),
                mapper.indexOf("<select id=\"enterpriseTopSites\""));
        String invoiceRows = block(mapper, "enterpriseInvoiceRows");
        String detailRows = block(mapper, "enterpriseInvoiceDetailRows");
        String orderRows = block(mapper, "enterpriseAnalysisChargingOrders");

        assertFalse(baseWhere.contains("co.del_flag"));
        assertFalse(baseWhere.contains("co.recharge_payment_status"));
        assertTrue(baseWhere.contains("AND toi.status IN (2, 3)"));
        assertFalse(baseWhere.contains("AND toi.status IN (1, 2, 3)"));
        assertTrue(analysis.contains("LEFT JOIN t_charging_order co ON co.id = tod.order_id"));
        assertTrue(analysis.contains("COUNT(1) AS invoiceCount"));
        assertTrue(analysis.contains("ORDER BY invoiceCount DESC"));
        assertFalse(analysis.contains("chargingCount"));
        assertTrue(analysis.contains("TRIM(toi.name) AS companyName"));
        assertTrue(analysis.contains("IFNULL(TRIM(toi.tax_identification_number), '') AS socialCreditCode"));
        assertTrue(analysis.contains("detailInvoiceAmount"));
        // 企业发票单表查（逆反查，不再跨分片 JOIN）：抬头级别过滤可下推到各分片
        assertTrue(invoiceRows.contains("invoicing_object_type = 2"));
        assertTrue(invoiceRows.contains("status IN (2, 3)"));
        assertFalse(invoiceRows.contains("LEFT JOIN"));
        assertFalse(invoiceRows.contains("INNER JOIN"));
        // 不按订单维度过滤：历史充电订单被删除的发票仍会计数（订单缺失的兜底逻辑在 Service 层）
        assertFalse(invoiceRows.contains("co.del_flag"));
        assertFalse(invoiceRows.contains("recharge_payment_status"));

        // 明细只取充电订单（order_type=1），按 order_invoice_id 分片键 IN 路由
        assertTrue(detailRows.contains("order_type = 1"));
        assertTrue(detailRows.contains("order_invoice_id IN"));

        // 订单数据按 id IN 走主键、单表不 JOIN（t_charging_order 50 片按 app_user_id 分片，不可与发票跨片 JOIN）
        assertTrue(orderRows.contains("FROM t_charging_order"));
        assertTrue(orderRows.contains("WHERE id IN"));
        assertFalse(orderRows.contains("JOIN"));
    }

    private static String block(String mapper, String id) {
        int start = mapper.indexOf("<select id=\"" + id + "\"");
        int end = mapper.indexOf("</select>", start);
        if (start < 0 || end < 0) {
            throw new AssertionError("missing select block: " + id);
        }
        return mapper.substring(start, end);
    }
}