Database-based data index and query optimization system and method

Abstract

The invention discloses a database-based data indexing and query optimization system and method, belongs to the technical field of databases, and aims to solve the technical problem of how to improve the data indexing efficiency and query performance of a database system. According to the technical scheme, the system comprises a query optimization module, an index construction module and a data storage module; the data storage module is used for storing data records in a database; data records are stored in a column mode, the data compression rate and query performance storage are improved, and disk I / O operation is reduced; the index construction module is used for constructing an index according to the data record in the data storage module, the index adopts a B + tree design, and the number of I / O operations during data transmission between a disk and a memory is reduced; and the query optimization module is used for receiving a query request and optimizing a query path according to the index constructed by the index construction module.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a data indexing and query optimization system and method based on a database. Background Art

[0002] A database is a collection of data that is stored on a computer's storage device for a long time, organized according to certain rules, and can be shared by various users or applications. It is a centralized data repository, usually consisting of one or more data tables, each of which contains multiple rows and columns for storing specific types of data. Databases are mainly used to effectively organize, store, and manage large amounts of data, providing functions such as structured storage, query, modification, sorting, and statistics of data.

[0003] The database system is an ideal data processing system developed to meet the needs of data processing. It is also a software system that provides data for actual operable storage, maintenance and application systems. The database system is generally composed of four parts: database (DB), hardware, software (including operating system, database management system DBMS and application program) and personnel (including system analysts, database designers, application programmers, end users and database administrators DBA). Among them, the database management system (DBMS) is the core software of the database system.

[0004] In recent years, with the substantial increase in the amount of data in society, existing database systems often encounter problems such as low database indexing efficiency and long query response time when processing large-scale data, which limits the performance of the database system.

[0005] Therefore, how to improve the data indexing efficiency and query performance of the database system is a technical problem that needs to be solved urgently. Summary of the invention

[0006] The technical task of the present invention is to provide a data indexing and query optimization system and method based on a database to solve the problem of how to improve the data indexing efficiency and query performance of the database system.

[0007] The technical task of the present invention is achieved in the following manner: a data indexing and query optimization system based on a database, the system comprising a query optimization module, an index building module and a data storage module;

[0008] Among them, the data storage module is used to store data records in the database; data records are stored in columnar format to improve data compression rate and query performance storage, and reduce disk I / O operations;

[0009] The index building module is used to build indexes based on the data records in the data storage module. The index adopts B+ tree design to reduce the number of I / O operations when data is transferred between disk and memory;

[0010] The query optimization module is used to receive query requests and optimize the query path according to the index built by the index building module.

[0011] Preferably, the data storage module distributes data to multiple physical disks through RAID, and uses different disk blocks when reading and writing different data, thereby improving the overall performance of data reading and writing;

[0012] During actual reading and writing, the data storage module uses a buffer pool composed of an internal database to reduce physical read and write operations on the disk.

[0013] Preferably, the index building module adopts a dynamic index structure based on data access frequency and data distribution, and dynamically adjusts the size of the index node according to the data access frequency; wherein the adjustment of the index node size is based on a preset performance threshold;

[0014] The index building module uses a distribution-aware algorithm based on cluster analysis and data skew detection to analyze the distribution of data on the storage medium and optimize the index structure.

[0015] Preferably, the query optimization module includes a query plan generator and a cost evaluator;

[0016] Among them, the query plan generator is used to generate an index-based query execution plan;

[0017] The cost evaluator is used to evaluate the query time of different paths by means of a machine learning model (cost model).

[0018] Preferably, the index-based query execution plan includes a multi-stage query execution process and an intermediate result caching strategy;

[0019] The multi-stage query execution process includes data retrieval, data filtering, data projection, sorting, joining and aggregation operations; data retrieval is to retrieve data from the storage engine based on the index; data filtering uses the WHERE clause to filter data to reduce the amount of data for subsequent operations; data projection selects the required columns according to the SELECT clause;

[0020] The intermediate result cache strategy includes selection strategy, cache invalidation strategy, and cache replacement strategy; the selection strategy is based on the temporal locality and spatial locality of the intermediate results generated by the query execution process; the cache invalidation strategy is to specify cache invalidation by using time standards while setting data cache; the cache replacement strategy uses the LRU algorithm to remove data from the cache area with a usage frequency lower than the set number threshold within the set time range.

[0021] Preferably, the reference indicators input to the machine learning model include CPU processing time, number of I / O operations, data transmission volume, and data transmission mode; each factor is initially assigned a different weight;

[0022] Among them, the weight is assigned according to the degree of influence of each reference indicator on the query time, and is described by a linear relationship. The formula is y=ax1+bx2+cx3+dx4; among them, x1, x2, x3, x4 are the values ​​of CPU processing time, I / O operation number, data transmission volume, and transmission method respectively, and a, b, c, d are the weights of each reference indicator respectively.

[0023] Preferably, the query plan generator uses statistical information and machine learning algorithms to predict the distribution of query results, and can dynamically adjust the query plan according to the prediction results. The content of dynamically adjusting the query plan includes adjusting the parallelism and resource allocation of query execution; specifically as follows:

[0024] First, obtain the load resource information of the remaining number of CPU cores, remaining memory capacity, and remaining broadband capacity of the database system;

[0025] Then, collect query statistics including data access patterns, data volume, and index usage, and use the configuration parameters of maximum parallelism (max_parallel_degree) and work memory (work_mem) to control parallelism and memory usage;

[0026] At the same time, partitioning technology is used for data tables whose size exceeds the set threshold, and each query operation is executed in parallel on different partitions.

[0027] A data indexing and query optimization method based on a database, the method is specifically as follows:

[0028] S1. Receive a query request and parse the content of the query request; wherein the content of the query request includes query conditions, sorting requirements, and data aggregation operations;

[0029] S2. Use the index building module to build or update the index to adapt to the changes in the data records in the data storage module; at the same time, the index building module dynamically adjusts the index structure according to the changes in the data access mode, and the content of the dynamic adjustment of the index structure includes splitting, merging or reorganizing the index; the index adjustment strategy of the index building module is based on the data access frequency, data distribution and system load setting, specifically: according to the past query logs, identify the data that needs to be accessed frequently, optimize the relevant indexes, and adjust the relevant indexes in combination with the changes in the data distribution after the data is updated;

[0030] S3. Generate a query execution plan using a query optimization module. The query execution plan includes using a cost evaluator to evaluate and select an optimal query path and using a machine learning model to predict the query result distribution.

[0031] S4. Execute the query and return the result, and collect the statistical information of the query execution for subsequent optimization; the statistical information includes the query execution time, resource consumption and user satisfaction.

[0032] Preferably, building or updating the index includes incremental update and full reconstruction;

[0033] Among them, incremental update adopts the partial update method, that is, when the data change amount does not exceed the set threshold, only the existing index is updated to reflect the insertion, deletion and update of data records;

[0034] When the scale of data changes exceeds the set threshold, the index is rebuilt to optimize performance, that is, the index is rebuilt for all data and the corresponding mapping relationship is established.

[0035] Preferably, the working process of the query optimization module is as follows:

[0036] S301, the initial query plan and the solution space saved for the query operation in the past are sent to the query optimization module as external input;

[0037] S302, the query optimization module continuously optimizes based on the established machine learning model and generates a corresponding query plan;

[0038] S303. The query plan is directly used for the query operation of the database, and the time information of the query operation is used to feed back the machine learning model calculation, and this process is repeated until a set of optimal results is found.

[0039] The database-based data indexing and query optimization system and method of the present invention have the following advantages:

[0040] (1) The present invention improves the efficiency of data indexing and query performance through a data storage module, an index building module, and a query optimization module;

[0041] (ii) The present invention stores data records in a database through a data storage module, and the data records are stored in a columnar manner, thereby improving data compression rate and query performance storage, and reducing disk I / O operations;

[0042] (III) The present invention constructs an index based on data records, and the index adopts a B+ tree design to reduce the number of I / O operations when data is transmitted between the disk and the memory. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] The present invention is further described below in conjunction with the accompanying drawings.

[0044] Attached Figure 1 Store user topology diagram for database system;

[0045] Attached Figure 2 This is a structural diagram of a database-based data index and query optimization system;

[0046] Attached Figure 3 It is a flowchart of the working process of the query optimization module;

[0047] Attached Figure 4 The flowchart is a data index and query optimization method based on a database. DETAILED DESCRIPTION

[0048] The following is a detailed description of the database-based data indexing and query optimization system and method of the present invention with reference to the accompanying drawings and specific embodiments.

[0049] Embodiment 1:

[0050] As attached Figure 1 and 2 As shown, this embodiment provides a data index and query optimization system based on a database, the system includes a query optimization module, an index construction module and a data storage module;

[0051] Among them, the data storage module is used to store data records in the database; data records are stored in columnar format to improve data compression rate and query performance storage, and reduce disk I / O operations;

[0052] The index building module is used to build indexes based on the data records in the data storage module. The index adopts B+ tree design to reduce the number of I / O operations when data is transferred between disk and memory;

[0053] The query optimization module is used to receive query requests and optimize the query path according to the index built by the index building module.

[0054] The data storage module in this embodiment distributes data to multiple physical disks through RAID. When reading and writing different data, different disk blocks are used to improve the overall performance of data reading and writing.

[0055] During actual reading and writing, the data storage module uses a buffer pool composed of an internal database to reduce physical read and write operations on the disk.

[0056] The index building module in this embodiment adopts a dynamic index structure based on data access frequency and data distribution, and dynamically adjusts the size of the index node according to the data access frequency; wherein the adjustment of the index node size is based on a preset performance threshold;

[0057] The index building module uses a distribution-aware algorithm based on cluster analysis and data skew detection to analyze the distribution of data on the storage medium and optimize the index structure.

[0058] The query optimization module in this embodiment includes a query plan generator and a cost evaluator;

[0059] Among them, the query plan generator is used to generate an index-based query execution plan;

[0060] The cost evaluator is used to evaluate the query time of different paths by means of a machine learning model (cost model).

[0061] In this embodiment, the index-based query execution plan includes a multi-stage query execution process and an intermediate result caching strategy;

[0062] The multi-stage query execution process includes data retrieval, data filtering, data projection, sorting, joining and aggregation operations; data retrieval is to retrieve data from the storage engine based on the index; data filtering uses the WHERE clause to filter data to reduce the amount of data for subsequent operations; data projection selects the required columns according to the SELECT clause;

[0063] The intermediate result cache strategy includes selection strategy, cache invalidation strategy, and cache replacement strategy; the selection strategy is based on the temporal locality and spatial locality of the intermediate results generated by the query execution process; the cache invalidation strategy is to specify cache invalidation by using time standards while setting data cache; the cache replacement strategy uses the LRU algorithm to remove data from the cache area with a usage frequency lower than the set number threshold within the set time range.

[0064] The reference indicators of the machine learning model input in this embodiment include CPU processing time, number of I / O operations, data transmission volume, and data transmission mode; each factor is initially assigned a different weight;

[0065] Among them, the weight is assigned according to the degree of influence of each reference indicator on the query time, and is described by a linear relationship. The formula is y=ax1+bx2+cx3+dx4; among them, x1, x2, x3, x4 are the values ​​of CPU processing time, I / O operation number, data transmission volume, and transmission method respectively, and a, b, c, d are the weights of each reference indicator respectively.

[0066] The query plan generator in this embodiment uses statistical information and machine learning algorithms to predict the distribution of query results, and can dynamically adjust the query plan according to the prediction results. The content of dynamically adjusting the query plan includes adjusting the parallelism and resource allocation of query execution; the details are as follows:

[0067] First, obtain the load resource information of the remaining number of CPU cores, remaining memory capacity, and remaining broadband capacity of the database system;

