Data query method and device, computer equipment and storage medium

Abstract

The invention discloses a data query method and device, computer equipment and a storage medium, which can be applied to the financial field and the medical health field, and the method comprises the following steps: determining columns participating in connection in a data table, and generating address columns according to addresses corresponding to the columns to construct a hash table; the drive table is divided into a plurality of intervals, and each thread processes one interval; and after the hit data is queried in the hash table, each thread reads the corresponding data by using the address column. By implementing the method provided by the invention, the memory occupation and the data reading time can be effectively reduced, the query performance of the database based on the LSM line storage during large table connection processing is remarkably improved, and meanwhile, the waste of system resources is reduced.

Description

Technical Field

[0001] The present invention relates to the technical fields of databases, finance, and medical and health technologies, and more specifically, to a data query method, device, computer device, and storage medium. Background Art

[0002] In database operations, hash join is a commonly used join operation method. Its basic principle is to load the data of the small table into memory, build a Hash table, and then continuously read the data of another table to query in the Hash table. If a hit occurs, the hit result is output. In the financial industry, the performance and efficiency of database operations are crucial for the real-time and accuracy of business. For example, in a stock trading system, it is necessary to quickly query and join a large number of transaction records and stock information; in a banking system, it is necessary to process a large amount of account information and transaction records to ensure the security of funds and the efficient operation of the business. However, when the data volume of both hash tables is extremely large, the following problems exist in the application of the prior art in the financial industry: a large amount of data needs to be loaded into memory, resulting in a long data reading time and a large memory consumption. In the financial industry, the data volume is usually very large. For example, stock trading records may contain millions or even tens of millions of records, and each record has multiple fields, such as trading time, trading price, trading quantity, etc. If all data is loaded into memory for hash join operations, it will occupy a large amount of memory resources and increase the burden on the system; based on the data storage characteristics of LSM (Log-Structured Merge Tree) row storage, a row of data in the table is stored in the same data block. When performing hashing, all data in the column will be read out, but in fact, only a few columns may be involved in the join, resulting in the reading and loading of a large amount of invalid data, causing waste of time and space. In the financial industry, the structure of database tables is often relatively complex, containing many fields, and in fact, when performing hash join operations, only a few key fields may be required, such as account number, transaction amount, etc. If all data is read, it will waste a large amount of disk I / O resources and memory space, affecting the performance and efficiency of the system.

[0003] Similarly, in the field of healthcare, similar challenges exist, and due to the diversity and complexity of data types, the problems may be more prominent. For example, in an electronic medical record system, the data of each patient not only includes basic personal information and medical records but may also include a large amount of medical images, laboratory test results, drug prescription information, etc. When performing cross-table queries to integrate a patient's complete medical history or analyze the prevalence trend of a specific disease, traditional hash join methods may cause a large amount of irrelevant data to be loaded into memory, increasing unnecessary resource consumption. Especially in large medical institutions or regional health information networks, the amount of data can reach millions or even hundreds of millions of records, and each record may involve dozens of fields. If the data columns participating in the join operation are not optimally selected, it will lead to serious performance bottlenecks, such as extended query response times and increased server loads.

[0004] Therefore, it is necessary to design a new method that effectively reduces memory occupancy and data reading time, significantly improves the query performance of databases based on LSM row storage when processing large table joins, and at the same time reduces waste of system resources. Summary of the Invention

[0005] The purpose of the present invention is to overcome the defects of the prior art and provide a data query method, device, computer device, and storage medium.

[0006] To achieve the above purpose, the present invention adopts the following technical solutions: A data query method, including:

[0007] Determine the columns participating in the join in the data table, generate an address column based on the addresses corresponding to the columns, and construct a hash table;

[0008] Divide the driving table into several intervals, where each thread processes one of the intervals;

[0009] When data is hit in the hash table, each thread reads the corresponding data using the address column.

[0010] Its further technical solution is: The determining the columns participating in the join in the data table, generating an address column based on the addresses corresponding to the columns, and constructing a hash table includes:

[0011] Determine the columns participating in the join from the data table structure that needs to be hash joined;

[0012] Combine the addresses corresponding to the columns with the offsets of the files corresponding to the columns to form an address column;

[0013] Generate a hash table based on the address column and the data corresponding to the columns.

[0014] A further technical solution thereof is: generating a hash table according to the address column and the data corresponding to the column, including:

[0015] Loading the data corresponding to the column and the address column into the memory, and generating a hash table according to the data corresponding to the column and the address column.

[0016] A further technical solution thereof is: dividing the driving table into several intervals, including:

[0017] Dividing the driving table into several intervals row by row.

[0018] A further technical solution thereof is: when data is hit in the hash table, each thread reads the corresponding data using the address column, including:

[0019] When data is hit in the hash table, each thread reads the corresponding file using the address column, and when the file exists, reads the corresponding data from the specified offset in the file.

[0020] A further technical solution thereof is: when data is hit in the hash table, each thread reads the corresponding data using the address column, and further including:

[0021] When data is hit in the hash table, each thread reads the corresponding file using the address column, and when the file does not exist, uses the keyword to query again and reads all columns of the row data.

[0022] A further technical solution thereof is: when data is hit in the hash table, multiple threads simultaneously process the data in the corresponding interval.

[0023] The present invention further provides a data query device, including:

[0024] A hash table construction unit, configured to determine the columns participating in the connection in the data table, generate an address column according to the addresses corresponding to the columns, and construct a hash table;

[0025] A division unit, configured to divide the driving table into several intervals, wherein each thread processes one of the intervals;

[0026] A reading unit, configured to when data is hit in the hash table, each thread reads the corresponding data using the address column.

[0027] The present invention further provides a computer device, the computer device includes a memory and a processor, a computer program is stored on the memory, and when the processor executes the computer program, the above method is implemented.

[0028] The present invention also provides a storage medium storing a computer program, which when executed by a processor implements the above method.

[0029] The beneficial effects of the present invention compared with the prior art are as follows: By generating an address column based on the columns participating in the join and constructing a hash table, and dividing the driving table into multiple intervals in combination with multi-threading, the present invention enables each thread to independently process different data intervals, thereby achieving parallel processing; when the data is hit in the hash table query, each thread directly reads the relevant data from the disk using the address column, avoiding repeated loading and unnecessary data storage, thus effectively reducing the memory occupancy and data reading time, significantly improving the query performance of the database based on LSM row storage when processing large table joins, and at the same time reducing the waste of system resources.

[0030] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0032] Figure 1 It is a schematic diagram of the application scenario of the data query method provided by the embodiment of the present invention;

[0033] Figure 2 It is a schematic flowchart of the data query method provided by the embodiment of the present invention;

[0034] Figure 3 It is a schematic sub-flowchart of the data query method provided by the embodiment of the present invention;

[0035] Figure 4 It is a schematic block diagram of the data query device provided by the embodiment of the present invention;

[0036] Figure 5 It is a schematic block diagram of the hash table construction unit of the data query device provided by the embodiment of the present invention;

[0037] Figure 6 It is a schematic block diagram of the computer device provided by the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0038] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0039] It should be understood that when used in this specification and the appended claims, the terms "comprising" and "including" indicate the presence of the described features, wholes, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components, and / or their combinations.

[0040] It should also be understood that the terms used in this specification of the present invention are merely for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in this specification of the present invention and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an", and "the" are intended to include the plural forms.

[0041] It should be further understood that the term "and / or" used in this specification of the present invention and the appended claims refers to any combination and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0042] Please refer to Figure 1 and Figure 2 , Figure 1 which is a schematic diagram of the application scenario of the data query method provided by the embodiment of the present invention. Figure 2 which is a schematic flowchart of the data query method provided by the embodiment of the present invention. This data query method is applied to a server that interacts with a terminal. Through reasonable memory management and data processing strategies, it significantly reduces memory occupancy and data reading time. In this method, first, data access is optimized by generating address columns and constructing a hash table to ensure that the data structure in memory is more compact and efficient. Second, by dividing the driving table into multiple intervals and having different threads process the data in their respective intervals in parallel, the multi-core processor is fully utilized to improve the query performance. After each thread queries and hits the data according to the hash table, it can accurately read the data in the relevant file, avoiding unnecessary data loading and repeated queries, thereby significantly improving the efficiency when joining large tables and effectively reducing the waste of system resources. Especially in the LSM row storage architecture, it provides efficient data processing capabilities.

[0043] Figure 2 is a flowchart of the data query method provided by the embodiment of the present invention. As Figure 2 shown, this method includes the following steps S110 to S130.

[0044] S110. Determine the columns involved in the join in the data table, and generate an address column based on the addresses corresponding to the columns to construct a hash table.

[0045] In this embodiment, in some database optimization operations, especially when performing a hash join, the address column (usually also referred to as a pointer column or address pointer) is used to store the location of the data in memory or on disk. These address columns store the specific location of each row of data, aiming to quickly locate the data during subsequent query processes without the need for repeated scans or calculations.

[0046] When performing a hash join, it is usually necessary to read data from one table (such as the driving table) and match it with the data in another table.

[0047] If there are address columns in memory, the system can use this address information to directly locate the disk storage location of the data without having to load all column data into memory.

[0048] This approach reduces unnecessary memory occupation and disk I / O operations, improving query efficiency, especially in scenarios with large amounts of data.

[0049] A hash table is a data storage structure that maps data to a location (bucket or slot) in the table through a hash function to achieve fast lookup. The main advantage of a hash table is that the average time complexity of query and insertion operations is O(1), which is very efficient.

[0050] Specifically, for a relatively small table, it is loaded into memory, and a hash table is constructed according to the join condition (such as a certain column). Here, the hash table distributes the data into different buckets.

[0051] In the hash table, not only the actual column data is stored, but also pointers or address columns pointing to the location of the data may be stored, which can help the system quickly locate the disk storage location of the data.

[0052] When looking up data in the second table (such as the joined table), the hash table and address columns can help the system efficiently perform matching, avoiding repeated lookups and memory operations.

[0053] In one embodiment, referring to Figure 3 , the above step S110 may include steps S111 to S113.

[0054] S111. Determine the columns involved in the join from the data table structure that needs to be hash joined.

[0055] In this embodiment, first, determine the columns that need to participate in the hash join according to requirements. For a stock trading system, perhaps only columns such as trading time and trading amount are needed; for a banking system, perhaps columns such as account number and trading amount are needed.

[0056] S112. Combine the address corresponding to the column and the offset of the file corresponding to the column to form an address column.

[0057] In this embodiment, construct a corresponding address column according to the selected columns. The address column contains the storage location and offset of each data row on the disk, so as to quickly locate the data. Such an address column (ColP) is crucial for optimizing the hash join operation. Especially when the data volume is very large, it can avoid multiple disk I / O operations.

[0058] S113. Generate a hash table according to the address column and the data corresponding to the column.

[0059] In this embodiment, load the data corresponding to the column and the address column into the memory, and generate a hash table based on the data corresponding to the column and the address column.

[0060] Load the data of the columns participating in the join and the corresponding address column into the memory, and construct a hash table using these data. The hash table will store the address column of each data row together with its corresponding data item, which is convenient for subsequent searching.

[0061] By optimizing the hash join operation, especially in data processing in the financial industry, the efficiency can be significantly improved, and the memory occupancy and the overhead of disk I / O operations can be reduced. By differentiating table data, only loading the columns related to the join, and combining multi-threaded parallel reading and address column (ColP) optimization, the system can maintain high efficiency when facing large-scale data, ensuring the real-time performance and accuracy of financial services. In high-frequency data processing scenarios such as stock trading and bank transaction records, this optimization technology will greatly improve the performance and response speed of the system.

[0062] Specifically, the data storage feature of LSM row storage is that one row of data in the table is stored in the same data block, as shown in Table 1.

[0063] Table 1. Data storage structure based on LSM row storage

[0064] Col1 Col2 Col3 Col...

[0065] There are Col1, Col2, Col3 and other columns in the table. When performing a hash join, all the data in the columns will be read out. When there are a very large number of columns, such as hundreds of columns, but only a few columns of data participate in the join, a large amount of invalid data will be read and loaded, resulting in waste of time and space.

[0066] Therefore, it can be considered to only load the columns involved in the join. Assuming that only Col1, Col2, and Col3 are involved in the Join, then only the data of Col1, Col2, and Col3 are read, and the address of Col1 is also used as the address column (ColP) and saved into the hash table together. This will greatly reduce the amount of data read and the data reading time.

[0067] For large-scale data tables in the financial industry, according to the actual query requirements, only the columns involved in the Join operation can be loaded. For example, when joining transaction data with customer data tables, if only columns such as transaction amount, transaction date, and customer ID are involved in the join operation, and other columns such as transaction status and customer address are not required, then only the data of these key columns need to be loaded. This can avoid loading a large amount of irrelevant column data into memory, thereby significantly reducing memory occupancy and I / O consumption.

[0068] A further optimized method is that in addition to loading the column data involved in the Join operation, an address column (ColP) can also be created for these columns. This address column not only stores the content of each row of data but also stores the physical address of this row of data in the storage system. In this way, during the Hash Join, the address column can help the system directly locate the position of the data row without having to rescan the entire table, thereby reducing the number of disk read operations and improving the query efficiency.

[0069] Suppose there are two tables in the financial industry: the Transaction table and the Customer table, which record the information of each transaction and the detailed information of customers respectively. It is necessary to join these two tables based on the customer ID to query all the transaction records of a certain customer.

[0070] The Transaction table may have the following columns: Transaction ID (TransactionID), Amount, Date, Customer ID (CustomerID), Status, etc.

[0071] The Customer table has the following columns: Customer ID (CustomerID), Name, Address, Balance, etc.

[0072] When performing the join operation, assume that only the transaction amount, transaction date, and customer ID are of concern, and columns such as transaction status and customer address are not required to be queried. If the traditional hash join method is used, the entire Transaction table and Customer table will be loaded into memory, which may lead to a large amount of unnecessary data reading.

[0073] When performing a hash join, only the data of the transaction amount, transaction date, and customer ID columns are read, without loading other columns (such as transaction status, customer address, etc.).

[0074] Create an address column (ColP) for the customer ID column, which stores the location of each row of data in the storage system. When performing a join operation, the storage location of the data row on the disk can be directly located through this address column, avoiding re-scanning the entire table and greatly improving the query efficiency.

[0075] For another example, in the field of healthcare, assume that an electronic medical record system is being developed, which aims to support a clinical decision support system to assist doctors in making better diagnosis and treatment decisions by analyzing information such as patients' diagnostic records, treatment plans, and laboratory test results. To achieve this goal, efficient data join operations need to be performed on multiple large database tables.

[0076] Specifically, in the data analysis of a diabetes management system, it involves:

[0077] Patient table: Contains patients' personal information, such as patient ID, name, gender, date of birth, etc.

[0078] Diagnosis table: Records the diagnostic information of each patient, including diagnosis ID, patient ID, diagnostic code (such as ICD-10 code), diagnosis date, etc.

[0079] Laboratory test table: Stores the laboratory test results of each patient, including test ID, patient ID, test item (such as blood glucose level), test value, test date, etc.

[0080] Treatment plan table: Records the treatment plans of each patient, including treatment plan ID, patient ID, drug name, dosage, start date, end date, etc.

[0081] To evaluate the treatment effect of a specific disease, such as diabetes, relevant information needs to be extracted from the above four tables, including patients' diagnostic records, medication status, and the latest blood glucose monitoring results.

[0082] First, determine which columns need to participate in the hash join operation according to the actual requirements. In this example, the following columns will be selected as the join basis:

[0083] "Patient ID" in the patient table.

[0084] "Patient ID" and "diagnostic code" in the diagnosis table (filter out diabetes-related diagnoses).

[0085] "Patient ID" and "test item" in the laboratory test table (limited to blood glucose level tests).

[0086] "Patient ID" in the treatment plan table.

[0087] Next, construct an address column based on the selected columns. The address column will contain the storage location and offset of each data row on disk to quickly locate the data. For example, in the diagnosis table, the address column corresponding to "Patient ID" can help us quickly find all the diagnosis records of that patient without scanning the entire table or loading irrelevant data.

[0088] Finally, load the data of the selected columns and their corresponding address columns into memory and generate a hash table based on this. The advantage of doing this is that when performing a hash join, the system can directly access the required data through the address column, thus avoiding duplicate data loading and disk I / O operations. For example, when looking up the latest blood glucose level of a certain diabetic patient, the system can directly use the address column in the hash table to jump to the location of the relevant record instead of rereading the entire laboratory test table.

[0089] S120. Divide the driving table into several intervals, where each thread processes one of the intervals.

[0090] In this embodiment, the driving table is divided into several intervals by rows.

[0091] When querying and hitting data in the hash table, multiple threads simultaneously process the data in the corresponding intervals.

[0092] The driving table is the table participating in the join, and its data volume is usually large. Especially in applications in the financial industry, it may involve tens of thousands of rows of records.

[0093] The purpose of dividing the driving table into several intervals by rows is to parallelize the operation, assign tasks to multiple threads for parallel processing, and thus accelerate the entire query process.

[0094] Specifically, the intervals can be divided based on a certain distribution rule of the data or according to a fixed number of rows. For example, the data in the driving table can be divided by a partitioning field (such as customer ID) or by row number. This can ensure that the data volume processed by each thread is roughly equal, avoiding some threads being overloaded while others are idle.

[0095] An interval refers to a subset of the data in the driving table, and each interval is a continuous part composed of several rows of data. For example, if the driving table has 10,000 rows of data and is divided into 10 intervals, each interval contains 1,000 rows of data. Each thread will process the data of one interval.

[0096] The division of the driving table should consider the distribution characteristics of the data to ensure that the interval data after division is evenly distributed, avoiding the data volume processed by a certain thread being too large or too small.

[0097] Once the driving table is divided into multiple intervals, multiple threads can work in parallel, with each thread processing the data in one interval.

[0098] When each thread processes the interval it is responsible for, it queries each row of data in the built hash table to check if there is a matching row. If a match is found, a join operation is performed and the result is saved.

[0099] Through this parallelization method, the execution speed of the hash join can be significantly improved. Especially when dealing with large-scale data sets, multiple threads can perform queries and matches simultaneously, reducing the overall query time.

[0100] S130. After querying and hitting data in the hash table, each thread reads the corresponding data using the address column.

[0101] In this embodiment, after querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file exists, it reads the corresponding data from the specified offset in the file.

[0102] After querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file does not exist, it uses the keyword to re-query and reads all columns of the row data.

[0103] Specifically, before step S130, a hash table has been built and a hash query has been executed. If the hash query hits data, then the next task is how to read the corresponding detailed data from the storage system.

[0104] A hash query hit means that during the query process of a certain thread, based on the data in the driving table (such as customer ID), a matching item can be found in the hash table. At this time, the query not only needs to confirm whether the matching data exists, but also needs to determine how to obtain the detailed content of the matching data.

[0105] Suppose each thread is processing a row of data in the driving table. For example, it queries whether a customer ID finds a matching customer information in the hash table. If the hash query hits, the hash table will provide an address column, and this address column points to the data location (i.e., offset) stored in the disk file.

[0106] If the file pointed to by the address column in the hash table exists, the thread will directly read the data according to the offset specified in the address column.

[0107] A "column of addresses" will be stored in the hash table, which records the location where the data is stored. This location is usually a file address that points to the specific file storing the data and contains the offset of the file (i.e., the specific location of the data in the file).

[0108] When a query hits, the column of addresses will point to an actual existing file. For example, assume that this file stores customer details (such as name, address, contact information, etc.).

[0109] Each thread can locate the data in the file through the offset provided in the column of addresses. This enables the system to directly jump to the specified location, thus effectively reading the data of that row.

[0110] Assume that the information stored in the hash table points to a customer information file, which may store a large amount of customer data.

[0111] The thread will quickly locate the correct position in the file according to the column of addresses and the offset provided by the hash table. In this way, the thread can directly read the relevant data, thus avoiding the process of full table scan and improving the query efficiency.

[0112] In some cases, the column of addresses in the hash table may point to a file that does not exist, or the file has been lost / inaccessible. At this time, the system needs to adopt another way to read the data.

[0113] The situation where the file does not exist may occur in the following cases:

[0114] The file is not stored in the expected storage system;

[0115] The storage system fails, resulting in the file being inaccessible;

[0116] The file has been deleted or lost.

[0117] For these situations, the thread will not be able to directly read the data from the file according to the column of addresses.

[0118] When the file does not exist, the thread will no longer rely on the offset in the column of addresses, but use keywords (such as customer ID, transaction ID, etc.) to re-query and read the required data.

[0119] When re-querying, the system will re-retrieve the data from the original data source or database according to the keywords in the hash table. This may include:

[0120] Re-finding all relevant data according to the query conditions (such as customer ID or transaction ID).

[0121] In the absence of the file offset, the system will fall back to the traditional query method and re-retrieve the data from the database or other data storage.

[0122] This approach ensures that even if the data file is unavailable, the system can still continue to process queries, guaranteeing the robustness and fault tolerance of the queries.

[0123] Due to the inability to access the file, the thread will reread all columns of the row data from the data source (such as a database, distributed storage, etc.). At this time, the system will find the complete row data based on the query keywords and extract the required information from it. For some business scenarios, this may mean rereading all customer-related field information, rather than just the keyword fields stored in the hash table.

[0124] Step S130 describes how to read the corresponding data according to the address column when the hash query hits, which is divided into two cases:

[0125] When the file exists: directly locate the file through the offset in the address column and efficiently read the corresponding data.

[0126] When the file does not exist: when the file cannot be directly read, the system will use the keywords to query the data again and read all columns of the row data according to the query results.

[0127] This process ensures data integrity and system efficiency by effectively managing file read operations (including using offsets and keyword queries) after the hash query hits. At the same time, it also provides a fault tolerance mechanism to handle the situation of missing or inaccessible files, guaranteeing the stability and high availability of the system in large-scale data processing.

[0128] In this embodiment, the data is stored in the LSM row storage format, and there are only two states for the file: existing or deleted. Therefore, when accessing data, in addition to ordinary row data, it also includes an "address column" (ColP). The address column stores information about the file path and file offset. When a certain row of data needs to be read, if the file exists, the data can be directly located through the path and offset in the address column, avoiding multiple disk reads. If the file does not exist, it is necessary to use the Key to search again and read the data of all relevant columns.

[0129] This approach improves the data read efficiency by reducing disk I / O operations, especially in scenarios where the hard disk reads slowly.

[0130] To further improve the query efficiency, multi-threaded processing is used for hash joins. When constructing the hash table in memory, the data of the driving table is divided into multiple intervals according to the number of threads, and each thread processes one data interval. The parallel processing of multiple threads significantly accelerates the data processing process.

[0131] When looking up a hash table in memory, if the matching data is found, it is a "hit". After each hit, the hash join process retrieves the address column (ColP) corresponding to the row data. Then, using the address column, it quickly locates and reads the relevant data from disk. In this way, in a multi-threaded framework, after each thread queries the data, it continues to use the address column (ColP) to obtain the complete row data from disk instead of reloading the entire table, thus reducing unnecessary memory occupancy and disk I / O operations.

[0132] In a hash join operation, first, the Build-side table data is used to construct a hash table, and the table structure includes the join column and the address column (ColP). It should be noted that in a hash join operation, usually, the smaller table is selected as the build table (Build-side table), and its data is loaded into memory to construct the Hash table, while the larger table is used as the Probe-side table, that is, the driving table, to find matching records in the constructed hash table.

[0133] The driving table data is divided according to the number of threads, and each thread processes a data range.

[0134] After each thread queries the hash table in memory and finds the matching row data, it obtains the address column (ColP) from the Hash table and uses it to read the corresponding row data from disk.

[0135] In this way, each thread can perform parallel reads independently without interfering with each other, and at the same time, it can avoid reading irrelevant data, optimizing the efficiency of memory usage and disk reads.

[0136] This optimization scheme stores the address column (ColP) in memory and combines multi-threaded parallel processing to reduce unnecessary data loading and disk reads, thus significantly improving the performance of large table queries and reducing the consumption of system resources. During the Hash Join process, it not only improves the data search efficiency but also realizes more efficient resource utilization by reducing the storage and use of invalid data.

[0137] For example, in the financial industry, consider a typical application scenario: a large-scale transaction data table and a customer information table need to be joined for query to achieve customer transaction behavior analysis and risk assessment. Suppose we need to query a customer's transaction records, analyze the customer's behavior pattern, and determine whether there are abnormal transactions or potential risks. This query requires joining a large number of customer information tables (including customer ID, name, account information, etc.) and transaction record tables (including transaction ID, transaction amount, time, transaction type, etc.).

[0138] The customer table contains a large amount of customer data, each customer has a unique ID, and information such as name, account balance, and account opening time is recorded.

[0139] The transaction table contains a large number of transaction records. Each transaction has a unique ID and records information such as the transaction amount, transaction time, transaction type, and customer ID.

[0140] When it is necessary to query all customers whose transaction amounts exceed a certain threshold within a certain period of time, analyze the transaction behaviors of these customers, and identify possible abnormal transaction behaviors. For example, query all customers whose transaction amounts exceed 100,000 yuan in the past month, as well as their basic information.

[0141] Connect the customer ID in the transaction table with the customer ID in the customer table, so that the customer information of each transaction can be obtained. The customer ID exists in both tables, so it is the key column for the connection.

[0142] First, generate a hash table in memory based on the customer ID column of the customer table. Each customer ID is mapped to the detailed information of the customer in the customer table (such as name, account balance, etc.). In addition, generate an address column (ColP) that stores the disk storage location of the customer information, so that when querying later, the disk can be directly accessed without repeatedly reading the entire customer table.

[0143] Divide the transaction table (driving table) into certain intervals and use multiple threads to process it in parallel. Each thread is responsible for processing a part of the data in the transaction table. For each transaction record, the thread first looks up the hash table through the customer ID to determine whether there is matching customer data.

[0144] When the hash table finds a matching customer ID, the thread will directly load the detailed information of the customer from the disk using the address column, avoiding repeatedly loading all the data of the entire customer table. Through parallel processing, multiple threads read data from different intervals simultaneously, thus greatly accelerating the query process.

[0145] Since each thread only loads the customer information in the current interval and directly reads data from the disk through the address column, it avoids loading the entire customer table into memory, thus greatly reducing the memory usage. Using the address column to directly locate the position of the customer information on the disk reduces the data scanning time. Especially when dealing with large-scale transaction data, it can significantly improve the query efficiency. Through the parallel processing of multiple threads, multiple query tasks can be carried out simultaneously, reducing the overall query time. Each thread independently processes its own data interval, avoiding competition and resource conflicts between threads. Through reasonable memory and disk I / O optimization, unnecessary memory occupation and disk reads are avoided, maximizing the utilization efficiency of system resources. Especially when querying large-scale financial data, it can reduce the hardware burden and improve the system response speed.

[0146] In this query scenario in the financial industry, combined with the optimization method of LSM row storage, by generating an address column, constructing a hash table, and using multi-threading for data processing, the memory occupancy and data reading time are effectively reduced, the performance of large table join queries is improved, and at the same time, the waste of system resources is reduced. This enables quick responses to complex query requirements when dealing with large-scale data in the financial industry, supporting real-time risk analysis and decision-making. By differentiating data and improving the data reading method, the performance of data reading is enhanced, and the waste of system resources caused by storing invalid data in memory is reduced, thereby reducing the use of system resources while improving the performance of large table queries.

[0147] Take another example. Suppose it is necessary to analyze the treatment effects of a specific disease such as diabetes, which involves extracting relevant information from multiple tables: patients' diagnostic records, medication histories, blood glucose level monitoring results, and possible complication records. To improve query efficiency, the above data query method can be adopted. Specifically:

[0148] First, according to the query requirements, determine which columns need to participate in the join operation. In this example, perhaps only keyword fields such as the patient's unique identifier (e.g., medical record number), diagnostic code (diabetes), medication status, and blood glucose monitoring results are needed.

[0149] Construct a hash table for the selected columns and create an address column (ColP) for each data item, which contains information pointing to the actual data location on the disk. The purpose of this is to be able to quickly locate and read the required data during subsequent queries without scanning the entire table or loading irrelevant data.

[0150] For large-scale patient datasets, multi-threading technology can be used to divide the driving table into several intervals, which are processed in parallel by different threads. Each thread is responsible for querying the data within its corresponding interval and directly locating the associated data rows through the address column in the hash table, thus avoiding duplicate data loading and disk I / O operations.

[0151] If some files are inaccessible due to reasons, the thread will use keywords to query again to ensure that the necessary data analysis tasks can be completed even in the case of partial data loss.

[0152] In this way, not only can the query efficiency be significantly improved while ensuring data accuracy and integrity, but also the occupation of system resources can be reduced, which is particularly important when dealing with massive medical data. This is of crucial significance for supporting real-time clinical decision support systems, personalized medical plan formulation, and public health event responses. In addition, this optimization strategy can also be applied to other scenarios involving large data volume processing, such as genomics research, drug development, etc., to contribute to the development of precision medicine.

[0153] The above data query method can generate address columns based on the columns participating in the join and construct a hash table, and divide the driving table into multiple intervals by combining multiple threads, enabling each thread to independently process different data intervals, thereby achieving parallel processing; when the data is hit in the hash table query, each thread directly reads the relevant data from the disk using the address column, avoiding repeated loading and unnecessary data storage, thus effectively reducing the memory occupancy and data reading time, significantly improving the query performance of the database based on LSM row storage when processing large table joins, and at the same time reducing the waste of system resources.

[0154] Figure 4 It is a schematic block diagram of a data query device 300 provided by an embodiment of the present invention. As Figure 4 shown, corresponding to the above data query method, the present invention also provides a data query device 300. The data query device 300 includes units for executing the above data query method, and the device can be configured in a server. Specifically, please refer to Figure 4 , the data query device 300 includes a hash table construction unit 301, a division unit 302, and a reading unit 303.

[0155] The hash table construction unit 301 is used to determine the columns participating in the join in the data table, generate address columns according to the addresses corresponding to the columns, and construct a hash table; the division unit 302 is used to divide the driving table into several intervals, where each thread processes one of the intervals; the reading unit 303 is used to, when the data is hit in the hash table query, each thread reads the corresponding data using the address column.

