Data query method, data storage method, computing device, and storage medium

Abstract

Embodiments of the present specification provide a data query method, a data storage method, a computing device and a storage medium, wherein the data query method comprises: receiving a table lookup request, wherein the table lookup request comprises a data identifier of to-be-queried data; based on the data identifier, searching a first record unit containing the data identifier in a secondary index, wherein the secondary index comprises a hidden column, and the hidden column is used to store a record position of a primary key value of stored data in a clustered index; and querying the to-be-queried data in the clustered index according to a target record position corresponding to the first record unit. The complete scanning of the clustered index required in the table lookup process is reduced, thereby reducing the amount of scanned data, reducing the amount of disk read-write data, further shortening the data query delay, improving the data query efficiency, and at the same time reducing the resource occupation and improving the overall system throughput.

Description

Technical Field

[0001] The embodiments in this specification relate to the field of database technology, and in particular to a data query method and a data storage method. Background Technology

[0002] With the development of science and technology, the demand for database management systems in handling large-scale data storage and efficient querying is increasing.

[0003] Currently, in order to improve data query efficiency, database systems typically use index structures to accelerate data retrieval through table lookups. This method uses secondary indexes to quickly locate records that meet the criteria and clustered indexes to obtain complete record units for data querying.

[0004] However, current table lookup methods lead to high disk read / write overhead and resource consumption when processing large-scale data, especially when multiple table lookups are required. Since each table lookup requires a complete scan of the clustered index, it not only increases query latency but also reduces the overall data throughput of the system, impacting query efficiency. Therefore, a more efficient and resource-efficient data query method is urgently needed. Summary of the Invention

[0005] In view of this, embodiments of this specification provide a data query method. One or more embodiments of this specification also relate to a data storage method, a data query device, a data storage device, a computing device, a computer-readable storage medium, and a computer program product, to address the technical deficiencies existing in the prior art.

[0006] According to a first aspect of the embodiments of this specification, a data query method is provided, including: Receive a table lookup request, wherein the table lookup request includes the data identifier of the data to be queried; Based on the data identifier, the first record cell containing the data identifier is found in the secondary index. The secondary index includes a hidden column, which is used to store the record position of the primary key value of the stored data in the clustered index. Based on the target record position corresponding to the first record unit, query the data to be queried in the clustered index.

[0007] According to a second aspect of the embodiments of this specification, a data storage method is provided, comprising: Receive a data storage request, wherein the data storage request includes the data to be stored; Store the data to be stored in the clustered index; Write the record position of the data to be stored in the clustered index into the hidden column of the secondary index.

[0008] According to a third aspect of the embodiments of this specification, a data query apparatus is provided, comprising: The first receiving module is configured to receive a table lookup request, wherein the table lookup request includes the data identifier of the data to be queried; The lookup module is configured to search for the first record cell containing the data identifier in the secondary index based on the data identifier. The secondary index includes a hidden column that stores the record position of the primary key value of the stored data in the clustered index. The query module is configured to query the data to be queried in the clustered index based on the target record position corresponding to the first record unit.

[0009] According to a fourth aspect of the embodiments of this specification, a data storage device is provided, comprising: The second receiving module is configured to receive data storage requests, wherein the data storage requests include data to be stored; The storage module is configured to store data to be stored in a clustered index; The write module is configured to write the record position of the data to be stored in the clustered index to a hidden column of the secondary index.

[0010] According to a fifth aspect of the embodiments of this specification, a computing device is provided, comprising: Memory and processor; The memory is used to store computer-executable instructions, and the processor is used to execute the computer program / instructions, which, when executed by the processor, implement the steps of the above method.

[0011] According to a sixth aspect of the embodiments of this specification, a computer-readable storage medium is provided that stores a computer program / instructions that, when executed by a processor, implement the steps of the above-described method.

[0012] According to a seventh aspect of the embodiments of this specification, a computer program product is provided, including a computer program / instructions that, when executed by a processor, implement the steps of the above-described method.

[0013] One embodiment of this specification implements a data query method, including: receiving a table lookup request, wherein the table lookup request includes a data identifier of the data to be queried; based on the data identifier, searching for a first record unit containing the data identifier in a secondary index, wherein the secondary index includes a hidden column used to store the record position of the primary key value of the stored data in the clustered index; and querying the data to be queried in the clustered index according to the target record position corresponding to the first record unit.

[0014] By receiving a table lookup request carrying a data identifier of the data to be queried, the system determines the first record unit and the target record position corresponding to the first record unit in the secondary index based on the data identifier, and then queries the data to be queried in the clustered index based on the target record position. This reduces the need for a full scan of the clustered index during the table lookup process, thereby reducing the amount of data scanned, reducing the amount of data read and write on disk, and thus shortening the data query latency and improving the data query efficiency. At the same time, it also reduces resource consumption and improves the overall throughput of the system. Attached Figure Description

[0015] Figure 1 This is a schematic diagram of a prior art database index row format provided in one embodiment of this specification; Figure 2 This is a schematic diagram of a prior art table lookup path provided in one embodiment of this specification; Figure 3 This is a flowchart illustrating a data query method provided in one embodiment of this specification; Figure 4 This is a flowchart illustrating a data storage method provided in one embodiment of this specification; Figure 5 This is a schematic diagram of the index structure of a two-level index provided in one embodiment of this specification; Figure 6 This is a schematic diagram illustrating an embodiment of an accelerated table lookup path provided in this specification; Figure 7 This is a flowchart illustrating the processing procedure of a data query method provided in one embodiment of this specification; Figure 8 This is a schematic diagram of the structure of a data query device provided in one embodiment of this specification; Figure 9 This is a schematic diagram of the structure of a data storage device provided in one embodiment of this specification; Figure 10 This is a structural block diagram of a computing device provided in one embodiment of this specification. Detailed Implementation

[0016] Many specific details are set forth in the following description to provide a full understanding of this specification. However, this specification can be implemented in many other ways than those described herein, and those skilled in the art can make similar extensions without departing from the spirit of this specification. Therefore, this specification is not limited to the specific implementations disclosed below.

[0017] 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 be limiting of the one or more embodiments of this specification. The singular forms “a,” “described,” and “the” as used in one or more embodiments of this specification and 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 in one or more embodiments of this specification refers to and includes any or all possible combinations of one or more associated listed items.

[0018] It should be understood that although the terms first, second, 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, first may also be referred to as second without departing from the scope of one or more embodiments of this specification, and similarly, second may also be referred to as first. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to a determination."

[0019] Furthermore, it should be noted that 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 one or more embodiments of this specification are all information and data authorized by the user or fully authorized by all parties. Moreover, 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 entry points are provided for users to choose to authorize or refuse.

[0020] First, the terms and concepts used in one or more embodiments of this specification will be explained.

[0021] Secondary index: A secondary index is an auxiliary index structure used to accelerate query operations on non-primary key fields. It typically contains pointers to the record positions in the clustered index.

[0022] Clustered Index: A clustered index is an index structure in which data is physically arranged in primary key order. Each table can only have one clustered index.

[0023] Lookup Query: A lookup query is a process in a database system where a secondary index is used to quickly locate records that meet certain conditions, and then the clustered index is used to retrieve the complete data to be queried.

[0024] MySQL: MySQL is a widely used open-source relational database management system that supports multiple storage engines and advanced features.

[0025] Binary search: Binary search is an efficient search algorithm that quickly locates the target value by continuously halving the search interval. It is suitable for sorted data structures.

[0026] B+ Tree: A B+ tree is a balanced tree data structure commonly used in databases and file systems for efficient data retrieval and storage. Its characteristic is that all data is stored in leaf nodes, and there are links between leaf nodes.

[0027] Primary Key: A primary key (PK) is a field or combination of fields that uniquely identifies each record in a table, ensuring the uniqueness of each record, and is typically used to create clustered indexes. The value of the primary key field is the primary key value.

[0028] Secondary Key: A secondary key (SK) is a field in a table other than the primary key. It can be used to build secondary indexes to speed up specific query operations, but uniqueness is not guaranteed. The value of a secondary key field is the secondary key value.

[0029] With the development of science and technology, the demand for database management systems in handling large-scale data storage and efficient querying is increasing.

[0030] Currently, in order to improve data query efficiency, database systems typically use index structures to accelerate data retrieval through table lookups. This method uses secondary indexes to quickly locate records that meet the criteria and clustered indexes to obtain complete record units for data querying.

[0031] See Figure 1 , Figure 1 This specification illustrates a schematic diagram of a prior art database index row format according to an embodiment of the present specification, such as... Figure 1 As shown: The clustered index records store the full user records, along with transaction-related hidden columns such as the transaction number (trx_id), rollback segment pointer (roll_ptr), the primary key value (id), and non-primary key values ​​(k, c). The secondary index records, however, only retain the secondary key value (k) and the primary key value (id). Therefore, a typical table lookup path can be seen in [reference needed]. Figure 2 , Figure 2 This specification illustrates a schematic diagram of a prior art table lookup path according to an embodiment of the present specification, as shown below. Figure 2 As shown: After scanning the secondary index record (SK Record) "[k][id]" that meets the query conditions, it is also necessary to "return" to the clustered index to scan and obtain the complete row record "[id][trx_id][roll_ptr][k][c]" based on the primary key value [id] on the record.

[0032] However, this table lookup method leads to high disk read / write overhead and resource consumption when processing large-scale data, especially when multiple table lookups are required. Since each table lookup requires a complete scan of the clustered index, it not only increases query latency but also reduces the overall data throughput of the system, impacting query efficiency.

[0033] This specification provides a data query method in its embodiments. It also relates to a data storage method, a data query device, a data storage device, a computing device, a computer-readable storage medium, and a computer program product, which will be described in detail in the following embodiments.

[0034] See Figure 3 , Figure 3 A flowchart of a data query method according to an embodiment of this specification is shown, which specifically includes the following steps.

[0035] Step 302: Receive a table lookup request, wherein the table lookup request includes the data identifier of the data to be queried.

[0036] The data query methods provided in the embodiments of this specification are applied to relational database management systems (RDBMS), such as MySQL, Oracle, and PostgreSQL. MySQL is a widely used open-source relational database that supports multiple storage engines.

[0037] The data query methods provided in the embodiments of this specification can be used in various scenarios that require information management or information query. For example, in the scenario of managing product information, a database can be used to store information such as product name, product inventory, and product price; in the scenario of managing book information, a database can be used to store information such as book title, author, and publication date; and in the scenario of managing sports event information, a database can be used to store information such as event name, location, and time.

[0038] Different databases can use different storage engines. For example, MySQL can use the InnoDB storage engine; PostgreSQL can use a unified storage architecture; and Oracle can use the In-Memory storage engine. InnoDB is the default transaction-safe storage engine for MySQL, supporting row-level locking, foreign key constraints, and transaction processing.

[0039] Specifically, for the InnoDB storage engine in MySQL databases, B+ trees can be used to build secondary indexes and clustered indexes. The B+ tree is the default index structure used by the InnoDB storage engine and is suitable for most application scenarios, especially range queries and sorting operations. The leaf nodes of a B+ tree directly store record units (for clustered indexes) or primary key values ​​(for secondary indexes).

[0040] A primary key (PK) is a field or combination of fields used to uniquely identify each row of data records in a data table. Primary keys have the following characteristics: uniqueness (the primary key value is unique within the data table and there are no duplicate values); non-nullability (the primary key value cannot contain null values); and minimality (the primary key value should ideally be the smallest possible field or combination of fields to ensure uniqueness and avoid redundancy).

[0041] A secondary key (SK) refers to a key other than the primary key. For a given record unit, it can be a key field other than the primary key, storing the specific values ​​of the key fields in that record unit.

[0042] A clustered index is a special type of index that determines the physical storage order of data in a table. Typically, the primary key value of the data serves as the default clustered index. Clustered indexes are unique; that is, for any data stored in a clustered index, the corresponding index information is unique, usually identified by the primary key value.

[0043] Secondary indexes refer to indexes other than clustered indexes. The leaf nodes of a secondary index do not directly store record cells; instead, they store the values ​​of the indexed columns and the corresponding primary key values ​​pointing to the clustered index, facilitating table lookups. Specifically, for the same clustered index, different non-primary key values ​​(excluding the primary key value) can be selected as secondary key values ​​to build multiple secondary indexes. For example, in a product information management scenario, for product A, the product information stored in the clustered index could be: "[id: 101][Product Name: Product A][Product Inventory: 200][Product Price: 15]...", where id is the primary key value, and product name, product inventory, and product price are non-primary key values. Secondary indexes can be built using these non-primary key values. Taking product name as an example, the constructed secondary indexes could be: "[Product Name: Product A][id: 101]; [Product Name: Product B][id: 202]; [Product Name: Product C][id: 303]...".

[0044] A table lookup query refers to the process in a database system where a secondary index is used to quickly locate records that meet certain conditions, and then the clustered index (also known as the primary key index) is used to retrieve the complete data to be queried. Specifically, a table lookup query first finds the relevant primary key value using the secondary index, and then searches for and reads the complete record cell in the clustered index based on the primary key value. For example, in a scenario involving product information management, if the secondary index built around the product name finds the primary key value id of product A as 101, then the clustered index is used to find the record cell with primary key value id 101, and the complete information of product A is retrieved from it.

[0045] A table lookup request is a query request initiated by a user or application for the data to be queried. The purpose of a table lookup request is to obtain complete record units through secondary indexes and clustered indexes to complete the query of the data to be queried.

[0046] The data to be queried is the specific data that the table lookup query request needs to retrieve from the clustered index. The data to be queried is generally stored in the record cells of the clustered index in the form of rows or columns.

[0047] A data identifier is a specific identifier for the data to be queried, typically used to locate the corresponding record unit in a secondary index. For example, in a product information management scenario, product information can be queried based on the product name, where the product name is the data identifier; similarly, product information can be queried based on the product inventory, where the inventory is the data identifier. Likewise, in a book information management scenario, book information can be queried based on the book title, author, publication date, etc. Similarly, in a sports event information management scenario, sports event information can be queried based on the event name, location, and date, etc.

[0048] For example, taking the application scenario of product information management as an example, when querying the product information of product A, a table query request is received, in which the data identifier of the data to be queried, "product information of product A", is "product name: product A".

[0049] In this step, by receiving the table lookup request and determining the data identifier of the data to be queried, a data foundation is provided for subsequently determining the first record unit containing the data to be queried in the secondary index based on the data identifier.

[0050] Step 304: Based on the data identifier, find the first record cell containing the data identifier in the secondary index, wherein the secondary index includes a hidden column, which is used to store the record position of the primary key value of the stored data in the clustered index.

[0051] A record cell is the basic unit of data storage in a database. Data records can be stored in record cells in the form of rows or columns. In a secondary index, a record cell stores the secondary key value and the corresponding primary key value. In a clustered index, data records are stored in record cells within a data page. Each data record corresponds to one record cell, and each data page of a clustered index can contain multiple record cells. Each record cell contains all the information of that data record. Specifically, the record position of a record cell within a page of a clustered index can be determined by the page identifier (e.g., page number) and its position within the page (e.g., page offset). The page offset indicates the specific location of the record cell within the page.

