MySQL Common level sub-table technology scheme

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

1. By Time table

This kind of sub-table has certain limitations, when the data has a strong effectiveness, such as micro-Bo send records, message records, and so on, this data very few users will query a few months ago data, such as can be divided into monthly table.
2. Divide the table by the interval range


In general, there are strict self-increment ID requirements, 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 the ID or name of the original target, and then the corresponding table is accessed.
10 tables as follows:


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


In addition, the introduction I now is to use a simple module table:

/**
* @param string $table _name table name
* @param int $user _id User ID
* @param int $total total number of tables
*/
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. Using the merge Storage Engine sub-table

The merge storage engine feels like union in SQL, but the query is inefficient.
For example, the Old_user table with the 1000w record is as follows:
(1) Create a New_user table using the merge storage engine


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 be divided into tables:

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 best thing about this scenario is that you almost don't have to move your business code.


Original link: http://www.4u4v.net/mysql-common-sub-table-level-technical-solutions.html

MySQL Common level sub-table technology scheme