MySQL sharding Scalable Design

I. BACKGROUND

We know that when the amount of data in the database becomes larger and bigger, the pressure will become larger, whether it is read or written. Using MySQL replication multiple master slave scheme, load balancing is done in the upper layer, although the pressure can be relieved to some extent. But when the data in a table becomes very large, the pressure is still very large. Imagine, if the data in a table reached tens of millions of millions of levels, whether the index, optimize caching, etc., will face tremendous performance pressure.

Two. Define

Data sharding, also known as data segmentation, or partitioning. is to reduce the pressure on a single machine by spreading the data from the same database to multiple databases or multiple machines by some means.

Three. Classification

According to the segmentation rules, data partitioning can be divided into two categories:

(1) Vertical partitioning: The table as a unit, the different tables dispersed to different databases or hosts. Features are simple rules, easy to implement, suitable for the business between the low coupling system.

(2) Horizontal partitioning: The data in the same table is split on a different database or host by a certain condition in a behavioral unit. The feature is relatively complex and suitable for a single table of huge systems.

In practice, vertical partitions and horizontal partitions are sometimes used in combination.

Four. Schematic diagram

Below is a few graphs to give the visual effects of the above three kinds of zoning methods.

(1) Vertical partition

(2) Horizontal zoning

(3) Joint zoning

Five. Matters needing attention

The partitioning that we refer to below is mainly a horizontal partition.

(1) Before implementing partitions, we can see if the installed version of MySQL supports partitioning:

Mysql> Show variables like "%partition%";

If supported, it will display:

+-------------------+-------+

| variable_name | Value |

+-------------------+----- --+

| have_partitioning | YES |

+------------------ -+-------+

(2) partitions are applicable to all data and indexes of a table, you cannot partition only the data, not the index, and vice versa, and you cannot partition only part of a table.

(3) partition type:

RANGE partition: Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval.

List partition: Similar to a range partition, the difference is that the list partition is selected based on a column value matching a value in a discrete-value collection.

Hash partition: A partition that is selected based on the return value of a user-defined expression, calculated using the column values of the rows that will be inserted into the table.

Key partitions: Similar to a hash partition, except that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

Regardless of the type of partition that is used, partitions are always numbered automatically when they are created and recorded from 0. When a new row is inserted into a partitioned table, the partition number is used to identify the correct partition.

(4) MySQL provides a number of ways to modify partitioned tables. It is possible to add, delete, redefine, merge, or split existing partitions. All of these actions can be implemented by using the partition extensions of the ALTER TABLE command.

(5) You can partition a table that already exists, using the ALTER TABLE command directly.