Analysis and query method and device for database

Abstract

The invention discloses an analysis query method and device for a database, and the method comprises the steps: obtaining data corresponding to a query instruction from a pre-constructed preset storage structure through a preset index algorithm when the query instruction is received in a data query process of a database system, the preset storage structure is obtained by optimizing a storage structure design layer of a multi-version concurrency control mechanism, the preset storage structure is used for avoiding volume expansion caused by multi-version control and improving the performance of data analysis and query, and data is compressed through a data compression algorithm. And storing the compressed data in a preset storage structure in a column storage mode.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and more specifically, to a method and device for analyzing and querying a database. Background Art

[0002] When using the mainstream open source database system (postgresql) as the core of the data warehouse system, it involves a large number of repeated read and write operations on several data tables. However, the multi-version concurrency control (MVCC) mechanism designed by postgresql to support concurrent writing and consistency of data may cause storage bloat and index bloat in some cases. The main reason for the bloat is the retention of old versions and frequent update and delete operations.

[0003] Due to the MVCC mechanism, when updating or deleting data, the database will not delete the old version of the data immediately, but will retain it for a period of time to support concurrent reading and transaction rollback operations. Over time, a large amount of old version data that has not been cleaned up will accumulate, resulting in an increase in storage space. In business, if there are a large number of frequent update and delete operations, more old version data will be generated, exacerbating storage expansion.

[0004] Therefore, how to avoid the expansion of data file volume caused by storage expansion and index expansion and improve query performance is an urgent problem to be solved in this application. Summary of the invention

[0005] In view of this, the present application discloses a method and device for analyzing and querying a database, aiming to avoid the expansion of data file volume due to storage expansion and index expansion and improve query performance.

[0006] In order to achieve the above purpose, the disclosed technical solution is as follows:

[0007] The first aspect of the present application discloses a method for analyzing and querying a database, the method comprising:

[0008] In the process of querying data on the database system, when a query instruction is received, the data corresponding to the query instruction is obtained from the pre-built preset storage structure through a preset index algorithm;

[0009] Among them, the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control mechanism; the preset storage structure is used to avoid the volume expansion caused by multi-version control and improve the performance of data analysis and query; the data is compressed by a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

[0010] Preferably, storing the compressed data in the preset storage structure in a column-based storage manner includes:

[0011] Dividing the compressed data according to a column data division method; the column data division method is a division method in which data in the same column are continuously stored together;

[0012] Perform data type analysis on the divided data to obtain analysis results;

[0013] Map the repeated values ​​in the analysis result columns to integer identifiers to complete the duplicate value processing;

[0014] Determine the column type of the analysis result after the repeated value processing, and perform the corresponding storage operations according to the column type of the analysis result after the repeated value processing to complete the storage of the compressed data in the preset storage structure; wherein the column type includes at least a numeric column or a string column.

[0015] Preferably, determining the column type of the analysis result after the repeated value processing, and performing the corresponding storage operation according to the column type of the analysis result after the repeated value processing includes:

[0016] If the column type of the analysis result after the repeated value processing is the numerical column, obtaining the difference between adjacent data of the numerical column, and performing difference encoding on the difference to complete the storage operation;

[0017] If the column type of the analysis result after duplicate value processing is a string column, similar string fragments in the analysis result are obtained, and the similar string fragments are compressed using a preset compression algorithm.

[0018] Preferably, it also includes:

[0019] If there are zero values ​​or null values ​​in the column of the analysis result, the zero values ​​or null values ​​are encoded by a preset encoding method to avoid allocating storage space for the zero values ​​or null values.

[0020] Preferably, it also includes:

[0021] When data of the nested format type is received, the data of the nested format type is stored in the preset storage structure to be compatible with the storage of complex data structures.

[0022] The second aspect of the present application discloses an analysis and query device for a database, the device comprising:

[0023] An acquisition unit is used to acquire data corresponding to the query instruction from a pre-built preset storage structure through a preset index algorithm when a query instruction is received during a process of performing data query on a database system; wherein the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control mechanism; the preset storage structure is used to avoid volume expansion caused by multi-version control and improve the performance of data analysis query; the data is compressed through a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

[0024] Preferably, the acquiring unit storing the compressed data in the preset storage structure in a column-based storage manner includes:

[0025] A partitioning module, used to partition the compressed data according to a column data partitioning method; the column data partitioning method is a partitioning method in which data in the same column are stored together continuously;

[0026] An analysis module is used to analyze the data types of the divided data and obtain analysis results;

[0027] A processing module, used for mapping repeated values ​​in the columns of the analysis results to integer identifiers to complete repeated value processing;

[0028] A determination module is used to determine the column type of the analysis result after repeated value processing, and perform corresponding storage operations according to the column type of the analysis result after repeated value processing to complete the storage of compressed data in the preset storage structure; wherein the column type includes at least a numeric column or a string column.

[0029] Preferably, the determining module includes:

[0030] A first acquisition submodule is used for acquiring the difference between adjacent data of the numerical column if the column type of the analysis result after the repeated value processing is the numerical column, and performing difference encoding on the difference to complete the storage operation;

[0031] The second acquisition submodule is used to acquire similar string segments in the analysis result if the column type of the analysis result after the repeated value processing is a string column, and compress the similar string segments by using a preset compression algorithm.

[0032] Preferably, it also includes:

[0033] The encoding unit is used to encode the zero value or the null value in a preset encoding method if there is a zero value or a null value in the column of the analysis result, so as to avoid allocating storage space for the zero value or the null value.

[0034] Preferably, it also includes:

[0035] The storage unit is used to store the data of the nested format type in the preset storage structure when receiving the data of the nested format type, so as to be compatible with the storage of the complex data structure.

[0036] Through the above technical scheme, it can be known that the present application discloses a method and device for analyzing and querying a database. In the process of performing data query on a database system, when a query instruction is received, the data corresponding to the query instruction is obtained from a pre-built preset storage structure through a preset index algorithm, wherein the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrent control mechanism, and the preset storage structure is used to avoid the volume expansion caused by the multi-version control and improve the performance of data analysis query. The data is compressed by a data compression algorithm, and the compressed data is stored in the preset storage structure through a column storage method. Through the above scheme, in the process of performing data query on a database system, the data corresponding to the query instruction is obtained from a pre-built preset storage structure through a preset index algorithm. Since the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrent control mechanism, that is, the multi-version concurrent control mechanism is abandoned at the storage structure design level, the concurrent writing capability is slightly reduced, saving storage space and improving the online analytical processing (OLAP) query performance. The data corresponding to the query instruction obtained is compressed by a data compression algorithm, and the compressed data is stored in a preset storage structure by column storage. The data compression algorithm reduces the storage space requirements of the data and improves the data transmission efficiency, so that more data can be stored under the same storage capacity, and more efficient performance is provided for data transmission. In addition, the use of column storage technology provides a better compression ratio, while reducing the amount of data transmission after the query hits, which can provide higher reading performance and compression ratio. This avoids the expansion of data file volume caused by storage expansion and index expansion, and improves query performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0038] Figure 1 A flowchart of a method for analyzing and querying a database disclosed in an embodiment of the present application;

[0039] Figure 2 A schematic diagram of a process for storing compressed data in a preset storage structure according to an embodiment of the present application;

[0040] Figure 3 A schematic diagram of the structure of a database analysis and query device disclosed in an embodiment of the present application;

[0041] Figure 4 This is a schematic diagram of the structure of an electronic device disclosed in an embodiment of the present application. DETAILED DESCRIPTION

[0042] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0043] In this application, the terms "comprises", "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element.

[0044] As can be seen from the background technology, due to the MVCC mechanism, when the database updates or deletes data, it will not delete the old version of the data immediately, but will retain it for a period of time to support concurrent reading and transaction rollback operations. As time goes by, a large amount of old version data that has not been cleaned up will accumulate, resulting in an increase in storage space. In business, if there are a large number of frequent update and delete operations, more old version data will be generated, thereby exacerbating storage expansion. Therefore, how to avoid the expansion of data file volume caused by storage expansion and index expansion and improve query performance is a problem that needs to be solved in this application.

[0045] In order to solve the above problems, the present application discloses a method and device for analyzing and querying a database. In the process of querying data on a database system, the data corresponding to the query instruction is obtained from a pre-built preset storage structure through a preset index algorithm. Since the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrent control mechanism, that is, the multi-version concurrent control mechanism is abandoned at the storage structure design level, so that the concurrent writing capability is slightly reduced, the storage space is saved and the OLAP query performance is improved. And the data corresponding to the query instruction obtained is compressed by a data compression algorithm, and the compressed data is stored in a preset storage structure by a column storage method. The data compression algorithm reduces the storage space requirement of the data and improves the data transmission efficiency, so that more data can be stored under the same storage capacity, and more efficient performance is provided for data transmission. And the column storage technology is used to provide a better compression ratio, while reducing the amount of data transmission after the query hit, which can provide higher reading performance and compression ratio. Thereby avoiding the expansion of the data file volume caused by storage expansion and index expansion, and improving the query performance. The specific implementation method is specifically described by the following embodiments.

[0046] refer to Figure 1 As shown, an analysis and query method for a database disclosed in an embodiment of the present application is provided. The analysis and query method for the database mainly includes the following steps:

[0047] S101: In the process of performing data query on a database system, a query instruction is obtained.

[0048] In S101, in the process of performing data query on the database system, a query instruction sent by the user is obtained. The query instruction is a structured query instruction of the database.

[0049] S102: Obtain data corresponding to the query instruction from a pre-built preset storage structure through a preset index algorithm; wherein the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control (MVCC) mechanism; the preset storage structure is used to avoid volume expansion caused by multi-version control and improve the performance of data analysis queries; the data is compressed through a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

[0050] When processing data queries, the database system will first use the preset index algorithm to speed up the query process. The preset index algorithm allows the database system to quickly locate specific data in the table. Through the preset index algorithm, the database can avoid full table scans, thereby improving query efficiency.

[0051] The preset index algorithm includes but is not limited to an index algorithm compatible with a self-balancing tree structure (BTree). The BTree index algorithm can keep data in order and allow efficient search, sequential access, insertion and deletion of data, all of which are completed in logarithmic time. Unlike traditional binary search trees, BTree can have more than 2 child nodes, which makes the preset index algorithm more efficient when processing large amounts of data. The preset index algorithm is designed mainly to optimize the reading and writing operations of large blocks of data, especially on external storage devices such as disks. By reducing the intermediate processes experienced when locating records, the preset index algorithm can speed up data access. The indexing function is implemented based on the commonly used BTree algorithm, providing strong support for query acceleration.

[0052] It should be noted that the storage format of this solution avoids the volume expansion defect caused by the MVCC mechanism of the data storage (heap) format by optimizing the storage structure design layer of the MVCC mechanism.

[0053] Because the MVCC multi-version control mechanism introduced by the concurrent modification mechanism of data needs to be supported, there are two main implementation methods in the database field. One is the snapshot type represented by the storage engine (innodb) of the database (mysql); and the other is the rollback segment mechanism represented by the database oracle. Among them, postgresql and mysql's innodb are both snapshot types, but the MVCC mechanism designed by postgresql to support concurrent data writing and consistency will cause storage expansion and index expansion problems in some cases. The main reasons for the expansion are:

[0054] 1. Retention of old versions: The MVCC mechanism allows the database to not delete old versions of data immediately when updating or deleting data, but to retain them for a period of time to support concurrent reading and transaction rollback operations. Over time, a large amount of old versions of data that have not been cleaned up will accumulate, resulting in an increase in storage space.

[0055] 2. Frequent update and delete operations: In business, if there are a large number of frequent update and delete operations, more old versions of data will be generated, which will aggravate storage expansion. The main impacts are: (1) Waste of storage space: A large amount of storage space is occupied by useless old versions of data, reducing the utilization rate of storage resources; (2) Performance degradation: Storage expansion may cause the query and operation performance of the database to degrade because more data needs to be scanned when processing data. Suppose an e-commerce database, in which the order table is frequently updated, such as modifying the order status, and each update will generate a new version. If there is no timely cleanup mechanism, these old versions of order status data will continue to accumulate, resulting in a rapid increase in storage space, and it will also make the operation of querying the latest order status more complicated and time-consuming.

[0056] In order to solve the shortcomings of retaining old versions and frequent update and deletion operations, this solution is suitable for analytical tasks. This solution abandons the multi-version control mechanism to avoid the problem of a large number of historical versions remaining due to repeated reading and writing, and uses column storage to efficiently compress data.

[0057] By optimizing the storage structure design layer of the MVCC mechanism, the MVCC multi-version control mechanism is abandoned at the storage structure design level to avoid data file expansion. This solution is mainly used for analytical tasks, so the concurrent write capability is slightly reduced, saving storage space, improving OLAP query performance, and simplifying data management.

[0058] The use of column storage technology can provide a better compression ratio and reduce the amount of data transmission after the query hits. It can provide higher reading performance and compression ratio, especially with stronger adaptability when dealing with analytical query needs.

[0059] Column storage databases are particularly suitable for data analysis queries that require reading a small number of columns but a large number of rows, because it can quickly scan column data and reduce unnecessary read operations. The advantages of column storage technology mainly include: Improve query performance: Column storage technology only needs to read the required columns when executing queries, instead of reading the entire row like row storage, which can significantly reduce input / output (I / O) operations and improve query efficiency; Reduce storage space requirements: Since the data type of the same column is the same, column storage can achieve more efficient data compression and reduce storage space requirements; High compression efficiency: Data in column storage usually has a high similarity, which enables compression algorithms to work more effectively and further reduce the required storage space; Improve cache hit rate: Since column data is highly compressed, commonly used data pages are frequently accessed, which improves the effectiveness of the cache; Suitable for big data processing: Column storage technology is particularly suitable for processing and analyzing large-scale data sets, and can efficiently process PB-level data; Vectorized processing: Column storage can process data in a vectorized manner, which improves the processing efficiency of the CPU, especially when performing aggregation and sorting operations.

[0060] With the help of data compression algorithm tools, more data can be stored in the same storage capacity and more efficient performance can be provided for data transmission. With the help of data compression algorithm, more data can be stored in the same storage capacity and data transmission performance can be improved. The main reasons are as follows:

[0061] 1. Reduce storage space:

[0062] a) Data deduplication: Many data sets contain a large number of repeated data patterns or values. Compression algorithms can identify and encode these repeated parts, store the repeated pattern only once, and then use references or tags to indicate other repeated locations, thereby greatly reducing the actual amount of data stored. For example, in a database containing a large amount of identical product information, the same parts such as the product name and specifications only need to be stored once.

[0063] b) Data pattern utilization: Some data has obvious patterns, such as continuous zero values, increasing or decreasing numerical sequences, etc. Compression algorithms can perform special encodings for these patterns and represent them in a more compact form to save space. For example, a column containing a large number of continuous zero values ​​can use special markers and a small amount of information to represent this pattern instead of storing each zero value.

[0064] 2. Improve transmission efficiency:

[0065] a) Reduce the amount of data: When the compressed data is transmitted in the network, it requires less transmission time and bandwidth. This means that more data can be transmitted in the same amount of time, or data transmission can be completed faster under limited bandwidth.

[0066] Suppose that it may take a long time to transfer an uncompressed 1 Gigabyte (GB) file, but after compression it becomes 500 Megabit (MB), the transfer time will be greatly shortened.

[0067] b) Reduce the impact of transmission errors: The probability of errors in the transmission process is relatively low for smaller amounts of data. Even if errors occur, the cost of error correction and retransmission is lower due to the small amount of data transmitted.

[0068] For example, in an unstable network environment, compressed data is less likely to be affected by packet loss or errors and affect the overall transmission effect.

[0069] 3. Improved hardware efficiency:

[0070] a) Improved cache utilization: Since the amount of compressed data becomes smaller, more data can be loaded into the hardware cache, which improves the locality and hit rate of data access, thereby speeding up processing.

[0071] For the central processing unit (CPU) cache and disk cache, more relevant compressed data can be stored, reducing the number of times data is read from slower storage media.

[0072] b) Disk I / O optimization: When storing and reading on disk, smaller data volumes mean less disk seek time and read and write operations, improving disk I / O performance.

[0073] Especially for large-scale data storage and frequent read and write operations, the performance improvement brought by compression is more obvious.

[0074] In summary, data compression algorithms reduce the storage space requirements of data and improve data transmission efficiency, so that more data can be stored in the same storage capacity and provide more efficient performance for data transmission.

[0075] The specific process of storing the compressed data in the preset storage structure by column storage is as follows: Figure 2 shown.

[0076] S201: Divide the compressed data according to a column data division method; the column data division method is a division method in which data in the same column are continuously stored together.

[0077] In S201, the compressed data is organized and stored in rows according to the column data division method, so that the data in the same column are stored together continuously.

[0078] S202: Perform data type analysis on the divided data to obtain analysis results.

[0079] In S202, the data type of each column is analyzed in depth. For example, for an integer column, if the value range of the data is small, a more compact encoding method can be used, such as variable-length integer encoding.

[0080] S203: Mapping values ​​that appear repeatedly in the columns of the analysis results to integer identifiers to complete repeated value processing.

[0081] Among them, find duplicate values ​​in the columns of the analysis results. For columns with a large number of duplicate values, dictionary encoding can be used. For example, create a dictionary to map repeated values ​​to smaller integer identifiers to reduce storage space.

[0082] S204: Determine the column type of the analysis result after the duplicate value processing, and perform corresponding storage operations according to the column type of the analysis result after the duplicate value processing to complete the storage of the compressed data in a preset storage structure; wherein the column type includes at least a numeric column or a string column.

[0083] In S204, if the column type of the analysis result after the repeated value processing is a numeric column, the difference between adjacent data in the numeric column is obtained, and the difference is encoded to complete the storage operation; if the column type of the analysis result after the repeated value processing is a string column, similar string fragments in the analysis result are obtained, and the similar string fragments are compressed using a preset compression algorithm.

[0084] For the analysis results of numeric columns, you can perform difference encoding. For example, store the difference between adjacent data instead of the original value. If the change in the data is relatively small, the difference will usually take up less space than the original value.

[0085] For the analysis results of the string column, a data compression algorithm is used to compress similar string segments. The data compression algorithm includes but is not limited to a lossless data (LZ) compression algorithm.

[0086] If there are zero values ​​or null values ​​in the column of the analysis result, the zero values ​​or null values ​​are encoded by a preset encoding method to avoid allocating storage space for the zero values ​​or null values.

[0087] The preset encoding method includes but is not limited to dictionary encoding, difference encoding and other encoding methods.

[0088] Find duplicate values ​​in a column. For columns with a large number of duplicate values, you can use dictionary encoding. For example, you can create a dictionary that maps duplicate values ​​to smaller integer identifiers, thereby reducing storage space.

[0089] For numeric columns, you can do difference encoding. For example, store the difference between adjacent data instead of the original value. If the data changes are relatively small, the difference will usually take up less space than the original value.

[0090] In the string column, a compression algorithm, such as the LZ series algorithm, is used to compress similar string segments.

[0091] If there is a zero value or null value in a column, a special mark or encoding method can be used to represent it to avoid allocating full storage space for each zero value or null value. This further saves storage space and effectively solves the problem of excessive volume expansion caused by historical versions accumulated due to update and delete operations. At the same time, column storage is used for aggregate analysis queries that only need to query a small number of data fields. For example, suppose there is a sales database that contains a sales order table with fields such as order ID, customer ID, product ID, sales amount, and sales date. If we only care about the total sales within a specific time period, we only need to query the "sales amount" and "sales date" fields for aggregate calculations. In column storage, since the data of the "sales amount" and "sales date" columns are stored continuously, the database can quickly locate and read the data of these two columns without scanning the entire row data, thereby reducing the volume of data to be scanned and read and improving query efficiency.

[0092] When data of a nested format type is received, the data of the nested format type is stored in a preset storage structure to be compatible with the storage of a complex data structure.

[0093] Support for nested format types can better adapt to more complex data types in terms of backward compatibility, and support for nested data types to provide support for complex structures. Nested format types include custom data types, so that you can arbitrarily design complex situations such as dictionary-based, list-based, dictionary-based, and list-based, and dictionary-based.

[0094] When using postgresql as the core of the data warehouse system, it involves a large number of repeated read and write operations on several data tables. Due to the characteristics of postgresql's common storage format heap and MVCC mechanism, the data file volume will expand and the query performance will decrease. Therefore, a new storage structure is specially designed to avoid the volume expansion caused by multi-version control and improve the performance of analytical queries.

[0095] In the embodiment of the present application, in the process of performing data query on the database system, the data corresponding to the query instruction is obtained from the pre-built preset storage structure through the preset index algorithm. Since the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrent control mechanism, that is, the multi-version concurrent control mechanism is abandoned at the storage structure design level, so that the concurrent writing capability is slightly reduced, the storage space is saved and the OLAP query performance is improved. And the data corresponding to the query instruction obtained is compressed by the data compression algorithm, and the compressed data is stored in the preset storage structure through the column storage method. The data compression algorithm reduces the storage space requirement of the data and improves the data transmission efficiency, so that more data can be stored under the same storage capacity, and more efficient performance is provided for data transmission. And the use of column storage technology provides a better compression ratio, while reducing the amount of data transmission after the query hits, which can provide higher reading performance and compression ratio. Thereby avoiding the expansion of the data file volume caused by storage expansion and index expansion, and improving query performance.

[0096] Based on the above embodiment disclosed a method for analyzing and querying a database, the embodiment of the present application also discloses a corresponding device for analyzing and querying a database, such as Figure 3 As shown, the analysis and query device for the database includes:

[0097] The acquisition unit 301 is used to obtain the data corresponding to the query instruction from a pre-built preset storage structure through a preset index algorithm when a query instruction is received during the process of performing data query on the database system; wherein the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control mechanism; the preset storage structure is used to avoid the volume expansion caused by multi-version control and improve the performance of data analysis query; the data is compressed by a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

[0098] Furthermore, the acquisition unit 301 stores the compressed data in the preset storage structure in a column storage manner, including:

[0099] A partitioning module is used to partition the compressed data according to a column data partitioning method; the column data partitioning method is a partitioning method in which data in the same column are continuously stored together;

[0100] An analysis module is used to analyze the data types of the divided data and obtain analysis results;

[0101] A processing module, used for mapping repeated values ​​in the columns of the analysis results to integer identifiers to complete repeated value processing;

[0102] A determination module is used to determine the column type of the analysis result after the repeated value processing, and perform corresponding storage operations according to the column type of the analysis result after the repeated value processing to complete the storage of the compressed data in a preset storage structure; wherein the column type includes at least a numeric column or a string column.

[0103] Further, the modules are determined, including:

[0104] A first acquisition submodule is used for acquiring the difference between adjacent data of the numerical column if the column type of the analysis result after the repeated value processing is a numerical column, and performing difference encoding on the difference to complete the storage operation;

[0105] The second acquisition submodule is used to acquire similar string segments in the analysis result if the column type of the analysis result after the repeated value processing is a string column, and compress the similar string segments by using a preset compression algorithm.

[0106] Furthermore, the analysis and query device for the database also includes:

[0107] The encoding unit is used to encode the zero value or the null value in a preset encoding method if there is a zero value or a null value in the column of the analysis result, so as to avoid allocating storage space for the zero value or the null value.

[0108] Furthermore, the analysis and query device for the database also includes:

[0109] The storage unit is used to store the data of the nested format type in a preset storage structure when receiving the data of the nested format type, so as to be compatible with the storage of the complex data structure.

[0110] In the embodiment of the present application, in the process of performing data query on the database system, the data corresponding to the query instruction is obtained from the pre-built preset storage structure through the preset index algorithm. Since the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrent control mechanism, that is, the multi-version concurrent control mechanism is abandoned at the storage structure design level, so that the concurrent writing capability is slightly reduced, the storage space is saved and the OLAP query performance is improved. And the data corresponding to the query instruction obtained is compressed by the data compression algorithm, and the compressed data is stored in the preset storage structure through the column storage method. The data compression algorithm reduces the storage space requirement of the data and improves the data transmission efficiency, so that more data can be stored under the same storage capacity, and more efficient performance is provided for data transmission. And the use of column storage technology provides a better compression ratio, while reducing the amount of data transmission after the query hits, which can provide higher reading performance and compression ratio. Thereby avoiding the expansion of the data file volume caused by storage expansion and index expansion, and improving query performance.

[0111] An embodiment of the present application further provides a storage medium, which includes stored instructions, wherein when the instructions are executed, the device where the storage medium is located is controlled to execute the above-mentioned analysis and query method for the database.

[0112] The present application also provides an electronic device, whose structural diagram is as follows: Figure 4 As shown, it specifically includes a memory 401 and one or more instructions 402, wherein the one or more instructions 402 are stored in the memory 401 and are configured to be executed by one or more processors 403 to execute the above-mentioned analysis query method for the database.

[0113] For the aforementioned method embodiments, for the sake of simplicity, they are all described as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the order of the actions described, because according to the present application, some steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the present application.

