MySQL查询使用Java的时间太长 [英] mysql query takes too long using java
问题描述
我有三个mysql表:
I have three mysql tables:
- 客户
- 投诉
- 恢复
问题陈述::我有5万多个客户，我想搜索有恢复请求的客户.
Problem statement: I have 50000+ customers, I want to search a customer against whom there's recovery request.
问题已经很好解决，并且可以正常工作，但是问题是查询最多需要30分钟或更长的时间.
请指导我如何减少查询所花费的时间，或者如何加快搜索的速度?
Please guide me through how do I reduce the time that is taken by the query, or how can I fasten the search?
这是mysql表的快速帮助ERD:
Here's the quickhelp ERD of the mysql tables:
我再次明确指出，问题已解决&可以正常工作，但是由于数据库中有大量客户和投诉，因此查询最多需要30分钟才能完成.
Again I make it clear, the problem is solved & works fine, but the query takes too long upto 30 minutes due to large amount of customer and complaints in the database.
是否有任何减少查询时间或加快搜索速度的技术?
Is there any technique through which to reduce the query time, or fasten the search?
JAVA代码 我有两种方法:
- getMultiSelectionCustomersProfiles(....)
- searchRecoveryRequest(字符串customerRegistrationCode)
注意:method2在method1中被调用
Note: method2 is called in method1
方法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;
}
谢谢.
推荐答案
要在没有运行测试系统的情况下找到合适的解决方案并不容易.
It is not easy to find a proper solution to your problem without having a running test system to play around.
我发现可疑的是，您的第一个查询的每个结果行都会调用第二个方法，并且它似乎每次都打开和关闭数据库连接. (您的ConnectionHandler可能实现了正确的连接池，但我无法在不知道代码的情况下进行判断.如果每次都真正打开和关闭连接，则这是迄今为止代码中最昂贵的事情.)
What I find suspicious is the fact that your 2nd method is called for every single result row of your first query, and it seems to open and close database connections every single time. (Your ConnectionHandler might implement proper connection pooling, but I cannot judge this without knowing the code. If the connection is really opened and closed every time, this is by far the most expensive thing in your code).
但是即使有适当的连接池，您的第二个方法也会为每个调用创建并执行一个新的SQL语句.这称为 N + 1选择查询问题.
But even with proper connection pooling, your 2nd method creates and executes a new SQL statement for every call. This is called the N+1 Select Query Issue.
因此，我建议进行以下改进:
So I suggest the following improvements:
-
尝试将两个SQL语句合并为一个(可能使用嵌套的SELECT等).然后，您不得执行1 + N个查询，这将大大减少执行时间.
Try to merge both SQL statements into a single one (maybe using nested SELECTs, etc.). Then you must not execute 1+N queries, which will greatly reduce execution time.
如果不可能，请至少使用 PreparedStatement 用于第二种方法.在进入循环之前，请确保仅一次创建一次准备好的语句.因此，您必须使用第一种方法创建和准备它.因此，您需要第二个数据库连接.循环之后也分别在打开之前将其打开和关闭.
If that's not possible, use at least a PreparedStatement for the 2nd method. Make sure to create the prepared statement just once before entering the loop. So you'll have to create and prepare it in the first method. Therefore, you need a second database connection. Open and close it before respectively after the loop too.
另一种策略:运行第一个查询，迭代结果集并将所有regCodes放入列表中.关闭查询后，请使用通过PreparedStatement 进行批处理，以解决所有问题. regCodes.另一个优点:因此只需要一个数据库连接.
Another strategy: Run the first query, iterate the result set and put all regCodes in a list. After closing the query, use batch-processing via PreparedStatement to work off all the regCodes. Another advantage: Therefore only one database connection is required.
顺便说一句:像Hibernate这样的成熟的对象关系映射框架已经提供了开箱即用的这些策略，以避免或至少减轻了1 + N选择问题.
Btw.: Mature object-relational mapping frameworks like Hibernate already provide these strategies out of the box to avoid or at least extenuate the 1+N select problem.
这篇关于MySQL查询使用Java的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
