A method, apparatus, device, and storage medium for optimizing structured query statements.

Abstract

This invention discloses a method, apparatus, device, and storage medium for optimizing structured query statements. The method includes: acquiring the SQL statement to be optimized; determining the conditional expression to be optimized that contains binding parameters to be optimized within the SQL statement; when the conditional expression satisfies the target optimization condition, determining the expression optimization result of the conditional expression; if there is no alternative plan cache record in the plan cache that matches the SQL statement to be optimized and the expression optimization result, then determining the target execution plan for the SQL statement to be optimized based on the SQL statement to be optimized and the expression optimization result, and forming a corresponding plan cache record and registering it in the plan cache. This invention selects a suitable execution plan from the plan cache based on the actual value of the binding parameters. If no matching execution plan is found, the optimal execution plan for the query statement is generated, and the optimization record is registered in the plan cache. This can improve the execution efficiency and accuracy of the query statement during plan reuse.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database processing, and in particular, to a structured query statement optimization method, device, equipment and storage medium. BACKGROUND

[0002] For a structured query language (SQL) input by a user, a database management system first performs syntax and semantic analysis on the SQL, and then generates an execution plan according to the analysis result and statistical information of a table involved in the SQL, and finally executes the generated execution plan to obtain a result. When generating the execution plan, the number of data rows remaining after filtering is calculated according to a filtering condition in the SQL, and the optimal execution plan is generated according to the number of rows.

[0003] In the SQL, a user is allowed to replace a parameter in the SQL with "?" and then bind a specific parameter value when actually executing the SQL. Herein, the "?" is referred to as a binding parameter.

[0004] For a SQL query statement containing a binding parameter, when generating an execution plan, the actual value of the binding parameter is ignored regardless of whether the actual value has been specified, and the binding parameter is regarded as a variable to estimate the number of rows after filtering. The estimation result may have a relatively large error, which further leads to a suboptimal execution plan.

[0005] For a case where the actual value of the binding parameter has been specified, if the actual value can be used to estimate the number of rows, the estimation result is more accurate, and a more optimal execution plan can be generated according to the estimation result. At this time, another problem exists, that is, many databases have a plan cache mechanism, that is, after an execution plan is generated for a SQL statement, the SQL statement and the corresponding execution plan are stored in the plan cache, and when the same SQL statement is executed next time, the execution plan in the plan cache is directly used instead of generating another execution plan. At this time, if the execution plan in the plan cache is generated when the actual value of the binding parameter is A, when the same SQL statement is executed next time and the actual value of the binding parameter is B, the server still directly uses the execution plan in the plan cache, and the execution plan is not necessarily suitable for the current parameter actual value. SUMMARY

[0006] The present application provides a structured query statement optimization method, device, equipment and storage medium to accurately generate an optimal statement execution plan and improve the execution efficiency of a query statement.

[0007] According to an aspect of the present application, a structured query statement optimization method is provided, which includes:

[0008] obtain a to-be-optimized SQL statement, and determine a to-be-optimized condition expression containing a to-be-optimized bound parameter in the to-be-optimized SQL statement;

[0009] determine an expression optimization result of the to-be-optimized condition expression when the to-be-optimized condition expression meets a target optimization condition;

[0010] If there is no alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, determine a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result, and form a corresponding plan cache record and register the plan cache record in the plan cache.

[0011] According to another aspect of the present application, there is provided an optimization device for a structured query statement, the device comprising:

[0012] an expression extraction module configured to obtain a to-be-optimized SQL statement, and determine a to-be-optimized condition expression containing a to-be-optimized bound parameter in the to-be-optimized SQL statement;

[0013] an expression optimization module configured to determine an expression optimization result of the to-be-optimized condition expression when the to-be-optimized condition expression meets a target optimization condition;

[0014] a first statement optimization module configured to, if there is no alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, determine a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result, and form a corresponding plan cache record and register the plan cache record in the plan cache.

[0015] According to another aspect of the present application, there is provided an electronic device, the electronic device comprising:

[0016] at least one processor; and

[0017] a memory connected to the at least one processor in communication; wherein

[0018] the memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the optimization method for a structured query statement according to any one of the embodiments of the present application.

