Data table partitioning method and device, storage medium, and computer device

Abstract

The application discloses a data table partition method and device, a storage medium and a computer device, relates to the technical field of databases and the financial field, and mainly aims to solve the problem of high data table partition time and resource cost. Mainly comprising the following steps: in response to a data table partition request of target business data, a first data table in which the target business data is located is determined from a database; a second data table is created based on target business data attribute information, preset business data partition attributes and preset partition parameters, the second data table comprises a target table partition and at least one non-target table partition; a partition exchange process is performed on the target table partition in the first data table and the second data table, the second table name of the second data table after the partition exchange process is updated to the first table name of the first data table, and a target partition data table of the target business data is obtained. The application is mainly used for converting a non-partition table into a partition table.

Description

Technical Field

[0001] This invention relates to the field of database technology and the financial field, and in particular to a data table partitioning method and apparatus, storage medium, and computer equipment. Background Technology

[0002] With the development of database technology, more and more databases are being used in various industries, especially e-commerce, finance, and healthcare. Every day, a large amount of business data needs to be stored in different business data tables in the database. As new business data is continuously injected, the space occupied by the data tables is getting larger and larger, and the performance of data table access will also reach a bottleneck. How to reduce the space occupied by the database and improve the access performance has become an urgent problem to be solved.

[0003] Partitioning data tables to achieve partitioned storage can effectively archive data and release storage space, thus solving the above problems. However, existing data table partitioning methods usually require partitioning the data table offline. During the table partitioning process, normal business operations cannot access the data table content, affecting the normal operation of the business and resulting in high time and resource costs for table partitioning. Summary of the Invention

[0004] In view of this, the present invention provides a data table partitioning method and apparatus, storage medium, and computer equipment, the main purpose of which is to solve the problems of existing table partitioning processes affecting business data access and incurring high time and resource costs.

[0005] According to one aspect of the present invention, a data table partitioning method is provided, comprising:

[0006] In response to a data table partitioning request for target business data, the first data table containing the target business data is determined from the database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information.

[0007] A second data table is created based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition.

[0008] The target table partitions in the first data table and the second data table are subjected to partition swapping, and the second table name of the second data table after partition swapping is updated to the first table name of the first data table, thereby obtaining the target partition data table of the target business data.

[0009] Further, after updating the second table name of the second data table after the partition exchange processing to the first table name of the first data table to obtain the target partition data table of the target business data, the method further includes:

[0010] The first data table after the data exchange is deleted;

[0011] When a request for new business data is received, the new business data is stored in the corresponding non-target table partition based on the first attribute information of the new business data.

[0012] Furthermore, after storing the new business data in the corresponding non-target table partition based on the attribute information of the new business data, the method further includes:

[0013] In response to the triggering of preset business data cleanup conditions, obtain the second attribute information of the target business data to be cleaned;

[0014] At least one target cleanup table partition is determined from the target partition data table based on the second attribute information;

[0015] The target cleanup table partition is physically deleted to free up its storage space.

[0016] Furthermore, the step of creating a second data table based on the data attributes of the target business data, the preset business data partition attributes, and the preset partition parameters includes:

[0017] Based on the target attributes of the target business data and the preset business data partition attributes, the partition constraint information of the first table is determined, wherein the target attributes are the business data attributes corresponding to the preset business data partition attributes;

[0018] Based on the preset business data partition attributes and the preset partition parameters, at least one second table partition constraint information is determined;

[0019] A target table partition is created based on the partition constraints of the first table, and a non-target table partition is created based on the partition constraints of the second table, resulting in a second data table.

[0020] Furthermore, after determining the first data table containing the target business data from the database, the method further includes:

[0021] Configure a composite index primary key based on the preset business data partition attributes and the index information of the first data table;

[0022] Update the primary key of the first data table to the primary key of the composite index.

[0023] Furthermore, before creating the second data table based on the preset business data partition attributes and the preset partition parameters, the method further includes:

[0024] Perform data validity checks, partition attribute checks, and index extension attribute checks on the business data in the first data table;

[0025] If the data validity detection, partition attribute detection, and index extension attribute detection all pass, then the query statements and business data volume of the first data table whose query frequency is higher than the preset query frequency threshold are obtained for query statement processing and data volume detection after the partition exchange.

[0026] If any of the data validity check, partition attribute check, or index extended attribute check fails, a warning message is generated to indicate that the data table partition is abnormal.

[0027] Furthermore, the method also includes:

[0028] After obtaining the target partition data table of the target business data, perform data validity checks on the business data in the target partition data table;

[0029] Based on the data volume of the partition exchange, the data volume of the business data in the target partition data table is detected;

[0030] If both the data validity check and the data volume check pass, the query statement is solidified based on the preset business data partition attributes.

[0031] According to another aspect of the present invention, a data table partitioning device is provided, comprising:

[0032] The determination module is used to determine the first data table where the target business data is located from the database in response to the data table partitioning request of the target business data. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information.

[0033] A creation module is used to create a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition.

[0034] The exchange module is used to perform partition exchange processing on the target table partitions of the first data table and the second data table, and update the second table name of the second data table after the partition exchange processing to the first table name of the first data table, so as to obtain the target partition data table of the target business data.

[0035] Furthermore, the device also includes:

[0036] The first deletion module is used to delete the first data table after the data exchange.

[0037] The storage module is used to store the new business data into the corresponding non-target table partition based on the first attribute information of the new business data when the update module receives a request for new business data.

[0038] Furthermore, the device also includes:

[0039] The first acquisition module is used to acquire the second attribute information of the target business data to be cleaned in response to the triggering of preset business data cleanup conditions.

[0040] The determining module is further configured to determine at least one target cleanup table partition from the target partition data table based on the second attribute information;

[0041] The second deletion module is used to physically delete the target cleanup table partition in order to release the storage space of the target cleanup table partition.

[0042] Furthermore, the creation module includes:

[0043] The first determining unit is used to determine the partition constraint information of the first table based on the target attribute of the target business data and the preset business data partition attribute, wherein the target attribute is the business data attribute corresponding to the preset business data partition attribute;

[0044] The second determining unit is used to determine at least one second table partition constraint information based on the preset business data partition attributes and the preset partition parameters.

[0045] A creation unit is used to create a target table partition based on the partition constraint information of the first table, and to create a non-target table partition based on the partition constraint information of the second table, thereby obtaining a second data table.

[0046] Furthermore, the device also includes:

[0047] The configuration module is used to configure the composite index primary key based on the preset business data partition attributes and the index information of the first data table;

[0048] The update module is used to update the primary key of the index of the first data table to the primary key of the composite index.

[0049] Furthermore, the device also includes:

[0050] The first detection module is used to perform data validity detection, partition attribute detection, and index extension attribute detection on the business data in the first data table.

[0051] The second acquisition module is used to acquire the query statements and the amount of business data in the first data table whose query frequency is higher than a preset query frequency threshold if the detection results of the data validity detection, the partition attribute detection, and the index extension attribute detection are all passed, so as to be used for query statement processing and data volume detection after the partition exchange.

[0052] The generation module is used to generate a warning message indicating data table partitioning anomalies if any of the data validity detection, partition attribute detection, or index extended attribute detection results in failure.

[0053] Furthermore, the device also includes:

[0054] The second detection module is used to perform data validity detection on the business data in the target partition data table after obtaining the target partition data table of the target business data;

[0055] The third detection module is used to perform data volume detection on the business data in the target partition data table based on the data volume of the partition exchange;

[0056] The solidification module is used to solidify the query statement based on the preset business data partition attributes if the results of the data validity detection and the data volume detection are both passed.

[0057] According to another aspect of the present invention, a storage medium is provided, wherein at least one executable instruction is stored therein, the executable instruction causing a processor to perform an operation corresponding to the data table partitioning method described above.

[0058] According to another aspect of the present invention, a computer device is provided, comprising: a processor, a memory, a communication interface, and a communication bus, wherein the processor, the memory, and the communication interface communicate with each other via the communication bus;

[0059] The memory is used to store at least one executable instruction, which causes the processor to perform the operation corresponding to the data table partitioning method described above.

[0060] By employing the above-described technical solutions, the technical solutions provided by the embodiments of the present invention have at least the following advantages:

[0061] This invention provides a data table partitioning method, apparatus, storage medium, and computer device. In an embodiment of this invention, in response to a data table partitioning request for target business data, a first data table containing the target business data is determined from a database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. A second data table is created based on the target business data attribute information, the preset business data partitioning attributes, and the preset partitioning parameters. The second data table includes a target table partition and at least one non-target table partition. Partition exchange processing is performed between the first data table and the target table partition in the second data table, and the second table name of the partition-exchanged second data table is updated to the first table name of the first data table, resulting in a target partition data table for the target business data. By exchanging data from the original data table to the partition table, data is transferred without doubling the storage space. Furthermore, data table partitioning can be achieved without affecting normal business data access, significantly reducing the impact of table partitioning on normal business operations, thereby effectively reducing the time and resource costs of table partitioning.

[0062] The above description is merely an overview of the technical solution of the present invention. In order to better understand the technical means of the present invention and to implement it in accordance with the contents of the specification, and in order to make the above and other objects, features and advantages of the present invention more apparent and understandable, specific embodiments of the present invention are described below. Attached Figure Description

[0063] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings:

[0064] Figure 1 A flowchart of a data table partitioning method provided by an embodiment of the present invention is shown;

[0065] Figure 2 A flowchart of another data table partitioning method provided by an embodiment of the present invention is shown;

[0066] Figure 3 This invention provides a flowchart of another data table partitioning method according to an embodiment of the invention.

[0067] Figure 4 This diagram illustrates a data table partitioning device according to an embodiment of the present invention.

[0068] Figure 5 A schematic diagram of the structure of a computer device provided in an embodiment of the present invention is shown. Detailed Implementation

[0069] Exemplary embodiments of the present disclosure will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.

[0070] Existing data table partitioning methods typically require partitioning the data table offline. During the partitioning process, normal business operations cannot access the data table content, affecting normal business operations and resulting in high time and resource costs for table partitioning. This invention provides a data table partitioning method, such as... Figure 1 As shown, the method includes:

[0071] 101. In response to the data table partitioning request of the target business data, determine the first data table where the target business data is located from the database.

[0072] In this embodiment of the invention, the database is an Oracle relational database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. The target business data is internal business data of a fintech company. This target business data is stored in a data table within the financial business database. When a request to partition the data table containing the target business data is received, the first data table used to store the target business data is retrieved from the database based on the target business data attribute information. This first data table is a non-partitioned table. The target business data attribute information may include the data upload ID of the target business data, the business type corresponding to the data, the financial customer attributes corresponding to the data, etc., and this embodiment of the invention does not impose specific limitations.

[0073] It should be noted that the preset business data partition attributes constrain the attribute basis of table partitions. These attributes are set based on the business archiving and periodic cleanup requirements of the target business data and can be time-based, customer-based, project-based, etc. For business data with large volumes and high update frequencies, the preset business data partition attributes can be configured as time-based, meaning the data table is partitioned according to the time attribute. The preset partition parameters constrain the partitioning rules of the table partitions and are also set based on the business archiving and periodic cleanup requirements of the target business data. For business data with large volumes and high update frequencies, the preset partition parameters can be configured as a single calendar day; for business data with moderate volumes, the preset partition parameters can be configured as a month. For example, when the partition attribute is time-based, the preset partition parameters can be a month, seven days, or a single calendar day, meaning each table partition stores business data generated in different months, different weeks, and different calendar days. The settings for preset business data partition attributes and preset partition parameters can be customized according to specific application requirements; this embodiment of the invention does not impose specific limitations. Configuring different table partitioning methods based on the attribute characteristics of the target business data allows the partitioned data tables to better meet business needs and ensure business data access performance.

[0074] 102. Create a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters.

[0075] In this embodiment of the invention, the second data table is a newly created blank data table, including a target table partition and at least one non-target table partition. The target table partition stores existing target business data from the first data table. The non-target table partition stores subsequently added business data, and can be one, two, or more. The constraint information of the target table partition is determined based on the target business data attribute information and preset business data partition attributes. For example, if the preset business data partition attribute is a time attribute, and the target business data was all generated before May 2022, then the constraint information of the target table partition is that the creation time is less than April 30, 2022. The constraint information of the non-target table partition is determined based on the preset business data partition attributes and preset partition parameters. For example, if the preset business data partition attribute is a time attribute and the preset partition parameter is one month, then the non-target table partition can include a May table partition, a June table partition, ... a December table partition, etc. By creating partitioned data tables that include target table partitions and non-target table partitions, business data from existing non-partitioned data tables can be migrated to partitioned data tables without affecting normal business data access. Furthermore, when new business data is injected, effective partitioning and archiving can be performed, thus ensuring normal access to business data while implementing data table partitioning.

[0076] 103. Perform partition swapping on the target table partitions of the first data table and the second data table, and update the second table name of the second data table after the partition swapping to the first table name of the first data table, to obtain the target partition data table of the target business data.

[0077] In this embodiment of the invention, to store the target business data in a partitioned data table, the target business data in the first data table is swapped to the target table partition in the second data table. Simultaneously, empty data in the target table partition of the second data table is swapped to the first data table. The target table partition in the second data table after the swap stores all the target business data, while the first data table after the swap is an empty table without any business data. After the partition swap, to ensure normal access to the business data, the table name of the first data table after the partition swap is renamed, and the table name of the second data table after the partition swap is renamed back to the first table name. This allows the target business data to be found based on the original table name during business data access. The renaming of the first table name can be a random table name; for example, if the first table name of the first data table is T1, it can be renamed to T1_OLD. This embodiment of the invention does not impose specific limitations.

[0078] It's important to note that since renaming a data table takes only seconds, online data table partitioning is supported. Business operations are largely unaware of the table partitioning process where target business data is moved from the first table to the second. Therefore, it's possible to convert target business data from a non-partitioned table to a partitioned table without affecting business data access, significantly reducing business waiting time and effectively minimizing the impact of table partitioning on business operations, as well as reducing the time and resource costs of table partitioning. Furthermore, by exchanging target business data with empty data through partition swapping, it doesn't require twice the data storage space, resulting in lower storage space requirements. This makes the table partitioning method suitable for databases with various storage space conditions, thus improving its applicability.

[0079] In one embodiment of the present invention, for further illustration and limitation, such as Figure 2 As shown, after step 103, which involves updating the second table name of the second data table after the partition exchange process to the first table name of the first data table to obtain the target partition data table for the target business data, the method further includes:

[0080] 201. Delete the first data table after the data exchange.

[0081] 202. When a request for new business data is received, the new business data is stored in the corresponding non-target table partition based on the first attribute information of the new business data.

[0082] In this embodiment of the invention, after the partition exchange is completed, the business data in the first data table is completely exchanged to the target table partition of the second data table. The first data table is a blank table, and its original name is also transferred to the second data table. Therefore, the first data table can be deleted, and subsequent business data access will be performed on the target partition data table instead of the first data table. When new business data is added, it is stored in the corresponding non-target table partition based on the attributes of the new business data and the constraint information of different table partitions in the target partition data table. For example, if the table partition attribute is time, and the generation time of the new business data is 20220801, and the non-target table partitions include the first table partition corresponding to 202207, the second table partition corresponding to 202208, and the third table partition corresponding to 202209, then the new business data will be stored in the second table partition. After obtaining the target partition data table, subsequent business data access and insertion are performed based on different table partitions of the target partition data table, thereby achieving the effect of archiving business data.

[0083] In one embodiment of the present invention, for further illustration and limitation, such as Figure 3 As shown, after storing the new business data in the corresponding non-target table partition based on the attribute information of the new business data in step 202, the method further includes:

[0084] 301. In response to the triggering of preset business data cleanup conditions, obtain the second attribute information of the target business data to be cleaned;

[0085] 302. Determine at least one target cleanup table partition from the target partition data table based on the second attribute information;

[0086] 303. Physically delete the target cleanup table partition to release the storage space of the target cleanup table partition.

[0087] In this embodiment of the invention, the preset business data cleanup condition can be a preset business data volume threshold or a preset cleanup time. For example, the preset business data volume threshold is 1T, and the preset cleanup time is 00:00 on the 1st of each month. The second attribute information is the common attribute information of the business data to be cleaned. For example, the second attribute information is: time attribute 202101-202106. When the business data volume reaches the preset business data volume threshold, or the current time meets the preset cleanup time, the drop command is executed on the table partition corresponding to 202101-202106 in the target partition data table. For example, alter table table_name drop partition partition_name, to delete the corresponding table partition and release storage space.

[0088] It's important to note that if business data is stored in a non-partitioned table, the `drop` command cannot be executed on the business data during normal business operation; only the `delete` command can be used. However, deleting business data using the `delete` command does not truly release memory space because the number of blocks used before and after deletion remains unchanged. By partitioning the data table, different business data is stored in different table partitions. When it's necessary to clean up invalid business data in some table partitions, the corresponding table partitions can be physically deleted using the `drop` command while the business is running. This ensures that the business continues normally while effectively releasing database space.

[0089] In one embodiment of the present invention, for further explanation and limitation, step 102, which involves creating a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters, includes:

[0090] Based on the preset business data partition attributes, target attribute information is extracted from the target business data attribute information, and the first table partition constraint information is configured based on the target attribute information.

[0091] Based on the preset business data partition attributes and the preset partition parameters, the second table partition constraint information is configured for at least one second table partition.

[0092] A target table partition is created based on the partition constraints of the first table, and a non-target table partition is created based on the partition constraints of the second table, resulting in a second data table.

[0093] In this embodiment of the invention, table partition constraint information is information that constrains the attribute range of the corresponding table partition. For example, a table partition may only store business data corresponding to customer A, or a table partition may only store business data generated between August 1, 2022 and August 31, 2022. Target attribute information is attribute information in the target business data attribute information that is consistent with the preset business data partition attribute. For example, if the target business data attribute information includes time attributes, customer attributes, and project attributes, and the preset business data partition attribute is time attributes, then all time attribute information in the target business data attribute information is extracted. The created second data table is used to store existing business data in the first data table, as well as all subsequently added business data. Existing business data in the first data table needs to be transferred to the second data table as the content of a table partition. Therefore, firstly, target attribute information consistent with the preset business data partition attribute is extracted from the multiple attribute information included in the target business data attribute information, and then the first table partition constraint information is configured according to the target attribute information. For example, if the target attribute information of all business data is distributed between March 5, 2021 and August 20, 2022, then the data before August 20, 2021, will be configured as the first table partition constraint information for the target table partition. Subsequent new business data will be stored in the non-target table partition according to the second table partition constraint information. The specific partitioning method of the table can be configured according to preset business data partition attributes and preset partition parameters. For example, partitioning by time attribute, creating a table partition every 7 days. By creating a second data table including the target table partition and the non-target table partition, the requirement for partitioning the data table is met. At the same time, it enables separate archiving of existing business data, ensuring normal access to business data.

[0094] In one embodiment of the present invention, for further explanation and limitation, after determining the first data table containing the target business data from the database in step 101, the method further includes:

[0095] Configure a composite index primary key based on the preset business data partition attributes and the index information of the first data table.

[0096] Update the primary key of the first data table to the primary key of the composite index.

[0097] In this embodiment of the invention, the index information is the original primary key information used to access the first data table, such as ID. The composite index primary key is a combined primary key that includes the original primary key and preset business data partition attributes, such as ID + create time. By transforming the index primary key of the first data table into a composite primary key that includes partition attribute information, the index access method of the business data in the first data table is converted into an index access method suitable for partitioned tables. When business data in the first data table is exchanged to a table partition of the second data table, the access to the business data is not affected by the business data migration, thereby ensuring the normal operation of the business during the table partitioning process.

[0098] In one embodiment of the present invention, for further explanation and limitation, before step 102, which involves creating a second data table based on the preset business data partition attributes and the preset partition parameters, the method further includes:

[0099] Perform data validity checks, partition attribute checks, and index extension attribute checks on the business data in the first data table.

[0100] If the data validity check, partition attribute check, and index extension attribute check all pass, then the query statements and business data volume of the first data table whose query frequency is higher than the preset query frequency threshold are obtained for use in query statement processing and data volume detection after the partition exchange.

