Paging retrieval method and system for inclined work order under large data volume condition

Abstract

The invention relates to the technical field of communication, in particular to a paging retrieval method and system for inclined work orders under the condition of large data volume, and the method comprises the following steps: collecting distribution information of high-frequency key values in a work order data table stored in a relational database by using a frequency distribution histogram; data distribution of the high-frequency key values in partitions and dates is explored by analyzing distribution statistical information of the high-frequency key values; according to the date range and the high-frequency key value in the query condition, dynamically planning and constructing a paging query SQL statement; the method has the beneficial effects that partitions are established for a work order data table in weeks or months, and database statistical information collection is started for key fields; collecting statistical information of high-frequency key values by taking partitions and dates as granularities in a frequency histogram mode; according to partition and date distribution of high-frequency key values, a query sql is dynamically planned for paging query, so that the partition can be accurately used, and data retrieval and sorting can be performed in a small range.

Description

Technical Field

[0001] The present invention relates to the field of communication technologies, and particularly to a method and system for paging and retrieving skewed work orders in the case of a large amount of data. Background Art

[0002] In the work order system of telecom operators, there are a large number of different types of work orders, and the distribution of these work order data is skewed; especially when the query time period spans a large range and a key value with serious data skew is used for query on the front-end interface, the database response will be slow due to the query of a large amount of data; even if paging is adopted on the front-end interface, sorting all the data will also cause the database response to be slow, resulting in the front-end interface response timing out, and at the same time affecting the normal operation of other services and the customer experience. Summary of the Invention

[0003] The purpose of the present invention is to provide a method and system for paging and retrieving skewed work orders in the case of a large amount of data, so as to solve the problems raised in the above background art.

[0004] To achieve the above purpose, the present invention provides the following technical solution: A method for paging and retrieving skewed work orders in the case of a large amount of data, the method comprising the following steps:

[0005] a) Use a frequency distribution histogram to collect the distribution information of high-frequency key values in the work order data table stored in a relational database. The work order data table is partitioned and stored by week or month according to the work order volume, and the work order volume of each partition does not exceed one million;

[0006] b) By analyzing the distribution statistical information of the high-frequency key values, explore the data distribution of the high-frequency key values in terms of partitions and dates;

[0007] c) According to the date range and high-frequency key values in the query conditions, dynamically plan and construct a paging query SQL statement, and the SQL statement specifies the partitions and dates used for the query to avoid full-scale retrieval and sorting of large amounts of data and improve the query efficiency.

[0008] Preferably, the work order data table in the relational database in step a) is partitioned and stored by month to store work order data for multiple years, and the work order data of each month is stored in an independent partition.

[0009] Preferably, the exploration of the data distribution of the high-frequency key values in terms of partitions and dates in step b) includes:

[0010] Identify the high-frequency key values with data skew in the work order data table;

[0011] In the form of a frequency histogram, the statistical information of the high-frequency key values is collected and stored daily at the granularity of partitions and dates. The statistical information includes attribute values, attribute names, partition identifiers, dates, and the corresponding number of rows.

[0012] Preferably, the dynamic programming in step c) to construct the paging query SQL statement includes:

[0013] c1) According to the date range and high-frequency key values in the query conditions, using the statistical information of the high-frequency key values, filter out the partition statistical information that meets the query conditions;

[0014] c2) Calculate the start row and end row corresponding to the subset of query results included in each date within each partition;

[0015] c3) According to the conditions of the paging query, locate the partition and date where the work order data that meets the conditions is located;

[0016] c4) Construct an SQL statement based on the statistical information, and specify the partitions and dates used in this query in the SQL statement.

[0017] Preferably, constructing the SQL statement based on the statistical information in step c4) further includes specifying the sorting method of the query results in the SQL statement to further improve the query efficiency and customer perception.

[0018] A paging retrieval system for skewed work orders in the case of a large amount of data is applied to a paging retrieval method for skewed work orders in the case of a large amount of data. The system includes:

[0019] An information collection module that uses a frequency distribution histogram to collect the distribution information of high-frequency key values in the work order data table stored in a relational database. The work order data table is partitioned and stored weekly or monthly according to the work order volume, and the work order volume of each partition does not exceed one million;

[0020] A data distribution exploration module that explores the data distribution of high-frequency key values on partitions and dates by analyzing the distribution statistical information of the high-frequency key values;

[0021] A dynamic programming module that dynamically plans and constructs a paging query SQL statement according to the date range and high-frequency key values in the query conditions. The SQL statement specifies the partitions and dates used in the query to avoid full-scale retrieval and sorting of large amounts of data and improve the query efficiency.

[0022] Preferably, the work order data table in the relational database in the information collection module is partitioned and stored monthly to store work order data for multiple years, and the work order data for each month is stored in an independent partition.

[0023] Preferably, the exploration of the data distribution of the high-frequency key values in the data distribution exploration module includes:

[0024] Identifying the high-frequency key values with data skew in the work order data table;

