Database processing methods, equipment and storage media

Abstract

This disclosure provides a database processing method, device, and storage medium. The method includes: receiving an operation instruction for the database and determining whether dictionary-based optimization is enabled for the target column associated with the operation instruction; if dictionary-based optimization is enabled, obtaining a dictionary corresponding to the target column, wherein the dictionary includes a mapping relationship between values ​​in the target column and corresponding replacement values; encoding the target column according to the dictionary to replace the values ​​in the target column with corresponding replacement values; converting the operation instruction into an operation instruction corresponding to the encoded target column according to the dictionary, executing the converted operation instruction, and decoding the operation result according to the dictionary. This disclosure can automatically determine whether dictionary-based optimization is enabled during database operations and then obtain the dictionary, reducing user maintenance costs and improving database operation performance.

Description

Technical Field

[0001] This disclosure relates to the field of computer and network communication technology, and in particular to a database processing method, device and storage medium. Background Technology

[0002] During database querying or scanning, dictionary encoding / decoding can be used to optimize query performance. For example, if a data column is of string type (e.g., varchar(30)) and the value of this column is known to be only "aaa", "bbb", and "ccc", then at the beginning of the query process, this column can be dictionary encoded, for example, replacing "aaa" with integer 0, "bbb" with integer 1, and "ccc" with integer 2. The query command is also replaced accordingly during the query process. At the end of the query process, the query result can be decoded, replacing 0 with "aaa", 1 with "bbb", and 2 with "ccc", thus obtaining the same result as the original query. The entire query process is based on the replacement value, which can optimize query performance.

[0003] In existing technologies, the use of dictionaries during database processing is controlled by the user, resulting in additional resource overhead, increased user maintenance costs, and potential degradation of database query performance. Summary of the Invention

[0004] This disclosure provides a database processing method, device, and storage medium to improve database operation performance and reduce user maintenance costs.

[0005] In a first aspect, embodiments of this disclosure provide a database processing method, including:

[0006] Receive database operation instructions and determine whether dictionary-based optimization is enabled for the target column associated with the operation instructions;

[0007] If dictionary-based optimization is enabled, the dictionary corresponding to the target column is obtained, which includes the mapping relationship between the values ​​in the target column and the corresponding replacement values;

[0008] The target column is encoded according to a dictionary so that the values ​​included in the target column are replaced with the corresponding replacement values;

[0009] The operation instructions are converted into operation instructions corresponding to the encoded target columns according to the dictionary, the converted operation instructions are executed, and the operation results are decoded according to the dictionary.

[0010] In a second aspect, embodiments of this disclosure provide a database processing device, comprising:

[0011] The decision unit is used to receive operation instructions for the database and determine whether dictionary-based optimization should be enabled for the target column associated with the operation instruction.

[0012] The dictionary acquisition unit is used to acquire the dictionary corresponding to the target column if it is determined that dictionary-based optimization is enabled. The dictionary includes the mapping relationship between the values ​​in the target column and the corresponding replacement values.

[0013] The encoding unit is used to encode the target column according to the dictionary, so as to replace the values ​​included in the target column with the corresponding replacement values;

[0014] The execution unit is used to convert operation instructions into operation instructions corresponding to the encoded target column according to the dictionary, execute the converted operation instructions, and decode the operation results according to the dictionary.

[0015] Thirdly, embodiments of this disclosure provide an electronic device, including: at least one processor and a memory;

[0016] The memory stores instructions that the computer executes;

[0017] At least one processor executes computer execution instructions stored in memory, causing at least one processor to perform the database processing method as described in the first aspect above and various possible designs of the first aspect.

[0018] Fourthly, embodiments of this disclosure provide a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the database processing method described in the first aspect and various possible designs of the first aspect.

[0019] Fifthly, embodiments of this disclosure provide a computer program product including computer execution instructions, which, when executed by a processor, implement the database processing method described in the first aspect and various possible designs of the first aspect.

[0020] The database processing method, device, and storage medium provided in this disclosure receive operation instructions for the database and determine whether dictionary-based optimization is enabled for the target column associated with the operation instructions. If dictionary-based optimization is enabled, the dictionary corresponding to the target column is obtained, and the target column is encoded according to the dictionary to replace the values ​​included in the target column with corresponding replacement values. Finally, the operation instructions are converted into operation instructions corresponding to the encoded target column according to the dictionary, the converted operation instructions are executed, and the operation results are decoded according to the dictionary. This disclosure can automatically determine whether dictionary-based optimization is enabled during database operations on a mobile device before obtaining the dictionary, reducing user maintenance costs and improving database operation performance. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of this disclosure or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this disclosure. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 A schematic diagram of a scenario for a database processing method provided in an embodiment of this disclosure;

[0023] Figure 2 This is a schematic flowchart of a database processing method provided in an embodiment of the present disclosure;

[0024] Figure 3 A flowchart illustrating the maintenance dictionary provided in one embodiment of this disclosure;

[0025] Figure 4 This is a schematic flowchart of a database processing method provided in another embodiment of the present disclosure;

[0026] Figure 5 This is a structural block diagram of a database processing device provided in an embodiment of the present disclosure;

[0027] Figure 6 This is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present disclosure. Detailed Implementation

[0028] To make the objectives, technical solutions, and advantages of the embodiments of this disclosure clearer, the technical solutions of the embodiments of this disclosure will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this disclosure, and not all embodiments. Based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0029] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0030] To clearly understand the technical solution of this application, the solutions of the prior art will be described in detail first.

[0031] During database querying or scanning, dictionary encoding / decoding can be used to optimize query performance. For example, if a column in the database is of string type (e.g., varchar(30)) and the values ​​of this column are known to be only "aaa", "bbb", and "ccc", then dictionary encoding can be performed on this column at the beginning of the query process. For example, integers 0 can be used to replace "aaa", 1 to replace "bbb", and 2 to replace "ccc", and the query command can be replaced accordingly during the query process. At the end of the query process, the result can be decoded, replacing 0 with "aaa", 1 with "bbb", and 2 with "ccc", thus obtaining the same result as the original query. The entire query process is based on the replacement values, which can optimize query performance.

[0032] After dictionary encoding optimization, query execution efficiency will be significantly improved because:

[0033] 1. The amount of data processed by the query is significantly reduced. For example, in the example above, after replacing varchar(30) with int64, the amount of data in the column can be reduced by more than 3 times.

[0034] 2. For most database execution engines, the efficiency of processing integer and fixed-length data types is much higher than that of processing variable-length data types.

[0035] Because dictionary encoding greatly improves database query performance, this technology has been widely used in many database products to help improve query performance.

[0036] However, current dictionary optimization techniques have the following problems:

[0037] 1. The dictionary content needs to be provided by the user and cannot be automatically derived by the database system.

[0038] 2. When the data in the database changes, the contents of the dictionary need to be updated. Existing solutions for updating the dictionary are as follows, but each has certain problems:

[0039] a. It requires users to manually update the dictionary content, therefore it is not automated enough;

[0040] b. You can configure the dictionary update cycle to update the dictionary content periodically, but the dictionary obtained in this way may not be up-to-date;

[0041] c. The dictionary content can be dynamically retrieved in real time during dictionary queries, but this method incurs performance overhead, especially when the data does not change, making it unnecessary to regenerate the dictionary.

[0042] 3. Introducing dictionary encoding optimization does not necessarily improve query performance. For example, if the dictionary is too large, the overhead of encoding and decoding will increase, potentially leading to a performance regression in queries. For instance, if a database has 100 rows and a certain column has 100 different values, the dictionary would need to include 100 entries. This might not significantly improve query performance, but the encoding and decoding process incurs substantial performance overhead. Existing systems cannot distinguish between these situations and will indiscriminately use dictionary optimization techniques.

[0043] 4. Existing technologies can only perform dictionary encoding on columns in database tables, but cannot encode intermediate results of queries, such as the intermediate results of join and projection operations. This makes it impossible to optimize some queries using dictionary encoding.

[0044] To address at least one of the aforementioned technical problems, this disclosure provides a database processing method that can automatically determine whether dictionary-based optimization needs to be enabled for the target column associated with the database operation instruction. If dictionary-based optimization is required, the dictionary corresponding to the target column is obtained, thereby reducing user maintenance costs and preventing query performance regression.

[0045] The database processing method provided in this disclosure can be applied to, for example, Figure 1 In the application scenario shown, upon receiving an operation instruction for the database, it is determined whether dictionary-based optimization is enabled for the target column associated with the operation instruction. If dictionary-based optimization is enabled, the dictionary corresponding to the target column is obtained, and the target column is encoded according to the dictionary to replace the values ​​included in the target column with the corresponding replacement values. Finally, the operation instruction is converted into an operation instruction corresponding to the encoded target column according to the dictionary, the converted operation instruction is executed, and the operation result is decoded according to the dictionary.

[0046] The database processing method of this disclosure will be described in detail below with reference to specific embodiments.

[0047] refer to Figure 2 , Figure 2 This is a schematic flowchart of a database processing method according to an embodiment of the present disclosure. The method of this embodiment can be applied to electronic devices such as servers, and the database processing method includes:

[0048] S201. Receive the operation command for the database and determine whether dictionary-based optimization is enabled for the target column associated with the operation command.

[0049] In this embodiment, in order to improve query performance and avoid using a dictionary which may actually reduce query performance, after receiving a user's operation command for the database, it is necessary to first measure the query performance of the target column corresponding to the operation command after using a dictionary. Based on the measurement results, it is determined whether it is necessary to enable dictionary-based optimization. Then, under the premise of dictionary-based optimization, the dictionary is obtained to avoid blindly using a dictionary and reducing query performance.

[0050] The operation instructions may include, but are not limited to, query operations (or filter operations), scan operations, join operations, and project operations based on the target column in the database.

[0051] S202. If it is determined that dictionary-based optimization is enabled, then obtain the dictionary corresponding to the target column.

[0052] In this embodiment, the dictionary includes a mapping relationship between the values ​​in the target column and their corresponding replacement values. The replacement value can refer to the value obtained by replacing a value in the target column. Optionally, the length of the replacement value can be shorter than the value in the target column, and / or the data type of the replacement value can differ from the data type of the value in the target column.

[0053] In this embodiment, when dictionary-based optimization is enabled, the electronic device can obtain the dictionary corresponding to the target column and encode the data of the target column according to the dictionary.

[0054] In one possible implementation, the dictionary corresponding to the target column is obtained, including the following two methods:

[0055] Retrieve the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, retrieve all the distinct values ​​in the target column and construct a dictionary based on these values; and / or

[0056] Based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, obtain the different values ​​included in the target column, and construct a dictionary based on some or all of the values.

[0057] In this embodiment, in the first method of obtaining the dictionary, the electronic device can obtain the number of distinct values ​​(NDV) included in the target column. This indicator is an important indicator in database statistics, used to describe the number of distinct values ​​included in the target column. For example, if the target column can only have three possible values, "aaa", "bbb", and "ccc", then the NDV of the target column is 3. Optionally, the number of distinct values ​​included in the target column can be queried by SQL query commands, obtained from the statistical information maintained by the database system (such as meta metadata information), or obtained by any other feasible method. No restrictions are imposed here.

[0058] Furthermore, the number of distinct values ​​in the target column can be compared with a preset threshold. If the number of distinct values ​​is less than the preset threshold, it indicates that the number of distinct values ​​in the target column is sufficiently small, and the performance overhead of encoding and decoding to generate the dictionary is minimal. Therefore, the distinct values ​​in the target column can be obtained, and the dictionary can be constructed based on these distinct values. This method achieves automatic dictionary generation through statistical information, eliminating the need for users to provide dictionary content and reducing user costs.

[0059] Optionally, if NDV is less than a preset data threshold, an execution plan for querying the distinct values ​​included in the target column can be sent to the database execution engine. For ease of description, this is referred to as the second execution plan. The execution engine then executes this second execution plan to query the distinct values ​​included in the target column and receives the distinct values ​​included in the target column returned by the execution engine. As an example, the SQL query instruction corresponding to the second execution plan can be: `select distinctx from tbl where x is not null`; where x refers to the value in the target column, and tbl refers to the target column. Of course, in this embodiment, any other feasible method can be used to obtain the distinct values ​​included in the target column, such as directly sending an SQL execution instruction instead of an execution plan, etc., which will not be elaborated here.

[0060] Furthermore, when constructing the dictionary based on the different values ​​included in the target column, the values ​​in the target column are actually mapped to a replacement value. That is, a mapping relationship is constructed between the different values ​​in the target column and their corresponding replacement values. Optionally, the different values ​​in the target column can be sorted, with each value corresponding to a unique replacement value. For example, if the different values ​​are (aaa, bbb, ccc), the mapping relationship constructed in the dictionary is {0: aaa, 1: bbb, 2: ccc}, meaning 0 replaces "aaa", 1 replaces "bbb", and 2 replaces "ccc". In the second method of obtaining the dictionary, some or all values ​​of the different values ​​included in the target column can be obtained based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, and the dictionary can be constructed based on these values. Automatic dictionary generation is achieved through theoretical derivation, eliminating the need for users to provide dictionary content and reducing user costs.

[0061] The first execution plan can refer to the detailed sequence of instructions generated by the optimizer when the database executes operation instructions.

[0062] Operators can refer to identifiers that operate on the values ​​of a target column, including but not limited to scan, filter, join, and project operators.

[0063] The scan operator is used to iterate through the values ​​of a target column;

[0064] The filter operator is used to filter, modify, and select values ​​in a target column, ensuring that only records that meet specific conditions are selected or displayed.

[0065] The join operator is used to connect two or more tables.

[0066] The projection operator can be used to extract specified information from a target column.

[0067] Functions can include substr, concat, reverse, trim, ltrim, rtrim, lower, upper, left, right, replace, replicate, etc. from the database.

[0068] Based on operators and / or functions

[0069] In one possible implementation, different methods of obtaining the dictionary can be used in different situations for the dictionary acquisition methods provided in the above embodiments. Specifically, obtaining the dictionary corresponding to the target column may include:

[0070] Generate a first execution plan based on the operation instructions, and determine the types of operators included in the first execution plan;

[0071] If the operator type includes a scan operator, then obtain the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, then obtain all the distinct values ​​in the target column and construct a first dictionary based on the distinct values ​​in the target column; and / or

[0072] If the operator type includes filter operators or join operators, then obtain the distinct values ​​included in the target column based on the predicates in the operators, and construct a second dictionary based on the distinct values ​​included in the target column; and / or

[0073] If the operator type includes projection operators, then the different values ​​included in the target column are obtained according to the functions in the operator, and a third dictionary is constructed based on the different values ​​included in the target column.

[0074] In this embodiment, since different methods of obtaining the dictionary have different performance overheads and the dictionary size may also be different, the method of obtaining the dictionary can be selected according to actual needs. Specifically, after the database optimizer generates the first execution plan for the operation instructions, the method of obtaining the dictionary can be determined according to the different types of operators included in the first execution plan. For example, different dictionary obtaining methods can be adopted when the types of operators are selection scan operators, filtering operators, join operators and / or projection operations.

[0075] Specifically, if the operator type is a scan operator and the number of different values ​​is less than a preset threshold, then a dictionary needs to be constructed using all the different values ​​included in the target column. By obtaining all the different values ​​included in the target column, the most complete dictionary can be constructed, which is referred to here as the first dictionary, and can realize the encoding and decoding of all values ​​in the target column.

[0076] If the operator type includes filter operators or join operators, it may involve partial or all values ​​of the different values ​​included in the target column. Specifically, it can be reflected in the predicate of the operator. In the operator, the predicate can be used to describe the conditions or constraints of the data. Therefore, partial or all values ​​of the different values ​​included in the target column can be obtained according to the predicate in the operator, and a second dictionary can be constructed based on the partial or all values.

[0077] For example, if the operators in the first execution plan include filter operators or join operators, then all predicates of the operators are extracted. If the predicate is an in-predicate, it is used to check whether the value of the target column is in a specific set. For example, the predicate of the filter operator is x in('a','b','c'), which means filtering data in the target column x whose values ​​are in the set ('a','b','c'). This indicates that only the three values ​​'a', 'b', and 'c' are considered for the target column, and a dictionary {0:a,1:b,2:c} can be constructed. If the predicate is disjunctive normal form (i.e., a logical expression used to simplify and optimize query conditions) and has the same variables, such as x = 'a' or x = 'b' or x = 'c', this also indicates that only the three values ​​'a', 'b', and 'c' are considered for the target column, and a dictionary {0:a,1:b,2:c} can be constructed for the target column x.

[0078] If the first execution plan includes projection operators, then all function calls within those operators are extracted. For example, the function `substr(x,1,2)` calculates a subsequence of length 2 starting from index 1 in the target column `x`. Assuming the values ​​in the target column `x` are known to be {'aaa','bbb','ccc'}, it can be deduced that the subsequence constructed based on the function `substr(x,1,2)` will contain the values ​​{'aa','bb','cc'}. Therefore, a dictionary corresponding to the subsequence can be constructed: {0:aa,1:bb,2:cc}. Of course, functions can also include, but are not limited to, those found in databases such as `concat`, `reverse`, `trim`, `ltrim`, `rtrim`, `lower`, `upper`, `left`, `right`, `replace`, and `replicate`. The usage of each function will not be elaborated here.

[0079] S203. Encode the target column according to the dictionary so that the values ​​included in the target column are replaced with the corresponding replacement values.

[0080] In this embodiment, the target column is encoded according to the dictionary, that is, the values ​​included in the target column are replaced with the corresponding replacement values ​​according to the dictionary. The specific encoding process will not be described in detail here.

[0081] S204. Convert the operation instructions into operation instructions corresponding to the encoded target column according to the dictionary, execute the converted operation instructions, and decode the operation results according to the dictionary.

[0082] In this embodiment, a dictionary is used to convert operation instructions into operation instructions corresponding to the encoded target column. That is, if the operation instruction involves a specific value of the target column, it is replaced with the corresponding replacement value according to the dictionary. If the target instruction does not involve a specific value of the target column, such as a join operation, the conversion can specify that the join operation is performed on the encoded target column, rather than the original target column. Furthermore, the converted operation instruction can be executed to obtain the operation result, and then the replacement value in the operation result can be replaced with the original value of the target column according to the dictionary, thus achieving encoding and decoding of the target column and improving query performance.

[0083] The database processing method provided in this embodiment receives operation instructions for the database and determines whether dictionary-based optimization is enabled for the target column associated with the operation instructions. If dictionary-based optimization is enabled, the method obtains the dictionary corresponding to the target column, encodes the target column according to the dictionary, replaces the values ​​included in the target column with corresponding replacement values, and finally converts the operation instructions into operation instructions corresponding to the encoded target column according to the dictionary. The converted operation instructions are then executed, and the operation results are decoded according to the dictionary. This embodiment automatically determines whether dictionary-based optimization is enabled and then obtains the dictionary, without user intervention, reducing user maintenance costs, improving database processing performance, and can be applied to any database operation process, and can also realize dictionary-based optimization of intermediate results.

[0084] In one possible implementation, obtaining the dictionary corresponding to the target column in step S202 may further include:

[0085] Determine if the dictionary is stored in the cache;

[0086] If the dictionary is not stored in the cache, create the dictionary and store it in the cache; or

[0087] If the dictionary is stored in the cache and has not expired, then the dictionary is retrieved from the cache.

[0088] In this embodiment, the dictionary generated from statistical information requires sending an execution plan to the execution engine, thus incurring some performance overhead. In contrast, the dictionary obtained through theoretical derivation incurs almost no performance overhead. Therefore, to reduce the performance overhead of generating the dictionary from statistical information, the dictionary generated from the target column using statistical methods needs to be cached. Optionally, the cache uses a key-value data structure. The key includes the names of the table and the target column, as well as the version number of the table used to generate the dictionary, where the table is the one containing the target column.

[0089] Figure 3This is a flowchart illustrating the dictionary maintenance process according to an embodiment of this disclosure. When an operation instruction is received requiring the retrieval of the dictionary corresponding to a target column, the system checks whether a corresponding dictionary is stored in the cache. If not, the dictionary is created. If it is, the version number of the target column is compared with the version number of the data corresponding to the dictionary in the cache. If the version number comparison result is different, it indicates that the data of the target column has been updated, and the original dictionary has expired and is no longer applicable, requiring a new dictionary to be created. If it has not expired, the dictionary is retrieved from the cache. This achieves automatic detection of the dictionary status, thereby regenerating the dictionary as needed, ensuring that the dictionary and data used for the query always match.

[0090] Optionally, in the above embodiments, after receiving an operation instruction, it can first determine whether the number of different values ​​in the target column is less than a preset number threshold. If it is not less than the preset number threshold, there is no need to check whether the corresponding dictionary is stored in the cache; if it is less than the preset number threshold, then check whether the corresponding dictionary is stored in the cache.

[0091] refer to Figure 4 , Figure 4 This is a schematic flowchart illustrating a database processing method according to another embodiment of this disclosure. The method of this embodiment can be applied to electronic devices such as servers. Figure 2 Based on the illustrated embodiment, the database processing method further includes:

[0092] S301. Determine whether to enable dictionary-based optimized prediction cost metrics for the target column associated with the operation instruction.

[0093] In this embodiment, dictionary encoding optimization does not necessarily lead to performance improvements. Therefore, a cost model is needed to evaluate the overhead and benefits of enabling dictionary optimization on the execution plan.

[0094] This cost model should consider the following factors:

[0095] 1. The larger the number of distinct values ​​(NDV) in the target column, the greater the cost. This is because a larger NDV results in a larger dictionary, which in turn increases the overhead of data encoding and decoding.

[0096] 2. The smaller the amount of data obtained after encoding compared to the original amount of data, the greater the performance gain;

[0097] 3. The more complex operations the target column involves, the greater the performance gain. If the data is simply encoded first and then decoded last, it will only add extra overhead without any benefit. These complex operations include:

[0098] a. Columns used as inequalities in a join condition;

[0099] b. As one of the aggregate columns in the aggregation operation;

[0100] c. As part of the shuffle (filtering) data.

[0101] Based on the above, in order to determine the cost of enabling dictionary-based optimization, a predicted cost metric is used to measure the overhead and benefits of dictionary-based optimization, thereby enabling a more accurate assessment of whether to enable dictionary-based optimization.

[0102] Specifically, the dictionary-based optimized prediction cost metric is determined for the target column associated with the operation instruction, including:

[0103] Determine the dictionary-based optimized performance overhead and performance benefit metrics for the target column associated with the operation command, and determine the predicted cost metric based on the performance overhead and performance benefit metrics.

[0104] In this embodiment, the predicted cost indicator is determined based on the difference between the performance cost indicator and the performance benefit indicator. The performance cost indicator can refer to the resource usage after dictionary-based optimization; the performance benefit indicator can refer to the performance improvement brought about by dictionary-based optimization.

[0105] Specifically, determine the dictionary-based optimized performance overhead metrics for the target columns associated with the operation instructions, including:

[0106] Determine the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results;

[0107] Determine the dictionary-based performance gains for the target columns associated with the operation instructions, including:

[0108] Determine the primary performance gain metric resulting from changes in data width and / or data type due to dictionary encoding of the target column's values; and / or

[0109] Determine the second performance benefit metric generated by the target column during database operations after the target column is encoded according to the dictionary.

[0110] In this embodiment, the process of encoding the target column and decoding the operation result incurs performance overhead, which can be used to determine a performance overhead index. After encoding the target column, changes in the data width and / or data type of each value in the target column can bring certain benefits, which can be used to determine a first performance benefit index. Alternatively, the database operation process involving the target column can also bring certain benefits due to dictionary-based optimization, which can be used to determine a second performance benefit index. The first performance benefit index may be zero, and the second performance benefit index may also be zero. Therefore, a predicted cost index can be determined based on the performance overhead index and the first performance benefit index, or based on the performance overhead index and the second performance benefit index, or based on the performance overhead index, the first performance benefit index, and the second performance benefit index.

[0111] Specifically, the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results are determined, including:

[0112] Determine the first amount of data to encode the target column according to the dictionary, and the second amount of data to decode the operation result;

[0113] The first performance threshold of the encoding process is determined by using a preset encoding complexity function based on the first data volume, and the second performance threshold of the decoding process is determined by using a preset decoding complexity function based on the second data volume.

[0114] The sum of the first performance cost and the second performance cost is defined as the performance overhead metric.

[0115] In this embodiment, the overhead of the encoding process is related to the first amount of data encoded according to the dictionary for the target column, and also to the complexity of the encoding process. Therefore, the first performance overhead of the encoding process can be characterized based on the first amount of data and the complexity of the encoding process. Optionally, the first performance overhead can be determined by multiplying the first amount of data encoded by a preset encoding complexity function value. Similarly, the overhead of the decoding process is related to the second amount of data decoded according to the dictionary for the operation result, and also to the complexity of the decoding process. Therefore, the second performance overhead of the decoding process can be characterized based on the second amount of data decoded by the complexity of the decoding process. Optionally, the second performance overhead can be determined by multiplying the second amount of data decoded by a preset decoding complexity function value. Then, the performance overhead index is determined based on the sum of the first performance overhead and the second performance overhead.

[0116] For example, assuming the first amount of data involved in encoding is E, the second amount of data involved in decoding is D, and the dictionary size is G, the formula for calculating the performance overhead is as follows:

[0117] Performance overhead metric = E*e(G) + D*d(G)

[0118] Here, e() and d() are functions describing the encoding and decoding complexity, respectively. The specific form of these functions depends on the encoding / decoding algorithm and data structure used. For example, if a binary search tree is used as the encoding / decoding algorithm, then e(G) = c1 * log(G). If the data structure uses an array to store the dictionary, then d(G) = c2, where c1 and c2 are constants.

[0119] In the above embodiments, determining the first performance gain metric resulting from changes in data width and / or data type in the values ​​included in the target column after encoding the target column according to the dictionary may specifically include:

[0120] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0121] The first target coefficient is determined based on the first data type and the second data type;

[0122] The product of the difference between the first average data width and the second average data width and the first target coefficient is obtained, and the first performance benefit index is determined based on the product.

[0123] The first and second data types can include variable-length data types or fixed-length data types.

[0124] For example, assuming the first average data width of the values ​​included in the target column is B, such as B = 30 for the character data type char(30); and the second average data width of the replacement values ​​in the dictionary is A, such as A = 4 for the integer data type int32, then the formula for the first performance benefit metric corresponding to a single data record can be:

[0125] First performance gain metric = F * (BA)

[0126] Where F is the first target coefficient, and: if the first data type is a variable-length data type and the second data type is a fixed-length data type, then F>1; if both the first and second data types are fixed-length or variable-length data types, then F=1; if the first data type is a fixed-length data type and the second data type is a variable-length data type, then F<1.

[0127] In the above embodiments, determining the second performance benefit metric generated by the target column during database operations after encoding the target column according to the dictionary may specifically include:

[0128] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0129] The first target coefficient is determined based on the first data type and the second data type;

[0130] Determine the types of operations and the number of records involved in the database operations performed on the target column after encoding it according to the dictionary; determine the second target coefficient based on the types of operations.

[0131] The product of the difference between the first average data width and the second average data width and the first target coefficient, the second target coefficient, and the number of records is obtained, and the second performance benefit index is determined based on the product.

[0132] In this embodiment, since the performance benefit index is also related to the complexity of the types of operations performed on the target column in the database operation process, different second target coefficients are preset for different complexity types of operations to take into account the operation type factor. Furthermore, it is also related to the number of records (i.e., rows) participating in the database operation process. Therefore, the second performance benefit index can be obtained by multiplying the difference between the first average data width and the second average data width by the first target coefficient, the second target coefficient, and the number of records. The specific formula can be:

[0133] ci*R*F*(BA)

[0134] Where ci is the second target coefficient corresponding to operation type i. Operation types can include some more complex operations such as join, aggregate, and shuffle. The second target coefficient is different for different operation types. R is the number of records (i.e., rows) in the database operation process involving the target column.

[0135] Optionally, a second performance gain metric is determined based on the database operations performed by the target column after it has been encoded according to a dictionary, including:

[0136] If the target column is encoded according to the dictionary and then participates in multiple database operations, the second performance benefit index generated in each database operation is obtained and accumulated to obtain the accumulated second performance benefit index.

[0137] For example, assuming the first average data width is B, the second average data width is A, the first target coefficient is F, the second target coefficients for join, aggregate, and shuffle are c1, c2, and c3 respectively, the number of records participating in join in the target column is R1, the number of records participating in aggregate is R2, and the number of records participating in shuffle is R3, then the second performance gain metric is: c1*R1*F*(BA)+c2*R2*F*(BA)+c3*R3*F*(BA).

[0138] S302. Determine whether to enable dictionary-based optimization based on the predicted cost metric.

[0139] In this embodiment, the prediction cost index represents whether the benefits brought by the dictionary are greater than the cost it introduces. If the performance cost index in the prediction cost index is less than the performance benefit index, dictionary-based optimization can be enabled. If the performance cost index in the prediction cost index is greater than or equal to the performance benefit index, dictionary-based optimization is not enabled.

[0140] Another embodiment of this disclosure provides a database processing method that determines whether to enable dictionary-based optimization of the predicted cost index for the target column associated with the operation command, and determines whether to enable dictionary-based optimization based on the predicted cost index. By considering the performance overhead and performance benefit indices of the target column after dictionary-based optimization, the predicted cost index is obtained, and then a judgment is made based on the predicted cost index. By comprehensively considering the performance benefit and overhead, the method avoids the situation where configuring the dictionary leads to a decrease in performance, realizes automatic judgment on whether to enable dictionary-based optimization, improves the database operation performance, and requires no user intervention or user provision and maintenance of the dictionary, thus reducing user costs.

[0141] Corresponding to the database processing method in the above embodiments, Figure 5 This is a structural block diagram of a database processing device provided in an embodiment of this disclosure. For ease of explanation, only the parts relevant to the embodiments of this disclosure are shown. (Refer to...) Figure 5 The database processing device 40 includes: a decision-making unit 401, a dictionary acquisition unit 402, an encoding unit 403, and an execution unit 404.

[0142] Among them, the decision unit 401 is used to receive operation instructions for the database and determine whether dictionary-based optimization should be enabled for the target column associated with the operation instructions;

[0143] The dictionary acquisition unit 402 is used to acquire the dictionary corresponding to the target column if it is determined that dictionary-based optimization is enabled, wherein the dictionary includes the mapping relationship between the values ​​included in the target column and the corresponding replacement values;

[0144] Encoding unit 403 is used to encode the target column according to the dictionary so as to replace the values ​​included in the target column with the corresponding replacement values;

[0145] The execution unit 404 is used to convert the operation instructions into operation instructions corresponding to the encoded target column according to the dictionary, execute the converted operation instructions, and decode the operation results according to the dictionary.

[0146] In one or more embodiments of this disclosure, the dictionary acquisition unit 402 is further configured to:

[0147] Retrieve the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, retrieve all the distinct values ​​in the target column and construct a dictionary based on these values; and / or

[0148] Based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, obtain the different values ​​included in the target column, and construct a dictionary based on some or all of the different values.

[0149] In one or more embodiments of this disclosure, the dictionary acquisition unit 402 is further configured to:

[0150] Generate a first execution plan based on the operation instructions, and determine the types of operators included in the first execution plan;

[0151] If the operator type includes a scan operator, then obtain the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, then obtain all the distinct values ​​in the target column and construct a first dictionary based on the distinct values ​​in the target column; and / or

[0152] If the operator type includes filter operators or join operators, then obtain the distinct values ​​included in the target column based on the predicates in the operators, and construct a second dictionary based on the distinct values ​​included in the target column; and / or

[0153] If the operator type includes projection operators, then the different values ​​included in the target column are obtained according to the functions in the operator, and a third dictionary is constructed based on the different values ​​included in the target column.

[0154] In one or more embodiments of this disclosure, the dictionary acquisition unit 402 is further configured to:

[0155] If the number of distinct values ​​is less than a preset threshold, a second execution plan for querying the distinct values ​​included in the target column is sent to the database execution engine, and the distinct values ​​included in the target column are returned by the execution engine according to the second execution plan.

[0156] In one or more embodiments of this disclosure, the dictionary acquisition unit 402 is further configured to:

[0157] Determine if the dictionary is stored in the cache;

[0158] If the dictionary is not stored in the cache, or if the dictionary in the cache has expired, then create the dictionary and store it in the cache; or

[0159] If the dictionary is stored in the cache and has not expired, then the dictionary is retrieved from the cache.

[0160] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0161] Determine whether to enable dictionary-based optimized prediction cost metrics for the target columns associated with the operation instructions, and determine whether to enable dictionary-based optimization based on the prediction cost metrics.

[0162] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0163] Determine the dictionary-based optimized performance overhead and performance benefit metrics for the target column associated with the operation command, and determine the predicted cost metric based on the performance overhead and performance benefit metrics.

[0164] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0165] Determine the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results;

[0166] Determine the dictionary-based performance gains for the target columns associated with the operation instructions, including:

[0167] Determine the primary performance gain metric resulting from changes in data width and / or data type due to dictionary encoding of the target column's values; and / or

[0168] Determine the second performance benefit metric generated by the target column during database operations after the target column is encoded according to the dictionary.

[0169] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0170] Determine the first amount of data to encode the target column according to the dictionary, and the second amount of data to decode the operation result;

[0171] The first performance threshold of the encoding process is determined by using a preset encoding complexity function based on the first data volume, and the second performance threshold of the decoding process is determined by using a preset decoding complexity function based on the second data volume.

[0172] The sum of the first performance cost and the second performance cost is defined as the performance overhead metric.

[0173] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0174] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0175] The first target coefficient is determined based on the first data type and the second data type;

[0176] The product of the difference between the first average data width and the second average data width and the first target coefficient is obtained, and the first performance benefit index is determined based on the product.

[0177] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0178] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0179] The first target coefficient is determined based on the first data type and the second data type;

[0180] Determine the types of operations and the number of records involved in the database operations performed on the target column after encoding it according to the dictionary; determine the second target coefficient based on the types of operations.

[0181] The product of the difference between the first average data width and the second average data width and the first target coefficient, the second target coefficient, and the number of records is obtained, and the second performance benefit index is determined based on the product.

[0182] In one or more embodiments of this disclosure, decision unit 401 is further configured to:

[0183] If the target column is encoded according to the dictionary and then participates in multiple database operations, the second performance benefit index generated in each database operation is obtained and accumulated to obtain the accumulated second performance benefit index.

[0184] The device provided in this embodiment can be used to execute the technical solutions of the above method embodiments. Its implementation principle and technical effect are similar, and will not be described again here.

[0185] refer to Figure 6 The diagram illustrates a structural schematic of an electronic device 500 suitable for implementing embodiments of the present disclosure. The electronic device 500 can be a terminal device or a server. The terminal device can include, but is not limited to, mobile terminals such as mobile phones, laptops, digital radio receivers, personal digital assistants (PDAs), portable Android devices (PADs), portable media players (PMPs), and in-vehicle terminals (e.g., in-vehicle navigation terminals), as well as fixed terminals such as digital TVs and desktop computers. Figure 6 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of the embodiments disclosed herein.

[0186] like Figure 6 As shown, the electronic device 500 may include a processing unit (e.g., a central processing unit, a graphics processing unit, etc.) 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage device 508 into a random access memory (RAM) 503. The RAM 503 also stores various programs and data required for the operation of the electronic device 500. The processing unit 501, ROM 502, and RAM 503 are interconnected via a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0187] Typically, the following devices can be connected to I / O interface 505: input devices 506 including, for example, touchscreens, touchpads, keyboards, mice, cameras, microphones, accelerometers, gyroscopes, etc.; output devices 507 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices 508 including, for example, magnetic tapes, hard disks, etc.; and communication devices 509. Communication device 509 allows electronic device 500 to communicate wirelessly or wiredly with other devices to exchange data. Although Figure 6 An electronic device 500 with various devices is shown; however, it should be understood that it is not required to implement or possess all of the devices shown. More or fewer devices may be implemented or possessed alternatively.

[0188] In particular, according to embodiments of this disclosure, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of this disclosure include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device 509, or installed from a storage device 508, or installed from a ROM 502. When the computer program is executed by the processing device 501, it performs the functions defined in the methods of embodiments of this disclosure.

[0189] It should be noted that the computer-readable medium described in this disclosure can be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium can be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this disclosure, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in connection with an instruction execution system, apparatus, or device. In this disclosure, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium can be any computer-readable medium other than a computer-readable storage medium, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted using any suitable medium, including but not limited to: wires, optical fibers, RF (radio frequency), etc., or any suitable combination thereof.

[0190] The aforementioned computer-readable medium may be included in the aforementioned electronic device; or it may exist independently and not assembled into the electronic device.

[0191] The aforementioned computer-readable medium carries one or more programs, which, when executed by the electronic device, cause the electronic device to perform the methods shown in the above embodiments.

[0192] Computer program code for performing the operations of this disclosure can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a Local Area Network (LAN) or a Wide Area Network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0193] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0194] The units described in the embodiments of this disclosure can be implemented in software or in hardware. The name of a unit does not necessarily limit the unit itself; for example, the first acquisition unit can also be described as "a unit that acquires at least two Internet Protocol addresses".

[0195] The functions described above in this document can be performed, at least in part, by one or more hardware logic components. For example, exemplary types of hardware logic components that can be used, without limitation, include: Field Programmable Gate Arrays (FPGAs), Application-Specific Integrated Circuits (ASICs), Application Standard Products (ASSPs), System-on-Chip (SoCs), Complex Programmable Logic Devices (CPLDs), and so on.

[0196] In the context of this disclosure, a machine-readable medium can be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0197] In a first aspect, according to one or more embodiments of this disclosure, a database processing method is provided, comprising:

[0198] Receive database operation instructions and determine whether dictionary-based optimization is enabled for the target column associated with the operation instructions;

[0199] If dictionary-based optimization is enabled, the dictionary corresponding to the target column is obtained, which includes the mapping relationship between the values ​​in the target column and the corresponding replacement values;

[0200] The target column is encoded according to a dictionary so that the values ​​included in the target column are replaced with the corresponding replacement values;

[0201] The operation instructions are converted into operation instructions corresponding to the encoded target columns according to the dictionary, the converted operation instructions are executed, and the operation results are decoded according to the dictionary.

[0202] According to one or more embodiments of this disclosure, obtaining the dictionary corresponding to the target column includes:

[0203] Retrieve the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, retrieve all the distinct values ​​in the target column and construct a dictionary based on these values; and / or

[0204] Based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, obtain the different values ​​included in the target column, and construct a dictionary based on some or all of the different values.

[0205] According to one or more embodiments of this disclosure, obtaining the dictionary corresponding to the target column includes:

[0206] Generate a first execution plan based on the operation instructions, and determine the types of operators included in the first execution plan;

[0207] If the operator type includes a scan operator, then obtain the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, then obtain all the distinct values ​​in the target column and construct a first dictionary based on the distinct values ​​in the target column; and / or

[0208] If the operator type includes filter operators or join operators, then obtain the distinct values ​​included in the target column based on the predicates in the operators, and construct a second dictionary based on the distinct values ​​included in the target column; and / or

[0209] If the operator type includes projection operators, then the different values ​​included in the target column are obtained according to the functions in the operator, and a third dictionary is constructed based on the different values ​​included in the target column.

[0210] According to one or more embodiments of this disclosure, if the number of different values ​​is less than a preset threshold, the different values ​​included in the target column are obtained, including:

[0211] If the number of distinct values ​​is less than a preset threshold, a second execution plan for querying the distinct values ​​included in the target column is sent to the database execution engine, and the distinct values ​​included in the target column are returned by the execution engine according to the second execution plan.

[0212] According to one or more embodiments of this disclosure, obtaining the dictionary corresponding to the target column includes:

[0213] Determine if the dictionary is stored in the cache;

[0214] If the dictionary is not stored in the cache, or if the dictionary in the cache has expired, then create the dictionary and store it in the cache; or

[0215] If the dictionary is stored in the cache and has not expired, then the dictionary is retrieved from the cache.

[0216] According to one or more embodiments of this disclosure, determining whether dictionary-based optimization is enabled for a target column associated with an operation instruction includes:

[0217] Determine whether to enable dictionary-based optimized prediction cost metrics for the target columns associated with the operation instructions, and determine whether to enable dictionary-based optimization based on the prediction cost metrics.

[0218] According to one or more embodiments of this disclosure, determining to enable a dictionary-optimized prediction cost metric for a target column associated with an operation instruction includes:

[0219] Determine the dictionary-based optimized performance overhead and performance benefit metrics for the target column associated with the operation command, and determine the predicted cost metric based on the performance overhead and performance benefit metrics.

[0220] According to one or more embodiments of this disclosure, determining the dictionary-based optimized performance overhead metric for a target column associated with an operation instruction includes:

[0221] Determine the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results;

[0222] Determine the dictionary-based performance gains for the target columns associated with the operation instructions, including:

[0223] Determine the primary performance gain metric resulting from changes in data width and / or data type due to dictionary encoding of the target column's values; and / or

[0224] Determine the second performance benefit metric generated by the target column during database operations after the target column is encoded according to the dictionary.

[0225] According to one or more embodiments of this disclosure, determining the performance overhead metrics for encoding a target column according to a dictionary and decoding the operation result includes:

[0226] Determine the first amount of data to encode the target column according to the dictionary, and the second amount of data to decode the operation result;

[0227] The first performance threshold of the encoding process is determined by using a preset encoding complexity function based on the first data volume, and the second performance threshold of the decoding process is determined by using a preset decoding complexity function based on the second data volume.

[0228] The sum of the first performance cost and the second performance cost is defined as the performance overhead metric.

[0229] According to one or more embodiments of this disclosure, determining a first performance gain metric arising from changes in data width and / or data type of values ​​included in a target column after encoding the target column according to a dictionary includes:

[0230] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0231] The first target coefficient is determined based on the first data type and the second data type;

[0232] The product of the difference between the first average data width and the second average data width and the first target coefficient is obtained, and the first performance benefit index is determined based on the product.

[0233] According to one or more embodiments of this disclosure, a second performance gain metric is determined based on the database operations performed by the target column after it has been encoded according to a dictionary, including:

[0234] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0235] The first target coefficient is determined based on the first data type and the second data type;

[0236] Determine the types of operations and the number of records involved in the database operations performed on the target column after encoding it according to the dictionary; determine the second target coefficient based on the types of operations.

[0237] The product of the difference between the first average data width and the second average data width and the first target coefficient, the second target coefficient, and the number of records is obtained, and the second performance benefit index is determined based on the product.

[0238] According to one or more embodiments of this disclosure, a second performance gain metric is determined based on the database operations performed by the target column after it has been encoded according to a dictionary, including:

[0239] If the target column is encoded according to the dictionary and then participates in multiple database operations, the second performance benefit index generated in each database operation is obtained and accumulated to obtain the accumulated second performance benefit index.

[0240] Secondly, according to one or more embodiments of this disclosure, a database processing device is provided, comprising:

[0241] The decision unit is used to receive operation instructions for the database and determine whether dictionary-based optimization should be enabled for the target column associated with the operation instruction.

[0242] The dictionary acquisition unit is used to acquire the dictionary corresponding to the target column if it is determined that dictionary-based optimization is enabled. The dictionary includes the mapping relationship between the values ​​in the target column and the corresponding replacement values.

[0243] The encoding unit is used to encode the target column according to the dictionary, so as to replace the values ​​included in the target column with the corresponding replacement values;

[0244] The execution unit is used to convert operation instructions into operation instructions corresponding to the encoded target column according to the dictionary, execute the converted operation instructions, and decode the operation results according to the dictionary.

[0245] According to one or more embodiments of this disclosure, the dictionary acquisition unit is further configured to:

[0246] Retrieve the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, retrieve all the distinct values ​​in the target column and construct a dictionary based on these values; and / or

[0247] Based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, obtain the different values ​​included in the target column, and construct a dictionary based on some or all of the different values.

[0248] According to one or more embodiments of this disclosure, the dictionary acquisition unit is further configured to:

[0249] Generate a first execution plan based on the operation instructions, and determine the types of operators included in the first execution plan;

[0250] If the operator type includes a scan operator, then obtain the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, then obtain all the distinct values ​​in the target column and construct a first dictionary based on the distinct values ​​in the target column; and / or

[0251] If the operator type includes filter operators or join operators, then obtain the distinct values ​​included in the target column based on the predicates in the operators, and construct a second dictionary based on the distinct values ​​included in the target column; and / or

[0252] If the operator type includes projection operators, then the different values ​​included in the target column are obtained according to the functions in the operator, and a third dictionary is constructed based on the different values ​​included in the target column.

[0253] According to one or more embodiments of this disclosure, the dictionary acquisition unit is further configured to:

[0254] If the number of distinct values ​​is less than a preset threshold, a second execution plan for querying the distinct values ​​included in the target column is sent to the database execution engine, and the distinct values ​​included in the target column are returned by the execution engine according to the second execution plan.

[0255] According to one or more embodiments of this disclosure, the dictionary acquisition unit is further configured to:

[0256] Determine if the dictionary is stored in the cache;

[0257] If the dictionary is not stored in the cache, or if the dictionary in the cache has expired, then create the dictionary and store it in the cache; or

[0258] If the dictionary is stored in the cache and has not expired, then the dictionary is retrieved from the cache.

[0259] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0260] Determine whether to enable dictionary-based optimized prediction cost metrics for the target columns associated with the operation instructions, and determine whether to enable dictionary-based optimization based on the prediction cost metrics.

[0261] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0262] Determine the dictionary-based optimized performance overhead and performance benefit metrics for the target column associated with the operation command, and determine the predicted cost metric based on the performance overhead and performance benefit metrics.

[0263] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0264] Determine the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results;

[0265] Determine the dictionary-based performance gains for the target columns associated with the operation instructions, including:

[0266] Determine the primary performance gain metric resulting from changes in data width and / or data type due to dictionary encoding of the target column's values; and / or

[0267] Determine the second performance benefit metric generated by the target column during database operations after the target column is encoded according to the dictionary.

[0268] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0269] Determine the first amount of data to encode the target column according to the dictionary, and the second amount of data to decode the operation result;

[0270] The first performance threshold of the encoding process is determined by using a preset encoding complexity function based on the first data volume, and the second performance threshold of the decoding process is determined by using a preset decoding complexity function based on the second data volume.

[0271] The sum of the first performance cost and the second performance cost is defined as the performance overhead metric.

[0272] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0273] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0274] The first target coefficient is determined based on the first data type and the second data type;

[0275] The product of the difference between the first average data width and the second average data width and the first target coefficient is obtained, and the first performance benefit index is determined based on the product.

[0276] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0277] Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values;

[0278] The first target coefficient is determined based on the first data type and the second data type;

[0279] Determine the types of operations and the number of records involved in the database operations performed on the target column after encoding it according to the dictionary; determine the second target coefficient based on the types of operations.

[0280] The product of the difference between the first average data width and the second average data width and the first target coefficient, the second target coefficient, and the number of records is obtained, and the second performance benefit index is determined based on the product.

[0281] According to one or more embodiments of this disclosure, the decision unit is further configured to:

[0282] If the target column is encoded according to the dictionary and then participates in multiple database operations, the second performance benefit index generated in each database operation is obtained and accumulated to obtain the accumulated second performance benefit index.

[0283] Thirdly, according to one or more embodiments of the present disclosure, an electronic device is provided, comprising: at least one processor and a memory;

[0284] The memory stores the instructions that the computer executes;

[0285] At least one processor executes computer execution instructions stored in memory, causing at least one processor to perform the database processing method as described in the first aspect above and various possible designs of the first aspect.

[0286] Fourthly, according to one or more embodiments of the present disclosure, a computer-readable storage medium is provided, which stores computer-executable instructions that, when executed by a processor, implement the database processing method described in the first aspect and various possible designs of the first aspect.

[0287] Fifthly, according to one or more embodiments of the present disclosure, a computer program product is provided, including computer execution instructions, which, when executed by a processor, implement the database processing method described above as a first aspect and various possible designs of the first aspect.

[0288] The above description is merely a preferred embodiment of this disclosure and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this disclosure is not limited to technical solutions formed by specific combinations of the above-described technical features, but should also cover other technical solutions formed by arbitrary combinations of the above-described technical features or their equivalents without departing from the above-described concept. For example, technical solutions formed by substituting the above features with (but not limited to) technical features disclosed in this disclosure that have similar functions.

[0289] Furthermore, while the operations are described in a specific order, this should not be construed as requiring these operations to be performed in the specific order shown or in a sequential order. In certain environments, multitasking and parallel processing may be advantageous. Similarly, while several specific implementation details are included in the above discussion, these should not be construed as limiting the scope of this disclosure. Certain features described in the context of individual embodiments may also be implemented in combination in a single embodiment. Conversely, various features described in the context of a single embodiment may also be implemented individually or in any suitable sub-combination in multiple embodiments.

[0290] Although the subject matter has been described using language specific to structural features and / or methodological logic, it should be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or actions described above. Rather, the specific features and actions described above are merely illustrative examples of implementing the claims.

Claims

