The design idea and realization of the sub-table processing

Author: Heiyeluren (Night passers-by)
Blog: Http://blog.csdn.net/heiyeshuwu
Time: 2007-01-19 01:44:20
I. Overview
The table is a relatively popular concept, especially in the case of heavy load, the table is a good way to spread the pressure of the database.
First of all to understand why the table, the advantages of the table. Let's take a look at some of the following database execution procedures for SQL:
Receive SQL--> put into SQL execution queue--> use parser to decompose SQL--> extract or modify data according to analysis results--> return processing results
Of course, this flowchart is not necessarily correct, it's just my own subjective consciousness so I think. So what's the easiest thing to do with the process? That is, if the previous SQL is not completed, the following SQL will not be executed, because in order to ensure the integrity of the data, the data table file must be locked, including the shared lock and the exclusive lock two locks. A shared lock is a locked period in which other threads can access the data file, but it is not allowed to modify the operation, and, accordingly, the exclusive lock is the entire file that is owned by one thread, and other threads cannot access the data file. In general MySQL, the fastest storage engine MyISAM, it is based on table locking, that is, if a lock, then the entire data file is inaccessible outside, you must wait until the previous operation to receive the next operation, then in this previous operation is not completed, The last operation waiting in the queue can not be executed in the case called blocking, generally we are called "lock table" in the popular sense.
What are the immediate consequences of locking the table? Is that a large number of SQL cannot be executed immediately, and must wait until all of the SQL in front of the queue has been completed before execution can continue. This inability to execute SQL can result in no results, or a serious delay, affecting the user experience.
Especially for some tables that use more frequently, for example, SNS system in the User Information table, forum system in the post table and so on, are a large number of visits to the table, in order to ensure rapid data extraction returned to the user, must use some processing methods to solve this problem, this is what I want to talk about the table technology.
As the name suggests, a number of tables that store the same type of data are divided into tables and tables, and when data is extracted, different users visit different tables, not conflict, reducing the chance of locking the table. For example, currently save the user table has two tables, one is user_1 table, there is a user_2 table, two tables to save different user information, user_1 saved the first 100,000 of the user information, user_2 saved the last 100,000 users of information, now if the simultaneous query user Heiyeluren1 and Heiyeluren2 This two users, then the table is extracted from different tables, reducing the possibility of locking the table.
I am going to talk about the two kinds of table methods I have not tested, do not guarantee accurate use, but to provide a design ideas. The following is an example of a table I assume is based on a paste system to deal with and build. (If you have not used post bar users to quickly google)
Second, based on the basic table of the table-processing
The basic idea of a table-based table approach is that a primary table holds all the basic information, and if a project needs to find the table it stores, it must look for items from the underlying table, such as the corresponding table name, so that you can access the table directly. If you feel that the base table is not fast enough, you can save the entire underlying table in cache or in memory, and make it easier to query efficiently.
Based on the post bar, we build 3 tables that assume the following:
1. Paste the table: Save the information
2. Paste Theme Table: Save the topic information in the Forum, used to browse
3. Post-paste reply form: Save the original content and reply content of the topic
The Paste list contains the following fields:
Forum ID board_id Int (10)
Forum name Board_name Char (50)
Child table ID table_id smallint (5)
Generation Time created DateTime
The Paste Topic table contains the following fields:
Subject ID topic_id int (10)
Theme name Topic_name char (255)
Forum ID board_id Int (10)
Creation Time created DateTime
The field of "post-paste reply table" is as follows:
Reply ID reply_id int (10)
Reply Content Reply_text text
Subject ID topic_id int (10)
Forum ID board_id Int (10)
Creation Time created DateTime
Then the above saved our entire bar in the table structure information, the three tables correspond to the relationship is:
Forum--> Multiple Themes
Topic--> Multiple Replies
So, the relationship of table file size is:
Table file < topic Table File < reply form file
So it's almost certain that the topic table and the reply table need to be divided into tables, which increases the speed and performance of our data retrieval query changes.
Look at the above table structure, it will be obvious that in the "plate table" to save a "table_id" field, this field is used to save a section of the corresponding theme and reply are the table kept in the list.
For example, we have a bar called "PHP", board_id is 1, the child table ID is also 1, then this record is:
board_id | Board_name | table_id | Created
1 | PHP | 1 | 2007-01-19 00:30:12
Accordingly, if I need to extract all the topics in the "PHP" bar, then you have to follow the table saved by the table_id to combine a stored theme tables name, such as our topic table prefix is "topic_", then the combination of "PHP" the corresponding topic table should be: "Topic_1" , then we execute: