A complex connection load generation method and system for different connection shapes

Abstract

The application discloses a complex connection load generation method for different connection shapes, comprising the following steps: step one: connection graph generation: according to the number of user-defined participating connection tables, the queried connection shape and the reference relationship between the tables, a connection template generation method suitable for different connection shapes is used to generate a load template with different connection shapes; step two: filter predicate generation: according to the connection graph, a filter predicate is randomly generated for each table in the template; step three: parameter instantiation: the filter predicate is filled with parameters through a result-oriented parameter instantiation method, so that the query result is not empty. The application also discloses a load generation system for implementing the load generation method and application of the load generation system in complex connection load generation. The application can automatically generate large-scale, rich, diverse and effective complex connection loads, provide rich test scenarios for query optimizer evaluation, and help database administrators effectively evaluate query optimization performance.

Description

Technical Field

[0001] This invention belongs to the fields of analytical databases and load generation technology, and relates to a method and system for generating complex connection loads for different connection shapes. Background Technology

[0002] Analytical database management systems are primarily used to handle online analytical processing (OLAP) workloads and are widely applied in scenarios such as financial analysis, sales analysis, and marketing management. OLAP workloads often involve complex multi-table join queries, which pose challenges to the query optimizer. The query optimizer is the core component of the query engine, designed to select the optimal query execution plan for the executor to execute.

[0003] Modern query optimizers are basically composed of three modules: plan enumeration, cardinality estimation, and cost modeling. To obtain an optimal query execution plan, the query optimizer uses methods such as dynamic programming to enumerate a subset of effective join orders, uses the estimated cardinality as input to the cost model, and selects the plan with the minimum cost from semantically equivalent candidate plans. However, this optimization process is an NP-hard problem. [1] For planned enumeration, due to the commutative and associative nature of join operations, the search space grows exponentially with the number of join tables. For example, considering deep left trees, deep right trees, and dense trees, the search space for joining N tables can reach a staggering [number missing]. Unable to solve in polynomial time [2] For cardinality estimation, due to factors such as the skewness of real-world data distribution, complex relationships between data points, complex predicates, and multiple table joins, the cardinality estimation module of the query optimizer has difficulty obtaining accurate cardinality estimates. [3] For cost models, the cost of operators is calculated by combining costs such as CPU and I / O with weights and cardinality. The weight parameters used are set by the optimizer designer, and there is no explicit method to determine the ideal value of the cost weights, which can introduce errors. [4] .

[0004] Given the importance and difficulty of optimizing query optimizers, there is an urgent need for effective load balancing to assess their performance, help them identify shortcomings, and improve them. Common standard benchmarks include Star SchemaBenchmark. [5] TPC-H [6] TPC-DS [7]Primarily used for overall performance evaluation of database query execution, its data distribution is uniform, while real-world application scenarios often involve non-uniformly distributed data with significant data skew; furthermore, its load only involves simple join shapes of Chain, Tree, and Star types. Therefore, these benchmarks are not well-suited for cardinality estimation and join order selection evaluation. Based on the IMDb dataset. [8] Join Order Benchmark (JOB) [9] It can be used to evaluate cardinality estimation and join order selection, but its dataset is not scalable and has a fixed data skew. While it contains relatively complex join loads, they are all cyclic, thus preventing specific evaluation of particular join shapes and lacking metrics for join order selection. Many works... [10-12] The evaluation of cardinality prediction will use a subset of JOB, JOB-LIGHT, based on a simplified IMDb dataset containing only 70 queries, all of which are Star-type loads with 5 or fewer connections. STATS-CEB is another benchmark for cardinality prediction.

[13] and IMDB-CEB

[14] We manually constructed query loads using real datasets STATS and IMDb, respectively. These datasets lack a randomly controllable number of joins and a rich variety of join shapes.

[0005] In summary, existing benchmark loads lack a random and controllable number of connections and diverse connection shapes, failing to provide rich test scenarios for query optimizer evaluation and thus hindering effective assessment of query optimization performance. Evaluation results obtained using these loads may lack practical significance. Summary of the Invention

[0006] The database query optimizer is the core component of the query engine. To effectively evaluate the performance of the query optimizer, the present invention aims to provide a method and system for generating complex join loads with different join shapes. This method can automatically generate complex join loads with different join shapes suitable for evaluating the query optimizer, while ensuring query validity, where validity means that the query results are not empty.

[0007] The present invention proposes a method for generating complex connection loads for different connection shapes, comprising the following steps:

[0008] Step 1: Connection Graph Generation: Based on the number of tables involved in the connection, the connection shape of the query, and the reference relationships between the tables (see Definition 1), a load template with different connection shapes is generated using a connection template generation method suitable for different connection shapes.

[0009] Definition 1: Referential Relationship. Let fk be one or a set of attributes of table T1, but not the primary key of table T1, and pk be the primary key of table T2. If fk corresponds to pk, then fk is called a foreign key of T1. There is a referential relationship between tables T1 and T2: T1.fk = T2.pk, and table T1 is called the referencing table, and table T2 is called the referenced table.

[0010] Step 2: Filtering predicate generation: Based on the connection graph, randomly generate filtering predicates for each table in the template.

[0011] Step 3: Parameter instantiation: Fill in the parameters for the filtering predicate using a result-oriented parameter instantiation method to ensure that the query results are not empty and generate complex connection load.

[0012] Steps one and three are the core inventive points of this invention. Currently, no existing standard evaluation benchmark includes all seven connection shapes. For example, the Star Schema Benchmark... [5] TPC-H [6] TPC-DS [7] Artificially synthesized loads are used, involving only simple connection shapes of Chain, Tree, and Star. Join Order Benchmark (JOB) [9] The workloads designed based on the real-world IMDB dataset are all of Cyclic type, with an average number of connections of 8. The JOB subset, JOB-LIGHT, only includes Star type workloads with 5 or fewer connections. Furthermore, the filtering predicates of these existing benchmark workloads are fixed and cannot be extended. While real-world application workloads have a rich variety of connection numbers, connection shapes, and filtering predicates, a significant amount of manpower is required to organize and classify these workloads, and this task demands substantial database knowledge. Meanwhile, existing workload generation tools cannot generate a large number of workloads with randomly controllable connection numbers, rich connection shapes, and diverse filtering predicates. In addition, existing workload generation tools cannot simultaneously satisfy syntactic and semantic correctness and query validity (i.e., query results are not empty). For example, RGAS...

[15] A large number of loads were generated randomly based on a given pattern, and functional testing of the SQL Server database was performed using differential testing methods. However, approximately 50% of the queries generated by RAGS returned empty results, resulting in a significant waste of testing resources.

[16] Only 30% of the generated queries were syntactically correct, and less than 5% of those were semantically correct. (SQLSmith)

[17] SparkFuzz generates a large number of syntactically correct queries by randomly generating abstract syntax trees (ASTs) and translating them into SQL statements. However, it cannot guarantee the semantic correctness or validity of the generated queries.

[18] SparkFuzz generates test loads randomly using a fuzz testing method. It employs a recursive load generation model to randomly select query keywords for load generation, ignoring the impact of parameter selection in the filtering predicate on the query results. Therefore, the execution result of the load generated by SparkFuzz is likely to be empty. (Artemis)

[19] While it can guarantee the syntactic and semantic correctness of the load, it only supports generating Chain type loads and cannot guarantee that the query results are not empty. Furthermore, Artemis relies on the external Mathematica tool for query result generation, which has many limitations and high overhead. Therefore, existing benchmarks, real-world application loads, and existing load generation tools cannot meet the need for rich test scenarios for query optimizer evaluation. The method for generating complex join loads with different join shapes proposed in this invention effectively solves the above problems in steps one and three. It can automatically generate a large number of loads with randomly controllable join numbers, rich join shapes, and diverse filtering predicates, while ensuring that the loads meet syntactic correctness, semantic correctness, and query validity. The time complexity of this method is O(N), where N is the number of tables involved in the join. Typically, N is not very large; for example, in a JOB load, N is at most 16.

[0013] In step one, the connection template generation method applicable to different connection shapes adds new nodes (tables) and new edges (connection conditions) to the initially empty connection graph based on the reference relationships between tables specified in the user-specified database schema, the number of tables involved in the connection, and the shape of the query connection graph, respectively, through node addition and edge addition operations. The schema is a description of the logical structure and characteristics of all data in the database, including the size of each table, the data type of each table's attribute columns, and the reference relationships between tables. The node addition operation randomly adds a table that meets the connection shape requirements as a node to the connection graph, while the edge addition operation adds connection conditions as connection edges between two tables in the connection graph. Join conditions include primary key-foreign key joins (PK-FK Join) and foreign key-foreign key joins (FK-FK Join). For example, the primary key-foreign key join condition T1.pk = T2.fk means that the two tables are joined through primary and foreign key references between tables T1 and T2. The foreign key-foreign key join condition T1.fk = T2.fk requires that tables T1 and T2 have reference relationships with another table T3, namely T1.fk = T3.pk and T2.fk = T3.pk, respectively. T1 and T2 are joined through the transfer of primary and foreign key reference relationships among the three tables. This method supports join shapes including chain, star, tree, cycle, cyclic, grid, and clique.

