An achievable architecture for the sub-database sub-table strategy

The Sub-database table is The main means to solve the MySQL level expansion.

There are many discussions about strategy, such as hash extension, expansion by time, extension by scope and so on. But the people who really want to implement the sub-list of the library often feel that " the strategy listens to the end of the light, aware of this matter to code ", so the main purpose of this article is to provide friends with an achievable architecture.

　　JdbcTemplate and Hibernate

We all know that.Hibernateis aORM(Object-relational databaseMapping) in the sense of the first real"Domination level"products. JdbcTemplateIt is theSpringtheJDBCa simple package, relative toHibernate, engineers need to write their ownSQL, and not likeHibernatethe direct manipulation of objects solves the problem of database persistence.

Because of the exposure of SQL,jdbctemplate Of course also does not benefit cross-database (after all, the implementation of each database product SQL is not the same ). But most Internet companies are now inclined to use JdbcTemplaterather than Hibernate.

I think the main reason is the performance problem:

(1) In order to obtain better performance, often according to different databases to adopt a unique optimization method, even the DAO layer all with Hibernate Implementation, migrating the database is not easy to work.

(2) using Hibernate to process associations often loads large amounts of data into business system memory, rather than processing them in the database system, but returning the final result. This undermines the decoupling of the production system and the db , resulting in db optimization difficulties and unsafe production systems.

(3) The Sub-database table is more complicated for Hibernate

It can be said that the third reason is the main one. This article will implement a sub-list around JdbcTemplate, if you are still using Hibernate, it is recommended to switch to jdcbtemplate .

Sub-database sub-table strategy

　　The branch table strategy is simply to assign a library or table to read / write based on the data being persisted . so the dbsplitstrategy interface is defined as follows :

　　Interface Dbsplitstrategy {

String Getdbname (long id); Get Library name

String Gettablesuffix (long id); Get table name

JdbcTemplate getidxjdbctemplate (long id); get db JT

JdbcTemplate getidxjdbctemplate (String dbname); Get DB JT based on library name

JdbcTemplate getidxjdbctemplatebytable (String table); Get DB JT based on table name

}

Interface definition is around the most basic:key , logical library name / table name , Physical library name / Table name

Implementation class

In the case of the most common hashsplit , first we need a few basic configuration items:

　(1) Basic library name, can also be called library name prefix;

(2) The total number of sub-databases;

(3) Total number of tables;

(4) The physical address of the sub-Library, i.e. the jdbctemplate definition

Spring Configuration

<bean id= "DataService" class= "Dbsplitstrategy" >
<property name= "Dbnamebase" value= "Session_"/>
<property name= "Splitdbcount" value= "/>"
<property name= "Splittbcount" value= "Up"/>
<property name= "Dmjts" >
<map>
<entry key= "session_1" value-ref= "Jts1" ></entry>
<entry key= "session_2" value-ref= "Jts2" ></entry>
...

With the above configuration, the code works only need to convert the Input keyword installation strategy into a logical library name, table name, pseudo-code as follows: code

Public String gettablename (long id) {

Long hash = Gethash4split (ID, splitcount);

return tbnamebase + string.valueof (hash/sharedbcount + 1);

}

Public String getdbname (long id) {

Long hash = Gethash4split (ID, splitcount);

return dbnamebase + (hash% sharedbcount + 1);

}

There's an interesting logic in this code, if your business primary key is from1has been growing, the result of the sub-database table is: library1, the table0; library2, the table0; library3, the table0;..... Library1, the table2; library2, the table2;... 

Summarize

Mysql Sub-database sub-table, horizontal expansion There are a lot of problems here are not involved, for example,

What if I don't have enough of the first allocated scale? This is the initial decision to divide the table is an important issue to consider, because the hash is easy,rehash difficult.

So much data scattered in different library tables, how to analyze and mining it?

What kind of sub-library strategy is better for you?

An achievable architecture for the sub-database sub-table strategy