A SQL query method and system based on cluster database

Abstract

The present invention provides a SQL query method and system based on a cluster database, which relates to the field of database query. The method comprises: receiving an SQL instruction sent by a client; parsing the SQL instruction, and generating an execution plan according to the parsed SQL instruction; encoding the execution plan through utf8mb4 encoding to obtain a first character set, performing SQL query according to the first character set to obtain an execution result; encoding the execution result to obtain a second character set, and returning the second character set to the client. The present invention can ensure data integrity, and ensure unified deployment between nodes through a consistent character set, has high availability and strong compatibility, and efficiently realizes data isolation and consistency.

Description

Technical Field

[0001] The present invention relates to the technical field of database query, and in particular to a SQL query method and system based on a cluster database. Background Art

[0002] In a cluster, how each node needs to achieve unified processing of character sets is an important challenge, especially for distributed databases. When the character sets of each node are inconsistent, how to achieve data interoperability through unified deployment through management nodes is an important research direction.

[0003] In addition to the consistency between management nodes and data nodes, the character set configuration of the user client also needs to be considered. If the client character set of the product used by the user is inconsistent with the character set of the GBase database itself, the SQL obtained by GBase will contain garbled characters and the database cannot be used normally. In addition, for the compatibility issue of uncommon characters, database support for uncommon characters is also essential.

[0004] The above problems will cause result set errors. When different operators interact, if their character set formats do not match, result set errors may occur. For example, if the operator values ​​are the same but the character sets are different, the comparison will still result in a match failure. Summary of the invention

[0005] The present invention aims to solve at least one of the technical problems existing in the related art. To this end, the present invention provides a SQL query method and system based on a cluster database.

[0006] The present invention provides a SQL query method based on a cluster database, comprising:

[0007] S1: Receive SQL instructions sent by the client;

[0008] S2: parsing the SQL instruction, and generating an execution plan according to the parsed SQL instruction;

[0009] S3: Encode the execution plan in utf8mb4 encoding to obtain a first character set, and perform SQL query according to the first character set to obtain an execution result;

[0010] S4: Encode the execution result to obtain a second character set, and return the second character set to the client.

[0011] According to a cluster database-based SQL query method provided by the present invention, the first character set in step S3 is a system_charset character set type.

[0012] According to the SQL query method based on a cluster database provided by the present invention, the second character set in step S4 is a result_charset character set type.

[0013] According to the SQL query method based on a cluster database provided by the present invention, the format of the result_charset character set type in step S4 is the same as the format of the character set type of the client.

[0014] According to a cluster database-based SQL query method provided by the present invention, in step S3, when encoding the execution plan, the naked character string in the SQL instruction corresponding to the execution plan is encoded.

[0015] The present invention also provides a SQL query system based on a cluster database, which is used to execute the SQL query method based on a cluster database as described in any one of the above items, comprising:

[0016] Receiving module: used to receive SQL instructions sent by the client;

[0017] Generation module: used to parse the SQL instructions and generate an execution plan according to the parsed SQL instructions;

[0018] A first conversion module: used for encoding the execution plan by using utf8mb4 encoding to obtain a first character set;

[0019] Execution module: used for performing SQL query according to the first character set to obtain execution results;

[0020] A second conversion module: used for encoding the execution result to obtain a second character set;

[0021] Return module: used for returning the second character set to the client.

[0022] According to the SQL query system based on cluster database provided by the present invention, the receiving module, the generating module and the first conversion module are all established based on cluster nodes.

[0023] According to the SQL query system based on cluster database provided by the present invention, the execution module, the second conversion module and the return module are all established based on a single machine node.

[0024] The present invention provides a SQL query method and system based on a cluster database, which supports various rare characters, is compatible with flexible character set settings of multiple nodes in the cluster database, can achieve efficient information management, and is compatible with clients of various character set encodings, thereby enhancing product usability, reducing frequent conversion of character sets, and saving a lot of performance.

[0025] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0027] Figure 1 It is a flow chart of a SQL query method based on a cluster database provided by an embodiment of the present invention.

[0028] Figure 2 The present invention provides a schematic diagram of a SQL query system structure based on a cluster database.

[0029] Reference numerals:

[0030] 100, receiving module; 200, generating module; 300, first conversion module; 400, executing module; 500, second conversion module; 600, returning module. DETAILED DESCRIPTION

[0031] In order to make the purpose, technical scheme and advantages of the present invention clearer, the technical scheme of the present invention will be clearly and completely described below in conjunction with the drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present invention. The following embodiments are used to illustrate the present invention, but cannot be used to limit the scope of the present invention.

[0032] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the embodiment of the present invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.

[0033] In order to better understand the present invention, some technical terms mentioned in the present invention are explained below.

[0034] Cluster: Cluster technology is a relatively new technology. As the scale and complexity of data continue to grow, traditional single database systems face many challenges. Against this background, cluster databases came into being.

[0035] A cluster database connects multiple database servers together to form a collaborative system. Compared with traditional single-node databases, cluster databases have the advantages of high availability, performance improvement, scalability, and data security, providing users with reliable and efficient data management solutions. Currently, many databases have begun to use clusters to build databases, and GBase is a representative product among them.

[0036] The nodes of the cluster database are the basic units that constitute the cluster system. Each node can be regarded as an independent database server. They are connected together through the network and work together to complete data storage, processing and management.

[0037] The core of cluster database lies in the transmission of data between nodes. The database usually contains multiple nodes, which need to exchange data efficiently and reliably to ensure the normal operation of the entire system.

[0038] Character set: The main function of a character set is to represent, store and process text data in a computer. It defines a series of characters and their corresponding encoding values, enabling computers to recognize and process various texts, symbols and emoticons.

[0039] Commonly used character sets in databases are:

[0040] Latin-1: This is a widely used character set that includes Latin letters, numbers, Greek letters, and some special characters;

[0041] UTF-8: This is a widely used Unicode character set that can represent almost all characters. UTF-8 is efficient and compatible. It represents different characters through byte sequences of different lengths.

[0042] Utf8mb4: is an extension of UTF-8. The main difference is that it supports more characters, including some special ideograms, emoji expressions, etc. It is compatible with UTF-8 under the premise of high storage efficiency;

[0043] GBK: This is a standard Chinese character set, which contains a large number of Chinese characters and some commonly used symbols. GBK is suitable for processing Chinese text, but the support for some rare characters or special characters may be limited;

[0044] GB18030: It is an upgraded version of GBK. It expands the range of characters to include more Chinese characters and minority languages. GB18030 is more commonly used in some financial applications.

[0045] In the database, character set processing is essential, especially when performing Chinese storage, calculation, batch processing and other operations. The setting of character sets is particularly important. If the encoding format does not match the data type, data garbled will occur, causing serious consequences.

[0046] Compared with other databases that have strict consistency requirements on character sets, GBase has very flexible character set settings. When using the database, the character set settings usually include: the encoding of each node, the encoding of the database, the encoding of the table, the encoding of the columns in the table, and the client input encoding when the client interacts with the database.

[0047] In practical applications, the three core components of GBase 8a MPP Cluster are the distributed scheduling cluster GCluster, the distributed management cluster GCWare, and the distributed storage computing cluster GNode.

[0048] The modules related to character sets are:

[0049] Client: The client determines the encoding method of the SQL input by the user;

[0050] GCluster: responsible for SQL parsing, SQL optimization, distributed execution plan generation, and execution scheduling;

[0051] GNode: It is the most basic storage and computing unit. GNode is responsible for the actual storage of cluster data on the node, and receives and executes the decomposed SQL execution plan from GCluster, and returns the execution result to GCluster.

[0052] Combine the following Figure 1 to Figure 2 Embodiments of the present invention are described.

[0053] The present invention provides a SQL query method based on a cluster database, comprising:

[0054] S1: Receive SQL instructions sent by the client;

[0055] S2: parsing the SQL instruction, and generating an execution plan according to the parsed SQL instruction;

[0056] In step S1 and step S2, after GBase obtains SQL data in the cluster node, it first performs data parsing, checking, and execution plan generation. The specific process is to first perform lexical parsing on the SQL statement: the lexical parser decomposes the SQL query into a series of tokens, such as keywords, identifiers, string constants, numbers, etc.; for example, for the query SELECT * FROM users WHERE id =1, the lexical parser will decompose it into tokens such as SELECT, *, FROM, users, WHERE, id, =, and 1; then the syntax parser combines the tokens generated by the lexical parser into grammatical structures (such as expressions, statements, etc.) and generates an abstract syntax tree. The syntax parser also checks whether the SQL query conforms to the syntax rules of the SQL language. If there is a syntax error in the query, the database will return an error message.

[0057] Secondly, query optimization is performed. At this stage, the optimizer will consider multiple possible execution strategies and select the best one. Factors that the optimizer may consider include the use of indexes, the order of table joins, the evaluation method of subqueries, etc. The optimizer will generate a query execution plan that describes how to access data in the database to meet the query requirements.

[0058] The second step is execution plan generation, which is output from the query optimizer mentioned above. The execution plan usually includes a series of operators, such as scanning, filtering, joining, etc., as well as the data flow between these operators, so that the execution plan can be used to access data and generate query results.

[0059] In addition, when checking, parsing, and plan generation are performed in cluster nodes, the process is simplified in single-machine nodes, and the character set of string data is no longer checked twice. This is because the detection has been performed in the cluster nodes, and the cluster retains the complete original string information (client information). In the single-machine computing node, it has been unified as system_charset. The goal of the check should be whether the original string information can be converted. It is meaningless to detect it in a single machine, so skipping the secondary detection can reduce performance loss.

[0060] S3: Encode the execution plan in utf8mb4 encoding to obtain a first character set, and perform SQL query according to the first character set to obtain an execution result;

[0061] Furthermore, using utf8mb4 as system_charset can ensure that the rare characters input by the client can be submitted to the data node in full, and that the rare character calculation results of the data node can be transmitted to the client without being affected by the default encoding method of each node.

[0062] Wherein, the first character set in step S3 is the system_charset character set type.

[0063] After step S1 and step S2, we uniformly convert SQL into the system_charset character set for node distribution and then interact with actual data. This not only retains the client's character set information, but also performs character set conversion after generating the execution plan. The execution logic of the plan retains the original encoding information, does not cause data loss, and ensures data integrity. In addition, no matter how the client's character set changes, it does not affect the interaction with the actual data. Since a consistent character set is used, the correctness of the data set obtained by the data node is guaranteed.

[0064] Wherein, in step S3, when encoding the execution plan, the bare character string in the SQL instruction corresponding to the execution plan is encoded.

[0065] During data transmission, the present invention only converts the naked character string in the variable SQL into the system_charset encoding mode, because the client's character set only affects the SQL including the character string, instruction name, etc., and can greatly reduce the performance loss.

[0066] S4: Encode the execution result to obtain a second character set, and return the second character set to the client.

[0067] Wherein, the second character set in step S4 is the result_charset character set type.

[0068] Furthermore, the encoding method of the second character set is the same as the encoding method of the first character set, that is, the encoding method of the result_charset character set is the same as the encoding method of the system_charset character set, which can ensure data consistency.

[0069] The format of the result_charset character set type in step S4 is the same as the format of the character set type of the client.

[0070] Specifically, to solve the problem of unified allocation among nodes, GBase uses system_charset by default for all SQL transmissions between nodes. When the cluster gcluseter sends information to a single machine, it transmits SQL of the system_charset type and passes the character set of the cluster management node as result_charset. The format of the result_charset character set is theoretically consistent with the character set of the client. After each node receives the SQL instruction of the system_charset character set type, it performs single-machine calculations within the single-machine node. After converting the calculation result into result_charset, it sends it back to the cluster node. This data transfer method can efficiently achieve data isolation and consistency.

[0071] It should also be noted that correspondingly, during encoding in the cluster, security conversion detection of the character set is performed, while in the single machine, the security conversion detection of the character set is skipped. For example: Select * from t1 where a='abc', that is, it is detected whether the character sets of a and 'abc' can be compared. In the cluster: compare the character set of a and the character set of 'abc'. Here, the character set of 'abc' is the character set of the client, and it is necessary to detect whether they can be unified before comparison; while in the single machine: the character set of 'abc' has already become system_charset, so there is no need to perform detection anymore.

[0072] In addition, when performing character set conversion through encoding in the above steps S3 and S4, the cluster uniformly converts to system and sends it to the single machine, and the single machine uniformly converts to result and sends it to the cluster. This ensures that the cluster can uniformly allocate all single machines, perform unified information reception and transmission, and the cluster ignores the encoding and processing inside the single machine. The cluster and the single machine are independent, so that the nodes can be uniformly allocated.

[0073] Generally speaking, in the above SQL query method based on a cluster database, the method of encoding the first character set and the second character set can also be used for other data transmissions and other functions in some embodiments. For example, Insert into t1 values('中') will face the problem of how to ensure the correct storage of data when the database and the client are inconsistent. That is, the above encoding method can be used for correct character set collation and insertion.

[0074] The present invention also provides a SQL query system based on a cluster database for executing a SQL query method based on a cluster database as described in any one of the above, including:

[0075] Receiving module 100: used to receive SQL instructions sent by the client;

[0076] Generating module 200: used to parse the SQL instruction and generate an execution plan according to the parsed SQL instruction;

[0077] The first conversion module 300 is used to encode the execution plan in utf8mb4 encoding to obtain a first character set;

[0078] Execution module 400: used to perform SQL query according to the first character set to obtain execution results;

[0079] The second conversion module 500 is used to encode the execution result to obtain a second character set;

[0080] Return module 600: used to return the second character set to the client.

[0081] The receiving module 100 , the generating module 200 and the first converting module 300 are all established based on cluster nodes.

[0082] The execution module 400, the second conversion module 500 and the return module 600 are all established based on a single node.

[0083] Furthermore, the cluster supporting the operation of the above modules generates a cluster plan for distributed management, and the stand-alone machine generates a stand-alone plan for specific data processing. The above-mentioned execution module and return module can also exist in a stand-alone machine or a cluster. The return module of the stand-alone machine returns the result to the cluster, and the return module of the cluster returns the result to the client.

[0084] The present invention provides a SQL query method and system based on a cluster database. Through the use of system_charset, system_charset is a unified character set with a fixed value that does not change with encoding methods such as client, database encoding, and table encoding. Under the premise of being compatible with flexible character set settings of multiple nodes in the cluster database, efficient information management is achieved, and the system is compatible with clients of various character set encodings, thereby enhancing product usability, reducing frequent conversion of character sets, and saving computing performance.

[0085] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A SQL query method based on a cluster database, characterized in that: include: S1: Receive SQL instructions sent by the client; S2: parsing the SQL instruction, and generating an execution plan according to the parsed SQL instruction; S3: Encode the execution plan in utf8mb4 encoding to obtain a first character set, and perform SQL query according to the first character set to obtain an execution result; S4: Encode the execution result to obtain a second character set, and return the second character set to the client; wherein the first character set is of the system_charset character set type, and the second character set is of the result_charset character set type; the format of the result_charset character set type is the same as the format of the character set type of the client; in order to solve the problem of unified deployment between nodes, the cluster database uses system_charset by default for SQL transmission between nodes. When the cluster sends information to a single machine, it transmits SQL of the system_charset type, and the format of the result_charset character set is consistent with the character set of the client. After each node obtains the SQL instruction of the system_charset character set type, it performs stand-alone calculations in the stand-alone node, converts the calculation results into result_charset, and then sends them back to the cluster node.

2. The SQL query method based on cluster database according to claim 1, characterized in that: In step S3, when encoding the execution plan, the bare character string in the SQL instruction corresponding to the execution plan is encoded.

3. A SQL query system based on a cluster database, used to execute a SQL query method based on a cluster database as claimed in any one of claims 1 to 2, characterized in that: include: Receiving module: used to receive SQL instructions sent by the client; Generation module: used to parse the SQL instructions and generate an execution plan according to the parsed SQL instructions; A first conversion module: used for encoding the execution plan by using utf8mb4 encoding to obtain a first character set; Execution module: used for performing SQL query according to the first character set to obtain execution results; The second conversion module is used to encode the execution result to obtain a second character set; wherein the first character set is the system_charset character set type, and the second character set is the result_charset character set type; the format of the result_charset character set type is the same as the format of the client character set type; in order to solve the problem of unified deployment between nodes, the cluster database uses system_charset by default for SQL transmission between nodes. When the cluster sends information to a single machine, it transmits SQL of the system_charset type, and the format of the result_charset character set is consistent with the client character set. After each node obtains the SQL instruction of the system_charset character set type, it performs stand-alone calculation in the stand-alone node, converts the calculation result into result_charset, and then sends it back to the cluster node; Return module: used for returning the second character set to the client.

4. The SQL query system based on cluster database according to claim 3, characterized in that: The receiving module, the generating module and the first converting module are all established based on cluster nodes.

5. The SQL query system based on cluster database according to claim 3, characterized in that: The execution module, the second conversion module and the return module are all established based on a single machine node.

Images