[0101] If any of the data validity check, partition attribute check, or index extended attribute check fails, a warning message is generated to indicate that the data table partition is abnormal.

[0102] In this embodiment of the invention, data validity detection checks whether the business data in the first data table is valid, such as whether the index is invalid or contains null values. It also prevents confusion after conversion to the second data table regarding whether the timeliness issue stems from the conversion or is a problem with the original business data. Partition attribute detection checks whether the field corresponding to the partition attribute is null. For example, if the partition attribute is time, it checks whether `create_time` contains null values. If it is null, the business data cannot be accessed correctly after changing the primary key. Index extension attribute detection checks the performance of the extended primary key, confirming whether the current business data supports extending the primary key to a composite primary key, ensuring that the primary key extension does not affect existing business field attributes. When all the above checks pass, it indicates that the current business data supports table partitioning. To ensure that the SQL performance of the table remains unchanged before and after partitioning, frequently accessed SQL statements are retrieved. The specific frequency of SQL statements required can be limited by setting a preset query frequency threshold. This preset query frequency threshold can be customized according to application requirements; this embodiment of the invention does not impose a specific limitation. To facilitate verification of whether data loss has occurred after partition swapping, it is also necessary to obtain the total data volume of all business data in the first data table, i.e., the data volume of business data.

[0103] In one embodiment of the present invention, for further explanation and limitation, the method further includes:

[0104] After obtaining the target partition data table of the target business data, perform data validity checks on the business data in the target partition data table;

[0105] Based on the data volume of the partition exchange, the data volume of the business data in the target partition data table is detected;

[0106] If both the data validity check and the data volume check pass, the query statement is then solidified based on the preset business data partition attributes. 。

[0107] In this embodiment of the invention, after the partition swap is completed, the validity of the business data needs to be checked. Simultaneously, it is confirmed whether the amount of valid business data before and after the partition swap is the same, and whether the total amount of business data is the same, to ensure the availability and integrity of the business data after the partition swap. After confirming that the business data is complete and valid after the partition swap, the SQL statements are solidified, that is, the query statements are solidified into queries based on a preset business data partition attribute + ID index, to ensure normal access to the business data.

[0108] This invention provides a data table partitioning method. In an embodiment of this invention, in response to a data table partitioning request for target business data, a first data table containing the target business data is determined from a database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. A second data table is created based on the target business data attribute information, the preset business data partitioning attributes, and the preset partitioning parameters. The second data table includes a target table partition and at least one non-target table partition. Partition exchange processing is performed between the target table partitions in the first and second data tables, and the second table name of the partition-exchanged second data table is updated to the first table name of the first data table, resulting in a target partition data table for the target business data. This data exchange transfers data from the original data table to the partition table without requiring double the storage space. Furthermore, it enables data table partitioning without affecting normal business data access, significantly reducing the impact of table partitioning on normal business operations, thereby effectively reducing the time and resource costs of table partitioning.

[0109] Furthermore, as a response to the above Figure 1 The implementation of the method shown in this invention provides a data table partitioning device, such as... Figure 4 As shown, the device includes:

[0110] The determination module 41 is used to determine the first data table where the target business data is located from the database in response to the data table partitioning request of the target business data. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information.

[0111] The creation module 42 is used to create a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition.

[0112] The exchange module 43 is used to perform partition exchange processing on the target table partitions of the first data table and the second data table, and update the second table name of the second data table after the partition exchange processing to the first table name of the first data table, so as to obtain the target partition data table of the target business data.

[0113] Furthermore, the device also includes:

[0114] The first deletion module is used to delete the first data table after the data exchange.

[0115] The storage module is used to store the new business data into the corresponding non-target table partition based on the first attribute information of the new business data when the update module receives a request for new business data.

[0116] Furthermore, the device also includes:

[0117] The first acquisition module is used to acquire the second attribute information of the target business data to be cleaned in response to the triggering of preset business data cleanup conditions.

[0118] The determining module 41 is further configured to determine at least one target cleanup table partition from the target partition data table based on the second attribute information;

[0119] The second deletion module is used to physically delete the target cleanup table partition in order to release the storage space of the target cleanup table partition.

[0120] Furthermore, the creation module 42 includes:

[0121] The first determining unit is used to determine the partition constraint information of the first table based on the target attribute of the target business data and the preset business data partition attribute, wherein the target attribute is the business data attribute corresponding to the preset business data partition attribute;

[0122] The second determining unit is used to determine at least one second table partition constraint information based on the preset business data partition attributes and the preset partition parameters.

[0123] A creation unit is used to create a target table partition based on the partition constraint information of the first table, and to create a non-target table partition based on the partition constraint information of the second table, thereby obtaining a second data table.

[0124] Furthermore, the device also includes:

[0125] The configuration module is used to configure the composite index primary key based on the preset business data partition attributes and the index information of the first data table;

[0126] The update module is used to update the primary key of the index of the first data table to the primary key of the composite index.

[0127] Furthermore, the device also includes:

[0128] The first detection module is used to perform data validity detection, partition attribute detection, and index extension attribute detection on the business data in the first data table.

[0129] The second acquisition module is used to acquire the query statements and the amount of business data in the first data table whose query frequency is higher than a preset query frequency threshold if the detection results of the data validity detection, the partition attribute detection, and the index extension attribute detection are all passed, so as to be used for query statement processing and data volume detection after the partition exchange.

[0130] The generation module is used to generate a warning message indicating data table partitioning anomalies if any of the data validity detection, partition attribute detection, or index extended attribute detection results in failure.

[0131] Furthermore, the device also includes:

[0132] The device further includes:

[0133] The second detection module is used to perform data validity detection on the business data in the target partition data table after obtaining the target partition data table of the target business data;

[0134] The third detection module is used to perform data volume detection on the business data in the target partition data table based on the data volume of the partition exchange;

[0135] The solidification module is used to solidify the query statement based on the preset business data partition attributes if the results of the data validity detection and the data volume detection are both passed.

[0136] This invention provides a data table partitioning device. In an embodiment of this invention, in response to a data table partitioning request for target business data, a first data table containing the target business data is determined from a database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. A second data table is created based on the target business data attribute information, the preset business data partitioning attributes, and the preset partitioning parameters. The second data table includes a target table partition and at least one non-target table partition. Partition exchange processing is performed between the first data table and the target table partition in the second data table, and the second table name of the partition-exchanged second data table is updated to the first table name of the first data table, resulting in a target partition data table for the target business data. By exchanging data from the original data table to the partition table, data can be transferred without doubling the storage space. Furthermore, data table partitioning can be achieved without affecting normal business data access, significantly reducing the impact of table partitioning on normal business operations, thereby effectively reducing the time and resource costs of table partitioning.

[0137] According to one embodiment of the present invention, a storage medium is provided, the storage medium storing at least one executable instruction, the computer-executable instruction being able to execute the data table partitioning method in any of the above method embodiments.

[0138] Figure 5 The diagram illustrates a structural schematic of a computer device according to an embodiment of the present invention. The specific embodiments of the present invention do not limit the specific implementation of the computer device.

[0139] like Figure 5 As shown, the computer device may include: a processor 502, a communication interface 504, a memory 506, and a communication bus 508.

[0140] The processor 502, communication interface 504, and memory 506 communicate with each other via communication bus 508.

[0141] Communication interface 504 is used to communicate with other network elements such as clients or other servers.

[0142] The processor 502 is used to execute program 510, specifically to execute the relevant steps in the above-described data table partitioning method embodiment.

[0143] Specifically, program 510 may include program code that includes computer operation instructions.

[0144] Processor 502 may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention. The computer device includes one or more processors, which may be processors of the same type, such as one or more CPUs; or they may be processors of different types, such as one or more CPUs and one or more ASICs.

[0145] Memory 506 is used to store program 510. Memory 506 may include high-speed RAM memory, and may also include non-volatile memory, such as at least one disk storage device.

[0146] Specifically, program 510 can be used to cause processor 502 to perform the following operations:

[0147] In response to a data table partitioning request for target business data, the first data table containing the target business data is determined from the database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information.

[0148] A second data table is created based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition.

