Time series data indexing method and device

Abstract

The invention relates to the technical field of databases, and particularly provides a time series data indexing method and device.An index entry of a time series data indexing structure comprises all data position statistical information in an interval range, and the content comprises the first time TS of the interval range; (2) the logic offset P of the first record in the storage in the interval range; (3) recording the number C of data in the interval; (4) an average time interval I of data in the interval; the end time TE of the interval range is obtained according to (TS + (C-1) * I), so that storage is not needed. Compared with the prior art, the method has the advantages that the storage space of the index can be reduced, and the data writing, searching, updating and deleting efficiency of the index can be improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and specifically provides a time series data indexing method and device. Background Art

[0002] With the development of IoT technology, the number of IoT devices and applications has grown rapidly, and the demand for efficient storage, management and analysis of the massive data generated by IoT devices has also increased.

[0003] Compared with the data processed by traditional databases, the data generated by IoT devices and applications has obvious time series data processing characteristics, such as:

[0004] (1) Data always contains timestamp attributes and is usually generated frequently and stably in chronological order;

[0005] (2) Huge amounts of data. For example, a sensor generates about 1.4M data in a day. A factory with tens of millions of sensors can generate 500T of data in a year. In emerging industries such as the Internet of Vehicles and digital energy, managing hundreds of millions or even billions of devices is becoming a trend.

[0006] (3) Data is updated and deleted less frequently;

[0007] (4) Queries usually use time as a filtering condition, and aggregate analysis is usually performed on the device and time dimensions.

[0008] Since traditional data processing technologies are difficult to efficiently store and analyze such large-scale data, time series data processing has emerged as a specialized data processing technology and has developed rapidly. In addition to the Internet of Things industry, time series data processing technology has also been widely used in systems monitoring, stock trading and other industries.

[0009] One of the biggest challenges in time series data processing is data index design. Because time series data is huge and has high requirements for write performance, traditional index forms such as B+ tree index and hash index will occupy huge storage space and greatly increase the overhead of write and delete operations, and cannot meet the requirements of time series data processing. Some time series databases use LSM trees to store data in chronological order, but when processing massive time series data, the LSM tree structure also has serious read amplification and write amplification problems, which affects read and write performance. Summary of the invention

[0010] The present invention aims to solve the above-mentioned deficiencies of the prior art and provides a time series data indexing method with strong practicality.

[0011] A further technical task of the present invention is to provide a time series data indexing device that is reasonably designed, safe and applicable.

[0012] The technical solution adopted by the present invention to solve the technical problem is:

[0013] A time series data indexing method, wherein the index items of the time series data index structure include all data position statistics within a range, including:

[0014] (1) The time TS at which the interval starts;

[0015] (2) The logical offset P of the first record in the range;

[0016] (3) The number of data records in the interval C;

[0017] (4) the average time interval I of the data within the interval;

[0018] The end time TE of the interval range is obtained by (TS+(C-1)*I), so it does not need to be stored.

[0019] Furthermore, the process of finding the location of record R1 is as follows:

[0020] A1. Use the tree structure search algorithm to find "TS is less than the time T of the record to be searched R1" R1 "In the index items of ", the index item E1 with the largest start time of the interval range;

[0021] A2. If E1 does not exist, then record R1 is earlier than all records in the index, that is, it has not been covered by the index;

[0022] A3. If E1 exists, check whether E1 covers T R1 .

[0023] Furthermore, in step A3, it further includes:

[0024] A3-1: If (E1 range end time TE E1 +E1 data average time interval I E1 *Fault tolerance ratio)>T R1 , then E1 covers the time of R1;

[0025] (a) Find the storage offset P R2 =(T R1 -TS E1 ) / I E1 +P E1 (Logical offset of the first record of E1 in storage) corresponding to record R2;

[0026] (b) If the time of R2 is equal to the time of R1, the record is found;

[0027] (c) If the time of R2 is less than the time of R1, from P R2The position is searched backward until a record with the same time as R1 is found;

[0028] (d) If the time of R2 is greater than the time of R1, from P R2 Search forward until a record with the same time as R1 is found;

[0029] A3-2: Otherwise, E1 does not cover the time of R1, that is, R1 is not recorded in the index.

[0030] Furthermore, the index insertion process is as follows:

[0031] B1. When a new record R1 is written into the data table, the time T corresponding to the record needs to be found in the index. R1 Whether it has been covered by an index item E1;

