ASP. NET Website optimization series database optimization table sharding (Vertical Split, horizontal partition)

I talked about database sharding in the previous article.

1. Vertical table sharding
Vertical table sharding refers to splitting a table with 20 columns into two tables, one table with 10 columns and one table with 11 columns. This reduces the capacity of a single table, it can improve the query performance, reduce the lock row to a certain extent, and lock the performance loss of the strap.

What is the principle of vertical table sharding and how should we split it? The answer is to split the table based on the needs of the business logic. If the business of a table accesses part of data in a table twice, the split can be performed based on the different columns each time; you can also split columns Based on the update frequency. For example, some columns must be updated three times a day, and some columns are rarely updated from creation.

Example:
In this scenario, I have a user table, which contains the following columns:
ID, username, password, realname, gender, email, isemailvalid, birthday, country, city, address, mobile, phone, zipcode, hometown, officephone, company, position, industry, latestlogintime, latestloginip, logintimes, onlineminutes

Assume that our login has a performance problem, and user login often times out in the database. We plan to solve this problem by splitting the table. First, let's take a look at the login fields: username, password, latestlogintime, latestloginip, and logintimes. Then we can split the original table into two tables, userlogin and userbase, the latter includes other column information except logon information. Both tables must contain the primary key ID.

2. Horizontal partitioning
A horizontal partition is used to split a table from the perspective of rows. For example, you can place the data created before 05 years in one partition and place the data between 05 years and 08 years in another partition, and so on. The columns of the horizontal partition must be clustered indexes, which are usually divided by time and primary key ID.

Horizontal partitions divide data into different zones. When querying by partition column conditions, you can narrow the query range to improve query performance. In addition, if the database server has multiple CPUs, in this way, you can achieve better performance through parallel operations.

In the end, horizontal Partitioning Based on the column is related to the query. When creating a table, we need to analyze and query based on the column.

Example:
1. Orders are very effective entities. We seldom query order data a few years ago, so we can create partition functions in the order creation time column for partitioning.
2. for example, a post is usually accessed only when the latest post recommended on the home page is frequently accessed, but a few years ago the probability of a post being accessed is small, at this time, we can partition based on the primary key ID of the post. If the ID is less than 300 W, it is in one partition, And the ID is between and W.
For more information about partitioning, see ms SQL Server database partitioning for high performance website building
For more information about how to partition, see SQL Server partition drill.

 

Related Articles: ASP. NET Website optimization series database optimization sub-Databases