[0052] Record location refers to the specific location information of the record unit where each data record is stored in a clustered index. Record location typically consists of two parts: the data page identifier (page id) and the data page offset (page offset).

[0053] Hidden columns refer to information implicitly stored within the index structure. This information is typically used to assist in queries and data location but is not directly exposed to users. The specific content of hidden columns depends on the index type (secondary index or clustered index). Specifically, adding a hidden column to a secondary index first requires creating or retrieving an index memory object for the secondary index. This involves maintaining a data structure (such as `dict_index_t`) in memory as the index memory object to store the metadata information of the secondary index. Next, the content of the hidden column needs to be determined. For example, record location information extracted from the clustered index based on the primary key value can be added to the hidden column. After determining the content to be stored in the hidden column, it is added to the index memory object of the secondary index. Finally, the index memory object containing the hidden column is written to the database's metadata data dictionary to ensure the persistent storage of the index structure so that the index state can be restored after a system restart.

[0054] In a secondary index, hidden columns can store data information other than the secondary key value and the primary key value. This includes the data type of the secondary key value or the record location information of the corresponding primary key value within the clustered index. For example, in a product information management scenario, for a secondary index built around the product name, if product A has a record in the secondary index containing both the product name and the primary key value, "[Product Name: Product A][id: 101]", then the hidden columns of the secondary index can include the record location of the product record corresponding to the primary key value "[id: 101]" within the clustered index, including the page number (page id: 123) and the page offset (page offset: 45), i.e., "[123, 45]".

[0055] In a clustered index, hidden columns typically include transaction-related metadata such as the transaction number (trx_id) and rollback segment pointer (roll_ptr).

[0056] For example, following the previous example, taking the application scenario of product information management as an example, when querying product information of product A, based on the data identifier "product name: product A", the first record unit containing the data identifier "[product name: product A][id: 101]" can be found in the secondary index built with the product name. And according to the primary key value "[id: 101]", the record position of the corresponding product information in the clustered index "[123, 45]" is determined in the hidden column.

[0057] In this step, the record containing the data identifier in the secondary index is found based on the data identifier in the query conditions, and the corresponding primary key value and its record position information in the clustered index are obtained, which provides a data foundation for retrieving the complete query data from the clustered index in the future.

[0058] Step 306: Based on the target record position corresponding to the first record unit, query the data to be queried in the clustered index.

[0059] The target record position corresponding to the first record unit is the record position of the data to be queried, which is determined in the hidden column of the secondary index based on the primary key value corresponding to the data identifier of the data to be queried.

[0060] Once the target record location is obtained, the data to be queried can be retrieved from the clustered index based on that location. Specifically, the record unit storing the data to be queried can be determined in the corresponding target page of the clustered index based on the data page identifier and data page offset included in the target record location, and the data to be queried can be read from it.

[0061] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, after determining the target record position "[123, 45]" corresponding to the first record unit "[Product Name: Product A][id: 101]", the product information of product A "[id: 101][Product Name: Product A][Product Inventory: 200][Product Price: 15]..." can be read at the offset 45 position in the 123 data page of the clustered index according to the target record position "[123, 45]".

[0062] In this embodiment, by receiving a table lookup request carrying a data identifier of the data to be queried, the first record unit and the target record position corresponding to the first record unit are determined in the secondary index based on the data identifier, and the data to be queried is queried in the clustered index based on the target record position. This reduces the need for a complete scan of the clustered index during the table lookup process, thereby reducing the amount of data scanned, reducing the amount of data read and written to the disk, and thus shortening the data query latency, improving the data query efficiency, reducing resource consumption, and improving the overall system throughput.

[0063] In an optional embodiment of this specification, after querying the data to be queried in the clustered index according to the target record position corresponding to the first record unit, the method further includes: If the data to be queried is not found based on the target record location, scan the clustered index to determine the record location of the target record cell in the clustered index that stores the data to be queried. Update the target record position in the secondary index based on the record position of the target record unit.

[0064] The target record unit is the actual record unit in the clustered index where the data to be queried is stored. The record location of the target record unit may differ from the target record location in the secondary index. This can be caused by various reasons. For example, the record unit of the data to be queried may have shifted within the data page stored in the clustered index, causing a change in the data page offset. Alternatively, the insertion of other stored data into the clustered index may have caused the data page to split, or the deletion of other stored data into the clustered index may have caused the data page to merge, thus changing the data page where the record unit of the data to be queried is located, i.e., changing the data page identifier.

[0065] If the data to be queried is not found at the target record location, that is, the primary key value of the data record stored in the record cell corresponding to the target record location does not match the primary key value of the data to be queried, it means that the target record location information in the secondary index has become invalid or inaccurate, and it is necessary to determine the actual location of the record cell where the data to be queried is stored.

[0066] By scanning the clustered index, the target record location of the record unit storing the data to be queried in the clustered index is determined. Specifically, a tree scan of the clustered index can be performed based on the primary key value. Starting from the root node of the clustered index, the search is performed layer by layer in each data page to accurately determine the leaf node containing the data to be queried, and then the target location of the record unit storing the data to be queried is determined within that leaf node.

[0067] After determining the record location of the target record unit, the target record location in the secondary index needs to be updated based on this record location to ensure that subsequent queries for the data to be queried can accurately find the data in the clustered index based on the updated target record location.

[0068] For example, following the previous example, taking the application scenario of product information management as an example, when querying product information of product A, if the product information of product A is not found at the offset 45 position in the 123 data page of the clustered index according to the target record position "[123, 45]", then it is necessary to scan the clustered index to determine that the position of the record unit corresponding to the product information of product A in the clustered index is the offset 55 position in the 126 data page, and the corresponding record position is "[126, 55]". Then, the target record position "[123, 45]" corresponding to the primary key value "[id: 101]" stored in the hidden column of the secondary index is updated to "[126, 55]".

[0069] In the embodiments described in this specification, when a query based on the target record location fails, the clustered index is scanned to determine the record location of the target record unit, and the target record location in the secondary index is updated. This ensures the consistency between the secondary index and the clustered index, as well as the reliability of the query. It can effectively handle the problem of record location changes caused by data page offsets, splits, or mergers, improve the accuracy of subsequent queries, simplify the query logic, reduce the full scan of the clustered index, reduce read and write overhead, and improve query efficiency, thereby significantly improving the overall performance and stability of the system.

[0070] In one optional embodiment of this specification, the target record location corresponding to the first record unit includes a data page identifier and a data page offset; querying the data to be queried in the clustered index according to the target record location corresponding to the first record unit includes: The target data page is determined in the clustered index based on the data page identifier; The second record unit is determined in the target data page based on the data page offset; Read the data to be queried from the second record unit.

[0071] A data page identifier is a number or identifier used in a clustered index to uniquely identify a specific data page. Each data page is typically a fixed-size storage unit (e.g., a common data page size is 16KB) containing multiple record units for storing multiple data records. The data page identifier helps quickly locate the data page containing a specific data record. Using the data page identifier, the database management system can quickly find the data page containing the record to be queried from the clustered index without needing to perform a full scan of the entire clustered index.

[0072] A data page offset refers to the specific location within a data page where the first record unit of a data record is stored. The data page offset represents the byte offset from the beginning of the data page to the data record. After identifying the correct data page, the data page offset helps the database system to further pinpoint the specific record unit.

[0073] The second record unit is the record unit determined by the clustered index based on the data page identifier and data page offset included in the target record location. This second record unit stores the complete record data of the data to be queried, including information on all fields and hidden columns related to the transaction. Reading the data to be queried from the second record unit allows you to obtain the complete record information.

[0074] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, the target record position corresponding to the first record unit is "[123, 45]", then the target data page is determined to be the 123rd data page in the clustered index. According to the data page offset 45, the position of offset 45 in the 123rd data page is determined to be the second record unit. Then, the product information of product A is read from the second record unit: "[id: 101][product name: product A][product inventory: 200][product price: 15]...".

[0075] In the embodiments described in this specification, by using the data page identifier and data page offset included in the target record location, the database system can efficiently locate and read the second record unit in the clustered index, thereby reducing the full scan of the clustered index and disk read / write operations, improving the accuracy and response speed of queries, and also reducing the consumption of system resources. This ensures that even in large-scale datasets and high-concurrency environments, the system can still quickly and accurately return the required data records.

[0076] In one optional embodiment of this specification, determining the second record unit in the target data page based on the data page offset includes: The second record unit is determined based on the matching result between the primary key value in the record unit corresponding to the data page offset and the target primary key value, where the target primary key value is the primary key value of the data to be queried.

[0077] The primary key value of the record cell corresponding to the data page offset refers to the value of the primary key field in the specific record cell located within the target data page based on the data page offset. Each record cell includes a primary key value, which uniquely identifies the data record within that record cell.

[0078] The target primary key value refers to the primary key value of the data to be queried. It is used to uniquely identify the specific data record being searched during the query process. The target primary key value can be determined by querying the secondary index based on the data identifier of the data to be queried. Specifically, the first record cell containing the data identifier is determined in the secondary index, and the target primary key value is read from this first record cell.

[0079] Primary key values ​​are important identifiers used by database systems to quickly locate and distinguish different records. By comparing primary key values, we can confirm whether the current record is the target record we are looking for.

[0080] The matching result refers to the comparison between the primary key value in the record cell corresponding to the data page offset and the target primary key value. If the two matching primary key values ​​are the same, the match is successful; if the two matching primary key values ​​are different, the match fails. The matching result determines whether the current record cell is the target record we are looking for. If the match is successful, we can confirm that this record cell is the second record cell; if the match fails, further searching or processing is required.

[0081] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, the target record position is "[123, 45]", then the corresponding record unit is determined in the clustered index according to the data page offset 45, the primary key value of the record unit is read, and the primary key value is matched with the target primary key value. Based on the matching result, it is determined whether the record unit corresponding to the data page offset 45 is the second record unit.

[0082] In the embodiments of this specification, by matching the primary key value in the record unit corresponding to the data page offset with the target primary key value, the database system can confirm whether the currently determined record unit is the record unit corresponding to the data to be queried, thereby improving the efficiency and accuracy of determining the record unit, and thus improving the accuracy and reliability of data query, and enhancing query efficiency and system performance.

[0083] In one optional embodiment of this specification, determining the second record unit based on the matching result between the primary key value in the record unit corresponding to the data page offset and the target primary key value includes: If the primary key value in the record unit corresponding to the data page offset matches the target primary key value, the record unit corresponding to the data page offset is determined as the second record unit. If the primary key value in the record cell corresponding to the data page offset does not match the target primary key value, a search is performed within the target data page to determine the second record cell whose primary key value matches the target primary key value.

[0084] If the primary key value in the record unit corresponding to the data page offset is the same as the target primary key value, it can be determined that the primary key value is successfully matched, that is, the data stored in the record unit is the data to be queried, and the record unit is confirmed as the second record unit.

[0085] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, the target record position is "[123, 45]". Then, in the clustered index, the corresponding record unit is determined according to the data page offset 45. The primary key value of the record unit is read as "[id: 101]", and the target primary key value is also "[id: 101]". Since they are the same, it is determined that the primary key value is successfully matched, that is, the product information stored in the record unit is the product information of product A. The record unit corresponding to the data page offset 45 is determined as the second record unit, and the product information of product A is read from the second record unit as "[id: 101][Product Name: Product A][Product Inventory: 200][Product Price: 15]...".

[0086] If the primary key value in the record cell corresponding to the data page offset is different from the target primary key value, it can be determined that the primary key value matching failed, that is, the data stored in the record cell is not the data to be queried. In this case, it is necessary to search within the current data page, obtain the primary key value corresponding to each record cell, and match it with the target primary key value to determine the record cell with the matching primary key value as the second record cell.

[0087] There are several methods to determine the second record unit within the target data page. For example, a linear search can be used, starting from the first record unit of the target data page and sequentially retrieving the primary key value of each record unit until the target value is found or the entire target data page is traversed. Alternatively, a binary search can be used. Since clustered indexes typically use a B+ tree index structure, the record units of data records in the target data page are arranged in order of primary key values. Therefore, by continuously dividing the search range in half within the target data page, determining the relationship between the middle key value of the divided search range and the primary key value of the data to be queried, and continuing the segmentation search within the half containing the primary key value of the data to be queried, the record unit matching the target primary key value can be found quickly.

[0088] For example, continuing with the previous example, taking the application scenario of product information management as an example, when querying product information for product A, the target record location is "[123, 45]". The corresponding record unit is determined in the clustered index based on the data page offset 45. The primary key value of this record unit is read as "[id: 202]", while the target primary key value is "[id: 101]". Since they are different, it is determined that the primary key value match fails, meaning that the product information stored in this record unit is not the product information for product A. Therefore, a search needs to be performed within the data page 123. Based on the primary key value of the record unit in the data page 123, the record unit that matches the target primary key value "[id: 101]" is determined as the second record unit, and the product information for product A, "[id: 101][Product Name: Product A][Product Inventory: 200][Product Price: 15]...", is read from the second record unit.

[0089] In the embodiments of this specification, the second record unit is directly confirmed when the primary key value in the record unit corresponding to the data page offset matches the target primary key value, or a linear search or binary search is used to search within the target data page when the primary key value fails to match, in order to determine the second record unit with the matching primary key value. This allows the database system to efficiently and accurately locate and read the data to be queried, improving the accuracy and response speed of the query, optimizing resource utilization, reducing the full scan of the clustered index and disk read / write operations, thereby improving the accuracy and reliability of data queries, and enhancing query efficiency and system performance.

[0090] In an optional embodiment of this specification, after searching within the target data page to determine a second record unit whose primary key value matches the target primary key value, the method further includes: If the primary key value of a record cell in the target data page does not match the target primary key value, scan the clustered index to determine the second record cell whose primary key value matches the target primary key value.

[0091] If the primary key value of the record cell corresponding to the target record location does not match the target primary key value, the search within the target data page will scan a smaller amount of data and consume fewer resources. Therefore, the search will be performed first within the target data page corresponding to the target record location. However, if the search within the target data page determines that the primary key values ​​of all record cells within it do not match the target primary key value, then the clustered index needs to be scanned further to identify a second record cell whose primary key value matches the target primary key value.

[0092] Specifically, the absence of a record unit in the target data page that matches the primary key value may be due to data insertion, data update, or other actions. These actions may have caused the target data page to split or merge, resulting in the record unit corresponding to the target record position migrating from one data page to another. In other words, the data page identifier has changed, but the target record position in the secondary index has not yet been updated.

[0093] To perform a complete search of a clustered index, a B+ tree search method can be used. Starting from the root node, a binary search is performed within the data page using the primary key value to locate the data page identifier of the leaf node at the next level. This method is used to search downwards layer by layer until the data page containing the target primary key value is finally determined, and the record unit with the matching primary key value is found within the data page.

[0094] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, the target record position is "[123, 45]", and the record unit corresponding to the data page offset 45 in the data page 123 fails to match the target primary key value. Furthermore, searching all record units in the data page 123 does not find a record unit that matches the target primary key value. Therefore, it is necessary to scan the clustered index. The primary key value of the record unit corresponding to the data page offset 55 in the data page 126 is found to be "[id: 101]", which matches the target primary key value "[id: 101]". Then, the second record unit at the record position "[126, 55]" is determined, and the product information of product A "[id: 101][Product Name: Product A][Product Inventory: 200][Product Price: 15]..." is read from the second record unit.

[0095] In the embodiments described in this specification, by first querying the target data page corresponding to the target record location, and further scanning the entire clustered index to determine the second record cell that matches the target primary key value when no record cell matching the primary key value is found, the database system can still accurately locate and read the data to be queried even when data pages are split or merged, thereby improving the effectiveness of data query.

[0096] In an optional embodiment of this specification, after determining the second record unit whose primary key value matches the target primary key value, the method further includes: Update the data page identifier and data page offset corresponding to the first record unit in the secondary index based on the data page identifier and data page offset corresponding to the second record unit.

[0097] After determining the second record unit that matches the primary key value with the target primary key value, the target record position corresponding to the first record unit in the secondary index can be updated according to the data page identifier and data page offset corresponding to the determined second record unit. This ensures that the information in the secondary index is consistent with the actual data storage location, thereby improving the accuracy and efficiency of subsequent queries.

[0098] Specifically, if the location of the determined second record unit differs from the target record location, the target record location needs to be updated. Updating the target record location can be understood as performing a write operation on the hidden column of the secondary index, with the written data being the updated target record location. The update can be categorized into updating only the data page offset and updating both the data page identifier and the data page offset simultaneously.

[0099] In practical applications, since scanning the target data page generally consumes less database read and write resources than writing to a secondary index, the target record position is usually not updated when the data page identifier is accurate but the data page offset is inaccurate. However, when the data page identifier is inaccurate, scanning the entire clustered index generally consumes significantly more database read and write resources than writing to a secondary index. Therefore, after a query with an inaccurate data page identifier, the target record position in the secondary index is updated immediately. Optionally, queries with accurate data page identifiers but inaccurate data page offsets can be recorded and updated uniformly when the database is idle or when a preset number of records are reached.

[0100] For example, following the previous example, taking the application scenario of product information management as an example, when querying the product information of product A, the target record position is "[123, 45]", but the actual determined record position of the second record unit is "[126, 55]", then the target record position "[123, 45]" corresponding to the primary key value "[id: 101]" of product A in the hidden column of the secondary index is updated to "[126, 55]".

[0101] In the embodiments of this specification, after determining the second record unit whose primary key value matches the target primary key value, the target record position corresponding to the first record unit in the secondary index is updated according to the data page identifier and data page offset corresponding to the second record unit. This ensures that the information in the secondary index is consistent with the actual data storage location, thereby improving the accuracy and efficiency of subsequent queries. Furthermore, it significantly reduces query complexity and resource consumption when the data page identifier is inaccurate, optimizes resource utilization, and enhances the stability and consistency of the system.

[0102] In one embodiment of this specification, a data storage method is provided. See also... Figure 4 , Figure 4 A flowchart of a data storage method according to an embodiment of this specification is shown, which specifically includes the following steps.

[0103] Step 402: Receive a data storage request, wherein the data storage request includes the data to be stored; Step 404: Store the data to be stored in the clustered index; Step 406: Write the record position of the data to be stored in the clustered index into the hidden column of the secondary index.

[0104] A data storage request is a request sent by a client or application that contains the data to be stored. This request may also include metadata related to the data to be stored, such as primary key values ​​and timestamps. For example, in a product information management scenario, storing product information for product B would include information such as product name, inventory, and price, as well as the primary key value of product B's information in the database. Similarly, in a book information management scenario, storing book information for book B would include information such as book title, author, and publication date, as well as the primary key value of book B's information in the database. Likewise, in a sports event information management scenario, storing sports event information for sports event B would include information such as event name, location, and time, as well as the primary key value of sports event B's information in the database.

[0105] During the storage of data, after receiving the data, the general logical order is to first store it in the clustered index, and then in the secondary index. By storing it in the clustered index first, the record position of the record cell into which the data to be stored can be obtained. Thus, when storing the primary key value and other key values ​​of the data to be stored into the corresponding secondary index, the record position of the record cell can be written as the target record position into the hidden column of the secondary index.

[0106] Specifically, when writing data to be stored into a clustered index, the data is usually stored in the corresponding record unit according to the order of the primary key values ​​of the data. At the same time, depending on the specific application scenario and requirements, other storage strategies can also be adopted, such as writing according to timestamp order or writing according to partition key order.

[0107] For example, taking the application scenario of product information management as an example, to store the product information of product B, the system receives a data storage request including the product information of product B, such as "[Product Name: Product B][Product Inventory: 280][Product Price: 20]...", stores the product information of product B in the record cell of the clustered index, and writes the record position of the record cell into the hidden column of the secondary index.

[0108] In the embodiments of this specification, after acquiring the data to be stored, the data is first stored in the clustered index, and the record position information is written into the hidden column of the secondary index. This ensures the consistency between the record position in the secondary index and the actual position of the record unit in the clustered index during the data storage process. This provides a data foundation for the data query process, ensures the accuracy and efficiency of the query, reduces the full scan of the clustered index and disk read / write operations during the data query process, optimizes resource utilization, guarantees the integrity and consistency of the data during the data storage process, and enhances the stability and reliability of the system.

[0109] In one optional embodiment of this specification, the recording location includes a data page identifier and a data page offset; Store the data to be stored in the clustered index, including: Based on the order of the primary key values ​​of the data to be stored, the data to be stored is stored in the record cells of the clustered index; Write the record position of the data to be stored in the clustered index into the hidden column of the secondary index, including: Write the data page identifier and data page offset of the record unit storing the data to be stored into the hidden column of the secondary index.

[0110] In database management systems, clustered indexes typically use a B+ tree index structure, which stores the data according to the order of the primary key values.

[0111] The primary key value of the data to be stored can typically be specified by the user or automatically generated by the system. Specifically, user-specified primary key values ​​include explicitly providing the value when storing the data. This value can be determined based on existing data or randomly assigned. For example, in a product information management system, if product information for product B is already stored and its primary key value is 1, the user can manually input a primary key value of 2 for product B. Automatic system generation includes automatically generating a unique primary key value for each newly inserted data entry. This can be done using an auto-incrementing primary key, where the database management system automatically generates an incrementing integer as the primary key value, or by determining the primary key value based on a timestamp or other key values.

[0112] Once the primary key value of the data to be stored is determined, the corresponding record cell in the clustered index can be determined according to the order of the primary key values ​​of the data to be stored, and the data to be stored can be stored in that record cell.

[0113] For example, following the previous example, taking the application scenario of product information management as an example, to store the product information of product B, the primary key value of the product information of product B "[Product Name: Product B][Product Inventory: 280][Product Price: 20]..." is determined to be "[id: 202]". Then, based on the primary key value and the product information already stored in the clustered index, the product information of product B is stored in the record unit of the clustered index.

[0114] After storing the data to be stored in a record cell of the clustered index, the record position of that record cell in the clustered index, namely the data page identifier and the data page offset, can be obtained, and the data page identifier and the data page offset can be written into the hidden column of the secondary index.

[0115] For example, following the previous example, taking the application scenario of product information management as an example, to store the product information of product B, when storing the product information of product B "[Product Name: Product B][Product Inventory: 280][Product Price: 20]" as the primary key value "[id: 202]" in the record cell of the clustered index, the record position "[124, 67]" including the data page identifier "pageid: 124" and the data page offset "page offset: 67" of the record cell is written into the hidden column of the secondary index.

[0116] In the embodiments described in this specification, the data to be stored is stored in the clustered index according to the order of the primary key values, and the data page identifier and data page offset of the record unit are written into the hidden column of the secondary index. This ensures the consistency between the record position in the secondary index and the actual position of the record unit in the clustered index during data storage, providing a data foundation for the data query process, ensuring the accuracy and efficiency of the query, reducing the full scan of the clustered index and disk read / write operations during the data query process, optimizing resource utilization, ensuring data integrity and consistency during data storage, and enhancing the stability and reliability of the system.

[0117] In one optional embodiment of this specification, storing the data to be stored in the record cells of the clustered index according to the order of the primary key values ​​of the data to be stored includes: Based on the order of the primary key values ​​of the data to be stored, determine the data page where the data should be stored; If there are free record units in the data page where the data to be stored is to be stored that have more free storage space than the storage space required by the data to be stored, then the data to be stored will be stored in the free record units. If there is no free record unit in the data page where the data to be stored is to be stored that has more free storage space than the storage space required by the data to be stored, then identify the blank data page that includes the free record unit, add the blank data page to the clustered index, and store the data to be stored in the free record unit.

[0118] Since clustered indexes typically use ordered index structures such as B+ trees, data is usually stored according to the order of the primary key values. For example, the data to be stored is written to the record cells in a data page in descending order of the primary key values.

[0119] Based on the order of the primary key values ​​of the data to be stored, the data page to which the data should be stored is determined. In a clustered index, the record units of each data page are known and arranged in order of primary key value. Therefore, the data page in the clustered index that stores the primary key value can be determined based on the primary key value of the data to be stored. For example, for a certain data to be stored whose primary key value is "[id: 80]", it can be determined that the data to be stored should be written to the data pages storing primary key values ​​"[id: 51]~[id: 100]".

[0120] After determining the corresponding data page, since the amount of data that can be stored in each data page is usually a fixed value (e.g., 16KB), it is necessary to compare the free storage space of the remaining free record units with the storage space required by the data to be stored. If the free storage space of the free record unit is greater than the storage space required by the data to be stored, the data to be stored is stored in the free record unit within that data page. If the free storage space of the free record unit is less than the storage space required by the data to be stored, a blank data page needs to be determined. This data page can be allocated by the database management system. The blank data page is added to the clustered index, and a free record unit for storing the data to be stored is determined within this blank data page. The data to be stored is then stored in the free record unit.

[0121] For example, for a certain piece of data to be stored, which requires 512 bytes of storage space and has a primary key value of "[id: 101]", and data records with primary key values ​​of "[id: 1]~[id: 100]" are stored in data page 127, then the free storage space of the remaining record units in data page 127 is determined to be 1KB, which is greater than the storage space required by the data to be stored. Therefore, the data to be stored can be placed in the free storage unit within data page 127. For another piece of data to be stored, which requires a storage space of... The primary key value is "[id: 102]". Data records with primary key values ​​"[id: 51]~[id: 101]" are stored in data page 127. It is determined that the free storage space of the remaining record units in data page 127 is 512B, which is less than the storage space required for the data to be stored. Therefore, the database management system can allocate a blank data page, add the blank data page to the clustered index as data page 128, and store the space to be stored in the free storage unit within the blank data page 128.

[0122] In the embodiments of this specification, the data page to be stored is determined according to the order of the primary key values ​​of the data to be stored. If there is a free record unit on the target data page with more free storage space than the storage space required by the data to be stored, the data to be stored is directly stored; or if there is no free record unit with more free storage space than the storage space required by the data to be stored, a blank data page is added to the clustered index, and the data to be stored is stored in the free record unit of the blank data page. This ensures the orderliness and efficiency of data storage, improves the accuracy and efficiency of queries, reduces the probability of data page splitting, and avoids the invalidation of stored record positions caused by frequent data page splitting, thereby improving the effectiveness and efficiency of data query process.

[0123] In an optional embodiment of this specification, before writing the record location of the data to be stored in the clustered index into the hidden column of the secondary index, the method further includes: Based on the primary key values ​​of the data already stored in the clustered index, construct a secondary index; Add a hidden column to the index memory object of the secondary index. The index memory object includes the metadata information of the secondary index, and the hidden column is used to store the record position of the primary key value of the stored data in the clustered index. Write the index memory object into the metadata data dictionary.

[0124] An index memory object is a data structure maintained in memory to store metadata information for a specific index. For example, in MySQL, `dict_index_t` is a common index memory object structure, containing the index's name, type, field information, etc. Index memory objects are primarily used to improve the efficiency of index operations, avoid frequent disk accesses, and facilitate index management and optimization by the database management system. When building a secondary index, its index memory object is also built simultaneously. Furthermore, when adding a hidden column to a secondary index, the metadata of that hidden column—such as the types of information stored in the hidden column and its mapping relationship with the primary key value—is actually stored in the hidden column memory object (e.g., `dict_field_t`) included in the secondary index's index memory object.

[0125] A metadata data dictionary is a data structure in a database management system used to store and manage metadata information about database objects (such as tables, indexes, views, constraints, etc.). This metadata information includes, but is not limited to: the name of the database object, the structural description of the object, the record location information of the object, and the logical relationships between the objects. The metadata data dictionary is typically stored internally within the database and is persistent, meaning that this metadata information will not be lost even if the database system restarts. It is crucial for the normal operation of the database and efficient querying.

[0126] When building a secondary index, non-primary key values ​​other than the primary key value are selected as secondary key values, and the secondary index is built based on the mapping relationship between the secondary key value and the primary key value.

[0127] See Figure 5 , Figure 5 The diagram illustrates a secondary index structure according to an embodiment of this specification. In addition to the secondary key value "[k]" and the primary key value "[id]", the secondary index record (SK Record) also has a hidden column for recording the record position (pk_position) of the primary key value in the clustered index, including the data page identifier (page id) and the data page offset (page offset).

[0128] For example, taking the application scenario of commodity information management as an example, for multiple commodity information stored in the clustered index: "[id: 101][commodity name: commodity A][commodity inventory: 200][commodity price: 15]...", "[id: 202][commodity name: commodity B][commodity inventory: 280][commodity price: 20]...", "[id: 303][commodity name: commodity C][commodity inventory: 330][commodity price: 30]...", the commodity name can be selected as the secondary key value to construct the secondary index as: "[commodity name: commodity A][id: 101], [commodity name: commodity B][id: 202], [commodity name: commodity C][id: 303]".

[0129] After constructing a secondary index and adding a hidden column to the index memory object of the secondary index, the record position of the primary key value of the stored data in the clustered index can be written to the hidden column, and the index memory object can be written to the metadata data dictionary to achieve persistent storage of the index structure of the secondary index, ensuring that the index state can be restored even after the system restarts.

[0130] Optionally, to determine whether to add hidden columns to the in-memory object of the secondary index when building the secondary index, a user-controlled MySQL parameter can be added to the secondary index. The parameter value can be either 1 or 0 to control whether to add hidden columns when building the secondary index.

[0131] Adding a hidden column to a secondary index to store the location of stored data in the clustered index will occupy some storage space. However, for scenarios that require improved data query efficiency, the benefits of this "space-for-time" strategy far outweigh the cost. Furthermore, since the clustered index stores all the stored data, in practical applications, the storage space occupied by the clustered index is the main storage cost. Adding an extra hidden column to the secondary index will not place an excessive burden on storage space.

[0132] In this embodiment, a secondary index is constructed based on the primary key values ​​of each stored data in the clustered index. A hidden column is added to the index memory object to store record location information. Finally, the index memory object is written to the metadata data dictionary to achieve persistent storage. This achieves persistent storage of the index structure, ensuring that the index state can be restored after the system restarts. This guarantees the high availability and data integrity of the system, provides a data foundation for the data query process, and ensures the accuracy and efficiency of data queries.

[0133] See Figure 6 , Figure 6 This diagram illustrates an embodiment of an accelerated table lookup path provided in this specification, such as... Figure 6 As shown: After determining the first record cell "[k][id]" in the secondary index, the record position (pk_position) of the primary key value in the clustered index is determined from the persistent hidden column based on the primary key value [id]. Then, based on the page identifier (page id) and page offset included therein, the second record cell is directly located, and the complete record of the data to be queried, "[id][trx_id][roll_ptr][k][c]", is read from it. This avoids a complete scan of the entire clustered index, reduces the amount of data scanned, and improves the accuracy and efficiency of data queries.

[0134] The following is in conjunction with the appendix Figure 7 Taking the data query method provided in this specification as an example in a product information management scenario, the data query method will be further explained. Among other things, Figure 7 The present specification shows a flowchart of a data query method according to an embodiment, which includes the following steps.

[0135] Step 702: Receive a table lookup request, wherein the table lookup request includes the product information identifier of the product information to be queried.

[0136] Step 704: Based on the product information identifier, find the first record cell containing the product information identifier in the secondary index. The secondary index includes a hidden column, which is used to store the record position of the primary key value of the stored product information in the clustered index.

[0137] Step 706: Determine the corresponding target record position based on the first record unit. The target record position includes the data page identifier and the data page offset.

[0138] Step 708: Determine the target data page in the clustered index based on the data page identifier, and determine the corresponding record unit in the target data page based on the data page offset.

[0139] Step 710: If the primary key value in the record unit corresponding to the data page offset matches the target primary key value, determine the record unit corresponding to the data page offset as the second record unit, wherein the target primary key value is the primary key value of the product information to be queried.

[0140] Step 712: If the primary key value in the record cell corresponding to the data page offset does not match the target primary key value, search within the target data page to determine the second record cell whose primary key value matches the target primary key value.

[0141] Step 714: If the primary key value of the record cell in the target data page does not match the target primary key value, scan the clustered index to determine the second record cell whose primary key value matches the target primary key value.

[0142] Step 716: Read the product information to be queried from the second record unit.

[0143] Step 718: Update the data page identifier and data page offset corresponding to the first record unit in the secondary index according to the data page identifier and data page offset corresponding to the second record unit.

[0144] In this embodiment, by receiving a table lookup request carrying a product information identifier of the product to be queried, determining the first record unit and the target record position corresponding to the first record unit in the secondary index based on the product information identifier, and querying the product information to be queried in the clustered index based on the target record position, the complete scan of the clustered index required during the table lookup process is reduced, thereby reducing the amount of scanned data, reducing the amount of disk read and write data, and thus shortening the data query latency, improving the data query efficiency, reducing resource consumption, and improving the overall system throughput.

[0145] Corresponding to the above method embodiments, this specification also provides embodiments of a data query device. Figure 8 A schematic diagram of a data query device according to one embodiment of this specification is shown. Figure 8 As shown, the device includes: The first receiving module 802 is configured to receive a table lookup request, wherein the table lookup request includes the data identifier of the data to be queried; The lookup module 804 is configured to search for the first record cell containing the data identifier in the secondary index based on the data identifier, wherein the secondary index includes a hidden column used to store the record position of the primary key value of the stored data in the clustered index; The query module 806 is configured to query the data to be queried in the clustered index based on the target record position corresponding to the first record unit.

[0146] Optionally, the data query device further includes an update module configured to: scan the clustered index to determine the record position of the target record cell storing the data to be queried in the clustered index when the data to be queried is not found according to the target record position; and update the target record position in the secondary index according to the record position of the target record cell.

[0147] Optionally, the target record location corresponding to the first record unit includes a data page identifier and a data page offset; correspondingly, the query module 806 is further configured to: determine the target data page in the clustered index according to the data page identifier; determine the second record unit in the target data page according to the data page offset; and read the data to be queried from the second record unit.

[0148] Optionally, the query module 806 is further configured to: determine the second record unit based on the matching result of the primary key value in the record unit corresponding to the data page offset and the target primary key value, wherein the target primary key value is the primary key value of the data to be queried.

[0149] Optionally, the query module 806 is further configured to: if the primary key value in the record unit corresponding to the data page offset matches the target primary key value, determine the record unit corresponding to the data page offset as the second record unit; if the primary key value in the record unit corresponding to the data page offset does not match the target primary key value, search within the target data page to determine the second record unit whose primary key value matches the target primary key value.

[0150] Optionally, the query module 806 is further configured to: scan the clustered index to determine a second record unit whose primary key value matches the target primary key value if the primary key value of the record unit in the target data page does not match the target primary key value.

[0151] Optionally, the query module 806 is further configured to update the data page identifier and data page offset corresponding to the first record unit in the secondary index based on the data page identifier and data page offset corresponding to the second record unit.

[0152] The above is an illustrative scheme of a data query device according to this embodiment. It should be noted that the technical solution of this data query device and the technical solution of the data query method described above belong to the same concept. For details not described in detail in the technical solution of the data query device, please refer to the description of the technical solution of the data query method described above.

[0153] Corresponding to the above method embodiments, this specification also provides data storage device embodiments. Figure 9 A schematic diagram of a data storage device according to one embodiment of this specification is shown. Figure 9 As shown, the device includes: The second receiving module 902 is configured to receive a data storage request, wherein the data storage request includes data to be stored; Storage module 904 is configured to store data to be stored in a clustered index; Write module 906 is configured to write the record position of the data to be stored in the clustered index to a hidden column of the secondary index.

[0154] Optionally, the record location includes a data page identifier and a data page offset; correspondingly, the storage module 904 is further configured to store the data to be stored in the record unit of the clustered index according to the order of the primary key values ​​of the data to be stored; the write module 906 is further configured to write the data page identifier and data page offset of the record unit storing the data to be stored into the hidden column of the secondary index.

[0155] Optionally, the write module 906 is further configured to: determine the data page to which the data to be stored is to be stored based on the order of the primary key values ​​of the data to be stored; if there is a free record unit in the data page to which the data to be stored is to be stored that has more free storage space than the storage space required by the data to be stored, store the data to be stored in the free record unit; if there is no free record unit in the data page to which the data to be stored is to be stored that has more free storage space than the storage space required by the data to be stored, determine a blank data page including the free record unit, add the blank data page to the clustered index, and store the data to be stored in the free record unit.

[0156] Optionally, the data storage device further includes a construction module configured to: construct a secondary index based on the primary key values ​​of each stored data in the clustered index; add a hidden column to the index memory object of the secondary index, wherein the index memory object includes metadata information of the secondary index, and the hidden column is used to store the record position of the primary key value of the stored data in the clustered index; and write the index memory object into a metadata data dictionary. The above is an illustrative scheme of a data storage device according to this embodiment. It should be noted that the technical solution of this data storage device and the technical solution of the data storage method described above belong to the same concept. For details not described in detail in the technical solution of the data storage device, please refer to the description of the technical solution of the data storage method described above.

[0157] Figure 10 A structural block diagram of a computing device 1000 according to one embodiment of this specification is shown. The components of the computing device 1000 include, but are not limited to, a memory 1010 and a processor 1020. The processor 1020 is connected to the memory 1010 via a bus 1030, and a database 1050 is used to store data.

[0158] The computing device 1000 also includes an access device 1040, which enables the computing device 1000 to communicate via one or more networks 1060. Examples of these networks include Public Switched Telephone Network (PSTN), Local Area Network (LAN), Wide Area Network (WAN), Personal Area Network (PAN), or combinations of communication networks such as the Internet. The access device 1040 may include one or more of any type of wired or wireless network interface (e.g., a network interface card (NIC)), such as an IEEE 802.11 Wireless Local Area Network (WLAN) wireless interface, a Wi-MAX (Worldwide Interoperability for Microwave Access) interface, an Ethernet interface, a Universal Serial Bus (USB) interface, a cellular network interface, a Bluetooth interface, or a Near Field Communication (NFC) interface.

