Data processing method, database optimizer, device and readable storage medium
By estimating and comparing the memory usage of index column conditions with thresholds, the scan range is dynamically adjusted, solving the memory overflow problem caused by long SQL statements with IN conditions in the database. This achieves efficient construction of scan ranges and reduction of data under limited memory.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- PINGCAP XINGCHEN (BEIJING) TECH CO LTD
- Filing Date
- 2023-01-16
- Publication Date
- 2026-05-22
AI Technical Summary
SQL statements with long IN conditions in the database can result in a large number of scan ranges, consuming a lot of memory and even causing system memory overflow.
By estimating the memory usage of the scan range constructed by each index column condition and the previous index column condition, and comparing it with a preset memory usage threshold, the scan range corresponding to the next index column condition is dynamically determined, thus avoiding memory overruns.
Without causing system memory overflow, maximize the use of index column conditions to construct the scan range, reduce the amount of data scanned, and improve the efficiency of the execution plan.
Smart Images
Figure CN116049220B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database technology, and more specifically, to a data processing method, a database optimizer, an apparatus, and a readable storage medium. Background Technology
[0002] The IN condition (or IN operator) in a Structured Query Language (SQL) query filters out data that matches any value in the IN condition. The database optimizer attempts to translate the IN condition of the SQL statement into an index scan range, uses statistics to estimate the data volume and execution cost, and selects the lowest-cost plan from all considered candidate plans as the optimal execution plan for the SQL statement.
[0003] However, for SQL statements with long IN conditions in the database, the number of corresponding scan ranges is large, and the scan ranges as a whole will occupy a lot of memory, causing the optimizer to consume a lot of memory, and even causing the entire system to overflow. Summary of the Invention
[0004] This application provides a data processing method, a database optimizer, an apparatus, and a readable storage medium to solve the technical problem of high optimizer memory consumption leading to system memory overflow.
[0005] Firstly, a data processing method is provided, the method comprising:
[0006] Receive the query conditions of the SQL statement, which include multiple index column conditions;
[0007] Perform the following operations until each of the multiple index column conditions has been traversed:
[0008] For the current index column condition among the multiple index column conditions, the memory usage of the second scan range is estimated. The second scan range is constructed using the first scan range corresponding to the current index column condition and the previous index column condition.
[0009] Based on the estimated memory usage of the second scan range and a preset memory usage threshold, the scan range corresponding to the conditions for determining the next index column is determined.
[0010] In one possible implementation, determining the scan range for determining the next index column condition based on the estimated memory usage of the second scan range and a preset memory usage threshold includes:
[0011] If the estimated memory usage of the second scan range is less than the memory usage threshold, then the second scan range is used to determine the scan range corresponding to the next index column condition.
[0012] In another possible implementation, the method further includes:
[0013] If the memory usage of the second scan range is greater than the memory usage threshold, estimate the memory usage of the third scan range constructed by using the minimum and maximum values in the current index column conditions and the first scan range;
[0014] If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
[0015] In yet another possible implementation, the method further includes:
[0016] If the estimated memory usage of the second scan range is equal to the memory usage threshold, then it is determined that the second scan range will be used to determine the scan range corresponding to the next index column condition.
[0017] or,
[0018] If the estimated memory usage of the second scan range is equal to the memory usage threshold, estimate the memory usage of the third scan range constructed using the minimum and maximum values in the current index column conditions and the first scan range;
[0019] If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
[0020] In yet another possible implementation, the method further includes:
[0021] If the estimated memory usage of the third scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
[0022] In yet another possible implementation, the method further includes:
[0023] If the estimated memory usage of the third scan range is equal to the memory usage threshold, it is determined that the third scan range will be used to determine the scan range corresponding to the next index column condition; or, it is determined that the first scan range will be used to determine the scan range corresponding to the next index column condition.
[0024] In yet another possible implementation, if the multiple index column conditions come from a first index list and a second index list, there is an index connection between the second index list and the first index list;
[0025] If the current index column condition is an index column condition that exists in both the second index list and the first index list, the method further includes:
[0026] Estimate the memory usage of the fourth scan range, which is constructed using the first scan range corresponding to the current index column condition and the previous index column condition, wherein the value in the current index column condition is dynamically determined when performing index join;
[0027] Based on the estimated memory usage of the fourth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0028] In yet another possible implementation, determining the scan range corresponding to the next index column condition includes at least one of the following operations:
[0029] If the estimated memory usage of the fourth scan range is less than the memory usage threshold, then the fourth scan range is used to determine the scan range corresponding to the next index column condition.
[0030] If the estimated memory usage of the fourth scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
[0031] In yet another possible implementation, the method further includes:
[0032] If the estimated memory usage of the fourth scan range is equal to the memory usage threshold, then it is determined that the fourth scan range is used to determine the scan range corresponding to the next index column condition, or the first scan range is used to determine the scan range corresponding to the next index column condition.
[0033] In yet another possible implementation, the execution of the index join includes:
[0034] Perform an index join using the first index list as the outer table and the second index list as the inner table; or...
[0035] Perform an index join with the second index list as the outer table and the first index list as the inner table.
[0036] In yet another possible implementation, if the current index column condition is the first index column condition among the plurality of index column conditions, the method further includes:
[0037] Estimate the memory usage of the fifth scan range constructed using the current index column conditions and the full scan range;
[0038] Based on the estimated memory usage of the fifth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0039] In yet another possible implementation, the process of determining the scan range corresponding to the next index column condition includes at least one of the following operations:
[0040] If the estimated memory usage of the fifth scan range is less than the memory usage threshold, then the fifth scan range is used to determine the scan range corresponding to the next index column condition.
[0041] If the estimated memory usage of the fifth scan range is greater than the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0042] If the estimated memory usage of the sixth scan range is less than the memory usage threshold, then the sixth scan range is used to determine the scan range corresponding to the next index column condition.
[0043] If the estimated memory usage of the sixth scan range is greater than the memory usage threshold, then the full scan range is used to determine the scan range corresponding to the next index column condition.
[0044] In yet another possible implementation, the method further includes:
[0045] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, then the fifth scan range is determined to be used to determine the scan range corresponding to the next index column condition.
[0046] or,
[0047] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0048] If the estimated memory usage of the sixth scan range is equal to the memory usage threshold, then it is determined that the sixth scan range is used to determine the scan range corresponding to the next index column condition, or the full scan range is used to determine the scan range corresponding to the next index column condition.
[0049] Secondly, a database optimizer is provided, including:
[0050] The data receiving module is configured to receive query conditions of SQL statements, wherein the query conditions include multiple index column conditions;
[0051] The data processing module is configured to perform the following operation until each of the multiple index column conditions has been traversed:
[0052] For the current index column condition among the multiple index column conditions, the memory usage of the second scan range is estimated. The second scan range is constructed using the first scan range corresponding to the current index column condition and the previous index column condition.
[0053] Based on the memory usage of the second scan range and a preset memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0054] Thirdly, an electronic device is provided, the electronic device comprising:
[0055] One or more processors;
[0056] Memory;
[0057] One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more applications being configured to: perform the data processing method described in the first aspect above.
[0058] For example, in a third aspect of this application, a computing device is provided, comprising: a processor, a memory, a communication interface, and a communication bus, wherein the processor, the memory, and the communication interface communicate with each other via the communication bus;
[0059] The memory is used to store at least one executable instruction that causes the processor to perform an operation corresponding to the data processing method shown in the first aspect of this application.
[0060] Fifthly, a computer-readable storage medium is provided, the processor-readable storage medium storing a computer program for causing a processor to perform the data processing method described in the first aspect above.
[0061] The beneficial effects of the technical solution provided in this application are:
[0062] For the current index column condition in the query conditions of the received SQL statement, the memory usage of the second scan range constructed using the first scan range corresponding to the previous index column condition is estimated. Based on the estimated memory usage of the second scan range and the preset memory usage threshold, the scan range used to determine the next index column condition is determined, and so on, until each index column condition in the query conditions is traversed. Through the above method, not only can system memory overflow be prevented when constructing the scan range, but the scan range can also be constructed to the maximum extent with limited memory, thereby reducing the amount of data scanned. Attached Figure Description
[0063] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments of this application will be briefly introduced below.
[0064] Figure 1 A flowchart illustrating a data processing method provided in an embodiment of this application;
[0065] Figure 2 A flowchart illustrating a data processing method provided in another embodiment of this application;
[0066] Figure 3 A flowchart illustrating a data processing method provided in another embodiment of this application;
[0067] Figure 4 This is a schematic diagram of the structure of a database optimizer provided in an embodiment of this application;
[0068] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation
[0069] The embodiments of this application are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain this application, and should not be construed as limiting the invention.
[0070] Those skilled in the art will understand that, unless explicitly stated otherwise, the singular forms “a,” “an,” “the,” and “the” used herein may also include the plural forms. It should be further understood that the term “comprising” as used in this application’s specification means the presence of the stated features, integers, steps, operations, elements, and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and / or groups thereof. It should be understood that when we say an element is “connected” or “coupled” to another element, it can be directly connected or coupled to the other element, or there may be intermediate elements. Furthermore, “connected” or “coupled” as used herein can include wireless connections or wireless coupling. The term “and / or” as used herein includes all or any units and all combinations of one or more associated listed items.
[0071] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.
[0072] First, let's introduce and explain several terms used in this application:
[0073] 1. Database: A database is a warehouse for storing data. Essentially, it is a file system. Data is stored in a specific format, and users can add, modify, delete, and query data in the database.
[0074] 2. SQL: Structured Query Language (SQL) is a database query and programming language used to access, query, update, and manage relational database systems. It mainly includes:
[0075] (1) Data Definition Language (DDL): Used to define database objects such as databases, tables, and columns. Keywords: create, alter, drop, etc.
[0076] (2) Data Manipulation Language (DML): Used to update records in tables within a database. Keywords: insert, delete, update, etc.
[0077] (3) Data Control Language (DCL): It is used to define database access permissions and security levels, as well as to create users.
[0078] (4) Data Query Language (DQL): Used to query records in tables of a database. Keywords: select, from, where, etc.
[0079] 3. Database Optimizer: The query optimizer (or simply optimizer) is a crucial component of a database. It determines the most efficient way for a SQL statement to access the requested data. The optimizer attempts to generate an optimal execution plan for a SQL statement. It selects the lowest-cost plan from all considered candidate plans. The optimizer uses statistics to estimate data volume and execution cost. For a specific query in a given environment, cost calculation considers various factors of query execution, such as I / O, CPU, and communication.
[0080] 4. OOM: Out of memory occurs when the system uses too much memory and cannot allocate additional memory for programs or the operating system, causing programs or the operating system to fail to run.
[0081] 5. SQL IN list: The IN condition (or IN operator) in an SQL query filters out data that matches any value in the IN list. It helps reduce the need for multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE statements. The optimizer attempts to convert the IN condition into an index scan range, reducing the amount of data scanned.
[0082] For SQL statements with long IN list operations in the database, such as "create table t(pk int primarykey, a int, key(a)); explain select * from t where a in(1, 2, ..., 18000) and pk in(1, 2, ..., 8000);", when calculating the scan range of key(a), the optimizer implicitly appends pk after a. Therefore, for the conditions a in (1, 2, ..., 18000) and pk in (1, 2, ..., 18000), 18000 * 18000 scan ranges will be generated. Assuming that each scan range consumes approximately 100B of memory, the total scan range will occupy approximately 32GB of memory. This will cause the optimizer to consume a large amount of memory, and may even lead to a memory overflow of the entire system.
[0083] To address the aforementioned problems, this application provides a data processing method, a database optimizer, an electronic device, and a computer-readable storage medium that ensures no OutOfMemoryError (OOM) occurs when constructing the scan range, while maximizing the use of index column conditions to construct the scan range and reducing the amount of data scanned within limited memory.
[0084] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.
[0085] This application provides a data processing method, such as... Figure 1 As shown, the method includes:
[0086] S101. Receive the query conditions of the SQL statement, wherein the query conditions include multiple index column conditions;
[0087] S102. Perform the following operation until each of the multiple index column conditions is traversed:
[0088] For the current index column condition among the multiple index column conditions, the memory usage of the second scan range is estimated. The second scan range is constructed using the first scan range corresponding to the current index column condition and the previous index column condition.
[0089] Based on the estimated memory usage of the second scan range and a preset memory usage threshold, the scan range corresponding to the conditions for determining the next index column is determined.
[0090] In this embodiment, for the current index column condition in the query conditions of the received SQL statement, the memory usage of the second scan range constructed using the first scan range corresponding to the previous index column condition is estimated. Based on the estimated memory usage of the second scan range and a preset memory usage threshold, the scan range for determining the next index column condition is determined, until each index column condition in the query conditions is traversed. Through the above method, not only can system memory overflow be prevented when constructing the scan range, but the scan range can also be constructed to the maximum extent with limited memory, thereby reducing the amount of data scanned.
[0091] This application provides one possible implementation method, such as... Figure 2 As shown, determining the scan range for determining the next index column condition based on the estimated memory usage of the second scan range and a preset memory usage threshold includes:
[0092] S1. If the estimated memory usage of the second scan range is less than the memory usage threshold, determine that the second scan range is used to determine the scan range corresponding to the next index column condition.
[0093] Specifically, in this embodiment, before determining whether to add the current index column condition to the first scan range corresponding to the previous index column condition, the memory usage of the second scan range obtained after adding the current index column condition to the first scan range is estimated. If the estimated memory usage is less than or equal to a preset memory usage threshold range_max_size (used to limit the maximum memory usage for the optimizer to construct the scan range), then the current index column condition will actually be added to the first scan range to obtain the second scan range, which is used to determine the scan range corresponding to the next index column condition.
[0094] It should be noted that if the current index column condition is the last index column condition in the query conditions, then the obtained second scan range is the final determined scan range.
[0095] This application provides one possible implementation method, such as... Figure 2 As shown, the above method may further include:
[0096] S2. If the memory usage of the second scan range is greater than the memory usage threshold, estimate the memory usage of the third scan range constructed by using the minimum and maximum values in the current index column conditions and the first scan range.
[0097] S3. If the estimated memory usage of the third scan range is less than the memory usage threshold, determine that the third scan range is used to determine the scan range corresponding to the next index column condition.
[0098] Specifically, in this embodiment, before determining whether to add the current index column condition to the first scan range corresponding to the previous index column condition, the memory usage of the second scan range obtained after adding the current index column condition to the first scan range is estimated. If the estimated memory usage is greater than range_max_size, the memory usage of the third scan range obtained after adding the minimum and maximum values in the current index column condition to the first scan range is estimated. Only if the estimated memory usage is less than or equal to range_max_size will the minimum and maximum values in the current index column condition be actually added to the first scan range to obtain the third scan range, which is used to determine the scan range corresponding to the next index column condition.
[0099] It should be noted that if the current index column condition is the last index column condition in the query conditions, then the resulting third scan range is the final determined scan range.
[0100] In another possible implementation, the above method may also include:
[0101] If the estimated memory usage of the second scan range is equal to the memory usage threshold, then it is determined that the second scan range will be used to determine the scan range corresponding to the next index column condition.
[0102] or,
[0103] If the estimated memory usage of the second scan range is equal to the memory usage threshold, estimate the memory usage of the third scan range constructed using the minimum and maximum values in the current index column conditions and the first scan range;
[0104] If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
[0105] This application provides one possible implementation method, such as... Figure 2 As shown, the above method may further include:
[0106] S4. If the estimated memory usage of the third scan range is greater than the memory usage threshold, determine that the first scan range is used to determine the scan range corresponding to the next index column condition.
[0107] Specifically, in this embodiment, before determining whether to add the current index column condition to the first scan range corresponding to the previous index column condition, the memory usage of the second scan range obtained after adding the current index column condition to the first scan range is estimated. If the estimated memory usage is greater than range_max_size, the memory usage of the third scan range obtained after adding the minimum and maximum values in the current index column condition to the first scan range is estimated. If the estimated memory usage is greater than range_max_size, it is determined that the first scan range will be used to determine the scan range corresponding to the next index column condition.
[0108] It should be noted that if the current index column condition is the last index column condition in the query conditions, then the first scan range is the final scan range.
[0109] In another possible implementation, the above method may also include:
[0110] If the estimated memory usage of the third scan range is equal to the memory usage threshold, it is determined that the third scan range will be used to determine the scan range corresponding to the next index column condition; or, it is determined that the first scan range will be used to determine the scan range corresponding to the next index column condition.
[0111] The following describes the solutions in the above embodiments in detail, with reference to Example 1.
[0112] For example, the query conditions of the received SQL statement include:
[0113] create table t(a int, b int, c int, d int, e int, index idx(a, b, c, d));
[0114] select*from t where a in(1,2,3)and b in(4,5,6)and c in(7,8,9)and d in(10,11,12);
[0115] Step 11: Try adding a in(1,2,3) to the full scan range to obtain the scan range [1,1], [2,2], [3,3]. If the memory usage of the obtained scan range does not exceed the memory limit (does not exceed the preset memory usage threshold), proceed to step 12; if it exceeds the memory limit, try using the scan range [1,3]. If it still exceeds the memory limit, use the full scan range [-inf, +inf].
[0116] Step 12: Try adding b in (4, 5, 6) to the scan range [1, 1], [2, 2], [3, 3] determined in Step 11, resulting in the scan range [1 4, 1 4], [1 5, 1 5], [1 6, 1 6], [2 4, 2 4], [2 5, 2 5], [2 6, 2 6], [3 4, 3 4], [3 5, 3 5], [3 6, 3 6]. If the memory usage of the obtained scan range does not exceed the memory limit, proceed to Step 13; if it exceeds the memory limit, try using the scan range [1 4, 1 6], [2 4, 2 6], [3 4, 3 6]. If it still exceeds the memory limit, use the scan range [1, 1], [2, 2], [3, 3].
[0117] In this embodiment, 1 4 in the scan range [1 4, 1 4] means a = 1 and b = 4. The meaning of the values in other scan ranges is the same, and will not be repeated here.
[0118] Step 13: Try adding c in(7, 8, 9) to the scan range [1 4, 14], [1 5, 1 5], [16, 1 6], [2 4, 2 4], [2 5, 2 5], [2 6, 2 6], [3 4, 3 4], [3 5, 3 5], [3 6, 3 6] determined in Step 12 for memory judgment, and determine the scan range to be used based on the judgment result. The specific process is similar to Step 12 above, and will not be repeated for the sake of brevity.
[0119] Step 14: Try adding d in(10, 11, 12) to the scan range determined in step 13 for memory judgment, and determine the scan range to be used based on the judgment result. The specific process is similar to step 12 above, and will not be repeated for the sake of brevity.
[0120] Through the above process of constructing a scan range with memory limitations, the optimizer avoids memory overflow and maximizes the use of indexes to reduce the amount of scanned data, thereby improving the execution efficiency of the plan.
[0121] It should be noted that the memory determination process in the above embodiments may be: determining whether the total memory used during the scan, based on the second scan range obtained by attempting to add the current index column condition to the first scan range corresponding to the previous index column condition, exceeds a preset memory usage threshold.
[0122] This application provides a possible implementation method in which, if the multiple index column conditions come from a first index list and a second index list, there is an index connection between the second index list and the first index list;
[0123] If the current index column condition is an index column condition that exists in both the second index list and the first index list, the method further includes:
[0124] Estimate the memory usage of the fourth scan range, which is constructed using the first scan range corresponding to the current index column condition and the previous index column condition, wherein the value in the current index column condition is dynamically determined when performing index join;
[0125] Based on the estimated memory usage of the fourth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0126] In the above embodiments, performing an index join includes:
[0127] Perform an index join using the first index list as the outer table and the second index list as the inner table; or...
[0128] Perform an index join with the second index list as the outer table and the first index list as the inner table.
[0129] This application provides a possible implementation method, wherein determining the scan range corresponding to the next index column condition includes at least one of the following operations:
[0130] If the estimated memory usage of the fourth scan range is less than the memory usage threshold, then the fourth scan range is used to determine the scan range corresponding to the next index column condition.
[0131] If the estimated memory usage of the fourth scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
[0132] In another possible implementation, the method further includes:
[0133] If the estimated memory usage of the fourth scan range is equal to the memory usage threshold, then it is determined that the fourth scan range is used to determine the scan range corresponding to the next index column condition, or the first scan range is used to determine the scan range corresponding to the next index column condition.
[0134] The following describes the solutions in the above embodiments in detail, with reference to Example 2.
[0135] For example, the query conditions of the received SQL statement include:
[0136] create table t1(b int, index idx_b(b));
[0137] create table t2(a int, b int, c int, d int, e int, index idx_a_b_c_d(a, b, c, d));
[0138] select count(*)from t1 join t2 on t1.b=t2.b where t2.a in(1,3,5)andt2.c in(2,4,6);
[0139] The optimizer attempts an index join with t1 as the outer table and t2 as the inner table.
[0140] Step 21: Try adding t2.a in(1, 3, 5) to the full scan range to obtain the scan range [1, 1], [3, 3], [5, 5]. If the memory limit is not exceeded, proceed to step 22. Otherwise, try the range scan range [1, 3]. If the limit is still exceeded, use the full scan range [-inf, +inf].
[0141] Step 22: Attempt to add t2.b = t1.b to the scan range [1,1], [3,3], [5,5] determined in Step 21, resulting in the scan range [1?, 1?], [3?, 3?], [5?, 5?]. Here, "?" indicates that the value of t2.b is dynamically determined during the index join execution. If the memory limit is not exceeded, proceed to Step 23; otherwise, determine the scan range to use: [1,1], [3,3], [5,5].
[0142] Step 23: Try adding t2.c in(2, 4, 6) to the scan range [1?, 1?], [3?, 3?], [5?, 5?] determined in step 22 for memory judgment, and determine the scan range to be used based on the judgment result. The specific process is similar to step 12 above, and will not be repeated for the sake of brevity.
[0143] The above process of constructing the internal table scan range with memory constraints avoids memory overflow during the query optimization phase and also prevents memory overflow when repeatedly filling the internal table scan range during the query execution phase.
[0144] This application embodiment provides a possible implementation method. If the current index column condition is the first index column condition among the plurality of index column conditions, the method further includes:
[0145] Estimate the memory usage of the fifth scan range constructed using the current index column conditions and the full scan range;
[0146] Based on the estimated memory usage of the fifth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0147] This application provides a possible implementation method in which the process of determining the scan range corresponding to the next index column condition includes at least one of the following operations:
[0148] If the estimated memory usage of the fifth scan range is less than the memory usage threshold, then the fifth scan range is used to determine the scan range corresponding to the next index column condition.
[0149] If the estimated memory usage of the fifth scan range is greater than the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0150] If the estimated memory usage of the sixth scan range is less than the memory usage threshold, then the sixth scan range is used to determine the scan range corresponding to the next index column condition.
[0151] If the estimated memory usage of the sixth scan range is greater than the memory usage threshold, then the full scan range is used to determine the scan range corresponding to the next index column condition.
[0152] In another possible implementation, the method further includes:
[0153] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, then the fifth scan range is determined to be used to determine the scan range corresponding to the next index column condition.
[0154] or,
[0155] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0156] If the estimated memory usage of the sixth scan range is equal to the memory usage threshold, then it is determined that the sixth scan range is used to determine the scan range corresponding to the next index column condition, or the full scan range is used to determine the scan range corresponding to the next index column condition.
[0157] This application also provides a possible implementation method: if the query conditions include two index column conditions, namely: a first index column condition and a second index column condition, then:
[0158] The specific implementation scheme for determining the scan range corresponding to the second index column condition can refer to the scheme for determining the scan range corresponding to the next index column condition when the current index column condition is the first index column condition among multiple index column conditions in the above embodiment. For the sake of brevity, it will not be elaborated here.
[0159] The specific scheme for determining the final scan range based on the second index column condition can refer to the scheme for determining the scan range when the current index column condition is the last index column condition in the query conditions in the above embodiment. For the sake of brevity, it will not be repeated here.
[0160] The following is combined Figure 3 The flowchart shown provides a detailed description of the technical solution of the data processing method provided in the embodiments of this application.
[0161] For example, the query conditions of the received SQL statement include:
[0162] create table t(c1,c2,…,cn,index idx(c1,c2,…,cn));
[0163] select*from t where c1 in(…)and c2 in(…)and…and cn in(…);
[0164] like Figure 3 The methods shown include:
[0165] S201. When i = 1, the scan range r = [-inf, +inf];
[0166] S202. Try to add each value in the index condition of ci to the scan range r to obtain a new scan range r_new, where ci are c1, c2, ..., cn respectively when i ranges from 1 to n.
[0167] S203. Determine whether the estimated memory usage of the new scan range is less than the preset memory usage threshold. If yes, proceed to step S204; otherwise, proceed to step S207.
[0168] S204. Determine the scan range r as the new scan range r_new.
[0169] S205. Determine if i is less than or equal to n. If yes, update the value of i to i+1 and execute step S202. If no, execute step S206.
[0170] S206. Output the determined scan range r.
[0171] S207. Try to add the maximum and minimum values in the index conditions of ci to the scan range r to obtain a new scan range r_new, where ci are c1, c2, ..., cn respectively when i ranges from 1 to n.
[0172] S208. Determine whether the estimated memory usage of the new scan range is less than the preset memory usage threshold. If yes, proceed to step S209; otherwise, proceed to step S206.
[0173] S209. Determine the scan range r as the new scan range r_new, and execute step S206.
[0174] The technical solutions in the above embodiments can maximize the use of index column conditions to construct the scan range while limiting the scan index memory, thereby reducing the amount of data scanned.
[0175] For example, given the index idx(a, b) and the conditions a in (1, 3, 5) and b in (2, 4, 6), the optimizer will construct different scan ranges under range_max_size:
[0176] 1. [1 2, 1 2], [1 4, 1 4], [1 6, 1 6], [3 2, 3 2], [3 4, 3 4], [3 6, 36], [5 2, 52], [5 4, 5 4], [5 6, 5 6]
[0177] 2. [1 2, 1 6], [3 2, 3 6], [5 2, 5 6]
[0178] 3. [1, 1], [3, 3], [5, 5]
[0179] 4.[1, 5]
[0180] 5. [-inf, +inf]
[0181] The optimizer will select the scan range with the least amount of data scanned from the above five scan ranges (listed in descending order of memory usage) within the range_max_size constraint. This technique allows for more flexible selection of scan ranges under memory constraints, minimizing data access in the execution plan and thus avoiding memory overflows when constructing scan ranges, while ensuring the efficiency of the execution plan within memory limitations.
[0182] This application provides a database optimizer, such as... Figure 4 As shown, the database optimizer 30 may include: a data receiving module 301 and a data processing module 302, wherein,
[0183] The data receiving module 301 is configured to receive query conditions of SQL statements, wherein the query conditions include multiple index column conditions;
[0184] Data processing module 302 is configured to perform the following operation until each of the plurality of index column conditions has been traversed:
[0185] For the current index column condition among the multiple index column conditions, the memory usage of the second scan range is estimated. The second scan range is constructed using the first scan range corresponding to the current index column condition and the previous index column condition.
[0186] Based on the estimated memory usage of the second scan range and a preset memory usage threshold, the scan range corresponding to the conditions for determining the next index column is determined.
[0187] In one possible implementation, when the data processing module 302 determines the scan range for determining the next index column condition based on the estimated memory usage of the second scan range and a preset memory usage threshold, it is configured as follows:
[0188] If the estimated memory usage of the second scan range is less than the memory usage threshold, then the second scan range is used to determine the scan range corresponding to the next index column condition.
[0189] In another possible implementation, the data processing module 302 is further configured as follows:
[0190] If the memory usage of the second scan range is greater than the memory usage threshold, estimate the memory usage of the third scan range constructed by using the minimum and maximum values in the current index column conditions and the first scan range;
[0191] If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
[0192] In yet another possible implementation, the data processing module 302 is further configured as follows:
[0193] If the estimated memory usage of the second scan range is equal to the memory usage threshold, then it is determined that the second scan range will be used to determine the scan range corresponding to the next index column condition.
[0194] or,
[0195] If the estimated memory usage of the second scan range is equal to the memory usage threshold, estimate the memory usage of the third scan range constructed using the minimum and maximum values in the current index column conditions and the first scan range;
[0196] If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
[0197] In yet another possible implementation, the data processing module 302 is further configured as follows:
[0198] If the estimated memory usage of the third scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
[0199] In yet another possible implementation, the data processing module 302 is further configured as follows:
[0200] If the estimated memory usage of the third scan range is equal to the memory usage threshold, it is determined that the third scan range will be used to determine the scan range corresponding to the next index column condition; or, it is determined that the first scan range will be used to determine the scan range corresponding to the next index column condition.
[0201] In another possible implementation, if the multiple index column conditions come from a first index list and a second index list, and there is an index connection between the second index list and the first index list; and if the current index column condition is an index column condition that exists simultaneously in both the second index list and the first index list, the data processing module 302 is further configured as follows:
[0202] Estimate the memory usage of the fourth scan range, which is constructed using the first scan range corresponding to the current index column condition and the previous index column condition, wherein the value in the current index column condition is dynamically determined when performing index join;
[0203] Based on the estimated memory usage of the fourth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0204] In the above embodiments, performing an index join includes:
[0205] Perform an index join using the first index list as the outer table and the second index list as the inner table; or...
[0206] Perform an index join with the second index list as the outer table and the first index list as the inner table.
[0207] In yet another possible implementation, when determining the scan range corresponding to the next index column condition, the data processing module 302 is configured to perform at least one of the following operations:
[0208] If the estimated memory usage of the fourth scan range is less than the memory usage threshold, then the fourth scan range is used to determine the scan range corresponding to the next index column condition.
[0209] If the estimated memory usage of the fourth scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
[0210] In yet another possible implementation, the data processing module 302 is further configured as follows:
[0211] If the estimated memory usage of the fourth scan range is equal to the memory usage threshold, then it is determined that the fourth scan range is used to determine the scan range corresponding to the next index column condition, or the first scan range is used to determine the scan range corresponding to the next index column condition.
[0212] In yet another possible implementation, if the current index column condition is the first index column condition among the plurality of index column conditions, the data processing module 302 is further configured as follows:
[0213] Estimate the memory usage of the fifth scan range constructed using the current index column conditions and the full scan range;
[0214] Based on the estimated memory usage of the fifth scan range and the memory usage threshold, a scan range is determined to determine the conditions corresponding to the next index column.
[0215] In yet another possible implementation, when determining the scan range corresponding to the next index column condition, the data processing module 302 is configured to perform at least one of the following operations:
[0216] If the estimated memory usage of the fifth scan range is less than the memory usage threshold, then the fifth scan range is used to determine the scan range corresponding to the next index column condition.
[0217] If the estimated memory usage of the fifth scan range is greater than the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0218] If the estimated memory usage of the sixth scan range is less than the memory usage threshold, then the sixth scan range is used to determine the scan range corresponding to the next index column condition.
[0219] If the estimated memory usage of the sixth scan range is greater than the memory usage threshold, then the full scan range is used to determine the scan range corresponding to the next index column condition.
[0220] In yet another possible implementation, the data processing module 302 is further configured as follows:
[0221] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, then the fifth scan range is determined to be used to determine the scan range corresponding to the next index column condition.
[0222] or,
[0223] If the estimated memory usage of the fifth scan range is equal to the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range;
[0224] If the estimated memory usage of the sixth scan range is equal to the memory usage threshold, then it is determined that the sixth scan range is used to determine the scan range corresponding to the next index column condition, or the full scan range is used to determine the scan range corresponding to the next index column condition.
[0225] The database optimizer in this embodiment can execute the data processing method shown in the foregoing embodiments of this application. Its implementation principle is similar, and the beneficial effects it can achieve are the same as those of the file processing method shown in the foregoing embodiments, which will not be repeated here.
[0226] This application provides an electronic device, which includes a memory and a processor; at least one program stored in the memory, which, when executed by the processor, can achieve the following compared to the prior art: for the current index column condition in the query conditions of a received SQL statement, estimate the memory usage of a second scan range constructed using the first scan range corresponding to the previous index column condition, and determine the scan range corresponding to the next index column condition based on the estimated memory usage of the second scan range and a preset memory usage threshold, until each index column condition in the query conditions is traversed. Through the above method, not only can system memory overflow be prevented when constructing the scan range, but the scan range can also be constructed to the maximum extent using the index column conditions under limited memory conditions, thereby reducing the amount of data scanned.
[0227] In one alternative embodiment, an electronic device is provided, such as Figure 5 As shown, Figure 5 The illustrated electronic device 40 includes a processor 401 and a memory 403. The processor 401 and the memory 403 are connected, for example, via a bus 402. Optionally, the electronic device 40 may further include a transceiver 404, which can be used for data interaction between the electronic device and other electronic devices, such as sending and / or receiving data. It should be noted that in practical applications, the transceiver 404 is not limited to one type, and the structure of the electronic device 400 does not constitute a limitation on the embodiments of this application.
[0228] Processor 401 may be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this application. Processor 401 may also be a combination that implements computational functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.
[0229] Bus 402 may include a pathway for transmitting information between the aforementioned components. Bus 402 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. Bus 402 can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 5 The bus is represented by a single thick line, but this does not mean that there is only one bus or one type of bus.
[0230] The memory 403 may be a ROM (Read Only Memory) or other type of static storage device capable of storing static information and instructions, RAM (Random Access Memory) or other type of dynamic storage device capable of storing information and instructions, or an EEPROM (Electrically Erasable Programmable Read Only Memory), CD-ROM (Compact Disc Read Only Memory) or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but not limited thereto.
[0231] The memory 403 is used to store application code that executes the solution of this application, and its execution is controlled by the processor 401. The processor 401 is used to execute the application code stored in the memory 403 to implement the content shown in the foregoing method embodiments.
[0232] This application provides a computer-readable storage medium storing a computer program that, when run on a computer, enables the computer to execute the corresponding content in the aforementioned method embodiments. Compared with the prior art, for the current index column condition in the query conditions of a received SQL statement, the memory usage of a second scan range constructed using the first scan range corresponding to the previous index column condition is estimated. Based on the estimated memory usage of the second scan range and a preset memory usage threshold, a scan range for determining the next index column condition is determined, until each index column condition in the query conditions is traversed. This method not only ensures that system memory overflow does not occur when constructing the scan range, but also maximizes the utilization of index column conditions to construct the scan range and reduces the amount of data scanning under limited memory conditions.
[0233] The terms "first," "second," "third," "fifth," "1," "2," etc. (if present) in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in a sequence other than that shown in the figures or text.
[0234] It should be noted that the division of units in the embodiments of this application is illustrative and only represents one logical functional division. In actual implementation, other division methods may be used. Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated units described above can be implemented in hardware or as software functional units.
[0235] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a processor-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
[0236] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product implemented on one or more computer-usable storage media (including, but not limited to, disk storage and optical storage) containing computer-usable program code.
[0237] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer-executable instructions. These computer-executable instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0238] These processor-executable instructions may also be stored in a processor-readable memory that can direct a computer or other programmable data processing device to operate in a particular manner, such that the instructions stored in the processor-readable memory produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0239] These processors can execute instructions that can also be loaded onto a computer or other programmable data processing device, causing a series of operational steps to be performed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable device for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0240] It should be understood that although the steps in the flowcharts of the accompanying figures are shown sequentially as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the accompanying figures may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the sub-steps or stages of other steps.
[0241] The above description is only a partial embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.
Claims
1. A data processing method, characterized in that, include: Receive the query conditions of the SQL statement, which include multiple index column conditions; Perform the following operations until each of the multiple index column conditions has been traversed: For the current index column condition among the multiple index column conditions, estimate the memory usage of the second scan range, which is obtained by combining each value in the current index column condition with each range in the first scan range corresponding to the previous index column condition. Based on the estimated memory usage of the second scan range and the preset memory usage threshold, determine whether to use the second scan range to determine the scan range corresponding to the next index column condition; If the estimated memory usage of the second scan range is less than the memory usage threshold, then the second scan range is used to determine the scan range corresponding to the next index column condition.
2. The method according to claim 1, characterized in that, The method further includes: If the memory usage of the second scan range is greater than the memory usage threshold, estimate the memory usage of the third scan range constructed by using the minimum and maximum values in the current index column conditions and the first scan range; If the estimated memory usage of the third scan range is less than the memory usage threshold, then the third scan range is used to determine the scan range corresponding to the next index column condition.
3. The method according to claim 2, characterized in that, The method further includes: If the estimated memory usage of the third scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
4. The method according to claim 1, characterized in that, If the multiple index column conditions come from a first index list and a second index list, there is an index connection between the second index list and the first index list; If the current index column condition is an index column condition that exists in both the second index list and the first index list, the method further includes: Estimate the memory usage of the fourth scan range, which is constructed using the first scan range corresponding to the current index column condition and the previous index column condition, wherein the value in the current index column condition is dynamically determined when performing index join; Based on the estimated memory usage of the fourth scan range and the memory usage threshold, it is determined whether the fourth scan range is applicable to determine the scan range corresponding to the next index column condition.
5. The method according to claim 4, characterized in that, Determining whether the fourth scan range applies to determine the scan range corresponding to the next index column condition includes at least one of the following operations: If the estimated memory usage of the fourth scan range is less than the memory usage threshold, then the fourth scan range is used to determine the scan range corresponding to the next index column condition. If the estimated memory usage of the fourth scan range is greater than the memory usage threshold, then the first scan range is used to determine the scan range corresponding to the next index column condition.
6. The method according to any one of claims 1-5, characterized in that, If the current index column condition is the first index column condition among the plurality of index column conditions, the method further includes: Estimate the memory usage of the fifth scan range constructed using the current index column conditions and the full scan range; Based on the estimated memory usage of the fifth scan range and the memory usage threshold, it is determined whether to use the fifth scan range to determine the scan range corresponding to the next index column condition.
7. The method according to claim 6, characterized in that, The process of determining whether to use the fifth scan range to determine the scan range corresponding to the next index column condition includes at least one of the following operations: If the estimated memory usage of the fifth scan range is less than the memory usage threshold, then the fifth scan range is used to determine the scan range corresponding to the next index column condition. If the estimated memory usage of the fifth scan range is greater than the memory usage threshold, estimate the memory usage of the sixth scan range constructed using the minimum and maximum values in the current index column conditions and the full scan range; If the estimated memory usage of the sixth scan range is less than the memory usage threshold, then the sixth scan range is used to determine the scan range corresponding to the next index column condition. If the estimated memory usage of the sixth scan range is greater than the memory usage threshold, then the full scan range is used to determine the scan range corresponding to the next index column condition.
8. A database optimizer, characterized in that, include: The data receiving module is configured to receive query conditions of SQL statements, wherein the query conditions include multiple index column conditions; The data processing module is configured to perform the following operation until each of the multiple index column conditions has been traversed: For the current index column condition among the multiple index column conditions, estimate the memory usage of the second scan range, which is obtained by combining each value in the current index column condition with each range in the first scan range corresponding to the previous index column condition. Based on the memory usage of the second scan range and a preset memory usage threshold, determine the scan range corresponding to the next index column condition for whether to use the second scan range; If the estimated memory usage of the second scan range is less than the memory usage threshold, then the second scan range is used to determine the scan range corresponding to the next index column condition.
9. An electronic device, characterized in that, The electronic device includes: One or more processors; Memory; One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more applications being configured to: perform the data processing method according to any one of claims 1 to 7.
10. A processor-readable storage medium, characterized in that, The processor-readable storage medium stores a computer program for causing the processor to perform the data processing method according to any one of claims 1 to 7.