A method and device for querying database partition tables

Abstract

The purpose of this application is to provide a method and device for querying database table partitioning. This application obtains the SQL statement corresponding to the query instruction input by the user, and calls Gorm query based on the SQL statement; intercepts the SQL statement based on the called Gorm query; parses the intercepted SQL statement into an abstract syntax tree object, and determines whether there is a table partitioning rule according to the abstract syntax tree object. If so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters; performs table partitioning processing on the abstract syntax tree object according to the table partitioning related parameters to obtain the SQL statement after the table partitioning, and queries the database according to the SQL statement after the table partitioning to obtain the query result. Thus, the plug-in mechanism is implemented in Gorm, the ORM framework of Go, to intelligently and efficiently complete the database table partitioning action, and improve the performance, capacity and stability of the system.

Description

Technical Field

[0001] The present application relates to the field of computers, and in particular to a method and device for querying database partition tables. Background Art

[0002] Currently, large-scale users use applications to generate a large amount of data, and the core business tables in applications can reach hundreds of billions of data. In the existing technology, the processing method of a single database table will cause various problems under this data scale of hundreds of billions of data, such as decreased query performance, too large single machine capacity to store, and poor system stability.

[0003] There are relatively mature table partitioning solutions in other languages, but there is no proper solution for applying it to the Go language and Gorm ecological application environment. For example, it is easy to cause damage to the business, difficult to integrate into the existing system, and cause pollution to the business data. Summary of the invention

[0004] One purpose of the present application is to provide a method and device for querying database partition tables, so as to solve the problem that database partition tables cannot be implemented in the Go language and Gorm ecological application environment in the prior art.

[0005] According to one aspect of the present application, a method for querying a database table is provided, the method comprising:

[0006] Obtaining an SQL statement corresponding to the query instruction input by the user, and calling Gorm query based on the SQL statement;

[0007] Intercepting the SQL statement based on the called Gorm query;

[0008] Parse the intercepted SQL statement into an abstract syntax tree object, determine whether there is a table partitioning rule based on the abstract syntax tree object, and if so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters;

[0009] The abstract syntax tree object is processed into a table according to the table-related parameters to obtain a SQL statement after the table is divided, and a database is queried according to the SQL statement after the table is divided to obtain a query result.

[0010] Optionally, the SQL statement includes a string query instruction and a query value array parameter, and parsing the intercepted SQL statement into an abstract syntax tree object includes:

[0011] Assemble the intercepted string query instruction and query value array parameters to get a complete SQL statement;

[0012] Parse a complete SQL statement into an abstract syntax tree object.

[0013] Optionally, before calling the Gorm query based on the SQL statement, the method includes:

[0014] Determine the table partitioning rule by defining the table partitioning key, the table partitioning name generation function and the primary key number generation function, and store the table partitioning rule in a specified position of the hash table structure;

[0015] The table partitioning rule is registered in the Gorm plug-in of the Gorm layer.

[0016] Optionally, the SQL statement is intercepted based on the called Gorm query, including:

[0017] Modify the instance parameters of the database that needs to execute the SQL statement through the Gorm plug-in at the Gorm layer, and determine the database connection pool according to the modified instance parameters, wherein the database connection pool is used to connect the Gorm layer with the database;

[0018] The SQL statement is intercepted into the database connection pool.

[0019] Optionally, the table-splitting related parameters include a table-splitting key, a table-splitting name, and a table-splitting key value. The abstract syntax tree object is subjected to table-splitting processing according to the table-splitting related parameters to obtain a SQL statement after table-splitting, including:

[0020] Determining a query type of the query instruction according to the abstract syntax tree object, wherein the query type includes an insert statement, a query statement, an update statement, or a delete statement;

[0021] Based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain a SQL statement after the sub-table.

[0022] Optionally, based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain a SQL statement after the sub-table is obtained, including:

[0023] When the query type is a query statement, an update statement, or a delete statement, a new sub-table name is calculated according to the sub-table key value, and the sub-table name in the abstract syntax tree object is updated using the new sub-table name to obtain a SQL statement after the sub-table is divided;

[0024] When the query type is an insert statement, a new sub-table name is calculated according to the sub-table key value, the sub-table name in the abstract syntax tree object is updated using the new sub-table name, and it is determined whether a primary key exists. If not, a primary key value in a fixed format is generated according to a primary key calculation function, and the primary key value in the fixed format is used to update the primary key value in the abstract syntax tree object to obtain a SQL statement after the sub-table is obtained.

[0025] Optionally, judging whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traversing the child nodes in the abstract syntax tree object to determine table partitioning related parameters includes:

[0026] Determine whether there is a table partition rule according to the table partition name of the abstract syntax tree object. If so, traverse the child nodes of the abstract syntax tree object.

[0027] Check whether the sub-node has a sub-table key. If so, return sub-table related parameters; if not, report an error.

[0028] Optionally, reporting an error includes:

[0029] If the traversal of the child nodes is completed, an error will be reported if the sub-table key does not exist;

[0030] If the traversal of the child nodes fails, the child nodes are traversed again.

[0031] According to another aspect of the present application, a device for querying a database table is also provided, the device comprising:

[0032] A data acquisition module is used to obtain the SQL statement corresponding to the query instruction input by the user, and call the Gorm query based on the SQL statement;

