Cloud-native distributed database load index risk sentence identification system and method

Abstract

The application discloses a cloud-native distributed database load index risk statement identification system and method, and belongs to the technical field of distributed databases. The technical problem to be solved by the application is how to improve the accuracy of database load index, reduce the knowledge reserve and ability requirement of a database administrator, simplify the difficulty of index maintenance and optimization of the database administrator and users, improve work efficiency, and adopt the technical scheme. The system comprises a health analysis rule engine unit and a machine learning analysis unit. The health analysis rule engine unit is used for establishing a rule engine, considering rules from multiple dimensions, and establishing a mathematical statistical model based on the data distribution of the application load portrait multidimensional features to form a parameter-based rule. Each rule has corresponding parameters, which are default experience values and can be adjusted by users. Different analysis sensitivities are set by adjusting the analysis parameters to identify SQL statements that may have missing indexes and SQL statements that have optimization space.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, specifically to a cloud-native distributed database load index risk statement identification system and method. Background Technology

[0002] In modern database applications, index optimization is a crucial practical issue in database management systems. Especially when accessing large-scale data, creating suitable indexes can significantly reduce the number of disk scans and improve workload efficiency. However, index optimization and maintenance typically require a deep understanding of the internal optimization and execution principles of the database engine by operations or development personnel. Furthermore, users have limited resources, making it difficult to consider creating, modifying, or deleting indexes for every query, and finding the globally optimal index optimization solution is also challenging. Moreover, the constantly changing application load, including variations in data volume and the number of insert, delete, and update operations, leads to increased database complexity and maintenance costs. Therefore, application loads may suffer from missing or redundant indexes, or improperly created indexes.

[0003] The primary task in solving the above problems is to accurately identify SQL statements with index risks from massive application loads.

[0004] Current technologies for optimizing distributed database load indexes are not intelligent enough. They require manual selection of database instances and lists of instances to be optimized, and cannot identify the statement level, resulting in coarse-grained optimization. Furthermore, the limitations of manual intervention prevent accurate identification of objects requiring optimization. Moreover, the analysis only relies on collected logs and data, lacking analysis of application load characteristics and behavior, as well as index usage. This fails to provide users with SQL diagnostic information, including index risks, requiring database administrators (DBAs) to further analyze and assess the situation based on their experience. Consequently, DBAs cannot accurately grasp the characteristics and usage of indexes in specific applications. In addition, the lack of continuous monitoring of existing and optimized indexes prevents database administrators from intuitively assessing the impact of indexes on application load performance, thus affecting subsequent re-optimization of indexes or statements.

[0005] Therefore, how to improve the accuracy of database load indexes while reducing the knowledge and ability requirements for database administrators, simplifying the difficulty for database administrators and users to maintain and optimize indexes, and improving work efficiency are technical problems that urgently need to be solved. Summary of the Invention

[0006] The technical objective of this invention is to provide a cloud-native distributed database load index risk statement identification system and method to address the issues of how to improve the accuracy of database load indexes, while reducing the knowledge and skill requirements for database administrators, simplifying the difficulty for database administrators and users to maintain and optimize indexes, and improving work efficiency.

[0007] The technical objective of this invention is achieved as follows: a cloud-native distributed database load index risk statement identification system, the system comprising,

[0008] The health analysis rule engine unit is used to build a rule engine, considering rules from multiple dimensions and establishing mathematical statistical models based on the data distribution of multi-dimensional features of application load profiles (including logical plans and execution plans) to form parameter-based rules. Each rule has corresponding parameters, which are empirical values ​​by default and can be adjusted by the user. By adjusting the analysis parameters, different analysis sensitivities can be set to identify SQL statements that may have missing indexes and SQL statements whose indexes have room for optimization. Root cause analysis is performed on SQL statements with index risks, and the target SQL statements are accurately located for subsequent index optimization. The data distribution based on the multi-dimensional features of application load profiles includes the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information.

[0009] The machine learning analysis unit is used to form training sets on different data based on application load profiles and different rules. It uses unsupervised learning to identify outliers and trains the system to identify statements with indexing risks based on the judgment criteria.

[0010] Preferably, the health analysis rule engine unit includes,

[0011] Rule Engine Module 1 is used to perform index risk rule checks when the table scan method is a full table scan;

[0012] Rule Engine Module 2 is used to perform index risk rule checks when a statement uses an index.

[0013] Preferably, the machine learning analysis unit employs an unsupervised machine learning method to identify abnormal physical plans based on the statement physical plan execution information, index usage, field access methods, and predicate information related to the index's dimensional features, thereby identifying SQL statements with index risks.

[0014] More preferably, the working process of the rule engine module one is as follows:

[0015] When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold:

[0016] If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement.

[0017] The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

[0018] More preferably, the working process of the second rule engine module is as follows:

[0019] (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions.

[0020] (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix.

[0021] (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan and the predicate usage type in the AccessPattern is not EQUAL.

[0022] (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster. Compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate:

[0023] ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement;

[0024] ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

[0025] Preferably, the rule engine unit also includes a parameter setting module, which is used by users to customize the analysis parameters on the front end.

[0026] A method for identifying risky statements in a cloud-native distributed database load balancing index, which analyzes data in the distributed database to generate application-oriented index analysis, as detailed below:

[0027] The health analysis rule engine unit and machine learning engine unit based on application load SQL statements are extended to establish index risk rules and machine learning models. By analyzing the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information, SQL statements that may have missing indexes and SQL statements with index optimization potential are identified. Root cause analysis is performed on SQL statements with index risks to accurately locate target SQL statements for subsequent index optimization.

[0028] Preferably, the health analysis rule engine unit includes rule engine module one and rule engine module two;

[0029] The rule engine module 1 is used to perform index risk rule checks when the table scan method is a full table scan;

[0030] The second rule engine module is used to perform index risk rule checks when a statement uses an index.

[0031] More preferably, the working process of the rule engine module one is as follows:

[0032] When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold:

[0033] If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement.

[0034] The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

[0035] More preferably, the working process of the second rule engine module is as follows:

[0036] (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions.

[0037] (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix.

[0038] (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan and the predicate usage type in the AccessPattern is not EQUAL.

[0039] (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster. Compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate:

[0040] ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement;

[0041] ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

[0042] The cloud-native distributed database load index risk statement identification system and method of the present invention have the following advantages:

[0043] (i) This invention utilizes application-oriented databases to establish an unhealthy statement rule engine, including index risks, through data analysis, and provides diagnostic information. On the one hand, the conclusions of this invention are more accurate than traditional load monitoring methods, down to index problems. On the other hand, this invention reduces the knowledge and ability requirements for database administrators, greatly simplifies the difficulty for database administrators and users to maintain and optimize indexes, improves work efficiency, and reduces labor costs.

[0044] (ii) The distributed database index risk statement identification system of the present invention diagnoses risk statements under application load. By comparing the logical plan and physical plan of the statement, it analyzes the usage and efficiency of the index, as well as the usage of predicates on related fields, in order to identify risk statements with index problems.

[0045] (III) This invention, through preliminary evaluation, summarizes key indicators related to indexing issues, and establishes a rule engine and machine learning engine based on its data statistical model. Compared with traditional diagnostic methods, it starts from root cause analysis, which makes the diagnosis of database load statements more accurate and eliminates interfering factors. The conclusions are intuitive and reasonable. By reducing and avoiding unnecessary index analysis of SQL, it effectively reduces the resource consumption recommended by index analysis, shortens the analysis time, and improves the analysis performance.

[0046] (iv) This invention also considers more future use scenarios and different business logics, and has strong scalability. The rule engine can be optimized by adding rules, or the machine learning engine can be optimized by adding attributes, so as to support more accurate and faster risk root cause location.

[0047] (v) By establishing a rule engine, this invention increases the effective application load information provided by the data by about 20% compared to traditional diagnostic methods; and due to the fine-grained rules, the degree of risk misjudgment will be greatly reduced; database administrators can understand index-related issues based on diagnostic information and optimize them.

[0048] (vi) By analyzing application statements, this invention effectively reduces the possibility of potential database problems and lowers the requirements for database administrators, thus meeting the requirements for production applications. This invention is an innovation in distributed database load autonomous optimization, which can reduce the labor costs of enterprises and improve work efficiency. Attached Figure Description

[0049] The invention will be further described below with reference to the accompanying drawings.

[0050] Appendix Figure 1 This is a schematic diagram of the structure of a cloud-native distributed database load index risk statement identification system. Detailed Implementation

[0051] The cloud-native distributed database load index risk statement identification system and method of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

[0052] Example 1:

[0053] As attached Figure 1 As shown, this embodiment provides a cloud-native distributed database load index risk statement identification system, which includes,

[0054] The health analysis rule engine unit is used to build a rule engine, considering rules from multiple dimensions and establishing mathematical statistical models based on the data distribution of multi-dimensional features of application load profiles (including logical plans and execution plans) to form parameter-based rules. Each rule has corresponding parameters, which are empirical values ​​by default and can be adjusted by the user. By adjusting the analysis parameters, different analysis sensitivities can be set to identify SQL statements that may have missing indexes and SQL statements whose indexes have room for optimization. Root cause analysis is performed on SQL statements with index risks, and the target SQL statements are accurately located for subsequent index optimization. The data distribution based on the multi-dimensional features of application load profiles includes the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information.

[0055] The machine learning analysis unit is used to form training sets on different data based on application load profiles and different rules. It uses unsupervised learning to identify outliers and trains the system to identify statements with indexing risks based on the judgment criteria.

[0056] The health analysis rule engine unit in this embodiment includes,

[0057] Rule Engine Module 1 is used to perform index risk rule checks when the table scan method is a full table scan;

[0058] Rule Engine Module 2 is used to perform index risk rule checks when a statement uses an index.

[0059] In this embodiment, the machine learning analysis unit uses an unsupervised machine learning method to identify abnormal physical plans based on the statement physical plan execution information, index usage, field access methods, and predicate information related to the index's dimensional features, thereby identifying SQL statements with index risks.

[0060] The working process of the rule engine module one in this embodiment is as follows:

[0061] When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold:

[0062] If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement.

[0063] The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

[0064] The specific working process of the rule engine module two in this embodiment is as follows:

[0065] (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions.

[0066] (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix.

[0067] (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan and the predicate usage type in the AccessPattern is not EQUAL.

[0068] (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster. Compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate:

[0069] ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement;

[0070] ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

[0071] The rule engine unit in this embodiment also includes a parameter setting module, which is used by users to customize analysis parameters on the front end.

[0072] Example 2:

[0073] This embodiment provides a method for identifying risky statements in a cloud-native distributed database load balancing index. This method analyzes data in the distributed database to form application-oriented index analysis, as detailed below:

[0074] The health analysis rule engine unit and machine learning engine unit based on application load SQL statements are extended to establish index risk rules and machine learning models. By analyzing the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information, SQL statements that may have missing indexes and SQL statements with index optimization potential are identified. Root cause analysis is performed on SQL statements with index risks to accurately locate target SQL statements for subsequent index optimization.

[0075] The health analysis rule engine unit in this embodiment includes rule engine module one and rule engine module two;

[0076] The rule engine module 1 is used to perform index risk rule checks when the table scan method is a full table scan;

[0077] The second rule engine module is used to perform index risk rule checks when a statement uses an index.

[0078] The working process of the rule engine module one in this embodiment is as follows:

[0079] When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold:

[0080] If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement.

[0081] The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

[0082] The specific working process of the rule engine module two in this embodiment is as follows:

[0083] (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions.

[0084] (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix.

[0085] (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan and the predicate usage type in the AccessPattern is not EQUAL.

[0086] (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster. Compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate:

[0087] ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement;

[0088] ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

[0089] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A cloud-native distributed database load index risk statement identification system, characterized in that, The system includes, The health analysis rule engine unit is used to build the rule engine. It considers rules from multiple dimensions and establishes mathematical statistical models based on the data distribution of multi-dimensional features of application load profiles to form parameter-based rules. Each rule has corresponding parameters, which are empirical values ​​by default and can be adjusted by the user. By adjusting the analysis parameters, different analysis sensitivities can be set to identify SQL statements that may have missing indexes and SQL statements whose indexes have room for optimization. Root cause analysis is performed on SQL statements with index risks, and the target SQL statements are accurately located for subsequent index optimization. The data distribution based on the multi-dimensional features of application load profiles includes the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information. The machine learning analysis unit is used to form training sets on different data according to different rules based on application load profiles. It uses unsupervised learning to identify outliers and trains the system to identify statements with indexing risks based on the judgment criteria.

2. The cloud-native distributed database load index risk statement identification system according to claim 1, characterized in that, The health analysis rule engine unit includes: Rule Engine Module 1 is used to perform index risk rule checks when the table scan method is a full table scan; Rule Engine Module 2 is used to perform index risk rule checks when a statement uses an index.

3. The cloud-native distributed database load index risk statement identification system according to claim 1, characterized in that, The machine learning analysis unit uses an unsupervised machine learning method to identify abnormal physical plans based on the physical plan execution information, index usage, field access methods, and predicate information related to the index, thereby identifying SQL statements with index risks.

4. The cloud-native distributed database load index risk statement identification system according to claim 2, characterized in that, The specific working process of the rule engine module one is as follows: When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold: If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement. The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

5. The cloud-native distributed database load index risk statement identification system according to claim 2, characterized in that, The specific working process of the rule engine module two is as follows: (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions; (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix. (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan is not of the type EQUAL and the predicate used in the AccessPattern is not EQUAL. (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster, compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate: ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement; ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

6. The cloud-native distributed database load index risk statement identification system according to claim 1, characterized in that, The rule engine unit also includes a parameter setting module, which allows users to customize analysis parameters on the front end.

7. A method for identifying risky statements in a cloud-native distributed database load index, characterized in that, This method involves analyzing data in a distributed database to create application-oriented indexes, as detailed below: The health analysis rule engine unit and machine learning analysis unit based on application load SQL statements are extended to establish index risk rules and machine learning models. By analyzing the actual filtering rate of physical plan operators, index usage, and predicate filtering rate information, SQL statements that may have missing indexes and SQL statements with index optimization potential are identified. Root cause analysis is performed on SQL statements with index risks to accurately locate target SQL statements for subsequent index optimization. The machine learning analysis unit is used to form training sets on different data correspondences based on application load profiles and different rules. It uses unsupervised learning to identify outliers and trains the statements with indexing risks under the judgment criteria. The machine learning analysis unit uses unsupervised machine learning methods to identify abnormal physical plans based on the physical plan execution information, index usage, field access methods, and predicate information related to the index, thereby identifying SQL statements with index risks.

8. The cloud-native distributed database load index risk statement identification method according to claim 7, characterized in that, The health analysis rule engine unit includes rule engine module one and rule engine module two; The rule engine module 1 is used to perform index risk rule checks when the table scan method is a full table scan; The second rule engine module is used to perform index risk rule checks when a statement uses an index.

9. The cloud-native distributed database load index risk statement identification method according to claim 8, characterized in that, The specific working process of the rule engine module one is as follows: When the table is scanned as a full table scan, compare the actual filtering rate of the operator with the preset filtering rate threshold: If the actual filtering rate of the operator is less than the preset filtering rate threshold, it means that the entire table has been scanned. Without using an index, the number of rows that meet the conditions is less than the set value. That is, the predicate filtering rate is good, but there is no usable index on the predicate. Therefore, the statement may be at risk of missing indexes, and there is a possibility of creating an index to optimize the scan performance of this statement. The actual filtering rate of an operator refers to the ratio of the number of rows actually returned by the operator to the number of rows actually input.

10. The cloud-native distributed database load index risk statement identification method according to claim 8, characterized in that, The specific working process of the rule engine module two is as follows: (1) When the statement uses an index and the operator filtering rate is greater than the threshold, check whether the index is not used for filtering due to field order or predicate restrictions; (2) In the Open database, an index containing a predicate field can only be used for filtering when the usage type of the predicate field is LOCAL EQUAL or JOIN EQUAL, and the field must hit the index prefix. (3) Based on the premises of (1) and (2), this rule will check the SQL statements in which the index name used in the LogicalPlan is not of the type EQUAL and the predicate used in the AccessPattern is not EQUAL. (4) Obtain the field information of the index used and the Distinct Count of the field column from the monitored cluster, compare the obtained fields with the fields in the access method, check the usage type, record the position of the field whose usage type is not EQUAL in the index, and determine whether there is an EQUAL class after the non-EQUAL class predicate: ① If an EQUAL class predicate is followed by a non-EQUAL class predicate, then there is room for optimization in the order of the indexed fields in this statement; ② If there is no EQUAL predicate after a non-EQUAL predicate, sort them in descending order according to (predicate usage type, DistinctCount); where the predicate usage type is sorted as LOCAL EQUAL > JOIN EQUAL > OTHERS; if the final sorting result is inconsistent with the index field sorting, there is room for optimization of the index field order.

Images