CN114637759A - Data query method, electronic device and storage medium - Google Patents
Data query method, electronic device and storage medium Download PDFInfo
- Publication number
- CN114637759A CN114637759A CN202011484008.1A CN202011484008A CN114637759A CN 114637759 A CN114637759 A CN 114637759A CN 202011484008 A CN202011484008 A CN 202011484008A CN 114637759 A CN114637759 A CN 114637759A
- Authority
- CN
- China
- Prior art keywords
- query
- sub
- target
- hashmap
- instruction
- 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
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2445—Data retrieval commands; View definitions
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2255—Hash tables
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The invention provides a data query method, electronic equipment and a storage medium, wherein the data query method comprises the following steps: acquiring main sub-query instructions, and determining the main query instructions and the sub-query instructions from the main sub-query instructions; acquiring a target Hashmap according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction; and determining a target query result from the target Hashmap according to the main query instruction. According to the scheme provided by the embodiment of the invention, the Hashmap can be utilized to improve the data query efficiency, effectively reduce the scanning times of sub-queries and reduce the message backlog.
Description
Technical Field
The present invention relates to, but not limited to, the field of data storage, and in particular, to a data query method, an electronic device, and a storage medium.
Background
With the development of internet technology and computer technology, the amount of data in databases has increased exponentially, and distributed databases are widely used to improve data storage capacity and data processing capacity. The distributed Database system divides data according to a certain rule, distributes the data to different Database nodes, and when data query is carried out, each Database node sends the data meeting a query instruction to a Database intermediate node (dbproxy), and then the Database intermediate node finishes screening and summarizing the data. In order to improve the Query efficiency in mass data, in addition to writing an efficient Structured Query Language (SQL) instruction, the distributed database is required to have a better complex SQL processing performance, especially the processing performance of main and sub queries. The association sub-query is one of the main sub-queries, and the queried target data needs to satisfy the conditions of the main query and the sub-queries at the same time. However, when the data size of the sub-query is large, the full-table scan of the sub-query needs to consume a lot of time, which results in message backlog, and the query efficiency is low, affecting the performance of the distributed database.
Disclosure of Invention
The following is a summary of the subject matter described in detail herein. This summary is not intended to limit the scope of the claims.
The embodiment of the invention provides a data query method, electronic equipment and a storage medium, which can improve the efficiency of data query.
In a first aspect, an embodiment of the present invention provides a data query method, where the data query method includes:
acquiring main sub-query instructions, and determining the main query instructions and the sub-query instructions from the main sub-query instructions;
obtaining a target Hashmap (Hashmap) according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction;
and determining a target query result from the target Hashmap according to the main query instruction.
The embodiment of the invention comprises the following steps: acquiring main sub-query instructions, and determining the main query instructions and the sub-query instructions from the main sub-query instructions; acquiring a target Hashmap according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction; and determining a target query result from the target Hashmap according to the main query instruction. According to the scheme provided by the embodiment of the invention, the Hashmap can be utilized to improve the data query efficiency, effectively reduce the scanning times of sub-queries and reduce the message backlog.
In a second aspect, an embodiment of the present invention provides an electronic device, including: a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the data query method according to the first aspect when executing the computer program.
In a third aspect, an embodiment of the present invention provides a computer-readable storage medium storing computer-executable instructions for performing the data query method according to the first aspect.
Additional features and advantages of the invention will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. The objectives and other advantages of the invention will be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.
Drawings
The accompanying drawings are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the example serve to explain the principles of the invention and not to limit the invention.
FIG. 1 is a flow chart of a data query method provided by an embodiment of the invention;
fig. 2 is a flowchart of obtaining a target Hashmap according to another embodiment of the present invention;
fig. 3 is a flowchart of obtaining a target Hashmap according to a matching result according to another embodiment of the present invention;
FIG. 4 is a flow diagram of a hash hit provided by another embodiment of the invention;
FIG. 5 is a flow diagram of a hash hit based on a new sub-query result according to another embodiment of the invention;
fig. 6 is a flowchart of setting a hash key of a target Hashmap according to another embodiment of the present invention;
FIG. 7 is a flow diagram of sub-query deduplication provided by another embodiment of the present invention;
FIG. 8 is a flow diagram providing for determining a target query result based on multiple target Hashmaps according to another embodiment of the present invention;
FIG. 9 is a schematic diagram of a distributed database topology provided by another embodiment of the present invention;
fig. 10 is a schematic structural diagram of an electronic device according to another embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
It should be noted that although functional blocks are partitioned in a schematic diagram of an apparatus and a logical order is shown in a flowchart, in some cases, the steps shown or described may be performed in a different order than the partitioning of blocks in the apparatus or the order in the flowchart. The terms "first," "second," and the like in the description, in the claims, or in the drawings described above, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order.
The invention provides a data query method, electronic equipment and a storage medium, which are used for acquiring main and sub query instructions and determining the main query instruction and the sub query instruction from the main and sub query instructions; acquiring a target Hashmap according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction; and determining a target query result from the target Hashmap according to the main query instruction. According to the scheme provided by the embodiment of the invention, the Hashmap can be utilized to improve the data query efficiency, effectively reduce the scanning times of sub-queries and reduce the message backlog.
It should be noted that the present invention does not relate to structural improvement of a distributed database, and the structure in the prior art is adopted, that is, the present invention includes a dbproxy and a plurality of database nodes, and the dbproxy is in communication connection with all the database nodes, and further details are not repeated in the following.
The embodiments of the present invention will be further explained with reference to the drawings.
As shown in fig. 1, fig. 1 is a flowchart of a data query method applied to a dbproxy of a distributed database according to an embodiment of the present invention, including but not limited to step S110, step S120, and step S130.
Step S110, main sub-query instructions are obtained, and the main query instructions and the sub-query instructions are determined from the main sub-query instructions.
It can be understood by those skilled in the art that after the dbproxy acquires the query instruction, the keyword can be determined as a main sub-query through a specific SQL keyword, for example, it is recognized that the query instruction includes a select statement, which does not limit the embodiment. It should be noted that, for the main sub-query instruction, the main sub-query instruction may be split into the main query instruction and the sub-query instruction by a set rule, for example, the sub-query is split from the main sub-query instruction according to a preset keyword where, and the query instruction corresponding to each keyword where is split into one sub-query instruction. It is understood that there may be a plurality of sub-queries for the main sub-query, and therefore, based on the specific situation of the main sub-query, a plurality of sub-query instructions may be determined from the main sub-query instruction in the above manner, and the embodiment does not limit the number of sub-query instructions.
For example, for the main sub-query instruction "select t1.a1from t1 where exists" (select t2.a2from t2where t1.b1 ═ t2.b2), "the main query instruction is" select t1.a1from t1, "and the sub-query instruction is" select t2.a2from t2where t1.b1 ═ t2.b2, "where t1 is the table corresponding to the main query, a1 and b1 are the values in t1, t2 is the table corresponding to the sub-query, and a2 and b2 are the values in t2. It should be noted that the above instructions are only used for exemplary illustration, and do not limit the technical solution of the present application, and are not described in detail later.
It should be noted that, when the query instruction obtained by the dbproxy is identified as an instruction other than the main sub-query, the query may be completed by using a method in the prior art, which is not within the scope of the present disclosure and will not be described in detail later.
It is understood that the main sub-query command can be sent to the dbproxy in any manner, such as in a common client access form, which is not limited in this embodiment.
And step S120, acquiring a target Hashmap according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction.
It is worth noting that after the main query instruction and the sub-query instruction are determined, whether the sub-query instruction is the equivalence association sub-query can be judged, and since the equivalence association sub-query is one of the association sub-queries, namely the condition in the association sub-query is the equivalence condition, the equivalence association sub-query is performed by Hashmap hash hit, so that query results can be obtained quickly, and query efficiency is improved. It should be noted that the determination of the equivalence association sub-query can be determined by the structure of the sub-query instruction, for example, referring to the query instruction "select t1.a1from t1 where exists (select t2.a2from t2where t1.b1 ═ t2.b 2" in the above embodiment), since the condition in the sub-query instruction is that the b1 value in t1 is equal to the b2 value in t2, it can be determined as the equivalence association sub-query, and the above instruction is only an example of the present embodiment and does not limit the protection scope of the present invention. It should be understood that the equivalence association sub-query may be identified by a specific SQL join character, for example, join characters such as "exist", "in", or "═ between the main query instruction and the sub-query instruction are included, and are not described herein again.
It should be noted that, through step S120, the sub-query result is obtained from each database node according to the sub-query instruction and the Hashmap is generated, which can ensure that the data for performing the main query certainly meets the conditions of the sub-query, so that only one sub-query scan needs to be completed, thereby effectively reducing the data amount of the sub-query, reducing the query time, and improving the query performance of the distributed database.
It is worth noting that after the Hashmap is generated according to the sub-query instruction, the Hashmap can be stored in the dbproxy, so that when the same sub-query is performed again, the corresponding Hashmap can be directly applied, the data query to the database nodes is reduced, and the data query efficiency is improved. It can be understood by those skilled in the art that, in the above technical solution for storing Hashmap and multiplexing, new data need to be inserted into a data table corresponding to a sub-query instruction when a corresponding data table of a database node is not changed, and details are not described herein.
And step S130, determining a target query result from the target Hashmap according to the main query instruction.
It should be noted that, after the target query result is determined, a query result summary table may be generated according to the target query result, and after each target query result is obtained, a piece of corresponding data is added to the query result summary table, so that after all sub-query results are matched, the query result summary table is directly obtained, and the statistical efficiency of the result obtained by data query can be further improved.
It should be noted that, the matching is performed in the target Hashmap, a hash hit manner may be adopted, that is, hash hit is performed in the Hashmap according to a matching value in the main query instruction, for example, the query instruction "select t1.a1from t1 where exists (select t2.a2from t2where t1.b1 ═ t2.b 2)" and the main query value of the main query instruction is b1, after the Hashmap is obtained, hash hit determination is performed according to a value of b1, if hit is successful, the target query result is output, and if hit is not successful, no query result meeting the query condition is obtained, and no output is performed.
In addition, referring to fig. 2, in an embodiment, the step S120 in the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S210, determining a sub-query matching field in the sub-query instruction, and determining the sub-query matching field as a target hash key;
step S220, obtaining pre-stored alternative Hashmap, and determining the Hashmap key of the alternative Hashmap as an alternative Hashmap;
and step S230, acquiring the target Hashmap according to the matching result of the target Hashmap and the alternative Hashmap.
Those skilled in the art will appreciate that the hash key can be used for the Hashmap identification, and therefore, matching by the hash key can quickly determine the target Hashmap corresponding to the same sub-query instruction.
It is worth noting that the alternative Hashmap may be generated and stored after the sub-query result is obtained from the database node according to the sub-query instruction, so that the Hashmap is reused, and the number of sub-queries is reduced. It is to be understood that, in order to facilitate matching of the hash key according to the sub-query matching field, after the Hashmap is generated, the sub-query matching field of the corresponding sub-query instruction may be saved as the hash key.
It should be noted that the sub-query matching field is a field for characterizing a specific numerical query location in the sub-query instruction, for example, the query instruction "select t1.a1from t1 where exists (select t2.a2from t2where t1.b1 is t2.b 2)" is taken as an example, the sub-query instruction "select t2.a2from t2where t1.b1 is t2.b 2", and the sub-query matching field is t2.b2, which will not be described again.
It should be noted that determining that the target hash key and the alternative hash key can facilitate matching of data, and the matching result may be matching success and matching failure, and since the Hashmap is generated by executing the sub-query instruction, hashmaps generated by the same sub-query instruction are the same, acquiring the target Hashmap may be realized by acquiring the pre-stored alternative Hashmap, or acquiring the sub-query result fed back by the database node according to the sub-query instruction.
In addition, referring to fig. 3, in an embodiment, the step S220 in the embodiment shown in fig. 2 further includes, but is not limited to, the following steps:
step S310, when the corresponding alternative hash key is matched according to the target hash key, determining that the Hashmap corresponding to the matched alternative hash key is the target Hashmap;
alternatively, the first and second electrodes may be,
and step S320, when the corresponding alternative hash key cannot be matched according to the target hash key, issuing the sub-query instruction to the database node, and generating the target hash map according to the sub-query result fed back by the database node.
It can be understood that, if the matching result is that the matching is successful, it may be determined that the backup Hashmap successfully matched with the sub-query instruction, that is, the backup Hashmap is generated according to the sub-query instruction that is the same as the current sub-query instruction, so that the matched backup Hashmap can be directly used as the target Hashmap, repeated sub-queries are omitted, if the matching result is that the matching is failed, the same sub-queries have not been executed in dbproxy, and at this time, the Hashmap needs to be re-generated according to the sub-query instruction for matching the main query instruction.
It should be noted that, the generation of the target Hashmap may be completed through a preset hash mapping rule, the Hashmap may be updated after each sub-query result is fed back by a database node, or the target Hashmap may be generated after all the sub-query results are fed back, and the specific manner is selected according to actual requirements, which is not limited herein. It can be understood that the hash mapping rule may be a hash function used for determining a Hashmap, and a specific hash function form may be adjusted according to actual requirements, which is not limited in this embodiment.
It should be noted that after the sub-query instruction is issued to the database node, the process of the database node feeding back the sub-query result according to the sub-query instruction is not an improvement made in this embodiment, and details are not repeated here, and the sub-query result can be obtained. It can be understood that the number of database nodes corresponding to the sub-query instruction may be any number, which is not limited in this embodiment.
In addition, referring to fig. 4, in an embodiment, the step S130 in the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S410, determining a main query value in a main query instruction;
step S420, performing hash hit in the target Hashmap according to the main query value, and determining a sub-query result obtained by the hash hit as a target query result.
It can be understood that Hashmap can be implemented by using a MAP interface of a hash table, and the matching elements of the main query can be input into Hashmap to implement hash hit. And if the Hashmap is successfully hit, successfully obtaining a target query result in the Hashmap according to the main query value. For example, according to a query instruction "select t1.a 11 from t1 works exists (select t2.a2from t2 work t1.b1 ═ t2.b 2)", generating a Hashmap from the obtained sub-query result according to the value of t2.b2, when a main query is performed, performing hash hit on the Hashmap according to the value of b1, that is, in the query instruction, the b1 bit main query value, and when the hash hit is successful, representing that the Hashmap includes data of b1 ═ b2, thereby matching the target query result. Hashmap is adopted for Hashhit, whether a corresponding target query result exists in the sub-query result can be directly determined, the sub-query is not required to be traversed one by one, and the query speed is effectively improved.
In addition, referring to fig. 5, in an embodiment, when the target Hashmap is generated according to the sub-query result fed back by the database node, step S130 in the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S510, when it is detected that a new sub-query result is inserted into the target Hashmap, performing a hash hit in the target Hashmap according to the main query value.
It should be noted that, since the sub-query result needs to be obtained by scanning in the database node according to the sub-query instruction, the sub-query results are generated one by one, in this case, each time a sub-query result is generated, the sub-query result may be updated to Hashmap, in order to further improve the efficiency of data query, matching may be performed once after a new sub-query result is detected to be input to the hash table, that is, a streaming matching manner is adopted, and matching is performed after all sub-queries are completed, which can effectively save time and improve query efficiency. For example, under the condition of adopting the Hashmap, each database node feeds back the sub-query result to the Hashmap, namely one Hashmap hit is carried out, and if the hit is successful, the corresponding target query result is obtained, so that the backlog of the message is effectively reduced.
In addition, referring to fig. 6, in an embodiment, after the step S320 in the embodiment shown in fig. 3 is performed, the following steps are further included, but not limited to:
step S610, setting the sub-query matching field corresponding to the sub-query instruction as the hash key of the target Hashmap.
It is worth noting that the sub-query matching field can be set as a hash key of the target Hashmap and stored in dbproxy, and for SQL query instructions, the matching fields of the same query are usually the same, so that the sub-query matching field is used as the hash key, and it can be quickly determined that the Hashmap is generated by the same sub-query instruction.
In addition, referring to fig. 7, in an embodiment, the step S320 in the embodiment shown in fig. 3 further includes, but is not limited to, the following steps:
step S710, generating a duplicate removal sub-query instruction according to the sub-query instruction, and sending the duplicate removal sub-query instruction to the database node, so that the database node feeds back a sub-query result according to the duplicate removal sub-query instruction.
Referring to the foregoing embodiment, in the case of using Hashmap to store the sub-query result and using hash hit for matching, in order to avoid that the query amount is increased due to duplicate data, a keyword for deduplication, for example, a commonly used "partition" keyword, may be added to the sub-query instruction, and for the example statement "select t1.a1from t1 hands" in the foregoing embodiment (select t2.a2from t2where t1.b1 is t2.b2) ", the determined deduplication sub-query instruction may be" select partition t2.b 2from t2 ", and the foregoing keyword may not limit the present embodiment.
In addition, referring to fig. 8, in an embodiment, the sub-query instruction includes at least two sub-query matching fields, and step S130 in the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S810, acquiring at least two target Hashmaps according to the at least two sub-query matching fields;
and step S820, determining a target query result from at least two target Hashmaps according to the main query instruction.
In an embodiment, when the sub-query instruction at least includes two sub-queries matching, the corresponding hashmaps may be obtained respectively, and when the main query is executed, the sub-query result corresponding to the main query value that can be hit from all the hashmaps is taken as the target query result. For example, if the sub-query instruction includes two sub-query matching fields, a corresponding first Hashmap and a corresponding second Hashmap are obtained respectively, when the main query value in the main query instruction is used, hash hits are performed on the first Hashmap and the second Hashmap respectively, and when the first Hashmap and the second Hashmap can both hit, a sub-query result corresponding to the main query value in the two hashmaps is determined as a target query result.
It should be noted that the database nodes corresponding to the multiple sub-query matching fields may be different data tables in the same database node, or may be data tables in different database nodes, which is not limited herein
In addition, referring to the topology structure of the distributed database shown in fig. 9, in the topology structure, the distributed database includes three database nodes and one dbproxy, where the three database nodes are db1, db2, and db3 respectively, where the three database nodes all store data distributed according to a hash rule, and at the same time, the dbproxy may obtain an SQL instruction sent by a client and feed back an SQL result, which is a summary of a target query result, to the client, where the client may be installed in a client in an independent electronic device, or may be integrated in the dbproxy, which is not limited herein.
It should be noted that, for convenience of description, t1 is used as the main table in the following example, i.e. the lookup table corresponding to the main query, a1 and b1 are used as the fields in the main query, and similarly, t2 is Hashmap corresponding to the sub-query, a2 and b2 are used as the fields in the sub-query, and the following descriptions are not given one by one.
The technical solution of the present application is illustrated below by using three specific example instructions, it should be noted that, the following three examples take generating Hashmap according to a sub-query instruction as an example, and refer to the above-mentioned embodiments according to a principle that a sub-query instruction can directly match Hashmap for multiplexing, which is not described herein again:
example one: the equivalent exists associates sub-queries with a main sub-query instruction of "select t1.a1from t1 where exists (select t2.a2from t2where t1.b1 ═ t2.b 2".
Dbpproxy receives an SQL statement sent by a client, after recognizing that the current statement is exists associated sub-query, splitting an original instruction to obtain a main query instruction of 'select t1.a1from t 1', and a sub-query instruction of 'select t2.a2from t2where t1.b1 ═ t2.b 2');
preferentially executing sub-query of t2 table data, and adding a 'discontinuity' keyword in a sub-query instruction issued to each database node to obtain a heavy sub-query instruction: select distintint a2.b 2from t 2;
and D, summarizing the sub-query results fed back by db1, db2 and db3 by Dbproxy, generating a Hashmap according to the value of t2.b2, performing Hashmap hit on the generated Hashmap according to the b1 value in the main query instruction when the Hashmap receives one sub-query result, taking the data corresponding to successful hit as a target query result, and generating a summary table according to all the target query results.
Example two: the value in association sub-query, the main sub-query instruction is "select t1.a1from t1 where t1.a1 in (select t2.a2from t2where t1.b1 ═ t2.b 2)".
Dbpproxy receives an SQL statement sent by a client, after recognizing that the current statement is exists associated sub-query, splitting an original instruction to obtain a main query instruction of 'select t1.a1from t 1', and a sub-query instruction of 'select t2.a2from t2where t1.b1 ═ t2.b 2');
preferentially executing sub-query of t2 table data, and adding a 'discontinuity' keyword in a sub-query instruction issued to each database node to obtain a heavy sub-query instruction: select distintint a2, b 2from t 2;
and D, summarizing the sub-query results fed back by db1, db2 and db3 by Dbproxy, generating a Hashmap according to the value of t2.a2& & t2.b2, and when the Hashmap receives one sub-query result, performing Hashmap hit on the generated Hashmap according to a1 value and a1 value in the main query instruction, taking the data corresponding to successful hit as a target query result, and generating a summary table according to all the target query results.
Example three: the equivalent scalar associates the sub-query, with the main sub-query instruction "select t1.a1from t1 where t1.a1 ═ or (select t2.a2from t2where t1.b1 ═ t2.b 2)".
Dbpproxy receives an SQL statement sent by a client, after recognizing that the current statement is exists associated sub-query, splitting an original instruction to obtain a main query instruction of 'select t1.a1from t 1', and a sub-query instruction of 'select t2.a2from t2where t1.b1 ═ t2.b 2');
preferentially executing sub-query of t2 table data, and adding a 'discontinuity' keyword in a sub-query instruction issued to each database node to obtain a heavy sub-query instruction: select distintint a2, b 2from t 2;
and D, summarizing the sub-query results fed back by db1, db2 and db3 by Dbproxy, generating a Hashmap according to the value of t2.a2& & t2.b2, and when the Hashmap receives one sub-query result, performing Hashmap hit on the generated Hashmap according to a1 value and a1 value in the main query instruction, taking the data corresponding to successful hit as a target query result, and generating a summary table according to all the target query results.
In addition, referring to fig. 10, an embodiment of the present invention also provides an electronic device, where the electronic device 1000 includes: a memory 1010, a processor 1020, and computer programs stored on the memory 1010 and executable on the processor 1020.
The processor 1020 and the memory 1010 may be connected by a bus or other means.
The non-transitory software programs and instructions required to implement the data query method of the above-mentioned embodiment are stored in the memory 1010, and when executed by the processor 1020, the data query method of the above-mentioned embodiment is executed, for example, the method steps S110 to S130 in fig. 1, the method steps S210 to S230 in fig. 2, the method steps S310 to S320 in fig. 3, the method steps S410 to S420 in fig. 4, the method step S510 in fig. 5, the method step S610 in fig. 6, the method step S710 in fig. 7, and the method steps S810 to S820 in fig. 8, which are described above, are executed. The above described embodiments of the apparatus are merely illustrative, wherein the units illustrated as separate components may or may not be physically separate, may be located in one place, or may be distributed over a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment.
Furthermore, an embodiment of the present invention provides a computer-readable storage medium, where the computer-readable storage medium stores computer-executable instructions, which are executed by a processor or a controller, for example, by a processor in the above-mentioned embodiment of the electronic device, and enable the processor to execute the data query method in the above-mentioned embodiment, for example, to execute the above-mentioned method steps S110 to S130 in fig. 1, method steps S210 to S230 in fig. 2, method steps S310 to S320 in fig. 3, method steps S410 to S420 in fig. 4, method step S510 in fig. 5, method step S610 in fig. 6, method step S710 in fig. 7, and method steps S810 to S820 in fig. 8. One of ordinary skill in the art will appreciate that all or some of the steps, systems, and methods disclosed above may be implemented as software, firmware, hardware, and suitable combinations thereof. Some or all of the physical components may be implemented as software executed by a processor, such as a central processing unit, digital signal processor, or microprocessor, or as hardware, or as an integrated circuit, such as an application specific integrated circuit. Such software may be distributed on computer readable media, which may include computer storage media (or non-transitory media) and communication media (or transitory media). The term computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data, as is well known to those of ordinary skill in the art. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, Digital Versatile Disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can accessed by a computer. In addition, communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media as known to those skilled in the art.
While the preferred embodiments of the present invention have been described in detail, it will be understood by those skilled in the art that the foregoing and various other changes, omissions and deviations in the form and detail thereof may be made without departing from the scope of this invention.
Claims (10)
1.A method of data query, comprising:
acquiring main sub-query instructions, and determining the main query instructions and the sub-query instructions from the main sub-query instructions;
acquiring a target Hashmap according to the sub-query instruction, wherein the target Hashmap comprises a sub-query result matched with the sub-query instruction;
and determining a target query result from the target Hashmap according to the main query instruction.
2. The method of claim 1, wherein obtaining the target Hashmap according to the sub-query instruction comprises:
determining a sub-query matching field in the sub-query instruction, and determining the sub-query matching field as a target hash key;
acquiring a pre-stored alternative Hashmap, and determining a Hashmap key of the alternative Hashmap as an alternative Hashmap;
and obtaining the target Hashmap according to the matching result of the target Hashmap and the alternative Hashmap.
3. The method of claim 2, wherein the obtaining a target Hashmap according to the matching result of the target hash key and the alternative hash key comprises:
when the corresponding alternative hash key is matched according to the target hash key, determining that the Hashmap corresponding to the matched alternative hash key is the target Hashmap;
alternatively, the first and second electrodes may be,
and when the corresponding alternative hash key cannot be matched according to the target hash key, the sub-query instruction is issued to the database node, and the target hash map is generated according to the sub-query result fed back by the database node.
4. The method of any one of claims 1 to 3, wherein the determining a target query result from the target Hashmap according to the main query instruction comprises:
determining a main query value in the main query instruction;
and performing Hash hit in the target Hashmap according to the main query value, and determining a sub-query result obtained by Hash hit as a target query result.
5. The method as claimed in claim 4, wherein when generating a target Hashmap according to the sub-query result fed back by the database node, the performing hash hit in the target Hashmap according to the main query value comprises:
and when detecting that a new sub-query result is inserted into the target Hashmap, performing Hash hit in the target Hashmap according to the main query value.
6. The method of claim 3, wherein after the issuing the sub-query instruction to the database node and the generating the target Hashmap according to the sub-query result fed back by the database node, the method further comprises:
and setting the sub-query matching field corresponding to the sub-query instruction as a hash key of the target Hashmap.
7. The method of claim 3, wherein said issuing the sub-query instruction to a database node comprises:
and generating a duplicate removal sub-query instruction according to the sub-query instruction, and sending the duplicate removal sub-query instruction to the database node so that the database node feeds back a sub-query result according to the duplicate removal sub-query instruction.
8. The method of claim 2, wherein the sub-query instruction comprises at least two sub-query matching fields, and wherein obtaining the target Hashmap according to the sub-query instruction and determining the target query result from the target Hashmap according to the main query instruction comprises:
acquiring at least two target Hashmaps according to the at least two sub-query matching fields;
and determining a target query result from at least two target Hashmaps according to the main query instruction.
9. An electronic device, comprising: memory, processor and computer program stored on the memory and executable on the processor, characterized in that the processor implements the data query method according to any one of claims 1 to 8 when executing the computer program.
10. A computer-readable storage medium storing computer-executable instructions for performing the data query method of any one of claims 1 to 8.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011484008.1A CN114637759B (en) | 2020-12-16 | 2020-12-16 | Data query method, electronic device and storage medium |
| PCT/CN2021/128413 WO2022127417A1 (en) | 2020-12-16 | 2021-11-03 | Data query method, and electronic device and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011484008.1A CN114637759B (en) | 2020-12-16 | 2020-12-16 | Data query method, electronic device and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN114637759A true CN114637759A (en) | 2022-06-17 |
| CN114637759B CN114637759B (en) | 2023-04-18 |
Family
ID=81944468
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011484008.1A Active CN114637759B (en) | 2020-12-16 | 2020-12-16 | Data query method, electronic device and storage medium |
Country Status (2)
| Country | Link |
|---|---|
| CN (1) | CN114637759B (en) |
| WO (1) | WO2022127417A1 (en) |
Citations (20)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20040059719A1 (en) * | 2002-09-23 | 2004-03-25 | Rajeev Gupta | Methods, computer programs and apparatus for caching directory queries |
| US20080301125A1 (en) * | 2007-05-29 | 2008-12-04 | Bea Systems, Inc. | Event processing query language including an output clause |
| EP2040181A2 (en) * | 2007-09-11 | 2009-03-25 | Kabushiki Kaisha Toshiba | Apparatus, method and computer program product for processing database |
| US20100121839A1 (en) * | 2007-03-15 | 2010-05-13 | Scott Meyer | Query optimization |
| US8396860B1 (en) * | 2010-12-14 | 2013-03-12 | Teradata Us, Inc. | Eliminating sequenced inner and outer joins using temporal sequenced referential integrity and temporal uniqueness |
| US20130173590A1 (en) * | 2011-12-29 | 2013-07-04 | Ahmad Ghazal | Optimizing nested database queries that include windowing operations |
| US8484242B1 (en) * | 2010-08-24 | 2013-07-09 | ScalArc, Inc. | Method and system for transparent database connection pooling and query queuing |
| US8543554B1 (en) * | 2010-08-10 | 2013-09-24 | ScalArc Inc. | Method and system for transparent database query caching |
| CN103412858A (en) * | 2012-07-02 | 2013-11-27 | 清华大学 | Method for large-scale feature matching of text content or network content analyses |
| WO2014101777A1 (en) * | 2012-12-28 | 2014-07-03 | 华为技术有限公司 | Flow table matching method and device, and switch |
| CN104090962A (en) * | 2014-07-14 | 2014-10-08 | 西北工业大学 | Nested query method oriented to mass distributed-type database |
| CN104468381A (en) * | 2014-12-01 | 2015-03-25 | 国家计算机网络与信息安全管理中心 | Implementation method for multi-field rule matching |
| CN104536904A (en) * | 2014-12-29 | 2015-04-22 | 杭州华为数字技术有限公司 | Data management method, equipment and system |
| CN105429879A (en) * | 2014-08-26 | 2016-03-23 | 杭州华为数字技术有限公司 | Flow table item querying method, flow table item querying equipment and flow table item querying system |
| CN105868388A (en) * | 2016-04-14 | 2016-08-17 | 中国人民大学 | Method for memory on-line analytical processing (OLAP) query optimization based on field programmable gate array (FPGA) |
| CN109783508A (en) * | 2018-12-29 | 2019-05-21 | 亚信科技（南京）有限公司 | Data query method, apparatus, computer equipment and storage medium |
| CN111259013A (en) * | 2020-02-03 | 2020-06-09 | 京东数字科技控股有限公司 | Method and device for storing data |
| US20200251185A1 (en) * | 2005-03-03 | 2020-08-06 | Washington University | Method and Apparatus for Performing Similarity Searching |
| CN111538747A (en) * | 2020-05-27 | 2020-08-14 | 支付宝(杭州)信息技术有限公司 | Data query method, device and equipment and auxiliary data query method, device and equipment |
| CN111723092A (en) * | 2020-06-08 | 2020-09-29 | 阿里巴巴集团控股有限公司 | Data processing method and device |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104123288B (en) * | 2013-04-24 | 2018-06-01 | 阿里巴巴集团控股有限公司 | A kind of data query method and device |
| CN104123374B (en) * | 2014-07-28 | 2017-09-29 | 北京京东尚科信息技术有限公司 | The method and device of aggregate query in distributed data base |
| US11036732B2 (en) * | 2017-03-28 | 2021-06-15 | International Business Machines Corporation | Subquery predicate generation to reduce processing in a multi-table join |
| CN109241093B (en) * | 2017-06-30 | 2021-06-08 | 华为技术有限公司 | Data query method, related device and database system |
-
2020
- 2020-12-16 CN CN202011484008.1A patent/CN114637759B/en active Active
-
2021
- 2021-11-03 WO PCT/CN2021/128413 patent/WO2022127417A1/en active Application Filing
Patent Citations (20)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20040059719A1 (en) * | 2002-09-23 | 2004-03-25 | Rajeev Gupta | Methods, computer programs and apparatus for caching directory queries |
| US20200251185A1 (en) * | 2005-03-03 | 2020-08-06 | Washington University | Method and Apparatus for Performing Similarity Searching |
| US20100121839A1 (en) * | 2007-03-15 | 2010-05-13 | Scott Meyer | Query optimization |
| US20080301125A1 (en) * | 2007-05-29 | 2008-12-04 | Bea Systems, Inc. | Event processing query language including an output clause |
| EP2040181A2 (en) * | 2007-09-11 | 2009-03-25 | Kabushiki Kaisha Toshiba | Apparatus, method and computer program product for processing database |
| US8543554B1 (en) * | 2010-08-10 | 2013-09-24 | ScalArc Inc. | Method and system for transparent database query caching |
| US8484242B1 (en) * | 2010-08-24 | 2013-07-09 | ScalArc, Inc. | Method and system for transparent database connection pooling and query queuing |
| US8396860B1 (en) * | 2010-12-14 | 2013-03-12 | Teradata Us, Inc. | Eliminating sequenced inner and outer joins using temporal sequenced referential integrity and temporal uniqueness |
| US20130173590A1 (en) * | 2011-12-29 | 2013-07-04 | Ahmad Ghazal | Optimizing nested database queries that include windowing operations |
| CN103412858A (en) * | 2012-07-02 | 2013-11-27 | 清华大学 | Method for large-scale feature matching of text content or network content analyses |
| WO2014101777A1 (en) * | 2012-12-28 | 2014-07-03 | 华为技术有限公司 | Flow table matching method and device, and switch |
| CN104090962A (en) * | 2014-07-14 | 2014-10-08 | 西北工业大学 | Nested query method oriented to mass distributed-type database |
| CN105429879A (en) * | 2014-08-26 | 2016-03-23 | 杭州华为数字技术有限公司 | Flow table item querying method, flow table item querying equipment and flow table item querying system |
| CN104468381A (en) * | 2014-12-01 | 2015-03-25 | 国家计算机网络与信息安全管理中心 | Implementation method for multi-field rule matching |
| CN104536904A (en) * | 2014-12-29 | 2015-04-22 | 杭州华为数字技术有限公司 | Data management method, equipment and system |
| CN105868388A (en) * | 2016-04-14 | 2016-08-17 | 中国人民大学 | Method for memory on-line analytical processing (OLAP) query optimization based on field programmable gate array (FPGA) |
| CN109783508A (en) * | 2018-12-29 | 2019-05-21 | 亚信科技（南京）有限公司 | Data query method, apparatus, computer equipment and storage medium |
| CN111259013A (en) * | 2020-02-03 | 2020-06-09 | 京东数字科技控股有限公司 | Method and device for storing data |
| CN111538747A (en) * | 2020-05-27 | 2020-08-14 | 支付宝(杭州)信息技术有限公司 | Data query method, device and equipment and auxiliary data query method, device and equipment |
| CN111723092A (en) * | 2020-06-08 | 2020-09-29 | 阿里巴巴集团控股有限公司 | Data processing method and device |
Non-Patent Citations (1)
| Title |
|---|
| V. BENJAMIN LIVSHITS: "《Proceedings of the 14th conference on USENIX Security Symposium》" * |
Also Published As
| Publication number | Publication date |
|---|---|
| WO2022127417A1 (en) | 2022-06-23 |
| CN114637759B (en) | 2023-04-18 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9817858B2 (en) | Generating hash values | |
| CN107784044B (en) | Table data query method and device | |
| US11347787B2 (en) | Image retrieval method and apparatus, system, server, and storage medium | |
| US11269902B2 (en) | Time series data management method, device, and apparatus | |
| CN105608086A (en) | Transaction processing method and device of distributed database system | |
| US20220365957A1 (en) | Log parsing method and device, server and storage medium | |
| US20150143065A1 (en) | Data Processing Method and Apparatus, and Shared Storage Device | |
| KR20190044145A (en) | Processing mutations for a remote database | |
| US20140222870A1 (en) | System, Method, Software, and Data Structure for Key-Value Mapping and Keys Sorting | |
| CN108614837B (en) | File storage and retrieval method and device | |
| WO2016070751A1 (en) | Distributed cache range querying method, device, and system | |
| WO2021259217A1 (en) | Data association query method and apparatus, and device and storage medium | |
| US20180276252A1 (en) | Data Storage Method And Coordinator Node | |
| CN106547781A (en) | A kind of method for realizing distributed transaction, device and database server | |
| WO2022252510A1 (en) | Resource management method, apparatus and device | |
| US11360975B2 (en) | Data providing apparatus and data providing method | |
| CN114398371A (en) | Multi-copy fragmentation method, device, equipment and storage medium for database cluster system | |
| US20180173762A1 (en) | System and Method of Adaptively Partitioning Data to Speed Up Join Queries on Distributed and Parallel Database Systems | |
| CN114637759B (en) | Data query method, electronic device and storage medium | |
| CN113641686B (en) | Data processing method, data processing apparatus, electronic device, storage medium, and program product | |
| CN110413617B (en) | Method for dynamically adjusting hash table group according to size of data volume | |
| CN113282616A (en) | Incremental time sequence data conflict detection method and device and storage medium | |
| CN110162574A (en) | Determination method, apparatus, server and the storage medium of fast resampling mode | |
| CN112818010A (en) | Database query method and device | |
| US11880367B2 (en) | Method and apparatus for realizing globally unique index |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |