mysql 序列号生成办法

最新推荐文章于 2022-07-06 16:46:16 发布

原创最新推荐文章于 2022-07-06 16:46:16 发布 · 455 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了如何通过第三方生成主键来优化分库分表策略，提高查询速度，同时讨论了主键设计、存储过程实现及Java调用方式，并指出可能存在的风险和解决方案。

为什么要通过第三方生成主键：

海量数据一般要应用分库，分表策略，如用户表分成32张，每张数据量就小了，查询速度就会加快。

分表会碰到主键问题，要保证每张表的主键在32张表中都是唯一，你会随着业务量重新整合数据

32张表会变成64或更多，数据也可能要按新的规则存放，所以要保证主键唯一。

int型主键最利于索引和查询速度。有人会用uuid 32位字符串做主键，但这加大的索引存储，不利于查询优化

第一步：建表

CREATE TABLE `t_max_id` (
  `max_key` varchar(10) NOT NULL,
  `max_id` bigint(11) NOT NULL,
  PRIMARY KEY (`max_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

`max_key` 是业务类型

`max_id` 是下一个id，按升序增加

表类型用的InnoDB，支持事务

第二步：建存储过程

CREATE PROCEDURE `max_id`(p_max_key CHAR(10), OUT PARAM2 INT)
BEGIN
 START TRANSACTION;
	 SET @a= NULL;
	 SELECT max_id INTO @a FROM t_max_id WHERE max_key= p_max_key FOR UPDATE;
	 IF(@a IS NULL) THEN
	  SET @a=1;
	  INSERT INTO t_max_id(max_key,max_id) VALUES(p_max_key,2);
	 ELSE
	  UPDATE t_max_id SET max_id=max_id+1 WHERE max_key=p_max_key;
	 END IF;
	 SELECT @a INTO PARAM2;
 COMMIT;
END

select **** FOR UPDATE 是锁住当前结果集所在行，直到本事务COMMIT

第三步：java调用存储过程，取得某业务的主键id

@Test
	public void testCallPro(){
		  String param2Value = (String) jdbcTemplate.execute( 
				     new CallableStatementCreator() {
						public CallableStatement createCallableStatement(
								java.sql.Connection con) throws SQLException {
							// TODO Auto-generated method stub
							 String storedProc = "{call max_id(?,?)}";// 调用的sql 
					           CallableStatement cs = con.prepareCall(storedProc); 
					           cs.setString(1, "order");// 设置输入参数的值 
					           cs.registerOutParameter(2, java.sql.Types.INTEGER);// 注册输出参数的类型 
					         
							
							return cs;
						} 
				     }, new CallableStatementCallback() { 
				         public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { 
				           cs.execute(); 
				           return cs.getString(2);// 获取输出参数的值 
				     } 
				  }); 
		  
		  System.out.println(param2Value);
	}

求 order 表的主键

用的是spring jdbcTemplate

注意：

1、这种方式存在单点，所有的主键生成依赖某数据库，一时宕机将影响所有插入。

2、最好利地RMI提用服务，数据库只对某些ip的service提供调用存储过程服务，不提供insert,update,delete服务

-------------------------------------------------------------------------------------------------------------------------------

另一种简单调用办法

CREATE PROCEDURE `max_id`(p_max_key CHAR(10))

BEGIN
	
	START TRANSACTION;
	 
		SET @a= NULL;
		 
		SELECT max_id INTO @a FROM t_max_id WHERE max_key= p_max_key FOR UPDATE;
		 
		IF(@a IS NULL) THEN
		  
		SET @a=1;
		  
		INSERT INTO t_max_id(max_key,max_id) VALUES(p_max_key,2);
		 
		ELSE
		  
		UPDATE t_max_id SET max_id=max_id+1 WHERE max_key=p_max_key;
		 END IF;
		 
		SELECT @a AS max_id;
	 

	COMMIT;
    
END$$

DELIMITER ;

没有声明输出参数

@Test
	public void testCallPro2(){
		
		  System.out.println(jdbcTemplate.queryForInt("call max_id(?)",new Object[]{"order"}));
	}