The implementation scheme of MySQL database level-table

According to experience, MySQL table data generally reach millions, query efficiency will be very low, easy to cause table lock, or even accumulate a lot of connections, directly hang off; the level of the table can be very much less than these pressures.

1. Time-table

This type of table has certain limitations, when the data has a strong effect, such as micro-bo send records, micro-mail message records, and so on, this data is very few users will query a few months ago data, such as can be divided into monthly table.
2. Breakdown by Range

In general, there are strict requirements for self-increasing ID, such as according to the user_id level of the table:
Table_1 user_id from 1~100w
Table_2 user_id from 101~200w
Table_3 user_id from 201~300w
...
3.hash Sub-table

The table name of the data store table is computed by a certain hash algorithm through an ID or name of the original target, and then the corresponding table is accessed.
10 tables as follows:

code is as follows	copy code
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);   & nbsp       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

In addition, introduce me now is to adopt a simple modulus table:

The code is as follows	Copy Code
/** * @param string $table _name table name * @param int $user _id User ID * Total @param int $total table * @link http://www.phpddt.com */ function hash_table ($table _name, $user _id, $total) { Return $table _name. '_' . (($user _id% $total) + 1); } Echo hash_table ("artice", 1234, 5); Artice_5 Echo hash_table ("Artice", 3243, 5); Artice_4

4. Use the merge storage engine to divide the table

Sensory Merge Storage engine is similar to the feeling of Union in SQL, but query efficiency is not high.
For example, the Old_user table with a 1000w record is divided into the following tables:
(1) Create New_user table using the merge storage engine

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 ' New_user ' ( -> ' 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 New_user; +----+--------+-----+ | ID | name | sex | +----+--------+-----+ | 1 |   Zhang Ying | 0 | | 1 |   Tank | 1 | +----+--------+-----+ 2 rows in Set (0.00 sec) mysql> INSERT into ' new_user ' (' 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)

(2) I old_user the data to the table:

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

The biggest advantage of this scheme is that the business code is almost without moving