MySQL Vertical Split (Reading Notes)

MySQL Vertical Split (Reading Notes)
1. Vertical Split

Compared with horizontal split, vertical split is easier to implement. Vertical Split means to split different business data in the database into different databases. Vertical splitting can effectively distribute database pressure. This splitting method is not suitable for systems with relatively high coupling (Table Association) levels due to unclear business modules.

Some users query their orders quickly, but query their user information slowly. Why?

2. Advantages of vertical splitting

◆ Database splitting is simple and clear, and sharding rules are clear;
◆ The application module is clear and clear, and integration is easy;
◆ Data maintenance is convenient and easy to locate;

 

3. disadvantages of vertical splitting

◆ Some table associations cannot be completed at the database level and must be completed in the program;
◆ Tables with extremely frequent access and large data volumes still have stable performance and may not meet the requirements;
◆ Transaction processing is more complex;
◆ When the splitting reaches a certain degree, the scalability will be limited;
◆ Read splitting may result in complicated system transition and difficult to maintain.

For vertical splitting, data splitting and transaction problems may occur. It is difficult to find a good solution at the database level. In actual application cases, the vertical split of databases mostly corresponds to the modules of the application system. The data sources of the same module are stored in the same database, which can solve the internal data association problem of the module. Between modules, applications provide the required data in the form of service interfaces. Although this will indeed increase the total number of database operations, it is beneficial in terms of overall system scalability and modular architecture.

The response time for some operations may increase slightly, but the overall performance of the system may be improved. But the expansion bottleneck.

4. sharding rules

Split by module, such as user module, order module, log module, and system parameter Module
User Module table: uc_user table; uc_user_info table; uc_action table; uc_profile table
Order module table: order_action table; order_list table; shop table; order table;
Log Module: order_log table; uc_log table; plocc_log table;
System parameter module: plocc_parameter table;

Modules are all associated with each other. These modules are used by users and are associated with the user module.

As shown in:

 

5. Vertical splitting demonstration 5.1 prepare the mysql Environment

For details about how to create multiple instances, see:

SQL statement for creating a database:

-- 1 user module port 3307 database create database user_db; create table user_db. 'uc _ user' ('user _ id' bigint (20) not null, 'uc _ name' varchar (200) default null, 'created _ time' datetime default null, primary key ('user _ id') ENGINE = InnoDB default charset = utf8; grant insert, update, delete, select on user_db. * to tim @ '2017. 168.% 'identified by 'timgood2013 '; -- Query mysql -- socket =/usr/local/mysql3307/mysql. sock-e "select * from user_db.uc_user;"; -- 2 order module port 3308 database create database order_db; create table order_db. 'order' ('order _ id' bigint (20) not null, 'shop _ id' varchar (200) default null, 'created _ time' datetime default null, 'User _ id' bigint (20) not null, primary key ('order _ id') ENGINE = InnoDB default charset = utf8; grant insert, update, delete, select on order_db. * to tim @ '2017. 168.% 'identified by 'timgood2013 '; -- Query mysql -- socket =/usr/local/mysql3308/mysql. sock-e "select * from order_db.order;"; -- 3: Log Module, port 3309, database create database log_db; create table log_db. 'order _ log' ('orlog _ id' bigint (20) not null, 'order _ id' varchar (200) default null, 'created _ time' datetime default null, 'User _ id' bigint (20) not null, 'Action' varchar (2000) not null, primary key ('orlog _ id') ENGINE = InnoDB default charset = utf8; grant insert, update, delete, select on log_db. * to tim @ '2017. 168.% 'identified by 'timgood2013 '; -- Query mysql -- socket =/usr/local/mysql3309/mysql. sock-e "select * from log_db.order_log;"; -- 4 parameter module, port 33310, database create database plocc_db; create table plocc_db. 'plocc _ parameter '('plocc _ id' bigint (20) not null, 'pname' varchar (200) default null, 'created _ time' datetime default null, 'status' bigint (20) not null, 'creator' bigint not null, primary key ('plocc _ id') ENGINE = InnoDB default charset = utf8; grant insert, update, delete, select on plocc_db. * to tim @ '2017. 168.% 'identified by 'timgood2013 ';--

5.2 demonstrate java code

Package mysql; import java. math. bigInteger; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import java. text. simpleDateFormat; import java. util. calendar; import java. util. date; public class MySQLTest2 {public static long user_id; public static void main (String [] args) {MySQLTest2 Mt = new MySQLTest2 ();/* enter user data BigInteger user_id0 = new BigInteger ("10001"); Connection conn = mt. getConn ("user_db"); mt. insertUser (conn, bi, "tim --" + user_id0.longValue (); * // * enter the log data Connection conn2 = mt. getConn ("log_db"); BigInteger user_id = new BigInteger ("10001"); BigInteger order_id = new BigInteger ("20150531001"); BigInteger orlog_id = new BigInteger ("20150531001 "); mt. insertLog (conn2, use R_id, order_id, orlog_id, "create a order for tim"); * // Enter order data Connection conn3 = mt. getConn ("order_db"); BigInteger user_id2 = new BigInteger ("10001"); BigInteger order_id2 = new BigInteger ("20150531001"); BigInteger shop_id2 = new BigInteger ("20150531001 "); mt. insertOrder (conn3, order_id2, shop_id2, user_id2);} // gets the database connection. If it is extended, You can independently provide an interface to the programmer to call it. // String type: connection data type public Connection getC Onn (String type) {String port = "3307"; if (type = "user_db") {port = "3307";} else if (type = "order_db ") {port = "3308";} else if (type = "log_db") {port = "3309";} else if (type = "plocc_db ") {port = "3310";} else {port = "3311";} Connection conn = null; try {Class. forName ("com. mysql. jdbc. driver ");} catch (ClassNotFoundException e) {// TODO Auto-generated catch block e. printStackTrace ();} String url = "j Dbc: mysql: // 192.168.52.130: "+ port +"/"+ type; try {conn = DriverManager. getConnection (url, "tim", "timgood2013");} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();} System. out. println ("the current db is:" + url); return conn;} // obtain the date String public String getTimeByCalendar () {/* Calendar ar cal = Calendar ar. getInstance (); int year = cal. get (Calendar. YEAR); // obtain the YEAR int month = c Al. get (Calendar. MONTH); // obtain the MONTH int day = cal. get (Calendar. DATE); // get the day int hour = cal. get (Calendar. HOUR); // HOUR int minute = cal. get (Calendar. MINUTE); // MINUTE int second = cal. get (Calendar. SECOND); // second String strdate = year + "-" + month + "-" + day + "" + hour + ":" + minute + ":" + SECOND; */SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss"); // set the date format System. out. println (df. format (new Date (); // new Date () is used to obtain the current system time ret Urn df. format (new Date ();} // start to input user data public int insertUser (Connection cnn, BigInteger user_id, String name) {String SQL = "insert into user_db.uc_user (user_id, uc_name, created_time) values (?,?,?) "; Int I = 0; long uid = user_id.longValue (); Connection conn = cnn; try {PreparedStatement preStmt = conn. prepareStatement (SQL); preStmt. setLong (1, uid); preStmt. setString (2, name); preStmt. setString (3, getTimeByCalendar (); i1_prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} return I; // return the number of affected rows. 1 indicates execution successful.} // start to input the log data public int insertLog (Connection cnn, BigInteger user_id, BigInteger Order_id, BigInteger orlog_id, String action) {String SQL = "insert into log_db.order_log (orlog_id, order_id, created_time, user_id, action) values (?,?,?,?,?) "; Int I = 0; Connection conn = cnn; try {PreparedStatement preStmt = conn. prepareStatement (SQL); preStmt. setLong (1, user_id.longValue (); preStmt. setLong (2, order_id.longValue (); preStmt. setString (3, getTimeByCalendar (); preStmt. setLong (4, orlog_id.longValue (); preStmt. setString (5, action); imo-prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} return I; // return the number of affected rows. 1 indicates execution successful} // start Input Public int insertOrder (Connection cnn, BigInteger order_id, BigInteger shop_id, BigInteger user_id) {String SQL = "insert into order_db.order (order_id, shop_id, created_time, user_id) values (?,?,?,?) "; Int I = 0; Connection conn = cnn; try {PreparedStatement preStmt = conn. prepareStatement (SQL); preStmt. setLong (1, order_id.longValue (); preStmt. setLong (2, shop_id.longValue (); preStmt. setString (3, getTimeByCalendar (); preStmt. setLong (4, user_id.longValue (); imo-prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace () ;}return I; // return the number of affected rows. 1 indicates execution successful} // start to input the parameter data public int insertPlocc () {int I = 0; return I ;}} 

For more details, please continue to read the highlights on the next page:

1 2 Next Page