Why should the database be divided into tables _ database

Source: Internet
Author: User
Tags one table


1 The basic idea of what is the Sub-Library table.
a literal simple understanding is to store data that was stored in a library on multiple libraries, storing chunks of data that were originally stored in one table on multiple tables.
2 basic ideas why to divide the table. The
amount of data in the database is not necessarily controllable, and in the case of no sub-library, with the development of time and business, the table in the library will be more and more, the data in the table will be more and more large, accordingly, data operations, additions and deletions to check the cost will be more and more; In addition, because of the inability to deploy in a distributed manner, And a server's resources (CPU, disk, memory, IO, etc.) is limited, the final database can host the amount of data, data processing capacity will encounter bottlenecks.
3 The implementation strategy of the Sub-Library table.
There are two kinds of vertical segmentation and horizontal segmentation in the dividing table.
 3.1 What is vertical segmentation, the table is divided into functional modules, the relationship between the close degree, deployed to different libraries.
For example, we will establish the definition database WORKDB, the commodity database paydb, the user database UserDB, the log database logdb and so on, respectively uses in the storage Project data definition table, the Commodity definition table, the User data table, the log data table and so on. 3.2 What is horizontal segmentation, when the amount of data in a table is too large, we can classify the data of the table according to some rules, such as UserID hash, and then store it in a table with the same structure, and on different libraries. For example, in our UserDB user datasheet, each table has a large amount of data, you can cut the USERDB into the same structure of multiple userdb:part0db, part1db, and then userdb the User data table usertable,
Cut into a lot of usertable:usertable0, UserTable1, and so on, and then store these tables in accordance with certain rules on a number of USERDB.
3.3 You should use a way to implement the database sub-table, which depends on the data in the database bottleneck, and integrated project business type to consider.
If the database is caused by too much data, and the business logic of the project is divided into clear, low coupling, then the rules are simple and straightforward, easy to implement vertical segmentation must be preferred. If there are not many tables in the database, but the data in a single table is large, or data heat is very high, this situation should choose horizontal segmentation, Horizontal segmentation than vertical segmentation to be more complex, it will originally logically belong to a body of data for the physical division, in addition to the division of the granularity of segmentation to do the evaluation,
Considering data averages and load averages, the latter will also generate additional data management burdens for project personnel and applications. In real-world projects, these two situations are often the same, which requires trade-offs, even vertical segmentation and horizontal segmentation. Our game project will use the vertical and horizontal segmentation, we first vertical segmentation of the database, and then for a part of the table, usually the user data table, horizontal segmentation. 
4. The problem of the divide-and-sink table.
4.1 business issues.
after the Sub-Library is executed, database transaction management is difficult because the data is stored on different libraries. If you rely on the distributed transaction management capabilities of the database itself to perform transactions, you will pay a high performance cost; If the application is to help control, the logic of the program to form a transaction, it will cause the burden of programming.
4.2 Cross-Library join issues across tables.
after the Sub-Library has been executed, it is difficult to avoid dividing the original logically related data into different tables, different libraries, at which point the table's associated operations are limited, we cannot join tables in different libraries, and we cannot join tables with different table sizes. As a result, the original query can be completed in a business, may require multiple queries to complete.
4.3 additional data management burden and data operation pressure.
additional data management burden, the most obvious is the data positioning problems and data additions and deletions to check the repeated implementation of the problem, which can be solved through the application, but will inevitably cause additional logic operations, for example, for a record of user performance of the User data table usertable, Business requirements identify the best 100-digit, before you make a table, you can do it with just one order by statement, but after you do the table, you will need n order by statements to isolate the first 100 user data for each of the tables, and then consolidate the data to get the results.
Article reproduced from

Http://zhidao.baidu.com/link?url=HdKlAq9GsIn5sUQlgQGOBT6b_ 2fkr4ljsng8n0nlckd97l8v0vo2ep-tokmfufdtlureyllcycjgbgj4eit75_uuzfa7eb41jfty48yojrk

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.