Method, device, medium and product for managing memory overhead of SQL statements
By generating execution plans for SQL statements and calculating and adjusting memory overhead, the problem of SQL statement memory consumption is solved, achieving efficient memory management and database performance optimization.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- CETC JINCANG (BEIJING) TECH CO LTD
- Filing Date
- 2026-04-03
- Publication Date
- 2026-06-12
Smart Images
Figure CN122196016A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database technology, and in particular to a method, device, medium, and product for managing the memory overhead of SQL statements. Background Technology
[0002] Structured Query Language (SQL) is the standard database language and a highly versatile database language. SQL statements, written in SQL, serve as the core medium for interaction between applications and the database, and their execution efficiency directly determines the overall performance of the database system. Memory consumption is one of the key factors affecting execution efficiency during SQL statement execution. Monitoring and analyzing the memory consumption of SQL statements is of significant practical importance for database performance tuning, resource planning, and fault prevention. Summary of the Invention
[0003] One object of the present invention is to provide a method, apparatus, medium and product for optimizing SQL statement memory management by estimating the memory overhead of SQL statements.
[0004] Specifically, this invention provides a method for managing the memory overhead of SQL statements, comprising: Obtain the SQL statement and generate an execution plan for the SQL statement; Extract the plan nodes from the execution plan, as well as the number of rows and the row width corresponding to each plan node; The estimated memory overhead of the SQL statement is obtained based on the planned nodes and the corresponding number of rows and row width. Configure the execution memory required for the SQL statement based on the estimated memory overhead.
[0005] Optionally, the step of obtaining the estimated memory overhead of the SQL statement based on the plan node and the corresponding number of rows and row width includes: The estimated memory overhead of each planned node is calculated using the memory estimation formula corresponding to each planned node; The estimated memory cost of the execution plan is obtained by summing the estimated memory costs corresponding to all the planned nodes. The memory estimation formula is as follows: node_mem = plan_rows * plan_width * factor; node_mem is the estimated memory overhead for the planned node; plan_rows is the number of rows; plan_width is the line width; factor is a preset adjustment factor corresponding to each planned node.
[0006] Optionally, after the SQL statement is executed, the actual memory overhead of each plan node of the SQL statement is obtained; Compare the estimated memory overhead and actual memory overhead for each of the planned nodes; Adjust the adjustment factor of the planned node based on the comparison results.
[0007] Optionally, the step of adjusting the adjustment factor of the planning node based on the comparison result includes, prior to: Detect whether the difference between the estimated memory overhead and the actual memory overhead is greater than a preset threshold. If yes, output an error message; otherwise, execute the step of adjusting the adjustment factor of the planning node based on the comparison result.
[0008] Optionally, the step of adjusting the adjustment factor of the planning node based on the comparison result includes, prior to: If the difference between the actual number of rows and the estimated number of rows for each planned node is greater than a preset row count threshold, the optimizer statistics are updated; otherwise, the step of adjusting the adjustment factor of the planned node based on the comparison result is executed.
[0009] Optionally, the step of generating the execution plan for the SQL statement includes: Use the EXPLAIN command to generate an execution plan in JSON format.
[0010] Optionally, the planning node includes at least sorting nodes, hash nodes, materialization nodes, and set nodes.
[0011] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer executable program stored in the memory and running on the processor, wherein the processor, when executing the computer executable program, implements a memory overhead management method for SQL statements according to any of the preceding claims.
[0012] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer-executable program is stored, wherein the computer-executable program, when executed by a processor, implements a memory overhead management method for SQL statements according to any of the preceding claims.
[0013] According to another aspect of the present invention, a computer program product is also provided, comprising a computer executable program that, when executed by a processor, implements a memory overhead management method for SQL statements according to any of the preceding claims.
[0014] The SQL statement memory overhead management method of this invention obtains the SQL statement and generates its execution plan. It extracts the plan nodes and the corresponding row count and row width for each plan node. Based on these plan nodes and their corresponding row count and width, it obtains the estimated memory overhead of the SQL statement and configures the required execution memory for the SQL statement according to the estimated memory overhead. This method can estimate the memory overhead of the SQL statement before its actual execution, thereby allocating reasonable memory based on the estimated overhead and effectively avoiding execution interruptions due to insufficient memory. It also helps reduce disk I / O during SQL statement execution, thus improving execution efficiency. Furthermore, using the plan nodes in the execution plan to estimate the estimated memory overhead improves the comprehensiveness of memory overhead estimation. It also facilitates independent adjustment of the estimation accuracy for each plan node, further enhancing the accuracy of the estimated memory overhead. Additionally, by estimating the memory overhead of the SQL statement, it allows for analysis and optimization of SQL statements with high estimated memory overhead, thereby improving the overall performance of the database.
[0015] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description
[0016] The following sections will describe some specific embodiments of the invention in a detailed manner by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic flowchart of a method for managing the memory overhead of SQL statements according to an embodiment of the present invention; Figure 2 This is a schematic flowchart illustrating the step of estimating memory overhead in a method for managing the memory overhead of SQL statements according to an embodiment of the present invention. Figure 3 This is a schematic flowchart of a method for managing the memory overhead of SQL statements according to another embodiment of the present invention; Figure 4 This is a schematic flowchart of a method for managing the memory overhead of SQL statements according to yet another embodiment of the present invention; Figure 5 This is a schematic flowchart of a method for managing the memory overhead of SQL statements according to yet another embodiment of the present invention; Figure 6 This is a schematic flowchart of a method for managing the memory overhead of SQL statements according to yet another embodiment of the present invention; Figure 7This is a schematic diagram of a computer device according to an embodiment of the present invention; Figure 8 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 9 This is a schematic diagram of a computer program product according to an embodiment of the present invention. Detailed Implementation
[0017] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.
[0018] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).
[0019] The flowcharts provided in this invention are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.
[0020] like Figure 1 As shown, in one embodiment, the method for managing the memory overhead of SQL statements generally includes: Step S101: Obtain the SQL statement and generate the execution plan of the SQL statement.
[0021] Specifically, after obtaining the SQL statement, the EXPLAIN command is used to generate an execution plan in JSON format. JSON-formatted execution plans are easier for programs to parse and process. Furthermore, the JSON-formatted execution plan has a clear structure, explicitly displaying each plan node.
[0022] Step S102: Extract the plan nodes in the execution plan, as well as the number of rows and the row width corresponding to each plan node.
[0023] Specifically, plan nodes such as sorting nodes, hash nodes, materialization nodes, and set nodes are extracted from the execution plan. Furthermore, the number of rows and row width corresponding to each plan node are extracted. The number of rows is the estimated number of rows, which is the number of data rows that the optimizer estimates each plan node will return. The row width is the estimated row width, which is the number of bytes that the optimizer estimates each output row will occupy on average.
[0024] Step S103: Obtain the estimated memory overhead of the SQL statement based on the planned nodes and the corresponding number of rows and row width.
[0025] Reference Figure 2 As shown, in one embodiment, this step includes: Step S201: Calculate the estimated memory overhead of each planned node using the memory estimation formula corresponding to each planned node.
[0026] Specifically, the memory estimation formula is as follows: node_mem = plan_rows * plan_width * factor; node_mem is the estimated memory overhead for the planned node; plan_rows is the number of rows; plan_width is the line width; `factor` is a preset adjustment factor corresponding to each planning node. Each planning node's preset adjustment factor is configured with an initial value. For example, the initial value of the adjustment factor for the sorting node is 2.5, the initial value of the adjustment factor for the hash node is 1.8, the initial value of the adjustment factor for the materialization node is 1.2, and the initial value of the adjustment factor for the set node is 1.5.
[0027] Step S202: Add up the estimated memory overhead corresponding to all planned nodes to obtain the estimated memory overhead of the execution plan.
[0028] After calculating the estimated memory cost for each plan node, the estimated memory cost for the entire execution plan can be obtained by adding up the estimated memory costs for all plan nodes.
[0029] Step S104: Configure the execution memory required for the SQL statement based on the estimated memory overhead.
[0030] Specifically, after obtaining the estimated memory overhead of the SQL statement execution plan, the required execution memory for the SQL statement can be configured according to the estimated memory overhead to avoid insufficient memory during execution.
[0031] In this embodiment, the SQL statement is obtained and its execution plan is generated. The plan nodes and the number and width of rows corresponding to each plan node are extracted from the execution plan. Based on these plan nodes and their corresponding row numbers and widths, the estimated memory overhead of the SQL statement is obtained. The required execution memory for the SQL statement is then configured according to the estimated memory overhead. This allows for the estimation of the SQL statement's memory overhead before its actual execution, enabling the allocation of appropriate memory based on the estimated overhead and effectively preventing execution interruptions due to insufficient memory. It also helps reduce disk I / O during SQL statement execution, thereby improving execution efficiency. Furthermore, using plan nodes in the execution plan to estimate the estimated memory overhead improves the comprehensiveness of the memory overhead estimation. It also facilitates independent adjustment of the estimation accuracy for each plan node, further enhancing the accuracy of the estimated memory overhead. Additionally, by estimating the memory overhead of the SQL statement, SQL statements with high estimated memory overhead can be analyzed and optimized, thereby improving the overall performance of the database.
[0032] By utilizing execution plans to estimate the memory overhead of SQL statements, which can be obtained using the EXPLAIN command without modifying the database kernel, non-intrusive memory monitoring and analysis can be achieved. This method can be used without database kernel expertise, thus improving its versatility.
[0033] The estimated memory overhead for each planned node is calculated using a memory estimation formula, which facilitates estimation and makes it easy to adjust the adjustment factor.
[0034] like Figure 3 As shown, in one embodiment, the method for managing the memory overhead of SQL statements generally includes: after the SQL statement execution is complete: Step S301: Obtain the actual memory overhead of each plan node in the SQL statement.
[0035] Specifically, after the SQL statement is actually executed, obtain the actual memory overhead of each plan node of the SQL statement during the execution process.
[0036] Step S302: Compare the estimated memory cost and the actual memory cost of each planned node.
[0037] Specifically, compare the difference between the estimated memory overhead and the actual memory overhead for each planned node.
[0038] Step S303: Adjust the adjustment factor of the planning node according to the comparison results.
[0039] Specifically, if the actual memory overhead is greater than the estimated memory overhead, the adjustment factor is increased. If the actual memory overhead is less than the estimated memory overhead, the adjustment factor is decreased.
[0040] For example, a sorted node has an estimated number of rows of 7999, a row width of 84, and an adjustment factor of 2.5. The estimated memory overhead is 7999 * 84 * 2.5 = 1679790B = 1640KB. The actual memory overhead is 1590KB. Therefore, the adjustment factor needs to be reduced.
[0041] In this embodiment, after the SQL statement is executed, the actual memory cost of each plan node is obtained. The estimated memory cost and the actual memory cost of each plan node are compared, and the adjustment factor of the plan node is adjusted based on the comparison result. The memory estimation formula for each plan node can be continuously optimized, thereby improving the accuracy of memory cost estimation.
[0042] like Figure 4 As shown, the general methods for managing the memory overhead of SQL statements include: Step S401: Obtain the actual memory overhead of each plan node in the SQL statement.
[0043] Step S402: Compare the estimated memory cost and the actual memory cost of each planned node.
[0044] Step S403: Detect whether the difference between the estimated memory overhead and the actual memory overhead is greater than a preset threshold. If yes, proceed to step S404; otherwise, proceed to step S405.
[0045] Specifically, the preset threshold can be a preset percentage of the estimated memory overhead. In other words, it detects whether the difference between the estimated memory overhead and the actual memory overhead is too large.
[0046] Step S404: Output an exception message. If the difference between the estimated and actual memory usage is too large, it's likely due to abnormal data characteristics or changes in the runtime environment during SQL statement execution, rather than simply an inaccurate adjustment factor. Therefore, an exception message needs to be output to allow database administrators to optimize the SQL statement or the runtime environment promptly.
[0047] Step S405: Adjust the adjustment factor of the planning node based on the comparison results. If the estimated memory overhead differs from the actual memory overhead, but the difference is small, the adjustment factor may not be accurate enough, so adjust the adjustment factor of the planning node.
[0048] In this embodiment, before adjusting the adjustment factor of the planning node based on the comparison result, it is detected whether the difference between the estimated memory overhead and the actual memory overhead is greater than a preset threshold. If so, an exception message is output; otherwise, the step of adjusting the adjustment factor of the planning node based on the comparison result is executed. This allows monitoring of factors other than the adjustment factor that cause the difference between the estimated and actual memory overhead, thereby avoiding ineffective adjustments to the adjustment factor and enabling monitoring and feedback of database anomalies.
[0049] like Figure 5 As shown, the general methods for managing the memory overhead of SQL statements include: Step S501: Obtain the actual memory overhead of each plan node of the SQL statement.
[0050] Step S502: Compare the estimated memory cost and the actual memory cost of each planned node.
[0051] Step S503: Detect whether the difference between the actual number of rows and the estimated number of rows for each planned node is greater than the preset row count threshold. If yes, proceed to step S504; otherwise, proceed to step S505.
[0052] Specifically, the preset row count threshold is configured by the database administrator. After the SQL statement is executed, the actual number of rows returned by each plan node can be obtained, which checks whether the actual number of rows differs too much from the estimated number of rows used when estimating the estimated memory overhead.
[0053] Step S504: Update optimizer statistics. If the difference between the actual number of rows and the estimated number of rows is too large, it indicates that the estimated number of rows used when estimating the estimated memory overhead was inaccurate, and the discrepancy between the estimated and actual memory overhead is not caused by inaccurate adjustment factors. Therefore, it is necessary to update the optimizer statistics to enable the optimizer to more accurately estimate the number of rows returned.
[0054] Step S505: Adjust the adjustment factor of the planning node based on the comparison results. If the difference between the actual number of rows and the estimated number of rows is not large, the adjustment factor may not be accurate enough, so adjust the adjustment factor of the planning node.
[0055] In this embodiment, before adjusting the adjustment factor of the planning node based on the comparison result, it is checked whether the difference between the actual number of rows and the estimated number of rows of each planning node is greater than a preset row count threshold. If so, the optimizer statistics are updated; otherwise, the step of adjusting the adjustment factor of the planning node based on the comparison result is executed. This ensures timely updates to the optimizer statistics, guaranteeing the accuracy of the estimated row count and thus ensuring the accuracy of memory overhead estimation.
[0056] like Figure 6As shown, in one embodiment, the method for managing the memory overhead of SQL statements generally includes: Step S601: Obtain the execution plan in JSON format. Specifically, generate the execution plan in JSON format using the EXPLAIN command.
[0057] Step S602: Initialize the memory decomposition dictionary. Specifically, initialize the memory corresponding to each type of node.
[0058] Step S603: Recursively traverse the execution plan tree. Specifically, this involves searching for each plan node in the execution plan, including sorting nodes, hash nodes, materialized nodes, and set nodes. For each node found, calculate the estimated memory cost for that node and add it to the memory cost value of the corresponding node in the memory decomposition dictionary and the total memory cost value. After traversing all plan nodes once, the total estimated memory cost can be obtained.
[0059] Step S604: Compare the evaluation results with the actual results. Specifically, compare the actual memory overhead after the SQL statement execution with the estimated memory overhead.
[0060] Step S605: Adjust the adjustment factor. Adjust the adjustment factor for each planning node based on the comparison results.
[0061] This embodiment also provides a computer device and a computer-readable storage medium. Figure 7 This is a schematic diagram of a computer device 10 according to an embodiment of the present invention. Figure 8 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention.
[0062] The computer device 10 may include a memory 110, a processor 120, and a computer-executable program 11 stored on the memory 110 and running on the processor 120. When the processor 120 executes the computer-executable program 11, it implements the memory overhead management method for SQL statements of any of the above embodiments.
[0063] The computer-readable storage medium 20 stores a computer-executable program 11 thereon, which, when executed by a processor, implements the memory overhead management method for SQL statements in any of the above embodiments.
[0064] This embodiment also provides a computer program product. Figure 9 This is a schematic diagram of a computer program product 30 according to an embodiment of the present invention. The computer program product 30 includes a computer executable program 11, which, when executed by a processor 120, implements the memory overhead management method for any of the SQL statements described above.
[0065] Specifically, the computer executable program 11 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, computer instructions, computer-related instructions, microcode, firmware instructions, status setting data, or source code or object code written in any combination of one or more programming languages.
[0066] For the purposes of this embodiment, the computer-readable storage medium 20 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection having one or more wires (electronic device), a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable storage medium 20 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.
[0067] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.
[0068] Computer device 10 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 10 can be a cloud acquisition node. Computer device 10 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 10 can be implemented in a distributed cloud acquisition environment where tasks are performed by remote processing devices linked via a communication network. In a distributed cloud acquisition environment, program modules can reside on local or remote acquisition system storage media, including storage devices.
[0069] Computer device 10 may include a processor 120 adapted to execute stored instructions and a memory 110 that provides temporary storage space for the operation of said instructions during operation. Processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any other configuration. Memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.
[0070] The processor 120 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 10 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 10 or may be external devices connected to the acquisition device.
[0071] The processor 120 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 10 to a display device. The display device may include a display screen that is a built-in component of the computer device 10. The display device may also include an external computer monitor, television, or projector connected to the computer device 10. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 10 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computer device via the network.
[0072] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.
Claims
1. A method for managing the memory overhead of SQL statements, comprising: Obtain the SQL statement and generate an execution plan for the SQL statement; Extract the plan nodes from the execution plan, as well as the number of rows and the row width corresponding to each plan node; The estimated memory overhead of the SQL statement is obtained based on the planned nodes and the corresponding number of rows and row width. Configure the execution memory required for the SQL statement based on the estimated memory overhead.
2. The method for managing the memory overhead of SQL statements according to claim 1, wherein... The step of obtaining the estimated memory overhead of the SQL statement based on the plan node and the corresponding number of rows and row width includes: The estimated memory overhead of each planned node is calculated using the memory estimation formula corresponding to each planned node; The estimated memory cost of the execution plan is obtained by summing the estimated memory costs corresponding to all the planned nodes. The memory estimation formula is as follows: node_mem = plan_rows * plan_width * factor; node_mem is the estimated memory overhead for the planned node; plan_rows is the number of rows; plan_width is the line width; factor is a preset adjustment factor corresponding to each planned node.
3. The method for managing the memory overhead of SQL statements according to claim 2, wherein... After the SQL statement is executed, obtain the actual memory overhead of each plan node of the SQL statement; Compare the estimated memory overhead and actual memory overhead for each of the planned nodes; Adjust the adjustment factor of the planned node based on the comparison results.
4. The method for managing the memory overhead of SQL statements according to claim 3, wherein... Prior to the step of adjusting the adjustment factor of the planned node based on the comparison result, the following are included: Detect whether the difference between the estimated memory overhead and the actual memory overhead is greater than a preset threshold. If yes, output an error message; otherwise, execute the step of adjusting the adjustment factor of the planning node based on the comparison result.
5. The method for managing the memory overhead of SQL statements according to claim 3, wherein... Prior to the step of adjusting the adjustment factor of the planned node based on the comparison result, the following are included: If the difference between the actual number of rows and the estimated number of rows for each planned node is greater than a preset row count threshold, the optimizer statistics are updated; otherwise, the step of adjusting the adjustment factor of the planned node based on the comparison result is executed.
6. The method for managing the memory overhead of SQL statements according to claim 1, wherein, The steps for generating the execution plan for the SQL statement include: Use the EXPLAIN command to generate an execution plan in JSON format.
7. The method for managing the memory overhead of SQL statements according to claim 1, wherein, The plan nodes include at least sorting nodes, hash nodes, materialization nodes, and set nodes.
8. A computer device comprising a memory, a processor, and a computer-executable program stored in the memory and running on the processor, wherein the processor, when executing the computer-executable program, implements a memory overhead management method for SQL statements according to any one of claims 1 to 7.
9. A computer-readable storage medium having a computer-executable program stored thereon, wherein the computer-executable program, when executed by a processor, implements the memory overhead management method for SQL statements according to any one of claims 1 to 7.
10. A computer program product comprising a computer executable program, wherein the computer executable program, when executed by a processor, implements the memory overhead management method for SQL statements according to any one of claims 1 to 7.