Automatic table dividing method and equipment

Abstract

The invention relates to the technical field of database management, and provides an automatic table division method and device.The method comprises the steps that a basic plug-in is added into a system code library, and a table division configuration file is created; defining a java implementation class through a java application development framework, and injecting a sub-table plug-in through the defined java implementation class; a target table name is dynamically generated by obtaining a mechanism code to which a user belongs, and the mechanism code of the user is stored by using a local thread; and when a user executes database query, dynamically selecting the target table according to the table division logic to complete data query and operation. According to the automatic table dividing method and device, the performance, reliability and expandability of a database management system can be remarkably improved.

Description

Technical Field

[0001] The present application relates to the technical field of database management, and in particular to a method and device for automatically partitioning tables. Background Art

[0002] In the field of modern information technology, database management systems (DBMS) are one of the core components of enterprise information systems and are widely used in various business scenarios. With the rapid development of information technology, the amount of enterprise data has exploded and the complexity of business has increased significantly. In traditional database management systems, data is usually stored in a single data table. This design can meet basic needs when the amount of data is small and the business logic is simple. However, with the continuous accumulation of data and the increase in business complexity, the performance bottleneck of a single data table has gradually emerged, especially in large-scale enterprise applications. This problem is particularly prominent.

[0003] Large enterprises usually consist of multiple organizations or departments, and the data access patterns of each organization or department vary significantly. For example, some departments may frequently perform data read operations, while others may focus more on data write operations. This difference causes a single data table to be inefficient when handling different types of read and write operations, which in turn affects the response speed and performance of the entire system. To alleviate this problem, existing solutions usually rely on manually configured table partitioning strategies, that is, distributing data into multiple data tables to reduce the load pressure of a single data table. However, there are many disadvantages to manually configuring table partitioning strategies: on the one hand, the configuration process is complicated, requiring professional technicians to invest a lot of time and energy in planning and implementation; on the other hand, manual configuration is prone to errors. Once improperly configured, it may lead to data loss, query errors, or further degradation of system performance. In addition, the manual table partitioning strategy has poor scalability and is difficult to adapt to the rapid changes in enterprise business and the continuous growth of data volume.

[0004] In summary, existing database management systems have performance bottlenecks, high maintenance costs, prone to errors, and poor scalability when facing large-scale data and complex business needs. Therefore, there is an urgent need for an automated, efficient, and easy-to-maintain table partitioning method to meet the strict requirements of modern enterprises for database performance and scalability. Summary of the invention

[0005] In view of this, in order to overcome the deficiencies of the prior art, the present application aims to provide a method and system for automatically dividing tables.

[0006] According to a first aspect of the present application, a method for automatically dividing a table is provided, the method comprising the following steps: Add basic plug-ins to the system code base and create table partitioning configuration files; Define the Java implementation class through the Java application development framework, and inject the table partitioning plug-in through the defined Java implementation class; The target table name is dynamically generated by obtaining the user's institution code, and the user's institution code is saved using a local thread; When the user executes a database query, the target table is dynamically selected according to the table partitioning logic to complete the data query and operation.

[0007] Optionally, in the automatic table sharding method of the present application, the basic plug-in includes a library and table sharding framework and an SQL parsing tool, wherein the library and table sharding framework can intercept and rewrite SQL statements according to custom rules, and the SQL parsing tool can decompose SQL statements into structured data.

[0008] Optionally, in the automatic table partitioning method of the present application, creating a table partitioning configuration file includes: creating a Java configuration file, defining a table partitioning strategy through the created Java configuration file, wherein the table partitioning strategy includes an ignore list, a parsing list, and a table partitioning logic implementation class.

[0009] Optionally, in the automatic table partitioning method of the present application, defining a Java implementation class through a Java application development framework includes: defining a Java implementation class ShardConfig through the tag @Configuration of the Java application development framework Spring.

[0010] Optionally, in the automatic table sharding method of the present application, a table sharding plug-in is injected through a defined java implementation class, including: defining a shardPlugin plug-in and a sqlSessionFactory plug-in in the java implementation class ShardConfig, wherein the shardPlugin plug-in can intercept SQL requests and dynamically modify the table name according to the table sharding rules, and the sqlSessionFactory plug-in can manage the life cycle of the SQL Session.

