A method for dynamically switching data source mechanisms

Abstract

The present invention provides a method for dynamic switching of a data source mechanism, including: before executing business SQL, selecting a JDBC data source in the application corresponding to a specified database according to business data; there are two ways to implement the dynamic switching mechanism: the first is to create multiple data sources in the same project at the coding level, using the ifelse method to directly route in the code according to the conditions, and Spring has an abstract class support for dynamic switching of data sources; the second is to extend ShardingSphere‑JDBC, modify the SQL routing mechanism, and add a dynamic switching data source mechanism. Using the database sharding + partition table method for data sharding is more in line with the actual project development of the Southern Power Grid. The database sharding adopts a provincial method. During an interface request, SQL generally only needs to be routed to the specified provincial database instance. The sharding table is implemented using a database partition table, which is transparent to business development.

Description

Technical Field

[0001] The present invention relates to the field of data supervision, and in particular to a method for dynamically switching a data source mechanism. Background Art

[0002] After the Southern Power Grid was established as a central platform, the central platform needs to store data from various business domains, covering a wide range and a large amount of data. The performance, scalability, and reliability of data storage are very high. In the early stage, the functions and performance of the current mainstream sharding solutions were compared. Through the scenario analysis of each business domain, it was found that the use of sharding + partitioned tables for data sharding is more in line with the actual project development of the Southern Power Grid. The sharding is carried out on a provincial basis. During an interface request, SQL generally only needs to be routed to the database instance of the specified province. The sharding is implemented using database partition tables, which is transparent to business development. Summary of the Invention

[0003] In view of the above problems, the present invention is proposed to provide a method for dynamically switching a data source mechanism to overcome the above problems or at least partially solve the above problems.

[0004] According to one aspect of the present invention, a method for dynamically switching a data source mechanism is provided, comprising:

[0005] Before executing business SQL, select the JDBC data source corresponding to the specified database in the application based on the business data;

[0006] There are two ways to implement dynamic switching mechanisms:

[0007] The first is to create multiple data sources in the same project at the coding level, and use the ifelse method to directly route in the code according to the conditions. Spring has an abstract class that supports dynamic switching of data sources. The second is to extend ShardingSphere-JDBC, modify the SQL routing mechanism, and add a dynamic data source switching mechanism.

[0008] Optionally, the dynamic data source switching mechanism specifically includes:

[0009] The core principle of ShardingSphere-JDBC sharding middleware is to route SQL operations to the specified database or data table according to the configured data sharding strategy, thereby achieving horizontal expansion of data storage; ShardingSphere-JDBC provides 5 sharding strategies:

[0010] Standard sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEENAND in SQL statements, and only supports single sharding key;

[0011] Composite sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEENAND in SQL statements, and supports multiple sharding keys;

[0012] Hint sharding strategy: a sharding strategy that specifies sharding values through hints rather than extracting them from SQL;

[0013] No sharding strategy: No data sharding operation is performed.

[0014] Optionally, the extension of ShardingSphere-JDBC, modification of SQL routing mechanism, and addition of dynamic data source switching mechanism specifically include:

[0015] Dynamically switch data source mechanism by extending SQL router;

[0016] Before executing ShardingSphere routing code, add dynamic switching data source identification judgment. If the identification is set, the sharding value in the thread context and the configured sharding rules are taken out to calculate the data source to be used, and execute the SQL submitted by the upper code on the data source.

[0017] Optionally, a specific implementation method of the dynamic data source switching mechanism includes:

[0018] Step 1: Set the sub-library identification value before executing the interface business code;

[0019] Step 2: After the SQL router is expanded, it first checks whether the database sharding flag is set. If it is set, it proceeds to step 3.

[0020] Step 3: Calculate and select the data source based on the configured sharding identifier value and the sharding rules in the configuration file, and then proceed to step 5. The calculation and selection logic for the data source supports Groovy expressions.

[0021] Step 4. Execute the SQL routing code logic before ShardingSphere expansion;

[0022] Step 5. Execute the upper-level business SQL in the selected data source. The code logic is still implemented by calling the ShardingSphere execution engine.

