Deep page turning scene optimization method, system and equipment based on database statistical information and medium

Abstract

The invention discloses a deep page turning scene optimization method, system and equipment based on database statistical information and a medium, belongs to the technical field of database query optimization, and aims to solve the technical problems of low database query performance and excessive resource consumption in a deep page turning scene. According to the technical scheme, the method comprises the following steps of: collecting and storing data statistical information: periodically collecting the statistical information of each column of data in a table in the operation process of a database system, and storing the statistical information in a special system table or a data dictionary; receiving and analyzing deep page query: when a deep page query request is received, analyzing a query statement, and determining table and column related to query, the initial position of a deep page and deep page parameter information of the data volume displayed on each page; generating a filtering condition based on statistical information; calculating the filtering condition corresponding to the data range before the initial position of the deep page according to the statistical information of the query column and the parameter information of the deep page; executing data query and filtering; and returning a result and monitoring performance.

Description

Technical Field

[0001] The present invention relates to the technical field of database query optimization, and in particular to a method, system, device and medium for optimizing deep page turning scenarios based on database statistical information. Background Art

[0002] In database applications, deep paging refers to the operation of querying the data on the later pages of the user's query result set. For example, in a table containing a large number of records, the user requests to view the record information on page 100 and later. Traditional deep paging queries often first obtain all the data of the first N pages (N is the number of pages before the deep paging start page), then discard these data, and only return the target data on and after the deep paging start page. When the amount of data is large, this method will consume a lot of system resources, including CPU, memory, and disk I / O, resulting in poor query performance and long response time, which seriously affects the user experience and the overall performance of the system. Especially in database systems that process massive data, the performance bottleneck of deep paging operations becomes more and more obvious. Summary of the invention

[0003] The technical task of the present invention is to provide a deep page flipping scenario optimization method, system, device and medium based on database statistical information to solve the problems of poor database query performance and excessive resource consumption in deep page flipping scenarios.

[0004] The technical task of the present invention is achieved in the following way: a deep page turning scene optimization method based on database statistical information, the method is specifically as follows:

[0005] Collect and store data statistics: During the operation of the database system, periodically collect the statistics of each column of data in the table and store the statistics in a special system table or data dictionary so that it can be quickly obtained during subsequent query optimization;

[0006] Receiving and parsing deep page queries: When receiving a deep page query request, parse the query statement to determine the tables and columns involved in the query, as well as the deep page parameter information of the starting position of the deep page and the amount of data displayed on each page;

[0007] Generate filtering conditions based on statistical information: Calculate the filtering conditions corresponding to the data range before the deep page start position based on the statistical information of the query column and the deep page parameter information;

[0008] Execute data query and filtering: query the database table through filtering conditions;

[0009] Result return and performance monitoring: The results of deep page query are returned to the user, and the performance of this deep page operation is monitored and recorded in the background to obtain monitoring data. The monitoring data is used for subsequent optimization and adjustment of statistical information collection strategies and filter condition generation algorithms to further improve performance.

[0010] Preferably, the data statistics are collected and stored as follows:

[0011] Enable the task of collecting statistics: set the time period when the database system load is lower than the set threshold to collect data statistics, such as in the early morning of each day or during a specific maintenance window each week; the collection frequency is determined based on the data update frequency and system resources. For databases with data updates greater than the set update frequency threshold, collect statistics based on the update frequency, such as once a day; and for databases with relatively stable data, collect statistics once a week or month;

[0012] Formulate a calculation method for statistical information: In Kaiwudb, column data is stored in data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information for the data block; then collect the column data distribution histogram information and the maximum, minimum, and number of data rows of the entire column data; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals (buckets), and then counting the number of data corresponding to each interval;

[0013] Store the collected statistics in dedicated system tables or data dictionaries.

[0014] As a preferred method, deep page query reception and analysis are as follows:

[0015] The database system receives a deep page query request from a client via a network interface;

[0016] Parse key query information and extract the names of the database tables and sorted columns involved from the query statement;

[0017] The deep page flipping parameters are parsed to determine the starting position of the deep page flipping and the amount of data displayed on each page; the deep page flipping parameters also include sorting direction (ascending or descending) information.

[0018] As a preferred method, the filtering conditions based on statistical information are generated as follows:

[0019] When the query is sorted by any column and the data distribution histogram information of the corresponding column and the deep page starting position "start_pos" are known, if it is sorted in ascending order, the number of data rows before the deep page starting position is "start_pos". The maximum value "max_val" before the starting position and the number of filtered data rows "filter_count" are found through the histogram statistics. The corresponding filtering condition is "column_name>max_val", and the starting position is changed to "start_pos-filter_count".

[0020] As a preference, query and filter are performed as follows:

[0021] When executing a query, the database engine scans and filters the database table according to the filtering conditions, and only obtains data that may be within the target range of deep page flipping. During the execution process, the column block statistics are used to quickly filter out data blocks that do not meet the conditions, without scanning the source table data; or other indexes are used (if there are suitable indexes for the columns involved in the filtering conditions) to quickly locate the data rows that meet the conditions.

[0022] Sort and paging operations according to query requirements;

[0023] Returns the target data of a deep page request.

[0024] Preferably, the result return and performance monitoring are as follows:

[0025] The target data obtained after deep page query and filtering is returned to the client in the format and protocol agreed upon by the database system and the client;

[0026] Monitor various performance indicators of this deep page operation, and record the performance indicators in a special performance log table or system monitoring data storage area; the performance indicators include query execution time, data transmission volume, and filtered data volume; query execution time refers to the total time from receiving the query request to returning the result; data transmission volume refers to the size of data returned to the client; the filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

[0027] A deep page flipping scene optimization system based on database statistical information, the system includes a statistical information management module, a query parsing module, a filtering condition generation module, a data query and filtering module, and a result return and performance monitoring module;

[0028] The statistical information management module provides a statistical basis for statistical information collection, updating and storage;

[0029] The query parsing module is used as an entry point to receive query requests and distribute the parsed deep page query requests to the filter condition generation module;

[0030] The filter condition generation module is used to calculate the filter condition corresponding to the data range before the deep page start position according to the statistical information of the query column provided by the statistical information management module and the deep page parameter information obtained by the query parsing module, and generate the filter condition based on the statistical information;

[0031] The data query and filtering module is used to perform data query and filtering operations based on filtering conditions;

[0032] The result return and performance monitoring module is used to output the final results and monitor performance data.

[0033] Preferably, the statistical information management module includes:

[0034] The statistical information collection task startup submodule is used to set the period when the database system load is lower than the set threshold to collect data statistical information, such as in the early morning of each day or a specific maintenance window time of each week; the collection frequency is determined according to the data update frequency and system resource conditions. For databases with data updates greater than the set update frequency threshold, statistical information is collected according to the update frequency, such as once a day; and for databases with relatively stable data, statistical information is collected once a week or month;

[0035] The calculation method of statistical information is formulated as a submodule, which is used to store column data in Kaiwudb according to data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information of the data block; then collect the column data distribution histogram information and the maximum, minimum and number of data rows of the entire column data; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals (buckets), and then counting the number of data corresponding to each interval;

[0036] The statistical information storage submodule is used to store the collected statistical information in a dedicated system table or data dictionary;

[0037] The query parsing module includes:

[0038] A deep page query request receiving submodule is used for the database system to receive a deep page query request from a client through a network interface;

[0039] The query information parsing and extraction submodule is used to parse key query information and extract the names of the database tables and the sorted columns involved from the query statement; for example, for the query statement "SELECT * FROM orders ORDER BY order_date LIMIT 100,20", the table name "orders" and the column name "order_date" are extracted;

[0040] The deep page parameter parsing submodule is used to parse the deep page parameters, determine the starting position of the deep page (such as 100 in the above example, indicating that the query starts from the 101st record) and the amount of data displayed per page (such as 20 in the above example); at the same time, the deep page parameters also include sorting direction (ascending or descending) information;

[0041] The filter condition generation module is as follows: when the query is sorted by any column, and the data distribution histogram information of the corresponding column and the deep page start position "start_pos" are known, if it is sorted in ascending order, the number of data rows before the deep page start position is "start_pos", and the maximum value "max_val" before the start position and the number of filtered data rows "filter_count" are found through the histogram statistics information. The corresponding filter condition is "column_name>max_val", and the start position is changed to "start_pos-filter_count";

[0042] The data query and filtering modules include:

[0043] The scanning and filtering submodule is used by the database engine to scan and filter the database table according to the filtering conditions when executing the query, and only obtain the data that may be within the target range of deep page turning; during the execution process, the column block statistics are used to quickly filter the data blocks that do not meet the conditions, without scanning the source table data; or other indexes are used (if the columns involved in the filtering conditions have suitable indexes) to quickly locate the data rows that meet the conditions;

[0044] Sorting and paging submodule, used to perform sorting and paging operations according to query requirements;

[0045] The target data return submodule is used to return the target data of the deep page request;

[0046] The result return and performance monitoring modules include:

[0047] The result return submodule is used to return the target data obtained after deep page query and filtering to the client in the format and protocol agreed upon by the database system and the client;

[0048] The monitoring submodule is used to monitor the various performance indicators of this deep page operation and record the performance indicators in a special performance log table or a system monitoring data storage area; among them, the performance indicators include query execution time, data transmission volume and filtered data volume; query execution time refers to the total time taken from receiving the query request to returning the result; data transmission volume refers to the size of data returned to the client; the filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

[0049] An electronic device comprising: a memory and at least one processor;

[0050] Wherein, the memory stores a computer program;

[0051] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the deep page turning scenario optimization method based on database statistical information as described above.

[0052] A computer-readable storage medium stores a computer program, which can be executed by a processor to implement the deep page turning scenario optimization method based on database statistical information as described above.

[0053] The deep page flipping scene optimization method, system, device and medium based on database statistical information of the present invention have the following advantages:

[0054] (1) The present invention periodically collects and stores statistical information of each column of data in the table, such as the minimum value, maximum value, histogram, number of data rows and other related statistical information, and then parses out key information after receiving a deep page query request; then generates filtering conditions based on the parsed information and the stored statistical information; then uses this filtering condition to query and filter data, first filtering the data and then sorting and paging to obtain the target data; finally, returns the result and monitors the performance, and records indicators such as query execution time, data transmission volume and filtering volume for subsequent optimization, thereby solving the key problems of poor database query performance and excessive resource consumption in deep page query scenarios, effectively using statistical information to filter a large amount of irrelevant data in advance during deep page query, significantly improving query speed, and reducing system resource consumption such as CPU, memory and disk I / O, thereby improving the overall performance and resource utilization of the database system in deep page query scenarios;

[0055] (ii) The present invention generates accurate filtering conditions based on statistical information, which can quickly filter out a large amount of irrelevant data at the beginning of the query, so that the database engine can directly focus on the retrieval and processing of the deep page target data, greatly shortening the query response time, significantly improving the overall efficiency of deep page query, and enabling users to obtain the desired data page more quickly;

[0056] (III) The present invention uses statistical information to filter data in advance, effectively reducing the amount of data processing, thereby reducing the computing load of the CPU, reducing memory usage, and alleviating disk I / O pressure, achieving efficient utilization of system resources, and improving the overall performance and stability of the database system when processing deep page queries, so that the database system can better cope with a large number of concurrent deep page requests under limited resources;

[0057] (IV) The present invention generates filtering conditions using statistical information in deep page flipping scenarios, thereby filtering out a large amount of unnecessary data in advance, effectively reducing the amount of data transmitted, memory usage, and CPU processing time, significantly improving the performance of deep page flipping operations, and improving user experience. It is particularly suitable for database systems that process massive amounts of data, and can efficiently meet users' deep page flipping query needs in a big data environment, thereby improving the operating efficiency and resource utilization of the entire database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] The present invention is further described below in conjunction with the accompanying drawings.

[0059] Attached Figure 1 A flowchart of a deep page flipping scenario optimization method based on database statistical information;

[0060] Attached Figure 2 A structural block diagram of the system for optimizing deep page flipping scenarios based on database statistics. DETAILED DESCRIPTION

[0061] The deep page turning scene optimization method, system, device and medium based on database statistical information of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments of the specification.

[0062] Embodiment 1:

[0063] As attached Figure 1 As shown, this embodiment provides a deep page flipping scenario optimization method based on database statistical information, and the method is specifically as follows:

[0064] S1. Collect and store data statistics: During the operation of the database system, periodically collect the statistics of each column of data in the table and store the statistics in a special system table or data dictionary so that it can be quickly obtained during subsequent query optimization.

[0065] S2. Receiving and parsing deep page query: When a deep page query request is received, the query statement is parsed to determine the tables and columns involved in the query, the starting position of the deep page, and the deep page parameter information of the amount of data displayed on each page;

[0066] S3, generating filtering conditions based on statistical information: calculating filtering conditions corresponding to the data range before the deep page start position according to the statistical information of the query column and the deep page parameter information;

[0067] S4. Execute data query and filtering: query the database table according to the filtering conditions;

[0068] S5. Result return and performance monitoring: The result of the deep page query is returned to the user, and the performance of this deep page operation is monitored and recorded in the background to obtain monitoring data. The monitoring data is used for subsequent optimization and adjustment of the statistical information collection strategy and the filter condition generation algorithm to further improve performance.

[0069] The specific details of collecting and storing data statistics in step S1 of this embodiment are as follows:

[0070] S101, start the task of collecting statistical information: set the time period when the database system load is lower than the set threshold to collect data statistical information, such as in the early morning of each day or a specific maintenance window time of each week; the collection frequency is determined according to the data update frequency and system resource conditions. For a database whose data update frequency is greater than the set update frequency threshold, collect statistical information according to the update frequency, such as once a day; and for a database with relatively stable data, collect statistical information once a week or a month;

[0071] S102. Formulate a calculation method for statistical information: In Kaiwudb, column data is stored in data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information of the data block; then collect the column data distribution histogram information and the maximum and minimum values ​​of the entire column data and the number of data rows; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals (buckets), and then counting the number of data corresponding to each interval;

[0072] In Kaiwudb, the method to establish data distribution histogram information is to determine the appropriate number and range of intervals (buckets), and then count the number of data corresponding to each interval. For example, for a column that stores student scores (score range 0-100), you can set 10 intervals, each interval is 10 points (0-10, 10-20, etc.). Then traverse the column data and count the number of data in each interval. When encountering data with a score of 85, add 1 to the count in the interval of 80-90. In this way, you can create a statistical information table, as shown in the following table:

[0073]

[0074] S103. Store the collected statistical information in a dedicated system table or data dictionary.

[0075] The receiving and parsing of the deep page query in step S2 of this embodiment are specifically as follows:

[0076] S202, the database system receives a deep page query request from a client through a network interface;

[0077] S203, parsing key query information, extracting the name of the database table involved and the name of the sorted column from the query statement; for example, for the query statement "SELECT * FROM orders ORDER BY order_date LIMIT 100,20", extracting the table name "orders" and the column name "order_date";

[0078] S204, parse the deep page parameters to determine the starting position of the deep page (such as 100 in the above example, indicating that the query starts from the 101st record) and the amount of data displayed per page (such as 20 in the above example); at the same time, the deep page parameters also include sorting direction (ascending or descending) information.

[0079] The specific generation of the filtering conditions based on statistical information in step S3 of this embodiment is as follows:

[0080] When the query is sorted by any column and the data distribution histogram information of the corresponding column and the deep page starting position "start_pos" are known, if it is sorted in ascending order, the number of data rows before the deep page starting position is "start_pos". The maximum value "max_val" before the starting position and the number of filtered data rows "filter_count" are found through the histogram statistics. The corresponding filtering condition is "column_name>max_val", and the starting position is changed to "start_pos-filter_count".

[0081] For example, using the data distribution histogram information table in step 1, for the query statement "SELECT*FROM scoresORDER BY score LIMIT 500000,20", the starting position is 500000, the data volume per page is 20, and in ascending order of score, according to the histogram information, the maximum value before the starting position is calculated to be 50, and the number of data rows filtered out is 500000. Then the query statement can be rewritten as "SELECT*FROM orders where score>50ORDER BY score LIMIT 0,20".

[0082] The execution query and filtering in step S4 of this embodiment are specifically as follows:

[0083] S401. When executing a query, the database engine scans and filters the database table according to the filtering conditions, and only obtains data that may be within the target range of deep page turning; during the execution process, the column block statistics are used to quickly filter the data blocks that do not meet the conditions, without scanning the source table data; or other indexes are used (if the columns involved in the filtering conditions have suitable indexes) to quickly locate the data rows that meet the conditions;

[0084] S402, sorting and paging operations are performed according to the query requirements;

[0085] S403: Return the target data of the deep page flipping request.

[0086] The result return and performance monitoring in step S5 of this embodiment are specifically as follows:

[0087] S501, returning the target data obtained after deep page query and filtering to the client according to the format and protocol agreed upon between the database system and the client;

[0088] S502. Monitor various performance indicators of this deep page operation, and record the performance indicators in a special performance log table or a system monitoring data storage area; wherein the performance indicators include query execution time, data transmission volume, and filtered data volume; query execution time refers to the total time taken from receiving a query request to returning a result; data transmission volume refers to the size of data returned to the client; filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

[0089] For example, create a table containing fields such as "query_id" (unique identifier of this query), "execution_time", "data_transferred", "filtered_data_amount", etc., and insert a record after each deep page operation. These records can be used for subsequent performance analysis and optimization. If you find that the execution time of a query is too long or the filtering effect is not good, you can further adjust the statistical information collection strategy or the filtering condition generation algorithm.

[0090] Embodiment 2:

[0091] As attached Figure 2 As shown, this embodiment provides a deep page flipping scenario optimization system based on database statistical information, which includes a statistical information management module, a query parsing module, a filtering condition generation module, a data query and filtering module, and a result return and performance monitoring module;

[0092] The statistical information management module provides a statistical basis for statistical information collection, updating and storage;

[0093] The query parsing module is used as an entry point to receive query requests and distribute the parsed deep page query requests to the filter condition generation module;

[0094] The filter condition generation module is used to calculate the filter condition corresponding to the data range before the deep page start position according to the statistical information of the query column provided by the statistical information management module and the deep page parameter information obtained by the query parsing module, and generate the filter condition based on the statistical information;

[0095] The data query and filtering module is used to perform data query and filtering operations based on filtering conditions;

[0096] The result return and performance monitoring module is used to output the final results and monitor performance data.

[0097] The statistical information management module in this embodiment includes:

[0098] The statistical information collection task startup submodule is used to set the period when the database system load is lower than the set threshold to collect data statistical information, such as in the early morning of each day or a specific maintenance window time of each week; the collection frequency is determined according to the data update frequency and system resource conditions. For databases with data updates greater than the set update frequency threshold, statistical information is collected according to the update frequency, such as once a day; and for databases with relatively stable data, statistical information is collected once a week or month;

[0099] The calculation method of statistical information is formulated as a submodule, which is used to store column data in Kaiwudb according to data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information of the data block; then collect the column data distribution histogram information and the maximum, minimum and number of data rows of the entire column data; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals (buckets), and then counting the number of data corresponding to each interval;

[0100] The statistical information storage submodule is used to store the collected statistical information in a dedicated system table or data dictionary.

[0101] The query parsing module in this embodiment includes:

[0102] A deep page query request receiving submodule is used for the database system to receive a deep page query request from a client through a network interface;

[0103] The query information parsing and extraction submodule is used to parse key query information and extract the names of the database tables and the sorted columns involved from the query statement; for example, for the query statement "SELECT * FROM orders ORDER BY order_date LIMIT 100,20", the table name "orders" and the column name "order_date" are extracted;

[0104] The deep page parameter parsing submodule is used to parse the deep page parameters, determine the starting position of the deep page (such as 100 in the above example, indicating that the query starts from the 101st record) and the amount of data displayed per page (such as 20 in the above example); at the same time, the deep page parameters also include sorting direction (ascending or descending) information;

[0105] The specific filtering condition generation module is as follows: when the query is sorted by any column, and the data distribution histogram information of the corresponding column and the starting position of the deep page "start_pos" are known, if it is sorted in ascending order, the number of data rows before the starting position of the deep page is "start_pos", and the maximum value "max_val" before the starting position and the number of filtered data rows "filter_count" are found through the histogram statistics. The corresponding filtering condition is "column_name>max_val", and the starting position is changed to "start_pos-filter_count".

[0106] The data query and filtering module in this embodiment includes:

[0107] The scanning and filtering submodule is used by the database engine to scan and filter the database table according to the filtering conditions when executing the query, and only obtain the data that may be within the target range of deep page turning; during the execution process, the column block statistics are used to quickly filter the data blocks that do not meet the conditions, without scanning the source table data; or other indexes are used (if the columns involved in the filtering conditions have suitable indexes) to quickly locate the data rows that meet the conditions;

[0108] The sorting and paging submodule is used to perform sorting and paging operations according to query requirements;

