A method and device for setting a database index

Abstract

The present invention relates to the field of financial technology (Fintech), and discloses a method and device for setting database indexes. The method includes: collecting each search statement executed by the database, and for each search statement, determining the search range and search object of the search statement. Further, the search object includes at least one sub-object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement. Further, for each search statement in the same search range, counting the number of different search objects; wherein, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, then the second search object is determined as the first search object. Further, according to the number of different search objects, determining the index setting corresponding to the search range. This solution can determine an effective index setting based on the execution situation of the current search statement, improving the search efficiency.

Description

Technical Field

[0001] Embodiments of the present invention relate to the field of financial technology (Fintech), and in particular, to a method and device for setting database indexes. Background Art

[0002] With the development of computer technology, more and more technologies (such as big data, distributed, blockchain, artificial intelligence, etc.) are applied in the financial field. The traditional financial industry is gradually transforming into financial technology (Fintech). The optimization technology of data tables is no exception. However, due to the security and real-time requirements of the financial and payment industries, higher requirements are also put forward for technologies.

[0003] Currently, generally, the explain command is used to detect a search statement to determine whether the search statement hits the index of the corresponding data table, whether the search statement sets an index field, etc. However, in fact, the detection results obtained by the explain command are relatively complex, and users need to understand the meanings corresponding to each value, and then further analyze the search statement to determine the index setting of the corresponding data table, that is, it cannot directly give index optimization suggestions for the data table.

[0004] Therefore, a method for setting database indexes is needed to help optimize the indexes of data tables and improve search efficiency. Summary of the Invention

[0005] Embodiments of the present invention provide a method and device for setting database indexes to give index setting information of a data table according to a running search statement and improve search efficiency.

[0006] In a first aspect, a method for setting a database index provided by an embodiment of the present invention includes:

[0007] The client collects each search statement executed by the database. For each search statement, the search range and search object of the search statement are determined; the search object includes at least one sub-object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement. For each search statement in the same search range, the number of different search objects is counted; wherein, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, the second search object is determined as the first search object. Further, according to the number of different search objects, the index setting corresponding to the search range is determined.

[0008] In an embodiment of the present invention, the client processes the currently running search statement, determines the index setting table of the current search range in the database according to the search statement, that is, gives the index setting suggestion of the current search range, helps the user optimize the index of the current search range, and improves the search efficiency.

[0009] In a possible embodiment, the client determines the search scope and search object of the search statement, including:

[0010] The client performs word segmentation on the search statement to determine the key object of the search statement. Further, the client determines the search scope and at least one sub-object from the key objects, where the sub-object includes at least one of a conditional sub-object and a sorting sub-object. Still further, the client determines the search object according to the at least one sub-object.

[0011] In the embodiment of the present invention, the client performs word segmentation on the search statement to determine the key object, and then determines the search scope and sub-objects according to the key object, which helps to analyze the sub-objects corresponding to the search scope in subsequent steps, determine the index setting table of the search scope, and helps to improve the search efficiency.

[0012] In a possible embodiment, the client determines the search scope and at least one sub-object from the key objects, including:

[0013] The keyword located between the FROM keyword and the WHERE keyword in the key object is determined as the search scope. Further, the keyword located between the WHERE keyword and the ORDER keyword in the key object is determined as the conditional sub-object. The client then determines the keyword located after the ORDER keyword in the key object as the sorting sub-object.

[0014] In the embodiment of the present invention, the client determines the search scope, conditional sub-object, and sorting sub-object according to the keyword, which helps to determine the index setting information corresponding to the search scope in subsequent steps and helps to improve the search efficiency.

[0015] In a possible embodiment, the client collects each search statement executed by the database, including: detecting the entry position and return position of the preset function. Further, the client inserts stamping code at the above entry position and the above return position to collect each search statement executed by the database.

[0016] In the embodiments of the present invention, since the functions of execute / executeQuery / executeUpdate in the class com.mysql.jdbc.PreparedStatement are mainly used to execute database statements in the database, the client inserts instrumentation code at the entry position and return position of the above execute / executeQuery / executeUpdate functions. Further, the instrumentation code obtains each search statement executed in the database, realizes the collection of the search statements, and helps to analyze according to the collected search statements executed in the database in subsequent steps to determine the index information of the database table, so as to help the user perform index setting and index optimization.

[0017] In a possible embodiment, the client determines the keywords between the FROM keyword and the WHERE keyword in the key object as the search range, including:

[0018] The keyword between the FROM keyword and the WHERE keyword is a table field;

[0019] If the table field includes a first set symbol, it is divided by the first set symbol, and the first field of each divided part is determined as the table name; otherwise, the first field in the table field is determined as the table name; the table name is used to indicate the search range.

[0020] In the embodiments of the present invention, the client determines the search range corresponding to the table field according to the FROM keyword and the WHERE keyword, which prepares for determining the index setting subsequently.

[0021] In a possible embodiment, the client determines the keywords between the WHERE keyword and the ORDER keyword in the key object as the conditional sub-object, including:

[0022] The client determines the keyword containing the second set symbol from the keywords between the WHERE keyword and the ORDER keyword;

[0023] If the keyword containing the second set symbol contains a third set symbol, the character on the right side of the third set symbol is determined as the conditional sub-object; otherwise, the character on the left side of the second set symbol is determined as the conditional sub-object.

[0024] In the embodiments of the present invention, the client determines the conditional sub-object according to the second set symbol and the third set symbol between the WHERE keyword and the ORDER keyword, which prepares for determining the index setting subsequently.

[0025] In a possible embodiment, the client determines the sorting sub-objects from the keywords after the ORDER keyword in the key object, including:

[0026] If the keywords after the ORDER keyword contain the fourth set symbol, divide by the fourth set symbol, and each divided part is determined as a sorting sub-object.

[0027] In a possible embodiment, the client determines the search object corresponding to the search statement according to at least one sub-object, including:

[0028] The client sorts each conditional sub-object according to the execution order of each conditional sub-object in the search statement to obtain an initial search object. If all sorting sub-objects are included in each conditional sub-object, the initial search object is used as the search object corresponding to the search statement. If there are sorting sub-objects among all sorting sub-objects that are not included in each conditional sub-object, the sorting of the sorting sub-objects not included in each conditional sub-object is placed after the initial search object to obtain the search object corresponding to the search statement.

[0029] In the embodiments of the present invention, the client determines the sorting of each sub-object according to the execution order of the sub-object in the search statement, further determines the conditional sub-object and the sorting sub-object, then determines the initial search object, and finally obtains the search object corresponding to the search statement. Through the above steps, the search object corresponding to the search statement is determined, and then the index setting is determined according to the search object, which helps the user to perform index setting on the tables corresponding to each search range in the database and helps improve the search efficiency.

[0030] In a possible embodiment, after the client determines the index setting corresponding to the search range, it further includes:

[0031] Compare the index setting with the actual index setting of the search range to determine the index comparison result of the search range; the actual index setting is the index table corresponding to the search range.

[0032] In the embodiments of the present invention, after the client determines the index setting, it is then compared with the corresponding actual index setting to obtain index optimization information, which helps the user to optimize the index of the table corresponding to the index range and helps improve the search efficiency.

[0033] In a possible embodiment, the client connects to the database and executes a detection command; the detection command is used to detect the search statement executed on the database. The client obtains the detection result and analyzes it according to a preset rule to determine an index analysis result, where the preset rule includes at least one of the following:

[0034] When the value of type in the detection result is all, it is determined that no index is set within the search range.

[0035] When the value of possible Keys in the detection result is null, it is determined that the search statement does not hit the index within the search range.

[0036] When the associated value of the search range in the detection result is greater than the first threshold, it is determined that the search range associated with the search statement is too large.

[0037] When the value of the number of scanned rows in the detection result is greater than the second threshold, it is determined that the restricted field of the search statement needs to be increased.

[0038] In the embodiment of the present invention, after the client connects to the database, it executes a detection command. The client analyzes the running results of the search statements corresponding to each search range in the database according to the detection command, determines the detection result, and analyzes it according to a preset rule to obtain an analysis result that is easy for the user to understand, helping the user better set the index of the search range and modify the search statement, and improving the search efficiency.

[0039] In a second aspect, an apparatus for setting a database index provided by an embodiment of the present invention includes:

[0040] An acquisition unit for acquiring each search statement executed by the database;

[0041] A processing unit for, for each search statement, determining the search range and search object of the search statement; the search object includes at least one sub-object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement;

[0042] For each search statement in the same search range, counting the number of different search objects; wherein, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, the second search object is determined as the first search object; according to the number of different search objects, determining the index setting corresponding to the search range.

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

[0044] Segment the search statement to determine the key object of the search statement;

[0045] Determine the search range and at least one sub-object from the key object; the sub-object includes at least one of a conditional sub-object and a sorting sub-object;

[0046] Determine the search object according to at least one sub-object.

[0047] The processing unit is specifically configured to:

[0048] Determine the keyword between the FROM keyword and the WHERE keyword in the key object as the search range;

[0049] Determine the keyword between the WHERE keyword and the ORDER keyword in the key object as the conditional sub-object;

[0050] Determine the keyword after the ORDER keyword in the key object as the sorting sub-object.

[0051] Optionally, the processing unit is specifically configured to: determine the keyword between the FROM keyword and the WHERE keyword as the table field; if the first set symbol is included in the table field, divide it by the first set symbol, and determine the first field of each divided part as the table name; otherwise, determine the first field in the table field as the table name; the table name is used to indicate the search range.

[0052] Optionally, the processing unit is specifically configured to: determine the keyword containing the second set symbol from the keywords between the WHERE keyword and the ORDER keyword; if the third set symbol is included in the keyword containing the second set symbol, determine the character on the right side of the third set symbol as the conditional sub-object; otherwise, determine the character on the left side of the second set symbol as the conditional sub-object.

[0053] Optionally, the processing unit is specifically configured to: if the keyword after the ORDER keyword contains the fourth set symbol, divide it by the fourth set symbol, and determine each divided part as a sorting sub-object.

[0054] Optionally, the processing unit is specifically configured to: sort each conditional sub-object according to the execution order of each conditional sub-object in the search statement to obtain an initial search object; if each sorting sub-object is included in each conditional sub-object, use the initial search object as the search object corresponding to the search statement; if there is a sorting sub-object that is not included in each conditional sub-object among the sorting sub-objects, place the sorting of the sorting sub-object that is not included in each conditional sub-object after the initial search object to obtain the search object corresponding to the search statement.

[0055] Optionally, after determining the index setting corresponding to the search range, the processing unit is further configured to: compare the index setting with the actual index setting of the search range to determine the index comparison result of the search range; the actual index setting is the index table corresponding to the search range.

[0056] Optionally, the processing unit is further configured to connect to the database and execute a detection command, where the detection command is used to detect the search statement executed on the database, and further, obtain the detection result and analyze it according to a preset rule to determine an index analysis result, where the preset rule includes at least one of the following:

[0057] When the value of type in the detection result is all, it is determined that no index is set within the search range;

[0058] When the value of possible Keys in the detection result is null, it is determined that the search statement does not hit the index within the search range;

[0059] When the associated value of the search range in the detection result is greater than the first threshold, it is determined that the search range associated with the search statement is too large;

[0060] When the value of the number of rows scanned in the detection result is greater than the second threshold, it is determined that the restrictive fields of the search statement need to be increased.

[0061] In a third aspect, an embodiment of the present invention further provides a computing device, including:

[0062] A memory for storing a computer program;

[0063] A processor for calling the computer program stored in the memory and executing the method of the first aspect above according to the obtained program.

[0064] In a sixth aspect, an embodiment of the present invention further provides a computer-readable non-volatile storage medium, including a computer-readable program, which causes a computer to execute the method of the first aspect above when the computer reads and executes the computer-readable program. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] 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 drawings in the following description are only some embodiments of the present invention, and those of ordinary skill in the art can obtain other drawings based on these drawings without creative efforts.

[0066] Figure 1 A schematic diagram of a system architecture for setting a database index provided by an embodiment of the present invention;

[0067] Figure 2 A flowchart of a method for setting a database index provided by an embodiment of the present invention;

[0068] Figure 3 A flowchart of a method for setting a database index provided by an embodiment of the present invention;

[0069] Figure 4 A logical diagram of staking provided by an embodiment of the present invention;

[0070] Figure 5 A logical diagram of staking provided by an embodiment of the present invention;

[0071] Figure 6 Schematic structural diagram of a device for setting database indexes provided by an embodiment of the present invention. Detailed implementation manners

[0072] 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 rather than all of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0073] Figure 1 An exemplary system architecture applicable to an embodiment of the present invention is shown, including a server 20 used by a bank / financial institution for database script testing. The server 20 may include at least one database, and at least one data table is included in the database.

[0074] Among them, the client 10 may be connected to the database in the server 20. Further, the database in the server 20 may run a search statement to search the data table. Exemplarily, after the client 10 is connected to the server 20, the corresponding database driver is loaded to create a database connection. Further, the database runs the search statement and outputs the search result to the client 10.

[0075] Among them, the search statements are all run in the database by a unified method. Therefore, the client 10 can capture and analyze the search statements being executed in the database through function instrumentation. Further, the client 10 determines the index settings corresponding to the search statements through analyzing the captured search statements and outputs them to the user, so as to help the user obtain the index setting information corresponding to each data table and help improve the search efficiency.

[0076] It should be noted that the above Figure 1 shown system architecture is only an example, and the embodiments of the present invention are not limited thereto.

[0077] Based on the above description, Figure 2 An exemplary flowchart of a method for setting database indexes provided by an embodiment of the present invention is shown. This process can be executed by the client 10 and includes:

[0078] Step 201, the client 10 collects each search statement executed by the database in the server 20.

[0079] Among them, the client 10 detects the entry position of a preset function and the return position of the preset function. Further, the client 10 inserts instrumentation code at the entry position and the return position and collects each search statement executed by the database.

[0080] Exemplarily, since the functions execute / executeQuery / executeUpdate in com.mysql.jdbc.PreparedStatement are mainly used to execute database statements in the database, the client inserts instrumentation code at the entry position and return position of the above execute / executeQuery / executeUpdate functions. Further, the instrumentation code obtains each search statement executed in the database, realizes the collection of the search statements, helps to analyze according to the collected search statements executed in the database in subsequent steps, determines the index information of the database table, and helps the user to perform index setting and index optimization. Further, in specific applications, the client 10 can collect the search statements running in the database through function instrumentation. Among them, the search statement can be: "select field1, field2 from tabl where field3 = xx and field4 = xx order by field1".

[0081] In step 202, for each search statement, the client 10 determines the search range and search object of the search statement. The search object includes at least one sub-object, and the at least one sub-object is sorted according to the execution order of the sub-objects in the search statement.

[0082] For example, the client 10 determines the search objects in the search statement for keywords such as "select", "where", "order by" in the above search statement "select field1, field2 from tabl where field3 = xx and field4 = xx order by field1", prepares for determining the index settings corresponding to the search statement in subsequent steps, realizes helping to improve the index optimization settings of the corresponding search objects, and finally realizes improving the search efficiency of the search statement.

[0083] In step 203, for each search statement in the same search range, the client 10 counts the number of different search objects; wherein, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, the second search object is determined as the first search object; according to the number of different search objects, the index setting corresponding to the search range is determined.

[0084]

[0085] ​In a possible example, in step 202 above, the client 10 first tokenizes the collected search statement to determine the key objects of the search statement. Then, the search scope and the at least one sub-object are determined from the key objects, where the sub-object includes at least one of a conditional sub-object and a sorting sub-object. Further, the client 10 determines the search object according to the sub-object.

[0086] For example, for the search statement "select field1, field2 from tabl where field3 = xx and field4 = xx order by field1" collected by function instrumentation in step 201 above, it is parsed, and the search statement is split into multiple groups according to spaces, as shown in Table 1:

[0087] Table 1

[0088]

[0089] In the composition of this search statement, the search statement includes the table name after the FROM keyword, the field information and the order of the fields after the WHERE condition, and the simple field names after the SECECT keyword. Among them, the simple field names after the SECECT keyword in the search statement do not affect the execution of the search statement.

[0090] Further, in a possible embodiment, the client 10 determines the keyword between the FROM keyword and the WHERE keyword in the key object as the search scope; determines the keyword between the WHERE keyword and the ORDER keyword in the key object as the conditional sub-object, and determines the keyword after the ORDER keyword in the key object as the sorting sub-object.

[0091] Further, in the above search statement, the keyword "tab1" corresponding to the search scope, the keyword corresponding to the conditional sub-object is "field3 = xx and field4 = xx", and the keyword corresponding to the sorting sub-object is "field1". In the embodiments of the present invention, by performing the above processing on each search statement, the search scope, conditional sub-object, and sorting sub-object of each search statement are obtained, which helps to determine the index setting of the search statements for the same search scope in subsequent steps, helps users improve the index setting efficiency, and thus improves the search efficiency.

[0092] Further, in a possible example, in the above step 202, the client 10 determines the keyword between the FROM keyword and the WHERE keyword as the table field. If the table field includes the first set symbol, it is divided by the first set symbol, and the first field of each divided part is determined as the table name; otherwise, the first field in the table field is determined as the table name, and the table name is used to indicate the search range.

[0093] Exemplarily, for the above search statement "select field1, field2 from tabl where field3 = xx and field4 = xx order by field1", the client 10 determines the keyword "tab1" between the FROM keyword and the WHERE keyword as the table name, representing that the search range corresponding to this search statement is tab1. In this embodiment, the search range of the search statement is determined through the above processing, preparing for the subsequent index setting of this search range and helping to improve the index setting efficiency.

[0094] Optionally, the client 10 can also determine the table name of the search range through the following judgments:

[0095] Judgment 1: If the table field is 1, it is the table name. For example, the table name corresponding to "select a.*from tbl where XX" is "tbl";

[0096] Judgment 2: If the table field is 2, the first table field is the table name and the second table field is the alias. For example, the table field corresponding to "select a.*from tbl a where XX" is "tbl";

[0097] Judgment 3: When the table field is 3 and the second one is as, the first table field is the table name and the third field is the alias. For example, the table field corresponding to "select a.*from tbl as a where XX" is "tbl";

[0098] Judgment 4: When the table field exceeds 3 and includes the first set symbol ",", that is, there are multiple table associations in this search statement. The table name and table alias are parsed respectively according to Judgment 3. For example, the table fields corresponding to "select a.id, b.id from tbl1 as a, tbl2 as b where XX" are "tbl1" and "tbl2".

[0099] Further, in a possible example, in the above step 202, the client 10 determines a keyword containing a second setting symbol and a third setting symbol from the keywords located between the WHERE keyword and the ORDER keyword; if the keyword containing the second setting symbol contains the third setting symbol, the character to the left of the second setting symbol is determined as the conditional sub-object.

[0100] Exemplarily, judging the keywords between the WHERE keyword and the ORDER keyword in the search statement field by field may include:

[0101] Judgment 1: Determine whether the keyword contains the second setting symbol "=". If it does, split it according to the second setting symbol "=" and take out the field on the left. Further, when the field on the left does not contain the third setting symbol, that is, the period ".", the field on the left is determined to be a conditional sub-object. For example, "where name = '张三'", the conditional sub-object is "name"; further, when the field on the left contains the third setting symbol, that is, the period ".", the conditional sub-object is the field of the table alias, and then split it according to the period, take out the field on the right, and determine it as a conditional sub-object. For example, "select a.*from tbl asa where a.name = '张三'", the conditional sub-object is "name". Further, when the third setting symbol "." appears in the field on the right side of the equal sign, the search statement is a table association judgment statement, split it according to the period, take out the field on the right side of the period, and determine it as a conditional sub-object. For example, "select a.*,b.*from tbl as a,tab2 as b where a.name = b.name", the conditional sub-object of table B is "name".

