Database index addition methods, devices, computer equipment, and readable storage media

Abstract

This application discloses a database index adding method, apparatus, computer device, and readable storage medium. The method includes: responding to a database index adding request, obtaining multiple database information included in the index adding request, wherein the multiple database information includes database parameter information and field information of the field to be indexed; sequentially determining whether each database information meets its corresponding preset index adding conditions; adding an index to the field to be indexed if all database information meets the preset index adding conditions; and not adding an index to the field to be indexed if at least one database information does not meet the preset index adding conditions. This achieves targeted and automatic review of all obtained database information based on preset index adding conditions, sequentially determining whether each database information meets the preset conditions, improving both the efficiency and accuracy of data review.

Description

Technical Field

[0001] This application relates to the field of database management technology, and in particular to a database index adding method, apparatus, computer device, and readable storage medium. Background Technology

[0002] In many industries, massive amounts of data are generated every moment. When adding a new index to a database, the index needs to be evaluated to determine if it is feasible. Current technology relies on manual judgment by DBAs to determine whether an index can be added. This method typically depends on the experience of the DBA (Database Administrator). Insufficient DBA experience leading to incorrect assessments can have serious negative consequences, causing production line incidents, and is also time-consuming, labor-intensive, and has high labor costs. Summary of the Invention

[0003] In view of this, this application provides a database index addition method, apparatus, computer device and readable storage medium. The main purpose is to solve the problem that the method of manual judgment by DBAs is time-consuming and labor-intensive. If the DBAs are not experienced enough, incorrect evaluations will have serious adverse effects and lead to production line accidents.

[0004] According to a first aspect of this application, a method for adding a database index is provided, the method comprising:

[0005] In response to a database index addition request, retrieve multiple database information items included in the index addition request, including database parameter information and field information of the field to be indexed.

[0006] Check each database entry sequentially to see if it meets the corresponding preset index addition conditions;

[0007] If multiple database entries meet the preset index addition criteria, add an index to the field to be indexed.

[0008] If at least one database field does not meet the preset index addition conditions, no index will be added for the field to be added.

[0009] Optionally, the database parameter information includes the table type information of the database tables, and the steps of sequentially determining whether each database information meets its corresponding preset index addition conditions specifically include:

[0010] Based on the table type information, determine whether the database table is a non-core database table;

[0011] If the database table is a non-core database table, ensure that it meets the preset index addition conditions;

[0012] If the database table is a core database table, it is determined that it does not meet the preset index addition conditions;

[0013] Generate audit information based on information from multiple databases;

[0014] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0015] Optionally, the database information includes the number of days the server has remaining space. The process of sequentially determining whether each piece of database information meets the corresponding preset index addition criteria also includes:

[0016] If the remaining days of space usage are greater than the first preset space threshold, it is determined that the preset index addition conditions are met.

[0017] If the remaining days of space usage are less than or equal to the first preset space threshold, it is determined that the preset index addition conditions are not met.

[0018] Determine if the remaining days of space usage are greater than the second preset space threshold;

[0019] If the remaining days of space usage are less than or equal to the first preset space threshold, but greater than the second preset space threshold;

[0020] Generate audit information based on information from multiple databases;

[0021] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0022] Optionally, the database information includes multiple field values ​​of the field to be indexed, wherein the multiple field values ​​include multiple identical field values ​​and multiple different field values. The step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions also includes:

[0023] Based on multiple field values, determine a first quantity and a second quantity, where the first quantity is the total number of multiple field values ​​and the second quantity is the number of multiple distinct field values;

[0024] Divide the second quantity by the first quantity to obtain the distinguishability of the field to be indexed;

[0025] If the distinguishability is greater than the first preset threshold, it is determined that the preset index addition conditions are met;

[0026] If the discrimination is less than or equal to the first preset threshold, it is determined that the preset index addition conditions are not met.

[0027] Generate audit information based on information from multiple databases;

[0028] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0029] Optionally, the database information includes at least one field type of the field to be indexed, and the step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions also includes:

[0030] Determine the third quantity corresponding to each field type based on at least one field type;

[0031] Divide the third quantity by the second quantity to obtain the percentage value of each field type;

[0032] If the percentage of at least one field type is greater than the second preset threshold, it is determined that the preset index addition conditions are met.

[0033] If the percentage value of any field type is less than or equal to the second preset threshold, it is determined that the preset index addition conditions are not met.

[0034] Generate audit information based on information from multiple databases;

[0035] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0036] Optionally, the database information includes the number of target indexes, and the step of sequentially determining whether each database information meets its corresponding preset index addition conditions also includes:

[0037] If the number of target indexes is less than the third preset threshold, it is determined that the preset index addition conditions are met;

[0038] If the number of target indexes is greater than or equal to the third preset threshold, it is determined that the preset index addition conditions are not met.

[0039] Optionally, the database information includes the first index combination of the target field corresponding to the target index, and the step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions also includes:

[0040] Determine the second index combination to be added based on the field to be indexed;

[0041] Based on the first index combination and the second index combination, determine whether the index to be added is a redundant index;

[0042] If the index to be added is not a redundant index, it is determined that it meets the preset index addition conditions;

[0043] If the index to be added is a redundant index, it is determined that it does not meet the preset index addition conditions.

[0044] According to a second aspect of this application, a database index adding apparatus is provided, the apparatus comprising:

[0045] The acquisition module is used to respond to the database index addition request and acquire multiple database information included in the index addition request. These multiple database information include database parameter information and field information of the field to be added to the index.

[0046] The judgment module is used to sequentially determine whether each database piece of information meets its corresponding preset index addition conditions;

[0047] The Add module is used to add an index to the field to be indexed when multiple database information meets the preset index addition conditions;

[0048] The addition module is also used to prevent the addition of an index to a field if at least one database information does not meet the preset index addition conditions.

[0049] Optionally, the database parameter information includes the table type information of the database table, and the judgment module is specifically used for:

[0050] Based on the table type information, determine whether the database table is a non-core database table;

[0051] If the database table is a non-core database table, ensure that it meets the preset index addition conditions;

[0052] If the database table is a core database table, it is determined that it does not meet the preset index addition conditions;

[0053] Generate audit information based on information from multiple databases;

[0054] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0055] Optionally, the database information includes the number of days the server's space is still available. The judgment module is also specifically used for:

[0056] If the remaining days of space usage are greater than the first preset space threshold, it is determined that the preset index addition conditions are met.

[0057] If the remaining days of space usage are less than or equal to the first preset space threshold, it is determined that the preset index addition conditions are not met.

[0058] Determine if the remaining days of space usage are greater than the second preset space threshold;

[0059] If the remaining days of space usage are less than or equal to the first preset space threshold, but greater than the second preset space threshold;

[0060] Generate audit information based on information from multiple databases;

[0061] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0062] Optionally, the database information includes multiple field values ​​of the field to be indexed, wherein the multiple field values ​​include multiple identical field values ​​and multiple different field values. The judgment module is further used for:

[0063] Based on multiple field values, determine a first quantity and a second quantity, where the first quantity is the total number of multiple field values ​​and the second quantity is the number of multiple distinct field values;

[0064] Divide the second quantity by the first quantity to obtain the distinguishability of the field to be indexed;

[0065] If the distinguishability is greater than the first preset threshold, it is determined that the preset index addition conditions are met;

[0066] If the discrimination is less than or equal to the first preset threshold, it is determined that the preset index addition conditions are not met.

[0067] Generate audit information based on information from multiple databases;

[0068] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0069] Optionally, the database information includes at least one field type of the field to be indexed, and the determination module is further used for:

[0070] Determine the third quantity corresponding to each field type based on at least one field type;

[0071] Divide the third quantity by the second quantity to obtain the percentage value of each field type;

[0072] If the percentage of at least one field type is greater than the second preset threshold, it is determined that the preset index addition conditions are met.

[0073] If the percentage value of any field type is less than or equal to the second preset threshold, it is determined that the preset index addition conditions are not met.

[0074] Generate audit information based on information from multiple databases;

[0075] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0076] Optionally, the database information includes the number of target indexes, and the judgment module is also used for:

[0077] If the number of target indexes is less than the third preset threshold, it is determined that the preset index addition conditions are met;

[0078] If the number of target indexes is greater than or equal to the third preset threshold, it is determined that the preset index addition conditions are not met.

[0079] Optionally, the database information includes the first index combination of the target field corresponding to the target index, and the judgment module is further used for:

[0080] Determine the second index combination to be added based on the field to be indexed;

[0081] Based on the first index combination and the second index combination, determine whether the index to be added is a redundant index;

[0082] If the index to be added is not a redundant index, it is determined that it meets the preset index addition conditions;

[0083] If the index to be added is a redundant index, it is determined that it does not meet the preset index addition conditions.

[0084] According to a third aspect of this application, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of any of the methods in the first aspect.

[0085] According to a fourth aspect of this application, a readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described in any one of the first aspects.

[0086] By employing the above technical solution, this application provides a database index adding method, apparatus, computer device, and readable storage medium. Specifically, after obtaining all database information in the index adding request, the original database parameters and variable data of the fields to be added are determined. Then, using the preset index adding conditions corresponding to each database information, multiple database information are verified sequentially to determine whether each database information meets the preset conditions. An index can only be added when all database information meets the preset conditions; if any database information does not meet its corresponding preset conditions, an index cannot be added. Through this method, by using the rules required for creating an index and combining the judgment experience of DBAs to preset index adding conditions, all obtained database information is reviewed in a targeted and automatic manner based on the preset index adding conditions, and each database information is judged sequentially to determine whether it meets the preset conditions. This greatly shortens the judgment time, improves data review efficiency, and improves the accuracy of data review. This makes the database index adding process more rigorous, avoids inaccurate review results due to insufficient experience of manual review, greatly reduces reliance on DBAs, and makes maintaining the index database more convenient and effective.

[0087] The above description is only an overview of the technical solution of this application. In order to better understand the technical means of this application and to implement it in accordance with the contents of the specification, and to make the above and other objects, features and advantages of this application more obvious and understandable, the following are specific embodiments of this application. Attached Figure Description

[0088] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the scope of this application. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings:

[0089] Figure 1 This illustration shows a schematic flowchart of a database index addition method provided in an embodiment of this application;

[0090] Figure 2 A schematic diagram of a database index adding device provided in an embodiment of this application is shown. Detailed Implementation

[0091] Exemplary embodiments of the present application will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the present application are shown in the drawings, it should be understood that the present application may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this application will be thorough and complete, and will fully convey the scope of the present application to those skilled in the art.

[0092] This application provides a method for adding database indexes, such as... Figure 1 As shown, the method includes:

[0093] 101. In response to a database index addition request, obtain multiple database information included in the index addition request, including database parameter information and field information of the field to be indexed.

[0094] This application provides a method for adding database indexes, applicable to relational databases. Relational databases use a relational model to organize data, storing it in rows and columns for user understanding. These rows and columns are called tables, and a group of tables constitutes a database. In practical applications, many industries experience constant data changes in databases. To improve query performance, indexes are typically created for newly added database table fields. However, indexes are redundant and consume disk space; therefore, determining whether a new index can be added is crucial. Currently, the method for creating indexes for fields to be added to database tables usually involves manual judgment by the DBA. However, if the number of fields to be indexed is large, the DBA's manual review workload is significant, and relying solely on the DBA for judgment is time-consuming and inefficient. Inexperienced DBAs may make incorrect judgments, leading to production line issues. Based on these reasons, this application proposes an automatic method for determining whether an index can be added. This method visualizes DBA experience as corresponding judgment conditions. Only when all judgment conditions are met can an index be added, improving the accuracy of index addition judgments and simultaneously increasing the efficiency of adding indexes to the database.

[0095] In this step, the index addition request includes multiple database information items. Specifically, this includes the database's original parameter information and the field information corresponding to the fields to be indexed. The database parameter information includes basic database information such as database identifier and database user, database table information, and index information for existing indexes. The field information for the fields to be indexed refers to the information corresponding to fields in the database table that have been added, deleted, or modified. After obtaining multiple database information items, the system automatically determines whether each database item meets its corresponding preset index addition conditions to determine whether an index can be added.

[0096] 102. Determine whether each database entry meets the corresponding preset index addition conditions.

[0097] In this step, since the database information contains a large number of original database parameters and field information for the fields to be added, a pre-defined index addition condition is created for each database information. This allows for targeted index creation based on the original parameters and update information. Then, all database information is compared with the pre-defined conditions to determine if it meets the criteria. If it does, the index is added; otherwise, it is not. This standardizes and automates the entire index detection process, saving DBAs significant detection time, reducing their workload, and improving the efficiency of index addition detection.

[0098] It should be noted that the preset index addition conditions are preset conditions set in advance using the rules to be followed when creating indexes and the judgment experience of DBAs. Specifically, each database information has a corresponding preset index addition condition, and the database information is then compared in a targeted manner using the preset index addition conditions to determine whether the database information meets the preset conditions.

[0099] Optionally, during the entire database information judgment process, the judgment between all database information and the preset conditions is carried out one after another. When the database information at any stage does not meet its corresponding preset index addition conditions, the index addition process is stopped to avoid the failure of the entire index addition due to a problem at a certain stage, thereby improving the correctness of the index addition process.

[0100] 103. If multiple database entries meet the preset index addition conditions, add an index for the field to be indexed.

[0101] 104. If at least one database information does not meet the preset index addition conditions, do not add an index for the field to be added.

[0102] In steps 103 and 104, all database information is evaluated. If all database information meets the preset index addition conditions, an index can be added to the field to be indexed. If any database information does not meet its corresponding preset index addition conditions, the index addition process is stopped, and no index is added. The determination of whether to add an index is based on the comparison results between the original database parameters of the index and the variable to be added with the preset conditions. On the one hand, by adding an index only when all database information meets the preset index addition conditions, the index addition conditions are limited, eliminating the need to add an index immediately after data changes, reducing the number of index additions and the time consumed by frequent additions, and improving the efficiency of adding indexes when processing large amounts of data. On the other hand, by setting preset index addition conditions, the number of index additions is limited, saving system resources.

[0103] Optionally, all database information is evaluated, and after confirming that all database information meets the preset index addition conditions, the underlying database interface and third-party tools are called to add the index.

[0104] The database index addition method provided in this application embodiment, after obtaining all database information in the index addition request, determines the original database parameters and variable data of the fields to be added in the database information. Then, using the preset index addition conditions corresponding to each database information, it sequentially verifies multiple database information to determine whether each database information meets the preset conditions. An index can only be added when all database information meets the preset conditions; if any database information does not meet its corresponding preset conditions, the index cannot be added. Through the above method, by using the rules required to create an index and combining the judgment experience of DBAs to preset index addition conditions, all obtained database information is reviewed in a targeted and automatic manner based on the preset index addition conditions, and each database information is judged sequentially to determine whether it meets the preset conditions. This greatly shortens the judgment time, improves data review efficiency, and makes the database index addition process more rigorous. It avoids inaccurate review results due to insufficient experience of manual review, improves the accuracy of data review, greatly reduces the dependence on DBAs, and makes the maintenance of the index database more convenient and effective.

[0105] Furthermore, as a refinement and extension of the specific implementation methods of the above embodiments, and to fully illustrate the specific implementation process of this embodiment, this application provides another method for adding database indexes, which includes:

[0106] 201. In response to a database index addition request, obtain multiple database information included in the index addition request, including database parameter information and field information of the field to be indexed.

[0107] In this step, the index addition request includes multiple database information items. Specifically, this includes the database's original parameter information and the field information corresponding to the fields to be indexed. The database parameter information includes basic database information such as database identifier and database user, database table information, and index information for existing indexes. The field information for the fields to be indexed refers to the information corresponding to fields in the database table that have been added, deleted, or modified. After obtaining multiple database information items, the system automatically determines whether each database item meets its corresponding preset index addition conditions to determine whether an index can be added.

[0108] 202. Determine whether each database information meets the corresponding preset index addition conditions in turn.

[0109] In this step, since the database information contains a large number of original database parameters and field information for the fields to be added, a pre-defined index addition condition is created for each database information. This allows for targeted index creation based on the original parameters and update information. Then, all database information is compared with the pre-defined conditions to determine if it meets the criteria. If it does, the index is added; otherwise, it is not. This standardizes and automates the entire index detection process, saving DBAs significant detection time, reducing their workload, and improving the efficiency of index addition detection.

[0110] Optionally, in step 202, which involves sequentially determining whether each database information meets its corresponding preset index addition conditions, the steps include: determining whether the database table is a non-core database table based on the table type information; if the database table is a non-core database table, determining that it meets the preset index addition conditions; if the database table is a core database table, determining that it does not meet the preset index addition conditions; generating review information based on multiple database information; and sending the review information so that the database administrator can determine whether an index can be added based on the review information.

[0111] In this embodiment, the database parameter information includes the table type information of the database table. A database table is an object in the database used to store data, which is organized and arranged in rows and columns. The table type information is used to determine whether the database table to be indexed is a core production line table. If the database table is a non-core table, it meets the preset indexing conditions, and subsequent database information can be automatically reviewed. If the database table is a core table, it means that the database table to be indexed is a core production line table. To avoid operations on core tables affecting the production line, the indexing request is intercepted, and subsequent database information is not automatically reviewed. At this time, review information is generated based on all received database information and sent to the DBA for manual review of all database information.

[0112] By using the above method, it is determined whether the database table is a core table of the production line. If it is a core table of the production line, other data in the core table will no longer be reviewed, and the review authority will be given to the DBA to avoid operations on the core table of the production line from affecting the production line. This combines automatic review with expert experience, making the entire index addition review process more rigorous and improving the accuracy of the index addition review results.

[0113] Optionally, in this embodiment, step 202, which involves sequentially determining whether each database information meets its corresponding preset index addition conditions, further includes: if the remaining days of space usage are greater than a first preset space threshold, determining that the preset index addition conditions are met; if the remaining days of space usage are less than or equal to the first preset space threshold, determining that the preset index addition conditions are not met; determining whether the remaining days of space usage are greater than a second preset space threshold; if the remaining days of space usage are less than or equal to the first preset space threshold and greater than the second preset space threshold; generating review information based on multiple database information; and sending the review information so that the database administrator can determine whether an index can be added based on the review information.

[0114] In this embodiment, during the index addition process, it is necessary to check whether the server hosting the database meets the space requirements for this index addition. Therefore, the database information also includes the remaining days of server space usage. The remaining days of server space usage are compared with a preset space threshold. If the remaining days of server space usage are greater than a first preset space threshold, it means that there is enough space on the server to add the index, and the preset index addition conditions are met. If the remaining days of server space usage are less than the first preset space threshold, it means that there may not be enough space on the server to accommodate the new index, and further manual review is required. Specifically, when the remaining days of server space usage are less than or equal to the first preset space threshold and greater than a second space threshold, review information is generated based on multiple database information and sent to the DBA for manual judgment on whether the remaining usage days meet the conditions for this index addition. Furthermore, if the remaining days of server space usage are less than the second preset space threshold, it is determined that there is not enough space on the server to add the index, and the index addition review is stopped.

[0115] Optionally, after sending the review information to the DBA, if the DBA determines, based on the database information, that the remaining space on the server can accommodate the indexes to be added later, but the system has already automatically determined that the indexes cannot be added, the DBA can force approval by using "-f" and determine that the database information has passed the review.

[0116] By using the above methods, we can determine whether the server has sufficient remaining space, thus avoiding index creation failure due to insufficient space. At the same time, since the automatic review requirements are relatively strict, we can combine them with manual judgment by DBAs to avoid the system review being too absolute. This ensures that the conditions are met without wasting disk space, improving the flexibility and practicality of index addition review.

[0117] Optionally, the first preset space threshold and the second preset space threshold can be flexibly configured by the DBA according to the index creation rules and application scenarios. Specifically, the first preset space threshold can be 45 days, and the second preset space threshold can be 15 days. This application does not impose specific limitations here.

[0118] Optionally, in this embodiment, step 202, which involves sequentially determining whether each database piece of information meets its corresponding preset index addition conditions, further includes: determining a first quantity and a second quantity based on multiple field values, wherein the first quantity is the total number of multiple field values ​​and the second quantity is the number of multiple different field values; dividing the second quantity by the first quantity to obtain the distinguishability of the field to be indexed; if the distinguishability is greater than a first preset threshold, it is determined that the preset index addition conditions are met; if the distinguishability is less than or equal to the first preset threshold, it is determined that the preset index addition conditions are not met; generating review information based on multiple database pieces of information; and sending the review information so that the database administrator can determine whether an index can be added based on the review information.

[0119] In this embodiment, the database information also includes multiple field values ​​of the field to be indexed. These multiple field values ​​include multiple identical field values ​​and multiple distinct field values. The distinguishability can be calculated based on these multiple field values. Distinguishing ability refers to the proportion of all distinct field values ​​in a field to the total number of fields. A distinguishability closer to 1 indicates fewer duplicate fields in the field to be indexed, making it more suitable for indexing. Therefore, based on all the field values ​​to be indexed, the total number of multiple field values ​​(the first number) and the number of distinct field values ​​(the second number) are determined. The distinguishability is calculated by dividing the second number by the first number. If the distinguishability is greater than a first preset threshold, it indicates that the number of non-repeating field values ​​in the field to be indexed is relatively large, thus meeting the preset indexing conditions. If the distinguishability is less than or equal to the first preset threshold, it indicates that the number of non-repeating field values ​​is relatively small, and if an index is added, the indexing effect will be poor, making it unnecessary to create an index.

[0120] Furthermore, when the discrimination index is less than or equal to the first preset threshold, the system automatically determines that the index does not meet the preset index addition conditions. At this time, the system generates review information based on multiple database information and sends the review information to the DBA so that the DBA can further manually determine whether the index can be added based on this database information, thereby improving the flexibility and practicality of adding database indexes.

[0121] Optionally, the first preset threshold can be flexibly configured by the DBA according to the application scenario. Specifically, the first preset threshold can be 50%, but this application does not make any specific limitation here.

[0122] The above method enables automatic review of the degree of repetition of field values ​​corresponding to all indexes to be added, avoiding high repetition of field values ​​and poor index performance.

[0123] Optionally, in step 202, which is to determine whether each database information meets its corresponding preset index addition conditions in turn, the steps further include: determining a third quantity corresponding to each field type based on at least one field type; if the third quantity is greater than a second preset threshold, determining that the preset index addition conditions are met; if the third quantity is less than or equal to the second preset threshold, determining that the preset index addition conditions are not met; generating review information based on multiple database information; and sending the review information so that the database administrator can determine whether an index can be added based on the review information.

[0124] In this embodiment, in practical applications, an index is similar to the table of contents of a dictionary. If we want to add a new character to the "Xinhua" field, we must re-maintain the corresponding content in the dictionary's table of contents. Similarly, database indexes need to be updated and modified as the database runs and is used, especially when fields are added, deleted, or modified. However, sometimes, even if an index is added, it may not improve query efficiency. Therefore, the key is whether the index to be added to the field can actually be effective. Thus, whether or not to add an index requires considering the impact of data additions, deletions, and modifications. Based on the above reasons, for at least one field type in the database information to which an index needs to be added, it should be noted that field types include added fields, modified fields, and deleted fields. Each field type has a baseline, which is the proportion of that field type among all fields. Only when the proportion of field values ​​corresponding to that field type is greater than a preset threshold does it meet the condition for adding an index. Therefore, obtaining the third quantity for each field type can be understood as the number of field values ​​corresponding to each field type. The proportion of each field type is determined by dividing the number of field values ​​corresponding to each field type by the total number of field values. The percentage value of each field type is compared with the second preset threshold. Only when the percentage value of all field types is greater than the second preset threshold will the preset index addition condition be met; if the percentage value of any field type is less than or equal to the second preset threshold, the preset index addition condition will not be met.

[0125] Furthermore, when the percentage is less than or equal to the second preset threshold, the system automatically determines that the condition for adding the index does not meet the preset criteria. At this time, the system generates review information based on multiple database information and sends the review information to the DBA so that the DBA can further manually determine whether the index can be added based on this database information, thereby improving the flexibility and practicality of adding database indexes.

[0126] Optionally, the second preset threshold can be flexibly set by the DBA according to the application scenario. For example, the same preset threshold can be set for different field types, such as adding, deleting, modifying and querying fields. For example, the second preset threshold for all field types can be 50%. Alternatively, a second preset threshold can be set for each field type separately according to the application scenario.

[0127] By using the above methods, the proportion of field values ​​for each field type is reviewed to ensure that the proportion of each field type is within the preset range. This avoids situations where newly added indexes cannot be used, meaning that the new indexes do not help query efficiency but occupy more disk space, thus ensuring the practicality of newly added indexes.

[0128] Optionally, in step 202, which is to determine whether each database information meets its corresponding preset index addition conditions, the steps further include: if the number of target indexes is less than a third preset threshold, it is determined that the preset index addition conditions are met; if the number of target indexes is greater than or equal to the third preset threshold, it is determined that the preset index addition conditions are not met.

[0129] In this embodiment, the database information also includes the target number of indexes. It should be noted that the target number of indexes refers to the number of indexes already existing in the database. In practical applications, indexes are also data and require disk space. Disk is a resource as important as CPU and memory, especially since databases are used to store data and consume a significant amount of disk space. Therefore, it is necessary to limit the number of database indexes to keep index entries within a preset range and avoid excessive disk space consumption. Based on the above reasons, the number of existing indexes in the database is compared with a third preset threshold. Only when the target number of indexes is less than the third preset threshold will adding a new index not exceed the index number limit, thus meeting the preset index addition condition. If the target number of indexes is greater than or equal to the third preset threshold, adding another index would exceed the database index number limit, thus failing to meet the preset index addition condition.

[0130] Optionally, in practical applications, the total number of database indexes is usually no more than 5. Therefore, the third preset threshold is set to 5. Only when the number of existing indexes in the current database is less than 5 can it be ensured that even if a new index is added, it will not exceed the range of the number of database indexes.

[0131] By using the above methods, the number of existing indexes in the database is reviewed to ensure that the number of indexes is within the preset range to meet the conditions for adding indexes. Limiting the number of database indexes has two effects: firstly, it prevents the database from consuming too much disk space due to an excessive number of indexes, ensuring the database's operating performance and response speed to data queries; secondly, it prevents the SQL from hitting the wrong index due to an excessive number of indexes, thus improving the accuracy of index queries.

[0132] Optionally, in step 202, which is to determine whether each database information meets its corresponding preset index addition conditions, the steps further include: determining the second index combination of the index to be added based on the index field to be added; determining whether the index to be added is a redundant index based on the first index combination and the second index combination; if the index to be added is not a redundant index, determining that it meets the preset index addition conditions; if the index to be added is a redundant index, determining that it does not meet the preset index addition conditions.

[0133] In this embodiment, the database information also includes a first index combination of the target fields corresponding to the target index in the database. A second index combination is determined based on the field to be indexed. It can be understood that the first index combination is an index combination of existing indexes, and the second index combination is an index combination of the index to be added. In practical applications, MySQL allows multiple indexes to be created on the same column. If an index (a, b) is created, adding index (a) is redundant because it is only a prefix of the previous index. Therefore, (a, b) can also be used as (a), but (b, a) is not a redundant index, nor is index (b) because b is not the leftmost prefix column of index (a, b). Redundant indexes waste a lot of disk space, and during queries, multiple indexes may be available for the data, affecting database performance. For these reasons, redundancy analysis is performed on the first and second index combinations to determine if the index to be added is redundant. If the index to be added is redundant, it does not meet the preset index addition conditions; if the index to be added is not redundant, it meets the preset index addition conditions.

[0134] In a specific embodiment, the first index combination obtained from the database is (a, b, c). This composite index combination (a, b, c) is equivalent to (a), (a, b) and (a, b, c). If the second index combination obtained is (a, b), it may be determined that the index to be added is a redundant index, and therefore the addition of the index is not allowed.

[0135] Optionally, when all database information meets the preset index addition conditions, the host load is then assessed. If CPU usage is 40% and / or IO usage is 50%, it indicates that the host is under high load, and index addition is not performed at this time.

[0136] Optionally, when all database information meets the preset index addition conditions, the index name length is controlled to be within 25 bytes during index creation to avoid the index name being too long and thus preventing index addition. Further, the index type to be added is obtained, determining whether it is a unique index or a non-unique index, and the syntax for adding the index is determined based on the index type.

[0137] 203. If multiple database entries meet the preset index addition conditions, add an index for the field to be indexed.

[0138] 204. If at least one database information does not meet the preset index addition conditions, do not add an index for the field to be added.

[0139] In steps 203 and 204, all database information is evaluated. If all database information meets the preset index addition conditions, it is determined that an index can be added to the field to be indexed. If any database information does not meet its corresponding preset index addition conditions, the index addition process is stopped, and no index is added. The determination of whether to add an index is based on the comparison results between the original database parameters of the index and the variable to be added with the preset conditions. On the one hand, by adding an index only when all database information meets the preset index addition conditions, the index addition conditions are limited, eliminating the need to add an index immediately after data changes, reducing the number of index additions and the time consumed by frequent additions, and improving the efficiency of adding indexes when processing large amounts of data. On the other hand, by setting preset index addition conditions, the number of index additions is limited, saving system resources.

[0140] In a specific embodiment, this application provides a database index adding system for an autoddl tool for automatically adding database indexes. Specifically, the database index adding system is modularized into several modules: a core table judgment module, a space size judgment module, a table type judgment module, a column distinctness judgment module, a baseline judgment module, an index quantity verification module, an index duplication verification module, an index name generation module, a load balancing module, and an index creation module. Multiple database information items are input into the database index adding system, including database user information, database table parameters, field information of the fields to be indexed on the table, and the type of index to be added.

[0141] Specifically, the core table judgment module is used to determine whether the input table is a core table of the production line. If it is a core table, it needs to be reviewed by the DBA. If not, it is approved. The purpose of this module is to prevent operations on the core table from affecting the production line.

[0142] Furthermore, the space size judgment module is responsible for checking whether the target server meets the space requirements for creating this index. If the remaining space is greater than 45 days, it can be approved. For those with more than 15 days but less than or equal to 45 days, the DBA needs to manually review them. Only after review and approval with "-f" can it be approved. This module mainly prevents insufficient space from causing index creation failure.

[0143] Furthermore, the table type determination module is responsible for determining whether the input database table is a partitioned table or a non-partitioned table in order to determine the index creation statement.

[0144] Furthermore, the column discrimination judgment module is mainly responsible for judging the discrimination of columns that need to be indexed. Generally speaking, columns with high discrimination are better as index choices, while columns with low discrimination will not be indexed.

[0145] Furthermore, the baseline judgment module is mainly for Oracle databases because a query SQL has its own baseline. In order to avoid the inability to use the new index after it is added, it is necessary to judge the proportion of each field type.

[0146] Furthermore, the index quantity verification module mainly checks the number of existing indexes. If the number reaches 5, the audit will not be approved to avoid the SQL query being directed to the wrong index due to too many indexes.

[0147] Furthermore, the index duplication check module determines whether the index to be added is a redundant index. If the existing index combination is (a, b, c), then the addition of the index combination (a, b) is not allowed.

[0148] Furthermore, the index name generation module is responsible for generating index names that are less than or equal to 25 bytes in length, to prevent indexes from being unable to be added due to excessively long index names.

[0149] Furthermore, the load balancing module is used to monitor host load, such as CPU and I / O load. Under high load, index addition is paused first.

[0150] Furthermore, the index creation module is used to create indexes, calling the underlying database interface and third-party tools to add indexes.

[0151] The database index addition system provided in this application embodiment, developed using Python, solves the technical problem in the prior art where adding an index requires manual judgment by the DBA to determine whether the corresponding index can be created, resulting in a long review time and low index addition efficiency. By standardizing, programming, and modularizing the entire index addition process, it saves a lot of review time. At the same time, it incorporates expert experience, such as baseline judgment and discrimination judgment, making the entire review process more rigorous.

[0152] Furthermore, as Figure 1 To specifically implement the method, this application embodiment provides a database index adding device 200, such as... Figure 2 As shown, the device includes:

[0153] The acquisition module 201 is used to respond to the database index addition request and acquire multiple database information included in the index addition request, wherein the multiple database information includes database parameter information and field information of the field to be added to the index;

[0154] The judgment module 202 is used to sequentially determine whether each database piece of information meets its corresponding preset index addition conditions;

[0155] Add module 203 to add an index to the field to be indexed when multiple database information meets the preset index addition conditions;

[0156] The addition module 203 is also used to prevent the addition of an index to the field to be added if at least one database information does not meet the preset index addition conditions.

[0157] Optionally, the database parameter information includes the table type information of the database table. The judgment module 202 is specifically used for:

[0158] Based on the table type information, determine whether the database table is a non-core database table;

[0159] If the database table is a non-core database table, ensure that it meets the preset index addition conditions;

[0160] If the database table is a core database table, it is determined that it does not meet the preset index addition conditions;

[0161] Generate audit information based on information from multiple databases;

[0162] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0163] Optionally, the database information includes the number of days the server's space is still available. Module 202, in particular, is also used for:

[0164] If the remaining days of space usage are greater than the first preset space threshold, it is determined that the preset index addition conditions are met.

[0165] If the remaining days of space usage are less than or equal to the first preset space threshold, it is determined that the preset index addition conditions are not met.

[0166] Determine if the remaining days of space usage are greater than the second preset space threshold;

[0167] If the remaining days of space usage are less than or equal to the first preset space threshold, but greater than the second preset space threshold;

[0168] Generate audit information based on information from multiple databases;

[0169] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0170] Optionally, the database information includes multiple field values ​​of the field to be indexed, wherein the multiple field values ​​include multiple identical field values ​​and multiple different field values. The judgment module 202 is further used for:

[0171] Based on multiple field values, determine a first quantity and a second quantity, where the first quantity is the total number of multiple field values ​​and the second quantity is the number of multiple distinct field values;

[0172] Divide the second quantity by the first quantity to obtain the distinguishability of the field to be indexed;

[0173] If the distinguishability is greater than the first preset threshold, it is determined that the preset index addition conditions are met;

[0174] If the discrimination is less than or equal to the first preset threshold, it is determined that the preset index addition conditions are not met.

[0175] Generate audit information based on information from multiple databases;

[0176] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0177] Optionally, the database information includes at least one field type of the field to be indexed, and the judgment module 202 is further used for:

[0178] Determine the third quantity corresponding to each field type based on at least one field type;

[0179] Divide the third quantity by the second quantity to obtain the percentage value of each field type;

[0180] If the percentage of at least one field type is greater than the second preset threshold, it is determined that the preset index addition conditions are met.

[0181] If the percentage value of any field type is less than or equal to the second preset threshold, it is determined that the preset index addition conditions are not met.

[0182] Generate audit information based on information from multiple databases;

[0183] Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

[0184] Optionally, the database information includes the number of target indexes, and the judgment module 202 is further used for:

[0185] If the number of target indexes is less than the third preset threshold, it is determined that the preset index addition conditions are met;

[0186] If the number of target indexes is greater than or equal to the third preset threshold, it is determined that the preset index addition conditions are not met.

[0187] Optionally, the database information includes the first index combination of the target field corresponding to the target index. The judgment module 202 is further used for:

[0188] Determine the second index combination to be added based on the field to be indexed;

[0189] Based on the first index combination and the second index combination, determine whether the index to be added is a redundant index;

[0190] If the index to be added is not a redundant index, it is determined that it meets the preset index addition conditions;

[0191] If the index to be added is a redundant index, it is determined that it does not meet the preset index addition conditions.

[0192] The database index adding device 200 provided in this application embodiment, after obtaining all database information in the index adding request, determines the original database parameters and variable data of the fields to be added in the database information. Then, using the preset index adding conditions corresponding to each database information, it sequentially verifies multiple database information to determine whether each database information meets the preset conditions. An index can only be added when all database information meets the preset conditions; if any database information does not meet its corresponding preset conditions, the index cannot be added. Through the above method, by using the rules to be followed when creating an index and combining the judgment experience of DBAs to preset index adding conditions, all obtained database information is reviewed in a targeted and automatic manner based on the preset index adding conditions, and each database information is judged sequentially to determine whether it meets the preset conditions. This greatly shortens the judgment time, improves the efficiency of data review, and improves the accuracy of data review. This makes the database index adding process more rigorous, avoids inaccurate review results due to insufficient experience of manual review, greatly reduces the dependence on DBAs, and makes it more convenient and effective to maintain the index database.

[0193] In an exemplary embodiment, this application also provides a computer device, including a memory and a processor. The memory stores a computer program, and the processor executes the program stored in the memory to perform the database index addition method in the above embodiments.

[0194] In an exemplary embodiment, this application also provides a readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the database index addition method.

[0195] Through the above description of the embodiments, those skilled in the art can clearly understand that this application can be implemented in hardware or by using software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solution of this application can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) and includes several instructions to cause a computer device (such as a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0196] Those skilled in the art will understand that the accompanying drawings are merely schematic diagrams of a preferred embodiment, and the modules or processes shown in the drawings are not necessarily essential for implementing this application.

[0197] Those skilled in the art will understand that the modules in the apparatus of the implementation scenario can be distributed within the apparatus of the implementation scenario as described, or they can be located in one or more apparatuses different from this implementation scenario, with corresponding changes. The modules of the above-described implementation scenario can be combined into one module, or they can be further divided into multiple sub-modules.

[0198] The serial numbers in this application are for descriptive purposes only and do not represent the superiority or inferiority of the implementation scenario.

