Distributed database association table processing method and device, equipment and storage medium

Abstract

The invention provides a distributed database association table processing method and device, equipment and a storage medium. In some embodiments of the present disclosure, according to a query log, fields and field information used in a table or partition corresponding to each query are extracted, and fields and field information as query conditions in each query are extracted; traversing the query log, and identifying a candidate frequent item set according to a field and field information used in a table or a partition corresponding to each query, a field and field information as a query condition in each query, and a hash tree; according to the method, the target frequent item set with the same field information as the query condition is selected from the candidate frequent item set by identifying the frequent item set; adjusting a table or a partition corresponding to the target frequent item set to the same node of the distributed database; the search coverage is improved, the search efficiency is improved, the accuracy is improved, the labor cost is reduced, and the database structure updating efficiency is improved.

Description

Technical Field

[0001] The present disclosure relates to the field of database technology, and in particular to a distributed database association table processing method, device, equipment and storage medium. Background Art

[0002] A distributed database is a logically unified database formed by connecting multiple physically dispersed database units using a computer network. Each connected database unit is called a site or node. A transaction is an independent operation unit, an atomic unit operation that is either all or nothing.

[0003] In the business logic of the application, there are some common fields between two or more tables / partitions. The types and lengths of these fields are exactly the same, and the names of the fields may or may not be the same. Moreover, in the business processing, these tables always appear in the same SQL statement or business logic unit. In the physical design phase of the application software, the tables / partitions with related relationships in the database are placed on the same node of the distributed database as much as possible to reduce cross-node access to data, improve data access efficiency, and reduce database system overhead.

[0004] Currently, during the application design process, it is necessary to manually adjust tables or partitions with associated relationships in the database to the same node. The search coverage, efficiency, and accuracy of tables or partitions with associated relationships in the database are low, and the labor cost is high, which in turn affects the efficiency of updating the database structure. Summary of the invention

[0005] The present disclosure provides a distributed database association table processing method, device, equipment and storage medium to at least solve the problem that the table or partition search of the association relationship in the existing database has low coverage, low search efficiency, low accuracy and high labor cost, which in turn affects the efficiency of database structure update.

[0006] The technical solution of the present disclosure is as follows:

[0007] The present disclosure provides a distributed database association table processing method, including:

[0008] Get the query log of the database;

[0009] According to the query log, extract the fields and field information used in the table or partition corresponding to each query, and extract the fields and field information used as query conditions in each query;

[0010] Traversing the query log, identifying candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree;

[0011] Selecting a target frequent item set having the same field information and the field information appearing as a query condition from the candidate frequent item sets;

[0012] The table or partition corresponding to the target frequent itemset is adjusted to the same node of the distributed database.

[0013] Optionally, extracting the fields and field information used in the table or partition corresponding to each query, and extracting the fields and field information used as query conditions in each query according to the query log, includes:

[0014] Read each SQL statement from the query log;

[0015] Use regular expressions or SQL parsing libraries to parse each of the SQL statements to obtain the table or partition corresponding to each query;

[0016] For each table or partition corresponding to the query, query the database metadata to obtain the fields and field information used in the table or partition corresponding to the query;

[0017] A WHERE clause is extracted from each of the SQL statements, and the WHERE clause is parsed to obtain fields and field information used as query conditions in each query.

[0018] Optionally, traversing the query log to identify candidate frequent itemsets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree, includes:

[0019] Create an empty hash tree;

[0020] Traversing the query log, and generating a two-table combination for each query corresponding to the query log;

[0021] Inserting the target two tables that meet the field matching condition into the hash tree;

[0022] Generate a first multinomial set according to the target two-table combination;

[0023] Selecting a second multi-item set that satisfies the field matching condition and uses the confirmation field as a query condition from the first multi-item set, and updating the occurrence frequency of the second multi-item set;

[0024] A candidate frequent item set whose occurrence frequency is greater than or equal to a support threshold is selected from the second multinomial set.

[0025] Optionally, inserting the target two-table combination satisfying the field matching condition into the hash tree includes:

[0026] Get the field information of the first table and the field information of the second table;

[0027] Comparing the field information of the first table with the field information of the second table;

[0028] In the case where the field information of the first table is the same as the field information of the second table, the first table and the second table are inserted into the hash tree.

[0029] Optionally, selecting a target frequent item set having the same field information and the field information appearing as a query condition from the candidate frequent item sets includes:

[0030] A new function is defined, which includes the following parameters: a known frequent item set list, a query record list, and a table field information dictionary; wherein the known frequent item set list stores the candidate frequent item sets;

[0031] Initialize an empty list to store the filtered frequent item sets;

[0032] Traverse each frequent item set in the known frequent item set list one by one;

[0033] Use list comprehension to check whether every two tables in the current frequent itemset have the same fields;

[0034] In the case where all tables in the current frequent itemset have the same fields, traverse the query record list and check each query;

[0035] Extract the conditional fields in the current query;

[0036] Check whether there is a field in the condition field of the current query that is the same as a field in the table of the current frequent itemset;

[0037] If there is a field identical to the current frequent item set in the condition field of the current query, the current frequent item set is added to a new list until the target frequent item set is screened out.

[0038] Optionally, after selecting the target frequent item sets having the same field information and the field information appearing as the query condition from the candidate frequent item sets, the method further includes:

[0039] The target frequent itemsets are output in a structured manner.

[0040] The embodiment of the present disclosure also provides a distributed database association table processing device, including:

[0041] The acquisition module is used to obtain the query log of the database;

[0042] An extraction module, used to extract the fields and field information used in the table or partition corresponding to each query, and to extract the fields and field information used as query conditions in each query, according to the query log;

[0043] An identification module, configured to traverse the query log and identify candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree;

[0044] A selection module, used to select target frequent item sets with the same field information and whose field information appears as a query condition from the candidate frequent item sets;

[0045] The adjustment module is used to adjust the table or partition corresponding to the target frequent itemset to the same node of the distributed database.

[0046] The present disclosure also provides an electronic device, including:

[0047] processor;

[0048] a memory for storing instructions executable by the processor;

[0049] The processor is configured to execute the instructions to implement each step in the above method.

[0050] The embodiment of the present disclosure further provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, each step in the above method is implemented.

[0051] The embodiment of the present disclosure further provides a computer program product, including a computer program / instruction, which implements each step of the above method when executed by a processor.

[0052] The technical solution provided by the embodiments of the present disclosure brings at least the following beneficial effects:

[0053] In some embodiments of the present disclosure, a query log of a database is obtained; based on the query log, the fields and field information used in the table or partition corresponding to each query are extracted, and the fields and field information used as query conditions in each query are extracted; the query log is traversed, and candidate frequent item sets are identified based on the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and a hash tree; the present disclosure selects a target frequent item set with the same field information and the field information appearing as a query condition from the candidate frequent item set by identifying the frequent item set; the table or partition corresponding to the target frequent item set is adjusted to the same node of the distributed database; the table or partition with an associated relationship in the database is automatically searched, the search coverage is improved, the search efficiency is improved, the accuracy is improved, the labor cost is reduced, and the efficiency of updating the database structure is improved.

[0054] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] The drawings herein are incorporated into and constitute a part of the specification, illustrate embodiments consistent with the present disclosure, and together with the description are used to explain the principles of the present disclosure, and do not constitute improper limitations on the present disclosure.

[0056] Figure 1 A flowchart of a distributed database association table processing method provided for an exemplary embodiment of the present disclosure;

[0057] Figure 2 A schematic diagram of the structure of a distributed database association table processing device provided for an exemplary embodiment of the present disclosure;

[0058] Figure 3 A schematic structural diagram of an electronic device provided by an exemplary embodiment of the present disclosure. DETAILED DESCRIPTION

[0059] In order to enable ordinary persons in the art to better understand the technical solutions of the present disclosure, the technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the accompanying drawings.

[0060] It should be noted that the terms "first", "second", etc. in the specification and claims of the present disclosure and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present disclosure described herein can be implemented in an order other than those illustrated or described herein. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present disclosure. Instead, they are merely examples of devices and methods consistent with some aspects of the present disclosure.

[0061] It should be noted that the user information involved in this disclosure includes but is not limited to: user device information and user personal information; the collection, storage, use, processing, transmission, provision and disclosure of user information in this disclosure comply with the relevant laws and regulations and do not violate public order and good morals.

[0062] A distributed database is a logically unified database formed by connecting multiple physically dispersed database units using a computer network. Each connected database unit is called a site or node. A transaction is an independent operation unit, an atomic unit operation that is either all or nothing.

[0063] In the field of computer science and data mining, the Apriori Algorithm is one of the classic algorithms for learning association rules. The Apriori Algorithm is designed to process databases containing transaction information. The Apriori Algorithm uses a breadth-first search algorithm to search and a tree structure to efficiently count candidate item sets.

[0064] In the business logic of the application, there are some common fields between two or more tables / partitions. The types and lengths of these fields are exactly the same, and the names of the fields may or may not be the same. Moreover, in the business processing, these tables always appear in the same SQL statement or business logic unit. In the physical design phase of the application software, the tables / partitions with related relationships in the database are placed on the same node of the distributed database as much as possible to reduce cross-node access to data, improve data access efficiency, and reduce database system overhead.

[0065] Currently, during the application design process, the database logic design stage manually determines which tables or partitions are related, and if so, they are adjusted to the same node; after the application is launched, the database administrator analyzes the data transmission across nodes during the tuning process to determine whether it is a table or partition with a related relationship, and if so, further analyzes whether it should be adjusted to the same node. It is necessary to manually adjust the tables or partitions with related relationships in the database to the same node. In a large database containing thousands of library tables, the search efficiency and accuracy of related tables or partitions are low, and the labor cost is high. In addition, the search coverage of related tables or partitions may not be complete, and further leads to the problem of low efficiency in updating the database structure.

[0066] In view of the above technical problems, in some embodiments of the present disclosure, a query log of a database is obtained; based on the query log, the fields and field information used in the table or partition corresponding to each query are extracted, and the fields and field information used as query conditions in each query are extracted; the query log is traversed, and candidate frequent item sets are identified based on the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and a hash tree; the present disclosure selects target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets by identifying frequent item sets; the table or partition corresponding to the target frequent item set is adjusted to the same node of a distributed database; the tables or partitions with associated relationships in the database are automatically searched to improve the search coverage, improve the search efficiency, improve the accuracy, reduce the labor cost, and improve the efficiency of updating the database structure.

[0067] The technical solutions provided by various embodiments of the present disclosure are described in detail below in conjunction with the accompanying drawings.

[0068] Figure 1 The following is a flow chart of a distributed database association table processing method provided by an exemplary embodiment of the present disclosure. Figure 1 As shown, the method includes:

[0069] S101: Obtaining a query log of a database;

[0070] S102: extracting the fields and field information used in the table or partition corresponding to each query, and extracting the fields and field information used as query conditions in each query, according to the query log;

[0071] S103: traverse the query log, and identify candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree;

[0072] S104: Selecting target frequent itemsets with the same field information and whose field information appears as a query condition from the candidate frequent itemsets;

[0073] S105: Adjust the table or partition corresponding to the target frequent itemset to the same node of the distributed database.

[0074] In this embodiment, the execution subject of the above method is a terminal device or a server.

[0075] Among them, the terminal device includes but is not limited to a mobile station (MS), a mobile terminal, a mobile phone, a handset and a portable equipment, etc. The terminal device can communicate with one or more core networks via a radio access network (RAN). For example, the terminal device can be a mobile phone (or a "cellular" phone), a computer with a wireless communication function, etc. The terminal device can also be a computer with a wireless transceiver function, a virtual reality (VR) terminal device, an AR terminal device, a wireless terminal in industrial control, a wireless terminal in self driving, a wireless terminal in remote medical, a wireless terminal in a smart grid, a wireless terminal in transportation safety, a wireless terminal in a smart city, a wireless terminal in a smart home, etc., and the operating system installed on the terminal device includes but is not limited to: IOS, Android, windows, linux, Mac OS and other operating systems. In different networks, the terminal may be called different names, such as user equipment, mobile station, subscriber unit, station, cellular phone, personal digital assistant, wireless modem, wireless communication device, handheld device, laptop computer, cordless phone, wireless local loop station, television, etc. For the convenience of description, it is referred to as terminal equipment in this embodiment.

[0076] In this embodiment, the implementation form of the server is not limited. For example, the server can be a conventional server, a cloud server, a cloud host, a virtual center, or other server devices. The server mainly includes a processor, a hard disk, a memory, a system bus, etc., and a general computer architecture type.

[0077] In this embodiment, a query log of a database is obtained; according to the query log, the fields and field information used in the table or partition corresponding to each query are extracted, and the fields and field information used as query conditions in each query are extracted; the query log is traversed, and candidate frequent item sets are identified according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and a hash tree; the present disclosure selects target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets by identifying frequent item sets; the table or partition corresponding to the target frequent item set is adjusted to the same node of the distributed database; the table or partition with an associated relationship in the database is automatically searched, the search coverage is improved, the search efficiency is improved, the accuracy is improved, the labor cost is reduced, and the efficiency of updating the database structure is improved.

[0078] In this embodiment, a database table is a basic structure in a database, used to store data. A table consists of columns and rows, each column defines the attributes of the data in the table, and each row is a specific data instance. A table can contain multiple columns and multiple rows, used to organize and store data, and usually represents an entity or data set in a database.

[0079] Partition: Partition is a logical division of data in a database table. Partitions can divide the data in a table into different parts according to certain rules. Each part is called a partition. Partitions can be defined based on the values ​​of certain columns, such as time, geographic location, etc., or using other partitioning strategies, such as hash partitioning. The main purpose is to improve query efficiency, simplify data management, and optimize data loading and deletion operations.

[0080] In some embodiments of the present disclosure, a query log of a database is obtained. Exemplarily, according to specific settings of the database system, a query log recording function is enabled to collect the query log of the database.

[0081] In some embodiments of the present disclosure, the fields and field information used in the table or partition corresponding to each query are extracted based on the query log, and the fields and field information used as query conditions in each query are extracted. One achievable method is to read each SQL statement from the query log; parse each SQL statement using a regular expression or SQL parsing library to obtain the table or partition corresponding to each query; query the database metadata for the table or partition corresponding to each query to obtain the fields and field information used in the table or partition corresponding to each query; extract the WHERE clause from each SQL statement, and parse the WHERE clause to obtain the fields and field information used as query conditions in each query. It should be noted that the field information in this embodiment includes field type and field length.

[0082] In an exemplary embodiment, the table or partition involved in each query in the query log is extracted. Each SQL statement is read from the query log, and the SQL statement is analyzed using a regular expression or SQL parsing library to extract the table or partition involved. For example, a function can be defined using the sqlparse library to parse and extract the table names involved from a given SQL statement. The function first parses the input SQL, then traverses each parsed statement object, extracts and collects the table names therefrom. Finally, the function returns a list containing all extracted table names.

[0083] In an exemplary embodiment, for each table or partition involved in the query, the database metadata is queried to obtain the fields, field types, and field lengths. For example, a function is defined to connect to a specified database through a specific class library, query and return all fields and their data types of a given table name, and the results are returned in the form of a dictionary, where the key is the field name and the value is the field type and field length.

[0084] In an exemplary embodiment, extracting the fields used as query conditions in each query includes: extracting the WHERE clause from the SQL statement, analyzing the WHERE clause, and extracting the fields used as query conditions. Taking the use of the sqlparse library as an example, the specific operations are as follows: define a function and use the sqlparse library to extract the condition fields of the WHERE clause from the provided SQL statement. The function first parses the input SQL, and then traverses each parsed statement to find the WHERE clause. For the WHERE clause found, the field names are extracted and collected through the keywords representing the named entities (such as table names, column names) in the SQL statement. Finally, the function returns a list containing all the extracted field conditions.

[0085] In some embodiments of the present disclosure, query logs are traversed to identify candidate frequent item sets based on the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and a hash tree. One achievable method is to create an empty hash tree; traverse the query logs, generate a two-table combination for each query corresponding to the query log; insert the target two-table combination that meets the field matching condition into the hash tree; generate a first multi-item set based on the target two-table combination; select a second multi-item set that meets the field matching condition and confirms the field as the query condition from the first multi-item set, and update the occurrence frequency of the second multi-item set; select a candidate frequent item set whose occurrence frequency is greater than or equal to the support threshold from the second multi-item set.

[0086] In some embodiments of the present disclosure, a hash tree is used to calculate frequent 2-itemsets, and based on the frequent 2-itemsets, candidate combinations of larger itemsets, namely, first multi-itemsets, are generated.

[0087] In an optional embodiment, a hash tree is used to calculate frequent 2-item sets: an empty hash tree is created; the query log is traversed to generate all possible two-table combinations; for each combination, the field matching conditions are checked and the fields are confirmed to be used as query conditions at the same time; and the two-table combinations that meet the field matching conditions are inserted into the hash tree.

[0088] In some embodiments of the present disclosure, the steps of constructing a complete function to calculate frequent 2-itemsets are as follows:

[0089] Step S21: Create an empty hash tree. Initialize an empty hash tree to store possible two-table combinations. Specifically: define a new class, which is used to create a hash tree structure. Initialize an instance object of this new class. In this class, initialize an empty dictionary. This dictionary is used to store data in the hash tree, where the key of the dictionary represents the table name and the corresponding value is a list used to store the table combination associated with this key. In this class, create a method for inserting a table combination into the hash tree. This method requires a parameter, which is the table combination to be inserted. In order to simplify the creation of the hash tree, the method selects the first table in the table combination as the key of the dictionary. In this way, there will be multiple associated table combinations under the same key in the dictionary. The method will first check whether the key already exists in the dictionary. If the key does not exist in the dictionary, an empty list is created for the key, which is used to store the table combination associated with the key. Regardless of whether the key already exists in the dictionary, the method will add the current table combination to the list associated with the key.

[0090] Step S22: For each query in the query log, generate all possible combinations of two tables. Take Python as an example, the implementation is as follows: Import the itertools module of Python. Define a function that receives a parameter, which is expected to be a list containing multiple table names. In this function, use the combinations function in the itertools module to generate all possible pairwise combinations for the elements in the input list, and convert these combinations into a list and return them.

[0091] Step S23: For each combination, check the field matching condition to verify whether the field information of the two tables completely matches. Specifically: define a new function that receives three parameters: the first table, the second table, and a dictionary containing the table field information. The purpose of the function is to check whether the two tables have the same field attributes (based on the type and length of the field). From the provided dictionary, get the field information associated with the first table. Similarly, from the provided dictionary, get the field information associated with the second table. Compare the field information of the first table and the second table to determine whether they completely match, and return the corresponding Boolean value based on the comparison result. If the field information of the two tables is exactly the same, return True, otherwise return False.

[0092] Step S24: insert the two table combinations that meet the field matching conditions into the hash tree. One feasible way is to obtain the field information of the first table and the field information of the second table; compare the field information of the first table with the field information of the first table; when the field information of the first table is the same as the field information of the second table, insert the first table and the second table into the hash tree. Among them, if the two-table combination meets the field matching condition, insert it into the previously created hash tree. Specifically, define a function that receives two parameters: the first parameter is a hash tree object, and the second parameter is a table combination list. The purpose is to insert this table combination list into the specified hash tree object. Start a loop in the function and iterate through each combination in the table combination list one by one. In each loop, call the insert method of the hash tree object to add the current table combination to the hash tree.

[0093] In some embodiments of the present disclosure, based on frequent 2-itemsets, a candidate combination of a larger item set, i.e., a first multi-item set, is generated. The Apriori algorithm is used to identify a larger frequent item set. Starting from a known frequent 2-item set, a larger item set, i.e., a first multi-item set, is generated; the query log is traversed to check each candidate item set; for a second multi-item set that satisfies the field matching condition and confirms that these fields are also used as query conditions, the occurrence frequency of the second multi-item set is updated; and the candidate frequent item sets are screened out according to the support threshold. The following steps are included:

[0094] Step S31: Starting from a known frequent 2-item set, generate candidate combinations of larger item sets. Using the properties of Apriori, known frequent item sets can be used to generate new candidate item sets. For example, known frequent 2-item sets can be combined to generate candidate 3-item sets, and then 3-item sets can be combined to generate 4-item sets, and so on. Specifically as follows: Define a new function that receives two parameters: a list of known frequent item sets and an integer representing the size of the candidate item set to be generated. Initialize an empty set for temporarily storing the generated candidate item sets. Use an outer loop to traverse each item set in the list of known frequent item sets one by one. Then use an inner loop to traverse the list of known frequent item sets. Merge the two item sets in the outer loop and the inner loop, then sort and convert them into tuples to form a new union. Verify whether the number of elements in this union matches the given integer. If the number matches, add this union to the temporary storage set. After all loops are completed, convert the temporarily stored set into a list and return it.

[0095] Step S32: traverse the query log and check each candidate item set. In order to determine whether a candidate item set is truly frequent, it is necessary to traverse the query log to check how many queries each candidate item set appears in.

[0096] Step S33: For the item sets that meet the field matching condition, update their occurrence frequency. If a candidate item set appears in the query and meets the field matching condition, the occurrence frequency of this item set needs to be updated. Specifically: define a new function that receives three parameters: a candidate item set list, a query log list, and a dictionary containing table field information. Initialize a new dictionary, in which the key is each item set in the candidate item set list, and the initial value corresponding to each key is set to 0. This dictionary will be used to record the number of times each item set appears in the query log. Use an external loop to traverse each query in the query log list one by one. Use an existing extract_tables_from_sql function to extract all tables involved in the query and store these tables in a new list. Use an internal loop to traverse each item set in the candidate item set list one by one. Check whether the current item set is a subset of all tables in the new list. If so, it indicates that the item set is used in the current query. Combine every two tables in the current item set and check whether the fields between them are completely matched. If all combined fields match, proceed to the next step. If all the above conditions are met, add 1 to the value of the corresponding item set in the new dictionary. After all loops are completed, return the dictionary that records the frequency of occurrence of each item set.

[0097] Step S34: Select candidate frequent item sets whose occurrence frequency is greater than or equal to the support threshold from the second multinomial set. Specifically: define a new function that receives two parameters: a dictionary that records the number of occurrences of item sets and a support threshold. Use list comprehension to traverse each key-value pair in the dictionary that records the number of occurrences of item sets one by one. Only when the value (i.e., the number of occurrences of the item set) is greater than or equal to the support threshold, the key (i.e., the item set) will be added to the new list. Finally, return this new list, which contains all item sets that meet the support threshold condition.

[0098] It should be noted that the support standard refers to the minimum number of times two or more tables appear in the query log at the same time. The reference method for determining the support standard is as follows: Understand the query frequency of each table through the collected query log data and set a preliminary support threshold. Use this threshold to test and then adjust it based on the results. In addition, the threshold selection can be further optimized by combining business knowledge. The final support threshold will be used to filter frequent table combinations.

[0099] In summary, the Apriori algorithm can be used to identify larger frequent item sets. This process can be performed recursively until no more frequent item sets can be generated.

[0100] In some embodiments of the present disclosure, a target frequent item set with the same field information and the field information appearing as a query condition is selected from the candidate frequent item set. One achievable method is to initialize an empty list for storing the filtered frequent item sets; traverse each frequent item set in the known frequent item set list one by one; use list derivation to check whether every two tables in the current frequent item set have the same fields; if all tables in the current frequent item set have the same fields, traverse the query record list and check each query; extract the condition field in the current query; check whether there is the same field in the condition field of the current query as in the table in the current frequent item set; if there is the same field in the condition field of the current query as in the current frequent item set, add the current frequent item set to a new list until the target frequent item set is filtered out.

[0101] In an exemplary embodiment, the table combinations with exactly the same field information and used as conditions in the query are further screened from the candidate frequent itemsets. For each frequent itemset, check whether each pair of tables in the combination has completely matched fields and whether these fields appear as query conditions. If the conditions are met, the item set is added to the final result. Specifically, a new function is defined, which receives three parameters: a known frequent itemset list, a query record list, and a table field information dictionary. Initialize an empty list to store the filtered frequent itemsets. Traverse each frequent itemset in the known frequent itemset list one by one. Use list derivation to check whether every two tables in the current itemset have the same fields. If all table pairs have the same fields, mark it as True, otherwise mark it as False. If all tables in the current frequent itemset have the same fields, enter the next check link. Traverse the query record list and check each query. Extract the condition fields in the current query. Check whether there are fields in the condition fields of the current query that are the same as those in the tables in the current frequent itemset. If the condition field of the current query contains the same field as the current frequent item set, add this item set to the new list. Since the current item set has been added to the new list, the current query check can be ended and the next round can be entered. The filtered frequent item set list is returned.

[0102] In some embodiments of the present disclosure, the target frequent item sets are output in a structured manner. For example: each item set and its corresponding number of occurrences. Assume that in a database with N tables / partitions, the following frequent item sets are found: [(first table / partition, second table / partition), (third table / partition, fourth table / partition, fifth table / partition)…].

[0103] In some embodiments of the present disclosure, the corresponding table / partition is adjusted to the same node of the distributed database according to the obtained frequent itemsets.

[0104] The present disclosure identifies frequent item sets and finds out the combination of tables or partitions with exactly the same type and length, exactly the same fields, which often appear in the same query and are used as query conditions from a large database. The method not only provides a powerful tool for database optimization, but also helps to improve business logic, resource allocation and performance. Compared with the manual screening scheme, the present disclosure can check and analyze a large amount of data in a short time through automated data processing and analysis, especially in the scenario of a large database system, reducing the time consumption of operation and greatly improving efficiency. Compared with the manual screening scheme, the present disclosure adopts an objective method based on statistics and data mining, which can more accurately find the real frequent item sets and avoid the influence of subjective judgment, as well as bias or error. Compared with the manual screening scheme, the present disclosure provides a fixed and reproducible process to ensure that the results obtained each time are consistent, avoiding the situation where different people may get different results due to human experience and judgment. Compared with the manual screening scheme, the present disclosure can easily process a large amount of data when the scale of the database increases or the number of query logs increases, and has good scalability.

[0105] In the above method embodiment of the present disclosure, a query log of a database is obtained; according to the query log, the fields and field information used in the table or partition corresponding to each query are extracted, and the fields and field information used as query conditions in each query are extracted; the query log is traversed, and candidate frequent item sets are identified according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree; the present disclosure selects target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets by identifying frequent item sets; the table or partition corresponding to the target frequent item set is adjusted to the same node of the distributed database; the table or partition with an associated relationship in the database is automatically searched, the search coverage is improved, the search efficiency is improved, the accuracy is improved, the labor cost is reduced, and the efficiency of updating the database structure is improved.

[0106] Figure 2 FIG. 2 is a schematic diagram of a distributed database association table processing device 20 provided by an exemplary embodiment of the present disclosure. Figure 2 As shown, the distributed database association table processing device 20 includes: an acquisition module 21, an extraction module 22, an identification module 23, a selection module 24 and an adjustment module 25.

[0107] Wherein, the acquisition module 21 is used to obtain the query log of the database;

[0108] The extraction module 22 is used to extract the fields and field information used in the table or partition corresponding to each query, and to extract the fields and field information used as query conditions in each query according to the query log;

[0109] Identification module 23, used to traverse the query log, and identify candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree;

[0110] A selection module 24 is used to select target frequent item sets with the same field information and whose field information appears as a query condition from the candidate frequent item sets;

[0111] The adjustment module 25 is used to adjust the table or partition corresponding to the target frequent itemset to the same node of the distributed database.

[0112] Optionally, when the extraction module 22 extracts the fields and field information used in the table or partition corresponding to each query, and extracts the fields and field information used as query conditions in each query according to the query log, it is used to:

[0113] Read each SQL statement from the query log;

[0114] Use regular expressions or SQL parsing libraries to parse each SQL statement and obtain the table or partition corresponding to each query;

[0115] For each table or partition corresponding to the query, query the database metadata to obtain the fields and field information used in the table or partition corresponding to the query;

[0116] Extract the WHERE clause from each SQL statement and parse the WHERE clause to obtain the fields and field information used as query conditions in each query.

[0117] Optionally, when the identification module 23 traverses the query log and identifies candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree, it is used to:

[0118] Create an empty hash tree;

[0119] Traverse the query log and generate a combination of two tables for each query corresponding to the query log;

[0120] Insert the target two tables that meet the field matching conditions into the hash tree;

[0121] Generate the first multinomial set according to the combination of the two target tables;

[0122] Selecting a second polynomial set that satisfies the field matching condition and confirms the field as a query condition from the first polynomial set, and updating the occurrence frequency of the second polynomial set;

[0123] A candidate frequent item set whose occurrence frequency is greater than or equal to the support threshold is selected from the second multinomial set.

[0124] Optionally, when inserting the target two-table combination satisfying the field matching condition into the hash tree, the identification module 23 is used to:

[0125] Get the field information of the first table and the field information of the second table;

[0126] Compare the field information of the first table with the field information of the first table;

[0127] In the case where the field information of the first table is the same as the field information of the second table, the first table and the second table are inserted into the hash tree.

[0128] Optionally, when selecting a target frequent item set having the same field information and the field information appearing as a query condition from the candidate frequent item sets, the selection module 24 is configured to:

[0129] Define a new function, which includes the following parameters: a known frequent item set list, a query record list, and a table field information dictionary; wherein the known frequent item set list stores candidate frequent item sets;

[0130] Initialize an empty list to store the filtered frequent item sets;

[0131] Traverse each frequent item set in the known frequent item set list one by one;

[0132] Use list comprehension to check whether every two tables in the current frequent itemset have the same fields;

[0133] When all tables in the current frequent itemset have the same fields, traverse the query record list and check each query;

[0134] Extract the conditional fields in the current query;

[0135] Check whether there is a field in the condition field of the current query that is the same as the field in the table of the current frequent itemset;

[0136] If there is a field in the condition field of the current query that is the same as the current frequent item set, the current frequent item set is added to the new list until the target frequent item set is filtered out.

[0137] Optionally, after selecting target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets, the selection module 24 may also be used to:

[0138] Output the target frequent itemsets in a structured manner.

[0139] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0140] Figure 3 FIG. 1 is a schematic diagram of a structure of an electronic device provided by an exemplary embodiment of the present disclosure. Figure 3 As shown, the electronic device includes: a memory 31 and a processor 32. In addition, the electronic device also includes a power supply component 33 and a communication component 34.

[0141] The memory 31 is used to store computer programs and can be configured to store various other data to support operations on the electronic device. Examples of such data include instructions for any application program or method operating on the electronic device.

[0142] The memory 31 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk.

[0143] The communication component 34 is used for data transmission with other devices.

[0144] The processor 32 can execute computer instructions stored in the memory 31 to: obtain a query log of the database; extract the fields and field information used in the table or partition corresponding to each query, and extract the fields and field information used as query conditions in each query according to the query log; traverse the query log, and identify candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree; select a target frequent item set with the same field information and the field information appearing as the query condition from the candidate frequent item sets; and adjust the table or partition corresponding to the target frequent item set to the same node of the distributed database.

[0145] Accordingly, the present disclosure also provides a computer-readable storage medium storing a computer program. When the computer-readable storage medium stores the computer program and the computer program is executed by one or more processors, the one or more processors execute Figure 1 Each step in the method embodiment.

[0146] Accordingly, the present disclosure also provides a computer program product, the computer program product includes a computer program / instruction, the computer program / instruction is executed by a processor Figure 1 Each step in the method embodiment.

[0147] Above Figure 3 The communication component in is configured to facilitate wired or wireless communication between the device where the communication component is located and other devices. The device where the communication component is located can access a wireless network based on a communication standard, such as WiFi, 2G, 3G, 4G / LTE, 5G and other mobile communication networks, or a combination thereof. In an exemplary embodiment, the communication component receives a broadcast signal or broadcast-related information from an external broadcast management system via a broadcast channel. In an exemplary embodiment, the communication component also includes a near field communication (NFC) module to facilitate short-range communication. For example, the NFC module can be implemented based on radio frequency identification (RFID) technology, infrared data association (IrDA) technology, ultra-wideband (UWB) technology, Bluetooth (BT) technology and other technologies.

[0148] Above Figure 3 The power supply component in the device provides power to various components of the device where the power supply component is located. The power supply component may include a power management system, one or more power supplies, and other components associated with generating, managing, and distributing power to the device where the power supply component is located.

[0149] The electronic device also includes a display screen and an audio component.

[0150] The display screen includes a screen, and the screen may include a liquid crystal display (LCD) and a touch panel (TP). If the screen includes a touch panel, the screen may be implemented as a touch screen to receive input signals from a user. The touch panel includes one or more touch sensors to sense touch, slide, and gestures on the touch panel. The touch sensor may not only sense the boundary of a touch or slide action, but also detect the duration and pressure associated with the touch or slide operation.

[0151] The audio component may be configured to output and / or input audio signals. For example, the audio component includes a microphone (MIC), and when the device where the audio component is located is in an operating mode, such as a call mode, a recording mode, and a speech recognition mode, the microphone is configured to receive an external audio signal. The received audio signal may be further stored in a memory or sent via a communication component. In some embodiments, the audio component also includes a speaker for outputting an audio signal.

[0152] In the above-mentioned apparatus, device, storage medium and computer program product embodiments of the present disclosure, a query log of a database is obtained; according to the query log, the fields and field information used in the table or partition corresponding to each query are extracted, and the fields and field information used as query conditions in each query are extracted; the query log is traversed, and candidate frequent item sets are identified according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree; the present disclosure selects target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets by identifying frequent item sets; the table or partition corresponding to the target frequent item set is adjusted to the same node of the distributed database; the table or partition with an associated relationship in the database is automatically searched, the search coverage is improved, the search efficiency is improved, the accuracy is improved, the labor cost is reduced, and the efficiency of updating the database structure is improved.

[0153] Those skilled in the art will appreciate that the embodiments of the present disclosure may be provided as methods, systems, or computer program products. Therefore, the present disclosure may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present disclosure may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0154] The present disclosure is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present disclosure. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0155] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0156] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

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

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

[0159] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0160] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0161] The above are only specific embodiments of the present disclosure, so that those skilled in the art can understand or implement the present disclosure. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure will not be limited to these embodiments herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A distributed database association table processing method, characterized in that: include: Get the query log of the database; According to the query log, extract the fields and field information used in the table or partition corresponding to each query, and extract the fields and field information used as query conditions in each query; Traversing the query log, identifying candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree; Selecting a target frequent item set having the same field information and the field information appearing as a query condition from the candidate frequent item sets; The table or partition corresponding to the target frequent itemset is adjusted to the same node of the distributed database.

2. The method according to claim 1, characterized in that The extracting, according to the query log, the fields and field information used in the table or partition corresponding to each query, and the extracting the fields and field information used as query conditions in each query, include: Read each SQL statement from the query log; Use regular expressions or SQL parsing libraries to parse each of the SQL statements to obtain the table or partition corresponding to each query; For each table or partition corresponding to the query, query the database metadata to obtain the fields and field information used in the table or partition corresponding to the query; A WHERE clause is extracted from each of the SQL statements, and the WHERE clause is parsed to obtain fields and field information used as query conditions in each query.

3. The method according to claim 1, characterized in that The traversing the query log, identifying candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree, includes: Create an empty hash tree; Traversing the query log, and generating a two-table combination for each query corresponding to the query log; Inserting the target two tables that meet the field matching condition into the hash tree; Generate a first multinomial set according to the target two-table combination; Selecting a second multi-item set that satisfies the field matching condition and uses the confirmation field as a query condition from the first multi-item set, and updating the occurrence frequency of the second multi-item set; A candidate frequent item set whose occurrence frequency is greater than or equal to a support threshold is selected from the second multinomial set.

4. The method according to claim 3, characterized in that The step of inserting the target two tables that meet the field matching condition into the hash tree comprises: Get the field information of the first table and the field information of the second table; Comparing the field information of the first table with the field information of the second table; In the case where the field information of the first table is the same as the field information of the second table, the first table and the second table are inserted into the hash tree.

5. The method according to claim 1, characterized in that: The step of selecting target frequent item sets with the same field information and the field information appearing as query conditions from the candidate frequent item sets includes: A new function is defined, which includes the following parameters: a known frequent item set list, a query record list, and a table field information dictionary; wherein the known frequent item set list stores the candidate frequent item sets; Initialize an empty list to store the filtered frequent item sets; Traverse each frequent item set in the known frequent item set list one by one; Use list comprehension to check whether every two tables in the current frequent itemset have the same fields; In the case where all tables in the current frequent itemset have the same fields, traverse the query record list and check each query; Extract the conditional fields in the current query; Check whether there is a field in the condition field of the current query that is the same as a field in the table of the current frequent itemset; If there is a field identical to the current frequent item set in the condition field of the current query, the current frequent item set is added to a new list until the target frequent item set is screened out.

6. The method according to claim 1, characterized in that After selecting the target frequent item sets with the same field information and the field information appearing as the query condition from the candidate frequent item sets, the method further includes: The target frequent itemsets are output in a structured manner.

7. A distributed database association table processing device, characterized in that: include: The acquisition module is used to obtain the query log of the database; An extraction module, used to extract, according to the query log, the fields and field information used in the table or partition corresponding to each query, and to extract the fields and field information used as query conditions in each query; An identification module, configured to traverse the query log and identify candidate frequent item sets according to the fields and field information used in the table or partition corresponding to each query, the fields and field information used as query conditions in each query, and the hash tree; A selection module, used to select target frequent item sets with the same field information and whose field information appears as a query condition from the candidate frequent item sets; The adjustment module is used to adjust the table or partition corresponding to the target frequent itemset to the same node of the distributed database.

8. An electronic device, characterized in that: include: processor; a memory for storing instructions executable by the processor; The processor is configured to execute the instructions to implement each step in the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, each step of the method according to any one of claims 1 to 6 is implemented.

10. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instructions are executed by a processor, the steps in the method according to any one of claims 1 to 6 are implemented.

Images