Method, device, medium and system for checking consistency of statistical information of partition table

Abstract

The invention provides a consistency verification method, device, medium and system for partition table statistical information, and the method comprises the steps: determining a partition table corresponding to a partition as a target partition table under the condition that a preset operation executed on the partition is received, or under the condition that the statistical information of the partition table corresponding to the partition is detected to be updated; obtaining partition-level statistical information and whole-table-level statistical information of the target partition table, and determining whether the target partition table has statistical deviation or not according to the partition-level statistical information and the whole-table-level statistical information; and under the condition of determining that the statistical deviation exists in the target partition table, determining a to-be-updated partition with the statistical deviation in the target partition table, and updating the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the to-be-updated partition. According to the method and the device, the problem of low SQL statement execution efficiency caused by misjudgment of a database optimizer due to lack of unified coordination of statistical information of the partition table in an updating process in an existing database is solved.

Description

Technical Field

[0001] This application relates to the fields of big data and cloud computing technology, and more specifically, to a method for verifying the consistency of partition table statistics, a device for verifying the consistency of partition table statistics, a computer-readable storage medium, and a system for verifying the consistency of partition table statistics. Background Technology

[0002] With the continuous expansion of database scale and the widespread application of high-concurrency business scenarios, partitioned tables, as an important means to improve the efficiency of large table management and query performance, have been widely used in mainstream relational database systems. Partitioned tables typically consist of multiple partitions, and the database maintains statistical information for each partition, including data volume, data distribution, and value range, to support the optimizer in generating reasonable execution plans. The accuracy and completeness of the statistical information are crucial for the database optimizer to make correct decisions and have a direct impact on the execution efficiency of SQL statements.

[0003] However, in existing database systems, the update mechanism for partitioned table statistics is often triggered independently for each partition. This leads to inconsistencies in update frequency, asynchronous update timing, and long-term missing statistics for some partitions. Due to the lack of a unified coordination mechanism, the database optimizer may make incorrect estimates of the overall table size and data distribution based on incomplete, inconsistent, or distorted statistical information when generating execution plans. For example, the optimizer might incorrectly use the statistics of a single partition as representative information for the entire table, resulting in inappropriate execution plan selection, such as mistakenly choosing a full table scan, ignoring available indexes, or employing inefficient join strategies, thus causing a significant decrease in SQL statement execution efficiency.

[0004] This problem becomes more pronounced when statistical inconsistencies are significant. In high-concurrency or complex query scenarios, erroneous execution plans can lead to spikes in query latency, abnormally high CPU utilization, decreased system throughput, and even database performance fluctuations, severely impacting the stable operation of business systems. Therefore, the lack of a unified coordination and consistency guarantee mechanism for partitioned table statistics in existing database systems has become a major bottleneck affecting database performance. Summary of the Invention

[0005] The main objective of this application is to provide a method, device, computer-readable storage medium, and system for verifying the consistency of partition table statistics, so as to at least solve the problem that in existing databases, the lack of unified coordination in the updating process of partition table statistics leads to misjudgment by the database optimizer, resulting in low SQL statement execution efficiency.

[0006] To achieve the above objectives, according to one aspect of this application, a method for consistency verification of partition table statistics is provided, comprising: upon receiving a preset operation performed on a partition, or upon detecting an update to the statistics of the partition table corresponding to the partition, determining that the partition table corresponding to the partition is a target partition table, wherein the preset operation includes a database cleanup operation and a database statistics collection operation; acquiring partition-level statistics and whole-table-level statistics of the target partition table, and determining whether there is a statistical deviation in the target partition table based on the partition-level statistics and the whole-table-level statistics, wherein the partition-level statistics include at least the update time of statistics, row count statistics, and data distribution information of each partition included in the target partition table, and the whole-table-level statistics include at least the whole-table row count statistics, global data distribution information, and whole-table-level statistics update time of the target partition table; and, if it is determined that there is a statistical deviation in the target partition table, identifying the partition in the target partition table that has a statistical deviation and is to be updated, and updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated.

[0007] Optionally, obtaining partition-level statistics and whole-table statistics of the target partition table, and determining whether the target partition table has a statistical deviation based on the partition-level statistics and the whole-table statistics, includes: determining the row count statistics of multiple partitions corresponding to the target partition table, and determining the whole-table row count statistics of the target partition table; determining multiple ratios of each row count statistics to the whole-table row count statistics; and determining that the target partition table has a statistical deviation if a target ratio is found among all the ratios, wherein the target ratio is a ratio greater than a preset ratio.

[0008] Optionally, obtaining partition-level and whole-table-level statistical information of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level and whole-table-level statistical information, includes: determining the data distribution information of each partition corresponding to the target partition table, and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution bias index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information; and determining that the target partition table has a statistical bias if the data distribution bias index value is greater than a preset index value.

[0009] Optionally, determining the data distribution information of each partition corresponding to the target partition table and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution deviation index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information, including: obtaining the data distribution information of the target field and the global data distribution information in each partition corresponding to the target partition table; determining the minimum value and maximum value of the first field based on the data distribution information of the target field; determining the minimum value and maximum value of the second field based on the global data distribution information of the target field; determining the third difference as the difference between the maximum value and the minimum value of the second field; determining the fourth difference as the difference between the minimum value and the minimum value of the first field; determining the fifth difference as the difference between the maximum value and the maximum value of the second field; determining the first quotient as the quotient of the fourth difference and the third difference; determining the second quotient as the quotient of the fifth difference and the third difference; determining the data distribution deviation index of the target field as the sum of the first quotient and the second quotient.

[0010] Optionally, obtaining partition-level statistics and whole-table statistics of the target partition table, and determining whether the target partition table has statistical deviation based on the partition-level statistics and the whole-table statistics, includes: determining multiple first differences as the difference between the update time of the statistics of multiple partitions corresponding to the target partition table and the update time of the whole-table statistics; and determining that the target partition table has statistical deviation if there is a first difference greater than a first preset difference.

[0011] Optionally, determining the partition to be updated in the target partition table that has a statistical bias, and updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, includes: determining the partition to be updated in the target partition table that has a statistical bias; determining the current partition-level statistics of the partition to be updated, and updating the partition-level statistics of the partition to be updated based on the current data in the partition to be updated, to obtain the updated partition-level statistics; and determining the whole-table-level statistics of the target partition table based on the updated partition-level statistics, to obtain the updated whole-table-level statistics.

[0012] Optionally, after updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, the method further includes: determining whether the target partition table has a statistical deviation based on the updated partition-level statistics and the updated whole-table-level statistics; if it is determined that the target partition table has no statistical deviation, marking the statistical information consistency status of the target partition table as passed; if it is determined that the target partition table has a statistical deviation, marking the statistical information consistency status of the target partition table as failed.

[0013] According to another aspect of this application, a consistency verification device for partition table statistics is provided, comprising: a first determining unit, configured to determine the partition table corresponding to the partition as a target partition table upon receiving a preset operation performed on a partition, or upon detecting an update of the statistics of the partition table corresponding to the partition, wherein the preset operation includes a database cleanup operation and a database statistics collection operation; an acquiring unit, configured to acquire partition-level statistics and whole-table-level statistics of the target partition table, and determine whether the target partition table has a statistical deviation based on the partition-level statistics and the whole-table-level statistics, wherein the partition-level statistics include at least the update time of the statistics of each partition included in the target partition table, the row count statistics, and the data distribution information, and the whole-table-level statistics include at least the whole-table row count statistics, global data distribution information, and the whole-table-level statistics update time; and a second determining unit, configured to determine the partition in the target partition table that has a statistical deviation to be updated if the statistical deviation is determined to exist, and update the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated.

[0014] According to another aspect of this application, a computer-readable storage medium is provided, the computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform any of the methods described.

[0015] According to another aspect of this application, a consistency verification system for partition table statistics is provided, comprising: one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include methods for performing any one of the methods described.

[0016] By applying the technical solution of this application, when a preset operation is detected in a partition or an update to the partition table statistics is detected, the corresponding partition table is automatically identified as the target partition table. The partition-level and whole-table-level statistics of the target partition table are acquired in real time, thereby accurately identifying any statistical discrepancies between the partition-level and whole-table-level statistics as soon as changes occur. Through comprehensive analysis of multi-dimensional indicators such as statistical update time, row count, and data distribution, this application can comprehensively determine whether there are abnormal updates, asynchrony, or data distribution imbalances in the partition table's statistics. If a statistical discrepancy is determined in the target partition table, this application further identifies the corresponding partition to be updated and updates the partition-level statistics based on the current data in the partition to be updated, and correspondingly updates the whole-table-level statistics. Through this update strategy, this application ensures consistency between the statistics of each partition and the whole-table-level statistics, avoiding the database optimizer selecting an incorrect execution plan due to missing, outdated, or inconsistent statistical information. Therefore, this application can effectively prevent the optimizer from misjudging the data size and distribution in the table, significantly reducing the probability of problems such as SQL (Structured Query Language) execution plan deviation, false full table scans, and abnormal index usage, improving the execution efficiency of SQL statements, reducing CPU (Central Processing Unit) usage, and enhancing the overall database performance and service stability. In summary, by real-time monitoring, deviation detection, and consistency repair of partition table statistics, this application can ensure the coordination and correctness of partition table statistics during the update process, thereby improving the accuracy and execution efficiency of database query optimization. Attached Figure Description

[0017] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an undue limitation of this application. In the drawings:

[0018] Figure 1 A flowchart illustrating a consistency verification method for partition table statistics provided according to an embodiment of this application is shown.

[0019] Figure 2 A structural block diagram of a consistency verification device for partition table statistics provided according to an embodiment of this application is shown. Detailed Implementation

[0020] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0021] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0022] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this application described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0023] As described in the background section, in existing databases, the statistical information of partitioned tables lacks unified coordination during the update process, leading to misjudgment by the database optimizer and resulting in low execution efficiency of SQL statements. To solve the above technical problems, embodiments of this application provide a consistency verification method for partitioned table statistical information, a consistency verification device for partitioned table statistical information, a computer-readable storage medium, and a consistency verification system for partitioned table statistical information.

[0024] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.

[0025] Figure 1 This is a flowchart of a method for verifying the consistency of partition table statistics according to an embodiment of this application. Figure 1 As shown, the method includes the following steps:

[0026] Step S101: When a preset operation is received for a partition, or when the statistical information of the partition table corresponding to the partition is updated, the partition table corresponding to the partition is determined to be the target partition table. The preset operation includes database cleanup operation and database statistical information collection operation.

[0027] Specifically, the system first monitors the update behavior of partitioned table statistics in real time. When the database performs operations that may affect the accuracy of statistics, such as database cleanup operations to remove invalid data from partitions or database statistics collection operations to re-collect statistics, the system can immediately detect the impact of these operations on the partitions. Furthermore, when the system detects that the statistics of a partition are automatically updated by the database's internal mechanism or that a data change triggers a statistics refresh, it will also identify the change and use it as a trigger condition. After either of these trigger conditions occurs, the system automatically determines the partitioned table to which the partition belongs and uses this partitioned table as the target partitioned table for subsequent statistics consistency verification. Through this mechanism, the system can capture relevant events as soon as partition statistics change, ensuring that statistics consistency verification and subsequent deviation correction operations are performed on the target partitioned table, thereby effectively avoiding database optimizer misjudgments caused by untimely or inconsistent statistics updates.

[0028] Step S102: Obtain the partition-level statistical information and whole-table-level statistical information of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistical information and the whole-table-level statistical information. The partition-level statistical information includes at least the update time of the statistical information of each partition included in the target partition table, the row count statistics, and the data distribution information. The whole-table-level statistical information includes at least the whole-table row count statistics of the target partition table, the global data distribution information, and the whole-table statistical information update time.

[0029] Specifically, the partition-level statistics and table-wide statistics of the partitioned table are obtained first. Partition-level statistics reflect the data situation of each partition itself, including at least the update time of the statistics, the number of rows, and the data distribution information of key fields. Table-wide statistics reflect the overall data situation of the entire partitioned table, including at least the number of rows in the entire table, global data distribution information, and the update time of the entire table's statistics.

[0030] Step S103: If it is determined that the target partition table has the above-mentioned statistical deviation, determine the partition to be updated in the target partition table that has the statistical deviation, and update the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the partition to be updated.

[0031] Specifically, when a statistical discrepancy is detected between the partition-level statistics and the overall table-level statistics of the target partition table, the partition to be updated is further determined. Subsequently, based on the current data in the partition to be updated, the statistical information collection operation is re-executed for that partition to generate updated partition-level statistics. After obtaining the new partition-level statistics, the system then synchronously updates the overall table-level statistics according to the update results of each partition, ensuring consistency of the statistical information across the entire partition table.

[0032] Through the above embodiments, by automatically identifying the corresponding partition table as the target partition table when a preset operation is detected in the partition or when the partition table statistics are updated, and by acquiring the partition-level and whole-table-level statistics of the target partition table in real time, it is possible to accurately identify whether there is a statistical deviation between the partition-level and whole-table-level statistics as soon as the statistics change. By comprehensively analyzing multi-dimensional indicators such as the update time of statistics, the number of rows, and data distribution information, this application can comprehensively determine whether there are abnormal updates, asynchrony, or data distribution imbalances in the statistics of the partition table. If a statistical deviation is determined in the target partition table, this application further determines the corresponding partition to be updated, updates the partition-level statistics based on the current data in the partition to be updated, and updates the whole-table-level statistics accordingly. Through this update strategy, this application can ensure that the statistics of each partition are consistent with the statistics of the whole table, avoiding the database optimizer from selecting an incorrect execution plan due to missing, expired, or inconsistent statistics. Therefore, this application can effectively prevent the optimizer from misjudging the data size and distribution in the table, significantly reducing the probability of problems such as SQL execution plan deviation, false full table scans, and abnormal index usage, improving the execution efficiency of SQL statements, reducing CPU (Central Processing Unit) usage, and enhancing the overall database performance and service stability. In summary, by real-time monitoring, deviation detection, and consistency repair of partition table statistics, this application can ensure the coordination and correctness of partition table statistics during the update process, thereby improving the accuracy and execution efficiency of database query optimization.

