A lightweight database statistical information recommendation method

Abstract

This invention discloses a lightweight database statistics recommendation method, relating to the field of database technology. Addressing the shortcomings of existing database statistics collection strategies, the proposed solution includes: automatically creating the `sql_statistics` and `statistics_used` tables; loading the data from the `statistics_used` table into memory to form a statistics usage list `MapList M`; recording historical query statements in real-time to the `sql_statistics` table; collecting basic statistics, periodically determining whether the cold start phase has ended, and if so, periodically querying the system load; if the load does not exceed a preset threshold, executing corresponding processing logic to generate a temporary `MapList H`; filtering columns from the temporary `MapList H` to form a set `N`; creating statistics for all columns in set `N`, removing statistics from `MapList M` as needed, and then migrating the column names and corresponding frequencies from `N` from the temporary `MapList H` to `MapList M`, completing the update of `MapList M`, and synchronizing it to the `statistics_used` table; when the database triggers a schema change that involves statistics column names, modifying the corresponding column names in the `statistics_used` table and synchronously updating `MapList M`.

Description

Technical Field

[0001] This invention relates to the field of database technology, specifically a lightweight database statistical information recommendation method. Background Technology

[0002] Statistical information is the core foundation for cost-based query optimization in database systems. Its accuracy and timeliness directly determine whether the query optimizer can select the optimal query plan. If the statistical information is missing or outdated, the optimizer is very likely to make incorrect decisions, leading to a significant drop in system query performance.

[0003] However, the collection and updating of statistical information requires significant system resources such as CPU, memory, and I / O, making it impossible to exhaustively collect all columns and column combinations in the database. Taking KaiwuDB as an example, its current strategy is to collect statistical information from all indexed columns and the top 100 non-indexed columns. This strategy does not adaptively adjust to the actual user workload. This static collection strategy may cause two problems: firstly, columns frequently used as filtering conditions in actual queries may lack statistical information because they are not included in the collection scope, making it impossible for the optimizer to accurately assess query costs; secondly, some rarely accessed columns consume storage resources for statistical information and incur system overhead during updates, resulting in resource waste and weakening the optimization effect on query performance.

[0004] To address the aforementioned pain points, there is an urgent need for a lightweight method that can dynamically recommend high-value statistical information columns based on the user's actual query load, thereby reducing system resource consumption while providing key statistical information support for the query optimizer. Summary of the Invention

[0005] This invention addresses the problem that existing database statistical information collection strategies cannot adapt to the mismatch between users' actual workload, resource consumption, and the value of statistical information, and provides a lightweight database statistical information recommendation method.

[0006] The present invention provides a lightweight database statistical information recommendation method, and the technical solution adopted to solve the above-mentioned technical problems is as follows: A lightweight database statistical information recommendation method includes the following six stages: (A) Database installation and deployment phase: Automatically create the sql_statistics table to store historical DML SELECT query statements and the statistics_used table to store the frequency of use of statistical information; (B) Database startup phase: Load the data of the statistics_used table into memory to form a statistical information usage list MapList M. MapList M is a key-value pair structure, where the key is the column name and the value is the usage frequency of the corresponding column. (C) Database runtime phase: All executed DML select query statements are recorded in real time to the sql_statistics table to ensure complete collection of query load data; (D) Statistical information cold start phase: This is the initial operation phase of the database, used to complete the collection of basic statistical information. The timed task T determines whether the cold start phase has ended. If it has, it proceeds to the next phase; otherwise, it maintains the cold start mode. (E) Lightweight Statistical Information Recommendation Stage: The system load is queried through a scheduled task T. If the load exceeds a preset threshold, the system is determined to be in a busy state and the task is terminated. If the load does not exceed the preset threshold, the corresponding processing logic is executed according to whether this stage is being entered for the first time, and a temporary MapList H is generated. Then, columns with frequencies greater than a set frequency threshold are selected from the temporary MapList H to form a set N of high-frequency no-statistics filtering columns. Statistical information is created for all columns in set N. Then, the statistical information with the lowest frequency of use in MapList M is removed as needed. The column names and corresponding frequencies in set N are then migrated from the temporary MapList H to MapList M to complete the update of MapList M and synchronized to the statistics_used table. (F) Database Schema Change Processing Phase: When a database triggers a schema change and the change involves the column names of statistics information, modify the corresponding column names in the statistics_used table and update MapList M synchronously.