[0025] In the form of a frequency histogram, taking partitions and dates as granularity, collecting and storing the statistical information of the high-frequency key values every day, and the statistical information includes attribute values, attribute names, partition identifiers, dates, and the corresponding number of rows.

[0026] Preferably, the dynamic programming and construction of the paging query SQL statement in the data distribution exploration module includes:

[0027] According to the date range and high-frequency key values in the query conditions, using the statistical information of the high-frequency key values, filtering out the partition statistical information that meets the query conditions;

[0028] Calculating the start row and end row corresponding to the query result subset included in each date within each partition;

[0029] According to the conditions of the paging query, locating the partition and date where the work order data that meets the conditions is located;

[0030] Constructing an SQL statement based on the statistical information, and specifying the partitions and dates used in this query in the SQL statement.

[0031] Preferably, constructing the SQL statement based on the statistical information further includes specifying the sorting method of the query results in the SQL statement to further improve the query efficiency and customer perception.

[0032] Compared with the prior art, the beneficial effects of the present invention are:

[0033] The paging retrieval method and system for skewed work orders in the case of a large amount of data proposed by the present invention, by partitioning the work order data table by week or month and enabling the collection of database statistical information for key fields; collecting statistical information of high-frequency key values in the form of a frequency histogram with partitions and dates as granularity; dynamically programming the query sql for paging queries according to the partition and date distribution of high-frequency key values, enabling it to accurately use partitions and perform data retrieval and sorting within a small range. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 It is a flow chart of the method of the present invention. DETAILED DESCRIPTION OF THE INVENTION

[0035] In order to clearly and completely describe the objectives, technical solutions of the present invention, and make the advantages more clearly understood, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are part of the embodiments of the present invention, rather than all of the embodiments, and are only used to explain the embodiments of the present invention, not to limit the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art without creative efforts fall within the scope of protection of the present invention.

[0036] Please refer to Figure 1 , the present invention provides a technical solution: a method and system for paging retrieval of tilted work orders in the case of a large amount of data, specifically including:

[0037] 1) Partitioned storage of the work order data table

[0038] In a relational database, partitioned tables are established according to the work order volume on a weekly or monthly basis for storing work order data. In principle, the work order volume of each partition does not exceed one million.

[0039] For example: In the work_oder_done table of the completed work orders, there are 2 million work orders per month, and the customer requires storing for 4 years, with a total of 96 million work order information; then the partition table can be created monthly.

[0040] 2) Identify high-frequency key values

[0041] By analyzing the key value distribution statistics, high-frequency key values are identified.

[0042] For example: In the work_oder_done table of the completed work orders, there are 96 million work order records, and there is data skew in the key value of the work order type field STANDARD_SERVICE_NAME; among them, the proportion of the key value data volume of FV SURVEY exceeds 10%, that is, 9.6 million records, and FV SURVEY is considered a high-frequency key value.

[0043] 3) Collect the distribution of high-frequency key values in partitions and dates

[0044] The high-frequency key values are collected and statistically analyzed in the form of a frequency histogram, with partitions and dates as the granularity, and statistical information is collected every day.

[0045] For example: In the work_oder_done table of the completed work orders, there is data skew in the key value of the work order type field STANDARD_SERVICE_NAME; the statistical information of the high-frequency key value FV SURVEY of STANDARD_SERVICE_NAME can be collected and stored in the HighFrequency table. The data example in the HighFrequency table is as follows:

[0046]

[0047]

[0048]

[0049] 4) Paging query for high-frequency key values in dynamic programming

[0050] a) Calculate the total number of rows in the query result set

[0051] Based on the statistical information of high-frequency value data, after filtering out the high-frequency value data distribution within the corresponding date range through the date range and high-frequency numerical values in the query conditions, the total number of rows in the result data set is obtained through summarization.

[0052] If, in addition to the high-frequency value field and date field in the query conditions, there are also other subsidiary fields, it is necessary to explore the data in each partition before determining the number of rows in the query result. In this case, the total number of rows in the query result set cannot be calculated based on the statistical information of high-frequency data values, and the number of rows in the query result set is marked as Many.

[0053] b) Explore the partition and date where the paged data is located

[0054] First, based on the date range and high-frequency key values in the query conditions, use the statistical information of high-frequency value data to filter out the partition statistical information that meets the query conditions.

[0055] For example: Query the partition and date where the work orders of the FVSUSVE type are located in the time period from August 1, 2024 to August 7, 2024.

[0056]

[0057]

[0058] Secondly, calculate the start row and end row corresponding to the query result subset included in each date within each partition.

[0059] For example: Query the start row and end row of the work orders of the FVSUSVE type in each partition in the time period from August 1, 2024 to August 7, 2024. The statistics are as follows:

[0060]

[0061]

[0062] Then, based on the paging query conditions, locate the partition and date where the work order data that meets the conditions is located.

[0063] c) Construct SQL based on statistical information

[0064] When performing a paged query, specify the partitions and dates used in this query in the SQL statement to avoid retrieving and sorting large amounts of data, reduce IO and CPU time, and improve query efficiency.

[0065] Although the embodiments of the present invention have been shown and described, those of ordinary skill in the art can understand that various changes, modifications, substitutions, and variations can be made to these embodiments without departing from the principles and spirit of the present invention. The scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A paging retrieval method for tilted work orders in the case of large amounts of data, characterized by: The method comprises the following steps: a) Use frequency distribution histograms to collect the distribution information of high-frequency key values ​​in the work order data table stored in the relational database. The work order data table is partitioned by week or month according to the work order volume, and the work order volume in each partition does not exceed one million; b) exploring the data distribution of the high-frequency key values ​​on partitions and dates by analyzing the distribution statistics of the high-frequency key values; c) Dynamically plan and construct paging query SQL statements based on the date range and high-frequency key values ​​in the query conditions. The SQL statements specify the partitions and dates used in the query to avoid full retrieval and sorting of big data and improve query efficiency.

2. According to claim 1, a paging retrieval method for tilted work orders in the case of large amounts of data is characterized by: The work order data table in the relational database in step a) is partitioned and stored by month to store work order data for multiple years, wherein the work order data for each month is stored in an independent partition.

3. According to claim 1, a paging retrieval method for tilted work orders in the case of large amounts of data, characterized in that: Step b) explores the data distribution of high-frequency key values ​​on partitions and dates, including: Identify high-frequency key values ​​with data skew in the work order data table; The statistical information of the high-frequency key values ​​is collected and stored every day in the form of a frequency histogram with partition and date as the granularity. The statistical information includes attribute value, attribute name, partition identifier, date and corresponding number of rows.

4. According to claim 1, a paging retrieval method for tilted work orders in the case of large amounts of data is characterized in that: The dynamic planning and construction of the paging query SQL statement in step c) includes: c1) Based on the date range and high-frequency key values ​​in the query conditions, use the statistical information of the high-frequency key values ​​to filter out the partition statistical information that meets the query conditions; c2) Calculate the start and end rows corresponding to the query result subset contained in each date in each partition; c3) According to the paging query conditions, locate the partition and date where the work order data that meets the conditions is located; c4) constructing an SQL statement based on the statistical information, and specifying the partition and date used for this query in the SQL statement.

5. A paging retrieval method for tilted work orders in the case of large amounts of data according to claim 4, characterized in that: The step c4) of constructing an SQL statement based on the statistical information also includes specifying a sorting method of the query results in the SQL statement to further improve the query efficiency and customer perception.

6. A paging retrieval system for tilted work orders in the case of large amounts of data, applied to a paging retrieval method for tilted work orders in the case of large amounts of data as described in any one of claims 1 to 5, characterized in that: The system comprises: The information collection module uses a frequency distribution histogram to collect the distribution information of high-frequency key values ​​in the work order data table stored in the relational database. The work order data table is partitioned and stored by week or month according to the work order volume, and the work order volume in each partition does not exceed one million; A data distribution exploration module, which explores the data distribution of high-frequency key values ​​on partitions and dates by analyzing the distribution statistics of the high-frequency key values; The dynamic planning module dynamically plans and constructs paging query SQL statements based on the date range and high-frequency key values ​​in the query conditions. The SQL statements specify the partitions and dates used in the query to avoid full retrieval and sorting of big data and improve query efficiency.

7. A paging retrieval system for tilted work orders in the case of large amounts of data according to claim 6, characterized in that: The work order data table in the relational database in the information collection module is partitioned and stored by month to store work order data for multiple years, wherein the work order data for each month is stored in an independent partition.

8. A paging retrieval system for tilted work orders in the case of large amounts of data according to claim 6, characterized in that: The data distribution exploration module explores the data distribution of high-frequency key values ​​on partitions and dates, including: Identify high-frequency key values ​​with data skew in the work order data table; The statistical information of the high-frequency key values ​​is collected and stored every day in the form of a frequency histogram with partition and date as the granularity. The statistical information includes attribute value, attribute name, partition identifier, date and corresponding number of rows.

9. A paging retrieval system for tilted work orders in the case of large amounts of data according to claim 6, characterized in that: The dynamic planning and construction of paging query SQL statements in the data distribution exploration module include: Based on the date range and high-frequency key values ​​in the query conditions, use the statistics of the high-frequency key values ​​to filter out the partition statistics that meet the query conditions; Calculate the start and end rows of the query result subsets contained in each date in each partition; According to the paging query conditions, locate the partition and date where the work order data that meets the conditions is located; An SQL statement is constructed based on the statistical information, and the partition and date used for this query are specified in the SQL statement.

10. A paging retrieval system for tilted work orders in the case of large amounts of data according to claim 9, characterized in that: Constructing SQL statements based on statistical information also includes specifying the sorting method of query results in the SQL statement to further improve query efficiency and customer perception.

Images