A read-write separation and cross-database distributed query solution, system, electronic device and storage medium

Abstract

The present invention discloses a read-write separation and cross-database distributed query solution, system, electronic device and storage medium, including copying the data of MySQL master database to MySQL slave database; copying the data of MySQL slave database to MaterializeMySQL in Clickhouse aggregation database; Materialized View forms materialized view based on the data in MaterializeMySQL. Clickhouse of the present invention supports materialized view, and data can be updated in real time with source table.

Description

Technical Field

[0001] The present invention relates to the field of database query technology, and in particular to a read-write separation and cross-database distributed query solution, system, electronic device and storage medium. Background Art

[0002] Each microservice (front-end, back-end, data storage layer) is decoupled, and microservices can be developed and deployed independently. In other words, in principle, each microservice has its own database, which is isolated from the databases of other microservices. Based on this principle, SCP (supply chain industry platform) plans microservices according to business fields. MySql big data high-performance general solution: vertical split / sharding by business field, planning the database of each microservice; horizontal split / sharding of the microservice's own database, that is, sub-library and sub-table at the microservice level; read-write separation, SCP needs to do read-write separation and sub-library in the business field, and will not do sub-library and sub-table of the microservice's own database for the time being; realize data aggregation through the Java layer. Paging is directly performed based on the business data of the microservice.

[0003] For common form development, whether it is hard-coding based on an open source framework or quick query based on a low-code platform, there will be inefficiencies. Hard coding based on an open source framework requires a lot of manpower and has high technical requirements. Quick construction based on a low-code platform will be limited by the limitations of the platform's technology stack, and there are certain limitations in scalability and customization. The compatibility in the banking and financial system is poor, so it is fundamentally impossible to achieve the purpose of quick form construction. Summary of the invention

[0004] In view of the shortcomings existing in the above problems, the present invention provides a read-write separation and cross-library distributed query solution, system, electronic device and storage medium.

[0005] To achieve the above objectives, the present invention provides a read-write separation and cross-database distributed query solution, including:

[0006] Copy the data of the MySQL master database to the MySQL slave database;

[0007] Copy the data from the MySQL database to MaterializeMySQL in the Clickhouse aggregation database;

[0008] MaterializedView forms a materialized view based on the data in MaterializeMySQL.

[0009] Preferably, the MySQL data and the Clickhouse aggregation library are replicated in a 1:1 master-slave manner.

[0010] Preferably, the MaterializeMySQL engine materializes MySQL data in real time through binlog logs.

[0011] The present application also provides a read-write separation and cross-database distributed query solution system, including:

[0012] The first replication module is used to replicate the data of the MySQL master database to the MySQL slave database;

[0013] The second replication module is used to copy the data from the MySQL slave library to MaterializeMySQL in the Clickhouse aggregation library;

[0014] A formation module for MaterializedView to form a materialized view based on the data in the MaterializeMySQL.

[0015] The present invention also provides an electronic device, comprising at least one processing unit and at least one storage unit, wherein the storage unit stores a computer program, and when the program is executed by the processing unit, the processing unit executes the above method.

[0016] The present invention also provides a storage medium storing a computer program executable by an electronic device, and when the program runs on the electronic device, the electronic device executes the above method.

[0017] Compared with the prior art, the present invention has the following beneficial effects:

[0018] The Clickhouse of the present invention supports materialized views, and data can be updated in real time with the source table. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 It is a flow chart of the read-write separation and cross-database distributed query solution of the present invention. DETAILED DESCRIPTION

[0020] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0021] Reference Figure 1 The present invention provides a solution for read-write separation and cross-database distributed query, including:

[0022] Copy the data of the MySQL master database to the MySQL slave database;

[0023] Copy the data from the MySQL database to MaterializeMySQL in the Clickhouse aggregation database;

[0024] Materialized View forms a materialized view based on the data in MaterializeMySQL. When querying, the materialized view is directly queried to improve the overall query performance and efficiency.

[0025] In this embodiment, because the MaterializeMySQL engine currently implements 1:1 master-slave replication between the MySql database and the Clickhouse database, the MySql aggregation database is used as a data transfer (the service is not open to the Java layer). In the future, when Clickhouse can directly aggregate the MySQL databases of each microservice, the MySql aggregation database will be removed.

