A Random Smoothing Sub - table Method, Terminal and Storage Medium

Abstract

The present invention discloses a random smoothing sub - table method, a terminal and a storage medium. In the random smoothing sub - table method, annotation configurations are pre - associated with database tables to be mapped in a configuration file, and it specifically includes the following steps: Scan the database tables according to the annotation configuration items; Determine whether the scanned object conforms to the sub - table configuration. If it does not conform, check whether there are differences in database attributes in a multi - thread parallel manner. If there are no differences, end. If there are differences, generate an SQL for updating the database and execute it until the end; If it conforms to the sub - table configuration, automatically assign a sub - table name, and determine whether there is a sub - table mapping table. If there is already a sub - table mapping table, check whether there are differences in database attributes in a multi - thread parallel manner. If there is no sub - table mapping table, generate an SQL for updating the database and execute it until the end. The present invention provides a random smoothing sub - table method, which fully automatically updates the database table structure in a multi - thread parallel manner, automatically performs sub - table division, and significantly improves the development efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a random smoothing table partitioning method, a terminal and a storage medium. Background Art

[0002] For data persistence of Java-based server applications, MyBatis or Hibernate is used more often. In these two persistence frameworks, MyBatis does not support automatic table structure update, and cannot print the specific line numbers of the classes that call the persistence methods and the final SQL; although Hibernate supports automatic table creation, it does not support table partitioning, and the single-thread speed is slow. At the same time, it also cannot print the specific line numbers of the classes that call the persistence methods and the final SQL.

[0003] For third-party table partitioning solutions or plugins on the market, there are also problems such as slow single-threaded execution, the number of partitioned tables needs to be specified in advance, manual migration is required for the initial table partitioning, and when developing business, it is also necessary to manually query which partitioned table the data is specifically routed to, which is not conducive to rapid development. Summary of the Invention

[0004] The purpose of the present invention is to provide a random smoothing table partitioning method, which can automatically update the database table structure in parallel with multiple threads, automatically partition tables, and significantly improve development efficiency.

[0005] The technical solution adopted by a random smoothing table partitioning method disclosed by the present invention is as follows:

[0006] A random smoothing table partitioning method, which pre-associates annotation configurations with database tables to be mapped in a configuration file, and specifically includes the following steps:

[0007] Scan the database tables according to the annotation configuration items;

[0008] Judge whether the scanned object meets the table partitioning configuration. If it does not meet, check whether there are differences between the database column attributes and index attributes in parallel through multiple threads. If there are no differences, end. If there are differences, generate an SQL statement for updating the database and execute the update until the end;

[0009] If it meets the table partitioning configuration, automatically assign a table name to the partitioned table, and judge whether there is a partitioned table mapping table. If there is already a partitioned table mapping table, check whether there are differences between the database column attributes and index attributes in parallel through multiple threads. If there is no partitioned table mapping table, generate an SQL statement for updating the database and execute the update until the end.

[0010] As a preferred solution, between the steps of generating the SQL statement for updating the database and executing the update until completion, there is also a step of determining whether it is the first time to generate the sub-table mapping table: if it is not the first time to generate the sub-table mapping table, then execute the update in parallel with multiple threads until completion; if it is the first time to generate the sub-table mapping table, then add the mapping to the sub-table mapping table in parallel with multiple threads.

[0011] As a preferred solution, the specific steps of generating the SQL statement for updating the database and executing the update are as follows:

[0012] Call the built-in interfaces for addition, deletion, query, and modification;

[0013] Determine whether there is a sub-table configuration. If there is no sub-table configuration, directly generate the SQL statement for updating the database and execute the update. If there is a sub-table configuration, query whether there are sub-table records in the sub-table mapping table. If there are sub-table records, obtain the sub-table name of the record and generate the SQL statement for updating the database to execute the update. If a new sub-table name is obtained, insert a record into the sub-table mapping, and randomly call the weighted random sub-table method to generate the SQL statement for updating the database and execute the update.

[0014] As a preferred solution, after the step of calling the built-in interfaces for addition, deletion, query, and modification, it is also necessary to determine whether it is a batch operation. If it is not a batch operation, it remains unchanged. If it is a batch operation, when determining whether there is a sub-table configuration, it needs to be grouped by the sub-table key and executed in a loop for each group.

[0015] As a preferred solution, the weighted random sub-table method includes the following steps:

[0016] Generate an array numbered by table name according to the number of sub-tables;

[0017] According to the optional weight parameters notAllow (not allowed), highest (high probability), lowest (low probability), divide them into three groups, namely defaultArr (the specified default group), highestArr (high probability group), and lowestArr (low probability group);

[0018] Take the absolute value of the Hash value of the sub-table key value;

[0019] Take a random number of the absolute value and generate a default value, the starting number of the low probability interval;

[0020] Randomly obtain a generated sub-table name from the selected group.

[0021] As a preferred solution, the P6Spy component is also loaded in the above steps, and through the function of intercepting SQL statements, automatic printing is realized.

[0022] This application also provides a terminal, and the terminal includes:

[0023] At least one processor; and,

[0024] A memory communicatively connected to the at least one processor; wherein,

[0025] The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the random smoothing sub - table method as described above.

[0026] This application also provides a computer - readable storage medium, on which a computer program is stored, and when the program is executed by a processor, it implements the random smoothing sub - table method as described above.

[0027] The beneficial effects of a random smoothing sub - table method disclosed in the present invention are as follows: Annotation configurations are pre - associated with database tables to be mapped in a configuration file for scanning. Database tables are randomly scanned through annotation configuration items. It is determined whether the scanned object conforms to the sub - table configuration. If not, it is checked whether there are differences between the database column attributes and index attributes in a multi - thread parallel manner. If there are no differences, the process ends. If there are differences, an SQL statement for updating the database is generated and executed until the end. If it conforms to the sub - table configuration, a sub - table name is automatically assigned, and it is determined whether there is a sub - table mapping table. If there is already a sub - table mapping table, it is checked whether there are differences between the database column attributes and index attributes in a multi - thread parallel manner. If there is no sub - table mapping table, an SQL statement for updating the database is generated and executed until the end. By associating annotation configuration items to scan database tables (entity classes), it realizes fully automatic multi - thread parallel updating of the database table structure, automatic sub - table, and significantly improves development efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 is a schematic diagram of the directory file of a random smoothing sub - table method of the present invention.

[0029] Figure 2 is a diagram showing the usage mode of automatic table updating and sub - table of a random smoothing sub - table method of the present invention.

[0030] Figure 3 is a diagram showing the introduction method of a random smoothing sub - table method of the present invention.

[0031] Figure 4 is a flowchart of establishing a sub - table mapping of a random smoothing sub - table method of the present invention.

[0032] Figure 5 is a flowchart of automatic sub - table of a random smoothing sub - table method of the present invention.

[0033] Figure 6 is a flowchart of a weighted random sub - table method in a random smoothing sub - table method of the present invention.

[0034] Figure 7 It is the effect diagram of calling the method in the P6Spy component to print logs in a random smoothing sub - table method of the present invention. Specific embodiments

[0035] The following further elaborates and explains the present invention in conjunction with specific embodiments and the accompanying drawings of the specification:

[0036] Please refer to Figure 1 , a random smoothing sub - table method, which pre - associates annotation configurations with database tables to be mapped in a configuration file. Under the database path, BaseAutoUpdateTable is for automatically creating tables and updating table structures. BaseTable, BaseColumn are annotation for table configuration attributes, BaseIndex is an annotation for index attribute definition, BaseShardingTable is an annotation for sub - table configuration attributes, BaseSqlBuilder is a class for weighted random sub - table algorithm and Sql construction, and BaseMapper is an interface for built - in methods.

[0037] Among them, the annotation configuration specifically updates the table structure automatically according to the annotation, including the custom annotation BaseTable, whose attributes are name (the corresponding table name in the database), indexes (the corresponding indexes in the database, which includes name: index name, columns: index columns, indexType: index type), shardingTable (sub - table attributes, which includes column: sub - table field, size: sub - table field, notAllow: sub - tables not allowed to be added, highest: high - priority sub - tables, lowest: low - priority sub - tables), and BaseColumn, whose attributes are datatype (the corresponding database data type), defaultValue (default value), allowNull (whether it can be null), comment (remark), length (data length).

[0038] Please refer to Figure 2 The database tables (entity classes) that the annotation configuration needs to map in the configuration file, with quickcode.mysql.enable = true, quickcode.threadPool.enable = true, and quickcode.scan.package = "the configured package path", can obtain the functions of automatically creating tables and updating table structures and automatic migration.

[0039] Please refer to Figure 3 In the project, adding this code means executing the method provided in this embodiment. The specific steps of the random smoothing sub - table method are as follows (please refer to Figure 4 ):

[0040] Scan the database table according to the annotation configuration item.

[0041] Determine whether the scanned object conforms to the sharding table configuration. If not, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If there are no differences, end. If there are differences, generate an SQL statement to update the database and execute the update until the end.