[0199] The above disclosures are only a few specific implementation scenarios of this application. However, this application is not limited to these. Any variations that can be conceived by those skilled in the art should fall within the protection scope of this application.

Claims

1. A method for adding a database index, characterized in that, include: In response to a database index addition request, multiple database information included in the index addition request is obtained, wherein the multiple database information includes database parameter information, field information of the field to be indexed, and multiple field values ​​of the field to be indexed, the multiple field values ​​including multiple identical field values ​​and multiple different field values; Check each database entry sequentially to see if it meets the corresponding preset index addition conditions; If all the database information meets the preset index addition conditions, an index is added to the field to be indexed. If at least one database information does not meet the preset index addition conditions, no index will be added to the field to be indexed. Specifically, if the database information at any stage does not meet its corresponding preset index addition conditions, the index addition will be stopped. The step of sequentially determining whether each database piece of information meets its corresponding preset index addition conditions specifically includes: Based on the multiple field values, a first quantity and a second quantity are determined, wherein the first quantity is the total number of the multiple field values, and the second quantity is the number of the multiple different field values; Divide the second quantity by the first quantity to obtain the distinguishability of the field to be indexed; If the distinguishability is greater than the first preset threshold, it is determined that the preset index addition condition is met; If the distinguishability is less than or equal to the first preset threshold, it is determined that the preset index addition condition is not met. The database information includes at least one field type of the field to be indexed, and the step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions further includes: Based on the at least one field type, determine the third quantity corresponding to each field type; Divide the third quantity by the first quantity to obtain the percentage value of each field type; If the percentage of at least one field type is greater than the second preset threshold, it is determined that the preset index addition condition is met. If the percentage value of any field type is less than or equal to the second preset threshold, it is determined that the preset index addition condition is not met. Based on the information from the multiple databases, audit information is generated; The review information is sent so that the database administrator can determine whether an index can be added based on the review information.

2. The method according to claim 1, characterized in that, The database parameter information includes the table type information of the database tables. The step of sequentially determining whether each database information meets the corresponding preset index addition conditions further includes: Based on the table type information, determine whether the database table is a non-core database table; If the database table is a non-core database table, it is determined that it meets the preset index addition conditions; If the database table is a core database table, it is determined that it does not meet the preset index addition conditions; Based on the information from the multiple databases, audit information is generated; The review information is sent so that the database administrator can determine whether an index can be added based on the review information.

3. The method according to claim 1, characterized in that, The database information includes the remaining days of server space usage. The step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions further includes: If the remaining days of space usage are greater than the first preset space threshold, it is determined that the preset index addition condition is met; If the remaining days of space usage are less than or equal to the first preset space threshold, it is determined that the preset index addition condition is not met. Determine whether the remaining number of days of space usage is greater than a second preset space threshold; If the remaining days of space use are less than or equal to the first preset space threshold, and greater than the second preset space threshold; Based on the information from the multiple databases, audit information is generated; The review information is sent so that the database administrator can determine whether an index can be added based on the review information.

4. The method according to claim 1, characterized in that, The database information includes the target number of indexes. The step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions further includes: If the number of target indexes is less than a third preset threshold, it is determined that the preset index addition conditions are met. If the number of target indexes is greater than or equal to the third preset threshold, it is determined that the preset index addition conditions are not met.

5. The method according to claim 1, characterized in that, The database information includes a first index combination with indexed fields. The step of sequentially determining whether each piece of database information meets its corresponding preset index addition conditions further includes: Based on the fields to be indexed, determine the second index combination to be added; Based on the first index combination and the second index combination, determine whether the index to be added is a redundant index; If the index to be added is not the redundant index, it is determined that the preset index addition condition is met; If the index to be added is a redundant index, it is determined that it does not meet the preset index addition conditions.

6. A database index adding device, characterized in that, include: The acquisition module is used to respond to the database index addition request and acquire multiple database information included in the index addition request. The multiple database information includes database parameter information, field information of the field to be indexed, and multiple field values ​​of the field to be indexed. The multiple field values ​​include multiple identical field values ​​and multiple different field values. The judgment module is used to sequentially determine whether each database piece of information meets its corresponding preset index addition conditions; The addition module is used to add an index to the field to be indexed when all the database information meets the preset index addition conditions; The addition module is also used to prevent the addition of an index to the field to be indexed if at least one database information does not meet the preset index addition conditions. Specifically, when the database information at any stage does not meet its corresponding preset index addition conditions, the index addition is stopped. The judgment module is specifically used for: Based on the multiple field values, a first quantity and a second quantity are determined, wherein the first quantity is the total number of the multiple field values, and the second quantity is the number of the multiple different field values; Divide the second quantity by the first quantity to obtain the distinguishability of the field to be indexed; If the distinguishability is greater than the first preset threshold, it is determined that the preset index addition condition is met; If the distinguishability is less than or equal to the first preset threshold, it is determined that the preset index addition condition is not met. The database information includes at least one field type of the field to be indexed, and the judgment module is also used for: Determine the third quantity corresponding to each field type based on at least one field type; Divide the third quantity by the second quantity to obtain the percentage value of each field type; If the percentage of at least one field type is greater than the second preset threshold, it is determined that the preset index addition conditions are met. If the percentage value of any field type is less than or equal to the second preset threshold, it is determined that the preset index addition conditions are not met. Generate audit information based on information from multiple databases; Send an approval message so that the database administrator can determine whether an index can be added based on the approval information.

7. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 5.

8. A readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 5.

Images