CN112835887A

CN112835887A - Database management method, database management device, computing equipment and storage medium

Info

Publication number: CN112835887A
Application number: CN201911167443.9A
Authority: CN
Inventors: 孟媛媛; 耿方; 杜悦琨; 董亮亮
Original assignee: Aisino Corp
Current assignee: Aisino Corp
Priority date: 2019-11-25
Filing date: 2019-11-25
Publication date: 2021-05-25

Abstract

The application discloses a database management method, a database management device, a computing device and a storage medium. The method comprises the steps that at least one type of business data is stored in an Oracle database, each type of business data is divided into at least one partition table to be stored, each partition table corresponds to one table space, different partition tables of the same type of business data have the same table name and different table serial numbers, the partition tables of different types of business data have different table names, each table name and the maximum table serial number associated with the table name are maintained in a cache database, and increment partition is realized by monitoring the space capacity increase condition of the table space corresponding to the maximum table serial number associated with each table name so as to reduce the subsequent maintenance cost. On the basis, a read-write separation mode is also provided to improve the data query efficiency.

Description

Database management method, database management device, computing equipment and storage medium

Technical Field

The present application relates to the field of database technologies, and in particular, to a database management method, an apparatus, a computing device, and a storage medium.

Background

With the development of information technology, the dependence of various industries on information systems is increasing. As an important component of enterprise information systems, database management has become an essential part of system maintenance.

The Oracle database is an internationally leading database software, and is widely applied to the core Business field of various industries, such as Customer Relationship Management (CRM) of telecommunication operators and Business Operation Support System (BOSS) System; various Operation Data Storage (ODS) application systems for various big banks and the like; an identity card management system; a customs system; a Hospital Information System (HIS for short) in the third Hospital in each city.

While the Oracle database is widely applied, the data volume stored in the database is increased day by day, and the increase of the data volume not only improves the value of the data to enterprises, but also brings more challenges to developers in the aspects of database design and implementation. In this regard, how to improve the database management scheme to reduce the maintenance cost and improve the query performance of the database, and enhance the availability of the business system becomes a technical problem to be solved urgently.

Disclosure of Invention

The embodiment of the application provides a database management method, a database management device, a computing device and a storage medium, so that the maintenance cost is reduced, the query performance of a database is improved, and the service system availability is enhanced.

In a first aspect, an embodiment of the present application provides a database management method based on an Oracle database, where at least one type of service data is stored in the Oracle database, each type of service data is divided into at least one partition table for storage, each partition table corresponds to one table space, different partition tables of the same type of service data have the same table name and different table sequence numbers, the partition tables of different types of service data have different table names, and each table name and its associated maximum table sequence number are maintained in a cache database, the method includes:

obtaining each table name and the associated maximum table serial number from the cache database;

acquiring the space capacity of the table space corresponding to the partition table with the maximum table serial number from the Oracle database;

and if the space capacity of the table space of any partition table is larger than a preset capacity threshold value, creating a new table space and a corresponding partition table for the business data of the type to which the partition table belongs, and updating the maximum table serial number associated with the table name corresponding to the new partition table in the cache database.

Optionally, obtaining the space capacity of the table space corresponding to the partition table of each maximum table sequence number from the Oracle database includes:

requesting a server where the Oracle database is located to acquire the space capacity of a table space based on a viewing command, wherein the viewing command comprises table names and associated maximum table serial numbers acquired from the cache database;

and analyzing the character strings returned by the server where the Oracle database is positioned in response to the viewing command so as to obtain the space capacity of the table space corresponding to the partition table with the maximum table serial number.

Optionally, the view command is a du command.

Optionally, if the space capacity of the table space of any partition table is greater than a preset capacity threshold, creating a new table space and a corresponding partition table for the service data of the type to which the table space of any partition table belongs, including:

acquiring database information corresponding to the service data of the type to which the partition table belongs, wherein the database information comprises table names, file paths and main key information which are defined for different types of service data of the Oracle database in advance;

creating a new table space in the Oracle database based on a file path and a creation rule corresponding to the business data of the type to which the table space of any partition table belongs, wherein the partition table corresponding to the new table space is stored under the file path;

and creating a corresponding partition table for the new table space based on the primary key information, wherein the table name of the new partition table is the same as that of any partition table, and the table serial number of the new partition table is the table serial number of any partition table plus a preset value.

Optionally, the method further includes:

responding to a read request for reading data, and acquiring the data requested by the read request from a slave database of the Oracle database;

and responding to a data updating request, executing data updating operation in a partition table corresponding to the data updating request, and synchronously updating changed data to a slave database of the Oracle database after the updating is successful.

Optionally, if the data update request is a request for new data, executing a data update operation in a partition table corresponding to the data update request, including:

determining a table name corresponding to the type of the data to be newly added, and acquiring a maximum table sequence number associated with the table name of the data to be newly added from the cache database;

and setting a table identification ID field of the data to be newly added as the table name and the maximum table serial number, writing the data to be newly added into a partition table corresponding to the maximum table serial number, wherein the table identification ID field is a field which is maintained for the data to be newly added and is used for determining the partition table to which the data to be newly added belongs, and the table identification ID field comprises the table name and the table serial number of the partition table to which the data to be newly added belongs.

Optionally, if the data update request is a request for existing data, executing a data update operation in a partition table corresponding to the data update request, including:

inquiring a table identification ID field of the existing data to determine a partition table where the existing data is located, wherein the table identification ID field is a field which is maintained for the existing data and is used for determining the partition table where the existing data belongs, and the table identification ID field comprises a table name and a table sequence number of the partition table where the existing data belongs;

and performing data rewriting operation on the existing data in the partition table where the existing data is located.

Optionally, if the changed data is not updated to the slave database of the Oracle database in a synchronous manner successfully, the step of executing the data updating operation in the partition table corresponding to the data updating request is repeatedly executed;

if the synchronous updating is not successful after the repeated execution for the preset times, returning a synchronous failure prompt to the user client so that the user resubmits the data updating request;

the data updating request aiming at the same data is repeatedly executed for a plurality of times, and the corresponding data execution results are the same.

In a second aspect, an embodiment of the present application provides a database management device based on an Oracle database, where at least one type of service data is stored in the Oracle database, each type of service data is divided into at least one partition table for storage, each partition table corresponds to one table space, different partition tables of the same type of service data have the same table name and different table serial numbers, the table names of the partition tables of different types of service data are different, and each table name and its associated maximum table serial number are maintained in a cache database, where the device includes:

a table sequence number obtaining unit, configured to obtain each table name and a maximum table sequence number associated with the table name from the cache database;

the space capacity acquisition unit is used for acquiring the space capacity of the table space corresponding to the partition table with the maximum table serial number from the Oracle database;

and the partition creating unit is used for creating a new tablespace and a corresponding partition table for the business data of the type of any partition table if the space capacity of the tablespace of the partition table is larger than a preset capacity threshold value, and updating the maximum table serial number associated with the table name corresponding to the new partition table in the cache database.

In a third aspect, an embodiment of the present application further provides a computing device, including at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executable by the at least one processor to enable the at least one processor to perform any of the database management methods provided by the embodiments of the present application.

In a fourth aspect, the present application further provides a computer storage medium, where the computer storage medium stores computer-executable instructions, and the computer-executable instructions are configured to cause a computer to execute any database management method in the present application.

The database management method, the database management device, the computing equipment and the storage medium provided by the embodiment of the application can monitor the increase condition of each type of table space corresponding to a business system, and create a new partition table when the capacity of the table space increases to reach a preset capacity threshold value, so that the problem of high maintenance cost caused by a traditional partition mode is solved through an incremental partition mode, the follow-up capacity expansion operation is realized, and the economic loss caused by service suspension in the traditional capacity expansion mode can be avoided. On the basis, a data read-write separation mode is also provided, so that the data query efficiency is improved, and the problem that the Oracle query speed is not ideal under the condition of supporting mass data is solved.

Additional features and advantages of the application will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by the practice of the application. The objectives and other advantages of the application may be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.

Drawings

In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings needed to be used in the embodiments of the present invention will be briefly described below, and it is obvious that the drawings described below are only some embodiments of the present invention, and it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without creative efforts.

FIG. 1 is a schematic diagram of a system architecture according to one embodiment of the present application;

FIG. 2 is a schematic flow diagram of a database management method according to one embodiment of the present application;

FIG. 3 is a schematic flow chart illustrating the process of obtaining tablespace capacity according to one embodiment of the present application;

FIG. 4 is a schematic flow chart diagram illustrating the creation of a new partition table according to one embodiment of the present application;

FIG. 5 is a schematic flow diagram of a database management method according to one embodiment of the present application;

FIG. 6 is a schematic diagram of a data management process based on read-write separation according to an embodiment of the present application;

FIG. 7 is a schematic diagram of a database management apparatus according to one embodiment of the present application;

FIG. 8 is a schematic diagram of a computing device according to one embodiment of the present application.

Detailed Description

In order to clearly understand the technical solutions provided by the embodiments of the present application, the following terms appearing in the embodiments of the present application are explained, it should be noted that the terms in the embodiments of the present application are only explained to facilitate understanding of the present application, and are not used to limit the present application, and the terms include:

oracle database: oracle Database, also known as Oracle RDBMS, or simply Oracle. The Oracle database is a relational database system, is the most popular relational database system in the world at present, has good system portability, convenient use and strong function, and is suitable for various large, medium, small and microcomputer environments. The Oracle database is a database scheme which has high efficiency and good reliability and is suitable for high throughput.

Data table and data table structure: the data table is composed of three parts of a table name, fields in the table and records of the table. The data table structure describes a framework of a data table. The structure of the data table is designed before the data table is established. Designing a data table structure is essentially defining the data table file name, determining which fields the data table contains, the field names, field types, and widths of the fields, and inputting these data into the computer.

Table space: the Oracle database is divided into logical regions called tablespaces-the logical structures that form the Oracle database. A tablespace refers to a collection of one or more data files, all of which are stored in a designated tablespace, but primarily store tables, and are therefore referred to as tablespaces. An Oracle database can have one or more tablespaces, with one tablespace corresponding to one or more physical database files. Tablespaces are the smallest unit of Oracle database recovery, holding many database entities such as tables, views, indexes, clusters, fallback and temporary segments, etc.

Partitioning: a design technique of physical database aims to reduce the total amount of data read and write in specific SQL operation so as to reduce the response time. The data of the table can be balanced to different places by the partition, the data retrieval efficiency is improved, and the frequent IO pressure value of the database is reduced.

And (4) partitioning a table: the data of a large table is divided into many small subsets called partitions. The partition table may create its unique partition index. If the partition table is lost from the hard disk, data cannot be read and written in sequence, resulting in inoperability.

Physical file: the method comprises the following steps of (1) data file: the data in the database is physically stored in operating system files, which are data files, usually files with the suffix of.dbf. (2) Controlling a file: each Oracle database has a corresponding control file, which is a binary file that defines the state of the database. (3) Redoing the log file: and the database recovery method is used for recording all changes (such as addition, deletion, modification and the like) made to the database so as to recover the database based on the redo log file when the system fails.

As described above, with the development of information technology, the degree of dependence of various industries on information systems is increasing. As an important component of enterprise information systems, database management has become an essential part of system maintenance.

While the Oracle database is widely applied, the data volume stored in the database is increased day by day, and the increase of the data volume not only improves the value of the data to enterprises, but also brings more challenges to developers in the aspects of database design and implementation.

For example, when the amount of data in the Oracle data table is increasing, the speed of querying the data becomes slow, and the overall performance of the system is degraded, and the data table needs to be partitioned. After the data table is partitioned, the data table is still logically a complete table, and only the data in the table is physically stored in a plurality of table spaces (physical files). Thus, when data is queried, the whole data table does not need to be scanned every time. The partition can improve the query performance of the database, enhance the usability, facilitate the maintenance and balance the I/O.

Conventional table partition forms include Range partitions, list partitions, hash partitions, combined partitions, and the like. Usually, log data generated by a service system takes a partition form based on a time range, most service data needs to be hashed or listed and partitioned based on ID, category, and the like, and the partition mode needs to determine the number of partition tables before partitioning to perform partitioning operation, and the partition mode brings new problems. With the increase of system traffic, the data volume in each partition table also increases, and when the size of a single partition table exceeds a predetermined threshold (for example, 2GB), the query speed of data decreases again, and it is necessary for developers to stop service and perform capacity expansion, that is, to perform migration of part or all of partition table data. The operation can greatly improve the later maintenance cost, and the loss caused by stopping service of the service system of a large number of users on the support line can not be estimated.

In this regard, how to improve the database management scheme to reduce the maintenance cost and improve the query performance of the database, and enhance the availability of the business system becomes a technical problem to be solved urgently.

In view of this, embodiments of the present application provide a database management method and apparatus, which can monitor the increase condition of each type of tablespace corresponding to a business system, and create a new partition table when the capacity of the tablespace increases to reach a predetermined capacity threshold, so that by means of incremental partitioning, a higher maintenance cost caused by a traditional partitioning manner is solved, a subsequent capacity expansion operation is implemented, and an economic loss caused by suspension of service in the traditional capacity expansion manner can be avoided. On the basis, a data read-write separation mode is also provided, so that the data query efficiency is improved, and the problem that the Oracle query speed is not ideal under the condition of supporting mass data is solved.

In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention.

System architecture

FIG. 1 is a system architecture diagram according to one embodiment of the present application.

As illustrated in fig. 1, the system of the present application may include a database system 10, a management module 20, and a business system 30.

The database system 10 may include an Oracle database 11, a cache database 12, and a slave database 13 of the Oracle database 11. The database system 10 may provide database services, such as data storage services, etc., to the business system 30 via the management module 20.

It should be understood that although the management module 20 is separately disposed in fig. 1, those skilled in the art should understand that in other embodiments, the management module 20 may also be configured in the database system 10 or the business system 30, and implement the cooperative management of the slave database 13, for example, the Oracle database 11, the cache database 12, and the Oracle database 11, and provide the database service for the business system 30, which is not limited in this application.

The Oracle database 11, the cache database 12, and the slave database 13 of the Oracle database 11 may constitute an entire database system of the database system, and may provide related database services for the business system, such as storing business data.

The Oracle database 11 may store at least one type of service data, each type of service data is divided into at least one partition table for storage, each partition table corresponds to one table space, different partition tables of the same type of service data have the same table name and different table sequence numbers, and the partition tables of different types of service data have different table names.

The management module 20 may store a program code, based on which the management of the whole database system can be realized, and the management module may mainly complete the operation and management functions of the database system, such as creation of a database object, query of database storage data, addition, modification and deletion operations, user management of the database, authority management, and the like, and provide corresponding services for a service system associated with the database system. The management module 20 may further include different sub-modules, and implement corresponding management functions via the different sub-modules, for example, the partition module implements the partition function, and the read-write module implements read-write management on the service data, which is not described herein again.

The database management scheme of the embodiment of the application mainly relates to a partition table management scheme based on an increment partition mode and a data management scheme based on a read-write separation mode. The following description and examples are described with reference to the accompanying drawings.

Partition table management based on incremental partition mode

In one embodiment, the partition table may be named in the form of "table name + table serial number", the physical file corresponding to the partition table may be named in the form of "table name + table serial number, dbf", where the table serial number of each type of service data is initially "0", the table serial number is increased with the increase of the number of the partition table, the partition table with the largest table serial number may be used for the service system to perform a new addition operation, and the increase condition of the table space may be monitored by the management module 20 in real time. Each table name and the maximum table sequence number associated with the table name may be maintained in the cache database, and the management module 20 may monitor the table space size of the partition table corresponding to the maximum table sequence number corresponding to different types of service data at regular time, and dynamically create a new partition table when the table space size increases beyond a preset capacity threshold, so as to allow a service system to perform a new addition operation. Therefore, when the capacity of a single partition table exceeds a capacity threshold value, capacity expansion can be achieved without suspending service, the later maintenance cost is not increased, economic loss caused by suspending service can be avoided, and the method is more friendly to business systems of mass users on a support line.

