MySQL Sub-Library sub-table

Table is a good way to distribute the pressure of the database.

Table, the most straightforward meaning, is to divide a table structure into multiple tables, and then, can be in the same library, can also be placed in different libraries. (Vertical sub-Library: Different business data uses different database instance stores) of course, first of all, you need to know what the situation is, and then the table needs to be divided. Personally feel the number of single-table records reaches millions to tens use a tableThe 1, classification of the sub-table 1> longitudinal sub-tableThe content that would have been in the same table could have been artificially divided into multiple tables. (So-called originally, refers to the third paradigm according to the relational database requirements, is supposed to be in the same table.) Reason: Separate according to the activity of the data, (because different active data, processing way is different) case: For a blog system, the article title, author, classification, creation time, etc., is the change frequency is slow, the number of queries, and preferably have good real-time data, we call it cold data. and the number of blog views, replies, and so on, similar statistics, or other high-frequency changes in the data, we call it active data. Therefore, in the design of database structure, we should consider the sub-table, first of all, the processing of vertical table. This longitudinal table after: the first storage engine use different, cold data using MyISAM can have better query data. Active data, can use InnoDB, can have better update speed. Second, the cold data is more from the library configuration, because more operations when queried, so as to speed up the query. For thermal data, it is possible to handle the horizontal table of the main library relatively. In fact, for some special active data, you can also consider the use of memcache, Redis and other caches, such as accumulated to a certain amount to update the database. or MongoDB type of NoSQL database, here is just an example, not to say this first. 2> Horizontal sub-tableLiterally, it can be seen that the large table structure, horizontal cutting for the same structure of the different tables, such as user Information table, user_1,user_2 and so on. The table structure is exactly the same, however, according to some specific rules to partition the table, such as according to the user ID to take the module partition. Sub-table reason: According to the size of the data volume to divide, to ensure that the capacity of the single table is not too large, so as to ensure the single-table query processing capacity. Case: With the above example, the blog system. When the amount of blog is very large, you should take the horizontal segmentation to reduce the pressure of each single table, to improve performance. For example, a blog cold data table, if divided into 100 tables, when there are 1 million users in the browsing, if it is single table, will make 1 million requests, and now after the table, it is likely that each table for 10,000 data requests (because, can not be absolute average, just assume), so the pressure is much lower.

MySQL Sub-Library sub-table