Method and device for optimizing SQL (Structured Query Language) through large model, electronic equipment and storage medium

Abstract

The invention provides a method and a device for optimizing an SQL (Structured Query Language) by a large model, electronic equipment and a storage medium, and relates to the technical field of data processing. The statistical information comprises a text of the SQL to be optimized, average CPU consumption, average execution time and execution times; analyzing the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized; querying corresponding index information, statistical information and structural information from an internal database platform based on the table name, and forming a query list; and inputting the query list, the text of the to-be-optimized SQL, the average CPU consumption, the average execution time and the number of execution times into the trained SQL optimization model to obtain an optimization suggestion for the to-be-optimized SQL. Through deep analysis of the to-be-optimized SQL, more comprehensive SQL related information is obtained, so that a large model can understand complex logic and data structures behind SQL statements, and a deeper and more comprehensive optimization strategy is provided.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method, device, electronic device and storage medium for optimizing SQL for a large model. Background Art

[0002] In the database field, SQL statements are the key language for operations such as data query and connection. However, due to differences in experience, skill level, and understanding of the internal mechanisms of the database, different developers may write SQL statements with different efficiency and resource usage. Therefore, it is necessary to optimize SQL statements. At present, SQL optimization methods based on large models are often limited to the analysis of the SQL text itself. Common practices include: (1) Syntax check to ensure that the syntax of SQL statements is correct to avoid query execution failure or abnormal execution results due to syntax errors. For example, check whether the keywords are spelled correctly and whether the structure of the statement complies with the SQL syntax rules (such as whether the use of clauses such as SELECT, FROM, and WHERE is standardized). (2) Keyword optimization: analyze the keywords used in the query statement, such as reasonably selecting index-related keywords (such as using INDEX, UNIQUE, etc. on appropriate fields) to improve query efficiency. At the same time, for some replaceable keywords, choose a better performance expression according to specific needs.

[0003] It can be seen that traditional large model optimization methods are often limited to the analysis of the SQL text itself, and lack consideration of database tables and data characteristics. Therefore, the optimization suggestions given are often relatively general and difficult to meet the actual needs in specific scenarios. Summary of the invention

[0004] In view of this, the purpose of this application is to provide a method, device, electronic device and storage medium for optimizing SQL with a large model, which can obtain more comprehensive SQL-related information through in-depth analysis of the SQL to be optimized, so that the large model can understand the complex logic and data structure behind the SQL statement, thereby providing a more in-depth and comprehensive optimization strategy.

[0005] In a first aspect, an embodiment of the present application provides a method for optimizing SQL with a large model, the method comprising the following steps:

[0006] S1. Obtain statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and number of executions of the SQL to be optimized;

[0007] S2. Parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized;

[0008] S3. Based on the table name, query the corresponding index information, statistical information and structural information from the internal database platform, and form a query list;

[0009] S4. Input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized.

[0010] In some embodiments, parsing the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized includes the following steps:

[0011] S201, preprocessing the text of the SQL to be optimized and tabulating it to form a list L1;

[0012] S202, create result list R1;

[0013] S203, determining whether the SQL text to be optimized is a Union query; if it is a Union query, splitting the Union query into various non-Union query parts, and adding the split non-Union queries one by one to the result list R1;

[0014] S204, analyzing the first related content of the first keyword for each SQL text in the result list R1, and adding the first related content to the result list R1;

[0015] S205, analyzing the second related content of the second keyword for each SQL text in the result list R1, and adding the second related content to the result list R1;

[0016] S206, for each SQL text in the result list R1, analyzing the column information between the third keyword and the first keyword, and adding the column information to the result list R1;

[0017] S207. For each SQL text containing a subquery in the result list R1, execute steps S204 to S206, and push the query conditions outside the subquery into the query conditions inside the subquery to obtain the final result list R1; wherein the final result list R1 obtained includes the table name, table alias, table query condition column and table access column.

[0018] In some embodiments, the preprocessing and listing of the text of the SQL to be optimized to form a list L1 includes the following steps:

[0019] Convert all the SQL texts to lowercase.

[0020] The SQL text to be optimized that is converted into lowercase is split into multiple elements according to the set rules to form a list L1.

[0021] In some embodiments, the first keyword is From; wherein, for each SQL text in the result list R1, after finding the first keyword From, the first related content obtained by analyzing it includes the table name, table alias, link condition and subquery.

[0022] In some embodiments, the second keyword is Where; wherein, for each SQL text in the result list R1, after finding the second keyword Where, the second related content obtained by analyzing it includes query conditions and subqueries.

[0023] In some embodiments, the third keyword is select; wherein, for each SQL text in the result list R1, the column information between the third keyword select and the first keyword From is shared.

[0024] In some embodiments, the SQL optimization model adopts a Transformer architecture, and the output optimization suggestions for the SQL to be optimized include one or more of text rewriting, index adjustment, and data query logic re-planning.

[0025] In a second aspect, an embodiment of the present application provides a device for optimizing SQL using a large model, the device comprising:

[0026] An acquisition module is used to acquire statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and execution times of the SQL to be optimized;

[0027] A parsing module, used to parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized;

[0028] A generation module, used to query corresponding index information, statistical information and structural information from the internal database platform based on the table name, and form a query list;

[0029] The optimization module is used to input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized.

[0030] In a third aspect, an embodiment of the present application provides an electronic device, comprising: a processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor communicates with the memory through the bus, and when the machine-readable instructions are executed by the processor, the steps of the method for optimizing SQL with a large model as described in any one of the first aspects are performed.

[0031] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method for optimizing SQL with a large model described in any one of the first aspects are executed.

[0032] The method, device, electronic device and storage medium for optimizing SQL by a large model described in the present application obtain statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and number of executions of the SQL to be optimized; the text of the SQL to be optimized is parsed to obtain all table names involved in the SQL to be optimized; based on the table name, the corresponding index information, statistical information and structural information are queried from the internal database platform, and a query list is formed; the query list and the text, average CPU consumption, average execution time and number of executions of the SQL to be optimized are input into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized. Thus, by deeply parsing the SQL to be optimized, more comprehensive SQL-related information is obtained, so that the large model can understand the complex logic and data structure behind the SQL statement, thereby providing a more in-depth and comprehensive optimization strategy. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0034] Figure 1 A flowchart of the method for optimizing SQL with a large model according to an embodiment of the present application is shown;

[0035] Figure 2 A flowchart of parsing the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized is shown in an embodiment of the present application;

[0036] Figure 3 A schematic diagram of the structure of the device for optimizing SQL with a large model according to an embodiment of the present application is shown;

[0037] Figure 4 A structural block diagram of an electronic device described in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0038] To make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the drawings in the present application only serve the purpose of explanation and description and are not used to limit the scope of protection of the present application. In addition, it should be understood that the schematic drawings are not drawn in real proportion. The flowchart used in this application shows the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowchart can be implemented out of sequence, and the steps without logical context can be reversed in order or implemented simultaneously. In addition, those skilled in the art can add one or more other operations to the flowchart under the guidance of the content of the present application, or remove one or more operations from the flowchart.

[0039] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the drawings here can be arranged and designed in various configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present application.

[0040] It should be noted that the term "comprising" will be used in the embodiments of the present application to indicate the existence of the features declared thereafter, but does not exclude the addition of other features.

[0041] In view of the technical problems raised by the background technology, the present application provides a method, device, electronic device and storage medium for optimizing SQL with a large model, which can obtain more comprehensive SQL-related information through in-depth analysis of the SQL to be optimized, so that the large model can understand the complex logic and data structure behind the SQL statement, thereby providing a more in-depth and comprehensive optimization strategy.

[0042] See the instruction manual Figure 1 , a method for optimizing SQL with a large model provided in an embodiment of the present application includes the following steps:

[0043] S1. Obtain statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and number of executions of the SQL to be optimized;

[0044] S2. Parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized;

[0045] S3. Based on the table name, query the corresponding index information, statistical information and structural information from the internal database platform, and form a query list;

[0046] S4. Input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized.

[0047] Specifically, in step S1, the internal database platform initiates the process, and provides the text, average CPU consumption, average execution time, and number of executions of a SQL statement to be optimized based on the internal statistical information of the database. The entire process is initiated by the internal database platform, which ensures the reliability and pertinence of the data source, because the internal database platform usually has a deep understanding and direct control over the data it manages. The text of the SQL statement to be optimized is the core object to be optimized, which clarifies the specific query operation content, and the subsequent optimization work of the large model will revolve around this; the average CPU consumption reflects the average CPU resource occupancy of the SQL statement in the past execution process, which is an important indicator for measuring its execution efficiency. Higher CPU consumption may mean that the query has complex calculations or unreasonable operations, and optimization is required to reduce resource occupancy; the average execution time directly reflects the speed of the execution of the SQL statement. Long-term execution may cause problems such as system response delay, which is also an aspect that needs to be focused on in optimization; the number of executions reflects the execution frequency of the SQL statement. For frequently executed statements, even if the optimization improvement of a single execution time is small, it can bring significant overall performance improvement when accumulated, so its optimization priority is often relatively high.

[0048] In step S2, the present application processes the SQL text obtained from step S1 through an independently developed SQL text parsing module, and then obtains all the table names involved in the SQL. This step lays the foundation for further in-depth understanding of the detailed information related to these tables, because only by clarifying the tables involved can we query various key information of these tables in a targeted manner. Figure 2 , the text of the SQL to be optimized is parsed to obtain all table names involved in the SQL to be optimized, including the following steps:

[0049] S201, preprocessing the text of the SQL to be optimized and tabulating it to form a list L1;

[0050] S202, create result list R1;

[0051] S203, determining whether the SQL text to be optimized is a Union query; if it is a Union query, splitting the Union query into various non-Union query parts, and adding the split non-Union queries one by one to the result list R1;

[0052] S204, analyzing the first related content of the first keyword for each SQL text in the result list R1, and adding the first related content to the result list R1;

[0053] S205, analyzing the second related content of the second keyword for each SQL text in the result list R1, and adding the second related content to the result list R1;

[0054] S206, for each SQL text in the result list R1, analyzing the column information between the third keyword and the first keyword, and adding the column information to the result list R1;

[0055] S207. For each SQL text containing a subquery in the result list R1, execute steps S204 to S206, and push the query conditions outside the subquery into the query conditions inside the subquery to obtain the final result list R1; wherein the final result list R1 obtained includes the table name, table alias, table query condition column and table access column.

[0056] Specifically, in step S201, the text of the SQL to be optimized may be preprocessed by converting all the text of the SQL to be optimized into lowercase. The main purpose of doing so is for the consistency and convenience of subsequent processing, because text analysis without distinguishing between upper and lower cases can simplify the logic and avoid problems such as mismatching caused by different upper and lower cases. Then, the processed SQL text is further tabulated, that is, the entire SQL text is split into elements according to certain rules (such as using spaces, specific symbols, etc. as separators) to form a list L1. For example, for the SQL statement "SELECT*FROM users WHERE age>18", after this step, it may become a list form such as ['select', '*', 'from', 'users', 'where', 'age', '>', '18'].

[0057] The purpose of the operation of step S202 is to initialize a list R1 for storing subsequent analysis results. This list will be continuously filled with various key information related to the SQL statement during the entire parsing process, and finally form a comprehensive result list containing important elements such as table name, table alias, table query condition column, table access column, etc.

[0058] In step S203, it is first necessary to determine whether the input SQL text is a Union query. The Union query is an operation for merging the result sets of multiple SELECT statements together. In SQL, it is usually possible to determine whether it is a Union query by identifying a specific "UNION" keyword. Among them, if the judgment result is that it is a Union query, then it needs to be split. The Union query is split into various non-Union query parts, and these split non-Union queries are added to the result list R1 one by one. The advantage of doing this is that each individual query part can be analyzed in more detail, because each part of the Union query may involve different tables, query conditions, etc., and separate processing helps to more accurately parse out the specific information of each part.

[0059] In step S204, the first keyword is From. For each SQL text in the result list R1 (after being processed in step S203, there may be multiple separate SQL texts in the result list R1, and each of them is analyzed here), the "from" keyword must first be found. The "from" keyword is used in the SQL statement to specify which tables to query from, and is a very critical part. And after finding the "from" keyword, further analyze the content related to it, including the table name, the alias of the table (if any, the alias is another name given to the table in order to more conveniently reference the table during the query process), the link condition (the condition for connecting multiple tables, such as inner connection, outer connection, etc. through a common field) and the subquery (if the subquery is included after the "from" keyword). Then all the analyzed contents are added to the result list R1, so that the information can be comprehensively used later for more comprehensive analysis.

[0060] In step S205, the second keyword is Where. Similarly, for each SQL text in the result list R1, the "where" keyword is found. The "where" keyword is used to specify the query condition in the SQL statement, that is, to limit which data that meets the specific conditions are selected from the table specified by "from". After finding the "where" keyword, the query conditions related to it and whether there are sub-queries are analyzed. All the query conditions and sub-query related information analyzed are added to the result list R1, so that the specific screening conditions of each SQL text can be clarified, and the understanding of the entire SQL statement can be further improved.

[0061] In step S206, the third keyword is select, and for each SQL text in the result list R1, the column information between the "select" keyword and the "from" keyword is analyzed. In the SQL statement, the "select" keyword is followed by the columns to be selected from the table. These columns may be specific field names or expressions generated by functions and the like. The specific conditions of these columns are analyzed and added to the result list R1. In this way, it is possible to clearly know which columns of data are to be obtained for each SQL text, which is very important for understanding the purpose and operation method of the entire SQL query.

[0062] In step S207, since the existence of the subquery may have been identified in the previous steps (such as step S204 and step S205), it is necessary to perform a loop analysis on the subquery in the result list R1 in this step. That is, for each SQL text containing a subquery in the result list R1, steps S204 to S206 must be performed again on the subquery part. Through such a loop analysis, it can be ensured that the internal structure of the subquery, the tables involved, the query conditions, etc. can also be analyzed in depth and detail, just like the main query, so as to fully grasp the complex structure and specific content of the entire SQL statement. Further, the purpose of pushing the query conditions outside the subquery into the query conditions inside the subquery is to more deeply analyze the relationship between the subquery and the main query. By integrating the external query conditions into the subquery, the logic and execution mode of the entire SQL statement can be understood more accurately, and in some cases new optimization points may be found, such as improving the efficiency of the entire query by adjusting the condition settings inside the subquery.

[0063] The above steps result in the final result list R1, which contains key information such as table name, table alias, table query condition column, table access column, etc. This information is very important for subsequent further analysis, optimization of SQL statements and other operations.

[0064] In step S3, the table name is taken out from the result list R1 obtained in step S2, and the index information, statistical information and structure information of the table are queried in the business database to form a query list R2. The index information is crucial to improving the efficiency of SQL queries. Understanding the existing index settings of the table helps to determine whether there are missing or unreasonable indexes, so as to consider whether to add, modify or delete indexes in the optimization suggestions; the statistical information, such as the distribution of data and some statistical characteristics of fields, can help the large model more accurately predict the execution effect under different optimization strategies, similar to the statistical data such as average CPU consumption in step S1, but here is more detailed statistical information for specific tables; the table structure information clearly defines the field composition, data type, primary and foreign key relationships of the table, etc., which is very important for understanding the operation logic of the query statement at the table level and judging whether there are potential optimization points caused by the table structure, such as whether the query operation can be simplified by adjusting the table structure.

[0065] In step S4, considering the successful application of the Transformer architecture in the field of natural language processing, and the fact that SQL statements also have a sequence structure similar to natural language to some extent, in this application, the SQL optimization model adopts the Transformer architecture. When training the constructed SQL optimization model, the SQL statement tag sequence and related table structure information preprocessed according to the above steps S2 and S3 are used as input, and the corresponding optimized SQL statement or optimization strategy label is used as output to train the model. During the training process, the model will continuously adjust its parameters to minimize the loss function. For example, a small step size gradient descent algorithm (such as Adam) is used to update the model parameters, and the early stopping method (Early-Stopping) can be used to prevent the model from overfitting, that is, when the performance of the model on the validation set is no longer improved, stop training.

[0066] The text, average CPU consumption, average execution time, and number of executions of the SQL to be optimized obtained from step S1, as well as the query list R2 obtained from step S3, are all provided to the trained SQL optimization model. In this way, the large model has more comprehensive information, including both the performance of the SQL statement itself and the detailed information of each table related to the SQL statement. Based on the rich information received, the large model can use its powerful analytical capabilities and pre-trained knowledge system to give targeted SQL optimization suggestions. These suggestions may involve rewriting the SQL statement itself, adjusting the index, re-planning the data query logic, and other aspects to achieve the purpose of improving SQL query performance.

[0067] It can be seen that the method for optimizing SQL for a large model provided in the present application, through this series of steps S1 to S4, can systematically utilize the statistical information of the internal database platform, SQL text parsing, and detailed information query of the table, etc., to provide sufficient materials for the large model, so that it can give more targeted and effective SQL optimization suggestions.

[0068] Based on the same inventive concept, a device for optimizing SQL with a large model is also provided in an embodiment of the present application. Since the principle of solving the problem by the device in the embodiment of the present application is similar to the method for optimizing SQL with a large model mentioned above in the embodiment of the present application, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be repeated.

[0069] As the instruction manual Figure 3 As shown, the present application also provides a device for optimizing SQL for a large model, the device comprising:

[0070] The acquisition module 301 is used to acquire statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and execution times of the SQL to be optimized;

