An optimization method and device for SQL scripts

Abstract

An embodiment of the present invention provides a method and device for optimizing an SQL script. The method includes intercepting the SQL script when a trigger action for executing the SQL script is detected, and identifying the intercepted SQL script through an index mapping library to determine whether the SQL script belongs to a script with multiple index information. If so, based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information, the first index information required for executing the SQL script is determined, and the SQL script is adjusted based on the first index information to generate an SQL script that needs to be executed in an SQL execution engine. In this way, when it is identified that there are multiple index problems in the SQL script, the SQL script is automatically optimized through the selected optimal index information, so that the problem of incorrect index selection can be effectively avoided, and the execution efficiency of the SQL script can be effectively improved.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of financial technology (Fintech), and in particular, to a method and device for optimizing SQL scripts. Background Art

[0002] With the development of computer technology, more and more technologies are applied in the financial field. The traditional financial industry is gradually transforming into financial technology. However, due to the security and real-time requirements of the financial industry, higher requirements are also put forward for technologies. For the relevant business data involved in the financial business system, they are all stored in the database. Among them, for the relevant operations on the corresponding business data, the method of connecting to the database is adopted to write the corresponding SQL (Structured Query Language) scripts, and according to the specific business logic, the fields in the database table are added, deleted, modified, and queried.

[0003] However, in the related technologies, in order to improve the performance of database operations, an index is established for the specified fields of each relevant database table. Among them, in actual business, there may be different business logics, so there may be different SQL scripts, and thus there may be different index information created for different conditional fields, which may lead to multiple index information for one table. In this way, when executing the SQL script, the database engine will select index fields from the index information of the database table associated with the SQL script according to a certain strategy based on the conditional information of the SQL script. However, since the index selection is determined by the database execution engine, in the corresponding operations on the SQL script, business developers cannot perceive whether multiple indexes are hit, so the problem of misselecting indexes will occur, resulting in low execution efficiency of the SQL script.

[0004] In summary, there is an urgent need for a method for optimizing SQL scripts to effectively avoid the problem of misselecting indexes and thus effectively improve the execution efficiency of SQL scripts. Summary of the Invention

[0005] The embodiments of the present invention provide a method and device for optimizing SQL scripts to effectively avoid the problem of misselecting indexes and thus effectively improve the execution efficiency of SQL scripts.

[0006] In a first aspect, the embodiments of the present invention provide a method for optimizing a SQL script, including:

[0007] When it is detected that the execution of a Structured Query Language (SQL) script triggers an action, intercept the SQL script, and identify the intercepted SQL script through an index mapping library to determine whether the SQL script belongs to a script with multi-index information; the index mapping library is used to indicate the mapping relationship between the hash value of each SQL script and the corresponding m index information;

[0008] If it is determined that the SQL script belongs to a script with multi-index information, determine the first index information required for executing the SQL script based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information; wherein, the discrimination degree of each index field is used to characterize the proportion of the number of different business data values of the index field in the database table where the corresponding index information is located;

[0009] Adjust the SQL script based on the first index information to generate a SQL script for execution in the SQL execution engine.

[0010] In the above technical solution, the technical solution of the present invention can detect in real time the execution trigger action of the SQL script, and can timely and accurately identify whether the SQL script hits multiple index information through the index mapping library. According to the discrimination degrees (i.e., the data distribution characteristics of each index field in its associated database table) of each index field included in the multiple index information hit by the SQL script, the most suitable index information can be selected from the multiple index information to adjust the SQL script (i.e., optimize the SQL script through the most suitable index information), thereby effectively improving the execution efficiency of the SQL script and effectively avoiding the problem of incorrect index selection when selecting an index. Specifically, when detecting the execution trigger action of the SQL script, it can effectively intercept the SQL script, and at the same time, the intercepted SQL script can be identified through the index mapping library, so as to accurately determine whether the SQL script belongs to a script with multiple index information, that is, to determine whether there is a multiple index problem with the SQL script. When it is determined that the SQL script belongs to a script with multiple index information, based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information, the first index information required when executing the SQL script can be determined. The first index information is the most suitable index information selected. Then, through the first index information, the SQL script can be automatically adjusted (which can also be understood as automatically optimized), so that the SQL execution engine executes the adjusted SQL script. In this way, this solution can automatically identify whether the SQL script to be executed has multiple indexes, and automatically optimize the SQL script with multiple indexes, that is, select the most suitable index information according to a certain index selection strategy and automatically adjust the SQL script so that the SQL script uses the optimized index information, thus effectively solving the problem of incorrect index selection caused by the existence of multiple indexes in the SQL script in the prior art, and making the execution speed of the optimized SQL script faster, thereby effectively improving the execution efficiency of the SQL script. In addition, it should be noted that since the technical solution of the present invention also dynamically selects the most suitable index information in real time according to the discrimination degrees of each index field included in the multiple index information hit by the SQL script (the SQL script is included in the application program code) to achieve dynamic adjustment of the SQL script, without repairing the application program code where the SQL script is located, it can avoid repairing the application program code where the SQL script is located, thereby effectively reducing the repair cost of the problem of incorrect index selection for the SQL script.

[0011] Optionally, identifying the intercepted SQL script through the index mapping library to determine whether the SQL script belongs to a script with multi-index information includes:

[0012] Determining the first hash value of the SQL script;

[0013] Matching m corresponding index information from the index mapping library through the first hash value of the SQL script;

[0014] If the quantity of the m index information meets the quantity threshold, determine that the SQL script belongs to a script with multi-index information; if the quantity of the m index information does not meet the quantity threshold, determine that the SQL script does not belong to a script with multi-index information.

[0015] In the above technical solution, since the index mapping library pre-stores the mapping relationship between the hash value of each SQL script and the corresponding at least one index information, then through the index mapping library, it can be timely and accurately determined whether a certain SQL script to be executed will hit multiple indexes. That is, based on the existing SQL script information, the SQL script is analyzed to identify whether the SQL script hits multiple indexes, so as to facilitate accurately selecting the optimal index information for optimizing the SQL script (that is, specifying the optimal index information for the SQL script to use when running). Specifically, m corresponding index information is matched from the index mapping library through the hash value of a certain SQL script to be executed, and it is judged whether the quantity of the m index information meets the quantity threshold. When the quantity threshold is met, it is determined that the SQL script to be executed hits multiple indexes; otherwise, it is considered that the SQL script to be executed does not hit multiple indexes and can be directly run.

[0016] Optionally, the determining the first hash value of the SQL script includes:

[0017] Performing a formatted template extraction process on the SQL script through a SQL parameterized template class function to obtain the first SQL template information corresponding to the SQL script;

[0018] Performing a normalization process on the first SQL template information to obtain the second SQL template information;

[0019] Performing a hash operation on the second SQL template information to determine the first hash value of the SQL script.

[0020] In the above technical solution, during the actual operation of the business system table, the SQL business logic of the system basically does not change. Only the actual value information of the relevant business parameters in the logic changes. Therefore, in the business development code, there will be relevant SQL template statements. Thus, in order to improve the efficiency of multi-index judgment for SQL scripts, the SQL scripts will be normalized. Multiple SQL scripts with different parameter values but the same business logic are integrated into one SQL template, and then a hash operation is performed on the SQL template to determine the hash value of the SQL template. That is, the hash values corresponding to multiple SQL scripts with different parameter values but the same business logic are the same, and the index information corresponding to multiple SQL scripts with different parameter values but the same business logic is also the same. That is, only one hash value and the m index information corresponding to this hash value need to be stored in the index mapping library, thereby effectively improving the matching efficiency of whether the upcoming SQL script hits multiple indexes in the future.

[0021] Optionally, the normalization process of the first SQL template information to obtain the second SQL template information includes:

[0022] If there are capital letters in the first SQL template information, convert the capital letters to lowercase letters to obtain the converted first SQL template information;

[0023] If there is dynamic value information in the converted first SQL template information, replace the dynamic value information with a set replacement value to obtain the second SQL template information.

[0024] In the above technical solution, in order to accurately integrate multiple SQL scripts with different parameter values but the same business logic into one SQL template, the SQL scripts after formatted template extraction processing will be normalized. That is, when it is determined that there are capital letters in the first SQL template information, the capital letters will be converted to lowercase letters to obtain the converted first SQL template information. And when it is determined that there is dynamic value information in the converted first SQL template information, the dynamic value information can be replaced with a set replacement value, so as to achieve the format unification of the SQL template information, thereby avoiding a greater impact on the business execution efficiency when analyzing SQL scripts with different parameter values.

[0025] Optionally, the index mapping library is established by the following method:

[0026] For any SQL script, analyze and process the SQL script to obtain the third SQL template information corresponding to the SQL script, and perform a hash operation on the third SQL template information to determine the second hash value of the SQL script;

[0027] Perform word segmentation on the SQL script to obtain the data source information and condition field information corresponding to the SQL script, perform database table information extraction processing on the data source information corresponding to the SQL script to obtain each database table information arranged in order, and perform parsing processing on the condition field information corresponding to the SQL script to obtain each condition field information arranged in order; associate each database table information with each condition field information arranged in order to generate a database table condition field set; the database table condition field set includes each database table information arranged in order and at least one condition field information arranged in order associated with each database table information;

[0028] For each database table information, determine the database corresponding to the database table information, and obtain each index information arranged in order that the database table information has from the database; each index information includes at least one index field information arranged in order;

[0029] Match each condition field information associated with the database table information with at least one index field information arranged in order included in each index information arranged in order that the database table information has in turn, and use the at least one index information that matches successfully as the second index information for determining whether the SQL script belongs to a script with multi-index information;

[0030] Store the second hash values of each SQL script and at least one second index information corresponding to each second hash value into the index mapping library.

[0031] In the above technical solution, in order to improve the recognition performance of whether there is multi-index information in the SQL script to be executed, it is necessary to perform multi-index analysis on each SQL script in advance, and at the same time, it is necessary to analyze the index information and the associated index field information associated with each SQL script, that is, to analyze in advance whether each SQL script in each SQL script hits multi-index, that is, to determine the hash values of each SQL template, and to determine at least one index information that the database table information corresponding to each SQL template has, so as to generate a mapping relationship between each hash value and at least one index information corresponding to each hash value, so as to provide effective support for identifying whether there is multi-index information in the SQL script to be executed subsequently.

[0032] Optionally, determining the first index information required for executing the SQL script based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information includes:

[0033] For any index information corresponding to the SQL script, determine the database table where the index information is located and at least one index field included in the index information;

[0034] For each index field, determine the discrimination degree of the index field in the database table where the index information is located;

[0035] Compare the discrimination degrees of each index field included in the multiple index information, determine the maximum discrimination degree, and determine the first index field corresponding to the maximum discrimination degree;

[0036] Determine the index information corresponding to the first index field as the first index information required for executing the SQL script.

[0037] In the above technical solution, the optimal index information is dynamically selected based on the real-time data distribution of each index field and the corresponding at least one index information. Among them, the selected index may also be different according to the real-time data distribution of the index field, because the dynamically changing data distribution will make the selected optimal index information may also change. Therefore, the technical solution in the present invention analyzes the optimal index information by combining the actual data distribution of each index field and comparing the discrimination degrees of different index fields in the database table where they are located, and adjusts the upcoming SQL script through the optimal index information, so that the adjusted SQL script can perform corresponding execution operations according to the optimal index information.

[0038] Optionally, the discrimination degree of each index field is determined by the following method:

[0039] For each index field, count the first quantity of the total number of business data in the database table where the index information corresponding to the index field is located;

[0040] Deduplicate each business data value of the index field in the database table where the index information is located, and count the second quantity of at least one business data value after deduplication;

[0041] Determine the ratio of the second quantity to the first quantity as the discrimination degree of the index field in the database table where the index information is located.

[0042] In the above technical solution, for each index field, it is necessary to count the first quantity of the total number of business data contained in the database table where the index information corresponding to the index field is located, and perform deduplication processing on each business data value of the index field in the database table where the corresponding index information is located, so as to obtain the second quantity of at least one business data value after deduplication. Then, by performing corresponding operations on the second quantity and the first quantity, the discrimination degree of the index field in the database table where the corresponding index information is located can be accurately calculated, so as to provide effective data support for dynamically selecting the optimal index information subsequently.

[0043] Optionally, by adjusting the sql script based on the first index information, an sql script for execution in the sql execution engine is generated, including:

[0044] Assemble the first index information with the forced execution instruction symbol to obtain the assembled first index information;

[0045] Insert the assembled first index information between the database table key field of the sql script and the conditional key field of the sql script to generate an adjusted sql script, and execute the adjusted sql script through the sql execution engine.

[0046] In the above technical solution, when it is necessary to adjust the sql script to be executed, a specified index information will be added after the relevant table name. That is, the first index information is assembled with the forced execution instruction symbol, and the assembled index information is inserted between the relevant table name and the conditional key field, so that the sql execution engine can forcibly reference the index information, that is, the sql execution engine performs corresponding execution operations on the sql script according to the optimal index information.

[0047] In a second aspect, an embodiment of the present invention further provides an optimization device for an sql script, including:

[0048] An identification unit, configured to intercept the sql script when detecting an execution trigger action of a structured query language sql script, and identify the intercepted sql script through an index mapping library to determine whether the sql script belongs to a script with multiple index information; the index mapping library is used to indicate the mapping relationship between the hash values of each sql script and the corresponding m index information;

[0049] A processing unit, which is configured to, if it is determined that the SQL script belongs to a script with multi-index information, determine first index information required for executing the SQL script based on multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information; wherein, the discrimination degree of each index field is used to characterize the proportion of the number of different business data values of the index field in the database table where the corresponding index information is located; and adjust the SQL script based on the first index information to generate a SQL script for execution in a SQL execution engine.

[0050] Optionally, the recognition unit is specifically configured to:

[0051] Determine a first hash value of the SQL script;

[0052] Match m corresponding index information from the index mapping library through the first hash value of the SQL script;

[0053] If the number of the m index information meets a quantity threshold, determine that the SQL script belongs to a script with multi-index information; if the number of the m index information does not meet the quantity threshold, determine that the SQL script does not belong to a script with multi-index information.

[0054] Optionally, the recognition unit is specifically configured to:

[0055] Perform a formatted template extraction process on the SQL script through a SQL parameterized template class function to obtain first SQL template information corresponding to the SQL script;

[0056] Perform a normalization process on the first SQL template information to obtain second SQL template information;

[0057] Perform a hash operation on the second SQL template information to determine the first hash value of the SQL script.

[0058] Optionally, the recognition unit is specifically configured to:

[0059] If there are capital letters in the first SQL template information, convert the capital letters to lowercase letters to obtain the converted first SQL template information;

[0060] If there is dynamic value-taking information in the converted first SQL template information, replace the dynamic value-taking information with a set replacement value to obtain the second SQL template information.

[0061] Optionally, the recognition unit is specifically configured to:

[0062] For any SQL script, analyze and process the SQL script to obtain the third SQL template information corresponding to the SQL script, and perform a hash operation on the third SQL template information to determine the second hash value of the SQL script;

[0063] Perform word segmentation on the SQL script to obtain the data source information and condition field information corresponding to the SQL script, perform database table information extraction processing on the data source information corresponding to the SQL script to obtain each database table information arranged in order, and perform parsing processing on the condition field information corresponding to the SQL script to obtain each condition field information arranged in order; Associate each database table information with each condition field information arranged in order to generate a database table condition field set; The database table condition field set includes each database table information arranged in order and at least one condition field information arranged in order associated with each database table information;

[0064] For each database table information, determine the database corresponding to the database table information, and obtain each index information arranged in order possessed by the database table information from the database; Wherein, each index information includes at least one index field information arranged in order;

[0065] Match each condition field information associated with the database table information with at least one index field information arranged in order included in each index information possessed by the database table information in sequence, and use the at least one index information with successful matching as the second index information for determining whether the SQL script belongs to a script with multi-index information;

[0066] Store the second hash value of each SQL script and at least one second index information corresponding to each second hash value into the index mapping library.

[0067] Optionally, the processing unit is specifically configured to:

[0068] For any index information corresponding to the SQL script, determine the database table where the index information is located and at least one index field included in the index information;

[0069] For each index field, determine the discrimination degree possessed by the index field in the database table where the index information is located;

[0070] Compare the discrimination degrees possessed by each index field included in the multiple index information to determine the maximum discrimination degree, and determine the first index field corresponding to the maximum discrimination degree;

[0071] Determine the index information corresponding to the first index field as the first index information required when executing the SQL script.

[0072] Optionally, the processing unit is specifically configured to:

[0073] For each index field, count a first quantity of the total number of business data in the database table where the index information corresponding to the index field is located;

[0074] Deduplicate each business data value of the index field in the database table where the index information is located, and count a second quantity of at least one business data value after deduplication;

[0075] Determine the ratio of the second quantity to the first quantity as the discrimination degree of the index field in the database table where the index information is located.

[0076] Optionally, the processing unit is specifically configured to:

[0077] Assemble the first index information and the forced execution instruction symbol to obtain the assembled first index information;

[0078] Insert the assembled first index information between the database table key field and the conditional key field of the sql script to generate an adjusted sql script, and execute the adjusted sql script through the sql execution engine.

[0079] In a third aspect, an embodiment of the present invention provides a computing device, including at least one processor and at least one memory. Among them, the memory stores a computer program, and when the program is executed by the processor, the processor is caused to execute the optimization method of the sql script according to any one of the above first aspects.

[0080] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, which stores a computer program executable by a computing device. When the program runs on the computing device, the computing device is caused to execute the optimization method of the sql script according to any one of the above first aspects. BRIEF DESCRIPTION OF THE DRAWINGS

[0081] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments. Obviously, the following drawings are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0082] Figure 1 A possible system architecture diagram provided by an embodiment of the present invention;

[0083] Figure 2Flow schematic diagram of an SQL script optimization method provided by an embodiment of the present invention;

[0084] Figure 3 Schematic diagram of extracting formatted template information for an SQL statement provided by an embodiment of the present invention;

[0085] Figure 4 Schematic diagram of the structure of an SQL script optimization device provided by an embodiment of the present invention;

[0086] Figure 5 Schematic diagram of the structure of a computing device provided by an embodiment of the present invention. Detailed implementation manners

[0087] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0088] To facilitate the understanding of the embodiments of the present application, first, a possible system architecture shown in Figure 1 is taken as an example to illustrate the SQL script optimization system architecture applicable to the embodiments of the present application. This SQL script optimization system architecture can be applied to the optimization of an upcoming SQL script with multi-index problems. As Figure 1 shown, this system architecture may include a proxy module 100 and a multi-index analysis module 200.

[0089] Among them, in the embodiment of the present invention, by adopting Java bytecode technology, stubs are inserted before and after the sql running code of the application program, that is, the corresponding sql proxy module (i.e., proxy module 100) is inserted to parse and perform syntax analysis on the sql running script, so as to identify whether there is a multi-index problem in the sql running script. That is to say, the main function of the proxy module 100 is to identify whether the sql running script has multi-index information. For example, when detecting the execution trigger action of the sql (Structured Query Language) script through the proxy module 100, the sql script can be intercepted, and the intercepted sql script can be normalized to obtain sql template information. Then, the database (such as the index mapping library) is used to identify the sql script, that is, the hash value of the sql script is used to match the multi-index information in the index mapping library. That is to say, it is judged whether the sql script hits multiple indexes through the index mapping library. If not, the sql script can be directly executed by the sql execution engine. If it hits, at least one index information associated with the sql script and the data distribution of each index field included in the at least one index information can be used to identify the optimal index information, and the sql script can be adjusted accordingly through the optimal index information, so that the sql execution engine performs corresponding execution operations on the sql script according to the optimal index information. Then, the adjusted sql script and sql template information can be stored in the database.

[0090] In addition, to improve the recognition performance of whether there is multi-index information in the upcoming SQL script to be executed, it is necessary to perform multi-index analysis on each SQL script in advance. At the same time, it is necessary to analyze the index information and the associated index field information of each SQL script, that is, to analyze whether each SQL script in each SQL script hits the multi-index through the multi-index analysis module 200. Specifically, the multi-index analysis module 200 will trigger the execution of the analysis task at a set time interval (such as every 5 seconds, 10 seconds, 20 seconds, 30 seconds or more than 30 seconds, etc.). When the task execution start time is met, each SQL script can be obtained from the database, that is, the SQL script list information is obtained. For any SQL script, after performing word segmentation processing on the SQL script, the data source information and the condition field information corresponding to the SQL script can be extracted. Then, table information extraction is performed on the data source information to obtain at least one database table information, and condition information extraction is performed on the condition field information to obtain at least one condition field information. By associating at least one database table information with at least one condition field information, a database table condition field set can be obtained. The database table condition field set includes each database table information arranged in order and at least one condition field information arranged in order associated with each database table information. Then, for each database table information, the database of the database table information is obtained, and the corresponding ordered index information is obtained from the database. Each condition field information associated with the database table information is sequentially matched with at least one index field information included in the ordered index information of the database table information, so as to judge whether the SQL script has a multi-index attribute, and at the same time, the hash value of the SQL template information corresponding to each SQL script will be determined. Finally, each hash value and various multi-index conditions of each hash value (such as a certain hash value corresponding to one index information, or a certain hash value corresponding to multiple index information, etc.) are stored in the index mapping library, or can also be stored in other databases (such as MySQL database, Oracle database, etc.).

[0091] It should be noted that the above Figure 1 shown system architecture is only an example, and the embodiments of the present invention do not limit this.

[0092] Figure 2 Exemplarily shows the flow of an optimization method for an SQL script provided by an embodiment of the present invention. This flow can be executed by an optimization device for an SQL script. Among them, the optimization device for an SQL script can be a service device or a component (such as a chip or an integrated circuit) that can support the service device to implement the functions required by this method. Of course, it can also be other electronic devices with the functions required to implement this method.

[0093] Such as Figure 2As shown, the process specifically includes:

[0094] Step 201, when detecting an execution trigger action of a Structured Query Language (SQL) script, intercept the SQL script, and identify the intercepted SQL script through an index mapping library to determine whether the SQL script belongs to a script with multi-index information.

[0095] In an embodiment of the present invention, before determining multi-index for an upcoming SQL script, it is necessary to perform multi-index analysis on each SQL script in advance, and at the same time, analyze the index information and the associated index field information associated with each SQL script. That is, it is necessary to determine the hash value of each SQL template and determine at least one index information of the database table information corresponding to each SQL template. In this way, a mapping relationship between each hash value and at least one index information corresponding to each hash value can be generated to effectively support the identification of whether there is multi-index information in the upcoming SQL script to be executed. Specifically, for any SQL script (the SQL script processed here is a historical SQL script), analyze and process the SQL script to obtain the third SQL template information corresponding to the SQL script, and perform a hash operation on the third SQL template information to determine the second hash value of the SQL script. Among them, first, through a SQL parameterized template class function, format template extraction processing is performed on the SQL script to obtain the SQL template information corresponding to the SQL script, and normalization processing is performed on the SQL template information corresponding to the SQL script to obtain the third SQL template information. Then, perform a hash operation on the third SQL template information to determine the second hash value of the SQL script. Among them, if there are uppercase letters in the SQL template information corresponding to the SQL script, the uppercase letters can be converted into lowercase letters to obtain the converted SQL template information. If there is dynamic value-taking information in the converted SQL template information, the dynamic value-taking information can be replaced with a set replacement value to obtain the third SQL template information.

[0096] Furthermore, word segmentation can be performed on the SQL script to obtain the data source information and condition field information corresponding to the SQL script. Database table information extraction processing can be performed on the data source information corresponding to the SQL script to obtain the database table information arranged in sequence. At the same time, parsing processing can be performed on the condition field information corresponding to the SQL script to obtain the condition field information arranged in sequence. The database table information is associated with the condition field information arranged in sequence, so as to generate a database table condition field set. Among them, the database table condition field set can include the database table information arranged in sequence and at least one condition field information arranged in sequence associated with each database table information, or can also include the operator information attached after the condition field information. Then, for each database table information, the database corresponding to the database table information can be determined, and the index information arranged in sequence possessed by the database table information can be obtained from the database. Each index information can include at least one index field information arranged in sequence. Finally, each condition field information associated with the database table information is sequentially matched with at least one index field information arranged in sequence included in the index information arranged in sequence possessed by the database table information. The at least one index information with successful matching can be used as the second index information for determining whether the SQL script belongs to a script with multi-index information, and the second hash value of each SQL script and at least one second index information corresponding to each second hash value can be stored in the index mapping library. In this way, that is, a mapping relationship between each hash value and at least one second index information corresponding to each hash value is generated, so as to effectively support subsequent identification of whether there is multi-index information in the SQL script to be executed.

[0097] It should be noted that the SQL script can be different scripts written for different business logics in the business system. For example, the SQL script can include SQL query scripts (such as select scripts), SQL insertion scripts (such as insert scripts), SQL deletion scripts (such as delete scripts), SQL modification scripts (such as update scripts), etc. Of course, it can also include scripts for other operations on the database.

[0098] Exemplarily, in the multi-index analysis module, the multi-index analysis tasks can be scanned regularly, such as starting the scanning task at 2:30 am every day. Thus, when detecting the start of a multi-index analysis task, the sql list information can be obtained from the database. The sql list information includes multiple sql scripts. For any sql script (such as a certain sql statement), after performing formatted template extraction and normalization processing on the sql script, the sql template information corresponding to the sql script can be obtained. And through a set hash algorithm, such as the MD2 algorithm (Message Digest Algorithm 2), MD4 algorithm, MD5 algorithm, and SHA-1 algorithm (Secure Hash Algorithm), a hash operation is performed on the sql template information to obtain the hash value corresponding to the sql script. Then, word segmentation processing is performed on the sql script. Taking the sql query script as an example, the basic syntax of the select statement is usually [select option] field list [from] data source [where clause] [group by clause] [having clause] [order by clause] [limit clause]; based on the above syntax, relevant keyword domain information needs to be extracted from the executed sql statement according to the corresponding keyword information. Among them, the extracted keywords and the actual value information of the domain keywords can be shown in Table 1. Among them, the content shown in Table 1 takes the select statement as SELECT id, batch_id, script_id, success_count FROM tbl_batch_result WHERE id>0 AND batch_id BETWEEN 100 AND 110 GROUP BY script_id ORDER BY script_id LIMIT 100 as an example.

[0099] Table 1

[0100]

[0101]

[0102] Among them, the specific process of performing word segmentation processing (specifically, extracting keyword information) on the above sql statement is as follows:

[0103] Step 1: Search for the positions and actual value information corresponding to the relevant keywords in [select / from / where / having / group by / order by / limit] in the associated sub-list one by one from the sql statement.

[0104] Step 2: Locate the position of the keyword in the SQL statement. Denote the keyword as Key and the position as Key-Loc. For example, if Key is "select", then the position Loc is 1.

[0105] Step 3: Extract the information after the keyword as keyAfter. For example, after "select" is "id, batch_id, script_id, success_count FROM tbl_batch_result WHERE id>0 AND batch_id BETWEEN 100 AND 110 GROUP BY script_id ORDER BY script_id limit 100".

[0106] Step 4: Continue to obtain the positions of other keywords in the statement after KeyAfter.

[0107] Step 5: After obtaining the next keyword NextKey, record the starting position of the next keyword NetKeystartLoc. For example, if Nexkey is "from", the Loc of the keyword "from" is 45.

[0108] Step 6: Extract the character information between the KeyLoc position and the NetKeystartLoc position as the information of the keyword Key. For example, the information between "select" and "from" is "id, batch_id, script_id, success_count". Then the actual information of the keyword "select" is "id, batch_id, script_id, success_count".

[0109] Step 7: Repeat Steps 2 to 6 until the end.

[0110] Step 8: Based on the above operations, the data source information, operation field information, condition field information, grouping information, etc. of the SQL statement can be extracted from the SQL statement for use in subsequent steps.

[0111] Among them, since the specific table information targeted by the SQL operation statement needs to be known in advance in the index selection of the SQL statement, so that the index information of the corresponding table can be obtained from the index table, it is necessary to know the specific information of the table targeted in each SQL statement, that is, the SQL operation table. Specifically, for the data source information extracted for the above SQL statement, more detailed differentiation needs to be done for this data source information to identify the relevant operation table information. Among them, the content of the operation table includes the number of operation tables, table name / table alias. The specific format of the operation table is such as tblNum, tblList[tblName1 / tblAlias1, tblName2 / tblAlias2].

[0112] Among them, the data source information is mainly divided into the following situations:

[0113] First, under normal circumstances, if there are no space or comma characters after removing the left and right spaces for the data source information, it is a general situation. The content extracted at this time is the table being operated on. For example, taking the following SQL statement as an example, that is, select id, name, age from tbl_test where id>10, the content of the operation table extracted at this time includes the table number 1, tblList[tbl_test / ].

[0114] Second, in the case of table alias, if there is only a space after removing the left and right spaces for the data source information, it is the alias situation. At this time, the table name and table alias need to be extracted. For example, taking the following SQL statement as an example, that is, select a.id, a.name, a.age from tbl_test a where a.id>10, the content of the operation table extracted at this time includes the table number 1, tblList[tbl_test / a].

[0115] Third, in the case of multiple tables, if the data source information contains commas, it is the multiple-table situation. There may also be the situation of table aliases in the multiple-table scenario, so this needs to be differentiated. The extraction method is as follows: First, split by commas, and each split end after splitting is a table information. Then, judge whether there is a space for each table information. If there is no space, there is no table alias; if there is a space, it needs to be split by the space, and the first part after splitting is the table name, and the second part is the table alias. For example, taking the following SQL statement as an example, select a.id, a.name, a.age, b.loc from tbl_test1 a, tbl_test2 b where a.id>10 and a.id = b.id, the content of the operation table extracted at this time includes the table number 2, tblList[tbl_test1 / a, tbl_test2 / b].

[0116] Furthermore, for the conditional domain information extracted from the above SQL statement, it is necessary to parse the conditional domain information of the SQL statement to extract information such as the conditional fields, the order of conditional fields, and the conditional field operators. Among them, for the conditional fields, multiple conditional information is mainly combined through the "and" field. The specific operation method for parsing the conditional domain information of the SQL statement is as follows:

[0117] Step a: Traverse the conditional domain information to obtain the position of the first "and" phrase. Among them, the extracted information is and_before and and_after. The information before "and" is and_before, and the part after the "and" word is and_after.

[0118] Step b: Determine whether "between" exists in the and_before information. If it does not exist, the and_before information is the conditional field information, and the field extraction is performed on this conditional field information. Specifically, the field extraction needs to be performed according to the following operator information. The operator information includes =, !=, >, >=, <, <=, in, like, etc. First, the conditional domain information is split according to the operator information. The left information after splitting is the conditional field information. After extracting the field information, it is necessary to determine whether the field information contains a "." symbol (i.e., the small dot in English). If it contains, it means that the database table information is included, and the splitting can be performed for ".", and the database table information can be extracted. In this way, the finally obtained information is table information / field name / operator information.

[0119] Step c: If "between" exists in the and_before information, extract the position of the next "and" from the conditional fields, and obtain the information before the next "and" preposition, which together form the "between and" information, that is, fieldName between? and?. Then, split this "between and" information, and the split word is "between". The previous part of the split information is the conditional field information. After extracting the field information, determine whether the field information contains a "." symbol (i.e., the small dot in English). If it contains, it means that the table information is included, and the splitting can be performed for ".", and the database table information can be extracted. Finally, the extracted information is [table information / field information / between].

[0120] Step d: Loop through steps a to c to traverse all the "and" information.

[0121] Step e: Finally, the conditional field list information can be obtained, and each conditional field information contains [table information / field name / operation information]. Thus, the finally obtained conditional field list information can be in the following form: FieldList[tableName / fieldname1 / =,tableName / fieldName2 / >,...]. Among them, by grouping the conditional field list information and putting the field information of the same database table in one group, the table conditional field list information can be generated, and this table conditional field list information can be in the following form: [tbl1[fieldName1 / =,fieldNam2 / >...], tbl2[fieldName1 / =,fieldNam2 / >...],...].

[0122] It should be noted that since the index information is generally selected according to the conditional fields, the conditional field information obtained here mainly represents the corresponding table and field information involved in the conditions in the SQL statement. Generally, the possible index information is matched according to this information. Since the database sorts when creating indexes, the index selection generally follows the leftmost matching principle and the sorting fields need to be the same. Therefore, the operation information here mainly judges whether the index selection conforms to the above index selection rules so that the selected index can take effect.

[0123] In addition, the relevant database table information can be obtained through the conditional field list information obtained above, and the corresponding database can be queried through the relevant database table information. Through the corresponding database, the relevant index information and the index field information included in the relevant index information can be obtained. For example, the conditional field list information obtained above is tbl1[field1 / =,field2 / ],tbl2[field3 / =,field4 / >. Thus, through this conditional field list information, the relevant table information (such as tbl1 and tbl2) can be obtained, and then by connecting to the corresponding database, the relevant index field information of this table can be obtained from the database. Among them, the relevant index field record content obtained from the database for this table is: tbl1->[index1(field1,field2),index2(field3,field4)],tbl2->[index1(field2)].

[0124] Then, based on the relevant index field information of this table obtained from the database and the conditional field list information obtained above, it can be identified whether the above SQL statement hits multiple indexes. Among them, the specific identification process is as follows:

[0125] Step 1. By traversing the above-obtained conditional field list information [tbl1[fieldName1 / =, fieldName2 / >, …], tbl2[fieldName1 / =, fieldName2 / >, …], …], the conditional field information of each table can be obtained. For example, taking the database table tbl1 as an example, the conditional field information of the database table tbl1 can be obtained as tbl1[field1 / =, field2 / =, field3 / >].

[0126] Step 2. Perform the following operations on each table respectively. For example, for tbl1, according to the above-obtained index information corresponding to the table tbl1 (such as tbl1->[index1(field1, field2), index2(field3, field4)]), traverse the content of the index field list of tbl1, and at least one index information [index1(field1, field2), index2(field3)] can be obtained. For each index information, match each field with the conditional field information tbl1[field1 / =, field2 / =, field3 / >, tbl2[fieldName1 / =, fieldNam2 / >, …], …] of the corresponding table tbl1 in the field information [field1 / =, field2 / =, field3 / >] of the table tbl1. For example, the index information index1(field1, field2), ndex2(field3, field4) can be matched. If the index field can match the query conditional field, it means that the index is hit, and record the hit index and index field information. For example, the index information hit by the sql statement where the database table tbl1 is located is index1(field1, field2) and index2(field3).

[0127] Step 3. Repeat the above Step 1 to Step 2 until the index matching of all database tables involved in the sql statement is completed.

[0128] Finally, for any SQL statement, associate and map the hash value corresponding to the SQL statement with the index matching information determined by the above method (i.e., how many index information it corresponds to, such as one, two, or more than two, etc.). For example, if the index information corresponding to a certain SQL statement is one, then mark the single-index attribute on the index matching information associated with the hash value corresponding to the SQL statement. Or if the index information corresponding to a certain SQL statement is two or more than two, then mark the multi-index attribute on the index matching information associated with the hash value corresponding to the SQL statement, and store the associated and mapped hash value and the corresponding index matching information (such as one, two, or more than two index information) in the index mapping library, so that the proxy module can timely use the index mapping library to judge whether the SQL script to be executed will hit multiple indexes.

[0129] Furthermore, after the index mapping library is established, the index mapping library can be used to identify the intercepted SQL script to be executed, so as to determine whether the SQL script to be executed hits multiple indexes. Specifically, when the execution trigger action of a certain SQL script is detected, the SQL script can be intercepted. For example, by inserting stubs before and after the SQL running code of the application program and inserting the corresponding SQL proxy module to intercept the SQL script, and the intercepted SQL script can be identified through the index mapping library, so as to accurately determine whether the SQL script belongs to the script with multiple index information, that is, to determine whether there is a multiple index problem in the SQL script. Among them, the specific identification process is as follows: First, determine the first hash value of the SQL script. For example, perform a hash operation on the SQL script through a set hash algorithm (such as the MD5 algorithm) to calculate the hash value of the SQL script. Then, through the first hash value of the SQL script, match m index information corresponding to the first hash value from the index mapping library, and count the number of the m index information corresponding to the first hash value. If the number meets the quantity threshold, it can be determined that the SQL script belongs to the script with multiple index information. At this time, the SQL script needs to be adjusted. If the number of the m index information does not meet the quantity threshold, it can be determined that the SQL script does not belong to the script with multiple index information. At this time, there is no need to adjust the SQL script and it can be directly executed in the SQL execution engine. For example, assume that the quantity threshold is 2. If the number of the m index information corresponding to the first hash value is counted as 3, it can be known that 3 is greater than 2, so it can be confirmed that the SQL script hits multiple indexes. If the number of the m index information corresponding to the first hash value is counted as 1, it can be known that 1 is less than 2, so it can be confirmed that the SQL script does not hit multiple indexes. Among them, the quantity threshold can be set according to the experience of those skilled in the art or can be set according to the results obtained from multiple experiments or can be set according to the actual application scenario. The embodiments of the present invention do not limit this; m is an integer greater than or equal to 1.

[0130] It should be noted that after intercepting the SQL script executed for the business system, corresponding processing needs to be performed on the SQL script to improve the efficiency of multi-index judgment for the SQL script. That is, perform formatted template extraction and normalization processing on the SQL script, integrate multiple SQL scripts with different parameter values but the same business logic into one SQL template, and then perform a hash operation on the SQL template to determine the hash value of the SQL template. That is, the hash values corresponding to multiple SQL scripts with different parameter values but the same business logic are the same, and the index information corresponding to multiple SQL scripts with different parameter values but the same business logic is also the same. That is, only one hash value and the m index information corresponding to the hash value need to be stored in the index mapping library. That is, through the SQL parameterized template class function, perform formatted template extraction processing on the SQL script to obtain the first SQL template information corresponding to the SQL script, and perform normalization processing on the first SQL template information to obtain the second SQL template information. Then, perform a hash operation on the second SQL template information to determine the first hash value of the SQL script. Among them, in order to accurately integrate multiple SQL scripts with different parameter values but the same business logic into one SQL template, normalization processing will be performed on the SQL script after formatted template extraction processing. That is, if there are capital letters in the first SQL template information, the capital letters can be converted into lowercase letters to obtain the converted first SQL template information. When it is determined that there is dynamic value information in the converted first SQL template information, the dynamic value information can be replaced with a set replacement value to achieve unified formatting of the SQL template information.

[0131] Exemplarily, in the actual operation process of the business system table, the SQL business logic of the system basically does not change, and only the actual value information of the relevant business parameters in the logic changes. Therefore, in the business development code, there will be relevant SQL template statements. Therefore, in the business development code, there will be relevant SQL template statements. Therefore, in the proxy module, it is necessary to intercept the SQL statements during business operation and extract the formatted template information of the SQL statements from the intercepted SQL-related information. For example, as Figure 3 shown, it is a schematic diagram for extracting formatted template information for SQL statements provided by an embodiment of the present invention. Based on Figure 3The specific extraction process is as follows: First, Java bytecode technology is used to intercept the SQL execution functions execute / executeQuery / executeUpdate (the class is com.mysql.jdbc.PreparedStatement). After interception, relevant system information about this class can be obtained. Then, because the variable originalsql in the SQL parameterized template class com.mysql.jdbc.PreparedStatement contains the template information of the SQL statement, the parameterized template mainly obtains the relevant SQL statement formatting template information by extracting the value information of originalsql. For example, if a business logic is to query the user table for users with male gender and age greater than 18, the corresponding SQL statement is: select * from tbl_user where sex = '男' and age > 18. Then, the formatting template is extracted for this SQL statement, resulting in select * from tbl_test where sex = ? and age > ?.

[0132] In addition, after obtaining the SQL template information, due to the problem of SQL template syntax, the SQL template information will contain some dynamic information. This dynamic information needs to be removed to make the SQL template purer. Therefore, the SQL template information needs to be normalized. Specifically, the normalization of SQL template information can be divided into the following situations:

[0133] First, perform string minimization on the SQL template information.

[0134] To facilitate unified SQL processing, you need to minimize SQL statements by converting all uppercase letters in the SQL statement to lowercase. For example, consider a SQL template like this: Select * from TBL_TEST where id = ? By converting all uppercase letters in the SQL template to lowercase, you get the following SQL template: Select * from tbl_test where id = ?

[0135] Second, remove the character strings in the SQL template information.

[0136] Convert the string information contained in the SQL template information into a set replacement value. For example, a certain SQL template information is: Select*from TBL_TEST where name=‘abc’; Convert the uppercase letters in the SQL template information into lowercase letters, and convert the string information contained in the SQL template information into a set replacement value, and the processed SQL template information can be obtained as: select*from tbl_test where name=?.

[0137] Third, perform trimming processing on the in information in the SQL template information.

[0138] The in operator in SQL syntax represents a list. In each SQL operation, the content of the in list corresponding to the SQL statement may be different, so in needs to be unified. Replace the content of in(a,b,c) contained in the SQL template information with in(?). For example, a certain SQL template information is: Select*from TBL_TEST where name in(‘a’,‘b’,’c’); Convert the uppercase letters in the SQL template information into lowercase letters, and replace the content of in(a,b,c) contained in the SQL template information with in(?), and the processed SQL template information can be obtained as: select*from tbl_test where name in(?).

[0139] Fourth, perform unification processing on the limit syntax in the SQL template information.

[0140] The limit in SQL syntax represents the starting position and page size of paging. Since the starting position and page size may be inconsistent during each operation, limit needs to be unified. That is, the statement of limit a,b in the SQL template information is unified into limit?. For example, a certain SQL template information is: Select*from TBL_TEST where limit 1,10; Convert the uppercase letters in the SQL template information into lowercase letters, and perform unification processing on the limit syntax in the SQL template information, and the processed SQL template information can be obtained as: select*from tbl_testwhere limit?.

[0141] Fifth, perform right-value formatting processing on the comparison operators in the SQL template information.

[0142] There are some comparison operations in SQL syntax operations. For example, the comparison operators include >, >=, <, <=, ==, !=, etc. For the right values of these comparison operators, if there are specific values, it is necessary to unify the right values of these comparison operators and convert the specific right values of these comparison operators into dynamic values. For example, a certain SQL template information is: Select * from where id > 10; By converting the uppercase letters in this SQL template information into lowercase letters and formatting the right values of the comparison operators in this SQL template information, the processed SQL template information can be obtained as: select * from where id >?.

[0143] Step 202, if it is determined that the SQL script belongs to a script with multiple index information, based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information, determine the first index information required when executing the SQL script.

[0144] In the embodiments of the present invention, for any SQL script, if it is determined that the SQL script belongs to a script with multiple index information, corresponding adjustments need to be made to the SQL script. That is, by dynamically selecting the optimal index information based on the multiple index information corresponding to the SQL script and the real-time data distribution of each index field included in the multiple index information corresponding to the SQL script, and then making corresponding adjustments to the SQL script through the optimal index information. Specifically, for any index information corresponding to the SQL script, the database table where the index information is located and at least one index field included in the index information can be determined. For each index field, the discrimination degree of the index field in the database table where the index information is located can be determined, so that the discrimination degrees of each index field included in the multiple index information can be determined. Then, by comparing the discrimination degrees of each index field included in the multiple index information, the maximum discrimination degree can be determined, and the first index field corresponding to the maximum discrimination degree can be determined. The index information corresponding to the first index field can be used as the first index information required when executing the SQL script, that is, the optimal or most suitable index information. Using the first index information to make corresponding adjustments to the SQL script can enable the adjusted SQL script to perform corresponding execution operations according to the optimal index information.

[0145] Among them, for any index information, for each index field in at least one index field included in the index information, the first quantity of the total number of business data in the database table where the index information corresponding to the index field is located can be counted, and for each business data value of the index field in the database table where the index information is located, duplicate removal processing can be performed, so that at least one business data value after duplicate removal can be obtained, and the second quantity of at least one business data value after duplicate removal can be counted. Then, calculate the ratio of the second quantity to the first quantity, and this ratio can be used as the discrimination degree of the index field in the database table where the corresponding index information is located, so as to provide effective data support for dynamically selecting the optimal index information subsequently.

[0146] Exemplarily, for any SQL statement, the proxy module can match multiple index information corresponding to the SQL statement from the index mapping library according to the hash value corresponding to the SQL statement, and for each index information, it can judge the data distribution of each index field included in the index information (that is, the distribution of business field values of each index field), so that the optimal index selection strategy can be determined according to the distribution of business field values of each index field included in each index information, that is, the optimal index information can be selected. Among them, for the selection of the optimal index information, the specific selection process is as follows:

[0147] Step 1: Traverse the multi-index information list.

[0148] Step 2: For each index information, obtain at least one index field included in the index information, and obtain the grouping value (that is, the field value) of each index field from the relevant table associated with the index information. The acquisition of the grouping value is realized by querying the table. For example, for a certain database table (such as tbl1), the index fields are field1 and field2, then the query statement is: select count(1) from tbl1 where group by field1,field2. It should be noted that here, the discrete degree of the relevant index grouping is obtained from the relevant table according to the index field (that is, the total number of records is obtained by grouping). The higher the grouping discrete degree, the more dispersed the field values are, the highest the discrimination degree of the field is, and the highest the index query efficiency through this field is.

[0149] Among them, group by represents the number of different values of the corresponding field. For the same table, the number of records in the table is determined. Then, the larger the number of groups, the larger the ratio of the field combination occupying the table, which means that the field combination is more dispersed. For example, as shown in Table 2.

[0150] Table 2

[0151] field1 1 2 3 4 5 6 7 8 9 10 field2 11 11 12 12 13 13 14 14 15 16

[0152] Based on Table 2, if grouped by filed1, the number of groups is 10; if grouped by field2, the number of groups is 6. Comparatively speaking, filed1 is more discrete, that is, it has a higher dispersion degree, which also means that filed1 has a higher discrimination degree. It should be noted that during index lookup, first, the corresponding index record is queried according to the index information, and then according to the primary key id in the index record (each index record has a corresponding primary key id). Then, the required information is obtained from the data record corresponding to the primary key according to the primary key id. Therefore, if the index discrimination degree is higher, the corresponding record can be found faster.

[0153] Step 3: Compare the proportion of the number of groups calculated for each index information to obtain the name of the index information with the largest number of groups.

[0154] For example, for the two index information corresponding to tbl1, namely index information index1 (filed1, filed2) and index information index2 (filed3), assuming that the proportion of the number of groups of the index field filed3 included in index information index2 is the largest, then index2 can be used as the optimal index information for adjusting or optimizing the sql script.

[0155] In addition, it should be noted that in the prior art, since the misselected index is operated by the database engine, business developers cannot perceive it. Only when problems occur in production (such as the SQL execution time being too long) can it be known that the index is misselected. To solve the problem of misselected indexes, developers can only modify the SQL statement after the problem occurs, use force index(index_name) in the SQL statement to forcibly specify the index field, then rebuild the application system and deploy the system, and release the new SQL statement to the production environment through the application version to solve this problem. Among them, the SQL statements are all included in the application code. After the problem occurs, fixing the SQL statement means that the application code needs to be modified, and after the modification is completed, the version needs to be republished, and redeployment in the production environment is required to solve this problem. Therefore, the repair cost is relatively high. It can be seen that when the prior art solves the problem of misselected indexes in a multi-index scenario, it is to forcibly specify the index in the SQL statement. This is a fixed way, assuming that the data distribution is fixed by default. However, in real production data, the data distribution changes at any time. When the distribution of a certain type of data changes, the specified index will become invalid. However, in the technical solution of the present invention, when the application is running, the SQL execution statement is parsed in real time to identify the conditional fields in the SQL statement. Then, according to the conditional fields, the real-time data distribution form of the table associated with the SQL statement is obtained. According to a certain index selection strategy, the most suitable index information is dynamically selected, and the SQL execution statement is automatically changed through the most suitable index information, so that the SQL execution engine can forcibly reference the most suitable index information to execute the SQL statement. In this way, the situation of misselected indexes in a multi-index scenario can be effectively solved.

[0156] Step 203, adjust the SQL script based on the first index information to generate a SQL script for execution in the SQL execution engine.

[0157] In the embodiment of the present invention, when it is necessary to adjust the SQL script to be executed, the specified index information will be added after the relevant table name of the SQL script to be executed. That is, the first index information can be assembled with the forced execution instruction symbol to obtain the assembled index information. Then, the assembled index information can be inserted between the relevant table name and the conditional keyword field, so that the SQL execution engine can forcibly reference the index information to execute the SQL script. That is, the SQL execution engine will perform corresponding execution operations on the SQL script according to the optimal index information.

[0158] Exemplarily, for a certain SQL statement, assume that the SQL statement is "select id, name, age from tbl1 where field1 > 10 and field2 > 10 and field3 = 'aa'". And assume that the optimal index information obtained through the above steps of determining the optimal index information is index2. Then, after corresponding adjustment for this SQL statement, the adjusted SQL statement can be obtained as "select id, name, age from tbl1 force index(index2) where field1 > 10 and field2 > 10 and field3 = 'aa'". Thus, after corresponding adjustment for this SQL statement through the proxy module, the adjusted SQL statement can be sent to the SQL execution engine for execution operation of this adjusted SQL statement. In addition, it should be noted that during the actual operation and maintenance process, the operation and maintenance personnel will perform relevant operations on the table, such as adding, deleting, and modifying indexes, etc. Then, after the index is changed, the index information in the corresponding SQL statement will also change accordingly. Thus, in order to facilitate the multi-index analysis module to regularly update and analyze the index information corresponding to the SQL statement, after each SQL statement is executed, the SQL statement and the SQL normalization information (i.e., the normalized SQL template information) need to be asynchronously sent to the message middleware, and the multi-index analysis module will perform timed multi-index analysis on the SQL statement.

[0159] The above embodiments show that the technical solution in the present invention can detect in real time the execution trigger action of the SQL script, and can timely and accurately identify whether the SQL script hits multi-index information through the index mapping library. According to the discrimination degrees (i.e., the data distribution characteristics of each index field in its associated database table) of each index field included in the multi-index information hit by the SQL script, the most suitable index information can be selected from the multi-index information to adjust the SQL script (i.e., optimize the SQL script through the most suitable index information). Thus, the execution efficiency of the SQL script can be effectively improved, and the problem of incorrect index selection during index selection can be effectively avoided. Specifically, when the execution trigger action of the SQL script is detected, the SQL script can be effectively intercepted, and at the same time, the intercepted SQL script can be identified through the index mapping library, so as to accurately determine whether the SQL script belongs to a script with multi-index information, that is, to determine whether there is a multi-index problem with the SQL script. When it is determined that the SQL script belongs to a script with multi-index information, based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information, the first index information required when executing the SQL script can be determined. The first index information is the most suitable index information selected. Then, through the first index information, the SQL script can be automatically adjusted (which can also be understood as automatically optimized), so that the SQL execution engine executes the adjusted SQL script. In this way, this solution can automatically identify whether there are multi-indexes in the SQL script to be executed, and automatically optimize the SQL script with multi-indexes. That is, by selecting the most suitable index information according to a certain index selection strategy and automatically adjusting the SQL script so that the SQL script uses the optimized index information, the problem of incorrect index selection caused by the existence of multi-indexes in the SQL script in the prior art can be effectively solved, and the execution speed of the optimized SQL script can be accelerated, thus effectively improving the execution efficiency of the SQL script. In addition, it should be noted that since the technical solution in the present invention also dynamically selects the most suitable index information in real time according to the discrimination degrees of each index field included in the multi-index information hit by the SQL script (the SQL script is included in the application program code), so as to achieve dynamic adjustment of the SQL script, without repairing the application program code where the SQL script is located, the cost of repairing the problem of incorrect index selection for the SQL script can be effectively reduced.

[0160] Based on the same technical concept, Figure 4Exemplarily shown is an optimization device for an SQL script provided by an embodiment of the present invention. This device can execute the process of the SQL script optimization method. Among them, the optimization device for the SQL script can be a service device or a component (such as a chip or integrated circuit) that can support the service device to implement the functions required by this method. Of course, it can also be other electronic devices with the functions required to implement this method.

[0161] As Figure 4 shown, the device includes:

[0162] An identification unit 401, configured to intercept the SQL script when detecting an execution trigger action of a structured query language (SQL) script, and identify the intercepted SQL script through an index mapping library to determine whether the SQL script belongs to a script with multiple index information; the index mapping library is used to indicate the mapping relationship between the hash values of each SQL script and the corresponding m index information.

[0163] A processing unit 402, configured to, if it is determined that the SQL script belongs to a script with multiple index information, determine the first index information required when executing the SQL script based on the multiple index information corresponding to the SQL script and the discrimination degrees of each index field included in the multiple index information; where the discrimination degree of each index field is used to characterize the proportion of the number of different business data values of the index field in the database table where the corresponding index information is located; and adjust the SQL script based on the first index information to generate an SQL script for execution in an SQL execution engine.

[0164] Optionally, the identification unit 401 is specifically configured to:

[0165] Determine the first hash value of the SQL script;

[0166] Match the corresponding m index information from the index mapping library through the first hash value of the SQL script;

[0167] If the number of m index information meets the quantity threshold, determine that the SQL script belongs to a script with multiple index information; if the number of m index information does not meet the quantity threshold, determine that the SQL script does not belong to a script with multiple index information.

[0168] Optionally, the identification unit 401 is specifically configured to:

[0169] Perform a formatted template extraction process on the SQL script through an SQL parameterization template class function to obtain the first SQL template information corresponding to the SQL script;

[0170] Normalize the first SQL template information to obtain the second SQL template information;

[0171] Perform a hash operation on the second SQL template information to determine the first hash value of the SQL script.

[0172] Optionally, the recognition unit 401 is specifically configured to:

[0173] If there are capital letters in the first SQL template information, convert the capital letters to lowercase letters to obtain the converted first SQL template information;

[0174] If there is dynamic value-taking information in the converted first SQL template information, replace the dynamic value-taking information with a set replacement value to obtain the second SQL template information.

[0175] Optionally, the recognition unit 401 is specifically configured to:

[0176] For any SQL script, analyze and process the SQL script to obtain the third SQL template information corresponding to the SQL script, and perform a hash operation on the third SQL template information to determine the second hash value of the SQL script;

[0177] Perform word segmentation on the SQL script to obtain the data source information and condition field information corresponding to the SQL script, perform database table information extraction processing on the data source information corresponding to the SQL script to obtain each database table information arranged in order, perform parsing processing on the condition field information corresponding to the SQL script to obtain each condition field information arranged in order; associate each database table information with each condition field information arranged in order to generate a database table condition field set; the database table condition field set includes each database table information arranged in order and at least one condition field information arranged in order associated with each database table information;

[0178] For each database table information, determine the database corresponding to the database table information, and obtain each index information arranged in order possessed by the database table information from the database; where each index information includes at least one index field information arranged in order;

[0179] Match each condition field information associated with the database table information with at least one index field information arranged in order included in each index information possessed by the database table information in turn, and use the at least one index information that matches successfully as the second index information for determining whether the SQL script belongs to a script with multi-index information;

[0180] Store the second hash value of each SQL script and at least one second index information corresponding to each of the second hash values into the index mapping library.

[0181] Optionally, the processing unit 402 is specifically configured to:

[0182] For any index information corresponding to the SQL script, determine the database table where the index information is located and at least one index field included in the index information;

[0183] For each index field, determine the discrimination degree that the index field has in the database table where the index information is located;

[0184] Compare the discrimination degrees of the index fields included in the multiple index information, determine the maximum discrimination degree, and determine the first index field corresponding to the maximum discrimination degree;

[0185] Determine the index information corresponding to the first index field as the first index information required when executing the SQL script.

[0186] Optionally, the processing unit 402 is specifically configured to:

[0187] For each index field, count the first quantity of the total number of business data records in the database table where the index information corresponding to the index field is located;

[0188] Deduplicate each business data value that the index field has in the database table where the index information is located, and count the second quantity of at least one business data value after deduplication;

[0189] Determine the ratio of the second quantity to the first quantity as the discrimination degree that the index field has in the database table where the index information is located.

[0190] Optionally, the processing unit 402 is specifically configured to:

[0191] Assemble the first index information and the forced execution instruction symbol to obtain the assembled first index information;

[0192] Insert the assembled first index information between the database table key field of the SQL script and the conditional key field of the SQL script to generate an adjusted SQL script, and execute the adjusted SQL script through the SQL execution engine.

[0193] Based on the same technical concept, an embodiment of the present invention further provides a computing device, such as Figure 5As shown, it includes at least one processor 501 and a memory 502 connected to the at least one processor. In the embodiments of the present invention, the specific connection medium between the processor 501 and the memory 502 is not limited. Figure 5 Taking the example that the processor 501 and the memory 502 are connected by a bus. The bus can be divided into an address bus, a data bus, a control bus, etc.

[0194] In the embodiments of the present invention, the memory 502 stores instructions executable by the at least one processor 501. By executing the instructions stored in the memory 502, the at least one processor 501 can execute the steps included in the optimization method of the foregoing sql script.

[0195] Among them, the processor 501 is the control center of the computing device. It can use various interfaces and lines to connect various parts of the computing device. By running or executing the instructions stored in the memory 502 and calling the data stored in the memory 502, data processing can be achieved. Optionally, the processor 501 may include one or more processing units. The processor 501 may integrate an application processor and a modem processor. Among them, the application processor mainly processes the operating system, user interface, application programs, etc., and the modem processor mainly processes the issued instructions. It can be understood that the above modem processor may not be integrated into the processor 501 either. In some embodiments, the processor 501 and the memory 502 can be implemented on the same chip. In some embodiments, they can also be separately implemented on independent chips.

[0196] The processor 501 can be a general-purpose processor, such as a central processing unit (CPU), a digital signal processor, an application specific integrated circuit (ASIC), a field programmable gate array or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, which can implement or execute the various methods, steps and logic block diagrams disclosed in the embodiments of the present invention. The general-purpose processor can be a microprocessor or any conventional processor, etc. The steps of the method disclosed in combination with the embodiments of the sql script optimization method can be directly embodied as being completed by the execution of the hardware processor, or by the combination of the hardware and software modules in the processor.

[0197] The memory 502, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. The memory 502 can include at least one type of storage medium. For example, it can include flash memory, hard disks, multimedia cards, card-type memories, random access memory (RAM), static random access memory (SRAM), programmable read-only memory (PROM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), magnetic memories, magnetic disks, optical disks, and so on. The memory 502 is any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory 502 in the embodiments of the present invention can also be a circuit or any other device capable of implementing a storage function, for storing program instructions and / or data.

[0198] Based on the same technical concept, embodiments of the present invention also provide a computer-readable storage medium storing a computer program executable by a computing device. When the program runs on the computing device, it causes the computing device to execute the steps of the above-described optimization method for the SQL script.

[0199] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system, or a computer program product. Therefore, the present invention can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories, CD-ROMs, optical memories, etc.) containing computer-usable program code.

[0200] The present invention is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to the present invention. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, and the combination of flows and / or blocks in the flowcharts and / or block diagrams, can be realized by computer program instructions. These computer program instructions can be provided to the processors of general-purpose computers, special-purpose computers, embedded processors, or other programmable data processing devices to generate a machine, so that the instructions executed by the processors of the computer or other programmable data processing devices generate for realizing the processes Figure 1one process or multiple processes and / or boxes Figure 1 means for the functions specified in one box or multiple boxes.

[0201] These computer program instructions can also be stored in a computer-readable memory that can guide a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured article including an instruction device, and the instruction device implements the processes Figure 1 one process or multiple processes and / or boxes Figure 1 the functions specified in one box or multiple boxes.

[0202] These computer program instructions can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to produce a computer-implemented process. Therefore, the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in Figure 1 one process or multiple processes and / or boxes Figure 1 one box or multiple boxes.

[0203] Although the preferred embodiments of the present invention have been described, those skilled in the art can make additional changes and modifications once they learn the basic creative concepts. Therefore, the appended claims are intended to be interpreted to include the preferred embodiments and all changes and modifications that fall within the scope of the present invention.

[0204] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of this application and their equivalent technologies, the present invention is also intended to include these modifications and variations.

Claims

1. A method for optimizing a sql script, characterized in that: include: When a trigger action for executing a structured query language (SQL) script is detected, the SQL script is intercepted and identified through an index mapping library to determine whether the SQL script is a script with multiple index information; the index mapping library is used to indicate the mapping relationship between the hash value of each SQL script and the corresponding m index information; If it is determined that the SQL script is a script with multiple index information, first index information required for executing the SQL script is determined based on the multiple index information corresponding to the SQL script and the discrimination of each index field included in the multiple index information; wherein the discrimination of each index field is used to represent the proportion of different business data values of the index field in the database table where the corresponding index information is located; Generating a SQL script for execution in a SQL execution engine by adjusting the SQL script based on the first index information; The intercepted SQL script is identified through the index mapping library to determine whether the SQL script is a script with multiple index information, including: Determine a first hash value of the SQL script; Match the corresponding m index information from the index mapping library according to the first hash value of the SQL script; If the number of the m index information meets the number threshold, it is determined that the SQL script belongs to a script with multiple index information; if the number of the m index information does not meet the number threshold, it is determined that the SQL script does not belong to a script with multiple index information.

2. The method according to claim 1, wherein Determining the first hash value of the SQL script includes: Performing formatted template extraction processing on the SQL script through the SQL parameterized template class function to obtain first SQL template information corresponding to the SQL script; Normalize the first SQL template information to obtain second SQL template information; Perform a hash operation on the second SQL template information to determine a first hash value of the SQL script.

3. The method according to claim 2, wherein The normalizing the first SQL template information to obtain the second SQL template information includes: If there are uppercase letters in the first SQL template information, convert the uppercase letters into lowercase letters, thereby obtaining the converted first SQL template information; If there is dynamic value information in the converted first SQL template information, the dynamic value information is replaced with a set replacement value, thereby obtaining the second SQL template information.

4. The method according to claim 1, wherein The index mapping library is established in the following manner: For any SQL script, analyze and process the SQL script to obtain third SQL template information corresponding to the SQL script, and perform a hash operation on the third SQL template information to determine a second hash value of the SQL script; Perform word segmentation processing on the SQL script to obtain data source information and condition field information corresponding to the SQL script, perform database table information extraction processing on the data source information corresponding to the SQL script to obtain sequentially arranged database table information, and perform parsing processing on the condition field information corresponding to the SQL script to obtain sequentially arranged condition field information; Associating the database table information with the condition field information arranged in sequence, thereby generating a database table condition field set; the database table condition field set includes the database table information arranged in sequence and at least one condition field information arranged in sequence associated with each database table information; For each database table information, determine the database corresponding to the database table information, and obtain each index information of the database table information arranged in sequence from the database; wherein each index information includes at least one index field information arranged in sequence; Sequentially matching each condition field information associated with the database table information with at least one index field information arranged in sequence included in each index information arranged in sequence in the database table information, and using at least one index information that successfully matches as second index information for determining whether the SQL script belongs to a script with multiple index information; The second hash value of each SQL script and at least one second index information corresponding to each second hash value are stored in the index mapping library.

5. The method according to claim 1, wherein The determining, based on the plurality of index information corresponding to the SQL script and the discrimination of each index field included in the plurality of index information, first index information required for executing the SQL script includes: For any index information corresponding to the SQL script, determine the database table where the index information is located and at least one index field included in the index information; For each index field, determining the discrimination of the index field in the database table where the index information is located; Comparing the discriminations of the index fields included in the plurality of index information, determining a maximum discrimination, and determining a first index field corresponding to the maximum discrimination; The index information corresponding to the first index field is determined as the first index information required when executing the SQL script.

6. The method according to claim 5, wherein The discrimination level of each index field is determined as follows: For each index field, counting a first number of total business data items in the database table where the index information corresponding to the index field is located; performing deduplication processing on each business data value of the index field in the database table where the index information is located, and counting a second number of at least one business data value after deduplication; The ratio of the second number to the first number is determined as the discrimination of the index field in the database table where the index information is located.

7. The method according to any one of claims 1 to 6, wherein: Generating a SQL script for execution in a SQL execution engine by adjusting the SQL script based on the first index information includes: Assembling the first index information with the mandatory execution instruction to obtain assembled first index information; The assembled first index information is inserted between the database table key field of the sql script and the condition key field of the sql script to generate an adjusted sql script, and the adjusted sql script is executed by the sql execution engine.

8. A sql script optimization device, characterized in that: include: an identification unit configured to intercept the SQL script upon detecting an execution trigger action of a structured query language SQL script, and identify the intercepted SQL script through an index mapping library to determine whether the SQL script is a script with multiple index information; the index mapping library is configured to indicate a mapping relationship between a hash value of each SQL script and m corresponding index information; a processing unit configured to, if it is determined that the SQL script is a script having multiple index information, determine first index information required for executing the SQL script based on the multiple index information corresponding to the SQL script and the discrimination of each index field included in the multiple index information; wherein the discrimination of each index field is used to represent the proportion of different business data values of the index field in the database table where the corresponding index information is located; and generate a SQL script for execution in a SQL execution engine by adjusting the SQL script based on the first index information; The identification unit is specifically used for: Determine a first hash value of the SQL script; Match the corresponding m index information from the index mapping library according to the first hash value of the SQL script; If the number of the m index information meets the quantity threshold, it is determined that the SQL script is a script with multiple index information; If the number of the m index information does not meet the number threshold, it is determined that the SQL script does not belong to a script with multiple index information.

9. A computing device, characterized in that The method comprises at least one processor and at least one memory, wherein the memory stores a computer program, and when the program is executed by the processor, the processor executes the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that The computer program that can be executed by a computing device is stored therein, and when the program is run on the computing device, the computing device is caused to execute the method according to any one of claims 1 to 7.

Images