Database table division method and system based on JavaAgent and Javassist

Abstract

The invention relates to the technical field of SQL (Structured Query Language) databases, in particular to a database table division method and system based on JavaAgent and Javassist, comprising: for a target database, creating a table division thread and adding an execution node to an sql execution statement associated with the target database to form a replacement statement; when the replacement statement is executed, table name information in the replacement statement is modified according to the sub-table information to form an actual execution statement; and executing the actual execution statement to construct a database sub-table corresponding to the target database. In order to solve the problem that corresponding table names in service codes need to be modified after database table division, when table division occurs in a database system, corresponding table division threads are created, and execution nodes are inserted into associated sql execution statements. And when the sql execution statement is called, the corresponding table name information in the replacement statement is modified through the table division thread, so that the original statement points to the entity table subjected to table division processing, and the operation on the table division is realized without modifying a service code.

Description

Technical Field

[0001] The present invention relates to the technical field of SQL databases, and in particular to a database table partitioning method and system based on JavaAgent and Javassist. Background Art

[0002] Structured Query Language (SQL) is a special-purpose programming language. It is a database query and programming language used to access data and query, update and manage relational database systems. Table partitioning refers to decomposing a table in a database into N entity tables with independent storage space according to certain rules. When reading and writing, the system needs to obtain the corresponding word table according to the defined rules and then perform operations. Since the query of SQL database tables will become slower as the data increases, partitioning and table partitioning operations need to be performed frequently during the operation and maintenance of SQL databases.

[0003] In the prior art, there are technical solutions for maintaining SQL databases after sharding. For example, Chinese patent CN201711000959.5 discloses a method and device for translating logical SQL statements in a sharding mode, which receives and outputs logical SQL statements; performs statement analysis on the output logical SQL statements to obtain a syntax analysis tree of the logical SQL statements; matches the table names in the syntax analysis tree with the preset sharding strategies to obtain the sharding information of the table names in the syntax analysis tree; and translates the logical SQL statements into physical SQL statements based on the sharding information. This technical solution can provide a convenient and efficient physical SQL statement generation capability for sharding.

[0004] However, during the actual implementation process, the inventors found that when using this type of technical solution, for example, when operating on the data in a sub-table, it is still necessary to add the corresponding sub-table name in the form of code, and then the database side maintains the corresponding sub-table, which leads to inconvenience in use. In particular, when a new sub-table is generated based on the original business, the code of the related business needs to be modified to achieve normal operation. Summary of the invention

[0005] In view of the above problems existing in the prior art, a database table partitioning method based on JavaAgent and Javassist is provided; on the other hand, a database table partitioning system for implementing the database table partitioning method is also provided.

[0006] The specific technical solutions are as follows:

[0007] A database table partitioning method based on JavaAgent and Javassist, comprising:

[0008] Step S1: for a target database, create a table sharding thread and add an execution node to a SQL execution statement associated with the target database to form a replacement statement;

[0009] Step S2: when executing the replacement statement, the database system calls the table partitioning thread to obtain table partitioning information, and modifies the table name information in the replacement statement according to the table partitioning information to form an actual execution statement;

[0010] Step S3: Execute the actual execution statement to construct a database sub-table corresponding to the target database.

[0011] On the other hand, in step S1, the thread creation process of the table partitioning thread includes:

[0012] Step A11: creating the table partitioning thread for the target database;

[0013] Step A12: writing the sub-table information into the sub-table thread.

[0014] On the other hand, in step S1, the node insertion process of generating the replacement statement includes:

[0015] Step B11: traverse the sql execution statement, and search for an execution function in the sql execution statement;

[0016] Step B12: Add the execution node to the method body of the execution function to form the replacement statement.

[0017] On the other hand, in the step B11, the SQL execution statement includes a SQL statement associated with the connection object and a SQL statement associated with the pre-processing object.

[0018] On the other hand, the step S2 comprises:

[0019] Step S21: when the execution node is triggered, the database system calls the table partitioning thread to obtain the table partitioning information;

[0020] Step S22: Find the original table name from the replacement statement;

[0021] Step S23: replacing the original table name according to the sub-table information to obtain the actual execution statement.

[0022] On the other hand, after executing step S3, the method further includes:

[0023] Step S4: Clear the sub-table information in the sub-table thread.

[0024] A database table partitioning system based on JavaAgent and Javassist, used to implement the above database table partitioning method, comprises:

[0025] A thread generation module, wherein the thread generation module creates a table partitioning thread for a target database and adds an execution node to a SQL execution statement associated with the target database to form a replacement statement;

[0026] A statement replacement module, the statement replacement module is connected to the thread generation module, and when the database system executes the replacement statement, the statement replacement module calls the table sub-thread to obtain table sub-information, and modifies the table name information in the replacement statement according to the table sub-information to form an actual execution statement;

[0027] An execution module is connected to the statement replacement module, and the execution module executes the actual execution statement to construct a database sub-table corresponding to the target database.

[0028] On the other hand, the thread generation module includes:

[0029] A thread creation module, wherein the thread creation module creates the table partitioning thread for the target database;

[0030] A thread writing module is connected to the thread creation module, and the thread writing module writes the sub-table information into the sub-table thread.

[0031] On the other hand, the thread generation module includes:

[0032] A statement traversal module, wherein the statement traversal module traverses the SQL execution statement and searches for an execution function in the SQL execution statement;

[0033] A method body replacement module is connected to the statement traversal module, and the method body replacement module adds the execution node to the method body part of the execution function to form the replacement statement.

[0034] On the other hand, it also includes:

[0035] A thread clearing module, the thread clearing module is connected to the execution module, and the thread clearing module clears the sub-table information in the sub-table thread after constructing the database sub-table.

[0036] The above technical solution has the following advantages or beneficial effects:

[0037] In view of the problem in the prior art that the corresponding table name in the business code needs to be directly modified after the database table is split, which is inconvenient to use, this solution introduces the above-mentioned table splitting method. When a split table appears in the database system, a corresponding table splitting thread is created, and an execution node is inserted for the associated SQL execution statement. When the SQL execution statement is called, the corresponding table name information in the replacement statement is modified through the table splitting thread, so that the original statement points to the entity table after the table splitting processing, and the operation of the split table is realized without modifying the business code. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] The embodiments of the present invention will be described more fully with reference to the attached drawings, which are provided for illustration and description only and are not intended to limit the scope of the present invention.

[0039] Figure 1 is an overall schematic diagram of an embodiment of the present invention;

[0040] Figure 2 A schematic diagram of a thread creation process in an embodiment of the present invention;

[0041] Figure 3 A schematic diagram of a node insertion process in an embodiment of the present invention;

[0042] Figure 4 This is a schematic diagram of step S2 in an embodiment of the present invention;

[0043] Figure 5 This is a schematic diagram of step S4 in an embodiment of the present invention;

[0044] Figure 6 A schematic diagram of a system in an embodiment of the present invention;

[0045] Figure 7 A schematic diagram of a thread creation module in an embodiment of the present invention;

[0046] Figure 8 This is a schematic diagram of a statement traversal module in an embodiment of the present invention;

[0047] Fig. 9 Schematic diagram of a thread clearing module in an embodiment of the present invention. DETAILED DESCRIPTION

[0048] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only 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.

[0049] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0050] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments, but they are not intended to limit the present invention.

[0051] The present invention comprises:

[0052] A database partitioning method based on JavaAgent and Javassist, such as Figure 1 As shown, including:

[0053] Step S1: for the target database, create a table sharding thread and add an execution node to the SQL execution statement associated with the target database to form a replacement statement;

[0054] Step S2: When executing the replacement statement, the database system calls the table partitioning thread to obtain the table partitioning information, and modifies the table name information in the replacement statement according to the table partitioning information to form an actual execution statement;

[0055] Step S3: Execute the actual execution statement to construct a database sub-table corresponding to the target database.

[0056] Specifically, in view of the problem in the prior art that the corresponding table name in the business code needs to be directly modified after the database is split, which is inconvenient to use, this solution introduces the above-mentioned split table method. When a split table appears in the target database in the database system, a corresponding split table thread is created, and the SQL execution statement associated with the target database in the business system is inserted into the execution node. When the business system triggers the SQL execution statement, the corresponding table name information in the replacement statement is modified through the split table thread, so that the original statement points to the entity table after the split table processing, and the operation of the split table is realized without modifying the business code.

[0057] During the implementation process, the above-mentioned database table sharding method is configured in the database system as a software implementation example, which is used to monitor the table sharding status of several target databases in the database system and automatically perform maintenance when table sharding occurs, including creating table sharding threads corresponding to the table sharding and processing the business system associated with the target database.

[0058] The target database refers to a specific database that has at least one original database table. During use, as the amount of data increases, the database system will automatically generate several sub-tables and corresponding indexes to implement partitioned storage of data.

[0059] A business system refers to a system used for operating related production businesses. During its operation, some business codes will be triggered, and then the corresponding data will be queried from the database table to support business operations. Usually, this part of the business code needs to specify the table name information of the corresponding database table. Therefore, when a table is split, it is often necessary to modify the table name information contained in the business code to point to the database table after the table is split. This part of the business code is usually presented in the form of relevant SQL statements.

[0060] A table partitioning thread is a sub-thread running in a database system. It is usually created for one target database, and may also contain table name information of multiple target databases at the same time.

[0061] In one embodiment, Figure 2 As shown, in step S1, the thread creation process of the table partitioning thread includes:

[0062] Step A11: Create a table sharding thread for the target database;

[0063] Step A12: Write the table partition information in the table partition thread.

[0064] Specifically, in order to achieve a better automatic maintenance process of sub-table information, in this embodiment, a thread object is first created in the database system as a sub-table thread, and then the TransmittableThreadLocal tool is used to write the sub-table information into the sub-table thread. The sub-table information mainly includes the table of the specified target database and the sub-table to be replaced, as well as the corresponding custom sub-table rules. On this basis, the sub-table information is stored by the child thread of the sub-table thread to achieve a better automatic maintenance process of the sub-table information.

[0065] In one embodiment, Figure 3 As shown, in step S1, the node insertion process of generating a replacement statement includes:

[0066] Step B11: traverse the SQL execution statement and search for the execution function in the SQL execution statement;

[0067] Step B12: Add an execution node to the method body of the execution function to form a replacement statement.

[0068] Specifically, in order to achieve a better automatic maintenance effect on the business code in the business system, in this embodiment, first, the SQL execution statements in the business code associated with the target database are traversed, and then the execution function execute* is found, including execute, executeQuery, executeUpdate, etc. Then, the method body of the execution function is instrumented, and the execution node is added to form a replacement statement.

[0069] In one embodiment, in step B11, the SQL execution statement includes a SQL statement associated with the connection object and a SQL statement associated with the preprocessing object.

[0070] Specifically, in the actual implementation process, this part is mainly implemented through Javaagent and javassist. Among them, in the process of traversing the sql execution statement, the connection object Connection obtained by jdbc and the sql statement preprocessing object PreparedStatement object can be inserted through Javaagent to form the corresponding execution node. Then in the subsequent replacement process, after running to the execution node, the execute, executeQuery, executeUpdate and other methods of PreparedStatement are modified through javassist. The original logic mainly includes taking out the corresponding sub-table information from the sub-table thread and replacing the original database table information in the method body of the above-mentioned execution function, thereby realizing the replacement process.

[0071] In one embodiment, Figure 4 As shown, step S2 includes:

[0072] Step S21: When the execution node is triggered, the database system calls the table partitioning thread to obtain table partitioning information;

[0073] Step S22: Find the name of the original table from the replacement statement;

[0074] Step S23: Replace the original table name according to the sub-table information to obtain the actual execution statement.

[0075] Specifically, to achieve a better replacement effect, in this embodiment, after the execution node of the Javaagent plug-in is triggered, the database system calls the table partitioning thread to obtain the table partitioning information. At the same time, through javassist's execute, executeQuery, executeUpdate and other methods of PreparedStatement, the original table name in the original business code is extracted, and the original table name is replaced with the table partitioning information to obtain the actual execution statement.

[0076] In one embodiment, Figure 5 As shown, after executing step S3, the method further includes:

[0077] Step S4: Clear the table partitioning information in the table partitioning thread.

[0078] Specifically, in order to achieve a better automatic maintenance effect, in this embodiment, after the table sharding is completed, the table sharding information in the table sharding thread is cleared to facilitate subsequent maintenance.

[0079] A database table partitioning system based on JavaAgent and Javassist is used to implement the above database table partitioning method, such as Figure 6 As shown, including:

[0080] Thread generation module 1, thread generation module 1 creates a table partitioning thread for the target database and adds an execution node to the SQL execution statement associated with the target database to form a replacement statement;

[0081] The statement replacement module 2 is connected to the thread generation module 1. When the database system executes the replacement statement, the statement replacement module 2 calls the table sub-thread to obtain the table sub-information, and modifies the table name information in the replacement statement according to the table sub-information to form the actual execution statement;

[0082] Execution module 3, execution module 3 connects statement replacement module 2, and execution module 3 executes actual execution statement to construct a database sub-table corresponding to the target database.

[0083] Specifically, in view of the problem that the corresponding table name in the business code needs to be directly modified after the database is divided into tables in the prior art, which is inconvenient to use, the above-mentioned table division method is introduced in this solution. When a table division appears in the target database in the database system, the thread generation module 1 creates a corresponding table division thread and inserts an execution node into the SQL execution statement associated with the target database in the business system. When the business system triggers the SQL execution statement, the statement replacement module 2 modifies the corresponding table name information in the replacement statement through the table division thread, so that the original statement points to the entity table after the table division processing, and then is executed by the execution module 3. The operation of the table division is realized without modifying the business code.

[0084] In one embodiment, Figure 7 As shown, the thread generation module 1 includes:

[0085] A thread creation module 11, wherein the thread creation module 11 creates a table partitioning thread for a target database;

[0086] The thread writing module 12 is connected to the thread creating module 11 and writes the sub-table information into the sub-table thread.

[0087] Specifically, in order to achieve a better automatic maintenance process of sub-table information, in this embodiment, first, the thread creation module 11 creates a thread object as a sub-table thread in the database system, and then the thread writing module 12 uses the TransmittableThreadLocal tool to write the sub-table information into the sub-table thread. The sub-table information mainly includes the table of the specified target database and the sub-table to be replaced, as well as the corresponding custom sub-table rules. On this basis, the sub-table thread stores the sub-table information to achieve a better automatic maintenance process of the sub-table information.

[0088] In one embodiment, Figure 8 As shown, the thread generation module 1 includes:

[0089] A statement traversal module 13 traverses the SQL execution statement and searches for an execution function in the SQL execution statement;

[0090] The method body replacement module 14 is connected to the statement traversal module 13, and the method body replacement module 14 adds execution nodes to the method body part of the execution function to form a replacement statement.

[0091] Specifically, in order to achieve a better automatic maintenance effect on the business code in the business system, in this embodiment, first, the statement traversal module 13 traverses the SQL execution statements in the business code associated with the target database, and then finds the execution function execute*, including execute, executeQuery, executeUpdate, etc. Then the method body replacement module 14 inserts the method body part of the execution function and adds an execution node to form a replacement statement.