[0068] Then, collect query statistics including data access patterns, data volume, and index usage, and use the configuration parameters of maximum parallelism (max_parallel_degree) and work memory (work_mem) to control parallelism and memory usage;

[0069] At the same time, partitioning technology is used for data tables whose size exceeds the set threshold, and each query operation is executed in parallel on different partitions.

[0070] Embodiment 2:

[0071] As attached Figure 4 As shown, this embodiment provides a data index and query optimization method based on a database, and the method is specifically as follows:

[0072] S1. Receive a query request and parse the content of the query request; wherein the content of the query request includes query conditions, sorting requirements, and data aggregation operations;

[0073] S2. Use the index building module to build or update the index to adapt to the changes in the data records in the data storage module; at the same time, the index building module dynamically adjusts the index structure according to the changes in the data access mode, and the content of the dynamic adjustment of the index structure includes splitting, merging or reorganizing the index; the index adjustment strategy of the index building module is based on the data access frequency, data distribution and system load setting, specifically: according to the past query logs, identify the data that needs to be accessed frequently, optimize the relevant indexes, and adjust the relevant indexes in combination with the changes in the data distribution after the data is updated;

[0074] S3. Generate a query execution plan using a query optimization module. The query execution plan includes using a cost evaluator to evaluate and select an optimal query path and using a machine learning model to predict the query result distribution.

[0075] S4. Execute the query and return the result, and collect the statistical information of the query execution for subsequent optimization; the statistical information includes the query execution time, resource consumption and user satisfaction.

[0076] In this embodiment, building or updating the index includes incremental update and full reconstruction;

[0077] Among them, incremental update adopts the partial update method, that is, when the data change amount does not exceed the set threshold, only the existing index is updated to reflect the insertion, deletion and update of data records;

[0078] When the scale of data changes exceeds the set threshold, the index is rebuilt to optimize performance, that is, the index is rebuilt for all data and the corresponding mapping relationship is established.

[0079] As attached Figure 3 As shown, the working process of the query optimization module in this embodiment is as follows:

[0080] S301, the initial query plan and the solution space saved for the query operation in the past are sent to the query optimization module as external input;

[0081] S302, the query optimization module continuously optimizes based on the established machine learning model and generates a corresponding query plan;

[0082] S303. The query plan is directly used for the query operation of the database, and the time information of the query operation is used to feed back the machine learning model calculation, and this process is repeated until a set of optimal results is found.

[0083] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements 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 data indexing and query optimization system based on database, It is characterized in that The system includes a query optimization module, an index construction module and a data storage module; Among them, the data storage module is used to store data records in the database; data records are stored in columnar format to improve data compression rate and query performance storage, and reduce disk I / O operations; The index building module is used to build indexes based on the data records in the data storage module. The index adopts B+ tree design to reduce the number of I / O operations when data is transferred between disk and memory; The query optimization module is used to receive query requests and optimize the query path according to the index built by the index building module.

2. The data indexing and query optimization system based on a database according to claim 1, It is characterized in that The data storage module distributes data to multiple physical disks through RAID, and uses different disk blocks when reading and writing different data, thereby improving the overall performance of data reading and writing; During actual reading and writing, the data storage module uses a buffer pool composed of an internal database to reduce physical read and write operations on the disk.

3. The data indexing and query optimization system based on a database according to claim 1, It is characterized in that The index building module adopts a dynamic index structure based on data access frequency and data distribution, and dynamically adjusts the size of the index node according to the data access frequency; wherein the adjustment of the index node size is based on a preset performance threshold; The index building module uses a distribution-aware algorithm based on cluster analysis and data skew detection to analyze the distribution of data on the storage medium and optimize the index structure.

4. The data indexing and query optimization system based on a database according to claim 1, It is characterized in that The query optimization module includes a query plan generator and a cost evaluator; Among them, the query plan generator is used to generate an index-based query execution plan; The cost evaluator is used to evaluate the query time of different paths using a machine learning model.

