java code realizes mysql sub table operation of user behavior record

Set the method of project pneumatic execution times (check the table records once a day)

public class DayInterval implements ServletContextListener{
	private static SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static void showDayTime() {
			Timer dTimer = new Timer();
			dTimer.schedule(new TimerTask() {
			@Override
			public void run() {	
			  System.out.println(" Daily task execution :"+simpleDateFormat.format(new Date()));
			  LogTableCreate logTableCreate = new LogTableCreate();
			  Thread thread=new Thread(logTableCreate);
			  thread.start();
			}
			}, 1000 , 24* 60* 60 * 1000);//24* 60* 60 * 1000( No. 1 1 Times 1 Second, every time after that 1 Executed in days )
	}
	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
//		showDayTime();
	}
	@Override
	public void contextInitialized(ServletContextEvent arg0) {
		showDayTime();
	}
}

LogTableCreate is used to check whether the table sub-table has been created. If it is September now, check whether there is a table record of the current month when starting up. If it does not exist, create it if it does not exist. In addition, check whether there is a table record of October, and create it if it does not exist (if one table is created in advance, it will be empty, and so on).

Copy code to modify createsql (table building sql), URL (database address), USER (database connection user), PASSWORD (database connection password)

public class LogTableCreate extends TimerTask {	
	private static final Log log = LogFactory.getLog(LogTableCreate.class);
	public static final String TBASENAME="tb_log";
		private String createsql = " (\r\n" + 
				" `ID` varchar(64) NOT NULL COMMENT ' Primary key id',\r\n" + 
				" `userid` varchar(255) DEFAULT NULL COMMENT ' Users id',\r\n" + 
				" `username` varchar(255) DEFAULT NULL COMMENT ' User name ',\r\n" + 
				" `useridcard` varchar(255) DEFAULT NULL COMMENT ' User ID number ',\r\n" + 
				" `realname` varchar(64) DEFAULT NULL COMMENT ' Real name ',\r\n" + 
				" `logintime` varchar(255) DEFAULT NULL COMMENT ' Login time ',\r\n" + 
				" `exittime` varchar(64) DEFAULT NULL COMMENT ' Exit time ',\r\n" + 
				" `ippath` varchar(255) DEFAULT NULL COMMENT 'ip Address ',\r\n" + 
				" `macpath` varchar(255) DEFAULT NULL COMMENT 'mac Address ',\r\n" + 
				" `usercreatedtime` varchar(255) DEFAULT NULL COMMENT ' User creation time ',\r\n" + 
				" `userbusidaddress` varchar(255) DEFAULT NULL COMMENT ' User wallet address ',\r\n" + 
				" `member` int(11) DEFAULT NULL COMMENT ' Whether you are a member or not ',\r\n" + 
				" `membertype` int(11) DEFAULT NULL COMMENT ' Type of membership ',\r\n" + 
				" `spare1` varchar(255) DEFAULT NULL,\r\n" + 
				" `spare2` varchar(255) DEFAULT NULL,\r\n" + 
				" `spare3` varchar(255) DEFAULT NULL,\r\n" + 
				" PRIMARY KEY (`ID`)\r\n" + 
				")";	
 private SimpleDateFormat sdyyyy = new SimpleDateFormat("yyyy");
 private SimpleDateFormat sdmm = new SimpleDateFormat("MM");
 private static final String URL = "";
 private static final String USER = "";
 private static final String PASSWORD = "";
 // Get the table name 
 public static String gettable() {
 	Date date = new Date();
 	LogTableCreate logTableCreate=new LogTableCreate();
		String yyyy = logTableCreate.sdyyyy.format(date);
		String mm = logTableCreate.sdmm.format(date);
		String nmm = logTableCreate.getNextMM(mm);
		return TBASENAME+yyyy+mm;
 }
 
	// Get the following 1 Months 
	private String getNextMM(String mm){
		String nmm = "";
		int imm = Integer.parseInt(mm);
		if(imm>=12){
			nmm = "01";
		}else{
			imm++;
			if(imm>9)
				nmm = ""+imm;
			else
				nmm = "0"+imm;
		}
		return nmm;
	} 
	@Override
	public void run() {
		Date date = new Date();
		String yyyy = sdyyyy.format(date);
		String mm = sdmm.format(date);
		String nmm = getNextMM(mm);
		
		String nyyyy = "";
		if("01".equals(nmm)){
			nyyyy = ""+(Integer.parseInt(yyyy)+1);
		}else{
			nyyyy = yyyy;
		}
		
		log.info(" Log table checking and creating: "+yyyy+" - "+mm+" | "+nyyyy+"-"+nmm);
		String temp = TBASENAME+yyyy+mm; // Log table name 
		boolean has = false;
	
		try{
			has = hasTable(temp);
		}catch(Exception e){
			log.error(" An error occurred when judging whether the current operation log table exists :"+e.getMessage());
			return;
		}
		if(!has){
			try{
				createTable(temp);
			}catch(Exception e){
				log.error(" An error occurred while creating the current operation log table :"+e.getMessage());
				return;
			}
		}
		temp = TBASENAME+nyyyy+nmm;
		has = false;
		try{
			has = hasTable(temp);
		}catch(Exception e){
			log.error(" Error in determining whether the standby log table exists :"+e.getMessage());
			return;
		}
		if(!has){
			try{
				createTable(temp);
			}catch(Exception e){
				log.error(" An error occurred while creating the standby log table :"+e.getMessage());
				return;
			}
		}
		
		log.info(" End of log table check and creation ");
	}
	
