Technology Sharing

Interview - MySQL-3 Cluster Database and Table Sharing

2024-07-12

한어Русский язык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

1.2 Master-slave synchronization process:

insert image description here

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.
insert image description here

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

insert image description here

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.
insert image description here

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;
insert image description here

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:

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;


Summarize

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