[0109] The target data return submodule is used to return the target data of the deep page request.

[0110] The result return and performance monitoring module in this embodiment includes:

[0111] The result return submodule is used to return the target data obtained after deep page query and filtering to the client in the format and protocol agreed upon by the database system and the client;

[0112] The monitoring submodule is used to monitor the various performance indicators of this deep page operation and record the performance indicators in a special performance log table or a system monitoring data storage area; among them, the performance indicators include query execution time, data transmission volume and filtered data volume; query execution time refers to the total time taken from receiving the query request to returning the result; data transmission volume refers to the size of data returned to the client; the filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

[0113] Embodiment 3:

[0114] This embodiment also provides an electronic device, including: a memory and a processor;

[0115] Wherein, the memory stores computer-executable instructions;

[0116] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the deep page turning scenario optimization method based on database statistical information in any embodiment of the present invention.

[0117] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor may be a microprocessor or any conventional processor, etc.

[0118] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.

[0119] Embodiment 4:

[0120] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the deep page flipping scene optimization method based on database statistical information in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0121] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0122] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0123] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0124] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0125] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A deep page flipping scenario optimization method based on database statistical information, characterized in that: The method is as follows: Collect and store data statistics: During the operation of the database system, periodically collect the statistics of each column of data in the table and store the statistics in a special system table or data dictionary; Receiving and parsing deep page queries: When receiving a deep page query request, parse the query statement to determine the tables and columns involved in the query, as well as the deep page parameter information of the starting position of the deep page and the amount of data displayed on each page; Generate filtering conditions based on statistical information: Calculate the filtering conditions corresponding to the data range before the deep page start position based on the statistical information of the query column and the deep page parameter information; Execute data query and filtering: query the database table through filtering conditions; Result return and performance monitoring: The result of deep page query is returned to the user, and the performance of this deep page operation is monitored and recorded in the background to obtain monitoring data. The monitoring data is used for subsequent optimization and adjustment of statistical information collection strategy and filter condition generation algorithm.

2. The deep page flipping scene optimization method based on database statistical information according to claim 1 is characterized in that: The data statistics collected and stored are as follows: Enable the task of collecting statistical information: set the period when the database system load is lower than the set threshold to collect data statistical information; the collection frequency is determined according to the data update frequency and system resources. For databases with data updates greater than the set update frequency threshold, statistical information is collected according to the update frequency; for databases with relatively stable data, statistical information is collected once a week or month; Formulate a calculation method for statistical information: In Kaiwudb, column data is stored in data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information for the data block; Then collect the column data distribution histogram information and the maximum and minimum values ​​of the entire column data and the number of data rows; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals, and then counting the number of data corresponding to each interval; Store the collected statistics in dedicated system tables or data dictionaries.

3. The deep page flipping scene optimization method based on database statistical information according to claim 1 is characterized in that: The details of receiving and parsing deep page queries are as follows: The database system receives a deep page query request from a client via a network interface; Parse key query information and extract the names of the database tables and sorted columns involved from the query statement; The deep page flipping parameters are parsed to determine the starting position of the deep page flipping and the amount of data displayed on each page; the deep page flipping parameters also include sorting direction information.

4. The deep page flipping scene optimization method based on database statistical information according to claim 1 is characterized in that: Generate the following filtering conditions based on statistics: When the query is sorted by any column, and the data distribution histogram information of the corresponding column and the deep page start position "start_pos" are known, if it is sorted in ascending order, the number of data rows before the deep page start position is "start_pos", and the maximum value "max_val" before the start position and the number of filtered data rows "filter_count" are found through the histogram statistics. The corresponding filtering condition is "column_name>max_val", and the start position is changed to "start_pos-filter_count".

5. The deep page flipping scene optimization method based on database statistical information according to claim 1 is characterized in that: Execute query and filter as follows: When executing a query, the database engine scans and filters the database table according to the filtering conditions, and only obtains data that may be within the target range of deep page flipping. During the execution process, the column block statistics are used to quickly filter out data blocks that do not meet the conditions, without scanning the source table data; or other indexes are used to quickly locate data rows that meet the conditions. Sort and paging operations according to query requirements; Returns the target data of a deep page request.

6. The deep page flipping scenario optimization method based on database statistical information according to any one of claims 1 to 5, characterized in that: The results returned and performance monitoring are as follows: The target data obtained after deep page query and filtering is returned to the client in the format and protocol agreed upon by the database system and the client; Monitor various performance indicators of this deep page operation, and record the performance indicators in a special performance log table or system monitoring data storage area; the performance indicators include query execution time, data transmission volume, and filtered data volume; query execution time refers to the total time from receiving the query request to returning the result; data transmission volume refers to the size of data returned to the client; the filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

7. A deep page flipping scene optimization system based on database statistical information, characterized in that: The system includes a statistical information management module, a query parsing module, a filtering condition generation module, a data query and filtering module, and a result return and performance monitoring module; The statistical information management module provides a statistical basis for statistical information collection, updating and storage; The query parsing module is used as an entry point to receive query requests and distribute the parsed deep page query requests to the filter condition generation module; The filter condition generation module is used to calculate the filter condition corresponding to the data range before the deep page start position according to the statistical information of the query column provided by the statistical information management module and the deep page parameter information obtained by the query parsing module, and generate the filter condition based on the statistical information; The data query and filtering module is used to perform data query and filtering operations based on filtering conditions; The result return and performance monitoring module is used to output the final results and monitor performance data.

8. The deep page flipping scene optimization system based on database statistical information according to claim 7 is characterized in that: The statistics management module includes: The statistical information collection task start submodule is used to set the period when the database system load is lower than the set threshold to collect data statistical information; the collection frequency is determined according to the data update frequency and system resource conditions. For databases with data updates greater than the set update frequency threshold, statistical information is collected according to the update frequency; for databases with relatively stable data, statistical information is collected once a week or month; The calculation method of statistical information is formulated as a submodule, which is used to store column data in Kaiwudb according to data blocks, with one data block for every 1,000 rows; collect the minimum and maximum values ​​of each data block to establish statistical information of the data block; then collect the column data distribution histogram information and the maximum, minimum and number of data rows of the entire column data; in Kaiwudb, the data distribution histogram information is established by determining the appropriate number and range of intervals, and then counting the number of data corresponding to each interval; The statistical information storage submodule is used to store the collected statistical information in a dedicated system table or data dictionary; The query parsing module includes: A deep page query request receiving submodule is used for the database system to receive a deep page query request from a client through a network interface; The query information parsing and extraction submodule is used to parse key query information and extract the names of the database tables and sorted columns involved from the query statement; The deep page flip parameter parsing submodule is used to parse the deep page flip parameters to determine the starting position of the deep page flip and the amount of data displayed on each page; the deep page flip parameters also include sorting direction information; The filter condition generation module is as follows: when the query is sorted by any column, and the data distribution histogram information of the corresponding column and the deep page start position "start_pos" are known, if it is sorted in ascending order, the number of data rows before the deep page start position is "start_pos", and the maximum value "max_val" before the start position and the number of filtered data rows "filter_count" are found through the histogram statistics information. The corresponding filter condition is "column_name>max_val", and the start position is changed to "start_pos-filter_count"; The data query and filtering modules include: The scanning and filtering submodule is used by the database engine to scan and filter the database table according to the filtering conditions when executing the query, and only obtain the data that may be within the target range of the deep page turning; during the execution process, the column block statistics are used to quickly filter the data blocks that do not meet the conditions without scanning the source table data; or other indexes are used to quickly locate the data rows that meet the conditions; The sorting and paging submodule is used to perform sorting and paging operations according to query requirements; The target data return submodule is used to return the target data of the deep page request; The result return and performance monitoring modules include: The result return submodule is used to return the target data obtained after deep page query and filtering to the client in the format and protocol agreed upon by the database system and the client; The monitoring submodule is used to monitor the various performance indicators of this deep page operation and record the performance indicators in a special performance log table or a system monitoring data storage area; among them, the performance indicators include query execution time, data transmission volume and filtered data volume; query execution time refers to the total time taken from receiving the query request to returning the result; data transmission volume refers to the size of data returned to the client; the filtered data volume refers to the amount of data obtained by comparing with the amount of data that may be queried when no filtering conditions are used.

9. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the deep page turning scenario optimization method based on database statistical information as described in any one of claims 1 to 6.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the deep page turning scenario optimization method based on database statistical information as described in any one of claims 1 to 6.

Images