[0149] The target table partitions in the first data table and the second data table are subjected to partition swapping, and the second table name of the second data table after partition swapping is updated to the first table name of the first data table, thereby obtaining the target partition data table of the target business data.

[0150] It is obvious to those skilled in the art that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Optionally, they can be implemented using computer-executable program code, thereby storing them in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those presented herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular hardware and software combination.

[0151] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A data table partitioning method, characterized in that, include: In response to a data table partitioning request for target business data, the first data table containing the target business data is determined from the database. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. A second data table is created based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition. The non-target table partition is determined based on the preset business data partition attributes and the preset partition parameters and is used to store newly added business data. Perform partition swapping on the target table partitions of the first data table and the second data table, and update the second table name of the second data table after the partition swapping to the first table name of the first data table to obtain the target partition data table of the target business data. After storing the new business data in the corresponding non-target table partition, the method further includes: in response to the triggering of preset business data cleanup conditions, obtaining the second attribute information of the target cleanup business data; determining at least one non-running table partition from the target partition data table as the target cleanup table partition based on the second attribute information; and physically deleting the target cleanup table partition to release the storage space of the target cleanup table partition. Before creating the second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters, the following steps are also included: Perform data validity checks, partition attribute checks, and index extension attribute checks on the business data in the first data table; If the data validity detection, partition attribute detection, and index extension attribute detection all pass, then the query statements and business data volume of the first data table whose query frequency is higher than the preset query frequency threshold are obtained for query statement processing and data volume detection after the partition exchange. If any of the data validity check, partition attribute check, or index extended attribute check fails, a warning message is generated to indicate that the data table partition is abnormal.

2. The method according to claim 1, characterized in that, After updating the second table name of the second data table after the partition swapping process to the first table name of the first data table to obtain the target partition data table of the target business data, the method further includes: The first data table after the partition swap process is deleted; When a request for new business data is received, the new business data is stored in the corresponding non-target table partition based on the first attribute information of the new business data.

3. The method according to claim 1, characterized in that, The step of creating a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters includes: Based on the preset business data partition attributes, target attribute information is extracted from the target business data attribute information, and the first table partition constraint information is configured based on the target attribute information; Based on the preset business data partition attributes and the preset partition parameters, at least one second table partition is configured with second table partition constraint information; A target table partition is created based on the partition constraints of the first table, and a non-target table partition is created based on the partition constraints of the second table, resulting in a second data table.

4. The method according to claim 1, characterized in that, After determining the first data table containing the target business data from the database, the method further includes: Configure a composite index primary key based on the preset business data partition attributes and the index information of the first data table; Update the primary key of the first data table to the primary key of the composite index.

5. The method according to claim 1, characterized in that, The method further includes: After obtaining the target partition data table of the target business data, perform data validity checks on the business data in the target partition data table; Based on the data volume of the partition exchange, the data volume of the business data in the target partition data table is detected; If both the data validity check and the data volume check pass, the query statement is solidified based on the preset business data partition attributes.

6. A data table partitioning device, characterized in that, The apparatus is used to perform the data table partitioning method as described in any one of claims 1-5, comprising: The determination module is used to determine the first data table where the target business data is located from the database in response to the data table partitioning request of the target business data. The data table partitioning request carries preset business data partitioning attributes, preset partitioning parameters, and target business data attribute information. A creation module is used to create a second data table based on the target business data attribute information, the preset business data partition attributes, and the preset partition parameters. The second data table includes a target table partition and at least one non-target table partition. The exchange module is used to perform partition exchange processing on the target table partitions of the first data table and the second data table, and update the second table name of the second data table after the partition exchange processing to the first table name of the first data table, so as to obtain the target partition data table of the target business data.

7. A storage medium storing at least one executable instruction that causes a processor to perform an operation corresponding to the data table partitioning method as described in any one of claims 1-5.

8. A computer device, comprising: The processor, memory, communication interface, and communication bus are provided, wherein the processor, memory, and communication interface communicate with each other via the communication bus. The memory is used to store at least one executable instruction that causes the processor to perform the operation corresponding to the data table partitioning method as described in any one of claims 1-5.

Images