[0033] An instruction interception module, used to intercept the SQL statement based on the called Gorm query;

[0034] A semantic parsing module is used to parse the intercepted SQL statement into an abstract syntax tree object, determine whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters;

[0035] The data processing module is used to perform table-splitting processing on the abstract syntax tree object according to the table-splitting related parameters, obtain the SQL statement after the table-splitting, query the database according to the SQL statement after the table-splitting, and obtain the query result.

[0036] According to yet another aspect of the present application, a computer-readable medium is provided, on which computer-readable instructions are stored. The computer-readable instructions can be executed by a processor to implement any of the methods described above.

[0037] According to another aspect of the present application, a device for querying a database table is provided, the device comprising:

[0038] one or more processors; and

[0039] A memory storing computer readable instructions, which, when executed, cause the processor to perform the operations of any of the methods described above.

[0040] Compared with the prior art, the present application obtains an SQL statement corresponding to a query instruction input by a user, and calls a Gorm query based on the SQL statement; intercepts the SQL statement based on the called Gorm query; parses the intercepted SQL statement into an abstract syntax tree object, and determines whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traverses the child nodes in the abstract syntax tree object to determine table partitioning related parameters; performs table partitioning on the abstract syntax tree object according to the table partitioning related parameters to obtain a SQL statement after table partitioning, and queries a database according to the SQL statement after table partitioning to obtain a query result. When developers write business logic (SQL queries), they do not need to specify table partitioning specifically, and can still maintain SQL statements for ordinary single-table queries. When SQL execution reaches the table partitioning logic link in the middleware, it can be automatically processed, with little business pollution, transparent to the application, and non-destructive to the business. It only needs to be defined and adjusted in small amounts (query conditions that meet the table partitioning logic) to be integrated into the existing system. The extremely small string processing resource consumption enables it to have better performance and can support all SQL queries in the entire Go language ecosystem, regardless of the database or framework. Therefore, a plug-in mechanism is implemented in Go's ORM framework Gorm to intelligently and efficiently complete database table partitioning actions, thereby improving the system's performance, capacity, and stability. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Other features, objects and advantages of the present application will become more apparent by reading the detailed description of non-limiting embodiments made with reference to the following drawings:

[0042] Figure 1 A schematic diagram of a method flow for querying a database table partition provided according to one aspect of the present application is shown;

[0043] Figure 2 A schematic diagram of the semantic structure of an abstract syntax tree object in an optional embodiment of the present application is shown;

[0044] Figure 3 A schematic flow chart of a method for traversing a tree structure of an abstract syntax tree object in an optional embodiment of the present application is shown;

[0045] Figure 4A schematic diagram of a method flow for obtaining SQL statements after table partitioning in an optional embodiment of the present application is shown;

[0046] Figure 5a A schematic diagram of a method flow for querying a database table in an optional embodiment of the present application is shown;

[0047] Figure 5b A schematic diagram of a method flow of a Gorm layer calling a Gorm database in an optional embodiment of the present application is shown;

[0048] Figure 6 A diagram showing a device framework structure for database partition table query according to another aspect of the present application is shown.

[0049] The same or similar reference numerals in the drawings represent the same or similar components. DETAILED DESCRIPTION

[0050] The present application is described in further detail below in conjunction with the accompanying drawings.

[0051] In a typical configuration of the present application, the terminal, the device of the service network and the trusted party all include one or more processors (CPU), input / output interfaces, network interfaces and memories.

[0052] The memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0053] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include non-transitory media such as modulated data signals and carrier waves.

[0054] Figure 1A schematic flow chart of a method for querying a database table partitioning provided according to one aspect of the present application is shown, the method comprising: S100 to S400, wherein, in S100, an SQL statement corresponding to a query instruction input by a user is obtained, and a Gorm query is called based on the SQL statement; in S200, the SQL statement is intercepted based on the called Gorm query; in S300, the intercepted SQL statement is parsed into an abstract syntax tree object, and it is determined whether a table partitioning rule exists according to the abstract syntax tree object, and if so, the child nodes in the abstract syntax tree object are traversed to determine table partitioning related parameters; in S400, the abstract syntax tree object is partitioned according to the table partitioning related parameters to obtain a SQL statement after the table partitioning, and a database is queried according to the SQL statement after the table partitioning to obtain a query result. When developers write business logic (SQL queries), they do not need to specify table partitioning specifically, and can still maintain SQL statements for ordinary single-table queries. When SQL execution reaches the table partitioning logic link in the middleware, it can be automatically processed, with little business pollution, transparent to the application, and non-destructive to the business. It only needs to be defined and adjusted in small amounts (query conditions that meet the table partitioning logic) to be integrated into the existing system. The extremely small string processing resource consumption enables it to have better performance and can support all SQL queries in the entire Go language ecosystem, regardless of the database or framework. Therefore, a plug-in mechanism is implemented in Go's ORM framework Gorm to intelligently and efficiently complete database table partitioning actions, thereby improving the system's performance, capacity, and stability.

[0055] Specifically, in S100, an SQL statement corresponding to the query instruction input by the user is obtained, and a Gorm query is called based on the SQL statement. Here, an SQL statement corresponding to the query instruction input by the user is obtained through a web page or other tool, wherein the SQL statement may include an original query instruction (sql) of a string type and a value array parameter (args) of the SQL query, and a Gorm query is called based on the SQL statement, wherein the Gorm query is to call the Gorm database in the Gorm layer to replace the original connection port database. Among them, sql: the original SQL statement of the string type, such as select*from postswhere channel_id=100; args: the value array parameter of the SQL query, such as select*from postswhere channel_id=? and status=?, generally there will be a corresponding value corresponding to the? position, such as [100, 2].

[0056] In S200, the SQL statement is intercepted based on the called Gorm query. Here, all SQL statements input by the user are intercepted using the called Gorm query, and then the intercepted SQL statements can be replaced using MyConnPool to ensure that all SQL statements are intercepted.

[0057] In S300, the intercepted SQL statement is parsed into an abstract syntax tree object, and it is determined whether there is a table-splitting rule according to the abstract syntax tree object. If so, the sub-nodes in the abstract syntax tree object are traversed to determine the table-splitting related parameters. Here, some applications can be used to parse the intercepted SQL statement into an abstract syntax tree object (AST tree object) that is easy to understand by a program, such as SQL Parser, etc., and then it is determined whether the table-splitting rule for table-splitting is defined according to some relevant parameters in the abstract syntax tree object. If not, the SQL statement input by the user is not processed, and the original SQL statement is directly returned. What is obtained is a query request without table-splitting, and database resources are applied for to obtain query results based on the query request without table-splitting. When there is a table-splitting rule, the sub-nodes (node) in the abstract syntax tree object are traversed to determine the table-splitting related parameters, such as table-splitting key, table-splitting name and table-splitting query value. Among them, the AST tree object can be obtained based on SQLParser parsing, and the AST object is a tree structure.

[0058] In S400, the abstract syntax tree object is processed into a table according to the table-splitting related parameters to obtain a SQL statement after the table-splitting, and the database is queried according to the SQL statement after the table-splitting to obtain a query result. Here, the abstract syntax tree object is processed into a table according to the table-splitting related parameters, for example, the table-splitting name in the abstract syntax tree object is replaced according to the table-splitting name to obtain a SQL statement after the table-splitting, and the database is queried based on the SQL statement after the table-splitting to obtain a corresponding query result. When developers write business logic (SQL queries), they do not need to specify table partitioning specifically, and can still maintain SQL statements for ordinary single-table queries. When SQL execution reaches the table partitioning logic link in the middleware, it can be automatically processed, with little business pollution, transparent to the application, and non-destructive to the business. It only needs to be defined and adjusted in small amounts (query conditions that meet the table partitioning logic) to be integrated into the existing system. The extremely small string processing resource consumption enables it to have better performance and can support all SQL queries in the entire Go language ecosystem, regardless of the database or framework. Therefore, a plug-in mechanism is implemented in Go's ORM framework Gorm to intelligently and efficiently complete database table partitioning actions, thereby improving the system's performance, capacity, and stability.

[0059] In an optional embodiment of the present application, in S300, the SQL statement includes a string query instruction and a query value array parameter, and the intercepted string query instruction and query value array parameter are assembled to obtain a complete SQL statement; the complete SQL statement is parsed into an abstract syntax tree object. Here, the SQL statement includes a string query instruction (sql) and a query value array parameter (args). For example, when a user enters the column news that he wants to query, there are a total of 100 channel numbers, and when the latest 10 news data of channel number 100 need to be queried, due to the use of sub-table query, we do not know where the data of channel 100 is stored, so the sub-table sequence number is calculated by parsing the complete SQL statement into an abstract syntax tree object. In order to obtain all the abstract syntax tree objects corresponding to the complete SQL statement, the query value array parameter (args) is substituted into the string query instruction one by one to obtain the string query instruction under all query values, and a complete SQL statement is obtained. After parsing the complete SQL statement, a complete abstract syntax tree object can be obtained.

[0060] In an optional embodiment of the present application, before calling the Gorm query based on the SQL statement, the table sharding rule is determined by defining the table sharding key, table sharding name generation function and primary key number generation function, and the table sharding rule is stored in the specified position of the hash table structure; the table sharding rule is registered in the Gorm plug-in of the Gorm layer. Here, the data processing module 400 can be used to define the table sharding key, table sharding name generation function and primary key number generation function to determine the table sharding rule. In an optional embodiment of the present application, we need to define the table sharding rule, combine the characteristics of the Gorm plug-in (Gorm Plugin), and register the table sharding rule to the Gorm Plugin of the Gorm layer to ensure that all SQL queries of Gorm can be intercepted by our table sharding program. We need to define a table sharding rule for each table so that the database name and table name of each table can be determined. Define a table sharding rule and store it in resolvers. Resolvers is a hash table structure, the key is the original table name that needs to be sharded, and the key value is the rule definition that needs to be sharded. Each resolver needs to be defined separately: a sharding key (ShardingKey) of string type, which defines which field value this resolver uses to calculate the sharding table, which is used to query whether there is a sharding key and extract the value of the sharding key; a sharding name generation rule function (ShardingTable), which receives the extracted sharding key value and returns a sharding name of string type, which is used to generate the sharding name; a function for generating primary key numbers (GeneratePrimaryKey), which is used to generate primary keys for insert statements. Among them, resolver is the sharding rules defined for different tables, including key information describing the sharding rules such as sharding key ShardingKey, shardingTable, primary key GeneratePrimaryKey, etc.; resolvers is an array type, which is used to store the sharding rules (resolvers) we defined for different shards in the entire system.

[0061] In an optional embodiment of the present application, in S200, the instance parameters of the database that needs to execute SQL statements are modified at the Gorm layer through the Gorm plug-in, and a database connection pool is determined according to the modified instance parameters, wherein the database connection pool is used to connect the Gorm layer with the database; the SQL statement is intercepted into the database connection pool. Here, the instance parameters (db parameters) of the database that needs to execute SQL statements are modified at the Gorm layer through the Gorm plug-in, and a database connection pool (MyConnPool) is determined according to the modified instance parameters, MyConnPool is used to connect the Gorm layer with the database, and then the SQL statement is intercepted into MyConnPool.

[0062] Continuing with the above example, after defining the table partitioning and table partitioning rules, register these rules to the Gorm plugin. The Gorm plugin has a set of standard interfaces, so that our customized plugins can be supported by the Gorm plugin. Gorm provides a Use function to register plugins. The plugin needs to implement the Plugin interface code as follows:

[0063]

[0064]

[0065] Among them, Name is a function used to define the name of the plugin, and returns a plugin name of string type. The plugin can be named "gorm:sharding"; Initialize is a function that can receive a parameter of a Gorm database instance object. The modification action of db parameters can be implemented in the Initialize function of the plugin.

[0066] Here, there is a connection pool (ConnPool) object on the database instance (db instance) and db.Statement of Gorm (db.ConnPool and db.Statement.ConnPool are the same thing), which implements the connection instance method of the Go language standard library database / sql, that is, the connection pool (ConnPool). In order to intercept and replace all SQL statements before they are executed to the database, a custom MyConnPool can be implemented to replace (db.ConnPool and db.Statement.ConnPool), and all query instructions are passed through MyConnPool to complete the interception and processing of SQL statements issued by all users. When MyConnPool intercepts SQL statements, it calls the corresponding table partitioning function, and then replaces the SQL statement to achieve the purpose of table partitioning query. Among them, ConnPool is a Conn implementation based on the Go language database / sql, which includes several key functions: ExecContext, QueryContext, and QueryRowContext. SQL queries will pass through these three functions before they are executed to the database.

[0067] In an optional embodiment of the present application, in S400, the sub-table related parameters include a sub-table key, a sub-table name and a sub-table key value, and the query type of the query instruction is determined according to the abstract syntax tree object, wherein the query type includes an insert statement, a query statement, an update statement or a delete statement; based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain the SQL statement after the sub-table is divided. Here, the sharding table related parameters include the sharding table key (ShardingKey), the sharding table name (TableName) and the sharding table key value. The query type of the query instruction is determined according to the semantics in the abstract syntax tree, wherein the query type includes an insert statement (ast.INSERT), a query statement (ast.SELECT), an update statement (ast.UPDATE) or a delete statement (ast.DELETE). Then, the abstract syntax tree object is processed accordingly based on the query type. For example, the sharding table name generation function (ShardingTable function) can be used to calculate and determine the sharding table name according to the sharding table key value, and the sharding table name contained in the abstract syntax tree object is replaced with the calculated sharding table name to generate the SQL statement after the sharding table. Wherein, TableName is the table name of the string type of the SQL statement.

[0068] In an optional embodiment of the present application, in S400, when the query type is a query statement, an update statement, or a delete statement, a new sub-table name is calculated according to the sub-table key value, and the sub-table name in the abstract syntax tree object is updated using the new sub-table name to obtain a SQL statement after the sub-table is divided; when the query type is an insert statement, a new sub-table name is calculated according to the sub-table key value, and the sub-table name in the abstract syntax tree object is updated using the new sub-table name, and it is determined whether a primary key exists. If not, a primary key value in a fixed format is generated according to a primary key calculation function, and the primary key value in the abstract syntax tree object is updated using the primary key value in the fixed format to obtain a SQL statement after the sub-table is divided. Here, when the query type is an insert statement, the sub-table name in the abstract syntax tree object is updated using the new sub-table name, and it is determined whether a primary key exists. If not, a primary key value in a fixed format is generated according to a primary key calculation function, and the primary key value in the abstract syntax tree object is updated using the primary key value in the fixed format. Since the database primary key is incremented based on a single table, the default auto-increment primary key of the database cannot be used at this time, and some custom primary key value generation rules need to be implemented or adopted. The primary key calculation function can be called to generate a primary key value in a fixed format. By defining a GeneratePrimaryKey function, a primary key value of type int64 is generated and returned. In an optional embodiment of the present application, the following methods can be applied: snowflake, which can generate a number in int64 format. The front part is a timestamp, which becomes larger over time and can be used for sorting; uuid-UUID generator, which can generate a globally unique ID, but cannot be used for sorting; using the database auto-increment feature, for example, PostgreSQL can directly call the SequenceManipulation Functions function to achieve auto-increment number generation.