[0007] Optionally, both the sql_statistics and statistics_used tables involved are disk tables; Each row in the sql_statistics table contains two fields: a timestamp and a query SQL statement. The timestamp indicates when the query occurred, and the query SQL statement is used to extract and filter columns later. Each row in the statistics_used table contains two fields: column name and frequency of corresponding statistics. The column name is in the format "table name; column name", and the frequency of statistics indicates how many times the column is used as a filter column in the query.

[0008] Optionally, during the cold start phase of statistical information collection, the database's heuristic statistical information collection strategy is adopted to collect statistical information on all indexed columns and the first 100 non-indexed columns, ensuring that the database has basic statistical information support during initial operation.

[0009] Optionally, during the cold start phase of statistics, a scheduled task T is used to determine whether the cold start phase has ended. The specific operation is as follows: by executing the show statistics statement, the number of non-indexed columns of the currently collected statistics is queried. (i) If the number exceeds the system threshold, the cold start phase is determined to be over and the next phase is entered. (ii) If the number does not exceed the system threshold, the cold start mode is maintained.

[0010] Optionally, when performing operation (E) for the first time entering the lightweight statistical information recommendation stage, the following processing logic is executed: (a1) Execute the show statistics statement to query all indexed and non-indexed columns for which statistics have been collected, insert all these column names into MapList M, and set their initial frequency to 0; (a2) Query all historical DML SELECT query statements stored in the sql_statistics table, and extract the filter columns from each query statement using SQL parsing technology; (a3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; if the column does not exist in MapList M, insert it into temporary MapList H and increment its corresponding frequency by 1.

[0011] Alternatively, when performing operation (E) before entering the lightweight statistical information recommendation stage for the first time, the following processing logic is executed: (b1) Determine the time interval between the end time of the last recommendation phase and the start time of the current recommendation phase, and query the historical DML SELECT statements added to the sql_statistics table within this time interval; (b2) Extract the filter columns from each newly added query statement using SQL parsing technology; (b3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; if the column does not exist in MapList M, insert it into temporary MapList H and increment its corresponding frequency by 1.

[0012] Preferably, the temporary MapList H and MapList M use the same memory cache structure, and their key-value pair format is: the key corresponds to the column name, and the value corresponds to the statistical information of the column.

[0013] Further optionally, perform operation (E) to create statistics for all columns of set N, then remove the least frequently used statistics from MapList M as needed, and then migrate the column names and corresponding frequencies in set N from temporary MapList H to MapList M, completing the update of MapList M and synchronizing it to the statistics_used table. This process specifically includes the following operations: (c1) Call the CREATE Statistics interface provided by the database system to create statistics for all columns in set N; (c2) If the total number of statistical information columns in MapList M and set N exceeds x% of the system default parameter, the statistical information with the least frequency of use in MapList M will be removed from the system until the number of statistical information columns meets the system requirements. (c3) Move the column names and their corresponding frequencies in set N from temporary MapList H to MapList M, and complete the update of MapList M; (c4) Synchronize all data in the updated MapList M to the statistics_used table to ensure that the memory cache and disk table data are consistent.

[0014] Optionally, during the database schema change processing stage, when the database triggers a schema change and the change involves the column names of statistics information, the column names of the corresponding columns in the statistics_used table are automatically modified first, and then the synchronous update of MapList M in memory is triggered to modify the corresponding keys to the new column names, ensuring the consistency of column names between the memory cache and the disk table.

[0015] The lightweight database statistical information recommendation method of the present invention has the following advantages compared with the prior art: 1. This invention accurately identifies frequently used filter columns by dynamically tracking the filter columns in users' historical query statements. Combined with system load conditions, it lightweightly completes the recommendation and creation of statistical information without affecting the normal operation of the database. This enables adaptive adjustment of the statistical information collection strategy, prioritizing the creation of statistical information for frequently used filter columns to enhance the actual support value of statistical information for query optimization. At the same time, it ensures that statistical information-related data can be updated synchronously when the database schema changes, guaranteeing the consistency of statistical information management. Ultimately, while reducing system resource consumption, it provides key statistical information support for the query optimizer, improves database query performance, and solves the problem that existing database statistical information collection strategies cannot adapt to the user's actual workload, resource consumption, and the mismatch between statistical information value. 2. This invention dynamically identifies frequently used columns and prioritizes their statistical information creation by tracking the filtering columns in users' historical query statements. This matches the statistical information collection strategy with the actual query load, enhancing the supporting value of statistical information for query optimization. It reduces disk I / O operations through memory caching and executes statistical information recommendation tasks when the system is idle, significantly reducing resource consumption for normal database operation. It avoids collecting statistical information for infrequently used columns, concentrating limited system resources on high-frequency filtering columns and reducing resource waste. The cold start phase ensures basic statistical information support during initial database operation, while synchronously updating statistical information-related data during schema changes, guaranteeing the stability and consistency of the method. Based on disk tables, a scheduled task mechanism, and a statistical information creation interface, it requires no large-scale modification to the database kernel, offering strong compatibility and facilitating widespread application in various database systems. Attached Figure Description

[0016] Appendix Figure 1 This is a diagram illustrating the implementation architecture of the method of this invention. Detailed Implementation

[0017] To make the technical solution, the technical problem solved, and the technical effect of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments.

[0018] Example 1: Refer to Appendix Figure 1 This embodiment proposes a lightweight database statistical information recommendation method, which includes the following six stages: (A) Database installation and deployment phase: Automatically create the sql_statistics table to store historical DML SELECT query statements and the statistics_used table to store the frequency of use of statistical information.

[0019] Both the `sql_statistics` and `statistics_used` tables are disk-based tables. Specifically: Each row in the sql_statistics table contains two fields: a timestamp and a query SQL statement. The timestamp indicates when the query occurred, and the query SQL statement is used to extract and filter columns later. Each row in the statistics_used table contains two fields: column name and frequency of corresponding statistics. The column name is in the format "table name; column name", and the frequency of statistics indicates how many times the column is used as a filter column in the query.

[0020] (B) Database startup phase: Load the data of the statistics_used table into memory to form a statistical information usage list MapList M. MapList M is a key-value pair structure, where the key is the column name and the value is the usage frequency of the corresponding column.

[0021] Memory caching allows for quick access to and updates of usage frequency statistics, reducing direct operations on disk tables and lowering system overhead.

[0022] (C) Database operation phase: All executed DML select query statements are recorded in real time to the sql_statistics table to ensure complete collection of query load data.

[0023] (D) Statistical Information Cold Start Phase: This is the initial running phase of the database, used to complete the collection of basic statistical information. A scheduled task T (e.g., once a day) determines whether the cold start phase has ended. If it has, the database enters the next phase; otherwise, it maintains the cold start mode.

[0024] During the cold start phase of statistical information collection, the database's heuristic statistical information collection strategy is adopted to collect statistical information on all indexed columns and the first 100 non-indexed columns, ensuring that the database has basic statistical information support during initial operation.

[0025] During the cold start phase of statistics, a scheduled task T (e.g., once a day) is used to determine whether the cold start phase has ended. The specific operation is as follows: the show statistics statement is executed to query the number of non-indexed columns of the currently collected statistics. (i) If the number exceeds the system threshold, the cold start phase is determined to be over and the next phase is started. (ii) If the number does not exceed the system threshold, the cold start mode is maintained.

[0026] (E) Lightweight Statistical Information Recommendation Stage: First, the system load is queried via a scheduled task T (e.g., by obtaining indicators such as CPU utilization and memory utilization through interfaces provided by the operating system). If the load exceeds a preset threshold (e.g., CPU utilization exceeds 70% or memory utilization exceeds 80%), the system is determined to be in a busy state and the task is terminated. If the load does not exceed the preset threshold, the corresponding processing logic is executed based on whether this is the first time entering this stage, and a temporary MapList H is generated. (i) When entering the lightweight statistical information recommendation stage for the first time, the following processing logic is executed: (a1) Execute the show statistics statement to query all indexed and non-indexed columns for which statistics have been collected, insert all these column names into MapList M, and set their initial frequency to 0; (a2) Query all historical DML SELECT query statements stored in the sql_statistics table, and extract the filter columns (i.e. the columns involved in the WHERE clause) in each query statement through SQL parsing technology (such as using the database's built-in SQL parser). (a3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; if the column does not exist in MapList M, insert it into temporary MapList H and increment its corresponding frequency by 1.

[0027] (ii) When entering the lightweight statistical information recommendation stage for the first time, the following processing logic is executed: (b1) Determine the time interval between the end time of the last recommendation phase and the start time of the current recommendation phase, and query the historical DML SELECT statements added to the sql_statistics table within this time interval; (b2) Extract the filter columns (i.e. the columns involved in the WHERE clause) in each new query statement using SQL parsing techniques (such as using the SQL parser built into the database). (b3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; if the column does not exist in MapList M, insert it into temporary MapList H and increment its corresponding frequency by 1.

[0028] It should be added that the temporary MapList H and MapList M use the same memory cache structure, and their key-value pair format is: the key corresponds to the column name, and the value corresponds to the statistical information of the column (specifically, frequency data).

[0029] Subsequently, the temporary MapList H is sorted in reverse order by frequency, and columns with frequencies greater than a set frequency threshold (which can be flexibly configured according to actual business scenarios, for example, set to 5 times) are selected to form a set N of high-frequency, unstatistically filtered columns.

[0030] Finally, the CREATE Statistics interface provided by the database system is called to create statistics for all columns in set N. If the total number of statistics columns in MapList M and set N exceeds x% of the system default parameter (x is a system parameter that can be manually adjusted by the user), the statistics with the lowest frequency of use in MapList M is removed from the system until the number of statistics columns meets the system requirements. The column names and their corresponding frequencies in set N are migrated from the temporary MapListH to MapList M, completing the update of MapList M. All data in the updated MapList M is synchronized to the statistics_used table to ensure consistency between the memory cache and the disk table data.

[0031] (F) Database Schema Change Processing Stage: After the above stages are completed, when the database triggers a schema change and the change involves the column names of statistics information, modify the corresponding column names in the statistics_used table and update MapList M synchronously.

[0032] During the database schema change processing phase, when the database triggers a schema change (such as an ALTER TABLE statement causing column name modification), and the change involves the column names of statistics information columns, the column names of the corresponding columns in the statistics_used table are automatically modified first, and then the synchronous update of MapList M in memory is triggered to modify the corresponding keys (column names) to the new column names, ensuring the consistency of column names between the memory cache and the disk table.

[0033] In summary, the lightweight database statistical information recommendation method of the present invention can be implemented based on disk tables, scheduled task mechanisms, and statistical information creation interfaces without requiring large-scale modifications to the database kernel. It has strong compatibility and is easy to promote and apply in various database systems (especially lightweight databases).

[0034] The above specific examples illustrate the principles and implementation methods of the present invention in detail. These embodiments are merely for the purpose of helping to understand the core technical content of the present invention. Based on the above specific embodiments of the present invention, any improvements and modifications made to the present invention by those skilled in the art without departing from the principles of the present invention should fall within the patent protection scope of the present invention.

Claims

1. A lightweight database statistical information recommendation method, characterized in that, It includes the following six stages: (A) Database installation and deployment phase: Automatically create the sql_statistics table to store historical DML SELECT query statements and the statistics_used table to store the frequency of use of statistical information; (B) Database startup phase: Load the data of the statistics_used table into memory to form a statistical information usage list MapList M. MapList M is a key-value pair structure, where the key is the column name and the value is the usage frequency of the corresponding column. (C) Database runtime phase: All executed DML select query statements are recorded in real time to the sql_statistics table to ensure complete collection of query load data; (D) Statistical information cold start phase: This is the initial operation phase of the database, used to complete the collection of basic statistical information. The timed task T determines whether the cold start phase has ended. If it has, it proceeds to the next phase; otherwise, it maintains the cold start mode. (E) Lightweight statistical information recommendation stage: The system load is queried through the scheduled task T. If the load exceeds the preset threshold, the system is determined to be in a busy state and the task is terminated. If the load does not exceed the preset threshold, the corresponding processing logic is executed according to whether it is the first time entering this stage, and a temporary MapList H is generated. Then, columns with a frequency greater than the set frequency threshold are filtered from the temporary MapList H to form a set N of high-frequency, unstatistically filtered columns. Statistics are created for all columns in set N. Then, the statistics with the lowest frequency of use in MapList M are removed as needed. The column names and corresponding frequencies in set N are then migrated from the temporary MapList H to MapList M to complete the update of MapList M and synchronized to the statistics_used table. (F) Database Schema Change Processing Phase: When a database triggers a schema change and the change involves the column names of statistics information, modify the corresponding column names in the statistics_used table and update MapList M synchronously.

2. The lightweight database statistical information recommendation method according to claim 1, characterized in that, Both the sql_statistics and statistics_used tables are disk-based tables; Each row in the sql_statistics table contains two fields: a timestamp and a query SQL statement. The timestamp indicates when the query occurred, and the query SQL statement is used to extract and filter columns later. Each row in the statistics_used table contains two fields: column name and frequency of corresponding statistics. The column name is in the format "table name; column name", and the frequency of statistics indicates how many times the column is used as a filter column in the query.

3. The lightweight database statistical information recommendation method according to claim 1, characterized in that, During the cold start phase of statistical information collection, the database's heuristic statistical information collection strategy is adopted to collect statistical information on all indexed columns and the first 100 non-indexed columns, ensuring that the database has basic statistical information support during initial operation.

4. The lightweight database statistical information recommendation method according to claim 1, characterized in that, During the cold start phase of statistics, a scheduled task T is used to determine whether the cold start phase has ended. The specific operation is as follows: the showstatistics statement is executed to query the number of non-indexed columns of the currently collected statistics. (i) If the number exceeds the system threshold, the cold start phase is determined to be over and the next phase is started. (ii) If the number does not exceed the system threshold, the cold start mode is maintained.

5. The lightweight database statistical information recommendation method according to claim 1, characterized in that, When performing operation (E) and entering the lightweight statistical information recommendation stage for the first time, the following processing logic is executed: (a1) Execute the show statistics statement to query all indexed and non-indexed columns for which statistics have been collected, insert all these column names into MapList M, and set their initial frequency to 0; (a2) Query all historical DML SELECT query statements stored in the sql_statistics table, and extract the filter columns from each query statement using SQL parsing technology; (a3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; if the column does not exist in MapList M, insert it into temporary MapList H and increment its corresponding frequency by 1.

6. The lightweight database statistical information recommendation method according to claim 5, characterized in that, When performing operation (E), if it is not the first time entering the lightweight statistical information recommendation stage, the following processing logic is executed: (b1) Determine the time interval between the end time of the last recommendation phase and the start time of the current recommendation phase, and query the historical DML SELECT statements added to the sql_statistics table within this time interval; (b2) Extract the filter columns from each newly added query statement using SQL parsing technology; (b3) For each extracted filter column, if the column exists in MapList M, increment its corresponding frequency by 1; If the column does not exist in MapList M, it is inserted into a temporary MapList H, and its corresponding frequency is incremented by 1.

7. A lightweight database statistical information recommendation method according to claim 6, characterized in that, Temporary MapList H and MapList M use the same memory cache structure, and their key-value pair format is: the key corresponds to the column name, and the value corresponds to the statistics of the column.

8. The lightweight database statistical information recommendation method according to claim 6, characterized in that, Perform operation (E) to create statistics for all columns of set N, then remove the least frequently used statistics from MapList M as needed, and then migrate the column names and their corresponding frequencies from temporary MapList H to MapList M to complete the update of MapList M and synchronize it to the statistics_used table. This process specifically includes the following operations: (c1) Call the CREATE Statistics interface provided by the database system to create statistics for all columns in set N; (c2) If the total number of statistical information columns in MapList M and set N exceeds x% of the system default parameter, the statistical information with the least frequency of use in MapList M will be removed from the system until the number of statistical information columns meets the system requirements. (c3) Move the column names and their corresponding frequencies in set N from temporary MapList H to MapList M, and complete the update of MapList M; (c4) Synchronize all data in the updated MapList M to the statistics_used table to ensure that the memory cache and disk table data are consistent.

9. The lightweight database statistical information recommendation method according to claim 1, characterized in that, During the database schema change processing phase, when a schema change is triggered in the database and the change involves the column names of statistics information, the column names of the corresponding columns in the statistics_used table are automatically modified first, and then the synchronous update of MapList M in memory is triggered to modify the corresponding keys to the new column names, ensuring the consistency of column names between the memory cache and the disk table.

Images