java - 使用java迭代数据花费太长时间

Question

我有三个 mysql 表:

1. 客户
2. 投诉
3. 恢复

问题陈述::我有 50000 多个客户，我想搜索有恢复请求的客户。

问题已经解决，并且工作正常，但问题是查询需要长达 30 分钟或很长的时间。

请指导我如何减少查询所花费的时间，或者如何加快搜索速度？

这是 mysql 表的快速帮助 ERD:

我再次明确表示，问题已解决并且工作正常，但由于数据库中存在大量客户和投诉，查询时间太长，长达 30 分钟

是否有任何技术可以减少查询时间或加快搜索速度？

JAVA代码我有两种方法:

1. getMultiSelectionCustomersProfiles(....)
2. searchRecoveryRequest(字符串 customerRegistrationCode )

注意:方法2在方法1中被调用

方法一:

public ArrayList<CustomerRegistrationBean> getMultiSelectionCustomersProfiles(int selectedCountryId,
    int selectedZondId, int selectedRegionId, int selectedCityId, int selectedAreaId,
    int[] selectedMarkets, int selectedMOID, String searchStr, String userStatus,
    int selectedCatID, int[] selectedSubCategoryIds, int[] selectedDeprtIds) {
//System.out.println("getSelectedCustomersProfiles  calles :" + searchStr);

//        System.out.print("getSelected Customers");
//         System.out.print("Market ID Model :" + marketId);4
ConnectionHandler conHandler = ConnectionHandler.getConnectionHandler();
Connection con = conHandler.getConnection();

Statement stmt = null;
ArrayList<CustomerRegistrationBean> list = new ArrayList<CustomerRegistrationBean>();

try {
    ResultSet rs = null;
    stmt = con.createStatement();

    String selectQry = "SELECT cs.*, m.`NAME` as marketName, m.ID as marketID, category.`CATEGORY` as category, "
            + " area.`NAME` as areaName, area.`ID` as areaID, c.`NAME` as cityName,"
            + " c.`ID` as cityID, r.`NAME` as regionName, r.`ID` as regionID, z.name as zoneName,"
            + " z.ID as zoneID, ctr.name as countryName, ctr.`ID` as countryID, "
            + " color.NAME color ,brand.NAME brand, \n"
            + " vehicletype.`NAME` vehicleType, \n"
            + " manufacturer.`NAME` as `manufacturer` ,\n"
            + " brand.`MANUFACTURER_ID` as manfct_id,\n"
            + " cs.`BRAND_ID` as brandID,\n"
            + " cs.`VEHICLE_TYPE_ID` as vhcltypeId,\n"
            + " cs.`COLOR_ID` as colorID,\n"
            + " ul.`NAME` as createdBy ";

    if (selectedMOID > 0) {
        selectQry += ", mmb.`MO_ID` as moID, mo.`NAME` AS moName ";
    }
//            if (selectedSurveyId > 0 && surveyStatus == 1) {
//                selectQry += ", sr.* ";
//            }
    if (selectedDeprtIds.length > 0) {
        selectQry += ", dpt.`NAME` as department  ";
    }

    selectQry
            += " from customerprofile cs  \n";

    if (selectedMOID > 0) {
        selectQry += " join mo_market_bridge mmb on mmb.`MARKET_ID` = cs.`MARKET_ID` \n"
                + " join marketofficer mo on mo.`MO_ID` = mmb.`MO_ID` ";
    }
//            if (selectedSurveyId > 0 && surveyStatus == 1) {
//                selectQry += " LEFT OUTER JOIN survey_result sr on sr.`CUSTOMER_ID` = cs.`CUSTOMER_ID` ";
//            }
    selectQry
            += " join market m on cs.`MARKET_ID` = m.`ID`\n"
            + " join area area on  area.`ID` = m.`AREA_ID`\n"
            + " join city c on c.`ID` = area.`CITY_ID`\n"
            + " join region r on r.`ID` = c.`REGION_ID`\n"
            + " join zone z on z.id = r.`ZONE_ID`\n"
            + " left outer join user_login ul on cs.`CREATED_BY` = ul.`USER_ID`\n"
            + " join country ctr on  ctr.`ID` = z.country_id\n"
            + " LEFT OUTER JOIN category  on cs.`SUB_CATEGORY_ID` = category.`CATEGORY_ID`\n"
            + " LEFT OUTER JOIN category_type  on category.`CATEGORY_TYPE_ID` = category_type.`TYPE_ID`\n"
            + " LEFT OUTER JOIN color ON cs.COLOR_ID = color.COLOR_ID\n"
            + " LEFT OUTER JOIN brand ON cs.BRAND_ID = brand.BRAND_ID\n"
            + " LEFT OUTER JOIN manufacturer ON brand.`MANUFACTURER_ID` = manufacturer.`MANUFACTURER_ID`\n"
            + " LEFT OUTER JOIN vehicletype ON cs.`VEHICLE_TYPE_ID` = vehicletype.`TYPE_ID`\n";
    if (selectedDeprtIds.length > 0) {
        selectQry += "left join department_bridge dptb on dptb.`CUSTOMER_ID` = cs.`CUSTOMER_ID`\n"
                + "left join department dpt on dpt.`DEP_ID` = dptb.`DEPARTMENT_ID` ";
    }
    /*
     String selectQry = "SELECT cs.*, m.`NAME` as marketName, m.ID as marketID, area.`NAME` as areaName, \n"
     + "area.`ID` as areaID, c.`NAME` as cityName, c.`ID` as cityID, r.`NAME` as regionName, \n"
     + "r.`ID` as regionID, z.name as zoneName, z.ID as zoneID, ctr.name as countryName, ctr.`ID` as countryID,\n"
     + "color.NAME color ,brand.NAME brand, vehicletype.`NAME` vehicleType, manufacturer.`NAME` as `manufacturer`, brand.`MANUFACTURER_ID` as manfct_id\n"
     + " ,cs.`BRAND_ID` as brandID\n"
     + " ,cs.`VEHICLE_TYPE_ID` as vhcltypeId\n"
     + " ,cs.`COLOR_ID` as colorID from customerprofile cs  \n"
     + " join market m on cs.`MARKET_ID` = m.`ID`\n"
     + " join area area on  area.`ID` = m.`AREA_ID`\n"
     + " join city c on c.`ID` = area.`CITY_ID`\n"
     + " join region r on r.`ID` = c.`REGION_ID`\n"
     + " join zone z on z.id = r.`ZONE_ID`\n"
     + " join country ctr on  ctr.`ID` = z.country_id\n"
     + " LEFT OUTER JOIN color ON cs.COLOR_ID = color.COLOR_ID\n"
     + " LEFT OUTER JOIN brand ON cs.BRAND_ID = brand.BRAND_ID\n"
     + " LEFT OUTER JOIN manufacturer ON brand.`MANUFACTURER_ID` = manufacturer.`MANUFACTURER_ID`\n"
     + " LEFT OUTER JOIN vehicletype ON cs.`VEHICLE_TYPE_ID` = vehicletype.`TYPE_ID`";
     */
    String whereQry = " where (cs.REG_CODE like '%" + searchStr + "%'  "
            + " or cs.FULL_NAME like '%" + searchStr + "%'  "
            + " or cs.CNIC like '%" + searchStr + "%'  "
            + " or cs.CONTACT_NO_1 like '%" + searchStr + "%'  "
            + " or cs.CONTACT_NO_2 like '%" + searchStr + "%'  "
            + " or cs.SHOP_NAME like '%" + searchStr + "%')  ";

    if (userStatus == null || userStatus.trim().equals("")) {
        userStatus = "0";
    }

    if (userStatus != null || !userStatus.trim().equals("")) {
        whereQry += whereQry.trim().equals("") ? " Where " : " and ";

        whereQry += " cs.status = '" + userStatus + "' ";
    }

    if (selectedCountryId > 0) {
        whereQry += " and ctr.ID = " + selectedCountryId;
    }

    if (selectedZondId > 0) {
        whereQry += " and z.ID = " + selectedZondId;
    }

    if (selectedRegionId > 0) {
        whereQry += " and r.ID = " + selectedRegionId;
    }

    if (selectedCityId > 0) {
        whereQry += " and c.ID = " + selectedCityId;
    }

    if (selectedAreaId > 0) {
        whereQry += " and area.ID =  " + selectedAreaId;
    }
    for (int i = 0; i < selectedMarkets.length; i++) {
        System.out.println("selectedMarkets : " + selectedMarkets[i]);
        if (i == 0) {
            whereQry += " and (m.ID = " + selectedMarkets[0] + " ";
        } else if (i > 0 && i < selectedMarkets.length) {
            whereQry += " or m.ID =  " + selectedMarkets[i];
        } else if (i == selectedMarkets.length) {
            whereQry += " or  m.ID = " + selectedMarkets[selectedMarkets.length] + " ) ";
        }
        if (selectedMarkets.length - 1 == i) {
            whereQry += " ) ";
        }
    }
// 
//            if (selectedMarketId > 0) {
//                whereQry += " and m.ID = " + selectedMarketId;
//            }
    if (selectedMOID > 0) {
        whereQry += " and mo.`MO_ID` = " + selectedMOID;
    }
    if (selectedCatID > 0) {
        whereQry += " and category_type.`TYPE_ID` = " + selectedCatID;
    }
//            if (selectedSubCategory > 0) {
//                whereQry += " and category.`CATEGORY_ID` = " + selectedSubCategory;

//}

    for (int i = 0; i < selectedSubCategoryIds.length; i++) {
        System.out.println(selectedSubCategoryIds[i]);
        if (i == 0) {
            whereQry += " and (category.`CATEGORY_ID` = " + selectedSubCategoryIds[0] + " ";
        } else if (i > 0 && i < selectedSubCategoryIds.length) {
            whereQry += " or category.`CATEGORY_ID` =  " + selectedSubCategoryIds[i];
        } else if (i == selectedSubCategoryIds.length) {
            whereQry += " or  category.`CATEGORY_ID` = " + selectedSubCategoryIds[selectedSubCategoryIds.length] + " ) ";
        }
        if (selectedSubCategoryIds.length - 1 == i) {
            whereQry += " ) ";
        }
    }

//            if (selectedDeprtIds.length > 0) {
    for (int i = 0; i < selectedDeprtIds.length; i++) {
        System.out.println(selectedDeprtIds[i]);
        if (i == 0) {
            whereQry += " and (dpt.`DEP_ID` = " + selectedDeprtIds[0] + " ";
        } else if (i > 0 && i < selectedDeprtIds.length) {
            whereQry += " or dpt.`DEP_ID` =  " + selectedDeprtIds[i];
        } else if (i == selectedDeprtIds.length) {
            whereQry += " or  dpt.`DEP_ID` = " + selectedDeprtIds[selectedDeprtIds.length] + " ) ";
        }
        if (selectedDeprtIds.length - 1 == i) {
            whereQry += " ) ";
        }
    }
//}
//            if (selectedDeptId > 0) {
//                whereQry += "and dpt.`DEP_ID` = " + selectedDeptId;
//            }
//            if (selectedSurveyId > 0) {
//
//                whereQry += " and m.`ID` = " + selectedMarketId;
//
//                if (surveyStatus == 1) {
//                    whereQry += " and sr.`SURVEY_ID` =  " + selectedSurveyId;
//                } else {
//                    whereQry += " and cs.CUSTOMER_ID not in (SELECT CPPP.CUSTOMER_ID FROM (SELECT * FROM survey_result sr where  sr.`SURVEY_ID` = " + selectedSurveyId + " ) CPPP)";
//                }
//            }
    selectQry += whereQry;
//            System.out.println("selected method in Model 2nd calles");

    System.out.print(selectQry);

    rs = stmt.executeQuery(selectQry);

    CustomerRegistrationBean p;
    while (rs.next()) {
        p = new CustomerRegistrationBean();
//              
        p.setRegCode(rs.getString("REG_CODE"));
        p.setFullName(rs.getString("FULL_NAME"));
        p.setContactNo1(rs.getString("CONTACT_NO_1"));
        p.setContactNo2(rs.getString("CONTACT_NO_2"));
        p.setEmail(rs.getString("EMAIL"));
        p.setShopAddress(rs.getString("SHOP_ADDRESS"));
        p.setShopName(rs.getString("SHOP_NAME"));
        p.setWhatsAppNo(rs.getString("WHATSAPP_NO"));
        p.setRemarks(rs.getString("REMARKS"));
        p.setLatitude(rs.getDouble("LATITUDE"));
        p.setLongitude(rs.getDouble("LONGITUDE"));
        p.setCnicNo(rs.getString("CNIC"));
        p.setPassportNo(rs.getString("PASSPORT_NO"));
        p.setEntryDate(rs.getDate("ENTRY_DATE"));
        p.setWokringSince(rs.getDate("WORKING_SINCE"));
        p.setDob(rs.getDate("DATE_OF_BIRTH"));
        p.setMarketName(rs.getString("marketName"));
        p.setCountryName(rs.getString("countryName"));
        p.setCityName(rs.getString("cityName"));
        p.setAreaName(rs.getString("areaName"));
        p.setRegion(rs.getString("regionName"));
        p.setZone(rs.getString("zoneName"));
        p.setSelectedMarketId(rs.getInt("marketID"));
        p.setSelectedCountryId(rs.getInt("countryID"));
        p.setSelectedCityId(rs.getInt("cityID"));
        p.setSelectedRegionId(rs.getInt("regionID"));
        p.setSelectedAreaId(rs.getInt("areaID"));
        p.setSelectedZondId(rs.getInt("zoneID"));
        p.setRegNo(rs.getString("REG_NO"));
        p.setEngineNo(rs.getString("ENGINE_NO"));
        p.setChassisNo(rs.getString("CHASSIS_NO"));
        p.setSaleRefNo(rs.getString("SALE_REF_NO"));
        p.setModelYear(rs.getString("MODEL_YEAR"));
        p.setManufacturerId(rs.getInt("manfct_id"));
        p.setBrandId(rs.getInt("brandID"));
        p.setColorID(rs.getInt("colorID"));
        p.setVchlTypeID(rs.getInt("vhcltypeId"));
        p.setCallFrequency(rs.getString("CALL_FREQUENCY"));
        p.setUserStatus(rs.getInt("STATUS") + "");
        p.setCategoryName(rs.getString("category"));

        p.setSelectedCatID(rs.getInt("CATEGORY_TYPE_Id"));
        p.setSelectedSubCategory(rs.getInt("SUB_CATEGORY_ID"));
//                p.setSelectedMOID(rs.getInt("moID"));
//                p.setMoName(rs.getString("moName"));
        p.setCustomerId(rs.getInt("CUSTOMER_ID"));
        p.setCreatedBy(rs.getString("createdBy"));

        double testAmount = searchRecoveryRequest(p.getRegCode());
         System.out.println("testAmount : " + testAmount);


         if (selectedMOID > 0) {
            p.setSelectedMOID(rs.getInt("moID"));
            p.setMoName(rs.getString("moName"));
        }
        if (selectedDeprtIds.length > 0) {
            p.setDepartment(rs.getString("department"));
        }
//                System.out.println("p.getDepartment()" + p.getDepartment());

        list.add(p);
        p = null;
    }
} catch (Exception e) {
    System.out.println(e);

} finally {
    try {
        if (stmt != null) {
            stmt.close();
        }

    } catch (SQLException e) {
        System.out.println(e);
    }
    conHandler.freeConnection(con);
}

return list;

}

