Index recommendation method and device based on histogram statistics, equipment and medium
Through the index recommendation method based on histogram statistics, the problem of slow and inaccurate index recommendation in large data scenarios is solved, fast and accurate index recommendation is achieved, and the performance of the database system is improved.
Patent Information
- Application Number
- CN202411877954.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-19
- Publication Date
- 2025-03-28
AI Technical Summary
In the large data scenario, the existing database index recommendation method has the problem of slow and inaccurate index recommendation, which leads to high resource pressure and affects system performance.
The index recommendation method based on histogram statistics is adopted, and by receiving and parsing SQL query statements, identifying the target table, determining the order of table connection, generating and resorting the histogram, determining the index order, and searching in the target table to obtain the index recommendation results.
It improves data retrieval performance, shortens SQL execution time, realizes fast and accurate indexing recommendations in large data scenarios, and reduces resource consumption.
Smart Images
Figure CN119719157A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to an index recommendation method, device, equipment and medium based on histogram statistics. Background Art
[0002] In the era of big data, the amount of data that database systems need to process has increased dramatically. If complex SQL does not have a suitable index, it will lead to poor execution plans, slow SQL, and affect system performance. Therefore, using index recommendation technology can automatically give tuning suggestions to ensure stable system operation.
[0003] Currently, most database index recommendation methods perform data distribution statistics on the entire table or establish candidate indexes, which can reduce the amount of data that needs to be scanned when scanning the table. However, there is still a problem of slow and inaccurate collection of statistical information when recommending indexes in large data volume scenarios, which puts great resource pressure on the database and even blocks the normal operation of the business. Therefore, it is not suitable to apply the above methods to complex and slow SQL scenarios that need to be solved urgently. Summary of the invention
[0004] Based on this, the present invention provides an index recommendation method, device, equipment and medium based on histogram statistics to solve the problem that index recommendation in complex SQL scenarios is slow, inaccurate and wastes resources.
[0005] In a first aspect, an embodiment of the present invention provides an index recommendation method based on histogram statistics, the method comprising:
[0006] Receive the SQL query statement from the client and parse it to obtain the query string, and identify all target tables that match the query requirements based on the query string;
[0007] Determine the table join order of all target tables according to the pre-defined table join rules;
[0008] Generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large, to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field;
[0009] According to the order of each bucket in the second histogram of each target table, the index order corresponding to each target table is determined; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
[0010] In a second aspect, an embodiment of the present invention provides a device for mining association rules using a genetic algorithm, the device comprising:
[0011] The target table acquisition module is used to receive the SQL query statement from the client and parse it to obtain the query string, and identify all the target tables matching the query requirements according to the query string;
[0012] A table join order determination module is used to determine the table join order of all target tables according to a pre-defined table join rule;
[0013] A histogram construction module is used to generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field;
[0014] The index recommendation result acquisition module is used to determine the index order corresponding to each target table according to the order of each bucket in the second histogram of each target table; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
[0015] In a third aspect, an embodiment of the present invention provides an electronic device, the electronic device comprising:
[0016] at least one processor; and
[0017] a memory communicatively connected to the at least one processor; wherein,
[0018] The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute an index recommendation method based on histogram statistics described in any embodiment of the present invention.
[0019] In a fourth aspect, a computer-readable storage medium is also provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement an index recommendation method based on histogram statistics described in any embodiment of the present invention when executed.
[0020] The technical solution of the embodiment of the present invention determines the driving table and the driven table in the case of multi-table query by formulating a table connection rule, thereby determining the access order of each table and improving data retrieval performance; secondly, the characteristic of histogram that can record the data distribution statistics for a certain column is used to provide a fast and accurate way to estimate the data distribution information for index recommendation, thereby speeding up the SQL execution speed in large data volume scenarios. In complex SQL scenarios with multi-table connections and multiple filtering conditions, the beneficial effect of directly performing analysis and statistics on the database and outputting index recommendations is achieved.
[0021] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS
[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.
[0023] Figure 1 is a flowchart of an index recommendation method based on histogram statistics provided according to Embodiment 1 of the present invention;
[0024] Figure 2 is a flowchart of another index recommendation method based on histogram statistics provided according to the second embodiment of the present invention;
[0025] Figure 3 is a structural schematic diagram of an index recommendation device based on histogram statistics provided according to Embodiment 3 of the present invention;
[0026] Figure 4 It is a structural schematic diagram of an electronic device for implementing an index recommendation method based on histogram statistics according to an embodiment of the present invention. DETAILED DESCRIPTION
[0027] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.
[0028] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention 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 interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.
[0029] Embodiment 1
[0030] Figure 1 This is a flowchart of a method for index recommendation based on histogram statistics provided in the first embodiment of the present invention. This embodiment is applicable to the situation of forming fast and accurate index recommendations for complex SQL. The method can be executed by a device for index recommendation based on histogram statistics. The device can be implemented in the form of hardware and / or software, and the device can be configured in a database. Figure 1 As shown, the method includes:
[0031] S110 , receiving the SQL query statement from the client and parsing it to obtain a query string, and identifying all target tables matching the query requirement according to the query string.
[0032] When the database receives a query request, it needs to split the complete SQL query statement into multiple query strings and extract the tables, columns and various query conditions to be queried from the strings. That is, by parsing the SQL statement, the table that matches the query requirements will be obtained. All the tables containing the data required for the query are called all target tables.
[0033] S120: Determine the table join order of all target tables according to the pre-defined table join rule.
[0034] In multi-table association queries, the table join order is very important, and the join order directly determines the query results and performance. Therefore, in an embodiment of the present invention, a table join rule is proposed to further determine a table join order to ensure the optimal query performance.
[0035] Optionally, the table join order of all target tables is determined according to a pre-defined table join rule, which may include:
[0036] Input each target table into the cost model to calculate its single table access cost, and obtain the target table with the smallest single table access cost as the first table and add it to the table connection order set;
[0037] Obtain a current target table from the remaining target tables in order of the single table access cost from small to large;
[0038] Append the current target table to the end of each target table in the table join order set, build the current table join relationship, and use the cost model to calculate the current access cost corresponding to the current table join relationship;
[0039] If the preset cache area is empty, the current access cost is stored in the preset cache area; otherwise, when the current access cost is less than the cached cost in the cache area, the current access cost is updated and stored in the cache area;
[0040] Return to execute the operation of obtaining one current target table in the remaining target tables in order of the single table access cost from small to large, until the processing of all the remaining target tables is completed;
[0041] Obtain the target table corresponding to the cached cost currently stored in the cache area, and add the obtained target table to the table connection order set;
[0042] Return and execute the operation of obtaining a current target table from the remaining target tables in the order of the single table access cost from small to large, until only the unique target table remains;
[0043] Add the unique target table to the table join order set to obtain the table join order of all target tables.
[0044] The execution of complex SQL statements is usually divided into access plans for each table, that is, the access cost of each table is first calculated through the cost model. The access cost can include the number of accessed rows and the estimated cost, and the table with the lowest single-table access cost is used as the first table in the table join order, that is, the driving table, which refers to the first table processed when multiple tables are associated. The table join order set stores a part of the target tables whose join order has been determined. Therefore, in the initial stage of determining the table join order, there is only the first table in the table join order set. Next, the remaining target tables whose join order is to be determined are sequentially connected with the first table to build the current table join relationship.
[0045] It should be noted that the current table connection relationship is a set of connection relationships to be determined, that is, a set of tables constructed as the current table connection relationship needs to use the cost model to calculate the access cost after the connection. At the same time, the cache area stores a set of current table connection relationships with the lowest access cost. In a specific example, if the current table connection relationship is AB and the cache area is empty, AB is stored in the cache area, and the access cost of AB is recorded. Then, the remaining target table C is added to the tail of the table connection sequence set to construct the current table connection relationship AC. Then, the access cost of AC and AB is compared. If the access cost of AC is less than AB, AC replaces AB and is stored in the cache area. If AC is greater than AB, the current table connection relationship is eliminated, and the above operation is repeated for D in the remaining target tables until all the remaining target tables are traversed. Then the table connection sequence set is determined, and the table connection sequence in the table connection sequence set is the table connection sequence for all target tables.
[0046] S130, generating a first histogram corresponding to each target table, and reordering each bucket in each first histogram in order of filter factors from small to large, to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field.
[0047] Histogram is a basic statistical information provided in relational databases. Its basic principle is to sort the data and divide it into several buckets. According to the data bucketing method and the data stored in the buckets, it can be divided into equal width histograms and equal height histograms. Among them, the first histogram is the histogram generated when the filter factor is not introduced, and the second histogram is the histogram generated after the filter factor is introduced and sorted according to the filter factor. In the embodiment of the present invention, the filter factor refers to the ratio of the data value of each target field to the sum of all sampled data, that is, the frequency of occurrence of the value of each bucket.
[0048] S140. Determine the index order corresponding to each target table according to the order of each bucket in the second histogram of each target table; retrieve the SQL query statement in each target table according to the table connection order and the index order of each target table to obtain the index recommendation result.
[0049] In the above embodiment, a second histogram of a specified type is generated for each table. Since the filter factor has been determined, the arrangement order of the buckets in the second histogram is the index order corresponding to each target table, and the target fields are stored in the buckets. Therefore, the index order of each target table is also the access order of the target fields.
[0050] Further, searching the SQL query statement in each target table according to the table connection order and the index order of each target table to obtain the index recommendation result may include:
[0051] Determine the printing order of each target table according to the table connection order, and obtain a current target table in turn according to the printing order;
[0052] According to the index order of the current target table, a current index field is obtained in sequence;
[0053] Match each condition field in the query string of the SQL query statement with the current index field respectively;
[0054] If the match fails, then according to the index order of the current target table, the operation of obtaining the current index field in sequence is performed until the index field that matches successfully is obtained;
[0055] If the match is successful, the current index field and the filter factor corresponding to the current index field are printed out, and the process returns to execute the operation of obtaining a current target table in sequence according to the printing order until all target tables are processed.
[0056] In the embodiment of the present invention, the table connection sequence is used to determine the printing sequence of each target table, and the histogram is used to determine the index sequence of the current target table, and the index sequence determines the matching sequence of each index field in the current target table. Specifically, if the condition field in the SQL query statement fails to match the current index field, the traversal continues according to the index sequence in the histogram until the index field matches the condition field successfully. When the match is successful, the current index field that matches the condition field and the corresponding filter factor are printed out to complete the printing of the current table, and the above process is repeated until all target tables in the table connection sequence have completed the above printing.
[0057] The embodiment of the present invention determines the driving table and the driven table in the case of multi-table query by formulating a table connection rule, thereby determining the access order of each table and improving data retrieval performance; secondly, the characteristic of the histogram that can record the data distribution statistics for a certain column is used to provide a fast and accurate way to estimate the data distribution information for index recommendation, thereby speeding up the SQL execution speed in large data volume scenarios. In complex SQL scenarios with multi-table connections and multiple filtering conditions, the beneficial effect of directly performing analysis and statistics on the database and outputting index recommendations is achieved.
[0058] Embodiment 2
[0059] Figure 2A flowchart of another index recommendation method based on histogram statistics provided in the second embodiment of the present invention is provided. This embodiment is refined based on the above embodiment. Figure 2 As shown, the method specifically includes:
[0060] S210: Receive the SQL query statement from the client and parse it to obtain a query string, and identify all target tables matching the query requirement according to the query string.
[0061] S220: Determine the table join order of all target tables according to the pre-defined table join rule.
[0062] S230: Identify all condition fields in the query string of the SQL query statement, and determine the index fields that need to be accessed in each target table according to all the condition fields.
[0063] The condition fields in the SQL query string can be where, join, etc., and each condition field corresponds to the index field that needs to be accessed in the current target table. The index field can be the column name of a column of data.
[0064] S240 , performing sampling scans on the data corresponding to each index field in each target table according to a certain sampling ratio, and storing the extracted data in an initialized data buffer.
[0065] The sampling ratio is a preset percentage. Sampling the entire table data according to the preset percentage can avoid full table scanning while sampling the entire table evenly, while ensuring the accuracy of information and reducing performance overhead. The sampled data is stored in a map data structure (such as map<key,count> ) is initialized into a sampling buffer, where key represents the unique value (index field) of the sampled data, count represents the frequency of occurrence of the unique value in the table, and the sampling buffer is used to temporarily store the column data read from the clustered index of the table before building the histogram, and fill it in a random sampling manner.
[0066] S250 , traverse the initialized sampling buffer, and determine the histogram type corresponding to each target table according to the number of index fields.
[0067] When building the histogram, the map is read<key,count> The content in the sampling buffer is determined based on the number of different index fields stored therein and the number of histogram buckets to determine whether to create an equal-width histogram or an equal-height histogram.<key,count> The key-value pair information stored in the data is allocated to the corresponding buckets, and the statistics of each bucket are calculated. The statistics can be: minimum different value, maximum different value, and frequency, etc.
[0068] Optionally, the histogram type index fields corresponding to each target table are determined according to the number of index fields, which may include:
[0069] If the number of index fields in the current target table being processed does not exceed the preset number of histogram buckets, determining that the histogram type of the current target table is an equal-width histogram;
[0070] If the types of index fields in the current target table being processed are greater than the preset number of histogram buckets, it is determined that the histogram type of the current target table is an equal-height histogram.
[0071] Specifically, in the database, there are two main types of histograms, namely, equal-width histograms and equal-height histograms. The equal-width histogram divides the data distribution according to the frequency, that is, the number of data items in each bucket is equal; while the equal-height histogram divides the data distribution according to the value range, that is, the data range of each bucket is equal. In other words, each bucket in the equal-width histogram stores an index field, but the number of data items in each index field is different; each bucket in the equal-height histogram stores a different number of index fields, but the total number of data items stored in each bucket is the same.
[0072] S260. Generate a first histogram that matches each target table according to the histogram type and the sampling data of each index field in each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large to obtain a second histogram corresponding to each target table.
[0073] The first histogram is an initial histogram constructed by extracting data from the initialization buffer when no filter factor is introduced, that is, the index order of the current table has not yet been determined.
[0074] Further, generating a first histogram matching each target table according to the histogram type and the sampled data of each index field in each target table may include:
[0075] If the histogram type of the current target table being processed is an equal-width histogram, a bucket is created for each index field of the current target table, and an equal-width histogram containing multiple rectangles of equal width but unequal height is constructed based on all the sampled data corresponding to the adapted index field stored in each bucket;
[0076] If the histogram type of the current target table being processed is an equal-height histogram, buckets that match the preset number of histogram buckets are established, and the average storage quantity of each bucket is calculated based on the ratio of the number of sampled data to the number of histogram buckets. Then, an equal-height histogram containing multiple lines of different widths but the same height is constructed according to the average storage quantity.
[0077] Specifically, in an equal-width histogram, each bucket has the same width but different heights, that is, an index field is assigned to each bucket, but different index fields correspond to different numbers of data items; in an equal-height histogram, each bucket has the same height but different widths, that is, at least one index field with a certain volume is assigned to each bucket, and the capacity is the average storage quantity of each bucket.
[0078] Optionally, before reordering each bucket in each first histogram in the order of the filter factor from small to large, the following may also be included:
[0079] Count the total number of all sampled data in the current target table being processed in the initialized sampling buffer;
[0080] If the histogram type of the current target table is an equal-width histogram, the number of sampled data in each bucket of the first histogram of the current target table is counted, and the ratio of the number of sampled data in each bucket to the total number is calculated to obtain the filter factor corresponding to each bucket in the first histogram of the current target table;
[0081] If the histogram type of the current target table is an equal-height histogram, the filter factor corresponding to each bucket in the first histogram of the current target table is obtained based on the number of index fields contained in each bucket in the first histogram of the current target table, the average storage quantity of each bucket, the number of sampled data in each bucket, and at least one of the total quantity.
[0082] In an embodiment of the present invention, the filter factor is the ratio of the number of data items in each index field to the total sampled data, that is, the frequency in the sampled data. Specifically, in an equal-width histogram, since each bucket corresponds to an index field, the frequency of each index field is the ratio of the number of data items in the current bucket to the total sampled data. In each bucket of an equal-height histogram, there are two situations for storing data items. In one situation, the number of data items in the current index field is large and requires multiple buckets to store; or the number of data items in the current index field is small and does not fill the preset capacity of the bucket. In both of the above situations, there will be two or more index fields in a bucket, and the filter factor of the current bucket is the sum of the filter factors of multiple index fields.
[0083] S270. Determine the index order corresponding to each target table according to the order of each bucket in the second histogram of each target table; retrieve the SQL query statement in each target table according to the table connection order and the index order of each target table to obtain the index recommendation result.
[0084] The technical solution of the embodiment of the present invention mainly explains the construction of the histogram and the calculation of the filter factor by refining the overall solution. Specifically, the histogram statistical information is established through data sampling, which effectively avoids the resource consumption caused by the full table scan, so that statistical information can be collected more quickly in large data volume scenarios without affecting the business performance of the table; equal-width or equal-height histograms are established according to different data distribution situations, providing more accurate data distribution information, and determining the optimal index recommendation by introducing the filter factor.
[0085] Embodiment 3
[0086] Figure 3 This is a schematic diagram of the structure of an index recommendation device based on histogram statistics provided by Embodiment 3 of the present invention. Figure 3 As shown, the device comprises:
[0087] The target table acquisition module 310 is used to receive the SQL query statement from the client and parse it to obtain the query string, and identify all the target tables matching the query requirement according to the query string;
[0088] A table join order determination module 320, for determining the table join order of all target tables according to a pre-defined table join rule;
[0089] The histogram construction module 330 is used to generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field;
[0090] The index recommendation result acquisition module 340 is used to determine the index order corresponding to each target table according to the order of each bucket in the second histogram of each target table; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
[0091] In the embodiment of the present invention, a table connection rule is formulated to determine the driving table and the driven table in the case of multi-table query, thereby determining the access order of each table and improving data retrieval performance; secondly, the characteristic of the histogram that can record the data distribution statistics for a certain column is used to provide a fast and accurate way to estimate the data distribution information for index recommendation, thereby speeding up the SQL execution speed in large data volume scenarios. In complex SQL scenarios with multi-table connections and multiple filtering conditions, the beneficial effect of directly performing analysis and statistics on the database and outputting index recommendations is achieved.
[0092] Optionally, based on the above embodiments, the table join order determination module 320 may include:
[0093] A single table access cost calculation unit is used to input each target table into the cost model to calculate its single table access cost, and obtain the target table with the smallest single table access cost as the first table and add it to the table connection order set;
[0094] The remaining target table acquisition unit is used to sequentially acquire a current target table from the remaining target tables in the order of the single table access cost from small to large;
[0095] A temporary table connection relationship construction unit, used for appending the current target table to the end of each target table in the table connection sequence set, constructing the current table connection relationship, and calculating the current access cost corresponding to the current table connection relationship by using the cost model;
[0096] A cache access unit, for storing the current access cost in the preset cache if the preset cache is empty; otherwise, when the current access cost is less than the cached cost in the cache, updating and storing the current access cost in the cache;
[0097] A first loop processing unit is used to return to execute the operation of sequentially acquiring a current target table from the remaining target tables in the order of the single table access cost from small to large, until the processing of all the remaining target tables is completed;
[0098] A table join order set updating unit, used for acquiring a target table corresponding to a cached cost currently stored in the cache area, and adding the acquired target table to the table join order set;
[0099] The second loop processing unit is used to return to execute the operation of sequentially acquiring a current target table from the remaining target tables in the order of the single table access cost from small to large, until it is iterated until only a unique target table remains;
[0100] The table join order set determining unit is used to add the unique target table to the table join order set to obtain the table join order of all target tables.
[0101] Optionally, based on the above embodiments, the histogram construction module 330 may include:
[0102] A condition field identification unit, used to identify all condition fields in the query string of the SQL query statement, and determine the index fields that need to be accessed in each target table according to all the condition fields;
[0103] A data sampling unit is used to perform sampling scanning on the data corresponding to each index field in each target table according to a certain sampling ratio, and store the extracted data in an initialized data buffer;
[0104] A histogram type determination unit, used for traversing the initialized sampling buffer and determining the histogram type corresponding to each target table according to the number of index fields;
[0105] The histogram generating unit is used to generate a first histogram matching each target table according to the histogram type and the sampled data of each index field in each target table.
[0106] Optionally, based on the above embodiments, the histogram type determination unit may also be used to determine that the histogram type of the current target table is an equal-width histogram if the number of index fields in the current target table being processed does not exceed a preset number of histogram buckets;
[0107] If the types of index fields in the current target table being processed are greater than the preset number of histogram buckets, it is determined that the histogram type of the current target table is an equal-height histogram.
[0108] Optionally, based on the above embodiments, the histogram generating unit may also be used to, if the histogram type of the current target table being processed is an equal-width histogram, create a bucket for each index field of the current target table, and construct an equal-width histogram containing multiple rectangles of equal width but unequal height according to all the sampled data corresponding to the adapted index field stored in each bucket;
[0109] If the histogram type of the current target table being processed is an equal-height histogram, buckets that match the preset number of histogram buckets are established, and the average storage quantity of each bucket is calculated based on the ratio of the number of sampled data to the number of histogram buckets. Then, an equal-height histogram containing multiple lines of different widths but the same height is constructed according to the average storage quantity.
[0110] Optionally, based on the above embodiments, it further includes a filter factor calculation unit, which is used to count the total number of all sampled data in the current target table being processed in the initialized sampling buffer before reordering each bucket in each first histogram in the order of small to large filter factors;
[0111] If the histogram type of the current target table is an equal-width histogram, the number of sampled data in each bucket of the first histogram of the current target table is counted, and the ratio of the number of sampled data in each bucket to the total number is calculated to obtain the filter factor corresponding to each bucket in the first histogram of the current target table;
[0112] If the histogram type of the current target table is an equal-height histogram, the filter factor corresponding to each bucket in the first histogram of the current target table is obtained based on the number of index fields contained in each bucket in the first histogram of the current target table, the average storage quantity of each bucket, the number of sampled data in each bucket, and at least one of the total quantity.
[0113] Optionally, based on the above embodiments, the index recommendation result acquisition module 340 may further include:
[0114] Determine the printing order of each target table according to the table connection order, and obtain a current target table in turn according to the printing order;
[0115] An index field acquisition unit, used to sequentially acquire a current index field according to the index sequence of the current target table;
[0116] An index field matching unit, used to match each condition field in the query string of the SQL query statement with the current index field respectively;
[0117] A first matching determination unit, configured to, if the matching fails, sequentially obtain a current index field according to the index sequence of the current target table until a successfully matched index field is obtained;
[0118] The second matching determination unit is used to print out the current index field and the filter factor corresponding to the current index field if the match is successful, and return to execute the operation of sequentially acquiring a current target table according to the printing order until all target tables are processed.
[0119] An index recommendation device based on histogram statistics provided by an embodiment of the present invention can execute an index recommendation method based on histogram statistics provided by any embodiment of the present invention, and has functional modules and beneficial effects corresponding to the execution method.
[0120] Embodiment 4
[0121] Figure 4 A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.
[0122] like Figure 4 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0123] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.
[0124] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as an index recommendation method based on histogram statistics.
[0125] That is: receiving the SQL query statement from the client and parsing it to obtain the query string, and identifying all target tables that match the query requirements according to the query string;
[0126] Determine the table join order of all target tables according to the pre-defined table join rules;
[0127] Generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large, to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field;
[0128] According to the order of each bucket in the second histogram of each target table, the index order corresponding to each target table is determined; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
[0129] In some embodiments, a method for index recommendation based on histogram statistics may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the method for index recommendation based on histogram statistics described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to perform an index recommendation method based on histogram statistics in any other appropriate manner (e.g., by means of firmware).
[0130] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.
[0131] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.
[0132] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0133] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).
[0134] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.
[0135] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.
[0136] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and this document does not limit this.
[0137] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims
1. An index recommendation method based on histogram statistics, characterized in that: include: Receive the SQL query statement from the client and parse it to obtain the query string, and identify all target tables that match the query requirements based on the query string; Determine the table join order of all target tables according to the pre-defined table join rules; Generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large, to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field; According to the order of each bucket in the second histogram of each target table, the index order corresponding to each target table is determined; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
2. The method according to claim 1, characterized in that: Determine the table join order of all target tables according to the pre-defined table join rules, including: Input each target table into the cost model to calculate its single table access cost, and obtain the target table with the smallest single table access cost as the first table and add it to the table connection order set; Obtain a current target table from the remaining target tables in order of the single table access cost from small to large; Append the current target table to the end of each target table in the table join order set, build the current table join relationship, and use the cost model to calculate the current access cost corresponding to the current table join relationship; If the preset cache area is empty, the current access cost is stored in the preset cache area; otherwise, when the current access cost is less than the cached cost in the cache area, the current access cost is updated and stored in the cache area; Return to execute the operation of obtaining one current target table in the remaining target tables in order of the single table access cost from small to large, until the processing of all the remaining target tables is completed; Obtain the target table corresponding to the cached cost currently stored in the cache area, and add the obtained target table to the table connection order set; Return and execute the operation of obtaining a current target table from the remaining target tables in the order of the single table access cost from small to large, until only the unique target table remains; Add the unique target table to the table join order set to obtain the table join order of all target tables.
3. The method according to claim 1, characterized in that Generate the first histogram corresponding to each target table, including: Identify all condition fields in the query string of the SQL query statement, and determine the index fields that need to be accessed in each target table according to all the condition fields; Perform sampling scans on the data corresponding to each index field in each target table according to a certain sampling ratio, and store the extracted data in the initialized data buffer; Traverse the initialized sampling buffer and determine the histogram type corresponding to each target table according to the number of index fields; A first histogram matching each target table is generated according to the histogram type and the sampled data of each index field in each target table.
4. The method according to claim 3, characterized in that The histogram type index fields corresponding to each target table are determined according to the number of index fields, including: If the number of index fields in the current target table being processed does not exceed the preset number of histogram buckets, determining that the histogram type of the current target table is an equal-width histogram; If the types of index fields in the current target table being processed are greater than the preset number of histogram buckets, it is determined that the histogram type of the current target table is an equal-height histogram.
5. The method according to claim 3, characterized in that: Generating a first histogram matching each target table according to the histogram type and the sampled data of each index field in each target table, including: If the histogram type of the current target table being processed is an equal-width histogram, a bucket is created for each index field of the current target table, and an equal-width histogram containing multiple rectangles of equal width but unequal height is constructed based on all the sampled data corresponding to the adapted index field stored in each bucket; If the histogram type of the current target table being processed is an equal-height histogram, buckets that match the preset number of histogram buckets are established, and the average storage quantity of each bucket is calculated based on the ratio of the number of sampled data to the number of histogram buckets. Then, an equal-height histogram containing multiple lines of different widths but the same height is constructed according to the average storage quantity.
6. The method according to claim 3, characterized in that Before reordering each bucket in each first histogram in the order of filter factors from small to large, it also includes: Count the total number of all sampled data in the current target table being processed in the initialized sampling buffer; If the histogram type of the current target table is an equal-width histogram, the number of sampled data in each bucket of the first histogram of the current target table is counted, and the ratio of the number of sampled data in each bucket to the total number is calculated to obtain the filter factor corresponding to each bucket in the first histogram of the current target table; If the histogram type of the current target table is an equal-height histogram, the filter factor corresponding to each bucket in the first histogram of the current target table is obtained based on the number of index fields contained in each bucket in the first histogram of the current target table, the average storage quantity of each bucket, the number of sampled data in each bucket, and at least one of the total quantity.
7. The method according to any one of claims 1 to 6, characterized in that: According to the table connection order and the index order of each target table, the SQL query statement is searched in each target table to obtain the index recommendation result, including: Determine the printing order of each target table according to the table connection order, and obtain a current target table in turn according to the printing order; According to the index order of the current target table, a current index field is obtained in sequence; Match each condition field in the query string of the SQL query statement with the current index field respectively; If the match fails, then according to the index order of the current target table, the operation of obtaining the current index field in sequence is performed until the index field that matches successfully is obtained; If the match is successful, the current index field and the filter factor corresponding to the current index field are printed out, and the process returns to execute the operation of obtaining a current target table in sequence according to the printing order until all target tables are processed.
8. An index recommendation device based on histogram statistics, characterized in that: include: The target table acquisition module is used to receive the SQL query statement from the client and parse it to obtain the query string, and identify all the target tables matching the query requirements according to the query string; A table join order determination module is used to determine the table join order of all target tables according to a pre-defined table join rule; A histogram construction module is used to generate a first histogram corresponding to each target table, and reorder each bucket in each first histogram in the order of filter factors from small to large to obtain a second histogram corresponding to each target table; wherein each bucket corresponds to at least one index field; The index recommendation result acquisition module is used to determine the index order corresponding to each target table according to the order of each bucket in the second histogram of each target table; according to the table connection order and the index order of each target table, the SQL query statement is retrieved in each target table to obtain the index recommendation result.
9. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute an index recommendation method based on histogram statistics as described in any one of claims 1-7.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement an index recommendation method based on histogram statistics according to any one of claims 1 to 7 when executed.