[0071] The parsing module 302 is used to parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized;

[0072] A generation module 303 is used to query corresponding index information, statistical information and structural information from the internal database platform based on the table name, and form a query list;

[0073] The optimization module 304 is used to input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized. The SQL optimization model adopts the Transformer architecture, and the output optimization suggestions for the SQL to be optimized include one or more of text rewriting, index adjustment, and data query logic re-planning.

[0074] In some embodiments, the parsing module 302 parses the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized, including: S201, preprocessing the text of the SQL to be optimized and listing it to form a list L1; S202, creating a result list R1; S203, judging whether the text of the SQL to be optimized is a Union query; if it is a Union query, splitting the Union query into various non-Union query parts, and adding the split non-Union queries one by one to the result list R1; S204, analyzing the first related content of the first keyword for each SQL text in the result list R1, and adding the first related content to the result list R1; S205, for each SQL text in the result list R1, analyzing the second related content of the second keyword, and adding the second related content to the result list R1; S206, for each SQL text in the result list R1, analyzing the column information between the third keyword and the first keyword, and adding the column information to the result list R1; S207, for each SQL text containing a subquery in the result list R1, executing steps S204-S206, and pushing the query conditions outside the subquery into the query conditions inside the subquery to obtain the final result list R1; wherein the final result list R1 obtained includes the table name, table alias, table query condition column and table access column. Among them, the first keyword is From; among them, for each SQL text in the result list R1, after finding the first keyword From, the first related content obtained by analyzing it includes the table name, the table alias, the link condition and the subquery; the second keyword is Where; among them, for each SQL text in the result list R1, after finding the second keyword Where, the second related content obtained by analyzing it includes the query condition and the subquery; the third keyword is select; among them, for each SQL text in the result list R1, the column information between the third keyword select and the first keyword From is shared.

[0075] In some embodiments, the parsing module 302 preprocesses the text of the SQL to be optimized and lists it to form a list L1, including: converting all the text of the SQL to be optimized into lowercase; splitting the text of the SQL to be optimized converted into lowercase into multiple elements according to set rules to form a list L1.

[0076] The present application provides a device for optimizing SQL with a large model, which obtains statistical information of the SQL to be optimized from the internal database platform through an acquisition module; the statistical information includes the text, average CPU consumption, average execution time and execution times of the SQL to be optimized; the text of the SQL to be optimized is parsed by the parsing module to obtain all table names involved in the SQL to be optimized; the corresponding index information, statistical information and structural information are queried from the internal database platform based on the table name by the generation module, and a query list is formed; the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized are input into the trained SQL optimization model through the optimization module to obtain optimization suggestions for the SQL to be optimized. Thus, through the in-depth analysis of the SQL to be optimized, more comprehensive SQL related information is obtained, so that the large model can understand the complex logic and data structure behind the SQL statement, thereby providing a more in-depth and comprehensive optimization strategy.

[0077] Based on the same concept of the present invention, the specification is attached Figure 4 As shown, the structure of an electronic device 400 provided in an embodiment of the present application includes: at least one processor 401, at least one network interface 404 or other user interface 403, a memory 405, and at least one communication bus 402. The communication bus 402 is used to realize the connection and communication between these components. The electronic device 400 optionally includes a user interface 403, including a display (for example, a touch screen, LCD, CRT, holographic imaging (Holographic) or projection (Projector), etc.), a keyboard or a pointing device (for example, a mouse, a trackball (trackball), a touch pad or a touch screen, etc.).

[0078] The memory 405 may include a read-only memory and a random access memory, and provides instructions and data to the processor 401. A portion of the memory 405 may also include a non-volatile random access memory (NVRAM).

[0079] In some implementations, the memory 405 stores the following elements, which may be protected modules or data structures, or subsets thereof, or extended sets thereof:

[0080] Operating system 4051, including various system programs for implementing various basic services and processing hardware-based tasks;

[0081] The application module 4052 includes various application programs, such as a launcher, a media player, a browser, etc., which are used to implement various application services.

[0082] In an embodiment of the present application, by calling the program or instructions stored in the memory 405, the processor 401 is used to execute steps in a method for optimizing SQL using a large model, and can obtain more comprehensive SQL-related information through in-depth analysis of the SQL to be optimized, so that the large model can understand the complex logic and data structure behind the SQL statement, thereby providing a more in-depth and comprehensive optimization strategy.

[0083] The present application also provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps in the method for optimizing SQL with a large model are executed.