5. The data indexing and query optimization system based on a database according to claim 4, It is characterized in that The index-based query execution plan includes a multi-stage query execution process and an intermediate result caching strategy; The multi-stage query execution process includes data retrieval, data filtering, data projection, sorting, joining and aggregation operations; data retrieval is to retrieve data from the storage engine based on the index; data filtering uses the WHERE clause to filter data to reduce the amount of data for subsequent operations; data projection selects the required columns according to the SELECT clause; The intermediate result cache strategy includes selection strategy, cache invalidation strategy, and cache replacement strategy; the selection strategy is based on the temporal locality and spatial locality of the intermediate results generated by the query execution process; the cache invalidation strategy is to specify cache invalidation by using time standards while setting data cache; the cache replacement strategy uses the LRU algorithm to remove data from the cache area with a usage frequency lower than the set number threshold within the set time range.

6. The data indexing and query optimization system based on a database according to claim 4, It is characterized in that The reference indicators input to the machine learning model include CPU processing time, number of I / O operations, data transmission volume, and data transmission method; each factor is initially assigned a different weight; The weight is assigned according to the influence of each reference indicator on the query time, and is described by a linear relationship, with the formula y=ax 1 +bx 2 +cx 3 +dx 4 ; where x 1 ,x 2 ,x 3 ,x 4 They are the values ​​of CPU processing time, I / O operation number, data transmission volume, and transmission method. a, b, c, and d are the weights of each reference indicator.

7. The data indexing and query optimization system based on a database according to claim 4, It is characterized in that The query plan generator uses statistical information and machine learning algorithms to predict the distribution of query results, and can dynamically adjust the query plan according to the prediction results. The content of dynamically adjusting the query plan includes adjusting the parallelism and resource allocation of query execution; the details are as follows: First, obtain the load resource information of the remaining number of CPU cores, remaining memory capacity, and remaining broadband capacity of the database system; Then, query statistics including data access patterns, data volume, and index usage are collected, and configuration parameters for maximum parallelism and working memory are used to control parallelism and memory usage. At the same time, partitioning technology is used for data tables whose size exceeds the set threshold, and each query operation is executed in parallel on different partitions.

8. A data indexing and query optimization method based on database, It is characterized in that The method is as follows: S1. Receive a query request and parse the content of the query request; wherein the content of the query request includes query conditions, sorting requirements, and data aggregation operations; S2. Use the index building module to build or update the index to adapt to the changes in the data records in the data storage module; at the same time, the index building module dynamically adjusts the index structure according to the changes in the data access mode, and the content of the dynamic adjustment of the index structure includes splitting, merging or reorganizing the index; the index adjustment strategy of the index building module is based on the data access frequency, data distribution and system load setting, specifically: according to the past query logs, identify the data that needs to be accessed frequently, optimize the relevant indexes, and adjust the relevant indexes in combination with the changes in the data distribution after the data is updated; S3. Generate a query execution plan using a query optimization module. The query execution plan includes using a cost evaluator to evaluate and select an optimal query path and using a machine learning model to predict the query result distribution. S4. Execute the query and return the result, and collect the statistical information of the query execution for subsequent optimization; the statistical information includes the query execution time, resource consumption and user satisfaction.

9. The method for optimizing data index and query based on a database according to claim 8, It is characterized in that Building or updating an index includes incremental updates and full rebuilds; Among them, incremental update adopts the partial update method, that is, when the data change amount does not exceed the set threshold, only the existing index is updated to reflect the insertion, deletion and update of data records; When the scale of data changes exceeds the set threshold, the index is rebuilt to optimize performance, that is, the index is rebuilt for all data and the corresponding mapping relationship is established.

10. The method for data indexing and query optimization based on a database according to claim 8 or 9, It is characterized in that The working process of the query optimization module is as follows: S301, the initial query plan and the solution space saved for the query operation in the past are sent to the query optimization module as external input; S302, the query optimization module continuously optimizes based on the established machine learning model and generates a corresponding query plan; S303. The query plan is directly used for the query operation of the database, and the time information of the query operation is used to feed back the machine learning model calculation, and this process is repeated until a set of optimal results is found.

Images