SQL (Structured Query Language) logic detection and optimization method, device, equipment and medium

Abstract

The invention relates to the field of computers, can be applied to business system platforms of financial science and technology, medical treatment and health and the like, and discloses an SQL logic detection and optimization method, device, equipment and medium. The method comprises the steps that in response to a script detection request, a to-be-detected SQL script code is obtained; table connection scene recognition is conducted on the SQL script code, and whether a table connection scene exists in the SQL script code or not is determined; if a table connection scene exists in the SQL script code, obtaining connection key information behind a specified keyword position; performing global retrieval on the SQL script code according to the connection key information, and confirming whether a screening condition for the partition field exists or not; and if the screening condition for the partition field does not exist, outputting risk prompt information of full-table scanning so as to optimize the SQL script code. The resource consumption risk of the current script is automatically identified to optimize the code standardization degree by detecting the mechanical energy full-table scanning risk of the SQL script code in the development process, so that the timeliness of the data processing task is improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a SQL logic detection and optimization method, device, equipment and medium. Background Art

[0002] With the rapid development of information technology, databases, as core tools for data storage and management, have a direct impact on the operating efficiency of the entire information system. Ensuring task execution time and reducing CPU operating costs have always been key indicators of data management. In the financial and healthcare fields, data timeliness is the most critical.

[0003] In the healthcare sector, hospitals and healthcare institutions need to process massive amounts of patient health data, including electronic medical records, test results, and treatment records. This data is typically stored in partitioned tables, partitioned by time (such as date) or patient type (such as department). Timely and accurate analysis of this data is crucial for patient treatment and health management.

[0004] In the financial sector, risk assessment reports are a crucial part of the daily operations of financial institutions (such as banks and securities firms). These reports require extracting key information from massive amounts of transaction data, customer information, and market data, and performing complex calculations and analysis. The timeliness and accuracy of this data are crucial for risk assessment, as any delay or error can result in significant losses.

[0005] The two main factors affecting timeliness are resource limitations and script code complexity. In the era of big data, limited resources must be used as much as possible on higher-value data. However, full table scans, a serious resource drain, are often hidden and difficult to detect during development. This significantly impacts data push failures and reduces the timeliness of related data processing tasks. Summary of the Invention

[0006] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a SQL logic detection and optimization method, device, equipment and medium that can be applied to the medical field, financial technology or other related fields. Its main purpose is to identify the resource consumption risks of full table scans in scripts and optimize SQL logic to improve the timeliness of data processing tasks.

[0007] The technical solutions of the present invention are as follows:

[0008] A first aspect of the present invention provides a SQL logic detection and optimization method, comprising:

[0009] Responding to a script detection request, obtaining the SQL script code to be detected;

[0010] Performing table join scenario identification on the SQL script code to confirm whether a table join scenario exists in the SQL script code;

[0011] If a table join scenario exists in the SQL script code, then the join key information after the specified keyword position is obtained;

[0012] Perform a global search on the SQL script code based on the connection key information to confirm whether there is a screening condition for the partition field;

[0013] If there is no screening condition for the partition field, a risk warning message of a full table scan is output to optimize the SQL script code.

[0014] A second aspect of the present invention provides an SQL logic detection and optimization device, comprising:

[0015] A code acquisition module is used to obtain the SQL script code to be detected in response to a script detection request;

[0016] A connection scenario recognition module is used to perform table connection scenario recognition on the SQL script code to confirm whether a table connection scenario exists in the SQL script code;

[0017] A connection key acquisition module is used to obtain connection key information after a specified keyword position if a table connection scenario exists in the SQL script code;

[0018] A search confirmation module is used to perform a global search on the SQL script code according to the connection key information to confirm whether there is a screening condition for the partition field;

[0019] The risk decision module is used to output risk prompt information of a full table scan to optimize the SQL script code if there is no screening condition for the partition field.

[0020] A third aspect of the present invention provides a computer device comprising at least one processor; and

[0021] a memory communicatively connected to the at least one processor; wherein,

[0022] The memory stores instructions that can be executed by the at least one processor. The instructions are executed by the at least one processor to enable the at least one processor to perform the above-mentioned SQL logic detection and optimization method.

[0023] A fourth aspect of the present invention provides a non-volatile computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned SQL logic detection and optimization method.

