Mysql table sharding scenario analysis and simple table sharding operations

Table Partitioning can be performed in two ways::

: Place data rows in two independent tables based on the values of one or more columns of data.
Horizontal segmentation usually uses a large table in the following situations. After segmentation, the data to be read and the number of pages of the index can be reduced during query, and the number of layers of the index can be reduced, improves the query speed.
Data in a table is inherently independent. For example, data in different regions or periods is recorded in a table. In particular, some data is often used, while others are not.
Data needs to be stored on multiple media.
Horizontal segmentation increases the complexity of applications. It usually requires multiple table names during query, and all data needs to be queried.

: Put the primary code and some columns in one table, and then put the primary code and other columns in another table.
If some columns in a table are commonly used, but other columns are not commonly used, vertical segmentation can be used. In addition, vertical segmentation can reduce data rows and store more data on one data page, the disadvantage is that redundant columns need to be managed and all data needs to be queried.

The blog's page views, replies, and other similar statistical information, or other frequently-changing data, we call it active data.
Therefore, when designing the database structure, we should consider Table sharding. First, we should deal with vertical table sharding.
After Vertical table sharding:
First, the storage engine is used differently, and the cold data is used. Second, more slave database configuration is performed on the cold data, because more operations are performed on the query, which speeds up the query. For hot data, more Master databases can be used for horizontal table sharding.
In fact, you can also consider using some special active data.

For example, a blog cold data table can be divided

Mysqlhttp: // blog.csdn.net/heirenheiren/article/details/7896546

 show engines  \G;

Create a member table

     auto_increment  (    InnoDB  CHARSETutf8 AUTO_INCREMENT ; 

Step 2: Create a stored procedure and insert millions of data

         i  i  (i    member(name,sex) (concat(,i),i i  i 

      (    MyISAM   CHARSETutf8 AUTO_INCREMENT     tb_member2  tb_member1;

Step 4

    (    MERGE (tb_member1,tb_member2) INSERT_METHODLAST AUTO_INCREMENT ; 

 show   tb_member \G;

Step 5

  member  member.id  member  member.id ;

Step 6

Step 7: 

2. 4. REPLACE5. AUTO_INCREMENT
Create
FIRST