Database table partitioning method and device, computer equipment and storage medium

Abstract

The invention belongs to the field of data processing and financial science and technology, and relates to a database table partitioning method which comprises the following steps: acquiring all to-be-partitioned main tables in a database according to a partition creation request through a first process, acquiring all locks of the main tables, and releasing all locks; obtaining main table data, calling a second process to create a partition table according to a preset partition strategy, distributing the main table data into the partition table according to a preset partition rule to obtain different partitions, and sending a partition creation completion instruction to the first process; and in response to a partition creation completion instruction, calling the first process to re-acquire all the locks, and performing locking operation. The invention further provides a database table partitioning device, computer equipment and a storage medium. In addition, the invention also relates to a block chain technology, and all to-be-partitioned main tables can be stored in a block chain. According to the method and the device, the concurrency performance of the database can be improved, and meanwhile, due to the fact that the locking time of the main table is shortened, the waiting time of a user is shortened, and the user experience is improved.

Description

Technical Field

[0001] The present application relates to the fields of data processing and financial technology, and in particular to a database table partitioning method, apparatus, computer equipment and storage medium. Background Art

[0002] PostgreSQL (PG) is a free software object-relational database management system (ORDBMS) with a full range of features. It is also commonly known as FG database. It is an excellent open source database with rich functions. It is an open source database that fully surpasses MySQL in terms of functions and is not inferior to MySQL in terms of performance. At the same time, PostgreSQL, like Oracle, is a process architecture that can better play the performance of multiple CPUs. As the use of PG database becomes more and more common, the optimization of this database is also constantly developing. Among them, the setting of partition table is an important aspect of database optimization.

[0003] Common partitioning methods include range partitioning, hash partitioning, and list partitioning. For range partitioning, when inserting the target value, if the required partition does not exist, the required partition (hereinafter referred to as the target partition) can be created by automatically creating a partition, that is, an auto-increment partition. In the process of creating partitions with the auto-increment partition function, when creating a partition in the current process, the main table will be locked exclusively, that is, the highest level of lock, so that other processes cannot access the main table, affecting other sessions' access to the table. If the auto-increment partition statement is included in a long transaction, the table cannot be accessed by other sessions during the entire transaction, which severely limits concurrency and user experience. Summary of the invention

[0004] The purpose of the embodiments of the present application is to propose a database table partitioning method, apparatus, computer equipment and storage medium to solve the technical problem that the main table is restricted from access during the existing partition creation process, which seriously limits concurrency and affects the user experience.

[0005] In order to solve the above technical problems, the present application embodiment provides a database table partitioning method, which adopts the following technical solution:

[0006] Receive a partition creation request, obtain all master tables to be partitioned in the database according to the partition creation request through a first process, obtain all locks of the master tables, and release all the locks;

[0007] Access the master table, obtain the master table data, call the second process to create a partition table according to a preset partition strategy, and distribute the master table data to the partition table according to a preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process;

[0008] In response to the partition creation completion instruction, the first process is called to reacquire all the locks and perform a locking operation.

[0009] Furthermore, the step of obtaining all the main tables to be partitioned in the database according to the partition creation request through the first process, obtaining all the locks of the main tables, and releasing all the locks includes:

[0010] Parsing the partition creation request by the first process to obtain request content, acquiring all the main tables to be partitioned in the database based on the request content, and obtaining a list of the main tables to be partitioned;

[0011] Traversing the main table list through the first process, obtaining all locks of each main table in the main table list, and reading lock information of each lock;

[0012] Determine whether the lock is a valid lock according to the lock information, and if the lock is a valid lock, release all the valid locks.

[0013] Furthermore, the step of calling the second process to create a partition table according to a preset partition strategy includes:

[0014] Generate an SQL table creation statement according to a preset partitioning strategy, and pass the SQL table creation statement to the second process;

[0015] The second process is called to execute the SQL table creation statement to create the corresponding partition table.

[0016] Furthermore, the step of allocating the main table data to the partition table according to a preset partition rule includes:

[0017] Screening and sorting the main table data according to the preset partitioning rule to determine the partition table data allocated to the partition table;

[0018] Call the data writing function to write the partition table data into the corresponding partition table.

[0019] Furthermore, before the step of sending the partition creation completion instruction to the first process, the method further includes:

[0020] After the data allocation is completed, a data query request is received to determine whether the partition table is synchronized with the main table;

[0021] When the synchronization is completed, the step of sending a partition creation completion instruction to the first process is executed.

[0022] Further, in response to the partition creation completion instruction, the step of calling the first process to reacquire all the locks and performing a locking operation includes:

[0023] Parsing the partition creation completion instruction by the first process to obtain instruction content;

[0024] Confirming the partition creation and data allocation completion status according to the instruction content, and calling the first process to reacquire all locks of the main table;

[0025] After confirming that all the locks are successfully acquired, relocking operations are performed on all the locks.

[0026] Further, after confirming that all the locks are successfully acquired, the step of relocking all the locks further includes:

[0027] Detecting the status of each lock, and when all locks are successfully locked, performing data consistency check on the main table and the partition table through the first process;

[0028] After the consistency check passes, normal access and operation of the main table after the partition is created are restored.

[0029] In order to solve the above technical problems, the embodiment of the present application further provides a database table partitioning device, which adopts the following technical solution:

[0030] An acquisition module, configured to receive a partition creation request, acquire all master tables to be partitioned in the database according to the partition creation request through a first process, acquire all locks of the master tables, and release all the locks;

[0031] A creation module, used to access the master table, obtain the master table data, call the second process to create a partition table according to a preset partition strategy, and distribute the master table data to the partition table according to a preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process;

[0032] The locking module is used to call the first process to reacquire all the locks and perform a locking operation in response to the partition creation completion instruction.

[0033] In order to solve the above technical problems, the embodiment of the present application further provides a computer device, which adopts the following technical solution:

[0034] The computer device includes a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the database table partitioning method described above when executing the computer-readable instructions.

[0035] In order to solve the above technical problems, the embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solution:

[0036] The computer-readable storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by a processor, the steps of the database table partitioning method described above are implemented.

[0037] Compared with the prior art, this application has the following beneficial effects:

[0038] The present application provides a database table partitioning method, which obtains all main tables to be partitioned in a database according to a received partition creation request through a first process, obtains all locks of the main table and releases them, calls a second process to create a partition table according to a preset partition strategy, and distributes the main table data to the partition table according to a preset partition rule, and calls the first process to re-acquire all locks and perform a locking operation in response to a partition creation completion instruction; the present application releases all locks of the main table before creating a partition, so that other processes can still access the main table during the partition creation process, thereby improving the concurrency performance of the database, and at the same time, since the locking time of the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience; in addition, by using another process to create a partition, the time range for the current transaction to hold a high-level lock is greatly shortened, and other sessions and other transactions can access the main table faster without being restricted, thereby greatly optimizing performance and customer experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the scheme in the present application, a brief introduction is given below to the drawings required for use in the description of the embodiments of the present application. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0040] Figure 1 is an exemplary system architecture diagram to which the present application may be applied;

[0041] Figure 2 is a flow chart of an embodiment of a database table partitioning method according to the present application;

[0042] Figure 3 is a schematic diagram of the structure of an embodiment of a database table partitioning device according to the present application;

[0043] Figure 4 It is a structural diagram of an embodiment of a computer device according to the present application. DETAILED DESCRIPTION

[0044] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by technicians in the technical field of the present application; the terms used in the specification of the application herein are only for the purpose of describing specific embodiments and are not intended to limit the present application; the terms "including" and "having" and any variations thereof in the specification and claims of the present application and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of the present application or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

[0045] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0046] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0047] This application provides a database table partitioning method, which can be applied to Figure 1 In the system architecture 100 shown, the system architecture 100 may include terminal devices 101, 102, 103, a network 104 and a server 105. The network 104 is used to provide a medium for communication links between the terminal devices 101, 102, 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0048] Users can use terminal devices 101, 102, 103 to interact with server 105 through network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0049] Terminal devices 101, 102, 103 can be various electronic devices with display screens and supporting web browsing, including but not limited to smart phones, tablet computers, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 (Moving Picture Experts Group Audio Layer IV) players, laptop computers, desktop computers, etc.

[0050] The server 105 may be a server that provides various services, such as a background server that provides support for web pages displayed on the terminal devices 101 , 102 , and 103 .

[0051] It should be noted that the database table partitioning method provided in the embodiment of the present application is generally executed by a server / terminal device, and accordingly, the database table partitioning device is generally set in the server / terminal device.

[0052] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0053] Continue to refer Figure 2 , shows a flowchart of an embodiment of a database table partitioning method according to the present application, comprising the following steps:

[0054] Step S201, receiving a partition creation request, obtaining all main tables to be partitioned in the database according to the partition creation request through a first process, obtaining all locks of the main tables, and releasing all locks.

[0055] A partition creation request usually refers to a series of instructions and parameters for partitioning a table in a database. This request may be issued by a database administrator or an application program to split a large table into multiple smaller, more manageable parts, i.e., partitions, according to specific rules. The first process is the current process that executes the current transaction.

[0056] The partition creation request contains request content such as request identifier, table information, partition information, etc., wherein the table information indicates the name of the table to be partitioned and the database architecture in which it is located; the partition information includes but is not limited to the partition key, partition strategy, partition boundary, number of partitions, etc. The first process obtains all the main tables to be partitioned in the database according to the partition creation request, and then obtains the metadata of the main table, determines all the locks of the main table according to the metadata, releases all the locks, and then creates partitions in another process (the second process). At this time, the current process is blocked and waits for the result of the other process.

[0057] Metadata includes table name, column information, primary key, foreign key, index, constraint, comment, data type, trigger, partition information and access control. Column information includes column name, data type, whether null value (NULL) is allowed, default value, column-level constraint, etc.; primary key is used to uniquely identify the column or combination of columns for each row of data in the table; foreign key is a column in the table that points to the primary key of other tables and is used to maintain the relationship between tables; index is a data structure used to accelerate data retrieval, including index type, column, uniqueness, etc.; constraints include CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc., used to ensure data integrity and accuracy; comment is a descriptive text for tables or columns, used to explain their purpose or content; data type is the data type of each column in the table, such as integer, floating point number, string, date, etc.; trigger is a trigger associated with the table, which defines the rules that are automatically executed when a specific database operation is performed; partition information indicates if the table is partitioned, the relevant information of the partition, such as partition key, partition type, partition boundary, etc.; access control defines which users or roles can access the table and access rights.

[0058] In this embodiment, the electronic device (eg Figure 1 The server / terminal device shown in the figure can receive the partition creation request through a wired connection or a wireless connection. It should be noted that the above wireless connection method may include but is not limited to 3G / 4G connection, WiFi connection, Bluetooth connection, WiMAX connection, Zigbee connection, UWB (ultra wideband) connection, and other wireless connection methods currently known or to be developed in the future.

[0059] It should be emphasized that in order to further ensure the privacy and security of all the master tables to be partitioned, all the master tables to be partitioned can also be stored in a node of a blockchain.

[0060] The blockchain referred to in this application is a new application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, encryption algorithm, etc. Blockchain is essentially a decentralized database, a string of data blocks generated by cryptographic methods. Each data block contains a batch of network transaction information, which is used to verify the validity of its information (anti-counterfeiting) and generate the next block. Blockchain can include the underlying blockchain platform, platform product service layer, and application service layer.

[0061] In some optional implementations, the steps of obtaining all main tables to be partitioned in the database according to the partition creation request by the first process, obtaining all locks of the main tables, and releasing all locks include:

[0062] The partition creation request is parsed by the first process to obtain the request content, and all the main tables to be partitioned in the database are obtained based on the request content to obtain a list of the main tables to be partitioned;

[0063] The first process traverses the main table list, obtains all locks of each main table in the main table list, and reads the lock information of each lock;

[0064] Determine whether the lock is a valid lock according to the lock information, and if the lock is a valid lock, release all valid locks.

[0065] In this embodiment, the partition creation request is parsed to obtain the request content, the table information in the request content is extracted, and the information of all the master tables to be partitioned is determined according to the table information, thereby determining all the master tables to be partitioned, and forming a master table list to be partitioned with all the master tables to be partitioned. The first process is called to traverse the master table list, obtain all the locks of each master table in the master table list, record all the locks on the master table, and then read the lock information of each lock, which includes the type of lock (such as shared lock, exclusive lock), the granularity of the lock (such as row level, table level), the state of the lock (such as holding state, waiting state) and other information. According to the lock state information, it is determined which locks are valid and which locks are waiting, and the valid locks are released one by one to ensure that only valid locks are released to avoid misoperation affecting other sessions or transactions.

[0066] Among them, valid locks include shared locks, exclusive locks, update locks, row locks, table locks, etc.

[0067] In this embodiment, the process of recording the locks on the main table is as follows: because resources in PostgreSQL are hierarchical, locks are also hierarchical, and an irregular matrix can be used to record the locks at each level, where each row represents a level, and each level contains the locks to be released. The matrix can be implemented using a two-dimensional array data structure, or a linked list, where each node in the linked list is another linked list.

[0068] In this embodiment, the first process acquires all locks of the main table by calling the locking mechanism of the database, and uses the database lock management mechanism to release all valid locks on the main table to prevent other sessions or transactions from modifying the main table and ensure data consistency.

[0069] By releasing all locks on the main table, other sessions are allowed to access the main table in a shorter time, thereby improving the concurrency performance of the database. At the same time, since the lock time on the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience.

[0070] Step S202, access the main table, obtain the main table data, call the second process to create a partition table according to the preset partition strategy, and distribute the main table data to the partition table according to the preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process.

[0071] In this embodiment, by using autonomous transactions, partitions are created in another process, that is, DDL (Data Definition Language) operations are executed in another process to narrow the scope of the exclusive lock on the main table. Autonomous transactions are a concept in databases, referring to subtransactions started in a main transaction, which can be executed independently of the main transaction and have their own transaction control logic. Even if the main transaction fails, the autonomous transaction can continue to execute or roll back.

[0072] The preset partitioning strategy defines how to distribute data to different partitions based on the value of the partition key. PostgreSQL supports two main partitioning strategies: range partitioning and list partitioning. Range partitioning distributes data to different partitions based on a continuous range of values ​​or date ranges; list partitioning distributes data to different partitions based on a specific list of partition key values. The preset partitioning rules refer to specific partition creation and data distribution rules. These rules define the range or value list of each partition and how data is mapped to these partitions. Partitioning rules usually include the boundary value of each partition, the name of the partition, and the storage attributes of the partition.

[0073] For example, the preset partitioning strategy is partitioning by date, and the preset partitioning rule is one partition per month.

[0074] In the second process, an autonomous transaction is used to implement the specified operation, that is, to create a partition. Specifically, the specified operation can be implemented by using the pg_background plug-in, that is, to create a partition according to the requirements of the previous process (i.e., the first process). The entire operation is contained in a separate transaction, which is independent of the transaction in the previous process and is an independent transaction. Since the previous process has released all locks on the main table, it can access the main table and create the required specified partition for it. After the partition is created, the second process ends and the result is returned to the previously waiting process (i.e., the first process).

[0075] In some optional implementations, the step of calling the second process to create a partition table according to a preset partition strategy includes:

[0076] Generate an SQL table creation statement according to the preset partitioning strategy, and pass the SQL table creation statement to the second process;

[0077] Call the second process to execute the SQL table creation statement to create the corresponding partition table.

[0078] Among them, a partition table is created according to a preset partition strategy, a partition table name is obtained, an SQL table creation statement is generated according to the partition table name and the preset partition strategy, and a second process is called to execute the SQL table creation statement to create a corresponding partition table.

[0079] Creating a partitioned table using SQL statements helps maintain data integrity and accuracy.

[0080] In some optional implementations, the step of allocating the main table data to the partition table according to the preset partition rule includes:

[0081] Filter and sort the main table data according to the preset partitioning rules, and determine the partition table data to be allocated to the partition table;

[0082] Call the data writing function to write the partition table data into the corresponding partition table.

[0083] For example, if there are five main tables (table names are table1, table2, table3, table4, table5), all the five tables will be locked. After locking, the first process will release all locks of all main tables and obtain the main table list. The first process will access each main table and call the second process to create partition tables. For example, table1 creates partition tables such as table1_202301 and table1_202302 according to the date partition strategy. The partition rule may be based on the time field (such as created_at), and the data is allocated to the corresponding partition table by month. Using the created partition table name, the first process allocates the main table data to each partition table according to the preset rules. For example, read data from table1, and import the data of January into table1_202301 and the data of February into table1_202302 according to the created_at field.

[0084] By calling the data write function to write the partition table data into the corresponding partition table, partitioning can reduce the locking of the entire table by large transactions, thereby improving data consistency and concurrent access capabilities. At the same time, partition tables can perform data migration more flexibly.

[0085] In this embodiment, the main table data is allocated to the partition table according to the preset partitioning rules, and the results of the data allocation are recorded to obtain the partition table data sets corresponding to different partitions, record the completion status of the partition creation and data allocation, generate a partition creation completion instruction, and according to the partition creation completion instruction, the second process sends a notification to the first process to inform that the partition table creation and data allocation operations have been completed.

[0086] Step S203, in response to the partition creation completion instruction, calling the first process to reacquire all locks and perform a locking operation.

[0087] In this embodiment, before creating a partition, all locks held by the current transaction (i.e., the first process) on the main table are recorded and released. When creating a partition in another session (i.e., the second process), the current transaction enters a blocked state and waits for the partition creation to be completed. This method helps reduce lock contention and improves the concurrency performance and stability of the database.

[0088] After the partition is created, the first blocked process can obtain the previous locks based on the previously recorded lock information, and then reacquire these locks, that is, restore to the scene before the partition was created. In this way, after the partition is created, the subsequent process can continue.

[0089] In some optional implementations, the step of calling the first process to reacquire all locks and perform a locking operation in response to the partition creation completion instruction includes:

[0090] The partition creation completion instruction is parsed by the first process to obtain the instruction content;

[0091] Confirm the partition creation and data allocation completion status according to the instruction content, and call the first process to reacquire all locks of the main table;

[0092] After confirming that all locks are successfully acquired, relock all locks.

[0093] The first process receives the partition creation completion instruction, calls the first process, and obtains all the master table information to be partitioned through the first process. Based on the obtained master table information, the first process starts to reacquire all locks. If the first process successfully acquires all locks, the state of each lock is detected to ensure that all locks are in a locked state. If it is detected that a lock is not successfully locked, the first process releases all the acquired locks and performs the locking operation again.

[0094] By reacquiring the lock, it is ensured that the data has not been modified by other transactions during the partition creation process, and the data consistency is maintained after the operation is completed; the continuity and integrity of the transaction are maintained. By reacquiring the lock, the competition for data access in a high-concurrency environment is reduced, which helps to avoid potential data conflicts; quickly restoring the lock status after the partition creation is completed can speed up the execution of other transactions and improve the concurrent processing capability of the database.

[0095] In some optional implementations, after confirming that all locks are successfully acquired, the step of relocking all locks further includes:

[0096] Check the status of each lock. When all locks are locked successfully, perform data consistency check on the main table and partition table through the first process.

[0097] After the consistency check passes, restore normal access and operation of the main table after the partition is created.

[0098] When all locks are successfully locked, the first process accesses each main table and verifies the consistency of its data with the partition table data. If the data consistency verification passes, the current lock status and data consistency verification result are recorded. According to the recorded lock status and data consistency verification result, the first process generates lock completion information. After obtaining the lock completion information, the first process continues the operation before creating the partition.

[0099] Through consistency checking, the data consistency and integrity during the partition creation process are ensured, and the correct status of the main table and partition table after partitioning is obtained, providing protection for subsequent data operations and access.

[0100] This application releases all locks of the main table before creating a partition, so that other processes can still access the main table during the partition creation process, thereby improving the concurrency performance of the database. At the same time, since the locking time of the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience. In addition, by using another process to create a partition, the time range for the current transaction to hold a high-level lock is greatly shortened, and other sessions and other transactions can access the main table faster without being restricted, which greatly optimizes performance and customer experience.

[0101] In some optional implementations, before the step of sending the partition creation completion instruction to the first process, the method further includes:

[0102] After data allocation is completed, data query requests are received to determine whether the partition table and the main table are synchronized;

[0103] When the synchronization is completed, a step of sending a partition creation completion instruction to the first process is executed.

[0104] Among them, the database receives a data query request from an application or user, checks the data migration status according to the data query request, ensures that all data has been migrated to the corresponding partition, and verifies whether the records in the partition table are consistent with the records in the main table; when the records in the partition table are consistent with the records in the main table, determines that the partition table and the main table are synchronized; when the records in the partition table are inconsistent with the records in the main table, redirects the data query request to the main table, compares the data in the main table and the partition table, determines the inconsistent data, and repairs it.

[0105] By determining the synchronization between the partition table and the main table, the synchronized partition table can reduce the amount of data that must be scanned by the query and improve query performance. In addition, partitioning can reduce the locking of the entire table, thereby improving concurrent access capabilities and the overall performance of the system.

[0106] The database table partitioning method provided in this application can be applied to financial business. Financial business usually has clear time series characteristics. The partitioned table can adapt well to this characteristic and optimize data access through time partitioning. Secondly, the financial industry is facing digital transformation, and the flexibility and performance advantages of the partitioned table support the rapid innovation of financial products and services.

[0107] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0108] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through computer-readable instructions, and the computer-readable instructions can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the aforementioned storage medium can be a non-volatile storage medium such as a disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0109] It should be understood that, although the steps in the flowchart of the accompanying drawings are displayed in sequence as indicated by the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a part of the sub-steps or stages of other steps.

[0110] Further references Figure 3 , as a response to the above Figure 2The present application provides an embodiment of a database table partitioning device. Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0111] like Figure 3 As shown, the database table partitioning device 300 described in this embodiment includes: an acquisition module 301, a creation module 302 and a locking module 303. Among them:

[0112] The acquisition module 301 is used to receive a partition creation request, acquire all the main tables to be partitioned in the database according to the partition creation request through a first process, acquire all the locks of the main tables, and release all the locks;

[0113] The creation module 302 is used to access the main table, obtain the main table data, call the second process to create a partition table according to the preset partition strategy, and distribute the main table data to the partition table according to the preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process;

[0114] The locking module 303 is used for calling the first process to reacquire all the locks and perform a locking operation in response to the partition creation completion instruction.

[0115] It should be emphasized that in order to further ensure the privacy and security of all the master tables to be partitioned, all the master tables to be partitioned can also be stored in a node of a blockchain.

[0116] Based on the above-mentioned database table partitioning device 300, by releasing all locks of the main table before creating the partition, other processes can still access the main table during the partition creation process, thereby improving the concurrency performance of the database. At the same time, since the locking time of the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience; in addition, by using another process to create the partition, the time range for the current transaction to hold a high-level lock is greatly shortened, and other sessions and other transactions can access the main table faster without being restricted, thereby greatly optimizing performance and customer experience.