[0011] Optionally, in the automatic table sharding method of the present application, the target table name is dynamically generated by obtaining the code of the organization to which the user belongs, including: dynamically generating the target table name according to the organization to which the user belongs by overriding the getTargetTableName method of the ShardStrategy interface.

[0012] Optionally, in the automatic table partitioning method of the present application, the target table name is dynamically generated by obtaining the organization code of the user, including: obtaining the organization code of the current logged-in person through the local thread DynamicTableSourceKeyHolder method, returning the obtained organization code of the current logged-in person to the suffix of the database table, and locating the table corresponding to the corresponding organization.

[0013] Optionally, in the automatic table partitioning method of the present application, a local thread is used to save the user's organization code, including: intercepting all requests through the aspect-oriented programming aop method, and using the local thread DynamisTableSourceKexHolder method to save the organization code of the current logged-in person.

[0014] Optionally, in the automatic table sharding method of the present application, when a user executes a database query, a target table is dynamically selected according to the table sharding logic to complete the query and operation of data, including: All SQL requests are intercepted by the shardPlugin plug-in in the sharding plug-in, and the table name is dynamically modified by calling the getTargetTableName method of the ShardStrategy interface to modify the intercepted SQL requests; Send the modified SQL request to the database for query execution, and return the execution result to the user; After the SQL request ends, clear the user's organization in the DynamicTableSourceKeyHolder method.

[0015] According to a second aspect of the present application, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method described in the first aspect of the present application is implemented.

[0016] The automatic table partitioning method of the present application has the following beneficial technical effects: 1. The automatic table partitioning mechanism is adopted, and there is no need to manually configure the table partitioning strategy, which avoids errors and omissions caused by manual configuration. Through the combination of modern technical frameworks (such as AOP, ThreadLocal, etc.), the automatic execution of the table partitioning logic is realized, which greatly reduces the risk of manual operation and improves the stability and reliability of the system.

[0017] 2. By dynamically selecting data tables, data tables are allocated on demand according to the access mode of the user's organization, achieving efficient data management. This on-demand allocation mechanism can effectively reduce the load pressure of a single data table, significantly improve the system's response speed and data processing capabilities, and thus improve overall performance.

[0018] 3. Simplify the management process of table sharding logic, encapsulate complex table sharding strategies in an automated mechanism, and reduce the direct intervention of maintenance personnel in the underlying table sharding logic. This not only reduces the difficulty of maintenance, but also reduces the maintenance cost caused by changes in table sharding logic, making system maintenance more efficient and convenient.

[0019] 4. Support multiple table partitioning strategies, and be able to flexibly adjust the data table allocation method according to the needs of different business scenarios, so that the system can better adapt to the diversification and dynamic changes of corporate business, and enhance the adaptability and scalability of the system.

[0020] 5. It is suitable for various application scenarios that require dynamic allocation of data according to users or organizations, such as large-scale enterprise information systems, multi-tenant platforms, etc. It can effectively solve the performance bottleneck problem of traditional database management systems when facing complex business needs, and at the same time provide solid technical support for the long-term development of the system. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0022] Figure 1 A flowchart of a method for automatically dividing tables according to an embodiment of the present application; Figure 2 A schematic diagram of an automatic table partitioning method according to an embodiment of the present application; Figure 3 A schematic diagram of the structure of the device provided in this application. DETAILED DESCRIPTION

[0023] The embodiments of the present application are described in detail below with reference to the accompanying drawings.

[0024] It should be noted that the following embodiments and features in the embodiments may be combined with each other in the absence of conflict; and, based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in the field without making any creative work are within the scope of protection of the present disclosure.

[0025] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein may be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on the present disclosure, it should be understood by those skilled in the art that an aspect described herein may be implemented independently of any other aspect, and two or more of these aspects may be combined in various ways. For example, any number of aspects described herein may be used to implement the device and / or practice the method. In addition, other structures and / or functionalities other than one or more of the aspects described herein may be used to implement this device and / or practice this method.

[0026] The present application provides a method for automatic table partitioning, which is illustrated by the following embodiments.

[0027] Figure 1 is a flowchart of a method for automatically dividing tables according to an embodiment of the present application. Figure 2 FIG. 1 is a schematic diagram of an automatic table partitioning method according to an embodiment of the present application. Figure 1 and Figure 2 As shown, the method for automatically dividing the table includes the following steps: Step S101: Add a basic plug-in to the system code library and create a table partitioning configuration file.

[0028] As an optional example, in this embodiment, the basic plug-in includes a sub-library and table framework and a SQL parsing tool, wherein the sub-library and table framework can intercept and rewrite SQL statements according to custom rules, and the SQL parsing tool can decompose SQL statements into structured data.

[0029] For example, in this embodiment, the sub-library and table framework plug-in and the SQL parsing tool plug-in are introduced through the project's build management and dependency management tool Maven. The sub-library and table framework can be the Shardbatis sub-library and table framework, which can intercept and rewrite SQL statements according to custom rules. The SQL parsing tool plug-in can be JSQPParser, which can decompose complex SQL statements into structured data that is easy to process.

[0030] As an optional example, in this embodiment, a java configuration file is created, and a table partitioning strategy is defined through the created java configuration file. The table partitioning strategy includes an ignore list, a parsing list, and a table partitioning logic implementation class. For example, in this embodiment, a java configuration file shard_config.xml is created, and this file is placed in the configuration area of ​​the project, so that the java application development framework spring can read the content of the configuration file. The java class where the table partitioning logic implementation is located is specified in this file, and the implementation class can be defined as shardImpl.java; the database table objects that need to go through the table partitioning logic can be configured as needed in the configuration file shard_config.xml, and the table partitioning logic of which tables in the database needs to go to the implementation method of shardImpl.java is defined.

[0031] Step S102: define a Java implementation class through a Java application development framework, and inject the table partitioning plug-in through the defined Java implementation class.

[0032] As an optional example, define the Java implementation class ShardConfig through the tag @Configuration of the Java application development framework Spring. For example, define the shardPlugin plug-in and the sqlSessionFactory plug-in in the Java implementation class ShardConfig, where the shardPlugin plug-in can intercept SQL requests and dynamically modify the table name according to the table partitioning rules, and the sqlSessionFactory plug-in can manage the life cycle of the SQL Session.

[0033] Step S103: dynamically generate the target table name by obtaining the institution code of the user, and use the local thread to save the institution code of the user.

[0034] As an optional example, in this embodiment, by rewriting the getTargetTableName method of the ShardStrategy interface, the target table name is dynamically generated according to the organization to which the user belongs. For example, in this embodiment, the organization code of the current login person is obtained through the local thread DynamicTableSourceKeyHolder method, and the obtained organization code of the current login person is returned to the suffix of the database table, and the table corresponding to the corresponding organization is located, thereby realizing the table partitioning logic.

[0035] As an optional example, in this embodiment, all requests are intercepted by the aspect-oriented programming aop method, and the local thread DynamisTableSourceKexHolder method is used to save the organizational code of the current logged-in person to ensure that each request can correctly identify the user's organization.

[0036] Step S104: When the user executes a database query, the target table is dynamically selected according to the table partitioning logic to complete the data query and operation.

[0037] In this embodiment, when a user executes a database query, all SQL requests are intercepted by the shardPlugin plug-in in the table sharding plug-in, and the table name is dynamically modified by calling the getTargetTableName method of the ShardStrategy interface to modify the intercepted SQL request; the modified SQL request is sent to the database for query execution, and the execution result is returned to the user; after the SQL request is completed, the user's organization in the DynamicTableSourceKeyHolder method is cleared, thereby preventing memory leaks.

[0038] The automatic table partitioning method of this embodiment has the following beneficial technical effects: 1. The automatic table partitioning mechanism is adopted, and there is no need to manually configure the table partitioning strategy, which avoids errors and omissions caused by manual configuration. Through the combination of modern technical frameworks (such as AOP, ThreadLocal, etc.), the automatic execution of the table partitioning logic is realized, which greatly reduces the risk of manual operation and improves the stability and reliability of the system.

[0039] 2. By dynamically selecting data tables, data tables are allocated on demand according to the access mode of the user's organization, achieving efficient data management. This on-demand allocation mechanism can effectively reduce the load pressure of a single data table, significantly improve the system's response speed and data processing capabilities, and thus improve overall performance.

[0040] 3. Simplify the management process of table sharding logic, encapsulate complex table sharding strategies in an automated mechanism, and reduce the direct intervention of maintenance personnel in the underlying table sharding logic. This not only reduces the difficulty of maintenance, but also reduces the maintenance cost caused by changes in table sharding logic, making system maintenance more efficient and convenient.