FIG. 2 is a schematic flow diagram of a database management method according to an embodiment of the present application. The method steps mainly relate to a partitioning process in database management, and the process may be executed by the management module 20 shown in fig. 1 or a sub-module thereof (e.g., a partitioning module, not shown in fig. 1), which is not limited in this application.

As shown in fig. 2, in step S210, each table name and its associated maximum table sequence number may be obtained from the cache database.

In this embodiment, one type of service data may correspond to one table structure, one table structure may correspond to one table name and a plurality of partition tables, and each partition table of the same table structure has the same table name and its corresponding table serial number. The maximum table sequence numbers corresponding to the partition tables of different table structures may be maintained by a cache database (e.g., a Redis cache database or other types of cache data, which is not limited in this application). In the cache database, for example, the table name and the maximum table number associated therewith may be stored in a "key" to value "relationship, that is, the maximum table number information corresponding to all table structures may be stored in a" table name to maximum table number "format.

In step S210, the maximum table serial numbers corresponding to all the table structures may be obtained from the cache database, and a name list of the partition table to be monitored is generated by splicing. The name list includes names of all partition tables to be monitored of the Oracle database, i.e., "table name-table serial number", where the partition table to be monitored is a partition table with different table structures corresponding to the largest table serial number. Based on the name list, management module 20 may lock a tablespace corresponding to the partition table to be monitored.

Then, in step S220, the space capacity of the table space corresponding to the partition table with the largest table number can be obtained from the Oracle database.

In one embodiment, the management module 20 may obtain the space capacity of the table space corresponding to the partition table with the largest table sequence number by executing the stored related program codes regularly, for example. Specifically, the management module 20 may start a monitoring thread, scan a name list of the partition table to be monitored, periodically calculate the size of the tablespace of each partition table to be monitored, and obtain the capacity of each tablespace.

FIG. 3 is a flowchart illustrating a process of obtaining tablespace capacity according to an embodiment of the present application. The process may be executed by the management module 20 shown in fig. 1 or a sub-module thereof (e.g., a partition module, not shown in fig. 1), which is not limited in this application.

As shown in fig. 3, in step S221, the management module 20 may connect to a server where the Oracle database is located. The management module 20 may be, for example, remotely connected to the server where the Oracle database is located through a Secure Shell (SSH), which is not limited in the present application.

In step S222, a request is made to a server where the Oracle database is located to obtain a space capacity of a table space based on a viewing command, where the viewing command includes each table name and its associated maximum table sequence number obtained from the cache database. The view command may be, for example, a du command, and the command line corresponding to the view command may be "du-s-m" + "the path of the partition table corresponding to the physical file.

Then, the management module 20 may receive that the server where the Oracle database is located returns file information in response to the view command. The file information may be a character string in the form of "physical file name" + "\ t" + "file size", for example.

In step S223, the management module 20 may parse the character string returned by the server where the Oracle database is located in response to the view command, so as to obtain the space capacity of the table space corresponding to the partition table with the largest table serial number. As previously described, a tablespace may correspond to one or more physical database files. After parsing the character string in step S223, the management module 20 may further calculate a sum of sizes of a plurality of files belonging to the same tablespace, which is a current space capacity of the tablespace.

For each partition table to be monitored in the name list of the partition table to be monitored, after the table space capacity of any partition table to be monitored is obtained, the table space capacity of the partition table to be monitored can be compared with a preset capacity threshold (for example, 2GB) to judge the capacity increase condition of the table space.

As previously mentioned, as system traffic increases, the amount of data in each partition table also increases. When the space capacity of the table space of the partition table is larger than (i.e., exceeds) a preset capacity threshold (e.g., 2GB), the query speed of the data may be reduced and may cause a reduction in the overall performance of the system. In order to guarantee the query speed of data and the overall performance of the system, the data table needs to be partitioned.

Returning to fig. 1, in step S230, if the space capacity of the table space of any partition table in the partition table to be monitored is greater than the preset capacity threshold, a new table space and a corresponding partition table are created for the service data of the type to which the partition table belongs, and the maximum table serial number associated with the table name corresponding to the new partition table is updated in the cache database. If the partition table with the table space capacity exceeding the preset capacity threshold is not detected in step S230, the monitoring may be ended, and a next monitoring period is waited.

Therefore, the table space growth condition of each partition table of the Oracle database can be continuously monitored, a new partition table is dynamically created, dynamic expansion of different types of business data is achieved, maintenance cost is not increased, and economic loss caused by expansion under a condition that developers pause service lines in the prior art can be avoided.

FIG. 4 is a schematic flow chart illustrating the creation of a new partition table according to one embodiment of the present application. The process may be executed by the management module 20 shown in fig. 1 or a sub-module thereof (e.g., a partition module, not shown in fig. 1), which is not limited in this application.

As shown in fig. 4, in step S231, database information corresponding to the service data of the type to which the partition table belongs is obtained.

Here, any partition table is the partition table to be monitored, where the table space capacity detected in the foregoing step S220 is greater than the preset capacity threshold, and the partition table and the new partition table to be created correspond to the same type of service data. In other words, in the embodiment of the present application, when it is detected that the table space capacity of the partition table corresponding to the maximum table serial number of a certain data type exceeds the preset capacity threshold, a new partition table is created for the data type, so that a service system performs a new addition operation.

The database information may include, for example, table names, file paths, primary key information, creation rules, and the like, which are defined in advance for different types of business data of the Oracle database. Based on the obtained database information, it can be known how to create new table spaces and table structures for the corresponding types of business data applications.

In one embodiment, the database information may be obtained, for example, from a data dictionary of an Oracle database. The data dictionary (data dictionary) of the Oracle database is usually created when creating and installing the database, and is a collection of descriptions of data objects or items in the data model, and describes the logical contents of data streams and data stores through definitions of data items and data structures. The data dictionary has another meaning, namely a tool used in the design of the database, is used for describing the design of basic tables in the database, and mainly comprises field names, data types, primary keys, foreign keys and the like which describe the contents of attributes of the tables.

After the database information is obtained, in step S232, the management module 20 may load an Oracle database driver to connect the Oracle database, and create a new table space in the Oracle database based on a file path and a creation rule corresponding to the service data of the type to which the table space of any partition table belongs, where a physical file corresponding to the new table space is stored in the file path.

In one embodiment, management module 20 may create a new tablespace by, for example, executing a statement that creates a tablespace (e.g., an SQL statement).

The created tablespace can be set to be fixed in initial space capacity and can be automatically expanded. As an example, in the embodiment of the present application, for example, the initial space capacity of the created new tablespace may be set to 1GB, and the new tablespace may be made to grow in space capacity by 20M at a time. After creating the tablespace, the management module 20 may also give the rights of the tablespace, such as connection rights (CONNECT), creation entity rights (RESOURCE), and the like, to the operating user, that is, the service data of the type to which the operating user belongs.

Then, in step S233, the management module 20 creates a corresponding partition table for the new table space based on the primary key information and the creation rule, and gives operation permissions of adding, querying, updating, deleting, and the like to the partition table to the operation user, that is, the service data of the type to which the partition table belongs. The table name of the new partition table is the same as that of any partition table, and the table serial number of the new partition table is the table serial number of any partition table added with a preset value. Thus, when the business system desires to perform a new operation for the type of business data, the corresponding operation can be performed based on the newly created table space and partition table.

Therefore, through the scheme, when the table space capacity of the partition table corresponding to the maximum table serial number of certain type of service data exceeds the preset capacity threshold value, the embodiment of the application can dynamically create a new table space and a new partition table for the type of service data, so that services can be provided for carrying out new addition operation on the type of service data based on the newly created table space and the newly created partition table, the speed of querying data is not slowed down due to the increase of data volume, unnecessary economic loss caused by the suspension of service for capacity expansion is also avoided, and various defects existing in the traditional partition mode can be overcome.

The incremental partition-based database management scheme of the present application has been described in detail with reference to fig. 2-4 and the embodiments.

Data management based on read-write separation mode

Returning to fig. 1, in another embodiment, based on the Oracle database 11, the cache database 12, and the database hierarchy of the Oracle database 11 and the slave database 13, the management module 20 may further provide a read-write separation mode to implement data access and/or data storage management for the entire database hierarchy.

FIG. 5 is a flowchart illustrating a database management method according to an embodiment of the present application. The method mainly relates to a flow of data access and/or data storage in database management, and the flow may be executed by the management module 20 shown in fig. 1 or a sub-module thereof (e.g., a read-write module, shown in fig. 1), which is not limited in this application.

As shown in fig. 5, in step S510, in response to a read request for reading data, the data requested by the read request is obtained from the slave database of the Oracle database.

In step S520, in response to the data update request, data update operation is performed in the partition table corresponding to the data update request, and after the data update is successful, the changed data is updated to the slave database of the Oracle database synchronously.

In one embodiment, the slave database of the Oracle database may be, for example, a non-relational database, such as a MongoDB database. Therefore, the overall query performance of the system can be improved by virtue of the advantage of fast data reading and writing of the non-relational database. Since the Oracle database is a relational database and is a different database type from the non-relational database, such as the MongoDB database, in this embodiment, the non-relational database may also be referred to as a "logical slave database" of the Oracle database.

Therefore, through the logic slave database of the Oracle database and the read-write separation mode, when the business system executes write operation, the Oracle database is updated firstly, and if the update is successful, the slave database of the Oracle database is updated. When the business system executes the reading operation, the data is directly read from the slave database of the Oracle database. The scheme can improve the data query efficiency and solve the problem that the query speed of the Oracle database is not ideal under the condition of supporting mass data.

Returning to fig. 1, as mentioned above, since at least one type of business data can be stored in the Oracle database, each type of business data is divided into at least one partition table for storage. In the embodiment of the application, for each piece of business data, a table identification ID field may be maintained, so that the partition table where the piece of business data is located can be accurately located according to the table identification ID field, so as to perform corresponding data access or data storage management. In one embodiment, the table ID field may include a table name and a table sequence number corresponding to the partition table to which the piece of business data belongs.

FIG. 6 is a schematic diagram of a data management process based on read-write separation according to an embodiment of the present application.

As shown in fig. 6, in step S601, a database operation request is received, where the database operation request may be used to request a series of operations performed on data on a database, including reading data, writing data, i.e., adding data, updating or modifying data, deleting data, and the like.

