MySQL Sub-table technology

In general, when the data of our database exceeds the 100w record, we should consider the table or partition, this time I would like to elaborate on some of the methods of the table.

The methods I know at the moment are MyISAM, innodb How to do the tables and keep the transactions and foreign keys, I don't quite know.

First of all, we need to figure out how many tables, the premise of course is to meet the application.

Here I used a relatively simple table method, that is, according to the mantissa of the self-increment ID points, that is, 0-9 total 10 tables, the value is also very good to do, that is, 10 to take the mold.

In addition, can be based on the MD5 value of a field to take a few of them to the table, so that can be divided into a lot of tables.

I set up 10 tables altogether:

The last table: A, a few notes: Insert_method=last, if it is insert_method=0, will be error, the execution is unsuccessful.

Note that the merged table must also have the same structure as the previous table, type, length, including the order of the fields must be consistent here.

Well, when we need to query, we can only operate on the article table, that is to say, this table can only be a select operation,

So what should be done with the insert operation, the first is to get a unique ID, and here you need a table to create the ID specifically, the code is as follows:

Next, we look at the table:

Immediately after, we carry on "adding and deleting to check" the concrete operation.

A. Adding data

Steps:

A-1, first through the table: cre_id, generate an ID value, and then modulo (modulo 10), get, 0,1,2,3,4,5,6,7,8,9 any value.

A-2, assembled data sheet: A_n, of which n=0,1,2,3,4,5,6,7,8,9.

A-3, perform a regular insert operation. INSERT into A_n (...) Values (...);

MySQL Sub-table technology