[0032] B2. If the position of R1 has been covered by an index item, it means that the addition of R1 will change the time interval rule of the original index item, and the index item E1 covering the record needs to be split;

[0033] B3. If the R1 position is not covered, find the interval range starting time less than or equal to T R1 The index item E2 with the largest interval start time among the index items;

[0034] Furthermore, in step B2, it further includes:

[0035] B2-1. If the record can be merged into the previous index item after the split of E1, merge the record into the previous index item;

[0036] B2-2. If the record cannot be merged into the previous index item after E1 split, a new index item is created. The process of adding new index items adopts the process of adding nodes in the traditional tree structure;

[0037] In step B3, further comprising:

[0038] B3-1. If R1 can be merged into E2, merge the record into E2;

[0039] B3-2. If R1 cannot be merged into E2, a new index item is created. The process of adding a new index item adopts the process of adding nodes in a traditional tree structure.

[0040] Furthermore, the merging process of record R1 and the previous index item EP is as follows:

[0041] C1. If the number of EP data records in the previous index item is 1, it can be merged;

[0042] C2, if the position P of R1 is recorded R1 The first record position P that is not equal to EP EP+ EP data record quantity C EP , it means that the record R1 is not continuous with the record in EP in storage space and cannot be merged, so exit;

[0043] C3, if the previous index item (EP range end time TE EP +EP data average time interval I EP * fault tolerance ratio)> the timestamp T of the record R1 , then continue processing, otherwise exit;

[0044] C4. Calculate the average time interval I of the merged index item EC EC =(T R1 -The time TS at which the interval range of the merged index item starts EC ) / (Number of data records of the merged index item C EC -1), if the time interval is less than the preset threshold, the merge can be performed; otherwise, exit;

[0045] C5, the number of data records of the merged index item C EC =C EP +1;

[0046] C6, average time interval of the merged index items I EC =(new record timestamp T R1 -The time TS at which the interval range of the merged index item starts EC ) / (C EC -1).

[0047] Furthermore, the splitting process of index item E is as follows:

[0048] D1. Determine the time point T at which the index item is split;

[0049] D2. Find the logical offset in the storage file corresponding to time point T, using sequence number P T represent;

[0050] D3. Update the original index item ES;

[0051] include:

[0052] D3-1, the number of data records in the original index item interval C ES =P T -The logical offset P of the first record in the storage E ;

[0053] D3-2, the average time interval of data within the original index item interval I ES =(P T The time of the location record - the start time of the interval range of the original index item TSE ) / (C ES -1);

[0054] D4. New index item EN is created;

[0055] include:

[0056] D4-1. Find P T The next record P T’ ;

[0057] D4-2. The time TS at which the new index entry range starts EN =P T’ The time of the corresponding record;

[0058] D4-3, the number of data records in the new index item interval C EN =C E -C ES ;

[0059] D4-4, average time interval of data within the new index item interval I EN = (the time TE at which the original index entry's interval range ends E -P T’ Location record time) / (C EN -1).

[0060] Furthermore, the index deletion process is as follows:

[0061] E1. When a record R is deleted, it is necessary to find the location of the time corresponding to the record in the index;

[0062] E2, record the index item of R time split covering R;

[0063] E3. For the first item E in the classified index item:

[0064] The number of data records in the index item E interval C E =C E -1, if the number of data records in E is 0, delete the index entry.

[0065] A time series data indexing device comprises: at least one memory and at least one processor;

[0066] The at least one memory is used to store a machine-readable program;

[0067] The at least one processor is used to call the machine-readable program to execute a time series data indexing method.

[0068] Compared with the prior art, the time series data indexing method and device of the present invention have the following outstanding beneficial effects:

[0069] The present invention proposes to record data statistical information within the key value range rather than the original data in the index item, thereby significantly reducing the number of index items that need to be stored in the index, thereby reducing the storage space of the index and improving the efficiency of index data writing, searching, updating and deleting. BRIEF DESCRIPTION OF THE DRAWINGS

[0070] In order to more clearly illustrate the embodiments of the present invention 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 some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0071] Attached Figure 1 It is a flowchart of indexing and searching records in a time series data indexing method;

[0072] Attached Figure 2 It is a flowchart of index inserting records in a time series data indexing method;

[0073] Attached Figure 3 It is a schematic diagram of the index structure when indexing in a time series data indexing method;

[0074] Attached Figure 4 This is an example diagram of merging inserted data with existing index items in a time series data indexing method;

[0075] Attached Figure 5 This is an example diagram of a time series data indexing method where inserted data cannot be merged with existing index items;

[0076] Attached Figure 6 It is a schematic diagram of the index structure when the traditional B+ tree index is used in the time series data indexing method. DETAILED DESCRIPTION

[0077] In order to enable those skilled in the art to better understand the solution of the present invention, the present invention is further described in detail below in conjunction with specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0078] A best embodiment is given below:

[0079] In this embodiment, a time series data indexing method is provided, in which the index items of the time series data index structure contain statistical information of all data locations within a range, rather than the key value (Key) and the physical location (Position) pair of each record recorded in the index items of the traditional database B+ tree index.

[0080] For example, assume that the target time series data table contains the following data:

[0081]

[0082]

[0083] The first column in the table is the data collection time, the second column is the collection value, and the third column is not the data in the table, but the logical address of the data in the storage file (the starting address of the storage file is 0). Since this index structure needs to be applied to the data storage structure that writes data in the form of appending, the logical address recorded in the storage file corresponds to the order in which the data is written.

[0084] The data in the above table is generated in the following scenario:

[0085] 1. A device starts collecting temperature information from "2024-08-01 09:00:00.000" and writes it to the data table, i.e., record address 0-9 in the data storage. The device collects data once per second, but due to device accuracy issues, the collection interval may be slightly higher or slightly lower than 1 second.

[0086] 2. The device fails for some reason and stops collecting data after "2024-08-01 09:00:09.003" and resumes collecting data at "2024-08-02 08:00:00.000" the next day. The data written after resuming collection are the 10th to 13th records in the data storage.

[0087] 3. After "2024-08-02 08:00:03.001", a part of the data ("2024-08-01 10:10:03.002" to "2024-08-01 10:10:18.000") is added. Although the added data is collected earlier than the written data, its writing time is later, so it is stored as the 14th to 29th records.

[0088] like Figure 6 As shown in the figure, if the B+ tree index form of the traditional database is used, the timestamp column index corresponding to the data in the above table will have 30 index items.

[0089] like Figure 3As shown, if the new index form proposed by the present invention is adopted, the timestamp column index corresponding to the data in the above table will have only 3 index items. In actual scenarios, due to the huge amount of time series data and the fact that it is generated sequentially and stably in most cases, the ratio of the number of index items of the new index proposed by the present invention to the number of index items of the B+ tree index or hash index of the traditional database will be even greater (less than one ten-thousandth, or even one millionth).

[0090] In the index structure proposed by the present invention, the index item represents information about a time interval. Its contents include:

[0091] (1) The time TS at which the interval starts;

[0092] (2) The logical offset P of the first record in the range;

[0093] (3) The number of data records in the interval C;

[0094] (4) the average time interval I of the data within the interval;

[0095] The end time TE of the interval range is obtained by (TS+(C-1)*I), so it does not need to be stored.

[0096] like Figure 1 As shown, the process of searching the index structure for the location of record R1 is as follows:

[0097] A1. Use the tree structure search algorithm to find "TS is less than the time T of the record to be searched R1" R1 "In the index items of ", the index item E1 with the largest start time of the interval range;

[0098] A2. If E1 does not exist, then record R1 is earlier than all records in the index, that is, it has not been covered by the index;

[0099] A3. If E1 exists, check whether E1 covers T R1 ;

[0100] Further including:

[0101] A3-1: If (E1 range end time TE E1 +E1 data average time interval I E1 *Fault tolerance ratio)>T R1 , then E1 covers the time of R1;

[0102] (a) Find the storage offset P R2 =(T R1 -TS E1 ) / I E1 +P E1(Logical offset of the first record of E1 in storage) corresponding to record R2;

[0103] (b) If the time of R2 is equal to the time of R1, the record is found;

[0104] (c) If the time of R2 is less than the time of R1, from P R2 The position is searched backward until a record with the same time as R1 is found;

[0105] (d) If the time of R2 is greater than the time of R1, from P R2 Search forward until a record with the same time as R1 is found;

[0106] A3-2: Otherwise, E1 does not cover the time of R1, that is, R1 is not recorded in the index.

[0107] In the index structure proposed by the present invention, the data within the time interval corresponding to the index item is continuous and ordered in time and in storage space. Therefore, the index needs to ensure this characteristic of the index item when writing, updating and deleting data.

[0108] like Figure 2 As shown, the index insertion process is as follows:

[0109] B1. When a new record R1 is written into the data table, the time T corresponding to the record needs to be found in the index. R1 Whether it has been covered by an index item E1;

[0110] B2. If the position of R1 has been covered by an index item, it means that the addition of R1 will change the time interval rule of the original index item, and the index item E1 covering the record needs to be split;

[0111] Further including:

[0112] B2-1. If the record can be merged into the previous index item after the split of E1, merge the record into the previous index item;

[0113] B2-2. If the record cannot be merged into the previous index item after E1 split, a new index item is created. The process of adding new index items adopts the process of adding nodes in the traditional tree structure;

[0114] B3. If the R1 position is not covered, find the interval range starting time less than or equal to T R1 The index item E2 with the largest interval start time among the index items;

[0115] Further including:

[0116] B3-1. If R1 can be merged into E2, merge the record into E2;

[0117] B3-2. If R1 cannot be merged into E2, a new index item is created. The process of adding a new index item adopts the process of adding nodes in a traditional tree structure.

[0118] The merging process of record R1 and the previous index item EP is as follows:

[0119] C1. If the number of EP data records in the previous index item is 1, it can be merged;

[0120] C2, if the position P of R1 is recorded R1 The first record position P that is not equal to EP EP + EP data record quantity C EP , it means that the record R1 and the record in EP are not continuous in storage space and cannot be merged (such as Figure 5 As shown), exit;

[0121] C3, if the previous index item (EP range end time TE EP +EP data average time interval I EP * fault tolerance ratio)> the timestamp T of the record R1 , then continue processing, otherwise exit;

[0122] C4. Calculate the average time interval I of the merged index item EC EC =(T R1 -The time TS at which the interval range of the merged index item starts EC ) / (Number of data records of the merged index item C EC -1), if the time interval is less than the preset threshold, then it can be merged (such as Figure 4 otherwise exit;

[0123] C5, the number of data records of the merged index item C EC =C EP +1;

[0124] C6, average time interval of the merged index items I EC =(new record timestamp T R1 -The time TS at which the interval range of the merged index item starts EC ) / (C EC -1).

[0125] The splitting process of index item E is as follows:

[0126] D1. Determine the time point T at which the index item is split;

[0127] D2. Find the logical offset in the storage file corresponding to time point T, using sequence number P T represent;

[0128] D3. Update the original index item ES;

[0129] include:

[0130] D3-1, the number of data records in the original index item interval C ES =P T -The logical offset P of the first record in the storage E ;

[0131] D3-2, the average time interval of data within the original index item interval I ES =(P T The time of the location record - the time TS at which the interval range of the original index item starts E ) / (C ES -1);

[0132] D4. New index item EN is created;

[0133] include:

[0134] D4-1. Find P T The next record P T’ ;

[0135] D4-2. The time TS at which the new index entry range starts EN =P T’ The time of the corresponding record;

[0136] D4-3, the number of data records in the new index item interval C EN =C E -C ES ;

[0137] D4-4, average time interval of data within the new index item interval I EN = (the time TE at which the original index entry's interval range ends E -P T’ Location record time) / (C EN -1).

[0138] The process of deleting an index is as follows:

[0139] E1. When a record R is deleted, it is necessary to find the location of the time corresponding to the record in the index;

[0140] E2, record the index item of R time split covering R;

[0141] E3. For the first item E in the classified index item:

[0142] The number of data records in the index item E interval C E =C E-1, if the number of data records in E is 0, delete the index entry.

[0143] Data updates based on timestamps do not affect data distribution, and data updates do not require index updates.

[0144] Based on the above method, a time series data indexing device in this embodiment includes: at least one memory and at least one processor;

[0145] The at least one memory is used to store a machine-readable program;

[0146] The at least one processor is used to call the machine-readable program to execute a time series data indexing method.

[0147] The above-mentioned specific implementations are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above-mentioned specific implementations. Any technical solutions that comply with the claims of the present invention and any appropriate changes or substitutions made by ordinary technicians in the relevant technical field shall fall within the patent protection scope of the present invention.

[0148] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A time series data indexing method, characterized in that: The index items of the time series data index structure include all data location statistics within a range, including: (1) The time TS at which the interval starts; (2) The logical offset P of the first record in the range; (3) The number of data records in the interval C; (4) the average time interval I of the data within the interval; The end time TE of the interval range is obtained by (TS+(C-1)*I), so it does not need to be stored.

2. A time series data indexing method according to claim 1, characterized in that: The process of finding the location of record R1 is as follows: A1. Use the tree structure search algorithm to find "TS is less than the time T of the record to be searched R1" R1 "In the index items of ", the index item E1 with the largest start time of the interval range; A2. If E1 does not exist, then record R1 is earlier than all records in the index, that is, it has not been covered by the index; A3. If E1 exists, check whether E1 covers T R1 .

3. A time series data indexing method according to claim 2, characterized in that: In step A3, further comprising: A3-1: If (E1 range end time TE E1 +E1 data average time interval I E1 *Tolerance ratio)>T R1 , then E1 covers the time of R1; (a) Find the storage offset P R2 =(T R1 -TS E1 ) / I E1 +P E1 (Logical offset of the first record of E1 in storage) corresponding to record R2; (b) If the time of R2 is equal to the time of R1, the record is found; (c) If the time of R2 is less than the time of R1, from P R2 The position is searched backward until a record with the same time as R1 is found; (d) If the time of R2 is greater than the time of R1, from P R2 Search forward until a record with the same time as R1 is found; A3-2: Otherwise, E1 does not cover the time of R1, that is, R1 is not recorded in the index.

4. A time series data indexing method according to claim 3, characterized in that: The index insertion process is as follows: B1. When a new record R1 is written into the data table, the time T corresponding to the record needs to be found in the index. R1 Whether it has been covered by an index item E1; B2. If the position of R1 has been covered by an index item, it means that the addition of R1 will change the time interval rule of the original index item, and the index item E1 covering the record needs to be split; B3. If the R1 position is not covered, find the interval range starting time less than or equal to T R1 The index item E2 with the largest start time of the interval range among the index items.

5. A time series data indexing method according to claim 4, characterized in that: In step B2, further comprising: B2-1. If the record can be merged into the previous index item after the split of E1, merge the record into the previous index item; B2-2. If the record cannot be merged into the previous index item after E1 split, a new index item is created. The process of adding new index items adopts the process of adding nodes in the traditional tree structure; In step B3, further comprising: B3-1. If R1 can be merged into E2, merge the record into E2; B3-2. If R1 cannot be merged into E2, a new index item is created. The process of adding a new index item adopts the process of adding nodes in a traditional tree structure.

6. A time series data indexing method according to claim 5, characterized in that: The merging process of record R1 and the previous index item EP is as follows: C1. If the number of EP data records in the previous index item is 1, it can be merged; C2, if the position P of R1 is recorded R1 The first record position P that is not equal to EP EP + EP data record quantity C EP , it means that the record R1 is not continuous with the record in EP in storage space and cannot be merged, so exit; C3, if the previous index item (EP range end time TE EP +EP data average time interval I EP * fault tolerance ratio)> the timestamp T of the record R1 , then continue processing, otherwise exit; C4. Calculate the average time interval I of the merged index item EC EC =(T R1 -The time TS at which the interval range of the merged index item starts EC ) / (Number of data records of the merged index item C EC -1), if the time interval is less than the preset threshold, the merge can be performed; otherwise, exit; C5, the number of data records of the merged index item C EC =C EP +1; C6, average time interval of the merged index items I EC =(new record timestamp T R1 -The time TS at which the interval range of the merged index item starts EC ) / (C EC -1).

7. A time series data indexing method according to claim 6, characterized in that: The splitting process of index item E is as follows: D1. Determine the time point T at which the index item is split; D2. Find the logical offset in the storage file corresponding to time point T, using sequence number P T represent; D3. Update the original index item ES; include: D3-1, the number of data records in the original index item interval C ES =P T -The logical offset P of the first record in the storage E ; D3-2, the average time interval of data within the original index item interval I ES =(P T The time of the location record - the time TS at which the interval range of the original index item starts E ) / (C ES -1); D4. New index item EN is created; include: D4-1. Find P T The next record P T’ ; D4-2. The time TS at which the new index entry range starts EN =P T’ The time of the corresponding record; D4-3, the number of data records in the new index item interval C EN =C E -C ES ; D4-4, average time interval of data within the new index item interval I EN = (the time TE at which the original index entry's interval range ends E -P T’ Location record time) / (C EN -1).

8. A time series data indexing method according to claim 7, characterized in that: The process of deleting an index is as follows: E1. When a record R is deleted, it is necessary to find the location of the time corresponding to the record in the index; E2, record the index item of R time split covering R; E3. For the first item E in the classified index item: The number of data records in the index item E interval C E =C E -1, if the number of data records in E is 0, delete the index entry.

9. A time series data indexing device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 8.

Images