[0156] In one embodiment, as Figure 5 shown, the hash table construction unit 301 includes a determination subunit 3011, an address column formation subunit 3012, and a hash table generation subunit 3013.

[0157] The determination subunit 3011 is used to determine the columns participating in the join from the data table structure that needs to be hash-joined; the address column formation subunit 3012 is used to form address columns by combining the addresses corresponding to the columns with the offsets of the files corresponding to the columns; the hash table generation subunit 3013 is used to generate a hash table according to the address columns and the data corresponding to the columns.

[0158] In one embodiment, the division unit 302 is used to divide the driving table into several intervals by rows.

[0159] In one embodiment, the reading unit 303 is used to, when the data is hit in the hash table query, each thread reads the corresponding file using the address column, and when the file exists, reads the corresponding data from the specified offset in the file.

[0160] In one embodiment, the reading unit 303 is configured to, after querying and hitting data in the hash table, each thread reads the corresponding file using the address column, and when the file does not exist, re-query using keywords and read all columns of the row data.

[0161] It should be noted that those skilled in the art can clearly understand the specific implementation processes of the above data query device 300 and each unit, and can refer to the corresponding descriptions in the foregoing method embodiments. For the convenience and brevity of description, they will not be elaborated herein.

[0162] The above data query device 300 can be implemented in the form of a computer program, and this computer program can run on a computer device as shown in Figure 6 shown.

[0163] Please refer to Figure 6 , Figure 6 which is a schematic block diagram of a computer device provided by an embodiment of the present application. The computer device 500 may be a server. Among them, the server may be an independent server or a server cluster composed of multiple servers.

[0164] Referring to Figure 6 , the computer device 500 includes a processor 502, a memory, and a network interface 505 connected through a system bus 501. Among them, the memory may include a non-volatile storage medium 503 and an internal memory 504.

[0165] The non-volatile storage medium 503 can store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions, and when the program instructions are executed, the processor 502 can be made to execute a data query method.

[0166] The processor 502 is used to provide computing and control capabilities to support the operation of the entire computer device 500.

[0167] The internal memory 504 provides an environment for the operation of the computer program 5032 in the non-volatile storage medium 503. When the computer program 5032 is executed by the processor 502, the processor 502 can be made to execute a data query method.

[0168] The network interface 505 is used for network communication with other devices. Those skilled in the art can understand that Figure 6The structure shown is only a block diagram of some structures related to the solution of this application, and does not constitute a limitation on the computer device 500 to which the solution of this application is applied. Specifically, the computer device 500 may include more or fewer components than those shown in the figure, or combine some components, or have different component arrangements.

[0169] Among them, the processor 502 is used to run the computer program 5032 stored in the memory to implement the following steps:

[0170] Determine the columns participating in the join in the data table, generate an address column according to the addresses corresponding to the columns to construct a hash table; divide the driving table into several intervals, where each thread processes one of the intervals; when the data is hit in the hash table query, each thread uses the address column to read the corresponding data.

[0171] Among them, when the data is hit in the hash table query, multiple threads process the data in the corresponding intervals simultaneously.

[0172] In one embodiment, when the processor 502 implements the steps of determining the columns participating in the join in the data table, generating an address column according to the addresses corresponding to the columns to construct a hash table, the specific implementation steps are as follows:

[0173] Determine the columns participating in the join from the data table structure that needs to be hash-joined; combine the addresses corresponding to the columns with the offsets of the files corresponding to the columns to form an address column; generate a hash table according to the address column and the data corresponding to the columns.

[0174] In one embodiment, when the processor 502 implements the step of generating a hash table according to the address column and the data corresponding to the columns, the specific implementation steps are as follows:

[0175] Load the data corresponding to the columns and the address column into the memory, and generate a hash table according to the data corresponding to the columns and the address column.

[0176] In one embodiment, when the processor 502 implements the step of dividing the driving table into several intervals, the specific implementation steps are as follows:

[0177] Divide the driving table into several intervals row by row.

[0178] In one embodiment, when the processor 502 implements the step that when the data is hit in the hash table query, each thread uses the address column to read the corresponding data, the specific implementation steps are as follows:

[0179] After querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file exists, the corresponding data is read from the specified offset in the file.

[0180] In one embodiment, when the processor 502 implements the step of after querying and hitting data in the hash table, each thread reads the corresponding data using the address column, the specific implementation is as follows:

[0181] After querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file does not exist, all columns of the row data are re-queryed and read using keywords.

[0182] It should be understood that in the embodiments of the present application, the processor 502 may be a central processing unit (CPU), and the processor 502 may also be other general-purpose processors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0183] Those of ordinary skill in the art can understand that all or part of the processes in the methods of implementing the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program includes program instructions, and the computer program can be stored in a storage medium, and the storage medium is a computer-readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the process steps of the embodiments of the above methods.

[0184] Therefore, the present invention also provides a storage medium. The storage medium may be a computer-readable storage medium. The storage medium stores a computer program, and when the computer program is executed by a processor, the processor executes the following steps:

[0185] Determine the columns participating in the connection in the data table, generate an address column according to the addresses corresponding to the columns to construct a hash table; divide the driving table into several intervals, where each thread processes one of the intervals; after querying and hitting data in the hash table, each thread reads the corresponding data using the address column.

[0186] Among them, when querying and hitting data in the hash table, multiple threads simultaneously process data in corresponding intervals.

[0187] In one embodiment, when the processor executes the computer program to implement the step of determining columns participating in a join in the data table, generating an address column according to the addresses corresponding to the columns to construct a hash table, the specific implementation is as follows:

[0188] Determine columns participating in a join from the data table structure that needs hash joining; combine the addresses corresponding to the columns with the offsets of the files corresponding to the columns to form an address column; generate a hash table according to the address column and the data corresponding to the columns.

[0189] In one embodiment, when the processor executes the computer program to implement the step of generating a hash table according to the address column and the data corresponding to the columns, the specific implementation is as follows:

[0190] Load the data corresponding to the columns and the address column into memory, and generate a hash table based on the data corresponding to the columns and the address column.

[0191] In one embodiment, when the processor executes the computer program to implement the step of dividing the driving table into several intervals, the specific implementation is as follows:

[0192] Divide the driving table into several intervals by rows.

[0193] In one embodiment, when the processor executes the computer program to implement the step that after querying and hitting data in the hash table, each thread reads the corresponding data using the address column, the specific implementation is as follows:

[0194] After querying and hitting data in the hash table, each thread reads the corresponding file using the address column, and when the file exists, reads the corresponding data from the specified offset in the file.

[0195] In one embodiment, when the processor executes the computer program to implement the step that after querying and hitting data in the hash table, each thread reads the corresponding data using the address column, the specific implementation is as follows:

[0196] After querying and hitting data in the hash table, each thread reads the corresponding file using the address column, and when the file does not exist, uses keywords to re-query and read all columns of the row data.

[0197] The storage medium may be a U disk, a mobile hard disk, a read-only memory (ROM), a magnetic disk, an optical disk, or other computer-readable storage media that can store program codes.

[0198] Those of ordinary skill in the art can realize that the units and algorithm steps of the examples described in combination with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the composition and steps of the examples have been generally described according to functions in the above description. Whether these functions are executed in a hardware or software manner depends on the specific application and design constraints of the technical solution. Professional technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the present invention.

[0199] In several embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of each unit is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed.

[0200] The steps in the method embodiments of the present invention can be adjusted, combined, and deleted according to actual needs. The units in the device embodiments of the present invention can be combined, divided, and deleted according to actual needs. In addition, the functional units in each embodiment of the present invention can be integrated into one processing unit, or each unit can exist physically alone, or two or more units can be integrated into one unit.

[0201] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a terminal, or a network device, etc.) to execute all or part of the steps of the methods described in each embodiment of the present invention.

[0202] As described above, the above are only specific embodiments of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art within the technical scope disclosed by the present invention can easily think of various equivalent modifications or substitutions, and these modifications or substitutions should all be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention should be subject to the protection scope of the claims.

Claims

1. A data query method, characterized in that, including: Determine the columns participating in the join in the data table, generate an address column according to the addresses corresponding to the columns, and construct a hash table; Divide the driving table into several intervals, where each thread processes one of the intervals; After querying and hitting data in the hash table, each thread reads the corresponding data using the address column.

2. The data query method according to claim 1, wherein The determining the columns participating in the join in the data table, generating an address column according to the addresses corresponding to the columns, and constructing a hash table includes: Determine the columns participating in the join from the data table structure that needs hash join; Combine the addresses corresponding to the columns with the offsets of the files corresponding to the columns to form an address column; Generate a hash table according to the address column and the data corresponding to the columns.

3. The data query method according to claim 2, wherein The generating a hash table according to the address column and the data corresponding to the columns includes: Load the data corresponding to the columns and the address column into the memory, and generate a hash table based on the data corresponding to the columns and the address column.

4. The data query method according to claim 1, wherein The dividing the driving table into several intervals includes: Divide the driving table into several intervals by rows.

5. The data query method according to claim 1, characterized in that, The after querying and hitting data in the hash table, each thread reads the corresponding data using the address column includes: After querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file exists, read the corresponding data from the specified offset in the file.

6. The data query method according to claim 5, wherein The after querying and hitting data in the hash table, each thread reads the corresponding data using the address column further includes: After querying and hitting data in the hash table, each thread reads the corresponding file using the address column. When the file does not exist, use keywords to re-query and read all columns of the row data.

7. The data query method according to claim 1, wherein When querying and hitting data in the hash table, multiple threads process the data in the corresponding intervals simultaneously.

8. A data query device, characterized in that, including: A hash table construction unit, configured to determine the columns participating in the join in the data table, generate an address column according to the addresses corresponding to the columns, and construct a hash table; A dividing unit, configured to divide the driving table into several intervals, where each thread processes one of the intervals; A reading unit, configured to after querying and hitting data in the hash table, each thread reads the corresponding data using the address column.

9. A computer device, characterized in that, The computer device includes a memory and a processor, and a computer program is stored on the memory. When the processor executes the computer program, the method described in any one of claims 1 to 7 is implemented.

10. A storage medium, characterized in that, The storage medium stores a computer program, and when the computer program is executed by a processor, the method described in any one of claims 1 to 7 is implemented.

Images