It Maple fighter 2022-01-15 03:46:30 阅读数:818
When users browse goods on the e-commerce platform , The first thing you see is the basic information of the commodity , If you are interested in the product, you will continue to view the detailed description of the product
The access frequency of commodity basic information is higher than that of commodity detailed description information , The access efficiency of commodity basic information is higher than that of commodity detailed description information ( Large field ). Because of the difference between the two kinds of data , Therefore, consider splitting the commodity information table as follows :
This split is called Vertical sub table . Vertical table definition : Spread the fields of one table into multiple tables , Each table stores some of the words ** paragraph .** The improvement brought by vertical table is :
Generally speaking , The access frequency of each data item in a business entity is different , Some data items may take up more storage space BLOB or TEXT, For example, in the example above Item description field . therefore , When the amount of data is large , Sure Split the table by field , Will hot fields 、 Unpopular fields are placed separately in different tables . Performance improvement brought by vertical segmentation , It mainly focuses on the operation efficiency of popular data , And disk contention is reduced . We usually split vertically according to the following principles :
Through the vertical sub table , Database performance has been improved to a certain extent , But it hasn't met the requirements , And the disk space is running out , Because the data is always stored on one server . The vertical table division in the library only solves the problem of too much data in a single table , But it doesn't distribute tables to libraries on different machines , Therefore, for reducing the pressure on the database , Limited role , We are still competing for the same physical machine CPU、 Memory 、 The Internet IO、 disk
Take e-commerce platform as an example , You can put the original SELLER_DB( Seller Library ), Split into PRODUCT_DB( Commodity bank ) and STORE_DB( Shop Library ), And distribute the two libraries to different servers , As shown in the figure below :
because Commodity information And Commodity Description High business coupling , So they are stored together in PRODUCT_DB( Commodity bank ); and Store information Relatively independent , So it's stored separately in STORE_DB( Shop Library ), This is called Vertical sub database
Vertical sub database It refers to classifying tables according to business , Distributed to different databases , Each library can be placed on a different server , So as to achieve the effect of sharing pressure among multiple servers . The improvement brought about by vertical sub database is :
after Vertical sub table and vertical sub database after , The database performance problem is completely solved ? Suppose an e-commerce platform is developing rapidly ,PRODUCT_DB( Commodity bank ) The data stored in a single database has exceeded the estimate . Suppose that the current platform has 8w The store , Each store has an average of 150 Products of different specifications , Add in the growth , Then the quantity of goods will reach 1500w+ Level , also PRODUCT_DB( Commodity bank ) It belongs to a very frequent resource , Performance bottlenecks reappear
Can you divide the library vertically again ? From a business perspective , At present, it is impossible to split vertically again . So we thought of another way , Judge the goods ID Is it odd or even , Then store the commodity information in two databases . in other words , To manipulate a piece of data , First analysis The commodity of this data ID, If the goods ID It's odd , Map this operation to RRODUCT_DB1( Commodity bank 1); If the goods ID For the even , Map operations to RRODUCT_DB2( Commodity bank 2), This is called Horizontal sub database
Horizontal sub database It is to split the data of the same table into different databases according to certain rules , Each library can be placed on a different server . The improvement it brings is :
When an application is no longer fine-grained for vertical segmentation , Or the number of data rows is still huge after segmentation , There is a single library reading and writing 、 Storage performance bottlenecks , It's time to do Horizontal sub database 了 , After the optimization of horizontal segmentation , Can often solve the single inventory reserves and performance bottlenecks . But because the same table is assigned in different databases , Routing work that requires additional data manipulation , Therefore, the system complexity is greatly increased
The database can be split horizontally , Is the data sheet ok ? We try to put some PRODUCT_DB( Commodity bank ) Table in , A horizontal split was made :
It is similar to the idea of horizontal sub database , But the goal of this split is table , Product information and product description are divided into two sets of tables . If the goods ID It's odd , Map this operation to product information 1 surface ; If the goods ID For the even , Map operations to product information 2 surface , This is called Horizontal sub table . Horizontal sub table It's in the same database , Split the data of the same table into multiple tables according to certain rules . The improvement it brings is :
The level table in the library , It solves the problem of too much data in a single table , The separated tables contain only a part of the data , Thus, the data quantity of a single table is reduced , Improve retrieval performance . But because the data of the same table is split into multiple tables , Additional routing for data operations is also required , Therefore, the system complexity is increased
Generally speaking , In the system design stage, we should determine the vertical sub base according to the business coupling tightness , Vertical table scheme , When the amount of data and access pressure is not particularly large , Consider caching first 、 Read / write separation 、 Index technology, etc . If the amount of data is huge , And continue to grow , Then consider the scheme of horizontal database and table
Before dividing the warehouse and table , We can search the products through the following SQL Query the store information :
SELECT p.*,s.[ Shop name ],s.[ credibility ] FROM [ Commodity information ] p LEFT JOIN [ Store information ] s ON p.id = s.[ The store it belongs to ] WHERE...ORDER BY...LIMIT...
But after dividing the database and table ,**[ Commodity information ] and [ Store information ]** Not in a database or a table , Not even on a server , Unable to get sql Statement for association query , We need additional programming to solve this problem
版权声明:本文为[It Maple fighter]所创,转载请带上原文链接,感谢。 https://javamana.com/2021/12/202112122326534522.html