CN112506893A - SQL pre-execution optimization method, device, equipment and readable medium - Google Patents
SQL pre-execution optimization method, device, equipment and readable medium Download PDFInfo
- Publication number
- CN112506893A CN112506893A CN202011399064.5A CN202011399064A CN112506893A CN 112506893 A CN112506893 A CN 112506893A CN 202011399064 A CN202011399064 A CN 202011399064A CN 112506893 A CN112506893 A CN 112506893A
- Authority
- CN
- China
- Prior art keywords
- sql
- task
- resource
- execution
- consumption
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Withdrawn
Links
- 238000005457 optimization Methods 0.000 title claims abstract description 47
- 238000000034 method Methods 0.000 title claims abstract description 45
- 238000013468 resource allocation Methods 0.000 claims description 18
- 238000004458 analytical method Methods 0.000 claims description 15
- 238000004590 computer program Methods 0.000 claims description 9
- 230000006870 function Effects 0.000 claims description 9
- 238000012545 processing Methods 0.000 claims description 3
- 238000005265 energy consumption Methods 0.000 abstract description 2
- 230000008569 process Effects 0.000 description 12
- 238000010586 diagram Methods 0.000 description 8
- 230000003287 optical effect Effects 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 3
- 230000009471 action Effects 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 239000000835 fiber Substances 0.000 description 2
- 230000008014 freezing Effects 0.000 description 2
- 238000007710 freezing Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000014509 gene expression Effects 0.000 description 1
- 238000007429 general method Methods 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 238000004064 recycling Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/46—Multiprogramming arrangements
- G06F9/50—Allocation of resources, e.g. of the central processing unit [CPU]
- G06F9/5005—Allocation of resources, e.g. of the central processing unit [CPU] to service a request
- G06F9/5027—Allocation of resources, e.g. of the central processing unit [CPU] to service a request the resource being a machine, e.g. CPUs, Servers, Terminals
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method for optimizing SQL pre-execution, which comprises the following steps: acquiring host resources, and creating an SQL pre-execution space based on the host resources; pre-executing the SQL task in the SQL pre-execution space, and judging whether the SQL task is a resource high-consumption SQL task; if the SQL task is the resource high-consumption SQL task, a database command is called to freeze the resource high-consumption SQL task and release resources occupied by the resource high-consumption SQL task; and optimizing the SQL task with high resource consumption, returning the optimized SQL task to the SQL pre-execution space for pre-execution and judging whether the SQL task with high resource consumption is performed. The invention also discloses a device, computer equipment and a readable storage medium for SQL pre-execution optimization. The invention allocates resources to the SQL slave host computer to be executed for SQL pre-execution, thereby determining the SQL with high energy consumption, further optimizing the SQL, ensuring that other SQL executions of the database are not influenced, and optimizing the SQL on the premise of ensuring the normal operation of the database.
Description
Technical Field
The present invention relates to the field of resource allocation technologies, and in particular, to a method, an apparatus, a device, and a readable medium for SQL pre-execution optimization.
Background
When the database SQL is executed, if the resources consumed during the execution of the SQL are too high, not only the execution efficiency of the SQL is reduced, but also the overall performance of the database is affected, so that the resources of the entire database are consumed too high, and thus other executing SQL cannot obtain more resources, which results in slower execution of the SQL, low efficiency, and slow overall performance of the database.
In the process of optimizing SQL, a general method in the prior art optimizes SQL by using resources of a database itself, so that the database resources are occupied in the process of optimizing SQL, and other normally executed SQL is affected.
Disclosure of Invention
In view of this, embodiments of the present invention provide a method, an apparatus, a device, and a readable medium for SQL pre-execution optimization, in which resources are allocated to an SQL slave host to be executed before SQL execution to perform SQL pre-execution, and a threshold of resource consumption is determined in the pre-execution process, so as to determine a high-energy-consumption SQL, further optimize the SQL, provide an optimization suggestion, provide help for subsequent SQL execution, and timely release allocated space after optimization is completed.
Based on the above object, an aspect of the embodiments of the present invention provides a method for optimizing SQL pre-execution, including the following steps: acquiring host resources, and creating an SQL pre-execution space based on the host resources; pre-executing the SQL task in the SQL pre-execution space, and judging whether the SQL task is a resource high-consumption SQL task; if the SQL task is the resource high-consumption SQL task, a database command is called to freeze the resource high-consumption SQL task and release resources occupied by the resource high-consumption SQL task; and optimizing the SQL task with high resource consumption, and returning the optimized SQL task to be pre-executed in the SQL pre-execution space and judging whether the SQL task with high resource consumption is performed.
In some embodiments, further comprising: if the SQL task is not the resource high-consumption SQL task, the optimization is finished, and the resources occupied by the SQL task are released.
In some embodiments, obtaining a host resource and creating an SQL pre-execution space based on the host resource comprises: judging whether the host resources have extra resources according to preset resource allocation parameters; if the host resources have extra resources, the host resources are divided into a database global area and a program sharing area, and an SQL pre-execution space is created.
In some embodiments, further comprising: and if the host computer resources have no additional resources, sending out a resource allocation failure alarm prompt and terminating the task.
In some embodiments, determining whether the SQL task is a resource-high-consumption SQL task comprises: and calculating the resource consumption value of the SQL task in real time, judging whether the resource consumption value exceeds a threshold value, and if the resource consumption value exceeds the threshold value, determining that the SQL task is the SQL task with high resource consumption.
In some embodiments, further comprising: if the SQL pre-execution space resource can not satisfy the SQL task, expanding the resource for the SQL task based on the consumed resource value.
In some embodiments, optimizing the resource-high consumption SQL task comprises: analyzing the SQL task, and obtaining an optimal execution plan with the lowest cost value based on an empirical analysis value of the SQL task and an automatic database analysis function; and optimizing the SQL task based on the optimal execution plan.
In another aspect of the embodiments of the present invention, a device for SQL pre-execution optimization is further provided, including: the resource allocation module is configured to acquire host resources and create an SQL pre-execution space based on the host resources; the system comprises a pre-execution module, a query language (SQL) pre-execution module and a query language (SQL) pre-execution module, wherein the pre-execution module is configured to pre-execute an SQL task in an SQL pre-execution space and judge whether the SQL task is a resource high-consumption SQL task; the processing module is configured to call a database command to freeze the SQL task with high resource consumption and release resources occupied by the SQL task with high resource consumption if the SQL task is the SQL task with high resource consumption; and the optimization module is configured to optimize the resource high-consumption SQL task and return the optimized SQL task to the pre-execution module.
In another aspect of the embodiments of the present invention, there is also provided a computer device, including: at least one processor; and a memory storing computer instructions executable on the processor, the instructions when executed by the processor implementing the steps of the method.
In a further aspect of the embodiments of the present invention, a computer-readable storage medium is also provided, in which a computer program for implementing the above method steps is stored when the computer program is executed by a processor.
The invention has the following beneficial technical effects: the SQL is distributed from the host to be executed to the resources to be executed to perform SQL pre-execution before the SQL is executed, the threshold value of resource consumption is judged in the pre-execution process, so that the high-energy SQL is determined, the SQL is further optimized, an optimization suggestion is given, help is provided for subsequent SQL execution, the distributed space is released in time after the optimization is completed, other SQL executions of the database in the whole process cannot be influenced, and the SQL optimization is performed on the premise of ensuring the normal operation of the database.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other embodiments can be obtained by using the drawings without creative efforts.
FIG. 1 is a schematic diagram of an embodiment of a method for SQL pre-execution optimization provided by the present invention;
FIG. 2 is a schematic diagram of an embodiment of an apparatus for SQL pre-execution optimization provided by the present invention;
FIG. 3 is a schematic diagram of an embodiment of a computer device provided by the present invention;
FIG. 4 is a schematic diagram of an embodiment of a computer-readable storage medium provided by the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the following embodiments of the present invention are described in further detail with reference to the accompanying drawings.
It should be noted that all expressions using "first" and "second" in the embodiments of the present invention are used for distinguishing two entities with the same name but different names or different parameters, and it should be noted that "first" and "second" are merely for convenience of description and should not be construed as limitations of the embodiments of the present invention, and they are not described in any more detail in the following embodiments.
Based on the above purpose, the first aspect of the embodiments of the present invention provides an embodiment of a method for SQL pre-execution optimization. Fig. 1 is a schematic diagram illustrating an embodiment of a method for SQL pre-execution optimization according to the present invention. As shown in fig. 1, the embodiment of the present invention includes the following steps performed at the maintenance device side:
s01, acquiring host resources, and creating an SQL pre-execution space based on the host resources;
s02, pre-executing the SQL task in the SQL pre-execution space, and judging whether the SQL task is a resource high-consumption SQL task;
s03, if the SQL task is the resource high-consumption SQL task, calling a database command to freeze the resource high-consumption SQL task and release the resources occupied by the resource high-consumption SQL task; and
and S04, optimizing the SQL task with high resource consumption, and returning the optimized SQL task to the steps of pre-executing in the SQL pre-execution space and judging whether the SQL task with high resource consumption is performed.
In the embodiment, before SQL execution, resources are allocated to the SQL slave host to be executed to perform SQL pre-execution, and a threshold value of resource consumption is judged in the pre-execution process, so that high-energy SQL is determined, the SQL is further optimized, an optimization suggestion is given, help is provided for subsequent SQL execution, the allocated space is released in time after optimization is completed, other SQL executions of the database in the whole process cannot be influenced, and SQL optimization is performed on the premise of ensuring normal operation of the database.
In this embodiment, the system includes a resource allocation device, an SQL pre-execution device, and a resource recycling device. The SQL pre-execution device comprises a resource consumption analysis device, an SQL stop device, an SQL freezing device and an SQL optimization device. The resource allocation device is used for pre-allocating memory, CPU resources and disk space from the database host to perform pre-execution on the SQL; the SQL pre-execution device is used for setting an SQL execution space in the pre-allocated resources to prepare for SQL execution; the resource consumption analysis device judges whether the SQL resource consumption is too high according to a threshold value. The high-consumption SQL session ID output by the resource consumption analysis device will be used as an input of the SQL stopping device. And after the SQL stopping device receives the SQL ID, finding out the corresponding SQL statement and all corresponding related execution plans through the system view. Invoking the database kill command kills the session so that the resources held by the SQL are freed. And meanwhile, all the execution plans related to the SQL and the related SQL in the SQL optimizer are completely frozen, so that the SQL can not be executed any more.
In some embodiments of the invention, further comprising: if the SQL task is not the resource high-consumption SQL task, the optimization is finished, and the resources occupied by the SQL task are released.
In this embodiment, if the allocated resources are available for SQL execution, the resource consumption analysis device continuously scans the SQL, calculates the consumed resource values of the CPU, the memory, and the disk IO of the SQL in real time, and determines whether the SQL is the resource high consumption SQL by comparing the comprehensive values with a preset threshold. If the threshold value is not exceeded in the whole SQL execution process, the optimization is finished, and the allocated resources are released.
In some embodiments of the present invention, obtaining the host resource and creating the SQL pre-execution space based on the host resource comprises: judging whether the host resources have extra resources according to preset resource allocation parameters; if the host resources have extra resources, the host resources are divided into a database global area and a program sharing area, and an SQL pre-execution space is created.
In this embodiment, whether additional resources exist in the host resources is determined according to preset resource allocation parameters, where the host resources include a CPU, a memory, and a storage space, and if additional resources exist, the additional resources are allocated, resources such as the CPU, the memory, and the storage space are allocated to a database space by a resource allocation device, the memory is allocated to a database global area and a program shared area, a new tablespace is created separately for the storage space, and the CPU resources are bound to SQL. And expanding the total resources of the database, wherein the expanded resources are used as temporary resources and released after SQL optimization is finished.
In some embodiments of the invention, further comprising: and if the host computer resources have no additional resources, sending out a resource allocation failure alarm prompt and terminating the task.
In this embodiment, whether additional resources exist in the host resources are determined according to preset resource allocation parameters, where the host resources include a CPU, a memory, and a storage space, and if the host does not have resources, the resource allocation device sends an alarm to prompt, the resource allocation action fails, and the subsequent task is terminated.
In some embodiments of the present invention, determining whether the SQL task is a resource-high-consumption SQL task comprises: and calculating the resource consumption value of the SQL task in real time, judging whether the resource consumption value exceeds a threshold value, and if the resource consumption value exceeds the threshold value, determining that the SQL task is the SQL task with high resource consumption.
In this embodiment, if the allocated resources are available for SQL execution, the resource consumption analysis device continuously scans the SQL, calculates the consumed resource values of the CPU, the memory, and the disk IO of the SQL in real time, and determines whether the SQL is the resource high consumption SQL by comparing the comprehensive values with a preset threshold. Once the computed value exceeds the threshold, the SQL is defined as a resource high consumption SQL.
In some embodiments of the invention, further comprising: if the SQL pre-execution space resource can not satisfy the SQL task, expanding the resource for the SQL task based on the consumed resource value.
In this embodiment, if the allocated resources cannot meet the resource requirements of SQL execution and the SQL execution fails, the resource allocation device reads the actually consumed resource value in the SQL execution process, re-allocates resources for SQL, and expands the resources such as the remaining CPU, memory, and storage space in the host to the database according to the actual requirements.
In some embodiments of the invention, optimizing the resource-intensive SQL task comprises: analyzing the SQL task, and obtaining an optimal execution plan with the lowest cost value based on an empirical analysis value of the SQL task and an automatic database analysis function; and optimizing the SQL task based on the optimal execution plan.
In this embodiment, a plurality of SQL execution plan cost values are collected based on empirical analysis values and the automated analysis function of the database itself. The empirical analysis value is an artificially preset empirical value and is based on SQL complexity, query data volume, query result data volume and IO execution plan cost value. Ranking is carried out through the cost values, if the cost value is found to be the lowest, the optimal execution plan is found, if the optimal execution plan is the SQL freezing execution plan, the optimization is failed, the SQL optimization device sends out an alarm prompt, manual intervention analysis is needed, whether the SQL has the problem of logic or data is carried out, and meanwhile, the allocated resources are released. If the cost value of the SQL optimal execution plan is lower than the previous SQL frozen execution plan, outputting the optimal proposal method with the most execution plan, stopping all processes related to optimization by the resource release device when the automatic optimization work is finished, recovering the resources temporarily allocated to the database, releasing the memory from the database global area and the program sharing area, deleting the temporarily created table space, and releasing the CPU resources.
In this embodiment, after obtaining the optimization suggestion, the user may further analyze the SQL, and by manual experience, if the optimization suggestion of the SQL is feasible, execute the relevant optimization suggestion work, and perform SQL optimization.
It should be particularly noted that, the steps in the embodiments of the SQL pre-execution optimization method described above may be mutually intersected, replaced, added, and deleted, so that these methods of SQL pre-execution optimization based on reasonable permutation and combination transformation also belong to the scope of the present invention, and should not limit the scope of the present invention to the embodiments.
In view of the foregoing, a second aspect of the embodiments of the present invention provides an apparatus for SQL pre-execution optimization. Fig. 2 is a schematic diagram illustrating an embodiment of an apparatus for SQL pre-execution optimization provided in the present invention. As shown in fig. 2, the embodiment of the present invention includes the following modules: the resource allocation module S11 is configured to acquire host resources and create an SQL pre-execution space based on the host resources; the pre-execution module S12 is configured to pre-execute the SQL task in the SQL pre-execution space and judge whether the SQL task is a resource high-consumption SQL task; the processing module S13 is configured to, if the SQL task is a resource high-consumption SQL task, call a database command to freeze the resource high-consumption SQL task and release resources occupied by the resource high-consumption SQL task; and an optimization module S14 configured to optimize the resource high-consumption SQL task and return the optimized SQL task to the pre-execution module.
In view of the above object, a third aspect of the embodiments of the present invention provides a computer device. Fig. 3 is a schematic diagram of an embodiment of a computer device provided by the present invention. As shown in fig. 3, an embodiment of the present invention includes the following means: at least one processor S21; and a memory S22, the memory S22 storing computer instructions S23 executable on the processor, the instructions when executed by the processor implementing the steps of the above method.
The invention also provides a computer readable storage medium. FIG. 4 is a schematic diagram illustrating an embodiment of a computer-readable storage medium provided by the present invention. As shown in fig. 4, the computer readable storage medium stores S31 a computer program that, when executed by a processor, performs the method as described above S32.
Finally, it should be noted that, as one of ordinary skill in the art can appreciate that all or part of the processes of the methods of the above embodiments can be implemented by a computer program to instruct related hardware, and the program of the method for SQL pre-execution optimization can be stored in a computer readable storage medium, and when executed, the program can include the processes of the embodiments of the methods as described above. The storage medium of the program may be a magnetic disk, an optical disk, a Read Only Memory (ROM), a Random Access Memory (RAM), or the like. The embodiments of the computer program may achieve the same or similar effects as any of the above-described method embodiments.
Furthermore, the methods disclosed according to embodiments of the present invention may also be implemented as a computer program executed by a processor, which may be stored in a computer-readable storage medium. Which when executed by a processor performs the above-described functions defined in the methods disclosed in embodiments of the invention.
Further, the above method steps and system elements may also be implemented using a controller and a computer readable storage medium for storing a computer program for causing the controller to implement the functions of the above steps or elements.
Those of skill would further appreciate that the various illustrative logical blocks, modules, circuits, and algorithm steps described in connection with the disclosure herein may be implemented as electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software, various illustrative components, blocks, modules, circuits, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as software or hardware depends upon the particular application and design constraints imposed on the overall system. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the disclosed embodiments of the present invention.
In one or more exemplary designs, the functions may be implemented in hardware, software, firmware, or any combination thereof. If implemented in software, the functions may be stored on or transmitted over as one or more instructions or code on a computer-readable medium. Computer-readable media includes both computer storage media and communication media including any medium that facilitates transfer of a computer program from one place to another. A storage media may be any available media that can be accessed by a general purpose or special purpose computer. By way of example, and not limitation, such computer-readable media can comprise RAM, ROM, EEPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a general-purpose or special-purpose computer, or a general-purpose or special-purpose processor. Also, any connection is properly termed a computer-readable medium. For example, if the software is transmitted from a website, server, or other remote source using a coaxial cable, fiber optic cable, twisted pair, 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 medium. Disk and disc, as used herein, includes Compact Disc (CD), laser disc, optical disc, Digital Versatile Disc (DVD), floppy disk, blu-ray disc where disks usually reproduce data magnetically, while discs reproduce data optically with lasers. Combinations of the above should also be included within the scope of computer-readable media.
The foregoing is an exemplary embodiment of the present disclosure, but it should be noted that various changes and modifications could be made herein without departing from the scope of the present disclosure as defined by the appended claims. The functions, steps and/or actions of the method claims in accordance with the disclosed embodiments described herein need not be performed in any particular order. Furthermore, although elements of the disclosed embodiments of the invention may be described or claimed in the singular, the plural is contemplated unless limitation to the singular is explicitly stated.
It should be understood that, as used herein, the singular forms "a", "an" and "the" are intended to include the plural forms as well, unless the context clearly supports the exception. It should also be understood that "and/or" as used herein is meant to include any and all possible combinations of one or more of the associated listed items.
The numbers of the embodiments disclosed in the embodiments of the present invention are merely for description, and do not represent the merits of the embodiments.
It will be understood by those skilled in the art that all or part of the steps for implementing the above embodiments may be implemented by hardware, or may be implemented by a program instructing relevant hardware, and the program may be stored in a computer-readable storage medium, and the above-mentioned storage medium may be a read-only memory, a magnetic disk or an optical disk, etc.
Those of ordinary skill in the art will understand that: the discussion of any embodiment above is meant to be exemplary only, and is not intended to intimate that the scope of the disclosure, including the claims, of embodiments of the invention is limited to these examples; within the idea of an embodiment of the invention, also technical features in the above embodiment or in different embodiments may be combined and there are many other variations of the different aspects of the embodiments of the invention as described above, which are not provided in detail for the sake of brevity. Therefore, any omissions, modifications, substitutions, improvements, and the like that may be made without departing from the spirit and principles of the embodiments of the present invention are intended to be included within the scope of the embodiments of the present invention.
Claims (10)
1. A method for optimizing SQL pre-execution is characterized by comprising the following steps:
acquiring host resources, and creating an SQL pre-execution space based on the host resources;
pre-executing the SQL task in the SQL pre-execution space, and judging whether the SQL task is a resource high-consumption SQL task;
if the SQL task is the resource high-consumption SQL task, a database command is called to freeze the resource high-consumption SQL task and release resources occupied by the resource high-consumption SQL task; and
and optimizing the resource high-consumption SQL task, and returning the optimized SQL task to the step of pre-executing in the SQL pre-execution space and judging whether the SQL task is the resource high-consumption SQL task.
2. The method of SQL pre-execution optimization of claim 1, further comprising:
if the SQL task is not the resource high-consumption SQL task, finishing the optimization and releasing the resources occupied by the SQL task.
3. The method of SQL pre-execution optimization of claim 1, wherein obtaining a host resource and creating an SQL pre-execution space based on the host resource comprises:
judging whether the host resources have extra resources according to preset resource allocation parameters;
if the host resources have extra resources, the host resources are divided into a database global area and a program sharing area, and an SQL pre-execution space is created.
4. The method of SQL pre-execution optimization of claim 3, further comprising:
and if the host computer resources have no additional resources, sending out a resource allocation failure alarm prompt and terminating the task.
5. The method of SQL pre-execution optimization of claim 1, wherein determining whether the SQL task is a resource-intensive SQL task comprises:
and calculating the resource consumption value of the SQL task in real time, judging whether the resource consumption value exceeds a threshold value, and if the resource consumption value exceeds the threshold value, determining that the SQL task is a resource high-consumption SQL task.
6. The method of SQL pre-execution optimization of claim 1, further comprising:
and if the SQL pre-execution space resource cannot meet the SQL task, expanding the resource for the SQL task based on the consumed resource value.
7. The method of SQL pre-execution optimization of claim 1, wherein optimizing the resource-intensive SQL task comprises:
analyzing the SQL task, and obtaining an optimal execution plan with the lowest cost value based on an empirical analysis value of the SQL task and an automatic database analysis function;
and optimizing the SQL task based on the optimal execution plan.
8. An apparatus for SQL pre-execution optimization, comprising:
the resource allocation module is configured to acquire host resources and create an SQL pre-execution space based on the host resources;
the pre-execution module is configured to pre-execute the SQL task in the SQL pre-execution space and judge whether the SQL task is a resource high-consumption SQL task;
the processing module is configured to call a database command to freeze the resource high-consumption SQL task and release the resource occupied by the resource high-consumption SQL task if the SQL task is the resource high-consumption SQL task; and
and the optimization module is configured to optimize the resource high-consumption SQL task and return the optimized SQL task to the pre-execution module.
9. A computer device, comprising:
at least one processor; and
a memory storing computer instructions executable on the processor, the instructions when executed by the processor implementing the steps of any of the methods 1-7.
10. A computer-readable storage medium, in which a computer program is stored which, when being executed by a processor, carries out the steps of the method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011399064.5A CN112506893A (en) | 2020-12-04 | 2020-12-04 | SQL pre-execution optimization method, device, equipment and readable medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011399064.5A CN112506893A (en) | 2020-12-04 | 2020-12-04 | SQL pre-execution optimization method, device, equipment and readable medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112506893A true CN112506893A (en) | 2021-03-16 |
Family
ID=74969728
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011399064.5A Withdrawn CN112506893A (en) | 2020-12-04 | 2020-12-04 | SQL pre-execution optimization method, device, equipment and readable medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112506893A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113032277A (en) * | 2021-04-16 | 2021-06-25 | 携程旅游网络技术（上海）有限公司 | Data query monitoring method and device, electronic equipment and storage medium |
-
2020
- 2020-12-04 CN CN202011399064.5A patent/CN112506893A/en not_active Withdrawn
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113032277A (en) * | 2021-04-16 | 2021-06-25 | 携程旅游网络技术（上海）有限公司 | Data query monitoring method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103473142B (en) | Virtual machine migration method under a kind of cloud computing operating system and device | |
| US20180046675A1 (en) | Automatic adjustment of an execution plan for a query | |
| CN105302657A (en) | Abnormal condition analysis method and apparatus | |
| CN111581234A (en) | RAC multi-node database query method, device and system | |
| CN110599166A (en) | Method and device for acquiring transaction dependency relationship in block chain | |
| CN112506893A (en) | SQL pre-execution optimization method, device, equipment and readable medium | |
| CN113254450A (en) | Method and system for storing account state of incremental MPT (message passing through) tree based on block chain | |
| CN109063040B (en) | Client program data acquisition method and system | |
| CN112463069B (en) | Method, device and equipment for recovering storage garbage and readable medium | |
| CN111752961A (en) | Data processing method and device | |
| CN114238390A (en) | Data warehouse optimization method, device, equipment and storage medium | |
| CN110059107A (en) | Plan optimization method, device, system and computer readable storage medium | |
| CN112181951B (en) | Heterogeneous database data migration method, device and equipment | |
| CN113961586A (en) | Control method and device for SQL (structured query language) statements | |
| CN111538491B (en) | Data event processing method, device, equipment and storage medium | |
| CN111680036B (en) | Configuration management database based on graph storage | |
| CN111026748B (en) | Data compression method, device and system for network access frequency management and control | |
| CN110780993A (en) | Kubernetes-based resource scheduling optimization method, equipment and medium | |
| CN112037017A (en) | Method, device and equipment for determining batch processing job evaluation result | |
| CN102902825B (en) | A kind of database optimizing method and device | |
| CN110795220B (en) | Task merging method and device and computer readable storage medium | |
| CN106484725B (en) | A kind of data processing method, device and system | |
| CN111209295B (en) | Optimization method of computation flow graph, database access method and device | |
| CN116757450B (en) | Method, device, equipment and medium for task allocation of sharing center | |
| CN112732715B (en) | Data table association method, device and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WW01 | Invention patent application withdrawn after publication | ||
| WW01 | Invention patent application withdrawn after publication |
Application publication date: 20210316 |