[0114] It should be noted that each embodiment in this specification is described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts between the embodiments can be referred to each other. For system embodiments, since they are basically similar to method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0115] The steps in the methods of the embodiments of the present application can be adjusted in order, combined, and deleted according to actual needs.

[0116] Finally, it should be noted that, in this article, relational terms such as first and second, etc. are merely used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations.

[0117] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

[0118] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for analyzing and querying a database, characterized in that: The method comprises: In the process of querying data on the database system, when a query instruction is received, the data corresponding to the query instruction is obtained from the pre-built preset storage structure through a preset index algorithm; Among them, the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control mechanism; the preset storage structure is used to avoid the volume expansion caused by multi-version control and improve the performance of data analysis and query; the data is compressed by a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

2. The method according to claim 1, characterized in that The storing the compressed data in the preset storage structure in a column-based storage manner includes: Dividing the compressed data according to a column data division method; the column data division method is a division method in which data in the same column are continuously stored together; Perform data type analysis on the divided data to obtain analysis results; Map the repeated values ​​in the analysis result columns to integer identifiers to complete the duplicate value processing; Determine the column type of the analysis result after the repeated value processing, and perform the corresponding storage operations according to the column type of the analysis result after the repeated value processing to complete the storage of the compressed data in the preset storage structure; wherein the column type includes at least a numeric column or a string column.

3. The method according to claim 2, characterized in that Determine the column type of the analysis result after the repeated value processing, and perform corresponding storage operations according to the column type of the analysis result after the repeated value processing, including: If the column type of the analysis result after the repeated value processing is the numerical column, obtaining the difference between adjacent data of the numerical column, and performing difference encoding on the difference to complete the storage operation; If the column type of the analysis result after duplicate value processing is a string column, similar string fragments in the analysis result are obtained, and the similar string fragments are compressed using a preset compression algorithm.

4. The method according to claim 2, characterized in that: Also includes: If there are zero values ​​or null values ​​in the column of the analysis result, the zero values ​​or null values ​​are encoded by a preset encoding method to avoid allocating storage space for the zero values ​​or null values.

5. The method according to claim 1, characterized in that Also includes: When data of the nested format type is received, the data of the nested format type is stored in the preset storage structure to be compatible with the storage of complex data structures.

6. An analytical query device for a database, characterized in that: The device comprises: An acquisition unit is used to acquire data corresponding to the query instruction from a pre-built preset storage structure through a preset index algorithm when a query instruction is received during a process of performing data query on a database system; wherein the preset storage structure is obtained by optimizing the storage structure design layer of the multi-version concurrency control mechanism; the preset storage structure is used to avoid volume expansion caused by multi-version control and improve the performance of data analysis query; the data is compressed through a data compression algorithm, and the compressed data is stored in the preset storage structure through column storage.

7. The device according to claim 6, characterized in that The acquiring unit storing the compressed data in the preset storage structure in a column-based storage manner includes: A partitioning module, used to partition the compressed data according to a column data partitioning method; the column data partitioning method is a partitioning method in which data in the same column are stored together continuously; An analysis module is used to analyze the data types of the divided data and obtain analysis results; A processing module, used for mapping repeated values ​​in the columns of the analysis results to integer identifiers to complete repeated value processing; A determination module is used to determine the column type of the analysis result after repeated value processing, and perform corresponding storage operations according to the column type of the analysis result after repeated value processing to complete the storage of compressed data in the preset storage structure; wherein the column type includes at least a numeric column or a string column.

8. The device according to claim 7, characterized in that The determining module comprises: A first acquisition submodule is used for acquiring the difference between adjacent data of the numerical column if the column type of the analysis result after the repeated value processing is the numerical column, and performing difference encoding on the difference to complete the storage operation; The second acquisition submodule is used to acquire similar string segments in the analysis result if the column type of the analysis result after the repeated value processing is a string column, and compress the similar string segments by using a preset compression algorithm.

9. The device according to claim 7, characterized in that Also includes: The encoding unit is used to encode the zero value or the null value in a preset encoding method if there is a zero value or a null value in the column of the analysis result, so as to avoid allocating storage space for the zero value or the null value.

10. The device according to claim 6, characterized in that Also includes: The storage unit is used to store the data of the nested format type in the preset storage structure when receiving the data of the nested format type, so as to be compatible with the storage of the complex data structure.

Images