[0117] In some optional implementations, the acquisition module 301 is further used to:

[0118] Parsing the partition creation request by the first process to obtain request content, acquiring all the main tables to be partitioned in the database based on the request content, and obtaining a list of the main tables to be partitioned;

[0119] Traversing the main table list through the first process, obtaining all locks of each main table in the main table list, and reading lock information of each lock;

[0120] Determine whether the lock is a valid lock according to the lock information, and if the lock is a valid lock, release all the valid locks.

[0121] By releasing all locks on the main table, other sessions are allowed to access the main table in a shorter time, thereby improving the concurrency performance of the database. At the same time, since the lock time on the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience.

[0122] In some optional implementations, the creation module 302 includes a generation submodule and a creation submodule, wherein:

[0123] A generating submodule, used for generating an SQL table creation statement according to a preset partitioning strategy, and passing the SQL table creation statement to the second process;

[0124] A submodule is created to call the second process to execute the SQL table creation statement to create the corresponding partition table.

[0125] Creating a partitioned table using SQL statements helps maintain data integrity and accuracy.

[0126] In some optional implementations, the creation module 302 further includes an allocation submodule and a writing submodule, wherein:

[0127] An allocation submodule, used to screen and sort the main table data according to the preset partitioning rule, and determine the partition table data allocated to the partition table;

[0128] The writing submodule is used to call the data writing function to write the partition table data into the corresponding partition table.

[0129] By calling the data write function to write the partition table data into the corresponding partition table, partitioning can reduce the locking of the entire table by large transactions, thereby improving data consistency and concurrent access capabilities. At the same time, partition tables can perform data migration more flexibly.

[0130] In some optional implementations, the database table partitioning device 300 further includes a synchronization module, which is used to:

[0131] After the data allocation is completed, a data query request is received to determine whether the partition table is synchronized with the main table;

[0132] When the synchronization is completed, the step of sending a partition creation completion instruction to the first process is executed.

[0133] By determining the synchronization between the partition table and the main table, the synchronized partition table can reduce the amount of data that must be scanned by the query and improve query performance. In addition, partitioning can reduce the locking of the entire table, thereby improving concurrent access capabilities and the overall performance of the system.

[0134] In some optional implementations, the locking module 303 includes a parsing submodule, an acquiring submodule, and a relocking submodule, wherein:

[0135] A parsing submodule, used for parsing the partition creation completion instruction through the first process to obtain instruction content;

[0136] an acquisition submodule, configured to confirm the partition creation and data allocation completion status according to the instruction content, and call the first process to reacquire all locks of the main table;

[0137] The relocking submodule is used to relock all the locks after confirming that all the locks are successfully acquired.

[0138] By reacquiring the lock, it is ensured that the data has not been modified by other transactions during the partition creation process, and the data consistency is maintained after the operation is completed; the continuity and integrity of the transaction are maintained. By reacquiring the lock, the competition for data access in a high-concurrency environment is reduced, which helps to avoid potential data conflicts; quickly restoring the lock status after the partition creation is completed can speed up the execution of other transactions and improve the concurrent processing capability of the database.

[0139] In some optional implementations, the database table partitioning device 300 further includes a verification module, which is used to:

[0140] Detecting the status of each lock, and when all locks are successfully locked, performing data consistency check on the main table and the partition table through the first process;

[0141] After the consistency check passes, normal access and operation of the main table after the partition is created are restored.

[0142] Through consistency checking, the data consistency and integrity during the partition creation process are ensured, and the correct status of the main table and partition table after partitioning is obtained, providing protection for subsequent data operations and access.

[0143] To solve the above technical problems, the present application also provides a computer device. Figure 4 , Figure 4 This is a basic structural block diagram of the computer device in this embodiment.

[0144] The computer device 4 includes a memory 41, a processor 42, and a network interface 43 that are interconnected through a system bus. It should be noted that the figure only shows a computer device 4 with components 41-43, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (Application Specific Integrated Circuit, ASIC), programmable gate arrays (Field-Programmable Gate Array, FPGA), digital processors (Digital Signal Processor, DSP), embedded devices, etc.

[0145] The computer device may be a computing device such as a desktop computer, a notebook, a PDA, a cloud server, etc. The computer device may interact with a user through a keyboard, a mouse, a remote controller, a touch pad, or a voice control device.

[0146] The memory 41 includes at least one type of readable storage medium, and the readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 41 can be an internal storage unit of the computer device 4, such as a hard disk or memory of the computer device 4. In other embodiments, the memory 41 can also be an external storage device of the computer device 4, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (FlashCard), etc. equipped on the computer device 4. Of course, the memory 41 can also include both the internal storage unit of the computer device 4 and its external storage device. In this embodiment, the memory 41 is generally used to store the operating system and various application software installed on the computer device 4, such as computer-readable instructions of a database table partitioning method, etc. In addition, the memory 41 can also be used to temporarily store various types of data that have been output or are to be output.

[0147] The processor 42 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips in some embodiments. The processor 42 is generally used to control the overall operation of the computer device 4. In this embodiment, the processor 42 is used to run the computer-readable instructions stored in the memory 41 or process data, such as computer-readable instructions for running the database table partitioning method.

[0148] The network interface 43 may include a wireless network interface or a wired network interface. The network interface 43 is generally used to establish a communication connection between the computer device 4 and other electronic devices.

[0149] This embodiment implements the steps of the database table partitioning method of the above embodiment when the processor executes the computer-readable instructions stored in the memory. By releasing all locks of the main table before creating the partition, other processes can still access the main table during the partition creation process, thereby improving the concurrency performance of the database. At the same time, since the locking time of the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience. In addition, by using another process to create the partition, the time range for the current transaction to hold a high-level lock is greatly shortened, and other sessions and other transactions can access the main table faster without being restricted, thereby greatly optimizing performance and customer experience.

[0150] The present application also provides another implementation mode, namely, providing a computer-readable storage medium, wherein the computer-readable storage medium stores computer-readable instructions, and the computer-readable instructions can be executed by at least one processor so that the at least one processor executes the steps of the database table partitioning method as described above, and by releasing all locks of the main table before creating the partition, other processes can still access the main table during the partition creation process, thereby improving the concurrency performance of the database. At the same time, since the locking time of the main table is reduced, the user's waiting time is also reduced, thereby improving the user experience; in addition, by using another process to create the partition, the time range for the current transaction to hold a high-level lock is greatly shortened, and other sessions and other transactions can access the main table faster and are no longer restricted, thereby greatly optimizing the performance and customer experience.

[0151] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0152] Obviously, the embodiments described above are only some embodiments of the present application, rather than all embodiments. The preferred embodiments of the present application are given in the accompanying drawings, but they do not limit the patent scope of the present application. The present application can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present application more thorough and comprehensive. Although the present application is described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions recorded in the aforementioned specific implementation methods, or to perform equivalent replacement of some of the technical features therein. Any equivalent structure made using the contents of the specification and drawings of this application, directly or indirectly used in other related technical fields, is similarly within the scope of patent protection of this application.

Claims

1. A database table partitioning method, characterized in that: The steps include: Receive a partition creation request, obtain all master tables to be partitioned in the database according to the partition creation request through a first process, obtain all locks of the master tables, and release all the locks; Access the master table, obtain the master table data, call the second process to create a partition table according to a preset partition strategy, and distribute the master table data to the partition table according to a preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process; In response to the partition creation completion instruction, the first process is called to reacquire all the locks and perform a locking operation.

2. The database table partitioning method according to claim 1, characterized in that: The step of obtaining all the main tables to be partitioned in the database according to the partition creation request through the first process, obtaining all the locks of the main tables, and releasing all the locks comprises: Parsing the partition creation request by the first process to obtain request content, acquiring all the main tables to be partitioned in the database based on the request content, and obtaining a list of the main tables to be partitioned; Traversing the main table list through the first process, obtaining all locks of each main table in the main table list, and reading lock information of each lock; Determine whether the lock is a valid lock according to the lock information, and if the lock is a valid lock, release all the valid locks.

3. The database table partitioning method according to claim 1, characterized in that: The step of calling the second process to create a partition table according to a preset partition strategy includes: Generate an SQL table creation statement according to a preset partitioning strategy, and pass the SQL table creation statement to the second process; The second process is called to execute the SQL table creation statement to create the corresponding partition table.

4. The database table partitioning method according to claim 1, characterized in that: The step of allocating the main table data to the partition table according to the preset partition rule comprises: Screening and sorting the main table data according to the preset partitioning rule to determine the partition table data allocated to the partition table; Call the data writing function to write the partition table data into the corresponding partition table.

5. The database table partitioning method according to claim 1, characterized in that: Before the step of sending the partition creation completion instruction to the first process, the method further includes: After the data allocation is completed, a data query request is received to determine whether the partition table is synchronized with the main table; When the synchronization is completed, the step of sending a partition creation completion instruction to the first process is executed.

6. The database table partitioning method according to claim 1, characterized in that: The step of calling the first process to reacquire all the locks and performing a locking operation in response to the partition creation completion instruction includes: Parsing the partition creation completion instruction by the first process to obtain instruction content; Confirming the partition creation and data allocation completion status according to the instruction content, and calling the first process to reacquire all locks of the main table; After confirming that all the locks are successfully acquired, relocking operations are performed on all the locks.

7. The database table partitioning method according to claim 6, characterized in that: After confirming that all the locks are successfully acquired, the step of relocking all the locks further includes: Detecting the status of each lock, and when all locks are successfully locked, performing data consistency check on the main table and the partition table through the first process; After the consistency check passes, normal access and operation of the main table after the partition is created are restored.

8. A database table partitioning device, characterized in that: include: An acquisition module, configured to receive a partition creation request, acquire all master tables to be partitioned in the database according to the partition creation request through a first process, acquire all locks of the master tables, and release all the locks; A creation module, used to access the master table, obtain the master table data, call the second process to create a partition table according to a preset partition strategy, and distribute the master table data to the partition table according to a preset partition rule to obtain different partitions, and send a partition creation completion instruction to the first process; The locking module is used to call the first process to reacquire all the locks and perform a locking operation in response to the partition creation completion instruction.

9. A computer device, comprising a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the database table partitioning method according to any one of claims 1 to 7 when executing the computer-readable instructions.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by a processor, the steps of the database table partitioning method according to any one of claims 1 to 7 are implemented.

Images