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 Split can be very good
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 Split can be very good
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:
User_db; create table user_db. 'uc _ user' ('user _ id' bigint (20) not null, 'created _ time' datetime default null, primary key ('user _ id ')) ENGINE = InnoDB,; -- Query mysql order_db; create table order_db. 'order' ('order _ id' bigint (20) not null, 'created _ time' datetime default null, 'user _ id' bigint (20) not null, primary key ('order _ id') ENGINE = InnoDB,; -- Query mysql log_db; create table log_db. 'order _ log' ('orlog _ id' bigint (20) not null, 'created _ time' datetime default null, 'user _ id' bigint (20) not null, primary key ('orlog _ id') ENGINE = InnoDB,; -- Query mysql plocc_db; create table plocc_db. 'plocc _ parameter '('plocc _ id' bigint (20) not null, 'created _ time' datetime default null, 'status' bigint (20) not null, 'creator' bigint not null, primary key ('plocc _ id') ENGINE = InnoDB,; -- 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; {user_id; (String [] args) {MySQLTest2 mt = new MySQLTest2 ();/* input 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, user_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);} Connection getConn (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 blocke. printStackTrace ();} String url = "jdbc: mysql: // 192.168.52.130:" + port +" http://www.linuxidc.com/ "+ Type; try {conn = DriverManager. getConnection (url, "tim", "timgood2013");} catch (SQLException e) {// TODO Auto-generated catch blocke. 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 = cal. get (Calendar. MONTH); // obtain the MONTH int day = cal. get (Calenda R. 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 return df. format (new Date ();} (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, javascuteupdate ();} catch (SQLException e) {e. printStackTrace () ;}return I; // return the number of affected rows. 1 indicates execution successful} (Connection cnn, BigInteger user_id, BigInteger order_id, BigInteger orlog_id, String action) {Str Ing 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(registry.prestmt.setstring(5, action= I =prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} return I; // returns the number of affected rows. 1 indicates execution successful} (Connection cnn, BigInteg Er 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({{ I }prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace () ;}return I; // return the number of affected rows. 1 indicates execution successful} () {int I = 0; return I ;}}
For more details, please continue to read the highlights on the next page:
