CN116775607A - Database access method, device, medium and equipment - Google Patents
Database access method, device, medium and equipment Download PDFInfo
- Publication number
- CN116775607A CN116775607A CN202310757817.2A CN202310757817A CN116775607A CN 116775607 A CN116775607 A CN 116775607A CN 202310757817 A CN202310757817 A CN 202310757817A CN 116775607 A CN116775607 A CN 116775607A
- Authority
- CN
- China
- Prior art keywords
- database
- service
- business
- servers
- service server
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 67
- 239000012634 fragment Substances 0.000 claims abstract description 3
- 238000004590 computer program Methods 0.000 claims description 12
- 238000012163 sequencing technique Methods 0.000 claims description 2
- 238000005516 engineering process Methods 0.000 abstract description 2
- 230000006870 function Effects 0.000 description 11
- 238000010586 diagram Methods 0.000 description 10
- 239000011159 matrix material Substances 0.000 description 10
- 238000013507 mapping Methods 0.000 description 6
- 238000004364 calculation method Methods 0.000 description 5
- 238000012544 monitoring process Methods 0.000 description 5
- 230000009467 reduction Effects 0.000 description 5
- 230000002068 genetic effect Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 3
- 230000002441 reversible effect Effects 0.000 description 3
- 238000013461 design Methods 0.000 description 2
- 238000011156 evaluation Methods 0.000 description 2
- 238000007726 management method Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000004075 alteration Effects 0.000 description 1
- 230000000903 blocking effect Effects 0.000 description 1
- 238000013480 data collection Methods 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 238000009795 derivation Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000013467 fragmentation Methods 0.000 description 1
- 238000006062 fragmentation reaction Methods 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 238000005070 sampling Methods 0.000 description 1
- 238000012216 screening Methods 0.000 description 1
- 238000000926 separation method Methods 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present application relates to the field of database technologies, and in particular, to a method, an apparatus, a medium, and a device for accessing a database, where the method includes: acquiring a database access request sent by a first service server; when the database access request is determined to have no fragment key information, determining N second business servers with performance indexes meeting the set requirements from the business service cluster; indicating N second service servers to execute corresponding access commands to be executed respectively; and sending a database access result to the first service server based on the access result fed back by the at least one second service server. By the method, N second business servers are determined in the business service cluster, N second business servers with performance indexes meeting the set requirements are screened out and used as the second business servers, the second business servers are indicated to execute the corresponding access command to be executed, the pressure of executing the access command to be executed by a single business service can be effectively reduced, the execution efficiency is improved, and the database access efficiency is further improved.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a method, an apparatus, a medium, and a device for accessing a database.
Background
With the rapid development of business, the data volume in the database is larger and larger, and the data storage mode of the single-library list brings great pressure to data access. Blocking of access requests may occur in scenarios where database access requests are highly concurrent.
From a machine perspective, the performance bottleneck of the database is that of a processor, a memory, a network, etc., and the performance bottleneck of the database can be solved by improving the performance of the machine, but the cost of the method is high. Therefore, from the software perspective, the data can be scattered into different databases for storage, so that the data volume of a single database is reduced, and the access pressure of the single database is relieved. When the data is sliced, a large table is divided into a plurality of sub-tables according to the slicing key by setting the slicing key, and the sub-tables are stored in different databases. When the business service accesses the database, the corresponding data can be found by carrying the slicing key. For the data access request without carrying the sharding key, the sharding key can be obtained through mapping calculation, and then the data is queried from the database. However, the non-sliced keys carried by the data access request without the sliced key are various, and the mapping calculation is required for each non-sliced key, which results in a reduction of the data access speed and occupies a large amount of computing resources, and finally results in lower database access efficiency.
Based on this, there is a need for a database access method, apparatus, medium and device, which are used to improve the efficiency of database access.
Disclosure of Invention
The embodiment of the application provides a database access method, a device, a medium and equipment, which are used for improving the efficiency of database access.
In a first aspect, an embodiment of the present application provides a database access method, applied to a database service center, where the method includes:
acquiring a database access request sent by a first service server; the first business server is any business server in a business service cluster with different business services;
when the database access request is determined to have no fragment key information, N second business servers with performance indexes meeting the set requirements are determined from the business service cluster; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
indicating the N second service servers to execute corresponding access commands to be executed respectively; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
And sending a database access result to the first service server based on the access result fed back by at least one second service server.
By the method, N second business servers are determined in the business service cluster, the performance index of the server is determined according to the connection relation between the database where the sub-table corresponding to the database access request is located and the business servers and the operation performance of the business servers, the N second business servers which are used as the second business servers and are in accordance with the set requirements are screened out, the second business servers are instructed to execute the corresponding access command to be executed, and the access result is obtained. The method and the device can effectively reduce the pressure of executing the access command to be executed by a single business service, improve the execution efficiency and further improve the database access efficiency.
In a possible implementation manner, determining N second service servers with performance indexes meeting set requirements from the service cluster includes:
determining m databases in which the sub-tables corresponding to the database access requests are located and a service server range designated by the database access requests;
determining performance indexes of n business servers in the range of the business server according to any database in the m databases;
According to the performance indexes of the m x N service servers, N second service servers with the performance indexes meeting the set requirements are determined, and m and N are positive integers.
By the method, N second servers are determined according to the performance indexes, the selection accuracy of the second servers can be improved, and the corresponding access command to be executed can be better executed.
In a possible implementation manner, according to performance indexes of m×n service servers, determining N second service servers with performance indexes meeting a set requirement includes:
sequencing the n service servers according to performance indexes aiming at any one database in the m databases, and determining the first a service servers with the optimal performance indexes;
and determining N service servers with the largest occurrence number from the m-a service servers as second service servers.
In the mode, the performance index of the selected service server is ensured to be absolutely optimal from the first a servers with the optimal performance indexes, and then the service server with the largest occurrence number is selected from the database, so that most databases can be ensured to be matched with the servers with the excellent performance indexes.
In one possible implementation of the method, the method comprises,
Determining a performance index of each of the n service servers under each of the m databases as follows, including:
acquiring a plurality of operation performances of the service server in a history period according to any service server in the n service servers and any database in the m databases;
determining a connection base according to whether the service server is connected with the database;
and determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient.
By the method, when the performance index of the service server is evaluated, the operation performance of the service server is considered, and the connection performance with the database is also considered. And the weight coefficient and the connection coefficient are set, so that the evaluation of the performance index is more accurate.
In a possible implementation manner, the operation performance includes: at least one of processor utilization, memory utilization, connection pool utilization;
before determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient, the method further comprises:
Excluding from the n service servers that meet one or more of the following conditions:
the processor utilization is greater than a first threshold;
the memory usage is greater than a second threshold;
the connection pool utilization is greater than a third threshold.
By the method, the evaluation quantity of the service servers is reduced, and the computing resources for computing the performance indexes are saved.
In a possible implementation manner, the method further includes determining the connection coefficient and the weight coefficient corresponding to each operation performance according to the following manner:
acquiring each operation performance, connection base and operation time of any service server in a history period;
and inputting each operation performance, the connection base and the operation time into a preset model, and determining a connection coefficient and a weight coefficient corresponding to each operation performance.
The historical data is used as a sample, and a preset model is trained, so that a connection coefficient and a weight coefficient are obtained, and the accuracy of determining the weight coefficient and the connection coefficient can be effectively improved.
In a possible implementation manner, before obtaining the database access request sent by the first service server, the method further includes:
and distributing database resources and resource identifiers corresponding to the database resources for the business servers based on the resource requests of the business servers for any business server in the business service cluster, wherein the resource identifiers are used for the business servers to determine database configuration.
By the method, after the resource identifier is allocated to the service server, the database configuration and the corresponding resource locator can be determined according to the resource identifier, so that the service server is not required to be notified when the database is required to be migrated, and the database management efficiency is benefited.
In a possible implementation manner, the method further includes:
and when the database access request is determined to have the sharding key information, indicating the first service server to acquire an access result corresponding to the database access request according to the sharding key information.
By the method, when the slicing keys are determined, the slicing keys can be directly routed to the corresponding sub-library sub-table, connection with a second service server is not required to be established, interaction times are reduced, and efficient utilization of computing resources is achieved.
In a second aspect, an embodiment of the present application provides an apparatus, including a database service center, the apparatus including:
the acquisition module is used for acquiring a database access request sent by the first service server; the first business server is any business server in a business service cluster with different business services;
the determining module is used for determining N second business servers with performance indexes meeting the set requirements from the business service cluster when the database access request is determined to have no piece-by-piece key information; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
The indication module is used for indicating the N second service servers to respectively execute the corresponding access commands to be executed; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
and the sending module is used for sending the database access result to the first service server based on the access result fed back by the at least one second service server.
The determining module is specifically configured to determine m databases where the sub-tables corresponding to the database access requests are located and a service server range specified by the database access requests;
determining performance indexes of n business servers in the range of the business server according to any database in the m databases;
according to the performance indexes of the m x N service servers, N second service servers with the performance indexes meeting the set requirements are determined, and m and N are positive integers.
The determining module is specifically configured to rank the n service servers according to performance indexes for any one of the m databases, and determine the first a service servers with the optimal performance indexes;
And determining N service servers with the largest occurrence number from the m-a service servers as second service servers.
The determining module is specifically configured to obtain, for any one of the n service servers and any one of the m databases, a plurality of operation performances of the service server in a history period;
determining a connection base according to whether the service server is connected with the database;
and determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient.
The running performance comprises: at least one of processor utilization, memory utilization, connection pool utilization;
the determining module is further configured to:
excluding from the n service servers that meet one or more of the following conditions:
the processor utilization is greater than a first threshold;
the memory usage is greater than a second threshold;
the connection pool utilization is greater than a third threshold.
The determining module is specifically configured to obtain each operation performance, connection base number and operation time of any service server in the history period;
And inputting each operation performance, the connection base and the operation time into a preset model, and determining a connection coefficient and a weight coefficient corresponding to each operation performance.
The determining module is further configured to:
and distributing database resources and resource identifiers corresponding to the database resources for the business servers based on the resource requests of the business servers for any business server in the business service cluster, wherein the resource identifiers are used for the business servers to determine database configuration.
The indication module is further configured to:
and when the database access request is determined to have the sharding key information, indicating the first service server to acquire an access result corresponding to the database access request according to the sharding key information.
In a third aspect, embodiments of the present application provide a computer readable storage medium storing a computer program which, when executed, performs any of the methods of the first aspect described above.
In a fourth aspect, embodiments of the present application provide a computing device comprising: a memory for storing program instructions; and a processor for calling program instructions stored in the memory and executing the method according to the obtained program.
In a fifth aspect, embodiments of the present application provide a computer program product for implementing a method as in any of the designs of the first aspect above, when the computer program product is run on a processor.
The advantages of the second to fifth aspects may be specifically referred to the advantages achieved by any of the designs of the first aspect, and are not described here again.
Drawings
FIG. 1 schematically illustrates a system architecture according to an embodiment of the present application;
FIG. 2 is a flow chart of a genetic method according to an embodiment of the present application;
fig. 3 is a schematic flow chart illustrating a database access method according to an embodiment of the present application;
fig. 4 schematically illustrates a business service cluster provided by an embodiment of the present application;
FIG. 5 schematically illustrates a system architecture according to an embodiment of the present application;
fig. 6 is a schematic flow chart illustrating a method for determining a second service server according to an embodiment of the present application;
FIG. 7 schematically illustrates a performance index ranking scheme according to an embodiment of the present application;
FIG. 8 illustrates a sample set data graph provided by an embodiment of the present application;
fig. 9 illustrates a schematic diagram of a database service center according to an embodiment of the present application.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application will be described in further detail below with reference to the accompanying drawings, and it is apparent that the described embodiments are only some embodiments of the present application, not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
Fig. 1 schematically illustrates a system architecture provided by an embodiment of the present application, where the system shown in fig. 1 includes a business service a, a business service B, a database 1, a database 2, and a Sharding-JDBC.
Business service a and business service B are specific service types, such as an Application (APP) or a settlement service. A plurality of servers are deployed in the business service for supporting the actual operation of business service a and business service B. The data generated during its actual operation is stored by the database or the data is retrieved from the database for actual operation. The Sbarding-JDBC is a distributed data access basic class library suitable for micro services, and completely realizes the functions of library division, table division, read-write separation and distributed primary key.
The databases 1 and 2 are used for storing the above data, one database can be connected with a plurality of business services, and one business service also has a plurality of databases connected with the business services. The operation process of business service generates a large amount of data, and the data amount is increased along with the expansion of business. After the generated data are fragmented, the fragmented data are scattered in different databases for storage, so that the pressure of a single database is reduced, and the efficiency of data query and storage is improved. In specific implementation, the sharding key can be set for different tables, is a database field for sharding, and is a key field for table level searching. For example, for a large order form, the mantissa of the order mark in the order form may be sliced, then the order mark is a slicing key, and the sub-bank or sub-form where the order is located is determined according to the order mark after the slicing. When inquiring, the slicing key can be determined by inputting order marks and taking the module, so that the corresponding sub-database or sub-table is found, and corresponding data are obtained.
However, when the database access is performed, the database access request does not necessarily carry the slicing key, but carries other non-slicing key information, and continuing the examples in the above sections, the service may carry the express bill identifier in the access request when the order form is queried, and at this time, the express bill identifier is subjected to modulo operation, so that the corresponding position of the database and the table can not be obtained, and the corresponding data can not be obtained. Therefore, a mapping table can be preset, namely, the mapping relation between the order identification and the express bill identification is stored. When the information carried in the access request is the express bill identifier, the order identifier corresponding to the express bill identifier can be acquired in the demapping table, and then the order identifier is subjected to modulo operation, so that the sub-library and the sub-table where the data to be accessed are located can be determined. However, in this way, a plurality of mapping tables corresponding to the sharding keys one by one must be stored, which increases the amount of data stored in the database, and more time is required for querying the mapping tables, thereby reducing the efficiency of database access.
In addition, the genetic relationship between non-fragmented bonds and fragmented bonds can also be calculated by genetic methods. The method comprises the steps of constructing a function F (x) between a slicing key and a non-slicing key in advance, calculating the slicing key through the function F after the non-slicing key is obtained, and then performing modular sampling to determine a corresponding sub-library or sub-table. Fig. 2 illustrates a flowchart of a genetic method provided by the embodiment of the present application, where, as shown in fig. 2, a set slice key is a user_id, information carried in an access request is a user_name, a user_id is obtained by calculating based on F (x) and the user_name, then the user_id is modulo 2, if a final result is 0, it is determined that data to be accessed is in database 0, and if a final result is 1, it is determined that data to be accessed is in database 1. In the above manner, although the one-to-one correspondence between the shard key and the non-shard key does not need to be stored in advance, F (x) of the shard key and the non-shard key needs to be predetermined, and after the specific non-shard key is acquired, the shard key needs to be calculated and determined for the non-shard key, which increases the calculation amount in database access and occupies more processor performance, thus also resulting in reduced efficiency of database access.
Based on the above, the embodiment of the application provides a database access method, which is used for reducing the occupancy rate of a processor, so that the access efficiency of a database is improved.
Fig. 3 is a schematic flow chart illustrating a method for accessing a database according to an embodiment of the present application, as shown in fig. 3, where the method includes:
step 301, obtaining a database access request sent by a first service server; the first service server is any service server in a service cluster with different service services.
Fig. 4 illustrates a schematic diagram of a business service cluster according to an embodiment of the present application. The service cluster shown in fig. 4 includes service a, service B, service C and service D, where each service includes a plurality of different service servers, and each service server is distinguished by serial numbers 1, 2, 3, 4, … and n. For example, the service A1 includes a service server A1, a service server A2, a service server A3, service servers A4 and … …, and a service server An. It should be understood that the sequence numbers herein are just one example, and that some business services may include only one server or less than 4 servers. Information of these business services and their corresponding servers are registered in the business service registration center. The service registry does not belong to the service cluster, and is shown to be included in a dashed box of the service cluster for clarity of wiring.
Corresponding to the service registry, fig. 4 may further include a service monitoring platform, as shown in fig. 5, for monitoring the performance of each server in the service registry.
The first service server in step 301 may be any service server in fig. 3, and in order to clearly illustrate the flow between step 301 and step 304, the service server A1 in the service a may be described as the first service server.
After receiving the operation instruction of the user, the service server A1 sends a database access request. Structured query statements (structured query language, SQL) are included in this database access request, and by parsing the SQL it is possible to determine whether the database access request contains shard key information. The context information is already described in the above background architecture, and will not be described in detail here.
Step 302, when determining that the database access request does not have the sharding key information, determining N second service servers with performance indexes meeting the set requirements from the service cluster; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
In step 301, if it is determined that the database access request does not have the shard key information, it is indicated that the specific database and the specific partition table corresponding to the database access request cannot be directly located according to the shard key. Therefore, in this step, N second service servers need to be determined from the service cluster. It should be noted that, the second service server and the first service server are different in name, and only the performance index meets the setting requirement, or may be the first service server itself.
In a possible manner, corresponding to step 302, when it is determined that the database access request has the sharded key information, the first service server is instructed to obtain an access result corresponding to the database access request according to the sharded key information.
Step 303, instructing the N second service servers to execute the corresponding access commands to be executed respectively; the access command to be executed is used for indicating the second service server to acquire an access result corresponding to the database access request from the corresponding database sub-table.
If the database access request is an update request, the database access request carries data to be written into the database, and the access result is the specific position of the data to be written into: store names, table names, etc. If the database access request is a query request, the database access request carries the name of the sub-table before the sub-table, for example, the sub-table is divided into an order table 1, an order table 2, an order table 3 and the like after the order table is divided into the sub-table, the query request carries an "order table", and the name of the sub-table at a specific position in the access result is "order table 1".
In addition, distinguishing between the query request and the update request may also determine whether the database access request accesses the master or slave. Specifically, a general query requests access to a slave library, while a database update requests access to a slave library.
And step 304, based on the access result fed back by at least one second service server, sending a database access result to the first service server.
In this step, the second server feeds back the access result and sends the access result to the first service server, which appears to the user to be the same as the access result directly obtained by the first service server.
By the method, N second business servers are determined in the business service cluster, the performance index of the server is determined according to the connection relation between the database where the sub-table corresponding to the database access request is located and the business servers and the operation performance of the business servers, the N second business servers which are used as the second business servers and are in accordance with the set requirements are screened out, the second business servers are instructed to execute the corresponding access command to be executed, and the access result is obtained. The method and the device can effectively reduce the pressure of executing the access command to be executed by a single business service, improve the execution efficiency and further improve the database access efficiency.
The following describes a method for determining N second service servers. Fig. 6 is a schematic flow chart illustrating a method for determining a second service server according to an embodiment of the present application, as shown in fig. 6, where the method includes:
step 601, determining m databases where the sub-tables corresponding to the database access requests are located and a service server range specified by the database access requests.
The database access request carries a large-class table name, and each corresponding sub-table and the database where the sub-table is located can be determined according to the table name. It should be understood that only the names of the sub-tables may be determined at this time, and not in which sub-table the data corresponding to the data access request is determined.
The service server range can be obtained from the service monitoring platform.
Step 602, determining performance indexes of n service servers in the service server range for any database in the m databases. The method specifically comprises the following steps:
step one: acquiring a plurality of operation performances of the service servers in a history period according to any service server in n service servers and any database in m databases; the operational performance may include processor usage, memory usage, connection pool usage, and the like.
For example, the historical period may be a collection period of 5 times in the past of the service monitoring platform, and for no data collection of 5 times, it is indicated that the service server has just been started, and no performance index rating is added. The calculation can be specifically performed with reference to the following formula:
service server CPU utilization:memory utilization rate of service server:time ratio for IO operations within a traffic server cycle: />Service server connection pool usage: />
Wherein CJ i Representing CPU utilization in the ith acquisition period; mi represents the memory usage in the ith acquisition period; i i Representing a time ratio for IO operation in an ith acquisition cycle; CM (CM) i Indicating the connection pool usage in the ith acquisition cycle.
Step two: and determining a connection base according to whether the service server is connected with the database.
Illustratively, when it is determined that the service server is connected to the database, the connection base number is recorded as 1; when the service server is determined to be disconnected from the database, the connection base number is recorded as 0; the service server and database connection may be due to an already established connection between the current service server and database and this connection is not cleaned up in the connection pool.
Step three: and determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient.
Illustratively, the performance index may be determined with the following formula:
wherein, SCpu is CPU usage rate weight coefficient, SMem is memory usage rate weight coefficient, SIo is time ratio weight coefficient for IO operation in period; SU is a connection pool usage weight coefficient; SC is the connection coefficient.
Step 603, determining N second service servers with performance indexes meeting the set requirements according to the performance indexes of m×n service servers, where m and N are positive integers.
For example, for any database in m databases, n service servers may be ranked according to performance indexes, and the first a service servers with the optimal performance indexes are determined, fig. 7 illustrates a performance index ranking schematic provided by an embodiment of the present application, as shown in fig. 7: m=5, and there are 5 databases, each of which corresponds to the performance index of all the service servers and is ranked according to the performance index. The first service server in the row is the service server with the optimal performance index, and so on. It should be noted that, except for one connection base and one connection coefficient, the service server and the database are in one-to-one correspondence, and the operation performance of the service server is the same for any database.
As shown in fig. 7, the service servers corresponding to the database 1 are ranked as A1, B2, C1, and B1 …, and then all the service servers include scores corresponding to the service servers, which are not described in detail herein.
Then let a=4, i.e. the first 4 service servers with the optimal performance index, the first 4 rows of servers in the corresponding graph.
Let n=3, i.e. the 3 service servers with the highest occurrence number are determined to be the second service server from the 5*4 service servers. As shown, the most frequently occurring is B2, and then A1 and C1, so far the second service server is determined to be B2, A1 and C1.
Since A1 is optimal for the performance metrics of database 1 and database 4, A1 can be used to access database 1 and database 4; b2 is optimal for the performance metrics of database 3 and database 5, and B2 may be used to access database 3 and database 5. The remaining database 3 may be assigned to C1 for access.
When the number of databases is large, if the number of databases allocated to one service server is excessive according to the rule, some databases can be allocated to the service servers with fewer corresponding databases.
The method for determining the connection coefficient and the weight coefficient corresponding to each operation performance is described as follows:
Step one: acquiring each operation performance, connection base and operation time of any service server in a history period;
step two: and inputting each operation performance, the connection base and the operation time into a preset model, and determining a connection coefficient and a weight coefficient corresponding to each operation performance.
Specifically according to the above formula:
can be simplified as:
f(x)＝K 0 X 0 +K 1 X 1 +K 2 X 2 +K 3 X 3 +K 4 X 4 +K 5
using a matrix representation:
f(x)＝KX
wherein,,i.e. a series of coefficients, K 5 Is a constant value, and is used for dynamically adjusting parameters.Is an input data matrix because of K 5 The constant term, so that the last column plus a column 1, a row of x, can be considered as a complete input data, the number of rows of the input matrix representing the data set, the number of m rows representing the data set, the number of columns representing a data set having n attributes, here 5 attributes.
Data set label set hereThe method comprises the following steps:is truly time consuming for the service server. When the real time consumption is calculated, the execution time of the corresponding statement needs to be subtracted from the collected operation time RT-m of the service server, so that the real time consumption of the service server is obtained.
What is to do at this time is to arrive at a series of parameters K such that f (x) =kx is as close to 1/y as possible. Fig. 8 illustrates a data graph of a sample set provided by the embodiment of the present application, as shown in fig. 8, the obtained K0-K5 values still have no unique result, and in addition, because the data of the sample set are not necessarily uniformly arranged on the straight line of the function f (x) =kx, noise reduction is required, so that cost loss is minimum, and a straight line is found by using a mean square error as a noise reduction function, so that the distance from all samples to the straight line is shortest.
Using a noise reduction function:
A T is the transposed matrix of A, which is transformed by the transposed matrix transformation formula |A T |*|A|＝A 2 The available noise reduction function is converted into a matrix:
then deriving an extremum to be 0 can solve k.
The derivation process is as follows:
and (3) unfolding to obtain:
deriving the above formula k:
and (3) related derivative formula:
order theAnd x is T x is a reversible matrix, A -1 Is the reversible matrix of A, and is converted into the formula |A by the reversible matrix -1 |a|= |e|=1, yielding:
k＝(x T x) -1 x T y
at this time, the samples of the X, Y set are input into the above formula, and the actual value of the k matrix, that is, the weight coefficient and the connection coefficient of each running performance, can be obtained by calculation.
In a possible implementation manner, before calculating the performance index, the service servers with the processor utilization rate greater than the first threshold and/or the memory utilization rate greater than the second threshold and/or the connection pool utilization rate greater than the third threshold may be excluded to reduce the screening number of the second service servers.
In one possible implementation manner, before the database access request sent by the first service server is obtained, a database resource and a resource identifier corresponding to the database resource can be allocated to the service server based on the resource request of the service server for any service server in the service cluster, and the resource identifier is used for determining the database configuration by the service server. The database configuration may include: the database stores information such as storage capacity, filing time, whether to divide the database into tables, and a slicing key.
By the method, after the resource identifier is allocated to the service server, the database configuration and the corresponding resource locator can be determined according to the resource identifier, so that the service server is not required to be notified when the database is required to be migrated, and the database management efficiency is benefited.
Fig. 8 schematically illustrates a schematic diagram of a database service center provided by the embodiment of the present application, where the database service center shown in fig. 8 includes a database gateway 01, a task scheduling center 02, and a database information configuration center 03, and a connection relationship of the database service center is shown in fig. 8.
The database gateway 01 is used for analyzing the database access request and analyzing SQL sentences of the database access request to judge whether the database access request carries a sharding key or not. According to whether the slicing key is carried, the task scheduling center 02 is caused to execute different operations, specifically as follows:
the task scheduling center 02 determines N second service servers with performance indexes meeting set requirements from the service cluster based on the service performance monitoring platform 11 and the database information configuration center 03 when determining that the database access request does not have the fragmentation key information; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
The task scheduling center 02 instructs the N second service servers to execute the corresponding access commands to be executed respectively; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
the task scheduling center 02 sends a database access result to the first service server based on the access result fed back by the at least one second service server.
In a possible implementation manner, the task scheduling center 02 may determine the corresponding database configuration from the database information configuration center 03 according to the resource identifier, where the information is stored in the database information configuration center 03 as information such as a database storage capacity, an archiving time, whether to divide a database into a table, and a sharding key.
Based on the same technical conception, the embodiment of the application also provides a database access device. Fig. 9 is a schematic diagram of a database access device according to an embodiment of the present application, where the device may perform the foregoing database access method, as shown in fig. 9, and the device includes:
the acquisition module is used for acquiring a database access request sent by the first service server; the first business server is any business server in a business service cluster with different business services;
The determining module is used for determining N second business servers with performance indexes meeting the set requirements from the business service cluster when the database access request is determined to have no piece-by-piece key information; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
the indication module is used for indicating the N second service servers to respectively execute the corresponding access commands to be executed; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
and the sending module is used for sending the database access result to the first service server based on the access result fed back by the at least one second service server.
The determining module is specifically configured to determine m databases where the sub-tables corresponding to the database access requests are located and a service server range specified by the database access requests;
determining performance indexes of n business servers in the range of the business server according to any database in the m databases;
According to the performance indexes of the m x N service servers, N second service servers with the performance indexes meeting the set requirements are determined, and m and N are positive integers.
The determining module is specifically configured to rank the n service servers according to performance indexes for any one of the m databases, and determine the first a service servers with the optimal performance indexes;
and determining N service servers with the largest occurrence number from the m-a service servers as second service servers.
The determining module is specifically configured to obtain, for any one of the n service servers and any one of the m databases, a plurality of operation performances of the service server in a history period;
determining a connection base according to whether the service server is connected with the database;
and determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient.
The running performance comprises: at least one of processor utilization, memory utilization, connection pool utilization;
the determining module is further configured to:
Excluding from the n service servers that meet one or more of the following conditions:
the processor utilization is greater than a first threshold;
the memory usage is greater than a second threshold;
the connection pool utilization is greater than a third threshold.
The determining module is specifically configured to obtain each operation performance, connection base number and operation time of any service server in the history period;
and inputting each operation performance, the connection base and the operation time into a preset model, and determining a connection coefficient and a weight coefficient corresponding to each operation performance.
The determining module is further configured to:
and distributing database resources and resource identifiers corresponding to the database resources for the business servers based on the resource requests of the business servers for any business server in the business service cluster, wherein the resource identifiers are used for the business servers to determine database configuration.
The indication module is further configured to:
and when the database access request is determined to have the sharding key information, indicating the first service server to acquire an access result corresponding to the database access request according to the sharding key information.
Based on the same technical idea, the embodiments of the present invention also provide a computer program product, which when run on a processor, implements the method shown in the above embodiments.
Based on the same technical concept, the embodiment of the application further provides a computing device, which comprises: a memory for storing program instructions;
and a processor for calling the program instructions stored in the memory and executing the method shown in the above embodiment according to the obtained program.
Based on the same technical idea, the embodiments of the present application also provide a computer-readable storage medium, which when run on a processor, implements the method shown in the above embodiments.
It will be appreciated by those skilled in the art that 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 the application. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations 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.
It will be apparent to those skilled in the art that various modifications and variations can be made to the present application without departing from the spirit or scope of the application. Thus, it is intended that the present application also include such modifications and alterations insofar as they come within the scope of the appended claims or the equivalents thereof.
Claims (11)
1. A database access method, for use in a database service center, the method comprising:
acquiring a database access request sent by a first service server; the first business server is any business server in a business service cluster with different business services;
when the database access request is determined to have no fragment key information, N second business servers with performance indexes meeting the set requirements are determined from the business service cluster; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
indicating the N second service servers to execute corresponding access commands to be executed respectively; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
and sending a database access result to the first service server based on the access result fed back by at least one second service server.
2. The method of claim 1, wherein determining N second service servers from the service cluster whose performance indicators meet a set requirement comprises:
Determining m databases in which the sub-tables corresponding to the database access requests are located and a service server range designated by the database access requests;
determining performance indexes of n business servers in the range of the business server according to any database in the m databases;
according to the performance indexes of the m x N service servers, N second service servers with the performance indexes meeting the set requirements are determined, and m and N are positive integers.
3. The method of claim 2, wherein determining N second service servers whose performance metrics meet the set requirements based on the performance metrics of the m x N service servers comprises:
sequencing the n service servers according to performance indexes aiming at any one database in the m databases, and determining the first a service servers with the optimal performance indexes;
and determining N service servers with the largest occurrence number from the m-a service servers as second service servers.
4. The method of claim 2, wherein,
determining a performance index of each of the n service servers under each of the m databases as follows, including:
Acquiring a plurality of operation performances of the service server in a history period according to any service server in the n service servers and any database in the m databases;
determining a connection base according to whether the service server is connected with the database;
and determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient.
5. The method of claim 4, wherein the operational performance comprises: at least one of processor utilization, memory utilization, connection pool utilization;
before determining the performance index of the service server under the database according to the plurality of operation performances, the weight coefficient corresponding to each operation performance, the connection base and the connection coefficient, the method further comprises:
excluding from the n service servers that meet one or more of the following conditions:
the processor utilization is greater than a first threshold;
the memory usage is greater than a second threshold;
the connection pool utilization is greater than a third threshold.
6. The method of claim 4, further comprising determining the connection coefficients and the weight coefficients for each runnability according to the following:
Acquiring each operation performance, connection base and operation time of any service server in a history period;
and inputting each operation performance, the connection base and the operation time into a preset model, and determining a connection coefficient and a weight coefficient corresponding to each operation performance.
7. The method according to any one of claims 1-6, further comprising, prior to obtaining the database access request sent by the first service server:
and distributing database resources and resource identifiers corresponding to the database resources for the business servers based on the resource requests of the business servers for any business server in the business service cluster, wherein the resource identifiers are used for the business servers to determine database configuration.
8. The method of any one of claims 1-6, wherein the method further comprises:
and when the database access request is determined to have the sharding key information, indicating the first service server to acquire an access result corresponding to the database access request according to the sharding key information.
9. A database access apparatus for inclusion in a database service center, said apparatus comprising:
the acquisition module is used for acquiring a database access request sent by the first service server; the first business server is any business server in a business service cluster with different business services;
The determining module is used for determining N second business servers with performance indexes meeting the set requirements from the business service cluster when the database access request is determined to have no piece-by-piece key information; the performance index is determined through the connection relation between the database of the sub-table corresponding to the database access request and the service server and the operation performance of the service server; n is a positive integer;
the indication module is used for indicating the N second service servers to respectively execute the corresponding access commands to be executed; the access command to be executed is used for indicating a second service server to acquire an access result corresponding to the database access request from a corresponding database sub-table;
and the sending module is used for sending the database access result to the first service server based on the access result fed back by the at least one second service server.
10. A computer readable storage medium, characterized in that the computer readable storage medium stores a computer program which, when run, performs the method according to any one of claims 1 to 8.
11. A computing device, comprising:
A memory for storing program instructions;
a processor for invoking program instructions stored in said memory to perform the method of any of claims 1-8 in accordance with the obtained program.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310757817.2A CN116775607A (en) | 2023-06-26 | 2023-06-26 | Database access method, device, medium and equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310757817.2A CN116775607A (en) | 2023-06-26 | 2023-06-26 | Database access method, device, medium and equipment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116775607A true CN116775607A (en) | 2023-09-19 |
Family
ID=87989150
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310757817.2A Pending CN116775607A (en) | 2023-06-26 | 2023-06-26 | Database access method, device, medium and equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116775607A (en) |
-
2023
- 2023-06-26 CN CN202310757817.2A patent/CN116775607A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107783985B (en) | Distributed database query method, device and management system | |
| US6801903B2 (en) | Collecting statistics in a database system | |
| CN110147407B (en) | Data processing method and device and database management server | |
| KR101365464B1 (en) | Data management system and method using database middleware | |
| EP1065606A2 (en) | Method and apparatus for identifying preferred indexes for databases | |
| US8271523B2 (en) | Coordination server, data allocating method, and computer program product | |
| CN107784030B (en) | Method and device for processing connection query | |
| US8051058B2 (en) | System for estimating cardinality in a database system | |
| US20070067261A1 (en) | System and a method for identifying a selection of index candidates for a database | |
| JP2004518226A (en) | Database system and query optimizer | |
| CN108536808B (en) | Spark calculation framework-based data acquisition method and device | |
| WO1997022939A1 (en) | Specifying indexes for relational databases | |
| CN107180031B (en) | Distributed storage method and device, and data processing method and device | |
| CN108009270A (en) | A kind of text searching method calculated based on distributed memory | |
| CN111324604A (en) | Database table processing method and device, electronic equipment and storage medium | |
| CN110941602B (en) | Database configuration method and device, electronic equipment and storage medium | |
| CN111428140B (en) | High concurrency data retrieval method, device, equipment and storage medium | |
| CN108733781A (en) | The cluster temporal data indexing means calculated based on memory | |
| CN116881287A (en) | Data query method and related equipment | |
| CN113568931A (en) | Route analysis system and method for data access request | |
| CN111125199A (en) | Database access method and device and electronic equipment | |
| CN111984625A (en) | Database load characteristic processing method, device, medium and electronic equipment | |
| CN116775607A (en) | Database access method, device, medium and equipment | |
| CN113360551B (en) | Method and system for storing and rapidly counting time sequence data in shooting range | |
| CN115114012B (en) | Task allocation method and device, electronic equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication |