Agent method and device for vertical sub-database, equipment and medium

Abstract

The invention relates to an agent method and device for a vertical sub-database, equipment and a medium. The method comprises the following steps: when a database creation instruction of a user is received, querying a target routing strategy corresponding to the database creation instruction; selecting a target fragment from the created fragments according to a target routing strategy; executing the database creation instruction on the target fragment to create a logic database, and generating a creation log of the logic database; after the logic database is created, generating a mapping relation between the logic database and the target fragment; the mapping relation and the creation log are stored in metadata fragments, the metadata fragments are one fragment in the created fragments, and the mapping relation is a routing rule of an instruction for operating the logic database. According to the method and the device, the problems of data skew, data migration difficulty and the like caused by routing rules can be avoided, additional metadata service storage and management of the metadata do not need to be introduced, and storage and management of the metadata are more convenient.

Description

Technical Field

[0001] The present disclosure relates to the field of database technology, and in particular to a proxy method, device, equipment and medium for a vertically distributed database. Background Art

[0002] In order to alleviate the load pressure of a single database, a common method is to introduce database middleware (such as a database proxy process) to optimize the sharding of libraries and tables, thereby splitting the existing data into multiple underlying database instances, so that the load pressure is evenly distributed to multiple shards.

[0003] There are usually two types of existing database sharding solutions. One is to implement sharding based on metadata services. This solution requires a separate set of metadata services to store the mapping relationship between the database and the shards. The other is to implement sharding based on local configuration files. This solution stores the mapping relationship between the database and the shards in the local configuration file. The database agent needs to read the local configuration file to load the mapping relationship when it starts.

[0004] The mapping relationship between database and shard is used as the routing rule of database operation instructions. It is frequently used in the proxy operation process of the database and is prone to new changes. Among them, in the solution of implementing database sharding based on metadata service, the database proxy synchronizes metadata through metadata service, which means that the update and maintenance of mapping relationship depends on the performance and availability of metadata service, which increases the load pressure and operation and maintenance cost. In the solution of implementing database sharding based on local configuration file, since the data stored in the configuration file is static, if you want to change the mapping relationship, you often need to reload the entire configuration file, which is not flexible enough. Moreover, when the number of mapping relationships is large, the cost of maintaining the configuration file will increase sharply. In addition, when the mapping relationship changes, the consistency of the mapping relationship in the configuration file is difficult to guarantee. In short, the design of the above two solutions makes the update and maintenance of the mapping relationship inconvenient. Summary of the invention

[0005] In order to solve the above technical problems or at least partially solve the above technical problems, the present disclosure provides a proxy method, device, equipment and medium for a vertically distributed database.

[0006] In a first aspect, the present disclosure provides a proxy method for a vertically distributed database, comprising:

[0007] Upon receiving a database creation instruction from a user, querying a target routing policy corresponding to the database creation instruction;

[0008] Select a target shard from the created shards according to the target routing strategy;

[0009] Executing the database creation instruction on the target shard to create a logical database and generating a creation log of the logical database;

[0010] After creating the logical database, generating a mapping relationship between the logical database and the target shard;

[0011] The mapping relationship and the creation log are stored in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating the logical database.

[0012] Optionally, the querying of the target routing policy corresponding to the database creation instruction includes:

[0013] Parsing the routing indication item in the database creation instruction, wherein the routing indication item is filled with the routing strategy selected by the user from the preset routing strategies;

[0014] The routing policy filled in the routing indication item is determined as the target routing policy.

[0015] Optionally, the preset routing strategy includes a first routing strategy, a second routing strategy, and a third routing strategy;

[0016] The first routing strategy is configured to select a shard with the least amount of stored data from the created shards;

[0017] The second routing strategy is configured to select one of the created shards using a hash modulo algorithm;

[0018] The third routing strategy is configured to select a slice with a user-specified name from the created slices;

[0019] The method further comprises:

[0020] The first routing policy is configured as a default routing policy, where the default routing policy is a routing policy selected when the routing indication item is empty.

[0021] Optionally, executing the database creation instruction on the target shard to create a logical database and generating a creation log of the logical database includes:

[0022] Parsing the database configuration items and database name in the database creation instruction;

[0023] Traversing the name of each created logical database, if there is a name identical to the database name in the names of the created logical databases, generating a result indicating that the database creation instruction failed to execute, and returning the result indicating that the database creation instruction failed to execute to the user;

[0024] If there is no name identical to the database name among the names of the created logical databases, the logical database is created according to the database configuration item and the database name, and a creation log of the logical database is generated.

[0025] Optionally, the creating the logical database according to the database configuration item and the database name, and generating a creation log of the logical database includes:

[0026] Before creating the logical database, generating a first log with content that the logical database is being created;

[0027] Create the logical database according to the database parameters in the database configuration item;

[0028] After the logical database is created, a second log is generated with the content indicating that the logical database is normally created, and the database name is assigned to the logical database;

[0029] The first log and the second log are used as creation logs of the logical database.

[0030] Optionally, storing the mapping relationship and the creation log in a metadata shard includes:

[0031] Creating a log table and a mapping relationship table in the metadata shard;

[0032] Recording the mapping relationship in the mapping relationship table;

[0033] The creation log is stored in the log table as an operation log of the logical database, and the operation logs in the log table are arranged in the order of log generation.

[0034] Optionally, the method further includes:

[0035] After the logical database is created, a version number is assigned to the logical database, and the version number is recorded in the mapping relationship.

[0036] Optionally, the method further includes:

[0037] When receiving a database deletion instruction from a user, parsing the version number of the database to be deleted specified in the database deletion instruction;

[0038] Searching the version number of the database to be deleted in the mapping relationship table, and if the version number of the database to be deleted is not found, generating a result indicating that the database deletion instruction has failed to execute, and returning the result indicating that the database deletion instruction has failed to execute to the user;

[0039] If the version number of the database to be deleted is found, determining the first shard in the mapping relationship table that is mapped to the database to be deleted;

[0040] Execute the database deletion instruction on the first shard to delete the database to be deleted, and generate a deletion log of the database to be deleted, wherein the deletion log includes a third log indicating that the database to be deleted is being deleted, and a fourth log indicating that the database to be deleted is normally deleted;

[0041] The deletion log is recorded in the log table to update the operation log of the database to be deleted, and the mapping relationship between the database to be deleted and the first shard in the mapping relationship table is deleted.

[0042] Optionally, before executing the database deletion instruction on the first shard, the step further includes:

[0043] A row lock is added to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table to prohibit other programs from accessing the mapping relationship between the database to be deleted and the first shard.

[0044] Optionally, the method further includes:

[0045] When a preset time period ends, a playback operation is performed on the operation logs generated in the preset time period in the log table.

[0046] In a second aspect, the present disclosure provides an agent device for a vertically distributed database, including:

[0047] A receiving module, configured to query a target routing policy corresponding to a database creation instruction upon receiving the database creation instruction from the user;

[0048] A routing module, used for selecting a target shard from the created shards according to the target routing strategy;

[0049] An execution module, configured to execute the database creation instruction on the target shard to create a logical database and generate a creation log of the logical database;

[0050] A processing module, after creating the logical database, generates a mapping relationship between the logical database and the target shard;

[0051] The storage module stores the mapping relationship and the creation log in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating the logical database.

[0052] Optionally, when querying the target routing policy corresponding to the database creation instruction, the receiving module is specifically used to parse the routing indication item in the database creation instruction, wherein the routing indication item is filled in with the routing policy selected by the user from the preset routing policies; and the routing policy filled in the routing indication item is determined as the target routing policy.

[0053] Optionally, the preset routing strategy includes a first routing strategy, a second routing strategy, and a third routing strategy; the first routing strategy is configured to select a shard with the least amount of stored data from the created shards; the second routing strategy is configured to use a hash modulus algorithm to select a shard from the created shards; the third routing strategy is configured to select a shard with a user-specified name from the created shards;

[0054] The routing module is further configured to configure the first routing strategy as a default routing strategy, where the default routing strategy is a routing strategy selected when the routing indication item is empty.

[0055] Optionally, when the execution module executes the database creation instruction on the target shard to create a logical database and generates a creation log of the logical database, it has a function for parsing the database configuration items and the database name in the database creation instruction; traversing the name of each created logical database, and if the name of the created logical database contains a name that is identical to the database name, generating a result of failure to execute the database creation instruction, and returning the result of failure to execute the database creation instruction to the user; if the name of the created logical database contains a name that is identical to the database name, creating the logical database according to the database configuration items and the database name, and generating a creation log for the logical database.

[0056] Optionally, when the execution module creates the logical database according to the database configuration item and the database name and generates a creation log of the logical database, it is specifically used to generate a first log with content indicating that the logical database is being created before the logical database is created; create the logical database according to the database parameters in the database configuration item; after the logical database is created, generate a second log with content indicating that the logical database is created normally, and assign the database name to the logical database; and use the first log and the second log as creation logs of the logical database.

[0057] Optionally, when the storage module stores the mapping relationship and the creation log in the metadata shard, it is specifically used to create a log table and a mapping relationship table in the metadata shard; record the mapping relationship in the mapping relationship table; store the creation log as the operation log of the logical database in the log table, and the operation logs in the log table are arranged in the chronological order of log generation.

[0058] Optionally, the processing module is further configured to assign a version number to the logical database after creating the logical database, and record the version number in the mapping relationship.

[0059] Optionally, the receiving module is further used to parse the version number of the database to be deleted specified in the database deletion instruction when receiving the user's database deletion instruction; the routing module is further used to search the version number of the database to be deleted in the mapping relationship table, and if the version number of the database to be deleted is not found, generate a result of failure to execute the database deletion instruction, and return the result of failure to execute the database deletion instruction to the user; the routing module is further used to determine the first shard mapped to the database to be deleted in the mapping relationship table if the version number of the database to be deleted is found; the execution module is further used to execute the database deletion instruction on the first shard to delete the database to be deleted, and generate a deletion log of the database to be deleted, the deletion log including a third log with content that the database to be deleted is being deleted, and a fourth log with content that the database to be deleted is normally deleted; the storage module is further used to record the deletion log in the log table to update the operation log of the database to be deleted, and delete the mapping relationship between the database to be deleted and the first shard in the mapping relationship table.

[0060] Optionally, before executing the database deletion instruction on the first shard, the execution module is further used to add a row lock to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table to prohibit other programs from accessing the mapping relationship between the database to be deleted and the first shard.

[0061] Optionally, the device further comprises a playback module, which is used to perform a playback operation on the operation logs generated in the preset time period in the log table when a preset time period ends.

[0062] In a third aspect, the present disclosure provides an electronic device, including:

[0063] Memory;

[0064] Processor; and

[0065] Computer programs;

[0066] The computer program is stored in the memory and is configured to be executed by the processor to implement the method as described in any one of the first aspects.

[0067] In a fourth aspect, the present disclosure provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the method as described in any one of the first aspects.

[0068] Compared with the prior art, the technical solution provided by the present invention has the following advantages:

[0069] The proxy method, device, equipment and medium of the vertical partition database provided by the present disclosure, by querying the target routing strategy corresponding to the received database creation instruction, selecting the target shard from the created shards according to the target routing strategy, and then executing the database creation instruction on the target shard to create a logical database, and generating a creation log of the logical database, and after creating the logical database, generating a mapping relationship between the logical database and the target shard as the routing rule of the instruction for operating the logical database, and finally storing the mapping relationship and the creation log in the metadata shard pre-selected from the created shards. Because the mapping method between the logical database and the shard is directly used as the routing rule of the logical database without binding a specific routing rule, the routing rule of the logical database can be flexibly adjusted to avoid problems such as data skew and data migration difficulties caused by the routing rules, and by selecting a shard from the created shards to store metadata, that is, the mapping relationship and the operation log, there is no need to introduce additional metadata services to store and manage metadata, making the storage and management of metadata more convenient. BRIEF DESCRIPTION OF THE DRAWINGS

[0070] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

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

[0072] Figure 1 A flowchart of a proxy method for a vertically distributed database provided by an embodiment of the present disclosure;

[0073] Figure 2 A flowchart of a method for deleting a database provided by an embodiment of the present disclosure;

[0074] Figure 3A schematic diagram of the structure of the proxy device of the vertical sub-database provided in the embodiment of the present disclosure;

[0075] Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present disclosure. DETAILED DESCRIPTION

[0076] In order to more clearly understand the above-mentioned objectives, features and advantages of the present disclosure, the scheme of the present disclosure will be further described below. It should be noted that the embodiments of the present disclosure and the features in the embodiments can be combined with each other without conflict.

[0077] In the following description, many specific details are set forth to facilitate a full understanding of the present disclosure, but the present disclosure may also be implemented in other ways different from those described herein; it is obvious that the embodiments in the specification are only part of the embodiments of the present disclosure, rather than all of the embodiments.

[0078] Databases usually run on servers as independent processes, which are called database instances. Database agents are service processes built on database instances to enhance or give database instances certain functions, such as sharding, read-write separation, and security auditing. Sharding is a database optimization technology that splits a single database or data table so that the original data is scattered across multiple databases or tables. According to the different splitting methods, it is generally divided into horizontal splitting and vertical splitting. Vertical splitting is to divide data into independent instances, libraries, and tables according to business logic, and not to shard and distribute data in the same library or table. Horizontal splitting is to split the data of a single data table into multiple tables and distribute them on different instances according to non-business requirements such as data volume. Sharding refers to a whole block of data being divided into multiple parts after being split, and each part is called a shard.

[0079] Logical database and physical database are two key concepts used in distributed database systems. Logical database is an abstract concept that represents a logically related collection of data. It provides a unified view, hides the details of the underlying physical database, and enables applications to transparently access and operate data. The logical database determines the distribution and access rules of data between physical databases through routing strategies. The physical database is the physical instance or shard that actually stores data. It is a component of the logical database and is responsible for storing and managing data. The physical database can be a database instance on a single database server or a data shard distributed on multiple servers.

[0080] Figure 1The flowchart of a proxy method for a vertical sub-database provided in an embodiment of the present disclosure is a flowchart of a proxy method for a vertical sub-database provided in an embodiment of the present disclosure. The method can be executed by a proxy device for a vertical sub-database, and the proxy device for a vertical sub-database can be configured in a server. The proxy method for a vertical sub-database includes the following steps:

[0081] S101. When receiving a database creation instruction from a user, query a target routing policy corresponding to the database creation instruction.

[0082] When receiving a database creation instruction, the database agent will execute the database creation instruction, that is, execute the database creation statement. The database agent needs to determine on which shard (group) the database to be created by the database creation instruction should be established through the routing strategy. The routing strategy in the embodiment of the present disclosure belongs to a type of routing rule. The routing strategy is used to determine the rule on which database shard to route the database creation instruction for execution when creating a logical database. These database shards have been created in advance, and each shard can be regarded as an independent physical database instance, responsible for storing and processing data fragments.

[0083] In some embodiments, querying the target routing strategy corresponding to the database creation instruction includes: parsing the routing indication item in the database creation instruction, the routing indication item is filled with the routing strategy selected by the user from the preset routing strategies; determining the routing strategy filled in the routing indication item as the target routing strategy.

[0084] When creating a database, you can specify the database routing strategy through Hint. Hint is an instruction or prompt, usually used in SQL statements (Structured Query Language) to guide the database system to take specific actions or strategies when performing a certain operation. The routing indicator item in the database creation instruction is a parameter item that specifies the database routing strategy through Hint, which is used to convey the information of the routing rule, in which the routing strategy selected by the user in the preset routing strategy is filled in. The database agent can obtain the routing strategy filled in the routing indicator item by parsing the routing indicator item in the database creation instruction. This routing strategy is the target routing strategy. Specifically, the database agent parses Hint to obtain the routing rule type, and then obtains the corresponding routing strategy based on the routing rule type. If the routing rule type is empty or not found, the default routing strategy is returned.

[0085] In the embodiment of the present disclosure, the preset routing strategy includes a first routing strategy, a second routing strategy, and a third routing strategy. Among them, the first routing strategy is configured to select a shard with the least amount of stored data from the created shards; the second routing strategy is configured to use a hash modulus algorithm to select a shard from the created shards; and the third routing strategy is configured to select a shard with a user-specified name from the created shards.

[0086] The first routing strategy is the minimum load strategy, which is used to route to the shard with the least number of tables. The second routing strategy is the hash modulo (hash_mod) strategy, which uses a hash modulo algorithm to determine the shard to be routed to. The hash modulo algorithm is a prior art and is not described in detail in the embodiments of the present disclosure. The third routing strategy is to route to a shard with a specified name. By supporting the above three routing strategies, the embodiments of the present disclosure expand more usage scenarios and improve the versatility of the solution. In the embodiments of the present disclosure, the first routing strategy is configured as the default routing strategy, which is the routing strategy selected when the routing indication item is empty.

[0087] S102: Select a target shard from the created shards according to the target routing policy.

[0088] Exemplarily, when the target routing strategy is the first routing strategy, the database proxy determines the shard with the least amount of data stored in the created shards as the target shard. When the target routing strategy is the second routing strategy, the database proxy uses a hash modulo algorithm to determine the target shard. When the target routing strategy is the third routing strategy, the database proxy searches for a shard with a user-specified name from the created shards as the target shard. If the shard does not exist, an error message is returned, prompting the user that the execution of the database creation instruction failed, and indicating that the reason is that the shard with the user-specified name does not exist in the created shards.

[0089] S103. Execute a database creation instruction on the target shard to create a logical database and generate a creation log of the logical database.

[0090] The database agent routes the database creation instruction to the target shard, and then executes the database creation instruction on the target shard to create a logical database. During the process of creating a logical database, the database agent also generates a creation log for the logical database.

[0091] In some embodiments, a database creation instruction is executed on a target shard to create a logical database and generate a creation log of the logical database, including: parsing the database configuration items and database name in the database creation instruction; traversing the name of each created logical database, and if the name of the created logical database contains a name that is identical to the database name, generating a result of a failure to execute the database creation instruction, and returning the result of a failure to execute the database creation instruction to the user; if the name of the created logical database contains a name that is identical to the database name, creating the logical database according to the database configuration items and the database name, and generating a creation log of the logical database.

[0092] Since users may create logical databases with the same name repeatedly, deduplication is required. The database agent parses the database creation instruction to obtain the database configuration items and database name of the logical database to be created. The database configuration items are used to indicate the configuration parameters of the logical database to be created, such as the database's memory allocation, storage path, number of concurrent connections, logging level, etc. The database name is the name of the logical database to be created.

[0093] The database proxy can exclude repeated requests to create a database with the same name through unique constraints. Specifically, the database proxy traverses the names of the created logical databases to determine whether the name of the logical database to be created already exists. If so, an error message is returned to prompt the user that the database creation instruction failed to execute and that the reason is that a logical database with the same name already exists. If not, the logical database is created on the target shard according to the database configuration items and database name, and a creation log of the logical database is generated.

[0094] In an embodiment of the present disclosure, a logical database is created according to a database configuration item and a database name, and a creation log of the logical database is generated, including: before creating the logical database, generating a first log with content indicating that the logical database is being created; creating the logical database according to the database parameters in the database configuration item; after the logical database is created, generating a second log with content indicating that the logical database is normally created, and assigning a database name to the logical database; and using the first log and the second log as creation logs of the logical database.

[0095] Since there may be a scenario where users repeatedly create and delete databases, it is necessary to ensure the order and correspondence of (create, delete) operations. Therefore, the disclosed embodiment implements a message queue based on a log table, and uses a self-incrementing sequence as the message sequence number. Specifically, to ensure that the log sequence number increases in the order of operations, when the operation status changes, the database agent inserts a log corresponding to the changed status. Taking the creation of a logical database as an example, before the creation of the logical database begins, a [Creating] log is generated, that is, the first log that the content is that the logical database is being created; after the creation of the logical database is completed, a [Normal] log is inserted, that is, the second log that the content is that the normal creation of the logical database is completed.

[0096] S104: After the logical database is created, a mapping relationship between the logical database and the target shard is generated.

[0097] After the database agent creates a logical database on the target shard, it will generate a mapping relationship between the logical database and the target shard. The mapping relationship of (database, shard) adopts a one-to-one mapping method, specifically, one database is mapped to only one shard, and one shard can map multiple databases. In this way, no matter what kind of routing rules are used, the mapping relationship between a database and a shard will be directly stored in the end, and will not be bound to a specific routing rule, which can be flexibly adjusted. That is, when operating the logical database later, the mapping relationship between the logical database and the target shard is used as the routing rule. Through this mapping relationship, the database agent can know that the operation instructions on the logical database need to be routed to the target shard for execution.

[0098] In some embodiments, the method further includes: after creating the logical database, assigning a version number to the logical database, and recording the version number in the mapping relationship.

[0099] In some scenarios, for example, after the database agent deletes a logical database, the database agent crashes, resulting in no log recording the completion of the logical database deletion, but only the log recording that the logical database is being deleted. If a user then creates a database with the same name as the deleted logical database through another database agent, during subsequent log playback, the database agent will replay the log recording that the logical database is being deleted and re-execute the operation of deleting the logical database, which will cause the database created above with the same name as the deleted logical database to be deleted.

[0100] To avoid this situation, after creating a logical database, the database agent will assign a unique version number to the logical database and record the changed version number in the mapping relationship. When deleting the logical database later, the database agent will only execute the database deletion operation when it determines that the version number information contained in the database deletion instruction is the same as the version number of the database to be deleted.

[0101] S105: Store the mapping relationship and the creation log in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating a logical database.

[0102] In the embodiment of the present disclosure, a shard is selected from the created shards as a metadata shard, and the shard can be specified through the configuration item of the database agent program. The database agent stores the mapping relationship and the creation log on the metadata shard. Exemplarily, the mapping relationship and the log can be stored in the metadata shard in the form of a data table. By selecting a shard from the created shards to store metadata, there is no need to introduce additional metadata services.

[0103] In one embodiment, the mapping relationship and the creation log are stored in the metadata shard, including: creating a log table and a mapping relationship table in the metadata shard; recording the mapping relationship in the mapping relationship table; storing the creation log as an operation log of the logical database in the log table, and the operation logs in the log table are arranged in the order of log generation.

[0104] The database agent creates a log table (db_mapping_log) and a mapping relationship table (db_mapping) in the metadata shard in advance. The database agent will record an operation log in the log table every time the database status changes. In this way, when some database agents crash and interrupt database operations, these interrupted database operations can be compensated through background scheduled tasks. The log table can be regarded as a message queue for recording operation logs. The order of logs can be maintained by means of auto-increment IDs. The auto-increment IDs of the logs in the log table are determined by the generation time of the logs.

[0105] It is understandable that the creation log of a logical database belongs to the operation log of the logical database. The database agent records the creation log of the logical database in the log table and assigns a self-incrementing ID to the creation log. The self-incrementing ID is to set the value of the ID field to self-increment when designing the table, so that when inserting a row of data, it is automatically filled according to the ID value of the previous field + 1 without specifying the id. For example, when logical database A is created, the ID assigned to the [Creating] log of logical database A is 001. Logical database B is created immediately after logical database A, and the ID of the [Creating] log of logical database B is 002. After that, logical database A is created first, and the ID of the [Normal] log of logical database A is 003. After the creation of logical database B is completed, its [Normal] log ID is 004.

[0106] The mapping relationship table is used to store all mapping relationships. The database proxy will load the mapping relationships in the table. When routing database operation instructions, the shard to which the database operation instructions are to be routed is determined based on the mapping relationships.

[0107] To ensure database performance, the created mapping relationships are cached in the database proxy. When the database system is started, the database proxy fully loads the mapping relationships of each database in the database system. When the mapping relationships of some databases change, the mapping relationships of the changed databases are incrementally loaded through scheduled tasks, reducing the frequency of loading mapping relationships and the amount of mapping relationship data that needs to be read.

[0108] The disclosed embodiment queries the target routing policy corresponding to the received database creation instruction, selects the target shard from the created shards according to the target routing policy, and then executes the database creation instruction on the target shard to create a logical database, and generates a creation log of the logical database. After the logical database is created, a mapping relationship between the logical database and the target shard is generated as a routing rule for the instruction to operate the logical database, and finally the mapping relationship and the creation log are stored in a metadata shard pre-selected from the created shards. Because the mapping method between the logical database and the shard is directly used as the routing rule of the logical database without binding a specific routing rule, the routing rule of the logical database can be flexibly adjusted to avoid problems such as data skew and data migration difficulties caused by the routing rules, and by selecting a shard from the created shards to store metadata, that is, the mapping relationship and the operation log, there is no need to introduce additional metadata services to store and manage metadata, making the storage and management of metadata more convenient.

[0109] Figure 2 A flowchart of a method for deleting a database provided in an embodiment of the present disclosure is provided. Figure 2The method flow shown is the process part of deleting the database in the proxy method of the vertical database. Figure 2 , the proxy method of the vertical sub-database also includes:

[0110] S201. When receiving a database deletion instruction from a user, parse the version number of the database to be deleted specified in the database deletion instruction.

[0111] When receiving a database deletion instruction from a user, the database agent will parse the database deletion instruction and obtain information about the database to be deleted, including a unique version number of the database to be deleted. The unique version number is generated when the database to be deleted is created, and serves as a unique identifier of the database to be deleted. The database version number has been introduced in the above embodiment and will not be repeated here.

[0112] S202: Search the mapping table for the version number of the database to be deleted.

[0113] S203: If the version number of the database to be deleted is not found, a result indicating that the database deletion instruction has failed to execute is generated, and the result indicating that the database deletion instruction has failed to execute is returned to the user.

[0114] In the above embodiment, it has been introduced that the database proxy will assign a version number to the logical database after creating it, and record the version number in the mapping relationship between the logical database and the shard where it is located. After parsing the database deletion instruction, the database proxy will search for the version number of the database to be deleted in the mapping relationship table of the metadata shard. If the version number of the database to be deleted is not found in the mapping relationship table, it means that the database to be deleted has been deleted or not created. Therefore, the result of the failure of the execution of the database deletion instruction is returned to the user, and the reason for the execution failure is that the version number of the database to be deleted does not exist.

[0115] S204: If the version number of the database to be deleted is found, determine the first shard in the mapping relationship table that is mapped to the database to be deleted.

[0116] When the version number of the database to be deleted is found in the mapping relationship table, the mapping relationship of the database to be deleted is obtained from the row in the mapping relationship table where the version number is located. The mapping relationship is also the routing rule of the database to be deleted. The first shard mapped to the database to be deleted can be determined from the mapping relationship of the database to be deleted. The first shard is the shard where the database to be deleted is located, that is, the destination of the database deletion instruction routing.

[0117] S205. Execute a database deletion instruction on the first shard to delete the database to be deleted, and generate a deletion log of the database to be deleted, wherein the deletion log includes a third log indicating that the database to be deleted is being deleted, and a fourth log indicating that the database to be deleted is normally deleted.

[0118] The database proxy routes the database deletion instruction to the first shard, and then executes the database deletion instruction on the first shard, thereby deleting the database to be deleted. Before deleting the database, a [deleting] log is generated, that is, the third log that the database to be deleted is being deleted. After deleting the database, a [deleted] log is generated, that is, the fourth log that the database to be deleted is deleted normally.

[0119] In some embodiments, before executing the database deletion instruction on the first shard, the method further includes: adding a row lock to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table to prohibit other programs from accessing the mapping relationship between the database to be deleted and the first shard.

[0120] In order to avoid conflicts between synchronous and asynchronous operations, for example, executing a database deletion instruction is a synchronous operation, and replaying the [deleting] log is an asynchronous operation. Therefore, the database agent will add a row lock to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table before executing the database deletion instruction. The row lock is used to achieve mutual exclusion of operations on databases with the same name. Row lock is a lock mechanism in the database that is used to control concurrent access to row-level data in the database. When multiple transactions access different rows of the same table at the same time, row lock can ensure data consistency and integrity. The role of row lock is to restrict other transactions from modifying and reading locked rows to avoid data conflicts and inconsistent results. When a transaction obtains a row lock for a row, other transactions must wait for the row lock to be released when they need to modify or read the row.

[0121] S205: Record the deletion log in the log table to update the operation log of the database to be deleted, and delete the mapping relationship between the database to be deleted and the first shard in the mapping relationship table.

[0122] After deleting the database to be deleted, the database proxy will record the deletion log in the log table to complete the update of the database operation log. At the same time, it will release the row lock imposed on the mapping relationship between the database to be deleted and the first shard, and delete the mapping relationship between the database to be deleted and the first shard in the mapping relationship table.

[0123] In addition, the proxy method of the vertically distributed database also includes solutions for database migration and database expansion and contraction. Among them, the database migration operation requires the cooperation of the database platform, and the database proxy only changes the status of the migrated database. Specifically, two status changes are made before and after the migration: for the database that needs to be migrated, before the migration, the [migrating] log of the database is generated and recorded in the log table, and the mapping relationship status of the database is updated to [offline]; after the migration, the [migrated] log of the database is generated and recorded in the log table, and the mapping relationship before the update is deleted.

[0124] The data migration process uses the Database Transfer Service (DTS) to migrate data on a database-by-database basis. The general process is as follows: 1. Before migration, mark the database to be migrated as [Offline]; 2. Check each database agent to confirm that the status change log is applied; 3. DTS performs the operation of migrating the database; 4. After migration, overwrite and update the mapping relationship of the migrated database; 5. Check each database agent to confirm that the status change log is applied; 6. Execute the operation of deleting the database on the old database instance to delete the database with the same name on the old instance.

[0125] The expansion process is as follows: 1. Create a new database instance; 2. Add the new database instance to the database agent configuration file; 3. Connect to the database agent to perform a configuration refresh operation to refresh the configuration of the new database instance; 4. Use DTS to migrate and catch up with the data of the specified instance.

[0126] The scaling-down process is as follows: 1. Use DTS to migrate the data of the scaled-down instance; 2. Update the configuration file of the database agent and remove the scaled-down instance; 3. Connect to the database agent to perform a configuration refresh operation to refresh the configuration of the newly created database instance.

[0127] In some embodiments, the method further includes: when a preset time period ends, performing a playback operation on the operation logs generated in the log table within the preset time period.

[0128] The database system has a preset time period. After each time period, the database system background will refresh the metadata scheduled task, which includes the playback operation of the newly added operation logs during the time period. For example, the time period can be set to one hour.

[0129] Specifically, the database agent completes the following tasks through the metadata timing task refreshed in the background: replay incremental logs: replay the logs newly added in the log table within the preset time period, and process the operations of each state; count the number of databases: count the number of databases under each shard and load them into memory; clean up expired logs: clean up expired logs in the log table. The expiration time can be specified by the cleanup cycle parameter. For example, the cleanup cycle can be set to one hour, that is, the operation of cleaning up expired logs is performed once an hour.

[0130] Among them, when replaying the incremental logs, the incremental logs generated in the cycle in the log table will be replayed one by one in the order of the operation logs. The processing of each state log includes: for the [Creating] log, the operation indicated by the database creation instruction will be re-executed, that is, the logical database indicated by the database creation instruction will be created, and then the [Creating] log will be cleaned up, and the corresponding [Normal] log will be generated and recorded. For the [Normal] log, the newly generated mapping relationship after the logical database is created is added to the mapping relationship table. For the [Deleting] log, the operation indicated by the database deletion instruction will be re-executed, and then the [Deleting] log will be cleaned up, and the corresponding [Deleted] log will be recorded. For the [Deleted] log, the mapping relationship of the database to be deleted corresponding to the log will be deleted from the mapping relationship table. For the [Migrating] log, the mapping relationship of the database to be migrated is obtained from the mapping relationship table, and the status of the mapping relationship is set to the [Offline] status.

[0131] The disclosed embodiment performs an asynchronous operation of replaying the log, thereby avoiding the problem of affecting database consistency caused by the incomplete execution of a database proxy operation interrupted due to a computer crash.

[0132] Figure 3 The schematic diagram of the structure of the proxy device of the vertical sub-library database provided in the embodiment of the present disclosure. The proxy device of the vertical sub-library database can be a component or assembly in the terminal of the above embodiment. The proxy device of the vertical sub-library database provided in the embodiment of the present disclosure can execute the processing flow provided in the proxy method embodiment of the vertical sub-library database, such as Figure 3 As shown, the proxy device 300 of the vertical sub-database includes:

[0133] The receiving module 301 is used to query the target routing policy corresponding to the database creation instruction when receiving the database creation instruction from the user;

[0134] A routing module 302, configured to select a target shard from the created shards according to a target routing policy;

[0135] An execution module 303 is used to execute a database creation instruction on a target shard to create a logical database and generate a creation log of the logical database;

[0136] Processing module 304, after creating the logical database, generates a mapping relationship between the logical database and the target shard;

[0137] The storage module 305 stores the mapping relationship and the creation log in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating a logical database.

[0138] In some embodiments, when querying the target routing policy corresponding to the database creation instruction, the receiving module 301 is specifically used to parse the routing indication item in the database creation instruction, in which the routing indication item is filled with the routing policy selected by the user from the preset routing policies; and the routing policy filled in the routing indication item is determined as the target routing policy.

[0139] In some embodiments, the preset routing strategy includes a first routing strategy, a second routing strategy, and a third routing strategy; the first routing strategy is configured to select a shard with the least amount of stored data from the created shards; the second routing strategy is configured to select a shard from the created shards using a hash modulo algorithm; the third routing strategy is configured to select a shard with a user-specified name from the created shards;

[0140] The routing module 302 is further configured to configure the first routing strategy as a default routing strategy, where the default routing strategy is a routing strategy selected when the routing indication item is empty.

[0141] In some embodiments, when the execution module 303 executes a database creation instruction on the target shard to create a logical database and generates a creation log of the logical database, it has a function for parsing the database configuration items and database name in the database creation instruction; traversing the name of each created logical database, if the name of the created logical database contains a name that is identical to the database name, generating a result of failure to execute the database creation instruction, and returning the result of failure to execute the database creation instruction to the user; if the name of the created logical database does not contain a name that is identical to the database name, creating the logical database according to the database configuration items and the database name, and generating a creation log for the logical database.

[0142] In some embodiments, when the execution module 303 creates a logical database according to the database configuration items and the database name and generates a creation log of the logical database, it is specifically used to generate a first log with the content of the logical database being created before the logical database is created; create the logical database according to the database parameters in the database configuration items; after the logical database is created, generate a second log with the content of the normal creation of the logical database and assign a database name to the logical database; and use the first log and the second log as the creation logs of the logical database.

[0143] In some embodiments, when the storage module 305 stores the mapping relationship and the creation log in the metadata shard, it is specifically used to create a log table and a mapping relationship table in the metadata shard; record the mapping relationship in the mapping relationship table; store the creation log as the operation log of the logical database in the log table, and the operation logs in the log table are arranged in the chronological order of log generation.

[0144] In some embodiments, the processing module 304 is further configured to assign a version number to the logical database after the logical database is created, and record the version number in the mapping relationship.

[0145] In some embodiments, the receiving module 301 is also used to parse the version number of the database to be deleted specified in the database deletion instruction when receiving the user's database deletion instruction; the routing module 302 is also used to search the version number of the database to be deleted in the mapping relationship table, and if the version number of the database to be deleted is not found, a result of failure to execute the database deletion instruction is generated, and the result of failure to execute the database deletion instruction is returned to the user; the routing module 302 is also used to determine the first shard mapped to the database to be deleted in the mapping relationship table if the version number of the database to be deleted is found; the execution module 303 is also used to execute the database deletion instruction on the first shard to delete the database to be deleted, and generate a deletion log of the database to be deleted, the deletion log including a third log with the content of the database to be deleted being deleted, and a fourth log with the content of the normal deletion of the database to be deleted; the storage module 305 is also used to record the deletion log in the log table to update the operation log of the database to be deleted, and delete the mapping relationship between the database to be deleted and the first shard in the mapping relationship table.

[0146] In some embodiments, before executing the database deletion instruction on the first shard, the execution module 303 is also used to add a row lock to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table to prohibit other programs from accessing the mapping relationship between the database to be deleted and the first shard.

[0147] In some embodiments, the device further includes a playback module 306 for performing a playback operation on the operation logs generated in the preset time period in the log table when a preset time period ends.

[0148] Figure 3 The proxy device of the vertical sub-library database in the illustrated embodiment can be used to execute the technical solution of the above-mentioned method embodiment. Its implementation principle and technical effect are similar and will not be repeated here.

[0149] Figure 4The electronic device provided in the embodiment of the present disclosure can execute the processing flow provided in the proxy method embodiment of the vertical sub-library database, such as Figure 4 As shown, the electronic device 400 includes: a memory 401, a processor 402, a computer program and a communication interface 403; wherein the computer program is stored in the memory 401 and is configured to be executed by the processor 402 to execute the proxy method of the vertical sub-library database as described above. In a specific embodiment, the memory 401 can be a non-volatile solid-state memory. In a specific embodiment, the memory 401 includes a read-only memory ROM (Read-Only Memory). In appropriate cases, the ROM can be a mask-programmed ROM, a programmable ROM (Programmable ROM, PROM), an erasable PROM (Electrical Programmable ROM, EPROM), an electrically erasable PROM (Electrically Erasable Programmable ROM, EEPROM), an electrically alterable ROM (Electrically Alterable ROM, EAROM) or a flash memory, or a combination of two or more of these.

[0150] In addition, the embodiment of the present disclosure also provides a computer-readable storage medium on which a computer program is stored, and the computer program is executed by a processor to implement the proxy method of the vertical sub-library database described in the above embodiment. The storage medium can be a non-volatile / non-temporary computer-readable storage medium, for example, the non-volatile / non-temporary computer-readable storage medium can be a ROM, a random access memory (Random Access Memory, RAM), a compact disc read-only memory (Compact Disc ROM, CD-ROM), a magnetic tape, a floppy disk, and an optical data storage device, etc.

[0151] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0152] The above description is only a specific embodiment of the present disclosure, so that those skilled in the art can understand or implement the present disclosure. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure will not be limited to the embodiments described herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A proxy method for a vertically distributed database, characterized in that: include: Upon receiving a database creation instruction from a user, querying a target routing policy corresponding to the database creation instruction; Select a target shard from the created shards according to the target routing strategy; Executing the database creation instruction on the target shard to create a logical database and generating a creation log of the logical database; After creating the logical database, generating a mapping relationship between the logical database and the target shard; The mapping relationship and the creation log are stored in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating the logical database.

2. The method according to claim 1, characterized in that The querying of the target routing strategy corresponding to the database creation instruction includes: Parsing the routing indication item in the database creation instruction, wherein the routing indication item is filled with the routing strategy selected by the user from the preset routing strategies; The routing policy filled in the routing indication item is determined as the target routing policy.

3. The method according to claim 2, characterized in that The preset routing strategy includes a first routing strategy, a second routing strategy, and a third routing strategy; The first routing strategy is configured to select a shard with the least amount of stored data from the created shards; The second routing strategy is configured to select one of the created shards using a hash modulo algorithm; The third routing strategy is configured to select a slice with a user-specified name from the created slices; The method further comprises: The first routing policy is configured as a default routing policy, where the default routing policy is a routing policy selected when the routing indication item is empty.

4. The method according to claim 1, characterized in that The executing the database creation instruction on the target shard to create a logical database and generating a creation log of the logical database includes: Parsing the database configuration items and database name in the database creation instruction; Traversing the name of each created logical database, if there is a name identical to the database name in the names of the created logical databases, generating a result indicating that the database creation instruction failed to execute, and returning the result indicating that the database creation instruction failed to execute to the user; If there is no name identical to the database name among the names of the created logical databases, the logical database is created according to the database configuration item and the database name, and a creation log of the logical database is generated.

5. The method according to claim 4, characterized in that The step of creating the logical database according to the database configuration item and the database name, and generating a creation log of the logical database, includes: Before creating the logical database, generating a first log with content that the logical database is being created; Create the logical database according to the database parameters in the database configuration item; After the logical database is created, a second log is generated with the content indicating that the logical database is normally created, and the database name is assigned to the logical database; The first log and the second log are used as creation logs of the logical database.

6. The method according to claim 5, characterized in that The storing the mapping relationship and the creation log in the metadata slice includes: Creating a log table and a mapping relationship table in the metadata shard; Recording the mapping relationship in the mapping relationship table; The creation log is stored in the log table as an operation log of the logical database, and the operation logs in the log table are arranged in the order of log generation.

7. The method according to claim 6, characterized in that The method further comprises: After the logical database is created, a version number is assigned to the logical database, and the version number is recorded in the mapping relationship.

8. The method according to claim 7, characterized in that The method further comprises: When receiving a database deletion instruction from a user, parsing the version number of the database to be deleted specified in the database deletion instruction; Searching the version number of the database to be deleted in the mapping relationship table, and if the version number of the database to be deleted is not found, generating a result indicating that the database deletion instruction has failed to execute, and returning the result indicating that the database deletion instruction has failed to execute to the user; If the version number of the database to be deleted is found, determining the first shard in the mapping relationship table that is mapped to the database to be deleted; Execute the database deletion instruction on the first shard to delete the database to be deleted, and generate a deletion log of the database to be deleted, wherein the deletion log includes a third log indicating that the database to be deleted is being deleted, and a fourth log indicating that the database to be deleted is normally deleted; The deletion log is recorded in the log table to update the operation log of the database to be deleted, and the mapping relationship between the database to be deleted and the first shard in the mapping relationship table is deleted.

9. The method according to claim 8, characterized in that Before executing the database deletion instruction on the first shard, the method further includes: A row lock is added to the mapping relationship between the database to be deleted and the first shard in the mapping relationship table to prohibit other programs from accessing the mapping relationship between the database to be deleted and the first shard.

10. The method according to claim 6 or 8, characterized in that: The method further comprises: When a preset time period ends, a playback operation is performed on the operation logs generated in the preset time period in the log table.

11. A proxy device for a vertically distributed database, characterized in that: include: A receiving module, configured to query a target routing policy corresponding to a database creation instruction upon receiving the database creation instruction from the user; A routing module, used for selecting a target shard from the created shards according to the target routing strategy; An execution module, configured to execute the database creation instruction on the target shard to create a logical database and generate a creation log of the logical database; A processing module, after creating the logical database, generates a mapping relationship between the logical database and the target shard; The storage module stores the mapping relationship and the creation log in a metadata shard, where the metadata shard is one of the created shards, and the mapping relationship is a routing rule for instructions for operating the logical database.

12. An electronic device, characterized in that: include: Memory; processor; as well as Computer programs; The computer program is stored in the memory and is configured to be executed by the processor to implement the method according to any one of claims 1 to 10.

13. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 10 is implemented.

Images