Index optimization method and device, electronic equipment and storage medium

Abstract

The invention provides an index optimization method and device, electronic equipment and a storage medium, and relates to the technical field of database query. The method comprises the steps that a query log of a database is analyzed, query detailed information is obtained, the query log comprises at least one query statement, and the query detailed information comprises query conditions indicated by the query statement, sorting fields, table connection relations and grouping information; the detailed query information is analyzed and counted to obtain query statistical data, and the query statistical data comprises the field access frequency, the data distribution condition and the index use condition; generating at least one candidate query path based on the query statistical data, wherein each candidate query path comprises a query index and a use sequence of the query index; and performing query cost evaluation on the at least one candidate query path, and determining a recommended index scheme. The method can automatically recommend a proper index scheme and improve the index optimization efficiency.

Description

Technical Field

[0001] The present application relates to the field of database query technology, and in particular to an index optimization method, device, electronic device, and storage medium. Background Art

[0002] Indexes are data structures within a database that accelerate data retrieval. Currently, database index optimization relies primarily on the DBA (database administrator) performing execution plan analysis (such as the EXPLAIN command), manually evaluating query patterns and index usage, and then creating or adjusting indexes based on experience. This results in less intelligent index recommendations, relying on manual experience, resulting in high workload and low efficiency. Summary of the Invention

[0003] This application provides an index optimization method, device, electronic device, and storage medium that can automatically recommend the best indexing solution. The technical solution is as follows:

[0004] According to one aspect of the present application, a method for index optimization is provided, the method comprising:

[0005] Parsing a query log of a database to obtain query details, wherein the query log includes at least one query statement, and the query details include query conditions, sorting fields, table connection relationships, and grouping information indicated by the query statement;

[0006] Analyze and collect statistics on the query details to obtain query statistics, including field access frequency, data distribution, and index usage;

[0007] generating at least one candidate query path based on the query statistical data, each candidate query path including a query index and a usage order of the query index;

[0008] A query cost evaluation is performed on the at least one candidate query path to determine a recommended indexing scheme.

[0009] According to another aspect of the present application, an index optimization device is provided, the device comprising:

[0010] A parsing module, configured to parse a query log of a database to obtain detailed query information, wherein the query log includes at least one query statement, and the detailed query information includes query conditions, sorting fields, table connection relationships, and grouping information indicated by the query statement;

[0011] An analysis module is used to analyze and collect statistics on the query details to obtain query statistics, wherein the query statistics include field access frequency, data distribution and index usage;

[0012] A first generating module is configured to generate at least one candidate query path based on the query statistical data, each candidate query path including a query index and a usage order of the query index;

[0013] The determination module is configured to perform query cost evaluation on the at least one candidate query path and determine a recommended indexing scheme.

[0014] According to one aspect of the present application, an electronic device is provided, including: a processor and a memory storing a program, wherein the program includes instructions, and when the instructions are executed by the processor, the processor executes the index optimization method described above.

[0015] According to another aspect of the present application, a non-transitory computer-readable storage medium storing computer instructions is provided, wherein the computer instructions are used to enable the computer to execute the index optimization method as described above.

[0016] According to another aspect of the present application, a computer program product is provided, the computer program product including computer instructions stored in a computer-readable storage medium. A processor of an electronic device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the above-mentioned index optimization method.

[0017] The beneficial effects of the technical solutions provided in the embodiments of the present application include at least:

[0018] In summary, the embodiment of the present application provides an intelligent index optimization method: by automatically parsing the query log of the database to obtain detailed query information, and analyzing and counting the detailed query information to obtain query statistical data, and then automatically generating at least one candidate query path based on the query statistical data, and determining the recommended index scheme by performing query cost evaluation on the candidate query path. The data index optimization tool can automatically parse SQL query statements, analyze query conditions, joined table fields and sorting fields, and automatically recommend appropriate index schemes in combination with the database table structure. This reduces the manual intervention of database administrators in the index optimization process and significantly improves the efficiency of index optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Further details, features and advantages of the present application are disclosed in the following description of exemplary embodiments in conjunction with the accompanying drawings, in which:

[0020] Figure 1 A flowchart of an index optimization method according to an exemplary embodiment of the present application is shown;

[0021] Figure 2A flowchart of another index optimization method according to an exemplary embodiment of the present application is shown;

[0022] Figure 3 This is a system architecture diagram of a data index optimization tool provided by an exemplary embodiment of the present application;

[0023] Figure 4 This is a schematic diagram of the structure of an index optimization device provided in an embodiment of the present application;

[0024] Figure 5 A structural block diagram of an exemplary electronic device that can be used to implement the embodiments of the present application is shown. DETAILED DESCRIPTION

[0025] The following describes embodiments of the present application in more detail with reference to the accompanying drawings. Although certain embodiments of the present application are shown in the accompanying drawings, it should be understood that the present application can be implemented in various forms and should not be construed as limited to the embodiments described herein. Instead, these embodiments are provided to provide a more thorough and complete understanding of the present application. It should be understood that the drawings and embodiments of the present application are for illustrative purposes only and are not intended to limit the scope of protection of the present application.

[0026] It should be understood that the various steps described in the method embodiments of the present application can be performed in different orders and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present application is not limited in this respect.

[0027] The term "including" and its variations used herein are open inclusions, i.e., "including but not limited to". The term "based on" means "based at least in part on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one other embodiment"; and the term "some embodiments" means "at least some embodiments". The relevant definitions of other terms will be given in the following description. It should be noted that the concepts of "first" and "second" mentioned in this application are only used to distinguish different devices, modules or units, and are not used to limit the order or interdependence of the functions performed by these devices, modules or units. It should be noted that the modifiers of "one" and "a plurality of" mentioned in this application are illustrative and not restrictive. Those skilled in the art should understand that unless the context clearly indicates otherwise, they should be understood as "one or more". The names of the messages or information exchanged between multiple devices in the embodiments of this application are for illustrative purposes only and are not used to limit the scope of these messages or information.

[0028] The following describes the solution of the present application with reference to the accompanying drawings, and illustrates the technical solution provided by the embodiments of the present application in detail through specific embodiments and their application scenarios.

[0029] Currently, database index optimization primarily relies on database administrators (DBAs) performing execution plan analysis (such as the EXPLAIN command), manually evaluating query patterns and index usage, and then creating or adjusting indexes based on experience. Index recommendations are not intelligent and rely heavily on manual experience, resulting in high workload and low efficiency.

[0030] In order to solve the problems caused by manual index adjustment, this application embodiment provides an intelligent index optimization method. Figure 1 , which shows a flow chart of an index optimization method according to an exemplary embodiment of the present application. The method is applied to an electronic device as an example for exemplary description. Figure 1 As shown, the method includes:

[0031] Step 101: parse the query log of the database to obtain query details. The query log includes at least one query statement. The query details include the query condition indicated by the query statement, sorting fields, table connection relationships, and grouping information.

[0032] To address the current issues associated with manual index adjustment, the present invention provides a data index optimization tool that, by invoking the data index optimization tool, enables intelligent index optimization. The data index optimization tool primarily consists of a SQL (Structured Query Language) parsing module, a query analysis and statistics module, a cost model optimization module, an index recommendation module, an index maintenance module, and a log and reporting module.

[0033] In one possible implementation, when triggering the index optimization task on the database, the SQL query log is first obtained from the database, and the query log is parsed by the SQL parsing module in the data index optimization tool. In the SQL parsing module, each query statement in the query log will be decomposed into an AST (Abstract Syntax Tree). AST is a data structure that can help the system understand the various components in the query statement, such as query conditions, sorting fields, table connection relationships, grouping information, etc., so as to obtain detailed query information including query conditions, sorting fields, table connection relationships and grouping information. In other words, the query log is input into the SQL parsing module, and the AST is output to provide query details of the query statement. The AST structure will be passed to the query analysis and statistics module for further analysis.

[0034] A query statement is used to retrieve user identity information in a table that is over 18 years old, sorted by age and grouped by gender. "Over 18" is the query condition, "Age in descending order" is the sorting field, and "Gender" is the grouping information. For example, if Table A is queried, and Table B has an association, then the association between Table A and Table B is a table join.