[0102] Decision 2: Determine whether the field is an "and" field. If so, skip it.

[0103] Judgment 3: Determine whether the field is "between". If so, continue to traverse downward until "and" appears, and skip the fields after "and", that is, the four fields "100", "200", "100", and "200" in "where id between 100and 200, that is, between 100and 200" must be skipped.

[0104] Further, in a possible example, in step 202 above, if the keyword following the ORDER keyword contains a fourth set symbol, it is divided by the fourth set symbol, and each divided part is determined as a sorting sub-object. Exemplarily, when the "order by" field corresponding to the ORDER keyword appears, the field after "order by" is taken as the sorting field, and the following judgment is made: Determine whether the subsequent field is the fourth set symbol ",", or "desc" or "asc". If so, skip it; if not, it is recorded as a sorting sub-object. For example, when the search statement part is "order by id" or "order by id desc", the corresponding sorting sub-object is "id". Further, when the search statement part is "order by id, name", the corresponding sorting fields are "id" and "name". Optionally, there is no order among the sorting sub-objects. For example, for the following search statements for the same search range tab1:

[0105] 1. select field1, field2 from tabl where field1 = xx and field2 = xx order by field3;

[0106] 2. select field1 from tabl where field1 = xx order by field2;

[0107] 3. select field3 from tabl where field3 = xx;

[0108] 4. select field3 from tabl where field3 = xx order by field2;

[0109] 5. select field2 from tabl where field3 = xx and field4 = xx order by field1;

[0110] After the above search statements are processed by step 202, the information shown in Table 2 is obtained:

[0111] Table 2

[0112] Search statement Search range Condition sub-object Sorting sub-object selct1 Tabl field1,field2 field3 Selc2 tab1 field1 field2 Selct3 tab1 field3 Selct4 tab1 field3 field2 Selct5 tab1 field2 field1

[0113] Further, in a possible example, after the client 10 determines the search scope, conditional sub-objects, and sorting sub-objects of the search statement, it further includes: sorting the conditional sub-objects according to their execution order in the search statement to obtain an initial search object. Further, if all the sorting sub-objects are included in the conditional sub-objects, then the initial search object is used as the search object corresponding to the search statement; if there are sorting sub-objects among the sorting sub-objects that are not included in the conditional sub-objects, then the sorting of the sorting sub-objects not included in the conditional sub-objects is placed after the initial search object to obtain the search object corresponding to the search statement.

[0114] Based on the above description, the obtained search objects are shown in Table 3 as follows:

[0115] Table 3

[0116] Search statement Search range Condition sub-object Sorting sub-object Search object selct1 Tabl field1,field2 field3 field1,field2,field3 selct2 tab1 field1 field2 field1,field2, selct3 tab1 field3 field3 selct4 tab1 field3 field2 field3,field2 selct5 tab1 field2 field1 field2,field1

[0117] Further, in a possible example, in step 203 above, the client 10 counts the number of search objects in each search statement for the same search scope.

[0118] Based on the above description, the sub-objects in the first search object corresponding to the search statement selct1 “select field1, field2 from tabl where field1 = xx and field2 = xx order by field3” are “field1, field2, field3”; the sub-objects in the second search object corresponding to the second search statement selct12 “select field1 from tabl where field1 = xx order by field2” are “field1, field2”. It can be seen that “field1, field2” is included in “field1, field2, field3” and the order of the sub-objects is the same. Therefore, the second search object in the second search statement is determined as the search object corresponding to the first search statement. Optionally, the second search statement can also be removed, and the weight of the first search object in the first search statement can be increased.

[0119] Further, the client 10 performs the same deduplication processing on the search statements selct3, selct4, and selct4, and finally obtains the index setting information shown in Table 4. It realizes determining the optimal index setting information for the corresponding search scope according to the number of search objects in the search statement, which helps to improve the search efficiency of the search statement.

[0120] Table 4

[0121] Search statement Search range Condition sub-object Sorting sub-object Search object selct1 Tabl field1,field2 field3 field1,field2,field3 selct4 tab1 field3 field2 field3,field2 selct5 tab1 field2 field1 field2,field1

[0122] Further, the index setting information as shown in Table 5 can be finally obtained and presented to the user in the form of text or table, helping the user to determine the optimal index setting information for the corresponding search range and improving the index setting efficiency.

[0123] Table 5

[0124] Search range Index setting Tabl field1,field2,field3 tab1 field3,field2 tab1 field2,field1

[0125] Further, in a possible example, after the above step 203, the client 10 compares the above index setting with the actual index setting of the search range to determine the index comparison result of the search range; the actual index setting is the index table corresponding to the search range.

