Time series database read performance optimization method and system based on multi-level index
By building a multi-level index structure in a time series database and optimizing timestamp and non-timestamp queries, the problem of low storage and query performance of time series databases in the existing technology is solved, and efficient data reading and analysis capabilities are achieved.
Patent Information
- Application Number
- CN202211007137.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-08-22
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2042-08-22
AI Technical Summary
When processing massive time series data, existing timing databases have problems such as high storage costs, high maintenance costs, low write throughput and poor query performance. Especially in the process of large-scale IoT-aware log storage and query, it cannot meet the efficiency. Reading and analyzing requirements.
Using a multi-level index based method, the secondary index of TSM files and the dictionary tree index in TSI are constructed, and time stamp and non-timestamp queries are optimized through the index structure to avoid full-table scanning and improve query efficiency.
It effectively improves the efficiency of timestamp range query and non-timestamp range query in timing databases, reduces memory and computing overhead, and provides an efficient query solution for large-scale timing data.
Smart Images

Figure CN115374240B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of database optimization, and in particular to a method and system for optimizing the read performance of a time series database based on a multi-level index. Background Art
[0002] With the development of Internet of Things technology, the application of IoT sensors in urban management is becoming more and more popular. Hundreds of millions of IoT sensors collect various data information all the time and report this information to the data management platform. This requires the data management platform to have strong data access capabilities and efficient reading capabilities.
[0003] The data generated by IoT devices has the following characteristics: 1. The data volume is large, the data grows over time, the same dimension is repeatedly taken, and the indicators change smoothly; 2. Data is continuously generated and written, and once written, it hardly needs to be modified; 3. When statistics are performed on data in different dimensions, there is a clear distinction between cold and hot data, and people are generally more concerned about recent data. From the above characteristics, it can be seen that the data generated by IoT sensing devices has obvious time series.
[0004] Conventional relational data storage systems such as MySQL and SQL Server have the following problems when processing time series data: 1. High storage cost, poor compression of time series database, and large machine resources are required; 2. High maintenance cost, single-machine system requires manual sub-library and table division at the upper layer; 3. Low write throughput, it is difficult to meet the write pressure of tens of millions of time series databases; 4. Poor query performance, poor aggregation and analysis performance of massive data. When using big data ecosystems such as Hadoop and Spark to store time series data, there are also the following problems: 1. High data latency, when doing offline batch processing, the time consumption is hours or even days; 2. Slow query performance, unable to make good use of indexes, relying on MapReduce tasks, and query time consumption is generally minutes.
[0005] The emergence of time series data effectively solves the storage problem of IoT perception data, and has made corresponding optimizations for data writing and reading. The time series database InfluxDB currently ranks first in the DB-Engines time series database management system. It uses a columnar storage file structure to manage time series data. Multiple data blocks are stored inside the file, and each data block stores data for a period of time in a time series. In order to meet the fast writing of time series data, influxDB sacrificed some reading performance during the design process. Therefore, in the storage process of large-scale IoT perception logs, the related query efficiency cannot meet the requirements of the industry.
[0006] When writing time series data, InfluxDB, like most database products, first writes WAL (Write Ahead Log), then writes to the cache, and finally refreshes the data to disk to ensure data integrity and availability.
[0007] The reading of time series data is generally divided into the following two categories: 1. Queries with timestamps as query conditions; 2. Queries with non-timestamp fields as query conditions. InfluxDB handles these two types of queries as follows.
[0008] According to the storage file structure of influxDB, the index block contains records of the maximum and minimum timestamps of the data in the file, and the internal data is ordered by timestamp. Therefore, in the query process with timestamp as the query condition, you can first read the content of the index block to determine whether there is data within the query range in the storage structure. If so, use binary query to read the data from the file. In the query with non-timestamp as the query condition, the inverted index structure in the storage file is used. The influxDB storage file stores the inverted index based on measurement and tag as keywords. The Hash Index structure is used to quickly locate the location of the data, traverse the seriesID in this inverted index, find the corresponding seriesKey, and query the required data.
[0009] The problems that exist in the process of reading influxDB data are as follows:
[0010] 1. When there are many time series to manage, the cost of sequence positioning is high, and all metadata needs to be read for traversal, resulting in low query efficiency.
[0011] 2. In the query process for non-timestamp fields, Hash Index has a very efficient query effect for equal queries. However, when the query conditions are > or <, the ordered storage of Tag Value is required to find the data that meets the conditions. This requires that Tag Value needs to be sorted when building TSI, which results in a large memory and computing overhead.
[0012] The invention patent with publication number CN108268546B discloses a method and device for optimizing a database, including: flexibly configuring the function points to be optimized and analyzed in the database to obtain configuration information; judging whether the current collection state of the function point is in a first state according to the configuration information, the first state is used to characterize that the collection of the function point has not been completed; when it is determined that the current collection state is in the first state, classifying the task type of the function point to obtain a task classification result; when it is detected that the current occupancy of the database resources is idle, selecting a priority processing strategy corresponding to the task classification result to execute data collection for the function point according to the task queue priority of the task type.
[0013] Comparison of technical points: This patent configures the function points to be optimized and analyzed, determines the specific state of the current database, and classifies the function points according to the specific state; when resources are idle, reclassifies the relevant tasks and adjusts the priority of the tasks. This patent is an optimization for the data collection process, which is equivalent to optimizing the data writing process. This application is mainly aimed at time series databases, optimizing the data reading process, and avoiding full table scanning by adding secondary indexes.
[0014] The invention patent with publication number CN113868251A discloses a global secondary index method and device for a distributed database. The specific implementation scheme is: in response to a received database write request, the original data to be written is obtained, and the original data is written into the distributed database; global secondary index processing is performed on the original data written into the distributed database to obtain global secondary index data; a global secondary index table corresponding to the global secondary index data and the primary key of the data table in the distributed database is established, and the global secondary index table is written into the index shard based on an asynchronous processing method.
[0015] Comparison of technical points: This patent is aimed at database writing scenarios, and establishes a global secondary index. The secondary index corresponds to the primary key in the data table, so that the location of data writing can be quickly located and the delay of data writing can be reduced. In this application, data writing is processed by first writing to the file, then writing to the memory cache, and finally writing to the disk. After the data is written to the file, it is directly returned to the client, and the delay of data writing is very low. The process of building an index in this application is mainly to improve the performance of data reading.
[0016] The invention patent with publication number CN103902693B discloses a method for a read-optimized in-memory database T-tree index structure to construct a tT-tree data structure: based on the existing data, a T-tree index structure is established, and data is inserted according to the size N of the node in the T-tree structure, and the order of the data in the node is ensured. If the data in a node is full, a split operation is performed to ensure the balance of the tree. In the process of constructing the tT-tree, the built-in T-tree left and right subtree fields in the tree structure do not perform any operations at this stage; data query operations are performed in the established tT-tree, and the query operations are divided into single-value queries and range queries.
[0017] Comparison of key technical points: This patent mainly constructs a T-tree index, ensures the orderliness of data within the node during data insertion, and ensures the balance of the T-tree by splitting the data. When reading data, the structure of the T-tree itself is used to ensure efficient access to data, and also improves the cache hit rate by reducing the use of pointers. In this application, secondary indexes are used to improve the efficiency of timestamp queries, and the storage location of data is located by indexing data to avoid full table scans during the query process; at the same time, the dictionary tree is used to optimize the TSI index, improve the range query of non-timestamp fields, and use the orderliness of the dictionary tree itself to quickly locate the storage location of data, making it easy to quickly find data. Summary of the invention
[0018] In view of the defects in the prior art, the present invention provides a method and system for optimizing the read performance of a time series database based on multi-level indexing.
[0019] According to a method and system for optimizing the read performance of a time series database based on a multi-level index, the solution is as follows:
[0020] In a first aspect, a method for optimizing read performance of a time series database based on a multi-level index is provided, the method comprising:
[0021] Steps for building the secondary index of TSM files: Build the timestamp index of all TSMs in the outer layer of TSM. For queries based on timestamp conditions, locate the storage files involved in the query conditions and obtain the storage address and offset of the files.
[0022] Steps for constructing a dictionary tree index in TSI: By constructing a dictionary tree and utilizing the orderliness of the dictionary tree itself, the data that meets the conditions in the range query is located.
[0023] Preferably, the step of constructing a secondary index of a TSM file includes:
[0024] Step S1.1: Design the index structure: The entire index structure is an array consisting of index nodes; the index node contains: the unique ID of the TSM, the minimum time minTime of the TSM, the maximum time maxTime, and the location where the TSM is stored on the disk; the nodes in the index array are sorted according to the unique ID of the TSM;
[0025] Step S1.2: Data writing process: first write to WAL, i.e. log structure, and then write to the cache structure in memory; when the data in memory meets the conditions for writing to disk, first write the data to the corresponding TSM storage structure in disk according to the original logic, and in the writing process obtain the unique ID of the TSM, TSM_ID1, minimum timestamp min_time1, maximum timestamp max_time1, and disk storage location information offset1;
[0026] Step S1.3: The query process based on the timestamp condition includes: 1) querying the data whose timestamp is equal to a certain value; 2) querying the data whose timestamp is within a certain range.
[0027] Preferably, the step S1.2 further comprises: searching in the outer index array whether there is an index node with TDMID TSM_ID1, using a binary search method;
[0028] If it exists, update the maximum and minimum time of the index node; if it does not exist, build a new index node based on the returned data and add the new index node to the corresponding position in the array.
[0029] Preferably, the step S1.3 comprises:
[0030] 1) The query condition is that the timestamp is equal to a certain value: When the query condition is that the timestamp is equal to a certain value, set the timestamp value to timestamp1, traverse the index array, and determine whether there is data that meets the conditions in the corresponding TSM based on the min_time and max_time of each index node. When the following conditions are met, the TSM storage file corresponding to the index meets the query condition:
[0031] min_time<=timestamp1<=max_time
[0032] According to the TSM unique ID information in the index node and the TSM storage location information, directly read the data in the corresponding TSM storage file and return it to the client;
[0033] 2) The query condition is the timestamp range:
[0034] When the query condition is a timestamp range, set the query timestamp range to min_time1, max_time1; traverse the index array, set the timestamp range of the traversed index nodes to min_time2, max_time2; when any of the following conditions is met, the found TSM meets the query condition of the timestamp range:
[0035] max_time1>min_time2&&max_time1 <max_time2
[0036] min_time1>min_time2&&min_time1 <max_time2
[0037] min_time1<min_time2&&max_time1> max_time2
[0038] Then, the qualified data is read from the found TSM file and returned to the client.
[0039] Preferably, the step of constructing a dictionary tree index in TSI includes:
[0040] Step S2.1: Design a dictionary tree. The next layer of the root node is the TagKey node, which stores the value information of the TagKey in the database. The TagValue of each TagKey is constructed with the TagKey node as the parent node. The child nodes of the TagKey node are guaranteed to be dictionary ordered.
[0041] Step S2.2: Construct a dictionary tree. After the data is written into the TSM file according to the writing process, the relevant information written into the TSM file is returned, including the unique ID of the TSM, TSM_ID1, and the data storage index information seriesIDList; at the same time, the TagKey and TagValue information of the data are returned, and the dictionary tree is constructed according to the TagKey and TagValue;
[0042] Step S2.3: Query using non-timestamp as query condition.
[0043] Preferably, constructing a dictionary tree according to TagKey and TagValue in step S2.2 includes:
[0044] In the dictionary tree, start looking for TagKey in the child nodes of the root node.
[0045] If it does not exist, add a child node to the root node, the node_id of the child node is the current timestamp, the node_value is TagKey, and the key_list_address is empty;
[0046] If it exists, construct the path of TagValue in the child node of TagKey;
[0047] The last node of the TagValue path needs to store the corresponding data storage index information seriesIDList, that is, key_list_address=seriesIDList.
[0048] Preferably, the query process using non-timestamp as query condition in step S2.3 includes:
[0049] Non-timestamp equality query:
[0050] 1) Get the non-timestamp TagKey value;
[0051] 2) Get the corresponding TagValue value;
[0052] 3) Search in the dictionary tree. If the path corresponding to TagKey and TagValue exists in the dictionary tree, and the key_list_address attribute after the path is not empty, then the seriesIDList corresponding to the TagKey and TagValue can be determined according to the corresponding key_list_address;
[0053] 4) Get seriesKeyList according to seriesIDList to locate the specific data storage location;
[0054] Non-timestamp range query:
[0055] 1) Get the non-timestamp TagKey value;
[0056] 2) Get the corresponding TagValue value;
[0057] 3) Search in the dictionary tree. If there is a path corresponding to TagKey and TagValue in the dictionary tree, set the last node to node1, and then execute step 5);
[0058] 4) If there is no path corresponding to TagKey and TagValue in the trie, find the longest prefix of TagKey and TagValue. Let the last node be node2. If the query condition is tagKey < TagValue, find the first node greater than TagValue in the full path of the children nodes of node2, and set it as node1; if the query condition is tagKey > TagValue, find the last node less than TagValue in the full path of the children nodes of node2, and set it as node1; then perform step 5).
[0059] 5) If the query condition is tagKey < TagValue, all the nodes on the left side of node1 in the trie and all the nodes with non-empty key_list_address on the TagValue path are the qualified nodes.
[0060] 6) If the query condition is tagKey > TagValue, all the nodes on the right side of node1 and all the nodes with non-empty key_list_address among the children nodes of node1 are the qualified nodes.
[0061] 7) According to the key_list_address of the qualified nodes, obtain all the seriesKeys, get the specific storage location of the data, read the data, and return it to the client.
[0062] In the second aspect, a time series database read performance optimization system based on a multi-level index is provided. The system includes:
[0063] A secondary index module for constructing TSM files: Build a timestamp index for all TSMs on the outer layer of TSM. For queries with timestamp conditions, locate the storage files involved in the query conditions, and obtain the storage address and offset of the files.
[0064] A trie index module for constructing TSI: By constructing a trie and using the orderliness of the trie itself, locate the data that meets the conditions in range queries.
[0065] Preferably, the secondary index module for constructing TSM files includes:
[0066] Module M1.1: Design the index structure: The entire index structure is an array composed of index nodes; each index node contains: the unique ID of the TSM, the minimum time minTime of the TSM, the maximum time maxTime, and the storage location of the TSM on the disk; the nodes in the index array are sorted according to the unique ID of the TSM.
[0067] Module M1.2: Data writing process: first write to WAL, i.e. log structure, and then write to the cache structure in memory; when the data in memory meets the conditions for writing to disk, first write the data to the corresponding TSM storage structure in disk according to the original logic, and in the writing process obtain the unique ID of the TSM, TSM_ID1, minimum timestamp min_time1, maximum timestamp max_time1, and disk storage location information offset1;
[0068] Module M1.3: The query process based on timestamp: including 1) querying data with timestamp equal to a certain value; 2) querying data with timestamp within a certain range;
[0069] The module M1.2 further includes: searching in the outer index array whether there is an index node with TDMID TSM_ID1, using a binary search method;
[0070] If it exists, update the maximum and minimum time of the index node; if it does not exist, build a new index node based on the returned data and add the new index node to the corresponding position in the array;
[0071] The module M1.3 includes:
[0072] 1) The query condition is that the timestamp is equal to a certain value: When the query condition is that the timestamp is equal to a certain value, set the timestamp value to timestamp1, traverse the index array, and determine whether there is data that meets the conditions in the corresponding TSM based on the min_time and max_time of each index node. When the following conditions are met, the TSM storage file corresponding to the index meets the query condition:
[0073] min_time<=timestamp1<=max_time
[0074] According to the TSM unique ID information in the index node and the TSM storage location information, directly read the data in the corresponding TSM storage file and return it to the client;
[0075] 2) The query condition is the timestamp range:
[0076] When the query condition is a timestamp range, set the query timestamp range to min_time1, max_time1; traverse the index array, set the timestamp range of the traversed index nodes to min_time2, max_time2; when any of the following conditions is met, the found TSM meets the query condition of the timestamp range:
[0077] max_time1>min_time2&&max_time1 <max_time2
[0078] min_time1>min_time2&&min_time1 <max_time2
[0079] min_time1<min_time2&&max_time1> max_time2
[0080] Then, the qualified data is read from the found TSM file and returned to the client.
[0081] Preferably, the secondary index module for constructing the TSM file includes:
[0082] Module M2.1: Design a dictionary tree. The next layer of the root node is the TagKey node, which stores the value information of the TagKey in the database. The TagValue of each TagKey is constructed with the TagKey node as the parent node. The child nodes of the TagKey node are guaranteed to be dictionary ordered.
[0083] Module M2.2: Build a dictionary tree. After the data is written into the TSM file according to the writing process, it returns the relevant information written into the TSM file, including the unique ID of the TSM, TSM_ID1, and the data storage index information seriesIDList; at the same time, it returns the TagKey and TagValue information of the data, and builds a dictionary tree based on the TagKey and TagValue;
[0084] Module M2.3: Query with non-timestamp as query condition;
[0085] The module M2.2 constructs a dictionary tree according to TagKey and TagValue, including:
[0086] In the dictionary tree, start looking for TagKey in the child nodes of the root node.
[0087] If it does not exist, add a child node to the root node, the node_id of the child node is the current timestamp, the node_value is TagKey, and the key_list_address is empty;
[0088] If it exists, construct the path of TagValue in the child node of TagKey;
[0089] Among them, the last node of the TagValue path needs to store the corresponding data storage index information seriesIDList, that is, key_list_address = seriesIDList;
[0090] The query process with non-timestamp as the query condition in the module M2.3 includes:
[0091] Non-timestamp equal value query:
[0092] 1) Obtain the TagKey value of the non-timestamp;
[0093] 2) Obtain the corresponding TagValue value;
[0094] 3) Search in the trie. If there is a path corresponding to the TagKey and TagValue in the trie, and the key_list_address attribute of the next node of the path is not empty, then the seriesIDList corresponding to the TagKey and TagValue can be determined according to the corresponding key_list_address;
[0095] 4) Obtain the seriesKeyList according to the seriesIDList, so as to locate the specific data storage location;
[0096] Non-timestamp range query:
[0097] 1) Obtain the TagKey value of the non-timestamp;
[0098] 2) Obtain the corresponding TagValue value;
[0099] 3) Search in the trie. If there is a path corresponding to the TagKey and TagValue in the trie, set the last node as node1, and then execute step 5);
[0100] 4) If there is no path corresponding to the TagKey and TagValue in the trie, then search for the longest prefix of the TagKey and TagValue, set the last node as node2. If the query condition is tagKey < TagValue, search for the first node greater than TagValue in the full path of the children nodes of node2, and set it as node1; if the query condition is tagKey > TagValue, search for the last node less than TagValue in the full path of the children nodes of node2, and set it as node1; then execute step 5);
[0101] 5) If the query condition is tagKey < TagValue, all the nodes with non-empty key_list_address among all the nodes on the left of node1 and all the nodes on the TagValue path in the trie are qualified nodes;
[0102] 6) If the query condition is tagKey>TagValue, then all nodes to the right of node1 and all child nodes of node1 whose key_list_address is not empty are nodes that meet the condition;
[0103] 7) According to the key_list_address of the node that meets the conditions, obtain all seriesKeys, get the specific storage location of the data, read the data, and return it to the client.
[0104] Compared with the prior art, the present invention has the following beneficial effects:
[0105] 1) The present invention can effectively improve the query efficiency of timestamp range query statements in time series databases;
[0106] For queries with timestamp as the query condition: add an index in the outer layer of influxDB to record the timestamp interval of each storage file, namely minTime and maxTime. In queries with timestamp as the query condition, first locate the storage file through this index to avoid full table scan;
[0107] 2) The present invention can effectively improve the query efficiency of non-timestamp range query statements in time series databases;
[0108] For queries with non-timestamp fields as query conditions: add a dictionary tree to the TSI of influxDB to replace the original hash index structure, so that when performing range queries, all data can be quickly located through the dictionary tree without the need for sorting, avoiding multiple sorting processes during TSI construction and speeding up index construction;
[0109] 3) The present invention can utilize the construction of a dictionary tree to avoid the writing of data for the sorting of TagValue;
[0110] 4) The present invention can provide a solution for efficient query of large-scale time series data. BRIEF DESCRIPTION OF THE DRAWINGS
[0111] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:
[0112] Figure 1 Write a flowchart for data;
[0113] Figure 2 This is a schematic diagram of a timestamp range query condition satisfying condition 1;
[0114] Figure 3 This is a schematic diagram of a timestamp range query condition satisfying condition 2;
[0115] Figure 4 This is a diagram showing that the timestamp range query condition satisfies both conditions 1 and 2;
[0116] Figure 5 This is a schematic diagram of a timestamp range query condition satisfying condition 3;
[0117] Figure 6 Construct a diagram for the dictionary tree;
[0118] Figure 7 This is a diagram of the data reading process - non-timestamp equivalent query;
[0119] Figure 8 This is a diagram of the data reading process - non-timestamp range query. DETAILED DESCRIPTION
[0120] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several changes and improvements can also be made without departing from the concept of the present invention. These all belong to the protection scope of the present invention.
[0121] The embodiment of the present invention provides a method for optimizing the read performance of a time series database based on a multi-level index. The technical problems to be solved are reflected in the following aspects:
[0122] 1) Solve the problem that queries in massive time series data storage require scanning the entire table.
[0123] 2) Quickly locate whether there is data in the storage file that meets the non-timestamp query conditions.
[0124] 3) Provide solutions for efficient query of IoT time series data.
[0125] It mainly includes building secondary indexes for TSM files and optimizing queries with timestamps as query conditions; building dictionary tree indexes in TSI and optimizing range queries with non-timestamps as query conditions.
[0126] 1. The index blocks stored in the TSM file store the minimum and maximum time ranges of all the data blocks in it. In the query process based on the time range, the time range in the index block needs to be scanned, which involves index scanning of multiple TSM blocks. Adding a layer of index outside TSM to record the time ranges of different TSM blocks can directly locate the specific TSM storage structure that meets the conditions and avoid global scanning.
[0127] 2. The original Hash Index in TSI needs to be combined with the ordered storage of Tag Value to achieve fast positioning of range condition queries. However, the data of Tag Value is not predictable in advance, so it will cause multiple sorting of TagValue in TSI, resulting in certain memory and computing overhead. By constructing a dictionary tree and utilizing the orderliness of the dictionary tree itself, data that meets the conditions in the range query can be quickly located.
[0128] For the improved TSM and TSI structures, there will be corresponding changes in data writing and reading. When writing, you need to join the index building process, and when reading, you can quickly locate data based on the built index.
[0129] Reference Figure 1 As shown, the present invention specifically includes:
[0130] Steps for building the secondary index of TSM files: Build the timestamp index of all TSMs in the outer layer of TSM. For queries based on timestamp conditions, locate the storage files involved in the query conditions and obtain the storage address and offset of the files.
[0131] Steps for constructing a dictionary tree index in TSI: By constructing a dictionary tree and utilizing the orderliness of the dictionary tree itself, the data that meets the conditions in the range query is located.
[0132] First, the steps to build the secondary index of the TSM file include:
[0133] Step S1.1: Design the index structure: The entire index structure is an array consisting of index nodes; the index node contains: the unique ID of the TSM, the minimum time minTime of the TSM, the maximum time maxTime, and the location where the TSM is stored on the disk.
[0134] {
[0135] TSMID; / / Index unique id
[0136] minTime; / / Minimum time of the index corresponding to the TSM file
[0137] maxTime; / / The maximum time of the TSM file corresponding to the index
[0138] offset; / / TSM file storage location offset
[0139] }
[0140] In order to minimize the impact on write efficiency, the nodes in the index array are sorted according to the unique ID of TSM. In this way, when the index is updated later, a binary search can be performed based on the TSMID to quickly locate the index that needs to be modified.
[0141] Step S1.2: Data writing process: first write to WAL, that is, the log structure, and then write to the cache structure in the memory; when the data in the memory meets the conditions for writing to the disk, first write the data to the corresponding TSM storage structure in the disk according to the original logic, and in the writing process obtain the unique ID of the TSM, TSM_ID1, the minimum timestamp min_time1, the maximum timestamp max_time1, and the disk storage location information offset1.
[0142] In the outer index array, search for an index node with TDMID TSM_ID1 using binary search. If it exists, update the maximum and minimum time of the index node; if it does not exist, build a new index node based on the returned data and add the new index node to the corresponding position in the array.
[0143] Step S1.3: Queries based on timestamps can be divided into two types: 1) querying data whose timestamps are equal to a certain value; 2) querying data whose timestamps are within a certain range (queries greater than a certain timestamp can be converted to a time range from the timestamp to infinity; similarly, queries less than a certain timestamp can be converted to a range from the infinitesimal time to the timestamp).
[0144] The step S1.3 specifically includes: 1) the query condition is that the timestamp is equal to a certain value: when the query condition is that the timestamp is equal to a certain value, the timestamp value is set to timestamp1, the index array is traversed, and the corresponding TSM is judged whether there is data that meets the condition according to the min_time and max_time of each index node. When the following conditions are met, the TSM storage file corresponding to the index meets the query condition:
[0145] min_time<=timestamp1<=max_time
[0146] According to the TSM unique ID information in the index node and the storage location information of the TSM, the data in the corresponding TSM storage file is directly read and returned to the client.
[0147] 2) The query condition is the timestamp range:
[0148] When the query condition is a timestamp range, set the query timestamp range to min_time1, max_time1; traverse the index array, set the timestamp range of the traversed index nodes to min_time2, max_time2; when any of the following conditions is met, the found TSM meets the query condition of the timestamp range:
[0149] max_time1>min_time2&&max_time1 <max_time2
[0150] min_time1>min_time2&&min_time1 <max_time2
[0151] min_time1<min_time2&&max_time1> max_time2
[0152] For detailed range examples, see Figure 2 , Figure 3 , Figure 4 and Figure 5 shown.
[0153] Then, the qualified data is read from the found TSM file and returned to the client.
[0154] Secondly, refer to Figure 6 As shown, the steps of constructing the dictionary tree index in TSI specifically include:
[0155] Queries in time series databases include not only queries for timestamps, but also queries for non-timestamps, which account for a large part. Regarding the construction of the inverted index in TSI and its existing Hash index, the tag value needs to be ordered so that range queries for non-timestamp query conditions can be efficiently performed. When writing data, the tag value is not predictable, so multiple sorting operations will be involved. Therefore, this step designs a dictionary tree to replace the original Hash index, and quickly solves the problem of non-timestamp range queries based on the orderliness of the dictionary tree itself.
[0156] Step S2.1: Design a dictionary tree. The node structure of the dictionary tree is as follows:
[0157] {
[0158] node_id; / / unique id of node
[0159] node_value; / / node value
[0160] key_list_address; / / If node is the end point of tagvalue, key_list_address is the address of the corresponding seriesIDList; otherwise key_list_address is empty.
[0161] child_node_list; / / array structure, array elements are Node nodes, representing child nodes of Node nodes, empty if there are no child nodes
[0162] }
[0163] In the process of constructing the dictionary tree, the next layer of the root node is the TagKey node, which stores the value information of the TagKey in the database. The TagValue of each TagKey is constructed with the TagKey node as the parent node, and the child nodes of the TagKey node are guaranteed to be dictionary-ordered.
[0164] Step S2.2: Construct a dictionary tree. After the data is written into the TSM file according to the writing process, the relevant information written into the TSM file is returned, including the unique ID of the TSM, TSM_ID1, and the data storage index information seriesIDList; at the same time, the TagKey and TagValue information of the data are returned, and the dictionary tree is constructed according to the TagKey and TagValue:
[0165] In the dictionary tree, start looking for TagKey in the child nodes of the root node.
[0166] If it does not exist, add a child node to the root node, the node_id of the child node is the current timestamp, the node_value is TagKey, and the key_list_address is empty;
[0167] If it exists, construct the path of TagValue in the child node of TagKey;
[0168] The last node of the TagValue path needs to store the corresponding data storage index information seriesIDList, that is, key_list_address=seriesIDList.
[0169] Step S2.3: Query using non-timestamp as query condition.
[0170] Specifically include:
[0171] Reference Figure 7 As shown, non-timestamp equality query:
[0172] 1) Get the non-timestamp TagKey value;
[0173] 2) Get the corresponding TagValue value;
[0174] 3) Search in the dictionary tree. If the path corresponding to TagKey and TagValue exists in the dictionary tree, and the key_list_address attribute after the path is not empty, then the seriesIDList corresponding to the TagKey and TagValue can be determined according to the corresponding key_list_address;
[0175] 4) Obtain the seriesKeyList according to the seriesIDList, so as to locate the specific data storage location;
[0176] Refer to Figure 8 As shown, non-timestamp range query:
[0177] 1) Obtain the TagKey value of non-timestamp;
[0178] 2) Obtain the corresponding TagValue value;
[0179] 3) Search in the trie. If there is a path corresponding to TagKey and TagValue in the trie, set the last node as node1, and then execute step 5);
[0180] 4) If there is no path corresponding to TagKey and TagValue in the trie, then find the longest prefix of TagKey and TagValue, set the last node as node2. If the query condition is tagKey < TagValue, find the first node greater than TagValue in the full path of the children nodes of node2 (full path: the full path of node refers to the string composed of the node_value of all nodes from the child node of TagKey to this node). Set it as node1; if the query condition is tagKey > TagValue, find the last node less than TagValue in the full path of the children nodes of node2, set it as node1; then execute step 5);
[0181] 5) If the query condition is tagKey < TagValue, all nodes on the left of node1 in the trie and all nodes on the TagValue path with non-empty key_list_address are qualified nodes;
[0182] 6) If the query condition is tagKey > TagValue, all nodes on the right of node1 and all child nodes of node1 with non-empty key_list_address are qualified nodes;
[0183] 7) According to the key_list_address of the nodes that meet the conditions, obtain all seriesKeys, get the specific data storage location, read the data, and return it to the client.
[0184] The embodiment of the present invention provides a method and system for optimizing the reading performance of a time series database based on multi-level indexing. In the process of large-scale IoT sensing device log collection and storage, the time series database is improved by using the method in this article, which can improve the query efficiency of data and quickly return query results according to the query conditions. In the monitoring system of a large server cluster, for the performance and operation data of large-scale servers generated in real time, the database structure improved by this method can be used for efficient data writing and data reading. In scenarios where aggregate analysis is required, the built-in drama and algorithm of the database can also be used to call the reading method of this method to improve the analysis speed.
[0185] Those skilled in the art know that, in addition to realizing the system and its various devices, modules, and units provided by the present invention in a purely computer-readable program code, it is entirely possible to realize the same functions in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, the system and its various devices, modules, and units provided by the present invention can be considered as a hardware component, and the devices, modules, and units included therein for realizing various functions can also be regarded as structures within the hardware component; the devices, modules, and units for realizing various functions can also be regarded as both software modules for realizing the method and structures within the hardware component.
[0186] The above describes the specific embodiments of the present invention. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which does not affect the essence of the present invention. In the absence of conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.
Claims
1. A method for optimizing the read performance of a time series database based on multi-level indexes, characterized in that: include: Steps for building a secondary index for TSM files: Build a timestamp index for all TSMs in the outer layer of TSM. For queries based on timestamp conditions, locate the storage files involved in the query conditions and obtain the storage address and offset of the files. Steps for constructing the dictionary tree index in TSI: by constructing the dictionary tree and taking advantage of the orderliness of the dictionary tree itself, the data that meets the conditions in the range query is located; The step of constructing a dictionary tree index in TSI includes: Step S2.1: Design a dictionary tree. The next layer of the root node is the TagKey node, which stores the value information of the TagKey in the database. The TagValue of each TagKey is constructed with the TagKey node as the parent node. The child nodes of the TagKey node are guaranteed to be dictionary ordered. Step S2.2: Construct a dictionary tree. After the data is written into the TSM file according to the writing process, the relevant information written into the TSM file is returned, including the unique ID of the TSM, TSM_ID1, and the data storage index information seriesIDList; at the same time, the TagKey and TagValue information of the data are returned, and the dictionary tree is constructed according to the TagKey and TagValue; Step S2.3: Query using non-timestamp as query condition.
2. According to claim 1, the method for optimizing the read performance of a time series database based on multi-level indexing is characterized in that: The steps of constructing the secondary index of the TSM file include: Step S1.1: Design the index structure: The entire index structure is an array consisting of index nodes; the index node contains: the unique ID of the TSM, the minimum time minTime of the TSM, the maximum time maxTime, and the location where the TSM is stored on the disk; the nodes in the index array are sorted according to the unique ID of the TSM; Step S1.2: Data writing process: first write to WAL, i.e. log structure, and then write to the cache structure in memory; when the data in memory meets the conditions for writing to disk, first write the data to the corresponding TSM storage structure in disk according to the original logic, and in the writing process obtain the unique ID of the TSM, TSM_ID1, minimum timestamp min_time1, maximum timestamp max_time1, and disk storage location information offset1; Step S1.3: The query process based on the timestamp condition includes 1) querying the data whose timestamp is equal to a certain value; 2) querying the data whose timestamp is within a certain range.
3. According to claim 2, the method for optimizing the read performance of a time series database based on multi-level indexing is characterized in that: The step S1.2 further includes: searching in the outer index array whether there is an index node with a TDMID of TSM_ID1, using a binary search method; If it exists, update the maximum and minimum time of the index node; if it does not exist, build a new index node based on the returned data and add the new index node to the corresponding position in the array.
4. The method for optimizing read performance of a time series database based on multi-level indexes according to claim 2 is characterized in that: The step S1.3 comprises: 1) The query condition is that the timestamp is equal to a certain value: When the query condition is that the timestamp is equal to a certain value, set the timestamp value to timestamp1, traverse the index array, and determine whether there is data that meets the conditions in the corresponding TSM based on the min_time and max_time of each index node. When the following conditions are met, the TSM storage file corresponding to the index meets the query condition: min_time <= timestamp1 <= max_time According to the TSM unique ID information in the index node and the TSM storage location information, directly read the data in the corresponding TSM storage file and return it to the client; 2) The query condition is the timestamp range: When the query condition is a timestamp range, set the query timestamp range to min_time1, max_time1; traverse the index array, set the timestamp range of the traversed index nodes to min_time2, max_time2; when any of the following conditions is met, the found TSM meets the query condition of the timestamp range: max_time1>min_time2 && max_time1 <max_time2 min_time1>min_time2 && min_time1 <max_time2 min_time1<min_time2 && max_time1> max_time2 Then, the qualified data is read from the found TSM file and returned to the client.
5. According to claim 1, the method for optimizing read performance of a time series database based on multi-level indexing is characterized in that: The step S2.2 constructs a dictionary tree according to TagKey and TagValue, including: In the dictionary tree, start looking for TagKey in the child nodes of the root node. If it does not exist, add a child node to the root node, the node_id of the child node is the current timestamp, the node_value is TagKey, and the key_list_address is empty; If it exists, construct the path of TagValue in the child node of TagKey; Among them, the last node of the TagValue path needs to store the corresponding data storage index information seriesIDList, that is, key_list_address = seriesIDList.
6. The method for optimizing read performance of a time series database based on multi-level indexes according to claim 1, characterized in that: The query process using non-timestamp as query condition in step S2.3 includes: Non-timestamp equality query: 1) Get the non-timestamp TagKey value; 2) Get the corresponding TagValue value; 3) Search in the dictionary tree. If the path corresponding to TagKey and TagValue exists in the dictionary tree, and the key_list_address attribute after the path is not empty, then the seriesIDList corresponding to the TagKey and TagValue can be determined according to the corresponding key_list_address; 4) Obtain the seriesKeyList according to the seriesIDList, so as to locate the specific data storage location; Non-timestamp range query: 1) Obtain the TagKey value of non-timestamp; 2) Obtain the corresponding TagValue value; 3) Search in the trie. If there is a path corresponding to TagKey and TagValue in the trie, set the last node as node1, and then execute step 5); 4) If there is no path corresponding to TagKey and TagValue in the trie, then find the longest prefix of TagKey and TagValue, set the last node as node2. If the query condition is tagKey < TagValue, find the first node greater than TagValue in the full path of the children nodes of node2, and set it as node1; if the query condition is tagKey > TagValue, find the last node less than TagValue in the full path of the children nodes of node2, and set it as node1; then execute step 5); 5) If the query condition is tagKey < TagValue, all the nodes on the left side of node1 in the trie and all the nodes with non-empty key_list_address on the TagValue path are the qualified nodes; 6) If the query condition is tagKey > TagValue, all the nodes on the right side of node1 and all the children nodes of node1 with non-empty key_list_address are the qualified nodes; 7) According to the key_list_address of the nodes that meet the conditions, obtain all the seriesKeys, get the specific data storage location, read the data, and return it to the client.
7. A time series database read performance optimization system based on multi-level index, characterized in that: Including: Construct a secondary index module for the TSM file: Build a timestamp index for all TSMs on the outer layer of the TSM. For queries with timestamp conditions, locate the storage file involved in the query condition, and obtain the storage address and offset of the file; Construct a trie index module in the TSI: By constructing a trie, use the orderliness of the trie itself to locate the data that meets the conditions in the range query; The said constructing a secondary index module for the TSM file includes: Module M2.1: Design a trie. The next layer of the root node is the TagKey node, which stores the value information of TagKey in the database. Each TagValue of TagKey is constructed with the TagKey node as the parent node, and the children nodes of the TagKey node are guaranteed to be lexicographically ordered; Module M2.2: Build a dictionary tree. After the data is written into the TSM file according to the writing process, it returns the relevant information written into the TSM file, including the unique ID of the TSM, TSM_ID1, and the data storage index information seriesIDList; at the same time, it returns the TagKey and TagValue information of the data, and builds a dictionary tree based on the TagKey and TagValue; Module M2.3: Query using non-timestamp query conditions.
8. The time series database read performance optimization system based on multi-level index according to claim 7, characterized in that: The secondary index module for constructing TSM files includes: Module M1.1: Design index structure: The entire index structure is an array consisting of index nodes; the index node contains: TSM's unique ID, TSM's minimum time minTime, maximum time maxTime, and TSM's storage location on disk; the nodes in the index array are sorted according to TSM's unique ID; Module M1.2: Data writing process: first write to WAL, i.e. log structure, and then write to the cache structure in memory; when the data in memory meets the conditions for writing to disk, first write the data to the corresponding TSM storage structure in disk according to the original logic, and in the writing process obtain the unique ID of the TSM, TSM_ID1, minimum timestamp min_time1, maximum timestamp max_time1, and disk storage location information offset1; Module M1.3: The query process based on timestamp: including 1) querying data with timestamp equal to a certain value; 2) querying data with timestamp within a certain range; The module M1.2 further includes: searching in the outer index array whether there is an index node with TDMID TSM_ID1, using binary search; If it exists, update the maximum and minimum time of the index node; if it does not exist, build a new index node based on the returned data and add the new index node to the corresponding position in the array; The module M1.3 includes: 1) The query condition is that the timestamp is equal to a certain value: When the query condition is that the timestamp is equal to a certain value, set the timestamp value to timestamp1, traverse the index array, and determine whether there is data that meets the conditions in the corresponding TSM based on the min_time and max_time of each index node. When the following conditions are met, the TSM storage file corresponding to the index meets the query condition: min_time <= timestamp1 <= max_time According to the TSM unique ID information in the index node and the TSM storage location information, directly read the data in the corresponding TSM storage file and return it to the client; 2) The query condition is the timestamp range: When the query condition is a timestamp range, set the query timestamp range to min_time1, max_time1; traverse the index array, set the timestamp range of the traversed index nodes to min_time2, max_time2; when any of the following conditions is met, the found TSM meets the query condition of the timestamp range: max_time1>min_time2 && max_time1 <max_time2 min_time1>min_time2 && min_time1 <max_time2 min_time1<min_time2 && max_time1> max_time2 Then, the qualified data is read from the found TSM file and returned to the client.
9. The time series database read performance optimization system based on multi-level index according to claim 7, characterized in that: The module M2.2 constructs a dictionary tree according to TagKey and TagValue, including: In the dictionary tree, start looking for TagKey in the child nodes of the root node. If it does not exist, add a child node to the root node, the node_id of the child node is the current timestamp, the node_value is TagKey, and the key_list_address is empty; If it exists, construct the path of TagValue in the child node of TagKey; Among them, the last node of the TagValue path needs to store the corresponding data storage index information seriesIDList, that is, key_list_address = seriesIDList; The query process using non-timestamp as query condition in the module M2.3 includes: Non-timestamp equality query: 1) Get the non-timestamp TagKey value; 2) Get the corresponding TagValue value; 3) Search in the dictionary tree. If the path corresponding to TagKey and TagValue exists in the dictionary tree, and the key_list_address attribute after the path is not empty, then the seriesIDList corresponding to the TagKey and TagValue can be determined according to the corresponding key_list_address; 4) Get seriesKeyList according to seriesIDList to locate the specific data storage location; Non-timestamp range query: 1) Get the non-timestamp TagKey value; 2) Get the corresponding TagValue value; 3) Search in the dictionary tree. If there is a path corresponding to TagKey and TagValue in the dictionary tree, set the last node to node1, and then execute step 5); 4) If there is no path corresponding to TagKey and TagValue in the trie, find the longest prefix of TagKey and TagValue. Let the last node be node2. If the query condition is tagKey < TagValue, find the first node greater than TagValue in the full path of the children of node2, and set it as node1; if the query condition is tagKey > TagValue, find the last node less than TagValue in the full path of the children of node2, and set it as node1; then perform step 5). 5) If the query condition is tagKey < TagValue, all the nodes on the left of node1 in the trie and all the nodes with non-empty key_list_address on the TagValue path are the qualified nodes. 6) If the query condition is tagKey > TagValue, all the nodes on the right of node1 and all the children of node1 with non-empty key_list_address are the qualified nodes. 7) According to the key_list_address of the nodes that meet the conditions, obtain all the seriesKeys, get the specific storage location of the data, read the data, and return it to the client.
Citation Information
Patent Citations
A method of read-optimized in-memory database t-tree index structure
CN103902693B
A method and apparatus for optimizing a database
CN108268546B
Global secondary indexing method and device for distributed database
CN113868251A
Time series data set loading method and device, storage medium and electronic equipment
CN110580253A
Data index construction method and device, electronic equipment and storage medium
CN113254451A
Cited By
AI Internet of Things data intelligent analysis method
CN122534013A
