Data query method and apparatus

Abstract

Provided in one or more embodiments of the present description are a data query method and apparatus. The method comprises: performing range filtering on index data on the basis of a query condition in a data query instruction and row storage data in pre-constructed index data, so as to obtain a first data range; filtering the first data range on the basis of other query conditions and column storage data in the index data, so as to obtain a second data range; and processing data within the second data range on the basis of the data query instruction, so as to obtain a data query result. In the embodiment of the present description, the row storage data and the column storage data are pre-constructed, the row storage data and the column storage data can be called at the same time in a data query operation, and the row storage data and the column storage data are used to calculate a data intersection set, so as to quickly obtain a queried data range by means of screening, thereby improving data query efficiency of a database.

Description

Data query method and device Technical Field

[0001] One or more embodiments of this specification relate to the field of database technology, and in particular, to a data query method and device. Background Art

[0002] With the development of technology, the amount of data of various types has exploded. Databases can provide services such as data storage and query. In related technologies, as business data continues to grow, the index data created to query business data will also increase accordingly, and the query efficiency of databases needs to be improved.

[0003] Summary of the Invention

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

[0005] According to a first aspect of one or more embodiments of the present specification, a data query method is provided, comprising: obtaining a data query instruction, the data query instruction comprising multiple query conditions; performing range filtering on index data based on at least one query condition among the multiple query conditions and row storage data in pre-built index data to obtain a first data range; filtering the first data range based on other query conditions among the multiple 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.

[0006] In one or more embodiments of the present specification, the range filtering of the index data based on at least one query condition among the multiple query conditions and the row storage data in the pre-built index data to obtain a first data range includes: determining a row offset that meets the query condition from the index data based on the query field corresponding to the at least one query condition and the row storage data; and range filtering the index data based on the row offset to obtain the first data range.

[0007] In one or more embodiments of the present specification, filtering the first data range to obtain the second data range based on other query conditions among the multiple query conditions and the column storage data in the index data includes: determining the column data range that meets the query conditions from the index data based on the query fields corresponding to the other query conditions and the column storage data; and determining the second data range based on the intersection of the column data range and the first data range.

[0008] In one or more embodiments of the present specification, the processing of the data in the second data range based on the data query instruction to obtain the data query result includes: in response to the number of columns of data in the second data range being greater than a preset threshold, reading the data in the second data range based on the row storage data, and processing the data to obtain the data query result; in response to the column data of the data in the second data range being less than or equal to the preset threshold, reading the data in the second data range based on the column storage data, and processing the data to obtain the data query result.

[0009] In one or more embodiments of the present specification, the range filtering of the index data to obtain a first data range based on at least one query condition among the multiple query conditions and the row storage data in the pre-built index data includes: determining target index data from the pre-built multiple index data based on the query field corresponding to the at least one query condition; and range filtering the target index data to obtain the first data range based on the at least one query condition and the target index data.

[0010] In one or more embodiments of the present specification, the process of pre-constructing the index data includes: generating a preset index table based on the data to be stored; writing the data to be stored into a memory based on the preset index table; in response to the amount of data written in the memory reaching a preset capacity, generating row storage data and column storage data for each column based on the data in the memory; and constructing the index data based on the row storage data and the column storage data.

[0011] In one or more embodiments of the present specification, the process of pre-constructing the index data includes: generating multiple preset index tables based on the query fields in the data to be stored; for each preset index table, constructing corresponding index data based on the preset index table.

[0012] According to a second aspect of one or more embodiments of the present specification, a data query device is provided, comprising: an instruction acquisition module configured to acquire a data query instruction, wherein the data query instruction includes multiple query conditions; a first filtering module configured to perform range filtering on index data based on at least one query condition among the multiple query conditions and row storage data in pre-built index data to obtain a first data range; a second filtering module configured to filter the first data range based on other query conditions among the multiple query conditions and column storage data in the index data to obtain a second data range; and a query result module configured to process data in the second data range based on the data query instruction to obtain a data query result.

[0013] In one or more embodiments of the present specification, the first filtering module is configured to: determine a row offset that meets the query condition from the index data based on the query field corresponding to the at least one query condition and the row storage data; and perform range filtering on the index data based on the row offset to obtain the first data range.

[0014] In one or more embodiments of the present specification, the second filtering module is configured to: determine the column data range that meets the query conditions from the index data based on the query fields corresponding to the other query conditions and the column storage data; and determine the second data range based on the intersection of the column data range and the first data range.

[0015] In one or more embodiments of the present specification, the query result module is configured to: in response to the number of columns of data in the second data range being greater than a preset threshold, read the data in the second data range based on the row storage data, and process the data to obtain the data query result; in response to the column data of the data in the second data range being less than or equal to the preset threshold, read the data in the second data range based on the column storage data, and process the data to obtain the data query result.

[0016] In one or more embodiments of the present specification, the first filtering module is configured to: determine target index data from a plurality of pre-constructed index data based on a query field corresponding to the at least one query condition; and perform range filtering on the target index data to obtain a first data range based on the at least one query condition and the target index data.

[0017] In one or more embodiments of the present specification, the device also includes an index construction module, which is configured to: generate a preset index table based on the data to be stored; write the data to be stored into the memory based on the preset index table; in response to the amount of data written in the memory reaching a preset capacity, generate row storage data and column storage data for each column based on the data in the memory; and construct the index data based on the row storage data and the column storage data.

[0018] In one or more embodiments of the present specification, the index construction module is configured to: generate multiple preset index tables based on the query fields in the data to be stored; for each preset index table, construct corresponding index data based on the preset index table.

[0019] According to a third aspect of one or more embodiments of this specification, a database system is provided, comprising: a processor; and a memory storing computer instructions, wherein the computer instructions are used to enable the processor to execute the method according to any embodiment of the first aspect.

[0020] According to a fourth aspect of one or more embodiments of this specification, a storage medium is provided, storing computer instructions, wherein the computer instructions are used to enable a computer to execute the method according to any embodiment of the first aspect.

[0021] The data query method of an embodiment of the present specification includes performing range filtering on the index data to obtain a first data range based on query conditions in a data query instruction and pre-constructed row storage data in index data, filtering 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 the data in the second data range based on the data query instruction to obtain a data query result. In the embodiment of the present specification, by pre-constructing index data that includes both row storage data and column storage data, the row storage data and column storage data can be simultaneously called during a data query operation, and data intersections can be calculated using the row storage data and column storage data, thereby quickly filtering out the queried data range and improving the efficiency of database data queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] FIG1 is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0023] FIG2 is a schematic diagram of a data query method according to an exemplary embodiment of this specification.

[0024] FIG3 is a schematic diagram of a data query method according to an exemplary embodiment of this specification.

[0025] FIG4 is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0026] FIG5 is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0027] FIG6 is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0028] FIG7 is a schematic diagram of a data query method according to an exemplary embodiment of this specification.

[0029] FIG8 is a flowchart of a data query method provided according to an exemplary embodiment of this specification.

[0030] FIG9 is a structural block diagram of a data query device provided according to an exemplary embodiment of this specification.

[0031] FIG10 is a structural block diagram of a database system provided according to an exemplary embodiment of this specification. DETAILED DESCRIPTION

[0032] Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. When the following description refers to the drawings, identical numerals in different figures represent identical or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments are not intended to represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with certain aspects of one or more embodiments of this specification, as detailed in the appended claims.

[0033] 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 those described in this specification. In addition, a single step described in this specification may be broken down into multiple steps for description in other embodiments; and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0034] 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, stored data, displayed data, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0035] With the development of technology, the amount of data of various types has exploded. Databases can provide services such as data storage and query. In related technologies, the storage structure of databases can include row storage and column storage.

[0036] Row-based storage refers to storing data in rows, each containing the values ​​of all fields in a table. Because all fields in a row are stored together, row-based storage offers excellent OLTP (Online Transaction Processing) capabilities, meeting the concurrency and consistency requirements of database data processing.

[0037] Column-based storage means storing data in columns as the basic unit. Data in the same column is stored together to facilitate data aggregation and compression. Therefore, compared with row-based storage, column-based storage greatly facilitates column-based query and filtering operations, improving the database's OLAP (Online Analysis Processing) performance.

[0038] A database index is a data structure that speeds up data queries within a database table. Indexes are used to quickly locate data without having to search every row in the table each time it is accessed. In related technologies, some databases not only apply columnar storage structures to data tables, but also apply them to indexes to build column-based indexes, thereby improving the performance of column-based queries and accelerating database OLAP operations. However, as data volumes continue to grow, database query performance will be affected, regardless of whether row-based or column-based indexes are used, resulting in poor data query efficiency.

[0039] Based on this, one or more embodiments of this specification provide a data query method, device, database system and storage medium, aiming to construct row storage data and column storage data respectively under the same index file, use redundant row and column storage data to quickly filter data, and improve the data query efficiency of the database.

[0040] The data query method of the embodiments of this specification can pre-build index data including row-store indexes and column-store indexes. Therefore, during data queries, these pre-built row-store indexes and column-store indexes can be simultaneously invoked to quickly filter the query data. For ease of understanding and explanation, the following first describes the index data construction process, followed by the data query process.

[0041] As shown in FIG. 1 , in some embodiments, the data query method exemplified in this specification includes steps S110 to S140 in which the index data is pre-built.

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

[0043] In the implementation manner of this specification, the data to be stored refers to data that needs to be written to the disk for permanent storage, and the preset index table refers to an index table generated by ordering the data to be stored based on one or more fields.

[0044] An index table includes one or more fields used to query data. Each row of data in the index table includes the values ​​corresponding to these fields. The index table also includes the primary key for each row of data. The primary key is one or more fields in the table, and the primary key value uniquely identifies the location of a row of data in the main table. For example, the index table in Figure 2 includes four columns of data, each corresponding to the fields "Age," "Length of Service," "Gender," and "Employee Number." The "Employee Number" field represents the primary key of the index table.

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

[0046] S120: Writing the data to be stored into the memory based on the preset index table.

[0047] In some implementations of this specification, an LSM tree (log-structured merge-tree) may be used as the basic storage data structure. However, those skilled in the art will appreciate that the data structure is not limited to the LSM tree and may also employ other data structures, such as a B-tree or a B+tree. This specification will use the LSM tree as an example to illustrate the implementations.

[0048] LSM Tree is a data structure that spans memory and disk, and includes the C0 Tree (also known as MemTable) in memory and multiple subtrees such as C1 Tree, C2 Tree, ..., Cn Tree on disk. LSM Tree writes write operations such as insertion, modification, and deletion into the memory Memtable in an append-write manner and pre-sorts them. When the amount of data in the MemTable reaches a certain threshold, the data is sequentially written to the disk for persistent storage. The dumped data structure is SSTable. Furthermore, in order to improve read performance, LSM Tree needs to regularly merge the SSTable files on disk. During the merge, the append-write operations for the same data will be merged to reduce the amount of data.

[0049] In the implementation manner of this specification, in combination with Figure 3, for the preset index table (Index Table), the data in the table is also the data to be stored as described in this specification. When constructing the index data, the data in the table can be written sequentially into the memory MemTable.

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

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

[0052] In the implementation of this specification, when writing data from the in-memory Memtable to the disk SSTable, not only is it necessary to write the data in row-wise storage to form row-stored data, but it is also necessary to write the data in each column in column-wise storage to form column-stored data. In other words, the SSTable written to the disk includes not only the row-stored data, but also the column-stored data for each column.

[0053] Taking the OceanBase database as an example, the database divides the disk into fixed-size macroblocks (Macro Blocks), and the internal data of the macroblocks is organized into multiple microblocks (Micro Blocks). As shown in Figure 3, when data is dumped from the in-memory MemTable to the disk SSTable, it is necessary to generate row storage (Row Store) data and column storage (Column Store) data under the same SSTable index file according to the row storage format and column storage format respectively. The row storage data under the SSTable is the data of row 1 to row n in the figure, and the column storage data is the data of C1 to C4 in the figure.

[0054] S140: Construct index data based on the row storage data and the column storage data.

[0055] In the implementation manner of this specification, after the row storage data and the column storage data are obtained through the aforementioned process, the above-mentioned row storage data and column storage data constitute index data for data query.

[0056] It is worth noting that in the implementation of this specification, compared with the traditional row storage index, a column storage index for each column of data is redundantly constructed based on the row storage index, and accelerated screening of query data can be achieved based on the row storage index and the column storage index at the same time. The process of data query is explained below in this specification.

[0057] Furthermore, in related technologies, such as SQL Server databases, when performing data queries, the optimizer selects the index data most relevant to the query column. When calling the index data to perform a query, the database can only call one index at a time. However, in the implementation of this specification, row-stored data and column-stored data are built into the same index data. Even if the database optimizer can only call one index during a data query, both the row-stored data and the column-stored data under the index data can be called simultaneously, implementing the data query process described below.

[0058] It is also understandable that column-stored data is generally compressed data in that column, making it difficult to perform in-place updates based on storage structures such as B-Tree and B+Tree, resulting in weak OLTP capabilities of the database. However, in some implementations of this specification, LSM Tree append-writes are used to store data, eliminating the need for in-place updates to SSTables. This does not affect the OLTP capabilities of the database, allowing the database to enhance OLAP capabilities using column-store indexes while retaining its original OLTP capabilities.

[0059] In the above-mentioned embodiment of Figure 1, the process of constructing index data based on a preset index table is explained. In fact, for the same data table, different preset index tables can be generated based on different query fields, so that corresponding index data can be constructed based on each preset index table according to the above-mentioned method process, which is explained below in conjunction with Figure 4.

[0060] As shown in FIG. 4 , in some embodiments, the data query method exemplified in this specification includes steps S410 to S420 in the process of constructing index data.

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

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

[0063] As can be seen from the foregoing, the data to be stored refers to data that needs to be written to a disk for permanent storage. For example, a data table of the data to be stored is shown in FIG2 .

[0064] It is understood that the data table to be stored includes multiple fields, such as "age", "years of service", "gender", and "employee number". The preset index table can be a data table obtained by sorting data based on one or more fields. For example, in the example of Figure 2, based on the "age" field being sorted from smallest to largest, an index table related to the "age" field can be obtained.

[0065] Similarly, data can also be sorted based on any field such as "length of service" or "gender", and when the value of the field is the same, sorting can be performed based on the next field value. For example, in one example, the data table shown in Figure 2 can be sorted from small to large based on the "age" field. For data with the same value of the age field, it can be further sorted from small to large based on the value of the "length of service" field, and so on.

[0066] Similarly, by processing the data table to be stored based on different query fields, multiple corresponding preset index tables can be obtained. For each preset index table, corresponding index data can be constructed according to the method process shown in Figure 1.

[0067] In the embodiments of this specification, multiple index data can be constructed based on multiple preset index tables, and at least one of the index data can be used as a clustered storage index, while the remaining index data can be used as non-clustered storage indexes. Clustered storage indexes and non-clustered storage indexes have the same functions, except that a clustered storage index is the primary storage index for the entire data table, while a non-clustered index is a secondary index created for the data table.

[0068] In related technologies, such as SQL Server databases, columnstore indexes in SQL Server are limited to a single nonclustered columnstore index due to other constraints. However, in the implementations of this specification, there is no limit on the number of nonclustered columnstore indexes. Multiple nonclustered columnstore indexes can be built based on multiple pre-set index tables, providing better support for OLTP tasks.

[0069] After constructing the index data SSTable including row storage data and column storage data through the above process, data query can be performed based on the index data SSTable, which is explained below in conjunction with Figure 5.

[0070] As shown in FIG5 , in some implementations, the data query method exemplified in this specification includes steps S510 to S540 .

[0071] S510: Obtain data query instructions.

[0072] In the implementation manner of this specification, a data query instruction refers to a query statement (query) used to query required data in a database. A data query instruction usually includes one or more query conditions. The database needs to filter data that meets the query conditions based on these query conditions and return the query results.

[0073] In some embodiments, the data query instruction may be expressed as a Structured Query Language (SQL) command, such as a Select command. Of course, the data query instruction is not limited thereto. For example, in some embodiments, the data query instruction may be implemented using other operating languages ​​of relational databases, operating languages ​​of non-relational databases, or other feasible computer languages ​​or information transmission formats. For example, the data query instruction may be expressed as a Hypertext Transfer Protocol (http) request, such as a data request using the Get method.

[0074] In addition, the one or more query conditions carried in the data query instruction can also have multiple specific forms of expression, and the forms of expression vary depending on the data query instruction. For example, a basic SQL query instruction can be expressed as "SELECT ID, Name FROM Student WHERE ID=5", which is used to query the number and name of the student with ID 5 in the data table Student. Therefore, the query condition in the query instruction can include two parts, one of which is "ID" and "Name" appearing after the SELECT identifier, which are used to represent the attributes of the queried data, and the other part is "ID=5" appearing after the WHERE identifier, which is used to represent the conditions that the data needs to meet.

[0075] It can be seen from the above description that in the implementation mode of this specification, the query condition carried in the data query instruction may include more than one, and the query condition may not be limited to a numerical relational expression, and the queried data records should meet all query conditions in the query instruction.

[0076] Of course, the above is just an example, and the implementation methods of this specification do not impose any restrictions on the specific form of the query conditions. For example, in some implementation methods of this specification, the target data record can also be located according to the row address and / or column address, so that the query condition corresponding to the query instruction can be related information of the row address and / or column address.

[0077] S520 : Based on at least one query condition among the multiple query conditions and the row storage data in the pre-built index data, perform range filtering on the index data to obtain a first data range.

[0078] In the implementation manner of this specification, when the data query instruction includes multiple query conditions, the row storage data in the aforementioned constructed index data can be first used, and the entire index data can be preliminarily range filtered according to one or more query conditions in the data query instruction to select data rows that meet the query conditions.

[0079] For example, a query instruction indicates "query all male employees in the table who are older than 40 years old". The query conditions include "age is older than 40 years old" and "gender is male", and the data in the query result must meet all the query conditions.

[0080] In this scenario, we can first use the query field "Age" corresponding to the query condition "Age greater than 40" and use row storage data to quickly filter out the row offsets in the index data table for rows with values ​​greater than 40 in the "Age" field. Then, based on this row offset, we filter out all rows with values ​​less than or equal to 40 in the "Age" field, 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 values ​​greater than 40 in the "Age" field.

[0081] 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.

[0082] S530: Based on other query conditions in the multiple query conditions and the column storage data in the index data, filter the first data range to obtain a second data range.

[0083] It can be understood that in S520, the data rows of the data table can be filtered based on the row storage data. Then, for the first data range after the row filtering, the column storage data in the aforementioned index data can be further combined to achieve fast screening of the data range.

[0084] In some embodiments, for other query conditions in the data query instruction, the column storage data that needs to be called can be determined based on the field corresponding to the query condition, and 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.

[0085] Using the scenario described above as an example, after filtering rows based on the query condition "Age greater than 40" and the row-stored data to obtain the first data range, the query condition "Gender is Male" determines that the corresponding field is "Gender." The column-stored data is then used to obtain a column data range containing the "Gender" field. As can be seen, since the column data range includes the gender data for the entire table, it is necessary to intersect this column data range with the first data range. The resulting second data range contains the gender data for all employees aged over 40.

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

[0087] In the implementation manner of this specification, after obtaining the second data range through the aforementioned data row filtering and data column filtering process, the data in the second data range can be read and processed accordingly to obtain the final data query result.

[0088] The data processing method needs to be determined based on the query conditions in the data query instruction. For example, in the previous example, the data query condition represents "male employees over 40 years old". Then, for each gender data in the second data range, the data with the value of "male" can be further filtered out, and the data query result is returned. The data query result includes all data information of all employees over 40 years old and male.

[0089] For example, in another example, the data query condition represents "the total number of male employees over 40 years old". After filtering out the data with the value of "male" for each gender data in the second data range, the filtered data is further aggregated to calculate the total number of data, and the data query result is returned. The data query result includes the number of all employees over 40 years old and whose gender is male.

[0090] Of course, those skilled in the art will understand that the manner of processing the data in the second data range is not limited to the above example, and this specification will not elaborate on this.

[0091] In some embodiments of this specification, when reading data within a second data range, the number of columns of the data in the second data range can be determined. If the data in the second data range has a large number of columns, reading the data based on column-based data storage requires performing a large number of decompression operations, and the IO overhead of the data query will exceed that of row-based data storage. Conversely, if the data in the second data range has a small number of columns, reading the data based on row-based data storage requires reading a large amount of redundant data in each row of data, and the IO overhead of the data query will far exceed that of column-based data storage.

[0092] Therefore, in some implementations of this specification, a corresponding preset threshold value can be set in advance for the number of columns in the second data range. During the data query stage, after determining the second data range, the number of columns included in the second data range can be compared with the preset threshold value. If the number of columns included in the second data range is greater than the preset threshold value, it means that there are many columns in the second data range that currently need to be queried. At this time, if the data is read based on column storage data, the IO overhead will be very large, so the required data can be read based on row storage data. Conversely, if the number of columns included in the second data range is less than or equal to the preset threshold value, it means that there are fewer columns in the second data range that currently need to be queried. At this time, if the data is read based on column storage data, the query efficiency will be greatly improved, and the IO overhead will be reduced compared to row storage data. Therefore, the required data can be read based on column storage data. This will be explained in the following implementations of this specification.

[0093] After reading the data within the second data range, the data can be processed according to the query conditions in the data query instruction according to the aforementioned data processing process to obtain and return the corresponding data query results, which will not be described in detail in this specification.

[0094] From the above, it can be seen that in the implementation mode of this specification, by pre-constructing index data including both row storage data and column storage data, row storage data and column storage data can be called simultaneously in data query operations, and the data intersection can be calculated using row storage data and column storage data, so as to quickly filter out the queried data range and improve the data query efficiency of the database.

[0095] As shown in FIG6 , in some embodiments, the data query method exemplified in this specification, the process of performing range filtering on index data to obtain a first data range, includes steps S521 to S522 .

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

[0097] S522: Scale the index data based on the row offset to obtain a first data range.

[0098] In the implementation manner of this specification, when performing a data query operation, first, based on the query field corresponding to the query condition and in combination with the row storage data in the pre-built index data, data rows that meet the query condition can be screened out.

[0099] It is worth noting that, in conjunction with the aforementioned embodiments, multiple preset index tables can be generated based on multiple query fields, and multiple index data can be constructed in the embodiments of this specification. Therefore, in the embodiments of this specification, when performing a data query, the target index data can first be determined from the multiple index data based on the query field corresponding to the query condition.

[0100] 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 years old and have more than 3 years of work experience". Therefore, based on the query field "age" corresponding to the query condition "age older than 30 years old", the target index data sorted by age can be determined from multiple index data.

[0101] For example, in one example, the preset index table corresponding to the target index data can be shown in Figure 7. The target index data includes four columns of data, each corresponding to the fields "Age," "Length of Service," "Gender," and "Employee Number," respectively. The "Employee Number" field represents the primary key of the index table. In the example in Figure 7, the index table is sorted from youngest to oldest based on the age field. If the age is the same, it is sorted based on the "Length of Service" field. If the length of service is the same, it is further sorted based on the "Gender" field.

[0102] In this example scenario, the query condition "Age greater than 30 years old" is first determined to correspond to the query field "Age". Then, based on the row-stored data and the query condition, the row offset (offset) that satisfies "Age greater than 30 years old" is determined in the index data. The row offset indicates the location of the data row that meets the query condition. For example, in Figure 7, the third row of data, "Age = 31, Years of Service = 2, Gender = Male", meets the query condition "Age greater than 30 years old". Therefore, the row offset is 3, indicating that the data in the third row and below meets the query condition.

[0103] After determining the row offset based on the row storage data, the index data is range-filtered based on the row offset to obtain the filtered first data range. For example, in the example scenario in Figure 7, filtering the first two rows of data range based on the row offset yields the remaining first data range. This first data range represents the data range of the third and subsequent rows in the solid-line box in Figure 7.

[0104] As shown in FIG8 , in some embodiments, the data query method exemplified in this specification, the process of filtering the first data range to obtain the second data range, includes steps S531 to S532 .

[0105] S531 . Based on the query fields and column storage data corresponding to other query conditions, determine a column data range that meets the query conditions from the index data.

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

[0107] Using the scenario shown in Figure 7 as an example, the data query instruction also includes the query conditions "Work experience greater than 3 years" and "Gender is male," corresponding to the query fields "Work experience" and "Gender," respectively. Therefore, by storing data in columns based on these query fields, two columns of data under the "Gender" and "Work experience" fields can be obtained.

[0108] For example, in Figure 7, the column data range selected based on the column-stored data is the data range within the dashed box. Then, by calculating the intersection of the column data range and the first data range, a second data range is obtained. In other words, in Figure 7, the intersection of the dashed-box column data range and the solid-box first data range is calculated to obtain the second data range. The data within the second data range represents the data that meets all the query conditions in the data query instruction.

[0109] After determining the second range of data, it is necessary to read corresponding data from the second range of data. In some embodiments of this specification, the process of reading data in the second data range includes: in response to the number of columns of data in the second data range being greater than a preset threshold, reading data in the second data range based on row storage data; in response to the number of columns of data in the second data range being greater than a preset threshold, reading data in the second data range based on column storage data.

[0110] It should be noted that the example in Figure 7 only shows a scenario with a small number of columns for ease of understanding and illustration. In reality, in big data processing scenarios, the number of columns in a data table is very large, and column-based data storage generally requires column data compression. Therefore, during the data query phase, reading data from many columns based on column-based data storage requires a large amount of data decompression operations, resulting in a high IO overhead for data queries, even exceeding the overhead of reading the corresponding column data based on row-based data storage.

[0111] Therefore, in some implementations 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 storage data or column storage data. The specific value of the preset threshold can be selected according to the application scenario, and this specification does not impose any restrictions on this.

[0112] In some embodiments, 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 a large number of columns need to be read. If data is read based on column storage data, it will not only not improve query performance, but may generate a large amount of IO overhead. Therefore, row storage data can be called to read data in the second data range.

[0113] 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 small. If data is read based on column-stored data, the data query efficiency will be greatly improved, and the IO overhead will be reduced compared to row-stored data. Therefore, column-stored data can be called to read data in the second data range.

[0114] After obtaining the data within the second data range, the data can be processed according to the query conditions in the data query instruction according to the aforementioned data processing process to obtain and return the corresponding data query result. For example, in the example scenario of Figure 7, after obtaining 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 aged over 30 and with more than 3 years of service, and this data query result is returned.

[0115] As can be seen from the above, in the implementation of this specification, row-stored data and column-stored data can be simultaneously called during data queries, and data intersections can be calculated using the row-stored and column-stored data, thereby quickly filtering the queried data range and improving database data query efficiency. Furthermore, when reading data, row-stored data or column-stored data can be selected based on the number of columns in the data to be read, thereby reducing I / O overhead and further improving data query efficiency and the database's OLAP capabilities.

[0116] In addition, combined with the above, it can be seen that in the implementation of this specification, there is no limit on the number of non-clustered storage indexes. Multiple non-clustered column storage indexes can be constructed based on multiple preset index tables to improve the OLTP capabilities of the database. Moreover, in the implementation of this specification, the LSM Tree append-write method is used to store data, eliminating the need to update the SSTable in place. Therefore, it will not affect the OLTP capabilities of the database. This allows the database to improve its OLAP capabilities by using column storage data while retaining its original OLTP capabilities.

[0117] In some embodiments, the present specification provides a data query device, as shown in Figure 9, the data query device includes: an instruction acquisition module 10, configured to obtain a data query instruction, the data query instruction including multiple query conditions; a first filtering module 20, configured to perform range filtering on the index data to obtain a first data range based on at least one query condition among the multiple query conditions and row storage data in pre-built index data; a second filtering module 30, configured to filter the first data range to obtain a second data range based on other query conditions among the multiple query conditions and column storage data in the index data; a query result module 40, configured to process the data in the second data range based on the data query instruction to obtain a data query result.

[0118] From the above, it can be seen that in the implementation mode of this specification, by pre-building row storage data and column storage data, row storage data and column storage data can be called simultaneously in data query operations, and the data intersection can be calculated using row storage data and column storage data, so as to quickly filter out the queried data range and improve the data query efficiency of the database.

[0119] In one or more embodiments of the present specification, the first filtering module 20 is configured to: determine a row offset that meets the query condition from the index data based on the query field corresponding to the at least one query condition and the row storage data; and perform range filtering on the index data based on the row offset to obtain the first data range.

[0120] In one or more embodiments of the present specification, the second filtering module 30 is configured to: determine the column data range that meets the query conditions from the index data based on the query fields corresponding to the other query conditions and the column storage data; and determine the second data range based on the intersection of the column data range and the first data range.

[0121] In one or more embodiments of the present specification, the query result module 40 is configured to: in response to the number of columns of data in the second data range being greater than a preset threshold, read the data in the second data range based on the row storage data, and process the data to obtain the data query result; in response to the column data of the data in the second data range being less than or equal to the preset threshold, read the data in the second data range based on the column storage data, and process the data to obtain the data query result.

[0122] In one or more embodiments of the present specification, the first filtering module 20 is configured to: determine target index data from a plurality of pre-constructed index data based on a query field corresponding to the at least one query condition; and perform range filtering on the target index data to obtain a first data range based on the at least one query condition and the target index data.

[0123] In one or more embodiments of the present specification, the device also includes an index construction module, which is configured to: generate a preset index table based on the data to be stored; write the data to be stored into the memory based on the preset index table; in response to the amount of data written in the memory reaching a preset capacity, generate row storage data and column storage data for each column based on the data in the memory; and construct the index data based on the row storage data and the column storage data.

[0124] In one or more embodiments of the present specification, the index construction module is configured to: generate multiple preset index tables based on the query fields in the data to be stored; for each preset index table, construct corresponding index data based on the preset index table.

[0125] As can be seen from the above, in the implementation of this specification, row-stored data and column-stored data can be simultaneously called during data queries, and data intersections can be calculated using the row-stored and column-stored data, thereby quickly filtering the queried data range and improving database data query efficiency. Furthermore, when reading data, row-stored data or column-stored data can be selected based on the number of columns in the data to be read, thereby reducing I / O overhead and further improving data query efficiency and the database's OLAP capabilities.

