Method for creating database index
By analyzing the SQL statement and table connection types, preset rules are used to determine the index fields and structures, which solves the problem that the database index is not used or consumes a lot of resources after creation, and achieves efficient index creation and database performance improvement.
Patent Information
- Application Number
- CN202411193627.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-28
- Publication Date
- 2024-12-03
AI Technical Summary
Existing database index creation methods are not used or consume too much resources after the index is created, resulting in database performance problems and it is difficult to effectively deal with performance degradation under large-scale data.
By analyzing the operation type, table join type and field name of SQL statements, using preset fields to add rules and sorting rules, we can finely determine the fields and index structures that need to be indexed to generate efficient non-clustered indexes.
Improves the database's response performance and retrieval efficiency, reduces I/O operations, saves resources, adapts to large-scale data, reduces maintenance costs, and makes full use of hardware resources.
Abstract
Description
Technical Field
[0001] The invention belongs to the technical field of database indexes, and in particular relates to a method for creating a database index. Background Art
[0002] For the relational databases currently on the market (such as Oracle, SQL Server, MySQL, PG, etc.), we create non-clustered indexes (or secondary indexes, auxiliary indexes) and strive to create ideal indexes to solve the performance problems that may be caused by the large amount of data in the database. An effective and good index is very important, and the benefits it brings can be higher than the hardware effects brought by the superposition of multiple CPUs and multiple memory superposition. Common problems when indexing are: 1. The index is not used when it is created; 2. Although the corresponding index is used, the consumption is still very large and the time consumption is still very long. Therefore, it is necessary to create an effective and efficient indexing method to improve the response performance of the database. Summary of the invention
[0003] The purpose of the present invention is to provide a method for creating a database index to solve the above problems existing in the prior art.
[0004] In order to achieve the above object, the present invention adopts the following technical solutions:
[0005] A method for creating a database index comprises the following steps:
[0006] Determine whether the operation type of the SQL statement needs to be indexed: Determine the operation type of the SQL statement and create indexes for SQL statements whose operation types contain query, modification, and deletion meanings;
[0007] Identify the table join type of the SQL statement;
[0008] Identify table types, which include user tables and system tables, extract field names of user tables; ignore system tables; the field names include multiple types of fields; the fields include: condition fields, grouping and sorting fields, query and display fields, and modification and setting fields;
[0009] According to the preset field addition rules, confirm the fields that need to be added to the index;
[0010] According to the preset field sorting rule corresponding to the table connection type, the fields added to the index are sorted to generate the index.
[0011] Furthermore, the preset field adding rules include:
[0012] Get the number of rows after filtering by the conditional field, and determine whether the number of rows is greater than the preset threshold or the number of rows is greater than the preset value of the percentage of the total number of rows in the corresponding table. If so, add all fields in the SQL statement to the index; if not, add the conditional field to the index.
[0013] Furthermore, the preset field adding rules include:
[0014] Get the number of fields and index length. If the number of fields exceeds the preset limit of the number of index fields, they are added to the index in the order of condition fields, grouping and sorting fields, query display fields and / or modification setting fields.
[0015] Furthermore, the preset field adding rules include:
[0016] Remove large character fields that exceed the preset length;
[0017] Remove fields that have been modified more frequently than the preset frequency;
[0018] If a field in the entire query display field is not added to the index due to the preset rule, the corresponding entire query display field is removed;
[0019] If a field in the entire group sorting field is not added to the index due to the preset rules, the corresponding entire group sorting field is removed;
[0020] If a field of the entire modification setting field is not added to the index due to a preset rule, the corresponding entire modification setting field is removed.
[0021] Furthermore, the preset field adding rule includes: adding a preset field defined as having poor selectivity to the index.
[0022] Furthermore, the preset field adding rule includes: if the field type only includes a grouping and sorting field and a query and display field, both the grouping and sorting field and the query and display field are added to the index.
[0023] Furthermore, according to the preset field sorting rule corresponding to the table connection type, the fields added to the index are sorted from left to right, and the sorting method includes:
[0024] If there is no grouping sort field in the SQL statement, perform the following operations:
[0025] The fields of the created index are from left to right: the first field, the second field, and the remaining fields.
[0026] The first field includes all or part of the fields in the equal value condition field and / or all or part of the fields in the ISNULL condition field, and the equal value condition field and the ISNULL condition field in the first field can be interchanged;
[0027] The second field is a range condition field with the best filtering effect in the condition field, and the range condition field with the best filtering effect is determined according to a preset filtering effect determination condition;
[0028] The remaining fields are the fields remaining in the current SQL statement except the first field and the second field, and the order of the multiple fields of the remaining fields can be interchanged;
[0029] When the first field is part of the equal value condition field and / or part of the ISNULL condition field, it is recorded as the first field of type A; when the first field is all the fields of the equal value condition field and / or the ISNULL condition field is all the fields, it is recorded as the first field of type B;
[0030] Assume that the number of rows filtered by the first field of type B plus the second field is α, and the number of rows filtered by the first field of type A plus the second field is β. If β-α<γ is satisfied, and γ is a first preset threshold, the first field selects the first field of type A or the first field of type B; if not satisfied, the first field selects the first field of type B.
[0031] If the SQL statement has a grouping sort field, perform the following operations:
[0032] Obtain the best filtering effect field in the condition field in the SQL statement, where the best filtering effect field is the range condition field in the condition field that meets the preset filtering requirements;
[0033] When the following conditions are met:
[0034] The first field of the grouping sorting field is the same as a field in the filtering effect superior field; or
[0035] The difference between the number of rows filtered out by the first field and the number of rows filtered out after adding all the range condition fields is less than a fourth preset threshold; or
[0036] The first field of the grouping sorting field is queried immediately after the first field, and the number of rows obtained is C. The second field is queried immediately after the first field, and the number of rows obtained is D. The number of rows after filtering all fields of the SQL is E. If (CD) / E is less than or equal to J, J is the fifth preset threshold;
[0037] The created index fields are the first field, the grouping and sorting field, and the remaining fields from left to right;
[0038] If not satisfied, the created index fields are, from left to right, the first field, the second field, and the remaining fields.
[0039] Further, according to the preset field sorting rule corresponding to the table join type, the table join type includes a single-table join type and a multi-table join type; when the table join type is a multi-table join type, the following operations are further performed:
[0040] If the SQL statement has grouped sorting fields, and all the grouped sorting fields are fields of a certain table X among multiple tables;
[0041] Obtain the number of rows filtered by the local condition fields of each table.
[0042] Judge whether the difference between the number of rows filtered by the local condition fields of table X and the number of rows filtered by the local condition fields of another associated table is less than or equal to the sixth preset threshold. If so, select the table X where the sorting grouped field is located as the entry table; the entry table is the driving table when using nested loops for multi-table join queries.
[0043] When the difference between the number of rows filtered by the local condition fields of table X and the number of rows filtered by the local condition fields of another associated table is greater than the sixth preset threshold, the following rules are used for judgment:
[0044] Let the value of the number of rows filtered by the local condition fields of the table where the grouped sorting field is located be M, the value of the number of rows filtered by the local condition fields of another associated table be N, and the value of the number of rows to be sorted after the two tables are associated be G.
[0045] When (M - N) / G < H, where H is a preset value, select the table where the grouped sorting field is located as the entry table.
[0046] The value of the number of rows filtered by the local condition fields of the table where the grouped sorting field is located minus the value of the number of rows filtered by the local condition fields of another associated table divided by the value of the number of rows to be sorted after the two tables are associated is less than the preset threshold. At this time, select the table where the grouped sorting field is located as the entry table.
[0047] Further, when finding the second field, when the first field exists on the left side of the index, the range condition field to be judged needs to be searched and filtered together with the first field to judge the filterability of the range condition field; if the difference between the number of rows after search and filter corresponding to multiple range condition fields is less than the second preset threshold, select one field as the second field according to the preset field selection condition.
[0048] Furthermore, for the determination of the remaining fields, if the remaining fields are conditional fields, then the conditional fields should be added to the index. When the conditional fields have no effect on data filtering and other SQL statements modify the fields more than the preset frequency, the conditional fields do not need to be added to the index.
[0049] Beneficial effects:
[0050] 1. The advantage of the above indexing method is that it is fast. It only needs to establish the index according to the rules. After matching the rules, only one index will be generated, and no large number of indexes will be generated. The impact on the performance and disk space of the user database is very small. When the number of fields in the filtering conditions of the SQL statement is large, a large number of combinations will be generated. Theoretically, due to too many combinations, not all combinations can be truly generated. Even if they can be generated, the time consumed is very large, and the experience is also very bad. Optimized index creation: The present invention can more accurately identify the occasions where indexes need to be established by carefully analyzing and judging the operation type, connection type and field name of the SQL statement, thereby optimizing the index creation process, avoiding unnecessary index creation, and improving the operation efficiency of the database.
[0051] 2. Improve retrieval efficiency: The non-clustered index (secondary index, auxiliary index) created by the present invention can better meet complex query requirements, improve data retrieval efficiency, reduce I / O operations, and save time and resources.
[0052] 3. Adaptability to large-scale data: The index creation method of the present invention can better cope with large-scale data. By optimizing the index structure, it can avoid the degradation of database performance caused by excessive data volume and improve the stability and reliability of the database.
[0053] 4. Make full use of hardware resources: The index creation method of the present invention can better utilize multi-core CPUs and large-capacity memory, and further improve the performance of the database by optimizing the resource utilization of the database system.
[0054] 5. Reduce maintenance costs: The index creation method of the present invention can reduce invalid index creation, reduce database maintenance costs, and improve database utilization efficiency. DETAILED DESCRIPTION
[0055] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the present invention is briefly introduced below with reference to the embodiments or the prior art. Obviously, the description of these embodiments is used to help understand the present invention, but does not constitute a limitation of the present invention.
[0056] Example:
[0057] This embodiment provides a method for creating a database index, which is characterized by including the following steps:
[0058] Determine whether the operation type of the SQL statement requires creating an index: Determine the operation type of the SQL statement, and create an index for the SQL statement whose operation type contains the meanings of query, modification, and deletion;
[0059] Identify the table join type of the SQL statement;
[0060] Identify the table type, where the table type includes user tables and system tables, and extract the field names of the user tables; ignore the system tables; the field names include various types of fields; the fields include: conditional fields, grouping and sorting fields, query display fields, and modification setting fields;
[0061] According to the preset field addition rules, confirm the field types to be added to the index;
[0062] According to the preset field sorting rules corresponding to the table join type, sort the fields added to the index to generate an index. Specifically, the sorting is a left - right sorting according to the priority, and the priority of the left - hand side is greater than that of the right - hand side.
[0063] The field types include: conditional fields, grouping and sorting fields, query display fields, and modification setting fields. Specific examples are as follows:
[0064] Taking specific example fields as an example, the example fields are described in detail as follows:
[0065] select t1.a,t1.b,t2.c,t1.d,t2.g from t1,t2
[0066] where t1.d='xxx'and t1.e in('xxx')and t1.f in('xxx','xxx2')and t2.g>='xxx'and t2.h like'中国%'and t2.k+100>1000and t2.m is null and t1.id1=t2.id2
[0067] 1: Conditional fields:
[0068] Among them, d, e, f, g, h, k, m, id1, and id2 are all fields for filtering and screening, and these fields are called conditional fields;
[0069] For table t1, its own conditional fields that are not related to other tables, d, e, and f, are called local conditional fields, and the field id1 used to connect with other tables is called the connection conditional field;
[0070] For table t2, its own condition fields that are not linked to other tables are called local condition fields by ghkm, and the fields that are linked to other tables using the id2 field are called the link condition fields by ghkm.
[0071] The de field is also called the equal value condition field;
[0072] The fgh field is also called the range condition field. Note that f in('xxx','xxx2') and f = 'xxx' or f = 'xxx2' have the same meaning. If the two values of between and are different, they are also considered to be range condition fields.
[0073] The k field is called a calculated conditional field;
[0074] The m field is called the ISNULL condition field. If the filter condition of m is m is not null, then the m field is called the range condition field.
[0075] 2: Group sorting fields:
[0076] Fields for grouping and sorting, such as the field after group by (group by field), the field after order by (order by field), and the field after distinct (distinct field). Other grouping and sorting fields refer to fields that are also used for grouping and sorting in addition to the three keywords mentioned above, such as the field after partition by in the window function;
[0077] 3: Query display fields:
[0078] The abc field is a query display field, which refers to the fields in the query statement except the condition field and the grouping and sorting field;
[0079] 4: Modify the setting fields:
[0080] For example, in update t1 set a=b+1where c=100, the ab field belongs to the modification setting field, which refers to the field in the modification statement except the condition field.
[0081] In a specific implementation, the preset field adding rules include:
[0082] Get the number of rows after filtering by the conditional field, and determine whether the number of rows is greater than the preset threshold or the number of rows is greater than the preset value of the percentage of the total number of rows in the corresponding table. If so, add all fields in the SQL statement to the index; if not, add the conditional field to the index.
[0083] During specific implementation, the input value of the conditional field should be as close as possible to the input value of the production environment. There are many ways to obtain the number of rows. The specific acquisition method is not the focus of the present invention and will not be described here. The number of rows refers to the original data that meets the conditions. When there is a grouping and duplicate removal operation, it should be the data before the grouping and duplicate removal; when there are keywords such as top n and limit n that take the first few rows, n should be assigned a value that is used most of the time to ensure the accuracy of the number of rows of toplimit as much as possible, similar to count(*). When the number of rows is relatively large, such as greater than 1,000, or greater than 20% of the total number of rows in the table, all fields in the SQL must be added to the index; if the number of rows is relatively small, and there are large character type fields and frequently modified fields in the SQL, only the conditional fields that effectively filter data should be added to the index.
[0084] In a specific implementation, the preset field adding rules include:
[0085] Get the number of fields and index length. If the number of fields exceeds the preset limit of the number of index fields, they are added to the index in the order of condition fields, grouping and sorting fields, query and display fields, and modification and setting fields.
[0086] In a specific implementation, the preset field adding rule includes: filtering fields that meet preset conditions;
[0087] Remove large character fields that exceed the preset length;
[0088] Remove fields that have been modified more frequently than the preset frequency;
[0089] If a field in the entire query display field is not added to the index due to the preset rule, the corresponding entire query display field is removed;
[0090] If a field in the entire group sorting field is not added to the index due to the preset rules, the corresponding entire group sorting field is removed;
[0091] If a field of the entire modification setting field is not added to the index due to a preset rule, the corresponding entire modification setting field is removed.
[0092] Specifically, the presence of large character type fields in the field will cause the index to increase in size. Some types of fields do not support being added to the index, which will reduce the index efficiency. In this case, there is no need to add fields of this type to the index, especially when the number of rows is small.
[0093] Specifically, depending on whether the field is modified frequently, if there is a field whose modification frequency exceeds the preset frequency, the field will not be added to the index;
[0094] If some fields are not conditional fields, or are conditional fields but do not play an important role in filtering, and other SQL statements have fields that exceed the preset modification frequency (for example, more than 10 times per second), the field will not be added to the index.
[0095] For example, if a query display field is not added to the index for some reason, then in order to reduce the index, all query display fields are not added to the index; similarly, if a grouping sort field is not added to the index for some reason, then in order to reduce the index, all grouping sort fields are not added to the index.
[0096] In a possible implementation, the preset field adding rule includes: adding a preset field defined as poorly selective to the index. For example, a field with poor selectivity such as gender is not affected in being added to the index.
[0097] In a possible implementation, the preset field adding rule includes: if the field type only has a group sorting field and a query display field, both the group sorting field and the query display field are added to the index. Specifically, if there is no condition field, only a group sorting field and a query display field, these fields are added to the index.
[0098] In a possible implementation, the fields added to the index are sorted from left to right according to a preset field sorting rule corresponding to the table connection type. The sorting method includes:
[0099] If there is no grouping sort field in the SQL statement, perform the following operations:
[0100] The fields of the created index are from left to right: first field, second field, remaining fields.
[0101] The first field includes all or part of the fields in the equal value condition field and / or all or part of the fields in the ISNULL condition field, and the equal value condition field and the ISNULL condition field in the first field can be interchanged;
[0102] The second field is a range condition field with the best filtering effect in the condition field, and the range condition field with the best filtering effect is determined according to a preset filtering effect determination condition;
[0103] The remaining fields are the fields remaining in the current SQL statement except the first field and the second field, and the order of the multiple fields of the remaining fields can be interchanged;
[0104] When the first field is part of the equal value condition field and / or part of the ISNULL condition field, it is recorded as the first field of type A; when the first field is all the fields of the equal value condition field and / or the ISNULL condition field is all the fields, it is recorded as the first field of type B;
[0105] Assume that the number of rows filtered by the first field of type B plus the second field is α, and the number of rows filtered by the first field of type A plus the second field is β. If β-α<γ is satisfied, and γ is a first preset threshold, the first field selects the first field of type A or the first field of type B; if not satisfied, the first field selects the first field of type B.
[0106] If the SQL statement has a grouping sort field, perform the following operations:
[0107] Obtain the best filtering effect field in the condition field in the SQL statement, where the best filtering effect field is the range condition field in the condition field that meets the preset filtering requirements;
[0108] When the following conditions are met:
[0109] The first field of the grouping sorting field is the same as a field in the filtering effect superior field; or
[0110] The difference between the number of rows filtered out by the first field and the number of rows filtered out after adding all the range condition fields is less than a fourth preset threshold; or
[0111] The first field of the grouping sorting field is queried immediately after the first field, and the number of rows obtained is C. The second field is queried immediately after the first field, and the number of rows obtained is D. The number of rows after filtering all fields of the SQL is E. If (CD) / E is less than or equal to J, J is the fifth preset threshold;
[0112] The created index fields are the first field, grouping and sorting fields, and remaining fields from left to right.
[0113] If not, the created index fields are the first field, the second field, and the remaining fields from left to right.
[0114] For example, if the fifth preset threshold is 1, it is considered that the overhead of placing the "grouping sort field" immediately after the "equal value condition field and all or part of the fields in the ISNULL condition field" is lower. Then, the fields of the created index are the first field, the grouping sort field, and the remaining fields from left to right.
[0115] Specifically, a SQL statement filters 20,000 rows that meet the criteria. The number of rows obtained by filtering using "all or part of the fields in the equal condition fields and ISNULL condition fields" plus the first field of the "grouping sort field" is 10,000 more than the number of rows obtained by filtering using "all or part of the fields in the equal condition fields and ISNULL condition fields" plus the "field with the best filtering effect". However, since additional sorting is not required, the cost of sorting 20,000 rows of data is saved, so the cost of not sorting additionally is lower at this time.
[0116] First, the table connection type includes a single table type and a table connection type.
[0117] During implementation, determine how to sort the fields added to the index from left to right according to the following rules:
[0118] For single-table SQL, this type of SQL contains only one table and does not contain subqueries or multi-table associations.
[0119] When there is no grouping and sorting field, such as: select a,b,c,d,e,f,g from t1 where a=xxx andb=xxx and c=xxx and d>xxx and e>='xxx'and f<'xxx';
[0120] You can create the following indexes. Choose the most appropriate one based on SQL and business conditions:
[0121] The fields of the created index are, from left to right, the equal value condition fields and all or part of the fields in the ISNULL condition fields, the fields with the best filtering effect, and the remaining fields; the equal value condition fields and the ISNULL condition fields have parallel priorities;
[0122] Specifically, all or part of the fields in the equal condition field and the ISNULL condition field: such as the three fields abc in this example, the left and right order of the three fields abc can be interchanged. Of course, it is not necessary to put all three fields abc on the leftmost side of the index. If not all three fields abc are put on the left side, then the filtering effect should not be too different (not higher than the preset threshold of 100), otherwise the index performance will be affected, that is: the number of rows filtered by the first field of type A (part of the fields in all or part of the fields in the equal condition field and the ISNULL condition field) plus the second field (the field with the best filtering effect) is β, and the number of rows filtered by the first field of type B (all the fields in all or part of the fields in the equal condition field and the ISNULL condition field) plus the second field (the field with the best filtering effect) is α, which satisfies β-α<γ, γ is the first preset threshold, and in the specific example, the first preset threshold is 100.
[0123] The filtering effect refers to the number of rows obtained by filtering according to the preset conditions. For example, if the number of rows that meet the condition where a=xxx is 10,000, and the number of rows that meet the condition where b=xxx is 20,000, then the filtering effect of field b is worse than that of field a.
[0124] The fields with the best filtering effect refer to: only the range condition fields that can fully utilize the index are used, and the calculated condition fields are directly ignored. For example, where d>xxx filters out 10,000 data items, while where e>='xxx' filters out 20,000 data items. In this case, we believe that field d has better filtering performance, so field d is the field with the best filtering effect.
[0125] In a specific implementation, according to the preset field sorting rule corresponding to the table connection type, the table connection type includes a single table connection type and a multi-table connection type; when the table connection type is a multi-table connection type, the following operations are further performed:
[0126] If the SQL statement contains grouping and sorting fields, and all the grouping and sorting fields are fields of a table X among multiple tables;
[0127] Get the number of rows filtered by the local condition fields of each table;
[0128] Determine whether the difference between the number of rows filtered out of table X through the local condition field and the number of rows filtered out of another associated table through the local condition field is less than or equal to a sixth preset threshold value, and if so, select table X where the sorting group field is located as the entry table; the entry table is a driving table when a nested loop is used in a multi-table join query;
[0129] When the difference between the number of rows filtered from Table X by the local condition field and the number of rows filtered from another associated table by the local condition field is greater than the sixth preset threshold, the following rules are used for judgment:
[0130] Let the value of the number of rows filtered from the table where the grouping and sorting field is located by the local condition field be M, the value of the number of rows filtered from another associated table by the corresponding local condition field be N, and the value of the number of rows to be sorted after the two tables are associated be G.
[0131] When (M - N) / G < H, where H is a preset value, for example, H is 50, in this case, the table where the grouping and sorting field is located is selected as the entry table.
[0132] In a specific implementation manner, when looking for the second field, if the first field is on the left side of the index, the range condition field to be judged needs to be searched and filtered together with the first field to determine the range condition field with better filtering performance; if the difference between the number of rows after search and filter corresponding to multiple range condition fields is less than the second preset threshold, then according to the preset field selection condition, one field is selected as the second field. Specifically, when looking for a field with excellent filtering effect, if the left side of the index is "all or part of the equal value condition field and the ISNULL condition field", then when obtaining which range condition field has better filtering performance, the left side "all or part of the equal value condition field and the ISNULL condition field" needs to be added. For example, in this example, when obtaining the filtering performance of field d, instead of using the condition "where d > xxx" to obtain it, we use the condition "where a = xxx and b = xxx and c = xxx and d > xxx" to obtain it.
[0133] In specific implementation, if the number of rows filtered by "all or part of the equal value condition field and the ISNULL condition field" is very small (for example, the second preset threshold is less than 100 rows), then there is no need to find out which field is the field with excellent filtering effect, that is: any range condition field can be considered as the "field with excellent filtering effect".
[0134] In a possible implementation manner, for the determination of the remaining fields, if the remaining field belongs to the condition field, then the condition field should be added to the index. When the condition field has no effect on data filtering and other SQL makes frequent modifications to this field, then the condition field is not added to the index.
[0135] The indexing method provided in this embodiment has the advantage of being fast. It only needs to establish the index according to the rules. After matching the rules, only one index will be generated instead of a large number of indexes. The impact on the performance of the user database, disk space, etc. is very small. In the existing invention, when the number of fields in the filter conditions of the SQL statement is large, a large number of combinations will be generated. Theoretically, since there are too many combinations, not all combinations can be truly generated. Even if they can be generated, the time consumed is very large and the experience is also very bad.
[0136] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.
Claims
1. A method for creating a database index, characterized in that: The following steps are involved: Determine whether the operation type of the SQL statement needs to be indexed: Determine the operation type of the SQL statement and create indexes for SQL statements whose operation types contain query, modification, and deletion meanings; Identify the table join type of the SQL statement; Identify table types, which include user tables and system tables, and extract field names of the user tables; Ignore the system table; The field names include multiple types of fields; the fields include: condition fields, grouping and sorting fields, query and display fields, and modification and setting fields; According to the preset field addition rules, confirm the fields that need to be added to the index; According to the preset field sorting rule corresponding to the table connection type, the fields added to the index are sorted to generate the index.
2. The method for creating a database index according to claim 1, characterized in that: The preset field joining rules include: Get the number of rows after filtering by the conditional field, and determine whether the number of rows is greater than the preset threshold or the number of rows is greater than the preset value of the percentage of the total number of rows in the corresponding table. If so, add all fields in the SQL statement to the index; if not, add the conditional field to the index.
3. The method for creating a database index according to claim 1, characterized in that: The preset field joining rules include: Get the number of fields and index length. If the number of fields exceeds the preset limit of the number of index fields, they are added to the index in the order of condition fields, grouping and sorting fields, query display fields and / or modification setting fields.
4. The method for creating a database index according to claim 1, characterized in that: The preset field joining rules include: Remove large character fields that exceed the preset length; Remove fields that have been modified more frequently than the preset frequency; If a field in the entire query display field is not added to the index due to the preset rule, the corresponding entire query display field is removed; If a field in the entire group sorting field is not added to the index due to the preset rules, the corresponding entire group sorting field is removed; If a field of the entire modification setting field is not added to the index due to a preset rule, the corresponding entire modification setting field is removed.
5. The method for creating a database index according to claim 1, characterized in that: The preset field joining rules include: adding the preset fields defined as poorly selective to the index.
6. The method for creating a database index according to claim 1, characterized in that: The preset field adding rules include: if the field type only has a grouping sorting field and a query display field, both the grouping sorting field and the query display field are added to the index.
7. The method for creating a database index according to claim 1, characterized in that: According to the preset field sorting rule corresponding to the table connection type, the fields added to the index are sorted from left to right. The sorting method includes: If there is no grouping sort field in the SQL statement, perform the following operations: The fields of the created index are from left to right: the first field, the second field, and the remaining fields. The first field includes all or part of the fields in the equal value condition field and / or all or part of the fields in the ISNULL condition field; The second field is a range condition field with the best filtering effect in the condition field, and the range condition field with the best filtering effect is determined according to a preset filtering effect determination condition; The remaining fields are the fields remaining in the current SQL statement except the first field and the second field; When the first field is part of the equal value condition field and / or part of the ISNULL condition field, it is recorded as the first field of type A; when the first field is all the fields of the equal value condition field and / or the ISNULL condition field is all the fields, it is recorded as the first field of type B; Assume that the number of rows filtered by the first field of type B plus the second field is α, and the number of rows filtered by the first field of type A plus the second field is β. If β-α<γ is satisfied, and γ is a first preset threshold, the first field selects the first field of type A or the first field of type B; if not satisfied, the first field selects the first field of type B. If the SQL statement has a grouping sort field, perform the following operations: Obtain the best filtering effect field in the condition field in the SQL statement, where the best filtering effect field is the range condition field in the condition field that meets the preset filtering requirements; When the following conditions are met: The first field of the grouping sorting field is the same as a field in the filtering effect superior field; or The difference between the number of rows filtered out by the first field and the number of rows filtered out after adding all the range condition fields is less than a fourth preset threshold; or The first field of the grouping sorting field is queried immediately after the first field, and the number of rows obtained is C. The second field is queried immediately after the first field, and the number of rows obtained is D. The number of rows after filtering all fields of the SQL is E. If (CD) / E is less than or equal to J, J is the fifth preset threshold; The created index fields are the first field, the grouping and sorting field, and the remaining fields from left to right; If not, the created index fields are the first field, the second field, and the remaining fields from left to right.
8. The method for creating a database index according to claim 7, characterized in that: According to the preset field sorting rule corresponding to the table connection type, the table connection type includes a single table connection type and a multi-table connection type; when the table connection type is a multi-table connection type, the following operations are further performed: If the SQL statement contains grouping and sorting fields, and all the grouping and sorting fields are fields of a table X among multiple tables; Get the number of rows filtered by the local condition fields of each table; Determine whether the difference between the number of rows filtered out of table X through the local condition field and the number of rows filtered out of another associated table through the local condition field is less than or equal to a sixth preset threshold value, and if so, select table X where the sorting group field is located as the entry table; the entry table is a driving table when a nested loop is used in a multi-table join query; When the difference between the number of rows filtered out of table X through the local condition field and the number of rows filtered out of another associated table through the local condition field is greater than the sixth preset threshold, the following rules are used for judgment: Suppose the number of rows filtered by the local condition field in the table where the group sorting field is located is M, the number of rows filtered by the corresponding local condition field in another associated table is N, and the number of rows that need to be sorted after the two tables are associated is G. When (M - N) / G < H, where H is a preset value, the table where the grouped sorting field is located is selected as the entry table.
9. The method for creating a database index according to claim 7, wherein When looking for the second field, if there is a first field on the left side of the index, the range condition field to be judged needs to be searched and filtered together with the first field to judge the filterability of the range condition field; if the difference between the number of rows after search and filter corresponding to multiple range condition fields is less than a second preset threshold, then according to the preset field selection condition, one field is selected as the second field.
10. The method for creating a database index according to claim 7, characterized in that: For the determination of the remaining fields, if the remaining fields belong to the conditional fields, then the conditional fields should be added to the index. When the conditional fields do not play a role in filtering the data and other SQL statements modify the fields more than the preset frequency, the conditional fields are not added to the index.
Citation Information
Patent Citations
Automated database index creation method and system
CN103810212A
Database index creation method and device
CN107016019A
A method and apparatus for creating a database index
CN109145004A
Index generation method and device, electronic equipment and readable storage medium
CN114281845A
Method, system and program product for rewriting structured query language (SQL) statements
US20060106766A1
