Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

---

Preface

Do you know MySQL clusters? Do you understand the process of master-slave replication? How do you deal with massive amounts of data? This article focuses on introducing interview questions, and I wish every programmer can land a job!!!

---

1. MySQL Cluster

In the online environment, we usually deploy 1 master and 1 slave, or 1 master and multiple slaves MySQL instance to achieve high availability and read-write separation of MySQL; MySQL master-slave synchronization is performed through binlog logs.

1.1 Building a Mysql cluster:

For the construction of MySQL master-slave cluster, please refer to the following article by the blogger

• Mysql Architecture - Mysql (MS) Master-Slave Synchronization
• Mysql Architecture - Mysql (MM) Master-Slave Synchronization

1.2 Master-slave synchronization process:

2. How does MySQL deal with massive data storage

As a relational data storage system, Mysql will encounter performance bottlenecks when the data volume of a single table exceeds 30 million. Similarly, the number of client connections and concurrency supported by a single instance of MySQL has certain bottlenecks. At this time, the system needs to consider using the technology of sharding libraries and tables to achieve this.

2.1 Vertical Split

2.1.1 Vertical sub-database:

For example, microservices have been vertically split. Each microservice module may be connected to its own Mysql database instance. Its characteristics are based on tables. Different tables are split into different databases according to the business. Data is hierarchically managed, maintained, monitored, and expanded according to the business. Under high concurrency, the disk I0 and data volume connection number are increased.

2.1.2 Vertical table:

Based on the fields, different fields are split into different tables according to the field attributes. This can achieve: separation of hot and cold data; reduce IO transition contention, and the two tables do not affect each other

2.2 Horizontal Split:

2.2.1 Horizontal database:

Split the data of a database into multiple databases. This solves the performance bottleneck problem of large number and high concurrency in a single database and improves the stability and availability of the system.

2.2.2 Horizontal table:

Split the data of a table into multiple tables (can be in the same database). Optimize the performance issues caused by excessive data volume in a single table; avoid IO contention and reduce the chance of locking the table;

2.3 Have you used sharding in your project?

We set up database instances according to different businesses in spring-boot managed by spring-cloud, and performed vertical partitioning. In order to cope with the storage of massive data, our project also used Mycat middleware to partition the database and tables.

Mycat installation and Spring-boot integration:

• Alibaba Cloud Lightweight Server – Docker – Mycat Installation;
• Sub-library and sub-table chapter-2.3 SpringBoot integrates Mycat (1.6);

2.4 Did you encounter any technical challenges when sharding the database and tables?

After the database and table are divided, because the data will be stored in multiple tables of multiple databases, distributed transactions will be encountered; distributed global ID, routing rule settings, and cross-node paging issues;

• Mycat sets many fragmentation rules, such as: modulo, range, enumeration, ER fragmentation, etc. The fragmentation rules can be referred to:Partitioning - 2.2 Mycat - Sharding Rules
• For distributed transactions, Mycat 1.6 and later versions have solved distributed transactions through two-phase commit, and also support the use of snowflake algorithm to generate primary key IDs. For reference:Sub-library and table-2.4 SpringBoot integrates Mycat (1.6) Sub-library and table, read-write separation, distributed transactions
• Mycat provides ER sharding to ensure that data with the same association relationship will be assigned to the same database to avoid cross-node queries as much as possible; for cross-node paging, Mycat will send SQL to multiple Mysql instances and finally summarize the results and return them;

---

Summarize

This article sorts out some interview questions about MySQL cluster and sharding.