CN115687359A - Data table partitioning method and device, storage medium and computer equipment - Google Patents
Data table partitioning method and device, storage medium and computer equipment Download PDFInfo
- Publication number
- CN115687359A CN115687359A CN202211427639.9A CN202211427639A CN115687359A CN 115687359 A CN115687359 A CN 115687359A CN 202211427639 A CN202211427639 A CN 202211427639A CN 115687359 A CN115687359 A CN 115687359A
- Authority
- CN
- China
- Prior art keywords
- partition
- data
- target
- service data
- data table
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a data table partitioning method and device, a storage medium and computer equipment, relates to the technical field of databases and the financial field, and mainly aims to solve the problem of high time and resource cost of data table partitioning. The method mainly comprises the steps of responding to a data table partitioning request of target service data, and determining a first data table where the target service data are located from a database; creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition; and performing partition exchange processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of the target service data. The method is mainly used for converting the non-partition table into the partition table.
Description
Technical Field
The invention relates to the technical field of databases and the financial field, in particular to a data table partitioning method and device, a storage medium and computer equipment.
Background
With the development of database technology, more and more databases are applied to various industries, particularly the e-commerce industry, the financial industry, the medical service industry and the like, a large amount of business data are generated every day and need to be stored in different business data tables in the databases, and with the continuous injection of newly added business data, the space occupation of the data tables is larger and larger, the performance of data table access can also be bottleneck, the space occupation of the databases is reduced, and the improvement of the access performance becomes a problem to be solved urgently.
The data table is partitioned to realize partitioned storage of the data, the data can be effectively filed, and the storage space can be effectively released, so that the problems are solved.
Disclosure of Invention
In view of this, the present invention provides a method and an apparatus for partitioning a data table, a storage medium, and a computer device, and mainly aims to solve the problems that the existing table partitioning process affects service data access and the time and resource costs are high.
According to an aspect of the present invention, there is provided a data table partitioning method, including:
responding to a data table partition request of target service data, and determining a first data table where the target service data is located from a database, wherein the data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information;
creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition;
and performing partition exchange processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of the target service data.
Further, after the second table name of the second data table after the partition exchange processing is updated to the first table name of the first data table, and the target partition data table of the target service data is obtained, the method further includes:
deleting the first data table after the data exchange;
and when a newly added service data request is received, storing the newly added service data into the corresponding non-target table partition based on the first attribute information of the newly added service data.
Further, after the newly added service data is stored in the corresponding non-target table partition based on the attribute information of the newly added service data, the method further includes:
responding to the trigger of a preset service data cleaning condition, and acquiring second attribute information of the target cleaning service data;
determining at least one target cleaning table partition from the target partition data table according to the second attribute information;
and physically deleting the target cleaning list partition to release the storage space of the target cleaning list partition.
Further, the creating a second data table based on the data attribute of the target service data, the partition attribute of the preset service data, and the partition parameter includes:
determining first table partition constraint information based on a target attribute of the target service data and the partition attribute of the preset service data, wherein the target attribute is a service data attribute corresponding to the partition attribute of the preset service data;
determining at least one second table partition constraint information based on the preset service data partition attribute and the preset partition parameter;
and creating a target table partition based on the first table partition constraint information, and creating a non-target table partition based on the second table partition constraint information to obtain a second data table.
Further, after the first data table in which the target service data is located is determined from the database, the method further includes:
configuring a composite index main key based on the preset service data partition attribute and the index information of the first data table;
and updating the index main key of the first data table into the composite index main key.
Further, before creating the second data table based on the preset service data partition attribute and the preset partition parameter, the method further includes:
performing data validity detection, partition attribute detection and index extension attribute detection on the service data in the first data table;
if the detection results of the data validity detection, the partition attribute detection and the index extension attribute detection are all passed, acquiring query statements and data volume of service data of which the query frequency of the first data table is higher than a preset query frequency threshold value for query statement processing and data volume detection after partition exchange;
and if any detection result of the data validity detection, the partition attribute detection and the index extension attribute detection is failed, generating early warning information for prompting the abnormal partition of the data table.
Further, the method further comprises:
after a target partition data table of the target service data is obtained, performing data validity detection on the service data in the target partition data table;
performing data volume detection on the service data in the target partition data table based on the data volume exchanged by the partitions;
and if the data validity detection result and the data quantity detection result are both passed, performing curing processing on the query statement based on the preset service data partition attribute.
According to another aspect of the present invention, there is provided a data table partitioning apparatus, including:
the determining module is used for responding to a data table partition request of target service data, and determining a first data table where the target service data is located from a database, wherein the data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information;
a creating module, configured to create a second data table based on the target service data attribute information, the preset service data partition attribute, and the preset partition parameter, where the second data table includes a target table partition and at least one non-target table partition;
and the switching module is used for performing partition switching processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition switching processing to a first table name of the first data table to obtain a target partition data table of the target service data.
Further, the apparatus further comprises:
the first deleting module is used for deleting the first data table after the data exchange;
and the storage module is used for storing the newly added service data into the corresponding non-target table partition based on the first attribute information of the newly added service data when the updating module receives a request of the newly added service data.
Further, the apparatus further comprises:
the first acquisition module is used for responding to the trigger of a preset service data cleaning condition and acquiring second attribute information of the target cleaning service data;
the determining module is further configured to determine at least one target cleaning table partition from the target partition data table according to the second attribute information;
and the second deleting module is used for physically deleting the target cleaning table partition so as to release the storage space of the target cleaning table partition.
Further, the creating module includes:
a first determining unit, configured to determine first table partition constraint information based on a target attribute of the target service data and the preset service data partition attribute, where the target attribute is a service data attribute corresponding to the preset service data partition attribute;
a second determining unit, configured to determine at least one second table partition constraint information based on the preset service data partition attribute and the preset partition parameter;
and the creating unit is used for creating a target table partition based on the first table partition constraint information and creating a non-target table partition based on the second table partition constraint information to obtain a second data table.
Further, the apparatus further comprises:
the configuration module is used for configuring a composite index main key based on the partition attribute of the preset service data and the index information of the first data table;
and the updating module is used for updating the index main key of the first data table into the composite index main key.
Further, the apparatus further comprises:
the first detection module is used for carrying out data validity detection, partition attribute detection and index extension attribute detection on the service data in the first data table;
a second obtaining module, configured to obtain, if all detection results of the data validity detection, the partition attribute detection, and the index extended attribute detection pass, query statements and data amount of service data in which a query frequency of the first data table is higher than a preset query frequency threshold, so as to be used for query statement processing and data amount detection after the partition exchange;
and the generating module is used for generating early warning information for prompting the abnormal partition of the data table if any detection result of the data validity detection, the partition attribute detection and the index extension attribute detection is failed.
Further, the apparatus further comprises:
the second detection module is used for carrying out data validity detection on the service data in the target partition data table after the target partition data table of the target service data is obtained;
a third detection module, configured to perform data volume detection on the service data in the target partition data table based on the data volume exchanged by the partition;
and the curing module is used for curing the query statement based on the preset service data partition attribute if the data validity detection result and the data quantity detection result both pass.
According to another aspect of the present invention, a storage medium is provided, and at least one executable instruction is stored in the storage medium, and the executable instruction causes a processor to execute operations corresponding to the data table partitioning method.
According to still another aspect of the present invention, there is provided a computer apparatus including: the system comprises a processor, a memory, a communication interface and a communication bus, wherein the processor, the memory and the communication interface complete mutual communication through the communication bus;
the memory is used for storing at least one executable instruction, and the executable instruction enables the processor to execute the operation corresponding to the data table partitioning method.
By the technical scheme, the technical scheme provided by the embodiment of the invention at least has the following advantages:
the invention provides a data table partitioning method and device, a storage medium and computer equipment, wherein a first data table where target service data are located is determined from a database by responding to a data table partitioning request of the target service data, and the data table partitioning request carries preset service data partitioning attributes, preset partitioning parameters and target service data attribute information; creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition; the method comprises the steps of carrying out partition exchange processing on a first data table and a target table partition in a second data table, updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of target service data, exchanging data in an original data table to the partition table through data exchange, occupying no double storage space, realizing partition of the data table under the condition of not influencing normal service data access, greatly reducing influence of the table partition on normal service operation, and effectively reducing time and resource cost of the table partition.
The foregoing description is only an overview of the technical solutions of the present invention, and the embodiments of the present invention are described below in order to make the technical means of the present invention more clearly understood and to make the above and other objects, features, and advantages of the present invention more clearly understandable.
Drawings
Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are only for purposes of illustrating the preferred embodiments and are not to be construed as limiting the invention. Also, like reference numerals are used to refer to like parts throughout the drawings. In the drawings:
FIG. 1 is a flowchart illustrating a method for partitioning a data table according to an embodiment of the present invention;
FIG. 2 is a flow chart of another method for partitioning a data table according to an embodiment of the present invention;
FIG. 3 is a flow chart of a further method for partitioning a data table according to an embodiment of the present invention;
FIG. 4 is a block diagram of a data table partitioning apparatus according to an embodiment of the present invention;
fig. 5 shows a schematic structural diagram of a computer device according to an embodiment of the present invention.
Detailed Description
Exemplary embodiments of the present disclosure will be described in more detail below 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 embodied in various forms and should not be limited by 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.
The method aims at the technical problems that the existing data table partitioning method generally needs to partition the data table in an off-line state, normal service cannot access the content of the data table in the table partitioning process, normal operation of the service is influenced, and time cost and resource cost of table partitioning are high. An embodiment of the present invention provides a data table partitioning method, as shown in fig. 1, the method includes:
101. and responding to a data table partitioning request of the target service data, and determining a first data table in which the target service data is located from a database.
In the embodiment of the invention, the database is an Oracle relational database. The data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information. The target business data is the business data inside the financial science and technology enterprise. The target business data is stored in a data table in a financial business database. When a request for partitioning the data table where the target service data is located is received, a first data table used for storing the target service data is searched from a database according to the attribute information of the target service data, wherein the first data table is a non-partitioned table. The attribute information of the target service data may be information such as a data upload ID of the target service data, a service type corresponding to the data, and a financial client attribute corresponding to the data, and the embodiment of the present invention is not particularly limited.
It should be noted that the preset service data partition attribute restricts the attribute basis of the table partition, and is set for the service filing requirement and the service periodic cleaning requirement based on the target service data, and may be a time attribute, a client attribute, a project attribute, and the like. For the service data with large data volume and high updating frequency, the preset service data partition attribute can be configured as the time attribute, that is, the data table is partitioned according to the time attribute. The preset partition parameters constrain the partition rules of the table partitions and are also set for the business filing requirements and the business periodic cleaning requirements based on the target business data. For a service with a large data volume and a high update frequency, the preset partition parameter may be configured to be a natural day, and for a service data with a general data volume, the preset partition parameter may be configured to be a month. For example, when the partition attribute is a time attribute, the preset partition parameter may be one month, seven days, and one natural day, that is, each table partition stores therein service data generated in different months, service data generated in different weeks, and service data generated in different natural days. The preset service data partition attribute and the preset partition parameter may be set according to a specific application requirement, and the embodiment of the present invention is not limited specifically. Different table partitioning modes are configured based on the attribute characteristics of the target service data, so that the partitioned data table can better meet service requirements, and the service data access performance is ensured.
102. And creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter.
In the embodiment of the invention, the second data table is a newly-built blank data table and comprises a target table partition and at least one non-target table partition. The target table partition is used for storing target service data existing in the first data table. The number of the non-target table partitions is one, two or more than two, and the non-target table partitions are used for storing the subsequently added service data. The constraint information of the target table partition is determined based on the target service data attribute information and the preset service data partition attribute. For example, the preset service data partition attribute is a time attribute, the target service data are all data generated 5 months before 2022, and the constraint information of the target table partition is that the creation time is less than 20220430. And the constraint information of the non-target table partition is determined based on the preset service data partition attribute and the preset partition parameter. For example, if the attribute of the preset service data partition is a time attribute and the preset partition parameter is 1 month, the non-target table partition may include a 5-month table partition, a 6-month table partition … … month table partition, and the like. By creating the partition data table comprising the target table partition and the non-target table partition, the service data in the original non-partition data table can be migrated into the partition data table, the data access of normal services is not affected, and when newly added service data is injected, effective partition filing can be carried out, so that the normal access of the service data is ensured while the data table partition is realized.
103. And performing partition exchange processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of the target service data.
In the embodiment of the invention, in order to store the target service data into the partition data table, the target service data in the first data table is exchanged into the target table partition in the second data table, and meanwhile, the null data in the target table partition in the second data table is exchanged into the first data table. All the target service data are stored in the target table partition in the exchanged second data table, and the exchanged first data table is an empty table which does not contain the service data. After the partition exchange processing, in order to ensure the normal access of the service data, the table name of the first data table after the partition exchange processing, namely the first table name, is renamed, and the table name of the second data table after the partition exchange processing, namely the second table name, is renamed to the first table name, so that the service data access process can also find the target service data based on the original table name. The renaming of the first table name may be a random table name, for example, the first table name of the first data table is T1, and the renaming is T1_ OLD, which is not specifically limited in the embodiment of the present invention.
It should be noted that renaming the data table only needs to be completed in the second level, online data table partitioning can be supported, and the service basically has no perception on the table partitioning of the target service data from the first data table to the second data table, so that conversion of the target service data from the non-partitioned data table to the partitioned data table can be completed on the premise of not affecting service data access, data table partitioning is realized, the waiting time of the service is greatly reduced, the influence of the table partitioning on the service is effectively reduced, and the time and resource cost of the table partitioning are reduced. In addition, the target service data and the null data are exchanged in a partition exchange mode, double data storage space is not needed, the requirement on the storage space is low, the table partitioning method is suitable for databases with various storage space conditions, and therefore the applicability of table partitioning is improved.
In an embodiment of the present invention, for further explanation and limitation, as shown in fig. 2, after the step 103 updates the second table name of the second data table after the partition swap processing to the first table name of the first data table, and obtains a target partition data table of the target service data, the method further includes:
201. and deleting the first data table after the data exchange.
202. And when a new service data request is received, storing the new service data into the corresponding non-target table partition based on the first attribute information of the new service data.
In the embodiment of the invention, after partition exchange is completed, the service 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 the original table name of the first data table is also converted to the second data table, so that the first data table can be deleted, and the data tables accessed by the subsequent service data are all converted into the target partition data table by the first data table. And when new service data is stored, storing the new service data into the corresponding non-target table partition according to the attribute of the new service data and the constraint information of different table partitions to the attribute in the target partition data table. For example, the partition attribute of the table is time, the generation time of the new service data is 20220801, the non-target table partition includes a first table partition corresponding to 202207, a second table partition corresponding to 202208, and a third table partition corresponding to 202209, and then the new service data is stored in the second table partition. After the target partition data table is obtained, subsequent service data access and insertion are carried out on the basis of different table partitions of the target partition data table, so that the effect of archiving service data is achieved.
In an embodiment of the present invention, for further explanation and limitation, as shown in fig. 3, after the step 202 stores the newly added service data into the corresponding non-target table partition based on attribute information of the newly added service data, the method further includes:
301. responding to the trigger of a preset service data cleaning condition, and acquiring second attribute information of the target cleaning service data;
302. determining at least one target cleaning table partition from the target partition data table according to the second attribute information;
303. and physically deleting the target cleaning table partition to release the storage space of the target cleaning table partition.
In the embodiment of the present invention, the preset service data clearing condition may be a preset service data volume threshold, or may also be a preset clearing time, for example, the preset service data volume threshold is 1T, and the preset clearing time is 1 day zero every month. The second attribute information is common attribute information of the service data to be cleaned, for example, the second attribute information is: the time attributes 202101 to 202106, when the data volume of the service data reaches the preset service data volume threshold or the current time meets the preset clearing time, execute a drop command, for example, an individual table table _ name drop partition _ name, to the table partition corresponding to the 202101 to 202106 in the target partition data table to delete the corresponding table partition and release the storage space.
It should be noted that if the service data is stored in the non-partition table, in the normal operation process of the service, the drop command cannot be executed on the service data, and only the delete command can be executed, but deleting the service data by the delete command does not change the number of block blocks used before and after deletion, so that the memory space is not really released. By partitioning the data table, different service data are stored in different table partitions, and when invalid service data in a part of the table partitions need to be cleared, drop physical deletion can be performed on the corresponding table partitions while a service is performed. Therefore, the database space is effectively released while the normal operation of the service is ensured.
In an embodiment of the present invention, for further explanation and limitation, the creating, in step 102, a second data table based on the target service data attribute information, the preset service data partition attribute, and the preset partition parameter includes:
and extracting target attribute information from the target service data attribute information based on the preset service data partition attribute, and configuring first table partition constraint information based on the target attribute information.
And configuring second table partition constraint information for at least one second table partition based on the preset service data partition attribute and the preset partition parameter.
And creating a target table partition based on the first table partition constraint information, and creating a non-target table partition based on the second table partition constraint information to obtain a second data table.
In the embodiment of the present invention, the table partition constraint information is information for constraining the attribute range of the corresponding table partition, for example, only the service data corresponding to the a client is stored in a certain table partition, or only the service data generated during periods 20220801 to 20220831 is stored in a certain table partition. The target attribute information is attribute information which is consistent with the attribute of the preset service data partition in the target service data attribute information. For example, the target service data attribute information includes a time attribute, a client attribute, and a project attribute, and if the preset service data partition attribute is the time attribute, all time attribute information in the target service data attribute information is extracted. The created second data table is used for storing the existing service data in the first data table and all the subsequent newly added service data. The existing service data in the first data table needs to be transferred to the second data table as the content of one table partition. Therefore, the target attribute information consistent with the preset service data partition attribute is extracted from the plurality of attribute information included in the target service data attribute information, and the first table partition constraint information is configured according to the target attribute information. For example, the target attribute information of all the service data is distributed in: between times 20210305 and 20220820, 20110820 is configured as the first table partition constraint information for the target table partition. And storing the subsequent newly-added service data in the non-target table partition according to the constraint information of the second table partition. The specific table partitioning mode may be configured according to the preset service data partition attribute and the preset partition parameter, for example, partitioning according to the time attribute, and partitioning a table every 7 days. The second data table comprising the target table partition and the non-target table partition is created, so that the requirement for partitioning the data table is met, meanwhile, the existing business data is independently filed, and the normal access of the business data is guaranteed.
In an embodiment of the present invention, for further explanation and limitation, after the step 101 of determining the first data table in which the target service data is located from the database, the method further includes:
and configuring a composite index main key based on the preset service data partition attribute and the index information of the first data table.
And updating the index main key of the first data table to be the composite index main key.
In the embodiment of the present invention, the index information is information, such as an ID, used for accessing the original primary key of the first data table. The composite index primary key is a combined primary key containing the original primary key and the preset service data partition attribute, such as ID + create time. The index main key of the first data table is transformed into a combined main key containing partition attribute information, so that the index access mode of the business data in the first data table is converted into the index access mode suitable for the partition table, and when the business data in the first data table is exchanged to one table partition of the second data table, the access of the business data is not influenced by the migration of the business data, thereby ensuring the normal operation of the business in the table partition process.
In an embodiment of the present invention, for further explanation and limitation, before the step 102 of creating the second data table based on the preset partition attribute of the service data and the preset partition parameter, the method further includes:
and performing data validity detection, partition attribute detection and index extension attribute detection on the service data in the first data table.
And if the detection results of the data validity detection, the partition attribute detection and the index extension attribute detection are passed, acquiring query statements and data volume of service data of which the query frequency of the first data table is higher than a preset query frequency threshold value, so as to be used for query statement processing and data volume detection after partition exchange.
And if any detection result of the data validity detection, the partition attribute detection and the index extension attribute detection is failed, generating early warning information for prompting the abnormal partition of the data table.
In the embodiment of the present invention, the data validity detection is to check whether the service data in the first data table is valid data, for example, whether the index is invalid or not, whether the index contains a Null value, or not. Meanwhile, the problem that after the conversion is carried out into the second data table, the service data aging caused by the conversion or the original service data cannot be distinguished is also prevented. The partition attribute detection is to detect whether a field corresponding to the partition attribute is empty, for example, if the partition attribute is time, it is checked whether create _ time contains Null, and if the field is empty, after the index primary key is changed, the service data cannot be accessed correctly. The index extension attribute detection is also a check performed on the performance of the extension primary key, that is, whether the extension of the primary key into the joint primary key is supported by the current service data is confirmed, so as to ensure that the extension of the primary key does not affect the existing service field attribute. And when the detection is passed, indicating that the current service data supports the partition transformation of the table. In order to ensure that the SQL performance of the table is not changed before and after the table is partitioned, and obtain the SQL statements with higher search frequency, the SQL statements requiring more than one high frequency may be limited by setting a preset query frequency threshold, and the preset query frequency threshold may be self-defined according to the application requirements, which is not specifically limited in the embodiments of the present invention. In order to facilitate checking whether the stored data is lost after the partition exchange, the data volume of all the service data in the first data table, that is, the data volume of the service data, needs to be acquired.
In an embodiment of the present invention, for further explanation and limitation, the method further comprises:
after a target partition data table of the target service data is obtained, performing data validity detection on the service data in the target partition data table;
performing data volume detection on the service data in the target partition data table based on the data volume exchanged by the partitions;
if the data validity detection result and the data quantity detection result are both passed, the query statement is solidified based on the preset service data partition attribute 。
In the embodiment of the invention, after the partition exchange is completed, the validity of the service data is required to be detected, and meanwhile, whether the data volume of the valid service data before and after the partition exchange is the same or not and whether the total service data volume is the same or not are confirmed, so that the availability and integrity of the service data after the partition exchange are ensured. And after the service data after partition exchange is confirmed to be complete and effective, the SQL statement is solidified, namely, the query statement is solidified into the statement queried based on the index of the preset service data partition attribute + ID, so that normal access of the service data is ensured.
The invention provides a data table partitioning method.A first data table where target service data are located is determined from a database by responding to a data table partitioning request of the target service data, wherein the data table partitioning request carries preset service data partitioning attributes, preset partitioning parameters and target service data attribute information; creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition; the method comprises the steps of carrying out partition exchange processing on a first data table and a target table partition in a second data table, updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of target service data, exchanging data in an original data table to the partition table through data exchange, occupying no double storage space, realizing partition of the data table under the condition of not influencing normal service data access, greatly reducing influence of the table partition on normal service operation, and effectively reducing time and resource cost of the table partition.
Further, as an implementation of the method shown in fig. 1, an embodiment of the present invention provides a data table partitioning apparatus, and as shown in fig. 4, the apparatus includes:
a determining module 41, configured to determine, in response to a data table partitioning request of target service data, a first data table in which the target service data is located from a database, where the data table partitioning request carries a preset service data partitioning attribute, a preset partitioning parameter, and target service data attribute information;
a creating module 42, configured to create a second data table based on the target service data attribute information, the preset service data partition attribute, and the preset partition parameter, where the second data table includes a target table partition and at least one non-target table partition;
the switching module 43 is configured to perform partition switching processing on the first data table and a target table partition in the second data table, and update a second table name of the second data table after the partition switching processing to a first table name of the first data table, so as to obtain a target partition data table of the target service data.
Further, the apparatus further comprises:
the first deleting module is used for deleting the first data table after the data exchange;
and the storage module is used for storing the newly added service data into the corresponding non-target table partition based on the first attribute information of the newly added service data when the updating module receives a request of the newly added service data.
Further, the apparatus further comprises:
the first acquisition module is used for responding to the trigger of a preset service data cleaning condition and acquiring second attribute information of the target cleaning service data;
the determining module 41 is further configured to determine at least one target cleaning table partition from the target partition data table according to the second attribute information;
and the second deleting module is used for physically deleting the target cleaning table partition so as to release the storage space of the target cleaning table partition.
Further, the creating module 42 includes:
a first determining unit, configured to determine first table partition constraint information based on a target attribute of the target service data and the preset service data partition attribute, where the target attribute is a service data attribute corresponding to the preset service data partition attribute;
a second determining unit, configured to determine at least one second table partition constraint information based on the preset service data partition attribute and the preset partition parameter;
and the creating unit is used for creating a target table partition based on the first table partition constraint information and creating a non-target table partition based on the second table partition constraint information to obtain a second data table.
Further, the apparatus further comprises:
the configuration module is used for configuring a composite index main key based on the partition attribute of the preset service data and the index information of the first data table;
and the updating module is used for updating the index main key of the first data table into the composite index main key.
Further, the apparatus further comprises:
the first detection module is used for carrying out data validity detection, partition attribute detection and index extension attribute detection on the service data in the first data table;
a second obtaining module, configured to obtain, if the detection results of the data validity detection, the partition attribute detection, and the index extended attribute detection all pass, a query statement and data amount of service data of which query frequency of the first data table is higher than a preset query frequency threshold, so as to be used for query statement processing and data amount detection after partition exchange;
and the generating module is used for generating early warning information for prompting the abnormal partition of the data table if any detection result of the data validity detection, the partition attribute detection and the index extension attribute detection is failed.
Further, the apparatus further comprises:
the device further comprises:
the second detection module is used for carrying out data validity detection on the service data in the target partition data table after the target partition data table of the target service data is obtained;
a third detection module, configured to perform data volume detection on the service data in the target partition data table based on the data volume exchanged by the partition;
and the curing module is used for curing the query statement based on the preset service data partition attribute if the data validity detection result and the data quantity detection result both pass.
The invention provides a data table partitioning device, which is characterized in that a first data table where target service data are located is determined from a database by responding to a data table partitioning request of the target service data, wherein the data table partitioning request carries preset service data partitioning attributes, preset partitioning parameters and target service data attribute information; creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition; the method comprises the steps of carrying out partition exchange processing on a first data table and a target table partition in a second data table, updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of target service data, exchanging data in an original data table to the partition table through data exchange, occupying no double storage space, realizing partition of the data table under the condition of not influencing normal service data access, greatly reducing influence of the table partition on normal service operation, and effectively reducing time and resource cost of the table partition.
According to an embodiment of the present invention, a storage medium is provided, where the storage medium stores at least one executable instruction, and the computer executable instruction can execute the data table partitioning method in any of the above method embodiments.
Fig. 5 is a schematic structural diagram of a computer device according to an embodiment of the present invention, and the specific embodiment of the present invention does not limit the specific implementation of the computer device.
As shown in fig. 5, the computer apparatus may include: a processor (processor) 502, a communication interface (Commun I cat I ons I interface) 504, a memory 506, and a communication bus 508.
Wherein: the processor 502, communication interface 504, and memory 506 communicate with one another via a communication bus 508.
A communication interface 504 for communicating with network elements of other devices, such as clients or other servers.
The processor 502 is configured to execute the program 510, and may specifically perform the relevant steps in the above-described data table partitioning method embodiment.
In particular, program 510 may include program code that includes computer operating instructions.
The processor 502 may be a central processing unit CPU, or a specific integrated circuit AS IC (App I cat I on Spec I f I C I integrated Ci rcuit), 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 the same type of processor, such as one or more CPUs; or may be different types of processors, such AS one or more CPUs and one or more AS ics.
And a memory 506 for storing a program 510. The memory 506 may comprise high-speed RAM memory, and may also include non-volatile memory (non-vo at i e memory), such as at least one disk memory.
The program 510 may specifically be used to cause the processor 502 to perform the following operations:
responding to a data table partition request of target service data, and determining a first data table where the target service data is located from a database, wherein the data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information;
creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition;
and performing partition exchange processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of the target service data.
It will be apparent to those skilled in the art that the modules or steps of the invention described above may be implemented in a general purpose computing device, they may be centralized on a single computing device or distributed across a network of computing devices, and optionally they may be implemented in program code executable by a computing device, such that they may be stored in a memory device and executed by a computing device, and in some cases, the steps shown or described may be performed in an order different than that described herein, or they may be separately fabricated into individual integrated circuit modules, or multiple ones of them may be fabricated into a single integrated circuit module. Thus, the present invention is not limited to any specific combination of hardware and software.
The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention, and various modifications and changes may be made by those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (10)
1. A method for partitioning a data table, comprising:
responding to a data table partition request of target service data, and determining a first data table where the target service data is located from a database, wherein the data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information;
creating a second data table based on the target service data attribute information, the preset service data partition attribute and the preset partition parameter, wherein the second data table comprises a target table partition and at least one non-target table partition;
and performing partition exchange processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition exchange processing to a first table name of the first data table to obtain a target partition data table of the target service data.
2. The method according to claim 1, wherein after the second table name of the second data table after the partition exchange processing is updated to the first table name of the first data table, and a target partition data table of the target service data is obtained, the method further includes:
deleting the first data table after the data exchange;
and when a new service data request is received, storing the new service data into the corresponding non-target table partition based on the first attribute information of the new service data.
3. The method according to claim 2, wherein after storing the newly added service data into the corresponding non-target table partition based on the attribute information of the newly added service data, the method further comprises:
responding to the trigger of a preset service data cleaning condition, and acquiring second attribute information of the target cleaning service data;
determining at least one target cleaning table partition from the target partition data table according to the second attribute information;
and physically deleting the target cleaning table partition to release the storage space of the target cleaning table partition.
4. The method of claim 1, wherein the creating a second data table based on the data attribute of the target service data, the preset service data partition attribute, and the preset partition parameter comprises:
extracting target attribute information from the target service data attribute information based on the preset service data partition attribute, and configuring first table partition constraint information based on the target attribute information;
configuring second table partition constraint information for at least one second table partition based on the preset service data partition attribute and the preset partition parameter;
and creating a target table partition based on the first table partition constraint information, and creating a non-target table partition based on the second table partition constraint information to obtain a second data table.
5. The method of claim 1, wherein after determining the first data table in which the target service data is located from the database, the method further comprises:
configuring a composite index main key based on the preset service data partition attribute and the index information of the first data table;
and updating the index main key of the first data table into the composite index main key.
6. The method of claim 1, wherein before creating the second data table based on the pre-defined service data partition attribute and the pre-defined partition parameter, the method further comprises:
performing data validity detection, partition attribute detection and index extension attribute detection on the service data in the first data table;
if the detection results of the data validity detection, the partition attribute detection and the index extension attribute detection are all passed, acquiring query statements and data volume of service data of which the query frequency of the first data table is higher than a preset query frequency threshold value for query statement processing and data volume detection after partition exchange;
and if any detection result of the data validity detection, the partition attribute detection and the index extension attribute detection is failed, generating early warning information for prompting the abnormal partition of the data table.
7. The method of claim 6, further comprising:
after a target partition data table of the target service data is obtained, performing data validity detection on the service data in the target partition data table;
performing data volume detection on the service data in the target partition data table based on the data volume exchanged by the partitions;
and if the data validity detection result and the data quantity detection result are both passed, performing curing processing on the query statement based on the preset service data partition attribute.
8. A data table partitioning apparatus, comprising:
the determining module is used for responding to a data table partition request of target service data, and determining a first data table where the target service data is located from a database, wherein the data table partition request carries preset service data partition attributes, preset partition parameters and target service data attribute information;
a creating module, configured to create a second data table based on the target service data attribute information, the preset service data partition attribute, and the preset partition parameter, where the second data table includes a target table partition and at least one non-target table partition;
and the switching module is used for performing partition switching processing on the first data table and a target table partition in the second data table, and updating a second table name of the second data table subjected to partition switching processing to a first table name of the first data table to obtain a target partition data table of the target service data.
9. A storage medium having stored therein at least one executable instruction that causes a processor to perform operations corresponding to the data table partitioning method of any one of claims 1-7.
10. A computer device, comprising: the system comprises a processor, a memory, a communication interface and a communication bus, wherein the processor, the memory and the communication interface complete mutual communication through the communication bus;
the memory is used for storing at least one executable instruction, and the executable instruction causes the processor to execute the operation corresponding to the data table partition method according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211427639.9A CN115687359A (en) | 2022-11-15 | 2022-11-15 | Data table partitioning method and device, storage medium and computer equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211427639.9A CN115687359A (en) | 2022-11-15 | 2022-11-15 | Data table partitioning method and device, storage medium and computer equipment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115687359A true CN115687359A (en) | 2023-02-03 |
Family
ID=85051121
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211427639.9A Pending CN115687359A (en) | 2022-11-15 | 2022-11-15 | Data table partitioning method and device, storage medium and computer equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115687359A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116701382A (en) * | 2023-08-03 | 2023-09-05 | 成都数默科技有限公司 | Automatic efficient data rollback method based on clickhouse database |
-
2022
- 2022-11-15 CN CN202211427639.9A patent/CN115687359A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116701382A (en) * | 2023-08-03 | 2023-09-05 | 成都数默科技有限公司 | Automatic efficient data rollback method based on clickhouse database |
| CN116701382B (en) * | 2023-08-03 | 2023-10-20 | 成都数默科技有限公司 | Automatic efficient data rollback method based on clickhouse database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110799960B (en) | System and method for database tenant migration | |
| CN109684307B (en) | Data storage method, device, equipment and storage medium | |
| US10296498B2 (en) | Coordinated hash table indexes to facilitate reducing database reconfiguration time | |
| US11442961B2 (en) | Active transaction list synchronization method and apparatus | |
| US9767131B2 (en) | Hierarchical tablespace space management | |
| US20160212206A1 (en) | Deterministic database system and data transferring method thereof | |
| US20110060724A1 (en) | Distributed database recovery | |
| US20150081637A1 (en) | Difference determination in a database environment | |
| CN110019251A (en) | A kind of data processing system, method and apparatus | |
| CN110399333B (en) | Method, apparatus and computer program product for deleting snapshots | |
| US12093236B2 (en) | Probalistic data structure for key management | |
| CN108694230B (en) | Management of unique identifiers in a database | |
| US20200097581A1 (en) | Systems and methods of managing manifest refresh in a database | |
| CN115687359A (en) | Data table partitioning method and device, storage medium and computer equipment | |
| CN109710698A (en) | A kind of data assemblage method, device, electronic equipment and medium | |
| JP5283767B2 (en) | Database management method, master node, and program | |
| CN113778975A (en) | Data processing method and device based on distributed database | |
| US10353920B2 (en) | Efficient mirror data re-sync | |
| KR20210058613A (en) | Locking method for parallel i/o of a single file in non-volatiel memeroy file system and computing device implementing the same | |
| JPH07306795A (en) | Data base equivalent processor of duplex system computer | |
| CN117708092A (en) | Database management method and electronic equipment | |
| CN115757452A (en) | Blocking method, device, equipment and storage medium | |
| CN117596298A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN113934737A (en) | Database system, and method and device for managing transactions | |
| CN114706832A (en) | Data redistribution method, device, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |