Summary of several methods of MySQL table

Sub-table

1, do MySQL cluster, for example: Use MySQL cluster, MySQL proxy,mysql Replication,drdb and so on
Some people will ask MySQL cluster, what is the relationship between the root table? Although it is not in the actual sense of the table, but it is set to the role of the table, what is the meaning of the cluster? Reduce the burden for a database, the white line is to reduce the number of SQL queued SQL, for example: There are 10 SQL requests, if placed in a database server queue, he has to wait a long time, if the 10 SQL requests to the 5 database server queue, There are only 2 queues in a database server, so is the wait time greatly shortened? This is already obvious. So I've listed it within the scope of the list, and I've done some MySQL clusters:
installation, configuration, and read-write separation of Linux MySQL proxy
MySQL replication interoperability from installation and configuration, and data synchronization
Advantages: Good scalability, no complex operation after multiple tables (PHP code)
Disadvantage: The amount of data in a single table has not changed, the time spent on one operation is still so much, the hardware cost is large.
2, anticipate the occurrence of large amount of data and visit the table frequently, divide it into several tables
This estimate big difference, the forum publishes the post the table, the time long this table must be very big, hundreds of thousands of, millions of is possible. Chat room Information table, dozens of people together for a night, a long time, this table data must be very large. There are a lot of things like this. So this can be predicted by the large data scale, we will be in advance of the n table, this n is how much, according to the actual situation. Take the chat information table for example:
I built 100 of these in advance, message_00,message_01,message_02..........message_98,message_99. And then based on the user ID to determine the user's chat information into which table, You can use a hash of the way to obtain, you can use the way to obtain the remainder, a lot of ways, everyone think of the bar. The following hash method is used to get the table name:
View copy print?

code is as follows	copy code
<?php   Function get_hash_table ($table, $userid) {    $STR = CRC32 ($userid);   if ($str & lt;0) {    $hash = "0". substr (ABS ($STR), 0, 1);   }else{    $hash = substr ($str, 0, 2);   }     return $table. _ ". $hash;  }    Echo get_hash_table (' message ', ' user18991 ');    // The result is message_10  Echo get_hash_table (' message ', ' user34523 ');   //result is message_13  ?

Explain, the above method, tell us user18991 This user's message is recorded in message_10 this table, user34523 this user's messages are recorded in message_13 this table, read the time, as long as from their own table read on the line.
Advantages: Avoid a table of millions of data, shorten the execution time of a SQL
Disadvantages: When a rule is determined, breaking this rule will be troublesome, in the example above I used the hash algorithm is CRC32, if I do not want to use this algorithm now, instead of using the MD5, the same user's message will be stored in a different table, so that the data is messed up. Scalability is poor.

3, using the merge storage engine to implement the sub-table
I think this method is more suitable, those who did not consider beforehand, but has been, the data query slow situation. This time if you want to separate the large data scale is more painful, the most painful thing is to change the code, because the program inside the SQL statement has been written, now a table to be divided into dozens of tables, or even hundreds of tables, so that the SQL statement is not to rewrite it? For example, I like lifting a child.

Mysql>show engines, you will find that Mrg_myisam is actually the merge.

The code is as follows

Copy Code

mysql> CREATE TABLE IF not EXISTS ' user1 ' ( -> ' id ' int (one) not NULL auto_increment, -> ' name ' varchar DEFAULT NULL, -> ' sex ' int (1) not NULL DEFAULT ' 0 ', -> PRIMARY KEY (' id ') ->) Engine=myisam DEFAULT Charset=utf8 auto_increment=1; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE IF not EXISTS ' User2 ' ( -> ' id ' int (one) not NULL auto_increment, -> ' name ' varchar DEFAULT NULL, -> ' sex ' int (1) not NULL DEFAULT ' 0 ', -> PRIMARY KEY (' id ') ->) Engine=myisam DEFAULT Charset=utf8 auto_increment=1; Query OK, 0 rows affected (0.01 sec) Mysql> INSERT into ' user1 ' (' name ', ' Sex ') VALUES (' Zhang Ying ', 0); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' user2 ' (' name ', ' Sex ') VALUES (' Tank ', 1); Query OK, 1 row Affected (0.00 sec) mysql> CREATE TABLE IF not EXISTS ' AllUser ' ( -> ' id ' int (one) not NULL auto_increment, -> ' name ' varchar DEFAULT NULL, -> ' sex ' int (1) not NULL DEFAULT ' 0 ', -> INDEX (ID) ->) Type=merge union= (user1,user2) insert_method=last auto_increment=1; Query OK, 0 rows affected, 1 Warning (0.00 sec) Mysql> select Id,name,sex from AllUser; +----+--------+-----+ | ID | name | sex | +----+--------+-----+ | 1 |   Zhang Ying | 0 | | 1 |   Tank | 1 | +----+--------+-----+ 2 rows in Set (0.00 sec) mysql> INSERT into ' alluser ' (' name ', ' Sex ') VALUES (' Tank2 ', 0); Query OK, 1 row Affected (0.00 sec) Mysql> Select Id,name,sex from User2 ->; +----+-------+-----+ | ID | name | sex | +----+-------+-----+ | 1 |   Tank | 1 | | 2 |   Tank2 | 0 | +----+-------+-----+

2 rows in Set (0.00 sec) from the above operation, I do not know you have found something? If I have a user table, there are 50W data, now I want to split into two sheets User1 and user2, each table 25W data,

The code is as follows	Copy Code
INSERT into User1 (user1.id,user1.name,user1.sex) SELECT (user.id,user.name,user.sex) from user where User.ID <= 250000 INSERT into User2 (user2.id,user2.name,user2.sex) SELECT (user.id,user.name,user.sex) from user where user.id > 250000

So I succeeded in putting a user table, divided into two tables, this time there is a problem, the code of the SQL statement, the previous is a table, now become two tables, code changes very large, so that the programmer brought a lot of work, there is no good way to solve this? The way is to back up the previous user table, and then delete, the above operation I created a alluser table, only the table name of this AllUser table changed to user on the line. However, not all MySQL operations are available.
A, if you use ALTER TABLE to change the merge table to another table type, the mapping to the underlying table is lost. Instead, rows from the underlying MyISAM table are copied into the replaced table, which is then assigned the new type.
b, the Internet to see some say replace does not work, I tried to work. Dizzy One First

code is as follows	copy code
mysql> UPDATE alluser SET sex=replace (sex, 0, 1) where id=2;  Query OK, 1 row Affected (0.00 sec)   Rows match ed:1  changed:1  warnings:0    mysql> select * from alluser;  +----+--------+ -----+  | id | name   | sex |  +----+--------+-----+  |  1 | Zhang Ying |   0 |  |  1 | tank   |   1 |  |  2 | tank2  |   1 |  +----+--------+-----+

3 Rows in Set (0.00 sec) C, a merge table cannot maintain a unique constraint on the entire table. When you perform an insert, the data enters the first or last MyISAM table (depending on the value of the Insert_method option). MySQL ensures that unique key values remain unique in that MyISAM table, but not across all tables in the collection.
D, when you create a merge table, there is no check to make sure that the underlying table exists and that there are identical bodies. When the merge table is used, MySQL checks that the record length of each mapped table is equal, but this is not very reliable. If you never create a merge table from a similar MyISAM table, you are likely to bump into strange questions.
Good sleepy, C and D on the Internet to see, there is no test, we try it.
Advantages: Scalability is good, and the program code changes are not very large
Disadvantage: This method is less effective than the second

Third, to sum up

Of the three methods mentioned above, I have actually done two kinds, the first and the second. The third has not been done, so say a little bit. Ha ha. There is a degree of doing everything, more than a degree has become very poor, can not blindly do the database server cluster, hardware is to spend money to buy, also do not blindly, divided into 1000 tables, the storage of MySQL in the final analysis of the file with the situation of the hard disk, a table corresponding to three files, The 1000-point table corresponds to 3,000 files, which can be slow to retrieve. My advice is.
Method 1 and Method 2 are combined in a way that is divided into tables
Method 1 and Method 3 are combined in a way that is divided into tables
My two suggestions are suitable for different situations, depending on your personal circumstances, I think there will be a lot of people to choose Method 1 and Method 3 combined way