[0019] According to another aspect of the present application, there is provided a computer readable storage medium storing computer instructions for enabling a processor to execute the optimization method for a structured query statement according to any one of the embodiments of the present application.

[0020] The technical scheme of the embodiment of the present application comprises the following steps: obtaining a to-be-optimized SQL statement, determining a to-be-optimized condition expression containing a to-be-optimized binding parameter in the to-be-optimized SQL statement; when the to-be-optimized condition expression meets a target optimization condition, determining an expression optimization result of the to-be-optimized condition expression; if there is no alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, determining a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result, and forming a corresponding plan cache record and registering the plan cache record in the plan cache; the present application selects a suitable execution plan in the plan cache according to the actual value of the binding parameter, and if there is no matching execution plan, generates an optimal execution plan of the query statement and registers the optimization record in the plan cache; when the plan is reused, the statement execution efficiency and accuracy can be improved.

[0021] It should be understood that the content described in this part is not intended to identify the key or important features of the embodiments of the present application, nor is it used to limit the scope of the present application. Other features of the present application will become apparent from the following description. BRIEF DESCRIPTION OF DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0023] Figure 1 is a flow chart of a structured query statement optimization method according to the first embodiment of the present application;

[0024] Figure 2 is a structural schematic diagram of a structured query statement optimization device according to the second embodiment of the present application;

[0025] Figure 3 is a structural schematic diagram of an electronic device implementing the structured query statement optimization method of the present application. DETAILED DESCRIPTION

[0026] In order to enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor should be within the scope of protection of the present application.

[0027] It should be noted that the terms "first", "second", "third", "target", and the like in the description, claims, and drawings of the application, and the above-mentioned are used to distinguish similar objects, and do not necessarily have to be used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged under appropriate circumstances, so that the embodiments of the application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device including a series of steps or units does not have to be limited to those steps or units clearly listed, but can include other steps or units not clearly listed or inherent to these processes, methods, products or devices.

[0028] Embodiment one

[0029] Figure 1 A flowchart of an optimization method for a structured query statement is provided for the first embodiment of the application. This embodiment can be applied to the optimization of a structured query statement containing a bound parameter. The method can be performed by a structured query statement optimization device, which can be implemented in the form of hardware and / or software, and can be configured in a computer device. As shown in Figure 1 The method comprises:

[0030] S110, obtaining a SQL statement to be optimized, and determining a condition expression to be optimized containing a bound parameter to be optimized in the SQL statement to be optimized.

[0031] Generally, for the structured query language SQL input by the user, the database management system will first perform syntax and semantic analysis, and then generate an execution plan according to the analysis results and the statistical information of the tables involved in the SQL statement, and finally execute the generated execution plan to obtain the result. In generating the execution plan, the number of data rows remaining after filtering needs to be calculated according to the filtering conditions in the SQL statement, and the optimal execution plan is generated according to the number of rows. In the SQL statement, the user is allowed to replace the parameters in the statement with "?", and the specific parameter values are bound when the actual execution is performed. Here, we call "?" a bound parameter.

[0032] In this embodiment, the SQL statement containing the bound parameter can be used as the SQL statement to be optimized. When a SQL statement to be optimized is obtained, the bound parameter contained therein can be referred to as a bound parameter to be optimized, and the filtering condition expression in which the bound parameter to be optimized is located can be extracted and referred to as a condition expression to be optimized. The structure and parameters of the condition expression to be optimized are analyzed to determine whether the SQL statement to be optimized can be optimized.

[0033] S120, when the to-be-optimized condition expression meets the target optimization condition, determining an expression optimization result of the to-be-optimized condition expression.

[0034] Optionally, the target optimization condition can include that the to-be-optimized condition expression is a comparison expression of a to-be-optimized binding parameter and a constant expression, and a specified actual value of the to-be-optimized binding parameter is a constant.

[0035] In the embodiment, when the to-be-optimized condition expression is a comparison expression of a to-be-optimized binding parameter and a constant expression, and the to-be-optimized binding parameter has a specified actual value which is a constant, it can be considered that the to-be-optimized condition expression meets the target optimization condition, and the expression optimization result of the to-be-optimized condition expression can be calculated. In the determination of the expression optimization result, the specified actual value of the to-be-optimized binding parameter can be compared with the corresponding constant, and the condition expression can be replaced by TRUE or FALSE according to the comparison result, so as to simplify the filtering condition and make the row number estimation more accurate.

[0036] Optionally, the method for optimizing the structured query statement provided in the embodiment can further include the following steps: when the to-be-optimized condition expression does not meet the target optimization condition, determining a target execution plan according to the to-be-optimized SQL statement, and forming a corresponding plan cache record based on the to-be-optimized SQL statement and the corresponding target execution plan and registering the plan cache record in the plan cache.

[0037] In the embodiment, when the to-be-optimized condition expression does not meet the target optimization condition, it can be considered that the to-be-optimized SQL statement does not meet the optimization condition, and then a record in all plan cache records in the plan cache can be searched, in which the SQL statement itself is the same as the to-be-optimized SQL statement, and then it can be judged whether the specified actual values of the binding parameters in the above record are consistent with the specified actual values of the binding parameters in the to-be-optimized SQL statement. Only when the SQL statement itself and the specified actual values of the binding parameters are consistent, it can be considered that the matching is successful, and the execution plan in the matched plan cache record can be used as the target execution plan of the to-be-optimized SQL statement. When there is no plan cache record in the cache plan which is completely matched with the to-be-optimized SQL statement, a target execution plan of the to-be-optimized SQL statement can be determined according to the to-be-optimized SQL statement by using a conventional method, and the to-be-optimized SQL statement itself and the corresponding target execution plan can be registered in the plan cache.

[0038] Optionally, the plan cache record can include the SQL statement, the execution plan and the statement optimization information.

[0039] Further, the statement optimization information can include the binding parameter number, the specified actual value of the binding parameter and the expression optimization result.

[0040] In the embodiment, the plan cache stores the plan cache record corresponding to the executed SQL statement, which can include the SQL statement itself, the execution plan, and the statement optimization information. The statement optimization information can be considered as the information recording the filter condition expression containing the binding parameter. The statement optimization information can include the binding parameter number, the binding parameter specified actual value, and the expression optimization result. It should be noted that there can be multiple binding parameters in a SQL statement. To distinguish the binding parameters, the binding parameters can be numbered in sequence, for example, 1, 2, …, n. The number is the binding parameter number.

[0041] When the to-be-optimized condition expression containing the to-be-optimized binding parameter in the to-be-optimized SQL statement does not satisfy the target optimization condition, the statement optimization information formed can only include the binding parameter number, the binding parameter specified actual value, and the expression optimization result is empty, or the statement optimization information formed can only include the binding parameter number and the binding parameter specified actual value, and the expression optimization result is empty.

[0042] In actual application, when a to-be-optimized SQL statement contains multiple to-be-optimized condition expressions, it can be determined whether each to-be-optimized condition expression satisfies the target optimization condition. When at least one to-be-optimized condition expression satisfies the target optimization condition, it is considered that the to-be-optimized SQL statement can be optimized. At this time, the expression optimization result of the to-be-optimized condition expression that can be optimized is determined, and the step S130 is continued. When all to-be-optimized condition expressions do not satisfy the target optimization condition, it is considered that the to-be-optimized SQL statement does not satisfy the optimization condition. At this time, the optimization process can be exited, the target execution plan of the to-be-optimized SQL statement is determined according to the to-be-optimized SQL statement by using a conventional method, and the corresponding plan cache record is formed based on the to-be-optimized SQL statement itself and the target execution plan and is registered in the plan cache.

[0043] S130, if the plan cache does not exist the alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result, the target execution plan of the to-be-optimized SQL statement is determined according to the to-be-optimized SQL statement and the expression optimization result, and the corresponding plan cache record is formed and registered in the plan cache.

[0044] Optionally, the structured query statement optimization method provided in the embodiment can further include the following step: if the plan cache exists the alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result, the alternative execution plan in the alternative plan cache record is determined as the target execution plan of the to-be-optimized SQL statement.