[0033] In one optional approach, obtaining partition-level and whole-table-level statistical information of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level and whole-table-level statistical information, includes: determining the row count statistics of multiple partitions corresponding to the target partition table, and determining the whole-table row count statistics of the target partition table; determining multiple ratios between each row count statistics and the whole-table row count statistics; and determining that the target partition table has a statistical bias if a target ratio exists among all the ratios, wherein the target ratio is greater than a preset ratio.

[0034] In the above embodiments, by obtaining the row count statistics of each partition in the target partition table and using the ratio between the row count of each partition and the total row count of the table as the data proportion of the partition in the whole table, it is possible to accurately identify whether the data volume of a certain partition is abnormally large or small. When at least one ratio exceeds a preset ratio threshold, it indicates that the row count proportion of the partition is inconsistent with the normal range, suggesting that the statistical information of the partition may be lagging, outdated, or out of sync with the statistical information of the whole table. Therefore, by using the statistical deviation detection method based on the row count proportion, inconsistencies in the data volume dimension of the partition table can be quickly and intuitively discovered, providing an accurate basis for subsequent statistical information updates and database execution plan optimization, effectively avoiding optimizer misjudgments caused by distorted row count statistics, thereby improving the execution efficiency of SQL statements and the stability of the database system.

[0035] In another optional approach, the partition-level and whole-table-level statistical information of the target partition table are obtained, and the presence of statistical bias in the target partition table is determined based on the partition-level and whole-table-level statistical information. This includes: determining the data distribution information of each partition corresponding to the target partition table, and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution bias index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information; and determining that the target partition table has statistical bias if the data distribution bias index value is greater than a preset index value.

[0036] In the above embodiments, by comparing the data distribution information of each partition in the target partition table with the global data distribution information of the entire table, it is possible to identify whether there are any anomalies in the statistical information of the partition table from the data distribution dimension. The data value range of each partition can reflect the true distribution of key fields in that partition, while the global data distribution information represents the overall distribution structure of the entire table on the same field. When the data distribution deviation index value of a certain partition exceeds a preset threshold, it indicates that there is a significant difference between the data distribution of that partition and the overall distribution of the entire table. This usually means that the statistical information of that partition has not been updated in time, the statistical histogram is distorted, or the data distribution is inconsistent with the actual situation. Through the deviation detection mechanism based on data distribution, this application can effectively discover the abnormal situation of partitions with similar row numbers but unbalanced distribution, thereby avoiding the optimizer from using incorrect distribution information when performing cost estimation and path selection, and reducing the probability of generating erroneous execution plans. By detecting the deviation between partition data distribution and global data distribution, the accuracy of statistical information consistency verification is further improved, which helps to improve the estimation accuracy of the database optimizer and SQL query performance, and improve the overall operating efficiency and stability of the system.

[0037] In some exemplary embodiments, determining the data distribution information of each partition corresponding to the target partition table and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution deviation index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information includes: obtaining the data distribution information of the target field and the global data distribution information in each partition corresponding to the target partition table; determining the minimum value and maximum value of the first field based on the data distribution information of the target field; determining the minimum value and maximum value of the second field based on the global data distribution information of the target field; determining the third difference as the difference between the maximum value and the minimum value of the second field; determining the fourth difference as the difference between the minimum value and the minimum value of the first field; determining the fifth difference as the difference between the maximum value and the maximum value of the first field; determining the first quotient as the quotient of the fourth difference and the third difference; determining the second quotient as the quotient of the fifth difference and the third difference; and determining the data distribution deviation index of the target field as the sum of the first quotient and the second quotient.

[0038] In the above embodiments, by calculating the offset of the partition minimum value relative to the global minimum value and the offset of the partition maximum value relative to the global maximum value, and normalizing these offsets with the global field span, the resulting first and second quotients can reflect the missing proportions of the partition on the left and right sides of the field range, respectively. Furthermore, by summing these two values ​​to obtain a data distribution deviation index, the system can intuitively present the overall deviation of the partition field range from the global field range. When this deviation index exceeds a preset threshold, it indicates a significant difference between the data distribution range of the partition and the global distribution, meaning that the statistical information of the partition has not been updated in a timely manner or has failed to accurately reflect the current data value range of the partition. Based on this detection method, it can effectively identify distributional statistical anomalies that cannot be detected by simply relying on row count or update time, thereby significantly improving the accuracy of judging the consistency of statistical information in partitioned tables, avoiding the database optimizer from generating inefficient execution plans due to incorrect field distribution estimation, and ultimately improving query performance and overall database stability.

[0039] Specifically, according to The data distribution deviation index value for each of the above partitions is determined, where Smin is the global minimum value of the target field in the entire partition table, i.e., the minimum value of the second field. Smax is the global maximum value of the target field in the entire partition table, i.e., the maximum value of the second field. The minimum value of the target field in the i-th partition is the minimum value of the first field. This represents the maximum value of the target field in the i-th partition, which is the maximum value of the first field.

[0040] In other exemplary embodiments, obtaining partition-level statistics and whole-table statistics of the target partition table, and determining whether the target partition table has statistical deviation based on the partition-level statistics and whole-table statistics, includes: determining multiple first differences as the difference between the update time of the statistical information of the multiple partitions corresponding to the target partition table and the update time of the whole-table statistics; and determining that the target partition table has statistical deviation if the first difference is greater than a first preset difference.

[0041] In the above embodiments, by obtaining the update time of statistical information for each partition and the update time of statistical information for the entire table, and calculating the time difference between the two, it is possible to determine whether there is a statistical deviation in the target partition table from the perspective of the timeliness of statistical information updates. When the difference between the update time of statistical information for any partition and the update time of statistical information for the entire table exceeds a preset threshold, it indicates that the update of statistical information for that partition is significantly lagging behind the statistical information for the entire table, or that there is a lack of a synchronization update mechanism between the two. Based on the above time difference detection mechanism, this application can promptly detect problems such as statistical information not being updated synchronously as expected, some partition statistical information being outdated for a long time, or missing updates. This can effectively prevent the database optimizer from referencing outdated statistical information when generating execution plans, thereby reducing execution plan misjudgments, cost estimation biases, and inefficient query path selection, improving SQL execution efficiency and the overall performance and stability of the database system.

[0042] In some exemplary embodiments of this application, determining the partition to be updated in the target partition table that has a statistical bias, and updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, includes: determining the partition to be updated in the target partition table that has a statistical bias; determining the current partition-level statistics of the partition to be updated, and updating the partition-level statistics of the partition to be updated based on the current data in the partition to be updated, so as to obtain the updated partition-level statistics; and determining the whole-table-level statistics of the target partition table based on the updated partition-level statistics, so as to obtain the updated whole-table-level statistics.

[0043] In the above embodiments, partition-level statistics for the partition to be updated are regenerated based on the current real data in the partition, ensuring that the row count, data distribution, and update time of the statistics are consistent with the actual data. After obtaining the updated partition-level statistics, the table-wide statistics of the target partition table are synchronously updated based on the latest statistical results of each partition, restoring the table-wide row count and global data distribution to a state consistent with the real data. Through the above-mentioned coordinated updates at the partition and table levels, statistical deviations caused by outdated, missing, or inconsistent partition updates can be quickly corrected. This ensures that the statistical information referenced by the optimizer when generating the execution plan is accurate and reliable, avoiding misjudgments of execution paths, false triggering of full table scans, and performance degradation due to incorrect statistical information, further improving the query efficiency and operational stability of the database system.

[0044] In some further exemplary embodiments of this application, after updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, the method further includes: determining whether there is a statistical deviation in the target partition table based on the updated partition-level statistics and the updated whole-table-level statistics; if it is determined that there is no statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as passed; if it is determined that there is a statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as failed.

[0045] In the above embodiments, after updating the partition-level statistics of the partition to be updated and the whole-table statistics of the target partition table, a second round of statistical deviation detection is performed based on the updated partition-level and whole-table statistics. This verifies whether the aforementioned statistical information update operation has truly eliminated the statistical deviation between the partition and the whole table, avoiding residual problems caused by incomplete updates or some partitions still being abnormal. On the other hand, by explicitly marking the consistency status of the target partition table's statistical information as passed or failed, a clear basis can be provided for subsequent operation and maintenance monitoring, alarm strategies, or manual intervention, ensuring the effectiveness and traceability of the statistical information repair process, improving the automation and reliability of partition table statistical information consistency management, and thus providing a more stable and reliable statistical information foundation for the database optimizer.

[0046] To enable those skilled in the art to better understand the technical solution of this application, the implementation process of the consistency verification method for partition table statistics of this application will be described in detail below with reference to specific embodiments.

[0047] This embodiment relates to a specific method for consistency verification of partition table statistics, including the following steps:

[0048] Step S1: Trigger the determination of the target partition table;

[0049] The system continuously monitors preset operations performed on partitions in the database. When any of the following operations are detected on a partition, the subsequent processing flow of this method is triggered: database cleanup operations, such as garbage collection or invalid row cleanup; and database statistics collection operations, such as rebuilding the statistical histogram. In addition to monitoring explicit preset operations, the system can also detect statistical update events in internal database records. Automatic refresh or updates to the statistics of a partition by other modules are also considered trigger conditions. Upon detecting any of these trigger conditions, the system determines the partition table to which the partition belongs based on its metadata information and designates that partition table as the target partition table, proceeding to the subsequent statistics consistency verification process.

[0050] Step S2: Obtain partition-level statistics and table-wide statistics;

[0051] The system retrieves partition-level statistics from the database system directory or statistics view, collecting partition-level statistics for all partitions under the target partition table. Each partition's partition-level statistics include at least: update time (representing the most recent update time); row count (representing the current number of rows in the partition); and data distribution information (representing the value range and distribution characteristics of target fields (such as amount, time, quantity, etc.) within the partition, such as minimum value, maximum value, and interval distribution). The system also retrieves overall table-level statistics for the target partition table, including at least: row count (representing the total number of rows in the entire partition table); global data distribution information (representing the value range and overall distribution of fields after merging all partitions); and overall table-level statistics update time (representing the most recent update time of the overall table-level statistics).

[0052] Step S3: Determine whether there is statistical bias based on multidimensional indicators;

[0053] Based on the statistical information obtained in step S2, the presence of statistical bias in the target partition table is determined from multiple dimensions. This embodiment may include the following sub-steps (which can be used individually or in combination): For each partition in the target partition table, calculate the time difference between the update time of the partition's statistical information and the update time of the overall table's statistical information, obtaining multiple first differences; if any first difference is greater than a preset time difference threshold (e.g., exceeding a certain number of hours or days), it is determined that the target partition table has a statistical bias in the statistical information update time dimension. Based on row count ratio consistency detection, calculate the ratio of each partition's row count to the overall table's row count, i.e., the ratio of the row count to the overall table's row count; if any partition's row count ratio is greater than a preset ratio threshold (e.g., exceeding a reasonable upper limit of the total number of rows in the entire table), it indicates that the partition has a significantly abnormal data distribution, and it is determined that the target partition table has a statistical bias in the row count dimension. For the data distribution deviation index of each partition in the target partition table, if the data distribution deviation index is greater than the preset index value, it indicates that the field value range of that partition is significantly shortened or deviated from the coverage range of the entire table, and it is determined that there is a statistical deviation in the data distribution dimension of the target partition table. If any of the above dimensions (update time, row count ratio, or data distribution) exceeds the corresponding preset threshold, it is determined that there is a statistical deviation in the target partition table; otherwise, the current statistical information of the target partition table can be considered to be within the consistency range.

[0054] Step S4: Determine the partition to be updated and update the partition-level statistics and table-wide statistics;

[0055] If statistical bias is found in the target partition table, partitions with significant statistical anomalies are identified as those to be updated based on the biases in each of the aforementioned dimensions. These partitions may include: partitions with significantly delayed statistical updates; partitions with significantly abnormal row count ratios; and partitions where data distribution deviation values ​​exceed preset thresholds. For each partition to be updated, the system performs statistical data collection based on the currently stored real data in that partition, updating its partition-level statistics. This includes: recalculating the row count for that partition; recalculating the value range of the target field and related data distribution information; and updating the statistical update time for that partition. After the update, the updated partition-level statistics for that partition are obtained. After updating the partition-level statistics of the partitions to be updated, the system recalculates the overall table-level statistics based on the latest partition-level statistics of each partition in the target partition table. This includes: summarizing the row counts of each partition to obtain the updated overall table row count; comprehensively calculating the updated global data distribution information based on the field value ranges and data distribution information of each partition; and updating the overall table statistical update time. This results in updated table-wide statistics, ensuring consistency between the table-wide statistics and the partition-level statistics of each partition in terms of data volume and range.

[0056] Step S5: Update the re-verification and consistency status flag;

[0057] After updating the partition-level and table-wide statistics, the updated partition-level and table-wide statistics are retrieved again. The system then checks again to determine if statistical discrepancies still exist in the target partition table. If no statistical discrepancies are detected during the re-check, the consistency status of the target partition table's statistics is marked as "passed." If statistical discrepancies still exist, the consistency status of the target partition table's statistics is marked as "failed," and an alarm or log entry may be triggered for subsequent manual intervention or strategy optimization.

[0058] This application also provides a specific implementation method for performing distribution consistency verification based on value range offset on key fields of a target partitioned table. Specifically, the system obtains the minimum and maximum values ​​of fields in each partition and compares them with the global minimum and maximum values ​​of the entire table. Then, it calculates the offset of the partition's minimum value relative to the global minimum value and the offset of the partition's maximum value relative to the global maximum value, and divides both by the global field span to obtain the normalized offset ratio. The sum of these two is then used as the distribution deviation index for that partition. This distribution deviation index can accurately characterize the degree of coverage gap between the partition's field range and the entire table's field range, making it particularly suitable for typical partitioned table scenarios such as bank transaction logs, e-commerce order tables, and log time series tables. For example, in financial transaction systems, databases typically partition transaction details by date. When a partition for a certain day experiences abnormal data import, fails to trigger statistical updates, or contains dirty data, resulting in a transaction amount range in that partition being significantly smaller than the actual range of the entire table, traditional row count detection methods cannot identify such anomalies. However, the distribution deviation index of this application can immediately capture the phenomenon of narrowing field ranges. This mechanism enables the system to detect statistical distortions before the optimizer generates a query plan, thus preventing the optimizer from selecting costly execution plans such as full table scans due to incorrect field distribution estimations. Therefore, this application not only improves the accuracy of statistical consistency detection for partitioned tables but also significantly enhances the query stability and overall performance of the database in large-scale real-time transaction environments.

[0059] This application also provides a scenario for maintaining statistical information of a database partitioned table, including an analysis and coordination module for monitoring database statistical information update events and triggering a partitioned table consistency assessment process when statistical information changes are detected; a consistency scoring module for scoring the statistical information, including partition missing rate, standard deviation of statistical information update events, row skewness, sample completeness, and data distribution deviation indicators; an optimizer control module for determining whether statistical information needs to be supplemented and performing analysis operations or supplementing sampling on a partition based on the indicator deviation provided by the scoring module; and a diagnosis and alarm module. Specifically, step 1: The analysis and coordination module continuously monitors statistical information-related operations performed on partitions within the database. When a related operation is detected, the analysis and coordination module identifies the partition corresponding to the event and its partitioned table, and uses the table as the target object for consistency assessment. Step 2: Collect statistical information of the partitioned table, including the statistical information update time, row count statistics, minimum and maximum values ​​of the target field, and histogram information for each partition. The number of sampled tuples (used to determine whether sampling has reached a threshold). Simultaneously, collect overall table statistics, including the total number of rows in the entire table, the range of global field values, and the update time of overall table statistics. Step 3: Determine if some partitions have not generated statistics. For example, if 2 out of 10 partitions are missing, the partition missing rate is 20%. Calculate the standard deviation of the update time for all partition statistics to reflect whether the updates are synchronized. If the update times of some partitions differ by several hours or even days, the standard deviation will increase significantly. Row skewness: Used to determine if the number of rows in a partition is abnormal. If p1 has 1000 rows and p2 has 100000 rows, it indicates that inconsistent updates may have caused this deviation. Determine sample data integrity: Used to determine whether the statistical information is sufficiently sampled, for example, whether the sampling ratio reaches 5%. Field data distribution deviation index: Calculate the partition field distribution deviation based on the minimum and maximum values. The consistency scoring module calculates the total score for the above indicators according to their weights. If the score is lower than the threshold, it is considered that the statistical information of the partition table is inconsistent. The optimizer control module will determine the abnormal partitions based on the source of the deviation, including missing partitions, partitions with lagging update times, etc. For abnormal partitions, perform a completion operation, re-execute the analysis operation, supplement the sampled tuples, and repair the partition statistics metadata. After completion, re-launch the consistency scoring module. If the score reaches the threshold, it is marked as passed; otherwise, it is marked as failed.

[0060] It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases the steps shown or described may be executed in a different order than that shown here.

[0061] This application also provides a consistency verification device for partition table statistics. It should be noted that this device can be used to execute the consistency verification method for partition table statistics provided in this application. This device is used to implement the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that performs a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0062] The consistency verification device for partition table statistics provided in the embodiments of this application will be described below.

[0063] Figure 2 This is a schematic diagram of a consistency verification device for partition table statistics according to an embodiment of this application. Figure 2 As shown, the device includes:

[0064] The first determining unit 10 is used to determine the partition table corresponding to the partition as the target partition table when a preset operation is received for the partition or when the statistical information of the partition table corresponding to the partition is updated. The preset operation includes a database cleanup operation and a database statistical information collection operation.

[0065] The acquisition unit 20 is used to acquire the partition-level statistical information and the whole table-level statistical information of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistical information and the whole table-level statistical information. The partition-level statistical information includes at least the update time of the statistical information of each partition included in the target partition table, the row count statistics, and the data distribution information. The whole table-level statistical information includes at least the whole table row count statistics, the global data distribution information, and the whole table statistical information update time of the target partition table.

[0066] The second determining unit 30 is used to determine the partitions in the target partition table that have statistical deviations when it is determined that there are statistical deviations, and to update the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the partitions to be updated.

[0067] In one optional embodiment, the acquisition unit includes: a first determining module, configured to determine the row count statistics of multiple partitions corresponding to the target partition table, and to determine the total row count statistics of the target partition table; a second determining module, configured to determine multiple ratios between each row count statistics and the total row count statistics; and a third determining module, configured to determine that the target partition table has a statistical deviation if a target ratio exists among all the ratios, wherein the target ratio is greater than a preset ratio.

[0068] In another optional embodiment, the acquisition unit further includes: a fourth determining module, used to determine the data distribution information of each partition corresponding to the target partition table, and to determine the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; a fifth determining module, used to determine the data distribution deviation index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information; and a sixth determining module, used to determine that the target partition table has a statistical deviation when the data distribution deviation index value is greater than a preset index value.

[0069] In some exemplary embodiments, the fourth determining module includes: an acquisition submodule, configured to acquire the data distribution information and global data distribution information of the target field in each of the partitions corresponding to the target partition table; a first determining submodule, configured to determine the minimum value and maximum value of the first field based on the data distribution information of the target field; a second determining submodule, configured to determine the minimum value and maximum value of the second field based on the global data distribution information of the target field; a third determining submodule, configured to determine the third difference as the difference between the maximum value and the minimum value of the second field; a fourth determining submodule, configured to determine the fourth difference as the difference between the minimum value and the minimum value of the first field; a fifth determining submodule, configured to determine the fifth difference as the difference between the maximum value and the maximum value of the first field; a sixth determining submodule, configured to determine the first quotient as the quotient of the fourth difference and the third difference; a seventh determining submodule, configured to determine the second quotient as the quotient of the fifth difference and the third difference; and an eighth determining submodule, configured to determine the data distribution deviation index of the target field as the sum of the first quotient and the second quotient.

