Range partitioning methods, media, products and equipment for data in databases

Abstract

The present invention provides a range partitioning method, medium, product and device for data in a database. The above method includes: obtaining data to be partitioned; obtaining partition boundary information in a preset partition table; judging whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table; if it belongs to an automatic partition segment, judging whether the data to be partitioned belongs to an existing partition in the automatic partition segment; if not, taking the partition boundary information as a starting point and determining the remote target boundary information step by step according to the preset partition interval; determining the proximal target boundary information according to the existing partition in the automatic partition segment; creating a target partition according to the remote target boundary information and the proximal target boundary information, and storing the data to be partitioned in the target partition. This method can directly compare with the partition boundary information to determine which partition segment the data to be partitioned belongs to without performing additional sorting operations when inserting data, thereby reducing the resource consumption of the database.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a range partitioning method, medium, product and device for data in a database. Background Art

[0002] In the database, as the business expands, the amount of data accumulates more and more. In order to better utilize hardware resources and improve performance, it is necessary to partition the data. According to the different partitioning methods used, they are divided into range partitioning, hash partitioning and list partitioning. Data related to numbers and dates accounts for a large proportion, making range partitioning more widely used. When new data needs to be recorded in the partition table, these data often do not meet the existing partitions. At this time, the DBA (Database Administrator) needs to maintain the partition table, which brings challenges to the DBA's work.

[0003] To deal with the above scenarios, automatic partitioning is generally chosen. When new data does not meet the existing partition layout, a new partition is automatically created to store the data. However, this solution generally chooses to mark whether the partition is a declared partition or an automatically created partition by partition type. Each time a DML operation is performed on the partition table, all the boundaries of the partition are sorted, and the largest boundary information in the declarative partition is found as the critical point of the automatic partition. When the partition table has a large number of partitions, the sorting operation will consume more computing resources. At the same time, when performing DDL operations on the partition table, it is necessary to synchronously maintain the partition type and adjust all partitions smaller than the operation partition boundary to the same type as the declared partition. This will modify n system table records and generate more logs and file writes. Summary of the invention

[0004] In view of the above problems, the present invention proposes a method, medium, product and device for range partitioning of data in a database that overcomes the above problems or at least partially solves the above problems.

[0005] An object of the present invention is to improve the execution efficiency of automatic partitioning.

[0006] A further object of the present invention is to reduce the resource consumption of the database.

[0007] In particular, the present invention provides a range partitioning method for data in a database, comprising:

[0008] Get the data to be partitioned;

[0009] Get the partition boundary information in the preset partition table. The preset partition table is used to record the boundary information of each range partition. The partition boundary information is the boundary information between the declared partition segment and the automatic partition segment. The declared partition segment is the partition segment to which the partition created in advance belongs, and the automatic partition segment is the partition segment to which the partition automatically created by the database belongs.

[0010] Determine whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table according to the partition boundary information;

[0011] In the case where the data to be partitioned belongs to the automatic partitioning segment, determining whether the data to be partitioned belongs to an existing partition in the automatic partitioning segment;

[0012] If not, then taking the partition boundary information as the starting point and determining the remote target boundary information corresponding to the data to be partitioned step by step according to the preset partition interval, the remote target boundary information is the boundary information that can accommodate the data to be partitioned;

[0013] Determine the near-end target boundary information according to the existing partitions in the automatic partitioning segment, where the near-end target boundary information is the farthest boundary information in the existing partitions in the automatic partitioning segment;

[0014] A new target partition is created according to the far-end target boundary information and the near-end target boundary information, and the data to be partitioned is stored in the target partition.

[0015] Optionally, after the step of determining whether the data to be partitioned belongs to an existing partition in the automatic partition segment, the following step is further included:

[0016] In the case where the data to be partitioned belongs to an existing partition in the automatic partitioning segment, determining a target automatic partition to which the data to be partitioned belongs;

[0017] Determine whether the data to be partitioned belongs to the last preset partition interval in the target automatic partition;

[0018] If yes, the data to be partitioned is stored in the target automatic partition.

[0019] Optionally, after the step of determining whether the data to be partitioned belongs to the last preset partition interval in the target automatic partition, the step further includes:

[0020] In the case where the data to be partitioned does not belong to the last preset partition interval in the target automatic partition, the inner target boundary information of the target automatic partition is obtained, where the inner target boundary information is the boundary information of the target automatic partition close to the declared partition segment;

[0021] Determine outer target boundary information corresponding to the data to be partitioned in the target automatic partition, where the outer target boundary information is boundary information in the target automatic partition that can accommodate the data to be partitioned;

[0022] Create a new target partition according to the inner target boundary information and the outer target boundary information, and store the data to be partitioned into the newly created target partition;

[0023] The inner target boundary information corresponding to the target automatic partition is modified to the outer target boundary information.

[0024] Optionally, the step of determining outer target boundary information corresponding to the data to be partitioned in the target automatic partitioning includes:

[0025] Get the preset partition interval;

[0026] Taking the inner target boundary information as the starting point, the outer target boundary information corresponding to the data to be partitioned is searched step by step according to the preset partition interval.

[0027] Optionally, after the step of determining whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table, the method further includes:

[0028] In the case where the data to be partitioned belongs to the declared partition segment, determining the target declared partition corresponding to the data to be partitioned in the declared partition segment;

[0029] Store the data to be partitioned into the target declared partition.

[0030] Optionally, the operation method of presetting the partition table includes:

[0031] Obtaining a target operation instruction corresponding to a preset partition table, where the target operation instruction is used to operate a declared partition recorded in the preset partition table or to convert a partition in an automatic partition segment into a declared partition;

[0032] Determine the partition to be operated according to the target operation instruction;

[0033] Obtain boundary information to be operated corresponding to the partition to be operated, and determine whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment;

[0034] If not, the partition to be operated is operated according to the target operation instruction.

[0035] Optionally, after the step of determining whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment, the method further includes:

[0036] When the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment, remote boundary information to be operated that is farthest from the partition boundary information in the boundary information to be operated is obtained, and the partition boundary information is replaced with the remote boundary information to be operated;

[0037] The partition to be operated is operated according to the target operation instruction.

[0038] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned methods for range partitioning of data in a database are implemented.

[0039] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any of the above-mentioned methods for range partitioning of data in a database are implemented.

[0040] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned methods for range partitioning of data in a database when executing the machine executable program.

[0041] The range partitioning method of data in a database of the present invention first obtains data to be partitioned; then obtains partition boundary information in a preset partition table, wherein the preset partition table is used to record boundary information of each range partition, the partition boundary information is boundary information between a declared partition segment and an automatic partition segment, the declared partition segment is a partition segment to which a partition created in advance belongs, and the automatic partition segment is a partition segment to which a partition automatically created by a database belongs; according to the partition boundary information, it is judged whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table; when the data to be partitioned belongs to an automatic partition segment, it is judged whether the data to be partitioned belongs to an existing partition in the automatic partition segment; if not, taking the partition boundary information as a starting point and according to the preset partition interval, the remote target boundary information corresponding to the data to be partitioned is determined step by step, the remote target boundary information being boundary information that can accommodate the data to be partitioned; the near-end target boundary information is determined according to the existing partitions in the automatic partition segment, the near-end target boundary information being the farthest boundary information in the existing partitions in the automatic partition segment; a new target partition is created according to the remote target boundary information and the near-end target boundary information, and the data to be partitioned is stored in the target partition. Through this method, on the one hand, no additional sorting operations are required when inserting data. The data to be partitioned can be directly compared with the partition boundary information to determine which partition segment it belongs to, thereby reducing the resource consumption of the database. On the other hand, it can ensure that the partition layout is reasonable and the data is stored in an orderly manner, providing strong support for data query, analysis and the stable operation of the overall database.

[0042] Further, in the range partitioning method of data in the database of the present invention, the operation method for the preset partition table includes: obtaining the target operation instruction corresponding to the preset partition table, the target operation instruction is used to operate the declared partition recorded in the preset partition table or to convert the partition in the automatic partition segment into the declared partition; determining the partition to be operated according to the target operation instruction; obtaining the boundary information to be operated corresponding to the partition to be operated, and judging whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment; if not, operating the partition to be operated according to the target operation instruction; in the case where the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment, obtaining the remote boundary information to be operated farthest from the partition boundary information in the boundary information to be operated, and replacing the partition boundary information with the remote boundary information to be operated; operating the partition to be operated according to the target operation instruction. Through this method, when operating the partition, only the boundary information needs to be recorded in the partition table, and the information modification of the partition itself is not involved, so that only one system record needs to be modified when the partition table is maintained, avoiding traversing the partition, reducing the time of locking the partition table, and not adding write locks to the partition, reducing disk write operations, and reducing the blocking of concurrent services.

[0043] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0045] Figure 1 is a flow chart of a method for range partitioning of data in a database according to an embodiment of the present invention;

[0046] Figure 2 is a flow chart of a method for range partitioning of data in a database according to another embodiment of the present invention;

[0047] Figure 3 is a flow chart of a method for range partitioning of data in a database according to yet another embodiment of the present invention;

[0048] Figure 4 It is a schematic diagram of the operation flow of presetting a partition table in a range partitioning method for data in a database according to an embodiment of the present invention;

[0049] Figure 5 is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0050] Figure 6 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0051] Figure 7 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0052] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0053] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein can be considered as a sequenced list of executable instructions for implementing logical functions, which can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0054] In the current database field, the commonly used implementation technology for automatic partitioning is to add attribute information to the system table to record the partition type. When new data is inserted, the partition type is used to determine whether to add a partition with an interval size. When the partition table is maintained, each partition is maintained. However, this has three defects:

[0055] 1. Each partition needs to be recorded, which increases the metadata overhead;

[0056] 2. When maintaining the partition table, you need to maintain the type of each partition synchronously. If there are a large number of partitions, the application will be blocked;

[0057] 3. When adding a new partition, you need to traverse the existing partitions to find the largest boundary partition or sort the partitions by boundary size in advance.

[0058] In order to eliminate the above defects, improve the automatic partitioning performance, and reduce the partition table maintenance resources, the present invention records the partition boundaries on the partition table. Because there is only one partition table and n partitions, only one system record needs to be modified during partition table maintenance, thereby improving the partition table maintenance performance and reducing storage overhead.

[0059] The present invention provides a range partitioning method for data in a database. Figure 1FIG. 1 is a flow chart of a method for range partitioning of data in a database according to an embodiment of the present invention. Figure 1 As shown, the range partitioning method for data in the database includes at least the following steps S101 to S107.

[0060] Step S101, obtain the data to be partitioned. Among them, the data to be partitioned usually comes from new record information generated by various operations of the business system. For example, in the e-commerce platform, new order data (including order number, order time, product information, customer information, amount and other field contents), customer registration information, or newly entered purchase order details and inventory change records in the enterprise resource management system (ERP) may become the data to be partitioned that needs to be partitioned and stored. These data exist in the form of records, and their various attribute values ​​will become the basis for subsequent judgment of partition attribution. In this step, the method of obtaining the data to be partitioned can generally be active input by the user, or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide the method of obtaining the data to be partitioned according to actual conditions.

[0061] Step S102, obtaining partition boundary information in the preset partition table. The preset partition table is generally used to record the boundary information of each range partition. The partition boundary information is the boundary information between the declared partition segment and the automatic partition segment. The declared partition segment is the partition segment to which the partition created by the pre-declaration belongs, and the automatic partition segment is the partition segment to which the partition automatically created by the database belongs. The partition boundary information clarifies the division boundary between the declared partition segment and the automatic partition segment. For example, in a database divided by time range, the declared partition segment may include fixed time period partitions such as "the first quarter of 2024 (January 1-March 31)" and "the second quarter of 2024 (April 1-June 30)", which are used to store data that meets the characteristics of the corresponding time interval. These partitions have been explicitly created during the initial design of the database or subsequent specific business adjustments. The automatic partition segment is to cope with the dynamic changes of data. When the new data exceeds the range of the declared partition segment or does not meet other rules of the declared partition, the database automatically creates the interval to which the new partition belongs according to certain rules. Its boundaries are flexible and change with the continuous insertion of new data and the automatic adjustment of the partition.

[0062] Step S103, determine whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table. In some optional embodiments, the judgment method can generally be to compare the attribute value related to the partition basis in the data to be partitioned (such as time data is judged by date attribute, numerical data is judged by size range attribute, etc.) with the partition boundary information recorded in the preset partition table. For example, for a database partitioned by student score range, if the data to be partitioned is a student's score of 85 points, and the declared partition segments in the preset partition table have partitions such as "60-70 points" and "70-80 points", and the starting boundary of the automatic partition segment is set to be greater than 80 points, then by comparison, it can be determined that the score of 85 points belongs to the automatic partition segment, thereby determining which partition rule the data should be further processed according to.