[0045] In actual application, when the to-be-optimized SQL statement contains a to-be-optimized condition expression, and the to-be-optimized condition expression meets the target optimization condition, the same record as the to-be-optimized SQL statement can be found in all the plan cache records in the plan cache, and then it is found in the statement optimization information in the above record whether there is statement optimization information matching the expression optimization result. Specifically, when the to-be-optimized condition expression in the to-be-optimized SQL statement and the expression optimization result are consistent with the related information in a certain plan cache record, the plan cache record is a candidate plan cache record matching the to-be-optimized SQL statement and the expression optimization result. At this time, it is considered that the SQL statement in the candidate plan cache record can use the same execution plan when executing the to-be-optimized SQL statement, and therefore the candidate execution plan in the candidate plan cache record is determined as the target execution plan of the to-be-optimized SQL statement. When there is no candidate plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, the target execution plan of the to-be-optimized SQL statement needs to be regenerated according to the to-be-optimized SQL statement and the expression optimization result. At this time, the optimization scheme of the to-be-optimized SQL statement does not exist in the plan cache, and therefore a corresponding plan cache record can be formed and registered in the plan cache.

[0046] When a plurality of to-be-optimized condition expressions are contained in a to-be-optimized SQL statement, and at least one to-be-optimized condition expression meets the target optimization condition, a record in the plan cache that has the same SQL statement as the to-be-optimized SQL statement can be searched in all plan cache records in the plan cache, and then it can be searched in the statement optimization information in the record whether there is statement optimization information that matches the expression optimization result. Specifically, when the to-be-optimized condition expressions in the to-be-optimized SQL statement and the corresponding expression optimization result are consistent with the related information in a certain plan cache record, the plan cache record is a candidate plan cache record that matches the to-be-optimized SQL statement and the expression optimization result. At this time, it is considered that the SQL statement in the candidate plan cache record can use the same execution plan when executed as the to-be-optimized SQL statement, and therefore the candidate execution plan in the candidate plan cache record is determined as the target execution plan of the to-be-optimized SQL statement. When any to-be-optimized condition expression in the to-be-optimized SQL statement is inconsistent with the related information in a certain plan cache record, or the expression optimization result corresponding to any to-be-optimized condition expression in the to-be-optimized SQL statement is inconsistent with the related information in a certain plan cache record, the plan cache record is not a candidate plan cache record that matches the to-be-optimized SQL statement and the expression optimization result. When there is no candidate plan cache record that matches the to-be-optimized SQL statement and the expression optimization result in the plan cache, the target execution plan of the to-be-optimized SQL statement needs to be regenerated according to the to-be-optimized SQL statement and all expression optimization results, and a corresponding plan cache record is formed and registered in the plan cache.

[0047] Optionally, the method for optimizing a structured query statement provided in the embodiment can further include the following step: executing the to-be-optimized SQL statement according to the target execution plan.

[0048] In actual application, when the target execution plan of the to-be-optimized SQL statement is determined, the to-be-optimized SQL statement can be executed according to the target execution plan.

[0049] For example, it is assumed that there is an existing data table T, the table T contains a field C1, and the data type of C1 is integer. When a user inputs a SQL statement as follows:

[0050] SELECT * FROM T WHERE? > 5 AND? < 20;

[0051] The function of the statement can be understood as: querying data in the T table whose value of the first binding parameter "?" is greater than 5 and whose value of the second binding parameter "?" is less than 20.

[0052] When executed, the value of the first binding parameter "?" is C1, and the corresponding binding parameter number is 1; the value of the second binding parameter "?" is 10, and the corresponding binding parameter number is 2.

[0053] In the prior art, the actual value of the binding parameter "?" is directly ignored when generating an execution plan, and "?" is taken as a variable. An estimated value is given to the selection rate of the expression?>5 and?<20 according to statistical information or past experience, and the two estimated values are combined to obtain the selection rate of the T table. The total number of rows of data in the T table multiplied by the selection rate of the T table is the number of rows of data remaining in the T table after filtering. Obviously, such row number estimation has a large error. The influence of this error is not obvious for a simple SQL statement, but when multiple tables participate in a join query, the row number estimation for each table can directly affect the execution mode and order of the join, and thus the influence of the row number estimation error is more obvious.

[0054] When the method described in the embodiment is used for optimization, the actual value of the first binding parameter "?" is C1, the expression?>5 is replaced by C1>5, and the selection rate of C1>5 in the T table is obtained. Meanwhile, the actual value of the second binding parameter "?" is 10, the result of the expression 10<20 is judged, the result is TRUE, and then the expression?<20 is replaced by TRUE. This expression is always true and will not actually filter data, that is, the selection rate is 1. The selection rate of the expression C1>5 is combined to obtain the selection rate of the T table, and finally the number of rows of data remaining in the T table after filtering is obtained. Compared with directly estimating the selection rates of the expressions?>5 and?<20, the accuracy of this row number value is obviously higher. After generating the execution plan, the SQL statement, the execution plan, and the related information of the two binding parameters are registered in the plan cache.

[0055] When a new SQL statement identical to the above SQL statement is executed again, the actual value of the new binding parameter and the result of the condition expression to be optimized can be obtained. If the actual value of the first binding parameter is still C1, and the result of the second condition expression to be optimized?<20 is still TRUE, the execution plan in the plan cache can be directly used. Otherwise, a new execution plan is generated by reanalysis and registered in the plan cache.

[0056] The embodiment of the present application acquires a to-be-optimized SQL statement, determines a to-be-optimized condition expression containing a to-be-optimized binding parameter in the to-be-optimized SQL statement, determines an expression optimization result of the to-be-optimized condition expression when the to-be-optimized condition expression meets a target optimization condition, determines a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result and forms a corresponding plan cache record for registration in a plan cache if there is no alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, and selects a suitable execution plan in the plan cache according to an actual value of the binding parameter, generates an optimal execution plan of the query statement if there is no matching execution plan, and registers the optimization record in the plan cache, so that the statement execution efficiency and accuracy can be improved when the plan is reused.

[0057] Embodiment two

[0058] Figure 2 A structural diagram of an optimization device for a structured query statement is provided for the second embodiment of the present application. As shown in the figure, Figure 2 The device comprises:

[0059] An expression extraction module 210 is configured to acquire a to-be-optimized SQL statement and determine a to-be-optimized condition expression containing a to-be-optimized binding parameter in the to-be-optimized SQL statement.

[0060] An expression optimization module 220 is configured to determine an expression optimization result of the to-be-optimized condition expression when the to-be-optimized condition expression meets a target optimization condition.

[0061] A first statement optimization module 230 is configured to determine a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result and form a corresponding plan cache record for registration in a plan cache if there is no alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache.

[0062] Optionally, the device further comprises:

[0063] A second statement optimization module is configured to determine an alternative execution plan in an alternative plan cache record as a target execution plan of the to-be-optimized SQL statement if there is an alternative plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache.

[0064] Optionally, the device further comprises:

[0065] The execution plan determination module is configured to determine a target execution plan according to the SQL statement to be optimized when the condition expression to be optimized does not satisfy the target optimization condition, and form a corresponding plan cache record based on the SQL statement to be optimized and the target execution plan, and register the plan cache record into the plan cache.

[0066] Optionally, the target optimization condition comprises:

[0067] The condition expression to be optimized is a comparison expression of a to-be-optimized bound parameter and a constant expression, and the specified actual value of the to-be-optimized bound parameter is a constant.

[0068] Optionally, the plan cache record comprises an SQL statement, an execution plan and statement optimization information.

[0069] Optionally, the statement optimization information comprises a bound parameter number, a bound parameter specified actual value and an expression optimization result.

[0070] The structured query statement optimization device provided by the embodiments has the functions of the structured query statement optimization method provided by any of the embodiments, and has the beneficial effects of the execution method.

[0071] Embodiment three

[0072] Figure 3 A structural diagram of an electronic device 10 that can be used to implement embodiments of the present application is shown. The electronic device is intended to represent various forms of digital computers, such as laptops, desktops, tablets, personal digital assistants, servers, blade servers, mainframes, and other appropriate computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular telephones, smart phones, wearable devices (e.g., headsets, glasses, watches, etc.), and other similar computing devices. The components shown here, their connections and relationships, and their functions, are meant to be examples only, and are not intended to limit the inventiveness in the present document as described and / or claimed.

[0073] As Figure 3As shown, the electronic device 10 includes at least one processor 11, and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., communicatively connected to the at least one processor 11, where the memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer programs stored in the read-only memory (ROM) 12 or loaded into the random access memory (RAM) 13 from the storage unit 18. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0074] Various components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc., an output unit 17, such as various types of displays, a speaker, etc., a storage unit 18, such as a magnetic disk, an optical disk, etc., and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network, such as the Internet, and / or various telecommunication networks.

[0075] The processor 11 can be various general and / or special-purpose processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 performs various methods and processes described above, such as the optimization method of structured query statements.

[0076] In some embodiments, the optimization method of structured query statements can be implemented as a computer program tangibly embodied in a computer readable storage medium, such as the storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed onto the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the optimization method of structured query statements described above can be performed. Alternatively, in other embodiments, the processor 11 can be configured to perform the optimization method of structured query statements by any other appropriate means, such as by means of firmware.

[0077] The various embodiments of the systems and techniques described above can be implemented in digital electronic circuitry, integrated circuitry, a field programmable gate array (FPGA), an application specific integrated circuit (ASIC), a system on a chip (SOC), a load programmable logic device (CPLD), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include implementation in one or more computer programs that are executable and / or interpretable on a programmable system including at least one programmable processor, which can be special or general purpose, coupled to receive data and instructions from, and to transmit data and instructions to, a storage system, at least one input device, and at least one output device.

[0078] Computer programs used to implement the processes of the application can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus, such that the computer program, when executed, can cause instructions defined in the flow charts and / or block diagrams to be implemented. The computer program can be executed entirely on a machine, partially on a machine, partially on a machine as a standalone software package and partially on a remote machine or entirely on a remote machine or server.

[0079] In the context of the present application, a computer-readable storage medium can be a tangible medium that can contain or store computer programs for use by or in connection with an instruction execution system, apparatus, or device. Computer-readable storage media can include, but are not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium can be a machine-readable signal medium. More specific examples of the machine-readable storage medium will include one or more lines of electrical connections, portable computer disks, hard disk drives, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or Flash memory), optical fibers, portable compact disc read-only memories (CD-ROMs), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0080] To provide for interaction with a user, the systems and techniques described here can be implemented on an electronic device having a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can be used to provide for interaction with a user as well; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form, including acoustic, speech, or tactile input.

[0081] The systems and techniques described here can be implemented in a computing system that includes a back end component (e.g., as a data server), or that includes a middleware component (e.g., an application server), or that includes a front end component (e.g., a user computer having a graphical user interface or a Web browser through which a user can interact with an implementation of the systems and techniques described here), or any combination of such back end, middleware, or front end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0082] The computing system can include clients and servers. A client and server are generally remote from each other and typically interact through a communication network. The relationship of client and server arises by virtue of computer programs running on the respective computers and having a client-server relationship to each other. A server can be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system, to solve the defects of large management difficulty and weak business scalability in traditional physical host and VPS service.

[0083] It should be understood that the various forms of flow shown above can be re-ordered, added to, or deleted from without departing from the scope of the present disclosure. For example, the steps recited in the present disclosure can be executed in parallel, executed in sequence, or executed in a different order, as long as the desired results of the present disclosure are achieved, and the present disclosure is not limited herein.

[0084] The specific embodiments described above are not intended to be limiting, and persons skilled in the art will appreciate that various modifications, combinations, sub-combinations and alternatives can be made to the specific embodiments without departing from the spirit and principles of the disclosure. Accordingly, the disclosure is not limited to the specific embodiments described above, but only by the scope of the appended claims.

Claims

1. A method of optimizing a structured query statement, characterized by, The method comprises the following steps: acquiring a to-be-optimized SQL statement, and determining a to-be-optimized conditional expression containing a to-be-optimized binding parameter in the to-be-optimized SQL statement; when the to-be-optimized conditional expression meets a target optimization condition, determining an expression optimization result of the to-be-optimized conditional expression; if there is no alternative plan cache record in the plan cache that matches the to-be-optimized SQL statement and the expression optimization result, determining a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result, and forming a corresponding plan cache record and registering the plan cache record in the plan cache; if there is an alternative plan cache record in the plan cache that matches the to-be-optimized SQL statement and the expression optimization result, determining an alternative execution plan in the alternative plan cache record as the target execution plan of the to-be-optimized SQL statement; wherein the plan cache record comprises a SQL statement, an execution plan and statement optimization information; the statement optimization information comprises a binding parameter number, a binding parameter specified actual value and an expression optimization result; the determination of the expression optimization result of the to-be-optimized conditional expression comprises the following steps: comparing a specified actual value of the to-be-optimized binding parameter contained in the to-be-optimized conditional expression with a corresponding constant, and replacing the to-be-optimized conditional expression with TRUE or FALSE according to a comparison result; the method further comprises the following steps: when the to-be-optimized conditional expression does not meet the target optimization condition, determining a target execution plan according to the to-be-optimized SQL statement, and forming a corresponding plan cache record based on the to-be-optimized SQL statement and the corresponding target execution plan and registering the plan cache record in the plan cache; the method further comprises the following steps: if the to-be-optimized SQL statement contains a plurality of to-be-optimized conditional expressions, it is determined whether each to-be-optimized conditional expression meets the target optimization condition respectively, and when at least one to-be-optimized conditional expression meets the target optimization condition, an expression optimization result of the to-be-optimized conditional expression meeting the target optimization condition is determined.

2. The method of claim 1, wherein, after the target execution plan of the to-be-optimized SQL statement is determined, the method further comprises the following step: executing the to-be-optimized SQL statement according to the target execution plan.

3. The method of claim 1, wherein, the target optimization condition comprises the following steps: the to-be-optimized conditional expression is a comparison expression of a to-be-optimized binding parameter and a constant expression, and a specified actual value of the to-be-optimized binding parameter is a constant.

4. An apparatus for optimizing structured query statements, characterized by: The method comprises the following steps: an expression extraction module is configured to acquire a to-be-optimized SQL statement, and determine a to-be-optimized conditional expression containing a to-be-optimized binding parameter in the to-be-optimized SQL statement; an expression optimization module is configured to, when the to-be-optimized conditional expression meets a target optimization condition, determine an expression optimization result of the to-be-optimized conditional expression; a first statement optimization module is configured to, if there is no alternative plan cache record in the plan cache that matches the to-be-optimized SQL statement and the expression optimization result, determine a target execution plan of the to-be-optimized SQL statement according to the to-be-optimized SQL statement and the expression optimization result, and form a corresponding plan cache record and register the plan cache record in the plan cache; The second statement optimization module is configured to, if there is a candidate plan cache record matching the to-be-optimized SQL statement and the expression optimization result in the plan cache, determine an alternative execution plan in the candidate plan cache record as a target execution plan of the to-be-optimized SQL statement; wherein the plan cache record comprises a SQL statement, an execution plan and statement optimization information; the statement optimization information comprises a bound parameter number, a bound parameter specified actual value and an expression optimization result; The determining the expression optimization result of the to-be-optimized conditional expression comprises: comparing the specified actual value of the to-be-optimized bound parameter contained in the to-be-optimized conditional expression with a corresponding constant, and replacing the to-be-optimized conditional expression with TRUE or FALSE according to a comparison result; The apparatus further comprises: The execution plan determination module is configured to, when the to-be-optimized conditional expression does not satisfy the target optimization condition, determine a target execution plan according to the to-be-optimized SQL statement, and form a corresponding plan cache record based on the to-be-optimized SQL statement and the corresponding target execution plan, and register the plan cache record into the plan cache; The apparatus is further configured to: If the to-be-optimized SQL statement comprises a plurality of to-be-optimized conditional expressions, it is respectively judged whether each to-be-optimized conditional expression satisfies a target optimization condition; when there is at least one to-be-optimized conditional expression satisfying the target optimization condition, the expression optimization result of the to-be-optimized conditional expression satisfying the target optimization condition is determined.

5. An electronic device, comprising: The electronic device comprises: at least one processor; and a memory connected with the at least one processor in communication; wherein The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the structured query statement optimization method in any one of claims 1-3.

6. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer instructions for enabling the processor to execute the structured query statement optimization method in any one of claims 1-3 when executed.

Images