[0092] In one embodiment, Fig. 9 As shown, it also includes:

[0093] The thread clearing module 4 is connected to the execution module 3. The thread clearing module 4 clears the sub-table information in the sub-table thread after building the database sub-table.

[0094] Specifically, in order to achieve a better automatic maintenance effect, in this embodiment, after the table is divided, the thread clearing module 4 also clears the table division information in the table division thread to facilitate subsequent maintenance.

[0095] The above are only preferred embodiments of the present invention, and are not intended to limit the implementation methods and protection scope of the present invention. Those skilled in the art should be aware that all solutions obtained by equivalent substitutions and obvious changes made using the description and illustrations of the present invention should be included in the protection scope of the present invention.

Claims

1. A database table partitioning method based on JavaAgent and Javassist, characterized in that: include: Step S1: for a target database, create a table sharding thread and add an execution node to a SQL execution statement associated with the target database to form a replacement statement; Step S2: when executing the replacement statement, the database system calls the table partitioning thread to obtain table partitioning information, and modifies the table name information in the replacement statement according to the table partitioning information to form an actual execution statement; Step S3: Execute the actual execution statement to construct a database sub-table corresponding to the target database.

2. The database table partitioning method according to claim 1, characterized in that: In step S1, the thread creation process of the table partitioning thread includes: Step A11: creating the table partitioning thread for the target database; Step A12: writing the sub-table information into the sub-table thread.

3. The database table partitioning method according to claim 1, characterized in that: In step S1, the node insertion process of generating the replacement statement includes: Step B11: traverse the sql execution statement, and search for an execution function in the sql execution statement; Step B12: Add the execution node to the method body of the execution function to form the replacement statement.

4. The database table partitioning method according to claim 3, characterized in that: In the step B11, the SQL execution statement includes a SQL statement associated with the connection object and a SQL statement associated with the preprocessing object.

5. The database table partitioning method according to claim 1, characterized in that: The step S2 comprises: Step S21: when the execution node is triggered, the database system calls the table partitioning thread to obtain the table partitioning information; Step S22: Find the original table name from the replacement statement; Step S23: replacing the original table name according to the sub-table information to obtain the actual execution statement.

6. The database table partitioning method according to claim 2, characterized in that: After executing step S3, the method further includes: Step S4: Clear the sub-table information in the sub-table thread.

7. A database table partitioning system based on JavaAgent and Javassist, characterized in that: The method for implementing the database table partitioning method according to any one of claims 1 to 6 comprises: A thread generation module, wherein the thread generation module creates a table partitioning thread for a target database and adds an execution node to a SQL execution statement associated with the target database to form a replacement statement; A statement replacement module, the statement replacement module is connected to the thread generation module, and when the database system executes the replacement statement, the statement replacement module calls the table sub-thread to obtain table sub-information, and modifies the table name information in the replacement statement according to the table sub-information to form an actual execution statement; An execution module is connected to the statement replacement module, and the execution module executes the actual execution statement to construct a database sub-table corresponding to the target database.

8. The database table partitioning system according to claim 7, characterized in that: The thread generation module comprises: A thread creation module, wherein the thread creation module creates the table partitioning thread for the target database; A thread writing module is connected to the thread creation module, and the thread writing module writes the sub-table information into the sub-table thread.

9. The database table partitioning system according to claim 7, characterized in that: The thread generation module comprises: A statement traversal module, wherein the statement traversal module traverses the SQL execution statement and searches for an execution function in the SQL execution statement; A method body replacement module is connected to the statement traversal module, and the method body replacement module adds the execution node to the method body part of the execution function to form the replacement statement.

10. The database table partitioning system according to claim 8, characterized in that: Also includes: A thread clearing module, the thread clearing module is connected to the execution module, and the thread clearing module clears the sub-table information in the sub-table thread after constructing the database sub-table.

Images