The database table Segmentation technology includes the following content: horizontal segmentation vertical segmentation database table hash. next, we will introduce the preceding sections in detail. If you are interested, you can find it helpful for you to maintain the database in the future.
The database table Segmentation technology includes horizontal/vertical/database table hashing. next, we will introduce the preceding sections in detail. If you are interested, you can find it helpful for you to maintain the database in the future.
1. horizontal segmentation
What is horizontal segmentation? In an image-like metaphor, there is only one window for dining in the canteen. The waiting queues are too long and all of them are in the S-type format. This makes it easy for the queuing staff to get anxious, it is prone to confusion. At this moment, a manager stands up and adds multiple meal preparation windows to cut the long team apart into several teams. For a better understanding, you take a "surgical knife" and slashes a large table. As a result, this big table is changed to several small tables.
Horizontally split data into two or more independent tables based on certain conditions. That is, it is divided by records. Different records can be saved separately, and each sub-table has the same number of columns. Horizontal cutting divides a table into multiple tables. Each table contains the same number of columns, but fewer data rows. For example, you can partition a table with billions of rows into 12 tables horizontally. Each small table indicates the data of one month in a specific year. For any query that requires data of a specific month, you only need to reference the table of the corresponding month.
The conditions used for horizontal table segmentation are: date, time, and region. Of course, there are more business dimensions. Let me give you a few examples to illustrate.
Case 1: A company's sales records have a large amount of data. We can split them horizontally on a monthly basis, and each month's sales records are made into a separate table.
Case 2: A group has branches in various regions. The order data table of the group is too large. We can perform horizontal cutting according to the region where the branch is located.
Case 3: After cutting a telecom company by date or city level, the company finds that the data volume is too large, and then they perform horizontal cutting by brand and number segment.
Horizontal segmentation is usually used in the following scenarios:
(1) The table has a large amount of data. After partitioning, the data to be read and the number of pages of the index can be reduced during query. At the same time, the number of layers of the index can be reduced and the query speed can be improved.
(2) data in a table is inherently independent. For example, data in different regions or periods is recorded in a table. In particular, some data is commonly used, while others are not commonly used.
(3) data must be stored on multiple media.
(4) split the historical data and the current data.
Advantages:
1: reduces the number of data to be read and the number of index pages during query, as well as the number of indexes and query speed.
Disadvantages:
1: horizontal split will increase the complexity of the application. It usually requires multiple table names during query, and the union operation is required to query all data. In many database applications, this complexity will exceed the advantage it brings, because as long as the index keyword is not large, the amount of data in the table is increased by two to three times when the index is used for query, the query increases the number of times an index layer disk is read.
Ii. vertical segmentation
What is vertical segmentation? In an image metaphor, a small company has changed to a large multinational enterprise after just a few years of development. The former department architecture obviously cannot meet the current business development requirements, the CEO divided the company into the Finance Department, the personnel department, the production department, and the Sales Department ....., at one time, multiple departments were set up to perform their respective duties. This is quite an image, right? Haha
You vertically split the table (without disrupting the third paradigm), put the primary code (primary key) and some columns in one table, and then put the primary code (primary key) and other columns in another table. The original table is divided into multiple tables that only contain fewer columns. If some columns in a table are commonly used, but other columns are not commonly used, vertical segmentation can be used.
Advantages:
1: vertical segmentation can reduce the size of row data. A data Block can store more data, the number of I/O operations is reduced during query (less blocks are read each time ).
2: vertical table segmentation can maximize the use of Cache.
Disadvantages:
1: After Vertical table segmentation, the master code (primary key) is redundant and redundant columns need to be managed.
2: Table JOIN Operations (increasing CPU overhead) need to be avoided from the business.
3. Database and table hash
Table Hash is similar to horizontal segmentation, but it does not have the obvious segmentation boundary as horizontal segmentation. Hash algorithms are used to distribute data to various table shards, which improves IO balancing. Generally, databases are separated by business or functional modules. Different modules correspond to different databases or tables, A page or function is further hashed based on certain policies. For example, the user table is hashed according to the user ID, and 128 tables are hashed, therefore, the system performance should be improved at a low cost with good scalability.
