Database query statement optimization method and device, computer equipment and storage medium
By creating a temporary hash table with the same data type as the target list in the database query and calculating the hash value of the source data table, the performance bottleneck of the IN expression in large-scale data scenarios is solved, efficient data search is achieved, and query efficiency and resource utilization efficiency are improved.
Patent Information
- Application Number
- CN202510822862.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-06-19
- Publication Date
- 2025-10-10
AI Technical Summary
The IN expression in existing database queries has obvious performance bottlenecks in large-scale data scenarios, especially in video cloud scenarios, where query performance drops significantly, resulting in low search efficiency.
By receiving SQL database query requests carrying IN expressions, the number of data elements to be queried in the target list is obtained. When the number reaches the preset threshold, a temporary hash table with the same data type as the target list is created, and the hash value of each row of data in the source data table is calculated. The query is performed from the temporary hash table to reduce the query time complexity.
When the target list contains a large number of elements, the query time complexity is reduced from O(n) to O(1), significantly improving query efficiency. This is especially true in large-scale data scenarios, where performance is significantly improved while also taking into account the rational use of resources and ensuring the accuracy of query results.
Smart Images
Figure CN120763191A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of database query, and in particular, to a database query statement optimization method and device, computer equipment, computer readable storage medium and computer program product. BACKGROUND
[0002] In database query, IN expression is a commonly used filter condition, which is widely used in various scenarios, such as the query of license plate number, mobile phone number, face feature value and other fields in the video cloud scenario. The basic form of IN expression is var IN (list), where list is a list containing multiple values, and the query will check whether the variable var exists in the list.
[0003] In the existing database implementation, IN expression is usually processed by storing the values in the list in an array and converting the expression into the form of var = ANY (array). In the traditional IN (list) query, the database needs to compare each row of data in the source data table one by one to check whether it exists in the list. The time complexity of this implementation is O (n), where n is the number of elements in the list.
[0004] When the number of elements in the list is small, this implementation can usually meet the performance requirements. However, in actual applications, the size of the list may become very large, especially in some data-intensive scenarios. For example, in the video cloud scenario, it may be necessary to query a list containing thousands or even tens of thousands of license plate numbers or mobile phone numbers. In this case, the computing performance of IN expression will decrease significantly, becoming a bottleneck of query performance.
[0005] Therefore, there is an urgent need for a database query statement optimization method, device, computer equipment, computer readable storage medium and computer program product, which can improve the data search efficiency of IN expression. SUMMARY
[0006] Therefore, there is an urgent need for a database query statement optimization method, device, computer equipment, computer readable storage medium and computer program product, which can improve the data search efficiency of IN expression.
[0007] In a first aspect, the present application provides a database query statement optimization method, comprising:
[0008] receiving a SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list;
[0009] According to the IN expression, the number of the data elements to be queried in the target list is obtained;
[0010] When the number of the data elements to be queried reaches a preset number threshold, creating a temporary hash table of the target list, wherein the temporary hash table has the same data element type as that of the target list;
[0011] Calculate the first hash value corresponding to each row of data in the source data table;
[0012] The first hash value is used as a query target, and a query is performed from the temporary hash table to obtain a query result.
[0013] In one embodiment, creating a temporary hash table of the target list includes:
[0014] Traversing each row of data in the target list, and calculating a second hash value for each row of data in the target list according to a preset connection condition;
[0015] A temporary hash table of the target list is created according to the second hash value and the corresponding row data.
[0016] In one embodiment, calculating the first hash value corresponding to each row of data in the source data table includes:
[0017] Traverse each row of data in the source data table, and calculate the first hash value of each row of data in the source data table according to the preset connection condition, where the preset connection condition is an equal value association condition between the temporary hash table and the target list.
[0018] In one embodiment, the step of using the first hash value as a query target and performing a query from the temporary hash table to obtain a query result includes:
[0019] Using the first hash value of each row of data in the source data table as a query target, querying from the temporary hash table;
[0020] When the first hash value is equal to the second hash value in the temporary hash table, row data corresponding to the second hash value is used as hit target data.
[0021] In one embodiment, after creating the temporary hash table of the target list, the method further includes:
[0022] The query condition of the IN expression is deleted from the WHERE clause of the query statement, and a query condition of a hash connection between the temporary hash table and the target list is established.
[0023] In one of the embodiments, after the number of the data elements to be queried in the target list is obtained, the method further comprises:
[0024] In a case where the number of the data elements to be queried does not reach the preset number threshold, each row of data in the target list is iteratively queried based on each row of data in the source data table by using the IN expression, and a query result is obtained.
[0025] In a second aspect, the present application further provides a database query statement optimization device, comprising:
[0026] a request receiving module configured to receive a SQL database query request, the SQL database query request carrying an IN expression used for querying data elements from a target list;
[0027] a searching module configured to obtain a number of data elements to be queried in the target list according to the IN expression;
[0028] a hash table establishing module configured to create a temporary hash table of the target list in a case where the number of the data elements to be queried reaches a preset number threshold, the temporary hash table being of the same type of data elements as the target list;
[0029] a hash value calculating module configured to calculate a first hash value corresponding to each row of data in a source data table;
[0030] a querying module configured to query from the temporary hash table by taking the first hash value as a query target, and obtain a query result.
[0031] In a third aspect, the present application further provides a computer device, comprising a memory and a processor, the memory storing a computer program, and the processor realizing the following steps when executing the computer program:
[0032] receiving a SQL database query request, the SQL database query request carrying an IN expression used for querying data elements from a target list;
[0033] obtaining a number of data elements to be queried in the target list according to the IN expression;
[0034] creating a temporary hash table of the target list in a case where the number of the data elements to be queried reaches a preset number threshold, the temporary hash table being of the same type of data elements as the target list;
[0035] calculating a first hash value corresponding to each row of data in a source data table;
[0036] querying from the temporary hash table by taking the first hash value as a query target, and obtaining a query result.
[0037] In a fourth aspect, the present application provides a computer readable storage medium, having stored thereon a computer program, which when executed by a processor implements the following steps:
[0038] receiving a SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list;
[0039] obtaining a number of data elements to be queried in the target list according to the IN expression;
[0040] creating a temporary hash table of the target list in a case where the number of data elements to be queried reaches a preset number threshold, the temporary hash table being of a same type as data elements in the target list;
[0041] calculating a first hash value corresponding to each row of data in a source data table;
[0042] querying the temporary hash table with the first hash value as a query target to obtain a query result.
[0043] In a fifth aspect, the present application provides a computer program product, comprising a computer program, which when executed by a processor implements the following steps:
[0044] receiving a SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list;
[0045] obtaining a number of data elements to be queried in the target list according to the IN expression;
[0046] creating a temporary hash table of the target list in a case where the number of data elements to be queried reaches a preset number threshold, the temporary hash table being of a same type as data elements in the target list;
[0047] calculating a first hash value corresponding to each row of data in a source data table;
[0048] querying the temporary hash table with the first hash value as a query target to obtain a query result.
[0049] The database query statement optimization method, device, computer equipment, computer readable storage medium and computer program product can receive a SQL database query request carrying an IN expression, obtain the number of data elements to be queried in a target list, and create a temporary hash table of the same data type as the target list when the number reaches a preset threshold. Then, the first hash value of each row of data in the source data table is calculated and used as a query target to quickly query the temporary hash table to obtain the result. This method reduces the query time complexity from the traditional O(n) to O(1) when the number of elements in the target list is large, significantly improving the query efficiency, especially in large-scale data scenarios. At the same time, by flexibly setting the threshold, unnecessary complexity and resource consumption are avoided in small-scale data, and the reasonable use of resources is considered. In addition, the efficiency of hash connection also ensures the accuracy of the query result, thereby optimizing the performance and resource utilization efficiency of the database query as a whole. BRIEF DESCRIPTION OF DRAWINGS
[0050] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the related art, the drawings needed to be used in the description of the embodiments of the present application or the related art will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other related drawings can also be obtained without creative labor.
[0051] Figure 1 An application environment diagram of the database query statement optimization method in an embodiment;
[0052] Figure 2 A flowchart of the database query statement optimization method in an embodiment;
[0053] Figure 3 A flowchart of the database query statement optimization method in another embodiment;
[0054] Figure 4 A structural block diagram of the database query statement optimization device in an embodiment;
[0055] Figure 5 An internal structure diagram of the computer equipment in an embodiment. DETAILED DESCRIPTION
[0056] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application will be further described in detail below in combination with the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and not to limit the present application.
[0057] The database query statement optimization method provided by the embodiments of the present application can be applied to, for example,Figure 1 In the application environment shown, server 102 communicates with server 104 via a network. The data storage system can store data that server 104 needs to process. The data storage system can be integrated on server 104 or placed on the cloud or other network servers.
[0058] Server 104 receives an SQL database query request through server 102, where the SQL database query request carries an IN expression for querying data elements from a target list; server 104 obtains the number of data elements to be queried in the target list based on the IN expression; when the number of data elements to be queried reaches a preset threshold, a temporary hash table of the target list is created, where the temporary hash table has the same data element type as that in the target list; a first hash value corresponding to each row of data in the source data table is calculated; the first hash value is used as a query target, and a query is performed from the temporary hash table to obtain a query result.
[0059] Among them, server 102 and server 104 can be independent physical servers, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud computing services.
[0060] In an exemplary embodiment, Figure 2 As shown, a database query statement optimization method is provided, which is applied to Figure 1 The server 104 in the example is used as an example to illustrate the process, including the following steps S202 to S210.
[0061] Step S202: Receive an SQL database query request, where the SQL database query request carries an IN expression for querying data elements from a target list.
[0062] Specifically, SQL (Structured Query Language) is a declarative programming language used to manage relational database systems. A database query request is a query operation initiated by a user to a database system using SQL statements, with the goal of retrieving data from the database that meets specific criteria.
[0063] First, the SQL database query request is lexically and syntactically analyzed. During the semantic analysis phase, the expression in the where clause is checked. If the where clause does not contain "var in (list)", that is, does not contain an IN expression, no special processing is performed and the original process is executed.
[0064] The IN expression is a common SQL construct used to specify a condition in the WHERE clause to filter out data that matches a specific set of values. Its basic syntax is: column_name IN(value1,value2,...,valueN); where column_name is a column in a database table, and value1,value2,...,valueN is a list of values (the target list) representing multiple possible values to match during the query.
[0065] The target list is the set of values specified in the IN expression. These values are the candidate values that the user hopes to match in a column of a database table. For example, in the following SQL statement: SELECT * FROM table_name WHERE column_name IN('value1','value2','value3'); the target list is ('value1','value2','value3'), which means that the query will find records in the table_name table where the value of the column_name column is 'value1', 'value2', or 'value3'.
[0066] In this context, querying data elements refers to selecting records (or rows) from a database table that meet the IN expression criteria. The database system checks the value of the column_name column in each row of the table. If the value is in the target list, the row is considered to meet the query criteria and is returned as the query result.
[0067] Step S204: Obtain the number of data elements to be queried in the target list according to the IN expression.
[0068] Specifically, in SQL database query requests, the IN expression is often used to specify a set of values for filtering data. When processing this query, it's necessary to parse the target list in the IN expression and count the number of elements in the list. For example, if the target list contains a small number of elements, a simple array comparison can be used; if the number of elements is large, a more efficient hash join can be used.
[0069] Suppose there is a SQL database query request as follows:
[0070] SELECT * FROM employees WHERE department IN ('HR','Marketing','Engineering'); In this example, the IN expression is department IN ('HR','Marketing','Engineering'). The target list is ('HR','Marketing','Engineering'). Get the number of elements in the target list, that is, count the number of elements in ('HR','Marketing','Engineering'). In this example, the target list has three elements.
[0071] Step S206 : When the number of data elements to be queried reaches a preset number threshold, a temporary hash table of the target list is created. The temporary hash table has the same data element type as that in the target list.
[0072] Specifically, when processing an IN expression, the database system first counts the number of elements in the target list. If this number exceeds a pre-set threshold (for example, 100), a specific optimization strategy is triggered. This threshold can be adjusted based on the actual application scenario to balance query performance and resource consumption.
[0073] When the number of elements in the target list reaches a threshold (for example, 100), the database system creates a temporary hash table. A hash table is a data structure that uses a hash function to map data to a smaller range, enabling fast lookup operations. The search time complexity of a hash table is typically O(1), much higher than the traditional linear search (O(n)).
[0074] The data structure of the temporary hash table should be consistent with the data element type in the target list. This means that if the elements in the target list are strings, the temporary hash table should also store string data; if the elements in the target list are integers, the temporary hash table should also store integer data. This ensures data consistency and query accuracy.
[0075] For example, because the target list has 150 elements, which exceeds the preset threshold of 100, the database system will create a temporary hash table. The structure of the temporary hash table is as follows:
[0076] Table name: temp_hash_table; column name: department; data type: consistent with the data element type in the target list (assuming it is VARCHAR type); store data elements in the target list: insert all department names in the target list into the temporary hash table temp_hash_table.
[0077] Step S208, calculate the first hash value corresponding to each row of data in the source data table.
[0078] Specifically, the source data table is a table in a database that stores raw data and is the object of a query operation. For example, in a SQL query, the table_name in SELECT * FROM table_name is the source data table. The data in the source data table is organized in the form of records, and each record contains the values of multiple columns. For example, a row in an employee table may contain employee ID, name, department, and other information.
[0079] The hash value is a numerical value obtained by calculating the data through a hash function, which is usually used for fast lookup in hash tables. In this embodiment, the first hash value refers to the value obtained by hashing a certain column (usually the column specified in the IN expression) of each row of data in the source data table.
[0080] For example, the source data table is employees, and the values of the department column ('HR', 'Marketing', 'Engineering') of the source data table need to be hashed to obtain the first hash value corresponding to each row of data. Assuming that a simple hash function hash() is used, the calculation process is as follows:
[0081] For the first row (employee_id = 1): the value of the department column is 'HR'. Calculate the hash value: hash('HR').
[0082] For the second row (employee_id = 2): the value of the department column is 'Engineering'. Calculate the hash value: hash('Engineering').
[0083] For the third row (employee_id = 3): the value of the department column is 'Marketing'. Calculate the hash value: hash('Marketing').
[0084] Step S210, query the temporary hash table with the first hash value as the query target to obtain the query result.
[0085] Specifically, the query target refers to the hash values (i.e. the first hash values) of the specified columns of each row of data in the source data table. These hash values will be used to search in the temporary hash table.
[0086] The temporary hash table has pre-stored hash values of all elements in the target list. By comparing the first hash value with the hash values in the temporary hash table, it can be quickly determined whether the data in the source data table meets the conditions of the IN expression. If the first hash value finds a matching hash value in the temporary hash table, it means that the data in this row of the source data table meets the conditions of the IN expression, and it is returned as part of the query result.
[0087] In the above database query statement optimization method, by receiving a SQL database query request carrying an IN expression, the number of data elements to be queried in the target list is obtained, and when the number reaches a preset threshold, a temporary hash table of the same data type as the target list is created. Then calculate the first hash value of each row of data in the source data table, and use it as a query target to quickly query from the temporary hash table to obtain the result. This method reduces the query time complexity from the traditional O(n) to O(1) when the number of elements in the target list is large, significantly improving the query efficiency, especially in large-scale data scenarios. At the same time, by flexibly setting the threshold, unnecessary complexity and resource consumption are avoided in small-scale data, and the reasonable use of resources is taken into account. In addition, the efficiency of hash connection also ensures the accuracy of the query result, thereby optimizing the performance and resource utilization efficiency of the database query as a whole.
[0088] In an exemplary embodiment, as shown in Figure 3 the temporary hash table of the target list is created, including:
[0089] Step S302, traverse each row of data in the target list, and calculate the second hash value of each row of data in the target list according to the preset connection condition;
[0090] Step S304, according to the second hash value and the corresponding row of data, create a temporary hash table of the target list.
[0091] Specifically, the preset connection condition refers to the matching condition between the values in the target list and the columns in the source data table in the IN expression. For example, in the IN expression, the values in the target list need to be matched with a column (such as department) in the source data table. This condition determines how to compare the values in the target list with the columns in the source data table.
[0092] For each row of data (each value) in the target list, a hash function is used to calculate its hash value. This hash value is called "second hash value". For example, assuming that the values in the target list are of string type, a hash function (such as hash()) can be used to calculate the hash value of each value. First, traverse each value in the target list to calculate its second hash value, and then insert each value and its corresponding second hash value into the temporary hash table.
[0093] The temporary hash table is a data structure that stores the values in the target list and their corresponding hash values. This table typically contains two columns: one column stores the original values in the target list, and the other column stores the hash values of these values. For example, one column is department ('HR', 'Marketing', 'Engineering'), and the other column is hash_value ('hash('HR')', 'hash('Marketing')', 'hash('Engineering')').
[0094] In this embodiment, by creating a temporary hash table, the values in the target list and their hash values can be pre-stored, so that in the subsequent query process, the hash values can be quickly searched, avoiding one-by-one comparison of the target list. This method is particularly suitable for large target lists, which can significantly improve query performance.
[0095] In an exemplary embodiment, the first hash value corresponding to each row of data in the source data table is calculated, including:
[0096] Each row of data in the source data table is traversed, and the first hash value of each row of data in the source data table is calculated according to the preset join condition, which is an equal value association condition in the temporary hash table and the target list.
[0097] Specifically, the source data table is a table in a database that stores original data, and it is the object of the query operation. For example, in a SQL query, table_name in SELECT * FROM table_name is the source data table.
[0098] For example, the preset join condition refers to the matching condition between the values in the target list and the columns in the source data table in the IN expression. For example, in the IN expression, the values in the target list need to be matched with a column (such as department) in the source data table. The equal value association condition refers to the equal value matching condition between the values in the target list and the columns in the source data table in the IN expression. For example, in the IN expression, the values in the target list need to be matched with a column (such as department) in the source data table.
[0099] It should be noted that the preset join condition is not limited to the equal value association condition, and the preset join condition can be selected in different forms according to specific query requirements, including range, non-equal value, fuzzy matching, multi-column matching, function processing, subquery, logical combination, and regular expression.
[0100] Calculate a hash value for the specified column (usually the column involved in the IN expression) in each row of data in the source data table. This hash value is called the "first hash value." For example, if the source data table has a column called "department," you can use a hash function (such as hash()) to calculate the hash value of the department column for each row of data.
[0101] In this embodiment, by calculating the first hash value corresponding to each row of data in the source data table, these hash values can be quickly compared with the hash values in the temporary hash table to find matching records. This hash-based search method is more efficient than traditional linear search and can significantly improve query performance, especially when processing large amounts of data.
[0102] In an exemplary embodiment, the first hash value is used as a query target, and a query is performed from the temporary hash table to obtain a query result, including:
[0103] Use the first hash value of each row of data in the source data table as the query target and query from the temporary hash table;
[0104] When the first hash value is equal to the second hash value in the temporary hash table, the row data corresponding to the second hash value is used as the hit target data.
[0105] Specifically, the first hash value is used as the query target, and a search is performed in the temporary hash table to determine whether there is an equivalent second hash value. If a matching hash value is found, this row of data in the source data table meets the query criteria. The row of data corresponding to the matching second hash value in the temporary hash table is marked as the hit target data, and this hit target data is returned as part of the final query results.
[0106] Assume that there are the following source data table employees and target table; where:
[0107] Source data table employees:
[0108]
[0109] Target list: IN('HR','Marketing').
[0110] Temporary hash table temp_hash_table:
[0111]
[0112] Calculate the first hash value:
[0113] Calculate the hash value for the department column of each row in the source data table employees:
[0114] First row (department='HR'): hash('HR');
[0115] Second row (department='Engineering'): hash('Engineering');
[0116] Third row (department='Marketing'): hash('Marketing');
[0117] Fourth row (department='HR'): hash('HR');
[0118] For the first hash value of each row of data, use the first hash value as a query target to find whether there is an equal second hash value in the temporary hash table:
[0119] First row: hash('HR') matches hash('HR') in temp_hash_table.
[0120] Second row: hash('Engineering') does not match hash values in temp_hash_table.
[0121] Third row: hash('Marketing') matches hash('Marketing') in temp_hash_table.
[0122] Fourth row: hash('HR') matches hash('HR') in temp_hash_table.
[0123] Mark the matching row of data as a hit target data:
[0124] First row (employee_id=1);
[0125] Third row (employee_id=3);
[0126] Fourth row (employee_id=4);
[0127] Final query result:
[0128]
[0129] In this embodiment, by leveraging the efficient search feature of hash tables (with a time complexity of O(1)), query performance can be significantly improved, especially when processing large amounts of data. Traditional linear searches (with a time complexity of O(n)) become very inefficient when the target list is large, while hash table searches can be completed in constant time, greatly improving query efficiency.
[0130] In an exemplary embodiment, after creating a temporary hash table of the target list, the method further includes:
[0131] Delete the query condition of the IN expression from the WHERE clause of the query statement and establish the query condition of the hash join between the temporary hash table and the target list.
[0132] Specifically, during the optimization process, to utilize the temporary hash table for efficient hash joins, the IN expression query condition must be removed from the WHERE clause. This removes the IN expression from the original WHERE clause and no longer directly filters data. After removing the IN expression query condition, a new query condition must be established to perform a hash join between the source data table and the temporary hash table. Specifically, this new query condition is typically based on an equality match condition based on hash values.
[0133] In this embodiment, this method can convert IN expression query conditions that originally required one-by-one comparison in the WHERE clause into efficient hash join queries. The hash join has a time complexity of O(1), which can significantly improve query performance compared to traditional linear search (time complexity of O(n)), especially when processing large-scale data.
[0134] In an exemplary embodiment, after obtaining the number of data elements to be queried in the target list, the method further includes:
[0135] When the number of data elements to be queried does not reach the preset threshold, the IN expression is used to traverse each row of data in the query target list based on each row of data in the source data table to obtain the query result.
[0136] Specifically, the preset quantity threshold is a predefined value used to determine when to use the hash table to optimize the query and when to use the traditional IN expression query. For example, the threshold can be set to 100.
[0137] If the number of elements in the target list is less than the preset number threshold, it indicates that the target list is small, and in this case, using the traditional IN expression query can be more efficient because the overhead of creating and maintaining a hash table can be greater than the overhead of direct query. With the IN expression, based on each row of data in the source data table, each row of data in the query target list is traversed to obtain the query result. In this case, the IN expression is used directly for query. For each row of data in the source data table, the value of the specified column is checked one by one to see if it is in the target list. For each row of data in the source data table, each value in the target list is checked one by one to see if it matches. If it matches, the row of data meets the query condition.
[0138] In this embodiment, because the target list is small, direct use of the IN expression for query can be more efficient because the overhead of creating and maintaining a hash table can be greater than the overhead of direct query. In this way, unnecessary complexity and resource consumption can be avoided when the target list is small, while maintaining the flexibility and efficiency of the query.
[0139] It should be understood that although each step in the flowchart involved in each of the above-described embodiments is shown in sequence according to the direction of the arrow, these steps are not necessarily executed in sequence according to the direction of the arrow. Unless otherwise specified herein, there is no strict order limitation on the execution of these steps, and these steps can be executed in other orders. Moreover, at least part of the steps in the flowchart involved in each of the above-described embodiments can include multiple steps or stages, which are not necessarily executed at the same time but can be executed at different times, and the execution order of these steps or stages is not necessarily sequential but can be alternately or alternately executed with at least part of other steps or steps or stages in other steps.
[0140] Based on the same inventive concept, the embodiments of the present application also provide a database query statement optimization device for implementing the above-mentioned database query statement optimization method. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme described in the above method, so the specific limitations in one or more database query statement optimization device embodiments provided below can refer to the limitations of the database query statement optimization method described above, which will not be repeated here.
[0141] In one exemplary embodiment, as shown in Figure 4 a database query statement optimization device is provided, comprising:
[0142] The request receiving module 402 is configured to receive a SQL database query request, and the SQL database query request carries an IN expression for querying data elements from a target list;
[0143] Search module 404, used to obtain the number of data elements to be queried in the target list according to the IN expression;
[0144] A hash table creation module 406 is configured to create a temporary hash table of the target list when the number of data elements to be queried reaches a preset number threshold, where the data elements in the temporary hash table are of the same type as those in the target list;
[0145] A hash value calculation module 408 is used to calculate a first hash value corresponding to each row of data in the source data table;
[0146] The query module 410 is configured to use the first hash value as a query target, perform a query in the temporary hash table, and obtain a query result.
[0147] In an exemplary embodiment, the hash value calculation module 408 is further used to traverse each row of data in the target list and calculate the second hash value of each row of data in the target list according to a preset connection condition; the hash table establishment module 406 is specifically used to create a temporary hash table of the target list based on the second hash value and the corresponding row data.
[0148] In an exemplary embodiment, the hash value calculation module 408 is specifically used to traverse each row of data in the source data table and calculate the first hash value of each row of data in the source data table according to a preset connection condition. The preset connection condition is an equal value association condition between the temporary hash table and the target list.
[0149] In an exemplary embodiment, the query module 410 is specifically used to use the first hash value of each row of data in the source data table as the query target and query from the temporary hash table; when the first hash value is equal to the second hash value in the temporary hash table, the row data corresponding to the second hash value is used as the hit target data.
[0150] In an exemplary embodiment, the deletion module is configured to delete the query condition of the IN expression from the WHERE clause of the query statement and establish the query condition of the hash connection between the temporary hash table and the target list.
[0151] In an exemplary embodiment, the query module 410 is also used to use the IN expression to traverse each row of data in the query target list based on each row of data in the source data table to obtain the query result when the number of data elements to be queried does not reach a preset number threshold.
[0152] Each module in the database query statement optimization apparatus can be realized by software, hardware, or a combination thereof, in whole or in part. Each module can be embedded in or independent of a processor in a computer device in hardware form, or can be stored in a memory in the computer device in software form, so as to be called and executed by the processor to perform operations corresponding to each module.
[0153] In an exemplary embodiment, a computer device, which can be a server, is provided, and an internal structure diagram of the computer device can be as shown in Figure 5 The computer device includes a processor, a memory, an input / output interface (I / O), and a communication interface. The processor, the memory, and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for running the operating system and the computer program in the non-volatile storage medium. The database of the computer device is configured to store a target list and source data table data. The input / output interface of the computer device is configured to exchange information between the processor and external devices. The communication interface of the computer device is configured to communicate with external terminals through a network connection. The computer program is executed by the processor to implement a database query statement optimization method.
[0154] Those skilled in the art can understand that Figure 5 The structure shown in the above description is only a block diagram of part of the structure related to the scheme of the present application, and does not constitute a limitation on the computer device to which the scheme of the present application is applied. Specifically, the computer device can include more or fewer components than those shown in the diagram, or combine certain components, or have a different arrangement of components.
[0155] In an exemplary embodiment, a computer device is provided, which includes a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the following steps:
[0156] receiving an SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list;
[0157] According to the IN expression, the number of data elements to be queried in the target list is obtained;
[0158] In a case where the number of data elements to be queried reaches a preset number threshold, a temporary hash table of the target list is created, and the temporary hash table is of the same type as the data elements in the target list;
[0159] calculating a first hash value corresponding to each row of data in the source data table;
[0160] querying the temporary hash table with the first hash value as a query target to obtain a query result.
[0161] In one embodiment, the processor, when executing the computer program, further implements the following steps:
[0162] traversing each row of data in the target list, and calculating a second hash value of each row of data in the target list according to a preset connection condition;
[0163] creating a temporary hash table of the target list according to the second hash value and the corresponding row of data.
[0164] In one embodiment, the processor, when executing the computer program, further implements the following steps:
[0165] traversing each row of data in the source data table, and calculating a first hash value of each row of data in the source data table according to a preset connection condition, the preset connection condition being an equivalent association condition in the temporary hash table and the target list.
[0166] In one embodiment, the processor, when executing the computer program, further implements the following steps:
[0167] querying the temporary hash table with the first hash value of each row of data in the source data table as a query target;
[0168] in a case where the first hash value is equal to the second hash value in the temporary hash table, taking the row of data corresponding to the second hash value as hit target data.
[0169] In one embodiment, the processor, when executing the computer program, further implements the following steps:
[0170] deleting a query condition of an IN expression from a WHERE clause of the query statement to establish a query condition of hash connection between the temporary hash table and the target list.
[0171] In one embodiment, the processor, when executing the computer program, further implements the following steps:
[0172] in a case where the number of to-be-queried data elements does not reach a preset number threshold, traversing each row of data in the query target list based on each row of data in the source data table by using the IN expression to obtain a query result.
[0173] In one embodiment, a computer readable storage medium is provided, and the computer readable storage medium stores a computer program, and the computer program, when executed by a processor, implements the following steps:
[0174] receiving an SQL database query request, the SQL database query request carrying an IN expression used to query data elements from a target list;
[0175] According to the IN expression, the number of data elements to be queried in the target list is obtained;
[0176] In the case where the number of data elements to be queried reaches a preset number threshold, a temporary hash table of the target list is created, and the temporary hash table is of the same type of data elements in the target list;
[0177] The first hash value corresponding to each row of data in the source data table is calculated;
[0178] The first hash value is taken as a query target to query from the temporary hash table to obtain a query result.
[0179] In one embodiment, the computer program is executed by the processor to further implement the following steps:
[0180] Each row of data in the target list is traversed, and a second hash value of each row of data in the target list is calculated according to a preset connection condition;
[0181] According to the second hash value and the corresponding row of data, a temporary hash table of the target list is created.
[0182] In one embodiment, the computer program is executed by the processor to further implement the following steps:
[0183] Each row of data in the source data table is traversed, and a first hash value of each row of data in the source data table is calculated according to a preset connection condition, and the preset connection condition is an equal value association condition in the temporary hash table and the target list.
[0184] In one embodiment, the computer program is executed by the processor to further implement the following steps:
[0185] The first hash value of each row of data in the source data table is taken as a query target to query from the temporary hash table;
[0186] In the case where the first hash value is equal to the second hash value in the temporary hash table, the row data corresponding to the second hash value is taken as a hit target data.
[0187] In one embodiment, the computer program is executed by the processor to further implement the following steps:
[0188] The query condition of the IN expression is deleted from the WHERE clause of the query statement, and the query condition of the hash connection between the temporary hash table and the target list is established.
[0189] In one embodiment, the computer program is executed by the processor to further implement the following steps:
[0190] When the number of data elements to be queried does not reach the preset threshold, the IN expression is used to traverse each row of data in the query target list based on each row of data in the source data table to obtain the query result.
[0191] In one embodiment, a computer program product is provided, comprising a computer program, which, when executed by a processor, implements the following steps:
[0192] receiving an SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list;
[0193] According to the IN expression, obtain the number of data elements to be queried in the target list;
[0194] When the number of data elements to be queried reaches a preset threshold, a temporary hash table of the target list is created, and the data element type of the temporary hash table is the same as that of the target list;
[0195] Calculate the first hash value corresponding to each row of data in the source data table;
[0196] The first hash value is used as the query target, and a query is performed from the temporary hash table to obtain a query result.
[0197] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:
[0198] Traverse each row of data in the target list and calculate the second hash value of each row of data in the target list according to the preset connection conditions;
[0199] A temporary hash table of the target list is created according to the second hash value and the corresponding row data.
[0200] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:
[0201] Traverse each row of data in the source data table and calculate the first hash value of each row of data in the source data table according to the preset connection condition. The preset connection condition is the equal value association condition between the temporary hash table and the target list.
[0202] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:
[0203] Use the first hash value of each row of data in the source data table as the query target and query from the temporary hash table;
[0204] When the first hash value is equal to the second hash value in the temporary hash table, the row data corresponding to the second hash value is used as the hit target data.
[0205] In one embodiment, the computer program, when executed by the processor, further implements the following steps:
[0206] The query condition of the IN expression is deleted from the WHERE clause of the query statement, and a temporary hash table is established to build a hash connection between the query conditions.
[0207] In one embodiment, the computer program, when executed by the processor, further implements the following steps:
[0208] In the case where the number of data elements to be queried does not reach the preset number threshold, the IN expression is used to traverse each row of data in the query target list based on each row of data in the source data table to obtain a query result.
[0209] It should be noted that the user information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in the present application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of related data need to comply with relevant regulations.
[0210] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a non-volatile computer readable storage medium, and when executed, can include the processes of the above-mentioned embodiment methods. Any reference to memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. The non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical storage, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. The volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration but not limitation, the RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc. The database involved in the embodiments provided in the present application can include at least one of a relational database and a non-relational database. The non-relational database can include a distributed database based on a block chain, etc., without being limited thereto. The processor involved in the embodiments provided in the present application can be a general-purpose processor, a central processing unit, a graphics processing unit, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., without being limited thereto.
[0211] The technical features of the above embodiments can be combined in any manner. To make the description concise, not all possible combinations of the technical features in the above embodiments are described, but as long as the combinations of the technical features do not exist contradictions, they should be considered as the scope of the present application.
[0212] The above-described embodiments are merely illustrative of several embodiments of the present application, which are described in more detail and in a specific manner, but should not be construed as limiting the scope of the patent of the present application. It should be noted that, for those of ordinary skill in the art, several modifications and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the appended claims.
Claims
1. A database query statement optimization method, characterized in that: The method comprises: Receive an SQL database query request, the SQL database query request carrying an IN expression for querying data elements from a target list; According to the IN expression, the number of the data elements to be queried in the target list is obtained; When the number of the data elements to be queried reaches a preset number threshold, creating a temporary hash table of the target list, wherein the temporary hash table has the same data element type as that of the target list; Calculate the first hash value corresponding to each row of data in the source data table; The first hash value is used as a query target, and a query is performed from the temporary hash table to obtain a query result.
2. The method according to claim 1, characterized in that The step of creating a temporary hash table of the target list comprises: Traversing each row of data in the target list, and calculating a second hash value for each row of data in the target list according to a preset connection condition; A temporary hash table of the target list is created according to the second hash value and the corresponding row data.
3. The method according to claim 2, characterized in that The step of calculating the first hash value corresponding to each row of data in the source data table includes: Traverse each row of data in the source data table, and calculate the first hash value of each row of data in the source data table according to the preset connection condition, where the preset connection condition is an equal value association condition between the temporary hash table and the target list.
4. The method according to claim 3, characterized in that The step of using the first hash value as a query target and performing a query in the temporary hash table to obtain a query result includes: Using the first hash value of each row of data in the source data table as a query target, querying from the temporary hash table; When the first hash value is equal to the second hash value in the temporary hash table, row data corresponding to the second hash value is used as hit target data.
5. The method according to claim 1, wherein After creating the temporary hash table of the target list, the method further includes: The query condition of the IN expression is deleted from the WHERE clause of the query statement, and a query condition of a hash connection between the temporary hash table and the target list is established.
6. The method according to claim 1, characterized in that After obtaining the number of the to-be-queried data elements in the target list, the method further includes: When the number of the data elements to be queried does not reach the preset number threshold, the IN expression is used to traverse and query each row of data in the target list based on each row of data in the source data table to obtain a query result.
7. A database query statement optimization device, characterized in that: The device comprises: A request receiving module, configured to receive an SQL database query request, wherein the SQL database query request carries an IN expression for querying data elements from a target list; A search module, configured to obtain the number of data elements to be queried in the target list according to the IN expression; a hash table establishment module, configured to create a temporary hash table of the target list when the number of the data elements to be queried reaches a preset number threshold, wherein the temporary hash table has the same data element type as that of the target list; A hash value calculation module is used to calculate the first hash value corresponding to each row of data in the source data table; The query module is configured to use the first hash value as a query target, perform a query from the temporary hash table, and obtain a query result.
8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.
10. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.