Detailed Design and Application of mysql table sharding

Detailed Design and Application of mysql table sharding generally, when the data in our database exceeds records, we should consider Table sharding or partition. This time I will introduce in detail some table sharding methods. Currently, all the methods I know are MYISAM. I do not know much about how INNODB performs table sharding and retains transactions and Foreign keys. First, we need to think about the number of tables to be divided, provided that the application is satisfied. Here I use a simple table sharding method, which is to divide the table based on the ending number of the auto-increment id. That is to say, there are 10 tables in total 0-9, and the value is also very good, it is the modulo of 10. In addition, you can use the md5 value of a field to retrieve several digits for table sharding. In this way, there will be a lot of tables that can be divided. Let's CREATE a TABLE first. The Code is as follows: create table 'test '. 'Article _ 0' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 1' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLL ATE utf8_general_ci create table 'test '. 'Article _ 2' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 3' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf 8 COLLATE utf8_general_ci create table 'test '. 'Article _ 4' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 5' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = MYISAM CHARACTER S ET utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 6' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 7' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = MYISAM CHARA Cter set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 8' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = myisam character set utf8 COLLATE utf8_general_ci create table 'test '. 'Article _ 9' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id') ENGINE = MYISAM Character set utf8 COLLATE utf8_general_ci 10 tables have been created. Note that the id here cannot be SET to auto-increment, and all tables must have the same structure, including structure, type, the length and field order must be consistent. How can I obtain this id? I will describe it in detail later. Now, we need a merged TABLE for query. The code for creating a merged TABLE is as follows: create table 'test '. 'Article' ('id' BIGINT (20) not null, 'subobject' VARCHAR (200) not null, 'content' text not null, primary key ('id ')) ENGINE = MRG_MyISAM default charset = utf8 INSERT_METHOD = 0 UNION = ('Article _ 0', 'Article _ 1', 'Article _ 2', 'Article _ 3 ', 'Article _ 4', 'Article _ 5', 'Article _ 6', 'Article _ 7', 'Article _ 8', 'Article _ 9'); note, the merged table must also have the same structure, type, length, and field sequence as the preceding table. Here, the INSERT _ METHOD = 0 indicates that the insert operation on the table is not allowed. Well, when you need to query the table, we can only operate on the article table, that is to say, this table can only be used for select operations. So how should we perform insert or insert operations? First, we need to obtain a unique id. Here we need a table to create an id, the Code is as follows: create table 'test '. 'create _ id' ('id' BIGINT (20) not null AUTO_INCREMENT primary key) ENGINE = MYISAM that is to say, when we need to insert data, this table must generate id values. The method of my php code is as follows: function get_AI_ID () {$ SQL = "insert into create_id (id) values ('')"; $ this-> db-> query ($ SQL); return $ this-> db-> insertID ();} Now let's assume that I We have to insert a piece of data. What should we do? Continue with the Code. function new_Article () {$ id = $ this-> get_AI_ID (); $ table_name = $ this-> get_Table_Name ($ id ); $ SQL = "insert into {$ table_name} (id, subject, content) values ('{$ id}', 'test title', 'test content ')"; $ this-> db-> query ($ SQL);}/*** is used to obtain the table name based on the id */function get_Table_Name ($ id) {return 'Article _'. intval ($ id) % 10;} is actually very simple, right, that is, get the id first, and then obtain the table to which it should be inserted according to the id, then it is very simple. I don't think we need to talk about the update operation. It's nothing more than having an id, getting the table name, and then performing the update operation.