MySQL database table method Summary (1/2)

First, let's talk about why you want to divide the table.

When a single piece of data reaches millions of, you'll have more time to query, and if there's a joint query, I think I might have died 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 in the queue;

3, execute SQL;

4, returns the execution result.

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 the MySQL cluster, what is the relationship with the 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:

Code

code is as follows	copy code
<?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 ');//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 example, I like lifting a child.

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

Code

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)

Home 1 2 last page