Multi-mode database statistical operator execution information method and device and medium
By recording and displaying the execution information of each execution operator in a multi-mode database, the problem of high problem positioning cost during multi-mode database query is solved, and the query efficiency is improved.
Patent Information
- Application Number
- CN202410924906.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-11
- Publication Date
- 2024-11-26
AI Technical Summary
During the multi-mode database query process, problem positioning costs are high, making it difficult to effectively carry out data queries and cross-mode data queries in complex scenarios.
Based on the query optimizer, timing engine and relationship engine, add the statement explain analyze and the SQL statement to be executed, and record the execution information of each execution operator, including the number of data strips read by the operator, the execution time and CPU occupies and collects and displays this information uniformly through query optimizers.
Through detailed execution information recording and display, it is possible to more conveniently locate which operator the problem occurs, thereby targeting the problem positioning cost and improving the multi-mode database query efficiency.
Smart Images

Figure CN119025537A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of multi-mode database optimization, and in particular to a multi-mode database system computing sub-execution information method, device and medium. Background Art
[0002] A multi-model database is a database system that can support the processing of a mixture of multiple data modes. A multi-model database involves a database of data in multiple modes (time series or relational).
[0003] In the operation of multi-modal databases, it is often necessary to perform data queries in complex scenarios (complex time series queries and cross-modal data queries). If a problem occurs in a scenario, the cost of tracking the problem is very high because the links are too complicated.
[0004] Therefore, how to avoid the high cost of problem location during multi-modal database query is a technical problem that needs to be solved urgently. Summary of the invention
[0005] The technical task of the present invention is to provide a multi-mode database statistical computer execution information method, device and medium to solve the problem of how to avoid the high cost of problem location during multi-mode database query.
[0006] The technical task of the present invention is achieved in the following way: a method for collecting operator execution information of a multi-mode database, which adds a statement explainanalyse for displaying the plan and a SQL statement to be executed on the basis of a query optimizer, a timing engine and a relational engine, and records the execution information of each execution operator during the execution process;
[0007] The execution information of each execution operator includes the number of data read by the operator, execution time, and CPU usage;
[0008] The query optimizer is used to split SQL statements;
[0009] The timing engine is used to execute the timing part;
[0010] The relational engine is used to execute the relational part and collect and display the results in a unified manner.
[0011] Preferably, the method is as follows:
[0012] Execute explain analyse statement;
[0013] Logical plan writes collected statistics switch;
[0014] Generate a physical plan and write the collected statistics switch into flowSpec;
[0015] Execute the timing engine and relational engine to collect statistical execution information;
[0016] The query optimizer processes statistics and displays them.
[0017] Preferably, the timing engine and the relationship engine statistical execution information are as follows:
[0018] The operator starts executing and the start time is recorded;
[0019] Get the data required by the operator and record the number of data items;
[0020] Process the acquired data and record the system information of CPU usage and memory usage during the processing. The statistical information here can be added according to different operators to see the actual situation.
[0021] When the processing is finished, record the end time and take the difference with the start time to get the operator execution time;
[0022] Returns the result.
[0023] Preferably, the statistical information of the overall timing execution process of the timing engine is stored in an array, and the operator ID of the specific operator is recorded for subsequent display of statistical information.
[0024] Preferably, the timing engine executes operators by layer, and each layer of operators counts its own execution data. After the execution is completed, it returns to the operator of the previous layer and writes the statistical information array.
[0025] Preferably, the query optimizer processes statistics as follows:
[0026] Split the obtained statistical information array and bind it to each operator according to the operator ID;
[0027] According to different statistical information, add display prefixes to organize statistical information. For example, the number of data items will be converted to: "read rows: numbers";
[0028] When generating the physical plan display Json, write the sorted statistical information data for the final plan display.
[0029] A multi-mode database system computing sub-execution information system, the system is used to execute the multi-mode database system computing sub-execution information method as described above; the system includes a multi-mode database engine unit and a display plan unit;
[0030] The display plan unit is used to add the explain analyse statement of the display plan and the SQL statement to be executed, and record the execution information of each execution operator during the execution process; the execution information of each execution operator includes the number of data read by the operator, the execution time and the CPU usage;
[0031] The multi-mode database engine unit includes a time series engine, a relational engine, and a query optimizer;
[0032] The query optimizer is used to split SQL statements;
[0033] The timing engine is used to execute the timing part;
[0034] The relational engine is used to execute the relational part and collect and display the results in a unified manner.
[0035] As a preferred embodiment, the working process of the system is as follows:
[0036] ①Execute the explain analyse statement;
[0037] ② Add the collection flag collected in the logical plan stage to indicate that the corresponding statement is a statement for collecting statistical information;
[0038] ③ The operator starts executing, verifies the collected flag, and records the start time;
[0039] ④ Obtain the data required by the operator and record the number of data items;
[0040] ⑤Process the acquired data and record the statistical information required by the operator;
[0041] ⑥When the processing is completed, record the end time;
[0042] ⑦ Collect statistical information to the query optimizer, split the obtained statistical information array, and bind it to each operator according to the operator ID;
[0043] ⑧Add display prefixes and organize statistical information according to different statistical information;
[0044] ⑨Write the sorted statistical information data when generating the physical plan display Json for the final plan display.
[0045] Among them, time series data refers to time series data, which is a data column recorded in chronological order for the same unified indicator.
[0046] Cross-modal data: data results from combined queries of time series data and relational data.
[0047] An electronic device comprising: a memory and at least one processor;
[0048] Wherein, the memory stores a computer program;
[0049] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the multi-mode database system computing sub-information execution method as described above.
[0050] A computer-readable storage medium stores a computer program, which can be executed by a processor to implement the multi-mode database statistical calculation sub-execution information method as described above.
[0051] The multi-mode database statistical computer execution information method, device and medium of the present invention have the following advantages:
[0052] (1) The present invention adds a special statement similar to the display plan explain analyse + SQL statement to be executed, and records the execution information of each execution operator during the execution process, such as the number of data read by the operator, the execution time, the CPU usage, etc. Through these statistical information, it is more convenient to locate which operator the problem occurs on, so as to handle it more targetedly;
[0053] (ii) The present invention solves the problem of high problem location cost in multi-mode database query process by executing the statistical execution information of the timing engine and the relational engine and processing and displaying the statistical information by the query optimizer, thereby reducing the problem location cost and improving the efficiency of multi-mode database query. BRIEF DESCRIPTION OF THE DRAWINGS
[0054] The present invention is further described below in conjunction with the accompanying drawings.
[0055] Attached Figure 1 A flowchart of a method for executing information for a multi-modal database statistics computer;
[0056] Attached Figure 2 A flowchart for executing statistics of execution information for the timing engine and the relational engine;
[0057] Attached Figure 3 A flowchart of the process of processing statistics for the query optimizer;
[0058] Attached Figure 4 It is a schematic diagram of the structure of a multi-mode database unit;
[0059] Attached Figure 5 Schematic diagram showing statistical feedback. DETAILED DESCRIPTION
[0060] The multi-mode database system computer execution information method, device and medium of the present invention are described in detail below with reference to the drawings and specific embodiments of the specification.
[0061] Embodiment 1:
[0062] This embodiment provides a method for collecting operator execution information in a multi-mode database. The method adds explain analyse statements for displaying plans and SQL statements to be executed on the basis of a query optimizer, a time series engine, and a relational engine, and records the execution information of each operator during the execution process.
[0063] The execution information of each execution operator includes the number of data read by the operator, execution time, and CPU usage;
[0064] The query optimizer is used to split SQL statements;
[0065] The timing engine is used to execute the timing part;
[0066] The relational engine is used to execute the relational part and collect and display the results in a unified manner.
[0067] As attached Figure 1 As shown, the method is as follows:
[0068] S1. Execute the explain analyse statement;
[0069] S2: Logical plan writes collected statistics switch;
[0070] S3. Generate a physical plan and write the collected statistics switch into flowSpec;
[0071] S4, executing the timing engine and the relationship engine to collect statistics on execution information;
[0072] S5. The query optimizer processes and displays the statistical information.
[0073] As attached Figure 2 As shown, the execution timing engine and the relationship engine statistical execution information in step S4 of this embodiment are specifically as follows:
[0074] S401, the operator starts to execute and the start time is recorded;
[0075] S402, obtaining the data required by the operator and recording the number of data items;
[0076] S403, processing the acquired data, and recording the system information of CPU usage and memory usage during the processing; wherein the statistical information here can be added according to different operators to see the actual situation;
[0077] S404, the processing is completed, the end time is recorded, and the difference between the end time and the start time is calculated to obtain the operator execution time;
[0078] S405. Return the result.
[0079] The statistical information of the overall timing execution process of the timing engine is stored in an array, and the operator ID of the specific operator is recorded for the subsequent display of statistical information.
[0080] The timing engine execution operators in this embodiment are executed layer by layer. Each layer of operators counts its own execution data. After the execution is completed, it returns to the operator of the previous layer and writes the statistical information array.
[0081] As attached Figure 3 As shown, the query optimizer in step S5 of this embodiment processes the statistical information as follows:
[0082] S501, split the obtained statistical information array and bind it to each operator according to the operator ID;
[0083] S502, adding display prefixes according to different statistical information, and sorting the statistical information, for example: the number of data items will be converted to: "read rows: numbers";
[0084] S503. When generating the physical plan display Json, write the sorted statistical information data for displaying the final plan.
[0085] As attached Figure 5 As shown, the information of the relational engine and the timing engine is statistically collected and displayed as feedback.
[0086] Embodiment 2:
[0087] This embodiment provides a multi-mode database statistical calculation sub-execution information system, which is used to execute the multi-mode database statistical calculation sub-execution information method in Example 1; the system includes a multi-mode database engine unit and a display plan unit;
[0088] The display plan unit is used to add the explain analyse statement of the display plan and the SQL statement to be executed, and record the execution information of each execution operator during the execution process; the execution information of each execution operator includes the number of data read by the operator, the execution time and the CPU usage;
[0089] As attached Figure 4 As shown, the multi-mode database engine unit includes a timing engine, a relational engine, and a query optimizer;
[0090] The query optimizer is used to split SQL statements;
[0091] The timing engine is used to execute the timing part;
[0092] The relational engine is used to execute the relational part and collect and display the results in a unified manner.
[0093] The working process of the system is as follows:
[0094] ①Execute the explain analyse statement;
[0095] ② Add the collection flag collected in the logical plan stage to indicate that the corresponding statement is a statement for collecting statistical information;
[0096] ③ The operator starts executing, verifies the collected flag, and records the start time;
[0097] ④ Obtain the data required by the operator and record the number of data items;
[0098] ⑤Process the acquired data and record the statistical information required by the operator;
[0099] ⑥When the processing is completed, record the end time;
[0100] ⑦ Collect statistical information to the query optimizer, split the obtained statistical information array, and bind it to each operator according to the operator ID;
[0101] ⑧Add display prefixes and organize statistical information according to different statistical information;
[0102] ⑨Write the sorted statistical information data when generating the physical plan display Json for the final plan display.
[0103] Embodiment 3:
[0104] This embodiment also provides an electronic device, including: a memory and a processor;
[0105] Wherein, the memory stores computer-executable instructions;
[0106] The processor executes the computer execution instructions stored in the memory, so that the processor executes the multi-mode database statistical calculation sub-execution information method in any embodiment of the present invention.
[0107] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor may be a microprocessor or any conventional processor, etc.
[0108] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.
[0109] Embodiment 4:
[0110] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the multi-mode database system computing sub-information execution method in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code for implementing the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.
[0111] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.
[0112] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.
[0113] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.
[0114] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.
[0115] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.
Claims
1. A method for calculating the execution information of multi-mode database statistics. It is characterized in that This method adds explain analyse statement for displaying the plan and SQL statement to be executed on the basis of query optimizer, timing engine and relational engine, and records the execution information of each execution operator during the execution process; The execution information of each execution operator includes the number of data read by the operator, execution time, and CPU usage; The query optimizer is used to split SQL statements; The timing engine is used to execute the timing part; The relational engine is used to execute the relational part and collect and display the results in a unified manner.
2. The method for calculating the execution information of a multi-mode database statistics sub-computer according to claim 1, It is characterized in that The method is as follows: Execute explain analyse statement; Logical plan writes collected statistics switch; Generate a physical plan and write the collected statistics switch into flowSpec; Execute the timing engine and relational engine to collect statistical execution information; The query optimizer processes statistics and displays them.
3. The method for calculating the execution information of a multi-mode database statistics sub-computer according to claim 2, It is characterized in that The execution statistics of the timing engine and relational engine are as follows: The operator starts executing and the start time is recorded; Get the data required by the operator and record the number of data items; Process the acquired data and record the system information of CPU usage and memory usage during the processing; When the processing is finished, record the end time and take the difference with the start time to get the operator execution time; Returns the result.
4. The method for calculating the execution information of a multi-mode database statistics computer according to claim 1, It is characterized in that The statistical information of the overall timing execution process of the timing engine is stored in an array, and the operator ID of the specific operator is recorded for the subsequent display of statistical information.
5. The method for calculating the execution information of a multi-mode database statistics sub-computer according to claim 1, It is characterized in that The timing engine executes operators by layer. Each layer of operators counts its own execution data. After the execution is completed, it returns to the operator of the previous layer and writes the statistical information array.
6. The method for calculating the execution information of a multi-mode database statistics sub-computer according to claim 2, It is characterized in that The query optimizer processes statistics as follows: Split the obtained statistical information array and bind it to each operator according to the operator ID; According to different statistical information, add display prefixes and organize statistical information; When generating the physical plan display Json, write the sorted statistical information data for the final plan display.
7. A multi-mode database computing sub-execution information system, It is characterized in that The system is used to execute the multi-mode database statistical calculation sub-information execution method as claimed in any one of claims 1 to 6; the system comprises a multi-mode database engine unit and a display plan unit; The display plan unit is used to add the explain analyse statement of the display plan and the SQL statement to be executed, and record the execution information of each execution operator during the execution process; the execution information of each execution operator includes the number of data read by the operator, the execution time and the CPU usage; The multi-mode database engine unit includes a time series engine, a relational engine, and a query optimizer; The query optimizer is used to split SQL statements; The timing engine is used to execute the timing part; The relational engine is used to execute the relational part and collect and display the results in a unified manner.
8. The multi-mode database statistical computer execution information system according to claim 7, It is characterized in that The working process of the system is as follows: ①Execute the explain analyse statement; ② Add the collection flag collected in the logical plan stage to indicate that the corresponding statement is a statement for collecting statistical information; ③ The operator starts executing, verifies the collected flag, and records the start time; ④ Obtain the data required by the operator and record the number of data items; ⑤Process the acquired data and record the statistical information required by the operator; ⑥When the processing is completed, record the end time; ⑦ Collect statistical information to the query optimizer, split the obtained statistical information array, and bind it to each operator according to the operator ID; ⑧Add display prefixes and organize statistical information according to different statistical information; ⑨Write the sorted statistical information data when generating the physical plan display Json for the final plan display.
9. An electronic device, It is characterized in that include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the multi-modal database statistical computer execution information method according to any one of claims 1 to 6.
10. A computer-readable storage medium, It is characterized in that The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the multi-modal database statistical computer execution information method according to any one of claims 1 to 6.
