Analysis of scalable MySQL Sharding Design

Analysis of the scalable design of MySQL Sharding I. Background We know that when the data volume in the database is getting bigger and bigger, the pressure on both reading and writing will increase. The MySQL Replication multi-master and multi-slave scheme is used for load balancing at the upper layer, although the load can be mitigated to a certain extent. However, when the data in a table becomes very huge, the pressure is still very high. Imagine that when the data volume in a table reaches tens of millions or even hundreds of millions of data records, both index creation and Cache Optimization will face huge performance pressure. Www.2cto.com II. Define data sharding, also known as data sharding or partition. A condition is used to distribute data in the same database to multiple databases or machines to reduce the pressure on a single machine. Iii. classification data partitions can be divided into two categories based on sharding rules:
(1) vertical partitioning: in tables, different tables are distributed to different databases or hosts. It features simple rules and convenient implementation, and is suitable for systems with low coupling degrees between businesses. (2) Horizontal partitioning: splits data in the same table into different databases or hosts based on certain conditions. Features are relatively complex and suitable for systems with large single tables. In practice, vertical and horizontal partitions are used in combination. 4. The following figure shows the effects of the preceding three partitioning methods.
(1) Vertical partition www.2cto.com

(2) Horizontal partitioning

(3) Joint partitioning

5. Note the following Partition refers to horizontal partition.
(1) Before partitioning, we can check whether mysql of the installed version supports partitioning:
Mysql> show variables like "% partition % ";
If YES, the system displays: + ----------------- + ------- + | Variable_name | Value | + ----------------- + ------- + | have_partitioning | YES | + ------------------- + ------- + (2) partitions are applicable to all data and indexes in a table. You cannot partition only data but not the index, and vice versa. You cannot partition only a part of the table. (3) partition type:
RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval. LIST partition: similar to partitioning by RANGE, the difference is that LIST partition is selected based on the column value matching a value in a discrete value set. HASH partition: select a partition based on the return value of a user-defined expression. This expression uses the column values of the rows to be inserted into the table for calculation. KEY partitioning: similar to HASH partitioning, the difference is that KEY partitioning only supports 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 always automatically numbered at the time of creation and records from 0. When a new row is inserted into a partition table, these partition numbers are used to identify the correct partitions. (4) MySQL provides many ways to modify partition tables. It is possible to add, delete, redefine, merge, or split existing partitions. All these operations can be achieved through the alter table command partition extension. www.2cto.com (5) can partition existing tables, directly use the alter table command. Vi. References:
1. MySQL performance tuning and Architecture Design 2. pathenon, author of MySQL 5.1 Reference Manual