Method, apparatus, device and storage medium for establishing a database composite index

Abstract

The present invention relates to the field of artificial intelligence, and discloses a method, device, equipment and storage medium for establishing a database combined index. The method includes: obtaining historical query logs in the database, and parsing the historical query logs to obtain at least two field item sets; calculating the support degree of each field item set relative to all field item sets respectively, and screening out the field item sets with a support degree greater than a preset support degree threshold from all the field item sets; obtaining multiple groups of conditional fields arranged in order from all the field item sets according to the preset number of field combinations as field association units, and constructing corresponding confidence linkages according to each of the field association units; filtering the confidence linkages according to a preset confidence threshold, and establishing a combined index of the database according to the filtered confidence linkages and the screened field item sets. The combined index relationship of the data in the database is efficiently established.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence, and in particular, to a method, device, equipment and storage medium for establishing a database combined index. Background Art

[0002] With the development of Internet technology, data query is applied in more and more scenarios, and the requirement for the speed of data query is also getting higher and higher. For this, by adopting the method of database index, it is to assist the rapid query of data and update the data in the database table. The effect of the database index directly affects the performance of data query, and the difference in data query performance may be huge due to different degrees of configuration optimization. Due to the complexity of the database, if manual configuration is adopted, the workload and difficulty of database index optimization are large. How to improve the database query performance and reduce the index optimization time is the key problem to be solved in the database index optimization work.

[0003] Currently, the establishment of indexes mainly depends on the experience of developers. When implementing the functions of the business system, indexes are created while creating tables according to past development experience. As a result, it is not possible to well predict which interfaces will be frequently accessed and which structured query statements are hot statements, so as to establish good indexes; and due to the underlying implementation logic of the combined index, there are certain order rules for the fields of the index. Failing to perform data indexing according to the requirements will cause the index to fail and lead to full table queries, resulting in a decrease in the efficiency of data indexing. That is, the current method for establishing a database index has low efficiency. Summary of the Invention

[0004] The main object of the present invention is to solve the problem that the current method for establishing a database index has low efficiency.

[0005] In a first aspect of the present invention, a method for establishing a database combined index is provided. The method for establishing a database combined index includes: obtaining historical query logs in the database, and parsing the historical query logs to obtain at least two field item sets, where each field item set includes at least two conditional fields arranged in sequence; calculating the support degree of each field item set relative to all field item sets respectively, and screening out the field item sets with a support degree greater than a preset support degree threshold from all field item sets; obtaining multiple groups of conditional fields arranged in sequence from all field item sets according to a preset number of field combinations as field association units, and constructing corresponding confidence linkages according to each field association unit; filtering the confidence linkages according to a preset confidence threshold, and establishing a combined index of the database according to the filtered confidence linkages and the screened field item sets.

[0006] Optionally, in the first implementation manner of the first aspect of the present invention, parsing the historical query log to obtain at least two field item sets includes: identifying delimiters in the historical query log, and using the delimiters to split the historical query log into statements to obtain at least two query statements; and extracting field item sets in each query statement according to the statement structure of each query statement.

[0007] Optionally, in the second implementation manner of the first aspect of the present invention, calculating the support degree of each field item set relative to all field item sets respectively includes: respectively extracting conditional fields sorted in order in each field item set as field items; sequentially comparing each field item with each field item set, and determining, according to the comparison result, field item sets in each field item set that contain the same field item; respectively calculating the ratio between the number of field item sets corresponding to each field item and the number of all field item sets; and using the ratio calculated for each field item as the support degree of the corresponding field item set relative to all field item sets.

[0008] Optionally, in the third implementation manner of the first aspect of the present invention, constructing a corresponding confidence link according to each field association unit includes: sequentially using each field association unit to traverse each field item set, and determining, according to the traversal result, field item sets in each field item set that contain the field association unit; respectively calculating the ratio between the number of field item sets corresponding to each field association unit and the number of all field item sets; and performing permutation and combination on the ratios calculated for each field association unit to obtain a corresponding confidence link.

[0009] Optionally, in the fourth implementation manner of the first aspect of the present invention, the confidence threshold includes a first confidence threshold and a second confidence threshold. Filtering the confidence link according to a preset confidence threshold includes: sequentially traversing the confidence link using the first confidence threshold, and determining a first confidence that is lower than the first confidence threshold and first appears in the confidence link; extracting a segmented confidence link in the confidence link before the first confidence; sequentially traversing the segmented confidence link using the second confidence threshold, and determining a second confidence that is lower than the second confidence threshold in the segmented confidence link; and combining the determined second confidences in the traversal order to obtain a filtered confidence link.

[0010] Optionally, in the fifth implementation manner of the first aspect of the present invention, establishing a combined index of the database according to the filtered confidence link and the selected field item set includes: extracting each field item set in the filtered confidence link; according to each extracted field item set, selecting the field item sets that are the same as those in the selected field item set, and using the selected field item sets to establish a combined index of the database.

[0011] The second aspect of the present invention provides an apparatus for establishing a combined index of a database, including: a log parsing module, configured to obtain a historical query log in the database and parse the historical query log to obtain at least two field item sets, where each field item set includes at least two conditional fields arranged in sequence; a calculation module, configured to calculate the support degree of each field item set relative to all field item sets respectively, and filter out the field item sets with a support degree greater than a preset support degree threshold from all field item sets; a link construction module, configured to obtain multiple groups of conditional fields arranged in sequence as field association units from all field item sets according to a preset number of field combinations, and construct corresponding confidence links according to each field association unit; an index establishment module, configured to filter the confidence links according to a preset confidence threshold, and establish a combined index of the database according to the filtered confidence links and the selected field item sets.

[0012] Optionally, in the first implementation manner of the second aspect of the present invention, the log parsing module includes: a symbol recognition unit, configured to recognize delimiters in the historical query log and split the historical query log into at least two query statements by using the delimiters; an item set extraction unit, configured to extract field item sets in each query statement according to the statement structure of each query statement.

[0013] Optionally, in the second implementation manner of the second aspect of the present invention, the calculation module includes: a field extraction unit, configured to extract the conditional fields arranged in sequence in each field item set as field items respectively; a comparison unit, configured to compare each field item with each field item set in sequence, and determine the field item sets that contain the same field items in each field item set according to the comparison results; a ratio calculation unit, configured to calculate the ratio between the number of field item sets corresponding to each field item and the number of all field item sets respectively; a support degree calculation unit, configured to use the ratio calculated for each field item as the support degree of the corresponding field item set relative to all field item sets.

[0014] Optionally, in the third implementation manner of the second aspect of the present invention, the link construction module includes: a traversal unit, configured to sequentially traverse each of the field item sets by using each of the field association units, and determine, according to the traversal result, the field item sets that contain the field association units in each of the field item sets; a ratio calculation unit, configured to calculate the ratio between the number of the field item sets respectively determined by each of the field association units and the number of all the field item sets; and a permutation and combination unit, configured to perform permutation and combination on the ratios calculated by each of the field association units to obtain a corresponding confidence link.

[0015] Optionally, in the fourth implementation manner of the second aspect of the present invention, the index establishment module includes: a first traversal unit, configured to sequentially traverse the confidence link by using the first confidence threshold, and determine a first confidence that is lower than the first confidence threshold and first appears in the confidence link; a segmented extraction unit, configured to extract a segmented confidence link in the confidence link before the first confidence; a second traversal unit, configured to sequentially traverse the segmented confidence link by using the second confidence threshold, and determine a second confidence that is lower than the second confidence threshold in the segmented confidence link; and a combination unit, configured to combine the determined second confidences in the traversal order to obtain a filtered confidence link.

[0016] Optionally, in the fifth implementation manner of the second aspect of the present invention, the index establishment module further includes: an extraction unit, configured to extract each field item set in the filtered confidence link; and an index establishment unit, configured to select, according to the extracted each field item set, the field item sets that are the same as the selected field item sets, and establish a combined index of the database by using the selected field item sets.

[0017] The third aspect of the present invention provides a device for establishing a combined index of a database, including: a memory and at least one processor, where instructions are stored in the memory; and the at least one processor calls the instructions in the memory to enable the device for establishing a combined index of the database to execute each step of the above method for establishing a combined index of the database.

[0018] The fourth aspect of the present invention provides a computer-readable storage medium, where instructions are stored in the computer-readable storage medium, and when the instructions run on a computer, the computer is enabled to execute each step of the above method for establishing a combined index of the database.

[0019] In the technical solution provided by the present invention, historical query logs in a database are obtained, and the historical query logs are parsed to obtain at least two field item sets, where each field item set includes at least two conditional fields arranged in sequence; the support degree of each field item set relative to all field item sets is calculated respectively, and field item sets with a support degree greater than a preset support degree threshold are filtered out from all field item sets. Compared with the prior art, by calculating the support degree of each field item set relative to all field item sets and filtering the calculation results, the present application can obtain field item sets with a relatively high query frequency in the query field item sets, so as to be able to filter out field item sets with a relatively large degree of relationship.

[0020] According to the preset number of field combinations, multiple groups of conditional fields arranged in sequence are obtained from all field item sets as field association units, and corresponding confidence linkages are constructed according to each field association unit; according to a preset confidence threshold, the confidence linkages are filtered, and a combined index of the database is established according to the filtered confidence linkages and the filtered field item sets. Compared with the prior art, by establishing confidence chains for each field item set, and then using the filtered field item sets and confidence chains to determine field item data with a relatively large degree of association, and then establishing an index relationship, the present application realizes the efficient establishment of the combined index relationship of data in the database and improves the query efficiency of relevant data in the database. Brief Description of the Drawings

[0021] Figure 1 It is a schematic diagram of the first embodiment of the method for establishing a combined index of a database in the present invention;

[0022] Figure 2 It is a schematic diagram of the second embodiment of the method for establishing a combined index of a database in the present invention;

[0023] Figure 3 It is a schematic diagram of the third embodiment of the method for establishing a combined index of a database in the present invention;

[0024] Figure 4 It is a schematic diagram of the fourth embodiment of the method for establishing a combined index of a database in the present invention;

[0025] Figure 5 It is a schematic diagram of the fifth embodiment of the method for establishing a combined index of a database in the present invention;

[0026] Figure 6 It is a schematic diagram of an embodiment of the device for establishing a combined index of a database in the present invention;

[0027] Figure 7 It is a schematic diagram of another embodiment of the device for establishing a combined index of a database in the present invention;

[0028] Figure 8Schematic diagram of an embodiment of the device for establishing a database combined index in the present invention. Detailed implementation manners

[0029] The embodiments of the present invention provide a method, device, equipment and storage medium for establishing a database combined index. The method includes: obtaining historical query logs in the database, and parsing the historical query logs to obtain at least two field item sets; calculating the support degree of each field item set relative to all field item sets respectively, and screening out the field item sets with the support degree greater than a preset support degree threshold from all the field item sets; obtaining multiple groups of conditional fields arranged in order from all the field item sets according to the preset number of field combinations as field association units, and constructing corresponding confidence links according to each field association unit; filtering the confidence links according to a preset confidence threshold, and establishing a combined index of the database according to the filtered confidence links and the screened field item sets. The combined index relationship of the data in the database is efficiently established.

[0030] Terms such as "first", "second", "third", "fourth", etc. (if any) in the specification, claims and above-mentioned drawings of the present invention are used to distinguish similar objects, and do not have to be used to describe a specific order or sequence. It should be understood that such data can be interchanged under appropriate circumstances so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "comprising" or "having" and any variation thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product or equipment that includes a series of steps or units does not necessarily have to be limited to those steps or units clearly listed, but may include other steps or units not clearly listed or inherent to these processes, methods, products or equipment.

[0031] For the convenience of understanding, the specific process of the embodiments of the present invention will be described below. Please refer to Figure 1 The first embodiment of the method for establishing a database combined index in the embodiments of the present invention includes:

[0032] 101. Obtain historical query logs in the database, and parse the historical query logs to obtain at least two field item sets, where each field item set includes at least two conditional fields arranged in order;

[0033] It can be understood that the execution subject of the present invention can be a device for establishing a database combined index, or a terminal or a server. Specifically, no limitation is made here. The embodiments of the present invention will be described by taking the server as the execution subject as an example.

[0034] The embodiments of the present application can acquire and process relevant data based on artificial intelligence technology. Among them, artificial intelligence (AI) is the theory, method, technology and application system that uses digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0035] Artificial intelligence basic technologies generally include technologies such as sensors, dedicated artificial intelligence chips, cloud computing, distributed storage, big data processing technology, operation / interaction systems, and mechatronics. Artificial intelligence software technologies mainly include several major directions such as computer vision technology, robotics, biometric technology, speech processing technology, natural language processing technology, and machine learning / deep learning.

[0036] In this embodiment, the database here refers to a collection of a large amount of data that is long-term stored in a computer, organized, shareable, and uniformly managed. Here, mysql (relational database management system) is taken as an example for illustration; the historical query log here refers to the query records of users on the data in the mysql database; the field item set here refers to the set of field items in the queried data.

[0037] In practical applications, by retrieving relevant query log data in the mysql database, where the retrieved query log can be set to query log data within a certain time range (for example, a 3-day time range can be set), so as to obtain the historical query log within a certain time range in the database; then parse the obtained historical query log, by identifying the delimiter in the historical query log, and using the above delimiter to split the historical query log into statements, obtaining at least two query statements; thus, according to the statement structure of each query statement, extract the field item set in each query statement. Among them, each field item set contains at least two conditional fields arranged in sequence. By obtaining the relevant historical query log in the database within a certain time, and then through analysis, the relevant field item set queried by users within a certain time period can be obtained, realizing the accurate acquisition of relevant processed data.

[0038] 102. Calculate the support degree of each field item set relative to the entire field item set respectively, and screen out the field item sets with a support degree greater than the preset support degree threshold from the entire field item set;

[0039] In this embodiment, the support here refers to the probability that the support representation field item set 1 (such as AB) and the field item set 2 (such as ABC) appear simultaneously, which is the probability that field A and field B appear simultaneously here; the support threshold here refers to the probability that the common index fields appear by analyzing historical index fields using big data, and the support threshold is obtained by adjusting the calculation. By calculating the corresponding support for each field item set, and then using the support threshold to screen the support of each field item set obtained by calculation, it is possible to screen the field item sets with more query frequencies, so as to screen the field item sets queried by a large number of users and provide a data basis for the corresponding index relationship.

[0040] In practical applications, according to the field item sets obtained by the above processing, the conditional fields arranged in sequence in each field item set are respectively extracted as field items, and then the above-extracted field items are used in turn to compare each field item set, and according to the comparison results, the field item sets containing the same field items in each of the above field item sets are determined; then the ratios between the numbers of the field item sets corresponding to each of the above field items and the number of all field item sets are respectively calculated, so that the ratios calculated for each of the above field items are used as the support of the corresponding field item set relative to all field item sets.

[0041] 103. Obtain multiple groups of conditional fields arranged in sequence from all field item sets according to the preset number of field combinations as field association units, and construct corresponding confidence links according to each field association unit;

[0042] In this embodiment, the number of field combinations here refers to the permutation and combination of all field items provided for user queries according to the preset data query field items in the database, so as to obtain the corresponding number of field combinations of data; the confidence link here refers to using the field association unit to perform permutation and combination according to the above field combinations, and calculating the probability that adjacent fields appear simultaneously as the confidence of the field connection link, so as to construct the confidence link corresponding to each field association unit. By analyzing each field item set using the preset number of field combinations and constructing the corresponding confidence chain using the analysis result field association unit, it is possible to obtain the confidence of the connection relationship between each field item in the field item set, so as to obtain the field items with a larger link relationship between the connected field items, and thus it is possible to screen the connected field items with the corresponding confidence and perform preliminary index binding.

[0043] In practical applications, according to the preset number of field combination, field items are analyzed and processed from each field item set, so as to obtain multiple sets of conditional fields arranged in sequence as field association units; then each of the above field association units is used to traverse each of the above field item sets in turn, and according to the traversal results, the field item sets containing the field association units in each of the above field item sets are determined; the ratios between the quantities of the field item sets corresponding to each of the above field association units and the quantity of all field item sets are calculated respectively; the ratios calculated for each of the above field association units are arranged and combined to obtain the confidence link corresponding to the field items in each field item set.

[0044] 104. Filter the confidence link according to the preset confidence threshold, and establish a combined index of the database based on the filtered confidence link and the selected field item set.

[0045] In this embodiment, the confidence threshold here refers to the probability of the frequently queried link fields obtained by analyzing the historical link fields using big data. The support threshold is obtained by adjustment. The confidence threshold includes the first confidence threshold and the second confidence threshold; by filtering the processed confidence link, then using the filtered confidence chain and the selected field item set, the field items that exist simultaneously are selected, and a corresponding combined index is established according to the chain, so as to realize the establishment of a corresponding combined index for the frequently queried field item set, thereby accelerating the query speed of users for relevant data.

[0046] In practical applications, traverse the above confidence link in the order of the above first confidence threshold, and determine the first confidence lower than the above first confidence threshold that first appears in the above confidence link; extract the segmented confidence link in the above confidence link before the above first confidence; traverse the above segmented confidence link in the order of the above second confidence threshold, and determine the second confidence lower than the above second confidence threshold in the above segmented confidence link; combine the determined second confidence in the traversal order to obtain the filtered confidence link. Extract each field item set in the filtered confidence link; according to each extracted field item set, select the field item sets that are the same as those in the selected field item set, and use the selected field item sets to establish a combined index of the database.

[0047] In an embodiment of the present invention, historical query logs in a database are obtained and parsed to obtain at least two field item sets, where each field item set contains at least two conditional fields arranged in sequence; the support degree of each field item set relative to all field item sets is calculated respectively, and field item sets with a support degree greater than a preset support degree threshold are screened out from all field item sets. Compared with the prior art, in this application, by calculating the support degree of each field item set relative to all field item sets and screening the calculation results, field item sets with a higher query frequency in the query field item set can be obtained, so that field item sets with a greater degree of relationship can be screened out.

[0048] According to the preset number of field combinations, multiple groups of conditional fields arranged in sequence are obtained from all field item sets as field association units, and corresponding confidence linkages are constructed according to each field association unit; according to a preset confidence threshold, the confidence linkages are filtered, and a combined index of the database is established based on the filtered confidence linkages and the screened field item sets. Compared with the prior art, in this application, by establishing confidence chains for each field item set, and then using the screened field item sets and confidence chains to determine field item data with a greater association relationship, and then establishing an index relationship, the combined index relationship of the data in the database is efficiently established, and the query efficiency of the relevant data in the database is improved.

[0049] Please refer to Figure 2 , the second embodiment of the method for establishing a combined index of the database in the embodiment of the present invention includes:

[0050] 201. Identify the delimiter in the historical query log, and use the delimiter to split the historical query log into at least two query statements;

[0051] In this embodiment, the delimiter here refers to splitting the corresponding statement by setting a corresponding delimiter, and analyzing the historical query statement by using the preset delimiter, so that the corresponding query statement can be obtained and the required field item set can be obtained.

[0052] In practical applications, by setting a certain time range as the query time period, relevant query log data in the database mysql is retrieved, so as to obtain the historical query log within a certain time range in the database; then the delimiter in the above historical query log is identified, and the character with the highest occurrence frequency is selected as the field delimiter of the sample log. Specifically, the specific algorithm for obtaining the field delimiter is: after excluding letters, numbers, and special characters (characters that are generally not used as field delimiters, such as ^, etc.), the preset delimiter character is taken, and the above historical query log is split into at least two query statements by using this delimiter;

[0053] 202. Extract the field item sets in each query statement according to the statement structure of each query statement;

[0054] In this embodiment, the statement structure here refers to a pre-defined standardized query statement for database data. The field item set includes the field items corresponding to the data to be queried by the query statement. By analyzing the statement structure in historical query statements, the corresponding field item set can be obtained, and thus the query field item set of the user in the corresponding time period can be obtained, laying a data foundation for establishing the index relationship of the corresponding fields.

[0055] In practical applications, the field item sets in each query statement are extracted according to the statement structure of the above query statement. Specifically, by counting the field item sets within a preset time period: assume that the query field table table provided by the system has query field items {A, B, C, D, E, F, G}. Data statistics are performed within the preset time period. For example, if the preset time is 3 days and the data is summarized on the evening of the 3rd day, a total of 5 relevant select statement query fields (and they need to be arranged in order, where A = xx and B = yy and where B = xx and A = yy are different, which are {A, B} and {B, A} respectively) are collected. One select statement is a transaction, so there are 5 transactions at this time: namely item_ab = {A, B}, item_abc = {A, B, C}, item_abd = {A, B, D}, item_adcef = {A, D, B, E, F}, item_gbca = {G, B, C, A}, etc., thus obtaining 5 field item sets. Among them, each of the above field item sets contains at least two conditional fields arranged in order;

[0056] 203. Calculate the support degree of each field item set relative to the entire field item set respectively, and screen out the field item sets with a support degree greater than the preset support degree threshold from the entire field item set;

[0057] 204. Obtain multiple groups of conditional fields arranged in order from the entire field item set as field association units according to the preset number of field combinations, and construct the corresponding confidence link according to each field association unit;

[0058] 205. Filter the confidence link according to the preset confidence threshold, and establish a combined index of the database according to the filtered confidence link and the screened field item sets.

[0059] In an embodiment of the present invention, a delimiter in a historical query log is recognized, and the historical query log is split into statements by using the delimiter to obtain at least two query statements; according to the statement structures of the respective query statements, a set of field items in each query statement is extracted. Compared with the prior art, in this application, query statements are obtained by performing delimiter processing on historical query statements, and then analyzed and processed by using a preset statement structure, so as to obtain a set of field items queried by a user. It can not only analyze the query data of the corresponding database relatively simply, but also collect a set of field items for establishing an index association relationship, laying a data foundation for analyzing the relationship between field items.

[0060] Please refer to Figure 3 , the third embodiment of the method for establishing a database combined index in an embodiment of the present invention includes:

[0061] 301. Obtain a historical query log in a database, and parse the historical query log to obtain at least two sets of field items, where each set of field items includes at least two conditional fields arranged in sequence;

[0062] 302. Respectively extract the conditional fields arranged in sequence in each set of field items and use them as field items;

[0063] In this embodiment, the conditional fields arranged in sequence here refer to the result of the permutation and combination of the search field conditions provided by the system to the user. For example, if the set of search field items provided by the system to the user is {A, B, C}, then the field items arranged in sequence are {A, B}, {B, C}, {A, C}, {B, A}, {C, B}, {C, A}.

[0064] In practical applications, according to the sets of field items obtained by the above processing, the conditional fields arranged in sequence in each set of field items are respectively extracted as the required field items.

[0065] 303. Compare each field item with each set of field items in turn, and determine the sets of field items that contain the same field item in each set of field items according to the comparison results;

[0066] In this embodiment, according to the field items obtained by processing, each field item is successively used to compare with the set of field items obtained by the above historical log query processing. Then, according to the comparison results, it is determined whether there is a set of field items containing the same field items in each set of field items. For example, here, taking the field items {A, B} as an example, there are 5 sets of field items obtained by the above processing at this time: namely, item_ab = {A, B}, item_abc = {A, B, C}, item_abd = {A, B, D}, item_adcef = {A, D, B, E, F}, item_gbca = {G, B, C, A}, etc., a total of 5 field items are obtained. At this time, comparing the set of field items in which field A and field B appear simultaneously, the field {A, B} appears in transaction 1, transaction 2, and transaction 3 respectively (it should be noted that transaction 4 contains A and B, but there is D in the middle, so it does not count; although transaction 5 contains A and B, it does not count because the order does not conform). Then, according to the comparison results, it is determined that the sets of field items containing the same field items in each set of field items are transaction 1, transaction 2, and transaction 3.

[0067] 304. Calculate the ratio between the number of the set of field items corresponding to each field item and the number of all sets of field items respectively;

[0068] In this embodiment, according to the above comparison and determination results, calculate the ratio between the number of the set of field items corresponding to each of the above field items and the number of all sets of field items respectively. As the above comparison and determination results, the sets of field items containing the same field items for the field items {A, B} are transaction 1, transaction 2, and transaction 3. Therefore, the ratio between the number of the set of field items corresponding to the field items {A, B} and the number of all sets of field items can be obtained as 3:5 = 60%.

[0069] 305. Take the ratio calculated for each field item as the support degree of the corresponding set of field items relative to all sets of field items;

[0070] In this embodiment, according to the results of the above processing, take the ratio calculated for each of the field items as the support degree of the corresponding set of field items relative to all sets of field items. The support degree of the above field items {A, B} can be 3 / 5 = 60%. We calculate the support degree of each item set, let it be the support degree of the field items {A, B} support1 = 60%, the support degree of the field items {B, C} support2 = 20%, the support degree of the field items {C, D} support3 = 20%, etc. And screen out the set of field items with a support degree greater than the preset support degree threshold from all sets of field items; assume that 20% is the threshold (the threshold can be adjusted manually), and define the items above 20% as frequent item sets.

[0071] 306. Obtain multiple groups of conditional fields arranged in order from all field item sets according to the preset number of field combinations as field association units, and construct corresponding confidence links based on each field association unit.

[0072] 307. Filter the confidence links according to the preset confidence threshold, and establish a combined index for the database based on the filtered confidence links and the selected field item sets.

[0073] In the embodiments of the present invention, the conditional fields arranged in order in each field item set are extracted separately and used as field items; each field item is compared with each field item set in turn, and according to the comparison results, the field item sets containing the same field items in each field item set are determined; the ratios between the quantities of the field item sets corresponding to each field item and the quantity of all field item sets are calculated respectively; the ratios calculated for each field item are used as the support degrees of the corresponding field item sets relative to all field item sets. Compared with the prior art, the present application calculates the corresponding support degrees for the field item sets using the field items arranged in order. Through the analysis of the support degrees, it can be obtained which field items have a higher query frequency, and the higher-frequency field items are initially screened, thereby realizing the establishment of a better index relationship for the corresponding field items.

[0074] Please refer to Figure 4 , the fourth embodiment of the method for establishing a combined index for the database in the embodiments of the present invention includes:

[0075] 401. Obtain the historical query logs in the database, and parse the historical query logs to obtain at least two field item sets, where each field item set contains at least two conditional fields arranged in order.

[0076] 402. Calculate the support degrees of each field item set relative to all field item sets respectively, and screen out the field item sets with support degrees greater than the preset support degree threshold from all field item sets.

[0077] 403. Traverse each field item set with each field association unit in turn, and according to the traversal results, determine the field item sets containing the field association unit in each field item set.

[0078] In this embodiment, the field association unit here refers to the field association unit obtained by arranging and combining the query fields provided by the system.

[0079] In practical applications, according to the results of the above analysis of the historical query logs, multiple groups of conditional fields arranged in order are obtained from all field item sets according to the preset number of field combinations as field association units, and each field item set is traversed with each field association unit in turn. Then, according to the traversal results, the field item sets containing the field association unit in each field item set are determined.

[0080] 404. Calculate the ratio between the number of field item sets determined for each field association unit and the number of all field item sets respectively;

[0081] In this embodiment, according to the above determination results, calculate the ratio between the number of field item sets determined for each field association unit and the number of all field item sets respectively. For example, confidence represents the probability that event 2 occurs when event 1 occurs. Taking the item set {A, B} as an example, it is the probability that field B appears when field A appears. The support count of {A, B} is 3, the support count of {A} is 5, and the confidence is: 3 / 5 = 60%. By analogy, the confidence of {B, C} is: 2 / 5 = 40%.

[0082] 405. Perform permutation and combination on the ratios calculated for each field association unit to obtain the corresponding confidence link;

[0083] In this embodiment, according to the above calculation results, perform permutation and combination on the ratios calculated for each field association unit, so as to obtain the corresponding confidence link. For example, in the above calculation results, the support count of {A, B} is 3, the support count of {A} is 5, and the confidence is: 3 / 5 = 60%. By analogy, the confidence of {B, C} is: 2 / 5 = 40%, thus constructing a confidence link [60%, 40%, 0%, 0%, 100%, 0%] of the above table.

[0084] 406. Filter the confidence link according to a preset confidence threshold, and establish a combined index of the database based on the filtered confidence link and the selected field item sets.

[0085] In the embodiment of the present invention, each field association unit is used to traverse each field item set in turn, and according to the traversal results, determine the field item sets in each field item set that contain the field association unit; calculate the ratio between the number of field item sets determined for each field association unit and the number of all field item sets respectively; perform permutation and combination on the ratios calculated for each field association unit to obtain the corresponding confidence link. Compared with the prior art, this application calculates the ratio of the corresponding field association unit for the obtained field item sets, and then establishes a confidence chain according to the ratio, so as to analyze the association and degree of association between field items, and better establish the index relationship between field items with an association relationship.

[0086] Please refer to Figure 5 , the fifth embodiment of the method for establishing a combined index of the database in the embodiment of the present invention includes:

[0087] 501. Obtain the historical query logs in the database, and parse the historical query logs to obtain at least two field item sets, where each field item set contains at least two conditional fields arranged in sequence;

[0088] 502. Calculate the support of each field item set relative to the entire field item set respectively, and filter out the field item sets with a support greater than the preset support threshold from the entire field item set;

[0089] 503. According to the preset number of field combinations, obtain multiple groups of conditional fields arranged in order from the entire field item set as field association units, and construct corresponding confidence links based on each field association unit;

[0090] 504. Traverse the confidence link in order with the first confidence threshold, and determine the first confidence that is lower than the first confidence threshold and appears for the first time in the confidence link;

[0091] In this embodiment, according to the confidence link obtained from the above processing, traverse the above confidence link in order with the preset first confidence threshold, and determine the first confidence that is lower than the first confidence threshold and appears for the first time in the confidence link. For example, if the preset first confidence threshold is 1%, then traverse the confidence link [60%, 40%, 0%, 0%, 100%, 0%] of the above table, which means that when A appears, the probability of B appearing is 60%, when B appears, the probability of C appearing is 40%, and when C appears, the probability of D appearing is 0%. At this time, we set the threshold to 40%, then filter out the fields {A, B}, and do not include the probability of 100% that F appears when E appears. Because when D appears, the probability of field E appearing is 0%, and the probability of the simultaneous appearance of field E and field F has no statistical significance, it can be known that the third field item {C} with a confidence lower than the first confidence threshold.

[0092] 505. Extract the segmented confidence link before the first confidence in the confidence link;

[0093] In this embodiment, according to the first confidence determined above, extract the segmented confidence link before the first confidence in the above confidence link. For example, if the first confidence corresponds to 0% in the third position, then extract the previous segmented confidence link to get {60%, 40%}.

[0094] 506. Traverse the segmented confidence link in order with the second confidence threshold, and determine the second confidence that is lower than the second confidence threshold in the segmented confidence link;

[0095] In this embodiment, according to the segmented confidence link obtained from the above processing, traverse the segmented confidence link in order with the second confidence threshold, and then determine the second confidence that is lower than the second confidence threshold in the segmented confidence link. For example, if the second confidence threshold is 60%, then traverse the segmented confidence link {60%, 40%}, and it can be determined that the one lower than the second confidence threshold is 40%.

[0096] 507. Combine the determined second confidence levels in the traversal order to obtain a filtered confidence link;

[0097] In this embodiment, according to the result of the above processing, combine the determined second confidence levels in the traversal order to obtain a filtered confidence link. For example, if the result of the above processing is {60%} and there is only one confidence level left, the filtered confidence link is {60%}.

[0098] 508. Extract each field item set in the filtered confidence link;

[0099] In this embodiment, according to the above filtered confidence link, extract each field item set in the filtered confidence link. For example, if the above filtered confidence link is {60%}, the extracted field item set is {A, B}.

[0100] 509. According to each extracted field item set, select the field item sets that are the same as the filtered field item sets, and use the selected field item sets to establish a combined index for the database.

[0101] In this embodiment, according to the above extraction result, select the field item sets that have the same field items as the above filtered field item sets, and then establish a combined index for the field item sets according to the selection result of the field item sets. For example, when counting the situations of each item set, the high-frequency item set collection is [{A, B}], and the field set filtered by confidence level is {A, B}. Combining the results of both, both field A and field B exist, so a combined index for the database is established for A and B.

[0102] In the embodiment of the present invention, the confidence link is traversed in the order of the first confidence threshold, and the first confidence level that is lower than the first confidence threshold and first appears in the confidence link is determined; the segmented confidence link before the first confidence level in the confidence link is extracted; the segmented confidence link is traversed in the order of the second confidence threshold, and the second confidence level that is lower than the second confidence threshold in the segmented confidence link is determined; the determined second confidence levels are combined in the traversal order to obtain a filtered confidence link; each field item set in the filtered confidence link is extracted; according to each extracted field item set, the field item sets that are the same as the filtered field item sets are selected, and a combined index for the database is established using the selected field item sets. Compared with the prior art, by using the analyzed confidence link and the filtered field item sets, this application selects and establishes index fields, can establish a combined index for the database for the relevant field items that users often query, and can speed up the acquisition of relevant data in the database.

[0103] The method for establishing a database combined index in the embodiments of the present invention has been described above. Next, the device for establishing a database combined index in the embodiments of the present invention will be described. Please refer to Figure 6 An embodiment of the device for establishing a database combined index in the embodiments of the present invention includes:

[0104] A log parsing module 601, configured to obtain historical query logs in the database and parse the historical query logs to obtain at least two field item sets, where each of the field item sets includes at least two conditional fields arranged in sequence;

[0105] A calculation module 602, configured to calculate the support degree of each field item set relative to all field item sets respectively, and screen out the field item sets with a support degree greater than a preset support degree threshold from all the field item sets;

[0106] A link construction module 603, configured to obtain multiple groups of conditional fields arranged in sequence from all the field item sets as field association units according to a preset number of field combinations, and construct corresponding confidence links according to each of the field association units;

[0107] An index establishment module 604, configured to filter the confidence links according to a preset confidence threshold, and establish a combined index of the database according to the filtered confidence links and the screened field item sets.

[0108] In the embodiments of the present invention, historical query logs in the database are obtained and the historical query logs are parsed to obtain at least two field item sets, where each of the field item sets includes at least two conditional fields arranged in sequence; the support degree of each field item set relative to all field item sets is calculated respectively, and the field item sets with a support degree greater than a preset support degree threshold are screened out from all the field item sets. Compared with the prior art, by calculating the support degree of each field item set relative to all field item sets and screening the calculation results, the present application can obtain the field item sets with a higher query frequency in the query field item sets, so as to screen out the field item sets with a greater degree of relationship.

[0109] Multiple groups of conditional fields arranged in sequence are obtained from all the field item sets as field association units according to a preset number of field combinations, and corresponding confidence links are constructed according to each of the field association units; the confidence links are filtered according to a preset confidence threshold, and a combined index of the database is established according to the filtered confidence links and the screened field item sets. Compared with the prior art, by establishing confidence chains for each field item set, and then using the screened field item sets and confidence chains to determine the field item data with a greater degree of association, and then establishing an index relationship, the present application realizes the efficient establishment of the combined index relationship of the data in the database and improves the query efficiency of the relevant data in the database.

[0110] Please refer to Figure 7 , another embodiment of the database combined index establishment device in the embodiments of the present invention includes:

[0111] A log parsing module 601, configured to obtain historical query logs in the database and parse the historical query logs to obtain at least two field item sets, where each of the field item sets includes at least two conditional fields arranged in sequence;

[0112] A calculation module 602, configured to calculate the support degree of each field item set relative to all field item sets respectively, and screen out the field item sets with a support degree greater than a preset support degree threshold from all the field item sets;

[0113] A link construction module 603, configured to obtain multiple groups of conditional fields arranged in sequence as field association units from all the field item sets according to a preset number of field combinations, and construct corresponding confidence links according to each of the field association units;

[0114] An index establishment module 604, configured to filter the confidence links according to a preset confidence threshold, and establish a combined index of the database according to the filtered confidence links and the screened field item sets.

[0115] Further, the log parsing module 601 includes:

[0116] A symbol recognition unit 6011, configured to recognize delimiters in the historical query logs, and split the historical query logs using the delimiters to obtain at least two query statements;

[0117] An item set extraction unit 6012, configured to extract field item sets in each of the query statements according to the statement structures of the query statements.

[0118] Further, the calculation module 602 includes:

[0119] A field extraction unit 6021, configured to extract the conditional fields arranged in sequence in each of the field item sets as field items;

[0120] A comparison unit 6022, configured to compare each of the field items with each of the field item sets in sequence, and determine the field item sets that contain the same field items in each of the field item sets according to the comparison results;

[0121] A ratio calculation unit 6023, configured to calculate the ratio between the number of the field item sets corresponding to each of the field items and the number of all the field item sets respectively;

[0122] The support degree calculation unit 6024 is configured to use the ratio calculated for each of the field items as the support degree of the corresponding field item set relative to the entire field item set.

[0123] Further, the link construction module 603 includes:

[0124] The traversal unit 6031 is configured to sequentially traverse each of the field item sets by using each of the field association units, and determine, according to the traversal result, the field item sets in each of the field item sets that contain the field association unit;

[0125] The ratio calculation unit 6032 is configured to calculate the ratio between the number of the field item sets respectively determined by each of the field association units and the number of the entire field item sets;

[0126] The permutation and combination unit 6033 is configured to perform permutation and combination on the ratios calculated by each of the field association units to obtain the corresponding confidence degree link.

[0127] Further, the index establishment module 604 includes:

[0128] The first traversal unit 6041 is configured to sequentially traverse the confidence degree link by using the first confidence degree threshold, and determine the first confidence degree that first appears and is lower than the first confidence degree threshold in the confidence degree link;

[0129] The segmented extraction unit 6042 is configured to extract the segmented confidence degree link before the first confidence degree in the confidence degree link;

[0130] The second traversal unit 6043 is configured to sequentially traverse the segmented confidence degree link by using the second confidence degree threshold, and determine the second confidence degree that is lower than the second confidence degree threshold in the segmented confidence degree link;

[0131] The combination unit 6044 is configured to combine the determined second confidence degrees in the traversal order to obtain the filtered confidence degree link.

[0132] Further, the index establishment module 604 further includes:

[0133] The extraction unit 6045 is configured to extract each field item set in the filtered confidence degree link;

[0134] The index establishment unit 6046 is configured to select the field item sets that are the same as the selected field item sets according to the extracted each field item set, and establish a combined index of the database by using the selected field item sets.

[0135] In the embodiments of the present invention, through the statistical analysis of the historical query logs of the database, screening the obtained field item sets and establishing confidence links, and then using the confidence links and the screening results to establish a combined index for the corresponding fields, so as to achieve the optimal query effect of the entire system.

[0136] Above Figure 6 And Figure 7 The apparatus for establishing a database combined index in the embodiments of the present invention is described in detail from the perspective of modular functional entities. Next, the device for establishing a database combined index in the embodiments of the present invention is described in detail from the perspective of hardware processing.

[0137] Figure 8 FIG. is a schematic structural diagram of a device for establishing a database combined index provided by an embodiment of the present invention. The device 800 for establishing a database combined index may vary greatly due to configuration or performance, and may include one or more processors (central processing units, CPUs) 810 (for example, one or more processors) and a memory 820, and one or more storage media 830 for storing application programs 833 or data 832 (for example, one or more mass storage devices). Among them, the memory 820 and the storage media 830 may be transient storage or persistent storage. The program stored in the storage media 830 may include one or more modules (not shown in the figure), and each module may include a series of instruction operations on the device 800 for establishing a database combined index. Further, the processor 810 may be configured to communicate with the storage media 830 and execute a series of instruction operations in the storage media 830 on the device 800 for establishing a database combined index.

[0138] The device 800 for establishing a database combined index may further include one or more power supplies 840, one or more wired or wireless network interfaces 850, one or more input / output interfaces 860, and / or one or more operating systems 831, such as Windows Serve, Mac OS X, Unix, Linux, FreeBSD, etc. Those skilled in the art can understand that Figure 8 The shown structural diagram of the device for establishing a database combined index does not limit the device for establishing a database combined index, and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0139] The present invention also provides a device for establishing a database combined index. The computer device includes a memory and a processor. When the computer-readable instructions stored in the memory are executed by the processor, the processor executes each step of the method for establishing a database combined index in the above embodiments.

[0140] The present invention also provides a computer-readable storage medium, which may be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium. Instructions are stored in the computer-readable storage medium. When the instructions are run on a computer, the computer is caused to execute the steps of the method for establishing a database combined index.

[0141] Those skilled in the art can clearly understand that for the convenience and conciseness of description, the specific working processes of the above-described systems, devices, and units can refer to the corresponding processes in the foregoing method embodiments and will not be elaborated herein.

[0142] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The foregoing storage medium includes: various media such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disc that can store program codes.

[0143] This application can be used in numerous general-purpose or special-purpose computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and so on. This application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. This application can also be practiced in a distributed computing environment where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0144] As described above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements on some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims

1. A method for establishing a database composite index, characterized in that, The method for establishing a database combined index includes: Obtain historical query logs in the database, and parse the historical query logs to obtain at least two field item sets, where each of the field item sets includes at least two conditional fields arranged in sequence; Calculate the support degree of each field item set relative to all field item sets respectively, and screen out the field item sets with a support degree greater than a preset support degree threshold from all the field item sets; According to the preset number of field combinations, obtain multiple groups of conditional fields arranged in sequence from all the field item sets as field association units, and construct corresponding confidence links according to each of the field association units; Traverse the confidence link in sequence using a first confidence threshold, and determine the first confidence that first appears below the first confidence threshold in the confidence link; Extract the segmented confidence link in the confidence link before the first confidence; Traverse the segmented confidence link in sequence using a second confidence threshold, and determine the second confidence that is below the second confidence threshold in the segmented confidence link; Combine the determined second confidences in the traversal order to obtain a filtered confidence link; Establish a combined index of the database according to the filtered confidence link and the screened field item sets.

2. The method for establishing a database combined index according to claim 1, wherein The parsing of the historical query logs to obtain at least two field item sets includes: Identify the delimiter in the historical query logs, and use the delimiter to split the historical query logs into statements to obtain at least two query statements; Extract the field item sets in each of the query statements according to the statement structure of each of the query statements.

3. The method for establishing a database combined index according to claim 1, characterized in that, The calculating the support degree of each field item set relative to all field item sets respectively includes: Extract the conditional fields arranged in sequence in each of the field item sets respectively and use them as field items; Compare each of the field items with each of the field item sets in turn, and determine the field item sets that contain the same field item in each of the field item sets according to the comparison results; Calculate the ratio between the number of the field item sets corresponding to each of the field items and the number of all field item sets respectively; Use the ratios calculated for each of the field items as the support degree of the corresponding field item set relative to all field item sets.

4. The method for establishing a database combined index according to claim 1, characterized in that, The constructing the corresponding confidence link according to each of the field association units includes: Traverse each of the field item sets in turn using each of the field association units, and determine the field item sets that contain the field association unit in each of the field item sets according to the traversal results; Calculate the ratio between the number of the field item sets corresponding to each of the field association units and the number of all field item sets respectively; Arrange and combine the ratios calculated for each of the field association units to obtain the corresponding confidence link.

5. The method for establishing a database combined index according to claim 1, wherein The establishing a combined index of the database according to the filtered confidence link and the screened field item sets includes: Extract each of the field item sets in the filtered confidence link; According to the extracted each of the field item sets, select the field item sets that are the same as the screened field item sets, and use the selected field item sets to establish a combined index of the database.

6. An apparatus for establishing a database composite index, characterized in that, The device for establishing a database combined index includes: A log parsing module, configured to obtain historical query logs in a database and parse the historical query logs to obtain at least two field item sets, where each of the field item sets includes at least two conditional fields arranged in sequence; A calculation module, configured to calculate the support degree of each field item set relative to all field item sets respectively, and filter out the field item sets with a support degree greater than a preset support degree threshold from all the field item sets; A link construction module, configured to obtain multiple groups of conditional fields arranged in sequence as field association units from all the field item sets according to a preset number of field combinations, and construct corresponding confidence links according to each of the field association units; An index establishment module, configured to sequentially traverse the confidence link using a first confidence threshold, and determine a first confidence that is lower than the first confidence threshold and appears for the first time in the confidence link; extract a segmented confidence link in the confidence link before the first confidence; sequentially traverse the segmented confidence link using a second confidence threshold, and determine a second confidence that is lower than the second confidence threshold in the segmented confidence link; combine the determined second confidences in the traversal order to obtain a filtered confidence link; establish a combined index of the database according to the filtered confidence link and the filtered-out field item sets.

7. The apparatus for establishing a database combined index according to claim 6, wherein The calculation module includes: A field extraction unit, configured to extract the conditional fields arranged in sequence in each of the field item sets respectively as field items; A comparison unit, configured to sequentially compare each of the field items with each of the field item sets, and determine the field item sets that contain the same field item according to the comparison results; A ratio calculation unit, configured to calculate the ratio between the number of the field item sets corresponding to each of the field items and the number of all field item sets respectively; A support degree calculation unit, configured to use the ratios calculated for each of the field items as the support degree of the corresponding field item set relative to all field item sets.

8. An apparatus for establishing a database composite index, characterized in that, The device for establishing the database combined index includes: a memory and at least one processor, and instructions are stored in the memory; The at least one processor calls the instructions in the memory, so that the device for establishing the database combined index executes each step of the method for establishing the database combined index according to any one of claims 1-5.

9. A computer-readable storage medium having instructions stored thereon, characterized in that, When the instructions are executed by the processor, each step of the method for establishing the database combined index according to any one of claims 1-5 is implemented.

Images