Dynamic database division and table division method based on business requirements

By extracting business requirements and database data characteristics, matching database partitioning rules, and dynamically adjusting database configuration, the problem that database connection pool configuration in the existing technology is difficult to follow business changes, improving database performance and system stability, and reducing operations Maintenance cost.

CN118916343AActive Publication Date: 2024-11-08HUANENG INFORMATION TECH CO LTD

Patent Information

Application Number
CN202410711202.0
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-06-03
Publication Date
2024-11-08
Estimated Expiration
2044-06-03

AI Technical Summary

Technical Problem

The existing database connection pool configuration is difficult to flexibly follow changes in business needs, resulting in frequent adjustments in high concurrency environments, affecting system stability and performance, and each adjustment requires restart of services, increasing operation and maintenance costs.

Method used

By obtaining business requirements information and database data information, extracting feature sets, matching database partitioning rules, dynamically adjusting database configuration, optimizing data storage and query policies, updating configuration relationships in real time, and improving database performance.

Benefits of technology

It realizes dynamic optimization of database performance, reduces the number of restarts of connection pool configurations, reduces operation and maintenance costs, and improves system flexibility and stability.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118916343A_ABST
    Figure CN118916343A_ABST
Patent Text Reader

Abstract

The invention provides a dynamic database division and table division method based on business demands, and relates to the technical field of data processing, and the method comprises the steps: obtaining business demand information and data information in an original database, carrying out the feature extraction, and generating a demand-data feature set; based on the demand-data feature set, in combination with a preset feature-rule matching table, obtaining a matched sub-library sub-table rule in a rule database; the method comprises the following steps of: distributing a corresponding database and a table structure to original data in an original database and to-be-written data based on a database and table division rule, and establishing a configuration relationship between the data and the database as well as between the data and the table structure; and based on the database and table division rule and the configuration relationship, performing database division and table division on the original database, and optimizing a data storage structure and a data query strategy after database division and table division in combination with the business demand information. According to the method, the configuration of the database connection pool is adjusted in combination with the real-time changing business demand information, so that the storage and query performance of the database is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method for dynamic database and table partitioning based on business needs. Background Art

[0002] A database connection pool is a buffer pool that stores pre-created database connections. When an application needs to communicate with a database, it can obtain a connection from the connection pool instead of recreating the connection each time. Therefore, a database connection pool is suitable for applications that need to interact with the database frequently, especially in a high-concurrency environment, which can effectively reduce the overhead of connection creation and destruction and improve system stability and performance.

[0003] With the rapid development of business, the configuration of a single fixed database connection pool can hardly meet the needs of business development. With the change of concurrency, the database connection configuration has to be adjusted. However, many systems run 24 / 7. Each time the connection pool configuration is modified, the service must be restarted. Each downtime affects the smooth operation of the business system to a certain extent, bringing heavy operation and maintenance costs. This model is relatively cumbersome and difficult to flexibly follow the rhythm of the business system.

[0004] Therefore, the present invention provides a dynamic database and table partitioning method based on business needs. Summary of the invention

[0005] The present invention provides a method for dynamic database and table subdivision based on business needs, which adjusts the configuration of a database connection pool in combination with real-time changing business demand information, thereby improving the storage and query performance of the database and ensuring the stable operation of the system.

[0006] The present invention provides a dynamic database and table subdivision method based on business needs, comprising: Step 1: Obtain business demand information and data information in the original database, perform feature extraction on the business demand information and data information, and generate a demand-data feature set; Step 2: Based on the demand-data feature set and in combination with the preset feature-rule matching table, the matching library and table sub-rule rules are obtained in the rule database; Step 3: Based on the database and table subdivision rules, the corresponding database and table structure are allocated to the original data and the data to be written in the original database, and at the same time, a configuration relationship between the original data and the data to be written and the corresponding database and table structure is established; Step 4: Based on the library and table partitioning rules and configuration relationships, the original database is partitioned into libraries and tables, and the data storage structure and data query strategy after the partitioning are optimized in combination with the business demand information.

[0007] Preferably, step 1 includes: Obtaining business category requirements and performance requirements under corresponding categories, and generating business requirement information based on the category requirements and corresponding performance requirements; The types of requirements include: data management requirements, business process requirements, cross-platform and integration requirements, and user experience requirements Preferably, step 1 further includes: Obtaining category information, data structure and performance parameters of the original database, and comprehensively generating data information; The category information includes: relational database, non-relational database, document database and graph database; The data structure includes: table structure, index and database management system structure.

[0008] Preferably, step 1 further includes: Extracting features from the business demand information to generate a first feature set, and at the same time, extracting features from the data information to generate a second feature set; Feature matching is performed on the first feature set and the second feature set, and a demand-data feature set is output.

[0009] Preferably, step 2 includes: Selecting features consistent with the demand instructions from the demand-data feature set to construct a feature set to be matched; Based on the feature set to be matched, corresponding rule matching information is selected in the preset feature-rule matching table; Based on the rule matching information, corresponding library and table sub-rules are selected from the rule database.

[0010] Preferably, step 3 includes: Obtain the library and table partitioning rules and parse them to obtain the library and table partitioning rules and the library and table partitioning mapping relationship; Acquire original data and corresponding data types and data structures in the original database, and output first data information; At the same time, the data type and performance requirement of the data to be written corresponding to the business requirement information are obtained, and the second data information is output; Based on the first data information and the second data information, and in combination with the preset information-indicator matching table, a corresponding rule matching indicator is obtained; Generate data-indicator allocation information based on the first data information, the second data information and the corresponding rule matching indicators; Based on the data-indicator allocation information, select and obtain corresponding allocation instructions in the instruction database; Based on the data-indicator allocation information and the allocation instruction, allocate corresponding database sub-rules and table sub-rules to the first data information and the second data information; At the same time, based on the data-indicator allocation information, and in combination with the system selection instruction and the manual selection instruction, a target database server is selected from the server candidate library; Based on the library sub-rule and table sub-rule, output the sub-library and sub-table allocation result under the corresponding target database server; Based on the sub-library sub-table allocation result, establishing a data-sub-table mapping relationship between the first data information, the second data information and the sub-library sub-table; In combination with the sub-library-sub-table mapping relationship and the data-sub-table mapping relationship, a configuration file under the corresponding mapping relationship is obtained through a preset mapping-configuration matching module; Based on the configuration file, a configuration relationship between the first data information, the second data information and the sub-library sub-table is established.

[0011] Preferably, step 4 includes: Monitor the business demand information in real time, and generate a business demand acquisition log in combination with a timestamp corresponding to the business demand information; Performing cluster analysis on all business demand information within a preset time period in the business demand acquisition log, and outputting demand clustering results; Performing content analysis on the business demand information, obtaining business demand change data belonging to the first category within a preset period of time, and generating first business change information; At the same time, obtaining the business demand change data belonging to the second category within a preset time period in the business demand information, and generating the second business change information; Perform statistical analysis on the demand clustering results, all business first change information and business second change information within the preset time period, and output the business demand change information in combination with the timestamp corresponding to the preset time period; The database connection pool is monitored in real time through a preset monitoring module to obtain the latest configuration parameters of the database and output them as first configuration information; Based on the business demand change information and the database and table sub-rules, and in combination with the latest configuration parameters, a configuration modification instruction is selected from the instruction database; Based on the configuration modification instruction, modify the configuration relationship corresponding to the business demand change information to generate configuration parameter modification information; Based on the configuration parameter modification information, the configuration port information is obtained in the configuration-port comparison table, and the port state corresponding to the configuration port information is switched to a modification state; Modify the configuration parameters corresponding to the port in the modification state, delete the first object corresponding to the original configuration parameters, and generate a second object in combination with the first configuration information; Establishing a connection relationship between the second object and the first configuration information and updating the configuration relationship; Based on the updated configuration relationship and in combination with the library and table sharding rules, the library and table sharding strategy is adjusted, and based on the adjusted library and table sharding strategy, the data storage structure of the original database is updated.

[0012] Preferably, the data storage structure and data query strategy after the database and table division are optimized in combination with the business demand information, including: Determine the capacity optimization value of the database connection pool based on the concurrent demand information in the business demand information; ; in, represents the capacity optimization value; Indicates the maximum number of connections set to the database connection pool. Indicates the maximum number of connections determined based on the concurrent demand information, Indicates the performance evaluation factor corresponding to the maximum number of connections; Representation and The corresponding benchmark value; Indicates the minimum number of connections set to the database connection pool; Indicates the minimum number of connections determined based on the concurrent demand information; Indicates the performance evaluation factor corresponding to the minimum number of connections; Representation and The corresponding benchmark value; , Indicates the capacity incompatibility coefficient based on the maximum number of connections and the minimum number of connections; Obtain a first data storage structure before library and table division and a second data storage structure after library and table division, and compare the first storage structure with the second storage structure to obtain structure change data; Based on the structure change data and in combination with the business demand information, the operation parameter change data of the database connection pool is obtained, and the performance value of the database connection pool is calculated;

[0013] in, Indicates the performance value of the database connection pool; represents the exponential function; Indicates the ratio of the number of reused connections to the total number of connections in the database connection pool determined based on the running parameter change data; Indicates the ratio of the average increase in the connection duration of all connections in the database connection pool determined based on the running parameter change data to the original average connection duration; Indicates the incremental connection ratio, Indicates the parameter value of the database connection pool under the i-th performance evaluation indicator before sharding; It indicates the parameter value of the database connection pool under the i-th performance evaluation index after database and table sharding; represents the performance evaluation factor under the i-th performance evaluation indicator; Represents the total number of performance evaluation indicators; represents the correction factor corresponding to the number of n performance evaluation indicators selected; and, ; When the capacity optimization value is less than the first preset value and the performance value is less than the second preset value, the indicator to be optimized is determined, and the database connection pool parameters, data storage structure and data query strategy are continuously optimized.

[0014] The present invention provides a method for dynamic database and table sharding based on business needs. First, the business demand information and the original data structure in the database and the data information stored therein are accurately acquired to accurately grasp the dynamic changes of the business demand, and then feature extraction is performed to obtain a demand-data feature set; then, the matching database and table sharding rules are selected in the rule database through the obtained feature set, and the corresponding database and table structures are allocated to the original data in the database and the data to be written, and at the same time, the corresponding configuration relationship is established, and the real-time update of the configuration relationship is synchronously realized according to the dynamic changes of the business demand; finally, the database is sharded and tabled in combination with the business demand information, and the data storage structure and data query strategy are continuously optimized, so as to continuously improve the query and storage performance of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0016] Figure 1 It is a flowchart of a method for dynamic database and table partitioning based on business needs provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0017] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0018] like Figure 1 As shown, an embodiment of the present invention provides a dynamic database and table sharding method based on business needs, including: Step 1: Obtain business demand information and data information in the original database, extract features from the business demand information and data information, and generate a demand-data feature set; Step 2: Based on the demand-data feature set and the preset feature-rule matching table, the matching database and table sharding rules are obtained in the rule database; Step 3: Based on the database and table sharding rules, the corresponding database and table structures are allocated to the original data and the data to be written in the original database, and at the same time, the configuration relationship between the original data and the data to be written and the corresponding database and table structures is established; Step 4: Based on the database and table sharding rules and configuration relationships, the original database is sharded, and the data storage structure and data query strategy after sharding are optimized based on business demand information.

[0019] In this embodiment, the business requirement information includes the type requirement of the business and the performance requirement data of the corresponding type; In this embodiment, the original database refers to the initial database before the database and table are divided; In this embodiment, data information: that is, original data information stored in the original database; In this embodiment, the demand-data feature set: a set of extracted features including business demand information and data information; In this embodiment, the preset feature-rule matching table: a table containing the mapping relationship between the extracted features and the corresponding library and table division rules, is preset; In this embodiment, the rule database: a database containing a large number of sub-library and sub-table rules; In this embodiment, the library and table partitioning rules are rules for partitioning the original database into libraries and tables according to business demand information, which are usually pre-set; In this embodiment, original data refers to data stored in the original database before the database and table are divided; In this embodiment, the data to be written: the new data to be written corresponding to the business demand information; In this embodiment, the configuration relationship refers to the configuration and mapping relationship between the original data and the data to be written and the database and table structure after the structure change; In this embodiment, the data storage structure is a new storage structure formed by dividing the original database into libraries and tables; In this embodiment, the data query strategy refers to a strategy for querying data and optimizing the query.

[0020] The implementation principle and beneficial effects of this embodiment: The present invention first accurately obtains the business demand information and the original data structure in the database and the data information stored therein, accurately grasps the dynamic changes of business needs, and then performs feature extraction to obtain a demand-data feature set; then, the matching sub-library and table rules are selected in the rule database through the obtained feature set, and the corresponding database and table structures are allocated to the original data in the database and the data to be written. At the same time, the corresponding configuration relationship is established, and the real-time update of the configuration relationship is synchronously realized according to the dynamic changes of business needs; finally, the database is sub-library and table based on the business demand information, and the data storage structure and data query strategy are continuously optimized, so as to continuously improve the query and storage performance of the database.

[0021] The embodiment of the present invention provides a method for dynamic database and table sharding based on business needs, wherein step 1 includes: Obtain the type requirements of the business and the performance requirements under the corresponding type, and generate business requirement information based on the type requirements and the corresponding performance requirements; Among them, the types of requirements include: data management requirements, business process requirements, cross-platform and integration requirements, and user experience requirements.

[0022] In this embodiment, category requirements: business requirements are classified according to different dimensions to facilitate rapid determination of the category to which the business requirements belong, so as to improve the access performance and storage performance of the database; In this embodiment, performance requirements: that is, various performance requirements under the category to which the business requirements belong; In this embodiment, data management requirements: information on requirements for data storage and management, security and privacy, and data backup and recovery; In this embodiment, business process requirements: requirement information on business workflow, task allocation, business rule configuration, etc.; In this embodiment, cross-platform and integration requirements: requirement information on cross-platform compatibility of services and system integration; In this embodiment, the user experience requirement: requirement information that can improve the user experience, such as responsive design or multi-language support.

[0023] The implementation principle and beneficial effects of this embodiment: By obtaining the type requirements of the business and the performance requirements under the corresponding types, the present invention can accurately determine the type information of the business to be processed and the business being processed and the corresponding performance requirement information, improve the resolution of the business, accurately grasp the dynamic demand information of each business, and provide convenience for subsequent configuration updates and sub-library and table divisions.

[0024] The embodiment of the present invention provides a method for dynamic database and table sharding based on business needs, wherein step 1 further includes: Obtain category information, data structure and performance parameters of the original database, and comprehensively generate data information; The category information includes: relational database, non-relational database, document database and graph database; Data structures include: table structure, index and database management system structure.

[0025] In this embodiment, the category information: that is, the category to which the original database belongs; In this embodiment, data structure: that is, structural information of the original database, such as tables, columns, rows, primary keys, and foreign keys; In this embodiment, the performance parameter refers to the performance data of the database, such as query performance and storage performance; In this embodiment, relational database: a database that uses a table structure to organize and manage data, for example, MySQL, Oracle, SQL Server and other databases; In this embodiment, non-relational database: a database that does not use a traditional table structure to organize and manage data, such as a column storage database, a key-value database, etc.; In this embodiment, the document-type database is a database that stores data in the form of documents; In this embodiment, the graph database: a database storing data using images; In this embodiment, table structure: a database that organizes and manages data in a table structure, each table usually contains multiple rows of data; In this embodiment, index: a data structure used to speed up data retrieval; In this embodiment, the database management system structure: a software system for managing a database, responsible for data storage, retrieval, update and management, for example, a database connection pool, etc.; The implementation principle and beneficial effects of this embodiment: The present invention can accurately identify the type information of the original database, and at the same time, accurately obtain the data structure of the database and the performance parameters of the database, thereby improving the recognition accuracy of the original database, facilitating the subsequent division and structural adjustment of the database according to the type of business demand information, and providing accurate data support for the subsequent matching of sub-library and sub-table rules.

[0026] The embodiment of the present invention provides a method for dynamic database and table sharding based on business needs, wherein step 1 further includes: Extracting features from the business demand information to generate a first feature set, and at the same time, extracting features from the data information to generate a second feature set; Feature matching is performed on the first feature set and the second feature set, and a demand-data feature set is output.

[0027] In this embodiment, the first feature set is a set of features obtained by extracting features from the business demand information; In this embodiment, the second feature set is a set of features obtained by extracting features from data information; In this embodiment, feature matching: matching the features in the first feature set and the second feature set to facilitate subsequent rule matching operations.

[0028] The implementation principle and beneficial effects of this embodiment: The present invention obtains key data features in the business demand information by performing feature extraction on the business demand information, and establishes a first feature set. At the same time, feature extraction is performed on the data information corresponding to the original database to obtain the feature set of the original database, and a second feature set is constructed. The features in the first feature set and the second feature set are then matched, which facilitates the subsequent database sub-library and sub-table matching while ensuring data accuracy.

[0029] The embodiment of the present invention provides a method for dynamic database and table sharding based on business needs, wherein step 2 includes: Select features consistent with the demand instructions from the demand-data feature set to construct a feature set to be matched; Based on the feature set to be matched, corresponding rule matching information is selected from the preset feature-rule matching table; Based on the rule matching information, the corresponding database and table sub-rules are selected from the rule database.

[0030] In this embodiment, the demand instruction is an instruction for selecting a corresponding target feature from the demand-data feature set; In this embodiment, the feature set to be matched is a set of features selected from the demand-data feature set, which is used for subsequent matching to obtain library and table partitioning rules; In this embodiment, rule matching information refers to matching information obtained from a preset feature-rule matching table according to a feature set to be matched, and includes a mapping relationship between features and rules.

[0031] The implementation principle and beneficial effects of this embodiment: The present invention first selects features consistent with the demand instructions from the demand-data feature set to obtain the feature set to be matched, thereby improving the matching degree between each feature in the feature set to be matched and the business demand information, and then selects rule matching information from the preset feature-rule matching table through the feature set to be matched, and selects corresponding sub-library and sub-table rules from the rule database based on the rule matching information, thereby improving the matching accuracy between the sub-library and sub-table rules and the business demand information and the original database.

[0032] The embodiment of the present invention provides a method for dynamic database and table sharding based on business needs, wherein step 3 includes: Obtain and parse the database and table sharding rules to obtain the database and table sharding rules and the database and table sharding mapping relationship; Acquire original data and corresponding data types and data structures in an original database, and output first data information; At the same time, the data type and performance requirement of the data to be written corresponding to the business requirement information are obtained, and the second data information is output; Based on the first data information and the second data information, and in combination with the preset information-indicator matching table, a corresponding rule matching indicator is obtained; Generate data-indicator allocation information based on the first data information, the second data information and the corresponding rule matching indicators; Based on the data-indicator allocation information, select and obtain corresponding allocation instructions in the instruction database; Based on the data-indicator allocation information and the allocation instruction, allocate corresponding database sub-rules and table sub-rules to the first data information and the second data information; At the same time, based on the data-index allocation information, and in combination with the system selection instructions and the manual selection instructions, the target database server is selected from the server candidate library; Based on the database and table sub-rules, output the sub-database and sub-table allocation results under the corresponding target database server; Based on the sub-library sub-table allocation result, a data-sub-table mapping relationship is established between the first data information, the second data information and the sub-library sub-table; Combined with the sub-library-sub-table mapping relationship and the data-sub-table mapping relationship, the configuration file under the corresponding mapping relationship is obtained through the preset mapping-configuration matching module; Based on the configuration file, a configuration relationship between the first data information, the second data information and the sub-library sub-table is established.

[0033] In this embodiment, database partitioning rules: a partitioning rule for re-dividing the original database into multiple small databases, which is used to disperse data storage and query pressure and improve database performance and scalability; In this embodiment, the table division rule: a division rule for dividing a single table into multiple sub-tables to increase data retrieval speed and improve system maintainability; In this embodiment, the sub-library and sub-table mapping relationship: that is, the mapping relationship between the sub-library and the sub-table; In this embodiment, the first data information includes the original data in the original database and the information of the data type and data structure thereof; In this embodiment, the second data information includes the data type and performance requirement of the data to be written; In this embodiment, the preset information-indicator matching table: a matching table including the mapping relationship between the first data information, the second data information and the rule matching indicator, which is preset and used to match and obtain the corresponding rule matching indicator according to the input first data information or second data information; In this embodiment, the rule matching index is an index used to allocate library and table sub-rules to corresponding data according to the preset information-index matching table; In this embodiment, the data-indicator allocation information includes the first data information, the second data information and the corresponding rule allocation indicator information, which is used to select the allocation instruction from the instruction database later; In this embodiment, the allocation instruction is an instruction selected from an instruction database and used to allocate library and table sub-division rules to the first data information and the second data information; In this embodiment, the system selection instruction: that is, the server selection instruction automatically matched by the system; In this embodiment, the manual selection instruction: that is, the server selection instruction input manually, generally has a higher priority than the system selection instruction; In this embodiment, the server candidate library is a database containing information of various database servers, and is used to select a matching database server according to a system selection instruction and a manual selection instruction; In this embodiment, the target database server is a database server selected from the server candidate library; In this embodiment, the sub-library and sub-table allocation result is the allocation result obtained after each target data server is divided according to the sub-library and sub-table rules; In this embodiment, the data-sub-table mapping relationship is a mapping relationship between the first data information, the second data information and each sub-library sub-table obtained by division; In this embodiment, the preset mapping-configuration matching module is a module used to obtain the corresponding configuration file according to the input sub-library and sub-table mapping relationship and the data-sub-table mapping relationship, which is preset; In this embodiment, the configuration file is generated according to a preset mapping-configuration module and corresponds to each mapping relationship, and is used for subsequently constructing a configuration relationship between data and a database.

[0034] The implementation principle and beneficial effects of this embodiment: The present invention performs in-depth analysis on the acquired library and table sharding rules to obtain library and table sharding rules, table sharding rules and library and table sharding mapping relationships, and at the same time, in combination with the preset information-indicator matching table, assigns corresponding rule allocation indicators to the first data information corresponding to the original database and the second data information corresponding to the business demand information, thereby improving the matching degree between the library and table sharding rules and the business demand and the database, and then establishing the configuration relationship between the data and the library and table sharding rules through the acquired configuration file, thereby improving the compatibility and adaptability between the library and table sharding rules and the data, thereby ensuring the stable operation of the system.

[0035] The embodiment of the present invention provides a method for dynamic database and table partitioning based on business needs, wherein step 4 includes: Monitor business demand information in real time and generate business demand acquisition logs based on the timestamps corresponding to the business demand information; Perform cluster analysis on all business demand information within a preset period in the business demand acquisition log, and output the demand clustering results; Analyze the content of the business demand information, obtain business demand change data belonging to the first category within a preset period, and generate first business change information; At the same time, obtaining the business demand change data belonging to the second category within a preset period in the business demand information, and generating the second business change information; Perform statistical analysis on the demand clustering results, all business first change information and business second change information within the preset time period, and output the business demand change information in combination with the timestamp corresponding to the preset time period; The database connection pool is monitored in real time through a preset monitoring module to obtain the latest configuration parameters of the database and output them as first configuration information; Based on the business demand change information and the database and table sharding rules, and combined with the latest configuration parameters, the configuration modification instructions are selected from the instruction database; Based on the configuration modification instruction, the configuration relationship corresponding to the business demand change information is modified to generate configuration parameter modification information; Based on the configuration parameter modification information, the configuration port information is obtained in the configuration-port comparison table, and the port state corresponding to the configuration port information is switched to a modification state; Modify the configuration parameters corresponding to the port in the modification state, delete the first object corresponding to the original configuration parameters, and generate a second object in combination with the first configuration information; Establishing a connection relationship between the second object and the first configuration information and updating the configuration relationship; Based on the updated configuration relationship and in combination with the database and table sharding rules, the database and table sharding strategy is adjusted, and based on the adjusted database and table sharding strategy, the data storage structure of the original database is updated.

[0036] In this embodiment, timestamp: i.e., the time feature of each business requirement, used to record the time information of each event in the system; In this embodiment, the business requirement acquisition log: a log that records each business requirement received by the system according to the timestamp of the business requirement; In this embodiment, preset period: a preset period of time used to analyze the change data of business needs within a fixed period of time; In this embodiment, demand clustering result: the result obtained after clustering analysis of the business demand information can count the types of all business demands and the frequency of occurrence of business demands under each category, so as to facilitate subsequent data processing; In this embodiment, the first category: that is, the category that is the same as the original business requirement type or the original performance requirement type; In this embodiment, the first service change information includes information on service demand change data of the first category; In this embodiment, the second category: namely, newly added business requirement types and newly added performance requirements; In this embodiment, the second service change information includes information on the change data of the second type of service demand; In this embodiment, the business demand change information is used to characterize the business demand change within a preset period of time, and can record the dynamic change data of the business demand information; In this embodiment, the preset monitoring module: a module for monitoring the configuration parameters in the database is preset; In this embodiment, database connection pool: a technology for managing database connections, which can improve the access efficiency and performance of the database; In this embodiment, the first configuration information: that is, the latest configuration parameters of the database obtained by the preset monitoring module; In this embodiment, the configuration modification instruction is an instruction selected from the instruction database through the latest configuration parameters and used to modify the configuration relationship; In this embodiment, configuration parameter modification information: configuration parameter change information obtained after modifying the configuration relationship; In this embodiment, the configuration-port comparison table: a comparison table including the mapping relationship between configuration parameter modification information and configuration port information, used to obtain corresponding configuration port information according to the input configuration parameter modification information, so as to modify the corresponding configuration parameters; In this embodiment, configuration port information: a control port for adjusting configuration parameters; In this embodiment, the modification state is a state in which the configuration port can receive external configuration modification information and thus can modify the configuration parameters in the port; In this embodiment, the first object: that is, the connection pool object corresponding to the original configuration parameters; In this embodiment, the second object: that is, the connection pool object corresponding to the latest configuration parameters; The implementation principle and beneficial effects of this embodiment: The present invention monitors business demand information in real time and generates a business demand acquisition log in combination with a timestamp, which can not only accurately grasp the dynamic change data of the business demand information, but also facilitate subsequent operation and maintenance; at the same time, the present invention can also monitor and obtain the latest configuration parameters of the database in real time, and update the connection pool object and connection pool configuration according to the latest parameters, thereby reducing the number of restarts of the database connection pool configuration, which not only ensures the normal and stable operation of the business system, but also improves the flexibility of the database connection pool and reduces the subsequent operation and maintenance costs of the database.

[0037] The embodiment of the present invention provides a dynamic database and table sharding method based on business needs, which optimizes the data storage structure and data query strategy after database and table sharding in combination with business demand information, including: Determine the capacity optimization value of the database connection pool based on the concurrent demand information in the business demand information; ; in, represents the capacity optimization value; Indicates the maximum number of connections set to the database connection pool. Indicates the maximum number of connections determined based on concurrent demand information. Indicates the performance evaluation factor corresponding to the maximum number of connections; Representation and The corresponding benchmark value; Indicates the minimum number of connections set to the database connection pool; Indicates the minimum number of connections determined based on concurrent demand information; Indicates the performance evaluation factor corresponding to the minimum number of connections; Representation and The corresponding benchmark value; , Indicates the capacity incompatibility coefficient based on the maximum number of connections and the minimum number of connections; Obtain a first data storage structure before library and table division and a second data storage structure after library and table division, and compare the first storage structure with the second storage structure to obtain structure change data; Based on the structural change data and combined with the business demand information, the operating parameter change data of the database connection pool is obtained, and the performance value of the database connection pool is calculated;

[0038] in, Indicates the performance value of the database connection pool; represents the exponential function; Indicates the ratio of the number of reused connections to the total number of connections in the database connection pool determined based on the running parameter change data; Indicates the ratio of the average increase in the connection duration of all connections in the database connection pool determined based on the running parameter change data to the original average connection duration; Indicates the incremental connection ratio, Indicates the parameter value of the database connection pool under the i-th performance evaluation indicator before sharding; It indicates the parameter value of the database connection pool under the i-th performance evaluation index after database and table sharding; represents the performance evaluation factor under the i-th performance evaluation indicator; Represents the total number of performance evaluation indicators; represents the correction factor corresponding to the number of n performance evaluation indicators selected; and, ; When the capacity optimization value is less than the first preset value and the performance value is less than the second preset value, the indicator to be optimized is determined, and the database connection pool parameters, data storage structure and data query strategy are continuously optimized.

[0039] In this embodiment, the concurrent demand information: that is, the number of concurrent requests that the system needs to process in the same period; In this embodiment, the capacity optimization value is a value used to characterize the capacity optimization performance of the database connection pool before and after the database and table sharding process; In this embodiment, the first data storage structure: that is, the data storage structure before the database and table are divided; In this embodiment, the second data storage structure: that is, the data storage structure after the database and table are divided; In this embodiment, the structure change data: that is, the change data obtained by comparing the first data storage structure with the second data storage structure; In this embodiment, the operation parameter change data refers to the operation parameter change data of the database connection pool before and after the database and table sharding process; In this embodiment, the first preset value is a preset parameter used for comparison with the capacity optimization value to determine whether the system needs to be optimized; In this embodiment, the second preset value is a preset parameter used for comparison with the performance value to determine whether the system needs to be optimized, and is used in combination with the first preset value.

[0040] The implementation principle and beneficial effects of this embodiment: The present invention can determine the capacity optimization value of the database connection pool before and after the database and table sharding process by obtaining concurrent demand information in the business demand information, and then obtain the performance change data of the database connection pool by obtaining the data structure change information of the original database before and after the database and table sharding, so as to compare the capacity optimization value and the performance value with the preset parameters, and determine the optimizable indicators of the database connection pool, data storage structure and data query test class, so as to continuously improve the query performance and storage performance of the database.

[0041] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A dynamic database and table sharding method based on business needs, characterized in that: include: Step 1: Obtain business demand information and data information in the original database, perform feature extraction on the business demand information and data information, and generate a demand-data feature set; Step 2: Based on the demand-data feature set and in combination with the preset feature-rule matching table, the matching library and table sub-rule rules are obtained in the rule database; Step 3: Based on the database and table subdivision rules, the corresponding database and table structure are allocated to the original data and the data to be written in the original database, and at the same time, a configuration relationship between the original data and the data to be written and the corresponding database and table structure is established; Step 4: Based on the library and table partitioning rules and configuration relationships, the original database is partitioned into libraries and tables, and the data storage structure and data query strategy after the partitioning are optimized in combination with the business demand information.

2. According to a method for dynamic database and table division based on business needs according to claim 1, it is characterized in that: Step 1 includes: Obtaining business category requirements and performance requirements under corresponding categories, and generating business requirement information based on the category requirements and corresponding performance requirements; The types of requirements include: data management requirements, business process requirements, cross-platform and integration requirements, and user experience requirements.

3. According to a method for dynamic database and table division based on business needs as described in claim 1, it is characterized in that: Step 1 also includes: Obtaining category information, data structure and performance parameters of the original database, and comprehensively generating data information; The category information includes: relational database, non-relational database, document database and graph database; The data structure includes: table structure, index and database management system structure.

4. The method for dynamic database and table partitioning based on business needs according to claim 1, characterized in that: Step 1 also includes: Extracting features from the business demand information to generate a first feature set, and at the same time, extracting features from the data information to generate a second feature set; Feature matching is performed on the first feature set and the second feature set, and a demand-data feature set is output.

5. The method for dynamic database and table partitioning based on business needs according to claim 1, characterized in that: Step 2 includes: Selecting features consistent with the demand instructions from the demand-data feature set to construct a feature set to be matched; Based on the feature set to be matched, corresponding rule matching information is selected in the preset feature-rule matching table; Based on the rule matching information, corresponding library and table sub-rules are selected from the rule database.

6. The method for dynamic database and table partitioning based on business needs according to claim 1, characterized in that: Step 3 includes: Obtain the library and table partitioning rules and parse them to obtain the library and table partitioning rules and the library and table partitioning mapping relationship; Acquire original data and corresponding data types and data structures in the original database, and output first data information; At the same time, the data type and performance requirement of the data to be written corresponding to the business requirement information are obtained, and the second data information is output; Based on the first data information and the second data information, and in combination with the preset information-indicator matching table, a corresponding rule matching indicator is obtained; Generate data-indicator allocation information based on the first data information, the second data information and the corresponding rule matching indicators; Based on the data-indicator allocation information, select and obtain corresponding allocation instructions in the instruction database; Based on the data-indicator allocation information and the allocation instruction, allocate corresponding database sub-rules and table sub-rules to the first data information and the second data information; At the same time, based on the data-indicator allocation information, and in combination with the system selection instruction and the manual selection instruction, a target database server is selected from the server candidate library; Based on the library sub-rule and table sub-rule, output the sub-library and sub-table allocation result under the corresponding target database server; Based on the sub-library sub-table allocation result, establishing a data-sub-table mapping relationship between the first data information, the second data information and the sub-library sub-table; In combination with the sub-library-sub-table mapping relationship and the data-sub-table mapping relationship, a configuration file under the corresponding mapping relationship is obtained through a preset mapping-configuration matching module; Based on the configuration file, a configuration relationship between the first data information, the second data information and the sub-library sub-table is established.

