Statistical information collection method of database and related device

By multiplexing the data of the query statement, collecting statistical information in the database, the reading operation of data tables is avoided, the collection efficiency of statistical information is improved, the problem of low efficiency of statistical information collection in the existing technology is solved, and query optimization is optimized execution plan of the device.

CN118939684APending Publication Date: 2024-11-12CETC JINCANG (BEIJING) TECH CO LTD
View PDF 0 Cites 1 Cited by

Patent Information

Application Number
CN202411310831.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-09-19
Publication Date
2024-11-12

AI Technical Summary

Technical Problem

Existing database statistical information collection methods require reading operations on the data table, resulting in conflicts with structured query statements, which are inefficient and easily lead to performance bottlenecks.

Method used

Collect statistical information by multiplexing the query statement to read the table, avoiding reading the data table when collecting statistical information, using shared memory to store query results and perform statistical processing by the second node to realize statistical information Efficient collection.

Benefits of technology

It improves the efficiency of collecting database statistical information, avoids competition with query statement resources, ensures the timeliness and accuracy of statistical information, and optimizes the execution plan of the query optimizer.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939684A_ABST
    Figure CN118939684A_ABST
Patent Text Reader

Abstract

The invention provides a statistical information collection method of a database and a related device, and relates to the technical field of data processing, the method is applied to a database management system, the database management system comprises a first node and a second node, and the method comprises the following steps: the first node obtains a query statement for performing read operation on the database; the first node generates a query plan of the query statement; the first node executes the query statement based on the query plan to obtain a target query result, and stores the target query result in a shared memory; and under the condition that the line number corresponding to the target query result is not matched with the estimated query line number, the second node reads the target query result from the shared memory, and performs statistical processing on the target query result to obtain statistical information. Through the method, the collection efficiency of the statistical information can be effectively improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method for collecting statistical information of a database and related devices. Background Art

[0002] The performance of structured query statements depends on the optimization effect of the query optimizer, and database statistics is one of the key factors affecting the optimization effect of the query optimizer.

[0003] In the related art, the method for collecting database statistical information is mainly to analyze and obtain the full amount of data in the data table in the database, or to analyze and obtain the data by sampling the data table.

[0004] However, the above methods all require reading operations on the data table, which will conflict with the structured query statement. In order to avoid performance bottlenecks in the database, data can usually only be collected during idle time, and the efficiency of collecting database statistical information is low. Summary of the invention

[0005] The embodiments of the present application provide a method and a related device for collecting statistical information of a database, which can improve the efficiency of collecting statistical information of the database.

[0006] In a first aspect, an embodiment of the present application provides a method for collecting statistical information of a database, which is applied to a database management system, wherein the database management system includes a first node and a second node, and the method includes:

[0007] The first node obtains a query statement for performing a read operation on the database;

[0008] The first node uses a query optimizer to generate a query plan for the query statement and an estimated number of query rows for the query statement;

[0009] The first node executes the query statement based on the query plan to obtain a target query result, and stores the target query result in a shared memory;

[0010] When the number of rows corresponding to the target query result does not match the estimated number of query rows, the second node reads the target query result from the shared memory and performs statistical processing on the target query result to obtain the statistical information.

[0011] In some embodiments, if the number of rows of the target query result is not within the row number range determined based on the query row number, the number of data rows corresponding to the target query result does not match the estimated query row number; if the number of rows of the target query result is within the row number range, the number of data rows corresponding to the target query result matches the estimated query row number.

[0012] In some embodiments, the method further comprises:

[0013] The first node obtains an upper limit value and a lower limit value of the row number range based on the estimated query row number and a row number adjustment parameter corresponding to the query statement;

[0014] The first node determines the row number range based on the upper limit value and the lower limit value.

[0015] In some embodiments, the first node stores the target query result in a shared memory, including:

[0016] The first node inserts a preset tag into a preset field of the target query result to obtain the target query result with the tag added, and stores the target query result with the tag added in the shared memory; the preset tag is used to characterize the target query result for generating statistical information;

[0017] The second node reads the target query result from the shared memory, including:

[0018] The second node reads the target query result from the shared memory based on the label, and performs statistics on the target query result to obtain the statistical information.

[0019] In some embodiments, the method further comprises:

[0020] The first node sends instruction information to the second node, where the instruction information is used to instruct the second node to read the target query result from the shared memory, and the instruction information includes a label of the target query result;

[0021] or,

[0022] The second node monitors the shared memory, and when determining that data matching the preset tag exists in the shared memory, reads the data matching the preset tag from the shared memory, and uses the data as the target query result.

[0023] In some embodiments, the method further comprises:

[0024] The second node updates a statistical information table based on the statistical information.

[0025] In some embodiments, the method further comprises:

[0026] The first node obtains the row number adjustment parameter from a preset parameter library according to the estimated query row number corresponding to the query statement.

[0027] In a second aspect, the present application provides an electronic device, including: a memory and a processor;

[0028] The memory is used to store computer instructions; the processor is used to execute the computer instructions stored in the memory to implement any method in the first aspect.

[0029] In a third aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, and the computer program is executed by a processor to implement any one of the methods in the first aspect.

[0030] In a fourth aspect, the present application provides a computer program product, comprising a computer program, which implements any one of the methods in the first aspect when executed by a processor. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] Figure 1 A schematic diagram of the structure of a database management system provided in an embodiment of the present application;

[0032] Figure 2 A schematic diagram of the structure of another database management system provided in an embodiment of the present application;

[0033] Figure 3 A flowchart of a statistical information collection method provided in an embodiment of the present application;

[0034] Figure 4 A flowchart of another statistical information collection method provided in an embodiment of the present application;

[0035] Figure 5 A schematic diagram of a statistical information collection process provided in an embodiment of the present application;

[0036] Figure 6 A schematic diagram of the structure of a statistical information collection system provided in an embodiment of the present application;

[0037] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0038] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0039] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects, and do not limit their order. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.

[0040] It should be noted that in the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0041] For ease of understanding, the technical terms involved in the embodiments of the present application are briefly introduced first:

[0042] 1. The query optimizer is a component in a database management system (for example, SQL Server) that performs internal optimization for user requests, generates (or reuses) execution plans, transmits them to the storage engine to operate data, and finally returns results to the user. The query optimizer is one of the core components of a relational database management system. It determines which indexes and association algorithms to use for a specific query so that it can run efficiently. It is one of the most important components in the optimizer.

[0043] 2. Cost-based query optimizer is an optimization technology that generates a series of execution plans for a given query statement and estimates its execution cost (for example, CPU, memory, network, I / O and other resource consumption), and finally selects the plan with the lowest cost to execute. This optimization method relies on the collected statistical information of the data table, including the distribution of the data table, the usage of the index, etc., to evaluate different execution paths and select the optimal path.

[0044] 3. Statistical information is metadata that describes the distribution of data in a table or index.

[0045] 4. Cardinality Estimation refers to the process of predicting the number of rows or records that will be returned in the query results.

[0046] The performance of structured query statements depends on the optimization effect of the query optimizer. Currently, the mainstream query optimizers of databases are cost-based, which heavily relies on the statistical information of tables and columns in the database. Data statistics are one of the key factors affecting the optimization effect of the query optimizer.

[0047] In the related art, the collection of database statistical information mainly relies on two methods. One is to analyze and obtain the full amount of data in the data table. The advantage of this method is that the statistical information obtained is relatively accurate, but the disadvantage is that it takes a long time. As the amount of data in the table expands, the time consumption may become unacceptable. The second method is to perform sampling analysis on the data table. The advantage of this method is that it takes less time, but the disadvantage is that the statistical information obtained is not accurate enough, which affects the optimization effect of the query optimizer.

[0048] The common disadvantage of the above two methods is that they both require table read operations, which manifests as a behavior of "competing for resources" with query statements. Therefore, in order to avoid database performance bottlenecks, analysis can only be performed during idle time, and the efficiency of collecting database statistical information is poor.

[0049] In view of this, an embodiment of the present application provides a method and related device for collecting statistical information of a database, which collects statistical information by reusing query statements to perform read operations on the table, avoiding reading operations on the table when collecting statistical information, thereby improving the efficiency of collecting statistical information.

[0050] The following specific embodiments are used to describe in detail the technical solution of the present application and how the technical solution of the present application solves the above technical problems. The following specific embodiments can be implemented independently or in combination with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.

[0051] Figure 1 Schematic diagram of application scenarios of the embodiments of the present application, such as Figure 1 As shown, the database management system includes a client 1 , a server 2 and a database 3 .

[0052] The client 1 is used to interact with the user, for example, to receive a query statement input by the user, and send the query statement to the server 2. The client 1 receives a query result corresponding to the query statement returned by the server, and displays the query result to the user.

[0053] When the server 2 receives the query statement sent by the client 1 , it generates a query plan according to the query statement, executes the query plan in the database 3 , obtains the query result, and sends the query result to the client 1 .

[0054] In some embodiments, Figure 2 As shown, the server 2 may be a distributed server or a server cluster. The server 2 may include multiple nodes, and each node may be composed of one or more servers.

[0055] When the client receives the query statement input by the user, the query statement can be distributed to the corresponding server 2 node 1 (distribution node). Node 1 splits the query statement, generates multiple execution plans, and distributes the execution plans to the corresponding nodes (execution nodes). For example, the distribution is performed through load balancing, or based on the type of query statement, etc. The embodiments of the present application do not limit this.

[0056] Optionally, the database 3 may also be composed of multiple databases. When executing a query statement, the execution node may poll all databases for query, or query one or more specified databases.

[0057] exist Figure 1 and Figure 2 Based on the embodiment shown, the following Figure 3 The method for collecting statistical information of a database provided in an embodiment of the present application is described. The execution subject of the embodiment of the present application may be a server of the database management system in the above embodiment.

[0058] Figure 3 A flowchart of a method for collecting statistical information of a database provided in an embodiment of the present application is shown in FIG. Figure 3 As shown, the steps include:

[0059] S301: A first node obtains a query statement for performing a read operation on a database.

[0060] The database management system includes a first node and a second node.

[0061] In some embodiments, Figure 1 As shown, if the server is an independent server, the first node and the second node may be threads in the server, for example, the first node is a first thread in the server, and the second node is a second thread in the server.

[0062] In some embodiments, Figure 2 As shown, if the server is a distributed deployed server, the first node may include an allocation node and an execution node, and the second node may also include an allocation node and an execution node, which is not limited in this embodiment of the present application.

[0063] The first node may receive a query statement sent by the client for performing a read operation on the database, for example, select*from t1 where a>3.

[0064] S302: The first node generates a query plan for the query statement.

[0065] In some embodiments, for an independent server, when the first node receives a query statement, it may call a query optimizer to perform cost estimation on the query statement to obtain a query plan (also called an execution plan) of the query statement.

[0066] The query plan may include a cardinality estimation result of the query statement, that is, cardinality estimation of the query statement is a part of cost estimation of the query statement, and the cardinality estimation result of the query statement is the estimated number of query rows of the query statement.

[0067] In some embodiments, for a distributed server, when a distribution node receives a query statement, it may call a query optimizer to perform cost estimation on the query statement to obtain a query plan for the query statement.

[0068] Optionally, the first node or the allocation node may also obtain the query plan in other ways, which is not limited in the embodiments of the present application.

[0069] S303: The first node executes the query statement based on the query plan to obtain a target query result, and stores the target query result in a shared memory.

[0070] In some embodiments, the query plan may include the execution process of one or more steps, and in each step execution process, the query result corresponding to the step can be obtained. Correspondingly, the target query result can be the final result obtained by executing the query statement, or it can be the intermediate result obtained when executing each step.

[0071] In some embodiments, the shared memory is a storage space configured by the first node for storing data.

[0072] For an independent server, when the first node obtains the query plan of the query statement, the query statement can be executed in the database to obtain the target query result.

[0073] For a distributed server, the allocation node distributes the query plan including the cardinality estimation result to the execution node, and the execution node executes the query statement in the database to obtain the target query result.

[0074] S304: When the number of rows corresponding to the target query result does not match the estimated number of query rows, the second node reads the target query result from the shared memory, and performs statistical processing on the target query result to obtain the statistical information.

[0075] For an independent server, when the first node obtains the target query result, it can compare the number of rows of the target query result with the estimated number of query rows during cardinality estimation. If the number of data rows corresponding to the target query result does not match the estimated number of query rows, the first node determines that the target query result meets the conditions for collecting statistical information.

[0076] The mismatch between the number of data rows corresponding to the target query result and the estimated number of query rows may refer to the difference between the number of data rows corresponding to the target query result and the estimated number of query rows being greater than a preset threshold. For example, if the estimated number of rows is 10,000 rows and the query result is 15,000 rows, and the difference between the number of rows is greater than the preset threshold (3,000) rows, then the first node determines that the target query result meets the condition for collecting statistical information.

[0077] For a distributed server, when the execution node obtains the target query result, the number of rows of the target query result can be compared with the number of query rows estimated during cardinality estimation. If the number of data rows corresponding to the target query result does not match the estimated number of query rows, it is determined that the target query result meets the conditions for collecting statistical information.

[0078] Optionally, when the first node stores the target query result in the shared memory, the first node may continue to execute the original query statement, for example, returning the query result to the client to display it to the user.

[0079] Optionally, when the first node determines that the target query result does not satisfy the condition for collecting statistical information, the first node continues to execute the original query statement.

[0080] In some embodiments, for an independent server, when the first node determines that the target query result meets the condition for collecting statistical information, the second node may be notified to read the target query result from the shared memory, and perform statistical analysis on the target query to obtain the statistical information. For example, the second node extracts statistical information such as the number of rows, maximum value, and minimum value in the query result. When the second node obtains the statistical information, it may update the statistical information table according to the statistical information to complete the collection of statistical information.

[0081] In some embodiments, for a distributed server, when the execution node determines that the target query result meets the conditions for collecting statistical information, it performs statistical analysis on the target query to obtain the statistical information, and sends the statistical information to the allocation node. When the allocation node obtains the statistical information, it can update the statistical information table according to the statistical information to complete the collection of statistical information. Among them, the execution node determines that the target query result meets the conditions for collecting statistical information and performs statistical analysis on the target query can be completed by different threads in the execution node. For example, the first thread of the execution node executes a query statement and stores the target query result in a shared memory. When it is determined that the target query result meets the conditions for collecting statistical information, the second thread can be notified to read the target query result from the shared space.

[0082] It should be understood that when the query result is an intermediate result, the number of rows of each intermediate result will be compared with the number of query rows estimated during cardinality estimation. If the above-mentioned statistical information collection conditions are met, the subsequent step of the second node reading the target query result from the shared memory is executed.

[0083] The method for collecting statistical information of a database provided in an embodiment of the present application comprises the following steps: a first node obtains a query statement for performing a read operation on a database; the first node generates a query plan for the query statement; the first node executes the query statement based on the query plan to obtain a target query result, and stores the target query result in a shared memory; when the number of data rows corresponding to the target query result does not match the estimated number of query rows, the second node reads the target query result from the shared memory, and performs statistical processing on the target query result to obtain the statistical information. When collecting statistical information, the query results of the query statement for performing a read operation on the data table are reused to update the statistical information. When collecting statistical information, there is no need to perform a read operation on the data table, thereby avoiding resource competition with the query statement, thereby improving the efficiency of collecting statistical information.

[0084] Based on the above embodiment, the following takes an independent server as an example. Figure 4 The method for collecting statistical information of the database provided in the embodiment of the present application is further explained.

[0085] Figure 4 Schematic diagram of the process of collecting statistical information of the database provided in the embodiment of the present application Figure 2 ,like Figure 4 As shown, including:

[0086] S401: The first node obtains a query statement for performing a read operation on a database.

[0087] S402: The first node uses a query optimizer to generate a query plan for the query statement.

[0088] S403: The first node executes the query statement based on the query plan to obtain a target query result.

[0089] In the embodiment of the present application, the specific implementation method of steps S401 to S403 is the same as Figure 3 The specific implementation methods of steps S301 to S303 in the illustrated embodiment are similar and will not be described again here.

[0090] S404: The first node determines the row number range based on the estimated query row number and a row number adjustment parameter corresponding to the query statement.

[0091] In some embodiments, the row number adjustment parameter may be predefined in the server, and the first node may read the row number adjustment parameter when determining the estimated query row number.

[0092] The row number adjustment parameter may be a unitless coefficient, such as 1.5 times; or may be a unit entity number, such as the number of rows.

[0093] Exemplarily, the first node obtains the upper limit and lower limit of the row number range based on the estimated query row number and the row number adjustment parameter corresponding to the query statement; the first node determines the row number range based on the upper limit and the lower limit.

[0094] For example, for the query select * from t1 where a>3:

[0095] If the row adjustment parameter is a unitless coefficient, such as 1.5:

[0096] When the optimizer performs cardinality estimation, it obtains an estimated query row number of 100,000 rows for a>3, and the row number adjustment parameter is 1.5. Then the upper limit of the row number range is 150,000 rows (100,000*1.5), and the lower limit is 66,667 rows (100,000 / 1.5), that is, the row number range is 66,667-150,000.

[0097] If the row adjustment parameter is a unit entity number, for example 20000 rows:

[0098] When the optimizer performs cardinality estimation, it obtains an estimated query row number of 100,000 rows for a>3, and the row number adjustment parameter is 20,000 rows. Then the upper limit of the row number range is 120,000 rows (100,000+20,000), and the lower limit is 80,000 rows (100,000-20,000), that is, the row number range is 80,000-120,000.

[0099] In some embodiments, a parameter library is predefined in the server, which includes multiple row number adjustment parameters, each row number adjustment parameter is set with a corresponding applicable range, and the first thread can obtain the row number adjustment parameter from the preset parameter library based on the estimated query row number corresponding to the query statement.

[0100] For example, the row number parameter library includes the following row number adjustment parameter, which is a unitless coefficient:

[0101] 1.5---Applicable for less than 100,000 rows;

[0102] 2.0---Applicable to 100,000 to 200,000 rows;

[0103] 2.5---Applicable for more than 200,000 rows.

[0104] The first thread can select the corresponding row number adjustment parameter based on the applicable range of the row number adjustment parameter within which the estimated number of query rows of the query statement falls. For example, if the estimated number of query rows is 120,000 rows, which is the applicable range corresponding to the row number adjustment parameter 2.0, the first thread can select 2.0 as the row number adjustment parameter corresponding to the query statement.

[0105] S405, the first node determines whether the number of rows corresponding to the target query result matches the estimated number of query rows. If not, the step shown in S406 is executed. If it matches, the original query statement process continues to be executed.

[0106] In some embodiments, when the first node determines that the number of rows of the target query result is not within the row number range determined based on the query row number, the number of data rows corresponding to the target query result does not match the estimated query row number.

[0107] If the number of rows of the target query result is within the range of rows, the number of data rows corresponding to the target query result matches the estimated number of query rows.

[0108] For example, the number of rows corresponding to the target query result is 150,000 rows, and the row number range is 80,000-120,000 rows. It can be determined that the number of data rows corresponding to the target query result does not match the estimated number of query rows, that is, the collection of statistical data is triggered.

[0109] S406: The first node inserts a preset tag into a preset field of the target query result to obtain the target query result with the tag added, and stores the target query result with the tag added in the shared memory.

[0110] The preset label is used to characterize the target query result for generating statistical information.

[0111] In some embodiments, a variety of data may be stored in the shared memory. In order to facilitate the subsequent second node to obtain the target query result, the first node may mark the target query result with a preset label.

[0112] For example, the preset tag is inserted into the first field of the query result, or the preset tag is inserted into the last field of the query result, or a new field is added to the query result and the preset tag is inserted into the new field. The embodiment of the present application does not limit the method of inserting the preset tag.

[0113] In some embodiments, the preset tag may be a field of fixed length, or a string, or a Boolean value, etc., which is not limited in the embodiments of the present application.

[0114] After adding a label to the target query result, the first node stores the labeled target query result in the shared memory.

[0115] S407: The second node reads the target query result from the shared memory based on the label, and performs statistics on the target query result to obtain the statistical information.

[0116] In some embodiments, the second node may read the target query result from the shared memory in the following manner:

[0117] The first possible implementation is:

[0118] The first node sends indication information to the second node, where the indication information is used to instruct the second node to read the target query result from the shared memory, and the indication information includes a label of the target query result.

[0119] When the first node stores the target query result with the tag added in the shared memory, it can send indication information including the tag of the target query result to the second node to wake up the second thread. When the second node receives the indication information, it can extract the tag and access address from the indication information, read the data tag carried by each data from the shared memory through the access address, and extract the data with the same data tag as the tag in the indication information as the target query result. In the above manner, the second node is in a dormant state when not working, which can effectively reduce the power consumption of the server.

[0120] The second possible implementation is:

[0121] The second node monitors the shared memory, and when determining that data matching the preset tag exists in the shared memory, reads the data matching the preset tag from the shared memory, and uses the data as the target query result.

[0122] In this manner, the second node can monitor the shared memory in real time. When storing data in the shared memory, the second node can determine whether the data label of each stored data is consistent with the preset label. If they are consistent, it can be determined that the stored data is the target query result. If they are inconsistent, the stored data is not the target query result.

[0123] In some embodiments, the shared memory may also be a memory dedicated to storing the target query result, and the first thread may directly store the target query result in the shared memory. Since there is no other data in the shared memory, the first node does not need to add a label to distinguish the target query result from other data, which can improve the processing efficiency of the target query result.

[0124] S408: The second node updates a statistical information table based on the statistical information.

[0125] In some embodiments, when the second node obtains the target query result, it can collect statistics on the target query result, wherein the main statistical information includes maximum value, minimum value, number of distinct values, most common value, histogram, etc.

[0126] When the second node obtains the statistical information, it can update the data corresponding to the statistical information table according to the type of statistical information. For example, if the statistical information is the data of the entire table, all statistical information is directly updated. If the collected statistical information is partial data of the table, only the statistical information of this part of the data is updated. If it is necessary to update the part of the fusion calculation, the fusion calculation is performed according to the actual situation and then updated.

[0127] For example, according to the condition a>3, only the statistical information of a>3 is collected, and the number of distinct values, most common values, and histograms of the part of column a that is greater than 3 can be directly updated. The number of rows, maximum value, minimum value, etc. need to be calculated and updated.

[0128] The number of rows = the original total number of rows - the original number of rows with a>3 (calculated by the optimizer) + the most recently counted number of rows with a>3.

[0129] The maximum value is updated directly (since all data with a>3 are obtained, the maximum value can be updated directly).

[0130] Update of the minimum value: If the original minimum value is less than or equal to 3, keep the original minimum value; if it is greater than 3, take the latest minimum value.

[0131] In summary, if Figure 5 As shown, the statistical information collection method provided in the embodiment of the present application avoids the problem of statistical information collection and ordinary queries competing for database input and output (IO) resources by setting up dual nodes (dual threads), and the second thread reuses the query results of the first thread executing the query statement, and sets a trigger condition for comparison with the cardinality estimation result to trigger statistical information collection when the difference is large, which can ensure the timeliness of statistical information collection and ensure that the statistical information is accurate, which is conducive to the optimizer to obtain the optimal execution plan.

[0132] On the basis of the above-mentioned embodiments, the embodiments of the present application further provide a statistical information collection system for a database, which is applied to a database management system, and the database management system includes a first node and a second node.

[0133] Figure 6 A schematic diagram of the structure of a database statistical information collection system 60 provided in an embodiment of the present application, as shown in FIG. Figure 6 As shown, including:

[0134] The first node is used to obtain a query statement for performing a read operation on the database;

[0135] The first node is used to generate a query plan for the query statement; the query plan includes an estimated number of query rows for the query statement;

[0136] The first node is used to execute the query statement based on the query plan, obtain a target query result, and store the target query result in a shared memory;

[0137] The second node is used to read the target query result from the shared memory when the number of rows corresponding to the target query result does not match the estimated number of query rows, and perform statistical processing on the target query result to obtain the statistical information.

[0138] In some embodiments, the first node is also used to, if the number of rows of the target query result is not within the row number range determined based on the query row number, then the number of data rows corresponding to the target query result does not match the estimated query row number; if the number of rows of the target query result is within the row number range, then the number of data rows corresponding to the target query result matches the estimated query row number.

[0139] In some embodiments, the first node is further used to obtain the upper limit and lower limit of the row number range based on the estimated query row number and the row number adjustment parameter corresponding to the query statement; and determine the row number range based on the upper limit and the lower limit.

[0140] In some embodiments, the first node is also used to insert a preset tag into a preset field of the target query result to obtain the target query result with the tag, and store the target query result with the tag in the shared memory; the preset tag is used to characterize the target query result for generating statistical information.

[0141] In some embodiments, the second node is further used to read the target query result from the shared memory based on the label, and perform statistics on the target query result to obtain the statistical information.

[0142] In some embodiments, the first node is further used to send indication information to the second node, where the indication information is used to instruct the second node to read the target query result from the shared memory, and the indication information includes a label of the target query result.

[0143] In some embodiments, the second node is further used to monitor the shared memory, and when it is determined that there is data matching the preset tag in the shared memory, read the data matching the preset tag from the shared memory and use the data as the target query result.

[0144] In some embodiments, the second node is further used to update a statistical information table based on the statistical information.

[0145] In some embodiments, the first node is also used for the first node to obtain the row number adjustment parameter from a preset parameter library according to the estimated query row number corresponding to the query statement.

[0146] The database statistical information collection system provided in the embodiment of the present application can execute the database statistical information collection method provided in any of the above embodiments, and its principles and technical effects are similar, which will not be repeated here.

[0147] An embodiment of the present application also provides an electronic device.

[0148] Figure 7 A schematic diagram of the structure of an electronic device 70 provided in an embodiment of the present application is shown in FIG. Figure 7 As shown, including:

[0149] Processor 701.

[0150] The memory 702 is used to store executable instructions of the terminal device.

[0151] Specifically, the program may include program codes, and the program codes include computer operation instructions. The memory 702 may include a high-speed RAM memory, and may also include a non-volatile memory (non-volatile memory), such as at least one disk memory.

[0152] The processor 701 is used to execute the computer execution instructions stored in the memory 702 to implement the technical solution of the database statistical information collection method embodiment described in the above method embodiment.

[0153] The processor 701 may be a central processing unit (CPU), or an application specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application.

[0154] Optionally, the electronic device 70 may further include a communication interface, so that it can communicate and interact with an external device through the communication interface, and the external device may be, for example, a user terminal (e.g., a mobile phone, a tablet). In a specific implementation, if the communication interface, the memory 702, and the processor 701 are implemented independently, the communication interface, the memory 702, and the processor 701 may be interconnected through a bus and communicate with each other.

[0155] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc., but it does not mean that there is only one bus or one type of bus.

[0156] Optionally, in a specific implementation, if the communication interface, the memory 702 and the processor 701 are integrated on a chip, the communication interface, the memory 702 and the processor 701 can communicate through an internal interface.

[0157] A computer-readable storage medium is also provided in an embodiment of the present application, on which a computer program is stored. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. The implementation principle and technical effect are similar and will not be repeated here.

[0158] In one possible implementation, a computer-readable medium may include a random access memory (RAM), a read-only memory (ROM), a compact disc read-only memory (CD-ROM) or other optical disk storage, a magnetic disk storage or other magnetic storage device, or any other medium that is intended to carry or store the required program code in the form of instructions or data structures and can be accessed by a computer. Moreover, any connection is appropriately referred to as a computer-readable medium. For example, if the software is transmitted from a website, server or other remote source using a coaxial cable, a fiber optic cable, a twisted pair, a digital subscriber line (DSL) or wireless technologies such as infrared, radio and microwave, then the coaxial cable, fiber optic cable, twisted pair, DSL or wireless technologies such as infrared, radio and microwave are included in the definition of the medium. Disks and optical disks as used herein include optical disks, laser disks, optical disks, digital versatile disks (DVDs), floppy disks and Blu-ray disks, where disks usually reproduce data magnetically, while optical disks reproduce data optically using lasers. Combinations of the above should also be included within the scope of computer-readable media.

[0159] A computer program product is also provided in an embodiment of the present application, including a computer program. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. Its implementation principle and technical effect are similar and will not be repeated here.

[0160] In the specific implementation of the above-mentioned terminal device or server, it should be understood that the processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. The general-purpose processor can be a microprocessor or the processor can be any conventional processor, etc. The steps of the method disclosed in the embodiments of the present application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor.

[0161] Those skilled in the art will appreciate that all or part of the steps of any of the above method embodiments may be completed by hardware associated with program instructions. The aforementioned program may be stored in a computer-readable storage medium, and when the program is executed, all or part of the steps of the above method embodiments are executed.

[0162] If the technical solution of the present application is implemented in the form of software and sold or used as a product, it can be stored in a computer-readable storage medium. Based on such an understanding, all or part of the technical solution of the present application can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a computer program or several instructions. The computer software product enables a computer device (which can be a personal computer, a server, a network device, or a similar electronic device) to perform all or part of the steps of the method described in the embodiment of the present application.

[0163] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.

[0164] It should be further noted that, although the various steps in the flowchart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flowchart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0165] It should be understood that the above-mentioned device embodiments are only illustrative, and the device of the present application can also be implemented in other ways. For example, the division of units / modules in the above-mentioned embodiments is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units, modules or components can be combined, or can be integrated into another system, or some features can be ignored or not executed.

[0166] In addition, unless otherwise specified, each functional unit / module in each embodiment of the present application may be integrated into one unit / module, each unit / module may exist physically separately, or two or more units / modules may be integrated together. The above-mentioned integrated unit / module may be implemented in the form of hardware or in the form of a software program module.

[0167] If the integrated unit / module is implemented in the form of hardware, the hardware may be a digital circuit, an analog circuit, etc. The physical implementation of the hardware structure includes but is not limited to transistors, memristors, etc. If not specifically stated, the processor may be any appropriate hardware processor, such as a CPU, a GPU, an FPGA, a DSP, an ASIC, etc. If not specifically stated, the storage unit may be any appropriate magnetic storage medium or magneto-optical storage medium, such as a resistive random access memory RRAM (Resistive Random Access Memory), a dynamic random access memory DRAM (Dynamic Random Access Memory), a static random access memory SRAM (Static Random-Access Memory), an enhanced dynamic random access memory EDRAM (Enhanced Dynamic Random Access Memory), a high-bandwidth memory HBM (High-Bandwidth Memory), a hybrid memory cube HMC (Hybrid Memory Cube), etc.

[0168] If the integrated unit / module is implemented in the form of a software program module and sold or used as an independent product, it can be stored in a computer-readable memory. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art or all or part of the technical solution, can be embodied in the form of a software product, which is stored in a memory and includes several instructions for a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned memory includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0169] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0170] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application 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 application.

Claims

1. A method for collecting statistical information of a database, characterized in that: Applied to a database management system, the database management system includes a first node and a second node, and the method includes: The first node obtains a query statement for performing a read operation on the database; The first node generates a query plan for the query statement; the query plan includes an estimated number of query rows for the query statement; The first node executes the query statement based on the query plan to obtain a target query result, and stores the target query result in a shared memory; When the number of rows corresponding to the target query result does not match the estimated number of query rows, the second node reads the target query result from the shared memory and performs statistical processing on the target query result to obtain the statistical information.

2. The method according to claim 1, characterized in that If the number of rows of the target query result is not within the row number range determined based on the query row number, then the number of data rows corresponding to the target query result does not match the estimated query row number; If the number of rows of the target query result is within the range of rows, the number of data rows corresponding to the target query result matches the estimated number of query rows.

3. The method according to claim 2, characterized in that The method further comprises: The first node obtains an upper limit value and a lower limit value of the row number range based on the estimated query row number and a row number adjustment parameter corresponding to the query statement; The first node determines the row number range based on the upper limit value and the lower limit value.

4. The method according to claim 3, characterized in that The storing the target query result in a shared memory includes: The first node inserts a preset tag into a preset field of the target query result to obtain the target query result with the tag added, and stores the target query result with the tag added in the shared memory; the preset tag is used to characterize the target query result for generating statistical information; The second node reads the target query result from the shared memory, including: The second node reads the target query result from the shared memory based on the label, and performs statistics on the target query result to obtain the statistical information.

5. The method according to claim 4, characterized in that The method further comprises: The first node sends instruction information to the second node, where the instruction information is used to instruct the second node to read the target query result from the shared memory, and the instruction information includes a label of the target query result; or, The second node monitors the shared memory, and when determining that data matching the preset tag exists in the shared memory, reads the data matching the preset tag from the shared memory, and uses the data as the target query result.

6. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: The second node updates a statistical information table based on the statistical information.

7. The method according to claim 3, characterized in that The method further comprises: The first node obtains the row number adjustment parameter from a preset parameter library according to the estimated query row number corresponding to the query statement.

8. An electronic device, characterized in that: include: Memory for storing computer programs; A processor, configured to execute the computer program to implement the method according to any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and the computer program is executed by a processor to implement the method according to any one of claims 1 to 7.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a controller.

Citation Information

Cited By

  • Data query method and system, device cluster, medium, and program product

    US20250342154A1