以下是我的数据库建表语句:
String sql = "create table if not exists "+tableName
+ "(id int auto_increment primary key,"
+ "clientNum varchar(30) NOT NULL,"
+ "tranDate varchar(10) NOT NULL,"
+ "jieDaiBiaoZhi varchar(5) NOT NULL,"
+ "money varchar(30) NOT NULL,"
+ "accountNum varchar(40) NOT NULL,"
+ "clientName varchar(30) NOT NULL,"
+ "moneyType varchar(5) NOT NULL,"
+ "xianJinZhuanZhangBiaoZhi varchar(40) NOT NULL," //现金转账标志
+ "index(clientNum,tranDate,jieDaiBiaoZhi)"
+ ")default charset=utf8";
以下是我的数据库对表的查询操作语句:
String sql = "select * from " + tableName + " where clientNum in"
+ "(select clientNum from " + tableName
+ " group by clientNum,tranDate,jieDaiBiaoZhi "
+ "HAVING sum(money)>=" + BenBiWindow.cashNum
+ ")";
我目前要对一个20w的数据进行该查询操作,但是查询超级慢!请问大神该如何解决这个问题呢?怎么优化?
String sql = "create table if not exists "+tableName
+ "(id int auto_increment primary key,"
+ "clientNum varchar(30) NOT NULL,"
+ "tranDate varchar(10) NOT NULL,"
+ "jieDaiBiaoZhi varchar(5) NOT NULL,"
+ "money varchar(30) NOT NULL,"
+ "accountNum varchar(40) NOT NULL,"
+ "clientName varchar(30) NOT NULL,"
+ "moneyType varchar(5) NOT NULL,"
+ "xianJinZhuanZhangBiaoZhi varchar(40) NOT NULL," //现金转账标志
+ "index(clientNum,tranDate,jieDaiBiaoZhi)"
+ ")default charset=utf8";
以下是我的数据库对表的查询操作语句:
String sql = "select * from " + tableName + " where clientNum in"
+ "(select clientNum from " + tableName
+ " group by clientNum,tranDate,jieDaiBiaoZhi "
+ "HAVING sum(money)>=" + BenBiWindow.cashNum
+ ")";
我目前要对一个20w的数据进行该查询操作,但是查询超级慢!请问大神该如何解决这个问题呢?怎么优化?
explain select ...
show index from ..
以供分析。
1. clientNum唯一性高么,是否有合适的索引
2. 既然在程序中,你可以考虑将一下这段单独查询出来放在一张临时表里,因为你后续程序可以重复使用这些数据,每次查询就不用执行中间这段最耗资源的部分了。
select clientNum from " + tableName
+ " group by clientNum,tranDate,jieDaiBiaoZhi "
+ "HAVING sum(money)>=" + BenBiWindow.cashNum