[0070] In some other exemplary embodiments, the acquisition unit further includes: a seventh determining module, configured to determine that a plurality of first differences are the differences between the update time of the statistical information of the plurality of partitions corresponding to the target partition table and the update time of the statistical information of the whole table; and an eighth determining module, configured to determine that the target partition table has a statistical deviation when there is a first difference greater than a first preset difference.

[0071] In some exemplary embodiments of this application, the second determining unit includes: a ninth determining module, configured to determine the partition to be updated in the target partition table that has a statistical deviation; a tenth determining module, configured to determine the current partition-level statistical information of the partition to be updated, and update the partition-level statistical information of the partition to be updated according to the current data in the partition to be updated, so as to obtain the updated partition-level statistical information; and an eleventh determining module, configured to determine the whole-table-level statistical information of the target partition table based on the updated partition-level statistical information, so as to obtain the updated whole-table-level statistical information.

[0072] In some further exemplary embodiments of this application, the above-described apparatus further includes: a third determining unit, configured to determine whether the target partition table has a statistical deviation based on the updated partition-level statistical information and the updated whole-table-level statistical information; a first marking unit, configured to mark the statistical information consistency status of the target partition table as passed if it is determined that the target partition table has no statistical deviation; and a second marking unit, configured to mark the statistical information consistency status of the target partition table as failed if it is determined that the target partition table has a statistical deviation.

[0073] The consistency verification device for the aforementioned partition table statistics includes a processor and a memory. The aforementioned first determining unit, the aforementioned acquiring unit, and the aforementioned second determining unit are all stored as program units in the memory, and the processor executes the aforementioned program units stored in the memory to achieve the corresponding functions. All of the aforementioned modules are located in the same processor; or, the aforementioned modules are located in different processors in any combination.

[0074] The processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured. By adjusting kernel parameters, the statistical information of partitioned tables in the existing database lacks unified coordination during the update process, leading to misjudgments by the database optimizer and resulting in low SQL statement execution efficiency.

[0075] The memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0076] This invention provides a computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device where the computer-readable storage medium is located to perform a consistency verification method for partition table statistics.

[0077] Specifically, the consistency verification methods for partition table statistics include:

[0078] Step S101: When a preset operation is received for a partition, or when the statistical information of the partition table corresponding to the partition is updated, the partition table corresponding to the partition is determined to be the target partition table. The preset operation includes database cleanup operation and database statistical information collection operation.

[0079] Specifically, the system first monitors the update behavior of partitioned table statistics in real time. When the database performs operations that may affect the accuracy of statistics, such as database cleanup operations to remove invalid data from partitions or database statistics collection operations to re-collect statistics, the system can immediately detect the impact of these operations on the partitions. Furthermore, when the system detects that the statistics of a partition are automatically updated by the database's internal mechanism or that a data change triggers a statistics refresh, it will also identify the change and use it as a trigger condition. After either of these trigger conditions occurs, the system automatically determines the partitioned table to which the partition belongs and uses this partitioned table as the target partitioned table for subsequent statistics consistency verification. Through this mechanism, the system can capture relevant events as soon as partition statistics change, ensuring that statistics consistency verification and subsequent deviation correction operations are performed on the target partitioned table, thereby effectively avoiding database optimizer misjudgments caused by untimely or inconsistent statistics updates.

[0080] Step S102: Obtain the partition-level statistical information and whole-table-level statistical information of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistical information and the whole-table-level statistical information. The partition-level statistical information includes at least the update time of the statistical information of each partition included in the target partition table, the row count statistics, and the data distribution information. The whole-table-level statistical information includes at least the whole-table row count statistics of the target partition table, the global data distribution information, and the whole-table statistical information update time.

[0081] Specifically, the partition-level statistics and table-wide statistics of the partitioned table are obtained first. Partition-level statistics reflect the data situation of each partition itself, including at least the update time of the statistics, the number of rows, and the data distribution information of key fields. Table-wide statistics reflect the overall data situation of the entire partitioned table, including at least the number of rows in the entire table, global data distribution information, and the update time of the entire table's statistics.

[0082] Step S103: If it is determined that the target partition table has the above-mentioned statistical deviation, determine the partition to be updated in the target partition table that has the statistical deviation, and update the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the partition to be updated.

[0083] Specifically, when a statistical discrepancy is detected between the partition-level statistics and the overall table-level statistics of the target partition table, the partition to be updated is further determined. Subsequently, based on the current data in the partition to be updated, the statistical information collection operation is re-executed for that partition to generate updated partition-level statistics. After obtaining the new partition-level statistics, the system then synchronously updates the overall table-level statistics according to the update results of each partition, ensuring consistency of the statistical information across the entire partition table.

[0084] In one exemplary embodiment of this application, obtaining partition-level statistics and whole-table statistics of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level statistics and whole-table statistics, includes: determining the row count statistics of multiple partitions corresponding to the target partition table, and determining the whole-table row count statistics of the target partition table; determining multiple ratios between each row count statistics and the whole-table row count statistics; and determining that the target partition table has a statistical bias if a target ratio is found among all the ratios, wherein the target ratio is a ratio greater than a preset ratio.

[0085] In one exemplary embodiment of this application, obtaining partition-level and whole-table-level statistical information of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level and whole-table-level statistical information, includes: determining the data distribution information of each partition corresponding to the target partition table, and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution bias index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information; and determining that the target partition table has a statistical bias if the data distribution bias index value is greater than a preset index value.

[0086] In one exemplary embodiment of this application, determining the data distribution information of each partition corresponding to the target partition table and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution deviation index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information includes: obtaining the data distribution information of the target field and the global data distribution information in each partition corresponding to the target partition table; determining the minimum value and maximum value of the first field based on the data distribution information of the target field; determining the minimum value and maximum value of the second field based on the global data distribution information of the target field; determining the third difference as the difference between the maximum value and the minimum value of the second field; determining the fourth difference as the difference between the minimum value and the minimum value of the first field; determining the fifth difference as the difference between the maximum value and the maximum value of the first field; determining the first quotient as the quotient of the fourth difference and the third difference; determining the second quotient as the quotient of the fifth difference and the third difference; and determining the data distribution deviation index of the target field as the sum of the first quotient and the second quotient.

[0087] In one exemplary embodiment of this application, obtaining partition-level statistical information and whole-table-level statistical information of the target partition table, and determining whether the target partition table has statistical deviation based on the partition-level statistical information and the whole-table-level statistical information, includes: determining multiple first differences as the difference between the update time of the statistical information of the multiple partitions corresponding to the target partition table and the update time of the whole-table-level statistical information; and determining that the target partition table has statistical deviation if the first difference is greater than a first preset difference.

[0088] In one exemplary embodiment of this application, determining the partition to be updated in the target partition table that has a statistical bias, and updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, includes: determining the partition to be updated in the target partition table that has a statistical bias; determining the current partition-level statistics of the partition to be updated, and updating the partition-level statistics of the partition to be updated based on the current data in the partition to be updated, to obtain the updated partition-level statistics; and determining the whole-table-level statistics of the target partition table based on the updated partition-level statistics, to obtain the updated whole-table-level statistics.

[0089] In one exemplary embodiment of this application, after updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, the method further includes: determining whether there is a statistical deviation in the target partition table based on the updated partition-level statistics and the updated whole-table-level statistics; if it is determined that there is no statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as passed; if it is determined that there is a statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as failed.

[0090] This invention provides a consistency verification system for partition table statistics, including a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, it performs at least the following steps:

[0091] Step S101: When a preset operation is received for a partition, or when the statistical information of the partition table corresponding to the partition is updated, the partition table corresponding to the partition is determined to be the target partition table. The preset operation includes database cleanup operation and database statistical information collection operation.

[0092] Specifically, the system first monitors the update behavior of partitioned table statistics in real time. When the database performs operations that may affect the accuracy of statistics, such as database cleanup operations to remove invalid data from partitions or database statistics collection operations to re-collect statistics, the system can immediately detect the impact of these operations on the partitions. Furthermore, when the system detects that the statistics of a partition are automatically updated by the database's internal mechanism or that a data change triggers a statistics refresh, it will also identify the change and use it as a trigger condition. After either of these trigger conditions occurs, the system automatically determines the partitioned table to which the partition belongs and uses this partitioned table as the target partitioned table for subsequent statistics consistency verification. Through this mechanism, the system can capture relevant events as soon as partition statistics change, ensuring that statistics consistency verification and subsequent deviation correction operations are performed on the target partitioned table, thereby effectively avoiding database optimizer misjudgments caused by untimely or inconsistent statistics updates.

[0093] Step S102: Obtain the partition-level statistical information and whole-table-level statistical information of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistical information and the whole-table-level statistical information. The partition-level statistical information includes at least the update time of the statistical information of each partition included in the target partition table, the row count statistics, and the data distribution information. The whole-table-level statistical information includes at least the whole-table row count statistics of the target partition table, the global data distribution information, and the whole-table statistical information update time.

[0094] Specifically, the partition-level statistics and table-wide statistics of the partitioned table are obtained first. Partition-level statistics reflect the data situation of each partition itself, including at least the update time of the statistics, the number of rows, and the data distribution information of key fields. Table-wide statistics reflect the overall data situation of the entire partitioned table, including at least the number of rows in the entire table, global data distribution information, and the update time of the entire table's statistics.

[0095] Step S103: If it is determined that the target partition table has the above-mentioned statistical deviation, determine the partition to be updated in the target partition table that has the statistical deviation, and update the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the partition to be updated.

[0096] Specifically, when a statistical discrepancy is detected between the partition-level statistics and the overall table-level statistics of the target partition table, the partition to be updated is further determined. Subsequently, based on the current data in the partition to be updated, the statistical information collection operation is re-executed for that partition to generate updated partition-level statistics. After obtaining the new partition-level statistics, the system then synchronously updates the overall table-level statistics according to the update results of each partition, ensuring consistency of the statistical information across the entire partition table.

[0097] In one exemplary embodiment of this application, obtaining partition-level statistics and whole-table statistics of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level statistics and whole-table statistics, includes: determining the row count statistics of multiple partitions corresponding to the target partition table, and determining the whole-table row count statistics of the target partition table; determining multiple ratios between each row count statistics and the whole-table row count statistics; and determining that the target partition table has a statistical bias if a target ratio is found among all the ratios, wherein the target ratio is a ratio greater than a preset ratio.

[0098] In one exemplary embodiment of this application, obtaining partition-level and whole-table-level statistical information of the target partition table, and determining whether the target partition table has a statistical bias based on the partition-level and whole-table-level statistical information, includes: determining the data distribution information of each partition corresponding to the target partition table, and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution bias index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information; and determining that the target partition table has a statistical bias if the data distribution bias index value is greater than a preset index value.

[0099] In one exemplary embodiment of this application, determining the data distribution information of each partition corresponding to the target partition table and determining the global data distribution information of the target partition table, wherein the data distribution information includes the value range of each partition; determining the data distribution deviation index value of each partition corresponding to the target partition table based on the data distribution information and the global data distribution information includes: obtaining the data distribution information of the target field and the global data distribution information in each partition corresponding to the target partition table; determining the minimum value and maximum value of the first field based on the data distribution information of the target field; determining the minimum value and maximum value of the second field based on the global data distribution information of the target field; determining the third difference as the difference between the maximum value and the minimum value of the second field; determining the fourth difference as the difference between the minimum value and the minimum value of the first field; determining the fifth difference as the difference between the maximum value and the maximum value of the first field; determining the first quotient as the quotient of the fourth difference and the third difference; determining the second quotient as the quotient of the fifth difference and the third difference; and determining the data distribution deviation index of the target field as the sum of the first quotient and the second quotient.

[0100] In one exemplary embodiment of this application, obtaining partition-level statistical information and whole-table-level statistical information of the target partition table, and determining whether the target partition table has statistical deviation based on the partition-level statistical information and the whole-table-level statistical information, includes: determining multiple first differences as the difference between the update time of the statistical information of the multiple partitions corresponding to the target partition table and the update time of the whole-table-level statistical information; and determining that the target partition table has statistical deviation if the first difference is greater than a first preset difference.

[0101] In one exemplary embodiment of this application, determining the partition to be updated in the target partition table that has a statistical bias, and updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, includes: determining the partition to be updated in the target partition table that has a statistical bias; determining the current partition-level statistics of the partition to be updated, and updating the partition-level statistics of the partition to be updated based on the current data in the partition to be updated, to obtain the updated partition-level statistics; and determining the whole-table-level statistics of the target partition table based on the updated partition-level statistics, to obtain the updated whole-table-level statistics.

[0102] In one exemplary embodiment of this application, after updating the partition-level statistics and the whole-table-level statistics based on the current partition-level statistics of the partition to be updated, the method further includes: determining whether there is a statistical deviation in the target partition table based on the updated partition-level statistics and the updated whole-table-level statistics; if it is determined that there is no statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as passed; if it is determined that there is a statistical deviation in the target partition table, marking the consistency status of the statistical information of the target partition table as failed.

[0103] The devices mentioned in this article can be servers, PCs, tablets, mobile phones, etc.

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

[0105] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0106] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0107] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0108] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0109] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0110] Memory may include non-persistent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, like read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0111] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0112] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0113] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0114] As can be seen from the above description, the embodiments of this application achieve the following technical effects:

[0115] The consistency verification method for partition table statistics in this application automatically identifies the corresponding partition table as the target partition table when a preset operation is detected in a partition or when the partition table statistics are updated. It then acquires the partition-level statistics and whole-table-level statistics of the target partition table in real time, thereby accurately identifying whether there is a statistical deviation between the partition-level statistics and the whole-table-level statistics as soon as the statistics change.

