Database sub-table storage method based on intelligent algorithm

Abstract

The invention discloses a database sub-table storage method based on an intelligent algorithm, which comprises the following steps: S10, an application starting stage: when an application is started, an application starting unit triggers an operation of creating a basic table structure, a basic table is created in a database, and meanwhile, a global configuration unit loads global configuration of a sub-table strategy; s20, a service data operation stage: when a service end initiates an addition, deletion, modification and check operation request, the request is transmitted to a mybatis component, and after the mybatis converts the service request into an SQL statement, the SQL statement is transmitted to a share-jdbc component through data source pointing; s30, a sub-table routing calculation stage: integrating calculation results of time and data volume, and outputting a sub-table decision result; s40, a dynamic table creation stage; and S50, a data storage stage: the intelligent table division algorithm module returns a table division decision result to the sharding-jdb component, and the sharding-jdbc component routes the SQL statement to a corresponding target table to complete the addition, deletion, modification and check operation of the data.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of database storage, and particularly relates to a database table splitting storage method based on an intelligent algorithm. BACKGROUND

[0002] With the explosive growth of business data, the single table storage mode faces performance bottlenecks, and the database table splitting technology becomes a key means to solve the problem. The traditional table splitting scheme mainly relies on manual planning of table splitting rules, such as fixed time period table splitting and fixed data volume table splitting, and has the following defects: (1) high maintenance cost: manual monitoring of table data volume and time period is required, and new tables are created and table splitting routes are adjusted manually, which is tedious; (2) poor scalability: the table splitting rules are fixed, and cannot be adjusted adaptively according to the dynamic changes of business data (such as data growth rate fluctuation), which is prone to the situation that new tables are created too early or too late, affecting the storage performance; (3) strong coupling: the table splitting logic is deeply coupled with the business logic, and when the table splitting strategy is adjusted, the business code needs to be modified, which is not conducive to the iterative maintenance of the system. SUMMARY

[0003] In view of the above problems, the application provides a database table splitting storage method based on an intelligent algorithm, which is used to realize dynamic decision of table splitting rules and automatic expansion of table structure, reduce maintenance cost, and improve the flexibility and performance of database storage.

[0004] To solve the above technical problems, the application adopts the following technical scheme: A database table splitting storage method based on an intelligent algorithm, characterized by being applied to a database table splitting storage system comprising a business end, a database middleware, an intelligent table splitting algorithm module, a database and an application end, wherein the database middleware comprises a mybatis component and a sharding-jdbc component, the intelligent table splitting algorithm module further comprises a table splitting field analysis calculation unit, a time intelligent calculation unit and a data volume intelligent calculation unit, the database comprises a basic table and a dynamically expanded table, and the application end further comprises a global configuration unit, a table splitting creation unit and an application start unit, and comprises the following steps: S10, application start stage: when the application starts, the application start unit triggers a basic table structure creation operation to create a basic table in the database, wherein the basic table defines the general structure of business data, and at the same time, the global configuration unit loads the global configuration of the table splitting strategy; S20, business data operation stage: when the business end initiates an add, delete, modify or query operation request, the request is transmitted to the mybatis component, the mybatis component converts the business request into a SQL statement, and then the SQL statement is transmitted to the sharding-jdbc component through a data source pointer; S30, a sharding table routing calculation stage: the sharding-jdbc component delivers the SQL statement to a sharding table field analysis calculation unit, analyzes the key field for sharding in the SQL, the time intelligent calculation unit calculates the time shard to which the data should belong based on the order time and the globally configured time threshold, the data volume intelligent calculation unit calculates the data volume shard to which the data should belong based on the real-time data volume of each table in the database and the globally configured data volume threshold, and the time and data volume calculation results are integrated to output the sharding decision result, i.e., the corresponding target table in the database module, and the intelligent sharding algorithm module determines that the target table to which the data should point is the basic table or a certain dynamic expansion table. S40, a dynamic table creation stage: if the intelligent sharding algorithm module determines that a new dynamic expansion table needs to be created, a new table creation instruction is triggered to the sharding creation unit, and the sharding creation unit creates a new dynamic expansion table in the database module based on the structure of the basic table. S50, a data storage stage: the intelligent sharding algorithm module returns the sharding decision result to the sharding-jdb component, the sharding-jdbc component routes the SQL statement to the corresponding target table, and completes the data addition, deletion, modification and query operation.

[0005] In a possible implementation, the basic table structure in S10 includes fields, data types and index information.

[0006] In a possible implementation, the global configuration information in S10 includes a time sharding threshold, a data volume sharding threshold and a basic table structure definition.

[0007] In a possible implementation, the sharding decision result outputted by integrating the time and data volume calculation results in S30 includes that after the basic table reaches the upper limit, subsequent data goes to a new dynamic expansion table, and the dynamic table is full of the upper limit to automatically create a new dynamic expansion table under the time shard.

[0008] The present application has the following advantages: (1) Intelligent sharding decision: through the time intelligent calculation and data volume intelligent calculation modules, the dynamic self-adaptation of the sharding rule is realized, the sharding opportunity can be decided according to the actual situation of data growth without manual intervention, and the rationality of sharding is improved.

[0009] (2) Automatic table structure expansion: the sharding creation unit of the application end can automatically create a dynamic expansion table, reduces the manual maintenance cost, and ensures the consistency of the table structure and the basic table.

[0010] (3) Low coupling: the sharding logic is encapsulated in the independent intelligent sharding algorithm module and the application end module, which is decoupled from the business end logic, facilitates the iterative optimization of the sharding strategy, and does not affect the stability of the business function.

[0011] (4)High expansibility: support unlimited expansion of dynamic table according to the growth trend of business data, break through the performance bottleneck of single table storage, and improve the overall storage and query performance of the database. BRIEF DESCRIPTION OF DRAWINGS

[0012] Figure 1 A step flow chart of a database table storage method based on an intelligent algorithm according to an embodiment of the present application; Figure 2 A structural schematic diagram of a database table storage system applied to the database table storage method based on the intelligent algorithm according to an embodiment of the present application. DETAILED DESCRIPTION

[0013] The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are some of the embodiments of the present application, but not all the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the scope of protection of the present application.

[0014] Referring to Figure 1 , a step flow chart of a database table storage method based on an intelligent algorithm according to an embodiment of the present application is shown, which is applied to a database table storage system including a business end, a database middleware, an intelligent table splitting algorithm module, a database and an application end. The database middleware includes a mybatis component and a sharding-jdbc component. The intelligent table splitting algorithm module further includes a table splitting field analysis and calculation unit, a time intelligent calculation unit and a data volume intelligent calculation unit. The database includes a basic table and a dynamically expanded table. The application end further includes a global configuration unit, a table splitting creation unit and an application start unit. The following steps are included: S10, application start stage: when the application starts, the application start unit triggers the creation of a basic table structure operation to create a basic table in the database. The basic table defines the general structure of business data (such as order ID, user ID, order time, order amount and other fields and indexes of the order table). At the same time, the global configuration unit loads the global configuration of the table splitting strategy; S20, business data operation stage: when the business end initiates an add, delete, modify or query operation request, the request is passed to the mybatis component. After the mybatis component converts the business request into a SQL statement, it is passed to the sharding-jdbc component through the data source pointing; S30, the sharding table routing calculation stage: the sharding-jdbc component passes the SQL statement to the sharding table field analysis calculation unit, analyzes the key fields (such as time field, business identifier field, etc.) for sharding in SQL, and provides basic data for subsequent sharding calculation. The time intelligent calculation unit calculates the time shard (i.e. time dimension sharding table) to which the data belongs based on the order time and the globally configured time threshold; the data volume intelligent calculation unit calculates the data volume shard (i.e. data volume dimension sharding table) to which the data belongs based on the real-time data volume of each table in the database and the globally configured data volume threshold, and outputs the sharding decision result, i.e. the corresponding target table in the database module, based on the comprehensive time and data volume calculation result. The intelligent sharding algorithm module determines that the target table to which the data should point is the basic table or a certain dynamic expansion table; S40, the dynamic table creation stage: if the intelligent sharding algorithm module determines that a new dynamic expansion table needs to be created, it triggers a new table creation instruction to the sharding creation unit, and the sharding creation unit creates a new dynamic expansion table (such as order_202511 table) in the database module based on the structure of the basic table. S50, the data storage stage: the intelligent sharding algorithm module returns the sharding decision result to the sharding-jdb component, and the sharding-jdbc component routes the SQL statement to the corresponding target table to complete the data insertion, deletion, modification and query operation.

[0015] In a specific application example, the basic table structure in S10 includes field, data type and index information, which is also the structure template of the dynamic expansion table. The global configuration information includes time sharding threshold (e.g. create a new table after storing 30 days of data in a single table), data volume sharding threshold (e.g. create a new table after the data volume of a single table reaches 1 million), and basic table structure definition. The intelligent sharding algorithm module reads the configuration information of this module when performing sharding calculation.

[0016] In a specific application example, the sharding decision result outputted by the comprehensive time and data volume calculation result in S30 includes: after the basic table reaches the upper limit, the subsequent data goes to the new dynamic expansion table, first sharding by time, and when the dynamic table is full, a new dynamic expansion table under the time shard is automatically created.

[0017] Specifically, the base table is carried first, and the dynamic table is automatically switched after reaching the standard. The dynamic table is accurately routed according to "time slicing + data volume upper limit", and no manual intervention is required throughout the process. Further, the use scenarios of the base table include two cases: (1) the application is just started and has not created any dynamic table; (2) the base table does not reach the preset upper limit (such as data volume < 1 million and data time span < 30 days). If any of the above conditions is met, the data is directly written to the base table. After the base table is marked, that is, the data volume exceeds or the time span exceeds, the dynamic table usage scenario is entered, including two routing rules: (1) first by time: for example, configure "table by month", the data of November belongs to "202511" slice, and the data of December belongs to "202512" slice; (2) then control by data volume: under each time slice, if the dynamic table is full of the upper limit (such as 1 million), a new table is automatically created (such as "202511_01" is full, then "202511_02" is created).

