Database index creation method and device, electronic equipment and storage medium
By building a decision tree and using Apache Calcite to generate database indexes, the problem in existing technologies where database indexes cannot adapt to changes in data volume and query patterns in a timely manner is solved, thereby improving database query efficiency.
Patent Information
- Application Number
- CN202510840928.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-06-23
- Publication Date
- 2025-09-30
AI Technical Summary
In the prior art, the creation of database indexes relies on manual operations and cannot adapt to changes in the amount of data and query patterns in the database in a timely manner, resulting in low query efficiency.
A decision tree is constructed by obtaining query speed category labels and query feature groups. A database index is created based on the number of paths. Apache Calcite is used to generate the index, and performance indicators are monitored in real time to adjust and delete the index.
It achieves timely adaptability of database indexes, improves database query efficiency, and adapts to changes in data volume and query patterns.
Smart Images
Figure CN120723769A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of databases, and in particular to a method, device, electronic device and storage medium for creating a database index. Background Art
[0002] A database index is a data structure that helps the database system quickly locate and access data in a table.
[0003] In the prior art, the creation of database indexes mainly relies on manual operations, which has low generation efficiency and cannot adapt to changes in the amount of data, query patterns, etc. in the database in a timely manner, thereby reducing the query efficiency of the database. Summary of the Invention
[0004] In view of this, the purpose of this application is to provide a method, device, electronic device and storage medium for creating a database index, which can create a database index according to the query speed corresponding to the database query statement, and can adapt to changes in the amount of data, query mode, etc. in the database in a timely manner, thereby improving the query efficiency of the database.
[0005] In a first aspect, an embodiment of the present application provides a method for creating a database index, the method comprising:
[0006] Obtaining a query speed category label corresponding to each execution of a target database query statement within the current time period; and extracting query feature groups from the target database query statement; each query feature group includes a data table and a corresponding query field and operator;
[0007] Constructing a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator;
[0008] Obtaining a target path in the decision tree whose query speed category label is a target category; determining the number of paths corresponding to the same query field according to the target path;
[0009] A database index is created according to the query field whose number of paths is greater than a preset number.
[0010] In one possible implementation, the target database query statement is determined through the following steps:
[0011] Obtaining from the database query log the execution duration corresponding to each execution of the initial database query statement within the current time period;
[0012] For each initial database query statement, count the number of execution times of the initial database query statement that is longer than a preset execution time;
[0013] The initial database query statement that has been executed more than a preset number of times is determined as a target database query statement.
[0014] In a possible implementation, extracting the query feature group from the target database query statement includes:
[0015] Obtain each data table, query fields in each data table, and operators for each query field in each data table in the target database query statement;
[0016] For each query field in each data table, the data table, the query field in the data table, and the operator of the query field in the data table are combined into a query feature group.
[0017] In a possible implementation, creating a database index based on the query field having a number of paths greater than a preset number includes:
[0018] Combining the query fields to obtain query field groups; each query field group includes at least two query fields;
[0019] Create the same query field as a database index; create all query fields in the same query field group as a database index.
[0020] In one possible implementation, the method further includes:
[0021] If the index creation user turns on dynamic indexing and does not turn on low-load execution, the database index is immediately executed in the database;
[0022] If the index creation user turns on dynamic indexing and low-load execution, the database index is executed when the number of query services to be executed in the database is less than a preset number;
[0023] If the index creation user does not enable dynamic indexing, the database index is recommended to the index creation user; a target database index selected by the index creation user from all database indexes is obtained; and the target database index is executed in the database.
[0024] In a possible implementation, recommending the database index to the index creation user includes:
[0025] Get the weight value corresponding to each query field;
[0026] If the database index is created based on a query field, determining the weight value corresponding to the query field in the database index as the weight value corresponding to the database index;
[0027] If the database index is created based on multiple query fields, an average value of the weight values corresponding to all query fields in the database index is determined as the weight value corresponding to the database index;
[0028] The database index is recommended to the index creation user according to the weight value corresponding to the database index.
[0029] In a possible implementation, after executing the target database index, the method further includes:
[0030] Real-time monitoring of query performance indicators of the target database index;
[0031] If the query performance indicator does not meet the preset query performance indicator requirement, the target database index is deleted.
[0032] In a second aspect, an embodiment of the present application further provides a device for creating a database index, the device comprising:
[0033] An acquisition module is used to obtain a query speed category label corresponding to each execution of a target database query statement within a current time period; and extract query feature groups from the target database query statement; each query feature group includes a data table and a corresponding query field and operator;
[0034] A construction module constructs a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator;
[0035] The acquisition module is further configured to acquire a target path whose query speed category label is a target category in the decision tree;
[0036] A determination module, configured to determine the number of paths corresponding to the same query field according to the target path;
[0037] The creation module is used to create a database index according to the query field whose number of paths is greater than a preset number.
[0038] In a possible implementation, the acquisition module is further configured to:
[0039] Obtaining from the database query log the execution duration corresponding to each execution of the initial database query statement within the current time period;
[0040] For each initial database query statement, count the number of execution times of the initial database query statement that is longer than a preset execution time;
[0041] The initial database query statement that has been executed more than a preset number of times is determined as a target database query statement.
[0042] In one possible implementation, the acquisition module is specifically used to obtain each data table, the query field in each data table, and the operator of each query field in each data table in the target database query statement; for each query field in each data table, the data table, the query field in the data table, and the operator of the query field in the data table are combined into a query feature group.
[0043] In one possible implementation, a creation module is specifically configured to combine the query fields to obtain query field groups; each query field group includes at least two query fields; the same query field is created as a database index; and all query fields in the same query field group are created as a database index.
[0044] In a possible implementation, the apparatus further includes: an execution module;
[0045] The execution module is specifically used to immediately execute the database index in the database if the index creation user turns on dynamic indexing and does not turn on low-load execution; if the index creation user turns on dynamic indexing and turns on low-load execution, execute the database index when the number of query businesses to be executed in the database is less than a preset number; if the index creation user does not turn on dynamic indexing, recommend the database index to the index creation user; obtain the target database index selected by the index creation user from all database indexes; and execute the target database index in the database.
[0046] In one possible implementation, the execution module is specifically used to obtain the weight value corresponding to each query field; if the database index is created based on one query field, the weight value corresponding to the query field in the database index is determined as the weight value corresponding to the database index; if the database index is created based on multiple query fields, the average of the weight values corresponding to all query fields in the database index is determined as the weight value corresponding to the database index; based on the weight value corresponding to the database index, the database index is recommended to the index creation user.
[0047] In a possible implementation, the device further includes: a monitoring module, a deletion module;
[0048] A monitoring module, configured to monitor the query performance index of the target database index in real time after executing the target database index;
[0049] The deletion module is configured to delete the target database index if the query performance indicator does not meet the preset query performance indicator requirement.
[0050] In a third aspect, an embodiment of the present application further provides an electronic device comprising: a processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor. When the electronic device is running, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the method for creating a database index as described in any one of the first aspects.
[0051] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method for creating a database index as described in any one of the first aspects are executed.
[0052] The embodiment of the present application provides a method, device, electronic device and storage medium for creating a database index. The method includes: obtaining a query speed category label corresponding to each execution of a target database query statement within a current time period; and extracting a query feature group in the target database query statement; each query feature group includes a data table and a corresponding query field and operator; constructing a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature in the data table, query field and operator; obtaining a target path in the decision tree whose query speed category label is a target category; determining the number of paths corresponding to the same query field based on the target path; and creating a database index based on a query field whose number of paths is greater than a preset number. The present application can create a database index based on the query speed corresponding to the database query statement, can adapt to changes in the amount of data, query mode, etc. in the database in a timely manner, and improves the query efficiency of the database. BRIEF DESCRIPTION OF THE DRAWINGS
[0053] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other relevant drawings can be obtained based on these drawings without creative work.
[0054] Figure 1 A flowchart of a method for creating a database index provided in an embodiment of the present application is shown;
[0055] Figure 2 A flowchart showing another method for creating a database index provided in an embodiment of the present application is shown;
[0056] Figure 3 A schematic diagram of the structure of a database index creation device provided in an embodiment of the present application is shown;
[0057] Figure 4 A schematic structural diagram of an electronic device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION
[0058] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the drawings in the present application only serve the purpose of illustration and description and are not used to limit the scope of protection of the present application. In addition, it should be understood that the schematic drawings are not drawn to scale. The flowcharts used in this application illustrate the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowcharts can be implemented out of sequence, and steps without logical context can be reversed or implemented simultaneously. In addition, those skilled in the art, under the guidance of the contents of this application, can add one or more other operations to the flowchart, or remove one or more operations from the flowchart.
[0059] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all of the embodiments. The components of the embodiments of the present application generally described and shown in the drawings here can be arranged and designed in various configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the claimed application, but merely represents selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work are within the scope of protection of the present application.
[0060] To enable those skilled in the art to utilize the present disclosure, the following embodiments are provided in conjunction with a specific application scenario, the "database field." Those skilled in the art will appreciate that the general principles defined herein can be applied to other embodiments and application scenarios without departing from the spirit and scope of this disclosure. While this disclosure primarily focuses on the "database field," it should be understood that this is merely an exemplary embodiment.
[0061] It should be noted that the term "comprising" will be used in the embodiments of the present application to indicate the existence of the features declared thereafter, but does not exclude the addition of other features.
[0062] The following is a detailed description of a method for creating a database index provided in an embodiment of the present application.
[0063] Reference Figure 1FIG. 1 is a flow chart of a method for creating a database index according to an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:
[0064] S101: Obtain a query speed category label corresponding to each execution of a target database query statement within a current time period; and extract a query feature group from the target database query statement.
[0065] In an embodiment of the present application, the execution time corresponding to each execution of the initial database query statement in the current time period is obtained from the database query log; for each initial database query statement, the number of executions in which the execution time corresponding to the initial database query statement is greater than a preset time (e.g., 1s) is counted; the initial database query statement with an execution time greater than a preset number (e.g., 10 times) is determined as the target database query statement. The query speed category label includes a fast query category label and a slow query category label; for each execution of the initial database statement, if the execution time corresponding to the execution of the initial database query statement is greater than the preset execution time, then the query speed category label corresponding to the execution of the initial database query statement is a fast query category label; if the execution time corresponding to the execution of the initial database query statement is less than or equal to the preset execution time, then the query speed category label corresponding to the execution of the initial database query statement is a slow query category label.
[0066] Example 1: The execution duration of eleven executions of the initial database query statement A is obtained as [0.9s, 1s, 1.1s, 1.1s, 1s, 1.1s, 1.3s, 1.2s, 1.1s, 1.1s, 1.2s, 1.1s]; assuming that the preset duration is 1s and the preset number of times is 10 times; there are 11 executions of the initial database query statement A that exceed 1s, so the initial database query statement A is the target database query statement.
[0067] Example 2: Assume that the preset duration is 1 second; the execution duration of a target database query statement A is 0.9 seconds; then the query speed category label corresponding to the execution of the target database query statement A is a fast query category label.
[0068] Furthermore, each query feature group includes a data table and a corresponding query field and operator; extracting the query feature group in the target database query statement includes: obtaining each data table, the query field in each data table, and the operator of each query field in each data table in the target database query statement; for each query field in each data table, the data table, the query field in the data table, and the operator of the query field in the data table are combined into a query feature group.
[0069] In the implementation manner of the present application, the data table in the target database query statement refers to the data table involved in the target database query statement; the query field in the data table refers to the field involved in the data table in the target database query statement; the operator of the query field in the data table refers to the operator used when querying the query field in the data table in the target database query statement (such as "=", ">", "BETWEEN", etc.).
[0070] For example, if the target database query statement A is "SELECT name FROM students WHERE age = 18," the data table is students, and the query fields in the data table include name and age. Since name has no operator, the operator for name is set to null, and the operator for age is "=". Therefore, two query feature groups are obtained: the first query feature group is [students, name, null], and the second query feature group is [students, age, =].
[0071] S102: Construct a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator.
[0072] In the embodiment of the present application, for each target database query statement, each query feature group and each query speed category label corresponding to the target database query statement are combined into a sample data set; the C4.5 algorithm is used to construct a decision tree based on the sample data. Table 1 shows the sample data table provided in the embodiment of the present application.
[0073] Table 1
[0074] Data Sheet Query Field Operator Query speed category label Orders Order_id ＝ Slow query category label Orders customers_id ＝ Quickly query category tags customers Order_date BETWEEN Quickly query category tags Orders Order_id ＝ Quickly query category tags
[0075] Here, the C4.5 algorithm is a decision tree algorithm for classification. The following are the specific steps for constructing a decision tree based on the query speed category label and the query feature group: Based on the sample data, calculate the information gain rate of each feature (i.e., data table, query field, and operator) with respect to the query speed category label; the information gain rate measures the contribution of the feature to the query speed category label; the feature with the highest information gain rate is used as the root node of the decision tree; based on the different values of the feature with the highest information gain rate, the sample data set is divided into multiple subsets; for each subset, calculate the information gain rate of each feature in the subset, select the feature with the largest information gain rate as the node of the subtree, and continue to divide the sample data set until the following stopping conditions are met: (1) All samples in the subset belong to the same category, in which case the node is marked as a leaf node, and the category is the category of the samples in the subset; (2) There are no remaining features that can be used to divide the data set, in which case the node is marked as a leaf node, and the category is the category with the largest number of samples in the subset; (3) The number of samples in the subset is less than a pre-set threshold, and the node is also marked as a leaf node, and the category is the category with the largest number of samples in the subset.
[0076] Furthermore, the constructed decision tree model is evaluated using methods such as cross-validation. The collected data is divided into a training set and a test set. A decision tree is constructed on the training set, and evaluation metrics such as accuracy, recall, and F1-score are calculated on the test set. If the decision tree model performs poorly on the test set (e.g., accuracy is too low), adjustments to the decision tree parameters, such as adjusting the tree depth or performing pruning operations, are necessary to optimize the decision tree model.
[0077] S103: Obtain a target path in the decision tree whose query speed category label is a target category; and determine the number of paths corresponding to the same query field according to the target path.
[0078] In the embodiment of the present application, starting from the root node of the decision tree, the path of the decision tree is traversed. The target category is a quick query category label.
[0079] S104: Create a database index based on the query field whose number of paths is greater than a preset number.
[0080] In an embodiment of the present application, query fields are combined to obtain query field groups; each query field group includes at least two query fields; the same query field is created as a database index; all query fields in the same query field group are created as a database index.
[0081] Here, Apache Calcite is used to create an index and generate a database index. Apache Calcite also performs some pre-checks. If the database index already exists or affects existing queries or system performance, it will not be recommended to the index creation user.
[0082] Furthermore, the method also includes: if the index creation user turns on dynamic indexing and does not turn on low-load execution, the database index is immediately executed in the database; if the index creation user turns on dynamic indexing and turns on low-load execution, the database index is executed when the number of query services to be executed in the database is less than a preset number; if the index creation user does not turn on dynamic indexing, the database index is recommended to the index creation user; obtain the target database index selected by the index creation user from all database indexes; and execute the target database index in the database.
[0083] Specifically, recommending a database index to an index creation user includes: obtaining a weight value corresponding to each query field; if the database index is created based on one query field, determining the weight value corresponding to the query field in the database index as the weight value corresponding to the database index; if the database index is created based on multiple query fields, determining the average of the weight values corresponding to all query fields in the database index as the weight value corresponding to the database index; and recommending the database index to the index creation user based on the weight value corresponding to the database index.
[0084] In the embodiments of the present application, for each query field, the information entropy of the query field in the sample data can be used as the weight value of the query field, or the weight value of each query field can be preset. When recommending to the index creation user, the larger the weight value corresponding to the database index, the higher the ranking of the database index.
[0085] In addition, a preset number of database indexes are determined as target database indexes; a first query performance indicator (such as query duration) corresponding to the target database query statement before executing the target database index and a second query performance indicator corresponding to the target database query statement after executing the target database index are obtained; and whether the database index is valid is determined based on the first query performance and the second query performance.
[0086] Assuming that the query performance indicator is query duration, if the value corresponding to the first query performance indicator is greater than the value corresponding to the second query performance indicator, the database index is valid.
[0087] Furthermore, after executing the target database index, the method further includes: monitoring the query performance index of the target database index in real time; if the query performance index does not meet the preset query performance index requirement, deleting the target database index.
[0088] This method can be implemented in the MyBatis configuration file of the MyBatisDynamicIndexGenerator plug-in through <plugins>Tags are used to register this method, which enables seamless integration with MyBatis and allows configuration parameters to be passed through properties, such as whether to enable dynamic indexing and other functions.
[0089] The embodiment of the present application provides a method for creating a database index, the method comprising: obtaining a query speed category label corresponding to each execution of a target database query statement within a current time period; and extracting a query feature group in the target database query statement; each query feature group includes a data table and a corresponding query field and operator; constructing a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature in the data table, query field, and operator; obtaining a target path in the decision tree whose query speed category label is a target category; determining the number of paths corresponding to the same query field based on the target path; and creating a database index based on a query field whose number of paths is greater than a preset number. The present application can create a database index based on the query speed corresponding to the database query statement, can timely adapt to changes in the amount of data, query mode, etc. in the database, and improves the query efficiency of the database.
[0090] Reference Figure 2 FIG. 1 is a flow chart of another method for creating a database index provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:
[0091] S201: If the index creation user turns on dynamic indexing and does not turn on low-load execution, the database index is immediately executed in the database.
[0092] S202: If the index creation user turns on dynamic indexing and low-load execution, then when the number of query services to be executed in the database is less than a preset number, execute database indexing.
[0093] S203: If the index creation user does not enable dynamic indexing, recommending a database index to the index creation user; obtaining a target database index selected by the index creation user from all database indexes; and executing the target database index in the database.
[0094] An embodiment of the present application provides another method for creating a database index, which can adjust the time for executing the target database index according to user settings, thereby improving user experience.
[0095] Based on the same inventive concept, the embodiment of the present application also provides a database index creation device corresponding to the database index creation method. Since the principle of solving the problem by the device in the embodiment of the present application is similar to the above-mentioned database index creation method in the embodiment of the present application, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be repeated.
[0096] Reference Figure 3 FIG. 1 is a schematic diagram of a database index creation device provided in an embodiment of the present application, wherein the database index creation device includes:
[0097] The acquisition module 301 is used to obtain the query speed category label corresponding to each execution of the target database query statement in the current time period; and extract the query feature group in the target database query statement; each query feature group includes a data table and a corresponding query field and operator;
[0098] A construction module 302 constructs a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator;
[0099] The acquisition module 301 is further configured to acquire a target path whose query speed category label is a target category in the decision tree;
[0100] A determination module 303 is configured to determine the number of paths corresponding to the same query field according to the target path;
[0101] The creation module 304 is configured to create a database index according to the query field whose number of paths is greater than a preset number.
[0102] In a possible implementation, the acquisition module 301 is further configured to:
[0103] Obtaining from the database query log the execution duration corresponding to each execution of the initial database query statement within the current time period;
[0104] For each initial database query statement, count the number of execution times of the initial database query statement that is longer than a preset execution time;
[0105] The initial database query statement that has been executed more than a preset number of times is determined as a target database query statement.
[0106] In one possible implementation, the acquisition module 301 is specifically used to obtain each data table, the query field in each data table, and the operator of each query field in each data table in the target database query statement; for each query field in each data table, the data table, the query field in the data table, and the operator of the query field in the data table are combined into a query feature group.
[0107] In one possible implementation, the creation module 304 is specifically configured to combine the query fields to obtain query field groups; each query field group includes at least two query fields; the same query field is created as a database index; and all query fields in the same query field group are created as a database index.
[0108] In a possible implementation, the apparatus further includes: an execution module 305;
[0109] The execution module 305 is specifically used to immediately execute the database index in the database if the index creation user turns on dynamic indexing and does not turn on low-load execution; if the index creation user turns on dynamic indexing and turns on low-load execution, then when the number of query businesses to be executed in the database is less than a preset number, execute the database index; if the index creation user does not turn on dynamic indexing, recommend the database index to the index creation user; obtain the target database index selected by the index creation user from all database indexes; and execute the target database index in the database.
[0110] In one possible implementation, the execution module 305 is specifically used to obtain the weight value corresponding to each query field; if the database index is created based on one query field, the weight value corresponding to the query field in the database index is determined as the weight value corresponding to the database index; if the database index is created based on multiple query fields, the average value of the weight values corresponding to all query fields in the database index is determined as the weight value corresponding to the database index; based on the weight value corresponding to the database index, the database index is recommended to the index creation user.
[0111] In a possible implementation, the apparatus further includes: a monitoring module 306, a deleting module 307;
[0112] A monitoring module 306 is configured to monitor the query performance index of the target database index in real time after executing the target database index;
[0113] The deletion module 307 is configured to delete the target database index if the query performance indicator does not meet the preset query performance indicator requirement.
[0114] An embodiment of the present application provides a device for creating a database index, which can create a database index based on the query speed corresponding to the database query statement, can promptly adapt to changes in the amount of data, query mode, etc. in the database, and improve the query efficiency of the database.
[0115] like Figure 4 As shown, an electronic device 400 provided in an embodiment of the present application includes: a processor 401, a memory 402 and a bus, wherein the memory 402 stores machine-readable instructions executable by the processor 401. When the electronic device is running, the processor 401 communicates with the memory 402 through the bus, and the processor 401 executes the machine-readable instructions to perform the steps of the above-mentioned database index creation method.
[0116] Specifically, the memory 402 and processor 401 can be general-purpose memories and processors, which are not specifically limited here. When the processor 401 runs the computer program stored in the memory 402, the method for creating the database index can be executed.
[0117] Corresponding to the above-mentioned method for creating a database index, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the above-mentioned method for creating a database index are executed.
[0118] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the system and device described above can refer to the corresponding process in the method embodiment, and will not be repeated in this application. In the several embodiments provided in this application, it should be understood that the disclosed system, device and method can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of the modules is only a logical function division. There may be other division methods in actual implementation. For example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, indirect coupling or communication connection of devices or modules, which can be electrical, mechanical or other forms.
[0119] The modules described as separate components may or may not be physically separate, and the components shown as modules may or may not be physical units, that is, they may be located in one place or distributed across multiple network elements. Some or all of these units may be selected to achieve the purpose of this embodiment according to actual needs.
[0120] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.
[0121] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a non-volatile computer-readable storage medium that is executable by a processor. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or the 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 enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the information processing method described in each embodiment of the present application. The aforementioned storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a ROM, a RAM, a magnetic disk, or an optical disk.
[0122] The above are only specific embodiments of the present application, but the scope of protection of this application is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in this application should be included in the scope of protection of this application. Therefore, the scope of protection of this application should be based on the scope of protection of the claims.< / plugins>
Claims
1. A method for creating a database index, characterized in that: The method comprises: Obtaining a query speed category label corresponding to each execution of a target database query statement within the current time period; and extracting query feature groups from the target database query statement; each query feature group includes a data table and a corresponding query field and operator; Constructing a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator; Obtaining a target path in the decision tree whose query speed category label is a target category; determining the number of paths corresponding to the same query field according to the target path; A database index is created according to the query field whose number of paths is greater than a preset number.
2. The method for creating a database index according to claim 1, wherein: Determine the target database query statement through the following steps: Obtaining from the database query log the execution duration corresponding to each execution of the initial database query statement within the current time period; For each initial database query statement, count the number of execution times corresponding to the initial database query statement whose execution time is greater than the preset time; The initial database query statement that has been executed more than a preset number of times is determined as a target database query statement.
3. The method for creating a database index according to claim 1 or 2, characterized in that: The extracting of the query feature group from the target database query statement includes: Obtain each data table, query fields in each data table, and operators for each query field in each data table in the target database query statement; For each query field in each data table, the data table, the query field in the data table, and the operator of the query field in the data table are combined into a query feature group.
4. The method for creating a database index according to claim 1, wherein: The step of creating a database index based on the query field having a number of paths greater than a preset number includes: Combining the query fields to obtain query field groups; each query field group includes at least two query fields; Create the same query field as a database index; create all query fields in the same query field group as a database index.
5. The method for creating a database index according to claim 4, wherein: The method further comprises: If the index creation user turns on dynamic indexing and does not turn on low-load execution, the database index is immediately executed in the database; If the index creation user turns on dynamic indexing and low-load execution, the database index is executed when the number of query services to be executed in the database is less than a preset number; If the index creation user does not enable dynamic indexing, the database index is recommended to the index creation user; a target database index selected by the index creation user from all database indexes is obtained; and the target database index is executed in the database.
6. The method for creating a database index according to claim 5, wherein: The recommending the database index to the index creation user includes: Get the weight value corresponding to each query field; If the database index is created based on a query field, determining the weight value corresponding to the query field in the database index as the weight value corresponding to the database index; If the database index is created based on multiple query fields, an average value of the weight values corresponding to all query fields in the database index is determined as the weight value corresponding to the database index; The database index is recommended to the index creation user according to the weight value corresponding to the database index.
7. The method for creating a database index according to claim 5, wherein: After executing the target database index, the method further includes: Real-time monitoring of query performance indicators of the target database index; If the query performance indicator does not meet the preset query performance indicator requirement, the target database index is deleted.
8. A device for creating a database index, characterized in that: The device comprises: An acquisition module is used to obtain a query speed category label corresponding to each execution of a target database query statement within a current time period; and extract query feature groups from the target database query statement; each query feature group includes a data table and a corresponding query field and operator; A construction module constructs a decision tree based on the query speed category label and the query feature group; each path in the decision tree includes a query speed category label and at least one feature of a data table, a query field, and an operator; The acquisition module is further configured to acquire a target path whose query speed category label is a target category in the decision tree; A determination module, configured to determine the number of paths corresponding to the same query field according to the target path; The creation module is used to create a database index according to the query field whose number of paths is greater than a preset number.
9. An electronic device, characterized in that: include: A processor, a storage medium, and a bus, wherein the storage medium stores machine-readable instructions executable by the processor. When the electronic device is running, the processor and the storage medium communicate via the bus, and the processor executes the machine-readable instructions to perform the steps of the method for creating a database index as described in any one of claims 1 to 7.
10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for creating a database index according to any one of claims 1 to 7 are executed.