[0024] Beneficial Effects: The present invention discloses a method, apparatus, device, and medium for SQL logic detection and optimization. Compared with the prior art, the embodiments of the present invention provide a method, apparatus, device, and medium for SQL logic detection and optimization, including: obtaining SQL script code to be detected in response to a script detection request; identifying table connection scenarios on the SQL script code to confirm whether a table connection scenario exists in the SQL script code; if a table connection scenario exists in the SQL script code, obtaining connection key information after a specified keyword position; performing a global search on the SQL script code based on the connection key information to confirm whether there are filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputting risk warning information for a full table scan to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] In order to more clearly illustrate the solutions in the present invention, a brief introduction is given below to the drawings required for use in describing the embodiments of the present invention. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0026] Figure 1 A schematic diagram of an application environment of the SQL logic detection and optimization method provided by an embodiment of the present invention;

[0027] Figure 2 A flow chart of the SQL logic detection and optimization method provided by an embodiment of the present invention;

[0028] Figure 3 A flowchart of step S202 in the SQL logic detection and optimization method provided by an embodiment of the present invention;

[0029] Figure 4 A flow chart of step S203 in the SQL logic detection and optimization method provided by an embodiment of the present invention;

[0030] Figure 5 A flow chart of step S204 in the SQL logic detection and optimization method provided by an embodiment of the present invention;

[0031] Figure 6 A schematic diagram of the functional modules of the SQL logic detection and optimization device provided by an embodiment of the present invention;

[0032] Figure 7 A schematic diagram of the hardware structure of a computer device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0033] To make the objectives, technical solutions, and effects of the present invention more clear and distinct, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are merely for the purpose of explaining the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below with reference to the accompanying drawings.

[0034] The SQL logic detection and optimization method provided by the embodiment of the present invention can be applied to Figure 1 In an application environment, the system includes a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 is a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired and / or wireless communication links, etc.

[0035] The user may use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 via the network 104 to receive or send messages, etc. Various communication client applications may be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as knowledge reading applications, web browser applications, search applications, instant messaging tools, email clients, and / or social platform software (for example only).

[0036] The first terminal device 101 , the second terminal device 102 , and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0037] The server 105 may be a server that provides various services, such as a backend server that provides support for the content browsed by the user using the first terminal device 101, the second terminal device 102, and the third terminal device 103 (for example only). The backend server may analyze and process the received user requests and other data, and feed back the processing results (such as web pages, information, or data obtained or generated according to the user request) to the terminal device. The server 105 may 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 difficult management and weak business scalability in traditional physical hosts and VPS services ("Virtual Private Server", or "VPS" for short). The server 105 may also be a server for a distributed system, or a server combined with a blockchain.

[0038] It should be noted that the SQL logic detection and optimization method provided in the embodiments of the present application can generally be executed by the first terminal device 101, the second terminal device 102, or the third terminal device 103. Accordingly, the SQL logic detection and optimization apparatus provided in the embodiments of the present invention can also be provided in the first terminal device 101, the second terminal device 102, or the third terminal device 103. Alternatively, the SQL logic detection and optimization method provided in the embodiments of the present invention can generally be executed by the server 105. Accordingly, the SQL logic detection and optimization apparatus provided in the embodiments of the present invention can generally be provided in the server 105.

[0039] It should be understood that the numbers of the above terminal devices, networks and servers are merely illustrative and any number of terminal devices, networks and servers may be provided as required.

[0040] like Figure 2 As shown, the SQL logic detection and optimization method provided by the embodiment of the present invention specifically includes the following steps:

[0041] S201: In response to a script detection request, obtain the SQL script code to be detected.

[0042] In this embodiment, the SQL script code to be tested refers to the SQL query statements written by the developer, which may include operations such as table joins and data filtering. The developer platform has a built-in script detection function module. When the developer is writing an SQL script, they can trigger a script detection request by clicking the script detection button on the interface. Alternatively, the developer's operations can be monitored in real time, and a script detection request can be automatically triggered when the developer modifies and saves the code, ensuring the timeliness of the detection process.

[0043] After receiving the request, the system responds to the script detection request and reads the SQL script code to be detected from the code editor or code storage area of the developer platform. Alternatively, the system can receive SQL script code from other systems through an API interface, such as directly pulling the script from a code version control system (such as Git) for detection. Alternatively, the system can upload a file, that is, the developer uploads the SQL script file, and the system reads the file content for detection. This embodiment does not limit the method for obtaining the SQL script code to be detected. Preferably, after obtaining the SQL script code to be detected, the code can be formatted to remove unnecessary spaces, line breaks, and comments, etc., so that subsequent analysis is more efficient.

[0044] For example, in the financial sector, risk assessment system developers at financial institutions write SQL scripts to extract key transaction data and customer information from massive amounts of transaction data, customer information, and market data. After completing the script, the developer clicks the script check button to initiate a check request. Upon receiving the request, the SQL script code is read from the code editor, ensuring that the system accurately retrieves the SQL script code to be checked. This provides basic data for subsequent check processes, thereby optimizing the performance of the risk assessment system.

[0045] In the healthcare sector, hospital electronic medical record system developers write SQL scripts to query large amounts of patient health data, including electronic medical records, test results, and treatment records. Developers pre-configure a script to automatically initiate a check request each time the code is edited. After the script is written and saved, the system automatically triggers a script check request and reads the SQL script code from the code editor. This ensures that every code modification is checked, thereby ensuring the performance of the medical record query system.

[0046] S202: Identify a table join scenario on the SQL script code to confirm whether a table join scenario exists in the SQL script code.

[0047] In this embodiment, after obtaining the SQL script code, alignment is performed to identify table join scenarios. A string matching algorithm (such as str.IndexOf()) is used to search the SQL script code for join operations between multiple tables implemented by keywords such as JOIN (including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, etc.) and ON, thereby confirming whether there is a table join scenario in the SQL script code. If not, the current code does not risk a full table scan, and the process can be terminated directly to save computing resources.

[0048] Specifically, when identifying a table join scenario, regular expression matching can also be used, that is, regular expression matching can be used to more accurately identify the grammatical structure of the table join, or SQL syntax tree parsing can be used, that is, by parsing the SQL syntax tree, directly extracting relevant information of the table join, etc. to confirm whether a table join scenario exists. This embodiment does not limit this.

[0049] For example, in the financial sector, a financial institution's risk assessment system needs to extract key information from transaction data and customer information to assess a customer's credit risk. This data is stored in the customers and transactions tables. When a developer writes an SQL script to jointly query a customer's transaction records and basic information, the SQL script code can join the customers and transactions tables using the customer_id field.

[0050] In the healthcare field, the hospital's electronic medical record system needs to query patients' medical records and test results. This data is stored in different tables. For example, the patients table stores basic patient information, and the lab_results table stores laboratory test results. When developers write SQL scripts to jointly query patients' medical records and the latest test results, the patients table and lab_results table can be connected through the patient_id field.

[0051] By quickly identifying whether there are table join operations in the SQL scripts in the above-mentioned different application scenarios, a basis is provided for subsequent extraction of join key information and detection of partition field screening conditions.

[0052] S203: If a table connection scenario exists in the SQL script code, obtain connection key information after the specified keyword position.

[0053] In this embodiment, if a table join scenario is identified in the SQL script code, the join key information after the specified keyword position is further obtained. The specified keyword is used to identify the starting position of the table join condition, such as ON, and the join key information is used to associate the two tables in the table join operation, including the fields, conditions, and table names involved in the join. Specifically, the system can locate the position of the specified keyword and extract the join key information from the code that follows it, or it can extract the content after the specified keyword through string segmentation. By accurately extracting the table join key information, a basis is provided for further analysis of the partition field screening conditions.

[0054] S204: Perform a global search on the SQL script code according to the connection key information to confirm whether there is a screening condition for the partition field.

[0055] In this embodiment, the SQL script code is further globally searched based on the connection key information, that is, a specific condition is searched within the global scope of the SQL script code to confirm whether there are any screening conditions for the partition fields. Since the risk of a full table scan is common in the table connection and condition screening part of the partition table in the SQL code, the developer writes the screening condition after the ON connection key instead of in the WHERE. Although the data results are the same, it will cause the CPU to perform a full table scan of the partition table instead of trimming based on the partition restrictions, resulting in a large amount of resource waste. Therefore, matching is performed based on the connection key information and the partition field, and the partition field information can be pre-stored in a mapping table for fast matching and searching. If there is a partition field in the connection key, it is necessary to further confirm whether there is a corresponding WHERE condition screening, so as to determine whether there is a risk of a full table scan, so as to optimize the system performance as much as possible under resource constraints.

[0056] S205: If there is no screening condition for the partition field, output risk warning information of full table scan to optimize the SQL script code.

[0057] In this embodiment, if the connection key information does not contain a partition field, or the connection key information contains a partition field and there is a corresponding partition field screening condition, there is no full table scan analysis, and the detection process ends directly. If the connection key contains a partition field, but there is no corresponding partition field screening condition, it is assessed that there is a full table scan risk. At this time, the risk warning information of the full table scan is output, for example, the risk warning information is displayed through email, text message, pop-up window, etc., to prompt developers to optimize the SQL script code. Through code optimization and standardization, full table scans can be avoided as much as possible, which significantly reduces resource consumption and improves the timeliness of data processing.

[0058] In the above embodiment, the present invention discloses a SQL logic detection and optimization method. The method obtains SQL script code to be detected in response to a script detection request; identifies table join scenarios in the SQL script code to confirm whether a table join scenario exists in the SQL script code; if a table join scenario exists in the SQL script code, obtains join key information after a specified keyword position; performs a global search on the SQL script code based on the join key information to confirm whether there are any filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputs a full table scan risk warning message to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks.

[0059] In one embodiment, Figure 3As shown, step S202 includes:

[0060] S301, performing field matching on the SQL script code to identify whether the SQL script code contains a preset connection field;

[0061] S302: When the preset connection field is identified in the SQL script code, obtain the context of the preset connection field;

[0062] S303: Analyze the context of the preset connection field to identify the operation corresponding to the preset connection field in the SQL script code;

[0063] S304: When the preset connection field corresponds to a table connection operation, it is confirmed that a table connection scenario exists in the SQL script code; otherwise, no table connection scenario exists.

[0064] In this embodiment, when identifying whether a table join scenario exists in SQL script code, field matching is first performed through string matching or regular expressions to search the SQL script for specific keywords or fields, namely preset join fields. These preset join fields are predefined keywords for table join operations, such as JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, and ON. If a preset join field is identified in the SQL script code, to ensure the reliability of table join scenario identification, the code snippet surrounding the preset join field is further obtained, including the statement containing the field and the related code sections before and after it as its context. By obtaining the context of the table join field, detailed information is provided for subsequent analysis of the specific table join operation, avoiding incorrect identification of table join scenarios.

[0065] Based on the context code snippet of the obtained preset connection field, syntax parsing is performed, and the specific database operations performed by the preset connection field in the SQL script code, such as table connection operations, are further divided to ensure that they are used for table connection operations, rather than other purposes (such as string concatenation or appearing in comments), etc. Only when the preset connection field corresponds to a table connection operation, that is, when the context analysis shows that the connection operation between multiple tables is realized through the preset connection field, is it confirmed that there is a table connection scenario in the SQL script code. Otherwise, there is no table connection scenario. Not only is the actual database operation of the preset connection field confirmed by field matching, but it is also further combined with Shaanxi I analysis to avoid table connection scenario recognition errors caused by single field matching, thereby improving the reliability of SQL script detection.

[0066] In one embodiment, Figure 4 As shown, step S203 includes:

[0067] S401: If a table join scenario exists in the SQL script code, obtain location information of a specified keyword in the SQL script code;

[0068] S402: Extract the connection clause following the designated keyword according to the position information, and parse the connection clause to obtain the table names involved in the connection and the connection keys between the tables.

[0069] In this embodiment, when obtaining join key information, if a table join scenario exists in the SQL script code, the position information of a specified keyword in the SQL script code is first obtained. The specified keyword is a keyword used to identify a table join operation in the SQL script, such as ON. The position information is the specific position of the specified keyword in the SQL script code, which can be specifically represented by a character offset or a row and column number. For example, the position information of the ON keyword is found using the string matching algorithm str.IndexOf(). Assuming that the ON keyword is located at the 40th character in the 3rd row, the position information of the ON keyword is recorded as (row: 3, column: 40), providing a precise starting point for subsequent extraction of the join clause.

[0070] Based on this location information, the join clause following the specified keyword is extracted. The join clause is the code snippet following the specified keyword, describing the specific conditions for table joins. The SQL parsing tool then parses the join clause, breaking it down into the table names and join keys involved in the join. The table names are the names of the tables involved in the join, and the join keys are the field names and join conditions used to connect the two tables. Accurately extracting the join clause and parsing the table names and join keys involved in the join provides detailed information for further analysis of partition field screening conditions, reliably detecting logical risks in SQL script code.

[0071] In one embodiment, after step S402, the method further includes:

[0072] Create an initial dependency graph;

[0073] According to the table names involved in the connection and the connection keys between tables, corresponding nodes and directed edges are added to the dependency graph to generate a corresponding connection dependency graph.

[0074] In this embodiment, after parsing and obtaining the table names and inter-table connection keys involved in the connection in the current SQL script code, a connection dependency graph is generated based on the parsing results to facilitate more efficient analysis of the complexity of table connections and optimization of query logic. First, an initialized dependency graph is created. The dependency graph is a directed graph used to represent the connection dependency relationship between tables. In this embodiment, the nodes represent tables, and the directed edges represent the connection relationship between tables. First, an empty dependency graph is created that does not contain any nodes or edges. According to the table names and inter-table connection keys obtained by parsing, corresponding nodes and directed edges are added to the initialized dependency graph. That is, for each connection condition, the table names participating in the connection are added to the graph as nodes, and directed edges are added between each node according to the connection conditions between the tables to represent the dependency relationship between different tables, thereby generating a corresponding connection dependency graph. The dependency graph can more intuitively represent the connection relationship between tables, thereby improving the efficiency of SQL logic optimization detection.

[0075] In one embodiment, Figure 5 As shown, step S204 includes:

[0076] S501, determining all tables involved in the connection and the connection keys between tables according to the connection key information;

[0077] S502. Obtain partition field information of all tables involved in the connection from database metadata;

[0078] S503: traverse the fields in the inter-table join key, match the traversal result with the partition field information, and determine the matching partition field in the inter-table join key;

[0079] S504: Perform a global search on the SQL script code to determine whether there is a screening condition corresponding to the partition field.

[0080] In this embodiment, all tables involved in the connection and the connection keys between tables are determined based on the connection key information. For example, available() is used to obtain all the connection keys between tables and the names of the connected tables after ON to avoid omissions when matching with the partition fields and ensure the reliability of the script detection. Then, based on all the tables involved in the connection, the database metadata is queried to obtain the partition field information of all the tables involved in the connection, where the database metadata is the structural information describing the tables and fields in the database, including table name, field name, field type, partition field, etc. The fields used for partitioning in the tables involved in the connection are queried from the database metadata, such as partitioning by date or patient ID, so as to obtain the partition field information as the accurate matching basis for the partition field matching.

[0081] Check the fields in the table join key one by one, compare the fields in the join key with the partition field information to determine whether they are consistent. For example, use equal() to traverse the join key and partition field information for comparison, and determine the partition field in the table join key that matches the partition field information, that is, the partition field in the table join key. If the partition field does not exist in the join key, there is no risk of full table scan and the process ends; if the partition field exists, perform a global search of the SQL script code based on the partition field in the join key to determine whether there is a filtering condition corresponding to the partition field. For example, for the code snippet a left join bon a.no=b.no and a.part_dt='2023-06-27', the join key includes a.no=b.no and a.part_dt='2023-06-27'. After matching, it is confirmed that the partition field is part_dt, so check whether there is a global where The condition a.part_dt = '2023-06-27' exists, thereby determining whether there is a risk of a full table scan. This enables reliable and efficient full table scan risk detection and avoids the impact of full table scans on data timeliness.

[0082] In one embodiment, step S205 includes:

[0083] If there are no filter conditions for the partition field, a pop-up window component will be displayed in the user interface to indicate the risk of a full table scan.

[0084] When it is detected that the user triggers the preset repair button on the pop-up component, it automatically jumps to the corresponding code position to be modified according to the risk warning information to prompt the user to optimize the SQL script code.

[0085] In this embodiment, if it is confirmed that there is currently no filtering condition for the partition field, the risk warning information of the full table scan is output in the form of a pop-up window, where the pop-up window component is a pop-up window in the user interface, which is used to display prompt information to the user. The risk warning information is text information that informs the user that there is a full table scan risk in the SQL script, which includes the location of the risk point and optimization suggestions, etc. The pop-up window prompt intuitively informs the developer of the full table scan risk in the SQL script, and provides optimization suggestions to help the developer quickly locate the problem and optimize it.

[0086] Furthermore, a button provided in the pop-up component is a preset repair button. When the user clicks the button, the code optimization operation is automatically triggered. At this time, the code position that needs to be modified in the SQL script is automatically located according to the risk warning information. For example, the currently displayed risk warning information is: "Risk warning: There is a full table scan risk in your SQL script. It is recommended to add a filter condition for the partition field in the WHERE clause." The developer clicks the "Repair" button in the pop-up window, and automatically jumps to the clause position of the SQL script according to the risk warning information, and highlights the part where the WHERE condition needs to be added. Through automatic jumps and optimization prompts, developers can quickly locate the code position that needs to be modified, and provide specific optimization suggestions, which significantly improves development efficiency and optimizes the performance of the medical record query system.

[0087] In one embodiment, after step S205, the method further includes:

[0088] Perform risk point analysis on the risk warning information to obtain a risk point list and corresponding optimization strategies;

[0089] Obtaining a pre-configured automatic optimization granularity, and filtering the risk point list and corresponding optimization strategies according to the automatic optimization granularity;

[0090] An optimized SQL code snippet is generated based on the filtered risk point list and the corresponding optimization strategy, and the optimized SQL code snippet is inserted into the corresponding position of the SQL script code or replaces the original code at the corresponding position.

[0091] In this embodiment, after a full table scan risk is detected, automatic optimization is further implemented through risk point analysis and pre-set optimization strategies, thereby improving development efficiency and reducing human error. The detected full table scan risk is first analyzed in detail to identify specific risk points. All detected risk points are listed in a risk point list, and the corresponding optimization strategy is called for each risk point. This identifies specific issues in the SQL script and provides targeted optimization strategies to help developers quickly locate and resolve the issues.

[0092] The system then obtains the pre-configured automatic optimization granularity. This granularity is the pre-configured optimization level that determines the scope and extent of automatic system optimization. For example, simple issues are automatically corrected, while complex issues are only prompted. Risk points requiring automatic optimization and optimization strategies are screened based on the automatic optimization granularity. For example, a pre-configured automatic optimization granularity of "low" indicates that the system only automatically optimizes high-risk points. Therefore, filtering is performed based on the different risk points to obtain the risk points to be processed. Using the configured automatic optimization granularity, risk points requiring automatic optimization are screened, avoiding over-optimization or under-optimization and improving optimization efficiency.

[0093] Generate optimized SQL code snippets based on the filtered risk point list and the corresponding optimization strategy. That is, the improved SQL code generated according to the optimization strategy is inserted into the appropriate location of the original SQL script, or replaces the part of the original code that needs to be optimized. By automatically inserting or replacing the optimized SQL code snippet, problems in the SQL script can be quickly fixed, problems in the SQL script can be solved more efficiently, development efficiency can be improved, and data timeliness can be ensured.

[0094] It should be noted that there is not necessarily a certain order between the above steps. A person skilled in the art can understand, based on the description of the embodiments of the present invention, that in different embodiments, the above steps may have different execution orders, that is, they may be executed in parallel, or may be executed interchangeably, etc.

[0095] Further references Figure 6 , as a response to the above Figure 2 The present invention provides an embodiment of a SQL logic detection and optimization device, which is similar to Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0096] like Figure 6 As shown, the SQL logic detection and optimization device 60 described in this embodiment includes:

[0097] The code acquisition module 601 is used to obtain the SQL script code to be detected in response to the script detection request;

[0098] A connection scenario identification module 602 is used to identify a table connection scenario for the SQL script code and confirm whether a table connection scenario exists in the SQL script code;

[0099] A connection key acquisition module 603 is configured to acquire connection key information after a specified keyword position if a table connection scenario exists in the SQL script code;

[0100] A search confirmation module 604 is configured to perform a global search on the SQL script code based on the connection key information to confirm whether a screening condition exists for a partition field;

[0101] The risk decision module 605 is configured to output risk warning information of a full table scan to optimize the SQL script code if there is no screening condition for the partition field.

[0102] The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions. It is more suitable for describing the SQL logic detection and optimization execution process than a program. For the specific implementation of each module, please refer to the corresponding method embodiment above, which will not be repeated here.

[0103] In one embodiment, the connection scene recognition module 602 includes:

[0104] A field matching unit, configured to perform field matching on the SQL script code and identify whether the SQL script code contains a preset connection field;

[0105] a context acquiring unit, configured to acquire the context of the preset connection field when the preset connection field is identified in the SQL script code;

[0106] A context analysis unit, configured to analyze the context of the preset connection field and identify the operation corresponding to the preset connection field in the SQL script code;

[0107] The connection scenario confirmation unit is used to confirm that a table connection scenario exists in the SQL script code when the preset connection field corresponds to a table connection operation, otherwise no table connection scenario exists.

[0108] In one embodiment, the connection key acquisition module 603 includes:

[0109] a position acquisition unit, configured to acquire position information of a specified keyword in the SQL script code if a table join scenario exists in the SQL script code;

[0110] The clause parsing unit is used to extract the connection clause after the specified keyword according to the position information, and parse the connection clause to obtain the table names involved in the connection and the connection keys between tables.

[0111] In one embodiment, the connection key acquisition module 603 further includes:

[0112] Creation unit, used to create the initial dependency graph;

[0113] The relationship graph generating unit is used to add corresponding nodes and directed edges in the dependency graph according to the table names involved in the connection and the connection keys between tables, so as to generate a corresponding connection dependency graph.

[0114] In one embodiment, the search confirmation module 604 includes:

[0115] A connection confirmation unit, configured to determine all tables involved in the connection and the connection keys between the tables according to the connection key information;

[0116] Partition information acquisition unit, used to obtain partition field information of all tables involved in the connection from database metadata;

[0117] A traversal matching unit, configured to traverse the fields in the inter-table join key, match the traversal result with the partition field information, and determine the matching partition fields in the inter-table join key;

[0118] The search confirmation unit is used to perform a global search on the SQL script code to determine whether there is a screening condition corresponding to the partition field.

[0119] In one embodiment, the risk decision module 605 includes:

[0120] The risk warning unit is used to display the risk warning information of full table scan in the user interface through a pop-up component if there is no filtering condition for the partition field;

[0121] The jump unit is used to automatically jump to the corresponding code position to be modified according to the risk warning information when it is detected that the user triggers the preset repair button on the pop-up component, so as to prompt the user to optimize the SQL script code.

[0122] In one embodiment, the apparatus 60 further includes:

[0123] A risk analysis module is used to analyze the risk points of the risk warning information to obtain a list of risk points and corresponding optimization strategies;

[0124] An optimization filtering module, configured to obtain a pre-configured automatic optimization granularity and filter the risk point list and corresponding optimization strategies according to the automatic optimization granularity;

[0125] The automatic optimization module is used to generate an optimized SQL code snippet based on the filtered risk point list and the corresponding optimization strategy, and insert the optimized SQL code snippet into the corresponding position of the SQL script code or replace the original code at the corresponding position.

[0126] In the above embodiment, the present invention discloses an SQL logic detection and optimization device. The device obtains SQL script code to be detected by responding to a script detection request; identifies table join scenarios in the SQL script code to confirm whether a table join scenario exists in the SQL script code; if a table join scenario exists in the SQL script code, obtains join key information after a specified keyword position; performs a global search on the SQL script code based on the join key information to confirm whether there are any filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputs a full table scan risk warning message to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks.

[0127] Another embodiment of the present invention provides a computer device, such as Figure 7 As shown, the computer device 70 includes:

[0128] One or more processors 701 and memory 702, Figure 7 In the description, a processor 701 is used as an example. The processor 701 and the memory 702 can be connected via a bus or other means. Figure 7 The bus connection is taken as an example.

[0129] The processor 701 is used to complete various control logics of the computer device 70. It can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, discrete gate or transistor logic, discrete hardware components or any combination of these components. In addition, the processor 701 can also be any traditional processor, microprocessor or state machine. The processor 701 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, multiple microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0130] Memory 702, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules, such as program instructions corresponding to the SQL logic detection and optimization method in the embodiments of the present invention. Processor 701 executes the non-volatile software programs, instructions, and modules stored in memory 702 to execute various functional applications and data processing functions of computer device 70, thereby implementing the SQL logic detection and optimization method in the above-mentioned method embodiments.

[0131] The memory 702 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and application programs required for at least one function; the data storage area may store data created according to the use of the computer device 70, etc. In addition, the memory 702 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 702 may optionally include a memory remotely located relative to the processor 701, and these remote memories may be connected to the computer device 70 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof. One or more units are stored in the memory 702, and when executed by one or more processors 701, the steps of the SQL logic detection and optimization method in any of the above-mentioned method embodiments are executed.

[0132] In the above embodiment, the present invention discloses a computer device that, in response to a script detection request, obtains SQL script code to be detected; performs table join scenario identification on the SQL script code to confirm whether a table join scenario exists in the SQL script code; if a table join scenario exists in the SQL script code, obtains join key information after a specified keyword position; performs a global search on the SQL script code based on the join key information to confirm whether there are any filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputs a full table scan risk warning message to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks.

[0133] An embodiment of the present invention provides a non-volatile computer-readable storage medium storing computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the steps of the SQL logic detection and optimization method in any of the above method embodiments are performed.

[0134] In the above embodiment, the present invention discloses a non-volatile computer-readable storage medium that, in response to a script detection request, obtains SQL script code to be detected; performs table join scenario identification on the SQL script code to confirm whether a table join scenario exists in the SQL script code; if a table join scenario exists in the SQL script code, obtains join key information after a specified keyword position; performs a global search on the SQL script code based on the join key information to confirm whether there are filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputs a full table scan risk warning message to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks.

[0135] Through the description of the above embodiments, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus the necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better embodiment. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present invention.

[0136] The present invention can be used in a wide variety of general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and the like. The present invention can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present invention can also be practiced in distributed computing environments in which tasks are performed by remote processing devices connected via a communications network. In a distributed computing environment, program modules can be located in local and remote computer storage media, including storage devices.

[0137] In summary, the present invention discloses a method, apparatus, device, and medium for SQL logic detection and optimization, including: obtaining SQL script code to be detected in response to a script detection request; identifying table join scenarios on the SQL script code to confirm whether a table join scenario exists in the SQL script code; if a table join scenario exists in the SQL script code, obtaining connection key information after a specified keyword position; performing a global search on the SQL script code based on the connection key information to confirm whether there are any filtering conditions for partition fields; if there are no filtering conditions for partition fields, outputting a risk warning message for a full table scan to optimize the SQL script code. By detecting the risk of mechanical full table scans in SQL script code during the development process, the resource consumption risk of the current script is automatically identified to optimize code standardization, thereby improving the timeliness of data processing tasks.

[0138] Of course, those skilled in the art will appreciate that all or part of the processes in the above-described method embodiments can be implemented by instructing related hardware (such as a processor, controller, etc.) through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes in the above-described method embodiments. The storage medium can be a memory, a magnetic disk, a floppy disk, a flash memory, an optical storage device, etc.