[0126] Exemplarily, the client can use the index setting query command to query the actual index information set for the current search range, and compare it with the index setting obtained in the above step 203 to determine the optimization suggestion for the current index setting information. For example, when the above "field1, field2, field3" already exists in the actual index setting, no optimization hint is output; when the above "field3, field2" does not exist in the actual index setting, the optimization hint "Index needs to be set" is output; similarly for "field3, field2", "Index needs to be set" is output, as shown in Table 6 below. Thus, the purpose of helping the user optimize the index setting and improving the search efficiency is achieved.

[0127] Table 6

[0128] Search range Index setting Whether the actual index exists Optimization hint tabl field1,field2,field3 Exists tab1 field3,field2 Does not exist Index needs to be set tab1 field2,field1 Does not exist Index needs to be set

[0129] Based on the above description, an embodiment of the present invention further provides a method for setting a database index, as Figure 3 shown, including:

[0130] Step 301, the client 10 connects to the database and executes a detection command; the detection command is used to detect the search statement executed on the database;

[0131] In a possible embodiment, before step 301, the client 10 can perform function instrumentation on the database in the server 20 to realize real-time acquisition of each search statement running therein and its corresponding database connection information, and connect to the corresponding database according to the data connection information and execute the detection command to realize the detection of the search statement, helping the user to obtain the corresponding index setting analysis result and improving the search efficiency.

[0132] Step 302, the client 10 obtains the detection result and analyzes it according to a preset rule to determine an index analysis result, where the preset rule includes at least one of the following:

[0133] When the value of type in the detection result is all, it is determined that no index is set within the search range;

[0134] When the value of possible Keys in the detection result is null, it is determined that the search statement does not hit the index within the search range;

[0135] When the associated value of the search range in the detection result is greater than the first threshold, it is determined that the search range associated with the search statement is too large;

[0136] When the value of the number of rows scanned in the detection result is greater than the second threshold, it is determined that a limit field for the search statement needs to be added.

[0137] In a possible embodiment, after the client 10 obtains the search statement for database operation, it connects to the corresponding database, executes a detection command, such as an expalin statement, and obtains the execution result of the expalin statement, which is the detection result. Further, the client 10 analyzes the detection result according to a preset rule. When the detection result conforms to a certain rule in the preset rule library, a corresponding prompt is given, displayed to the user and provided for the user's further analysis. The analysis of the preset rule includes: obtaining the analysis result of the search statement by comparing the prediction result with the preset rule. The preset rule can also be incremented according to requirements. Exemplarily, the preset rule includes the content shown in Table 7 below:

[0138] Table 7

[0139] Detection result Output hint Type=all The SQL statement is a full table scan and no index is built Possible Keys=null The SQL statement does not hit the index When the search range associated value is greater than 3 The SQL statement joins more than 3 tables and needs to be optimized When the row scan value is greater than 10000 The Sql statement affects too many rows and optimization is recommended

[0140] By analyzing the detection result, the analysis result of the current index setting is determined, which helps to improve the index setting efficiency and the search efficiency.

[0141] In a possible embodiment, the process of the above function instrumentation includes:

[0142] By inserting search statement capture code at the entry and return points of three functions execute / executeQuery / executeUpdate in the class com.mysql.jdbc.PreparedStatement, the information of the executed search statement is obtained, as shown in Figure 4 below.

[0143] Exemplarily, the Java bytecode technology is used for instrumentation to obtain the search statement being executed in the database, and the process shown in Figure 5 below includes:

[0144] Start the application for database testing, and inject instrumentation code into the application by adding java:agent-related parameters in the startup parameters to achieve: 1. Inject instrumentation code tracking statements; 2. Generate a search statement timing collection thread (for regularly storing the obtained search statements into the analysis module of the search statements).

[0145] Furthermore, the client 10 executes the test cases of the database, and the test cases trigger the search statements in the application. At this time, the instrumentation code will capture the relevant information of the currently running search statements in real time, including database connection information and search statement information. Further, after obtaining the relevant information of the above search statements through the instrumentation code, record this information into the search statement analysis collection queue. Among them, the above-mentioned timing storage thread regularly stores the relevant information of the input search statements into the search statement analysis module for analysis in subsequent steps. Through the above instrumentation, the real-time collection of search statements is realized and the corresponding database connection information is obtained, which helps to determine the index setting information corresponding to the search statements, provides index setting suggestions for users, and improves the index setting efficiency and search efficiency.

[0146] Optionally, the client 10 can also extract the search statements through the log.

[0147] Optionally, the above Figure 3 The scheme for setting the database index can be combined with Figure 2 The scheme for setting the database index in, further improve the index setting information, provide comprehensive index setting suggestions for users, and further improve the index setting efficiency and search efficiency.

[0148] Based on the same technical concept,[[]] Figure 6 Exemplarily shows the structure of a device for setting a database index provided by an embodiment of the present invention. The device can execute the method flow of setting the above embodiment. The device can be located on the above client 10. The device specifically includes:

[0149] A collection unit 601, configured to collect each search statement executed by the database;

[0150] A processing unit 602, configured to determine the search range and search object of each search statement for each search statement; at least one sub-object is included in the search object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement;

[0151] For each search statement within the same search scope, count the number of different search objects; among them, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, then determine the second search object as the first search object; based on the number of different search objects, determine the index setting corresponding to the search scope.

[0152] Optionally, the processing unit 602 is specifically configured to: perform word segmentation on the search statement to determine the key objects of the search statement; determine the search scope and at least one sub-object from the key objects; the sub-object includes at least one of a conditional sub-object and a sorting sub-object; determine the search object according to the at least one sub-object.

[0153] The processing unit 602 is specifically configured to: determine the keyword between the FROM keyword and the WHERE keyword in the key objects as the search scope; determine the keyword between the WHERE keyword and the ORDER keyword in the key objects as the conditional sub-object; determine the keyword after the ORDER keyword in the key objects as the sorting sub-object.

[0154] Optionally, the processing unit 602 is specifically configured to: determine the keyword between the FROM keyword and the WHERE keyword as a table field; if the table field includes a first setting symbol, then divide it with the first setting symbol, and determine the first field of each divided part as the table name; otherwise, determine the first field in the table field as the table name; the table name is used to indicate the search scope.

[0155] Optionally, the processing unit 602 is specifically configured to: determine the keyword containing a second setting symbol from the keywords between the WHERE keyword and the ORDER keyword; if the keyword containing the second setting symbol contains a third setting symbol, then determine the character on the right side of the third setting symbol as the conditional sub-object; otherwise, determine the character on the left side of the second setting symbol as the conditional sub-object.

[0156] Optionally, the processing unit 602 is specifically configured to: if the keyword after the ORDER keyword contains a fourth setting symbol, then divide it with the fourth setting symbol, and determine each divided part as a sorting sub-object.

[0157] Optionally, the processing unit 602 is specifically configured to: sort each conditional sub-object according to the execution order of each conditional sub-object in the search statement to obtain an initial search object; if each sorted sub-object is included in each conditional sub-object, use the initial search object as the search object corresponding to the search statement; if there is a sorted sub-object among the sorted sub-objects that is not included in each conditional sub-object, place the sorting of the sorted sub-object that is not included in each conditional sub-object after the initial search object to obtain the search object corresponding to the search statement.

[0158] Optionally, after determining the index setting corresponding to the search range, the processing unit 602 is further configured to: compare the index setting with the actual index setting of the search range to determine the index comparison result of the search range; the actual index setting is the index table corresponding to the search range.

[0159] The processing unit 602 is further configured to connect to the database and execute a detection command, where the detection command is used to detect the search statement executed on the database. Further, obtain the detection result and analyze it according to a preset rule to determine an index analysis result, where the preset rule includes at least one of the following:

[0160] When the value of type in the detection result is all, it is determined that no index is set within the search range;

[0161] When the value of possible Keys in the detection result is null, it is determined that the search statement does not hit the index within the search range;

[0162] When the associated value of the search range in the detection result is greater than the first threshold, it is determined that the search range associated with the search statement is too large;

[0163] When the value of the number of rows scanned in the detection result is greater than the second threshold, it is determined that a limit field for the search statement needs to be added.

[0164] Based on the same technical concept, an embodiment of the present invention further provides a computing device, including:

[0165] A memory for storing program instructions;

[0166] A processor for calling the program instructions stored in the memory and executing the method for setting the database index and / or analyzing the database index as described above according to the obtained program.

[0167] Based on the same technical concept, an embodiment of the present invention further provides a computer-readable non-volatile storage medium, including computer-readable instructions, which when read and executed by a computer, cause the computer to execute the method for setting the database index and / or analyzing the database index as described above.

[0168] The present invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It should be understood that each flow and / or block in the flowchart illustrations and / or block diagrams, and combinations of flows and / or blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions executed by the processor of the computer or other programmable data processing apparatus create means for implementing the functions specified in the flowchart flow or flows and / or block or blocks. Figure 1 in a flow or flows and / or block or blocks Figure 1 or in a block or blocks.

[0169] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means that implement the functions specified in the flowchart flow or flows and / or block or blocks. Figure 1 in a flow or flows and / or block or blocks Figure 1 or in a block or blocks.

[0170] These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process, such that the instructions executed on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and / or block or blocks. Figure 1 in a flow or flows and / or block or blocks Figure 1 or in a block or blocks.

[0171] Although the preferred embodiments of the present invention have been described, additional changes and modifications can be made by those skilled in the art once they learn of the basic inventive concept. Therefore, the appended claims are intended to be construed to include the preferred embodiments and all changes and modifications that fall within the scope of the present invention.

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

Claims

1. A method for setting a database index, characterized in that, Including: Collecting each search statement executed by the database; For each search statement, determining the search scope and search object of the search statement; The search object includes at least one sub-object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement; For each search statement within the same search scope, counting the number of different search objects; wherein, if each sub-object in the second search object is included in the first search object and the order of the sub-objects is the same, then the second search object is determined as the first search object; according to the number of different search objects, determining the index setting corresponding to the search scope; Wherein, the determining the search scope and search object of the search statement includes: Performing word segmentation on the search statement to determine the key object of the search statement; Determining the search scope and the at least one sub-object from the key object; the sub-object includes at least one of a conditional sub-object and a sorting sub-object; Sorting each conditional sub-object according to the execution order of each conditional sub-object in the search statement to obtain an initial search object; If each sorting sub-object is included in each conditional sub-object, then taking the initial search object as the search object corresponding to the search statement; If there is a sorting sub-object among each sorting sub-object that is not included in each conditional sub-object, then placing the sorting of the sorting sub-object that is not included in each conditional sub-object after the initial search object to obtain the search object corresponding to the search statement.

2. The method according to claim 1, wherein The determining the search scope and the at least one sub-object from the key object includes: Determining the keyword located between the FROM keyword and the WHERE keyword in the key object as the search scope; Determining the keyword located between the WHERE keyword and the ORDER keyword in the key object as a conditional sub-object; Determining the keyword located after the ORDER keyword in the key object as a sorting sub-object.

3. The method according to claim 2, wherein The determining the keyword located between the FROM keyword and the WHERE keyword in the key object as the search scope includes: Determining the keyword located between the FROM keyword and the WHERE keyword as a table field; If the table field includes a first set symbol, then dividing with the first set symbol, and determining the first field of each divided part as the table name; otherwise, determining the first field in the table field as the table name; the table name is used to indicate the search scope.

4. The method according to claim 2, wherein The determining the keyword located between the WHERE keyword and the ORDER keyword in the key object as a conditional sub-object includes: Determining the keyword containing a second set symbol from the keywords located between the WHERE keyword and the ORDER keyword; If the keyword containing the second set symbol contains a third set symbol, then determining the character on the right side of the third set symbol as the conditional sub-object; otherwise, determining the character on the left side of the second set symbol as the conditional sub-object; Determining the keywords after the ORDER keyword in the key object as sorting sub-objects includes: If the keywords after the ORDER keyword contain a fourth set symbol, divide by the fourth set symbol, and each divided part is determined as a sorting sub-object.

5. The method according to claim 1, wherein The search statements executed by the collection database include: Detecting the entry position of the preset function and the return position of the preset function; Inserting stubs into the entry position and the return position; Collecting the search statements executed by the database.

6. The method according to any one of claims 1 to 5, characterized in that, After determining the index setting corresponding to the search range, it further includes: Comparing the index setting with the actual index setting of the search range to determine the index comparison result of the search range; the actual index setting is the index table corresponding to the search range.

7. The method according to claim 1, wherein It further includes: Connecting to the database and executing a detection command; The detection command is used to detect the search statements executed by the database; Obtaining the detection result and analyzing it according to a preset rule to determine an index analysis result, where the preset rule includes at least one of the following: When the value of type in the detection result is all, it is determined that no index is set within the search range; When the value of possible Keys in the detection result is null, it is determined that the search statement does not hit the index within the search range; When the associated value of the search range in the detection result is greater than a first threshold, it is determined that the search range associated with the search statement is too large; When the value of the number of rows scanned in the detection result is greater than a second threshold, it is determined that a limit field for the search statement needs to be added.

8. A database index setting device, characterized in that, It includes: An acquisition unit for collecting the search statements executed by the database; A processing unit for determining the search range and search object of each search statement; at least one sub-object is included in the search object, and the at least one sub-object is sorted according to the execution order of the sub-object in the search statement; The processing unit is further used to count the number of different search objects for each search statement within the same search range; where if all sub-objects in the second search object are included in the first search object and the order of the sub-objects is the same, the second search object is determined as the first search object; according to the number of different search objects, determining the index setting corresponding to the search range; Specifically, the processing unit tokenizes the search statement to determine the key object of the search statement; determines the search range and the at least one sub-object from the key object; the sub-object includes at least one of a conditional sub-object and a sorting sub-object; sorting the conditional sub-objects according to the execution order of the conditional sub-objects in the search statement to obtain an initial search object; if all sorting sub-objects are included in the conditional sub-objects, using the initial search object as the search object corresponding to the search statement; if there are sorting sub-objects that are not included in the conditional sub-objects among the sorting sub-objects, placing the sorting of the sorting sub-objects that are not included in the conditional sub-objects after the initial search object to obtain the search object corresponding to the search statement.

9. A computing device, characterized in that, It includes: A memory for storing a computer program; A processor for calling the computer program stored in the memory and executing the method according to any one of claims 1 to 7 in accordance with the obtained program.

10. A computer-readable non-volatile storage medium, characterized in that, Including a computer-readable program, when the computer reads and executes the computer-readable program, causing the computer to execute the method according to any one of claims 1 to 7.

Images