CN114398426A - SQL statement global variable attribute query method and tool - Google Patents
SQL statement global variable attribute query method and tool Download PDFInfo
- Publication number
- CN114398426A CN114398426A CN202210026065.8A CN202210026065A CN114398426A CN 114398426 A CN114398426 A CN 114398426A CN 202210026065 A CN202210026065 A CN 202210026065A CN 114398426 A CN114398426 A CN 114398426A
- Authority
- CN
- China
- Prior art keywords
- sql statement
- execution
- plan
- sql
- clause
- 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.)
- Pending
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/245—Query processing
- G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
- G06F16/2471—Distributed queries
-
- 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
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Software Systems (AREA)
- Probability & Statistics with Applications (AREA)
- Fuzzy Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a tool for querying global variable attributes of SQL sentences, which relate to the technical field of SQL sentences and are realized by the following steps: s1, the user inputs SQL sentences through the client, and the client receives the SQL sentences and sends the SQL sentences to the distributed database; s2, the distributed database analyzes the SQL statement through an analyzer to generate an abstract syntax tree; s3, the distributed database receives the abstract syntax tree through the actuator, and analyzes the abstract syntax tree to generate an execution plan; s4, the distributed physical engine judges the distribution and vectored of the SQL statement through the execution plan; and S5, executing the execution plan by the distributed database, judging whether the SQL statement is a global variable attribute query statement, if not, continuing to execute the plan according to the general SQL statement, if so, continuing to execute the execution plan of S3, and returning a corresponding result to the client. The invention optimizes the distributed database through the query result to supplement the information which is not available in the traditional database.
Description
Technical Field
The invention relates to the technical field of SQL (structured query language) statements, in particular to a method and a tool for querying global variable attributes of the SQL statements.
Background
At present, most databases (including relational databases such as MYSQL and PGSQL) have two schemes for querying information such as SQL statement execution plans: one scheme is that a SELECT statement is followed by an EXPLAIN, the method can only inquire an execution plan of the SELECT statement, the inquiry result is displayed in a form of a table, the table content comprises the type of each SELECT clause, the attribution table of the data in the row, the index type used in the inquiry and other information; another approach is to follow the exception analysis with an SQL statement (e.g., select.., insert.,). Compared with the first scheme, the second scheme has a larger range of clauses that can be queried, but the query results are different: by the second scheme, the execution plan tree is generated without returning data after the command is finished, and clauses are actually executed, so that the time consumption of key steps in the query plan, the scanning execution times and other data are measured.
Compared with the traditional relational database, the distributed database has the characteristics of strong consistency, expandability and high reliability. Therefore, the distributed database needs to query information such as distribution, vectored, and the like of the SQL statement, in addition to the time-consuming query and the like. In addition, compared with the tree structure of the planning node generated by the relational database such as MYSQL, PGSQL, and the like in the query result, the time consumed by each sub-tree and the number of scanning lines, the distributed database needs to query the information such as the node where each sub-tree of the statement planning tree is executed. By querying the global attributes of each SQL statement, the database may be optimized by analyzing the query results.
Disclosure of Invention
Aiming at the requirements and the defects of the prior art development, the invention provides a query method and a tool for SQL sentence global variable attributes.
Firstly, the invention discloses a method for protecting SQL sentence global variable attribute query, which adopts the following technical scheme for solving the technical problems:
a query method of SQL statement global variable attribute comprises the following steps:
step S1, the user inputs SQL sentences through the client, and the client receives the SQL sentences and sends the SQL sentences to the distributed database;
step S2, the distributed database analyzes the SQL statement through the analyzer to generate an abstract syntax tree;
step S3, the distributed database receives the abstract syntax tree through the executor, and analyzes the abstract syntax tree to generate an execution plan;
step S4, the distributed physical engine judges the distribution and vectored of the SQL statement through the execution plan;
step S5, the distributed database executes the execution plan, and determines whether the SQL statement is a global variable attribute query statement,
if not, continuing to execute the plan according to the general SQL statement;
if so, the execution plan generated in step S3 is continuously executed, and a corresponding result is returned to the client.
When step S2 is executed, the distributed database first parses the SQL statement into a series of identifiers by the parser, and then matches the identifiers to corresponding grammars, thereby generating an abstract syntax tree.
When step S4 is executed, the distributed physical engine determines distribution and vectored of the SQL statement through the execution plan, specifically determines whether the execution of the SQL statement is distributed or locally executed, and determines whether the vectorized column storage engine is started in the execution process of the SQL statement.
In the process of executing step S5, after the execution plan generated in step S3 is continuously executed, distribution and vectored of the clause are generated and the current timestamp is recorded, then the clause plan is executed, the timestamp is recorded again after the execution of the clause plan is completed, the time taken for completing the SQL statement execution plan is recorded by two timestamps, and the distribution and vectored of the clause are recorded and then returned to the client.
Preferably, the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded and returned to the client after the execution is finished.
Secondly, the invention discloses and protects a query tool for global variable attributes of SQL statements, and the technical scheme adopted for solving the technical problems is as follows:
an SQL statement global variable attribute query tool, the implementation of which involves:
the client module is used for receiving the SQL sentences input by the user and sending the SQL sentences to the distributed database;
the analysis module is deployed in the distributed database and used for analyzing the SQL statement to generate an abstract syntax tree;
the execution module is deployed in the distributed database and used for receiving the abstract syntax tree, analyzing the abstract syntax tree and generating an execution plan;
the distributed engine module is deployed in the distributed database and used for judging distribution and vectored of the SQL statement through the execution plan;
and the distributed database is used for executing the generated execution plan and judging whether the SQL statement is a global variable attribute query statement, if so, continuing to execute the generated execution plan and returning a corresponding result to the client module, and if not, continuing to execute the plan according to the general SQL statement.
Optionally, the related parsing module parses the SQL statement into a series of identifiers, and then matches the identifiers to the corresponding syntax, thereby generating the abstract syntax tree.
Optionally, the related distributed engine module determines distribution and vectored of the SQL statement through the execution plan, specifically determines whether the execution of the SQL statement is distributed or locally executed, and determines whether the vectorized column storage engine is started in the execution process of the SQL statement.
Optionally, after the related distributed database determines that the SQL statement is a global variable attribute query statement, the generated execution plan is continuously executed, then distribution and vectored of the clause are generated, the current timestamp is recorded, then the clause plan is executed, the timestamp is recorded again after the clause plan is executed, the time spent in completing the SQL statement execution plan is recorded by two timestamps, the distribution and vectored of the clause are recorded, and then the SQL statement is returned to the client module.
Preferably, in the distributed database, the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded, and is returned to the client module after the execution is finished.
Compared with the prior art, the SQL sentence global variable attribute query method and the tool have the beneficial effects that:
(1) the invention can query the global attribute of the SQL statement, and then optimize the distributed database through the query result;
(2) the query result of the invention supplements the information which is not available in the traditional database, and the SQL sentence to be queried is actually executed, so that the user and the developer can clearly know various information of the SQL sentence in the execution process, the developer can more intuitively know the performance of the distributed database, and the user is more familiar with the execution flow of the SQL sentence in the distributed database.
Drawings
FIG. 1 is a flow chart of a method according to a first embodiment of the present invention;
fig. 2 is a block diagram of module connection according to a second embodiment of the present invention.
The reference information in the drawings indicates:
1. the system comprises a client module 2, an analysis module 3, an execution module 4, a distributed engine module 5 and a distributed database.
Detailed Description
In order to make the technical scheme, the technical problems to be solved and the technical effects of the present invention more clearly apparent, the following technical scheme of the present invention is clearly and completely described with reference to the specific embodiments.
The first embodiment is as follows:
with reference to fig. 1, this embodiment provides a method for querying global variable attributes of an SQL statement, where the query process includes:
and step S1, the user inputs SQL sentences through the client, and the client receives the SQL sentences and sends the SQL sentences to the distributed database.
Step S2, the distributed database parses the SQL statement into a series of identifiers by the parser, and then matches the identifiers to corresponding grammars, generating an abstract syntax tree.
And step S3, the distributed database receives the abstract syntax tree through the executor, and analyzes the abstract syntax tree to generate an execution plan.
Step S4, the distributed physical engine determines distribution and vectored of the SQL statement through the execution plan, specifically determines whether the execution of the SQL statement is distributed or locally executed, and determines whether the vectorized column storage engine is started in the execution process of the SQL statement.
Step S5, the distributed database executes the execution plan, and determines whether the SQL statement is a global variable attribute query statement,
if not, continuing to execute the plan according to the general SQL statement;
if so, the execution plan generated in step S3 is executed continuously, distribution and vectored of the clause are generated, the current timestamp is recorded, the clause plan is then executed, the timestamp is recorded again after the execution of the clause plan is completed, the time taken for completing the SQL statement execution plan is recorded by two timestamps, and the distribution and vectored of the clause are recorded, and then returned to the client.
It is added that the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded, and is returned to the client after the execution is finished.
Example two:
with reference to fig. 2, this embodiment provides an SQL statement global variable attribute query tool, which is implemented by:
the client module 1 is used for receiving SQL sentences input by a user and sending the SQL sentences to the distributed database 5;
the analysis module 2 is deployed in the distributed database 5 and is used for analyzing and dividing the SQL statement into a series of identifiers, and then matching the identifiers with corresponding grammars to generate an abstract syntax tree;
the execution module 3 is deployed in the distributed database 5 and is used for receiving the abstract syntax tree, analyzing the abstract syntax tree and generating an execution plan;
the distributed engine module 4 is deployed in the distributed database 5, and is used for judging distribution and vectored of the SQL statement through the execution plan, specifically judging whether the execution of the SQL statement is distributed or locally executed, and judging whether a vectorized column storage engine is started in the execution process of the SQL statement;
the distributed database 5 is configured to execute the generated execution plan, determine whether the SQL statement is a global variable attribute query statement, if not, continue to execute the execution plan according to the general SQL statement, if so, continue to execute the generated execution plan, generate distribution and vectored of the clause, record a current timestamp, execute the clause plan, record a timestamp again after the clause plan is executed, record time spent in completing the SQL statement execution plan through the two timestamps, record distribution and vectored of the clause, and then return to the client module 1.
It should be added that in the distributed database 5, the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded, and returns to the client module 1 after the execution is finished.
In summary, the SQL sentence global variable attribute query method and the tool of the invention complement the information which is not available in the traditional database, so that the user and the developer can clearly know various information of the SQL sentence in the execution process, the developer can know the performance of the distributed database more intuitively, and the user is more familiar with the execution flow of the SQL sentence in the distributed database.
The principles and embodiments of the present invention have been described in detail using specific examples, which are provided only to aid in understanding the core technical content of the present invention. Based on the above embodiments of the present invention, those skilled in the art should make any improvements and modifications to the present invention without departing from the principle of the present invention, and therefore, the present invention should fall into the protection scope of the present invention.
Claims (10)
1. A query method for SQL sentence global variable attribute is characterized in that the query process comprises the following steps:
step S1, the user inputs SQL sentences through the client, and the client receives the SQL sentences and sends the SQL sentences to the distributed database;
step S2, the distributed database analyzes the SQL statement through the analyzer to generate an abstract syntax tree;
step S3, the distributed database receives the abstract syntax tree through the executor, and analyzes the abstract syntax tree to generate an execution plan;
step S4, the distributed physical engine judges the distribution and vectored of the SQL statement through the execution plan;
step S5, the distributed database executes the execution plan, and determines whether the SQL statement is a global variable attribute query statement,
if not, continuing to execute the plan according to the general SQL statement;
if so, the execution plan generated in step S3 is continuously executed, and a corresponding result is returned to the client.
2. The method according to claim 1, wherein in step S2, the distributed database first parses the SQL statement into a series of identifiers by a parser, and then matches the identifiers to corresponding syntax, thereby generating an abstract syntax tree.
3. The method according to claim 1, wherein when step S4 is executed, the distributed physical engine determines distribution and vectored of the SQL statement through the execution plan, specifically determines whether the execution of the SQL statement is distributed or locally executed, and determines whether the vectorized column storage engine is started during the execution of the SQL statement.
4. The method for querying the global variable attribute of the SQL statement according to claim 1, wherein during the step S5, after the execution plan generated in step S3 is executed, the distribution and vectored of the clause are generated, the current timestamp is recorded, then the clause plan is executed, the timestamp is recorded again after the execution of the clause plan is completed, the time taken for completing the execution plan of the SQL statement is recorded by two timestamps, and the distribution and vectored of the clause are recorded, and then the clause is returned to the client.
5. The method according to claim 4, wherein the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded, and is returned to the client after the execution is finished.
6. An SQL statement global variable attribute query tool, characterized in that the implementation involves:
the client module is used for receiving the SQL sentences input by the user and sending the SQL sentences to the distributed database;
the analysis module is deployed in the distributed database and used for analyzing the SQL statement to generate an abstract syntax tree;
the execution module is deployed in the distributed database and used for receiving the abstract syntax tree, analyzing the abstract syntax tree and generating an execution plan;
the distributed engine module is deployed in the distributed database and used for judging distribution and vectored of the SQL statement through the execution plan;
and the distributed database is used for executing the generated execution plan and judging whether the SQL statement is a global variable attribute query statement, if so, continuing to execute the generated execution plan and returning a corresponding result to the client module, and if not, continuing to execute the plan according to the general SQL statement.
7. The tool of claim 6, wherein the parsing module first parses the SQL statement into a series of identifiers, and then matches the identifiers to corresponding syntax, thereby generating an abstract syntax tree.
8. The tool of claim 6, wherein the distributed engine module determines distribution and vectored of the SQL statement through an execution plan, specifically determines whether the execution of the SQL statement is distributed or locally executed, and determines whether a vectorized column storage engine is started in the execution process of the SQL statement.
9. The SQL statement global variable attribute query tool according to claim 6, wherein after the SQL statement is determined as a global variable attribute query statement by the distributed database, the generated execution plan is executed continuously, then distribution and vectored of the clause are generated, the current timestamp is recorded, then the clause plan is executed, the timestamp is recorded again after the execution of the clause plan is completed, the time taken for completing the SQL statement execution plan is recorded by two timestamps, and the distribution and vectored of the clause are recorded, and then returned to the client module.
10. The tool of claim 9, wherein in the distributed database, the clause plan is executed separately, and the node where each execution step of the clause plan is located is recorded, and the clause plan is returned to the client module after the execution is finished.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210026065.8A CN114398426A (en) | 2022-01-11 | 2022-01-11 | SQL statement global variable attribute query method and tool |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210026065.8A CN114398426A (en) | 2022-01-11 | 2022-01-11 | SQL statement global variable attribute query method and tool |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114398426A true CN114398426A (en) | 2022-04-26 |
Family
ID=81230434
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210026065.8A Pending CN114398426A (en) | 2022-01-11 | 2022-01-11 | SQL statement global variable attribute query method and tool |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114398426A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114969203A (en) * | 2022-05-11 | 2022-08-30 | 深圳无一科技有限公司 | Data real-time synchronization method, device, equipment and medium |
-
2022
- 2022-01-11 CN CN202210026065.8A patent/CN114398426A/en active Pending
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114969203A (en) * | 2022-05-11 | 2022-08-30 | 深圳无一科技有限公司 | Data real-time synchronization method, device, equipment and medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108959433B (en) | Method and system for extracting knowledge graph from software project data and asking for questions and answers | |
| US8285707B2 (en) | Method of querying relational database management systems | |
| CN112559554B (en) | Query statement optimization method and device | |
| US7676460B2 (en) | Techniques for providing suggestions for creating a search query | |
| US6615222B2 (en) | System and process for evaluating the performance of a database system | |
| CN102682118B (en) | Multidimensional data model access method and device | |
| US6466931B1 (en) | Method and system for transparently caching and reusing query execution plans efficiently | |
| CN104765731B (en) | Database inquiry optimization method and apparatus | |
| CA2388515C (en) | System for managing rdbm fragmentations | |
| EP2608074A2 (en) | Systems and methods for merging source records in accordance with survivorship rules | |
| JPH1125096A (en) | Method for searching data base and computer system | |
| CN108009270A (en) | A kind of text searching method calculated based on distributed memory | |
| CN112988782A (en) | Hive-supported interactive query method and device and storage medium | |
| CN110909126A (en) | Information query method and device | |
| CN112732752A (en) | Query statement optimization method, device, equipment and storage medium | |
| CN114398426A (en) | SQL statement global variable attribute query method and tool | |
| CN110580170B (en) | Method and device for identifying software performance risk | |
| CN115114325B (en) | Data query method and device, electronic equipment and storage medium | |
| CN112445867A (en) | Intelligent analysis method and system for data relationship | |
| US20210034616A1 (en) | Query optimization | |
| CN115391424A (en) | Database query processing method, storage medium and computer equipment | |
| CN114925142A (en) | Multi-type database compatibility method, device, equipment and medium of ORM framework | |
| CN112783758B (en) | Test case library and feature library generation method, device and storage medium | |
| US6925630B1 (en) | Method for generating code for processing a database | |
| CN114547083A (en) | Data processing method and device and electronic equipment |
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: 20221212 Address after: Room 305-22, Building 2, No. 1158 Zhangdong Road and No. 1059 Dangui Road, China (Shanghai) Pilot Free Trade Zone, Pudong New Area, Shanghai, 200120 Applicant after: Shanghai Yunxi Technology Co.,Ltd. Address before: Building S02, 1036 Gaoxin Langchao Road, Jinan, Shandong 250100 Applicant before: Shandong Inspur Scientific Research Institute Co.,Ltd. |
|
| TA01 | Transfer of patent application right |