Mysql's 3 kinds of table scheme _mysql

First, why do you want to divide the table:
When a single piece of data reaches millions of, you'll have more time to query, and if you have a joint query, you might die there. The purpose of the table is to reduce the burden of the database and shorten the query time.

According to your personal experience, MySQL executes a SQL process as follows:
1, received the SQL;
2, put the SQL into the queue;
3, execute SQL;
4. Returns the result of execution.
Where do you spend most of your time in this process? First, the time to wait in line, and the second, the execution time of SQL. In fact, these two are one thing, waiting at the same time, there must be SQL in execution. So we want to shorten the execution time of SQL.

One of the mechanisms in MySQL is table locking and row locking. Why this mechanism is to ensure the integrity of the data, for example, if you have two SQL to modify the same data in the same table, what do you do at this time, whether two SQL can modify this data at the same time? It is clear that MySQL handles this situation with a table lock (MyISAM storage engine) and a row lock (InnoDB storage engine). Table locking means that you cannot operate on this table until I have finished with the table. Row locks are the same, and other SQL has to wait until I'm done with this data before I can operate on this piece of data. If there is too much data, it takes too long to execute at one time and the longer it waits, which is why we want to divide the table.

Second, the 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:

Copy Code code as follows:

<?php
function get_hash_table ($table, $userid) {
$str = CRC32 ($userid);
if ($str <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 '); The 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 instance, I like to cite examples

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

Copy Code code as follows:

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)  

mysql> CREATE TABLE IF not EXISTS ' user1 ' ( ->   ' id ' int (one) not NULL auto_increment,  bsp;  ' name ' varchar ($) Default null, ->   ' sex ' int (1) not NULL DEFAULT ' 0 ', -> &n Bsp 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) &n Bsp ->) 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,

Copy Code code as follows:

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

Copy Code code as follows:

mysql> UPDATE alluser SET sex=replace (sex, 0, 1) where id=2;
Query OK, 1 row Affected (0.00 sec)
Rows matched: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)

mysql> UPDATE alluser SET sex=replace (sex, 0, 1) where id=2; Query OK, 1 row Affected (0.00 sec) Rows matched: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.

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, summarize

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