A self-adaptive construction method for database index

Abstract

The present invention discloses a method for adaptively constructing a database index, comprising the following steps: S1. extracting a query information set based on a database log; S2. obtaining a high-frequency query statement based on a TF through the information set; S3. obtaining the importance values ​​of all fields in the high-frequency query statement; S4. constructing a database index according to the importance values ​​of the fields; and S5. periodically obtaining the importance values ​​of the database log constructed in S4, and dynamically adjusting the database index to improve the query efficiency and adaptability of the database system.

Description

Technical Field

[0001] The invention belongs to the field of database indexes, and in particular relates to a method for adaptively constructing a database index. Background Art

[0002] With the rapid development of information technology, database systems are facing performance bottlenecks when processing large amounts of data. Indexing is one of the key technologies to improve database query efficiency, but traditional index building methods often require manual intervention and are difficult to adapt to dynamic changes in data access patterns. In actual applications, database queries may change due to changes in user behavior and business logic, which may cause existing indexes to no longer be optimal, thus affecting query performance.

[0003] In order to solve the above problems, a method is needed to automatically adjust the index structure according to the database query log to improve the query efficiency and adaptability of the database system. Summary of the invention

[0004] In order to solve the deficiencies in the prior art, the present invention proposes a method for adaptively constructing a database index, which can dynamically adjust the database index, thereby improving the query efficiency and adaptability of the database.

[0005] In order to achieve the above object, the technical solution adopted by the present invention is a method for adaptively constructing a database index, comprising the following steps:

[0006] S1. Extract query information set based on database log;

[0007] S2. Through information collection based on TF-IDF Get high-frequency query statements;

[0008] S3. Obtain the importance values ​​of all fields in the high-frequency query statements;

[0009] S4. Build a database index based on the importance value of the field;

[0010] S5. Periodically obtain the importance value of the database log constructed in S4 and dynamically adjust the database index.

[0011] Preferably, in step S1:

[0012] Extract the SQL statements, query fields, query execution time, and information about the operations involved in the query from the database log L :

[0013] ,…, ;

[0014] in Indicates query statements, each query statement It can be expressed as:

[0015] ;

[0016] in, A collection of fields involved in the query; is the query execution time; The operations involved in the query.

[0017] Preferably, in step S2:

[0018] S21. For each query statement , calculate the frequency of occurrence in all query logs :

[0019] ;

[0020] in Yes Query The number of occurrences, is the sum of all query occurrences;

[0021] S22. Calculate the inverse document frequency of each query in the entire query log collection :

[0022] ;

[0023] in is the total number of query logs, Contains query The number of logs;

[0024] S23. Combine TF and IDF to get the TF-IDF value:

[0025] ;

[0026] S24. TF-IDF If the value is greater than the high-frequency threshold, it is a high-frequency query statement.

[0027] Preferably, in step S3:

[0028] S31. For each field , define the query frequency function :

[0029] ;

[0030] in, is the time period for evaluation; if t , If queried, , otherwise 0;

[0031] S32. For each field , define the query cost function :

[0032] ;

[0033] in: Indicates the number of records that need to be scanned during the query process; Indicates the selectivity of the field; Indicates the complexity of the field in the query; Indicates data type and size; , , , is the weight factor;

[0034] S33. Calculate the importance value based on the query rating and query cost :

[0035] ,

[0036] in: Is Field The query frequency, Is Field The cost in the query, and It is a weight that balances query frequency and query cost.

[0037] Preferably, in step S4:

[0038] When the importance value of a field is higher than a preset threshold, a field index is created.

[0039] Preferably, in step S5:

[0040] S51. When the importance value of the field is higher than the importance threshold and no index is created, an index is created;

[0041] S52. When the importance value of the field is higher than the importance threshold and the index has been established, the index is retained;

[0042] S53. When the importance value of the field is equal to or lower than the importance threshold and the index has been created, delete the index.

[0043] Preferably, in step S51:

[0044] Obtain the weighted importance value of the fields in the statement through the smoothing model :

[0045] ;

[0046] in is the smoothing coefficient, is the field importance value calculated in the current cycle; is the field importance value of the previous period.

[0047] Compared with the prior art, the advantages of this application are as follows:

[0048] Using the importance value of the field in the present invention to establish an index can comprehensively consider the frequency of field queries and the impact of query costs on the index, thereby improving the representativeness of the index and enhancing the query performance of the database;

[0049] Find high-frequency query statements from all query statements as pre-screening to ensure that the fields to be indexed come from high-frequency query statements, narrow the screening scope of index requirement fields, and improve the accuracy of screening;

[0050] By periodically observing the importance value of the database log and dynamically updating the database index, the adaptability of the database index can be improved; BRIEF DESCRIPTION OF THE DRAWINGS

[0051] Figure 1 This is a flow chart of the database index adaptive construction method of the present invention. DETAILED DESCRIPTION

[0052] The following detailed description is illustrative and is intended to provide further explanation of the present application. Unless otherwise specified, all technical and scientific terms used herein have the same meanings as those commonly understood by those of ordinary skill in the art to which the present application belongs. It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit the exemplary embodiments according to the present application.

[0053] Embodiment 1:

[0054] In order to improve the query efficiency and adaptability of the database, this embodiment provides a method for adaptively constructing a database index, and its flow chart is as follows: Figure 1 As shown, the method specifically comprises the following steps:

[0055] S1. Extract query information set based on database log;

[0056] Use the logging function provided by the database, such as MySQL's slow query log and PostgreSQL's pg_stat_statements, to configure the database to record all query requests and execution status.

[0057] Extract and return a collection of information about SQL statements, query fields, query execution time, and operations involved in the query from the database log L :

[0058] ,…, ;

[0059] in Indicates query statements, each query statement It can be expressed as:

[0060] ;

[0061] in, A collection of fields involved in the query; is the query execution time; The operations involved in the query (such as SELECT, UPDATE, etc.).

[0062] In order to accurately find out which fields are high-frequency fields, we must first find high-frequency query statements from all query statements as pre-screening. This ensures that the fields to be indexed come from high-frequency query statements, narrows the screening range of index requirement fields, and improves the accuracy of screening. For example, S2 uses the TF-IDF model to screen out high-frequency query statements.

[0063] S2. Through information collection based on TF-IDF Get high-frequency query statements;

[0064] S21. For each query statement , calculate the frequency of occurrence in all query logs :

[0065] ;

[0066] in Yes Query The number of occurrences, is the sum of all query occurrences;

[0067] S22. Calculate the inverse document frequency of each query in the entire query log collection :

[0068] in is the total number of query logs, Contains query The number of logs;

[0069] S23. Combine TF and IDF to get the TF-IDF value:

[0070] ;

[0071] S24. TF-IDF If the value is greater than the high-frequency threshold (set manually based on experience), it is a high-frequency query statement.

[0072] For the high-frequency query statements screened out in the previous step, use an SQL parsing tool (such as JSqlParser) to parse them. This method can extract all the fields that appear in the query statements and calculate the query frequency and importance of the fields.

[0073] S3. Obtain the importance values ​​of all fields in the high-frequency query statements;

[0074] S31. For each field , define the query frequency function :

[0075] ;

[0076] in, is the time period for evaluation (such as a day, a week, or a month); if t , Query If queried, , otherwise 0;

[0077] S32. For each field , define the query cost function :

[0078] ;

[0079] in: Indicates the number of records that need to be scanned during the query process; Indicates the selectivity of the field; Indicates the complexity of the field in the query, such as whether it participates in operations such as association, sorting, and grouping. You can set the enumeration value to participate in the calculation; Represents data type and size. Usually large data types result in higher reading and processing costs. , , , These weights can be adjusted through historical query data or experiments to better reflect the actual situation of the system;

[0080] S33. Calculate the importance value based on the query rating and query cost ,

[0081] ;

[0082] in: Is Field The query frequency, Is Field The cost in the query, and It is a weight that balances query frequency and query cost.

[0083] S4. Build a database index based on the importance value of the field;

[0084] According to the field importance value calculated by S3, when the value is higher than the preset threshold, there are three possibilities:

[0085] 1. The query frequency of the field is high, indicating that the field is often used for database queries

[0086] 2. The query cost of the field is high, indicating that the field is often used for sorting, grouping, and association operations

[0087] 3. The query frequency and query cost of the field are high, indicating that the field is often used for database queries and is often used for sorting, grouping, and association operations.

[0088] According to the principles of index establishment:

[0089] 1. Consider creating indexes for fields that are frequently used as query conditions

[0090] 2. The primary and foreign keys of the table or the linked table fields must be indexed, because this can greatly improve the performance of linked table queries.

[0091] 3. Fields that are often taken, sorted, and grouped based on ranges should be indexed because indexes are ordered and can speed up sorting time.

[0092] Therefore, when the importance value of a field is higher than a preset threshold, it meets the principle of creating an index for the field and needs to be indexed.

[0093] S5. In order to cope with the ever-changing queries, a feedback mechanism is established to update the index through periodic monitoring. Therefore, the database index is dynamically adjusted according to the importance value of the database constructed in S4 periodically. Specifically, it includes:

[0094] S51. When the importance value of a field is higher than the importance threshold (set manually based on experience) and the field is not indexed, an index is created for the field;

[0095] S52. When the importance value of a field is higher than the importance threshold and the field has been indexed, the field index is retained;

[0096] S53. When the importance value of a field is equal to or lower than the importance threshold and the field has been indexed, the field index is deleted.

[0097] Preferably, in order to ensure that the system responds quickly to recent query changes (such as a sudden increase in the query frequency of a field during peak hours), and to prevent short-term fluctuations when a large number of queries occur within a certain period of time, the weighted importance value of the field in the statement can be obtained through a smoothing model. To dynamically adjust the database index, the calculation method is:

[0098] ;

[0099] in is the smoothing coefficient, is the field importance value calculated in the current cycle; is the field importance value of the previous period.

[0100] When a large number of queries occur within a certain period of time, the exponential smoothing model will gradually attenuate their impact according to the smoothing coefficient, thereby preventing excessive creation or deletion of indexes due to short-term fluctuations (such as some occasional high-frequency queries), thereby balancing the query pressure and storage pressure of the database system.

[0101] The above description is only the preferred embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A method for adaptively constructing a database index, characterized in that: The following steps are involved: S1. Extract query information set based on database log; S2. Through information collection based on TF-IDF Get high-frequency query statements; S3. Obtain the importance values ​​of all fields in the high-frequency query statements; S4. Build a database index based on the importance value of the field; S5. Periodically obtain the importance value of the database log constructed in S4, dynamically adjust the database index, and the specific method of periodically obtaining the importance value is: obtain the weighted importance value of the field in the statement through the smoothing model : , in is the smoothing coefficient, is the field importance value calculated in the current cycle; is the field importance value of the previous period.

2. The method for adaptively constructing a database index according to claim 1, characterized in that: In step S1: Extract the SQL statements, query fields, query execution time, and information about the operations involved in the query from the database log L : ,…, ， in Indicates The query statement can be expressed as: ， in, A collection of fields involved in the query; is the query execution time; The operations involved in the query.

3. The method for adaptively constructing a database index according to claim 1, characterized in that: In step S2: S21. For each query statement , calculate the frequency of occurrence in all query logs : ， in Yes Query The number of occurrences, is the sum of all query occurrences; S22. Calculate the inverse document frequency of each query in the entire query log collection : ， in is the total number of query logs, Contains query The number of logs; S23. Combine TF and IDF to get the TF-IDF value: ， S24. If TF-IDF If the value is greater than the high-frequency threshold, it is a high-frequency query statement.

4. The method for adaptively constructing a database index according to claim 1, characterized in that: In step S3: S31. For each field , define the query frequency function : ， in, is the time period for evaluation; if , If queried, , otherwise 0; S32. For each field , define the query cost function : ， in: Indicates the number of records that need to be scanned during the query process; Indicates the selectivity of the field; Indicates the complexity of the field in the query; Indicates data type and size; , , , is the weight factor; S33. Calculate the importance value based on the query rating and query cost : ， in: Is Field The query frequency, Is Field The cost in the query, and It is a weight that balances query frequency and query cost.

5. The method for adaptively constructing a database index according to claim 1, characterized in that: In step S4: When the importance value of a field is higher than a preset threshold, a field index is created.

6. The method for adaptively constructing a database index according to claim 1, characterized in that: In step S5: S51. When the importance value of the field is higher than the importance threshold and no index is created, an index is created; S52. When the importance value of the field is higher than the importance threshold and the index has been established, the index is retained; S53. When the importance value of the field is equal to or lower than the importance threshold and the index has been created, delete the index.

Images