方法2

public double searchRecoveryRequest(String custRegCode) {

double recoveryAmount = 0;
ConnectionHandler conHandler = ConnectionHandler.getConnectionHandler();
Connection con = conHandler.getConnection();

Statement stmt = null;
try {
    ResultSet rs = null;
    stmt = con.createStatement();
    String selectQry = "select re.`AMOUNT` as amount \n"
            + "from complain cmp\n"
            + "join  customerprofile cp on cp.`REG_CODE` = cmp.`CUST_REG_NO`\n"
            + "JOIN `recovery` re ON re.`COMPLAINT_ID` = cmp.`CODE`\n"
            + "where cmp.`CUST_REG_NO` = '" + custRegCode + "' and cmp.`STATUS_CODE` <> 'CLOSED'\n";
    System.out.println(selectQry);
    rs = stmt.executeQuery(selectQry);

    if(rs.next()){

        recoveryAmount = rs.getDouble("amount");
    }

} catch (Exception e) {
    System.out.println(e);

} finally {
    try {
        if (stmt != null) {
            stmt.close();
        }

    } catch (SQLException e) {
        System.out.println(e);
    }
    conHandler.freeConnection(con);
}

return recoveryAmount;

}

这是表创建语句

客户资料表

CREATE TABLE  `crm`.`customerprofile` (
  `REG_CODE` varchar(55) DEFAULT NULL,
  `FULL_NAME` varchar(55) DEFAULT NULL,
  `CNIC` varchar(55) DEFAULT NULL,
  `DATE_OF_BIRTH` datetime DEFAULT NULL,
  `EMAIL` varchar(200) DEFAULT NULL,
  `CONTACT_NO_1` varchar(45) DEFAULT NULL,
  `LONGITUDE` decimal(11,8) NOT NULL,
  `ENTRY_DATE` datetime DEFAULT NULL,
  `SHOP_NAME` varchar(150) DEFAULT NULL,
  `MARKET_ID` int(11) unsigned DEFAULT NULL,
  `CUSTOMER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VEHICLE_TYPE_ID` int(10) DEFAULT NULL,
  `SALE_REF_NO` varchar(255) DEFAULT NULL,
  `MODEL_YEAR` varchar(4) DEFAULT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,

) ENGINE=InnoDB AUTO_INCREMENT=48608 DEFAULT CHARSET=utf8;

投诉表

CREATE TABLE  `crm`.`complain` (
  `CODE` int(11) NOT NULL AUTO_INCREMENT,
  `COMPLAIN_NO` varchar(10) DEFAULT NULL,
  `DATE` date DEFAULT NULL,
  `COMPLAIN_TYPE_CODE` int(11) NOT NULL,
  `COMPLAIN_REASON_CODE` int(11) NOT NULL,
  `DESCRIPTION` varchar(4000) DEFAULT NULL,
  `STATUS_CODE` int(10) DEFAULT NULL,
  `STAGE_CODE` int(11) DEFAULT NULL,
  `CUST_REG_NO` varchar(55) NOT NULL,
  `CREATION_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` int(11) DEFAULT NULL,
  `COMPLAIN_SUBTYPE_CODE` int(11) NOT NULL,
  `PARTS_ID` int(10) unsigned NOT NULL,
  `LEVEL_ID` int(2) DEFAULT NULL,
  `LAST_MODIFIED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRIORITY_CODE` int(10) unsigned NOT NULL DEFAULT '5',
  `TAT_STATUS_CODE` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`CODE`),
  KEY `STATUS_CODE_FK` (`STATUS_CODE`),
  KEY `STAGE_CODE` (`STAGE_CODE`),
  CONSTRAINT `STAGE_CODE` FOREIGN KEY (`STAGE_CODE`) REFERENCES `stage` (`CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `STATUS_CODE_FK` FOREIGN KEY (`STATUS_CODE`) REFERENCES `status` (`CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3478 DEFAULT CHARSET=latin1;

恢复表

CREATE TABLE  `crm`.`recovery` (
  `RECOVERY_ID` int(10) NOT NULL AUTO_INCREMENT,
  `COMPLAINT_ID` int(10) DEFAULT NULL,
  `AMOUNT` double DEFAULT NULL,
  `AGING_DATE` datetime DEFAULT NULL,
  `RO_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`RECOVERY_ID`),
  UNIQUE KEY `RECOVERY_ID_UNIQUE` (`RECOVERY_ID`),
  KEY `FK_RO_ID_idx` (`RO_ID`),
  CONSTRAINT `FK_RO_ID` FOREIGN KEY (`RO_ID`) REFERENCES `marketofficer` (`MO_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Answer 1

您的问题很简单，您在crm上没有任何键/索引。customerprofile。

您必须在表 crm 中的 REG_CODE 列上添加键。customerprofile。

每次尝试通过 reg_code 来识别客户时，mysql 都需要读取 50000 行才能找到。

另一个有用的: 表 crm 中 CUST_REG_NO 的索引。complain