	public boolean hasTable(String table) throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
  //2.  Get a database connection 
 Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
		boolean state = false;
		DatabaseMetaData meta = conn.getMetaData();
		ResultSet set;
		set = meta.getTables(null, null, table.toLowerCase(), null);
		while (set.next()) {
			state = true;
			break;
		}
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
		}catch(Exception e){
			log.error(" An error occurred while checking whether the log table exists :"+e.getMessage());
			throw e;
		}finally{
			if(stmt!=null)
				try {
					stmt.close();
				} catch (Exception e) {
					//e.printStackTrace();
				}
		}
		conn.close();
		return state;
	}
	public void createTable(String table)throws Exception{
		try{
		Class.forName("com.mysql.jdbc.Driver");
	  //2.  Get a database connection 
	 Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
		String sql = "create table "+table+createsql;
		Statement stmt = null;
		stmt = conn.createStatement();
		stmt.execute(sql);
		}catch(Exception e){
			log.error(" Error initializing log table :"+e.getMessage());
			throw e;
		}
	}
}

Supplement: java horizontal sub-table _Java development sub-library sub-table need to solve the problem and mycat is how to achieve sub-library sub-table

Introduction

Literally, it is simply understood that the data originally stored in one library is stored in multiple libraries in blocks, and the data originally stored in one table is stored in multiple tables in blocks.

The amount of data in the database is controllable. Without dividing the database into tables, with the development of time and business, there will be more and more tables in the database, and the amount of data in the tables will become larger and larger. Accordingly, the cost of data operation, addition, deletion and modification will become larger and larger;

In addition, because distributed deployment cannot be carried out, and the resources of one server (CPU, disk, memory, IO, etc.) are limited, the amount of data that the database can carry and the data processing capacity will encounter bottlenecks.

Necessity of dividing databases into tables

First of all, let's understand why we should make sub-libraries and sub-tables. In our business (web application), the relational database itself is easy to become the bottleneck of system performance, and the storage capacity, connection number and processing capacity of a single machine are very limited. The "stateful" of the database itself leads to that it is not as easy to expand as Web and application server. So in our business, whether it is really necessary to divide the database into tables can be considered from the above conditions.

Stand-alone storage capacity. Whether your data volume has encountered bottlenecks in stand-alone storage. For example, if you are hungry, the user behavior data generated in one day is 24T, which is definitely not enough in the traditional stand-alone storage.

Number of connections, processing capacity. When our users reach a certain level, the concurrency at a specific time becomes a big problem. In a highly concurrent website, the concurrency of 100,000 seconds is normal. In the ordinary stand-alone database, the operation problem of thousands of times in seconds is very big.

Therefore, before we divide the database into tables, we'd better consider whether our data volume is large enough and whether the concurrent volume is large enough. If your answer is yes, let's start doing it.

Transactional problem

At present, there are two feasible solutions to solve the transaction problem: distributed transaction and transaction realization through joint control of application and database. Here is a simple comparison between the two schemes.

Scenario 1: Using distributed transactions

Advantages: It is managed by database, which is simple and effective

Disadvantages: High performance cost, especially when shard is increasing

Scenario 2: Controlled by both the application and the database

Principle: Split a distributed transaction across multiple databases into multiple small transactions only on a single database, and control each small transaction through an application program.

Advantages: Advantages in performance

Disadvantages: The application needs to be designed flexibly in transaction control. If the transaction management of spring is used, it will be difficult to change it.

Implementation strategy of sub-database and sub-table.

There are two kinds of sub-database sub-table: vertical sub-table and horizontal sub-table.

3.1 What is vertical segmentation, that is, tables are divided according to functional modules and close relationship, and deployed to different libraries.

For example, we will establish definition database workDB, commodity database payDB, user database userDB, log database logDB, etc., which are used to store project data definition table, commodity definition table, user data table, log data table, etc.

3.2 What is horizontal segmentation? When the amount of data in a table is too large, we can divide the data of the table according to certain rules, such as userID hashing, and then store it in multiple tables with the same structure and different libraries.

For example, in the user data tables in our userDB, the amount of data in each table is very large, so we can divide userDB into multiple userDB: part0DB, part1DB with the same structure, and then divide userTable on userDB into many userTable: userTable0, userTable1, etc., and then store these tables in accordance with the rules specified in 1.

3.3 Which method should be used to implement database sub-database sub-table, which depends on the bottleneck of data volume in the database and the business type of the comprehensive project.

If the database is caused by too many tables, and the business logic of the project is clearly divided and has low coupling, then the vertical segmentation with simple rules and easy implementation must be the first choice.

And if there are not many tables in the database, However, the amount of data in a single table is very large, or the data heat is very high. In this case, horizontal segmentation should be selected. Horizontal segmentation is more complicated than vertical segmentation. It physically divides the data that originally logically belongs to one body. In addition to evaluating the granularity of segmentation, considering data average and load average, it will also generate extra data management burden for project personnel and applications in the later stage.

In real projects, it is often a combination of these two situations, which requires a trade-off, even requiring both vertical and horizontal segmentation. Our game project uses a combination of vertical and horizontal segmentation. We first cut the database vertically, and then cut it horizontally for a part table, usually a user data table.

How does mycat realize sub-library and sub-table? mycat implements fragmentation table by defining routing rules (fragmentation fields and fragmentation algorithms will be defined in routing rules). There are many slicing algorithms, and the hash you mentioned is one of them, as well as modulus taking, slicing by range and so on. In mycat, all passed sql statements will be routed (the basis of routing processing is whether the table is fragmented. If it is fragmented, it is necessary to judge which one, or which several, or all nodes sql should be passed to according to the fragmentation field and the corresponding fragmentation algorithm)

Summarize

The above is my Java development sub-library sub-table need to solve the problem and mycat is how to achieve sub-library sub-table problem and its optimization summary, if there are errors or not considered completely, please feel free to comment.