Data query method and apparatus

Abstract

The one or more embodiments of the specification provide a data query method and device, comprising: performing range filtering on index data based on a query condition in a data query instruction and row storage data in pre-constructed index data to obtain a first data range; performing filtering on the first data range based on other query conditions and column storage data in the index data to obtain a second data range; and processing data in the second data range based on the data query instruction to obtain a data query result. In the embodiments of the specification, the row storage data and the column storage data are pre-constructed, the row storage data and the column storage data can be called simultaneously in a data query operation, and the row storage data and the column storage data are used to calculate a data intersection, so that a queried data range is quickly filtered out, and the data query efficiency of a database is improved.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of database technology, and in particular to a data query method and apparatus. Background Technology

[0002] With the development of technology, the volume of various types of data is experiencing explosive growth, and databases can provide services such as data storage and querying. In related technologies, as business data continues to grow, the amount of index data built for querying business data also increases accordingly, and the query efficiency of databases needs to be improved. Summary of the Invention

[0003] To improve the data query performance of a database, this specification provides one or more embodiments of a data query method, apparatus, database system, and storage medium.

[0004] According to a first aspect of one or more embodiments of this specification, a data query method is provided, comprising:

[0005] Obtain a data query instruction, which includes multiple query conditions;

[0006] Based on at least one of the multiple query conditions and the row storage data in the pre-built index data, the index data is range filtered to obtain a first data range;

[0007] Based on other query conditions among the multiple query conditions, and the column storage data in the index data, the first data range is filtered to obtain the second data range;

[0008] The data in the second data range is processed based on the data query instruction to obtain the data query result.

[0009] In one or more embodiments of this specification, the step of performing range filtering on the index data to obtain a first data range based on at least one of the plurality of query conditions and row storage data in the pre-built index data includes:

[0010] Based on the query field corresponding to the at least one query condition and the row storage data, determine the row offset that meets the query condition from the index data;

[0011] The first data range is obtained by range filtering of the index data based on the row offset.

[0012] In one or more embodiments of this specification, the step of filtering the first data range to obtain a second data range based on other query conditions among the plurality of query conditions and column storage data in the index data includes:

[0013] Based on the query fields corresponding to the other query conditions and the column storage data, determine the range of column data that meets the query conditions from the index data;

[0014] The second data range is determined based on the intersection of the column data range and the first data range.

[0015] In one or more embodiments of this specification, processing the data in the second data range based on the data query instruction to obtain the data query result includes:

[0016] In response to the fact that the number of columns in the data in the second data range is greater than a preset threshold, the data in the second data range is read based on the row storage data, and the data is processed to obtain the data query result;

[0017] In response to a column data in the second data range being less than or equal to the preset threshold, data in the second data range is read based on the column storage data, and the data is processed to obtain the data query result.

[0018] In one or more embodiments of this specification, the step of performing range filtering on the index data to obtain a first data range based on at least one of the plurality of query conditions and row storage data in the pre-built index data includes:

[0019] Based on the query fields corresponding to the at least one query condition, the target index data is determined from multiple pre-built index data.

[0020] Based on the at least one query condition and the target index data, a first data range is obtained by range filtering of the target index data.

[0021] In one or more embodiments of this specification, the process of pre-constructing the index data includes:

[0022] Generate a preset index table based on the data to be stored;

[0023] The data to be stored is written into memory based on the preset index table;

[0024] In response to the amount of data written to the memory reaching a preset capacity, row storage data and column storage data for each column are generated based on the data in the memory.

[0025] The index data is constructed based on the row storage data and the column storage data.

[0026] In one or more embodiments of this specification, the process of pre-constructing the index data includes:

[0027] Generate multiple preset index tables based on the query fields in the data to be stored;

[0028] For each preset index table, corresponding index data is constructed based on the preset index table.

[0029] According to a second aspect of one or more embodiments of this specification, a data query apparatus is provided, comprising:

[0030] The instruction acquisition module is configured to acquire data query instructions, which include multiple query conditions.

[0031] The first filtering module is configured to perform range filtering on the index data to obtain a first data range based on at least one of the multiple query conditions and row storage data in the pre-built index data.

[0032] The second filtering module is configured to filter the first data range to obtain a second data range based on other query conditions among the plurality of query conditions and column storage data in the index data.

[0033] The query results module is configured to process the data in the second data range based on the data query instruction to obtain the data query results.

[0034] In one or more embodiments of this specification, the first filtering module is configured to:

[0035] Based on the query field corresponding to the at least one query condition and the row storage data, determine the row offset that meets the query condition from the index data;

[0036] The first data range is obtained by range filtering of the index data based on the row offset.

[0037] In one or more embodiments of this specification, the second filtering module is configured to:

[0038] Based on the query fields corresponding to the other query conditions and the column storage data, determine the range of column data that meets the query conditions from the index data;

[0039] The second data range is determined based on the intersection of the column data range and the first data range.

[0040] In one or more embodiments of this specification, the query result module is configured as follows:

[0041] In response to the fact that the number of columns in the data in the second data range is greater than a preset threshold, the data in the second data range is read based on the row storage data, and the data is processed to obtain the data query result;

[0042] In response to a column data in the second data range being less than or equal to the preset threshold, data in the second data range is read based on the column storage data, and the data is processed to obtain the data query result.

[0043] In one or more embodiments of this specification, the first filtering module is configured to:

[0044] Based on the query fields corresponding to the at least one query condition, the target index data is determined from multiple pre-built index data.

[0045] Based on the at least one query condition and the target index data, a first data range is obtained by range filtering of the target index data.

[0046] In one or more embodiments of this specification, the apparatus further includes an index building module, the index building module being configured to:

[0047] Generate a preset index table based on the data to be stored;

[0048] The data to be stored is written into memory based on the preset index table;

[0049] In response to the amount of data written to the memory reaching a preset capacity, row storage data and column storage data for each column are generated based on the data in the memory.

[0050] The index data is constructed based on the row storage data and the column storage data.

[0051] In one or more embodiments of this specification, the index building module is configured to:

[0052] Generate multiple preset index tables based on the query fields in the data to be stored;

[0053] For each preset index table, corresponding index data is constructed based on the preset index table.

[0054] According to a third aspect of one or more embodiments of this specification, a database system is provided, comprising:

[0055] processor; and

[0056] A memory storing computer instructions for causing a processor to perform the method according to any embodiment of the first aspect.

[0057] According to a fourth aspect of one or more embodiments of this specification, a storage medium is provided storing computer instructions for causing a computer to perform the method described according to any embodiment of the first aspect.

[0058] The data query method described in this specification includes: filtering the index data based on query conditions in a data query instruction and row-stored data in a pre-built index to obtain a first data range; filtering the first data range based on other query conditions and column-stored data in the index to obtain a second data range; and processing the data in the second data range based on the data query instruction to obtain a data query result. In this embodiment, by pre-building index data that includes both row-stored and column-stored data, the data query operation can simultaneously call both the row-stored and column-stored data, calculate the data intersection using the row-stored and column-stored data, thereby quickly filtering out the queried data range and improving the database's data query efficiency. Attached Figure Description

[0059] Figure 1 This is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0060] Figure 2 This is a schematic diagram of a data query method provided according to an exemplary embodiment of this specification.

[0061] Figure 3 This is a schematic diagram of a data query method provided according to an exemplary embodiment of this specification.

[0062] Figure 4 This is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0063] Figure 5 This is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0064] Figure 6 This is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0065] Figure 7 This is a schematic diagram of a data query method provided according to an exemplary embodiment of this specification.

[0066] Figure 8 This is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0067] Figure 9 This is a structural block diagram of a data query device provided according to an exemplary embodiment of this specification.

[0068] Figure 10 This is a structural block diagram of a database system provided according to an exemplary embodiment of this specification. Detailed Implementation

[0069] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0070] It should be noted that in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.

[0071] In addition, the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0072] With the development of technology, the volume of various types of data is growing explosively, and databases can provide services such as data storage and retrieval. Among related technologies, database storage structures can include row-based storage and column-based storage.

[0073] Row-based storage refers to storing data in rows as the basic unit, with each row containing the values ​​of all fields in the table. Because all fields of each row are stored together, row-based storage has good OLTP (Online Transaction Processing) capabilities, meeting the concurrency and consistency requirements of database data processing.

[0074] Columnar storage refers to storing data in columns as the basic unit, with data from the same column stored together to facilitate data aggregation and compression. Therefore, compared to row-based storage, columnar storage greatly facilitates column-based querying and filtering operations, improving the OLAP (Online Analysis Processing) performance of the database.

[0075] A database index is a data structure that improves the speed of data retrieval in a database table. Indexes are used to quickly locate data without searching every row in the table every time it is accessed. In related technologies, some databases not only apply columnar storage structures to data tables but also to indexes to build column-oriented indexes, thereby improving the performance of column-based queries and accelerating OLAP operations. However, as the amount of data continues to grow, the query performance of the database will be affected regardless of whether it is a row-oriented or column-oriented index, leading to a decrease in data retrieval efficiency.

[0076] Based on this, one or more embodiments of this specification provide a data query method, apparatus, database system, and storage medium, aiming to construct row-stored data and column-stored data under the same index file, and to quickly filter data by utilizing the redundant row and column-stored data, thereby improving the data query efficiency of the database.

[0077] The data query method described in this specification can pre-build index data including row storage indexes and column storage indexes, so that during data querying, the pre-built row storage indexes and column storage indexes can be called simultaneously to achieve fast filtering of the query data. For ease of understanding and explanation, the process of index data construction will be explained first, followed by the data query process.

[0078] like Figure 1 As shown, in some embodiments, the data query method exemplified in this specification includes the following process for pre-building indexed data:

[0079] S110. Generate a preset index table based on the data to be stored.

[0080] In the embodiments described in this specification, the data to be stored refers to the data that needs to be written to the disk for permanent storage, and the preset index table refers to the index table generated based on one or more fields of the data to be stored through ordered processing.

[0081] An index table includes one or more fields for querying data. Each row in the index table contains the values ​​corresponding to these fields. Additionally, the index table includes a primary key for each row. The primary key is one or more fields in the main table, and its value uniquely identifies the position of a row in the main table. For example... Figure 2The example index table contains four columns of data, each with fields for "Age", "Years of Service", "Gender" and "Employee ID". The "Employee ID" field represents the primary key of the index table.

[0082] It is worth noting that the default index table can be an index built based on one or more fields, for example... Figure 2 In the example, the index table can be a composite index built based on the fields of "age", "length of service" and "gender". That is, the index table is pre-sorted based on the "age" field, sorted based on the "length of service" field when the ages are the same, and further sorted based on the "gender" field when the length of service is the same. Those skilled in the art will understand this, and this specification will not elaborate further.

[0083] S120. Write the data to be stored into memory based on the preset index table.

[0084] In some embodiments of this specification, an LSM Tree (the log-structured merge-tree) may be used as the basic storage data structure. Of course, those skilled in the art will understand that the data structure is not limited to the LSM Tree form; it can also be, for example, a B Tree, a B+ Tree, etc. This specification will use an LSM Tree as an example for illustration.

[0085] An LSM Tree is a data structure spanning both memory and disk. It consists of a C0 Tree (MemTable) in memory and multiple subtrees, including C1 Tree, C2 Tree, ..., Cn Tree, located on disk. LSM Tree appends insertion, modification, and deletion operations to the in-memory Memtable, pre-sorting the data. When the data volume in the Memtable reaches a certain threshold, the data is sequentially written to disk for persistent storage. The resulting data structure is an SSTable. Furthermore, to improve read performance, LSM Tree periodically merges the SSTable files on disk. During merging, append operations on the same data are combined to reduce the data volume.

[0086] In the embodiments described in this specification, in conjunction with Figure 3 As shown, for the preset index table, the data in the table is the data to be stored as described in this specification. When constructing the index data, the data in the table can be sequentially written into the memory MemTable.

[0087] S130, In response to the amount of data written to memory reaching the preset capacity, generate row storage data and column storage data for each column based on the data in memory.

[0088] As mentioned above, when the amount of data written to the in-memory MemTable reaches a certain threshold, the data in memory needs to be dumped to the disk SSTable. For example, a preset capacity representing the capacity threshold can be set for the in-memory MemTable. When the amount of data written to the in-memory MemTable reaches this preset capacity, it means that the data in the in-memory MemTable needs to be written to the disk SSTable.

[0089] In the embodiments described in this specification, when writing data from the memory Memtable to the disk SSTable, it is necessary not only to write the data in a row-oriented manner to form row-based data, but also to write the data for each column in a column-oriented manner to construct column-based data. In other words, the SSTable written to the disk includes not only row-based data, but also column-based data for each column.

[0090] Taking OceanBase as an example, the database divides the disk into fixed-size macroblocks, and within each macroblock, the data is organized into multiple microblocks. Combined with... Figure 3 As shown, when data is transferred from the memory MemTable to the disk SSTable, it is necessary to generate row store data and column store data under the same SSTable index file according to the row storage format and column storage format respectively. The row store data under SSTable is the data of row 1 to row n in the figure, and the column store data is the data of C1 to C4 in the figure.

[0091] S140. Index data is constructed based on row-stored data and column-stored data.

[0092] In the embodiments described in this specification, after obtaining row storage data and column storage data through the aforementioned process, the row storage data and column storage data are used to form index data for data querying.

[0093] It is worth noting that, in the embodiments described in this specification, compared with the traditional row storage index, a column storage index is redundantly constructed on the basis of the row storage index for each column of data. This allows for accelerated filtering of queried data based on both the row storage index and the column storage index. The data query process will be described in the following section of this specification.

[0094] Furthermore, in related technologies, taking SQL Server database as an example, when performing data queries, the optimizer selects the index data most relevant to the query columns. When calling the index data to execute the query operation, the database can only call one index at a time. However, in the implementation method of this specification, the row storage data and column storage data are built in the same index data. Even if the database optimizer can only call one index during data query, it can simultaneously call the row storage data and column storage data under the index data, realizing the data query process described below.

[0095] Furthermore, it's understandable that column-stored data is typically compressed, making it unsuitable for in-place updates based on B-tree or B+tree storage structures, resulting in weak OLTP capabilities. However, some implementations in this specification use LSM Tree append-only writes to store data, eliminating the need for in-place updates to the SSTable. Therefore, this doesn't affect the database's OLTP capabilities, allowing the database to retain its original OLTP performance while leveraging column-store indexes to improve OLAP performance.

[0096] In the above Figure 1 The implementation method describes the process of constructing index data based on a preset index table. In fact, for the same data table, different preset index tables can be generated based on different query fields, thereby constructing corresponding index data based on each preset index table according to the above method. The following section will combine... Figure 4 Please provide an explanation.

[0097] like Figure 4 As shown, in some embodiments, the data query method exemplified in this specification includes the following process for constructing indexed data:

[0098] S410. Generate multiple preset index tables based on the query fields in the data to be stored.

[0099] S420. For each preset index table, construct the corresponding index data based on the preset index table.

[0100] As mentioned above, data to be stored refers to data that needs to be written to the disk for permanent storage, such as a data table containing data to be stored. Figure 2 For example.

[0101] It's understandable that the data table to be stored includes multiple fields, such as "age," "length of service," "gender," and "employee ID." The pre-defined index table can be a data table obtained by sorting the data based on one or more fields, for example... Figure 2 In the example, by sorting the "age" field from smallest to largest, an index table related to the "age" field can be obtained.

[0102] Similarly, data can be sorted based on any field such as "length of service" or "gender," and if values ​​in one field are the same, sorting can be done based on the next field. For example, in one example, data can be sorted from smallest to largest based on the "age" field. Figure 2 The data table shown is sorted. For data with the same age field value, it can be further sorted based on the "length of service" field value from smallest to largest, and so on.

[0103] Similarly, by processing the data table containing the data to be stored based on different query fields, multiple corresponding preset index tables can be obtained. For each preset index table, the aforementioned... Figure 1 The method shown in the diagram constructs the corresponding index data.

[0104] In the embodiments described in this specification, multiple index data can be constructed based on multiple preset index tables, and at least one of these index data can be used as a clustered storage index, while the remaining index data can be used as a non-clustered storage index. The clustered and non-clustered storage indexes function the same way; the difference lies in that the clustered storage index is the primary storage index for the entire data table, while the non-clustered index is an auxiliary index created for the data table.

[0105] In related technologies, taking SQL Server database as an example, SQL Server's columnstore indexes are subject to other constraints, thus allowing only one non-clustered columnstore index at most. However, in the implementation method of this specification, there is no limit to the number of non-clustered columnstore indexes; multiple non-clustered columnstore indexes can be built based on multiple preset index tables, providing better support for OLTP tasks.

[0106] After constructing the indexed data SSTable, which includes row-stored data and column-stored data, through the above process, data queries can be performed based on the indexed data SSTable. The following section will combine... Figure 5 Please provide an explanation.

[0107] like Figure 5 As shown, in some embodiments, the data query method exemplified in this specification includes:

[0108] S510, Data Query Command.

[0109] In the embodiments described in this specification, a data query instruction refers to a query statement (query) used to retrieve the required data from the database. A data query instruction typically includes one or more query conditions. The database needs to filter the data that meets the query conditions based on these query conditions and return the query results.

[0110] In some implementations, data query commands can be expressed as Structured Query Language (SQL) commands, such as the Select command. However, data query commands are not limited to this. For example, in some implementations, data query commands can also be implemented using other operation languages ​​of relational databases, operation languages ​​of non-relational databases, or other feasible computer languages ​​or information transmission formats. For instance, the data query command can also be expressed as a Hypertext Transfer Protocol (HTTP) request, such as a data request using the GET method.

[0111] Furthermore, one or more query conditions carried in a data query command can have various specific forms, and these forms vary depending on the specific query command. For example, a basic SQL query command can be expressed as "SELECT ID, Name FROM Student WHERE ID=5", used to query the student ID and name of the student with ID 5 in the Student table. Therefore, the query conditions in this command can include two parts: one part is "ID" and "Name" following the SELECT identifier, which represent the attributes of the retrieved data; the other part is "ID=5" following the WHERE identifier, which represents the condition that this data must satisfy.

[0112] As can be seen from the above description, in the embodiments of this specification, the query conditions carried in the data query instruction may include more than one, and the query conditions may not be limited to numerical relational expressions, while the queried data records should satisfy all the query conditions in the query instruction.

[0113] Of course, the above is just an example. The implementation of this specification does not limit the specific form of the query conditions. For example, in some implementations of this specification, the target data record can also be located according to the row address and / or column address, so that the query conditions corresponding to the query instruction can be the relevant information of the row address and / or column address.

[0114] S520. Based on at least one of the multiple query conditions and the row storage data in the pre-built index data, the index data is range filtered to obtain a first data range.

[0115] In the embodiments described in this specification, when a data query instruction includes multiple query conditions, the row storage data in the aforementioned constructed index data can be used first. Based on one or more query conditions in the data query instruction, the entire index data can be initially filtered to select the data rows that meet the query conditions.

[0116] For example, a query command might say "retrieve all male employees in the table who are older than 40 years old". The query criteria would be "older than 40 years old", "male", and the data in the query results must satisfy all the query criteria.

[0117] In this scenario, we can first use the query field "age" corresponding to the query condition "age greater than 40 years old" to quickly filter out the row offsets of data rows with "age" values ​​greater than 40 in the indexed data table using row storage data. Then, based on these row offsets, we can filter out all data rows with "age" values ​​less than or equal to 40, thus completing the initial row filtering of the data range and obtaining the first data range. That is, all data in the first data range have an "age" value greater than 40.

[0118] The process of filtering the data range based on the row-stored data to obtain the first data range will be further described in the following embodiments of this specification.

[0119] S530. Based on other query conditions among multiple query conditions and column storage data in the index data, the first data range is filtered to obtain the second data range.

[0120] It is understandable that in S520, data tables can be filtered based on row-stored data. Then, for the first data range after row filtering, the column-stored data in the aforementioned constructed index data can be further combined to achieve fast filtering of the data range.

[0121] In some implementations, for other query conditions in the data query instruction, the column storage data to be called can be determined based on the fields corresponding to the query conditions. Then, the data column of the first data range can be filtered based on the intersection of the column storage data and the first data range to obtain the second data range.

[0122] Continuing with the scenario described earlier, after filtering the data rows based on the query condition "age greater than 40 years old" and the row-stored data to obtain the first data range, the query condition "gender is male" determines that the corresponding field is "gender." Then, the column-stored data is called to obtain a column range with the field "gender." It's understandable that since the column range includes all gender data in the entire table, it's necessary to combine the first data range with the intersection of this column range to obtain the second data range, which contains the gender data for all employees older than 40 years old.

[0123] S540. Process the data in the second data range based on the data query instruction to obtain the data query result.

[0124] In the embodiments described in this specification, after obtaining the second data range through the aforementioned data row filtering and data column processes, the data in the second data range can be read and processed accordingly to obtain the final data query result.

[0125] The data processing method needs to be determined based on the query conditions in the data query command. For example, in the previous example, the data query condition is "male employees who are older than 40 years old". Then, for each gender data in the second data range, we can further filter out the data with the value "male" and return the data query results. The data query results include all data information of those who are older than 40 years old and whose gender is male.

[0126] For example, in another example, the data query condition is "the total number of male employees who are older than 40 years old". After filtering the data with the value "male" from the gender data in the second data range, the filtered data is further aggregated to calculate the total number of data and return the data query result. The data query result includes the number of all employees who are older than 40 years old and whose gender is male.

[0127] Of course, those skilled in the art will understand that the methods for processing the data in the second data range are not limited to the above examples, and this specification will not elaborate further on this.

[0128] In some embodiments of this specification, when reading data within a second data range, the number of columns in the second data range can be determined. If the number of columns in the second data range is large, reading data based on column-based storage would require numerous decompression operations, resulting in higher I / O overhead for data queries compared to row-based storage. Conversely, if the number of columns in the second data range is small, reading data based on row-based storage would require reading a large amount of redundant data from each row, leading to significantly higher I / O overhead for data queries compared to column-based storage.

[0129] Therefore, in some embodiments of this specification, a preset threshold can be set in advance for the number of columns in the second data range. During the data query phase, after determining the second data range, the number of columns included in the second data range can be compared with the preset threshold. If the number of columns included in the second data range is greater than the preset threshold, it indicates that there are many columns in the second data range that need to be queried. In this case, if data is read based on column-stored data, the IO overhead will be large. Therefore, the required data can be read based on row-stored data. Conversely, if the number of columns included in the second data range is less than or equal to the preset threshold, it indicates that there are few columns in the second data range that need to be queried. In this case, if data is read based on column-stored data, the query efficiency will be greatly improved, and the IO overhead will be reduced compared to row-stored data. Therefore, the required data can be read based on column-stored data. This will be explained in the embodiments below.

[0130] After reading the data within the second data range, the data can be processed according to the query conditions in the data query instruction based on the aforementioned data processing procedure, thereby obtaining the corresponding data query results and returning them. This instruction manual will not elaborate further on this.

[0131] As can be seen from the above, in the embodiments of this specification, by pre-constructing index data that includes both row-stored data and column-stored data, the row-stored data and column-stored data can be called simultaneously during data query operations. The intersection of the row-stored data and column-stored data is calculated using the row-stored data and column-stored data, thereby quickly filtering out the range of data to be queried and improving the data query efficiency of the database.

[0132] like Figure 6 As shown, in some embodiments, the example data query method of this specification includes the process of obtaining a first data range by range filtering of index data, comprising:

[0133] S521. Based on the query field and row storage data corresponding to at least one query condition, determine the row offset that meets the query condition from the index data.

[0134] S522. The first data range is obtained by performing a range test on the index data based on the row offset.

[0135] In the embodiments described in this specification, when performing a data query operation, the data rows that meet the query conditions can first be filtered out based on the query fields corresponding to the query conditions and the row storage data in the pre-built index data.

[0136] It is worth noting that, as can be seen from the preceding embodiments, the embodiments of this specification can generate multiple preset index tables based on multiple query fields, and construct multiple index data. Therefore, in the embodiments of this specification, when performing data queries, the target index data can first be determined from multiple index data based on the query fields corresponding to the query conditions.

[0137] For example, in one example, the data query instruction is expressed as "query the number of male employees in the table who are older than 30 and have worked for more than 3 years". This allows the target index data to be determined from multiple index data based on the query field "age" corresponding to the query condition "older than 30".

[0138] For example, in one example, the preset index table corresponding to the target index data can be as follows: Figure 7 As shown, the target index data includes four columns, each with fields for "Age", "Years of Service", "Gender", and "Employee ID". The "Employee ID" field represents the primary key of the index table. Figure 7 In the example, the index table is sorted from smallest to largest based on the age field, and then further sorted based on the "length of service" field if the ages are the same, and then further sorted based on the "gender" field if the length of service is the same.

[0139] In this example scenario, we can first determine the corresponding query field as "age" based on the query condition "age greater than 30 years old". Then, based on the row storage data and this query condition, we determine the row offset in the index data that satisfies "age greater than 30 years old". The row offset represents the location of the data row that meets the query condition. For example... Figure 7 In the example, the data in the third row, "age=31 years of service=2 gender=male", meets the query condition "age greater than 30 years old", so the row offset is 3, indicating that the data in the third row and below meet the query condition.

[0140] After determining the row offset based on the row-stored data, the index data is filtered using that row offset to obtain the first filtered data range. For example... Figure 7 In the example scenario, filtering the first two rows of data based on the row offset yields the remaining first data range, which is the representation of the first data range. Figure 7 The range of data in the third row and subsequent rows within the solid line box.

[0141] like Figure 8 As shown, in some embodiments, the data query method exemplified in this specification, the process of filtering a first data range to obtain a second data range, includes:

[0142] S531. Based on the query fields and column storage data corresponding to other query conditions, determine the range of column data that meet the query conditions from the index data.

[0143] S532. Determine the second data range based on the intersection of the column data range and the first data range.

[0144] Still with Figure 7 Taking the scenario shown as an example, the data query command also includes the query conditions "more than 3 years of service" and "male gender", with the corresponding query fields being "years of service" and "gender" respectively. Therefore, by storing data based on these query fields using column storage, two columns of data under the "gender" and "years of service" fields can be obtained.

[0145] For example Figure 7 In the example, the range of column data filtered based on column storage data is the data range within the dashed box in the diagram. Then, by calculating the intersection of the column data range and the first data range, the second data range can be obtained, which is... Figure 7 In the example, the intersection of the column data range in the dashed box and the first data range in the solid box is the second data range. The data in the second data range represents the data that satisfies all the query conditions in the data query instruction.

[0146] After determining the second data range, it is necessary to read the corresponding data from the second data range. In some embodiments of this specification, the process of reading data within the second data range includes:

[0147] In response to the fact that the number of columns in the second data range exceeds a preset threshold, data in the second data range is read based on row-stored data;

[0148] In response to the number of columns in the second data range exceeding a preset threshold, data in the second data range is read based on column storage data.

[0149] It should be noted that, in Figure 7 For ease of understanding and illustration, the example only shows a scenario with a small number of columns. In reality, in big data processing scenarios, the number of columns in a data table is enormous, and column-based storage typically requires column data compression. Therefore, during the data query phase, reading data from many columns using column-based storage requires extensive data decompression operations, resulting in high I / O overhead for data queries, sometimes exceeding the overhead of reading corresponding column data using row-based storage.

[0150] Therefore, in some embodiments of this specification, a corresponding preset threshold can be set in advance for the number of columns in the second data range. The preset threshold represents the critical value for calling row-stored data or column-stored data. The specific value of the preset threshold can be selected according to the application scenario, and this specification does not limit it.

[0151] In some implementations, the number of columns in the second data range can be compared with a preset threshold. If the number of columns is greater than the preset threshold, it means that there are many columns that need to be read. If data is read based on column storage data, it will not only not improve query performance, but may also generate a lot of IO overhead. Therefore, row storage data can be called to read the data in the second data range.

[0152] If the number of columns is less than or equal to the preset threshold, it means that the number of columns that need to be read is not large. If the data is read based on column storage, the data query efficiency will be greatly improved. Compared with row storage, the IO overhead will be reduced. Therefore, column storage can be called to read the data of the second data range.

[0153] After reading the data within the second data range, the data can be processed according to the query conditions in the data query instruction, based on the aforementioned data processing procedure, to obtain and return the corresponding data query results. For example, as mentioned above... Figure 7 In the example scenario, after reading the data within the second data range, all read data can be aggregated to obtain a data query result representing the total number of male employees who are older than 30 and have worked for more than 3 years, and this data query result is returned.

[0154] As described above, in the embodiments of this specification, both row-stored data and column-stored data can be accessed simultaneously during data querying. The intersection of the row-stored and column-stored data is calculated, thereby quickly filtering out the range of data to be queried and improving the database's data query efficiency. Furthermore, during data reading, either row-stored or column-stored data is selected based on the number of columns to be read, thereby reducing IO overhead and further improving data query efficiency and the database's OLAP capabilities.

[0155] Furthermore, as mentioned above, there is no limit to the number of non-clustered storage indexes in this embodiment. Multiple non-clustered column-oriented indexes can be built based on multiple preset index tables to improve the OLTP capability of the database. Moreover, this embodiment uses LSM Tree append-only writes to store data, eliminating the need for in-place updates to the SSTable. Therefore, it does not affect the OLTP capability of the database, allowing it to improve OLAP capability by utilizing column-oriented data storage while retaining its original OLTP capabilities.

[0156] In some embodiments, this specification provides a data query device, such as... Figure 9 As shown, the data query device includes:

[0157] Instruction acquisition module 10 is configured to acquire data query instructions, which include multiple query conditions;

[0158] The first filtering module 20 is configured to perform range filtering on the index data to obtain a first data range based on at least one of the plurality of query conditions and row storage data in the pre-built index data.

[0159] The second filtering module 30 is configured to filter the first data range to obtain a second data range based on other query conditions among the plurality of query conditions and column storage data in the index data.

[0160] The query result module 40 is configured to process the data in the second data range based on the data query instruction to obtain the data query result.

[0161] As can be seen from the above, in the embodiments of this specification, by pre-constructing row storage data and column storage data, the row storage data and column storage data can be called simultaneously in the data query operation. The intersection of the row storage data and column storage data is calculated using the row storage data and column storage data, thereby quickly filtering out the range of data to be queried and improving the data query efficiency of the database.

[0162] In one or more embodiments of this specification, the first filtering module 20 is configured to:

[0163] Based on the query field corresponding to the at least one query condition and the row storage data, determine the row offset that meets the query condition from the index data;

[0164] The first data range is obtained by range filtering of the index data based on the row offset.

[0165] In one or more embodiments of this specification, the second filtering module 30 is configured to:

[0166] Based on the query fields corresponding to the other query conditions and the column storage data, determine the range of column data that meets the query conditions from the index data;

[0167] The second data range is determined based on the intersection of the column data range and the first data range.

[0168] In one or more embodiments of this specification, the query result module 40 is configured as follows:

[0169] In response to the fact that the number of columns in the data in the second data range is greater than a preset threshold, the data in the second data range is read based on the row storage data, and the data is processed to obtain the data query result;

[0170] In response to a column data in the second data range being less than or equal to the preset threshold, data in the second data range is read based on the column storage data, and the data is processed to obtain the data query result.

[0171] In one or more embodiments of this specification, the first filtering module 20 is configured to:

[0172] Based on the query fields corresponding to the at least one query condition, the target index data is determined from multiple pre-built index data.

[0173] Based on the at least one query condition and the target index data, a first data range is obtained by range filtering of the target index data.

[0174] In one or more embodiments of this specification, the apparatus further includes an index building module, the index building module being configured to:

[0175] Generate a preset index table based on the data to be stored;

[0176] The data to be stored is written into memory based on the preset index table;

[0177] In response to the amount of data written to the memory reaching a preset capacity, row storage data and column storage data for each column are generated based on the data in the memory.

[0178] The index data is constructed based on the row storage data and the column storage data.

[0179] In one or more embodiments of this specification, the index building module is configured to:

[0180] Generate multiple preset index tables based on the query fields in the data to be stored;

[0181] For each preset index table, corresponding index data is constructed based on the preset index table.

[0182] As described above, in the embodiments of this specification, both row-stored data and column-stored data can be accessed simultaneously during data querying. The intersection of the row-stored and column-stored data is calculated, thereby quickly filtering out the range of data to be queried and improving the database's data query efficiency. Furthermore, during data reading, either row-stored or column-stored data is selected based on the number of columns to be read, thereby reducing IO overhead and further improving data query efficiency and the database's OLAP capabilities.

