CN109656946A - A kind of multilist relation query method, device and equipment - Google Patents
A kind of multilist relation query method, device and equipment Download PDFInfo
- Publication number
- CN109656946A CN109656946A CN201811144099.7A CN201811144099A CN109656946A CN 109656946 A CN109656946 A CN 109656946A CN 201811144099 A CN201811144099 A CN 201811144099A CN 109656946 A CN109656946 A CN 109656946A
- Authority
- CN
- China
- Prior art keywords
- query
- statement
- cost
- result set
- query statement
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 56
- 238000004458 analytical method Methods 0.000 claims description 3
- 238000010586 diagram Methods 0.000 description 14
- 230000008569 process Effects 0.000 description 11
- 230000006870 function Effects 0.000 description 9
- 230000006872 improvement Effects 0.000 description 9
- 238000003860 storage Methods 0.000 description 9
- 238000004590 computer program Methods 0.000 description 7
- 238000005516 engineering process Methods 0.000 description 5
- 238000012545 processing Methods 0.000 description 5
- 238000004422 calculation algorithm Methods 0.000 description 3
- 230000003190 augmentative effect Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 230000006399 behavior Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 230000001413 cellular effect Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 238000011156 evaluation Methods 0.000 description 1
- 230000002068 genetic effect Effects 0.000 description 1
- 230000000977 initiatory effect Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 238000005457 optimization Methods 0.000 description 1
- 229920001296 polysiloxane Polymers 0.000 description 1
- 230000000750 progressive effect Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
This specification embodiment discloses multilist relation query method, device and equipment.Scheme includes: to obtain the first query statement；Obtain the second query statement；Querying condition field having the same in first query statement and second query statement；Estimate the first Query Cost of first query statement；Estimate the second Query Cost of second query statement；Determine minimum Query Cost；It is inquired according to the corresponding query statement of the minimum Query Cost, obtains the first query results；According to first query results, other query statement is rewritten；It is inquired according to the revised query statement, obtains the second query results；According to first query results and second query results, correlation inquiry result set is obtained.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a method, an apparatus, and a device for multi-table association query.
Background
In the prior art, the data of the user is stored in a database in a multi-table manner. In order to query certain behaviors of a user, a multi-table association query needs to be performed. A multi-table associative query, generally refers to querying records from multiple data tables that have some common attributes.
For example, assume three data tables, Table A, Table B, and Table C. The user number and the user name are stored in the table A, the user number and the user age are stored in the table B, and the user number and the user gender are stored in the table C. Each user has a unique number. Then, if a male with the name of "three-Zhang" and the age of 20 is to be queried, a multi-table association query needs to be performed according to the three tables, so as to obtain the query result.
However, in the conventional multi-table association query method, query results of each data table related to a multi-table association query script are generally obtained in a memory of a query initiating device, and then the query results of each data table are filtered. The method carries out multi-table query and has low efficiency.
Disclosure of Invention
In view of this, embodiments of the present application provide a method, an apparatus, and a device for multi-table association query, which are used to improve query efficiency.
In order to solve the above technical problem, the embodiments of the present specification are implemented as follows:
the multi-table association query method provided by the embodiment of the specification comprises the following steps:
obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
estimating a first query cost of the first query statement;
estimating a second query cost for the second query statement;
comparing the first query cost with the second query cost to determine a minimum query cost;
inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
rewriting additional query statements according to the first query result set;
inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
and obtaining an associated query result set according to the first query result set and the second query result set.
An embodiment of the present specification provides a multi-table association query apparatus, including:
the first query statement acquisition module is used for acquiring a first query statement aiming at a first table; the first query statement is obtained by analyzing a multi-table association query script;
a second query statement acquisition module, configured to acquire a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
a first query cost estimation module, configured to estimate a first query cost of the first query statement;
a second query cost estimation module, configured to estimate a second query cost of the second query statement;
the query cost comparison module is used for comparing the first query cost with the second query cost and determining the minimum query cost;
the first query module is used for querying according to the query statement corresponding to the minimum query cost to obtain a first query result set;
a query statement rewriting module for rewriting another query statement according to the first query result set;
the second query module is used for querying according to the rewritten query statement to obtain a second query result set;
and the association result generation module is used for obtaining an association query result set according to the first query result set and the second query result set.
An apparatus provided by an embodiment of the present specification includes:
at least one processor; and the number of the first and second groups,
a memory communicatively coupled to the at least one processor; wherein,
the memory stores instructions executable by the at least one processor to enable the at least one processor to:
obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
estimating a first query cost of the first query statement;
estimating a second query cost for the second query statement;
comparing the first query cost with the second query cost to determine a minimum query cost;
inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
rewriting additional query statements according to the first query result set;
inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
and obtaining an associated query result set according to the first query result set and the second query result set.
The embodiment of the specification adopts at least one technical scheme which can achieve the following beneficial effects:
by estimating the query cost of each table, the query statement with lower query cost is executed firstly, and then the other query statement is rewritten according to the obtained query result set, so that the query range of the other query statement can be reduced, the query cost of the other query statement can be reduced, and the query efficiency of multi-table association query can be improved.
Drawings
The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiment(s) of the application and together with the description serve to explain the application and not to limit the application. In the drawings:
FIG. 1 is a diagram illustrating an application scenario of a multi-table association query method in an embodiment of the present disclosure;
FIG. 2 is a flowchart illustrating a multi-table association query method provided in an embodiment of the present disclosure;
FIG. 3 is a schematic structural diagram of a multi-table association lookup apparatus corresponding to FIG. 2 according to an embodiment of the present disclosure;
fig. 4 is a schematic structural diagram of a multi-table association query device corresponding to fig. 2 provided in an embodiment of this specification.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the technical solutions of the present application will be described in detail and completely with reference to the following specific embodiments of the present application and the accompanying drawings. It should be apparent that the described embodiments are only some of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
The technical solutions provided by the embodiments of the present application are described in detail below with reference to the accompanying drawings.
Fig. 1 is a schematic diagram of an application scenario of a multi-table association query method in an embodiment of the present specification. As shown in fig. 1, the workstation 10 is communicably connected to the databases 11, 12, and 13. The databases 11, 12, and 13 store a large number of data tables, respectively. The databases 11, 12 and 13 may be located at different locations, respectively. The databases 11, 12 and 13 may be different types of databases. When the workstation 10 acquires the multi-table association query script and the tables involved in the script are located in the database 11, the database 12 and the database 13, respectively, it is required to acquire relevant data information from the database 11, the database 12 and the database 13, respectively.
Fig. 2 is a flowchart illustrating a multi-table association query method according to an embodiment of the present disclosure. From the viewpoint of a program, the main body of execution of the flow may be a program or an application client mounted on an application server or a workstation computer.
As shown in fig. 2, the process may include the following steps:
s201: obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
the multi-table association query script can be a script adopting SQL statements.
The first table in the embodiment of the present specification may be a data table stored in a database.
Open source software can be adopted to analyze the multi-table association query script to obtain query statements for each table.
S202: obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
for example, the following is a simple multi-table association query script:
in the above paragraph of script, tables a and b are referred to. The script can be parsed by open source software to obtain a query statement for table a and a query statement for table b.
The query statement for table a may be
The query statement for table a may be
It can be seen that the query statement for table a has the same field id as the query condition in the query statement for table b.
S203: estimating a first query cost of the first query statement;
the query cost may refer to the time consumed by executing the query or the amount of occupied hardware resources. In the embodiment of the present specification, the query cost may be estimated from the perspective of the data amount of the query result, or from the perspective of the data amount of the metadata in the data table, or may be estimated in a manner of comprehensively considering the data amount of the query result, the occupancy rate of the memory, and the occupancy rate of the CPU.
The query cost can be estimated by adopting a dynamic programming algorithm and a genetic algorithm in combination with database metadata.
S204: estimating a second query cost for the second query statement;
s205: comparing the first query cost with the second query cost to determine a minimum query cost;
the result of the query cost may be a specific numerical value. Comparing the size of the specific numerical value can determine the minimum query cost.
S206: inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
for the query statement with the minimum query cost, the query can be performed first.
S207: rewriting additional query statements according to the first query result set;
additional query statements may be augmented with qualifiers using the first set of query results as newly augmented qualifiers.
When the query cost of the first query statement is minimal, the further query statement is a second query statement; the further query statement is the first query statement when the query cost of the second query statement is minimal.
S208: inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
since the restriction condition of the query statement after rewriting is increased, the data amount of the second query result set is smaller than the query result of the query statement before rewriting.
S209: and obtaining an associated query result set according to the first query result set and the second query result set.
Specifically, the first query result set and the second query result set may be connected through a connection algorithm to obtain a final associated query result set.
In the example of query statements given above, assuming that the query cost for the query statement for table a is 100 (indicating that 100 pieces of query results are expected) and the query cost for the query statement for table b is 1000 (indicating that 1000 pieces of query results are expected), the query statement for table a may be executed first. Assuming that the result set obtained after execution is d (a) { id } ═ 1,2,3,. 100}, the query statement for table b can be rewritten as select b.age, b.sex from b where b.aid in (1,2,3,. 100) and b.age >20and b.sex ═ male'. After rewriting, it means that the query statement for table b only needs to query from data with id between 1 and 100, the query scope is reduced, and the query result for table b is within 100. The query cost of the rewritten query statement is smaller than the query cost before rewriting. More specifically, before rewriting, when executing the query statement for the table b, the device of the workstation needs to read about 1000 pieces of data from the database into its own memory, and then filter the data related to the table a from the about 1000 pieces of data, so as to obtain the final 100 pieces of data. After rewriting, the device of the workstation only needs to read the data within 100 items from the database into its own memory, and then connect the data within 100 items with the query result set of the table a.
In summary, in the method in fig. 2, by estimating the query cost of each table, the query statement with the smaller query cost is executed first, and then the other query statement is rewritten according to the obtained query result set, so that the query range of the other query statement can be narrowed, the query cost of the other query statement can be reduced, and the query efficiency of the multi-table association query can be improved.
Based on the method of fig. 2, the present specification also provides some specific embodiments of the method, which are described below.
In the embodiment of the present specification, step S207: rewriting additional query statements according to the first query result set may specifically include:
defining the same field in the further query statement with the value of the first query result set.
In practical applications, when the data amount of a certain data table is large, the estimation of the query cost of the table also consumes a large amount of resources. When the amount of data exceeds a certain value, the cost of evaluating the query against the table may cause a crash of the database. In order to avoid such a situation, in the embodiment of the present specification, step S203: before estimating the first query cost of the first query statement, the method may further include the following steps:
judging whether the data volume of the first table is larger than a preset threshold value or not to obtain a first judgment result;
the estimating the first query cost of the first query statement specifically includes:
and when the first judgment result shows that the data volume of the first table is not larger than the preset threshold, estimating a first query cost of the first query statement.
When the first determination result indicates that the data amount of the first table is greater than the preset threshold, the step of estimating the first query cost of the first query statement may no longer be performed.
In the above step, the specific value of the preset threshold may be set according to the performance of the database.
And when the first judgment result shows that the data volume of the first table is not larger than the preset threshold, estimating the first query cost of the first query statement, and not causing the crash of the database.
When the first judgment result indicates that the data size of the first table is greater than the preset threshold, the following steps may be directly performed without estimating the first query cost of the first query statement:
inquiring according to the first inquiry statement to obtain a third inquiry result set;
inquiring according to the second inquiry statement to obtain a fourth inquiry result set;
and obtaining an associated query result set according to the third query result set and the fourth query result set.
In the steps, the query statement is not rewritten any more, and the original first query statement and the original second query statement are directly adopted for query, so that the crash of the database is avoided.
In the above example, a multi-table associative query is directed to two tables. In practical application, a multi-table association query can be performed on three tables or more tables.
If multi-table association query is performed on three tables, the following steps can be adopted:
obtaining a third query statement for a third table; the query conditions in the third query statement have the same field;
calculating a third query cost of the third query statement;
after the step S205 determines the minimum query cost, and before the step S206 performs query according to the query statement corresponding to the minimum query cost, the method may further include:
comparing the third query cost to the minimum query cost;
if the third query cost is less than the minimum query cost, performing query according to the third query statement to obtain a fifth query result set;
rewriting the query statement corresponding to the minimum query cost according to the fifth query result set;
step S206, performing query according to the query statement corresponding to the minimum query cost, which may specifically include:
and inquiring according to the inquiry statement corresponding to the rewritten minimum inquiry cost.
In the above step, when three tables are involved, the query costs of the three tables can be estimated respectively, and the table with the minimum query cost is selected from the three estimation results and is queried first. And after the query result is obtained, rewriting the query statement of the table with the intermediate query cost according to the query result. And after the query result of the second table is obtained, rewriting the query statement of the third table according to the query result of the second table, and then querying, thereby realizing statement optimization of multi-table association query of the three tables.
Furthermore, according to the query mode of the three tables in the above example, it can be known that, when there are more tables to be queried in association, the query costs of the tables can be estimated, the tables with the minimum query costs are queried according to the query costs obtained through estimation, the query statements with the minimum query costs are rewritten according to the query results, and the query and the rewriting are sequentially performed until the query results of the tables with the rewritten query statements are obtained.
In practical application, the execution process of the query statement is performed circularly. Sometimes, after the data in the data table has been completely queried, the query process is still in circulation, which may cause the system to still perform circulation query, resulting in wasted query time.
In order to avoid the above situation, in this embodiment of the present specification, after performing query according to the query statement corresponding to the minimum query cost, the method may further include the step of:
and marking the state of the table aimed at by the query statement corresponding to the minimum query cost as queried.
Similarly, in this embodiment of the present specification, after performing a query according to the rewritten query statement, the method may further include:
and marking the state of the table aimed at by the rewritten query statement as queried.
The state of the table is queried, which means that the table is queried completely, and after the subsequent process identifies the state mark, the circular query on the table can not be performed any more.
In practical application, each query result set may include a plurality of pieces of query data, and each query result set is associated to obtain a final associated query result set. The specific association method may be performed by external connection. The external connection comprises a left connection and a right connection. The specific external connection mode is determined by the initial multi-table association query script. In the original multi-table associative query script, left join can be used to represent left join and right join can be used to represent right join.
It should be noted that, in the embodiments of the present specification, the first query statement, the second query statement, and the like are obtained by analyzing the initial multi-table related query script. That is, before the first query statement for the first table is obtained, the following steps may be further included:
acquiring a multi-table association query script;
and analyzing the script to obtain the first query statement and the second query statement.
The analysis of the multi-table association query script can be realized by adopting open source software such as Jsqlparser or druid.
Based on the same idea, the embodiment of the present specification further provides a device corresponding to the above method. Fig. 3 is a schematic structural diagram of a multi-table association query apparatus corresponding to fig. 2 according to an embodiment of the present disclosure. As shown in fig. 3, the apparatus may include:
a first query statement obtaining module 301, configured to obtain a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
a second query statement obtaining module 302, configured to obtain a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
a first query cost estimation module 303, configured to estimate a first query cost of the first query statement;
a second query cost evaluation module 304, configured to evaluate a second query cost of the second query statement;
a query cost comparing module 305, configured to compare the first query cost with the second query cost, and determine a minimum query cost;
a first query module 306, configured to perform query according to the query statement corresponding to the minimum query cost to obtain a first query result set;
a query statement rewriting module 307, configured to rewrite another query statement according to the first query result set;
a second query module 308, configured to perform a query according to the rewritten query statement to obtain a second query result set;
and an association result generating module 309, configured to obtain an association query result set according to the first query result set and the second query result set.
Optionally, the query statement rewriting module 307 may specifically include:
a query statement rewrite unit configured to define the same field in the other query statement by a value of the first query result set.
Optionally, the apparatus may further include:
the first judging module is used for judging whether the data volume of the first table is larger than a preset threshold value or not before estimating the first query cost of the first query statement to obtain a first judging result;
the first query cost estimation module 303 may be specifically configured to:
and when the first judgment result shows that the data volume of the first table is not larger than the preset threshold, estimating a first query cost of the first query statement.
Optionally, the apparatus may further include:
the third query module is used for querying according to the first query statement to obtain a third query result set when the first judgment result indicates that the data volume of the first table is larger than the preset threshold;
the fourth query module is used for querying according to the second query statement to obtain a fourth query result set;
the association result generating module 309 is further configured to obtain an association query result set according to the third query result set and the fourth query result set.
Optionally, the apparatus may further include:
a third query statement acquisition module configured to acquire a third query statement for a third table; the query conditions in the third query statement have the same field;
a third query cost estimation module for estimating a third query cost of the third query statement;
the query cost comparing module 305 is further configured to, after determining a minimum query cost and before performing query according to the query statement corresponding to the minimum query cost, compare the third query cost with the minimum query cost;
the fourth query module is configured to query according to the third query statement to obtain a fifth query result set if the third query cost is smaller than the minimum query cost;
the query statement rewriting module 307 is further configured to rewrite the query statement corresponding to the minimum query cost according to the fifth query result set;
the first query module 306 is further configured to:
and inquiring according to the inquiry statement corresponding to the rewritten minimum inquiry cost.
Optionally, the apparatus may further include:
and the first marking module is used for marking the state of the table aimed at by the query statement corresponding to the minimum query cost as queried after querying according to the query statement corresponding to the minimum query cost.
Optionally, the apparatus may further include:
and the second marking module is used for marking the state of the table aimed at by the rewritten query statement as queried after querying according to the rewritten query statement.
Optionally, the correlation result generating module 309 is specifically configured to:
and externally connecting the first query result set and the second query result set to obtain a connected query result set.
Optionally, the apparatus may further include:
the query script acquisition module is used for acquiring a multi-table association query script before acquiring a first query statement aiming at a first table;
and the script analysis module is used for analyzing the script to obtain the first query statement and the second query statement.
Based on the same idea, the embodiment of the present specification further provides a device corresponding to the above method.
Fig. 4 is a schematic structural diagram of a multi-table association query device corresponding to fig. 2 provided in an embodiment of this specification. As shown in fig. 4, the apparatus 400 may include:
at least one processor 410; and the number of the first and second groups,
a memory 430 communicatively coupled to the at least one processor; wherein,
the memory 430 stores instructions 420 executable by the at least one processor 410 to enable the at least one processor 410 to:
obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
estimating a first query cost of the first query statement;
estimating a second query cost for the second query statement;
comparing the first query cost with the second query cost to determine a minimum query cost;
inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
rewriting additional query statements according to the first query result set;
inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
and obtaining an associated query result set according to the first query result set and the second query result set.
In the 90 s of the 20 th century, improvements in a technology could clearly distinguish between improvements in hardware (e.g., improvements in circuit structures such as diodes, transistors, switches, etc.) and improvements in software (improvements in process flow). However, as technology advances, many of today's process flow improvements have been seen as direct improvements in hardware circuit architecture. Designers almost always obtain the corresponding hardware circuit structure by programming an improved method flow into the hardware circuit. Thus, it cannot be said that an improvement in the process flow cannot be realized by hardware physical modules. For example, a Programmable Logic Device (PLD), such as a Field Programmable Gate Array (FPGA), is an integrated circuit whose Logic functions are determined by programming the Device by a user. A digital system is "integrated" on a PLD by the designer's own programming without requiring the chip manufacturer to design and fabricate application-specific integrated circuit chips. Furthermore, nowadays, instead of manually making an integrated Circuit chip, such Programming is often implemented by "logic compiler" software, which is similar to a software compiler used in program development and writing, but the original code before compiling is also written by a specific Programming Language, which is called Hardware Description Language (HDL), and HDL is not only one but many, such as abel (advanced Boolean Expression Language), ahdl (alternate Language Description Language), traffic, pl (core unified Programming Language), HDCal, JHDL (Java Hardware Description Language), langue, Lola, HDL, laspam, hardsradware (Hardware Description Language), vhjhd (Hardware Description Language), and vhigh-Language, which are currently used in most common. It will also be apparent to those skilled in the art that hardware circuitry that implements the logical method flows can be readily obtained by merely slightly programming the method flows into an integrated circuit using the hardware description languages described above.
The controller may be implemented in any suitable manner, for example, the controller may take the form of, for example, a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro) processor, logic gates, switches, an Application Specific Integrated Circuit (ASIC), a programmable logic controller, and an embedded microcontroller, examples of which include, but are not limited to, the following microcontrollers: ARC 625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicone Labs C8051F320, the memory controller may also be implemented as part of the control logic for the memory. Those skilled in the art will also appreciate that, in addition to implementing the controller as pure computer readable program code, the same functionality can be implemented by logically programming method steps such that the controller is in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers and the like. Such a controller may thus be considered a hardware component, and the means included therein for performing the various functions may also be considered as a structure within the hardware component. Or even means for performing the functions may be regarded as being both a software module for performing the method and a structure within a hardware component.
The systems, devices, modules or units illustrated in the above embodiments may be implemented by a computer chip or an entity, or by a product with certain functions. One typical implementation device is a computer. In particular, the computer may be, for example, a personal computer, a laptop computer, a cellular telephone, a camera phone, a smartphone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.
For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may 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, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
In a typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.
Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.
It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
The application may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The application may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.
The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.
The above description is only an example of the present application and is not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.
Claims (19)
1. A multi-table association query method comprises the following steps:
obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
estimating a first query cost of the first query statement;
estimating a second query cost for the second query statement;
comparing the first query cost with the second query cost to determine a minimum query cost;
inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
rewriting additional query statements according to the first query result set;
inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
and obtaining an associated query result set according to the first query result set and the second query result set.
2. The method of claim 1, wherein rewriting additional query statements according to the first set of query results specifically comprises:
defining the same field in the further query statement with the value of the first query result set.
3. The method of claim 1, prior to estimating the first query cost of the first query statement, further comprising:
judging whether the data volume of the first table is larger than a preset threshold value or not to obtain a first judgment result;
the estimating the first query cost of the first query statement specifically includes:
and when the first judgment result shows that the data volume of the first table is not larger than the preset threshold, estimating a first query cost of the first query statement.
4. The method of claim 3, further comprising:
when the first judgment result shows that the data volume of the first table is larger than the preset threshold value, inquiring according to the first inquiry statement to obtain a third inquiry result set;
inquiring according to the second inquiry statement to obtain a fourth inquiry result set;
and obtaining an associated query result set according to the third query result set and the fourth query result set.
5. The method of claim 1, further comprising:
obtaining a third query statement for a third table; the query conditions in the third query statement have the same field;
estimating a third query cost of the third query statement;
after the minimum query cost is determined and before the query is performed according to the query statement corresponding to the minimum query cost, the method further includes:
comparing the third query cost to the minimum query cost;
if the third query cost is less than the minimum query cost, performing query according to the third query statement to obtain a fifth query result set;
rewriting the query statement corresponding to the minimum query cost according to the fifth query result set;
the querying according to the query statement corresponding to the minimum query cost specifically includes:
and inquiring according to the inquiry statement corresponding to the rewritten minimum inquiry cost.
6. The method of claim 1, after the query is performed according to the query statement corresponding to the minimum query cost, further comprising:
and marking the state of the table aimed at by the query statement corresponding to the minimum query cost as queried.
7. The method of claim 1, after the querying according to the rewritten query statement, further comprising:
and marking the state of the table aimed at by the rewritten query statement as queried.
8. The method of claim 1, wherein obtaining an associated query result set according to the first query result set and the second query result set specifically comprises:
and externally connecting the first query result set and the second query result set to obtain a connected query result set.
9. The method of claim 1, prior to obtaining the first query statement for the first table, further comprising:
acquiring a multi-table association query script;
and analyzing the script to obtain the first query statement and the second query statement.
10. A multi-table associative lookup apparatus, comprising:
the first query statement acquisition module is used for acquiring a first query statement aiming at a first table; the first query statement is obtained by analyzing a multi-table association query script;
a second query statement acquisition module, configured to acquire a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
a first query cost estimation module, configured to estimate a first query cost of the first query statement;
a second query cost estimation module, configured to estimate a second query cost of the second query statement;
the query cost comparison module is used for comparing the first query cost with the second query cost and determining the minimum query cost;
the first query module is used for querying according to the query statement corresponding to the minimum query cost to obtain a first query result set;
a query statement rewriting module for rewriting another query statement according to the first query result set;
the second query module is used for querying according to the rewritten query statement to obtain a second query result set;
and the association result generation module is used for obtaining an association query result set according to the first query result set and the second query result set.
11. The apparatus according to claim 10, wherein the query statement rewrite module specifically includes:
a query statement rewrite unit configured to define the same field in the other query statement by a value of the first query result set.
12. The apparatus of claim 10, further comprising:
the first judging module is used for judging whether the data volume of the first table is larger than a preset threshold value or not before estimating the first query cost of the first query statement to obtain a first judging result;
the first query cost estimation module is specifically configured to:
and when the first judgment result shows that the data volume of the first table is not larger than the preset threshold, estimating a first query cost of the first query statement.
13. The apparatus of claim 12, further comprising:
the third query module is used for querying according to the first query statement to obtain a third query result set when the first judgment result indicates that the data volume of the first table is larger than the preset threshold;
the fourth query module is used for querying according to the second query statement to obtain a fourth query result set;
and the association result generation module is further configured to obtain an association query result set according to the third query result set and the fourth query result set.
14. The apparatus of claim 10, further comprising:
a third query statement acquisition module configured to acquire a third query statement for a third table; the query conditions in the third query statement have the same field;
a third query cost estimation module for estimating a third query cost of the third query statement;
the query cost comparison module is further configured to compare the third query cost with the minimum query cost after the minimum query cost is determined and before the query statement corresponding to the minimum query cost is queried;
the fourth query module is configured to query according to the third query statement to obtain a fifth query result set if the third query cost is smaller than the minimum query cost;
the query statement rewriting module is further configured to rewrite the query statement corresponding to the minimum query cost according to the fifth query result set;
the first query module is further configured to:
and inquiring according to the inquiry statement corresponding to the rewritten minimum inquiry cost.
15. The apparatus of claim 10, further comprising:
and the first marking module is used for marking the state of the table aimed at by the query statement corresponding to the minimum query cost as queried after querying according to the query statement corresponding to the minimum query cost.
16. The apparatus of claim 10, further comprising:
and the second marking module is used for marking the state of the table aimed at by the rewritten query statement as queried after querying according to the rewritten query statement.
17. The apparatus according to claim 10, wherein the association result generation module is specifically configured to:
and externally connecting the first query result set and the second query result set to obtain a connected query result set.
18. The apparatus of claim 10, further comprising:
the query script acquisition module is used for acquiring a multi-table association query script before acquiring a first query statement aiming at a first table;
and the script analysis module is used for analyzing the script to obtain the first query statement and the second query statement.
19. A multi-table associative lookup apparatus comprising:
at least one processor; and the number of the first and second groups,
a memory communicatively coupled to the at least one processor; wherein,
the memory stores instructions executable by the at least one processor to enable the at least one processor to:
obtaining a first query statement for a first table; the first query statement is obtained by analyzing a multi-table association query script;
obtaining a second query statement for a second table; the second query statement is obtained by analyzing the multi-table association query script; the query conditions in the first query statement and the second query statement have the same fields;
estimating a first query cost of the first query statement;
estimating a second query cost for the second query statement;
comparing the first query cost with the second query cost to determine a minimum query cost;
inquiring according to the inquiry statement corresponding to the minimum inquiry cost to obtain a first inquiry result set;
rewriting additional query statements according to the first query result set;
inquiring according to the rewritten inquiry statement to obtain a second inquiry result set;
and obtaining an associated query result set according to the first query result set and the second query result set.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811144099.7A CN109656946B (en) | 2018-09-29 | 2018-09-29 | Multi-table association query method, device and equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811144099.7A CN109656946B (en) | 2018-09-29 | 2018-09-29 | Multi-table association query method, device and equipment |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109656946A true CN109656946A (en) | 2019-04-19 |
| CN109656946B CN109656946B (en) | 2022-12-16 |
Family
ID=66110687
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811144099.7A Active CN109656946B (en) | 2018-09-29 | 2018-09-29 | Multi-table association query method, device and equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109656946B (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112307050A (en) * | 2020-09-16 | 2021-02-02 | 苏宁云计算有限公司 | Identification method and device for repeated correlation calculation and computer system |
| CN113297248A (en) * | 2020-07-27 | 2021-08-24 | 阿里巴巴集团控股有限公司 | Data processing method, resource allocation method, device, equipment and readable storage medium |
| CN113434529A (en) * | 2021-06-22 | 2021-09-24 | 青岛海尔科技有限公司 | Business data query method and device, storage medium, processor and electronic equipment |
| CN115062044A (en) * | 2022-06-17 | 2022-09-16 | 中国银联股份有限公司 | Data query method, device, equipment and storage medium |
Citations (14)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1556482A (en) * | 2003-12-31 | 2004-12-22 | 中兴通讯股份有限公司 | Data processing method for realizing data base multitable inguiry |
| CN101561817A (en) * | 2009-06-02 | 2009-10-21 | 天津大学 | Conversion algorithm from XQuery to SQL query language and method for querying relational data |
| CN101739453A (en) * | 2009-12-17 | 2010-06-16 | 中国电力科学研究院 | Method and device for carrying out condition query on database table |
| US20100185643A1 (en) * | 2009-01-20 | 2010-07-22 | Oracle International Corporation | Techniques for automated generation of queries for querying ontologies |
| CN103092998A (en) * | 2013-02-21 | 2013-05-08 | 用友软件股份有限公司 | Data query system and data query method |
| US20130173588A1 (en) * | 2011-12-28 | 2013-07-04 | Teradata Us, Inc. | Techniques for updating join indexes |
| US20130262498A1 (en) * | 2012-03-30 | 2013-10-03 | International Business Machines Corporation | Database query optimization |
| US20140032588A1 (en) * | 2012-07-29 | 2014-01-30 | Sergiy GETMANETS | Systems And Methods For Providing A Simplified Application Programming Interface For Converting From Two-Dimensional Query Languages Into Multi-Dimensional Query Languages To Query Multi-Dimensional Data Sources And MDX Servers |
| CN105574052A (en) * | 2014-11-06 | 2016-05-11 | 中兴通讯股份有限公司 | Database query method and apparatus |
| CN105630881A (en) * | 2015-12-18 | 2016-06-01 | 陕西师范大学 | Data storage method and query method for RDF (Resource Description Framework) |
| CN106250519A (en) * | 2016-08-04 | 2016-12-21 | 曙光信息产业(北京)有限公司 | Data query method and apparatus for parallel database |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
| CN108073641A (en) * | 2016-11-18 | 2018-05-25 | 华为技术有限公司 | The method and apparatus for inquiring about tables of data |
| CN108509637A (en) * | 2018-04-10 | 2018-09-07 | 口碑(上海)信息技术有限公司 | Tables of data relation query method and device |
-
2018
- 2018-09-29 CN CN201811144099.7A patent/CN109656946B/en active Active
Patent Citations (14)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1556482A (en) * | 2003-12-31 | 2004-12-22 | 中兴通讯股份有限公司 | Data processing method for realizing data base multitable inguiry |
| US20100185643A1 (en) * | 2009-01-20 | 2010-07-22 | Oracle International Corporation | Techniques for automated generation of queries for querying ontologies |
| CN101561817A (en) * | 2009-06-02 | 2009-10-21 | 天津大学 | Conversion algorithm from XQuery to SQL query language and method for querying relational data |
| CN101739453A (en) * | 2009-12-17 | 2010-06-16 | 中国电力科学研究院 | Method and device for carrying out condition query on database table |
| US20130173588A1 (en) * | 2011-12-28 | 2013-07-04 | Teradata Us, Inc. | Techniques for updating join indexes |
| US20130262498A1 (en) * | 2012-03-30 | 2013-10-03 | International Business Machines Corporation | Database query optimization |
| US20140032588A1 (en) * | 2012-07-29 | 2014-01-30 | Sergiy GETMANETS | Systems And Methods For Providing A Simplified Application Programming Interface For Converting From Two-Dimensional Query Languages Into Multi-Dimensional Query Languages To Query Multi-Dimensional Data Sources And MDX Servers |
| CN103092998A (en) * | 2013-02-21 | 2013-05-08 | 用友软件股份有限公司 | Data query system and data query method |
| CN105574052A (en) * | 2014-11-06 | 2016-05-11 | 中兴通讯股份有限公司 | Database query method and apparatus |
| CN105630881A (en) * | 2015-12-18 | 2016-06-01 | 陕西师范大学 | Data storage method and query method for RDF (Resource Description Framework) |
| CN106250519A (en) * | 2016-08-04 | 2016-12-21 | 曙光信息产业(北京)有限公司 | Data query method and apparatus for parallel database |
| CN108073641A (en) * | 2016-11-18 | 2018-05-25 | 华为技术有限公司 | The method and apparatus for inquiring about tables of data |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
| CN108509637A (en) * | 2018-04-10 | 2018-09-07 | 口碑(上海)信息技术有限公司 | Tables of data relation query method and device |
Non-Patent Citations (2)
| Title |
|---|
| 何志学等: "一种基于语义信息的XML Twig查询处理方法", 《微电子学与计算机》 * |
| 陈荦等: "基于尺度选择性的空间数据源选择与预取策略", 《国防科技大学学报》 * |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113297248A (en) * | 2020-07-27 | 2021-08-24 | 阿里巴巴集团控股有限公司 | Data processing method, resource allocation method, device, equipment and readable storage medium |
| CN112307050A (en) * | 2020-09-16 | 2021-02-02 | 苏宁云计算有限公司 | Identification method and device for repeated correlation calculation and computer system |
| CN112307050B (en) * | 2020-09-16 | 2022-11-15 | 苏宁云计算有限公司 | Identification method and device for repeated correlation calculation and computer system |
| CN113434529A (en) * | 2021-06-22 | 2021-09-24 | 青岛海尔科技有限公司 | Business data query method and device, storage medium, processor and electronic equipment |
| CN113434529B (en) * | 2021-06-22 | 2023-06-16 | 青岛海尔科技有限公司 | Service data query method and device, storage medium, processor and electronic equipment |
| CN115062044A (en) * | 2022-06-17 | 2022-09-16 | 中国银联股份有限公司 | Data query method, device, equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109656946B (en) | 2022-12-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107562775B (en) | Data processing method and device based on block chain | |
| CN109656946B (en) | Multi-table association query method, device and equipment | |
| JP7021228B2 (en) | Blockchain-based data storage and query methods and devices | |
| CN107526777B (en) | Method and equipment for processing file based on version number | |
| EP3547168A1 (en) | Block chain based data processing method and device | |
| CN107622080B (en) | Data processing method and equipment | |
| CN110399359B (en) | Data backtracking method, device and equipment | |
| US20240126465A1 (en) | Data storage methods, apparatuses, devices, and storage media | |
| CN110263050B (en) | Data processing method, device, equipment and storage medium | |
| CN115934161A (en) | Code change influence analysis method, device and equipment | |
| CN106156050B (en) | Data processing method and device | |
| CN109582300A (en) | Code analysis on altered project method, device and equipment based on path | |
| CN112182116B (en) | Data exploration method and device | |
| CN116303625B (en) | Data query method and device, storage medium and electronic equipment | |
| WO2024046015A1 (en) | Data query method and apparatus, storage medium, and electronic device | |
| US9824122B2 (en) | Requests for source code text | |
| CN114238345A (en) | Database processing and data query method and device | |
| CN111177506A (en) | Classification storage method and system based on big data | |
| CN110990421A (en) | Data query method, device and system | |
| CN108415914B (en) | Method, device and equipment for inquiring information in visual object | |
| CN111339117B (en) | Data processing method, device and equipment | |
| CN115878654A (en) | Data query method, device, equipment and storage medium | |
| CN112907198B (en) | Service state circulation maintenance method and device and electronic equipment | |
| TWI748247B (en) | Method, system and electronic equipment for generating statistical information | |
| CN114547086A (en) | Data processing method, device, equipment and computer readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| TA01 | Transfer of patent application right |
Effective date of registration: 20200927 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Applicant after: Advanced innovation technology Co.,Ltd. Address before: A four-storey 847 mailbox in Grand Cayman Capital Building, British Cayman Islands Applicant before: Alibaba Group Holding Ltd. Effective date of registration: 20200927 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Applicant after: Innovative advanced technology Co.,Ltd. Address before: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman Islands Applicant before: Advanced innovation technology Co.,Ltd. |
|
| TA01 | Transfer of patent application right | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |