Database statistical information updating method and device, computer equipment, readable storage medium and program product

Abstract

The invention relates to a database statistical information updating method and device, computer equipment, a readable storage medium and a program product. The method comprises the following steps: continuously updating an incremental information variable in a process of executing a structured query language (SQL) statement of data change; after the incremental information variable is updated, reading the stock information file, and updating the incremental information to the corresponding stock information to obtain updated stock information; the stock information is used for recording data characteristics of each column in the current table, and the increment information is used for recording data characteristics updated this time; determining statistical information based on the updated stock information to obtain updated statistical information; and storing the updated statistical information in a specified statistical information table. Therefore, the influence on the operation efficiency caused by inaccurate statistical information can be avoided, and the scheme cost of each path in the physical optimization process can be accurately calculated, so that the optimal execution plan is selected.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, computer device, computer-readable storage medium, and computer program product for updating database statistical information. Background Technology

[0002] When generating an execution plan, PostgreSQL (Object Relational Database Management System) generates different path schemes based on table access methods, join methods, and join order. It also needs to calculate the physical cost of each path and select the least costly one to ensure that the database performance requirements are met.

[0003] In related technologies, PostgreSQL provides two ways to update statistics: Analyze and Autovacuum. Analyze is a manual method, where statistics are updated on the target table by entering commands; Autovacuum is an automatic method, where the database determines whether the current situation has reached a cleanup threshold. Both update methods are based on random sampling of the overall data and calculating the corresponding statistics based on the sample.

[0004] However, when faced with scenarios involving large amounts of data and frequent updates, the two methods of updating statistical information mentioned above may result in problems such as untimely updates and sampling bias, affecting the execution efficiency of Structured Query Language (SQL) statements. Summary of the Invention

[0005] Therefore, it is necessary to provide a database statistical information updating method, apparatus, computer equipment, computer-readable storage medium, and computer program product that can improve the frequency and accuracy of statistical information updates and ensure the execution efficiency of SQL statements, in order to address the above-mentioned technical problems.

[0006] Firstly, this application provides a method for updating database statistical information, the method comprising:

[0007] During the execution of Structured Query Language (SQL) statements that modify data, incremental information variables are continuously updated;

[0008] After the incremental information variable is updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0009] Based on the updated stock information, statistical information is determined to obtain the updated statistical information;

[0010] Store the updated statistics in the specified statistics table.

[0011] In one embodiment, the method further includes, before executing the SQL statement that modifies the data:

[0012] With the database running, check if the attribute columns of the data tables are missing the corresponding inventory information file;

[0013] Without missing the corresponding existing information files, start executing the SQL statements for data modification and initialize the incremental information variables;

[0014] In the absence of the corresponding existing information file, after generating the existing information file in parallel using either a full table scan or a sampling scan method, the SQL statements for data modification are executed, and the incremental information variables are initialized.

[0015] The existing information and the incremental information use the same variable name.

[0016] In one embodiment, after updating the incremental information to the corresponding stock information to obtain the updated stock information, the method further includes: periodically writing the updated stock information into a stock information file, the stock information file being used to store the stock information;

[0017] Before storing the updated statistics in the specified statistics table, the method further includes: temporarily storing the updated statistics in a data queue or pipeline.

[0018] In one embodiment, after updating the incremental information to the corresponding existing information to obtain the updated existing information, the method further includes:

[0019] After the SQL statement that changed the data has finished executing, clear the remaining existing and incremental information.

[0020] In one embodiment, determining the statistical information based on the updated stock information to obtain the updated statistical information includes:

[0021] Based on the updated stock information, determine at least one of the following statistical information in the database table: the proportion of null values, the average width of columns, the number or proportion of duplicate attributes after deduplication, high-frequency values, equal-frequency histograms, correlation coefficients, high-frequency values ​​of arrays or other types, histograms of array types, histogram statistics based on length generated by range type, and histogram statistics based on boundaries of range type.

[0022] In one embodiment, when it is necessary to perform statistical analysis on the correlation coefficient, the method further includes:

[0023] Determine the number of samples for each layer of the database table;

[0024] Given that the number of samples at each layer meets the requirements, the correlation coefficient is calculated based on the object-relational database management system PostgreSQL.

[0025] If the number of samples in each layer does not meet the requirements, pages of the database table are randomly selected, and each selected page is sampled in layers until the number of samples in each layer meets the requirements. Then, the correlation coefficient is calculated based on PostgreSQL.

[0026] Secondly, this application also provides a database statistical information updating device, the device comprising:

[0027] The first update module is used to continuously update incremental information variables during the execution of structured query language (SQL) statements that change data.

[0028] The second update module is used to read the existing information file after the incremental information variable update is completed, and update the incremental information into the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0029] The statistics module is used to determine statistical information based on the updated stock information, and obtain the updated statistical information;

[0030] The storage module is used to store updated statistics into a specified statistics table.

[0031] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0032] During the execution of Structured Query Language (SQL) statements that modify data, incremental information variables are continuously updated;

[0033] After the incremental information variable is updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0034] Based on the updated stock information, statistical information is determined to obtain the updated statistical information;

[0035] Store the updated statistics in the specified statistics table.

[0036] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:

[0037] During the execution of Structured Query Language (SQL) statements that modify data, incremental information variables are continuously updated;

[0038] After the incremental information variable is updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0039] Based on the updated stock information, statistical information is determined to obtain the updated statistical information;

[0040] Store the updated statistics in the specified statistics table.

[0041] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:

[0042] During the execution of Structured Query Language (SQL) statements that modify data, incremental information variables are continuously updated;

[0043] After the incremental information variable is updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0044] Based on the updated stock information, statistical information is determined to obtain the updated statistical information;

[0045] Store the updated statistics in the specified statistics table.

[0046] The aforementioned database statistical information update method, apparatus, computer equipment, computer-readable storage medium, and computer program product continuously update incremental information variables during the execution of Structured Query Language (SQL) statements that modify data, thereby increasing the frequency of statistical information updates. After the incremental information variables are updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information. The existing information records the data characteristics of each column in the current table, while the incremental information records the data characteristics of this update. Therefore, a combination of existing and incremental information can be used to update statistical information, achieving near real-time statistical updates. Based on the updated existing information, statistical information is determined, resulting in the updated statistical information. The updated statistical information is then stored in a designated statistical information table. This avoids the impact of inaccurate statistical information on operational efficiency, ensuring accurate calculation of the cost of each path scheme during physical optimization, and thus selecting the optimal execution plan. Attached Figure Description

[0047] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0048] Figure 1 This is an application environment diagram of a database statistics update method in one embodiment;

[0049] Figure 2 This is a flowchart illustrating a database statistics update method in one embodiment;

[0050] Figure 3 This is a flowchart illustrating a database statistics update method in another embodiment;

[0051] Figure 4 This is a flowchart illustrating the database statistics update method in yet another embodiment;

[0052] Figure 5 This is a flowchart illustrating the calculation of statistical information in one embodiment of this application;

[0053] Figure 6 This is a schematic diagram of the random stratified sampling process in one embodiment of this application;

[0054] Figure 7 This is a structural block diagram of a database statistics information update device in one embodiment;

[0055] Figure 8This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0056] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0057] It should be noted that the terms "comprising" and "having," and any variations thereof, as used in this application, are intended to cover non-exclusive inclusion. The term "multiple" as used in this application refers to two or more. The term "and / or" as used in this application refers to one of the solutions, or any combination of multiple solutions.

[0058] To facilitate understanding of the technical solutions in the various embodiments of this application, the following is a brief explanation of the technical terms that may appear in the various embodiments of this application:

[0059] Selection rate: Based on statistical information, the proportion of data that can be filtered by a single constraint out of the total data volume is estimated. The calculation formula is as follows:

[0060]

[0061] PostgreSQL is a free object-relational database server (database management system). Analyze and Autovacuum are two important functions in the database management system used to maintain database performance and space utilization.

[0062] The Analyze function is primarily used to update table statistics. These statistics are collected by the ANALYZE command and can help the query optimizer generate more efficient execution plans.

[0063] Autovacuum is a background process for PostgreSQL databases that starts automatically with the database. Its functions include cleaning up expired dead tuples in tables, preventing business tables from growing excessively, and updating table statistics for the optimizer. Autovacuum determines when to run based on preset conditions (such as the number of tuple changes and table size ratio). Users can also optimize its behavior by adjusting relevant Autovacuum parameters to suit different workload requirements.

[0064] When generating an execution plan, PostgreSQL generates different path schemes based on table access methods, join methods, and join order. It needs to calculate the physical cost of each path and select the least expensive one to ensure database performance requirements are met. Cost calculation mainly relies on selectivity, number of pages accessed (I / O cost), and number of tuples accessed (CPU cost), which are obtained from statistical information. Statistical information reveals the characteristics of the data in the table, such as data distribution and high-frequency values. Inaccurate statistical information can lead to biases in selectivity calculation, resulting in reduced accuracy in cost calculation and the selection of a suboptimal execution path, ultimately affecting database query efficiency. I / O cost mainly refers to the performance loss during data transfer between memory and external devices (such as disks and networks), including time latency and bandwidth usage, and is a key factor affecting system efficiency. CPU (Central Processing Unit) cost is a quantitative indicator for evaluating CPU resource consumption in computing tasks. Core influencing factors include the number of instructions executed, data processing volume, and hardware performance, playing a crucial decision-making role in database optimization, multi-threaded scheduling, and other scenarios.

[0065] In related technologies, both the Analyze method and Autovacuum are based on random sampling of the overall data and calculating corresponding statistical information based on the samples. However, when faced with scenarios involving large amounts of data and high update frequency, these two methods of updating statistical information may suffer from problems such as untimely updates and sampling bias, thus failing to accurately reflect the latest data characteristics. This will lead to reduced accuracy in the calculation of selectivity and execution cost, resulting in the selection of a suboptimal path and ultimately reducing the execution efficiency of SQL statements.

[0066] To address the technical problems existing in related technologies, this application aims to provide a database statistical information update method, which can solve the problem that the original statistical information update method may not be updated in a timely manner or may have biases when facing business scenarios with large data volume and rapid data updates. This method can improve the frequency and accuracy of statistical information updates, ensure that the cost of each path scheme in the physical optimization process can be accurately calculated, and thus select the optimal execution plan.

[0067] The database statistics update method provided in this application embodiment can be applied to, for example... Figure 1In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or placed on the cloud or other network servers. During the execution of structured query language (SQL) statements for data changes, server 104 continuously updates incremental information variables. After the incremental information variables are updated, it reads the existing information file and updates the corresponding existing information with the incremental information, obtaining the updated existing information. The existing information records the data characteristics of each column in the current table, and the incremental information records the data characteristics of this update. Based on the updated existing information, statistical information is determined, resulting in updated statistical information. The updated statistical information is then stored in a designated statistical information table. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, drones, low-altitude aircraft, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can include smartwatches, smart bracelets, head-mounted devices, etc. Headset devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. Server 104 can be a standalone physical server, a server cluster or distributed system consisting of multiple physical servers, or a cloud server providing cloud computing services.

[0068] In one exemplary embodiment, such as Figure 2 As shown, a method for updating database statistics is provided, which can be applied to... Figure 1 Taking the server in the example, the explanation includes the following steps S201 to S204. Wherein:

[0069] Step S201: During the execution of the Structured Query Language (SQL) statement for data changes, the incremental information variables are continuously updated.

[0070] In this embodiment, incremental information can be continuously updated during the execution of SQL statements that modify data. Especially when processing large amounts of data or needing to track data changes, a near real-time update frequency can be achieved.

[0071] Incremental updates typically refer to updating only the data that has changed since the last update, rather than updating the entire dataset each time. This approach can significantly improve data processing efficiency and reduce unnecessary resource consumption.

[0072] Step S202: After the incremental information variable is updated, read the existing information file and update the incremental information to the corresponding existing information to obtain the updated existing information.

[0073] Among them, the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update.

[0074] In this embodiment, after the data change is completed, step S202 is executed in parallel. First, the existing information file is read, the incremental information is updated to the corresponding existing information, the statistical information is calculated based on the updated existing information, and finally the results are updated to the tables storing the statistical information (pg_statistic, pg_statistics_ext).

[0075] Step S203: Determine statistical information based on the updated stock information to obtain the updated statistical information.

[0076] In this embodiment, at least one of the following statistical information can be determined based on the updated stock information: the proportion of null values ​​in the database table, the average width of the column, the number or proportion of duplicate attributes, high-frequency values, equal-frequency histograms, correlation coefficients, high-frequency values ​​of arrays or other types, histograms of array types, histogram statistics based on length generated by range type, and histogram statistics based on boundaries of range type.

[0077] Optionally, incremental information collection and data modification can be carried out simultaneously. After the data modification is completed, the incremental information of the data modification is also statistically completed.

[0078] Step S204: Store the updated statistical information in the specified statistical information table.

[0079] In this embodiment, the updated statistical information can be updated in the pg_statistics table.

[0080] Optionally, to avoid the table being locked during updates and thus unable to update, the updated statistics can be temporarily stored in a data queue or pipeline before being stored in the specified statistics table. Furthermore, the calculation of multi-column statistics follows the same principle as single-column calculations, and the results are finally updated in the pg_statistics_ext table.

[0081] In the aforementioned database statistics update method, incremental information variables are continuously updated during the execution of Structured Query Language (SQL) statements that modify data, thereby increasing the frequency of statistics updates. After the incremental information variables are updated, the existing information file is read, and the incremental information is updated to the corresponding existing information to obtain the updated existing information. The existing information records the data characteristics of each column in the current table, while the incremental information records the data characteristics of this update. Therefore, a combination of existing and incremental information can be used to update statistics, achieving near real-time statistical updates. The updated statistics are determined based on the updated existing information and stored in a designated statistics table. This avoids the impact of inaccurate statistics on operational efficiency, ensuring accurate calculation of the cost of each path in the physical optimization process, and thus selecting the optimal execution plan.

[0082] In another exemplary embodiment, such as Figure 3 As shown, a method for updating database statistics is provided, which can be applied to... Figure 1 Taking the server in the example, the explanation includes the following steps S301 to S307. Wherein:

[0083] Step S301: With the database running, check if the attribute columns of the data table are missing the corresponding inventory information file. If yes, proceed to step S302; otherwise, proceed to step S303.

[0084] Step S302: After generating the existing information file in parallel using either a full table scan or a sampling scan, the SQL statements for data modification are executed, and the incremental information variables are initialized.

[0085] Step S303: Start executing the SQL statement for data modification and initialize the incremental information variables.

[0086] In this embodiment, the overall process can be divided into a startup process and a running process. During the database startup process, the existing information files (one file per attribute column) are scanned to check if the files correspond one-to-one with the attribute columns of the data table. For missing column existing information files, a full table scan or the sampling method provided in PostgreSQL can be used to generate existing data files in parallel. When the SQL for data modification is executed, the incremental information variables are initialized.

[0087] Step S304: During the execution of the Structured Query Language (SQL) statement for data changes, the incremental information variables are continuously updated.

[0088] Step S305: After the incremental information variable is updated, read the existing information file and update the incremental information to the corresponding existing information to obtain the updated existing information.

[0089] Step S306: Determine statistical information based on the updated stock information to obtain the updated statistical information.

[0090] Step S307: Store the updated statistical information in the specified statistical information table.

[0091] For the specific implementation process and technical effects of steps S304 to S307 in this embodiment, please refer to [link to documentation]. Figure 2 The relevant descriptions of steps S201 to S204 in the method embodiment shown will not be repeated here.

[0092] Optionally, after updating the incremental information to the corresponding stock information and obtaining the updated stock information, the updated stock information can also be periodically written to the stock information file.

[0093] Among them, the stock information file is used to store stock information.

[0094] Optionally, after updating the incremental information to the corresponding existing information and obtaining the updated existing information, the remaining existing and incremental information can be cleared after the execution of the SQL statement that changed the data has ended.

[0095] In this embodiment, residual existing and incremental information can be cleared in a timely manner after the update is completed, thereby freeing up storage resources; existing information can also be written to the existing information file at regular intervals, thereby ensuring that the existing information file is updated and keeps consistent with the existing information during the change process.

[0096] In yet another exemplary embodiment, such as Figure 4 As shown, a method for updating database statistics is provided, which can be applied to... Figure 1 Taking the server in the example, the following steps can be included:

[0097] Step S401: Start the database.

[0098] Step S402: Check if the column's existing data file is lost. If yes, proceed to step S403; otherwise, proceed to step S407.

[0099] Step S403: Determine whether sampling is required. If yes, proceed to step S404; otherwise, proceed to step S405.

[0100] Step S404: After sampling and scanning the table, proceed to step S406.

[0101] Step S405: After a full table scan, proceed to step S406.

[0102] Step S406: Generate existing information files in parallel.

[0103] Step S407: Begin executing data modification statements (such as insert, delete, and update).

[0104] Step S408: Initialize the incremental information variables.

[0105] Step S409: Update incremental information based on data changes.

[0106] Step S410: Read the existing information in parallel.

[0107] Step S411: Update the incremental information to the existing information in parallel.

[0108] Optional step S412: Update statistics.

[0109] Optional step S413: Periodically write the inventory information to a file.

[0110] Step S414: The execution of the data change statement ends.

[0111] Optional step S415: Clear existing and incremental information.

[0112] The overall logic of steps S401 to S415 above is as follows: When the database starts, it scans the existing information files (one file per attribute column) to check if the files correspond one-to-one with the attribute columns of the data tables. For missing columns in the existing information files, a full table scan or the sampling method provided in PostgreSQL can be used to generate existing data files in parallel. When the SQL for data modification is executed, the incremental information variable is initialized; the incremental information variable is continuously updated during the data modification process; after the data modification is completed, the following operations are performed in parallel: the existing information file is read, the incremental information is updated to the corresponding existing information, statistical information is calculated based on the updated existing information, and the results are updated to the tables storing statistical information (pg_statistic, pg_statistics_ext). After the update is completed, the remaining existing and incremental information is cleared. In addition, existing information is periodically written to the existing information file.

[0113] Optionally, taking the calculation of single-column information as an example, the existing information represents the data characteristics of each column in the current table, the incremental information records the data characteristics of this update, and the existing information file is used to store the existing information.

[0114] It should be understood that existing and incremental information share the same set of variables (i.e., existing and incremental information use the same variable names). Optionally, this involves the variables InfoDic and LineInfo.

[0115] The variable InfoDic contains key-value pairs (key and value), where the key and value are 64-bit integers (int64). The key is the hash value of the column value, and the value is the number of times that value appears. When data is deleted, the corresponding value in the value is represented by a negative number; when updated, the number of changes to the column value before and after the deletion must be updated separately.

[0116] The variable LineInfo is used to record the inventory information of the attribute column, and its structure is as follows:

[0117] struct LineInfo / / LineInfo structure;

[0118] {

[0119] oid tableOid; / / The oid of the table to which it belongs;

[0120] char *lineName; / / Column name;

[0121] int execType; / / Update operation type; 0 represents existing data, 1 represents insertion in incremental data, 2 represents deletion in incremental data, and 3 represents update in incremental data;

[0122] int64 rowCount; / / Total number of rows;

[0123] int64 nullCount; / / The number of null values;

[0124] int64 tooWideCount; / / Number of excessively long values;

[0125] int64 width; / / Total length of non-null values;

[0126] InfoDic *valueCountDic;

[0127] }

[0128] For example, Figure 5 This is a flowchart illustrating the calculation of statistical information in one embodiment of this application, as shown below. Figure 5As shown, the update of rowCount is obtained by calculating the sum of existing and incremental information (update operations are ignored); the update of valueCountDic is obtained by merging existing and incremental information (which can be summarized as taking the union). The update of nullCount is obtained by calculating the sum of existing and incremental information; the update of width is obtained by calculating the sum of existing and incremental information; the update of tooWideCount is obtained by calculating the sum of existing and incremental information, and CORRELATION represents the correlation coefficient. Specifically, for valueCountDic, if there are duplicate keys, the values ​​are added together; and key-value pairs with a value of 0 are deleted.

[0129] For example, the calculation methods in PostgreSQL are used to calculate a total of seven single-column statistics, including stanullfrac (the proportion of null values), stawidth (the average width of the column), stadistinct (the number or proportion of duplicates of the attribute after deduplication), and STATISTIC_KIND_MCV (high-frequency values), STATISTIC_KIND_HISTOGRAM (equal-frequency histogram), STATISTIC_KIND_CORRELATION (correlation coefficient), STATISTIC_KIND_MCELEM (high-frequency values ​​of arrays or other types), STATISTIC_KIND_DECHIST (historical chart of array type), STATISTIC_KIND_RANGE_LEGTH_HISTOGRAM (length-based histogram statistics generated for Range type), and STATISTIC_KIND_BOUNDS_HISTOGRAM (boundary-based histogram of Range type).

[0130] Optionally, when it is necessary to perform statistical analysis on the correlation coefficient, the sampling quantity of each layer of the database table is determined separately; if the sampling quantity of each layer meets the requirements, the correlation coefficient is calculated based on the object-relational database management system PostgreSQL; if the sampling quantity of each layer does not meet the requirements, pages of the database table are randomly selected, and each selected page is sampled in layers until the sampling quantity of each layer meets the requirements, and then the correlation coefficient is calculated based on PostgreSQL.

[0131] For example, Figure 6 This is a schematic diagram of the random stratified sampling process in one embodiment of this application, as shown below. Figure 6As shown, for STATISTIC_KIND_CORRELATION, instead of using stock and incremental updates for calculation, a random stratified sampling method is proposed based on the two-stage sampling method in PostgreSQL to minimize sampling bias caused by data distribution. See also Figure 6 The total number of samples in stratified sampling is defined by the user, while the number of samples in each stratum is calculated based on the frequency of each value using valueCountDic. The algorithm first determines whether the number of samples sampled in each stratum meets the requirements. If it does, the correlation coefficient is calculated using a method provided by PostgreSQL; otherwise, pages of the table are randomly selected first, and then stratified sampling is performed on each page.

[0132] It should be understood that in scenarios with frequent and large-volume data updates, the statistical information stored in the database may fail to accurately reflect the current data distribution characteristics, leading to inaccurate calculations of indicators such as selectivity and physical cost, thereby affecting the efficiency of SQL statement execution. Therefore, the method in this embodiment can update statistical information in near real-time, minimizing the impact on execution efficiency caused by inaccurate statistical information.

[0133] It should be understood that traditional statistical information sampling methods cannot encompass the overall data distribution, easily leading to biased sampling and inaccurate statistical information calculations. Except for calculating the correlation coefficient, the calculation and updates of statistical information in this embodiment rely on the overall data, thus accurately reflecting the data distribution characteristics. For the correlation coefficient, since ranking is involved, a sampling method is still used. However, the sampling method has been changed from the original random sampling to stratified random sampling, which minimizes the impact of sampling rate and data distribution.

[0134] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0135] Based on the same inventive concept, this application also provides a database statistics update apparatus for implementing the database statistics update method described above. The solution provided by this apparatus is similar to the implementation described in the above method; therefore, the specific limitations in one or more embodiments of the database statistics update apparatus provided below can be found in the limitations of the database statistics update method described above, and will not be repeated here.

[0136] In one exemplary embodiment, such as Figure 7 As shown, a database statistics information updating device is provided, including: a first updating module 701, a second updating module 702, a statistics module 703, and a storage module 704, wherein:

[0137] The first update module 701 is used to continuously update incremental information variables during the execution of structured query language (SQL) statements that change data.

[0138] The second update module 702 is used to read the existing information file after the incremental information variable is updated, and update the incremental information to the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update;

[0139] The statistics module 703 is used to determine statistical information based on the updated stock information and obtain the updated statistical information.

[0140] Storage module 704 is used to store updated statistical information into a specified statistical information table.

[0141] For example, the above apparatus may further include: a startup module 705, configured to, when the database is started, check whether the attribute columns of the data table are missing corresponding existing information files; if the corresponding existing information files are not missing, start executing the SQL statements for data modification and initialize incremental information variables; if the corresponding existing information files are missing, generate existing information files in parallel according to a full table scan or a sampling scan method, start executing the SQL statements for data modification, and initialize incremental information variables; wherein, existing information and incremental information use the same variable name.

[0142] For example, the storage module 704 is also used to periodically write the updated existing information into the existing information file after updating the incremental information to the corresponding existing information and obtaining the updated existing information. The existing information file is used to store the existing information.

[0143] The storage module 704 is also used to temporarily store the updated statistics in a data queue or pipeline before storing the updated statistics in a specified statistics table.

[0144] For example, the above apparatus may further include: a clearing module 706, used to clear the remaining existing information and incremental information after the SQL statement that changed the data has been executed, after the incremental information has been updated to the corresponding existing information and the updated existing information has been obtained.

[0145] For example, the statistics module 703 is specifically used to: determine, based on the updated stock information, at least one of the following statistical information in the database table: the proportion of null values, the average width of columns, the number or proportion of duplicate attributes, high-frequency values, equal-frequency histograms, correlation coefficients, high-frequency values ​​of arrays or other types, histograms of array types, histograms of range types generated based on length, and histograms of range types based on boundaries.

[0146] For example, the statistics module 703 is further configured to: determine the sampling quantity of each layer of the database table when it is necessary to perform statistics on the correlation coefficient; calculate the correlation coefficient based on the object-relational database management system PostgreSQL if the sampling quantity of each layer meets the requirements; and randomly select pages of the database table if the sampling quantity of each layer does not meet the requirements, and perform stratified sampling on each selected page until the sampling quantity of each layer meets the requirements, and then calculate the correlation coefficient based on PostgreSQL.

[0147] Each module in the aforementioned database statistical information update device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0148] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 8As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. When the computer program is executed by the processor, it implements a method for updating database statistical information. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0149] Those skilled in the art will understand that Figure 8 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0150] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0151] During the execution of the Structured Query Language (SQL) statements for data changes, incremental information variables are continuously updated. After the incremental information variables are updated, the existing information file is read, and the incremental information is updated into the corresponding existing information to obtain the updated existing information. The existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update. Statistical information is determined based on the updated existing information to obtain the updated statistical information. The updated statistical information is then stored in the specified statistical information table.

[0152] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0153] When the database is started, check if the corresponding existing information files are missing for the attribute columns of the data table. If the corresponding existing information files are not missing, start executing the SQL statements for data modification and initialize the incremental information variables. If the corresponding existing information files are missing, generate existing information files in parallel using either a full table scan or a sampling scan method, then start executing the SQL statements for data modification and initialize the incremental information variables. The existing information and incremental information use the same variable names.

[0154] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0155] After updating the incremental information to the corresponding stock information and obtaining the updated stock information, the updated stock information is periodically written to the stock information file, which is used to store the stock information; and / or, before storing the updated statistical information to the specified statistical information table, the updated statistical information is temporarily stored in the data queue or pipeline.

[0156] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0157] After updating the incremental information to the corresponding existing information and obtaining the updated existing information, the remaining existing and incremental information is cleared after the SQL statement that changed the data has been executed.

[0158] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0159] Based on the updated stock information, determine at least one of the following statistical information in the database table: the proportion of null values, the average width of columns, the number or proportion of duplicate attributes after deduplication, high-frequency values, equal-frequency histograms, correlation coefficients, high-frequency values ​​of arrays or other types, histograms of array types, histograms of range types generated based on length, and histograms of range types based on boundaries.

[0160] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0161] When it is necessary to calculate the correlation coefficient, the sampling quantity of each layer of the database table is determined. If the sampling quantity of each layer meets the requirements, the correlation coefficient is calculated based on the object-relational database management system PostgreSQL. If the sampling quantity of each layer does not meet the requirements, pages of the database table are randomly selected, and each selected page is sampled in layers until the sampling quantity of each layer meets the requirements. Then, the correlation coefficient is calculated based on PostgreSQL.

[0162] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the method steps of the various embodiments described above.

[0163] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the method steps of the various embodiments described above.

[0164] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory may include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory may include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0165] 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 application.

[0166] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed. However, they should not be construed as limiting the scope of this application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A database statistics update method, characterized by, The method comprises: During execution of a structured query language (SQL) statement for data change, an incremental information variable is constantly updated; After the incremental information variable is updated, a stock information file is read, and the incremental information is updated to corresponding stock information to obtain updated stock information; the stock information is used to record data characteristics of each column in a current table, and the incremental information is used to record data characteristics of this update; Based on the updated stock information, statistical information is determined to obtain updated statistical information; The updated statistical information is stored in a designated statistical information table.

2. The method of claim 1, wherein, Before execution of the SQL statement for data change, the method further comprises: In a case where a database is started, it is checked whether a property column of a data table is missing a corresponding stock information file; In a case where the corresponding stock information file is not missing, execution of the SQL statement for data change is started, and the incremental information variable is initialized; In a case where the corresponding stock information file is missing, after the stock information file is generated in a full table scanning mode or a sampling scanning mode in parallel, execution of the SQL statement for data change is started, and the incremental information variable is initialized; The stock information and the incremental information use the same variable name.

3. The method of claim 1, wherein, After the incremental information is updated to the corresponding stock information to obtain the updated stock information, the method further comprises: periodically writing the updated stock information into a stock information file, which is used to save the stock information; Before the updated statistical information is stored in the designated statistical information table, the method further comprises: storing the updated statistical information in a data queue or a pipe for temporary storage.

4. The method of claim 1, wherein, After the incremental information is updated to the corresponding stock information to obtain the updated stock information, the method further comprises: In a case where execution of the SQL statement for data change ends, remaining stock information and incremental information are cleared.

5. The method according to any one of claims 1 to 4, characterized in that, The determination of the statistical information based on the updated stock information to obtain the updated statistical information comprises: Based on the updated stock information, at least one of the following statistical information is determined: a proportion of null values in a database table, an average width of a column, a number or a proportion of de-duplicated properties, a high frequency value, an equal frequency histogram, a correlation coefficient, an array or other types of high frequency value, a histogram of an array type, a length-based histogram statistical information generated based on a range type, and a boundary-based histogram of a range type.

6. The method of claim 5, wherein, When the correlation coefficient needs to be counted, the method further comprises: Respective sampling numbers of layers of the database table are determined; In a case where the sampling numbers of the layers meet a requirement, a correlation coefficient is calculated based on an object-relational database management system (PostgreSQL); In a case where the sampling numbers of the layers do not meet the requirement, pages of the database table are randomly selected, and each selected page is sampled layer by layer until the sampling numbers of the layers meet the requirement, and then the correlation coefficient is calculated based on the PostgreSQL.

7. A database statistics update apparatus characterized by comprising: The device comprises: A first updating module configured to constantly update an incremental information variable during execution of a structured query language (SQL) statement for data change. The second update module is used to read the existing information file after the incremental information variable update is completed, and update the incremental information into the corresponding existing information to obtain the updated existing information; the existing information is used to record the data characteristics of each column in the current table, and the incremental information is used to record the data characteristics of this update; The statistics module is used to determine statistical information based on the updated stock information, and obtain the updated statistical information; The storage module is used to store updated statistics into a specified statistics table.

8. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having stored thereon a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

10. A computer program product comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images