A data processing method and device of a database, an electronic device, and a medium

Abstract

A data processing method and device of a database, an electronic device, and a medium are disclosed. The method includes: traversing database shards corresponding to a master database, obtaining an expiration processing strategy corresponding to each database shard in the traversal, the expiration processing strategy corresponding to the database shard including an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met; when the current time matches the expiration processing time corresponding to a target database shard and the specified execution operation corresponding to the target database shard indicates a deletion process, constructing a deletion instruction using a data definition language, and deleting the target database shard based on the deletion instruction; and when the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a transfer process, determining a target transfer object and transferring the target database shard to the target transfer object. The synchronization effect between the master and slave databases can be optimized, and the master-slave delay can be reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of Internet communication, and in particular to a database data processing method and device, electronic equipment and medium. BACKGROUND

[0002] The master-slave structure of a database (Master / Slave) usually corresponds to the form of one master (database) and one slave (database), or one master (database) and multiple slaves (databases). The master database maintains data changes (such as responding to write data operations), and one or more slave databases provide data query services to the outside (such as responding to read data operations), thereby realizing the effect of database read-write separation and reducing the pressure on the master data.

[0003] In related technologies, the synchronization between the master and slave data is realized through the original binlog (database binary log) replication mode. When there is a data cleaning requirement, ordinary delete (delete) is usually used for data cleaning, which is not only slow, but also greatly likely to cause master-slave delay, thereby causing the slave database to be unable to provide accurate read function and the overall effect of read-write separation to be unable to be realized. Therefore, a scheme for effectively reducing master-slave delay is needed. SUMMARY

[0004] In order to solve the problems of slow speed and easy to cause master-slave delay when the prior art is applied to clean data, the present application provides a database data processing method and device, electronic equipment and medium:

[0005] According to a first aspect of the present application, a database data processing method is provided, applied to a database with a master-slave structure, and the method comprises:

[0006] Iterating through database shards corresponding to the master database, obtaining an expiration processing strategy corresponding to each database shard in the iteration, the expiration processing strategy corresponding to the database shard comprising an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met;

[0007] When the current time meets the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a deletion processing, a delete instruction is constructed using a data definition language, and the target database shard is deleted based on the delete instruction;

[0008] When the current time meets the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a conversion processing, a target conversion object is determined and the target database shard is converted into the target conversion object.

[0009] According to a second aspect of the present application, a data processing apparatus of a database is provided, applied to a database having a master-slave structure, the apparatus comprising:

[0010] The traversal module is configured to traverse the database shards corresponding to the master database, and obtain the expiration processing strategy corresponding to each of the database shards in the traversal, the expiration processing strategy corresponding to each of the database shards comprising an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met;

[0011] The deletion module is configured to, when the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a deletion processing, construct a deletion instruction by using a data definition language, and delete the target database shard based on the deletion instruction;

[0012] The archiving module is configured to, when the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates an archiving processing, determine a target archiving object and archive the target database shard into the target archiving object.

[0013] According to a third aspect of the present application, an electronic device is provided, comprising a processor and a memory, the memory storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the data processing method of the database according to the first aspect.

[0014] According to a fourth aspect of the present application, a computer-readable storage medium is provided, the storage medium storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by a processor to implement the data processing method of the database according to the first aspect.

[0015] According to a fifth aspect of the present application, a computer program product or a computer program is provided, the computer program product or the computer program comprising computer instructions stored in a computer-readable storage medium. The processor of a computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions to enable the computer device to perform the data processing method of the database according to the first aspect.

[0016] The data processing method, apparatus, electronic device and medium of the database provided by the present application have the following technical effects:

[0017] The application manages data in the master database in a sharding manner, and deletes or stores by rotation according to a preset strategy when reaching the expiration processing time corresponding to the database shard. When deleting, DDL (data definition language) is used, which can reduce the master-slave delay and the occurrence of performance jitter. The storage by rotation can ensure that the master database can more effectively store hot data (relative to cold data, data with more access frequency). BRIEF DESCRIPTION OF DRAWINGS

[0018] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, and the advantages thereof, the drawings needed to be used in the embodiments or the prior art description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can be obtained based on these drawings without creative labor.

[0019] Figure 1 is a schematic diagram of an application environment provided by an embodiment of the present application;

[0020] Figure 2 is a flowchart of a database data processing method provided by an embodiment of the present application;

[0021] Figure 3 is a flowchart of configuring an expiration processing strategy corresponding to a database shard provided by an embodiment of the present application;

[0022] Figure 4 is also a flowchart of configuring an expiration processing strategy corresponding to a database shard provided by an embodiment of the present application;

[0023] Figures 5-8 is a UI diagram of creating a partitioned table provided by an embodiment of the present application;

[0024] Figure 9 is a schematic diagram of compression processing of a target database shard provided by an embodiment of the present application;

[0025] Figure 10 is a block diagram of a database data processing apparatus provided by an embodiment of the present application;

[0026] Figure 11 is a structural schematic diagram of an electronic device provided by an embodiment of the present application. DETAILED DESCRIPTION

[0027] With reference to the drawings, the technical solutions in the embodiments of the present application will be clearly and completely described in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by a person of ordinary skill in the art without creative work fall within the protection scope of the present application.

[0028] It should be noted that the terms "comprising" and "having" and any variations thereof in the specification and claims of the present application and the above drawings are intended to cover the non-exclusive inclusion, for example, a process, method, system, product or server comprising a series of steps or units does not have to be limited to the clearly listed steps or units, but can include other steps or units that are not clearly listed or inherent to the process, method, product or device.

[0029] Before the embodiments of the present application are further described in detail, the terms and phrases involved in the embodiments of the present application are explained, and the terms and phrases involved in the embodiments of the present application are applicable to the following explanations.

[0030] Database: It can be regarded as an electronic file cabinet, which is a place for storing electronic files, and users can perform operations such as adding, querying, updating and deleting data in the files. The so-called "database" is a collection of data stored together in a certain way, shared by multiple users, with as little redundancy as possible, and independent of application programs.

[0031] MySQL: An open source relational database management system. MySQL has become the most popular open source database due to its high performance, low cost, good reliability and other advantages in the past, and is widely used in small and medium-sized websites on the Internet. With the continuous maturity of MySQL, it is also gradually used in more large-scale websites and applications, such as Wikipedia, Google and Facebook websites. The "M" in the very popular open source software combination LAMP (which refers to a group of free software name initials usually used together to run dynamic websites or servers) refers to MySQL.

[0032] binlog: Database binary log, which can contain DML (Data Manipulation Language) log of the database.

[0033] replication: The data is transmitted from one instance to another instance through the binlog transmission mode, such as transmitting the data from the master data to the slave database.

[0034] Private Cloud: It is to create cloud infrastructure and hardware and software resources within the firewall for the departments of the institution or enterprise to share the resources in the data center. In addition to hardware resources, cloud devices (IaaS, Infrastructure as a Service) software are generally created in private clouds. Private cloud computing also contains three levels of cloud hardware, cloud platform, and cloud services. The difference is that cloud hardware is the user's own personal computer or server, not the data center of the cloud computing manufacturer. The purpose of the cloud computing manufacturer to build the data center is to provide public cloud services for millions of users, so it needs to have tens of millions of servers. Private cloud computing, for individuals, only serves friends and family, and for enterprises, only serves employees, customers and suppliers of the enterprise, so the personal computer or server of the individual or enterprise is enough to provide cloud services.

[0035] Please refer to Figure 1 , Figure 1 is a schematic diagram of an application environment provided by an embodiment of the application, which can include a master database and a slave database. In combination with Figure 1 , the following is the principle of master-slave replication: data change events (such as update (modification operation event), insert (insert operation event), and delete (delete operation event)) of the master database are written into a binary log (binlog). The slave database initiates a connection and connects to the master database. At this time, the master database creates a log backup thread (binlog dump thread) to send the contents of the binary log to the slave database. After the slave database is started, an input / output thread (I / 0 thread) can be created to read the log contents sent by the master database and write them into a relay log. The slave database can also create a query thread (SQL thread) to read the contents from the relay log, start executing the read change events from the Exec_Master_Log_Pos position, and write the change contents to the specified data storage area of the slave database. It should be noted that Figure 1 is only an example.

[0036] The master database can be regarded as a master, which includes a master database body and a server (referred to as a master server) in which the master database body is deployed. The slave database can be regarded as a slave, which includes a slave database body and a server in which the slave database body is deployed. The data in the master database can be managed based on the master server and a corresponding master client. The master client can be directly or indirectly connected with the master server through wired or wireless communication. The master server provides background services for the master client. A first master client can be used to generate a data change request. The first master client sends the data change request to the master server. The master server updates a corresponding data object in the master database in response to the data change request. A second master client can be used to generate a database shard creation request and a policy configuration request. The second master client sends the database shard creation request and the policy configuration request to the master server. The master server creates a database shard in the master database in response to the database shard creation request. The master server configures an expiration processing policy for the corresponding database shard in response to the policy configuration request. The master server traverses the database shards corresponding to the master database. For the database shard that reaches the expiration processing time, the database shard is deleted or migrated according to the preset policy. The first master client and the second master client can be the same client or two different clients.

[0037] The client can include an entity device such as a smart phone, a desktop computer, a tablet computer, a notebook computer, an augmented reality (AR) / virtual reality (VR) device, a digital assistant, a smart speaker, a smart wearable device, etc. The client can also include a software such as a computer program running in the entity device. The operating system corresponding to the client can include an Android system, an IOS system, a Linux, a Microsoft Windows, etc.

[0038] The server can be a standalone physical server, a server cluster or a distributed system composed of multiple physical servers, a cloud server providing cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and basic cloud computing services such as big data and artificial intelligence platforms. The server can include a network communication unit, a processor, a memory, etc.

[0039] In practical applications, MySQL is one of the main products of databases in today's cloud computing, and is sold on cloud service providers such as Ali Cloud, Tencent Cloud, and Huawei Cloud. It has important applications in today's Internet of Things, enterprise office, mobile application, and other industries. On the cloud service platform, ensuring high availability of services is a basic requirement and a basic guarantee for users. The current popular MySQL private cloud system architecture utilizes high-availability clusters, which can be seen in Figure 1 .

[0040] The following describes a specific embodiment of a database data processing method of the application, Figure 2 is a flowchart of a database data processing method provided by an embodiment of the application. The present specification provides method operation steps as described in the embodiments or flowcharts, but more or fewer operation steps can be included based on conventional or non-creative labor. The order of steps listed in the embodiments is only one of the many step execution orders, and does not represent the only execution order. In actual system or server product execution, the method order shown in the embodiments or the drawings can be executed in sequence or in parallel (for example, in a parallel processor or multi-threaded processing environment). Specifically, as shown in Figure 2 , the method can include:

[0041] S201: Traverse the database shards corresponding to the master database, and obtain the expiration processing strategy corresponding to each database shard in the traversal. The expiration processing strategy corresponding to the database shard includes an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met.

[0042] In an embodiment of the application, the database with a master-slave structure includes a master database and a slave database. The database shards corresponding to the master database can be traversed by the master server corresponding to the master database, and the expiration processing strategy corresponding to each database shard is obtained in the traversal. The identifier of the database shard corresponding to the master database can also be traversed by the master server or the master client corresponding to the master server, and the expiration processing strategy corresponding to each database shard is obtained in the traversal. The database shard here can be a data subtable or data partition created in advance based on the data in the master database. The data partition can indicate a part of a data subtable.

[0043] The trigger of the traversal can be based on a timer, which can be built based on a Java-based scheduler module. It should be noted that the traversal can be used to traverse the database shards corresponding to the main database (in the traversal, the expiration processing strategy corresponding to each database shard is obtained to determine whether it is expired, and whether it needs to be deleted or stored), and can also be used to traverse whether there is a reference table corresponding to the current time. If it exists, the pre-creation of the table partition can be performed based on the reference table as needed.

[0044] In actual application, as the storage capacity of a single machine rises, there is a need for data cleaning. The expiration processing strategy of the database shard includes an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met. The configuration of the expiration processing strategy realizes the automatic time-based elimination management of the database shard. The expiration processing strategy can be configured in advance for the database shard, so that the specified execution operation is implemented once the trigger condition is met. The automatic time-based elimination management of the database shard not only considers that data cannot be saved for a long time due to storage capacity (such as the weak horizontal expansion capability of MySQL itself), but also considers that the cold and hot separation of data cannot be achieved, which leads to a low hit rate of query service and cold data pollution of cache. The cold and hot separation of data can store cold data on a cheaper storage medium, avoiding the high cost caused by the storage of cold data and hot data in the same storage medium.

[0045] In one embodiment, as shown in Figure 3 the method further includes configuring the expiration processing strategy corresponding to the database shard:

[0046] S301: receiving a strategy configuration request, the strategy configuration request carrying a shard retention time;

[0047] S302: determining the expiration processing time based on the shard retention time and the receiving time;

[0048] S303: when the strategy configuration request does not carry a storage object identifier, generating a first type of specified execution operation indicating a deletion processing based on the expiration processing time, and constructing the expiration processing strategy corresponding to the database shard based on the expiration processing time and the first type of specified execution operation;

[0049] S304: when the strategy configuration request carries the storage object identifier, generating a second type of specified execution operation indicating a storage processing based on the expiration processing time and the storage object identifier, and constructing the expiration processing strategy corresponding to the database shard based on the expiration processing time and the second type of specified execution operation.

[0050] Refer toFigures 5-8 The policy configuration request can be initiated by a target object (such as a user, a user simulator; the user is not limited to a common user and a staff). The receiving time can be regarded as the time of receiving the policy configuration request, or the time when the policy configuration request is sent. The shard storage duration is the duration that the target object hopes the database shard to be stored. The master server or the master client takes the receiving time as the starting time of calculating the storage duration, and then determines the expiration processing time according to the shard storage duration and the receiving time. For example, the shard storage duration in the policy configuration request sent by the target user is 3 hours, and the receiving time is 2020-08-16 21:43:21, so the expiration processing time is 3 hours later, that is, 2020-08-17 00:43:21. If the policy configuration request carries a backup object identifier, it means that the target object hopes that the database shard will be backed up when it expires (corresponding to the second type of specified execution operation), that is, it will not be deleted, but will be backed up to a storage medium outside the storage medium corresponding to the master database. In practical applications, the backup processing also means that the target object hopes to be able to perform data archiving, that is, to separate hot and cold data. Of course, the database shard backed up to the storage medium corresponding to the backup object identifier can be permanently stored, or periodically deleted, or periodically backed up again. If the policy configuration request does not carry a backup object identifier, it means that the target object hopes that the database shard will be deleted when it expires (corresponding to the first type of specified execution operation). Correspondingly, the expiration processing strategy corresponding to the database shard is constructed based on the expiration processing time and the specified execution operation.

[0051] In another embodiment, as shown in Figure 4 the method further comprises configuring the expiration processing strategy corresponding to the database shard:

[0052] S401: receiving a policy configuration request, the policy configuration request carrying a shard storage duration;

[0053] S402: obtaining a policy template based on the receiving time;

[0054] S403: when the policy configuration request does not carry the backup object identifier, writing the shard storage duration into the policy template to obtain the expiration processing time and the first type of specified execution operation indicating deletion processing, and constructing the expiration processing strategy corresponding to the database shard based on the expiration processing time and the first type of specified execution operation;

[0055] S404: When the policy configuration request carries the snapshot object identifier, write the shard retention duration and the snapshot object identifier into the policy template to obtain the expiration processing time and the second type of specified execution operation indicating snapshot processing, and construct the expiration processing strategy corresponding to the database shard based on the expiration processing time and the second type of specified execution operation.

[0056] With reference to Figures 5-8 The policy configuration request can be initiated by a target object (such as a user, a user simulator; the user is not limited to an ordinary user and a staff). The receiving time can be regarded as the time of receiving the policy configuration request, or as the time of issuing the policy configuration request. The shard retention duration is the duration that the target object hopes the database shard to be retained. The master server or the master client obtains a policy template based on the receiving time, for example, the receiving time is 2020-08-16 21:00:00, and the policy template is the template corresponding to this time. The obtained policy template can also be regarded as a set of logic that is pre-assigned with the receiving time as the starting time for calculating the retention duration. Then 1) when the policy configuration request does not carry the snapshot object identifier, it means that the target object hopes the database shard to be deleted at expiration. Write the shard retention duration into the policy template to obtain the expiration processing time and the first type of specified execution operation indicating deletion processing; 2) when the policy configuration request carries the snapshot object identifier, it means that the target object hopes the database shard to be snapshoted (corresponding to the second type of specified execution operation) at expiration, that is, the database shard is not deleted, but is snapshoted to a storage medium outside the storage medium corresponding to the master database. In actual application, the snapshot processing also means that the target object hopes to be able to perform data archiving, that is, cold and hot data separation. Of course, the database shard snapshoted to the storage medium corresponding to the snapshot object identifier can be permanently retained, or can be periodically deleted, or can be periodically re-snapshoted. Write the shard retention duration and the snapshot object identifier into the policy template to obtain the expiration processing time and the second type of specified execution operation indicating snapshot processing. Correspondingly, construct the expiration processing strategy corresponding to the database shard based on the expiration processing time and the specified execution operation.

[0057] In practical applications, the division of the same time is also often diverse, such as being classified into the category of seasons, the category of months, the category of days, the category of hours, and the like. The policy configuration request can carry time granularity information, that is, information for determining a reference category (dimension) for a specific time. For example, when the time granularity information indicates the category of months, the receiving time corresponds to 2020-08; when the time granularity information indicates the category of days, the receiving time corresponds to 2020-08-16; and when the time granularity information indicates the category of hours, the receiving time corresponds to 2020-08-16 21. The template library can be determined according to the time granularity information carried by the policy configuration request and the receiving time, and the matched template can be used as the policy template. Since the receiving time is used as the starting time for calculating the storage duration, correspondingly, the storage duration of the shard set by the target object is also constrained by the time granularity information. When the time granularity information indicates the category of months, the unit of the storage duration of the shard can be months; when the time granularity information indicates the category of days, the unit of the storage duration of the shard can be days; and when the time granularity information indicates the category of hours, the unit of the storage duration of the shard can be hours or days.

[0058] In combination with the descriptions of steps S301-S304 and S401-S404, the embodiment of the present application proposes a high-efficiency and secure data cleaning policy in combination with the shard (sub-table or sub-zone) function, and provides data archiving capability, which can avoid deleting data to meet the needs of users for permanently storing data.

[0059] In another embodiment, when the database shard is a database sub-zone, the method further includes creating the database sub-zone: determining, in response to sub-zone field information and an associated data table identifier carried by a database sub-zone creation request, sub-zone data that matches the sub-zone field information in an associated data table corresponding to the associated data table identifier, and creating the database sub-zone based on the matched sub-zone data. The database sub-zone creation request can be initiated by a target object (such as a user, a user simulator, and a user who is not limited to an ordinary user and a staff member). The associated data table can be regarded as a database sub-table that is not configured with an expiration processing policy. The database sub-zone is divided from the associated data table based on the sub-zone field information.

[0060] In practical applications, the database shard can be a database sub-table or a database sub-zone. Figures 5-8 For the interactive interface provided for the target object, the application of the policy configuration function module provided by the main client for the target object can be regarded as a module based on which the user performs policy configuration. The target object can start the creation of the database sub-table through the "sub-table" button in Figure 5 , and start the creation of the database sub-zone through the "sub-zone" button in Figure 5The "Partition" button in the menu initiates the creation of database partitions. Based on... Figure 5 Clicking the "Splitter" or "Partition" button will redirect the user interface to... Figure 6 The target object can be accessed through... Figure 6 The "Add" button allows you to access specific settings for creating a table or partition.

[0061] like Figure 7 As shown, in the table partitioning settings, the target object can select the "Table Partitioning Type" (corresponding to the aforementioned time granularity information) representing the time granularity, specifically "Partition by Month," "Partition by Day," and "Partition by Hour." The target object needs to confirm that a reference table (corresponding to the aforementioned strategy template) already exists, and the table name of the reference table needs to be set according to the requirements in the "Note" section at the bottom of the image. The target object can set the retention period as needed. If the target object checks "Enable Associated Archive," then data tables exceeding the retention period will be moved to the relevant archive instance, thereby achieving cold and hot data separation and seamless migration, without the user's awareness. If "Enable Associated Archive" is not checked, then data tables exceeding the retention period will be directly cleaned up using the drop table method (deleting the table definition and all data, indexes, triggers, constraints, and permission specifications of the table). Table 1 below shows the reference tables pre-created for the user (for example, the target object's table partitioning settings are set up on April 15, 2020):

[0062]

[0063] Table 1

[0064] like Figure 8 As shown, in the partition creation settings, each partition corresponds to a related data table. The target object needs to specify the partition field based on the related data table; the field can only be an integer or a time type. In the above table creation settings, the names of the multiple created partitions carry a time attribute, allowing the business layer to distinguish the tables to be written to based on time. Since each partition corresponds to a related data table, the partition always presents the related data table name to the business layer.

[0065] The following statement can be used to automatically create data partitions for this table. Users do not need to worry about the generation of partitions; they only need to manage the time range for data retention.

[0066] CREATE TABLE `t_partition`(

[0067] `a`int(11)NOT NULL,

[0068] `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

[0069] PRIMARY KEY(`a`,`b`)

[0070] ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='1'

[0071] / *!50100 PARTITION BY RANGE(UNIX_TIMESTAMP(b))

[0072] (PARTITION p20200805 VALUES LESS THAN(1596643200) ENGINE=InnoDB,

[0073] PARTITION p20200806 VALUES LESS THAN(1596729600) ENGINE=InnoDB,

[0074] PARTITION p20200807 VALUES LESS THAN(1596816000) ENGINE=InnoDB,

[0075] PARTITION p20200808 VALUES LESS THAN(1596902400) ENGINE=InnoDB,

[0076] PARTITION p20200809 VALUES LESS THAN(1596988800) ENGINE=InnoDB)

[0077] S202: When the current time meets the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates deletion processing, a deletion instruction is constructed using a data definition language, and the target database shard is deleted based on the deletion instruction;

[0078] In the embodiments of the present application, the current time meeting the expiration processing time can indicate that the current time is equal to the expiration processing time, the current time exceeds the expiration processing time, or of course, the current time has not reached the expiration processing time but the difference between the two is less than a preset threshold. When deleting the target database shard, a delete instruction can be first constructed by using a data definition language, and then the target database shard is deleted based on the delete instruction. Compared with DML, the delete instruction constructed by DDL will not cause a large amount of binlog when executed, so that the master-slave delay caused thereby can be reduced.

[0079] When the target database shard is a table, a drop table SQL (Structured Query Language) statement is created to clean up data. When the target database shard is a partition, a drop partition SQL statement is created to clean up data.

[0080] When each database shard corresponding expiration processing strategy is obtained by the master server in the traversal, the step of deleting the target database shard at expiration can be continued to be executed by the master server. When each database shard corresponding expiration processing strategy is obtained by the master client in the traversal, the step of deleting the target database shard at expiration can be executed by the master server based on the expiration processing notification of the master client.

[0081] S203: When the current time meets the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates the migration processing, a target migration object is determined and the target database shard is migrated into the target migration object.

[0082] In the embodiments of the present application, the current time not meeting the expiration processing time can indicate that the current time has not reached the expiration processing time, or the current time has not reached the expiration processing time and the difference between the two is greater than or equal to a preset threshold. When migrating the target database shard, the target database shard can be first exported from the master database by using mysqldump (a logical backup tool provided by MySQL), and then imported into the target migration object (an archive instance) by using the load method. The data stored in the archive instance can also be managed by corresponding partition and table management (including data elimination based on the configured expiration processing strategy). The target migration object can be a relatively inexpensive storage medium compared with the master database, such as a normal sata disk (serial hard disk), so that the storage cost can be reduced.

[0083] As Figure 9As shown, after the target database shard is copied into the target copy object, the preset key-value pair storage engine and the preset lossless compression algorithm can be selected first, and then the target database shard is compressed based on the preset key-value pair storage engine and the preset lossless compression algorithm. In order to realize long-term storage of cold data, the preset key-value pair storage engine can be a high compression storage engine toku (a key-value pair storage engine based on ft-index (Fractal Tree Index, fractal tree index)), and the preset lossless compression algorithm can be a zstd compression algorithm (a lossless compression algorithm). The toku engine adopts the zstd compression algorithm, which can achieve a compression efficiency of 4:1, and can use large blocks of 4m pages to increase the compression efficiency.

[0084] When the expiration processing strategy corresponding to each database shard is obtained by the master server in the traversal, the step of copying the target database shard can be continued to be executed by the master server. When the expiration processing strategy corresponding to each database shard is obtained by the master client in the traversal, the step of copying the target database shard can be executed by the master server based on the expiration processing notification of the master client.

[0085] In actual application, for the expiration processing strategy corresponding to the data in a certain master database exceeding 400, the data deletion and archiving can solve the decrease of 30% of the disk storage cost.

[0086] In one embodiment, the method further comprises: when the current time meets the expiration processing time corresponding to the target database shard, generating an expiration reminder and sending the expiration reminder to a target object; in response to a postponement request returned by the target object, extending the expiration processing time corresponding to the target database shard. That is, the target object can assign a new expiration processing time to the expiration processing strategy according to the current expiration processing time, which can improve the flexibility of the target object in managing the database shard.

[0087] As can be seen from the technical solutions provided by the above embodiments of the present specification, in the embodiments of the present specification, the data in the master database is managed by shards, and when the expiration processing time corresponding to the database shard is reached, the data is deleted or copied according to the preset strategy. When deleted, DDL (data definition language) is used, which can reduce the master-slave delay and the occurrence of performance jitter. The copying can ensure that the master database can more effectively store hot data (relatively cold data, data with higher access frequency).

[0088] The embodiments of the present application also provide a database data processing device, as shown in Figure 10 As shown, the device is applied to a database with a master-slave structure, and the device comprises:

[0089] Traversal module 1010: used to traverse the database shards corresponding to the main database, and obtain the expiration handling policy corresponding to each database shard during the traversal. The expiration handling policy corresponding to the database shard includes the expiration handling time as a trigger condition and the specified execution operation after the trigger condition is met.

[0090] Deletion module 1020: When the current time meets the expiration processing time corresponding to the target database shard and the specified execution operation indication for deletion processing is given to the target database shard, it constructs a deletion instruction using a data definition language and deletes the target database shard based on the deletion instruction;

[0091] Transfer module 1030: When the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation instruction corresponding to the target database shard is transferred, it determines the target transfer object and transfers the target database shard to the target transfer object.

[0092] In one embodiment, the traversal module can be provided as an encapsulated interface to the main server or main client.

[0093] It should be noted that the apparatus and method embodiments described in the device embodiments are based on the same inventive concept.

[0094] This invention provides an electronic device including a processor and a memory. The memory stores at least one instruction or at least one program segment, which is loaded and executed by the processor to implement the database data processing method provided in the above method embodiments.

[0095] Furthermore, Figure 11 A schematic diagram of the hardware structure of an electronic device for implementing the data processing method of the database provided in the embodiments of the present invention is shown. The electronic device may participate in or include the data processing apparatus for the database provided in the embodiments of the present invention. Figure 11 As shown, the electronic device 110 may include one or more processors 1102 (shown as 1102a, 1102b, ..., 1102n in the figure) (processor 1102 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 1104 for storing data, and a transmission device 1106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 11The illustrated structure is merely a schematic and does not limit the structure of the electronic device described above. For example, the electronic device 110 can further include more or less components than those shown, or have a different configuration of components than those shown. Figure 11 The illustrated structure is merely a schematic and does not limit the structure of the electronic device described above. For example, the electronic device 110 can further include more or less components than those shown, or have a different configuration of components than those shown. Figure 11 The illustrated structure is merely a schematic and does not limit the structure of the electronic device described above. For example, the electronic device 110 can further include more or less components than those shown, or have a different configuration of components than those shown.

[0096] It should be noted that the one or more processors 1102 and / or other data processing circuitry described above can be referred to herein generally as "data processing circuitry." The data processing circuitry can be embodied in whole or in part as software, hardware, firmware, or any combination thereof. Furthermore, the data processing circuitry can be a single standalone processing module, or incorporated in whole or in part within any one of other elements of the electronic device 110 (or mobile device). As referred to in embodiments of the present application, the data processing circuitry serves as a processor to control, for example, selection of a variable resistance terminal path connected to an interface.

[0097] The memory 1104 can be used to store software programs and modules of application software, and program instructions / data storage means corresponding to the database data processing method as described in embodiments of the present application. The processor 1102 executes various functional applications and data processing by running the software programs and modules stored in the memory 1104, i.e., implements the database data processing method described above. The memory 1104 can include a high-speed random access memory, and can further include a non-volatile memory such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 1104 can further include a memory disposed remotely with respect to the processor 1102, which can be connected to the electronic device 110 through a network. Examples of the network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0098] The transmission device 1106 is configured to receive or send data via a network. Specific examples of the network can include a wireless network provided by a communication provider of the electronic device 110. In one example, the transmission device 1106 includes a network interface controller (NIC) that can be connected to other network devices through a base station so as to be able to communicate with the Internet. In one embodiment, the transmission device 1106 can be a radio frequency (RF) module configured to communicate with the Internet in a wireless manner.

[0099] The display can be, for example, a touch screen type liquid crystal display (LCD) that enables a user to interact with a user interface of the electronic device 110 (or mobile device).

[0100] The embodiment of the present application further provides a computer readable storage medium, which can be arranged in an electronic device to store at least one instruction or at least one program for implementing a data processing method of a database in the method embodiment, and the at least one instruction or the at least one program is loaded and executed by the processor to implement the data processing method of the database provided by the above method embodiment.

[0101] Optionally, in the embodiment, the storage medium can be located in at least one of a plurality of network servers of a computer network. Optionally, in the embodiment, the storage medium can include, but is not limited to, a U disk, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk, and various media capable of storing program codes.

[0102] It should be noted that the above-mentioned sequence of the embodiments of the present application is only for description, not representing the advantages and disadvantages of the embodiments. The above-mentioned embodiments of the present application are described. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be executed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multi-task processing and parallel processing are possible or can be advantageous.

[0103] Each of the embodiments in the present specification is described in a progressive manner, and the same or similar parts of each of the embodiments can be referred to each other. Each of the embodiments focuses on the differences from other embodiments. In particular, for the device and electronic device embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the part of the method embodiment.

[0104] Those skilled in the art can understand that all or part of the above-mentioned embodiments can be completed by hardware, or by program instructing relevant hardware to complete, and the program can be stored in a computer readable storage medium. The storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk.

[0105] The above-mentioned is only the preferred embodiment of the present application, and is not used to limit the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A data processing method of a database, characterized by, The method is applied to a database with a master-slave structure, and comprises the following steps: traversing database shards corresponding to a master database, obtaining an expiration processing strategy corresponding to each database shard in the traversal, the expiration processing strategy of the database shard comprising an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met; when the current time matches the expiration processing time corresponding to a target database shard and the specified execution operation corresponding to the target database shard indicates a deletion process, constructing a deletion instruction using a data definition language, and deleting the target database shard based on the deletion instruction; when the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a migration process, determining a target migration object and migrating the target database shard into the target migration object; when the database shard is a database partition, the method further comprises creating the database partition: determining partition data matching the partition field information in an associated data table corresponding to an associated data table identifier carried by a database partition creation request, and creating the database partition based on the matching partition data.

2. The method of claim 1, wherein, After the database shards corresponding to the master database are traversed, the method further comprises: when the current time matches the expiration processing time corresponding to a target database shard, generating an expiration reminder and sending the expiration reminder to a target object; in response to a postponement request returned by the target object, extending the expiration processing time corresponding to the target database shard.

3. The method of claim 1, wherein, The method further comprises configuring the expiration processing strategy of the database shard: receiving a strategy configuration request, the strategy configuration request carrying a shard retention duration; determining the expiration processing time based on the shard retention duration and the reception time; when the strategy configuration request does not carry a migration object identifier, generating a first type of specified execution operation indicating a deletion process based on the expiration processing time, and constructing the expiration processing strategy of the database shard based on the expiration processing time and the first type of specified execution operation; when the strategy configuration request carries the migration object identifier, generating a second type of specified execution operation indicating a migration process based on the expiration processing time and the migration object identifier, and constructing the expiration processing strategy of the database shard based on the expiration processing time and the second type of specified execution operation.

4. The method of claim 1, wherein, The method further comprises configuring the expiration processing strategy of the database shard: receiving a strategy configuration request, the strategy configuration request carrying a shard retention duration; obtaining a strategy template based on the reception time; when the strategy configuration request does not carry a migration object identifier, writing the shard retention duration into the strategy template to obtain the expiration processing time and a first type of specified execution operation indicating a deletion process, and constructing the expiration processing strategy of the database shard based on the expiration processing time and the first type of specified execution operation; when the strategy configuration request carries the migration object identifier, generating a second type of specified execution operation indicating a migration process based on the expiration processing time and the migration object identifier, and constructing the expiration processing strategy of the database shard based on the expiration processing time and the second type of specified execution operation. When the policy configuration request carries the snapshot object identifier, the snapshot object identifier and the snapshot duration are written into the policy template to obtain the expiration processing time and a second type of specified execution operation indicating snapshot processing, and the expiration processing strategy corresponding to the database shard is constructed based on the expiration processing time and the second type of specified execution operation.

5. The method of claim 4, wherein, The policy template is obtained based on the receiving time, including: According to the time granularity information carried by the policy configuration request and the receiving time, a template matched with the time granularity information and the receiving time is determined in a template library, and the matched template is taken as the policy template.

6. The method of claim 1, wherein, After the target database shard is snapshotted to the target snapshot object, the method further includes: Selecting a preset key-value pair storage engine; Obtaining a preset lossless compression algorithm; Based on the preset key-value pair storage engine and the preset lossless compression algorithm, the target database shard is compressed.

7. A data processing apparatus of a database, characterized by, The device is applied to a database with a master-slave structure, and includes: A traversal module: configured to traverse database shards corresponding to a master database, and obtain an expiration processing strategy corresponding to each database shard in the traversal, the expiration processing strategy including an expiration processing time as a trigger condition and a specified execution operation after the trigger condition is met; A deletion module: configured to, when a current time matches an expiration processing time corresponding to a target database shard and a specified execution operation corresponding to the target database shard indicates a deletion processing, construct a deletion instruction using a data definition language, and delete the target database shard based on the deletion instruction; A snapshot module: configured to, when the current time matches the expiration processing time corresponding to the target database shard and the specified execution operation corresponding to the target database shard indicates a snapshot processing, determine a target snapshot object and snapshot the target database shard into the target snapshot object; When the database shard is a database partition, the device is further configured to create the database partition: in response to an associated data table identifier and partition field information carried by a database partition creation request, determine partition data matched with the partition field information in an associated data table corresponding to the associated data table identifier, and create the database partition based on the matched partition data.

8. The apparatus of claim 7, wherein, The device is further configured to: When the current time matches the expiration processing time corresponding to a target database shard, generate an expiration reminder and send the expiration reminder to a target object; In response to a postponement request returned by the target object, extend the expiration processing time corresponding to the target database shard.

9. The apparatus of claim 7, wherein, The device is further configured to configure the expiration processing strategy corresponding to the database shard: Receive a policy configuration request, the policy configuration request carrying a shard duration; Determine the expiration processing time based on the shard duration and a receiving time; When the policy configuration request does not carry the archiving object identifier, a first type of specified execution operation indicating a deletion process is generated based on the expiration processing time, and an expiration processing policy corresponding to the database shard is constructed based on the expiration processing time and the first type of specified execution operation; When the policy configuration request carries the archiving object identifier, a second type of specified execution operation indicating an archiving process is generated based on the expiration processing time and the archiving object identifier, and an expiration processing policy corresponding to the database shard is constructed based on the expiration processing time and the second type of specified execution operation.

10. The apparatus of claim 7, wherein, The apparatus is further configured to configure an expiration processing policy corresponding to the database shard: receive a policy configuration request carrying a shard retention duration; obtain a policy template based on a reception time; When the policy configuration request does not carry the archiving object identifier, the shard retention duration is written into the policy template to obtain the expiration processing time and a first type of specified execution operation indicating a deletion process, and an expiration processing policy corresponding to the database shard is constructed based on the expiration processing time and the first type of specified execution operation; When the policy configuration request carries the archiving object identifier, the shard retention duration and the archiving object identifier are written into the policy template to obtain the expiration processing time and a second type of specified execution operation indicating an archiving process, and an expiration processing policy corresponding to the database shard is constructed based on the expiration processing time and the second type of specified execution operation.

11. The apparatus of claim 10, wherein, The obtaining of the policy template based on the reception time comprises: determining a template matching the time granularity information carried by the policy configuration request and the reception time from a template library, and taking the matching template as the policy template.

12. The apparatus of claim 7, wherein, The apparatus is further configured to: select a preset key-value pair storage engine; obtain a preset lossless compression algorithm; perform compression processing on the target database shard based on the preset key-value pair storage engine and the preset lossless compression algorithm.

13. An electronic device, comprising: The electronic device includes a processor and a memory, the memory stores at least one instruction or at least one program, the at least one instruction or the at least one program is loaded and executed by the processor to implement the database data processing method of any one of claims 1-6.

14. A computer-readable storage medium, characterized in that, The storage medium stores at least one instruction or at least one program, the at least one instruction or the at least one program is loaded and executed by the processor to implement the database data processing method of any one of claims 1-6.

15. A computer program product, characterised in that, The computer program product includes computer instructions stored in a computer readable storage medium, and a processor of a computer device reads and executes the computer instructions from the computer readable storage medium, so that the computer device executes the database data processing method of any one of claims 1-6.

Images