MySQL Slicing method

The first part:

Relational databases are slowly passing by over time, with a large number of operations per second, many open connections, large amounts of data, and very high write ratios. To avoid this, many large sites and SaaS applications are beginning to use sharding technology and their relational data.

How to apply fragmentation? The four points listed below are simple:

1, Analysis table schema to how to set the fragmentation

2. Open multiple MySQL instances

3, according to Shard configuration, import Export data

4, update the program code to support shard configuration

Analyze data:

In order to get a shard configuration, you need to do the following three steps:

1. Lists the table name and the size of the table. Large tables need to be fragmented most of the time, because many SQL commands are often executed on the table.

2, the list of foreign keys (if any) foreign key to help us understand the relationship between the table, such tables need to be very sensible division, otherwise your table will lose consistency

3, analysis of the SQL statements involved in some SQL statements in the Shard environment is difficult to implement. So you need to sort out an SQL statement to determine which tables don't need to be shard. We can also tell which tables are accessed at a high frequency and which are not.

Table Partitioning Rules:

When implementing Shard, it is necessary to understand that not all tables will be fragmented. Because sharding restricts the writing of SQL (not allowing joins between tables, uniqueness of tables, and self adding fields), you must force the application code to be modified. In general, some tables are Shard and others are copied to other Shard.

Determining which tables need to be fragmented;

The algorithm for Shard the table is as simple as described below:

1, looking for the largest table, many table structures have a few large tables, the rest is not.

2, analysis of SQL statements:

is a join query used between the tables?

A, yes, make the smaller tables as a global table

b, if not, you should also analyze the access frequency analysis according to the SQL statement

I, if these tables are not accessed frequently, let them become global tables

II, find frequently accessed tables, especially those that are heavily written for Shard, and then skip to step 2nd to make sure they can really be shard (no join operation)

Once you have identified which tables will be Shard, the next step is to select the Sharding keys, most of which use the table's primary key as Shard key. If a foreign key relationship exists between some tables, the use of a foreign key as a shard key can also be considered.

As with database partitions, sharding has many algorithms: Hash,list,range, and so on. In general, a list or hast algorithm is used to respond to multiple selection conditions. Save different user information through different db. Hashes tend to be more evenly distributed to the data.

What's the next step?

If you are a new environment, then you can skip this step.

1. Clone the data on all shard, copy the physical file or mysqldump.

2, for each shard (shard on each table)

A, delete all the indexes

b, delete all the extra data (script done)

This operation will produce a lot of fragments, you can consider to create a temporary table, only insert the relevant records, and finally delete the original table, and then renamed.