Database processing method and device and storage medium
By automatically judging and applying dictionary optimization, the problems of high maintenance costs and unstable performance in the existing technology are solved, and database operation performance is improved and resources are efficiently utilized.
Patent Information
- Application Number
- CN202411141095.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-19
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2044-08-19
AI Technical Summary
In the prior art, dictionary optimization has problems such as high manual maintenance costs for users, unstable query performance, inability to automatically judge the necessity of optimization, and inability to encode intermediate results during database processing, resulting in waste of resources and performance fallback.
By receiving database operation instructions, we automatically determine whether dictionary optimization is enabled for target columns, obtain dictionary of target columns, encode and decode, optimize operation instructions to improve query performance, and evaluate optimization overhead and benefits through predictive cost models to avoid blindly using dictionaries.
It reduces user maintenance costs, improves database operation performance, avoids query performance fallback, and realizes dictionary encoding optimization of intermediate results, improving the overall efficiency of database processing.
Smart Images

Figure CN119066089A_ABST
Abstract
Description
Technical Field
[0001] The embodiments of the present disclosure relate to the field of computer and network communication technology, and in particular, to a database processing method, device, and storage medium. Background Art
[0002] Dictionary encoding / decoding can be used to optimize query performance during the process of querying or scanning the database. For example, if the type of a data column is a string (such as varchar(30)), and it is known that the value of this column can only have three possible values: "aaa", "bbb", and "ccc", then at the beginning of the query processing, dictionary encoding can be performed on this column, such as replacing "aaa" with an integer 0, "bbb" with an integer 1, and "ccc" with an integer 2, and the query instructions are also replaced accordingly during the query processing; at the final stage of the query processing, the query results can be decoded, replacing 0 with "aaa", 1 with "bbb", and 2 with "ccc", thereby obtaining the same result as the original query. The entire query process is based on the replacement value, which can optimize the query performance.
[0003] In the prior art, the use of dictionaries in the database processing process is controlled by the user, which causes additional resource overhead, increases user maintenance costs, and may cause database query performance to deteriorate. Summary of the invention
[0004] The embodiments of the present disclosure provide a database processing method, device and storage medium to improve database operation performance and reduce user maintenance costs.
[0005] In a first aspect, an embodiment of the present disclosure provides a database processing method, including:
[0006] Receive an operation instruction for a database, and determine whether dictionary-based optimization is enabled for a target column associated with the operation instruction;
[0007] If it is determined to enable dictionary-based optimization, a dictionary corresponding to the target column is obtained, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values;
[0008] encoding the target column according to the dictionary to replace values included in the target column with corresponding replacement values;
[0009] 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.
[0010] In a second aspect, an embodiment of the present disclosure provides a database processing device, including:
[0011] A decision unit, used for receiving an operation instruction on a database, and determining whether dictionary-based optimization is enabled for a target column associated with the operation instruction;
[0012] A dictionary acquisition unit, configured to acquire a dictionary corresponding to the target column if it is determined to enable dictionary-based optimization, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values;
[0013] an encoding unit, configured to encode the target column according to the dictionary so as to replace the values included in the target column with corresponding replacement values;
[0014] The execution unit is used to convert the operation instruction into the 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.
[0015] In a third aspect, an embodiment of the present disclosure provides an electronic device, including: at least one processor and a memory;
[0016] Memory stores computer-executable instructions;
[0017] At least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the database processing method of the first aspect and various possible designs of the first aspect as described above.
[0018] In a fourth aspect, an embodiment of the present disclosure provides a computer-readable storage medium, in which computer execution instructions are stored. When a processor executes the computer execution instructions, the database processing method as described in the first aspect and various possible designs of the first aspect is implemented.
[0019] In a fifth aspect, an embodiment of the present disclosure provides a computer program product, including computer execution instructions. When a processor executes the computer execution instructions, it implements the database processing method as described in the first aspect and various possible designs of the first aspect.
[0020] The database processing method, device and storage medium provided by the embodiments of the present disclosure receive operation instructions for a database, and determine whether dictionary-based optimization is enabled for a target column associated with the operation instructions. If it is determined that dictionary-based optimization is enabled, a dictionary corresponding to the target column is obtained, the target column is encoded according to the dictionary, so as to replace the value included in the target column with a corresponding replacement value, and 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. The embodiments of the present disclosure can automatically determine whether dictionary-based optimization is enabled when operating a model database, and then obtain a dictionary, thereby reducing user maintenance costs and improving database operation performance. BRIEF DESCRIPTION OF THE DRAWINGS
[0021] In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0022] Figure 1 A schematic diagram of a scenario of a database processing method provided by an embodiment of the present disclosure;
[0023] Figure 2 A flowchart of a database processing method provided by an embodiment of the present disclosure;
[0024] Figure 3 A flowchart of maintaining a dictionary provided by an embodiment of the present disclosure;
[0025] Figure 4 A flowchart of a database processing method provided by another embodiment of the present disclosure;
[0026] Figure 5 A structural block diagram of a database processing device provided in one embodiment of the present disclosure;
[0027] Figure 6 A schematic diagram of the hardware structure of an electronic device provided in one embodiment of the present disclosure. DETAILED DESCRIPTION
[0028] In order to make the purpose, technical solution and advantages of the embodiments of the present disclosure clearer, the technical solution in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are part of the embodiments of the present disclosure, not all of the embodiments. Based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present 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, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.
[0030] In order to clearly understand the technical solution of the present application, the solution of the prior art is first introduced in detail.
[0031] Dictionary encoding / decoding can be used to optimize query performance during the process of querying or scanning the database. For example, if the type of a certain data column in the database is a string (such as varchar(30)), and it is known that the value of this column can only have three possible values: "aaa", "bbb", and "ccc", then at the beginning of the query processing, dictionary encoding can be performed on this column. For example, the integer 0 is used to replace "aaa", 1 is used to replace "bbb", and 2 is used to replace "ccc", and the query instructions are also replaced accordingly during the query processing; at the final stage of the query processing, the results can be decoded, and 0 is replaced with "aaa", 1 is replaced with "bbb", and 2 is replaced with "ccc", so as to obtain the same result as the original query. The entire query process is based on the replacement value, which can optimize the query performance.
[0032] After dictionary encoding optimization, the query execution efficiency will be significantly improved. This is because:
[0033] 1. The amount of data processed by the query is significantly reduced. For example, in the above example, 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 integers and fixed-length data types is much higher than the efficiency of processing variable-length data types.
[0035] Since dictionary encoding is very helpful in improving the performance of database queries, this technology has been widely used in multiple database products to help improve query performance.
[0036] However, the current dictionary optimization technology has the following problems:
[0037] 1. The content of the dictionary 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. The existing solutions for updating the dictionary are as follows, but they all have certain problems:
[0039] a. Users are required to manually update the dictionary content, so it is not automated enough;
[0040] b. You can configure the dictionary update cycle to update the dictionary content regularly, but the dictionary obtained in this way may not be the latest;
[0041] c. You can dynamically obtain the contents of the dictionary in real time during a query using the dictionary, but this method will have performance overhead, especially when the data has not changed and there is no need to regenerate the dictionary.
[0042] 3. After the introduction of dictionary encoding optimization, query performance will not necessarily improve. For example, if the dictionary is too large, the encoding and decoding overhead will also increase, which may cause query performance to decline. For example, if the database has 100 rows and a data column has 100 different values, then the dictionary needs to include 100 entries, which may not significantly improve query performance, but the encoding and decoding process requires a lot of performance overhead. The existing system cannot distinguish these situations and will use dictionary optimization technology indiscriminately.
[0043] 4. Existing technologies can only perform dictionary encoding on columns on database tables, but cannot encode intermediate results of queries, such as intermediate results of join operations, projection operations, etc., making it impossible to use dictionary encoding optimization for some queries.
[0044] In order to solve at least one of the above technical problems, the embodiment of the present disclosure provides a database processing method, which can automatically determine whether it is necessary to enable dictionary-based optimization for the target column associated with the operation instruction according to the operation instruction of the database. If it is necessary to enable dictionary-based optimization, the dictionary corresponding to the target column is obtained to reduce user maintenance costs and prevent query performance regression.
[0045] The database processing method provided by the embodiment of the present disclosure can be applied to Figure 1 In the application scenario shown, after receiving an operation instruction on the database, it is determined whether dictionary-based optimization is enabled for the target column associated with the operation instruction. If it is determined to enable dictionary-based optimization, 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 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 disclosed herein will be described in detail below in conjunction with specific embodiments.
[0047] refer to Figure 2 , Figure 2 A flowchart of a database processing method provided by 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] S20S201. Receive an operation instruction for a database, and determine whether dictionary-based optimization is enabled for a target column associated with the operation instruction.
[0049] In this embodiment, in order to improve query performance and avoid reducing query performance after using a dictionary, after receiving a user's operation instruction on the database, it is necessary to first measure the query performance of the target column corresponding to the operation instruction after using the dictionary, and determine whether it is necessary to enable dictionary-based optimization based on the measurement results. Then, under the premise of dictionary-based optimization, obtain the dictionary to avoid blindly using the dictionary to reduce 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 target columns in the database.
[0051] S202: If it is determined to enable dictionary-based optimization, obtain a dictionary corresponding to the target column.
[0052] In this embodiment, the dictionary includes a mapping relationship between the values included in the target column and the corresponding replacement values. The replacement value may refer to a value after replacing the value in the target column. Optionally, the length of the replacement value may be shorter than the value in the target column, and / or the data type of the replacement value may be different from the data type of the value in the target column.
[0053] In this embodiment, when it is determined to start dictionary-based optimization, the electronic device may obtain a dictionary corresponding to the target column, so as to encode the data of the target column according to the dictionary.
[0054] In a possible implementation, obtaining the dictionary corresponding to the target column includes the following two methods:
[0055] Obtaining the number of different values included in the target column of the database, if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a dictionary according to the different values included in the target column; and / or
[0056] According to the operator and / or function included in the first execution plan corresponding to the operation instruction, different values included in the target column are obtained, and a dictionary is constructed according to some or all of the values.
[0057] In this embodiment, in the first method of obtaining a dictionary, the electronic device can obtain the number of different values (Number of Distinct Values, NDV) included in the target column. This indicator is an important indicator in the database statistical information, which is used to describe the number of different values included in the target column. For example, the target column may only have three possible values of "aaa", "bbb", and "ccc", then the NDV of the target column = 3. Optionally, the number of different values included in the target column can be queried through an SQL query instruction, or obtained from statistical information maintained by the database system (such as meta metadata information), or obtained using any other feasible method, which is not limited here.
[0058] Furthermore, the number of different values included in the target column can be compared with a preset number threshold. If the number of different values is less than the preset number threshold, it means that the number of different values in the target column is small enough, and the performance overhead of encoding and decoding the dictionary generation is not large. Then, the different values included in the target column can be obtained, and a dictionary can be constructed based on the different values. Automatic dictionary generation is achieved through statistical information, without the need for users to provide dictionary content, thereby reducing user costs.
[0059] Optionally, if the NDV is less than a preset data threshold, an execution plan for querying the different values included in the target column can be sent to the execution engine of the database. For the convenience of description, it is recorded as the second execution plan here, and then the second execution plan is executed by the execution engine to query the different values included in the target column, and receive the different 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; wherein x refers to the value in the target column, and tbl refers to the target column. Of course, any other feasible method can also be used in this embodiment to obtain the different values included in the target column, such as directly sending SQL execution instructions instead of execution plans, etc., which will not be elaborated here.
[0060] Furthermore, when constructing a dictionary based on the different values included in the target column, the values included in the target column are actually mapped to a replacement value, that is, a mapping relationship between the different values included in the target column and the corresponding replacement values is constructed. Optionally, the different values included in the target column can be sorted, and each value corresponds to a replacement value, and the replacement values are different from each other. For example, the different values are (aaa, bbb, ccc), and the mapping relationship constructed in the dictionary is {0:aaa, 1:bbb, 2:ccc}, that is, 0 replaces "aaa", 1 replaces "bbb", and 2 replaces "ccc". In the second way of obtaining a dictionary, according to the operator and / or function included in the first execution plan corresponding to the operation instruction, some or all of the values in the different values included in the target column can be obtained, and a dictionary can be constructed based on some or all of the values in the different values. Automatic dictionary generation is achieved through theoretical deduction, without the need for users to provide dictionary content, thereby reducing user costs.
[0061] The first execution plan may refer to a detailed instruction sequence generated by the optimizer when the database executes an operation instruction.
[0062] An operator may refer to an identifier for operating on the value of a target column, including but not limited to a scan operator (scan), a filter operator (filter), a join operator (join), a projection operator (project), etc. Among them:
[0063] The scan operator (scan) is used to traverse the values of the target column;
[0064] The filter operator is used to select, filter, and modify the values in the target column to ensure that only records that meet specific conditions are selected or displayed;
[0065] The join operator (join) is used to connect two or more tables;
[0066] The projection operator (project) can be used to extract the specified information of the target column.
[0067] Functions can include substr, concat, reverse, trim, ltrim, rtrim, lower, upper, left, right, replace, replicate, etc. in the database.
[0068] According to operators and / or functions
[0069] In a possible implementation manner, for the method of obtaining a dictionary provided in the above embodiment, different methods of obtaining a dictionary may be used in different situations. Specifically, obtaining a dictionary corresponding to a target column may include:
[0070] Generate a first execution plan according to the operation instruction, and determine the type of operator included in the first execution plan;
[0071] If the operator type includes a scan operator, obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a first dictionary according to the different values included in the target column; and / or
[0072] If the operator type includes a filter operator or a join operator, obtaining different values included in the target column according to the predicate in the operator, and constructing a second dictionary according to the different values included in the target column; and / or
[0073] If the operator type includes a projection operator, different values included in the target column are obtained according to a function in the operator, and a third dictionary is constructed according to 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 sizes may also be different, the method of obtaining the dictionary can be selected according to actual needs. Specifically, after the database optimizer generates a first execution plan for the operation instruction, 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 acquisition methods can be used when the operator type is a selection scan operator, a filter operator, a connection operator and / or a projection operation.
[0075] Specifically, if the operator type is a scan operator and the number of different values is less than a preset threshold, a dictionary is 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 as the first dictionary here and can realize encoding and decoding of all values of the target column.
[0076] If the operator type includes a filter operator or a connection operator, it may involve partial values or all values of different values included in the target column, which can be specifically 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 values or all values of different values included in the target column can be obtained according to the predicate in the operator, and the second dictionary can be constructed according to the partial values or all values.
[0077] Exemplarily, if the operator type in the first execution plan includes a filter operator or a join operator, all predicates of the operator are extracted. If the predicate is 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'), that is, the data whose values in the target column x are in the set ('a', 'b', 'c') are filtered. It can be explained that only the three values of 'a', 'b', and 'c' are concerned with the target column, and a dictionary {0:a, 1:b, 2:c} can be constructed. If the predicate is a disjunctive normal form (that is, 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', it also means that only the three values of 'a', 'b', and 'c' are concerned with the target column, and a dictionary {0:a, 1:b, 2:c} can be constructed for the target column x.
[0078] If the operator type in the first execution plan includes a projection operator, all function calls in the operator are extracted, such as the function substr(x,1,2), where the target column x is a string column, and the function represents the calculation of a subsequence of the target column x starting from subscript 1 and having a length of 2. Assuming that the values in the target column x include {'aaa', 'bbb', 'ccc'}, it can be deduced that the values of the subsequence constructed based on the function substr(x,1,2) include {'aa', 'bb', 'cc'}, and thus the dictionary corresponding to the subsequence {0:aa, 1:bb, 2:cc} can be constructed based on this. Of course, functions may also include but are not limited to concat, reverse, trim, ltrim, rtrim, lower, upper, left, right, replace, replicate, etc. in the database, and the usage of each function is not described here one by one.
[0079] S203: Encode the target column according to the dictionary to replace the values included in the target column with 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 corresponding replacement values according to the dictionary, and the specific encoding process is not described in detail here.
[0081] S204 , 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.
[0082] In this embodiment, the operation instruction is converted into an operation instruction corresponding to the encoded target column through a dictionary, that is, if the operation instruction involves a specific value of the target column, it is replaced with a 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 instruction, the join operation can be specified by conversion to perform a join operation on the encoded target column instead of the original target column. Further, 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, so as to realize the encoding and decoding of the target column and improve the query performance.
[0083] The database processing method provided in this embodiment receives an operation instruction for a database, and determines whether dictionary-based optimization is enabled for a target column associated with the operation instruction. If it is determined that dictionary-based optimization is enabled, a dictionary corresponding to the target column is obtained, the target column is encoded according to the dictionary, and the value included in the target column is replaced with a corresponding replacement value. 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. This embodiment realizes automatic determination of whether to enable dictionary-based optimization, and then obtains the dictionary, without user intervention, reducing user maintenance costs, and improving database processing performance. It can be applied to any database operation process, and can also realize dictionary-based optimization of intermediate results.
[0084] In a possible implementation manner, obtaining a dictionary corresponding to the target column in S202 may further include:
[0085] Determine whether there is a dictionary stored in the cache;
[0086] If yes, then create a dictionary and store it in the cache; or
[0087] If there is a dictionary stored in the cache and it has not expired, the dictionary is obtained from the cache.
[0088] In this embodiment, the dictionary generated by statistical information needs to send an execution plan to the execution engine, so there is a certain performance overhead, while the dictionary obtained by theoretical deduction has almost no performance overhead. Therefore, in order to reduce the performance overhead of generating a dictionary according to statistical information, it is necessary to cache the dictionary generated by statistical means in the target column. Optionally, the cache uses a data structure in key-value (data is stored in key-value pairs) format. The key (keyword) includes not only the name of the table and the target column, but also the version number of the table used to generate the dictionary, where the table is the table where the target column is located.
[0089] Figure 3A flowchart of maintaining a dictionary provided for an embodiment of the present disclosure, when an operation instruction is received and the dictionary corresponding to the target column needs to be obtained, it can be checked whether the corresponding dictionary is stored in the cache. If not, a dictionary is created. If so, the data version number of the target column is compared with the data version number of the dictionary in the cache. If the version number comparison result is different, it means that the data of the target column has been updated, and the original dictionary has expired and is no longer applicable, so the dictionary needs to be recreated. If it has not expired, the dictionary is obtained from the cache. Automatic detection of the dictionary status is implemented, so that the dictionary is regenerated on demand to ensure that the dictionary and data used for the query always match.
[0090] Optionally, in the above embodiment, after receiving an operation instruction, it can be determined whether the number of different values of 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, check whether the corresponding dictionary is stored in the cache.
[0091] refer to Figure 4 , Figure 4 This is a flowchart of a database processing method provided by another embodiment of the present disclosure. The method of this embodiment can be applied to electronic devices such as servers. Figure 2 Based on the embodiment shown, the database processing method further includes:
[0092] S301 : Determine to enable a dictionary-based optimized prediction cost indicator for a target column associated with an operation instruction.
[0093] In this embodiment, since dictionary encoding optimization does not necessarily lead to performance improvement, a cost model is needed to evaluate the cost and benefit of the execution plan after dictionary optimization is enabled.
[0094] This cost model should take into account the following factors:
[0095] 1. The larger the number of different values (NDV) included in the target column, the greater the cost. This is because the larger the NDV, the larger the dictionary, and the greater the overhead of data encoding and decoding;
[0096] 2. After encoding, the smaller the amount of data obtained is relative to the original amount of data, the greater the performance benefit;
[0097] 3. If the target column is involved in more complex operations, the performance benefit will be greater. If the data is encoded first and then decoded, it will only add extra overhead without any benefit. The complex operations here include:
[0098] a. As a column in a join condition;
[0099] b. As one of the aggregate columns in the aggregate operation;
[0100] c. As part of shuffle (filter) data.
[0101] Based on the above situation, in order to determine the cost after enabling dictionary-based optimization, a predicted cost indicator is used to measure the cost and benefit after dictionary-based optimization, so as to more accurately evaluate whether to enable dictionary-based optimization.
[0102] Specifically, determining to enable the dictionary-based optimized prediction cost indicator for the target column associated with the operation instruction includes:
[0103] It is determined to enable the dictionary-based optimized performance cost indicator and performance benefit indicator for the target column associated with the operation instruction, and the prediction cost indicator is determined according to the performance cost indicator and the performance benefit indicator.
[0104] In this embodiment, the prediction cost index is determined according to the difference between the performance overhead index and the performance benefit index, wherein the performance overhead index may refer to the resource usage after dictionary-based optimization; and the performance benefit index may refer to the performance improvement brought about by dictionary-based optimization.
[0105] Specifically, determining to enable the dictionary-based optimized performance cost indicator for the target column associated with the operation instruction includes:
[0106] Determine the performance overhead of encoding the target column according to the dictionary and decoding the operation result;
[0107] Determine the performance benefit indicators after enabling dictionary-based optimization for the target columns associated with the operation instructions, including:
[0108] Determining a first performance benefit indicator resulting from a change in data width and / or data type for a value included in the target column after encoding the target column according to the dictionary; and / or
[0109] A second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary is determined.
[0110] In this embodiment, the process of encoding the target column and decoding the operation result generates performance overhead, based on which a performance overhead index can be determined. 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, and a first performance benefit index can be determined. Alternatively, the database operation process in which the target column participates can also bring certain benefits due to dictionary-based optimization, and a second performance benefit index can be determined, wherein the first performance benefit index may be zero and the second performance benefit index may also be zero. Therefore, a prediction cost index can be determined based on the performance overhead index and the first performance benefit index, or a prediction cost index can be determined based on the performance overhead index and the second performance benefit index, or a prediction cost index can be determined based on the performance overhead index, the first performance benefit index, and the second performance benefit index.
[0111] Specifically, determining the performance overhead index generated by encoding the target column according to the dictionary and decoding the operation result includes:
[0112] Determining a first data amount for encoding the target column according to the dictionary and a second data amount for decoding the operation result;
[0113] Determine a first performance overhead amount of the encoding process using a preset encoding complexity function according to the first data amount, and determine a second performance overhead amount of the decoding process using a preset decoding complexity function according to the second data amount;
[0114] The sum of the first performance overhead amount and the second performance overhead amount is determined as a performance cost indicator.
[0115] In this embodiment, the overhead of the encoding process is related to the first data amount for encoding the target column according to the dictionary, and is also related to the complexity of the encoding process, so the first performance overhead amount of the encoding process can be characterized based on the first data amount and the complexity of the encoding process. Optionally, the first performance overhead amount can be determined by determining the product of the first encoded data amount and the preset encoding complexity function value. Similarly, the overhead of the decoding process is related to the second data amount for decoding the operation result according to the dictionary, and is also related to the complexity of the decoding process, so the second performance overhead amount of the decoding process can be characterized based on the second data amount and the complexity of the decoding process. Optionally, the second performance overhead amount can be determined by determining the product of the second decoded data amount and the preset decoding complexity function value. The performance overhead index is then determined based on the sum of the first performance overhead amount and the second performance overhead amount.
[0116] Exemplarily, assuming that the amount of first data involved in encoding is E, the amount of second data involved in decoding is D, and the dictionary size is G, the calculation formula of the performance overhead indicator is as follows:
[0117] Performance cost index = E*e(G)+D*d(G)
[0118] Among them, e() and d() are functions that describe the complexity of encoding and decoding respectively. The specific form of the function depends on the encoding and decoding algorithm and data structure used. For example, if a binary search tree is used as the encoding and decoding algorithm, then e(G) = c1*log(G). If the data structure is to use an array to store a dictionary, then d(G) = c2, where c1 and c2 are both constants.
[0119] In the above embodiment, determining the first performance benefit indicator generated by the value included in the target column after encoding the target column according to the dictionary due to the change of data width and / or data type may specifically include:
[0120] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0121] determining a first target coefficient 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 a first performance benefit indicator is determined according to the product.
[0123] The first data type and the second data type may include a variable-length data type or a fixed-length data type.
[0124] Exemplarily, assuming that the first average data width of the values included in the target column is B, for example, for the character data type char(30), B=30; the second average data width of the replacement value in the dictionary is A, for example, for the integer data type int32, A=4, then the formula for the first performance benefit indicator corresponding to a single data record can be:
[0125] First performance benefit index = F*(BA)
[0126] Among them, F is the first target coefficient, where: 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 the first data type and the second data type are both fixed-length data types 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 embodiment, determining the second performance benefit indicator generated in the database operation process in which the target column participates after the target column is encoded according to the dictionary may specifically include:
[0128] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0129] determining a first target coefficient based on the first data type and the second data type;
[0130] Determine the type of operation and the number of records in the database operation process in which the target column participates after encoding the target column according to the dictionary; determine the second target coefficient according to the type of operation;
[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 a second performance benefit indicator is determined according to the product.
[0132] In this embodiment, since the performance benefit index is also related to the complexity of the operation type of the target column participating in the database operation process, in order to consider the factor of the operation type, different second target coefficients are preset for operation types of different complexity. In addition, it is also related to the number of records (that is, the number of rows) participating in the database operation process. Therefore, the second performance benefit index can be obtained according to the difference between the first average data width and the second average data width and the product of the first target coefficient, the second target coefficient and the number of records. The specific formula can be:
[0133] ci*R*F*(BA)
[0134] Among them, ci is the second target coefficient corresponding to the operation type i, where the operation type may include some more complex operations such as join, aggregate, shuffle, etc., and the second target coefficients of different operation types are different; R is the number of records (that is, the number of rows) involved in the database operation process of the target column.
[0135] Optionally, determining a second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary includes:
[0136] If the target column participates in multiple database operation processes after being encoded according to the dictionary, the second performance benefit index generated in each database operation process is obtained respectively and accumulated to obtain an accumulated second performance benefit index.
[0137] Exemplarily, assuming that the first average data width is B, the second average data width is A, the first target coefficient is F, the second target coefficients of 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 benefit indicator is: c1*R1*F*(BA)+c2*R2*F*(BA)+c3*R3*F*(BA).
[0138] S302: Determine whether to enable dictionary-based optimization according to the prediction cost index.
[0139] In this embodiment, the prediction cost indicator represents whether the benefit brought by the dictionary is greater than the overhead it introduces. If the performance overhead indicator in the prediction cost indicator is less than the performance benefit indicator, the dictionary-based optimization can be enabled. If the performance overhead indicator in the prediction cost indicator is greater than or equal to the performance benefit indicator, the dictionary-based optimization is not enabled.
[0140] Another embodiment of the present disclosure provides a database processing method, which determines a predicted cost indicator after enabling dictionary-based optimization for a target column associated with an operation instruction, and determines whether to enable dictionary-based optimization based on the predicted cost indicator, obtains a predicted cost indicator by considering a performance overhead indicator and a performance benefit indicator of the target column after dictionary-based optimization, and then makes a judgment based on the predicted cost indicator, comprehensively considering the performance benefit and overhead conditions, avoiding the situation where performance degradation occurs after the dictionary is configured, and automatically judging whether to enable dictionary-based optimization, thereby improving the database operation performance of the database; and no user intervention is required, and no user provision and maintenance of dictionaries is required, thereby reducing user costs.
[0141] Corresponding to the database processing method of the above embodiment, Figure 5 This is a structural block diagram of a database processing device provided by an embodiment of the present disclosure. For ease of explanation, only the parts related to the embodiment of the present disclosure are shown. Figure 5 The database processing device 40 includes: a decision unit 401, a dictionary acquisition unit 402, an encoding unit 403 and an execution unit 404.
[0142] The decision unit 401 is used to receive an operation instruction for a database, and determine whether to enable dictionary-based optimization for a target column associated with the operation instruction;
[0143] A dictionary acquisition unit 402 is used to acquire a dictionary corresponding to the target column if it is determined to enable dictionary-based optimization, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values;
[0144] An encoding unit 403, configured to encode the target column according to the dictionary to replace the values included in the target column with corresponding replacement values;
[0145] The execution unit 404 is used 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.
[0146] In one or more embodiments of the present disclosure, the dictionary acquisition unit 402 is further configured to:
[0147] Obtaining the number of different values included in the target column of the database, if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a dictionary according to the different values included in the target column; and / or
[0148] According to the operator and / or function included in the first execution plan corresponding to the operation instruction, different values included in the target column are obtained, and a dictionary is constructed according to some or all of the different values.
[0149] In one or more embodiments of the present disclosure, the dictionary acquisition unit 402 is further configured to:
[0150] Generate a first execution plan according to the operation instruction, and determine the type of operator included in the first execution plan;
[0151] If the operator type includes a scan operator, obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a first dictionary according to the different values included in the target column; and / or
[0152] If the operator type includes a filter operator or a join operator, obtaining different values included in the target column according to the predicate in the operator, and constructing a second dictionary according to the different values included in the target column; and / or
[0153] If the operator type includes a projection operator, different values included in the target column are obtained according to a function in the operator, and a third dictionary is constructed according to the different values included in the target column.
[0154] In one or more embodiments of the present disclosure, the dictionary acquisition unit 402 is further configured to:
[0155] If the number of different values is less than the preset number threshold, a second execution plan for querying 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 returned by the execution engine according to the second execution plan are received.
[0156] In one or more embodiments of the present disclosure, the dictionary acquisition unit 402 is further configured to:
[0157] Determine whether there is a dictionary stored in the cache;
[0158] If the dictionary is not stored in the cache, or the dictionary in the cache has expired, then create a dictionary and store it in the cache; or
[0159] If there is a dictionary stored in the cache and it has not expired, the dictionary is obtained from the cache.
[0160] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0161] It is determined that a predicted cost indicator after dictionary-based optimization is enabled for a target column associated with the operation instruction, and it is determined whether to enable dictionary-based optimization according to the predicted cost indicator.
[0162] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0163] It is determined to enable the dictionary-based optimized performance cost indicator and performance benefit indicator for the target column associated with the operation instruction, and the prediction cost indicator is determined according to the performance cost indicator and the performance benefit indicator.
[0164] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0165] Determine the performance overhead of encoding the target column according to the dictionary and decoding the operation result;
[0166] Determine the performance benefit indicators after enabling dictionary-based optimization for the target columns associated with the operation instructions, including:
[0167] Determining a first performance benefit indicator resulting from a change in data width and / or data type for a value included in the target column after encoding the target column according to the dictionary; and / or
[0168] A second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary is determined.
[0169] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0170] Determining a first data amount for encoding the target column according to the dictionary and a second data amount for decoding the operation result;
[0171] Determine a first performance overhead amount of the encoding process using a preset encoding complexity function according to the first data amount, and determine a second performance overhead amount of the decoding process using a preset decoding complexity function according to the second data amount;
[0172] The sum of the first performance overhead amount and the second performance overhead amount is determined as a performance cost indicator.
[0173] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0174] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0175] determining a first target coefficient 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 a first performance benefit indicator is determined according to the product.
[0177] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0178] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0179] determining a first target coefficient based on the first data type and the second data type;
[0180] Determine the type of operation and the number of records in the database operation process in which the target column participates after encoding the target column according to the dictionary; determine the second target coefficient according to the type of operation;
[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 a second performance benefit indicator is determined according to the product.
[0182] In one or more embodiments of the present disclosure, the decision unit 401 is further configured to:
[0183] If the target column participates in multiple database operation processes after being encoded according to the dictionary, the second performance benefit index generated in each database operation process is obtained respectively and accumulated to obtain an accumulated second performance benefit index.
[0184] The device provided in this embodiment can be used to execute the technical solution of the above method embodiment. Its implementation principle and technical effect are similar, and this embodiment will not be repeated here.
[0185] refer to Figure 6 , which shows a schematic diagram of the structure of an electronic device 500 suitable for implementing the embodiment of the present disclosure, and the electronic device 500 may be a terminal device or a server. The terminal device may include but is not limited to mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, personal digital assistants (PDAs), tablet computers (Portable Android Devices, PADs), portable multimedia players (PMPs), vehicle terminals (such as vehicle navigation terminals), etc., and fixed terminals such as digital TVs, desktop computers, etc. Figure 6 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.
[0186] like Figure 6 As shown, the electronic device 500 may include a processing device (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 to a random access memory (RAM) 503. Various programs and data required for the operation of the electronic device 500 are also stored in the RAM 503. The processing device 501, the ROM 502, and the RAM 503 are connected to each other via a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.
[0187] Typically, the following devices may be connected to the I / O interface 505: input devices 506 including, for example, a touch screen, a touch pad, a keyboard, a mouse, a camera, a microphone, an accelerometer, a gyroscope, etc.; output devices 507 including, for example, a liquid crystal display (LCD), a speaker, a vibrator, etc.; storage devices 508 including, for example, a magnetic tape, a hard disk, etc.; and communication devices 509. The communication devices 509 may allow the electronic device 500 to communicate with other devices wirelessly or by wire to exchange data. Although Figure 6 The electronic device 500 is shown with various devices, but it should be understood that it is not required to implement or possess all the devices shown. More or fewer devices may be implemented or possessed instead.
[0188] In particular, according to an embodiment of the present disclosure, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through 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, the above-mentioned functions defined in the method of the embodiment of the present disclosure are executed.
[0189] It should be noted that the computer-readable medium disclosed above may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in combination with an instruction execution system, device or device. In the present disclosure, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, in which a computer-readable program code is carried. This propagated data signal may take a variety of forms, including but not limited to an electromagnetic signal, an optical signal, or any suitable combination of the above. The computer readable signal medium may also be any computer readable medium other than a computer readable storage medium, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer readable medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (radio frequency), etc., or any suitable combination of the above.
[0190] The computer-readable medium may be included in the electronic device, or may exist independently without being incorporated into the electronic device.
[0191] The computer-readable medium carries one or more programs. When the one or more programs are executed by the electronic device, the electronic device executes the method shown in the above embodiment.
[0192] Computer program code for performing the operations of the present disclosure may be written in one or more programming languages or a combination thereof, including object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may 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 may be connected to an external computer (e.g., via the Internet using an Internet service provider).
[0193] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present disclosure. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some implementations as replacements, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.
[0194] The units involved in the embodiments described in the present disclosure may be implemented by software or hardware. The name of a unit does not limit the unit itself in some cases. For example, the first acquisition unit may also be described as a "unit for acquiring at least two Internet Protocol addresses".
[0195] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), and the like.
[0196] In the context of the present disclosure, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0197] In a first aspect, according to one or more embodiments of the present disclosure, a database processing method is provided, comprising:
[0198] Receive an operation instruction for a database, and determine whether dictionary-based optimization is enabled for a target column associated with the operation instruction;
[0199] If it is determined to enable dictionary-based optimization, a dictionary corresponding to the target column is obtained, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values;
[0200] encoding the target column according to the dictionary to replace values included in the target column with corresponding replacement values;
[0201] 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.
[0202] According to one or more embodiments of the present disclosure, obtaining a dictionary corresponding to a target column includes:
[0203] Obtaining the number of different values included in the target column of the database, if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a dictionary according to the different values included in the target column; and / or
[0204] According to the operator and / or function included in the first execution plan corresponding to the operation instruction, different values included in the target column are obtained, and a dictionary is constructed according to some or all of the different values.
[0205] According to one or more embodiments of the present disclosure, obtaining a dictionary corresponding to a target column includes:
[0206] Generate a first execution plan according to the operation instruction, and determine the type of operator included in the first execution plan;
[0207] If the operator type includes a scan operator, obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a first dictionary according to the different values included in the target column; and / or
[0208] If the operator type includes a filter operator or a join operator, obtaining different values included in the target column according to the predicate in the operator, and constructing a second dictionary according to the different values included in the target column; and / or
[0209] If the operator type includes a projection operator, different values included in the target column are obtained according to a function in the operator, and a third dictionary is constructed according to the different values included in the target column.
[0210] According to one or more embodiments of the present disclosure, if the number of different values is less than a preset number threshold, obtaining different values included in the target column includes:
[0211] If the number of different values is less than the preset number threshold, a second execution plan for querying 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 returned by the execution engine according to the second execution plan are received.
[0212] According to one or more embodiments of the present disclosure, obtaining a dictionary corresponding to a target column includes:
[0213] Determine whether there is a dictionary stored in the cache;
[0214] If the dictionary is not stored in the cache, or the dictionary in the cache has expired, then create a dictionary and store it in the cache; or
[0215] If there is a dictionary stored in the cache and it has not expired, the dictionary is obtained from the cache.
[0216] According to one or more embodiments of the present disclosure, determining whether dictionary-based optimization is enabled for a target column associated with an operation instruction includes:
[0217] It is determined that a predicted cost indicator after dictionary-based optimization is enabled for a target column associated with the operation instruction, and it is determined whether to enable dictionary-based optimization according to the predicted cost indicator.
[0218] According to one or more embodiments of the present disclosure, determining to enable a dictionary-based optimized prediction cost indicator for a target column associated with an operation instruction includes:
[0219] It is determined to enable the dictionary-based optimized performance cost indicator and performance benefit indicator for the target column associated with the operation instruction, and the prediction cost indicator is determined according to the performance cost indicator and the performance benefit indicator.
[0220] According to one or more embodiments of the present disclosure, determining to enable a dictionary-based optimized performance overhead indicator for a target column associated with an operation instruction includes:
[0221] Determine the performance overhead of encoding the target column according to the dictionary and decoding the operation result;
[0222] Determine the performance benefit indicators after enabling dictionary-based optimization for the target columns associated with the operation instructions, including:
[0223] Determining a first performance benefit indicator resulting from a change in data width and / or data type for a value included in the target column after encoding the target column according to the dictionary; and / or
[0224] A second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary is determined.
[0225] According to one or more embodiments of the present disclosure, determining a performance overhead indicator generated by encoding a target column according to a dictionary and decoding an operation result includes:
[0226] Determining a first data amount for encoding the target column according to the dictionary and a second data amount for decoding the operation result;
[0227] Determine a first performance overhead amount of the encoding process using a preset encoding complexity function according to the first data amount, and determine a second performance overhead amount of the decoding process using a preset decoding complexity function according to the second data amount;
[0228] The sum of the first performance overhead amount and the second performance overhead amount is determined as a performance cost indicator.
[0229] According to one or more embodiments of the present disclosure, determining a first performance benefit indicator generated by a value included in a target column due to a change in data width and / or data type after encoding the target column according to a dictionary includes:
[0230] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0231] determining a first target coefficient 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 a first performance benefit indicator is determined according to the product.
[0233] According to one or more embodiments of the present disclosure, determining a second performance benefit indicator generated in a database operation process in which the target column participates after encoding the target column according to a dictionary includes:
[0234] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0235] determining a first target coefficient based on the first data type and the second data type;
[0236] Determine the type of operation and the number of records in the database operation process in which the target column participates after encoding the target column according to the dictionary; determine the second target coefficient according to the type of operation;
[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 a second performance benefit indicator is determined according to the product.
[0238] According to one or more embodiments of the present disclosure, determining a second performance benefit indicator generated in a database operation process in which the target column participates after encoding the target column according to a dictionary includes:
[0239] If the target column participates in multiple database operation processes after being encoded according to the dictionary, the second performance benefit index generated in each database operation process is obtained respectively and accumulated to obtain an accumulated second performance benefit index.
[0240] In a second aspect, according to one or more embodiments of the present disclosure, a database processing device is provided, including:
[0241] A decision unit, used for receiving an operation instruction on a database, and determining whether dictionary-based optimization is enabled for a target column associated with the operation instruction;
[0242] A dictionary acquisition unit, configured to acquire a dictionary corresponding to the target column if it is determined to enable dictionary-based optimization, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values;
[0243] an encoding unit, configured to encode the target column according to the dictionary so as to replace the values included in the target column with corresponding replacement values;
[0244] The execution unit is used to convert the operation instruction into the 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.
[0245] According to one or more embodiments of the present disclosure, the dictionary acquisition unit is further configured to:
[0246] Obtaining the number of different values included in the target column of the database, if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a dictionary according to the different values included in the target column; and / or
[0247] According to the operator and / or function included in the first execution plan corresponding to the operation instruction, different values included in the target column are obtained, and a dictionary is constructed according to some or all of the different values.
[0248] According to one or more embodiments of the present disclosure, the dictionary acquisition unit is further configured to:
[0249] Generate a first execution plan according to the operation instruction, and determine the type of operator included in the first execution plan;
[0250] If the operator type includes a scan operator, obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a first dictionary according to the different values included in the target column; and / or
[0251] If the operator type includes a filter operator or a join operator, obtaining different values included in the target column according to the predicate in the operator, and constructing a second dictionary according to the different values included in the target column; and / or
[0252] If the operator type includes a projection operator, different values included in the target column are obtained according to a function in the operator, and a third dictionary is constructed according to the different values included in the target column.
[0253] According to one or more embodiments of the present disclosure, the dictionary acquisition unit is further configured to:
[0254] If the number of different values is less than the preset number threshold, a second execution plan for querying 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 returned by the execution engine according to the second execution plan are received.
[0255] According to one or more embodiments of the present disclosure, the dictionary acquisition unit is further configured to:
[0256] Determine whether there is a dictionary stored in the cache;
[0257] If the dictionary is not stored in the cache, or the dictionary in the cache has expired, then create a dictionary and store it in the cache; or
[0258] If there is a dictionary stored in the cache and it has not expired, the dictionary is obtained from the cache.
[0259] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0260] It is determined that a predicted cost indicator after dictionary-based optimization is enabled for a target column associated with the operation instruction, and it is determined whether to enable dictionary-based optimization according to the predicted cost indicator.
[0261] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0262] It is determined to enable the dictionary-based optimized performance cost indicator and performance benefit indicator for the target column associated with the operation instruction, and the prediction cost indicator is determined according to the performance cost indicator and the performance benefit indicator.
[0263] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0264] Determine the performance overhead of encoding the target column according to the dictionary and decoding the operation result;
[0265] Determine the performance benefit indicators after enabling dictionary-based optimization for the target columns associated with the operation instructions, including:
[0266] Determining a first performance benefit indicator resulting from a change in data width and / or data type for a value included in the target column after encoding the target column according to the dictionary; and / or
[0267] A second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary is determined.
[0268] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0269] Determining a first data amount for encoding the target column according to the dictionary and a second data amount for decoding the operation result;
[0270] Determine a first performance overhead amount of the encoding process using a preset encoding complexity function according to the first data amount, and determine a second performance overhead amount of the decoding process using a preset decoding complexity function according to the second data amount;
[0271] The sum of the first performance overhead amount and the second performance overhead amount is determined as a performance cost indicator.
[0272] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0273] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0274] determining a first target coefficient 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 a first performance benefit indicator is determined according to the product.
[0276] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0277] Determine a first average data width and a first data type of values included in the target column, and determine a second average data width and a second data type of corresponding replacement values;
[0278] determining a first target coefficient based on the first data type and the second data type;
[0279] Determine the type of operation and the number of records in the database operation process in which the target column participates after encoding the target column according to the dictionary; determine the second target coefficient according to the type of operation;
[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 a second performance benefit indicator is determined according to the product.
[0281] According to one or more embodiments of the present disclosure, the decision unit is further configured to:
[0282] If the target column participates in multiple database operation processes after being encoded according to the dictionary, the second performance benefit index generated in each database operation process is obtained respectively and accumulated to obtain an accumulated second performance benefit index.
[0283] In a third aspect, according to one or more embodiments of the present disclosure, there is provided an electronic device, comprising: at least one processor and a memory;
[0284] Memory stores computer-executable instructions;
[0285] At least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the database processing method of the first aspect and various possible designs of the first aspect as described above.
[0286] In a fourth aspect, according to one or more embodiments of the present disclosure, a computer-readable storage medium is provided, in which computer execution instructions are stored. When a processor executes the computer execution instructions, the database processing method as described in the first aspect and various possible designs of the first aspect is implemented.
[0287] In a fifth aspect, according to one or more embodiments of the present disclosure, a computer program product is provided, including computer execution instructions. When a processor executes the computer execution instructions, the database processing method as described in the first aspect and various possible designs of the first aspect is implemented.
[0288] The above description is only a preferred embodiment of the present disclosure and an explanation of the technical principles used. Those skilled in the art should understand that the scope of disclosure involved in the present disclosure is not limited to the technical solutions formed by a specific combination of the above technical features, but should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concept. For example, the above features are replaced with the technical features with similar functions disclosed in the present disclosure (but not limited to) by each other to form a technical solution.
[0289] In addition, although each operation is described in a specific order, this should not be understood as requiring these operations to be performed in the specific order shown or in a sequential order. Under certain circumstances, multitasking and parallel processing may be advantageous. Similarly, although some specific implementation details are included in the above discussion, these should not be interpreted as limiting the scope of the present disclosure. Some features described in the context of a separate embodiment can also be implemented in a single embodiment in combination. On the contrary, the various features described in the context of a single embodiment can also be implemented in multiple embodiments individually or in any suitable sub-combination mode.
[0290] Although the subject matter has been described in language specific to structural features and / or methodological logical actions, 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. On the contrary, the specific features and actions described above are merely example forms of implementing the claims.
Claims
1. A database processing method, characterized in that: include: receiving an operation instruction for a database, and determining whether dictionary-based optimization is enabled for a target column associated with the operation instruction; If it is determined to enable dictionary-based optimization, a dictionary corresponding to the target column is obtained, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values; encoding the target column according to the dictionary to replace values included in the target column with corresponding replacement values; 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.
2. The method according to claim 1, characterized in that: The obtaining the dictionary corresponding to the target column includes: Obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing the dictionary according to the different values included in the target column; and / or According to the operator and / or function included in the first execution plan corresponding to the operation instruction, different values included in the target column are obtained, and the dictionary is constructed according to some or all of the different values.
3. The method according to claim 1, characterized in that The obtaining the dictionary corresponding to the target column includes: generating a first execution plan according to the operation instruction, and determining the type of operators included in the first execution plan; If the operator type includes a scan operator, obtaining the number of different values included in the target column of the database, and if the number of different values is less than a preset number threshold, obtaining the different values included in the target column, and constructing a first dictionary according to the different values included in the target column; and / or If the operator type includes a filter operator or a join operator, obtaining different values included in the target column according to a predicate in the operator, and constructing a second dictionary according to the different values included in the target column; and / or If the operator type includes a projection operator, different values included in the target column are obtained according to a function in the operator, 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 different values is less than a preset number threshold, obtaining different values included in the target column includes: If the number of different values is less than a preset threshold, a second execution plan for querying 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 returned by the execution engine according to the second execution plan are received.
5. The method according to any one of claims 1 to 3, characterized in that: The 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 the dictionary in the cache has expired, then create a dictionary and store it in the cache; or If the dictionary is stored in the cache and is not expired, the dictionary is obtained from the cache.
6. The method according to claim 1, characterized in that The determining whether dictionary-based optimization is enabled for the target column associated with the operation instruction includes: It is determined to enable a dictionary-based optimized prediction cost indicator for a target column associated with the operation instruction, and it is determined whether to enable dictionary-based optimization according to the prediction cost indicator.
7. The method according to claim 6, characterized in that The determining to enable the dictionary-based optimized prediction cost indicator for the target column associated with the operation instruction includes: Determine to enable the dictionary-based optimized performance overhead indicator and performance benefit indicator for the target column associated with the operation instruction, and determine the predicted cost indicator according to the performance overhead indicator and the performance benefit indicator.
8. The method according to claim 7, characterized in that The determining to enable the dictionary-based optimized performance cost indicator for the target column associated with the operation instruction includes: Determining a performance overhead indicator generated by encoding the target column according to the dictionary and decoding an operation result; The determining to enable a dictionary-based optimized performance benefit indicator for a target column associated with the operation instruction includes: Determining a first performance benefit indicator resulting from a change in data width and / or data type of a value included in the target column after encoding the target column according to the dictionary; and / or A second performance benefit indicator generated during a database operation in which the target column participates after the target column is encoded according to the dictionary is determined.
9. The method according to claim 8, characterized in that The determining of a performance overhead indicator generated by encoding the target column according to the dictionary and decoding the operation result includes: determining a first data amount for encoding the target column according to the dictionary and a second data amount for decoding the operation result; Determine a first performance overhead amount of an encoding process using a preset encoding complexity function according to the first data amount, and determine a second performance overhead amount of a decoding process using a preset decoding complexity function according to the second data amount; The sum of the first performance overhead amount and the second performance overhead amount is determined as the performance overhead indicator.
10. The method according to claim 8, characterized in that The determining a first performance benefit indicator generated by a change in data width and / or data type of a value included in the target column after encoding the target column according to the dictionary includes: Determining a first average data width and a first data type of values included in the target column, and determining a second average data width and a second data type of corresponding replacement values; determining a first target coefficient 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 benefit indicator is determined according to the product.
11. The method according to claim 8, characterized in that The determining of a second performance benefit indicator generated in a database operation process in which the target column participates after the target column is encoded according to the dictionary includes: Determining a first average data width and a first data type of values included in the target column, and determining a second average data width and a second data type of corresponding replacement values; determining a first target coefficient based on the first data type and the second data type; Determine the type of operation and the number of records in the database operation process in which the target column participates after encoding the target column according to the dictionary; determine a second target coefficient according to the type of operation; 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 indicator is determined according to the product.
12. The method according to claim 8 or 9, characterized in that: The determining of a second performance benefit indicator generated in a database operation process in which the target column participates after the target column is encoded according to the dictionary includes: If the target column participates in multiple database operation processes after being encoded according to the dictionary, the second performance benefit index generated in each database operation process is obtained respectively, and accumulated to obtain an accumulated second performance benefit index.
13. A database processing device, characterized in that: include: A decision unit, configured to receive an operation instruction for a database, and determine whether dictionary-based optimization is enabled for a target column associated with the operation instruction; a dictionary acquisition unit, configured to acquire a dictionary corresponding to the target column if it is determined to enable dictionary-based optimization, wherein the dictionary includes a mapping relationship between values included in the target column and corresponding replacement values; an encoding unit, configured 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 used 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.
14. An electronic device, characterized in that: include: at least one processor and memory; The memory stores computer-executable instructions; The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor performs the method according to any one of claims 1 to 12.
15. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, and when the processor executes the computer-executable instructions, the method according to any one of claims 1 to 12 is implemented.
16. A computer program product, characterized in that The method comprises computer-executable instructions, and when a processor executes the computer-executable instructions, the method according to any one of claims 1 to 12 is implemented.
Citation Information
Patent Citations
Method and system for performing predecode-time optimized instructions
CN103365628A
Query optimization method and device for relational data
CN115617842A
Data acquisition method and device, electronic equipment and storage medium
CN117762984A
Background service for generating data dictionary metadata
CN117807572A
Derived data dictionary for optimizing transformations of encoded data
US20200183933A1