[0126] In addition, combined with the above, it can be seen that in the implementation of this specification, there is no limit on the number of non-clustered storage indexes. Multiple non-clustered column storage indexes can be constructed based on multiple preset index tables to improve the OLTP capabilities of the database. Moreover, in the implementation of this specification, the LSM Tree append-write method is used to store data, eliminating the need to update the SSTable in place. Therefore, it will not affect the OLTP capabilities of the database. This allows the database to improve its OLAP capabilities by using column storage data while retaining its original OLTP capabilities.

[0127] In some embodiments, this specification provides a database system, comprising: a processor; and a memory storing computer instructions, wherein the computer instructions are used to enable the processor to execute the method described in any of the above embodiments.

[0128] In some embodiments, this specification provides a storage medium storing computer instructions, wherein the computer instructions are used to enable a computer to execute the method described in any of the above embodiments.

[0129] FIG10 is a schematic structural diagram of a database system provided by an exemplary embodiment. Referring to FIG10 , at the hardware level, the system includes a processor 702, an internal bus 704, a network interface 706, a memory 708, and a non-volatile memory 710, and may also include hardware required for other scenarios. One or more embodiments of this specification may be implemented based on software, such as the processor 702 reading the corresponding computer program from the non-volatile memory 710 into the 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 software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but may also be hardware or logic devices.

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

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

[0132] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0133] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information using any method or technology. Information can be computer-readable instructions, data structures, program modules, 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 technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory media such as modulated data signals and carrier waves.

[0134] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0135] The foregoing description of specific embodiments of this specification describes the process. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in an order different from that described in the embodiments and still achieve the desired results. Furthermore, the processes depicted in the accompanying drawings do not necessarily require the specific order shown or the sequential order to achieve the desired results. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0136] The terms used in one or more embodiments of this specification are for the purpose of describing specific embodiments only and are not intended to limit one or more embodiments of this specification. The singular forms "a," "an," "the," and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0137] It should be understood that although the terms first, second, third, etc. may be used in one or more embodiments of this specification to describe various information, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. 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 "at the time of" or "when" or "in response to determining."

[0138] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit 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 in the scope of protection of one or more embodiments of this specification.

Claims

1. A data query method, comprising: Obtaining a data query instruction, wherein the data query instruction includes a plurality of query conditions; Based on at least one query condition among the multiple query conditions and the row storage data in the pre-built index data, range filtering is performed on the index data to obtain a first data range; Based on other query conditions in the multiple query conditions and the column storage data in the index data, the first data range is filtered to obtain a second data range; The data in the second data range is processed based on the data query instruction to obtain a data query result.

2. The method according to claim 1, wherein: The performing range filtering on the index data to obtain the first data range based on at least one query condition among the multiple query conditions and the 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, determining a row offset that meets the query condition from the index data; The index data is range-filtered based on the row offset to obtain the first data range.

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

4. The method according to claim 1, wherein: The processing of the data in the second data range based on the data query instruction to obtain a data query result includes: In response to the number of columns of data in the second data range being greater than a preset threshold, reading the data in the second data range based on the row storage data, and processing the data to obtain the data query result; In response to the column data of the data in the second data range being less than or equal to the preset threshold, the 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 performing range filtering on the index data to obtain the first data range based on at least one query condition among the multiple query conditions and the row storage data in the pre-built index data includes: Determining target index data from a plurality of pre-constructed index data based on a query field corresponding to the at least one query condition; Based on the at least one query condition and the target index data, range filtering is performed on the target index data to obtain a first data range.

6. The method according to any one of claims 1 to 5, wherein: The process of pre-building the index data includes: Generate a preset index table based on the data to be stored; Writing the data to be stored into the memory based on the preset index table; In response to the amount of data written in the memory reaching a preset capacity, generating row storage data and column storage data of each column based on the data in the memory respectively; 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-building the index data includes: 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: An instruction acquisition module is configured to acquire a data query instruction, wherein the data query instruction includes a plurality of query conditions; A first filtering module is configured to perform range filtering on the index data to obtain a first data range based on at least one query condition among the multiple query conditions and row storage data in the pre-built index data; A second filtering module is configured to filter the first data range to obtain a second data range based on other query conditions in the multiple query conditions and column storage data in the index data; The query result module is configured to process the data in the second data range based on the data query instruction to obtain a data query result.

9. A database system comprising: processor; and A memory storing computer instructions, wherein the computer instructions are used to enable a processor to execute the method according to any one of claims 1 to 7.

10. A storage medium storing computer instructions, wherein the computer instructions are used to enable a computer to execute the method according to any one of claims 1 to 7.

Images