[0014] In step two, the filtering predicate refers to the filtering conditions in the select operation of the SQL query statement, such as selecting tuples σ from the Student table whose age is greater than 20. age＞20 (Student), where the expression age > 20 is the filter predicate on the Student table.

[0015] The filtering predicates generated by this invention include single-attribute filtering predicates and multi-attribute joint filtering predicates. A single-attribute filtering predicate refers to a filtering expression containing only one attribute column, such as T.col > P (only involving the col attribute column of table T); a multi-attribute joint filtering predicate refers to a filtering expression containing two or more attribute columns, such as T.col1 + T.col2 < P (involving the col1 and col2 attribute columns of table T). The attribute columns and expression symbols involved in this step of the filtering predicate are randomly generated; and this step only generates an uninstantiated parameter P, meaning that parameter P is merely an abstract symbol without a concrete value. Step three will assign a value to parameter P.

[0016] In step three, the result-oriented parameters are instantiated as filter predicate parameters, which means assigning a value to parameter P in the filter predicate generated in step two (e.g., setting P = 100). The mapping function from the primary key to the attribute column and the join condition can deterministically determine the range of values ​​for the filter predicate parameters. Randomly selecting reasonable parameters from the range of values ​​will make the parameter-filled filter predicate effective, ensuring that no null values ​​are returned in the query. Here, the mapping function from the primary key to the attribute column refers to the value of the attribute column being mapped by the generating function with the primary key as the independent variable. For example, if the primary key x∈[0, n] of a table has an attribute column col whose generating function is F(x), then the value of col corresponding to the primary key x is y = F(x), where y∈{y|y = F(x), x∈[0, n]}.

[0017] The present invention also provides a system for implementing the above method, the system comprising a connection graph generation module, a filtering predicate generation module, and a parameter instantiation module.

[0018] Specifically, the connection graph generation module takes the user-defined database schema, the number of tables involved in the connection, and the query connection shape as input, and uses a connection template generation method suitable for different connection shapes. It samples and generates load templates with different connection shapes based on the reference relationships between the tables. For example, if there are 5 tables A, B, C, D, and E in the database, and the reference relationships between them are A.fk = B.pk, A.fk = C.pk, B.fk = D.pk, and D.fk = C.pk. To generate a Chain join with 3 tables, this module uses dot and edge operations to randomly select 3 tables that can form a Chain and the corresponding join conditions. For example, selecting tables A, B, and C, and the join conditions A.fk = B.pk and A.fk = C.pk, results in a Chain join template of "SELECT COUNT(*) FROM A, B, C WHERE A.fk = B.pk AND A.fk = C.pk". Similarly, to generate a Cycle join with 4 tables, this module uses dot and edge operations to randomly select 4 tables that can form a Cycle and the corresponding join conditions. For example, selecting tables A, B, C, and D, and the join conditions A.fk = B.pk, A.fk = C.pk, B.fk = D.pk, and D.fk = C.pk, results in a Cycle join template of "SELECT COUNT(*) FROM A, B, C, D". WHEREA.fk=B.pk AND A.fk=C.pk AND B.fk=D.pk AND D.fk=C.pk”.

[0019] The filtering predicate generation module randomly generates a variety of filtering predicates for each table involved in the join. On each table, the module randomly generates one or more filtering predicates (including single-attribute filtering predicates and multi-attribute joint filtering predicates) based on the table's attribute columns.

[0020] The parameter instantiation module fills in parameters for the filtering predicate using a result-oriented parameter instantiation method, ensuring that the query results are not empty and thus obtaining an effective query load.

[0021] The present invention also provides the application of the above-described load generation method and / or load generation system in the generation of complex connection loads.

[0022] This invention is a method and system for generating complex connection loads for different connection shapes. It can generate large-scale, diverse and effective complex connection loads, providing rich test scenarios for query optimizer evaluation and helping database administrators effectively evaluate query optimization performance.

[0023] The beneficial effects of this invention include: load diversity and load efficiency.

[0024] (i) Load Diversity: The load features a randomly controllable number of connections, rich connection shapes, and diverse filtering predicates, providing a wide range of test scenarios for query optimizer evaluation. Existing common standard benchmarks include StarSchema Benchmark. [5] TPC-H [6] TPC-DS [7] The load involves only simple connection shapes of Chain, Tree, and Star, according to the Join Order Benchmark (JOB). [9] The loads in the benchmarks are all of Cyclic type, with an average number of connections of 8; the JOB subset, JOB-LIGHT, only includes Star type loads with 5 or fewer connections. Furthermore, the filtering predicates of these benchmark loads are fixed and cannot be extended. While real-world application loads have a rich variety of connection numbers, connection shapes, and filtering predicates, this requires significant manpower to organize and classify a large number of loads, and this work demands substantial database knowledge. Therefore, neither existing benchmarks nor real-world application loads can meet the need for rich test scenarios for query optimizer evaluation. This invention can automatically generate loads with randomly controllable connection numbers, rich connection shapes, and diverse filtering predicates.

[0025] (II) Load Validity: All queries satisfy syntactic and semantic correctness; a result-oriented parameter instantiation method ensures that intermediate results of all operators are not empty, ultimately ensuring that no null values ​​are returned in the query. Among existing load generation tools, RGAS...

[15] Approximately 50% of the generated load queries returned empty results, resulting in a significant waste of test resources. (AFL)

[16] Only 30% of the generated queries were syntactically correct, and less than 5% of those were semantically correct. (SQLSmith)

[17] It can randomly generate a large number of syntactically correct queries, but it cannot guarantee the semantic correctness or validity of the generated queries. SparkFuzz

[18] Because the impact of parameter selection in the filtering predicate on query results is not considered, the execution result of SparkFuzz's load generation is likely to be empty. (Artemis)

[19] While it can guarantee the syntactic and semantic correctness of the payload, it cannot guarantee that the query results will not be empty. Furthermore, it relies on the external Mathematica tool to generate the query results, which has many limitations and high overhead. This invention can automatically generate a large number of payloads that satisfy syntactic and semantic correctness and ensure that the query results are not empty. Attached Figure Description

[0026] Figure 1 This is a system architecture diagram of the load generation system of the present invention.

[0027] Figure 2 These are schematic diagrams showing different shapes of the connection diagrams involved in this invention.

[0028] Figure 3 This is an example diagram of the Chain type connection involved in this invention.

[0029] Figure 4 This is an example diagram of a Star-type connection involved in this invention.

[0030] Figure 5 This is an example diagram of a Tree type connection involved in this invention.

[0031] Figure 6 This is an example diagram of a Cyclic type connection involved in this invention.

[0032] Figure 7 This is an example diagram of result-oriented parameter instantiation in an embodiment of the present invention. Detailed Implementation

[0033] The present invention will be further described in detail below with reference to the specific embodiments and accompanying drawings. Except for the contents specifically mentioned below, the processes, conditions, and experimental methods for implementing the present invention are all common knowledge and general knowledge in the art, and the present invention does not have any particular limitations.

[0034] The present invention proposes a method for generating complex connection loads for different connection shapes, comprising the following steps:

[0035] Step 1: Join Graph Generation: Based on the user-defined number of tables involved in the join, the join shape of the query, and the reference relationships between the tables, a load template with different join shapes is generated using a join template generation method suitable for different join shapes. The join template generation method for different join shapes adds new nodes (tables) and new edges (join conditions) to the initially empty join graph through node addition and edge addition operations, respectively, based on the user-specified database schema, the number of tables involved in the join, and the shape of the join graph. The node addition operation randomly adds a table that meets the join shape requirements as a node to the join graph, while the edge addition operation adds a join condition as a connection edge between two tables in the join graph. Supported join shapes include chain, star, tree, cycle, cyclic, grid, and clique.

[0036] Step Two: Filtering Predicate Generation: Based on the connection diagram, a filtering predicate is randomly generated for each table in the template. The filtering predicates generated by this invention include single-attribute filtering predicates and multi-attribute joint filtering predicates. A single-attribute filtering predicate refers to a filtering expression that contains only one attribute column, such as T.col>P (only involving the col attribute column of table T); a multi-attribute joint filtering predicate refers to a filtering expression that contains two or more attribute columns, such as T.col1+T.col2<P (involving the col1 and col2 attribute columns of table T). The attribute columns and expression symbols involved in the filtering predicates in this step are randomly generated; and this step only generates an uninstantiated parameter P, which means that the parameter P is merely an abstract symbol without a specific value. Step Three will assign a value to the parameter P.

[0037] Step 3: Parameter Instantiation: Parameters are populated for the filtering predicate using a result-oriented parameter instantiation method. This involves assigning a value to parameter P in the filtering predicate generated in Step 2 (e.g., setting P = 100). The key point of this step is to ensure that the query result is not empty. The mapping function from the primary key to the attribute column and the join condition can deterministically determine the range of values ​​for the filtering predicate parameters. Randomly selecting reasonable parameters from this range will make the parameter-filled filtering predicate effective, ensuring that no empty values ​​are returned in the query. The mapping function from the primary key to the attribute column refers to the value of the attribute column being mapped using the primary key as the independent variable through a generating function. For example, if the primary key x∈[0, n] of a table has an attribute column col whose generating function is F(x), then the value of col corresponding to the primary key x is y = F(x), where y∈{y|y = F(x), x∈[0, n]}.

[0038] The present invention includes the following modules:

[0039] The connection graph generation module takes user-defined database schema, number of tables involved in the connection, and query connection shape as input, and uses connection template generation methods suitable for different connection shapes to sample and generate load templates with different connection shapes based on the reference relationship between the tables.

[0040] The filter predicate generation module randomly generates diverse filter predicates for each table involved in the join. On each table, this module randomly generates one or more filter predicates (including single-attribute filter predicates and multi-attribute joint filter predicates) based on the table's attribute columns.

[0041] Parameter instantiation module: Fills in parameters for filtering predicates using a result-oriented parameter instantiation method to ensure that the query results are not empty and thus obtain an effective query load.

[0042] Basic architecture

[0043] Based on the above problem definition, the system input includes a user-defined configuration file; the system output is the query load, which is stored in the corresponding database. The basic architecture mainly consists of three modules: a connection graph generation module, a filter predicate generation module, and a parameter instantiation module, such as... Figure 1 As shown.

[0044] The connection diagram generation adopts a connection template generation method suitable for different connection shapes, and generates load templates with different connection shapes based on the reference relationship between each table. Figure 2 The system demonstrates the seven connection shapes it supports.

[0045] In the Chain type, tables are linked sequentially, a common practice. For example, to query detailed information about students who have taken the "Math" course, the corresponding SQL statement would be "SELECT Student.* FROM Student AS S, Course ASC, SC where S.Sno=SC.Sno AND C.Cno=SC.Cno AND C.Cname='Math'". The join graph for this query is shown below. Figure 3 As shown.

[0046] The START type is commonly used in data warehouse applications. This type of query involves a fact table and multiple dimension tables. The fact table is the central table in the data warehouse, storing factual records such as transaction records, sales details, and system logs. Dimension tables store the attribute values ​​of dimensions, i.e., detailed information about the attributes in the fact table, such as date dimension tables, product dimension tables, and user dimension tables. The START type can be seen as connecting multiple dimension tables to the central fact table. For example, to query detailed customer information for purchasing a product with the product number "20220111" and the place of origin "Shanghai", the corresponding SQL statement is "SELECT Customer.* FROM Order AS O, Customer AS C, Item AS I, Region AS R where O.Cno=C.Cno AND O.Ino=I.Ino AND O.Rno=R.Rno AND R.Rname='Shanghai' AND I.Ino='20220111'", where the Order table is the central fact table storing sales records, and the other tables are dimension tables. The connection diagram for this query is shown below. Figure 4 As shown.

[0047] The Tree type can be seen as a hybrid of Chain and Star types, and it is very common in practical applications. For example, to query the parts information of goods purchased by customer number "01" from supplier "XX", the corresponding SQL statement is "SELECT Part.* FROM Order AS O, Part AS P, Supplier AS S, Customer AS C, Item AS I where O.Cno=C.Cno AND O.Ino=I.Ino AND P.Pno=I.Pno AND S.Sno=I.SnoAND C.Cno='01'AND I.Ino='22'AND S.Sname='XX'". The corresponding join graph for this query is as follows: Figure 5 As shown.

[0048] Chain, Star, and Tree types do not contain loops. Cycle, Cyclic, Clique, and Grid types all contain loops and are commonly used in online analytical processing (OLAP) scenarios. These queries are often used for complex statistical analysis and decision support involving a large number of tables. In a Cycle type, all tables are joined to form a loop; in a Cyclic type, at least one loop exists in the join graph; in a Clique type, there is a join condition between any two tables; and in a Grid type, the table has a grid shape, with each table having at least two other tables in reference. The Cyclic type is the most common. For example, in a Cyclic job load (which is abstracted from the load on the real-world IMDB dataset), query Q13 to retrieve the ranking and release dates of movies produced by US companies would have the following SQL statement:

[0049] "SELECT MIN(cn.name)AS producing_company, MIN(miidx.info)AS rating, MIN(t.title)AS movie FROM company_name AS cn, company_type AS ct, info_type AS it, info_type AS it2, kind_type AS kt, movie_companies AS mc, movie_info AS mi, movie_info_idx AS miidx, title AS t WHERE cn.country_code='[us]'AND ct.kind='production companies'AND it.info='rating'AND it2.info='release dates'AND kt.kind='movie'AND mi.movie_id=t.id AND it2.id=mi.info_type_id AND kt.id=t.kind_id AND mc.movie_id=t.id AND cn.id=mc.company_id AND ct.id=mc.company_type_id AND "miidx.movie_id = t.id AND it1.id = miidx.info_type_id AND mi.movie_id = miidx.movie_id AND mi.movie_id = mc.movie_id AND miidx.movie_id = mc.movie_id", the connection graph of this query is as follows: Figure 6 As shown in the figure, there are 8 tables involved in the connection, including 4 rings.

[0050] The join template generation algorithm adds new nodes (tables) and new edges (join conditions) to an initially empty join graph based on the reference relationships between tables specified in the pattern, the number of tables involved in the join, and the shape of the join graph. This is done through node addition and edge addition operations. A node addition operation randomly adds a table that meets the join shape requirements as a node, while an edge addition operation adds a join condition as a connection edge between two tables in the join graph. For Chain, Star, Tree, and Cycle types, each operation performs one node addition and one edge addition; for Cyclic type, each operation performs one node addition or one edge addition; for Grid type, each operation performs two node additions and three edge additions; and for Clique type, each operation performs one node addition and n-1 edge additions, where n is the number of existing nodes in the join graph. Specifically, the node added during the dot addition operation must satisfy the following conditions: For Chain type, the node is connected to a node with degree 1 in the generated connected graph; for Star type, the node is connected to the central fact table in the generated connected graph; for Grid type, the node is connected to a node with degree 2 in the generated connected graph; for Clique type, the node is connected to every node in the generated connected graph. The specific algorithm is as follows:

[0051]

[0052] Next, the filter predicate generation module randomly generates filter predicates for the tables in the generated join graph template; then, the parameter instantiation module fills in the filter predicate parameters. The mapping function from the primary key to the attribute column and the join condition can deterministically determine the value range of the filter predicate parameters. By randomly selecting reasonable parameters from the value range, the filled filter predicate can be made effective, ensuring that no null values ​​are returned in the query. Figure 7This demonstrates an example of result-oriented parameter instantiation. The diagram shows two tables, T1 and T2, where the foreign key fk in T1 references the primary key pk in T2. ​​Assuming the randomly generated filtering predicate in table T2 is T2.col > P, the value of parameter P needs to be determined. Given that the primary key range of table T1 is [0, 100], and the generating function from the primary key to the foreign key fk is F1(x) = 3*x + 1, then the value range of T1.fk is [1, 301]. Also, given that the foreign key fk of T1 references the primary key pk of T2, i.e., T1.fk = T2.pk, the possible value range of T2.pk is also [1, 301]. Then, using the generating function from the primary key of T2 to the attribute column col, F2(x) = 2*x + 50, the possible value range of T2.col is [52, 652] (shaded area in the figure). Finally, the possible value range of parameter P for the filtering predicate T2.col > P is [52, 652]. Randomly selecting a value from this range to instantiate parameter P, such as T2.col > 100, yields an effective filtering predicate that ensures the query result is not empty.

[0053] Experimental conclusions

[0054] Experimental environment

[0055] Experimental hardware configuration: One physical node with CentOS 7.9 operating system, containing 8 CPUs, model Intel(R)Xeon(R)Gold 6240M CPU@2.60GHz; 32GB of memory; 150GB of storage, RAID-5, SSD disk.

[0056] Performance Evaluation

[0057] Experiment 1: Load Generation Efficiency

[0058] Generate a database instance containing 20 tables, each with 20 non-primary key attributes and 1,000 rows. Generate loads with different numbers and shapes of connections on this database instance, generating 50 loads of each type, and observe the average load generation time. For the experiment, only Chain, Star, and Cycle types are generated for 3-table connections; the number of Grid type connections is always even.

[0059]

[0060] This table shows the average query generation time for different join shapes with varying numbers of joins. Because result-oriented parameter instantiation is required, the parameter generation for the current filtering predicate is derived from the intermediate results of the previous join; therefore, the number of derivations increases with the number of joins. The table shows that the load generation time for various shapes increases with the number of joins; however, the average load generation time is not significantly different between different shapes, indicating that increased join complexity does not lead to a slower load generation speed. This demonstrates that the load generation method of this invention maintains stable efficiency for loads with different join shapes.

[0061] Experiment 2: Load Validity

[0062] Generate a database instance containing 20 tables, each with 20 non-primary key attributes, and each table has 1,000,000 rows. Generate workloads with different numbers and shapes of connections on this database instance, generating 50 of each type. Observe the range of the final query result set size for each workload. In the experiment, for 3-table joins, only Chain, Star, and Cycle types are generated; the number of Grid type connections is always even.

[0063]

[0064] This table shows the range of final query result set sizes for different join shapes and with varying numbers of joins. The data in the table shows that the final result set size for all generated loads is greater than 0 (i.e., not empty). Existing load generation tools like RGAS generate loads with approximately 50% of queries resulting in empty results and do not support all seven join shapes; Artemis, under the same experimental conditions, generates approximately 20% of queries with empty results in nine-table joins and only supports the Chain shape.

[0065] Regardless of the shape, this invention can guarantee that the generated query results are not empty payloads, providing rich test scenarios for query optimizer evaluation and helping database administrators effectively evaluate query optimization performance.

[0066] The above experiments demonstrate that the present invention can efficiently and stably generate a wide variety of effective complex connection loads.

[0067] References

[0068] [1]Chaudhuri S.An overview of query optimization in relationalsystems[C] / / Proceedings of the Seventeenth ACM SIGACTSIGMOD-SIGART Symposiumon Principles of Database Systems-PODS’98.Seattle:ACM Press,1998:34-43.

[0069] [2]Ibaraki T,Kameda T.On the optimal nesting order for computing n-relational joins[J].ACM Transactions on Database Systems(TODS),1984,9(3):482-502.

[0070] [3]Lohman G.Is query optimization a“solved”problem[C] / / Proc.Workshopon Database Query Optimization.Oregon Graduate Center Comp.Sci.Tech.Rep,2014,13:10.

[0071] [4]Manegold S,Boncz P,Kersten M L.Generic database cost models forhierarchical memory systems[C] / / VLDB′02:Proceedings of the 28th InternationalConference on Very Large Databases.Morgan Kaufmann,2002:191-202.

[0072] [5]O’Neil P,O’Neil E,Chen X.Star schema benchmark-revision 3[J].edn,2009.

[0073] [6]TPC.2013.TPC-H Benchmark.http: / / www.tpc.org / tpch /

[0074] [7]Nambiar R O,Poess M.The Making of TPC-DS[C] / / VLDB.2006,6:1049-1058.

[0075] [8]Premade VM for replication,http: / / git.io / imdb.

[0076] [9]Leis V,Gubichev A,Mirchev A,et al.How good are query optimizers,really?[J].Proceedings of the VLDB Endowment,2015,9(3):204-215.

[0077]

[10] Kipf A,Kipf T,Radke B,et al.Learned cardinalities:Estimatingcorrelated joins with deep learning[J].CIDR 2019.

[0078]

[11] Yang Z,Kamsetty A,Luan S,et al.NeuroCard:one cardinalityestimator for all tables[J].Proceedings of the VLDB Endowment,2021,14(1):61-73.

[0079]

[12] Zhu R,Wu Z,Han Y,et al.FLAT:fast,lightweight and accurate methodfor cardinality estimation[J].VLDB,2021,14(9):1489-1502.

[0080]

[13] Han Y,Wu Z,Wu P,et al.Cardinality Estimation in DBMS:AComprehensive Benchmark Evaluation[J].arXiv preprint arXiv:2109.05877,2021.

[0081]

[14] Marcus R, Kipf A, van Renen A, et al. Flow-Loss: Learning CardinalityEstimates That Matter[J]. Proceedings of the VLDB Endowment, 2021, 14(11).

[0082]

[15] Slutz D R.Massive stochastic testing ofSQL[C] / / Very Large DataBase.1998: 618-622.

[0083]

[16] Zalewski M.American fuzzy lop(2.52b).http: / / lcamtuf.coredump.cx / afl.

[0084]

[17] Seltenreich A, Tang B, Mullender S. SQLSmith. https: / / github.com / anse1 / sqlsmith.

[0085]

[18] Ghit B, Poggi N, Rosen J, et al.SparkFuzz: Searching correctnessregressions in modern query engines[C] / / Proceedings of the Workshop onTesting Database Systems.Portland Oregon: ACM, 2020: 1-6.

[0086]

[19] Mi K, Zhang C, Qian W, et al. Artemis: An automatic test suitegenerator for large seale olap database[C] / / International Symposium onBenchmarking, Measuring and Optimization.: Springer, 2020: 74-89.

[0087] The scope of protection of this invention is not limited to the above embodiments. Any variations and advantages that can be conceived by those skilled in the art without departing from the spirit and scope of this invention are included in this invention and are protected by the appended claims.

Claims

1. A method for generating complex connection loads for different connection shapes, characterized in that, Includes the following steps: Step 1: Connection Graph Generation: Based on the number of tables involved in the connection, the connection shape of the query, and the reference relationships between the tables as defined by the user, load templates with different connection shapes are generated using connection template generation methods suitable for different connection shapes; In step one, the method for generating connection templates applicable to different connection shapes is as follows: Based on the reference relationships between tables, the number of tables involved in the join, and the shape of the join graph as specified in the database schema, new nodes and edges are added to the initially empty join graph through point addition and edge addition operations, respectively. The new nodes are tables, and the new edges are join relationships. The schema describes the logical structure and characteristics of all data in the database, including the size of each table, the data type of each table's attribute columns, and the description of the reference relationships between tables. The point addition operation randomly adds a table that meets the join shape requirements as a node to the join graph, and the edge addition operation adds a join condition as a join edge between two tables in the join graph. The join conditions include primary key-foreign key join (PK-FK Join) and foreign key-foreign key join (FK-FK Join). Step 2: Filtering predicate generation: Based on the connection graph, randomly generate filtering predicates for each table in the template; Step 3: Parameter instantiation: Fill in the parameters for the filtering predicate using a result-oriented parameter instantiation method to ensure that the query results are not empty and generate complex connection load; In step three, the result-oriented parameter instantiation is as follows: The result-oriented parameter instantiation is used to fill in the filter predicate parameters, that is, to assign a value to parameter P in the filter predicate generated in step two; the value range of the filter predicate parameters can be deterministically obtained from the mapping function from the primary key to the attribute column and the join condition. By randomly selecting reasonable parameters from the value range, the filter predicate after parameter filling can be made effective, ensuring that no null values ​​are returned in the query; where the mapping function from the primary key to the attribute column refers to the value of the attribute column being mapped by the generation function with the primary key as the independent variable.

2. The method for generating complex connection loads for different connection shapes as described in claim 1, characterized in that, In step one, the reference relationship means: let fk be one or a group of attributes of table T1, but not the primary key of table T1, and pk be the primary key of table T2; if fk corresponds to pk, then fk is called a foreign key of T1; a reference relationship exists between tables T1 and T2. Table T1 is called the reference table, and Table T2 is called the referenced table.

3. The method for generating complex connection loads for different connection shapes as described in claim 1, characterized in that, In step one, the different connection shapes include chain, star, tree, cycle, cyclic, grid, and clique.

4. The method for generating complex connection loads for different connection shapes as described in claim 1, characterized in that, In step two, the filtering predicate refers to the filtering conditions selected in the select operation of the SQL query statement; The filtering predicates include single-attribute filtering predicates and multi-attribute joint filtering predicates; the single-attribute filtering predicate refers to a filtering expression that contains only one attribute column; the multi-attribute joint filtering predicate refers to a filtering expression that contains two or more attribute columns; the attribute columns and expression symbols involved in the filtering predicates are randomly generated; and in step two, only uninstantiated parameter P is generated, where uninstantiated means that parameter P is merely an abstract symbol without a specific value.

5. A load generation system implementing the load generation method as described in any one of claims 1-4, characterized in that, The system includes a connection graph generation module, a filter predicate generation module, and a parameter instantiation module.

6. The system as described in claim 5, characterized in that, The connection graph generation module takes information including user-defined database schema, number of participating connection tables, and query connection shape as input, and adopts a connection template generation method suitable for different connection shapes. It samples and generates load templates with different connection shapes based on the reference relationship between each table. The filtering predicate generation module randomly generates various filtering predicates for each table participating in the join; on each table, the filtering predicate generation module randomly generates one or more filtering predicates based on the attribute columns of the table, and the filtering predicates include single attribute filtering predicates and multi-attribute joint filtering predicates; The parameter instantiation module fills in parameters for the filtering predicate using a result-oriented parameter instantiation method, ensuring that the query results are not empty and thus obtaining an effective query load.

7. The application of the load generation method as described in any one of claims 1-4 in the generation of complex connection loads.

8. The application of the load generation system as described in claim 5 or 6 in the generation of complex connection loads.

Images