[0069] In an optional embodiment of the present application, in S300, it is determined whether there is a sub-table rule according to the sub-table name of the abstract syntax tree object. If so, the child nodes of the abstract syntax tree object are traversed; it is checked whether the child node has a sub-table key. If so, the sub-table related parameters are returned; if not, an error is reported. Here, it is determined whether the sub-table rule is defined according to the sub-table name (TableName) of the abstract syntax tree object. If so, the child nodes (Node) of the abstract syntax tree object are traversed. Optionally, before determining whether there is a sub-table rule according to the sub-table name of the abstract syntax tree object, it is determined whether there is a syntax error according to the child nodes in the abstract syntax tree object. If so, a syntax error is reported.

[0070] Figure 2The schematic diagram of the semantic structure of an abstract syntax tree object in an optional embodiment of the present application is shown. By traversing the tree structure of this abstract syntax tree object, we can find out the nodes such as SELECT, FROM, WHERE, ORDER, LIMIT, and the details of the child nodes. In the application scenario of the sub-table of this embodiment, it is preset that there is a news table (posts), which contains a channel field (channel_id). There are more data in the current posts table, and it needs to be split into multiple sub-tables. Taking the posts table as an example, we need to define the posts table and split it according to channel_id. The splitting method is that channel_id is evenly distributed in 16 tables. Then it is necessary to define a resolver: TableName is posts, ShardingKey is channel_id, and the ShardingTable function is implemented. The sub-table key value passed in is modulo 16 as the sub-table index (tableIndex), and then "posts_" + tableIndex is returned to get the sub-table name, that is, according to 16 sub-tables, the 16 table names of posts_0, posts_1...posts_14, and posts_15 can be obtained. Implement the GeneratePrimaryKey function and return a primary key number (int64 type) generated by Snowflake. TableIndex is an integer type starting from 0, indicating the first sub-table.

[0071] Continuing with the above embodiment, if it needs to be split into 16 sub-tables, the table splitting rule is set to split the table based on the value of channel_id modulo 16, and the name of the sub-table is prefixed with posts_, followed by the modulo value, and the range of the sub-table will be: posts_0, posts_2, ..., posts_15. That is, the code can be obtained as:

[0072] Subtable name = "posts_" + (channel_id% 16)

[0073] Here, we use query data as an example to illustrate the SQL parsing and replacement process. The process of inserting, updating or deleting data is similar. Assuming that the current data has been divided into 16 sub-tables, and the data is evenly distributed to posts_0, posts_2, ..., posts_15 based on the channel_id modulus, there is the following string type SQL statement:

[0074] select*from posts where channel_id=100and status=1limit 10order byid desc

[0075] The above SQL statement means to query the data of the news table with channel 100 and get the latest 10 pieces. Based on the 16-table partitioning rule, the data of channel 100 should be distributed in the posts_4 table, as shown below:

[0076] Subtable name = "posts_" + (100% 16) = "posts_4"

[0077] Next, replace the “from posts” part of the SQL statement with “from posts_4” to ensure that the query points to the correct subtable to retrieve data.

[0078] In an optional embodiment of the present application, in S300, if the traversal of the child node is completed, an error is reported for the non-existence of the sub-table key; if the traversal of the child node fails, the child node is traversed again. Here, when the traversal of the child node is completed, an error can be reported for the non-existence of the sub-table key, and the subsequent sub-table processing cannot be performed if the sub-table key does not exist; if the traversal of the child node is not successful, the child node is traversed again.

[0079] Figure 3 A flow chart of a method for traversing the tree structure of an abstract syntax tree object in an optional embodiment of the present application is shown. Here, if you want to find the sub-table name TableName, you can find the FROM node in the sub-node by traversing the sub-nodes of the abstract syntax tree object. When the FROM node is found, the value of the sub-table name posts can be obtained from the TableName node. Then, based on the sub-table name, determine whether the sub-table rule is defined. If not, ignore the table. If so, further traverse the sub-nodes of WHERE to check whether the sub-table key is included. If so, the sub-table name, sub-table key and sub-table key value can be successfully obtained. When the abstract syntax tree object is missing the necessary nodes, a syntax error is reported. When the sub-table key is not included, it is determined whether the traversal of the abstract syntax tree object is completed. If so, an error is reported for the lack of a sub-table key; if not, traverse the node again.

[0080] Figure 4A method flow chart of obtaining a SQL statement after a table is shown in an optional embodiment of the present application. This method can call a router function to process the table logic to realize the SQL statement after obtaining the table, wherein the router is a table rule parsing function (function), which is used to receive the SQL statement and replace the SQL statement according to the table rule. It will be called every time SQL is processed. Here, the sql and args input by the user are first assembled to obtain a complete SQL statement, and then the complete SQL statement is parsed into an abstract syntax tree object (AST object), and then the query type is determined according to the AST object. When the query type is a query statement, an update statement or a delete statement, it is first determined whether the table rule is defined. If so, it is further determined whether there is a table key. If there is a table key, the table, table key and table key value can be determined. Then, based on the table function (ShardingTable function), the table name is calculated according to the table key value, and then the table name in the abstract syntax tree object is replaced with the calculated table name, and then a new SQL statement is generated from the abstract syntax tree object, that is, a SQL statement of a string type (string type) after the table is divided.

[0081] Continuing with the above embodiment, when the query type is an insert statement, first determine whether the table splitting rule is defined. If so, further determine whether there is a table splitting key. If there is a table splitting key, the table splitting, table splitting key and table splitting key value can be determined. Then, the table splitting name is calculated based on the table splitting key value based on the table splitting function (ShardingTable function), and then the table splitting name in the abstract syntax tree object is replaced with the calculated table splitting name. At this time, determine whether there is a primary key. If so, a new SQL statement is generated from the abstract syntax tree object, that is, a SQL statement of a string type (string type) after the table splitting. When there is no primary key, a primary key value of type int64 is generated based on the primary key algorithm GeneratePrimaryKey function, and the generated primary key value of type int64 is used to replace the primary key value of the abstract syntax tree to generate a new SQL statement from the abstract syntax tree object, that is, a SQL statement of a string type (string type) after the table splitting. When the table splitting rule is not defined, the table is ignored and the original SQL statement data is returned; when there is no table splitting key, an error is reported for the non-existence of the table splitting key. The principle of this application is to deeply understand the characteristics of the Go language standard library, use the mechanism of intercepting SQL statements, combine SQLParser to parse the syntax of SQL statements, understand the table name (TableName) of the SQL statement, the query condition (Where statement) or the field name (Column Name) in the insert, update, and delete statements (Insert, Update, Delete and other possible update statements). When matching the table partitioning rules, replace the SQL statement with the table partitioning logic, and then continue to execute to the database. Since we have replaced the TableName in the SQL statement with a new table partitioning name, the SQL query will naturally be executed to different tables, thereby achieving the purpose of querying or writing to the table partitions, and since the entire set of logic is inside the database / sql or Gorm Plugin, the application developer does not need to specifically specify which table to query when writing the SQL query, and the internal implementation will automatically handle it, which has little pollution to the program logic and is easy to use.

[0082] In addition, because SQL replacement is implemented in the Conn interface of database / sql, this solution can also perform many derivative extension functions, such as double writing of data (writing to a sub-table and an aggregate table at the same time when writing data, and the aggregate table contains all data to facilitate some scenarios that require unified query), etc. This application is mainly based on Gorm to demonstrate how to implement the sub-table logic, and how to integrate the database / sql-based Conn to implement the sub-table solution by writing a Gorm Plugin. In fact, due to the characteristics of the Go language standard library, this implementation method can also be widely used in all Go language ORM frameworks (including direct query using the database / sql standard library).

[0083] Figure 5a A schematic flow chart of a method for querying a database table in an optional embodiment of the present application is shown, which obtains an SQL statement sent by a user, calls the Gorm query, and then calls the Gorm database for the Gorm layer in step A1. Figure 5b A flow chart of a method for a Gorm layer to call a Gorm database in an optional embodiment of the present application is shown, which is a sub-step of step A1. After entering the Gorm layer, the Gorm database is called, and then the code is executed: connPool.QueryContext(sql,args), thereby implementing a custom database instance connection pool (MyConnPool). Specifically, in the database / sql standard library of the Go language, Conn is a basic interface (Interface) for database queries. For example: DB.Exec executes the ExecContext function of SQL; DB.Query queries the SQL-QueryContext function. Basically, almost all database queries developed in Go language projects currently use the Conn Interface to implement database calls, and the execution of SQL statements will go through the three main functions of ExecContext, QueryContext, and QueryRowContext.

[0084] The following are the definitions of these functions in the Go language:

[0085] ExecContext function: executes SQL, used to perform actions such as Insert, Update, and Delete that require updating but do not need to return records;

[0086] QueryContext function: SQL query, execute SQL actions such as Select that need to obtain data;

[0087] QueryRowContext function: SQL query single row data.

[0088] Continuing with the above embodiment, to intercept SQL statements, you can implement a MyConnPool, and implement the three functions of ExecContext, QueryContext and QueryRowContext in MyConnPool. Finally, replace the ConnPool layer of Gorm or other Go language ORM frameworks with MyConnPool to intercept all SQL queries.

[0089] Next, in the MyConnPool layer, the router function can be called to process the table partitioning logic, assemble sql and args to get the complete SQL, and use SQL Parser to parse the SQL statement to get the abstract syntax tree object. According to the table partitioning name TableName in the abstract syntax tree object, it is determined whether the table partitioning rule is defined. If not, the SQL statement is not processed and the original SQL statement data is returned to perform untabled queries. Other untabled Tablecategories, channels, drafts and other instructions are executed in the database layer to get the query results. Among them, router is a table partitioning rule parsing function, which is used to receive SQL statements and replace SQL statements according to the table partitioning rules. It will be called every time SQL is processed.

[0090] If the sharding rule is defined, all nodes of the abstract syntax tree object are traversed to find the WhereColumn field, check whether there is a sharding key defined by the sharding rule and perform matching processing. When the sharding key (ShardingKey) is not found, an error is reported. The query here must contain the sharding key. When the sharding key is hit (that is, match_sharding_key is obtained), the sharding name is obtained. For example, the sharding sequence number "posts+(100%16)" is calculated with the value of WhereValue 100, and the sharding name = posts_4, and the new sharding name is obtained. The new sharding name is used to replace the sharding name (TableName) in the abstract syntax tree object to obtain the new SQL statement after the sharding. Next, the sharding query is performed based on the SQL statement after the sharding at the database layer. For example, according to 16 sub-tables, the data is evenly distributed to the 16 sub-tables posts_0, posts_2,..., posts_15 based on the channel_id modulus. Then, there is the following string type SQL statement:

[0091] select*from posts where channel_id=100and status=1limit 10order byid desc

[0092] Based on the above SQL statement, the database is queried in separate tables to obtain the data in the corresponding fifth table posts_4, and the query results are determined from the data in the posts_4 table.

[0093] The embodiment of the present application further provides a computer-readable medium on which computer-readable instructions are stored. The computer-readable instructions can be executed by a processor to implement the aforementioned method for querying database partition tables.

[0094] Corresponding to the method described above, the present application also provides a terminal, which includes a terminal capable of executing the above Figure 1 or Figure 2 or Figure 3 or Figure 4 or Figure 5a or Figure 5b The modules or units of the method steps described in each embodiment may be implemented by hardware, software or a combination of hardware and software, which is not limited by the present application. For example, in one embodiment of the present application, a device for querying a database table is also provided, wherein the device includes:

[0095] one or more processors; and

[0096] A memory storing computer-readable instructions, wherein the computer-readable instructions, when executed, cause the processor to perform the operations of the aforementioned database partition table query method.

[0097] For example, the computer readable instructions, when executed, cause the one or more processors to:

[0098] The method comprises the following steps: obtaining an SQL statement corresponding to a query instruction input by a user, calling a Gorm query based on the SQL statement; intercepting the SQL statement based on the called Gorm query; parsing the intercepted SQL statement into an abstract syntax tree object, judging whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traversing the child nodes in the abstract syntax tree object to determine table partitioning related parameters; performing table partitioning processing on the abstract syntax tree object according to the table partitioning related parameters to obtain a table partitioned SQL statement, querying a database according to the table partitioned SQL statement, and obtaining a query result.

[0099] Figure 6A device framework structure diagram for database table partitioning query according to another aspect of the present application is shown, wherein the device includes: a data acquisition module 100, used to obtain the SQL statement corresponding to the query instruction input by the user, and call the Gorm query based on the SQL statement; an instruction interception module 200, used to intercept the SQL statement based on the called Gorm query; a semantic parsing module 300, used to parse the intercepted SQL statement into an abstract syntax tree object, and determine whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters; a data processing module 400, used to perform table partitioning processing on the abstract syntax tree object according to the table partitioning related parameters to obtain the SQL statement after the table partitioning, and query the database according to the SQL statement after the table partitioning to obtain the query result. When developers write business logic (SQL queries), they do not need to specify table partitioning specifically, and can still maintain SQL statements for ordinary single-table queries. When SQL execution reaches the table partitioning logic link in the middleware, it can be automatically processed, with little business pollution, transparent to the application, and non-destructive to the business. It only needs to be defined and adjusted in small amounts (query conditions that meet the table partitioning logic) to be integrated into the existing system. The extremely small string processing resource consumption enables it to have better performance and can support all SQL queries in the entire Go language ecosystem, regardless of the database or framework. Therefore, a plug-in mechanism is implemented in Go's ORM framework Gorm to intelligently and efficiently complete database table partitioning actions, thereby improving the system's performance, capacity, and stability.

[0100] It should be noted that the contents executed by the data acquisition module 100, the instruction interception module 200, the semantic analysis module 300 and the data processing module 400 are respectively the same or correspondingly the same as the contents in the above steps S100, S200, S300 and S400, and for the sake of simplicity, they are not repeated here.

[0101] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

[0102] It should be noted that the present application can be implemented in software and / or a combination of software and hardware, for example, can be implemented using an application specific integrated circuit (ASIC), a general purpose computer or any other similar hardware device. In one embodiment, the software program of the present application can be executed by a processor to implement the steps or functions described above. Similarly, the software program of the present application (including relevant data structures) can be stored in a computer-readable recording medium, for example, a RAM memory, a magnetic or optical drive or a floppy disk and similar devices. In addition, some steps or functions of the present application can be implemented using hardware, for example, as a circuit that cooperates with a processor to perform each step or function.

[0103] In addition, a part of the present application may be applied as a computer program product, such as a computer program instruction, which, when executed by a computer, can call or provide the method and / or technical solution according to the present application through the operation of the computer. The program instruction for calling the method of the present application may be stored in a fixed or removable recording medium, and / or transmitted through a data stream in a broadcast or other signal-bearing medium, and / or stored in a working memory of a computer device that runs according to the program instruction. Here, according to an embodiment of the present application, a device is included, the device including a memory for storing computer program instructions and a processor for executing program instructions, wherein, when the computer program instruction is executed by the processor, the device is triggered to run the method and / or technical solution based on the aforementioned multiple embodiments according to the present application.

[0104] It is obvious to those skilled in the art that the present application is not limited to the details of the above exemplary embodiments, and that the present application can be implemented in other specific forms without departing from the spirit or basic features of the present application. Therefore, from any point of view, the embodiments should be regarded as exemplary and non-restrictive, and the scope of the present application is limited by the attached claims rather than the above description, so it is intended to include all changes that fall within the meaning and scope of the equivalent elements of the claims in the present application. Any figure mark in the claims should not be regarded as limiting the claims involved. In addition, it is obvious that the word "comprising" does not exclude other units or steps, and the singular does not exclude the plural. Multiple units or devices stated in the device claim can also be implemented by one unit or device through software or hardware. The words first, second, etc. are used to indicate names, and do not indicate any particular order.

Claims

1. A method for querying database tables, wherein: The method comprises: Determine the table partitioning rule by defining the table partitioning key, the table partitioning name generation function and the primary key number generation function, and store the table partitioning rule in a specified position of the hash table structure; Register the table partitioning rules to the Gorm plug-in of the Gorm layer, obtain the SQL statement corresponding to the query instruction input by the user, and call the Gorm query based on the SQL statement; Modify the instance parameters of the database that needs to execute the SQL statement through the Gorm plug-in at the Gorm layer, and determine the database connection pool according to the modified instance parameters, wherein the database connection pool is used to connect the Gorm layer with the database; Intercepting the SQL statement into the database connection pool; Parse the intercepted SQL statement into an abstract syntax tree object, determine whether there is a table partitioning rule based on the abstract syntax tree object, and if so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters; Performing table splitting processing on the abstract syntax tree object according to the table splitting related parameters to obtain a SQL statement after the table splitting, and querying the database according to the SQL statement after the table splitting to obtain a query result; The step of determining whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traversing the child nodes in the abstract syntax tree object to determine table partitioning related parameters includes: Determine whether there is a table partition rule according to the table partition name of the abstract syntax tree object. If so, traverse the child nodes of the abstract syntax tree object. Check whether the sub-node has a sub-table key. If so, return sub-table related parameters; if not, report an error; The table-splitting related parameters include table-splitting keys, table-splitting names, and table-splitting key values. Table-splitting processing is performed on the abstract syntax tree object according to the table-splitting related parameters to obtain a table-splitting SQL statement, including: Determining a query type of the query instruction according to the abstract syntax tree object, wherein the query type includes an insert statement, a query statement, an update statement, or a delete statement; Based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain a SQL statement after the sub-table.

2. The method according to claim 1, wherein: The SQL statement includes a string query instruction and a query value array parameter, and parsing the intercepted SQL statement into an abstract syntax tree object includes: Assemble the intercepted string query instruction and query value array parameters to get a complete SQL statement; Parse a complete SQL statement into an abstract syntax tree object.

3. The method according to claim 1, wherein: Based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain a SQL statement after the sub-table, including: When the query type is a query statement, an update statement, or a delete statement, a new sub-table name is calculated according to the sub-table key value, and the sub-table name in the abstract syntax tree object is updated using the new sub-table name to obtain a SQL statement after the sub-table is divided; When the query type is an insert statement, a new sub-table name is calculated according to the sub-table key value, the sub-table name in the abstract syntax tree object is updated using the new sub-table name, and it is determined whether a primary key exists. If not, a primary key value in a fixed format is generated according to a primary key calculation function, and the primary key value in the fixed format is used to update the primary key value in the abstract syntax tree object to obtain a SQL statement after the sub-table is obtained.

4. The method according to claim 1, wherein: The error reporting includes: If the traversal of the child nodes is completed, an error will be reported if the sub-table key does not exist; If the traversal of the child nodes fails, the child nodes are traversed again.

5. A device for querying database sub-tables, wherein: The device comprises: A storage module, used to determine a table partitioning rule by defining a table partitioning key, a table partitioning name generation function, and a primary key number generation function, and store the table partitioning rule in a specified position of a hash table structure; The data acquisition module is used to register the table partitioning rules to the Gorm plug-in of the Gorm layer, obtain the SQL statement corresponding to the query instruction input by the user, and call the Gorm query based on the SQL statement; An instruction interception module is used to modify the instance parameters of the database that needs to execute SQL statements through the Gorm plug-in at the Gorm layer, determine the database connection pool according to the modified instance parameters, wherein the database connection pool is used to connect the Gorm layer with the database; intercept the SQL statement into the database connection pool; A semantic parsing module is used to parse the intercepted SQL statement into an abstract syntax tree object, determine whether there is a table partitioning rule according to the abstract syntax tree object, and if so, traverse the child nodes in the abstract syntax tree object to determine the table partitioning related parameters; A data processing module, used to perform table-splitting processing on the abstract syntax tree object according to the table-splitting related parameters, obtain the SQL statement after the table-splitting, query the database according to the SQL statement after the table-splitting, and obtain the query result; Wherein, the semantic parsing module is used for: Determine whether there is a table partition rule according to the table partition name of the abstract syntax tree object. If so, traverse the child nodes of the abstract syntax tree object. Check whether the sub-node has a sub-table key. If so, return sub-table related parameters; if not, report an error; The sub-table related parameters include sub-table key, sub-table name and sub-table key value, and the data processing module is used to: Determining a query type of the query instruction according to the abstract syntax tree object, wherein the query type includes an insert statement, a query statement, an update statement, or a delete statement; Based on the query type, the abstract syntax tree object is processed accordingly according to the sub-table key, the sub-table name and the sub-table key value to obtain a SQL statement after the sub-table.

6. A computer-readable medium having computer-readable instructions stored thereon, wherein the computer-readable instructions can be executed by a processor to implement the method according to any one of claims 1 to 4.

7. A device for querying database sub-tables, wherein: The device comprises: one or more processors; and A memory storing computer readable instructions which, when executed, cause the processor to perform the operations of the method as claimed in any one of claims 1 to 4.

Images