[0183] Furthermore, as mentioned above, there is no limit to the number of non-clustered storage indexes in this embodiment. Multiple non-clustered column-oriented indexes can be built based on multiple preset index tables to improve the OLTP capability of the database. Moreover, this embodiment uses LSM Tree append-only writes to store data, eliminating the need for in-place updates to the SSTable. Therefore, it does not affect the OLTP capability of the database, allowing it to improve OLAP capability by utilizing column-oriented data storage while retaining its original OLTP capabilities.

[0184] In some embodiments, this specification provides a database system comprising:

[0185] processor; and

[0186] The memory stores computer instructions that cause the processor to perform the method described in any of the above embodiments.

[0187] In some embodiments, this specification provides a storage medium storing computer instructions for causing a computer to perform the methods described in any of the above embodiments.

[0188] Figure 10 This is a schematic diagram of a database system provided in an exemplary embodiment. Please refer to it. Figure 10 At the hardware level, the system includes a processor 702, an internal bus 704, a network interface 706, memory 708, and non-volatile memory 710, and may also include other hardware required for different scenarios. One or more embodiments of this specification can be implemented in software, such as the processor 702 reading the corresponding computer program from the non-volatile memory 710 into memory 708 and then running it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0189] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.

[0190] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0191] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0192] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0193] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0194] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims may be performed in a different order than those shown in the embodiments and may still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0195] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms “a,” “described,” and “the” used in one or more embodiments of this specification and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0196] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, without departing from the scope of one or more embodiments of this specification, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."

[0197] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification shall be included within the scope of protection of one or more embodiments of this specification.

Claims

1. A data query method, comprising: Obtain a data query instruction, which includes multiple query conditions; Based on at least one of the multiple query conditions and the row storage data in the pre-built index data, the index data is range-filtered to obtain a first data range; the index data includes: row storage data and column storage data, the row storage data is constructed by writing the preset index table into disk in row-oriented storage, and the column storage data is constructed by writing the index table into disk in column-oriented storage; Based on other query conditions among the multiple query conditions, and the column storage data in the index data, the first data range is filtered to obtain the second data range; The data in the second data range is processed based on the data query instruction to obtain the data query result.

2. The method according to claim 1, wherein the step of performing range filtering on the index data to obtain a first data range based on at least one of the plurality of query conditions and row storage data in the pre-built index data includes: Based on the query field corresponding to the at least one query condition and the row storage data, determine the row offset that meets the query condition from the index data; The first data range is obtained by range filtering of the index data based on the row offset.

3. The method according to claim 1, wherein filtering the first data range to obtain a second data range based on other query conditions among the plurality of query conditions and column storage data in the index data includes: Based on the query fields corresponding to the other query conditions and the column storage data, determine the range of column data that meets the query conditions from the index data; The second data range is determined based on the intersection of the column data range and the first data range.

4. The method according to claim 1, wherein processing the data in the second data range based on the data query instruction to obtain the data query result includes: In response to the fact that the number of columns in the data in the second data range is greater than a preset threshold, the data in the second data range is read based on the row storage data, and the data is processed to obtain the data query result; In response to a column data in the second data range being less than or equal to the preset threshold, data in the second data range is read based on the column storage data, and the data is processed to obtain the data query result.

5. The method according to claim 1, wherein the step of performing range filtering on the index data to obtain a first data range based on at least one of the plurality of query conditions and row storage data in the pre-built index data includes: Based on the query fields corresponding to the at least one query condition, the target index data is determined from multiple pre-built index data. Based on the at least one query condition and the target index data, a first data range is obtained by range filtering of the target index data.

6. The method according to any one of claims 1 to 5, wherein the process of pre-constructing the index data comprises: Generate a preset index table based on the data to be stored; The data to be stored is written into memory based on the preset index table; In response to the amount of data written to the memory reaching a preset capacity, row storage data and column storage data for each column are generated based on the data in the memory. The index data is constructed based on the row storage data and the column storage data.

7. The method according to any one of claims 1 to 5, wherein the process of pre-constructing the index data comprises: Generate multiple preset index tables based on the query fields in the data to be stored; For each preset index table, corresponding index data is constructed based on the preset index table.

8. A data query device, comprising: The instruction acquisition module is configured to acquire data query instructions, which include multiple query conditions. The first filtering module is configured to perform range filtering on the index data to obtain a first data range based on at least one of the multiple query conditions and the row storage data in the pre-built index data; the index data includes: row storage data and column storage data, the row storage data is formed by writing the preset index table into disk in row format, and the column storage data is formed by writing the index table into disk in column format; The second filtering module is configured to filter the first data range to obtain a second data range based on other query conditions among the plurality of query conditions and column storage data in the index data. The query results module is configured to process the data in the second data range based on the data query instruction to obtain the data query results.

9. A database system, comprising: processor; and A memory storing computer instructions for causing a processor to perform the method according to any one of claims 1 to 7.

10. A storage medium storing computer instructions for causing a computer to perform the method according to any one of claims 1 to 7.

Images