[0042] If it conforms to the sharding table configuration, automatically assign the sharding table name, and determine whether there is a sharding table mapping table. If there is already a sharding table mapping table, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If there is no sharding table mapping table, generate an SQL statement to update the database and execute the update until the end.

[0043] Scan the database table (entity class) by associating the annotation configuration item, realize fully automatic multi-threaded parallel update of the database table structure, automatic sharding, and significantly improve the development efficiency.

[0044] Preferably, between the step of generating an SQL statement to update the database and executing the update until the end, there is also a step of whether it is the first time to generate a sharding table mapping table: If it is not the first time to generate a sharding table mapping table, execute the update in a multi-threaded parallel manner until the end. If it is the first time to generate a sharding table mapping table, add the mapping to the sharding table mapping table in a multi-threaded parallel manner. The initial sharding automatically establishes the mapping without manual migration, which can greatly reduce the error probability.

[0045] Please refer to Figure 5 , where the specific steps of generating an SQL statement to update the database and executing the update are as follows:

[0046] Call the built-in add, delete, query, and modify interfaces.

[0047] Determine whether there is a sharding table configuration. If not, directly generate an SQL statement to update the database and execute the update. If there is, query whether there is a sharding record in the sharding table mapping table. If there is, obtain the sharding table name of the record and generate an SQL statement to update the database and execute the update. If a new sharding table name is obtained, insert a record into the sharding table mapping and randomly call the weighted random sharding method to generate an SQL statement to update the database and execute the update. New records are automatically sharded according to the weight, and other data operations automatically use the routing table without feeling.

[0048] Preferably, after the step of calling the built-in add, delete, query, and modify interfaces, it is also necessary to determine whether it is a batch operation. If it is not a batch operation, keep it unchanged. If it is a batch operation, when determining whether there is a sharding table configuration, it needs to be grouped by the sharding key and executed in a loop for each group.

[0049] The above steps are specifically executed as follows:

[0050] When the Bean writeSqlSessionFactory is loaded at project startup, it will be automatically called:

[0051] The BaseAutoUpdateTable.parallelScanDatabase method obtains all classes through the scan path specified by quickcode.scan.package in the configuration file, and then loops through each one to determine whether the BaseTable annotation exists. If it doesn't, it skips; if it does, it determines whether the name of its sub-property conforms to the naming convention. If it doesn't, it reports an error indicating that the table name is illegal. If it does, it obtains all the field properties and index properties configured according to the annotation and stores them temporarily in the TableProperty (table property) object, and also puts them into the TableProperty collection list list.

[0052] Then, it determines whether sharding is enabled based on the shardingTable property (the number of shards is greater than one and the sharding field is not empty). If sharding is enabled, it reads the notAllow, highest, and lowest properties. If there are duplicate properties among them, it reports an error indicating that duplicate configuration is not allowed. If the configured number is greater than the maximum number, it reports an error indicating that the specified sharding exceeds the maximum sharding. If the field selected for sharding is not in the valid properties, it reports an error indicating that the sharding key does not exist. Then, it constructs the TableProperty properties for each shard, also puts them into the TableProperty collection list list, and adds a record to shardingTableName.

[0053] After scanning is completed for all paths, it determines whether shardingTableName is empty.

[0054] If it is not empty, it means that there are configured shards. If there is a sharding configuration, it reads the built-in sharding table mapping BaseShardingTableMapping and constructs the TableProperty properties, which are also put into the TableProperty collection list list.

[0055] The TableProperty collection list is divided into CorePoolSize groups of TableProperty collection lists sharinglist according to the number of threads CorePoolSize in the system-built common thread pool. Next, the sharinglist is divided into CorePoolSize groups of TableProperty collection lists and executed in parallel: loop through the TableProperty collection list. For a single TableProperty, first query the corresponding table properties in the database. If the table does not exist in the database, directly construct a complete table creation command. If the table exists in the database, then sequentially determine whether the currently configured index is consistent with the one queried in the database. If they are inconsistent and there is a same name, first construct a deletion command and then an addition command. If they are inconsistent and have different names, directly construct an addition command.

[0056] When determining whether the database column properties are exactly the same as those configured in the BaseColumn (basic field) annotation (whether they are empty, data type, default value, remarks, data length), if they are inconsistent, construct a modification command. If the configuration in the BaseColumn annotation does not exist in the database, construct a single property addition command, and the addition position is after the last property in the class properties after excluding all parent classes.

[0057] After all commands are constructed, group them by creating a new database column for the table, deleting indexes, and adding indexes, and then execute them sequentially in parallel (divided into CorePoolSize (core connection pool size) groups of SQL command collection lists and executed in parallel). After execution, automatically update the table structure and automatically establish sub-tables to complete. For sub-tables, if there is no data in the built-in sub-table mapping table and the data in the sub-table is not zero, query and deduplicate according to the sub-table key and insert data into the built-in sub-table mapping table to complete the initial automatic migration of sub-table data (construct the sub-table mapping).

[0058] The newly added records are automatically calculated for sub-tables by weight, and other data operations automatically use the routing table without feeling. Then, obtain the specific properties configured in the BaseTable through the class specified by the current Mapper (mapping) generic type. Determine whether sub-tables are enabled according to the shardingTable (sub-table) property (the number of sub-tables is greater than one and the sub-table field is not empty). If sub-tables are enabled, first query in the sub-table mapping table. If it exists, return the specific routed sub-table. If it does not exist, call the weight random sub-table algorithm.

[0059] It should be noted that for non-new operations, there is no step of randomly distributing tables by call weight. For batch addition, batch modification, and batch deletion, there is an additional step of grouping by the table sharding key value and looping through each group for execution. This is to achieve the purpose that the specific routing operation of table sharding does not need to be concerned about during the call.

[0060] Please refer to Figure 6 , the method of randomly distributing tables by weight includes the following steps:

[0061] Generate an array of table names according to the number of sharded tables.

[0062] According to the optional weight parameters notAllow (not allowed), highest (high probability), lowest (low probability), divide them into three groups, namely defaultArr (the specified default group), highestArr (high probability group), and lowestArr (low probability group).

[0063] The sharding key value takes the absolute value of the Hash value.

[0064] Take a random number of the absolute value and generate a default value, the starting number of the low probability interval (specific judgment conditions are as Figure 6 ).

[0065] Randomly obtain a generated sharded table name in the selected group.

[0066] The specific method: First, according to the specified number of sharded tables from 0 to the number of sharded tables - 1, and after excluding those specified by notAllow, put them into the candidate array in turn. If the candidate is empty, an error will be reported indicating that the sharded table cannot be calculated. Then, the candidate is put into the lists highestArr and lowestArr according to the sharded tables specified by highest and lowest, and the remaining unassigned ones are all put into defaultArr.

[0067] Take the value of the sharding key of the record to be newly added currently, obtain its hashCode value, and calculate its absolute value sharding (remainder value / sharding key value). Then, calculate a random number random through sharding, calculate the starting number of the largest interval highestInt (sharding * 0.8), and calculate the intermediate value defaultInt (sharding * 0.5).

[0068] When random is greater than defaultInt and also greater than highestInt, if lowestArr is not empty, select lowestArr; if it is empty, check if highestArr is empty. If highestArr is not empty, select highestArr; otherwise, select defaultArr. When random is not greater than defaultInt, select defaultArr; otherwise, check for emptiness in sequence and select highestArr and lowestArr. When random is not greater than defaultInt and highestArr is not empty, select highestArr; otherwise, select lowestArr and defaultArr in sequence. Then, obtain a certain element from the selected Arr through a random number, which is the weighted random score table at the final calculation. When the number of elements in highestArr, lowestArr, and defaultArr is the same, based on 1 million random tests, the percentage specified by highest is 50%, the percentage specified by lowest is 15%, and the remaining unspecified (defaultArr) is 35%. After obtaining the sub-table name, construct the new SQL according to the sub-table name and complete the addition operation through JDBC call. The weight ratio response strategy configuration is as Figure 2 , and the main application scenario is in the case of a large amount of data, which is convenient for controlling the write volume of sub-table data and greatly reduces the pressure on the database table.

[0069] Please refer to Figure 7 , in the implementation steps provided in this embodiment (such as Figure 3 ), the P6Spy component is also loaded. Through the function of intercepting SQL statements, automatic printing is realized. The specific line number where the automatic printing method is called, and the method request parameters when the automatic printing reports an error are convenient for troubleshooting online problems. In other words, introducing the ability to obtain automatic printing and a visual operation method will make it more convenient for operation and maintenance personnel to troubleshoot errors.

[0070] Specifically, for the specific line number where the automatic printing operation is executed, through the P6Spy component and the function of intercepting SQL statements, before its execution, obtain the array of the current thread call stack information: Thread.currentThread().getStackTrace(). The SQL statement of mybatis is implemented through the dynamic proxy of Jdk.

[0071] Therefore, only need to intercept the array subscript with the call class name JdkDynamicAopProxy and move it two positions backward to obtain the specific class name and line number of the method that calls the Mapper.

[0072] This application also provides a terminal, and the terminal includes:

[0073] At least one processor; and,

[0074] a memory communicatively connected to the at least one processor; wherein,

[0075] the memory stores instructions executable by the at least one processor, and when the instructions are executed by the at least one processor, the at least one processor is enabled to execute the random smoothing table partitioning method as described above.

[0076] This application also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the random smoothing table partitioning method as described above is implemented.

[0077] The present invention provides a random smoothing table partitioning method, which pre-associates annotation configurations with database tables to be mapped in a configuration file for scanning. Randomly scan database tables through annotation configuration items. Determine whether the scanned object conforms to the table partitioning configuration. If it does not conform, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If there are no differences, end. If there are differences, generate an SQL statement for updating the database and execute the update until the end. If it conforms to the table partitioning configuration, automatically assign a table name for the partitioned table, and determine whether there is a partitioned table mapping table. If there is already a partitioned table mapping table, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If there is no partitioned table mapping table, generate an SQL statement for updating the database and execute the update until the end. By associating annotation configuration items to scan database tables (entity classes), it realizes fully automatic multi-threaded parallel update of the database table structure, automatic table partitioning, and significantly improves development efficiency.

[0078] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the protection scope of the present invention. Although the present invention has been described in detail with reference to the preferred embodiments, those of ordinary skill in the art should understand that the technical solutions of the present invention can be modified or equivalently replaced without departing from the essence and scope of the technical solutions of the present invention.

Claims

1. A random smoothing sub - table method, characterized in that, Pre-associate the annotation configuration with the database tables to be mapped in the configuration file, which specifically includes the following steps: Scan the database tables according to the annotation configuration items; Determine whether the scanned object conforms to the sharding table configuration. If it does not conform, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If there are no differences, end the process. If there are differences, generate an SQL statement to update the database and execute the update until the end; If it conforms to the sharding table configuration, automatically assign a sharding table name, and determine whether there is a sharding table mapping table. If the sharding table mapping table already exists, check whether there are differences between the database column attributes and index attributes in a multi-threaded parallel manner. If the sharding table mapping table does not exist, generate an SQL statement to update the database and execute the update until the end; The specific steps for generating the SQL statement to update the database and executing the update are as follows: Call the built-in interfaces for addition, deletion, query, and modification; Determine whether there is a sharding table configuration. If not, directly generate an SQL statement to update the database and execute the update. If there is a sharding table configuration, query whether there are sharding records in the sharding table mapping. If there are, obtain the sharding table name of the record and generate an SQL statement to update the database and execute the update. If a new sharding table name is obtained, insert a record into the sharding table mapping and randomly call the weighted random sharding method to generate an SQL statement to update the database and execute the update; The weighted random sharding method includes the following steps: Generate an array numbered by table name according to the number of sharding tables; Divide into three groups according to the optional weight parameters notAllow (indicating not allowed), highest (indicating high probability), and lowest (indicating low probability), namely defaultArr as the specified default group, highestArr as the specified high probability group, and lowestArr as the specified low probability group; The sharding key value takes the absolute value of the Hash value; Take a random number of the absolute value and generate a default value and the starting number of the low probability interval; Randomly obtain a generated sharding table name in the selected group.

2. The random smoothing sub - table method according to claim 1, characterized in that Between the steps of generating the SQL statement to update the database and executing the update until the end, there is also a step of whether it is the first time to generate the sharding table mapping table: If it is not the first time to generate the sharding table mapping table, execute the update in a multi-threaded parallel manner until the end. If it is the first time to generate the sharding table mapping table, add the mapping to the sharding table mapping in a multi-threaded parallel manner.

3. A random smoothing sub-table method as claimed in claim 1, wherein After the step of calling the built-in interfaces for addition, deletion, query, and modification, it is also necessary to determine whether it is a batch operation. If it is not a batch operation, keep it unchanged. If it is a batch operation, when determining whether there is a sharding table configuration, group by the sharding key and execute in a loop for each group.

4. A random smoothing sub - table method according to any one of claims 1 - 3, characterized in that, The P6Spy component is also loaded in the above steps, and automatic printing is realized through the function of intercepting SQL statements.

5. A terminal, characterized in that, The terminal includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the random smooth sharding method as described in any one of claims 1-4.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by a processor, it implements the random smoothing sub-table method described in any one of claims 1-4.

Images