Database index optimization method and related equipment

Abstract

The embodiment of the invention discloses a database index optimization method and related equipment, which are used for improving the timeliness of index optimization so as to improve the timeliness of database management. The method comprises the steps that a historical slow query is obtained, the historical slow query comprises a query statement, and an operation object of the historical slow query is a database; determining a candidate index set based on the query statement; constructing a first optimization statement based on the historical slow query and the candidate index set; inputting the first optimization statement into a large language model to obtain an optimization index, wherein the first optimization statement is used for indicating the output of the large language model and optimizing the index of the historical slow query execution time; and updating index information of the database based on the optimized index.

Description

Technical Field

[0001] Embodiments of the present application relate to the field of databases, and in particular to database index optimization methods and related devices. Background Art

[0002] In the process of database performance optimization, index optimization for slow queries is a key means to improve query efficiency. Among them, slow queries usually refer to historical queries with too long execution times.

[0003] To ensure the operation efficiency of the database, existing technical solutions mainly achieve optimization through the following methods. First, use a recording tool or a database management console to collect the query statements of slow queries and their execution plans; then, technical personnel analyze them manually, create new indexes for the involved data tables, and verify the effectiveness of the new indexes through repeated tests.

[0004] It can be seen that the index optimization in the prior art mainly relies on manual analysis by technical personnel, and the timeliness is poor. Summary of the Invention

[0005] Embodiments of the present application provide a database index optimization method and related devices, which are used to improve the timeliness of index optimization, and further improve the timeliness of database management.

[0006] The first aspect of the embodiments of the present application provides a database index optimization method, including:

[0007] Obtain historical slow queries, where the historical slow queries include query statements, and the operation object of the historical slow queries is the database;

[0008] Determine a candidate index set based on the query statements;

[0009] Construct a first optimization statement based on the historical slow queries and the candidate index set;

[0010] Input the first optimization statement into a large language model to obtain an optimized index, where the first optimization statement is used to instruct the large language model to output an index that optimizes the execution time of the historical slow query;

[0011] Update the index information of the database based on the optimized index.

[0012] In a specific implementation manner, the determining a candidate index set based on the query statements includes:

[0013] Construct a second optimization statement based on the historical slow queries;

[0014] Input the second optimized statement into the large language model to obtain candidate indexes, where the second optimized statement is used to instruct the large language model to output candidate indexes for optimizing the execution time of the historical slow query;

[0015] Determine the candidate index set based on the candidate indexes output by the large language model.

[0016] In a specific implementation manner, the determining the candidate index set based on the query statement includes:

[0017] Obtain at least one index field from the clauses of the query statement;

[0018] Construct the candidate index set based on the at least one index field, where any element in the candidate index set is a candidate index, and each candidate index includes at least one of the index fields.

[0019] In a specific implementation manner, the historical slow query further includes the execution plan of the query statement. Before constructing the first optimized statement based on the historical slow query and the candidate index set, the method further includes:

[0020] Obtain the historical index used by the database when executing the historical slow query from the execution plan;

[0021] If any candidate index in the candidate index set is the historical index, delete the any candidate index from the candidate index set.

[0022] In a specific implementation manner, the updating the index information of the database based on the optimized index includes:

[0023] Send each optimized index and the query statement to the database;

[0024] For each optimized index, obtain the optimized execution time of the database executing the query statement based on the optimized index;

[0025] Take the optimized index corresponding to the shortest optimized execution time as the target index corresponding to the historical slow query;

[0026] Replace the historical index in the database with the target index, where the historical index is determined based on the execution plan of the query statement.

[0027] In a specific implementation manner, after updating the index information of the database based on the optimized index, the method further includes:

[0028] Receive a new query statement;

[0029] If the new query statement is the same as the query statement of the historical slow query, the database is made to execute the new query statement by invoking the target index.

[0030] The second aspect of the embodiments of the present application provides a computer device, including:

[0031] An acquisition unit, configured to acquire a historical slow query, where the historical slow query includes a query statement, and the operation object of the historical slow query is a database;

[0032] A determination unit, configured to determine a candidate index set based on the query statement;

[0033] A construction unit, configured to construct a first optimization statement based on the historical slow query and the candidate index set;

[0034] An optimization unit, configured to input the first optimization statement into a large language model to obtain an optimized index, where the first optimization statement is used to instruct the large language model to output an index for optimizing the execution time of the historical slow query;

[0035] An update unit, configured to update the index information of the database based on the optimized index.

[0036] In a specific implementation manner, the determination unit is specifically configured to construct a second optimization statement based on the historical slow query;

[0037] Input the second optimization statement into the large language model to obtain candidate indexes, where the second optimization statement is used to instruct the large language model to output candidate indexes for optimizing the execution time of the historical slow query;

[0038] Determine the candidate index set based on the candidate indexes output by the large language model.

[0039] In a specific implementation manner, the determination unit is specifically configured to obtain at least one index field from the clauses of the query statement;

[0040] Construct the candidate index set based on the at least one index field, where any element in the candidate index set is a candidate index, and each candidate index includes at least one of the index fields.

[0041] In a specific implementation manner, the historical slow query further includes an execution plan of the query statement. Before constructing the first optimization statement based on the historical slow query and the candidate index set, the computer device further includes: a deletion unit;

[0042] The acquisition unit is further configured to obtain a historical index used by the database when executing the historical slow query from the execution plan;

[0043] The deletion unit is configured to delete any candidate index in the candidate index set from the candidate index set if the any candidate index in the candidate index set is the historical index.

[0044] In a specific implementation, the update unit is specifically configured to send each of the optimized indexes and the query statement to the database;

[0045] For each of the optimized indexes, obtain the optimized execution time of the database executing the query statement based on the optimized index;

[0046] Use the optimized index corresponding to the shortest optimized execution time as the target index corresponding to the historical slow query;

[0047] Replace the historical index in the database based on the target index, where the historical index is determined based on the execution plan of the query statement.

[0048] In a specific implementation, after updating the index information of the database based on the optimized index, the computer device further includes: a receiving unit and an execution unit;

[0049] The receiving unit is configured to receive a new query statement;

[0050] The execution unit is configured to, if the new query statement is the same as the query statement of the historical slow query, cause the database to execute the new query statement by invoking the target index.

[0051] A third aspect of the embodiments of the present application provides a computer device, including:

[0052] A central processing unit, a memory, and an input / output interface;

[0053] The memory is a transient storage memory or a persistent storage memory;

[0054] The central processing unit is configured to communicate with the memory and execute the instruction operations in the memory to execute the method described in the first aspect.

[0055] A fourth aspect of the embodiments of the present application provides a computer program product containing instructions, which when the computer program product runs on a computer, causes the computer to execute the method described in the first aspect.

[0056] A fifth aspect of the embodiments of the present application provides a computer storage medium, where instructions are stored in the computer storage medium, and when the instructions are executed on a computer, the computer is caused to execute the method described in the first aspect.

[0057] As can be seen from the above technical solutions, the embodiments of the present application have the following advantages: After obtaining the historical slow query, a candidate index set is determined according to the query statement in this historical slow query. Then, based on the historical slow query and the candidate index set, a first optimized statement is constructed, and this first optimized statement is used to instruct the large language model to output an index that optimizes the execution time of the historical slow query. Finally, the database index information is updated according to the optimized index obtained after inputting the first optimized statement into the large language model. After collecting the historical slow query, the ability of the large model is utilized to complete the index optimization of the corresponding query statement. When the database receives the same query statement again, it can be processed efficiently. While greatly reducing the labor cost, the operation and maintenance efficiency and system availability are improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] Figure 1 FIG. is a system architecture diagram of a database index optimization method disclosed in an embodiment of the present application;

[0059] Figure 2 FIG. is a schematic flowchart of a database index optimization method disclosed in an embodiment of the present application;

[0060] Figure 3 FIG. is a schematic structural diagram of a computer device disclosed in an embodiment of the present application;

[0061] Figure 4 FIG. is another schematic structural diagram of a computer device disclosed in an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0062] Next, the technical solutions in the embodiments of the present application will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present application.

[0063] The embodiments of the present application provide a database index optimization method and related devices, which are used to improve the timeliness of index optimization, and further improve the timeliness of database management.

[0064] It should be noted that the query statements mentioned in the foregoing and subsequent content of the present application refer to statements or instructions used to manage databases, perform data operations and analysis. For example, for relational databases, query statements based on structured language (SQL, structure query language) can be used, and for MongoDB databases, query statements described based on MongoDB query language (MQL, MongoDB query language) can be used, etc. No specific limitation is made here.

[0065] To better implement the database index optimization method of the embodiments of the present application, the present application provides a database index optimization framework. Please refer to Figure 1 , the database index optimization framework of the embodiments of the present application includes an optimization module 101 for deploying the database index optimization method of the present application and a database module 102 for deploying the database. Among them, the optimization module 101 is used to obtain historical slow queries with too long execution time or too high resource occupancy rate of the database during execution from the database module 102, and execute the database index optimization method described in any embodiment of the present application to obtain optimized indexes that can reduce the execution time of the query statements in the historical slow queries, and update the index information of the database in the database module 102 based on the optimized indexes. Thereafter, when the database receives a new query statement that is the same as the foregoing query statement, it can use the foregoing optimized indexes to complete the execution of the new query statement faster.

[0066] It should be noted that the optimization module 101 and the database module 102 of the embodiments of the present application can be respectively set in different computer devices or different computer device clusters, or set in the same computer device or the same computer device cluster, which is not limited herein.

[0067] Please refer to Figure 2 , the embodiments of the present application provide a database index optimization method, including the following steps:

[0068] 201. Obtain historical slow queries. The historical slow queries include query statements, and the operation object of the historical slow queries is the database. Among them, the operation object of the historical slow queries refers to the database for executing the historical slow queries.

[0069] The object optimized by the database index optimization method of the embodiments of the present application is a query whose execution time exceeds the preset execution time or the occupancy rate of the database during execution exceeds the preset occupancy rate threshold, that is, a slow query. It can be understood that since slow queries are identified by the execution status of the queries (execution time and the occupancy rate of the database during execution), all identified slow queries are queries that the database has already executed. In addition, all slow queries that can be optimized for indexes can be screened out from the database through the above method, and each slow query obtained by screening can be used as a historical slow query; or the slow queries obtained by screening are screened again to screen out high-frequency slow queries such as those with a large number of occurrences or a high occurrence probability as historical slow queries, and finally, the obtained historical slow queries are optimized for indexes through steps 201 to 205 and related embodiments.

[0070] Taking a historical slow query as an example below, the database index optimization method of the embodiments of the present application is described.

[0071] Among them, the database can perform corresponding database management operations by executing the query statements included in the historical slow queries, such as performing operations on indexes, data tables, and even adding, deleting, modifying, and querying indexes.

[0072] 202. Determine a candidate index set based on the query statement.

[0073] The query statement usually accurately describes the fields to be accessed for the database to execute. An index is a data structure built based on one or more fields (table headers). Each index contains multiple index entries, where each index entry consists of the field value of the corresponding field and its corresponding physical location identifier of the data row. For example, if an index is established for the name field of the user table, the entire index is a retrieval structure covering all names, and a single index entry specifically refers to the association information between the specific name "Zhang San" and its data row, similar to the relationship between the entry "Zhang San" and its contact information in a phone book. The index is the entire phone book, and the index entry is each record in it.

[0074] For example, there is an SQL statement as follows: SELECT name, age, salary FROM employees WHERE age > 30 AND salary > 5000. The meaning of this SQL query statement is: from the data table named employees, filter out all employees whose age (age) is greater than 30 and salary (salary) is greater than 5000, and return the names (name), ages (age), and salaries (salary) of these employees. The query sets two conditions through the WHERE clause, requiring the age to be greater than 30 and the salary to be greater than 5000. Only the records that meet both conditions will be returned. Then, the indexes that can be built include the index based on the age field, the index based on the salary field, and the index based on the salary field and the age field. And these indexes that can be built, or rather the candidate indexes, constitute the candidate index set of the aforementioned SQL.

[0075] 203. Build a first optimized statement based on the historical slow query and the candidate index set.

[0076] After obtaining the candidate index set that can be used to optimize the execution time of the historical slow query, the embodiments of the present application can build a first optimized statement based on the historical slow query and the candidate index set. Among them, the query statement in the historical slow query determines the way for the database to execute it, and each candidate index in the candidate index set represents an index that the database can use when executing the aforementioned query statement. Therefore, building the first optimized statement including the historical slow query and the candidate index set can help the large language model accurately output the indexes that can optimize the execution time of the historical slow query.

[0077] In addition, the first optimized statement of the embodiments of the present application can be obtained by filling the corresponding positions in the first optimization template with the historical slow queries and the candidate index set respectively, or directly using the historical slow queries and the candidate index set as the first optimized statement that can be input into the large model. The embodiments of the present application do not make any limitations. One specific example of the first optimization template is as follows: "I now have an SQL: [filling position of historical slow query], and the candidate indexes for this SQL are: [filling position of candidate index set]. Please help me select an index that can optimize the execution time of this SQL."

[0078] In some specific implementation manners, the historical slow query of the embodiments of the present application further includes the execution plan adopted by the database when executing the query statement. Generally, the execution plan of the query statement includes the detailed query execution process, including access type, used index, scanned row count, etc., which helps to accurately judge the query performance bottleneck, can more precisely select and optimize indexes, and improve the optimization efficiency.

[0079] 204. Input the first optimized statement into the large language model to obtain an optimized index, where the first optimized statement is used to instruct the large language model to output an index that optimizes the execution time of the historical slow query.

[0080] Based on the foregoing step 203, the first optimized statement can guide the large language model to output an optimized index that makes the execution time of the foregoing query statement shorter (or optimizes the execution time of the foregoing query statement). Therefore, after inputting the first optimized statement into the large language model, the large language model will output the corresponding optimized index.

[0081] 205. Update the index information of the database based on the optimized index.

[0082] Update the index information in the database by using the optimized index output by the large language model, so that when the database receives a new query statement that is the same as the foregoing query statement again, it can use at least some of the optimized indexes obtained in step 204, thereby improving the execution efficiency of the database when executing this new query statement.

[0083] In the embodiments of the present application, after collecting the historical slow queries, the ability of the large model is used to complete the index optimization of the corresponding query statements. When the database receives the same query statement again, it can efficiently complete the processing, effectively enhancing the ability of system automated operation and maintenance, greatly reducing manual intervention, and reducing the incidence of manual operations. During the non-working unattended time, it can also solve the slow query problems existing in the database, improve the availability of the system, and ensure the continuity of system services and the stability of user experience.

[0084] In some specific implementation manners, step 202 of this application can also be implemented in the following manner: obtaining at least one index field from the clauses of a query statement; constructing a candidate index set based on the at least one index field, where any element in the candidate index set is a candidate index, and each candidate index includes at least one index field.

[0085] First of all, it should be noted that the index fields in an index must be columns that actually exist in the database table. Therefore, the index fields obtained by the embodiments of this application from the clauses of the query statement should be columns that actually exist in the database table.

[0086] If the query statement is SQL, the query statement usually includes multiple clauses, and these multiple clauses contain the index fields to be queried. For example, there is an SQL as shown below: SELECT name, age, salary FROM employees WHERE age>30 AND salary>5000, which includes three clauses, namely SELECT name, age, salary, FROM employees, and WHERE age>30 AND salary>5000. Among them, SELECT, FROM, and WHERE are the keywords corresponding to the respective clauses, employees is the data source table, and name, age, and salary are respectively columns that actually exist in the database table. Based on the above content, it can be seen that not all fields in the clauses belong to the columns that actually exist in the database table, and it may also be the name of the database table in the FROM field. Specifically, the embodiments of this application can obtain the index fields from the query statement by means including but not limited to regular expression matching and SQL parser (AST traversal), etc.

[0087] Next, an index can be a single-column index, a composite index, or a covering index, etc. Therefore, in addition to constructing a candidate index based on each index field, candidate indexes can also be constructed based on any number of index fields. For example, if the index fields include name and age, then the candidate index set that can be constructed includes {name}, {age}, {name, age}, {age, name}.

[0088] It should be noted that an important principle in database index usage is the LeftmostPrefix Rule, which is mainly applied to the query optimization of multi-column indexes (composite indexes). The core idea of this principle is that in a multi-column index, the query conditions must start from the leftmost column and match sequentially to the right in the order of the index columns. Therefore, in order to efficiently utilize the index, the order of the index fields in the index needs to be consistent with the filtering order of the conditions in the query statement. Thus, {name, age} and {age, name} belong to two different indexes.

[0089] In another implementation, step 202 can be specifically implemented as follows: constructing a second optimization statement based on historical slow queries; inputting the second optimization statement into a large language model to obtain candidate indexes, where the second optimization statement is used to instruct the large language model to output candidate indexes that optimize the execution time of the historical slow query; and determining a candidate index set based on the candidate indexes output by the large language model.

[0090] To fully utilize the capabilities of the large language model, embodiments of this application can use the large language model to output candidate indexes and form a candidate index set with the candidate indexes output by the large language model as elements. Specifically, different from steps 203 - 204, in the process of obtaining the candidate index set in embodiments of this application, no alternative indexes (such as the candidate indexes in steps 203 - 204) are input to the large language model.

[0091] The same is that the query statement in the historical slow query determines the way the database executes it and the index fields available for selection in the process of constructing candidate indexes (i.e., the columns actually existing in the database table in the clause). Therefore, constructing a second optimization statement including the historical slow query can help the large language model accurately output candidate indexes that can optimize the execution time of the historical slow query.

[0092] In addition, the second optimization statement in embodiments of this application can be obtained by filling the historical slow query into the corresponding position in the second optimization template, or directly using the historical slow query as the second optimization statement that can be input into the large model. Embodiments of this application do not make a limitation. One specific example of the second optimization template is as follows: "I now have an SQL: [filling position of the historical slow query], please help me select an index that can optimize the execution time of this SQL."

[0093] In some specific implementations, the historical slow query in embodiments of this application further includes the execution plan adopted by the database when executing the query statement. Generally, the execution plan of the query statement contains the detailed query execution process, including access type, used index, scanned row count, etc., which helps the large language model accurately judge the query performance bottleneck, can more accurately select candidate indexes, and improve the optimization efficiency.

[0094] Based on the foregoing embodiments, in practical applications, the historical slow query of the embodiments of the present application further includes the execution plan of the query statement. Before the foregoing step 203, the method of the embodiments of the present application further includes: obtaining the historical index used by the database when executing the historical slow query from the execution plan; if any candidate index in the candidate index set is a historical index, deleting any candidate index from the candidate index set.

[0095] Before step 201 of the embodiments of the present application, the database has already executed a historical slow query (that is, the database has already executed the query statement in the historical slow query). Therefore, the execution plan of the query statement in the historical slow query of the embodiments of the present application is the one used by the database when executing the foregoing query statement before.

[0096] Considering that the goal of optimizing the database index in the embodiments of the present application is to shorten the time for the database to execute the foregoing historical slow query, it is obvious that using the foregoing historical index cannot meet this optimization goal. Therefore, the embodiments of the present application need to delete the historical index in the execution plan from the candidate index set to reduce the screening range and processing efficiency of the large language model to obtain an optimized index based on the candidate index set and the historical slow query.

[0097] Further, the foregoing step 205 can be specifically implemented through the following steps: sending each optimized index and the query statement to the database; for each optimized index, obtaining the optimized execution time for the database to execute the query statement based on the optimized index; taking the optimized index corresponding to the shortest optimized execution time as the target index corresponding to the historical slow query; replacing the historical index in the database based on the target index, and the historical index is determined based on the execution plan of the query statement.

[0098] It can be understood that when the output accuracy of the large language model is limited, the embodiments of the present application will also introduce a performance evaluation process in step 205. Specifically, the embodiments of the present application execute the database to execute the query statement based on different optimized indexes and obtain the corresponding optimized execution times. Based on the optimized execution times corresponding to different optimized indexes, the performance optimization effects that can be obtained when the database executes the foregoing query statement based on different optimized indexes can be well evaluated.

[0099] It should be noted that considering that the historical index may have an optimization effect on other historical queries, in addition to directly replacing the historical index in the database based on the target index, the embodiments of the present application can also add the target index to the database while retaining the historical index. Furthermore, in order to ensure that the database has good index performance, the embodiments of the present application can also regularly delete less frequently used low-frequency indexes, and the determination conditions for low-frequency indexes can be configured as needed, which are not limited in the embodiments of the present application.

[0100] In addition, to ensure that the optimized index has a better optimization effect than the historical index corresponding to the historical slow query, the embodiment of the present application may further introduce a step of determining whether the shortest optimized execution time is less than the historical execution time, and only when the shortest optimized execution time is less than the historical execution time, replace the historical index in the database based on the target index. The historical execution time refers to the time when the database executed the foregoing query statement before the foregoing step 201.

[0101] Furthermore, after the foregoing step 205, the method of the embodiment of the present application may also complete the execution of the new query statement in the following manner, which specifically includes the following steps: receiving a new query statement; if the new query statement is the same as the query statement of the historical slow query, then cause the database to call the target index to execute the new query statement.

[0102] Specifically, after updating the index information in the database based on the optimized index, if the database receives a new query statement that is the same as the query statement in step 201 again, the execution of the new query statement can be completed with the support of the foregoing target index recorded in the database. Based on the foregoing embodiments, it can be known that the execution time of the new query statement in the embodiment of the present application must be shorter than the historical execution time corresponding to the foregoing query statement.

[0103] It can be understood that the advantage of the embodiment of the present application is that after updating the index information in the database based on the optimized index, when the same new query request is received later, it can be executed with higher efficiency. Therefore, the historical slow query obtained in step 201 of the present application should be a high-frequency query that will occur repeatedly. Specifically, the embodiment of the present application can identify the historical slow query in step 201 through means such as a database monitoring platform, enabling slow query logs, and parsing the execution plan of SQL in information management systems such as enterprise resource planning (ERP), enterprise management systems, financial systems, human resource systems, and supply chain systems.

[0104] Please refer to Figure 3 , the second aspect of the embodiment of the present application provides a computer device, including:

[0105] An obtaining unit 301, configured to obtain a historical slow query, where the historical slow query includes a query statement, and the operation object of the historical slow query is a database;

[0106] A determining unit 302, configured to determine a candidate index set based on the query statement;

[0107] A constructing unit 303, configured to construct a first optimization statement based on the historical slow query and the candidate index set;

[0108] Optimization unit 304, configured to input the first optimization statement into a large language model to obtain an optimization index, where the first optimization statement is used to instruct the large language model to output an index for optimizing the execution time of historical slow queries;

[0109] Update unit 305, configured to update the index information of the database based on the optimization index.

[0110] In a specific implementation, the determination unit 302 is specifically configured to construct a second optimization statement based on historical slow queries;

[0111] Input the second optimization statement into the large language model to obtain candidate indexes, where the second optimization statement is used to instruct the large language model to output candidate indexes for optimizing the execution time of historical slow queries;

[0112] Determine a candidate index set based on the candidate indexes output by the large language model.

[0113] In a specific implementation, the determination unit 302 is specifically configured to obtain at least one index field from the clauses of the query statement;

[0114] Construct a candidate index set based on the at least one index field, where any element in the candidate index set is a candidate index, and each candidate index includes at least one index field.

[0115] In a specific implementation, the historical slow query further includes an execution plan of the query statement. Before constructing the first optimization statement based on the historical slow query and the candidate index set, the computer device further includes: a deletion unit;

[0116] The acquisition unit 301 is further configured to obtain the historical index used by the database when executing the historical slow query from the execution plan;

[0117] The deletion unit is configured to delete any candidate index from the candidate index set if any candidate index in the candidate index set is a historical index.

[0118] In a specific implementation, the update unit 305 is specifically configured to send each optimization index and the query statement to the database;

[0119] For each optimization index, obtain the optimized execution time of the database for executing the query statement based on the optimization index;

[0120] Use the optimization index corresponding to the shortest optimized execution time as the target index corresponding to the historical slow query;

[0121] Replace the historical index in the database based on the target index, where the historical index is determined based on the execution plan of the query statement.

[0122] In a specific implementation manner, after updating the index information of the database based on the optimized index, the computer device further includes: a receiving unit and an execution unit;

[0123] The receiving unit is configured to receive a new query statement;

[0124] The execution unit is configured to, if the new query statement is the same as the query statement of the historical slow query, cause the database to call the target index to execute the new query statement.

[0125] Figure 4 FIG. 400 is a schematic structural diagram of a computer device provided by an embodiment of the present application. The computer device 400 may include one or more central processing units (CPUs) 401 and a memory 405. One or more application programs or data are stored in the memory 405.

[0126] Among them, the memory 405 may be volatile storage or persistent storage. The program stored in the memory 405 may include one or more modules, and each module may include a series of instruction operations on the computer device. Further, the central processing unit 401 may be configured to communicate with the memory 405 and execute a series of instruction operations in the memory 405 on the computer device 400.

[0127] The computer device 400 may further include one or more power supplies 402, one or more wired or wireless network interfaces 403, one or more input / output interfaces 404, and / or one or more operating systems, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc.

[0128] The central processing unit 401 may execute the operations performed by the optimization module 101 and / or the database module 102 in the foregoing Figures 1 to 3 illustrated embodiments, which will not be elaborated herein specifically.

[0129] It should be noted that although the steps in the flowcharts involved in the embodiments are drawn in sequence according to the arrows, unless otherwise clearly stated in this article, the execution of these steps is not strictly limited in order, and these steps may be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily executed at the same moment, but may be executed at different moments. The execution order of these steps or stages is not necessarily sequential, but may be executed alternately or alternately with at least a part of other steps or steps or stages in other steps.

[0130] Those skilled in the art can clearly understand that for the convenience and brevity of description, the specific working processes of the systems, devices, and units described above can refer to the corresponding processes in the foregoing method embodiments and will not be elaborated herein.

[0131] In several embodiments provided in the present application, it should be understood that the disclosed systems, devices, and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of the units is only a logical function division, and there can be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the displayed or discussed couplings or direct couplings or communication connections to each other can be through some interfaces, and the indirect couplings or communication connections of the devices or units can be in electrical, mechanical, or other forms.

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

[0133] In addition, in each embodiment of the present application, the functional units can be integrated into one processing unit, or each unit can exist physically alone, or two or more units can be integrated into one unit. The above-mentioned integrated units can be implemented in the form of hardware or in the form of software functional units.

[0134] If the above-mentioned integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in each embodiment of the present application. The foregoing storage medium includes: various media such as USB flash drives, mobile hard disks, read-only memories (ROM, read-only memory), random access memories (RAM, random access memory), magnetic disks, or optical discs that can store program codes.

[0135] An embodiment of the present application further provides a computer program product including instructions. When the computer program product runs on a computer, it causes the computer to execute the database index optimization method as described above.

Claims

1. A database index optimization method, characterized in that: include: Obtain a historical slow query, where the historical slow query includes a query statement, and an operation object of the historical slow query is a database; Determine a candidate index set based on the query statement; Building a first optimization statement based on the historical slow query and the candidate index set; Inputting the first optimization statement into the large language model to obtain an optimization index, wherein the first optimization statement is used to instruct the large language model to output and optimize the index of the historical slow query execution time; The index information of the database is updated based on the optimized index.

2. The database index optimization method according to claim 1, characterized in that: The determining a candidate index set based on the query statement includes: Constructing a second optimization statement based on the historical slow query; Inputting the second optimization statement into the large language model to obtain a candidate index, wherein the second optimization statement is used to instruct the large language model to output a candidate index that optimizes the execution time of the historical slow query; The candidate index set is determined based on the candidate index output by the large language model.

3. The database index optimization method according to claim 1, characterized in that: The determining a candidate index set based on the query statement includes: Obtain at least one index field from a clause of the query statement; The candidate index set is constructed based on the at least one index field, any element in the candidate index set is a candidate index, and each candidate index includes at least one index field.

4. The database index optimization method according to claim 2 or 3, characterized in that: The historical slow query also includes an execution plan of the query statement. Before constructing a first optimization statement based on the historical slow query and the candidate index set, the method further includes: Obtaining, from the execution plan, a historical index used by the database when executing the historical slow query; If any candidate index in the candidate index set is the historical index, the candidate index is deleted from the candidate index set.

5. The database index optimization method according to any one of claims 1 to 3, characterized in that: The updating of the index information of the database based on the optimized index includes: Sending each of the optimized indexes and the query statement to the database; For each of the optimized indexes, obtaining the optimized execution time of the query statement executed by the database based on the optimized index; The optimization index corresponding to the shortest optimization execution time is used as the target index corresponding to the historical slow query; A historical index in the database is replaced based on the target index, where the historical index is determined based on an execution plan of the query statement.

6. The database index optimization method according to claim 5, characterized in that: After updating the index information of the database based on the optimized index, the method further includes: Receive new query statements; If the new query statement is consistent with the query statement of the historical slow query, the database calls the target index to execute the new query statement.

7. A computer device, characterized in that: include: An acquisition unit, used for acquiring a historical slow query, wherein the historical slow query includes a query statement, and an operation object of the historical slow query is a database; A determination unit, configured to determine a candidate index set based on the query statement; A construction unit, configured to construct a first optimization statement based on the historical slow query and the candidate index set; an optimization unit, configured to input the first optimization statement into a large language model to obtain an optimization index, wherein the first optimization statement is used to instruct the large language model to output and optimize the index of the historical slow query execution time; An updating unit is used to update the index information of the database based on the optimized index.

8. A computer device, characterized in that: include: CPU, memory and input / output interface; The memory is a short-term storage memory or a persistent storage memory; The central processing unit is configured to communicate with the memory and execute instructions in the memory to perform the database index optimization method according to any one of claims 1 to 6.

9. A computer program product comprising instructions, characterized in that When the computer program product is run on a computer, the computer is enabled to execute the database index optimization method according to any one of claims 1 to 6.

10. A computer storage medium, characterized in that: The computer storage medium stores instructions, and when the instructions are executed on a computer, the computer executes the database index optimization method according to any one of claims 1 to 6.

Images