[0116] By comprehensively analyzing multiple dimensions of indicators such as statistical information update time, row count statistics, and data distribution information, this application can comprehensively determine whether there are abnormal updates, asynchrony, or data distribution imbalances in the statistical information of partitioned tables. If a statistical deviation is determined in the target partitioned table, this application further identifies the corresponding partition to be updated and updates the partition-level statistical information based on the current data in the partition to be updated, and correspondingly updates the overall table-level statistical information. Through this update strategy, this application can ensure consistency between the statistical information of each partition and the overall table statistical information, avoiding the database optimizer selecting an incorrect execution plan due to missing, outdated, or inconsistent statistical information. Therefore, this application can effectively prevent the optimizer from misjudging the data size and distribution in the table, significantly reducing the probability of SQL execution plan deviation, false full table scans, and abnormal index usage, improving the execution efficiency of SQL statements, reducing CPU (central processing unit) usage, and improving the overall database performance and service stability. In summary, by real-time monitoring, deviation detection, and consistency repair of partitioned table statistical information, this application can ensure the coordination and correctness of the statistical information of partitioned tables during the update process, thereby improving the accuracy and execution efficiency of database query optimization.

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

Claims

1. A method for consistency verification of statistical information in a partitioned table, characterized in that, include: Upon receiving a preset operation to be performed on a partition, or upon detecting an update to the statistics of the partition table corresponding to the partition, the partition table corresponding to the partition is determined to be the target partition table. The preset operation includes a database cleanup operation and a database statistics collection operation. Obtain the partition-level statistics and whole-table statistics of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistics and the whole-table statistics. The partition-level statistics include at least the update time of the statistics of each partition included in the target partition table, the row count statistics, and the data distribution information. The whole-table statistics include at least the whole-table row count statistics of the target partition table, the global data distribution information, and the update time of the whole-table statistics. If the statistical deviation is determined to exist in the target partition table, the partitions in the target partition table that have statistical deviations are identified as needing to be updated, and the partition-level statistics and the whole table-level statistics are updated according to the current partition-level statistics of the partitions to be updated.

2. The method according to claim 1, characterized in that, Obtaining partition-level and whole-table-level statistics of the target partition table, and determining whether there is a statistical bias in the target partition table based on the partition-level and whole-table-level statistics, including: Determine the row count statistics for the multiple partitions corresponding to the target partition table, and determine the total row count statistics for the target partition table; Determine multiple ratios between the statistical values ​​of each row count and the statistical values ​​of the total number of rows in the table; If a target ratio is found among all the ratios, it is determined that the target partition table has a statistical deviation, and the target ratio is the ratio that is greater than a preset ratio.

3. The method according to claim 1, characterized in that, Obtaining partition-level and whole-table-level statistics of the target partition table, and determining whether there is a statistical bias in the target partition table based on the partition-level and whole-table-level statistics, including: The data distribution information of each partition corresponding to the target partition table is determined, and the global data distribution information of the target partition table is determined, wherein the data distribution information includes the value range of each partition; Based on the data distribution information and the global data distribution information, determine the data distribution deviation index value of each partition corresponding to the target partition table; If the data distribution deviation index value is greater than the preset index value, it is determined that the target partition table has a statistical deviation.

4. The method according to claim 3, characterized in that, The data distribution information of each partition corresponding to the target partition table is determined, and the global data distribution information of the target partition table is determined, wherein the data distribution information includes the value range of each partition; Based on the data distribution information and the global data distribution information, the data distribution deviation index value of each partition corresponding to the target partition table is determined, including: Obtain the data distribution information and global data distribution information of the target field in each partition corresponding to the target partition table; The minimum and maximum values ​​of the first field are determined based on the data distribution information of the target field; The minimum value of the second field and the maximum value of the second field are determined based on the global data distribution information of the target field; The third difference is determined to be the difference between the maximum value and the minimum value of the second field. The fourth difference is determined to be the difference between the minimum value of the first field and the minimum value of the second field; The fifth difference is determined to be the difference between the maximum value of the second field and the maximum value of the first field; The first quotient is determined to be the quotient of the fourth difference and the third difference; The second quotient is determined to be the quotient of the fifth difference and the third difference; The data distribution deviation index of the target field is determined to be the sum of the first quotient and the second quotient.

5. The method according to claim 1, characterized in that, Obtaining partition-level and whole-table-level statistics of the target partition table, and determining whether there is a statistical bias in the target partition table based on the partition-level and whole-table-level statistics, including: Multiple first differences are determined as the differences between the update time of the statistical information of multiple partitions corresponding to the target partition table and the update time of the statistical information of the whole table; If the first difference is greater than the first preset difference, it is determined that the target partition table has a statistical deviation.

6. The method according to claim 1, characterized in that, Identify the partitions in the target partition table that have statistical biases and require updating, and update the partition-level statistics and the entire table-level statistics based on the current partition-level statistics of the partitions to be updated, including: Identify the partition to be updated in the target partition table that exhibits a statistical bias; Determine the current partition-level statistics of the partition to be updated, and update the partition-level statistics of the partition to be updated according to the current data in the partition to be updated, so as to obtain the updated partition-level statistics; Based on the updated partition-level statistics, the whole-table-level statistics of the target partition table are determined to obtain the updated whole-table-level statistics.

7. The method according to claim 1, characterized in that, After updating the partition-level statistics and the table-wide statistics based on the current partition-level statistics of the partition to be updated, the method further includes: Determine whether the target partition table has statistical bias based on the updated partition-level statistics and the updated whole-table-level statistics; If it is determined that there is no statistical bias in the target partition table, the statistical information consistency status of the target partition table is marked as passed; If it is determined that there is a statistical deviation in the target partition table, the consistency status of the statistical information of the target partition table is marked as failing.

8. A consistency verification device for partition table statistical information, characterized in that, include: The first determining unit is configured to determine the partition table corresponding to the partition as the target partition table when a preset operation is received for the partition, or when the statistical information of the partition table corresponding to the partition is updated. The preset operation includes a database cleanup operation and a database statistical information collection operation. The acquisition unit is used to acquire partition-level statistical information and whole-table-level statistical information of the target partition table, and determine whether there is a statistical deviation in the target partition table based on the partition-level statistical information and the whole-table-level statistical information. The partition-level statistical information includes at least the update time of statistical information, row count statistics, and data distribution information of each partition included in the target partition table. The whole-table-level statistical information includes at least the whole-table row count statistics, global data distribution information, and whole-table statistical information update time of the target partition table. The second determining unit is used to determine, when it is determined that the target partition table has the statistical deviation, the partition to be updated in the target partition table that has the statistical deviation, and update the partition-level statistical information and the whole table-level statistical information according to the current partition-level statistical information of the partition to be updated.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform the method according to any one of claims 1 to 7.

10. A consistency verification system for partitioned table statistical information, characterized in that, include: One or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising methods for performing any one of claims 1 to 7.

Images