[0159] In one embodiment of this specification, the above-described components of the computing device 1000 and Figure 10 Other components, not shown, can also be connected to each other, for example, via a bus. It should be understood that... Figure 10 The block diagram of the computing device shown is for illustrative purposes only and is not intended to limit the scope of this specification. Those skilled in the art can add or replace other components as needed.

[0160] The computing device 1000 can be any type of stationary or mobile computing device, including mobile computers or mobile computing devices (e.g., tablet computers, personal digital assistants, laptop computers, notebook computers, netbooks, etc.), mobile phones (e.g., smartphones), wearable computing devices (e.g., smartwatches, smart glasses, etc.) or other types of mobile devices, or stationary computing devices such as desktop computers or personal computers (PCs). The computing device 1000 can also be a mobile or stationary server.

[0161] The processor 1020 is used to execute the following computer program / instructions, which, when executed by the processor, implement the steps of the above method.

[0162] The above is an illustrative scheme of a computing device according to this embodiment. It should be noted that the technical solution of this computing device and the technical solution of the above method belong to the same concept, and all details not described in detail in the technical solution of the computing device can be referred to the description of the technical solution of the above method.

[0163] An embodiment of this specification also provides a computer-readable storage medium storing a computer program / instructions that, when executed by a processor, implement the steps of the above-described method.

[0164] The above is an illustrative scheme of a computer-readable storage medium according to this embodiment. It should be noted that the technical solution of this storage medium and the technical solution of the method described above belong to the same concept, and all details not described in detail in the technical solution of the storage medium can be referred to the description of the technical solution of the method described above.

[0165] An embodiment of this specification also provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of the above-described method.

[0166] The above is an illustrative example of a computer program according to this embodiment. It should be noted that the technical solution of this computer program and the technical solution of the method described above belong to the same concept. Details not described in detail in the technical solution of the computer program can be found in the description of the technical solution of the method described above.

[0167] 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 recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0168] The computer instructions include computer program code, which may be in the form of source code, object code, executable file, or certain intermediate forms. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording media, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium may be appropriately added or removed according to the requirements of patent practice. For example, in some regions, according to patent practice, computer-readable media may not include electrical carrier signals and telecommunication signals.

[0169] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that the embodiments in this specification are not limited to the described order of actions, because according to the embodiments in this specification, some steps can be performed in other orders or simultaneously. Furthermore, those skilled in the art should also understand that the embodiments described in this specification are all preferred embodiments, and the actions and modules involved are not necessarily essential to the embodiments in this specification.

[0170] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0171] The preferred embodiments disclosed above are merely illustrative of this specification. Optional embodiments do not exhaustively describe all details, nor do they limit the invention to the specific implementations described. Clearly, many modifications and variations can be made based on the embodiments described herein. These embodiments are selected and specifically described in this specification to better explain the principles and practical applications of the embodiments, thereby enabling those skilled in the art to better understand and utilize this specification. This specification is limited only by the claims and their full scope and equivalents.

Claims

1. A data query method, comprising: Receive a table lookup request, wherein the table lookup request includes the data identifier of the data to be queried; Based on the data identifier, the first record unit containing the data identifier is searched in the secondary index, wherein the secondary index includes a hidden column, which is used to store the record position of the primary key value of the stored data in the clustered index; Based on the target record position corresponding to the first record unit, the data to be queried is retrieved from the clustered index.

2. The method according to claim 1, after querying the data to be queried in the clustered index according to the target record position corresponding to the first record unit, further comprising: If the data to be queried is not found at the target record location, the clustered index is scanned to determine the record location of the target record unit storing the data to be queried in the clustered index. Update the target record position in the secondary index according to the record position of the target record unit.

3. The method according to claim 1, wherein the target record location corresponding to the first record unit includes a data page identifier and a data page offset; the step of querying the data to be queried in the clustered index according to the target record location corresponding to the first record unit includes: The target data page is determined in the clustered index based on the data page identifier; Based on the data page offset, determine the second record unit in the target data page; Read the data to be queried from the second recording unit.

4. The method according to claim 3, wherein determining the second record unit in the target data page based on the data page offset comprises: Based on the matching result between the primary key value in the record unit corresponding to the data page offset and the target primary key value, a second record unit is determined, wherein the target primary key value is the primary key value of the data to be queried.

5. The method according to claim 4, wherein determining the second record unit based on the matching result between the primary key value in the record unit corresponding to the data page offset and the target primary key value comprises: If the primary key value in the record unit corresponding to the data page offset matches the target primary key value, the record unit corresponding to the data page offset is determined to be the second record unit. If the primary key value in the record unit corresponding to the data page offset does not match the target primary key value, a search is performed within the target data page to determine a second record unit whose primary key value matches the target primary key value.

6. The method according to claim 5, after performing a search within the target data page to determine a second record unit whose primary key value matches the target primary key value, further comprising: If the primary key value of a record cell in the target data page does not match the target primary key value, the clustered index is scanned to determine a second record cell whose primary key value matches the target primary key value.

7. The method according to claim 6, further comprising, after determining the second record unit where the primary key value matches the target primary key value: Update the data page identifier and data page offset corresponding to the first record unit in the secondary index according to the data page identifier and data page offset corresponding to the second record unit.

8. A data storage method, comprising: Receive a data storage request, wherein the data storage request includes data to be stored; The data to be stored is stored in a clustered index; Write the record position of the data to be stored in the clustered index into the hidden column of the secondary index.

9. The method according to claim 8, wherein the recording location includes a data page identifier and a data page offset; The step of storing the data to be stored in the clustered index includes: Based on the order of the primary key values ​​of the data to be stored, the data to be stored is stored in the record unit of the clustered index; The step of writing the record position of the data to be stored in the clustered index into the hidden column of the secondary index includes: Write the data page identifier and data page offset of the record unit storing the data to be stored into the hidden column of the secondary index.

10. The method according to claim 9, wherein storing the data to be stored in the record unit of the clustered index according to the order relationship of the primary key values ​​of the data to be stored comprises: Based on the order of the primary key values ​​of the data to be stored, determine the data page to which the data to be stored should be stored; If there is a free record unit in the data page where the data to be stored is to be stored that has more free storage space than the storage space required by the data to be stored, the data to be stored is stored in the free record unit. If there is no free record unit in the data page where the data to be stored is to be stored that has more free storage space than the storage space required by the data to be stored, a blank data page including a free record unit is determined, the blank data page is added to the clustered index, and the data to be stored is stored in the free record unit.

11. The method of claim 8, further comprising, before writing the record position of the data to be stored in the clustered index into the hidden column of the secondary index: Based on the primary key values ​​of the data already stored in the clustered index, construct a secondary index; Add a hidden column to the index memory object of the secondary index, wherein the index memory object includes the metadata information of the secondary index, and the hidden column is used to store the record position of the primary key value of the stored data in the clustered index; Write the index memory object into the metadata data dictionary.

12. A computing device, comprising: Memory and processor; The memory is used to store computer programs / instructions, and the processor is used to execute the computer programs / instructions, which, when executed by the processor, implement the steps of the method according to any one of claims 1 to 11.

13. A computer-readable storage medium storing a computer program / instructions that, when executed by a processor, implement the steps of the method according to any one of claims 1 to 11.

14. A computer program product comprising a computer program / instructions that, when executed by a processor, implement the steps of the method according to any one of claims 1 to 11.

Images