Mysql database/table sharding _ MySQL

Mysql database/table sharding bitsCN.com

Mysql database/table sharding

Table Sharding is a good way to distribute database pressure.

Table sharding refers to dividing a table structure into multiple tables, which can be stored in the same database or in different databases.

Of course, you must first know under what circumstances to split tables. I personally think that table Sharding is required when the number of records in a single table reaches the level of millions to tens of millions.

1. table sharding classification

1> vertical table sharding

Divides the content that can be stored in the same table into multiple tables. (Originally, it refers to the requirement of the third paradigm of relational databases, which should be in the same table .)

Table sharding reason: Data is separated based on the data activity. (the processing method varies depending on the active data)

Case:

For a blog system, such as the title, author, category, and creation time of an article, the change frequency is slow, the number of queries is large, and it is best to have good real-time data, we call it cold data. 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. cold data can be better queried using MyIsam. Active data can be used in Innodb for better update speed.

Second, configure more slave databases for cold data, because more queries are performed during operations to speed up the query. For hot data, more master databases can be used for horizontal table sharding.

In fact, for some special active data, you can also consider using memcache, redis

Wait until a certain amount of data is accumulated before updating the database. Or a nosql database such as mongodb. here is just an example. let's not talk about this first.

2> horizontal table sharding

It can be seen that the large table structure is cut horizontally into different tables with the same structure, such as the user information table, user_1, user_2, etc. The table structure is the same, but tables are divided according to certain rules, such as MoD division based on the user ID.

Table sharding reason: based on the size of the data, the capacity of a single table is not too large, so as to ensure the query and other processing capabilities of a single table.

Case: The example above is the blog system. When the number of blogs reaches a high level, horizontal splitting should be adopted to reduce the pressure on each single table to improve performance. For example, a blog cold data table can be divided into 100 tables. when 1 million users are browsing a single table, 1 million requests will be made, it is possible that 10 thousand data requests are performed for each table (because it is impossible to perform an absolute average, just assume), which reduces the pressure a lot.

BitsCN.com