Database optimization-horizontal split vertical split

Under a certain condition, the data stored in the same database is distributed to multiple databases, distributed storage, routing rules to access a specific database, so each access is not a single server, but n servers, which can reduce the load pressure on a single machine. Tip: After SQL Server version 2005, you can support table partitioning friendly.

Vertical (Portrait) split: Refers to the function module split, such as the Order of the library, commodity library, user Library ... In this way, the table structure differs between multiple databases.

Horizontal (horizontal) Split: Blocks the data for the same table into separate databases with identical table structures.

▲ (split vertically)

▲ (split horizontally)

　　1, the principle of implementation : the use of vertical splitting, mainly to see whether the application type is suitable for such a split way, such as the system can be divided into, order system, commodity management system, user management system business system is relatively clear, vertical splitting can be very good to the role of decentralized database pressure. The business module is unclear, and a system with a higher coupling (table Association) degree is not suitable for this kind of splitting method. But the vertical splitting method does not completely solve all the pressure problems, such as having a 5000w order table, the pressure to operate the order library is still very large, such as we need to add (insert) a new data in this table, after the insert is completed, the database will re-index this table, 5000w row data The overhead of indexing is still not negligible, and conversely, if we divide the table into 100 tables, from table_001 to table_100,5000w rows, there is only 500,000 rows of data in each sub-table, This is when we index the Insert data in a table with only 50w rows of data, and the time for indexing is reduced by an order of magnitude, greatly improving the run-time efficiency of the DB and increasing the concurrency of the DB, which is split horizontally

　　2, the implementation method : Vertical Splitting, the implementation of the split method is relatively simple, according to the table name access to different databases. There are a lot of rules for horizontal splitting, and here is a summary of the previous points

(1) Sequential split: If you can by the date of the order by the year want $, 2003 in DB1, 2004 of DB2, and so on. Of course, you can also split by the primary key standard.

Pros: can be partially migrated

Disadvantage: Data distribution is uneven, May 2003 years of orders have 100w,2008 years of 500W.

(2) Hash modulus: user_id hash (or if the user_id is a numerical type directly using the value of user_id can also), and then use a specific number, such as the application needs to cut a database into 4 databases, we use 4 this number to User_ ID of the hash value of the modulo operation, that is user_id%4, so that each operation there are four possible: The result is 1 when the corresponding DB1; the result is 2 when the corresponding DB2; the result is 3 corresponds to DB3; The result is 0 corresponds to DB4, This allows the data to be distributed to 4 DB in a very uniform way.

Advantages: Uniform Data distribution

Cons: When data is migrated, you cannot allocate data according to machine performance.

(3) Save the database configuration in the authentication library

is to set up a DB, this DB separately save user_id to DB mapping, each time you access the database to query the database first to obtain the specific DB information, and then we need to do the query operation.

Advantages: Strong flexibility, one-on-one relationship

Disadvantage: The query before each query, will cause a certain performance loss.

Database optimization-horizontal split vertical split