MySQL data table sub-table strategy

MySQL sub-table method:

Method One, do the database cluster! Master-Slave database bidirectional hot backup (or one-to-many database real-time backup strategy), which can allocate database queries to several servers (can be combined with server load Balancing architecture)

Pros: Good extensibility, no complex operations after multiple tables (PHP code)
Cons: The amount of data in a single table is still unchanged, and the time spent on one operation is still much, and hardware overhead is high.

Method two, according to special circumstances, according to the specific rules of the table: such as user chat table,
Message_00,message_01,message_02..........message_98,message_99. Then according to the user's ID to determine the user's chat information into which table, you can use the hash method to obtain, Can be obtained by means of redundancy, many methods, such as the use of hash method to obtain the table name:

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_10echo get_hash_table (' message ', ' user34523 ');    The result is message_13

　　

Advantages: Avoid a table millions of data, shorten the execution time of a SQL statement, the above method, tells us user18991 this user's message is recorded in message_10 this table, user34523 This user's message is recorded in the Message_13 table, when read, as long as the reading from the respective tables on the line

Disadvantage: When a rule is determined, breaking this rule will be very troublesome, the above example, I use the hash algorithm is CRC32, if I do not want to use this algorithm, instead of MD5, will make the same user's message is stored in a different table, so the data is out of order. Extensibility is poor.

Method Three: Using the merge storage engine to implement the sub-table

If there is a user table users, there are 50W data, now to be split into two tables User1 and user2, each table 25W data,

CREATE TABLE ' test '. ' User ' (' ID ' INT (one) ' Not NULL auto_increment PRIMARY KEY, ' username ' VARCHAR ') CHARACTER SET UT F8 COLLATE utf8_general_ci not null, ' pwd ' VARCHAR ($) CHARACTER SET UTF8 COLLATE utf8_general_ci not null, ' email ' VAR CHAR (CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL) ENGINE = MYISAM DEFAULT Charset=utf8 auto_increment=1; CREATE TABLE ' test '. ' User1 ' (' id ' INT (one) ' Not NULL auto_increment PRIMARY KEY, ' username ' VARCHAR ') CHARACTER SET u Tf8 COLLATE utf8_general_ci not null, ' pwd ' VARCHAR ($) CHARACTER SET UTF8 COLLATE utf8_general_ci not null, ' email ' VA  Rchar (CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL) ENGINE = MYISAM DEFAULT Charset=utf8 auto_increment=1; CREATE TABLE ' test '. ' User2 ' (' ID ' INT (one) ' Not NULL auto_increment PRIMARY KEY, ' username ' VARCHAR CHARACTER SET UTF8 COLLATE utf8_general_ci not null, ' pwd ' VARCHAR ($) CHARACTER SET UTF8 COLLATE utf8_general_ci not null, ' email ' V Archar (CHARACTER S)ET UTF8 COLLATE utf8_general_ci not NULL) ENGINE = MYISAM DEFAULT Charset=utf8 auto_increment=1; INSERT into ' test '. ' User ' (' id ', ' username ', ' pwd ', ' email ') VALUES (NULL, ' user1 ', ' 123 ', ' [email protected] '); INS  ERT into ' test '. ' User ' (' id ', ' username ', ' pwd ', ' email ') VALUES (NULL, ' user2 ', ' 123 ', ' [email protected] '); INSERT Into ' test '. ' User ' (' id ', ' username ', ' pwd ', ' e-mail ') VALUES (NULL, ' user3 ', ' 123 ', ' [email protected] '); INSERT in To ' Test '. ' User ' (' id ', ' username ', ' pwd ', ' email ') VALUES (NULL, ' user4 ', ' 123 ', ' [email protected] '); INSERT into User1 (user1.id,user1.username,user1.pwd,user1.email) SELECT User.id,user.username,user.pwd,user.email From the user where user.id >=2;insert into User2 (user2.id,user2.username,user2.pwd,user2.email) SELECT user.id, User.username,user.pwd,user.email from user where user.id >2; DROP TABLE ' user '; CREATE TABLE ' test '. ' User ' (' ID ' INT (one) not NULL, ' username ' VARCHAR ') CHARACTER SET UTF8 COLLATE utf8_generAl_ci not NULL, ' pwd ' varchar ($) CHARACTER SET UTF8 COLLATE utf8_general_ci not null, ' email ' varchar CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL, INDEX (' id ')) ENGINE = Mrg_myisam union= (user1,user2) insert_method=last CHA Rset=utf8 auto_increment=1; Test: INSERT INTO ' user ' (' IDs ', ' username ', ' pwd ', ' email ') VALUES (5, ' user5 ', ' 123 ', ' [email protected] '); INSERT into ' User ' (' id ', ' username ', ' pwd ', ' email ') VALUES (6, ' user6 ', ' 123 ', ' [email protected] '); INSERT into ' user ' (' id ', ' u  Sername ', ' pwd ', ' email ') VALUES (7, ' user7 ', ' 123 ', ' [email protected] '); INSERT into ' user ' (' username ', ' pwd ', ' email ') VALUES (' User8 ', ' 123 ', ' [email protected] '); So the ID incredibly is 0 this is a bit strange, if not solve, each time inserting new data, to add last_id .... (PHP code to change)

Pros: Extensibility is good, and PHP code almost doesn't have to be changed, but it's something to watch out for:
1. You cannot turn the merge storage engine into another storage engine
2. Execute an INSERT, and the data enters the first or last MyISAM table (depending on the value of the Insert_method option). MySQL ensures that the unique key value remains unique in that MyISAM table, but not across all the tables in the collection.
3. The merge table must be identical to the structure of the table.

Cons: This method is less effective than the second one

Suggestion: Concrete Situation concrete analysis, method One, method two, method three comprehensive use.

MySQL data table sub-table strategy (GO)