[0035] Optionally, in addition to inputting query logs into the SQL parsing module, real-time SQL query statements may also be input.

[0036] Step 102 : Analyze and collect statistics on the query details to obtain query statistics, which include field access frequency, data distribution, and index usage.

[0037] After obtaining the query detailed information (AST) output by the SQL parsing module, the query detailed information is input into the query analysis and statistics module, which analyzes and counts the query detailed information to obtain query statistical data, which includes field access frequency, data distribution and index usage.

[0038] The analysis process of the query analysis and statistics module includes: extracting the key parts of the query statement from the AST, including query conditions, table connection relationships, sorting fields, etc., to determine the relevant database tables that the query statement ultimately queries, and analyzing the structural information of the relevant database tables, including: obtaining metadata information about the database tables (such as field types, data distribution, the size of the relevant database tables, etc.); then pulling statistical information from the database (such as the distribution of each field in the table, index usage, etc.) for analysis. Finally, the query analysis and statistics module generates statistical data based on the analysis results. The statistical data includes field access frequency, data distribution, and the usage status of existing indexes (i.e., index usage). The data distribution indicates the duplication of each field in the table; the usage status of existing indexes indicates the frequency of use of existing indexes in the database.

[0039] Step 103: Generate at least one candidate query path based on the query statistical data. Each candidate query path includes a query index and a usage order of the query index.

[0040] After the query analysis and statistics module outputs the query statistics, the query statistics can be further input into the cost model optimization module to recommend the optimal indexing scheme. In one possible implementation, the cost model optimization module first generates at least one candidate query path based on the query statistics, and then performs a query cost evaluation on each candidate query path to filter out the optimal indexing scheme based on the query cost. Regarding the method of generating candidate query paths, taking each query statement in the query log as an example, based on the query statistics, the final query field corresponding to the query statement is known, and based on the final query field, the data distribution in the query statistics, and the index usage, at least one candidate query path that is different from the original query path is generated. The candidate query path includes the query index used when executing the query and the order in which the query index is used.

[0041] For example, when querying table A for user information with ID X, a candidate query path can be: first query table A using index A, then query table A for user information with ID X using index B. The query indexes in this candidate query path include index A and index B, and the order of query index usage is: index A → index B.

[0042] Step 104: perform query cost evaluation on at least one candidate query path and determine a recommended indexing solution.

[0043] After generating at least one candidate query path, the cost model optimization module also calculates and evaluates the impact of different candidate query paths on query performance, so as to select the one with the lowest cost from multiple candidate query paths as the recommended indexing solution.

[0044] Optionally, the recommended indexing scheme may be the candidate query path with the lowest cost (optimal candidate query path), or both the suboptimal candidate query path and the optimal candidate query path may be used as recommended indexing schemes, and the user may select the recommended indexing scheme according to the user's needs.

[0045] In summary, the embodiment of the present application provides an intelligent index optimization method: by automatically parsing the query log of the database to obtain detailed query information, and analyzing and counting the detailed query information to obtain query statistical data, and then automatically generating at least one candidate query path based on the query statistical data, and determining the recommended index scheme by performing query cost evaluation on the candidate query path. The data index optimization tool can automatically parse SQL query statements, analyze query conditions, joined table fields and sorting fields, and automatically recommend appropriate index schemes in combination with the database table structure. This reduces the manual intervention of database administrators in the index optimization process and significantly improves the efficiency of index optimization.

[0046] When screening the optimal indexing solution, a specific quantification method is also provided to quantify the query performance of each candidate query path and intelligently select the optimal indexing solution.

[0047] Please refer to Figure 2 , which shows a flow chart of another index optimization method according to an exemplary embodiment of the present application. This method is described by taking the application of electronic equipment as an example. Figure 2 As shown, the method includes:

[0048] Step 201 : Parse the query log of the database to obtain query details. The query log includes at least one query statement. The query details include the query condition, sorting fields, table connection relationship and grouping information indicated by the query statement.

[0049] Step 202 : Analyze and count the query details to obtain query statistics, which include field access frequency, data distribution, and index usage.

[0050] Step 203: Generate at least one candidate query path based on the query statistical data. Each candidate query path includes a query index and a usage order of the query index.

[0051] The implementation of steps 201 to 203 may refer to steps 101 to 103 , and will not be described in detail in this embodiment.

[0052] Step 204 : For each candidate query path, calculate the index scan time, I / O cost, and CPU usage.

[0053] In one possible implementation, the query cost of each candidate query path is quantified across three performance dimensions: index scan time, I / O (input / output) cost, and CPU utilization. I / O cost refers to I / O time. The corresponding cost model optimization module will test each candidate query path separately to obtain query performance information such as index scan time, I / O cost, and CPU utilization, so that the query cost can be quantified through subsequent analysis of this query performance information.

[0054] Step 205 : Determine the candidate query cost of each candidate query path based on the index scan time, I / O cost, and CPU occupancy.

[0055] When quantifying the query cost based on index scan time, I / O cost, and CPU usage, a targeted quantification formula is provided. For example, the quantification formula for the query cost can be shown as formula (1):

[0056] Q=W1*A1+ W2*A2+ W3*A3 (1)

[0057] Wherein, Q represents the query cost, A1 represents the index scan time, W1 represents the weight of the index scan time, A2 represents the I / O cost, W3 represents the weight between the I / O costs, A3 represents the CPU occupancy rate, and W3 represents the weight of the CPU occupancy rate. Based on formula (1), the scores on the three performance dimensions can be combined to quantify the candidate query cost corresponding to the candidate query path. Optionally, when quantifying the query cost, the weights on each performance dimension can be fixed or dynamically adjusted. In order to better distinguish the differences in query costs corresponding to different candidate query paths, the greater the difference in the performance dimension, the greater the weight used in calculating the query cost. Correspondingly, in an exemplary example, step 205 can also include step 205A and step 205B.

[0058] Step 205A: compare the differences in index scan time, I / O cost, and CPU usage of each candidate query path, and determine a first weight corresponding to the index scan time, a second weight corresponding to the I / O cost, and a third weight corresponding to the CPU usage.

[0059] In one possible implementation, a first difference value in index scan time, a second difference value in I / O cost, and a third difference value in CPU usage are compared for each candidate query path. The first, second, and third difference values ​​are then compared to determine a first weight corresponding to index scan time, a second weight corresponding to I / O cost, and a third weight corresponding to CPU usage when calculating the query cost. The weights are positively correlated with the difference values; that is, the greater the difference value, the greater the weight, and conversely, the smaller the difference value, the smaller the weight.

[0060] For example, if the index scanning time corresponding to candidate query path A is 1ms, the I / O cost is 2ms, and the CPU occupancy rate is 3%, and the index scanning time corresponding to candidate query path B is 3ms, the I / O cost is 3ms, and the CPU occupancy rate is 3%, then comparing candidate query path A and candidate query path B, the first difference value in index scanning time is 2ms, the second difference value in I / O cost is 1ms, and the third difference value in CPU occupancy rate is 0, then the first difference value is greater than the second difference value, which is greater than the third difference value. Accordingly, the first weight of the index scanning time can be set to 1, the second weight of the I / O cost is 0.8, and the third weight of the CPU occupancy rate is 0.2, satisfying the size relationship that the first weight is greater than the second weight, which is greater than the third weight.

[0061] Optionally, the weight of the performance dimension corresponding to the largest difference value among the first difference value, the second difference value, and the third difference value can be fixed to 1, and the sum of the weights of the performance dimensions corresponding to the other difference values ​​can be fixed to 1. For example, if the first difference value is greater than the second difference value and greater than the third difference value, the weight of the index scan time corresponding to the first difference value is fixed to 1, and the sum of the weight of the I / O cost corresponding to the second difference value and the weight of the CPU usage corresponding to the third difference value is fixed to 1.

[0062] Step 205B: Determine the candidate query cost of the candidate query path based on the first weight, index scan time, I / O cost, the second weight, CPU occupancy, and the third weight.

[0063] After dynamically determining the weights corresponding to each performance dimension, the first weight, index scan time, I / O cost, second weight, CPU occupancy, and third weight can be substituted into formula (1) to calculate the candidate query cost of each candidate query path.

[0064] Step 206: Determine a recommended indexing solution from at least one candidate query path based on the candidate query cost.

[0065] The larger the candidate query cost, the lower the query performance. Conversely, the smaller the candidate query cost, the higher the query performance. The candidate query costs corresponding to each candidate query path are compared, and the candidate query path with the lowest candidate query cost is determined as the recommended index solution.

[0066] Optionally, in addition to recommending the candidate query path with the lowest query cost as the optimal indexing solution to the user, the suboptimal and optimal indexing solutions can also be recommended as recommended indexing solutions. For example, the candidate query paths can be ranked from low to high based on query cost, and the top K candidate query paths can be provided to the user or downstream modules as recommended indexing solutions. The value of K can be 2.

[0067] Step 207: Create a target query statement based on the recommended index solution.

[0068] Step 208: perform a query based on the target query statement, and obtain query performance information during the query process of executing the target query statement.

[0069] Step 209: Generate an index recommendation report based on the recommended index solution, the target query statement, and the query performance information.

[0070] After the cost model optimization module outputs the recommended index solution, the recommended index solution may be further input into the index recommendation module, which generates an index recommendation report based on the recommended index solution.

[0071] To further verify the query performance of the recommended index scheme provided by the cost model optimization module, the index recommendation module generates an actual target query statement (SQL statement) based on the recommended index scheme, executes the target query statement to perform data query, and obtains query performance information during the actual database query process of executing the target query statement, such as the actual index scan time, actual I / O cost, and actual CPU usage; then, based on the recommended index scheme, target query statement, and query performance information, an index recommendation report is generated to describe the background, query performance impact, recommendation reasons, recommended query statement, etc. of each recommended index scheme.

[0072] In addition to recommending indexing solutions, the data index optimization tool also optimizes existing indexes in the database based on the existing index analysis results in the query statistics. In one possible implementation, based on the index usage in the query statistics, inefficient and redundant indexes are identified and deleted from the database.

[0073] Regarding the method of identifying inefficient indexes and redundant indexes, based on the usage frequency of each index in the index usage, the index with a usage frequency below the frequency threshold is determined as an inefficient index; the index with duplicate fields is searched from the existing indexes, and based on the index usage frequency, the index with a lower usage frequency is determined as a redundant index.

[0074] In this embodiment, the cost of candidate queries is quantified across three performance dimensions (index scan time, I / O cost, and CPU utilization). The weights of each performance dimension are dynamically adjusted based on their differences, enabling the calculation and comparison of candidate query costs corresponding to candidate query paths. Furthermore, based on index usage in query statistics, inefficient and redundant indexes can be identified and deleted to reduce database burden and improve query performance.

[0075] Please refer to Figure 3 , which is a system architecture diagram of a data index optimization tool provided by an exemplary embodiment of the present application. The data index optimization tool includes an SQL parsing module, a query analysis and statistics module, a cost model optimization module, an index recommendation module, an index maintenance module, and a log and report module.

[0076] The SQL Parsing Module is a foundational component of the database index optimization tool. It collects SQL queries from the database, parses them, and generates an Abstract Syntax Tree (AST). This module supports input from a variety of query sources, such as query logs and real-time SQL statements. By parsing SQL statements, the module extracts query tables, fields, filter conditions, sort fields, and join table information. This information is then passed to the Query Analysis and Statistics Module for further analysis.

[0077] Data flow: Input SQL collection (query logs from the database or real-time SQL statements) and output abstract syntax tree (AST) for use by subsequent components.

[0078] Query Analysis and Statistics Module: This module performs detailed query analysis based on the abstract syntax tree (AST) obtained from the SQL parsing module. This module primarily analyzes the query conditions, involved table structures, and existing indexes. By combining database statistics, the module generates statistical data on query execution, helping the cost model optimization module further calculate query execution costs and generate index optimization recommendations based on query conditions and index usage.

[0079] Data flow: Input abstract syntax tree (AST), output query condition analysis, table structure analysis, existing index analysis results and statistical data.

[0080] The cost model optimization module is the core of index optimization. It generates and analyzes query execution plans based on statistical data. This module evaluates the impact of different indexing schemes on query performance based on the query table structure, index usage, and query conditions. By calculating the costs of different indexing schemes, the optimization module ultimately recommends the optimal indexing scheme to improve query efficiency.

[0081] Data flow: Input statistical data and query execution plan, and output candidate indexes, cost calculation results, and optimal index recommendations.

[0082] The Index Recommendation Module is responsible for generating the optimal indexing solution based on the cost model optimization module's recommendations. Based on the calculated optimal index recommendations, this module provides an index recommendation report and corresponding index creation SQL statements. These recommendation reports help DBAs understand why certain indexes are recommended and enable them to be directly generated using SQL statements. This process can be automated or manually confirmed by the DBA.

[0083] Data flow: Input optimal index recommendation, output index recommendation report and index creation SQL.

[0084] Index Maintenance Module: Responsible for regularly cleaning and optimizing database indexes. The module analyzes existing index usage, assesses which indexes have been unused or have poor performance, and generates strategies for cleaning inefficient indexes. Cleaning inefficient indexes and regenerating or rebuilding them reduces database load and improves query efficiency. This module can be triggered automatically or manually executed by the DBA.

[0085] Data flow: Input the existing index analysis results and output inefficient index cleanup operations and index optimization SQL (DROP / REBUILD).

[0086] The Log and Report Module is responsible for recording and managing log information during the index optimization process. This module records index creation and optimization SQL and generates optimization reports. The optimization log contains the detailed execution steps, and any failures or warnings are recorded, ensuring that DBAs can trace the details of each index optimization. The generated index optimization report provides a detailed explanation of all optimization decisions, helping DBAs understand the background and effectiveness of the optimization recommendations.

[0087] Data flow: input index creation SQL, index optimization SQL, output optimization log, index optimization report.

[0088] Please refer to Figure 4 , which is a structural diagram of an index optimization device provided in an embodiment of the present application. For example, Figure 4 As shown, the apparatus 400 includes.

[0089] Parsing module 401, configured to parse a query log of a database to obtain detailed query information, wherein the query log includes at least one query statement, and the detailed query information includes query conditions, sorting fields, table connection relationships, and grouping information indicated by the query statement;

[0090] An analysis module 402 is configured to analyze and collect statistics on the query details to obtain query statistics, including field access frequency, data distribution, and index usage;

[0091] A first generating module 403 is configured to generate at least one candidate query path based on the query statistical data, each candidate query path including a query index and a usage order of the query index;

[0092] The determination module 404 is configured to perform query cost evaluation on the at least one candidate query path and determine a recommended indexing solution.

[0093] Optionally, the determining module 404 is further configured to:

[0094] For each candidate query path, calculate the index scan time, I / O cost, and CPU usage;

[0095] Determining a candidate query cost for each candidate query path based on the index scan time, the I / O cost, and the CPU occupancy rate;

[0096] The recommended indexing scheme is determined from the at least one candidate query path based on the candidate query cost.

[0097] Optionally, the determining module 404 is further configured to:

[0098] Comparing the differences among the candidate query paths in the index scanning time, the I / O cost, and the CPU occupancy rate, and determining a first weight corresponding to the index scanning time, a second weight corresponding to the I / O cost, and a third weight corresponding to the CPU occupancy rate;

[0099] The candidate query cost of the candidate query path is determined based on the first weight, the index scan time, the I / O cost, the second weight, the CPU occupancy rate, and the third weight.

[0100] Optionally, the determining module 404 is further configured to:

[0101] The recommended indexing scheme is determined based on the candidate query path with the minimum cost of the candidate query.

[0102] Optionally, the device further comprises:

[0103] A creation module, configured to create a target query statement based on the recommended indexing scheme;

[0104] An acquisition module, configured to perform a query based on the target query statement and obtain query performance information during the query execution of the target query statement;

[0105] The second generating module is used to generate an index suggestion report based on the recommended index scheme, the target query statement and the query performance information.

[0106] Optionally, the device further comprises:

[0107] an identification module, configured to identify inefficient indexes and redundant indexes based on the index usage in the query statistics;

[0108] A deletion module is used to delete the inefficient index and the redundant index from the database.

[0109] In summary, the embodiment of the present application provides an intelligent index optimization method: by automatically parsing the query log of the database to obtain detailed query information, and analyzing and counting the detailed query information to obtain query statistical data, and then automatically generating at least one candidate query path based on the query statistical data, and determining the recommended index scheme by performing query cost evaluation on the candidate query path. The data index optimization tool can automatically parse SQL query statements, analyze query conditions, joined table fields and sorting fields, and automatically recommend appropriate index schemes in combination with the database table structure. This reduces the manual intervention of database administrators in the index optimization process and significantly improves the efficiency of index optimization.

[0110] The exemplary embodiments of the present application further provide an electronic device, comprising: at least one processor; and a memory communicatively connected to the at least one processor. The memory stores a computer program executable by the at least one processor, wherein the computer program, when executed by the at least one processor, causes the electronic device to perform the index optimization method according to the embodiments of the present application.

[0111] An exemplary embodiment of the present application further provides a non-transitory computer-readable storage medium storing a computer program, wherein the computer program, when executed by a processor of a computer, is used to cause the computer to perform the index optimization method according to an embodiment of the present application.

[0112] An exemplary embodiment of the present application further provides a computer program product, including a computer program, wherein when the computer program is executed by a processor of a computer, it is used to enable the computer to perform the index optimization method according to the embodiment of the present application.

[0113] refer to Figure 5 , a block diagram of an electronic device 500 that can serve as a server or client of the present application will now be described, which is an example of a hardware device that can be applied to various aspects of the present application. The electronic device is intended to represent various forms of digital electronic computer equipment, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present application described and / or required herein.

[0114] like Figure 5 As shown, the electronic device 500 includes a computing unit 501, which can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 502 or a computer program loaded from a storage unit 508 into a random access memory (RAM) 503. In the RAM 503, various programs and data required for the operation of the electronic device 500 can also be stored. The computing unit 501, the ROM 502, and the RAM 503 are connected to each other via a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0115] Multiple components within electronic device 500 are connected to I / O interface 505, including an input unit 506, an output unit 507, a storage unit 508, and a communication unit 509. Input unit 506 can be any type of device capable of inputting information into electronic device 500. Input unit 506 can receive input numeric or character information and generate key signal inputs related to user settings and / or function control of the electronic device. Output unit 507 can be any type of device capable of presenting information and may include, but is not limited to, a display, a speaker, a video / audio output terminal, a vibrator, and / or a printer. Storage unit 508 may include, but is not limited to, a magnetic disk or an optical disk. Communication unit 509 allows electronic device 500 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks, and may include, but is not limited to, a modem, a network card, an infrared communication device, a wireless communication transceiver and / or a chipset, such as a Bluetooth device, a WiFi device, a WiMax device, a cellular communication device, and / or the like.

[0116] The computing unit 501 may be a variety of general and / or specialized processing components with processing and computing capabilities. Some examples of the computing unit 501 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various computing units that run machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The computing unit 501 performs the various methods and processes described above. For example, in some embodiments, Figure 1 、 Figure 2 The illustrated method may be implemented as a computer software program tangibly embodied in a machine-readable medium, such as the storage unit 508. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 500 via the ROM 502 and / or the communication unit 509. In some embodiments, the computing unit 501 may be configured to execute the computer program in any other suitable manner (e.g., by means of firmware). Figure 1 、 Figure 2 The method shown.

[0117] The program code for implementing the methods of the present application can be written in any combination of one or more programming languages. Such program code can be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the program code is executed by the processor or controller, the functions / operations specified in the flow charts and / or block diagrams are implemented. The program code can be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0118] In the context of the present application, a machine-readable medium can be a tangible medium that can contain or store a program for use by an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared or semiconductor system, device or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium can include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0119] As used herein, the terms "machine-readable medium" and "computer-readable medium" refer to any computer program product, apparatus, and / or device (e.g., a magnetic disk, an optical disk, a memory, a programmable logic device (PLD)) for providing machine instructions and / or data to a programmable processor, including a machine-readable medium that receives machine instructions as a machine-readable signal. The term "machine-readable signal" refers to any signal for providing machine instructions and / or data to a programmable processor.