[0018] In the above database table splitting storage method based on intelligent algorithm, the business end is used to process the logic of each business layer, and initiates the add, delete, modify and query operation request to the database middleware, which is the entrance of business data operation, and is deeply bound with specific business scenarios such as order business and user business, encapsulates business logic and forwards data operation request to database middleware. The database middleware is a bridge connecting the business end and the intelligent table splitting algorithm module. The mybatis component serves as a persistent layer framework, responsible for converting the add, delete, modify and query request of the business end into a SQL statement executable by the database, and providing a result mapping function. The sharding-jdbc component, as an open source distributed database middleware, receives the SQL statement passed by mybatis, routes the SQL to the intelligent table splitting algorithm module for table splitting analysis through the data source pointing mechanism, and realizes the routing of data splitting (table splitting). Through the cooperative work of each module, intelligent decision and automatic expansion of table splitting are realized, effectively solving the shortcomings of traditional table splitting scheme and improving the performance and maintainability of database storage. Specifically, it can include: (1) breaking through the performance bottleneck of single table, realizing horizontal split storage of data through intelligent table splitting, greatly improving the read-write performance and concurrent processing capacity of the database, and meeting the needs of high-load business scenarios; (2) realizing dynamic self-adaptation of table splitting strategy, combining intelligent calculation of time dimension and data volume dimension, automatically deciding table splitting time and table splitting rules without manual intervention to adapt to the dynamic growth of business data; (3) reducing system operation and architecture iteration cost, through automatic table structure expansion, decoupling of table splitting logic and business logic, reducing human operation errors, and supporting the optimization and upgrade of table splitting strategy of the business system without interrupting service.

[0019] It is to be understood that the example embodiments described herein are illustrative rather than limiting. Although one or more embodiments of the application are described in connection with the accompanying drawings, it will be understood that various modifications in form and detail can be made without departing from the spirit and scope of the application as defined by the appended claims.

Claims

1. A database table partitioning storage method based on intelligent algorithms, characterized in that, This system applies to a database sharding storage system that includes a business side, database middleware, an intelligent sharding algorithm module, a database, and an application side. The database middleware includes MyBatis and Sharding-JDBC components. The intelligent sharding algorithm module further includes a sharding field parsing and calculation unit, a time-based intelligent calculation unit, and a data volume intelligent calculation unit. The database includes basic tables and dynamically extended tables. The application side further includes a global configuration unit, a sharding creation unit, and an application startup unit. The system includes the following steps: S10, Application Startup Phase: When the application starts, the application startup unit triggers the creation of basic table structure operation, creating a basic table in the database. The basic table defines the general structure of business data. At the same time, the global configuration unit loads the global configuration of the table partitioning strategy. S20, Business Data Operation Phase: When the business side initiates a request for CRUD operations, the request is passed to the MyBatis component. MyBatis converts the business request into an SQL statement and then passes it to the Sharding-JDBC component through the data source pointer. S30, Table Sharding Route Calculation Phase: The sharding-jdbc component passes the SQL statement to the table sharding field parsing and calculation unit, parses the key fields used for table sharding in the SQL, and the time intelligent calculation unit calculates the time shard to which the data should belong based on the order time and the globally configured time threshold. The data volume intelligent calculation unit calculates the data volume shard to which the data should belong based on the real-time data volume of each table in the database and the globally configured data volume threshold. Combining the calculation results of time and data volume, the table sharding decision result is output, which points to the corresponding target table in the database module. The intelligent table sharding algorithm module determines whether the target table to which the data should point is the base table or a certain dynamically extended table. S40, Dynamic Table Creation Stage: If the intelligent table partitioning algorithm module determines that a new dynamic extended table needs to be created, it triggers a new table creation instruction to the table partitioning creation unit. The table partitioning creation unit creates a new dynamic extended table in the database module based on the structure of the base table. S50, Data Storage Stage: The intelligent table partitioning algorithm module returns the table partitioning decision result to the sharding-jdb component. The sharding-jdbc component routes the SQL statement to the corresponding target table to complete the data CRUD operations.

2. The database table partitioning storage method based on intelligent algorithms as described in claim 1, characterized in that, The basic table structure in S10 includes fields, data types, and index information.

3. The database table partitioning storage method based on intelligent algorithms as described in claim 1, characterized in that, The global configuration information in S10 includes time-based table partitioning thresholds, data volume-based table partitioning thresholds, and basic table structure definitions.

4. The database table partitioning storage method based on intelligent algorithms as described in claim 1, characterized in that, S30 calculates the combined time and data volume, and outputs the table partitioning decision: After the basic table reaches its limit, all subsequent data will go through the new dynamic expansion table. First, it is partitioned by time. When the dynamic table is full, a new dynamic expansion table under the time partition is automatically created.

Images