7. The method for dynamic database and table partitioning based on business needs according to claim 1, characterized in that: Step 4 includes: Monitor the business demand information in real time, and generate a business demand acquisition log in combination with a timestamp corresponding to the business demand information; Performing cluster analysis on all business demand information within a preset time period in the business demand acquisition log, and outputting demand clustering results; Performing content analysis on the business demand information, obtaining business demand change data belonging to the first category within a preset period of time, and generating first business change information; At the same time, obtaining the business demand change data belonging to the second category within a preset time period in the business demand information, and generating the second business change information; Perform statistical analysis on the demand clustering results, all business first change information and business second change information within the preset time period, and output the business demand change information in combination with the timestamp corresponding to the preset time period; The database connection pool is monitored in real time through a preset monitoring module to obtain the latest configuration parameters of the database and output them as first configuration information; Based on the business demand change information and the database and table sub-rules, and in combination with the latest configuration parameters, a configuration modification instruction is selected from the instruction database; Based on the configuration modification instruction, modify the configuration relationship corresponding to the business demand change information to generate configuration parameter modification information; Based on the configuration parameter modification information, the configuration port information is obtained in the configuration-port comparison table, and the port state corresponding to the configuration port information is switched to a modification state; Modify the configuration parameters corresponding to the port in the modification state, delete the first object corresponding to the original configuration parameters, and generate a second object in combination with the first configuration information; Establishing a connection relationship between the second object and the first configuration information and updating the configuration relationship; Based on the updated configuration relationship and in combination with the library and table sharding rules, the library and table sharding strategy is adjusted, and based on the adjusted library and table sharding strategy, the data storage structure of the original database is updated.

8. The method for dynamic database and table partitioning based on business needs according to claim 1, characterized in that: Combined with the business demand information, the data storage structure and data query strategy after the database and table are divided are optimized, including: Determine the capacity optimization value of the database connection pool based on the concurrent demand information in the business demand information; ;in, represents the capacity optimization value; Indicates the maximum number of connections set to the database connection pool. Indicates the maximum number of connections determined based on the concurrent demand information, Indicates the performance evaluation factor corresponding to the maximum number of connections; Representation and The corresponding benchmark value; Indicates the minimum number of connections set to the database connection pool; Indicates the minimum number of connections determined based on the concurrent demand information; Indicates the performance evaluation factor corresponding to the minimum number of connections; Representation and The corresponding benchmark value; , Indicates the capacity incompatibility coefficient based on the maximum number of connections and the minimum number of connections; Obtain a first data storage structure before library and table division and a second data storage structure after library and table division, and compare the first storage structure with the second storage structure to obtain structure change data; Based on the structure change data and in combination with the business demand information, the operation parameter change data of the database connection pool is obtained, and the performance value of the database connection pool is calculated; ;in, Indicates the performance value of the database connection pool; represents the exponential function; Indicates the ratio of the number of reused connections to the total number of connections in the database connection pool determined based on the running parameter change data; Indicates the ratio of the average increase in the connection duration of all connections in the database connection pool determined based on the running parameter change data to the original average connection duration; Indicates the incremental connection ratio, Indicates the parameter value of the database connection pool under the i-th performance evaluation indicator before sharding; It indicates the parameter value of the database connection pool under the i-th performance evaluation index after database and table sharding; represents the performance evaluation factor under the i-th performance evaluation indicator; Represents the total number of performance evaluation indicators; represents the correction factor corresponding to the number of n performance evaluation indicators selected; and, When the capacity optimization value is less than the first preset value and the performance value is less than the second preset value, the indicator to be optimized is determined, and the database connection pool parameters, data storage structure and data query strategy are continuously optimized.

Citation Information

Patent Citations

  • Method and device for accessing database

    CN103714097A

  • Data writing and reading method and device

    CN106294421A

  • Database splitting method and device

    CN113486023A

  • Data access method, device and equipment based on sub-libraries and sub-tables, and medium

    CN116226124A

Cited By

  • System for performing sub-table storage on artificial intelligence system data

    CN121412230A

  • A system for partitioning and storing data in artificial intelligence systems

    CN121412230B

  • Meta-model-based library and table dividing method, electronic equipment and storage medium

    CN121705286A