[0084] Specifically, the storage medium can be a general storage medium, such as a mobile disk, a hard disk, etc. When the computer program on the storage medium is run, the above-mentioned large model optimization SQL method can be executed.

[0085] In the embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, and the indirect coupling or communication connection of devices or units can be electrical, mechanical or other forms.

[0086] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0087] In addition, each functional unit in the embodiments provided in the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

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

[0089] Finally, it should be noted that the above embodiments are only specific implementation methods of the present application, which are used to illustrate the technical solutions of the present application, rather than to limit them. The protection scope of the present application is not limited thereto. Although the present application is described in detail with reference to the aforementioned embodiments, ordinary technicians in the field should understand that any technician familiar with the technical field can still modify the technical solutions recorded in the aforementioned embodiments within the technical scope disclosed in the present application, or can easily think of changes, or make equivalent replacements for some of the technical features therein; and these modifications, changes or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application. They should all be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A method for optimizing SQL for a large model, characterized in that: The method comprises the following steps: S1. Obtain statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and number of executions of the SQL to be optimized; S2. Parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized; S3. Based on the table name, query the corresponding index information, statistical information and structural information from the internal database platform, and form a query list; S4. Input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized.

2. The method for optimizing SQL using a large model according to claim 1, characterized in that: The step of parsing the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized includes the following steps: S201, preprocessing the text of the SQL to be optimized and tabulating it to form a list L1; S202, create result list R1; S203, determining whether the SQL text to be optimized is a Union query; if it is a Union query, splitting the Union query into various non-Union query parts, and adding the split non-Union queries one by one to the result list R1; S204, analyzing the first related content of the first keyword for each SQL text in the result list R1, and adding the first related content to the result list R1; S205, analyzing the second related content of the second keyword for each SQL text in the result list R1, and adding the second related content to the result list R1; S206, for each SQL text in the result list R1, analyzing the column information between the third keyword and the first keyword, and adding the column information to the result list R1; S207. For each SQL text containing a subquery in the result list R1, execute steps S204 to S206, and push the query conditions outside the subquery into the query conditions inside the subquery to obtain the final result list R1; wherein the final result list R1 obtained includes the table name, table alias, table query condition column and table access column.

3. The method for optimizing SQL using a large model according to claim 2, characterized in that: The preprocessing and tabulating of the text of the SQL to be optimized to form a list L1 comprises the following steps: Convert all the SQL texts to lowercase. The SQL text to be optimized that is converted into lowercase is split into multiple elements according to the set rules to form a list L1.

4. The method for optimizing SQL using a large model according to claim 3, characterized in that: The first keyword is From; wherein, for each SQL text in the result list R1, after finding the first keyword From, the first related content obtained by analyzing it includes the table name, the table alias, the link condition and the subquery.

5. The method for optimizing SQL using a large model according to claim 4, characterized in that: The second keyword is Where; for each SQL text in the result list R1, after finding the second keyword Where, the second related content obtained by analyzing it includes query conditions and subqueries.

6. The method for optimizing SQL using a large model according to claim 5, characterized in that: The third keyword is select; for each SQL text in the result list R1, the column information between the third keyword select and the first keyword From is shared.

7. The method for optimizing SQL using a large model according to claim 6, characterized in that: in, The SQL optimization model adopts a Transformer architecture, and the output optimization suggestions for the SQL to be optimized include one or more of text rewriting, index adjustment, and data query logic replanning.

8. A device for optimizing SQL for a large model, characterized in that: The device comprises: An acquisition module is used to acquire statistical information of the SQL to be optimized from the internal database platform; the statistical information includes the text, average CPU consumption, average execution time and execution times of the SQL to be optimized; A parsing module, used to parse the text of the SQL to be optimized to obtain all table names involved in the SQL to be optimized; A generation module, used to query corresponding index information, statistical information and structural information from the internal database platform based on the table name, and form a query list; The optimization module is used to input the query list and the text, average CPU consumption, average execution time and execution times of the SQL to be optimized into the trained SQL optimization model to obtain optimization suggestions for the SQL to be optimized.

9. An electronic device, characterized in that: include: A processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor. When the electronic device is running, the processor and the memory communicate via the bus. When the machine-readable instructions are executed by the processor, the steps of the method for optimizing SQL for a large model as described in any one of claims 1 to 7 are performed.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which, when executed by a processor, executes the steps of the method for optimizing SQL with a large model as described in any one of claims 1 to 7.

Images