CN114398426A - SQL statement global variable attribute query method and tool - Google Patents

SQL statement global variable attribute query method and tool Download PDF

Info

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
Application number
CN202210026065.8A
Other languages
Chinese (zh)
Inventor
田燿华
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shanghai Yunxi Technology Co ltd
Original Assignee
Shandong Inspur Scientific Research Institute Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Shandong Inspur Scientific Research Institute Co Ltd filed Critical Shandong Inspur Scientific Research Institute Co Ltd
Priority to CN202210026065.8A priority Critical patent/CN114398426A/en
Publication of CN114398426A publication Critical patent/CN114398426A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/245Query processing
    • G06F16/2458Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
    • G06F16/2471Distributed queries
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/242Query formulation
    • G06F16/2433Query languages
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F8/00Arrangements for software engineering
    • G06F8/40Transformation of program code
    • G06F8/41Compilation
    • G06F8/42Syntactic 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

SQL statement global variable attribute query method and tool
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.
CN202210026065.8A 2022-01-11 2022-01-11 SQL statement global variable attribute query method and tool Pending CN114398426A (en)

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)

* Cited by examiner, † Cited by third party
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

Cited By (1)

* Cited by examiner, † Cited by third party
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