In step S602, the management module 20 may determine the type of the received database operation request. For example, the operation type corresponding to the database operation request is determined by identifying an operation statement (e.g., SQL statement).

If the read request is identified as a read request for reading data, in step S603, the management module 20 may respond to the read request for reading data, and obtain data requested by the read request from the slave database of the Oracle database. Here, the read request may include a related query parameter submitted by the front end of the business system, and based on the related query parameter, the business data meeting the related query parameter may be queried from a slave database of the Oracle database, for example, a MongoDB database.

If the data update request is identified, in step S604, the management module 20 may respond to the data update request and perform a data update operation in the partition table corresponding to the data update request. And after the update is successful, the changed data is synchronously updated to the slave database of the Oracle database (see step S611 below).

In the embodiment of the present application, requests triggered by write operations such as adding data to a database, updating or modifying data, and deleting data may be collectively referred to as data update requests (or write requests). The processing response performed at step S604 is not exactly the same for each different write operation. In the embodiment of the application, the Oracle database is configured to, if data is requested to be added newly in the Oracle database, write the data to be added newly into a partition table corresponding to the largest table sequence number in the partition table of the type to which the data belongs; and if the existing data in the Oracle database is requested to be updated, modified or deleted, and the like, executing corresponding rewriting operation in the partition table where the existing data is located.

In implementation, for example, if the data update request is a request for new data, in step S604, the method may specifically include: in step S606, a table name corresponding to the type of the newly added data is determined, and the maximum table sequence number associated with the table name of the newly added data is obtained from the cache database. In step S607, the table identifier ID field of the newly added data is set as the table name and the maximum table sequence number, and the newly added data is written into the partition table corresponding to the maximum table sequence number. The table identification ID field is a field which is maintained for the data to be newly added and is used for determining a partition table to which the data to be newly added belongs, and the table identification ID field comprises a table name and a table sequence number of the partition table to which the data to be newly added belongs.

In other words, in the embodiment of the present application, for a request for adding new data to a service system, a table name corresponding to the new data may be determined according to a type of the data to be added, and a maximum table sequence number corresponding to the table name may be obtained from a cache database (e.g., Redis). And then, setting the table identification ID field of the newly added data as the determined table name and the maximum table sequence number, and writing the data to be newly added into the partition table corresponding to the maximum table sequence number.

For another example, if the data update request is a request for existing data, in step S604, the method specifically includes: in step S608, a table identifier ID field of the existing data is queried to determine a partition table where the existing data is located, where the table identifier ID field is a field maintained for the existing data and used for determining the partition table to which the existing data belongs, and the table identifier ID field includes a table name and a table sequence number of the partition table to which the existing data belongs; in step S609, a data rewrite operation is performed on the existing data in the partition table in which the existing data is located.

In other words, in the embodiment of the present application, when the service system performs a data update operation, the partition table where the data corresponding to the data update operation is located is determined first, and then the data update operation is performed in the partition table where the data is located.

The request for the existing data may further include a deletion operation for the existing data. In this embodiment of the application, most of the data deletion operations in the service system are logical deletion, that is, the data deletion operations are implemented by modifying the deletion flag bits corresponding to the data, so the data update operations executed in step S609 may include both the update/modification operations on the existing data and the deletion flag bit operations on the data, which is not described herein again.

As described above, in the embodiment of the present application, after the database operation for the Oracle database is successful, the data of the occurrence table is synchronously updated to the slave database of the Oracle database, for example, the non-relational database, MongoDB. The corresponding data synchronization operation may be the same operation performed in the slave database of the Oracle database as the data update operation performed in the Oracle database.

Returning to FIG. 6, in step S610, it may be determined whether the database operation for the Oracle database was successfully performed. If the execution is successful, step S611 is entered, and the data in the occurrence table is synchronously updated to the slave database of the Oracle database. If the execution fails, step S612 is entered, and an exception prompt is fed back, for example, an operation failure result is returned to the front end of the service system, and the data processing flow is ended.

When data synchronization is performed between the Oracle database and the slave database, the operations in the Oracle database may be successfully executed, and the operations in the slave database may be failed to be executed. Returning to fig. 6, in step S613, it is determined whether the operation was successfully performed in the slave database, for example, MongoDB.

If the execution is successful, the data processing flow is ended.

If the execution fails, step S614 is performed, and the step of performing the data updating operation in the partition table corresponding to the data updating request is repeatedly performed, so as to synchronously update the data changed in the Oracle database to the slave database of the Oracle database. That is, the same operation as the data update operation performed in the Oracle database is repeatedly performed a predetermined number of times in the slave database of the Oracle database. Here, the predetermined number of times may be repeated. The predetermined number of times may be, for example, greater than or equal to N times, where N is an integer greater than 1 (empirically, N may be set, for example, to 3-5 times, and this application is not limited thereto). Therefore, the data updating operation is repeatedly executed in the slave database, so that abnormal problems caused by external environments such as network transmission and the like are prevented, and the data consistency between the Oracle database and the slave database is guaranteed.

If the operation fails after repeating the execution from the database, such as MongoDB, N times, step S615 may be entered, and the exception may be fed back to the front-end page of the business system, so as to prompt the user to resubmit the operation request, i.e., the data update request. Meanwhile, in step S616, the exception is recorded in an error log (e.g., the redo log file) to provide support for the subsequent completion of the system exception handling process. Then, the data processing flow is ended.