[0041] 4. Support multiple table partitioning strategies, and be able to flexibly adjust the data table allocation method according to the needs of different business scenarios, so that the system can better adapt to the diversification and dynamic changes of corporate business, and enhance the adaptability and scalability of the system.

[0042] 5. It is suitable for various application scenarios that require dynamic allocation of data according to users or organizations, such as large-scale enterprise information systems, multi-tenant platforms, etc. It can effectively solve the performance bottleneck problem of traditional database management systems when facing complex business needs, and at the same time provide solid technical support for the long-term development of the system.

[0043] like Figure 3 As shown, the present application also provides a device, including a processor 210, a communication interface 220, a memory 230 for storing a computer program executable by the processor, and a communication bus 240. The processor 210, the communication interface 220, and the memory 230 communicate with each other through the communication bus 240. The processor 210 implements the above-mentioned automatic table partitioning method by running the executable computer program.

[0044] Among them, the computer program in the memory 230 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0045] The system embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, i.e., they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected based on actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art may understand and implement it without creative effort.

[0046] Through the description of the above implementation modes, those skilled in the art can clearly understand that each implementation mode can be implemented by means of software plus a necessary general hardware platform, or of course by hardware. Based on such an understanding, the above technical solution can essentially or in other words be embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of each embodiment or some parts of the embodiment.

[0047] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A method for automatic table division, characterized in that: The method comprises the following steps: Add basic plug-ins to the system code base and create table partitioning configuration files; Define the Java implementation class through the Java application development framework, and inject the table partitioning plug-in through the defined Java implementation class; The target table name is dynamically generated by obtaining the user's institution code, and the user's institution code is saved using a local thread; When the user executes a database query, the target table is dynamically selected according to the table partitioning logic to complete the data query and operation.

2. The method for automatic table division according to claim 1, characterized in that: The basic plug-in includes a database and table sharding framework and an SQL parsing tool. The database and table sharding framework can intercept and rewrite SQL statements according to custom rules, and the SQL parsing tool can decompose SQL statements into structured data.

3. The method for automatic table division according to claim 1, characterized in that: Creating a table partitioning configuration file includes: creating a java configuration file, defining a table partitioning strategy through the created java configuration file, wherein the table partitioning strategy includes an ignore list, a parsing list, and a table partitioning logic implementation class.

4. The method for automatic table division according to claim 1, characterized in that: The Java implementation class is defined through the Java application development framework, including: defining the Java implementation class ShardConfig through the tag @Configuration of the Java application development framework Spring.

5. The method for automatic table division according to claim 4, characterized in that: Inject the table sharding plug-in through the defined java implementation class, including: defining the shardPlugin plug-in and the sqlSessionFactory plug-in in the java implementation class ShardConfig, where the shardPlugin plug-in can intercept SQL requests and dynamically modify the table name according to the table sharding rules, and the sqlSessionFactory plug-in can manage the life cycle of the SQL Session.

6. The method for automatic table division according to claim 1, characterized in that: The target table name is dynamically generated by obtaining the code of the organization to which the user belongs, including: dynamically generating the target table name according to the organization to which the user belongs by overriding the getTargetTableName method of the ShardStrategy interface.

7. The method for automatic table division according to claim 6, characterized in that: The target table name is dynamically generated by obtaining the organization code of the user, including: obtaining the organization code of the current login person through the local thread DynamicTableSourceKeyHolder method, returning the obtained organization code of the current login person to the suffix of the database table, and locating the table corresponding to the corresponding organization.

8. The method for automatic table division according to claim 1, characterized in that: Use local threads to save the user's organization code, including: intercepting all requests through the aop method of aspect-oriented programming, and using the local thread DynamisTableSourceKexHolder method to save the organization code of the current login person.

9. The method for automatic table division according to claim 1, characterized in that: When the user executes a database query, the target table is dynamically selected according to the table sharding logic to complete the data query and operation, including: All SQL requests are intercepted by the shardPlugin plug-in in the sharding plug-in, and the table name is dynamically modified by calling the getTargetTableName method of the ShardStrategy interface to modify the intercepted SQL requests; Send the modified SQL request to the database for query execution, and return the execution result to the user; After the SQL request ends, clear the user's organization in the DynamicTableSourceKeyHolder method.

10. A computer device, characterized in that: The computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to any one of claims 1 to 9 when executing the program.

Images