Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to carry out MySQL Sharding extensible design, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

one。 Background

We know that as the amount of data in the database becomes larger and larger, both reading and writing will become more and more stressful. The MySQL Replication multi-master multi-slave scheme is adopted to do load balancing in the upper layer, although it can relieve the pressure to some extent. But when the data in a table becomes very large, the pressure is still very great. Just imagine, if the amount of data in a table reaches tens of millions or even hundreds of millions of levels, whether it is indexing, optimizing cache and so on, it will face tremendous performance pressure.

two。 Define

Data sharding, also known as data segmentation, or partitioning. It refers to dispersing the data in the same database to multiple databases or multiple machines through certain conditions, so as to reduce the pressure on a single machine.

three。 classification

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

(1) Vertical partitioning: spread different tables to different databases or hosts in table units. It is characterized by simple rules and convenient implementation, and is suitable for systems with low coupling between businesses.

(2) horizontal partitioning: split the data in the same table to different databases or hosts according to certain conditions in behavioral units. It is characterized by relative complexity and is suitable for large systems with a single table.

In practice, vertical and horizontal partitions are sometimes used together.

four。 Schematic diagram

Here are a few pictures to show the intuitive effects of the above three partitioning methods.

(1) Vertical zoning

(2) horizontal zoning

(3) Joint zoning

five。 Matters needing attention

The zoning we are talking about below mainly refers to the horizontal zoning.

(1) before implementing partitioning, we can check whether the installed version of mysql supports partitioning:

Mysql > show variables like "partition%"

If it is supported, it displays:

+-+ +

| | Variable_name | Value |

+-+ +

| | have_partitioning | YES |

+-+ +

(2) partitioning applies to all data and indexes of a table; you cannot partition only the data without partitioning the index, and vice versa, and you cannot partition only part of the table.

(3) Partition type:

RANGE partition: assigns multiple rows to a partition based on column values that belong to a given contiguous interval.

LIST partitions: similar to partitioning by RANGE, except that LIST partitions are selected based on column values matching a value in a set of discrete values.

HASH partition: a partition that is selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table.

KEY partitions: similar to partitioning by HASH, except that KEY partitions only support computing one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.

No matter what type of partition is used, the partition is automatically numbered sequentially when it is created and is recorded starting at 0. When a new row is inserted into a partition table, these partition numbers are used to identify the correct partition.

(4) MySQL provides many ways to modify partitioned tables. It is possible to add, delete, redefine, merge, or split existing partitions. All of these operations can be achieved by using the partition extension of the ALTER TABLE command.

(5) you can partition existing tables and use the alter table command directly.

After reading the above, do you have any further understanding of how to do MySQL Sharding extensible design? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.