Taking the slave database MongoDB as an example, since the MongoDB is a non-relational database, the writing operation for the data is actually a file-based writing operation. Therefore, compared with the relational database such as Oracle, the probability that the MongoDB still fails after repeatedly executing the writing operation is extremely low, but in order to ensure that the data in the two types of databases are consistent, the Mongodb can be configured in advance, and once the Mongodb still fails after repeatedly executing the writing operation for N times from the databases, the Mongodb needs to prompt the user to resubmit the operation request to the front-end page of the business system. In terms of system functional design, it is also necessary to ensure that the functional design related to the write operation has "idempotency", that is, the data execution result is the same after the operation requests with the same parameters are repeatedly executed. That is, the results corresponding to operations repeatedly performed from the database for the same operation are the same.

Therefore, the overall performance of the business system is guaranteed through the exception handling process.

So far, the read-write separation based database management scheme of the present application has been described in detail with reference to fig. 6. By using a non-relational database such as MongoDB as a logical slave database of the Oracle database, a read-write separation mode can be realized, and the data query efficiency can be improved by virtue of the advantage of fast data reading and writing of the non-relational database, thereby improving the overall performance of a service system.

Based on the same conception, the application also provides a database management device based on the Oracle database.

Fig. 7 is a schematic diagram of a database management apparatus according to an embodiment of the present application. The Oracle database stores at least one type of business data, each type of business data is divided into at least one partition table for storage, each partition table corresponds to one table space, different partition tables of the same type of business data have the same table name and different table serial numbers, the partition tables of different types of business data have different table names, and each table name and the associated maximum table serial number are maintained in the cache database.

As shown in fig. 7, the database management apparatus 700 may include:

a table sequence number obtaining unit 710, configured to obtain each table name and the maximum table sequence number associated with the table name from the cache database.

And a space capacity obtaining unit 720, configured to obtain, from the Oracle database, the space capacity of the table space corresponding to the partition table with the largest table number.

The partition creating unit 730 is configured to create a new tablespace and a corresponding partition table for the service data of the type to which any partition table belongs if the space capacity of the tablespace of the partition table is greater than a preset capacity threshold, and update the maximum table sequence number associated with the table name corresponding to the new partition table in the cache database.

In one embodiment, the space capacity obtaining unit 720 is configured to:

connecting a server where the Oracle database is located;

requesting a server where the Oracle database is located to acquire the space capacity of a table space based on a viewing command, wherein the viewing command comprises table names and associated maximum table serial numbers thereof acquired from the cache database;

In one embodiment, the view command is a du command.

In one embodiment, the partition creating unit 730 is configured to:

acquiring database information corresponding to the service data of the type to which the partition table belongs, wherein the database information comprises table names, file paths, primary key information and creation rules which are defined for different types of service data of the Oracle database in advance;

loading an Oracle database driver to connect with the Oracle database, and creating a new table space in the Oracle database based on a file path and a creation rule corresponding to the business data of the type to which the table space of any partition table belongs, wherein a physical file corresponding to the new table space is stored under the file path;

and creating a corresponding partition table for the new table space based on the primary key information and the creation rule, wherein the table name of the new partition table is the same as that of any partition table, and the table serial number of the new partition table is the table serial number of any partition table added with a preset value.

In one embodiment, the database management apparatus may further include:

the data reading unit is used for responding to a reading request for reading data and acquiring the data requested by the reading request from a slave database of the Oracle database;

and the data updating unit is used for responding to the data updating request, executing data updating operation in the partition table corresponding to the data updating request, and synchronously updating the changed data to the slave database of the Oracle database after the updating is successful.

In one embodiment, if the data update request is a request for new data, the data update unit is configured to:

In one embodiment, if the data update request is a request for existing data, the data update unit is configured to:

In one embodiment, if the synchronous updating of the changed data to the slave database of the Oracle database fails, the data updating unit repeatedly executes the data synchronization operation for a predetermined number of times; if the synchronous updating is not successful after the data synchronization operation is repeatedly executed for the preset times, the data updating unit returns a synchronization failure prompt to the user client so that the user resubmits the data updating request; the data updating request aiming at the same data is repeatedly executed for a plurality of times, and the corresponding data execution results are the same.

So far, the database management apparatus of the present application has been described in detail. The details of the function implementation of the database management apparatus and the functional modules thereof are the same as or similar to the database management method described above with reference to fig. 1 to 7, and the specific implementation details may refer to the above description, which is not repeated herein.

Having described a database management method and apparatus of an exemplary embodiment of the present application, a computing device according to another exemplary embodiment of the present application is next described.

As will be appreciated by one skilled in the art, aspects of the present application may be embodied as a system, method or program product. Accordingly, various aspects of the present application may be embodied in the form of: an entirely hardware embodiment, an entirely software embodiment (including firmware, microcode, etc.) or an embodiment combining hardware and software aspects that may all generally be referred to herein as a "circuit," module "or" system.

In some possible implementations, a computing device according to the present application may include at least one processor, and at least one memory. Wherein the memory stores program code which, when executed by the processor, causes the processor to perform the steps of the database management method according to various exemplary embodiments of the present application described above in the present specification. For example, the processor may perform the related method steps of fig. 2-6 described above.

The computing device 130 according to this embodiment of the present application is described below with reference to fig. 8. The computing device 130 shown in fig. 8 is only an example and should not bring any limitations to the functionality or scope of use of the embodiments of the present application.