[0026] Furthermore, the Java layer implements read-write separation based on Apache sharding-jdbc. Zero intrusion on business code. Microservice business data aggregation library is implemented based on MySql multi-source replication (multiple masters and one slave) mechanism. It is a MySql native solution, stable and efficient. Master-slave replication from Mysql to Clickhouse is implemented based on the latest MaterializeMySQL engine of Clickhouse. The solution is simple, Clickhouse query performance is extremely powerful, and sql syntax is also supported. Data associated with multiple tables is pre-processed based on Clickhouse materialized view (wide table) to meet query performance requirements.

[0027] In this embodiment, the business data of a single microservice must be associated with the data dictionary table of the system data microservice, or the basic data table of other microservices, and other information such as the name must be queried based on the code. The business data of a single microservice must be associated with the master data of other microservices, and paging query, sorting, data aggregation processing, etc. must be performed. The business data of a single microservice must be associated with the business data of other microservices, and paging query, sorting, data aggregation processing, etc. must be performed.

[0028] Furthermore, Clickhouse is a powerful OLAP database and a columnar database. The query performance is faster than MySQL and can be used for real-time cross-database queries. Clickhouse 20.8 adds the MaterializeMySQL engine, which can materialize MySQL data in real time through binlog logs, greatly improving the query performance of the data warehouse and the timeliness of data synchronization; the data analysis work originally undertaken in MySQL can be handed over to Clickhouse, which can significantly reduce the load of online MySQL, and thus achieve a perfect integration of OLTP and OLAP business. Clickhouse is very different from Mysql, and the team needs to invest a lot of energy to familiarize themselves with its mechanism, usage, operation and maintenance solutions, etc. Clickhouse supports materialized views, and data can be updated in real time with the source table. When there are cross-database multi-table association queries, the data can be pre-processed through materialized views to improve the final query performance.

[0029] The present application also provides a read-write separation and cross-database distributed query solution system, including:

[0030] The first replication module is used to replicate the data of the MySQL master database to the MySQL slave database;

[0031] The second replication module is used to copy the data from the MySQL slave library to MaterializeMySQL in the Clickhouse aggregation library;

[0032] Formation module, used by MaterializedView to form materialized views based on data in MaterializeMySQL.

[0033] The present invention also provides an electronic device, comprising at least one processing unit and at least one storage unit, wherein the storage unit stores a computer program, and when the program is executed by the processing unit, the processing unit executes the above method.

[0034] The present invention also provides a storage medium storing a computer program executable by an electronic device. When the program runs on the electronic device, the electronic device executes the above method.

[0035] The above are only preferred embodiments of the present invention and are not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A solution for read-write separation and cross-database distributed query, characterized in that: include: Copy the data of the MySQL master database to the MySQL slave database; Copy the data from the MySQL database to MaterializeMySQL in the Clickhouse aggregation database; MaterializedView forms a materialized view based on the data in MaterializeMySQL.

2. The read-write separation and cross-database distributed query solution according to claim 1 is characterized in that: The MySQL data and the Clickhouse aggregation library are replicated 1:1 between the master and slave.

3. The read-write separation and cross-database distributed query solution according to claim 2 is characterized in that: The MaterializeMySQL engine materializes MySQL data in real time through binlog logs.

4. A read-write separation and cross-database distributed query solution system, characterized in that: include: The first replication module is used to replicate the data of the MySQL master database to the MySQL slave database; The second replication module is used to copy the data from the MySQL slave library to MaterializeMySQL in the Clickhouse aggregation library; A formation module for MaterializedView to form a materialized view based on the data in the MaterializeMySQL.

5. An electronic device, characterized in that: The method comprises at least one processing unit and at least one storage unit, wherein the storage unit stores a computer program, and when the program is executed by the processing unit, the processing unit executes the method according to any one of claims 1 to 3.

6. A storage medium, characterized in that: It stores a computer program executable by an electronic device. When the program runs on the electronic device, the electronic device executes the method described in any one of claims 1 to 3.

Images