[0063] In the current database field, partition types are generally used to mark whether a partition is created declaratively or automatically. Each time a DML operation is performed on a partition table, all boundaries of the partition are sorted, and the largest boundary information in the declarative partition is found as the critical point for automatic partitioning. When a partition table has a large number of partitions, the sorting operation consumes more computing resources. The method of the present invention records the partition boundary information to the partition table, and the DML operation does not need to perform additional sorting operations. The data can be determined by direct comparison to which partition segment it belongs.

[0064] Step S104, if it is determined in step S103 that the data belongs to the automatic partitioning segment, it is determined whether the data to be partitioned belongs to an existing partition in the automatic partitioning segment. After determining that the data belongs to the automatic partitioning segment, it is checked whether it belongs to an existing partition in order to clarify whether the current partition structure can accommodate the data. Although the automatic partitioning segment can automatically create a new partition, if the existing partition can meet the data storage requirements, there is no need to create a new partition, which can avoid unnecessary increase in the number of partitions and waste of resources, and maintain the relative simplicity and efficiency of the partition structure.

[0065] Assume that the automatic partition segment is partitioned according to product sales, and the existing partitions are "100-200 pieces", "200-300 pieces" and other intervals (the partition interval is 100 pieces), and the product sales corresponding to the data to be partitioned is 350 pieces. By comparison, it can be known that the data belongs to the automatic partition segment and does not belong to the existing partition. Further operations related to creating a new partition are required.

[0066] Step S105, if the determination in step S104 is no, the remote target boundary information corresponding to the data to be partitioned is determined step by step according to the preset partition interval, starting from the partition boundary information. The remote target boundary information is the boundary information that can accommodate the data to be partitioned. When it is determined that the data to be partitioned does not belong to the existing partition in the automatic partition segment, it is necessary to find a suitable new partition boundary for it to create a new partition to store the data. For example, if the preset partition interval is 10 days, the starting time corresponding to the partition boundary information is October 1, 2024, and the time corresponding to the data to be partitioned is October 15, 2024, then starting from October 1, 2024, consider the intervals such as "October 1, 2024-October 11, 2024" and "October 11, 2024-October 21, 2024" in sequence until an interval that can accommodate the time data of October 15, 2024 is found (here, "October 11, 2024-October 21, 2024"). The upper boundary of this interval is the remote target boundary information, which determines a key boundary range of the new partition. In this way, it can be ensured that the newly created partition can accurately accommodate the data to be partitioned, so that the partition system can dynamically adapt to new data situations, continuously expand and improve to meet the needs of continuous growth and changes in business data, while also maintaining the consistency of the partition interval and the regularity of the partition structure, which is convenient for subsequent data management and query operations. Those skilled in the art can determine the specific value of the preset partition interval according to actual conditions.

[0067] Step S106, determine the proximal target boundary information based on the existing partitions in the automatic partition segment. Among them, the proximal target boundary information is the farthest boundary information in the existing partitions in the automatic partition segment. When creating a new partition, in addition to determining the far-end target boundary information to define the upper limit of the range of the new partition, it is also necessary to consider the existing partition situation, and clarify the adjacent relationship between the new partition and the existing partition and the overall partition order by determining the proximal target boundary information. For example, the farthest boundary of the existing partition in the automatic partition segment is to a product with a sales value of 500 pieces, then the boundary corresponding to the 500 pieces is the proximal target boundary information, and the newly created partition will be reasonably connected with the existing partition in order, so that the partition layout of the entire automatic partition segment remains logically coherent and orderly.

[0068] Step S107, create a new target partition according to the remote target boundary information and the proximal target boundary information, and store the data to be partitioned in the target partition. Based on the remote target boundary information and the proximal target boundary information determined in step S105 and step S106, the specific scope of the new partition can be clarified, thereby formally creating a new target partition. For example, the remote target boundary information corresponds to the time range "October 21, 2024", and the proximal target boundary information corresponds to the deadline "October 1, 2024" of the previous adjacent partition, then a new partition can be created to store the data of the corresponding time period. Then, the data to be partitioned is stored in this newly created target partition, completing the entire data partition storage process, so that the data can be properly placed according to reasonable partitioning rules, ensuring that the database partition structure is continuously optimized and adjusted with the dynamic changes of the data, and always adapting to the needs of business development.

[0069] Through this method, on the one hand, no additional sorting operations are required when inserting data. The data to be partitioned can be directly compared with the partition boundary information to determine which partition segment it belongs to, thereby reducing the resource consumption of the database. On the other hand, it can ensure that the partition layout is reasonable and the data is stored in an orderly manner, providing strong support for data query, analysis and the stable operation of the overall database.

[0070] Figure 2 FIG. 1 is a flow chart of a method for range partitioning of data in a database according to another embodiment of the present invention. Figure 2 As shown, the range partitioning method for data in the database includes at least the following steps S201 to S211.

[0071] Step S201, obtaining data to be partitioned.

[0072] Step S202: Obtain partition boundary information from a preset partition table.

[0073] Step S203, determining whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table.

[0074] Step S204, when it is determined in step S203 that the data belongs to the automatic partitioning segment, it is determined whether the data to be partitioned belongs to an existing partition in the automatic partitioning segment.

[0075] Step S205, if the judgment in step S204 is yes, determine the target automatic partition to which the data to be partitioned belongs. When it is determined that the data to be partitioned belongs to a certain partition in the automatic partition segment, further determine the specific target automatic partition to which it belongs. This step is to more accurately locate the storage location of the data in the automatic partition system, so as to facilitate more detailed operations on the partition in the future, such as determining the interval position of the data in the partition, etc., and also provide accurate partition pointing for subsequent data management, query and other operations.

[0076] Step S206, determine whether the data to be partitioned belongs to the last preset partition interval in the target automatic partition. Different interval positions may mean different impacts on the partition structure, and corresponding appropriate operations need to be taken to maintain the rationality of the partition and the orderliness of data storage. Different interval positions mean that the distribution of data in the partition is different, and correspondingly different processing methods need to be adopted to maintain the rationality of the partition and the orderliness of data storage. For example, in the target automatic partition "50-100 pieces" partitioned by inventory quantity (assuming the partition interval is 10 pieces), if the inventory quantity corresponding to the data to be partitioned is 98 pieces, it is determined whether it falls within the last interval (90-100 pieces), which will determine whether the data is directly stored in the future or whether the partition needs to be adjusted to better accommodate the data.

[0077] Step S207, if the judgment in step S206 is yes, the data to be partitioned is stored in the target automatic partition. If the data to be partitioned belongs to the last preset partition interval in the target automatic partition, it means that the data just falls within the reasonable range originally set for the partition, and there is no need to make additional adjustments or modifications to the partition, and the data can be directly stored in this target automatic partition. This ensures the normal storage of data, maintains the stability of the partition structure, simplifies the data storage operation process, and also complies with the conventional processing logic for data falling within a reasonable range in the partition design.

[0078] Step S208, if the determination in step S206 is no, the inner target boundary information of the target automatic partition is obtained. The inner target boundary information is the boundary information on the side close to the declared partition segment in the target automatic partition. When the data to be partitioned does not belong to the last preset partition interval in the target automatic partition, the inner target boundary information of the target automatic partition needs to be obtained. This inner target boundary information is the boundary situation on the side closer to the declared partition segment from the perspective of the target automatic partition, and it can serve as an important starting reference point for subsequent partition adjustment operations. For example, in the target automatic partition "1000-2000 yuan" partitioned by sales (assuming the partition interval is 200 yuan), if the sales corresponding to the data to be partitioned is 1300 yuan, and the data does not belong to the last interval (1800-2000 yuan), then the boundary of the partition close to the declared partition segment, that is, 1000 yuan, is the inner target boundary information, which reflects the boundary situation of the current data relative to the starting position in the partition, and provides a basic basis for subsequent operations such as finding a suitable new partition boundary.

[0079] Step S209, determining outer target boundary information corresponding to the data to be partitioned in the target automatic partition, wherein the outer target boundary information is boundary information in the target automatic partition that can accommodate the data to be partitioned.

[0080] In some optional embodiments, step S209 may generally include: obtaining a preset partition interval; starting from the inner target boundary information, searching step by step according to the preset partition interval to obtain the outer target boundary information corresponding to the data to be partitioned.

[0081] The outer target boundary information is designed to find a suitable boundary that can accommodate the data to be partitioned within the target automatic partition range. First, obtain the preset partition interval, which is a fixed value determined during the partition design (such as the 200 yuan sales interval and the 10-piece inventory quantity interval in the previous example), which ensures the regularity and consistency of the partition structure. Then, starting from the inner target boundary information, gradually search for the appropriate outer target boundary information according to this preset partition interval. For example, in the example of the sales partition "1000-2000 yuan" (interval 200 yuan), the inner target boundary information is 1000 yuan, and the sales of the data to be partitioned is 1300 yuan. By searching step by step according to the interval of 200 yuan, it will be found that 1400 yuan is the outer target boundary information that can accommodate 1300 yuan of metadata (because 1300 yuan falls within the range of 1200-1400 yuan determined by the interval). In this way, the boundary range suitable for creating new partitions or adjusting partitions can be accurately defined according to the actual situation of the data.

[0082] Step S210, create a new target partition according to the inner target boundary information and the outer target boundary information, and store the data to be partitioned in the newly created target partition. Based on the inner target boundary information and the outer target boundary information determined in step S208 and step S209, the specific range boundary of the new partition can be clarified, and then a new target partition can be created to store the data to be partitioned. For example, if the inner target boundary information is 1000 yuan and the outer target boundary information is 1400 yuan, then a new partition "1000-1400 yuan" can be created, and then the corresponding data to be partitioned (here the data with sales of 1300 yuan) can be stored in this newly created target partition. Through such operations, the partition structure can be dynamically adjusted and expanded according to the actual situation of the data in the original partition, so that the partition system can flexibly adapt to the changing data characteristics, ensure that the data can always be stored in the corresponding partition in an orderly and reasonable manner, and improve the adaptability and effectiveness of database partition management.

[0083] Step S211, modify the inner target boundary information corresponding to the target automatic partition to the outer target boundary information. After creating a new target partition and storing the data to be partitioned, it is necessary to modify the inner target boundary information of the original target automatic partition to update it to the outer target boundary information. The purpose of this step is to maintain the consistency and rationality of the partition structure, ensure that the original partition and the newly created partition can be reasonably connected at the boundary, and avoid the confusion of partition boundaries or inconsistent data storage logic. For example, the original target automatic partition is "1000-2000 yuan". After the above steps, a new partition "1000-1400 yuan" is created and data is stored. The inner target boundary information of the original partition is 1000 yuan. It is modified to 1400 yuan, and the original partition becomes "1400-2000 yuan". In this way, the entire partition system remains orderly and complies with the preset partition rules, so that when similar data is inserted later, the partition system can still accurately determine the partition attribution of the data and perform corresponding partition adjustment operations according to the established rules, ensuring that the automatic partition mechanism continues to run stably and effectively manages data.

[0084] Through this method, the database can accurately and efficiently deal with data to be partitioned in different situations, flexibly adjust and optimize the automatic partitioning structure, thereby achieving scientific partition storage of data and improving the overall performance of the database.

[0085] Figure 3 FIG. 1 is a flow chart of a method for range partitioning of data in a database according to another embodiment of the present invention. Figure 3 As shown, the range partitioning method for data in the database at least includes the following steps S301 to S305.

[0086] Step S301, obtaining data to be partitioned.

[0087] Step S302: Obtain partition boundary information from a preset partition table.

[0088] Step S303, determining whether the data to be partitioned belongs to a declared partition segment or an automatic partition segment in the preset partition table.

[0089] Step S304, when it is determined in step S303 that it belongs to the declared partition segment, determine the target declared partition corresponding to the data to be partitioned in the declared partition segment. The declared partition segment is a set of partition ranges that are clearly defined in advance in database partition management. It is a set of partitions that are clearly defined and divided in the database design phase according to specific business rules, data attribute ranges or management strategies. Its main purpose is to store data in an organized manner so that data can be stored according to pre-defined logical classifications, which is convenient for subsequent data management, query and analysis operations. Therefore, the declared partition segment generally does not easily change the partition boundary. If the data to be partitioned belongs to the declared partition segment, the corresponding partition range can be found directly, and the data to be partitioned can be stored in the corresponding declared partition.

[0090] Step S305, storing the data to be partitioned into the target declared partition.

[0091] Through this method, the data to be partitioned can be accurately stored in the appropriate declared partition, which helps to improve the data management capabilities and usage value of the database.

[0092] Figure 4 FIG. 1 is a schematic diagram of an operation flow of presetting a partition table in a range partitioning method for data in a database according to an embodiment of the present invention. Figure 4 As shown, the operation flow of the preset partition table in the database at least includes the following steps S401 to S407.

[0093] Step S401, obtain the target operation instruction corresponding to the preset partition table. The target operation instruction is generally used to operate the declared partition recorded in the preset partition table or to convert the partition in the automatic partition segment into the declared partition. That is, perform data definition (DDL) operation on the preset partition table.

[0094] In some optional embodiments, the target operation instruction usually comes from a database administrator (DBA) or some automated data management scripts, applications, etc., and its purpose is to adjust or optimize the partition structure of the database to better adapt to changes in business needs. Common target operation instruction types cover a variety of data definition language (DDL) operations, such as creating new partitions (corresponding to new business scenarios or data classification requirements), modifying the scope of existing partitions (for example, changes in business rules make the original partition range unreasonable), deleting partitions that are no longer needed (such as some historical data partitions have expired and do not need to be retained), and converting partitions in automatic partition segments into declared partitions (perhaps after a period of data accumulation, the originally dynamically generated partitions can be fixed to form declared partitions for easy management), etc.

[0095] Step S402, determine the partition to be operated according to the target operation instruction. In some optional embodiments, the target operation instruction generally includes clear identification or condition information for locating the specific partition to be operated in the preset partition table. For example, if the target operation instruction is to modify the partition range corresponding to a specific time period, the system will search for the declared partition matching it in the preset partition table according to the time range condition given in the instruction.

[0096] Step S403: obtaining boundary information to be operated corresponding to the partition to be operated.

[0097] Step S404, determine whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment. Partition boundary information is usually used to distinguish between declared partition segments and automatic partition segments and define the relationship between each partition. Determining whether the boundary information to be operated exceeds the overall partition boundary information and belongs to the automatic partition segment is to determine whether the partition to be operated wants to divide part of the automatic partition into the declared partition segment, or only operate the partition in the declared partition segment.

[0098] Step S405, when the judgment in step S404 is no, the partition to be operated is operated according to the target operation instruction. When it is judged that the boundary information to be operated does not exceed the partition boundary information, it means that the partition to be operated does not belong to the automatic partition segment, so it is only necessary to operate the partition to be operated normally according to the target operation instruction. For example, if the target operation instruction wants to delete a partition in the declared partition segment, the database will automatically merge the deleted partition into the adjacent declared partition after the deletion, and there is no need to modify the partition boundary information. If you want to split a declared partition in the declared partition segment, you can split the original declared partition into the desired declared partition according to the specific operation instruction, which does not affect the partition boundary information.

[0099] Step S406, when the judgment in step S404 is yes, obtain the remote boundary information to be operated that is farthest from the partition boundary information in the boundary information to be operated, and replace the partition boundary information with the remote boundary information to be operated.

[0100] When it is determined that the boundary information to be operated exceeds the partition boundary information, it means that part or all of the partition to be operated belongs to the automatic partition segment. At this time, the remote boundary information to be operated that is farthest from the partition boundary information is obtained in the boundary information to be operated in order to redefine a reasonable partition range and return it to a state that conforms to the overall planning of the preset partition table. For example, in the scenario of time partitioning, the boundary of the original partition should be "March 31, 2024", but the target operation instruction needs to merge "March 31, 2024-April 31, 2024" in the automatic partition with "January 1, 2024-March 31, 2024". At this time, the partition boundary information needs to be adjusted to extend the boundary of the declared partition segment to April 31, 2024.

[0101] Step S407: operating the partition to be operated according to the target operation instruction.

[0102] Through this method, the preset partition table can flexibly respond to various partition adjustment requirements, ensure the dynamic adaptability and effectiveness of database partition management, and enable it to always provide reasonable and efficient storage and management services for business data.

[0103] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0104] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0105] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 5 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 6 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 7is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned range partitioning methods for data in a database when the computer program 11 is executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned range partitioning methods for data in a database when the computer program 11 is executed by the processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0106] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer through any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider through the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0107] For the purposes of the description of this embodiment, the computer program product 10 is a related product that includes a computer program 11. For the purposes of the description of this embodiment, the computer-readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 for use in or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer-readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the above.

[0108] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked through a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0109] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0110] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0111] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A range partitioning method for data in a database, comprising: Get the data to be partitioned; Obtaining partition boundary information in a preset partition table, wherein the preset partition table is used to record boundary information of each range partition, wherein the partition boundary information is boundary information between a declared partition segment and an automatic partition segment, wherein the declared partition segment is a partition segment to which a partition created in advance is assigned, and the automatic partition segment is a partition segment to which a partition automatically created by the database is assigned; In the case where the data to be partitioned belongs to the automatic partitioning segment, if the data to be partitioned does not belong to an existing partition in the automatic partitioning segment, remote target boundary information corresponding to the data to be partitioned is determined step by step according to a preset partitioning interval with the partition boundary information as a starting point, and the remote target boundary information is boundary information that can accommodate the data to be partitioned; Determine the near-end target boundary information according to the existing partitions in the automatic partition segment, wherein the near-end target boundary information is the farthest boundary information of the existing partitions in the automatic partition segment; Creating a target partition according to the far-end target boundary information and the near-end target boundary information, and storing the to-be-partitioned data in the target partition; In the case where the data to be partitioned belongs to an existing partition in the automatic partitioning section, determining a target automatic partition to which the data to be partitioned belongs; If the data to be partitioned does not belong to the last preset partition interval in the target automatic partition, then obtaining the inner target boundary information of the target automatic partition, the inner target boundary information being the boundary information of the target automatic partition close to the declared partition segment; Determine outer target boundary information corresponding to the data to be partitioned in the target automatic partition, wherein the outer target boundary information is boundary information in the target automatic partition that can accommodate the data to be partitioned; Create a new target partition according to the inner target boundary information and the outer target boundary information, and store the data to be partitioned into the newly created target partition; The inner target boundary information corresponding to the target automatic partition is modified to the outer target boundary information.

2. The range partitioning method for data in a database according to claim 1, wherein: After the step of determining the target automatic partition to which the to-be-partitioned data belongs, the following step further includes: In a case where the data to be partitioned belongs to the last preset partition interval in the target automatic partition, the data to be partitioned is stored in the target automatic partition.

3. The range partitioning method for data in a database according to claim 1, wherein: The step of determining the outer target boundary information corresponding to the to-be-partitioned data in the target automatic partitioning comprises: Obtaining the preset partition interval; The outer target boundary information corresponding to the to-be-partitioned data is obtained by searching step by step according to the preset partition interval, taking the inner target boundary information as a starting point.

4. The range partitioning method for data in a database according to claim 1, wherein: After the step of obtaining the partition boundary information in the preset partition table, the following step is further included: In a case where the data to be partitioned belongs to the declared partition segment, determining a target declared partition corresponding to the data to be partitioned in the declared partition segment; The data to be partitioned is stored in the target declared partition.

5. The range partitioning method for data in a database according to claim 1, wherein: The operation method of the preset partition table includes: Obtaining a target operation instruction corresponding to the preset partition table, wherein the target operation instruction is used to operate the declared partition recorded in the preset partition table or to convert the partition in the automatic partition segment into a declared partition; Determine the partition to be operated according to the target operation instruction; Acquire boundary information to be operated corresponding to the partition to be operated, and determine whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment; If not, the partition to be operated is operated according to the target operation instruction.

6. The range partitioning method for data in a database according to claim 5, wherein: After the step of determining whether the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment, the following step further includes: In the case that the boundary information to be operated exceeds the partition boundary information and belongs to the automatic partition segment, obtaining remote boundary information to be operated that is farthest from the partition boundary information in the boundary information to be operated, and replacing the partition boundary information with the remote boundary information to be operated; The partition to be operated is operated according to the target operation instruction.

7. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the steps of the method for range partitioning of data in a database according to any one of claims 1 to 6 are implemented.

8. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the method for range partitioning of data in a database according to any one of claims 1 to 6.

9. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the method for range partitioning of data in a database according to any one of claims 1 to 6 when executing the machine executable program.

Images