As shown in fig. 8, computing device 130 is embodied in the form of a general purpose computing device. Components of computing device 130 may include, but are not limited to: the at least one processor 131, the at least one memory 132, and a bus 133 that connects the various system components (including the memory 132 and the processor 131).

Bus 133 represents one or more of any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a processor, or a local bus using any of a variety of bus architectures.

The memory 132 may include readable media in the form of volatile memory, such as Random Access Memory (RAM)1321 and/or cache memory 1322, and may further include Read Only Memory (ROM) 1323.

Memory 132 may also include a program/utility 1325 having a set (at least one) of program modules 1324, such program modules 1324 including, but not limited to: an operating system, one or more application programs, other program modules, and program data, each of which, or some combination thereof, may comprise an implementation of a network environment.

Computing device 130 may also communicate with one or more external devices 134 (e.g., keyboard, pointing device, etc.), with one or more devices that enable a user to interact with computing device 130, and/or with any devices (e.g., router, modem, etc.) that enable computing device 130 to communicate with one or more other computing devices. Such communication may occur via input/output (I/O) interfaces 135. Also, computing device 130 may communicate with one or more networks (e.g., a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network, such as the internet) via network adapter 136. As shown, network adapter 136 communicates with other modules for computing device 130 over bus 133. It should be understood that although not shown in the figures, other hardware and/or software modules may be used in conjunction with computing device 130, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID systems, tape drives, and data backup storage systems, among others.

In some possible embodiments, aspects of a database management method provided herein may also be implemented in the form of a program product including program code for causing a computer device to perform the steps of a database management method according to various exemplary embodiments of the present application described above in this specification when the program product is run on the computer device, for example, the computer device may perform the relevant method steps as in fig. 2-6.

The program product may employ any combination of one or more readable media. The readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples (a non-exhaustive list) of the readable storage medium include: an electrical connection having one or more wires, a portable disk, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

The program product for database management of embodiments of the present application may employ a portable compact disk read only memory (CD-ROM) and include program code, and may be run on a computing device. However, the program product of the present application is not limited thereto, and in this document, a readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

A readable signal medium may include a propagated data signal with readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A readable signal medium may also be any readable medium that is not a readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.

Program code embodied on a readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

Program code for carrying out operations of the present application may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computing device, partly on the user's device, as a stand-alone software package, partly on the user's computing device and partly on a remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device may be connected to the user computing device over any kind of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computing device (e.g., over the internet using an internet service provider).

It should be noted that although several units or sub-units of the apparatus are mentioned in the above detailed description, such division is merely exemplary and not mandatory. Indeed, the features and functions of two or more units described above may be embodied in one unit, according to embodiments of the application. Conversely, the features and functions of one unit described above may be further divided into embodiments by a plurality of units.

Further, while the operations of the methods of the present application are depicted in the drawings in a particular order, this does not require or imply that these operations must be performed in this particular order, or that all of the illustrated operations must be performed, to achieve desirable results. Additionally or alternatively, certain steps may be omitted, multiple steps combined into one step execution, and/or one step broken down into multiple step executions.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

While the preferred embodiments of the present application have been described, additional variations and modifications in those embodiments may occur to those skilled in the art once they learn of the basic inventive concepts. Therefore, it is intended that the appended claims be interpreted as including preferred embodiments and all alterations and modifications as fall within the scope of the application.

It will be apparent to those skilled in the art that various changes and modifications may be made in the present application without departing from the spirit and scope of the application. Thus, if such modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is intended to include such modifications and variations as well.

Claims

1. A database management method based on an Oracle database is characterized in that at least one type of service data is stored in the Oracle database, each type of service data is divided into at least one partition table to be stored, each partition table corresponds to one table space, different partition tables of the same type of service data have the same table name and different table serial numbers, the table names of the partition tables of different types of service data are different, and each table name and the maximum table serial number associated with the table name are maintained in a cache database, and the method comprises the following steps:

2. The method of claim 1, wherein obtaining the space capacity of the table space corresponding to the partition table with the largest table sequence number from the Oracle database comprises:

3. The method of claim 2, wherein the view command is a du command.

4. The method according to claim 1, wherein if the space capacity of the tablespace of any partition table is greater than a preset capacity threshold, creating a new tablespace and a corresponding partition table for the business data of the type to which the tablespace of any partition table belongs, includes:

5. The method of claim 1, further comprising:

6. The method according to claim 5, wherein if the data update request is a request for new data, performing a data update operation in the partition table corresponding to the data update request includes:

7. The method according to claim 5, wherein if the data update request is a request for existing data, performing a data update operation in a partition table corresponding to the data update request, includes:

8. The method according to any one of claims 5 to 7, wherein if the slave database that synchronously updates the changed data to the Oracle database fails, the step of performing the data update operation in the partition table corresponding to the data update request is repeatedly performed;

9. A database management device based on an Oracle database is characterized in that at least one type of service data is stored in the Oracle database, each type of service data is divided into at least one partition table for storage, each partition table corresponds to one table space, different partition tables of the same type of service data have the same table name and different table serial numbers, the table names of the partition tables of different types of service data are different, and each table name and the associated maximum table serial number thereof are maintained in a cache database, the device comprises:

10. A computing device comprising at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to perform the database management method of any of claims 1-8.

11. A computer storage medium having stored thereon computer-executable instructions for causing a computer to perform the database management method of any of claims 1-8.