2017-6-5/mysql Sub-database sub-table

The sub-database, as the name implies, is stored in a library of a table of data chunked stored in multiple libraries on several tables. For large-scale Internet applications, when the amount of data in a single table reaches tens of millions, the database spends more time per query, and the database faces very high concurrent accesses. through the Sub-database table, so that the data evenly distributed across multiple tables, you can shorten the time required for data query, improve database throughput, reduce database pressure.

First, vertical segmentationThe table is divided into functional modules, close relationships, and deployed to different libraries. The common divide is 6 large libraries:1) User Class Library: Used to save information about the user. For example: Db_user,db_system,db_company and so on. 2) Business Class Library: Information that is used to hold the primary business. For example, the main business is the order, use this library to save the order business. For example: Db_order and so on. 3) Memory class Library: The main use of MySQL memory engine. Data from the foreground is found in the memory library and is faster. For example: Heap. 4) Picture Class Library: The index of the main save picture and association. For example: Db_img_index,db_img_res. 5) Log class Library: Record click, Refresh, login and other log information. For example: Db_log_click,db_log_fresh,db_log_login. 6) Statistical class Library: Statistics on the business, such as click Volume, refresh Amount and so on. such as Db_stat. 1, Vertical sub-tableVertical sub-table, popular parlance is called "Large table Split table", based on the "column" (field) in the relational database to split. In general , a table of more than one field, you can create a new "extension table", the less frequently used or the length of a large field split out into the "extension table", the number of data records in each table is generally the same, but the field is not the same. 2. Vertical Sub-LibraryThe vertical sub-Library is divided into different business libraries according to the different business functions. For example: A database contains both user data and order data, as well as product data, then vertical splitting can put the user data into the user library, the order data placed in the order database, the product data into the product library. second, horizontal splitwhen the amount of data in a table is too large, the data of the table can be divided into some rules, such as the UserID hash, and then stored in multiple tables and different libraries with the same structure. 1, the level of the tableHorizontal sub-table is also called horizontal table, that is, different data rows in the table are distributed to different database tables according to certain rules (still in the same database), so as to reduce the amount of single table data and optimize query performance. For example: There is a user table users, because the number of records too many, split it into 256 tables, split records according to user_id%256 to obtain the corresponding table for storage, such as user_id for 10001,10001%256= 17, the data of user_id 10001 is present in table User17. 2. Horizontal sub-Librarysimilar to the sub-table strategy, the library can also use a key field to model the way in which to route data access. three, vertical horizontal splitVertical Horizontal Split, is a combination of vertical and horizontal splitting method of a hybrid way, vertical split to different types of data stored in different libraries, combined with horizontal split, so that the single-table data to maintain a reasonable range, expand the system's concurrent processing capacity, improve the query performance of the single table. the routing strategy for a sub-database table is as follows (assuming user_id as the Routing field ): Intermediate variable =user_id% ( number of libraries × number of tables per library) Library = rounding (middle variable / number of tables per library) table = Intermediate variable% number of tables per library Assuming that the original single-Library order is split into 256 libraries, and each library contains 1024 tables, the route is calculated as follows for user_id=262145 access according to the routing policy mentioned earlier:? Intermediate variable =262145% (256x1024) = 1;? Library = rounding (1/1024) = 0;? Table =1%1024=1. This means that for the query and modification of the user_id=262145 order record, it will be routed to the 1th table in the No. 0 library for execution. Resourcesseveral common forms of the sub-database and the difficulties that may be encounteredthe practice summary and key steps of the sub-list of orders of the only commodities meeting

2017-6-5/mysql Sub-database sub-table