[0023] The present invention provides a method for dynamic switching of data source mechanisms, including: before executing business SQL, selecting a JDBC data source in the application corresponding to a specified database according to business data; there are two ways to implement the dynamic switching mechanism: the first is to create multiple data sources in the same project at the coding level, using the ifelse method to directly route in the code according to the conditions, and Spring has an abstract class support for dynamic switching of data sources; the second is to extend ShardingSphere-JDBC, modify the SQL routing mechanism, and add a dynamic switching data source mechanism. Using the database sharding + partition table method for data sharding is more in line with the actual project development of the Southern Power Grid. The database sharding adopts a provincial method. During an interface request, SQL generally only needs to be routed to the specified provincial database instance. The sharding table is implemented using a database partition table, which is transparent to business development.

[0024] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are specifically listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0026] Figure 1 The core flow chart of ShardingSphere-JDBC data sharding provided by the embodiment of the present invention;

[0027] Figure 2 An extended schematic diagram of a dynamic data source switching mechanism provided by an embodiment of the present invention;

[0028] Figure 3 The embodiment of the present invention provides a dynamic data source switching mechanism execution logic. DETAILED DESCRIPTION

[0029] Exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0030] The terms "comprises" and "comprising" and any variations thereof in the description, embodiments, claims and drawings of the present invention are intended to cover non-exclusive inclusions, for example, including a series of steps or units.

[0031] The technical solution of the present invention is further described in detail below with reference to the accompanying drawings and embodiments.

[0032] The sharding + partitioned table approach is typically implemented at the middleware level using a dynamic data source switching mechanism. The principle behind this dynamic data source switching mechanism is to select the JDBC data source corresponding to the specified database in the application based on the business data before executing the business SQL. Preliminary research and analysis revealed two main approaches to implementing this mechanism: The first is to create multiple data sources within the same project at the coding level, using the if-else approach to route directly based on conditions in the code. Spring provides abstract classes that support dynamic data source switching. The second approach is to extend ShardingSphere-JDBC, modify the SQL routing mechanism, and add a dynamic data source switching mechanism.

[0033] Taking into account the need to use other shardingSphere-JDBC sharding mechanisms, data sharding parameter compatibility, and reduce development complexity during actual project development, the sharding SDK directly references the ShardingSphere-JDBC jar package and expands it to add a unified dynamic data source switching mechanism for users to use directly. It is also compatible with other functional features of ShardingSphere-JDBC.

[0034] The core principle of ShardingSphere-JDBC sharding middleware is to route SQL operations to the specified database or data table according to the configured data sharding strategy, thereby achieving horizontal expansion of data storage. ShardingSphere-JDBC provides 5 sharding strategies:

[0035] Standard sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEENAND in SQL statements, and only supports single sharding key.

[0036] Composite sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEENAND in SQL statements, and supports multiple sharding keys.

[0037] Hint sharding strategy: A sharding strategy that specifies sharding values through hints rather than extracting them from SQL.

[0038] No sharding strategy: No data sharding operation is performed.

[0039] For the data sharding method of sharding database + partition table, the Hint sharding strategy in ShardingSphere-JDBC is more suitable for this scenario (standard sharding / compound sharding is to perform routing operations by parsing SQL, while Hint performs routing operations according to the set sharding value). However, the development process of Hint sharding strategy is still relatively cumbersome, and sharding parameters need to be set for each table separately. Therefore, in order to unify the code logic and simplify the development process, we have expanded on the basis of Hint sharding strategy, designed and encapsulated a sharding strategy that dynamically switches data sources, and simplified the development parameter configuration. For example, when developing and modifying the employee information interface, the employee information of the same province will be sharded into the same database, and this sharding strategy can be used. At the same time, after extending ShardingSphere-JDBC, if other sharding strategies need to be used in actual project development, they are still compatible.

[0040] like Figure 1 As shown in the figure, ShardingSphere-JDBC's data sharding mainly consists of the process of SQL parsing => executor optimization => SQL routing => SQL rewriting => SQL execution => result merging.

[0041] SQL parsing involves lexical parsing and syntactic parsing. The lexical parser first breaks the SQL into indivisible words. The syntactic parser then understands the SQL and ultimately extracts the parsing context. The parsing context includes tables, selections, sorting options, grouping options, aggregate functions, paging information, query conditions, and placeholder markers that may need to be modified.

[0042] Executor optimization: merge and optimize sharding conditions such as OR.

[0043] SQL routing: Matches the user-configured sharding strategy based on the parsing context and generates a routing path. Currently supports sharding routing and broadcast routing.

[0044] SQL rewriting: Rewrite SQL statements into statements that can be correctly executed in the real database. SQL rewriting is divided into correctness rewriting and optimization rewriting.

[0045] SQL execution: Asynchronous execution through a multi-threaded executor.

[0046] Result merging: Merge multiple execution result sets to facilitate output through a unified JDBC interface. Result merging includes stream merging, in-memory merging, and append merging using the decorator pattern.

[0047] The dynamic switching data source mechanism is achieved by extending ShardingSphere SQLRouter (SQL router), see Figure 2Before executing ShardingSphere routing code, add dynamic switching data source identification judgment. If the identification is set, the database value in the thread context and the configured database rules are taken out to calculate the data source to be used, and the SQL submitted by the upper code is executed on the data source. See Figure 3 The upper-level code needs to set the dynamic data source switch identifier value before executing the SQL business code. After the expansion, for the scenario of data sharding with sharded databases and partitioned tables, there is no need to configure cumbersome sharding strategies for each table separately, which simplifies the development process.

[0048] like Figure 3 As shown, specifically including:

[0049] Step 1: Set the sub-library identification value before executing the interface business code.

[0050] Step 2: After the SQL router is expanded, it first checks whether the database sharding flag is set. If it is set, it executes step 3.

[0051] Step 3: Calculate and select the data source based on the configured sharding identifier value and the sharding rules in the configuration file, and then proceed to step 5. The calculation and selection logic for the data source supports Groovy expressions.

[0052] Step 4. Execute the SQL routing code logic before ShardingSphere expansion.

[0053] Step 5. Execute the upper-level business SQL in the selected data source. The code logic is still implemented by calling the ShardingSphere execution engine.

[0054] Beneficial Effects: Using database sharding and partitioned tables for data sharding is highly suitable for actual project development in China Southern Power Grid. Database sharding is performed by province. During an interface request, SQL statements are generally routed to the database instance in the specified province. Table sharding is implemented using database partitioned tables, making it transparent to business development.

[0055] The above specific implementation methods further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above are only specific implementation methods of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for dynamic switching of data source mechanism, characterized in that: The dynamic switching method includes: Before executing business SQL, select the JDBC data source corresponding to the specified database in the application based on the business data; The mechanism to achieve dynamic switching is to extend ShardingSphere-JDBC, modify the SQL routing mechanism, and add a dynamic switching data source mechanism, including: Dynamically switch data source mechanism by extending SQL router; Before executing ShardingSphere routing code, add dynamic switching data source identification judgment. If the identification is set, the sharding value in the thread context and the configured sharding rules are taken out to calculate the data source to be used, and execute the SQL submitted by the upper code on the data source; The dynamic data source switching mechanism specifically includes: Step 1: Set the sub-database identification value before executing the interface business code; Step 2: After the SQL router is expanded, first check whether the database sharding flag is set. If it is set, go to step 3; if not, go to step 4. Step 3: Calculate and select the data source based on the configured sharding identifier value and the sharding rules in the configuration file, and then proceed to step 5. The calculation and selection logic for the data source supports Groovy expressions. Step 4. Execute the SQL routing code logic before ShardingSphere expansion; Step 5. Execute the upper-level business SQL in the selected data source. The code logic is still implemented by calling the ShardingSphere execution engine.

2. A data source mechanism dynamic switching method according to claim 1, characterized in that: The dynamic data source switching mechanism specifically includes: The core principle of ShardingSphere-JDBC sharding middleware is to route SQL operations to the specified database or data table according to the configured data sharding strategy, thereby achieving horizontal expansion of data storage; ShardingSphere-JDBC provides 4 sharding strategies: Standard sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEEN AND in SQL statements, and only supports single sharding key; Composite sharding strategy: provides sharding operations for =, >, <, >=, <=, IN, and BETWEEN AND in SQL statements, and supports multiple sharding keys; Hint sharding strategy: a sharding strategy that specifies sharding values through hints rather than extracting them from SQL; No sharding strategy: No data sharding operation is performed.

Images