Asp.Net website optimization series database optimization sub table (vertical split horizontal partition)
- 2020-05-10 17:58:23
- OfStack
1. Vertical subtables
Vertical sub-table refers to the division of a table with 20 columns into two tables according to the columns: 1 table with 10 columns and 1 table with 11 columns. In this way, the capacity of a single table will be greatly reduced, which can improve the performance of the query and reduce the performance loss caused by locking rows and locking tables to a certain extent.
What are the principles of vertical tables, and how should they be split? The answer is to split the table according to the needs of the business logic. For a table, if the business accesses the data of part 1 of a table twice, it can split the table according to the different columns of each visit. It is also possible to split columns based on how often they are updated, for example, some columns are updated three times a day, and some columns are rarely updated from creation.
For example:
For the scenario, I have a user table that contains columns:
ID, UserName, Password, RealName, Gender, Email, IsEmailValid, Birthday, Country, City, Address, Mobile, Phone, ZipCode, Hometown, OfficePhone, Company, Position, Industry, LatestLoginTime, LatestLoginIP, LoginTimes,OnlineMinutes
Let's say we have a performance problem with our logins now, and user logins often have database timeouts. We are going to solve this problem by disassembling a watch. Look at the first involves the login fields are: UserName, Password, LatestLoginTime, LatestLoginIP, LoginTimes; Then we can split the original table into two tables: UserLogin and UserBase. The latter contains other column information except the login information. Both tables should contain the primary key ID.
2. Horizontal partitioning
Horizontal partitioning is the splitting of a table from the perspective of rows, such as placing data created before 2005 on one partition, data created between 2005 and 2008 on another partition, and so on. Horizontal partitioning is based on columns that must be on a clustered index, usually divided by time, primary key id, and so on.
The horizontal partition divides the data into different zones, which can reduce the query range and improve the query performance. In addition, if the database server has multiple cpu, better performance can be achieved through parallel operation.
Which column should be used for horizontal partitioning is related to the query. When we are building a table, we need to analyze and query according to that column.
For example:
1. The order is a highly effective entity. We rarely query the order data several years ago, so we can create a partition function on the order creation time column to partition.
2. For example, usually only the most recent posts recommended on the home page are accessed a lot, while posts from several years ago are not. At this time, we can partition posts according to the primary key id, those with id less than 300w are on one partition, and those with id between 300w and 600w are on one partition.
For more information about partitioning, please refer to the MS Sql Server database partitioning for high-performance website construction
Refer to the Sql Server partitioning walkthrough for how to partition
Vertical sub-table refers to the division of a table with 20 columns into two tables according to the columns: 1 table with 10 columns and 1 table with 11 columns. In this way, the capacity of a single table will be greatly reduced, which can improve the performance of the query and reduce the performance loss caused by locking rows and locking tables to a certain extent.
What are the principles of vertical tables, and how should they be split? The answer is to split the table according to the needs of the business logic. For a table, if the business accesses the data of part 1 of a table twice, it can split the table according to the different columns of each visit. It is also possible to split columns based on how often they are updated, for example, some columns are updated three times a day, and some columns are rarely updated from creation.
For example:
For the scenario, I have a user table that contains columns:
ID, UserName, Password, RealName, Gender, Email, IsEmailValid, Birthday, Country, City, Address, Mobile, Phone, ZipCode, Hometown, OfficePhone, Company, Position, Industry, LatestLoginTime, LatestLoginIP, LoginTimes,OnlineMinutes
Let's say we have a performance problem with our logins now, and user logins often have database timeouts. We are going to solve this problem by disassembling a watch. Look at the first involves the login fields are: UserName, Password, LatestLoginTime, LatestLoginIP, LoginTimes; Then we can split the original table into two tables: UserLogin and UserBase. The latter contains other column information except the login information. Both tables should contain the primary key ID.
2. Horizontal partitioning
Horizontal partitioning is the splitting of a table from the perspective of rows, such as placing data created before 2005 on one partition, data created between 2005 and 2008 on another partition, and so on. Horizontal partitioning is based on columns that must be on a clustered index, usually divided by time, primary key id, and so on.
The horizontal partition divides the data into different zones, which can reduce the query range and improve the query performance. In addition, if the database server has multiple cpu, better performance can be achieved through parallel operation.
Which column should be used for horizontal partitioning is related to the query. When we are building a table, we need to analyze and query according to that column.
For example:
1. The order is a highly effective entity. We rarely query the order data several years ago, so we can create a partition function on the order creation time column to partition.
2. For example, usually only the most recent posts recommended on the home page are accessed a lot, while posts from several years ago are not. At this time, we can partition posts according to the primary key id, those with id less than 300w are on one partition, and those with id between 300w and 600w are on one partition.
For more information about partitioning, please refer to the MS Sql Server database partitioning for high-performance website construction
Refer to the Sql Server partitioning walkthrough for how to partition