[0120] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the computer. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0121] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer having a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network (LAN), a wide area network (WAN), and the Internet.

[0122] Computer systems may include clients and servers. A client and server are generally remote from each other and typically interact through a communication network. The client and server relationship arises through computer programs running on the respective computers and having a client-server relationship to each other.

Claims

1. An index optimization method, characterized in that: The method comprises: Parsing a query log of a database to obtain query details, wherein the query log includes at least one query statement, and the query details include query conditions, sorting fields, table connection relationships, and grouping information indicated by the query statement; Analyze and collect statistics on the query details to obtain query statistics, including field access frequency, data distribution, and index usage; generating at least one candidate query path based on the query statistical data, each candidate query path including a query index and a usage order of the query index; A query cost evaluation is performed on the at least one candidate query path to determine a recommended indexing scheme.

2. The method according to claim 1, characterized in that The performing query cost evaluation on the at least one candidate query path and determining a recommended indexing scheme includes: For each candidate query path, calculate the index scan time, I / O cost, and CPU usage; Determining a candidate query cost for each candidate query path based on the index scan time, the I / O cost, and the CPU occupancy rate; The recommended indexing scheme is determined from the at least one candidate query path based on the candidate query cost.

3. The method according to claim 2, characterized in that The determining, based on the index scan time, the I / O cost, and the CPU occupancy, of a candidate query cost for each candidate query path includes: Comparing the differences among the candidate query paths in the index scanning time, the I / O cost, and the CPU occupancy rate, and determining a first weight corresponding to the index scanning time, a second weight corresponding to the I / O cost, and a third weight corresponding to the CPU occupancy rate; The candidate query cost of the candidate query path is determined based on the first weight, the index scan time, the I / O cost, the second weight, the CPU occupancy rate, and the third weight.

4. The method according to claim 3, characterized in that The determining the recommended indexing scheme from the at least one candidate query path based on the candidate query cost includes: The recommended indexing scheme is determined based on the candidate query path with the minimum cost of the candidate query.

5. The method according to any one of claims 1 to 4, characterized in that: The method further comprises: Creating a target query statement based on the recommended index solution; Performing a query based on the target query statement, and obtaining query performance information during the query execution of the target query statement; An index suggestion report is generated based on the recommended index scheme, the target query statement, and the query performance information.

6. The method according to any one of claims 1 to 4, characterized in that: The method further comprises: Identifying inefficient indexes and redundant indexes based on the index usage in the query statistics; The inefficient index and the redundant index are deleted from the database.

7. An index optimization device, characterized in that: The device comprises: A parsing module, configured to parse a query log of a database to obtain detailed query information, wherein the query log includes at least one query statement, and the detailed query information includes query conditions, sorting fields, table connection relationships, and grouping information indicated by the query statement; An analysis module is used to analyze and collect statistics on the query details to obtain query statistics, wherein the query statistics include field access frequency, data distribution and index usage; A first generating module is configured to generate at least one candidate query path based on the query statistical data, each candidate query path including a query index and a usage order of the query index; The determination module is configured to perform query cost evaluation on the at least one candidate query path and determine a recommended indexing scheme.

8. The device according to claim 7, characterized in that The determining module is further configured to: For each candidate query path, calculate the index scan time, I / O cost, and CPU usage; Determining a candidate query cost for each candidate query path based on the index scan time, the I / O cost, and the CPU occupancy rate; The recommended indexing scheme is determined from the at least one candidate query path based on the candidate query cost.

9. An electronic device comprising: processor; as well as Memory for storing programs, The program includes instructions, which, when executed by the processor, enable the processor to perform the index optimization method according to any one of claims 1 to 6.

10. A non-transitory computer-readable storage medium storing computer instructions, wherein: The computer instructions are used to enable the computer to execute the index optimization method according to any one of claims 1 to 6.

Images