Agent-based SQL optimization method, device, equipment and medium

Abstract

The application relates to the technical field of artificial intelligence, and provides an SQL optimization method and device based on an agent, equipment and a medium, which can comprehensively and uniformly manage metadata through a metadata system, automatically query to-be-optimized SQL from the metadata system based on SQL selection rules without manual operation, process the to-be-optimized SQL by using an SQL agent, solve the problem that SQL diagnosis and optimization cannot be realized through coding, perform safety inspection on the to-be-optimized SQL and the optimized SQL to ensure safe diagnosis, perform A / B testing on the rewritten to-be-optimized SQL and the optimized SQL in a mirror environment to avoid data pollution caused by testing, calculate an effect improvement value according to test data, generate a confirmation event for the optimized SQL according to the effect improvement value, realize automatic optimization of the SQL, and improve optimization efficiency and optimization effect.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of artificial intelligence, and in particular to a SQL optimization method and device based on an agent, equipment and a medium. BACKGROUND

[0002] At present, information systems in various fields cannot do without SQL (Structured Query Language) data processing tools. From the traditional relational database MySQL (My Structured Query Language), to the Spark SQL (Spark Structured Query Language) used in big data offline computing, to the Flink SQL (Flink Structured Query Language) provided by the real-time computing framework Flink, and to ClickHouse (ClickHouse Analytical DBMS) for online analytical processing, etc., SQL has become a unified language standard throughout the data processing process.

[0003] In use, various types of SQL face similar problems, either taking too long to execute or consuming too many computing resources, seriously affecting the performance and stability of the system. Therefore, it is necessary to optimize the SQL.

[0004] However, the existing SQL optimization scheme still has many deficiencies, including:

[0005] (1) SQL is constantly changing, it is difficult to define rules to determine whether the writing method is inefficient, so it can only be distinguished by running time, resource consumption and other indicators;

[0006] (2) There may be a large number of "same patterns" in the SQL with problems. Since no aggregation is performed, it is difficult for the business manager to focus and prioritize reasonably;

[0007] (3) After discovering the SQL that needs to be managed, it is difficult to give specific optimization suggestions, and usually the business side needs to understand and modify it themselves, which is time-consuming and laborious;

[0008] (4) The link to complete the optimization is long, and needs to be executed in sequence to understand the SQL, try optimization, compare and verify the effect, finally modify and go online, which is inefficient. SUMMARY

[0009] In view of the above, it is necessary to provide an agent-based SQL optimization method, device, equipment and medium, aiming to solve the problem of poor SQL optimization effect.

[0010] An agent-based SQL optimization method, comprising:

[0011] Collecting multi-dimensional metadata and storing the collected metadata into a metadata system;

[0012] In response to an SQL optimization instruction, querying a to-be-optimized SQL from the metadata system based on SQL selection rules;

[0013] Processing the to-be-optimized SQL by using an SQL agent to obtain output information including an optimized SQL;

[0014] Performing security check on the to-be-optimized SQL and the optimized SQL;

[0015] When the to-be-optimized SQL and the optimized SQL pass the security check, constructing a mirror environment and rewriting the to-be-optimized SQL and the optimized SQL;

[0016] Performing A / B test on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain test data;

[0017] Calculating an effect improvement value of the optimized SQL relative to the to-be-optimized SQL according to the test data;

[0018] Generating a confirmation event for the optimized SQL according to the effect improvement value.

[0019] An agent-based SQL optimization device, comprising:

[0020] A collection unit configured to collect multi-dimensional metadata and store the collected metadata into a metadata system;

[0021] A query unit configured to query a to-be-optimized SQL from the metadata system based on SQL selection rules in response to an SQL optimization instruction;

[0022] A processing unit configured to process the to-be-optimized SQL by using an SQL agent to obtain output information including an optimized SQL;

[0023] A check unit configured to perform security check on the to-be-optimized SQL and the optimized SQL;

[0024] A constructing unit is configured to construct a mirror environment and rewrite the to-be-optimized SQL and the optimized SQL when the to-be-optimized SQL and the optimized SQL pass the security check;

[0025] A testing unit is configured to perform A / B testing on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain testing data;

[0026] A calculating unit is configured to calculate an effect improvement value of the optimized SQL relative to the to-be-optimized SQL according to the testing data;

[0027] A generating unit is configured to generate a confirmation event for the optimized SQL according to the effect improvement value.

[0028] A computer device comprises:

[0029] A memory is configured to store at least one instruction, and a processor is configured to execute the instruction stored in the memory to implement the SQL optimization method based on the agent.

[0030] A computer readable storage medium is configured to store at least one instruction, and the at least one instruction is executed by a processor in a computer device to implement the SQL optimization method based on the agent.

[0031] As can be seen from the above technical solutions, the present application can collect multi-dimensional metadata and store the metadata into a metadata system to realize comprehensive and unified management of the metadata; the to-be-optimized SQL is automatically queried from the metadata system based on a SQL selection rule without human operation; the to-be-optimized SQL is processed by a SQL agent to obtain output information including the optimized SQL, solving the problem that SQL diagnosis and optimization cannot be realized by coding; the to-be-optimized SQL and the optimized SQL are subjected to security check to ensure safe diagnosis; the rewritten to-be-optimized SQL and the optimized SQL are subjected to A / B testing in a mirror environment to avoid data pollution caused by testing; the effect improvement value is calculated according to the testing data, and a confirmation event for the optimized SQL is generated according to the effect improvement value, realizing automatic optimization of the SQL, improving not only the optimization efficiency but also the optimization effect. BRIEF DESCRIPTION OF DRAWINGS

[0032] Figure 1 is a flowchart of a preferred embodiment of the SQL optimization method based on the agent of the present application;

[0033] Figure 2 is a functional module diagram of a preferred embodiment of the SQL optimization device based on the agent of the present application;

[0034] Figure 3is a structural schematic diagram of a computer device of a preferred embodiment of the agent-based SQL optimization method of the present application. DETAILED DESCRIPTION

[0035] In order to make the purposes, technical solutions and advantages of the present application clearer, the present application will be described in detail below with reference to the drawings and specific embodiments.

[0036] As Figure 1 shown, is a flowchart of a preferred embodiment of the agent-based SQL optimization method of the present application. The order of steps in the flowchart can be changed according to different needs, and some steps can be omitted.

[0037] The agent-based SQL optimization method is applied in one or more computer devices, which is a device capable of automatically performing numerical calculation and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0038] The computer device can be any electronic product that can interact with users, such as personal computers, tablet computers, smart phones, personal digital assistants (PDAs), game consoles, interactive Internet Protocol Televisions (IPTVs), smart wearable devices, etc.

[0039] The computer device can also include network devices and / or user devices. The network devices include but are not limited to single network servers, server groups composed of multiple network servers, or clouds composed of a large number of hosts or network servers based on cloud computing.

[0040] The server can be a standalone server, or a cloud server providing cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDNs), and basic cloud computing services such as big data and artificial intelligence platforms.

[0041] Among them, artificial intelligence (Artificial Intelligence, AI) is to use digital computers or digital computer controlled machines to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results. Theory, method, technology and application system.

[0042] The basic technology of artificial intelligence generally includes technologies such as sensors, special artificial intelligence chips, cloud computing, distributed storage, big data processing technology, operation / interaction system, mechatronics, etc. Artificial intelligence software technology mainly includes computer vision technology, robot technology, biometric technology, speech processing technology, natural language processing technology, and machine learning / deep learning, etc. Several major directions.

[0043] The network in which the computer device is located includes but is not limited to the Internet, a wide area network, a metropolitan area network, a local area network, a virtual private network (Virtual Private Network, VPN), etc.

[0044] S10, collect multi-dimensional metadata, and store the collected metadata to a metadata system.

[0045] In this embodiment, the collection of multi-dimensional metadata includes:

[0046] Collect table data and obtain all types of table information and corresponding field information from the table data;

[0047] Collect description information of a historically run SQL (Structured Query Language) task;

[0048] Obtain historical SQL from the description information;

[0049] Use a large language model to perform template extraction on the historical SQL;

[0050] Generate a SQL task execution record according to the description information and the extracted template;

[0051] Generate the metadata according to the all types of table information and corresponding field information and the SQL task execution record.

[0052] Among them, different types of database engines and management systems can be connected to collect the table data. For example, the table data can be directly obtained from the Hive (Hive Data Warehouse Infrastructure) specialized metastore library (metadata database), the MySQL (My Structured Query Language) specialized information_schema library (information schema library), and the ClickHouse (ClickHouse Analytical DBMS) System library (system library).

[0053] Of course, the table data can also be obtained through the interface provided by the engine or management system.

[0054] Among them, the table information can include, but is not limited to, one or a combination of the following information: table name, table description, field information, table size, etc.

[0055] Among them, the description information of the SQL task can include, but is not limited to, one or a combination of the following information: the person in charge of the SQL task, the specific SQL statement executed, the running statistical indicators of the SQL (such as running time, CPU (Central Processing unit) consumption, memory consumption), etc.

[0056] Some SQL tasks have a special task scheduling system (such as HiveSQL (Hive Structured Query Language), SparkSQL (Spark Structured Query Language)), and all task information can be obtained through the interface provided by the system. The running time, CPU and memory consumption of the task can be obtained through the special service execution log (such as Spark Thriftserver).

[0057] Some can also be directly obtained through the execution record of the database, such as MySQL slow query log, and these information are recorded in the special log file.

[0058] Among them, when the large language model is used to extract templates from the historical SQL, the actual needs can be selected to deploy or call the service provider interface.

[0059] Since many SQL patterns are the same, only the parameters are different, the "table", "filter field" used by the SQL are the same, and the possible problems and optimization methods are the same. For example, for the SQL statement: select col1, col2 from db.table_name where col1 = 'value'. The value here will change according to the scene. Such a SQL can be abstracted into a template: select col1, col2 from db.table_name where col1 = '${value}'. The ${value} in it is a placeholder for extracting table information used by the SQL.

[0060] The SQL task execution record can include, but is not limited to: corresponding templates, tables used, resource consumption, and the like.

[0061] In the above embodiment, multiple dimensions of data are collected, which not only enables unified management of data, but also provides a comprehensive data basis for subsequent processing.

[0062] In this embodiment, the metadata system can provide a query interface to facilitate interaction with other systems.

[0063] S11, in response to the SQL optimization instruction, querying the SQL to be optimized from the metadata system based on the SQL selection rule.

[0064] In this embodiment, the SQL optimization instruction can be automatically triggered when it is detected that a SQL is generated.

[0065] In this embodiment, the querying the SQL to be optimized from the metadata system based on the SQL selection rule includes:

[0066] According to the SQL selection rule, determining the execution time threshold and the skew threshold corresponding to each SQL type and business scenario;

[0067] According to the execution time threshold and the skew threshold corresponding to each SQL type and business scenario, performing traversal query in the metadata system;

[0068] When the execution time of the queried SQL is greater than the corresponding execution time threshold, and / or the skew is greater than the corresponding skew threshold, the queried SQL is determined as the SQL to be optimized.

[0069] The SQL type can include, but is not limited to: Spark type, MySQL type, ClickHouse type, and the like.

[0070] It should be noted that the above embodiment is only taken as an example of SparkSQL, and the indicators used for different types of SQLs can also be different, and not all SQL types have skew indicators.

[0071] Of course, in other embodiments, CPU consumption or memory consumption can also be selected as an indicator to query the to-be-optimized SQL according to actual needs.

[0072] For example, for Spark type, if the execution time is more than 3 hours or the skew is more than 5, it is confirmed that the SQL is not healthy and needs to be optimized; for MySQL, if the execution time is more than 500 ms, it is confirmed that the SQL is slow and needs to be optimized.

[0073] Through the above embodiment, the to-be-optimized SQL can be automatically determined without human operation.

[0074] S12, processing the to-be-optimized SQL by using a SQL intelligent agent to obtain output information including an optimized SQL.

[0075] In the embodiment, the SQL intelligent agent can be a large language model, such as a large-scale parameter artificial intelligence model based on a deep learning architecture (Transformer, etc.) pre-trained by a large amount of data, which has generalization ability and can process complex tasks such as natural language understanding and content generation without a large amount of retraining, fine-tuning or receiving instructions.

[0076] In the embodiment, the processing the to-be-optimized SQL by using a SQL intelligent agent to obtain output information including an optimized SQL includes:

[0077] parsing the to-be-optimized SQL by using the SQL intelligent agent to obtain a SQL statement, a SQL type, associated data tables and optimization requirements of the to-be-optimized SQL;

[0078] generating an initial prompt word according to the SQL type;

[0079] reading metadata information of the associated data tables in the metadata system;

[0080] calling a database service to perform an explain operation to obtain execution plan information of the to-be-optimized SQL;

[0081] optimizing the initial prompt word according to the metadata information of the associated data tables and the execution plan information of the to-be-optimized SQL to obtain a target prompt word;

[0082] inputting the target prompt word into the SQL intelligent agent for processing to obtain the optimized SQL;

[0083] performing the explain operation to confirm whether the syntax of the optimized SQL is accurate;

[0084] When it is confirmed that the syntax of the optimized SQL is accurate, the SQL type, the syntax type of the optimized SQL, the associated data table, the metadata information of the associated data table, the execution record of the explain operation, the SQL to be optimized, the optimized SQL, and the high-optimization point are obtained as the output information.

[0085] Among them, the SQL intelligent agent can be visually arranged through Dify, n8n, etc. to meet individual needs and provide an interface for optimizing SQL.

[0086] Among them, the initial prompt word can include, but is not limited to:

[0087] (1) Domain knowledge: specific knowledge under the SQL type, business knowledge, constraints, etc. within the company.

[0088] For example: for Hive tables, the naming specification is: _d, _daily at the end of the day table; _hour, _hourly at the end of the hour table; the partition field name usually has: par, dt, etc. The date format example is: 20250101.

[0089] (2) Best practices. Some writing methods and specifications summarized from experience make SQL more efficient and safer.

[0090] For example: for Spark SQL, the scene of drop first and then create is recommended to use: create if not exists + insert overwrite;

[0091] For example: prohibit using or query. That is, change or of the same field to in, and change different fields to union or union all under the premise of logical equivalence (not applicable in the case of limit, order, etc.).

[0092] (3) Steps and requirements. Frame the reasoning steps to ensure that the required content and results are obtained, and define specific rules and requirements.

[0093] For example: ensure that the problem exists in the SQL is diagnosed under the premise of logical invariance, and is classified as high, medium, and low;

[0094] For example: accuracy first: all optimizations must guarantee 100% logical equivalence, and a verification method is required.

[0095] (4) Prohibited items. Some constraints to avoid big model illusion as much as possible.

[0096] For example: prohibit making table structure assumptions, etc.

[0097] (5) Output format. Define the format and specific content of the results.

[0098] For example: hope it is in json format, what fields, etc.

[0099] Among them, the table name, whether it is a partition table, field list, size, index, etc. related to the associated data table can be read in the metadata system as the metadata information of the associated data table.

[0100] Among them, by executing the explain operation, useful information such as index information used by SQL can be extracted as the execution plan information of the SQL to be optimized.

[0101] Among them, the syntax type can include, but is not limited to: drop, delete, select, etc.

[0102] Among them, the associated data table can include tables that are written and operated.

[0103] Among them, the execution record of the explain operation can include detailed diagnostic information, etc.

[0104] Through the above embodiments, automatic diagnosis of the SQL to be optimized can be realized based on the agent. The abstract ability of the large model makes it possible to extract and diagnose the SQL template that cannot be realized through code, and improves the diagnosis efficiency.

[0105] S13, safety check on the SQL to be optimized and the optimized SQL.

[0106] In this embodiment, the safety check on the SQL to be optimized and the optimized SQL includes:

[0107] Detect whether the preset operation exists in the SQL to be optimized and the optimized SQL;

[0108] When the preset operation does not exist in the SQL to be optimized and the optimized SQL, it is confirmed that the SQL to be optimized and the optimized SQL pass the safety check; or

[0109] When the preset operation exists in the SQL to be optimized and / or the optimized SQL, it is confirmed that the SQL to be optimized and / or the optimized SQL does not pass the safety check, and the subsequent operation is terminated.

[0110] Among them, the preset operation can include high-risk operations such as drop and delete. For such SQL, the process is directly ended.

[0111] The safety of the test can be improved through the safety check before the test.

[0112] S14, when the to-be-optimized SQL and the optimized SQL pass the safety check, constructing a mirror environment and rewriting the to-be-optimized SQL and the optimized SQL.

[0113] For the SQL of the type of "write" and "update", direct execution will pollute the existing data. Therefore, a mirror environment needs to be prepared first.

[0114] Specifically, the constructing a mirror environment comprises:

[0115] obtaining a data table of a to-be-executed SQL operation from the metadata system as a target table;

[0116] obtaining the SQL type of the to-be-optimized SQL and the optimized SQL as a target SQL type;

[0117] creating a mirror table consistent with the structure of the target table according to the target SQL type;

[0118] synchronizing the data in the target table to the mirror table.

[0119] For example, taking Spark as an example, for the SQL: insert overwrite table dw.dwd_track_logselect, a mirror table: dev.test__dw__dwd_track_log needs to be established, and then the SQL is rewritten as insert overwrite table dev.test__dw__dwd_track_log select.

[0120] Through the above embodiment, it can be ensured that the original data will not be rewritten due to the test operation.

[0121] S15, performing A / B test on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain test data.

[0122] In this embodiment, according to the generated unique test task identifier, the test can be carried out according to the given number of rounds, time interval, parallel or serial.

[0123] In this embodiment, after obtaining the test data, the running results of the to-be-optimized SQL and the optimized SQL can also be consistency checked to ensure that they are consistent before performing subsequent operations.

[0124] The verification dimensions can include the number of rows, the number of de-duplication of each field, the maximum value, the minimum value, etc.

[0125] In this embodiment, for the same SQL (as the extracted template), no repeated comparison is needed.

[0126] This embodiment realizes real effect comparison through A / B testing, avoids "false optimization" caused by large model illusion, realizes the whole process automatically, and improves processing efficiency.

[0127] S16, calculating an effect improvement value of the optimized SQL relative to the SQL to be optimized according to the test data.

[0128] In this embodiment, the calculation of the effect improvement value of the optimized SQL relative to the SQL to be optimized according to the test data comprises:

[0129] Obtaining a first time consumption, a first CPU consumption and a first skewness of the SQL to be optimized from the test data;

[0130] Obtaining a second time consumption, a second CPU consumption and a second skewness of the optimized SQL from the test data;

[0131] Calculating the quotient of the first time consumption and the second time consumption to obtain a first value;

[0132] Calculating the quotient of the first CPU consumption and the second CPU consumption to obtain a second value;

[0133] Calculating the product of the first value and the second value to obtain an efficiency improvement value;

[0134] Calculating the quotient of the first skewness and the second skewness to obtain a health improvement value;

[0135] Obtaining a first weight corresponding to the efficiency improvement value and a second weight corresponding to the health improvement value;

[0136] Calculating the weighted sum of the efficiency improvement value and the health improvement value according to the first weight and the second weight to obtain the effect improvement value.

[0137] Wherein, the higher the effect improvement value is, the better the improvement effect is.

[0138] It should be noted that the above embodiments are also only examples of SparkSQL, and the indicators used for different types of SQL may also be different (such as not all SQL types have skewness indicators), and the actual attributes and concerns of SQL can be selected according to the indicators, so that the calculated effect improvement value is more accurate.

[0139] Of course, the running consumption can also be collected, and for each SQL, the running time consumption is recorded, and the key indicators of the corresponding type of database are collected. For example: for Spark, CPU consumption, memory consumption and other indicators are important; for MySQL, CPU time, lock waiting time, I / O (Input / Output) waiting time is more critical.

[0140] S17, generating a confirmation event for the optimized SQL according to the effect improvement value.

[0141] Among them, the improvement threshold (such as 50%) can be configured, and the effect improvement value and the improvement threshold are compared, and according to the comparison result, it is determined whether there is improvement, small improvement, large improvement or no improvement (worsening). Further, according to different situations, the generation of the confirmation event is executed, so as to perform subsequent operations in a targeted manner when a certain degree of improvement is reached.

[0142] Among them, the confirmation event can be determined according to the associated database type, basic system condition and the like.

[0143] For example, the confirmation event can be to automatically generate a new task, support confirmation through a work order, and one-key offline old task and online new task.

[0144] Through the above embodiment, the SQLs that need to be optimized can be automatically screened, optimization suggestions can be obtained, and the effect can be tested and verified, which simplifies the optimization process and reduces the labor cost, not only improves the optimization efficiency, but also improves the optimization effect, and effectively promotes the business transformation optimization.

[0145] From the above technical solutions, it can be seen that the present application can collect multi-dimensional metadata and store it in a metadata system to realize comprehensive and unified management of the metadata; automatically query the SQL to be optimized from the metadata system based on the SQL selection rule without human operation; process the SQL to be optimized by using the SQL intelligent agent to obtain output information including the optimized SQL, solve the problem that SQL diagnosis and optimization cannot be realized through coding; perform safety inspection on the SQL to be optimized and the optimized SQL to ensure safe diagnosis; perform A / B test on the rewritten SQL to be optimized and the optimized SQL in the mirror environment to avoid data pollution caused by testing; calculate the effect improvement value according to the test data, and generate a confirmation event for the optimized SQL according to the effect improvement value, which realizes the automatic optimization of the SQL, not only improves the optimization efficiency, but also improves the optimization effect.

[0146] For example, Figure 2As shown, it is the function module diagram of the preferred embodiment of the SQL optimization device based on the agent of the application. The SQL optimization device based on the agent 11 includes an acquisition unit 110, a query unit 111, a processing unit 112, a checking unit 113, a construction unit 114, a test unit 115, a calculation unit 116, and a generation unit 117. The module / unit referred to in the application refers to a series of computer program segments capable of being executed by a processor and capable of completing a fixed function, which is stored in a memory. In this embodiment, the functions of each module / unit will be described in detail in the subsequent embodiments.

[0147] The acquisition unit 110 is used for acquiring multi-dimensional metadata and storing the acquired metadata to a metadata system.

[0148] The query unit 111 is used for querying a to-be-optimized SQL from the metadata system based on a SQL selection rule in response to a SQL optimization instruction.

[0149] The processing unit 112 is used for processing the to-be-optimized SQL by using a SQL agent to obtain output information including an optimized SQL.

[0150] The checking unit 113 is used for performing security checking on the to-be-optimized SQL and the optimized SQL.

[0151] The construction unit 114 is used for constructing a mirror environment and rewriting the to-be-optimized SQL and the optimized SQL when the to-be-optimized SQL and the optimized SQL pass the security checking.

[0152] The test unit 115 is used for performing A / B testing on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain test data.

[0153] The calculation unit 116 is used for calculating an effect improvement value of the optimized SQL relative to the to-be-optimized SQL according to the test data.

[0154] The generation unit 117 is used for generating a confirmation event for the optimized SQL according to the effect improvement value.

[0155] From the above technical scheme can be seen, the present application can collect multi-dimensional metadata and store to the metadata system, to realize the comprehensive and unified management to the metadata; based on the SQL selection rule from the metadata system automatically query the SQL to be optimized, without human operation; using SQL intelligent agent to process the SQL to be optimized, get the output information including the optimized SQL, solve the originally unable to realize SQL diagnosis and optimization through coding problem; the security check is carried out to the SQL to be optimized and the optimized SQL, to ensure safe diagnosis;In the mirror environment, the rewritten SQL to be optimized and the optimized SQL are subjected to A / B test, avoid data pollution due to test;According to the test data, the effect improvement value is calculated, and the confirmation event of the optimized SQL is generated according to the effect improvement value, realizes the automatic optimization of SQL, not only improves the optimization efficiency, also improves the optimization effect.

[0156] As Figure 3 Fig. 1 is a schematic diagram of the computer device according to the preferred embodiment of the present application.

[0157] The computer device 1 can include a memory 12, a processor 13 and a bus (the arrow in the figure is the bus), and can also include a computer program stored in the memory 12 and executable on the processor 13, such as an agent-based SQL optimization program.

[0158] Those skilled in the art can understand that the schematic diagram is only an example of the computer device 1 and does not constitute a limitation on the computer device 1, which can be a bus type structure or a star type structure, and the computer device 1 can also include more or less other hardware or software, or different component arrangements, such as the computer device 1 can also include input / output devices, network access devices, etc.

[0159] It should be noted that the computer device 1 is only an example, and other existing or future electronic products, such as those that can be adapted to the present application, should also be included within the scope of the present application and are hereby incorporated by reference.

[0160] The memory 12 includes at least one type of readable storage medium, such as a flash memory, a mobile hard disk, a multimedia card, a card-type memory (e.g., an SD or DX memory, etc.), a magnetic memory, a disk, an optical disk, etc. In some embodiments, the memory 12 can be an internal storage unit of the computer device 1, such as a mobile hard disk of the computer device 1. In other embodiments, the memory 12 can also be an external storage device of the computer device 1, such as a plug-in mobile hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. Further, the memory 12 can include both an internal storage unit and an external storage device of the computer device 1. The memory 12 can be used to store application software and various data installed in the computer device 1, such as the code of the agent-based SQL optimization program, and can also be used to temporarily store data that has been output or will be output.

[0161] The processor 13 can be composed of an integrated circuit in some embodiments, such as a single packaged integrated circuit or a plurality of packaged integrated circuits with the same or different functions, including one or more combinations of a central processing unit (CPU), a microprocessor, a digital processing chip, a graphics processor, and various control chips, etc. The processor 13 is a control unit of the computer device 1, which connects various components of the computer device 1 through various interfaces and lines, executes programs or modules stored in the memory 12 (e.g., executes the agent-based SQL optimization program, etc.), and calls data stored in the memory 12 to perform various functions and process data of the computer device 1.

[0162] The processor 13 executes an operating system and various application programs installed in the computer device 1. The processor 13 executes the application programs to implement the steps in each of the above agent-based SQL optimization method embodiments, such as Figure 1 the steps shown above.

[0163] By way of example, the computer program can be divided into one or more modules / units, which are stored in the memory 12 and executed by the processor 13 to accomplish the present application. The one or more modules / units can be a series of computer-readable instruction segments capable of accomplishing a specific function, which are used to describe the execution process of the computer program in the computer device 1. For example, the computer program can be divided into an acquisition unit 110, a query unit 111, a processing unit 112, a checking unit 113, a construction unit 114, a testing unit 115, a calculation unit 116, and a generation unit 117.

[0164] The integrated units implemented in the form of software functional modules described above can be stored in a computer-readable storage medium. The software functional modules described above stored in a storage medium include a plurality of instructions for causing a computer device (which can be a personal computer, a computer device, or a network device, etc.) or a processor to execute part of the agent-based SQL optimization method described in various embodiments of the present application.

[0165] The modules / units integrated in the computer device 1, if implemented in the form of software functional units and sold or used as independent products, can be stored in a computer-readable storage medium. Based on such understanding, the present application can also be implemented by a computer program to instruct related hardware devices to complete all or part of the processes of the above-described embodiments. The computer program can be stored in a computer-readable storage medium and executed by a processor to implement the steps of the above-described various method embodiments.

[0166] The computer program includes computer program code, which can be in the form of source code, object code, executable files, or some intermediate forms. The computer-readable medium can include any entity or device capable of carrying the computer program code, recording media, U disks, mobile hard disks, magnetic disks, optical disks, computer memories, read-only memories (ROM), random access memories, and the like.

[0167] Further, the computer-readable storage medium can mainly include a program storage area and a data storage area. The program storage area can store an operating system, at least one application required for a function, and the like. The data storage area can store data created according to the use of the blockchain node, and the like.

[0168] The blockchain referred to in the present application is a new application mode of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, and encryption algorithm. The blockchain is essentially a decentralized database, and is a series of data blocks associated using cryptographic methods. Each data block contains information about a batch of network transactions, and is used to verify the validity of the information (anti-fake) and generate the next block. The blockchain can include a blockchain underlying platform, a platform product service layer, and an application service layer.

[0169] The bus can be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one straight line is used in the drawings, but this does not mean that there is only one bus or only one type of bus. The bus is provided to realize the connection and communication between the memory 12, the at least one processor 13, etc. Figure 3

[0170] Although not shown, the computer device 1 can also include a power supply (such as a battery) for powering the various components. Preferably, the power supply can be logically connected to the at least one processor 13 through a power management device, so that the power management device can realize functions such as charge management, discharge management, and power consumption management. The power supply can also include one or more direct current or alternating current power supplies, recharging devices, power failure detection circuits, power converters or inverters, power status indicators, etc. The computer device 1 can also include various sensors, Bluetooth modules, Wi-Fi modules, etc., which will not be described here.

[0171] Further, the computer device 1 can also include a network interface. Optionally, the network interface can include a wired interface and / or a wireless interface (such as a WI-FI interface, a Bluetooth interface, etc.), which is usually used to establish a communication connection between the computer device 1 and other computer devices.

[0172] ​Optionally, the computer device 1 can further comprise a user interface, which can be a display, an input unit such as a keyboard, and optionally a standard wired interface, a wireless interface. Optionally, in some embodiments, the display can be an LED display, a liquid crystal display, a touch liquid crystal display, an OLED touch, etc. The display can also be appropriately referred to as a display screen or a display unit, for displaying information processed in the computer device 1 and for displaying a visualized user interface.

[0173] It should be understood that the embodiments are only for illustration and are not limited in the scope of the patent application by the structure.

[0174] It can be understood by those skilled in the art that, Figure 3 The structure shown does not constitute a limitation on the computer device 1, and can include fewer or more components than shown, or combine certain components, or different component arrangements.

[0175] In combination Figure 1 The memory 12 in the computer device 1 stores a plurality of instructions to implement an agent-based SQL optimization method, and the processor 13 can execute the plurality of instructions to implement:

[0176] Collecting multi-dimensional metadata and storing the collected metadata into a metadata system;

[0177] In response to an SQL optimization instruction, querying a to-be-optimized SQL from the metadata system based on SQL selection rules;

[0178] Processing the to-be-optimized SQL using an SQL agent to obtain output information including an optimized SQL;

[0179] Performing security checks on the to-be-optimized SQL and the optimized SQL;

[0180] When the to-be-optimized SQL and the optimized SQL pass the security checks, constructing a mirror environment and rewriting the to-be-optimized SQL and the optimized SQL;

[0181] Performing A / B testing on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain test data;

[0182] Calculating an effect improvement value of the optimized SQL relative to the to-be-optimized SQL according to the test data;

[0183] Generate a confirmation event for the optimized SQL according to the effect promotion value.

[0184] Specifically, the processor 13 can refer to the specific implementation method of the above instructions Figure 1 The description of the related steps in the corresponding embodiments will not be repeated here.

[0185] It should be noted that the data involved in the case are all legally obtained. The non-company software tools or components appearing in the embodiments of the application are only examples for introduction and do not represent actual use.

[0186] In several embodiments provided by the present application, it should be understood that the disclosed system, device and method can be implemented by other ways. For example, the device embodiments described above are only schematic, and for example, the division of the modules is only a logical function division, and there can be other division manners in actual implementation.

[0187] The application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, multi-processor 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, etc. The application 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 application can also be practiced in a distributed computing environment, in which tasks are performed by remote processing devices connected by a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media, including storage devices.

[0188] The modules described as separate components can or can not be physically separated, and the components displayed as modules can or can not be physical units, i.e. they can be located in one place or distributed to multiple network units. Part or all of the modules can be selected according to actual needs to achieve the purpose of the embodiment.

[0189] In addition, each functional module in each embodiment of the application can be integrated in one processing unit, or each unit can exist physically independently, or two or more units can be integrated in one unit. The integrated unit can be realized in the form of hardware or in the form of hardware plus software functional modules.

[0190] It will be obvious to a person skilled in the art that the application is not limited to the details of the above-described exemplary embodiments but can be implemented in other embodiments without departing from the scope of the application.

[0191] The embodiments should therefore be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the description given above, so that all changes coming within the meaning and equivalency range of the claims are intended to be embraced therein. Any reference signs in the claims should not be construed as limiting the scope of the claims.

[0192] Furthermore, it is clear that the word "comprising" does not exclude other elements or steps, and the singular does not exclude the plural. Multiple units or devices also can be presented by a single unit or device, for example by software or hardware. The use of relative terms like "first", "second" and the like is used for naming without necessarily implying any specific order.

[0193] Finally, it should be noted that the above-described embodiments are merely intended to illustrate the technical solutions of the present application, rather than limit the scope of the present application. Although the present application has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present application can be modified or equivalent replaced without departing from the spirit and scope of the present application.

Claims

1. An agent-based SQL optimization method, characterized in that, The agent-based SQL optimization method comprises the following steps: Collecting multi-dimensional metadata and storing the collected metadata into a metadata system; In response to an SQL optimization instruction, querying a to-be-optimized SQL from the metadata system based on an SQL selection rule; Processing the to-be-optimized SQL by using an SQL agent to obtain output information comprising an optimized SQL, including: parsing the to-be-optimized SQL by using the SQL agent to obtain an SQL statement, an SQL type, associated data tables and optimization requirements of the to-be-optimized SQL; generating an initial prompt word according to the SQL type; reading metadata information of the associated data tables in the metadata system; calling a database service to perform an explain operation to obtain execution plan information of the to-be-optimized SQL; optimizing the initial prompt word according to the metadata information of the associated data tables and the execution plan information of the to-be-optimized SQL to obtain a target prompt word; inputting the target prompt word into the SQL agent for processing to obtain the optimized SQL; performing the explain operation to confirm whether the syntax of the optimized SQL is accurate; when it is confirmed that the syntax of the optimized SQL is accurate, obtaining the SQL type, the syntax type of the optimized SQL, the associated data tables, the metadata information of the associated data tables, the execution record of the explain operation, the to-be-optimized SQL, the optimized SQL and a high-optimization point as the output information; Performing security checks on the to-be-optimized SQL and the optimized SQL; When the to-be-optimized SQL and the optimized SQL pass the security checks, constructing a mirror environment and rewriting the to-be-optimized SQL and the optimized SQL; Performing A / B testing on the rewritten to-be-optimized SQL and the optimized SQL in the mirror environment to obtain test data; According to the test data, calculating an effect improvement value of the optimized SQL relative to the to-be-optimized SQL; According to the effect improvement value, generating a confirmation event for the optimized SQL.

2. The agent-based SQL optimization method of claim 1, wherein, The collection of multi-dimensional metadata comprises the following steps: Collecting table data and obtaining all types of table information and corresponding field information from the table data; Collecting description information of a historically run SQL task; Obtaining historical SQL from the description information; Using a large language model to perform template extraction on the historical SQL; Generating an SQL task execution record according to the description information and the extracted template; Generating the metadata according to all types of table information and corresponding field information and the SQL task execution record.

3. The agent-based SQL optimization method of claim 1, wherein, The querying of the to-be-optimized SQL from the metadata system based on the SQL selection rule comprises the following steps: Determining execution time threshold values and inclination threshold values corresponding to each SQL type and business scenario according to the SQL selection rule; According to the execution time threshold values and inclination threshold values corresponding to each SQL type and business scenario, performing traversal queries in the metadata system; When the execution duration of the queried SQL is greater than a corresponding execution duration threshold and / or the skew is greater than a corresponding skew threshold, the queried SQL is determined as the SQL to be optimized.

4. The agent-based SQL optimization method of claim 1, wherein, The security check on the SQL to be optimized and the optimized SQL includes: detecting whether a preset operation exists in the SQL to be optimized and the optimized SQL; when the preset operation does not exist in the SQL to be optimized and the optimized SQL, confirming that the SQL to be optimized and the optimized SQL pass the security check; or when the preset operation exists in the SQL to be optimized and / or the optimized SQL, confirming that the SQL to be optimized and / or the optimized SQL do not pass the security check, and terminating the execution of subsequent operations.

5. The agent-based SQL optimization method of claim 1, wherein, The construction of the mirror environment includes: obtaining a data table of a SQL operation to be executed from the metadata system as a target table; obtaining a SQL type of the SQL to be optimized and the optimized SQL as a target SQL type; creating a mirror table consistent with the structure of the target table according to the target SQL type; synchronizing data in the target table to the mirror table.

6. The agent-based SQL optimization method of claim 1, wherein, The calculation of the effect improvement value of the optimized SQL relative to the SQL to be optimized according to the test data includes: obtaining a first time consumption, a first CPU consumption and a first skew of the SQL to be optimized from the test data; obtaining a second time consumption, a second CPU consumption and a second skew of the optimized SQL from the test data; calculating a quotient of the first time consumption and the second time consumption to obtain a first value; calculating a quotient of the first CPU consumption and the second CPU consumption to obtain a second value; calculating a product of the first value and the second value to obtain an efficiency improvement value; calculating a quotient of the first skew and the second skew to obtain a health improvement value; obtaining a first weight corresponding to the efficiency improvement value and a second weight corresponding to the health improvement value; calculating a weighted sum of the efficiency improvement value and the health improvement value according to the first weight and the second weight to obtain the effect improvement value.

7. An agent-based SQL optimization apparatus, comprising: The SQL optimization device based on an agent includes: a collection unit configured to collect multi-dimensional metadata and store the collected metadata to a metadata system; a query unit configured to query a SQL to be optimized from the metadata system based on a SQL selection rule in response to a SQL optimization instruction; The processing unit is configured to process the SQL to be optimized by using a SQL agent to obtain output information including an optimized SQL, including: parsing the SQL to be optimized by using the SQL agent to obtain an SQL statement, an SQL type, associated data tables, and optimization requirements of the SQL to be optimized; generating an initial prompt word according to the SQL type; reading metadata information of the associated data tables in the metadata system; calling a database service to perform an explain operation to obtain execution plan information of the SQL to be optimized; optimizing the initial prompt word according to the metadata information of the associated data tables and the execution plan information of the SQL to be optimized to obtain a target prompt word; inputting the target prompt word into the SQL agent for processing to obtain the optimized SQL; performing the explain operation to confirm whether the syntax of the optimized SQL is accurate; when it is confirmed that the syntax of the optimized SQL is accurate, obtaining the SQL type, a syntax type of the optimized SQL, the associated data tables, the metadata information of the associated data tables, execution records of the explain operation, the SQL to be optimized, the optimized SQL, and a high-optimization point as the output information; The checking unit is configured to perform security checking on the SQL to be optimized and the optimized SQL. The building unit is configured to build a mirror environment and rewrite the SQL to be optimized and the optimized SQL when the SQL to be optimized and the optimized SQL pass the security checking. The testing unit is configured to perform A / B testing on the rewritten SQL to be optimized and the optimized SQL in the mirror environment to obtain testing data. The computing unit is configured to calculate an effect improvement value of the optimized SQL relative to the SQL to be optimized according to the testing data. The generating unit is configured to generate a confirmation event for the optimized SQL according to the effect improvement value.

8. A computer device, comprising: The computer device includes: The memory stores at least one instruction; and the processor executes the instruction stored in the memory to implement the SQL optimization method based on the agent according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores at least one instruction, and the at least one instruction is executed by the processor in the computer device to implement the SQL optimization method based on the agent according to any one of claims 1 to 6.

Images