MySQL的sql优化问题,求大神帮忙

我使用MySQL数据库的自定义函数实现了像Oracle一样的sequence序列功能,这个功能在项目中是做一个单号生成的模块,
目前是在测试并发访问1000的时候让每个请求响应的时间不超过100毫秒,但是目前的结果是只有三分之二的响应时间能达到要求。以下是我的代码以及sql自定义函数。求大神帮忙优化。
方法
@RequestMapping(value ="/testP.location")
@ResponseBody
public String testPolicyNo(){
String p_type="P";
String productNO="1999";
Date date=new Date();
SimpleDateFormat simple=new SimpleDateFormat("yyyy");
String sys_year=simple.format(date).toString();
String department="0101L";
String creator="aaa";
long startTime=System.currentTimeMillis();
String policyNo = iGenerateIDService.generatePolicyNo(p_type,productNO,sys_year,department,creator);
long endTime=System.currentTimeMillis();
long t=endTime-startTime;
String tt="w-----"+String.valueOf(t);
log.debug(tt);
//System.out.println("保单号"+policyNo);
return null;
}

 @Override
    public String generatePolicyNos(String p_type, String productNO, String sys_year, String department,String creator) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String policyNo="";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
            ps=conn.prepareStatement("select lpad(nextval(?,?,?,?,?),?,'0')");
            ps.setString(1, productNO);
            ps.setString(2, p_type);
            ps.setString(3, sys_year);
            ps.setString(4, department);
            ps.setString(5, creator);
            ps.setInt(6, 7);
            rs=ps.executeQuery();
            while(rs.next()){
                policyNo=p_type+productNO+sys_year+department+rs.getString(1);
            }
            return policyNo;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "";
    }

sql函数:
1:建表语句
CREATE TABLE tbl_seq_mysql (
p_type varchar(5) NOT NULL,
productNO varchar(50) NOT NULL,
sys_year varchar(5) NOT NULL,
department varchar(5) NOT NULL,
currentValue int(50) NOT NULL DEFAULT '2',
increment int(11) NOT NULL DEFAULT '2',
creator varchar(50) NOT NULL,
created_date varchar(50) NOT NULL,
PRIMARY KEY (p_type,productNO,sys_year,department,currentValue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2:函数1 nextval
CREATE DEFINER=shopdev@% FUNCTION next_value(m varchar(50),a varchar(5),b varchar(5),c

varchar(5),d varchar(50)) RETURNS int(11)
begin
UPDATE tbl_seq_mysql
SET currentValue = currentValue + increment
WHERE
p_type = a
AND productNO = m
AND sys_year = b
AND department = c
AND creator=d;

return current_value(m,a,b,c,d);

end

3:函数currentval
CREATE DEFINER=shopdev@% FUNCTION current_value(n varchar(50),e varchar(5),f varchar

(5),g varchar(5),h varchar(50)) RETURNS int(11)
BEGIN
DECLARE _cur int;
DECLARE _curr int;
select COUNT(1) INTO _cur from tbl_seq_mysql where creator=h and department=g and productNO= n and p_type=e and sys_year=f;
IF _cur=0
THEN
insert into tbl_seq_mysql

(p_type,productNO,sys_year,department,creator,created_date) VALUES

(e,n,f,g,h,NOW());
END IF;
set _curr=(select currentValue from tbl_seq_mysql where creator=h and department=g and productNO= n and p_type=e and sys_year=f);
return _curr;
end

1个回答

wanglins
wanglins 谢谢
3 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