1. A database processing method, characterized in that, include: Receive an operation instruction for the database and determine to enable dictionary-based optimized prediction cost metric for the target column associated with the operation instruction. If the prediction cost metric indicates that the benefit brought by the dictionary is greater than the cost introduced by the dictionary, then determine to enable dictionary-based optimization. The predicted cost metric is determined by the difference between the performance overhead metric and the performance benefit metric; the performance overhead metric represents the optimized resource usage based on the dictionary. The performance gain metric represents the performance improvement brought about by dictionary-based optimization; If dictionary-based optimization is enabled, the dictionary corresponding to the target column is obtained, wherein the dictionary includes the mapping relationship between the values ​​included in the target column and the corresponding replacement values; wherein obtaining the dictionary corresponding to the target column includes: obtaining some or all values ​​of the different values ​​included in the target column in a way that adapts to the operators and / or functions included in the first execution plan corresponding to the operation instruction, and constructing a dictionary based on the obtained different values ​​included in the target column; wherein the operators include scan operators, filter operators, join operators, and projection operators; The target column is encoded according to the dictionary so that the values ​​included in the target column are replaced with corresponding replacement values; The operation instructions are converted into operation instructions corresponding to the encoded target columns according to the dictionary, the converted operation instructions are executed, and the operation results are decoded according to the dictionary.

2. The method according to claim 1, characterized in that, The step of obtaining the dictionary corresponding to the target column includes: Obtain the number of distinct values ​​in the target column of the database. If the number of distinct values ​​is less than a preset threshold, then obtain all the distinct values ​​in the target column and construct the dictionary based on the distinct values ​​in the target column; and / or Based on the operators and / or functions included in the first execution plan corresponding to the operation instruction, obtain the different values ​​included in the target column, and construct the dictionary based on some or all of the different values.

3. The method according to claim 1, characterized in that, The step of obtaining the dictionary corresponding to the target column includes: A first execution plan is generated based on the operation instructions, and the types of operators included in the first execution plan are determined. If the operator type includes a scan operator, then the number of distinct values ​​included in the target column of the database is obtained. If the number of distinct values ​​is less than a preset threshold, then the distinct values ​​included in the target column are obtained, and a first dictionary is constructed based on the distinct values ​​included in the target column; and / or If the operator type includes filter operators or join operators, then the different values ​​included in the target column are obtained according to the predicates in the operators, and a second dictionary is constructed based on the different values ​​included in the target column; and / or If the operator type includes projection operators, then the different values ​​included in the target column are obtained according to the functions in the operators, and a third dictionary is constructed according to the different values ​​included in the target column.

4. The method according to claim 2 or 3, characterized in that, If the number of distinct values ​​is less than a preset threshold, then the distinct values ​​included in the target column are obtained, including: If the number of different values ​​is less than a preset threshold, a second execution plan for querying the different values ​​included in the target column is sent to the execution engine of the database, and the different values ​​included in the target column are returned by the execution engine according to the second execution plan.

5. The method according to any one of claims 1-3, characterized in that, The step of obtaining the dictionary corresponding to the target column includes: Determine whether the dictionary is stored in the cache; If the dictionary is not stored in the cache, or if the dictionary in the cache has expired, then create the dictionary and store it in the cache; or If the dictionary is stored in the cache and it has not expired, then the dictionary is retrieved from the cache.

6. The method according to claim 1, characterized in that, The step of determining whether to enable the dictionary-optimized prediction cost metric for the target column associated with the operation instruction includes: Determine the dictionary-based optimized performance overhead and performance benefit metrics for the target column associated with the operation instruction, and determine the predicted cost metric based on the performance overhead and performance benefit metrics.

7. The method according to claim 6, characterized in that, The step of determining whether to enable dictionary-based optimized performance overhead metrics for the target column associated with the operation instruction includes: Determine the performance overhead metrics for encoding the target column according to the dictionary and decoding the operation results; The step of determining whether to enable dictionary-based optimized performance metrics for the target column associated with the operation instruction includes: Determine a first performance gain metric resulting from changes in data width and / or data type in the values ​​included in the target column after encoding the target column according to the dictionary; and / or Determine the second performance benefit metric generated by the target column during database operations after the target column is encoded according to the dictionary.

8. The method according to claim 7, characterized in that, The determination of the performance overhead metrics resulting from encoding the target column according to the dictionary and decoding the operation results includes: Determine a first amount of data to encode the target column according to the dictionary, and a second amount of data to decode the operation result; The first performance threshold of the encoding process is determined by using a preset encoding complexity function based on the first data volume, and the second performance threshold of the decoding process is determined by using a preset decoding complexity function based on the second data volume. The sum of the first performance overhead and the second performance overhead is determined as the performance overhead metric.

9. The method according to claim 7, characterized in that, The determination of the first performance gain metric resulting from changes in data width and / or data type due to the encoding of the target column according to the dictionary includes: Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values; The first target coefficient is determined based on the first data type and the second data type; The product of the difference between the first average data width and the second average data width and the first target coefficient is obtained, and the first performance gain index is determined based on the product.

10. The method according to claim 7, characterized in that, The determination of the second performance gain metric generated by the target column during database operations after encoding the target column according to the dictionary includes: Determine the first average data width and first data type of the values ​​included in the target column, and determine the second average data width and second data type of the corresponding replacement values; The first target coefficient is determined based on the first data type and the second data type; Determine the types of operations and the number of records involved in the database operation process after encoding the target column according to the dictionary; determine the second target coefficient based on the types of operations; The product of the difference between the first average data width and the second average data width and the first target coefficient, the second target coefficient and the number of records is obtained, and the second performance benefit index is determined based on the product.

11. The method according to claim 7 or 8, characterized in that, The determination of the second performance gain metric generated by the target column during database operations after encoding the target column according to the dictionary includes: If the target column is encoded according to the dictionary and then participates in multiple database operation processes, the second performance benefit index generated in each database operation process is obtained and accumulated to obtain the accumulated second performance benefit index.

12. A database processing device, characterized in that, include: The decision unit is used to receive operation instructions for the database and determine to enable dictionary-based optimized prediction cost indicators for the target column associated with the operation instructions. If the prediction cost indicator indicates that the benefits brought by the dictionary are greater than the overhead introduced by the dictionary, then it is determined to enable dictionary-based optimization. The predicted cost metric is determined by the difference between the performance overhead metric and the performance benefit metric; the performance overhead metric represents the optimized resource usage based on the dictionary. The performance gain metric represents the performance improvement brought about by dictionary-based optimization; A dictionary acquisition unit is configured to, if it is determined that dictionary-based optimization is enabled, acquire the dictionary corresponding to the target column, wherein the dictionary includes a mapping relationship between the values ​​included in the target column and the corresponding replacement values; wherein acquiring the dictionary corresponding to the target column includes: acquiring some or all values ​​of the different values ​​included in the target column in a manner adapted to the operators and / or functions included in the first execution plan corresponding to the operation instruction, and constructing a dictionary based on the acquired different values ​​included in the target column; wherein the operators include scan operators, filter operators, join operators, and projection operators; An encoding unit is used to encode the target column according to the dictionary, so as to replace the values ​​included in the target column with corresponding replacement values; An execution unit is configured to convert the operation instruction into an operation instruction corresponding to the encoded target column according to the dictionary, execute the converted operation instruction, and decode the operation result according to the dictionary.

13. An electronic device, characterized in that, include: At least one processor and memory; The memory stores computer-executed instructions; The at least one processor executes computer execution instructions stored in the memory, causing the at least one processor to perform the method as described in any one of claims 1-11.

14. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, implement the method as described in any one of claims 1-11.

15. A computer program product, characterized in that, Includes computer execution instructions, which, when executed by a processor, implement the method as described in any one of claims 1-11.

Images