[0139] It should be noted that if any software tools or components not developed by our company appear in the examples of this application, they are for illustration purposes only and do not represent actual use. It should be understood that the application of the present invention is not limited to the examples above. Those skilled in the art can make improvements or modifications based on the above description, and all such improvements and modifications shall fall within the scope of protection of the appended claims.

Claims

1. A SQL logic detection and optimization method, characterized in that: include: Responding to a script detection request, obtaining the SQL script code to be detected; Performing table join scenario identification on the SQL script code to confirm whether a table join scenario exists in the SQL script code; If a table join scenario exists in the SQL script code, then the join key information after the specified keyword position is obtained; Perform a global search on the SQL script code based on the connection key information to confirm whether there is a screening condition for the partition field; If there is no screening condition for the partition field, a risk warning message of a full table scan is output to optimize the SQL script code.

2. The SQL logic detection and optimization method according to claim 1, characterized in that: The identifying of a table join scenario on the SQL script code to confirm whether a table join scenario exists in the SQL script code includes: Performing field matching on the SQL script code to identify whether the SQL script code contains a preset connection field; When the preset connection field is identified in the SQL script code, obtaining the context of the preset connection field; Analyze the context of the preset connection field to identify the operation corresponding to the preset connection field in the SQL script code; When the preset connection field corresponds to a table connection operation, it is confirmed that a table connection scenario exists in the SQL script code; otherwise, no table connection scenario exists.

3. The SQL logic detection and optimization method according to claim 1, characterized in that: If a table connection scenario exists in the SQL script code, obtaining connection key information after the specified keyword position includes: If a table join scenario exists in the SQL script code, obtaining position information of a specified keyword in the SQL script code; The connection clause following the designated keyword is extracted according to the position information, and the connection clause is parsed to obtain the table names involved in the connection and the connection keys between the tables.

4. The SQL logic detection and optimization method according to claim 3, characterized in that: After extracting the join clause following the designated keyword based on the location information and parsing the join clause to obtain the table names and inter-table join keys involved in the join, the method further includes: Create an initial dependency graph; According to the table names involved in the connection and the connection keys between tables, corresponding nodes and directed edges are added to the dependency graph to generate a corresponding connection dependency graph.

5. The SQL logic detection and optimization method according to claim 1, characterized in that: The performing a global search on the SQL script code according to the connection key information to confirm whether there is a screening condition for the partition field includes: Determine all tables involved in the connection and the connection keys between tables according to the connection key information; Get the partition field information of all tables involved in the connection from the database metadata; Traversing the fields in the inter-table join key, matching the traversal results with the partition field information, and determining matching partition fields in the inter-table join key; A global search is performed on the SQL script code to determine whether there is a screening condition corresponding to the partition field.

6. The SQL logic detection and optimization method according to claim 1, characterized in that: If there is no screening condition for the partition field, outputting risk warning information of a full table scan to optimize the SQL script code includes: If there are no filter conditions for the partition field, a pop-up window component will be displayed in the user interface to indicate the risk of a full table scan. When it is detected that the user triggers the preset repair button on the pop-up component, it automatically jumps to the corresponding code position to be modified according to the risk warning information to prompt the user to optimize the SQL script code.

7. The SQL logic detection and optimization method according to claim 1, characterized in that: After outputting risk warning information of a full table scan to optimize the SQL script code if there is no screening condition for the partition field, the method further includes: Perform risk point analysis on the risk warning information to obtain a risk point list and corresponding optimization strategies; Obtaining a pre-configured automatic optimization granularity, and filtering the risk point list and corresponding optimization strategies according to the automatic optimization granularity; An optimized SQL code snippet is generated based on the filtered risk point list and the corresponding optimization strategy, and the optimized SQL code snippet is inserted into the corresponding position of the SQL script code or replaces the original code at the corresponding position.

8. A SQL logic detection and optimization device, characterized in that: include: A code acquisition module is used to obtain the SQL script code to be detected in response to a script detection request; A connection scenario recognition module is used to perform table connection scenario recognition on the SQL script code to confirm whether a table connection scenario exists in the SQL script code; A connection key acquisition module is used to obtain connection key information after a specified keyword position if a table connection scenario exists in the SQL script code; A search confirmation module is used to perform a global search on the SQL script code according to the connection key information to confirm whether there is a screening condition for the partition field; The risk decision module is used to output risk prompt information of a full table scan to optimize the SQL script code if there is no screening condition for the partition field.

9. A computer device, characterized in that: comprising at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the SQL logic detection and optimization method according to any one of claims 1 to 7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the SQL logic detection and optimization method according to any one of claims 1 to 7.

Images