CN111897813A - Flow control method and device for database resources - Google Patents
Flow control method and device for database resources Download PDFInfo
- Publication number
- CN111897813A CN111897813A CN202010653289.2A CN202010653289A CN111897813A CN 111897813 A CN111897813 A CN 111897813A CN 202010653289 A CN202010653289 A CN 202010653289A CN 111897813 A CN111897813 A CN 111897813A
- Authority
- CN
- China
- Prior art keywords
- flow control
- sql
- database
- field
- syntax tree
- 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.)
- Granted
Links
Images
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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
-
- 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/23—Updating
- G06F16/2379—Updates performed during online database operations; commit processing
-
- 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/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24552—Database cache management
Abstract
The invention discloses a flow control method and device for database resources, which can quickly sense the resource consumption condition of a database and perform flow control on the current request service. The method comprises the following steps: intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache; acquiring table definition information from a database at regular time and storing the table definition information into a third-party cache; finding out an abstract syntax tree from a third-party cache based on SQL primary key values and extracting field information in the abstract syntax tree; preparing to implement one or more levels of flow control by combining field information and a sublist number or a database server number according to a configured flow control strategy; and splicing the field information to generate a character string, obtaining a SQL resource primary KEY value through Hash calculation, taking the resource primary KEY value as a resource access amount of the KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a flow control method and device for database resources.
Background
With the rapid development of internet services, the provided product services are more and more abundant, great convenience is brought to the daily life of people, the rapid development of economy is promoted, and meanwhile, more severe requirements on high reliability, high concurrency, low delay and low cost of an internet system are provided.
The current flow control strategies or mechanisms in the internet field are various, and a plurality of existing open source frameworks exist, but flow control is performed in a service layer, when setting each service flow control threshold, the number of calls of each service in each machine in unit time needs to be calculated according to the system processing capacity, the number of application servers and each service call proportion of a production environment, the number of calls of each machine in each machine is obtained through a complex formula to serve as the flow control threshold, but the real production environment is a very complex operation environment, and network jitter, machine resource change, service call proportion change and the like can cause the change of the processing capacity of the system.
In order to improve the high concurrency and high availability of the system, most enterprises can carry out multi-computer-room cluster deployment on the system, disaster mutual backup is realized, and database division and table division are realized simultaneously, so that the system can be randomly expanded and contracted in theory, but the expansion and contraction of the database require data reformation, routing rules need adjustment, the expansion cost is high, and the expansion and contraction can be carried out at any time unlike application service, so that the database resources are still key protection objects.
Disclosure of Invention
The invention aims to provide a flow control method and device for database resources, which can quickly sense the resource consumption condition of a database and perform flow control on the current request service so as to relieve the temporary huge impact on a system caused by high peak of transaction amount and ensure the efficient and stable operation of the system.
In order to achieve the above object, a first aspect of the present invention provides a flow control method for database resources, including:
intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache;
the method comprises the steps that table definition information including a table name, a field list, a main key and an index field is obtained from a database at regular time and stored in a third-party cache, wherein the field list comprises a field name list, a field type list and a field length list;
finding out the abstract syntax tree from a third-party cache based on SQL primary key values of the SQL and extracting field information in the abstract syntax tree, wherein the field information comprises any one or more of table names, operation types and field key value pairs;
preparing to implement one or more levels of flow control according to a configured flow control strategy by combining the field information or simultaneously combining a sub-table number or a database server number corresponding to the abstract syntax tree, wherein the levels comprise a record level, a sub-table level, a table level and a library level;
and splicing the field information to generate a character string, obtaining a primary resource KEY value of the SQL after Hash calculation, taking the primary resource KEY value as a resource access amount of a KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
Preferably, the method for intercepting the SQL parsing submitted by the application server to the database and converting the SQL parsing into the abstract syntax tree comprises the following steps:
intercepting SQL submitted to a database by an application server through a proxy JDBC connector, and using an SQL parsing function in a dry connection pool management open source component to parse and convert the SQL into an abstract syntax tree.
Preferably, the method for caching the SQL primary key value obtained by the SQL hash and the abstract syntax tree in a third-party cache correspondingly comprises:
and taking the hash result of the SQL calculated by the MD5 as an SQL main key value, and caching the SQL main key value and the abstract syntax tree in a third-party cache in a key value pair mode.
Preferably, the method of periodically obtaining table definition information including a table name, a field list, a primary key and an index field from a database and storing the table definition information in a third-party cache includes:
obtaining table definition information including a table name, a field list, a primary key and an index field corresponding to each table in a database through a proxy JDBC connector;
and caching the table definition information of each table in the database into a third-party cache, and updating the table definition information in the third-party cache at regular time.
Preferably, according to the configured flow control policy, the method for preparing to implement one or more levels of flow control by combining the field information or simultaneously combining the table number or the database server number corresponding to the abstract syntax tree includes:
the configured flow control strategy comprises one or more of record level flow control, table level flow control and library level flow control;
when the configured flow control strategy is a record level flow control strategy, extracting a table name and an operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, simultaneously acquiring an index field from corresponding table definition information based on the table name, acquiring a corresponding index field value from a field key value pair list of WHERE conditions in the SQL according to the index field, and preparing to implement record level flow control;
when the configured flow control strategy is a table-level flow control strategy, extracting the table name and the operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, and simultaneously acquiring the sub-table number corresponding to the abstract syntax tree to prepare for implementing table-level flow control;
when the configured flow control strategy is a table level flow control strategy, extracting a table name and an operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, and preparing to implement table level flow control;
and when the configured flow control strategy is a library-level flow control strategy, extracting the table name and the operation type corresponding to the SQL from the abstract syntax tree in the third-party cache, and simultaneously acquiring the database server number corresponding to the abstract syntax tree to prepare for implementing the library-level flow control.
Preferably, the method for splicing the field information to generate a character string and obtaining the primary resource key value of the SQL after hash calculation includes:
splicing the table name, the operation type and the index field value to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
splicing the table name, the operation type and the sublist number to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
splicing the table name and the operation type to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
and splicing the table name, the operation type and the database server number to generate a character string, and hashing the character string through MD5 to be used as the SQL resource primary key value.
Further, the method for taking the resource primary KEY value as the resource access amount of the KEY value statistical database in unit time and implementing corresponding level flow control when the resource access amount exceeds the flow control threshold value comprises the following steps:
taking the primary resource KEY VALUEs as KEY VALUEs to be counted in a third-party cache, and taking the resource access amount of the database in unit time as a VALUE VALUE to be accumulated;
and when the accumulated result exceeds the flow control threshold, implementing one or more of the matched record-level flow control, the table-level flow control and the library-level flow control.
Further, the operation types are insertion operation, deletion operation, update operation and query operation, and the flow control threshold is a byte flow control threshold and a frequency flow control threshold;
when the operation type is an insertion operation or a deletion operation, selecting a byte number flow control threshold value as the flow control threshold value;
and when the operation type is an updating operation or an inquiring operation, selecting a flow control threshold for times.
Compared with the prior art, the flow control method for the database resources provided by the invention has the following beneficial effects:
the flow control method for the database resources, provided by the invention, comprises the steps of obtaining SQL submitted to a database by an application server, converting the SQL into an abstract syntax tree after analyzing the SQL, storing the abstract syntax tree in a third-party cache in a mode of taking the result of Hash of the SQL through MD5 as a SQL primary key VALUE, simultaneously storing the abstract syntax tree in the third-party cache in a key VALUE pair mode as VALUE, further, obtaining table definition information including a table name, a field list, a primary key and an index field of each table from the database at regular time, storing the table definition information into the third-party cache in the same mode, firstly searching the corresponding abstract syntax tree from the third-party cache according to the SQL primary key VALUE in the process of matching a flow control item, further extracting any one or more kinds of field information including the table name, the operation type and the key VALUE pair, and then combining the field information or simultaneously combining a sub-table number corresponding to the abstract syntax tree or a database server number according to the configured flow control strategy to prepare and implement one table definition tree Matching one or more levels of flow control items, such as record level, table level and library level flow control items, splicing field information into character strings, then implementing consumption statistics on database resources, namely calculating SQL (structured query language) resource primary KEY values through Hash, taking the resource primary KEY values as KEY values to count the resource access amount of the database in unit time, and implementing the flow control of the corresponding level when the resource access amount exceeds a flow control threshold value.
Therefore, the method and the system can quickly sense the resource consumption condition of the database and implement multi-level flow control measures on the current request service, further realize accurate control on each resource of the database, relieve the temporary huge impact on the system caused by high peak of transaction amount, and ensure the efficient and stable operation of the system.
A second aspect of the present invention provides a flow control apparatus for database resources, which is applied to the flow control method for database resources in the foregoing technical solution, and the apparatus includes:
the data interception unit is used for intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache;
the data acquisition unit is used for acquiring table definition information comprising a table name, a field list, a primary key and an index field from a database at regular time and storing the table definition information into a third-party cache, wherein the field list comprises a field name list, a field type list and a field length list;
a field extraction unit, configured to find the abstract syntax tree from a third-party cache based on the SQL primary key of the SQL and extract field information therein, where the field information includes any one or more of a table name, an operation type, and a pair of field keys;
the flow control item matching unit is used for combining the field information or simultaneously combining the sub-table number or the database server number corresponding to the abstract syntax tree to prepare and implement one or more levels of flow control according to the configured flow control strategy, wherein the levels comprise a record level, a table level and a library level;
and the flow control implementation unit is used for splicing the field information to generate a character string, obtaining the SQL resource primary KEY value through Hash calculation, taking the resource primary KEY value as the resource access amount of the KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
Compared with the prior art, the beneficial effect of the flow control device for the database resources provided by the invention is the same as that of the flow control method for the database resources provided by the technical scheme, and the detailed description is omitted here.
A third aspect of the present invention provides a computer-readable storage medium, on which a computer program is stored, which, when executed by a processor, performs the above-mentioned steps of the flow control method for database resources.
Compared with the prior art, the beneficial effects of the computer-readable storage medium provided by the invention are the same as those of the flow control method for the database resources provided by the technical scheme, and are not repeated herein.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention and not to limit the invention. In the drawings:
fig. 1 is a schematic flowchart of a method for controlling flow of database resources according to a first embodiment of the present invention;
fig. 2 is a system architecture diagram to which a method for controlling flow of database resources is applied in accordance with an embodiment of the present invention;
FIG. 3 is an exemplary diagram of a table dividing number according to an embodiment of the present invention.
Detailed Description
In order to make the aforementioned objects, features and advantages of the present invention comprehensible, embodiments accompanied with figures are described in detail below. It is to be understood that the described embodiments are merely exemplary of the invention, and not restrictive of the full scope of the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Example one
Referring to fig. 1 and fig. 2, the present embodiment provides a flow control method for database resources, including:
intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache; the method comprises the steps that table definition information including table names, field lists, main keys and index fields is obtained from a database at regular time and stored in a third-party cache, and the field lists include field name lists, field type lists and field length lists; finding out an abstract syntax tree from a third-party cache based on SQL (structured query language) primary key values and extracting field information in the abstract syntax tree, wherein the field information comprises any one or more of table names, operation types and field key value pairs; matching one or more grades of flow control items according to the field information or the sub-table number or the database server number corresponding to the abstract syntax tree, wherein the grades comprise a record grade, a sub-table grade, a table grade and a library grade; and splicing the field information to generate a character string, obtaining a SQL resource primary KEY value through Hash calculation, taking the resource primary KEY value as a resource access amount of the KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
In specific implementation, a flow control method for database resources is developed into a flow control component, and the flow control component is provided in a local component manner, so as to fulfill the function of a central controller, and provide a unified database resource flow control mechanism for an entire application server, so that the flow control component in the application can be matched with the central controller. Specifically, the central controller is realized by using java language, is provided for the system in the form of jar package, is integrated in the application server, and provides the flow control function in a transparent manner, namely, for the application system, the flow control component is invisible, and the application can be rapidly deployed in the system without modifying the system, so that the implementation cost of the scheme of the application is reduced.
In the flow control method for database resources provided in this embodiment, SQL submitted to a database by an application server is acquired, the SQL is converted into an abstract syntax tree after being parsed and stored in a third-party cache, the storage method is that a result obtained by hashing the SQL by an MD5 is used as a primary SQL key VALUE, and the abstract syntax tree is used as a VALUE to be stored in the third-party cache in a key-VALUE-pair manner, in addition, table definition information including a table name, a field list, a primary key, and an index field of each table is also periodically acquired from the database and stored in the third-party cache, during a process of matching a flow control item, first, a corresponding abstract syntax tree needs to be searched from the third-party cache according to the primary SQL key VALUE of the SQL, and then, field information including any one or more of the table name, the operation type, and the field key VALUE pair is extracted, and then, according to a configured flow control policy, the field information is combined or simultaneously combined with a sub-table number or a database server number corresponding to the abstract syntax Matching one or more levels of flow control items, such as record level, table level and library level flow control items, splicing field information into character strings, then implementing consumption statistics on database resources, namely calculating SQL resource primary KEY values through Hash, taking the resource primary KEY values as KEY values to count the resource access amount of the database in unit time, and implementing the flow control of the corresponding level when the resource access amount exceeds a flow control threshold value.
Therefore, the embodiment can quickly sense the resource consumption condition of the database and implement multi-level flow control measures on the current request service, so that accurate control on various resources of the database is realized, huge impact on the system caused by temporary high peak of transaction amount can be relieved, and efficient and stable operation of the system is guaranteed.
In the above embodiment, the method for intercepting SQL parsing submitted by an application server to a database and converting the SQL parsing into an abstract syntax tree includes:
and intercepting SQL submitted to a database by an application server through a proxy JDBC connector, and using an SQL parsing function in a dry connection pool management open source component to parse and convert the SQL into an abstract syntax tree.
In the above embodiment, the method for caching the SQL primary key value obtained by the SQL hash and the abstract syntax tree in the third-party cache includes:
and taking the hash result of SQL calculated by the MD5 as a SQL primary key value, and caching the SQL primary key value and the abstract syntax tree in a third-party cache in a key value pair mode.
It can be understood that the interception of SQL by the central controller is implemented by means of proxying the JDBC connector, the process of submitting SQL and waiting for execution of the result by the application server is the same as the process of using the native JDBC connector, the central controller returns the processing result after the processing is completed, and the application server has no any perception on the intermediate processing process, which is equivalent to a database self-contained flow control mechanism.
In specific implementation, the central controller captures SQL submitted to the database by the application server through the proxy JDBC connector, and uses the SQL parsing function in the managed open source component of the managed pool of the current connection pool to parse and convert the SQL into an abstract syntax tree, so as to obtain the table name (database table name), the operation type and the field key value pair list of the SQL operation. The operation types comprise query, insertion, update and deletion. In addition, in order to avoid parsing after capturing SQL each time, the abstract syntax tree is cached after the first parsing, so as to save system resources and improve processing efficiency. And during caching, taking the hash result of SQL calculated by MD5 as SQL primary key value, and storing the SQL primary key value and the analyzed abstract syntax tree into a third-party cache in a key-value pair mode. When obtaining the SQL, the SQL is hashed by using the MD5 to obtain the SQL primary key value, and then the abstract syntax tree corresponding to the SQL is searched in the third-party cache.
In the above embodiment, the method of periodically obtaining table definition information including a table name, a field list, a primary key, and an index field from a database and storing the table definition information in a third-party cache includes:
the method comprises the steps that table definition information including a table name, a field list, a primary key and an index field corresponding to each table in a database is obtained through a proxy JDBC connector, wherein the field list includes information including a field name, a field type, a field length and the like and is used for matching flow control items and calculating resource consumption; and caching the table definition information of each table in the database into a third-party cache, and updating the table definition information in the third-party cache at regular time.
In specific implementation, the table definition information is cached in a third-party cache after being acquired, the third-party cache preferably uses redis, and meanwhile, the third-party cache is set to acquire the table definition information of each table at regular time, for example, the table definition information is acquired at regular time every 1 hour, and if the required table definition information does not exist in the third-party cache during use, the third-party cache can actively ask for the table definition information from the database and cache the table definition information.
The method for preparing to implement one or more levels of flow control by combining field information or simultaneously combining a sublist number or a database server number corresponding to an abstract syntax tree according to a configured flow control policy in the above embodiments includes:
the configured flow control strategy comprises one or more of record level flow control, table level flow control and library level flow control;
when the configured flow control strategy is a record level flow control strategy, extracting a table name and an operation type corresponding to SQL from an abstract syntax tree in a third-party cache, simultaneously acquiring an index field from corresponding table definition information based on the table name, acquiring a corresponding index field value from a field key value pair list under WHERE conditions in SQL according to the index field, and preparing to implement record level flow control;
when the configured flow control strategy is a table-level flow control strategy, extracting a table name and an operation type corresponding to SQL from an abstract syntax tree in a third-party cache, and simultaneously acquiring a table number corresponding to the abstract syntax tree to prepare for implementing table-level flow control;
when the configured flow control strategy is a table level flow control strategy, extracting a table name and an operation type corresponding to SQL from an abstract syntax tree in a third-party cache, and preparing to implement table level flow control;
and when the configured flow control strategy is a library-level flow control strategy, extracting the table name and the operation type corresponding to the SQL from the abstract syntax tree in the third-party cache, and simultaneously acquiring the database server number corresponding to the abstract syntax tree to prepare for implementing the library-level flow control.
Illustratively, the configuration rules of the flow control policy are as follows:
it should be explained that:
name: the name of the flow control rule needs to be kept unique;
tableName: the table name of the flow control table is required;
LimitType: the flow control level, the values of which are illustrated as follows:
| flow control classes | Description of the invention |
| record | Recording stage |
| subtable | Grading of tables |
| table | Meter level |
| DB | Library level |
Opartype: types of operations, including update, select, insert, delete;
LimitCount: a flow control threshold value in unit time;
flag: a switch for which the rule is valid. close-not effective, open-effective;
remark: information is described.
In specific implementation, if the flow control level configured in the flow control strategy is a record level, the table name and the operation type of the SQL need to be acquired from a third-party cache, then an index field is acquired from corresponding table definition information in the third-party cache based on the table name, and a corresponding index field value is acquired from a field key value pair list of WHERE conditions in the SQL according to the index field, until the specific record of the SQL to be operated is determined to be completed, record level flow control can be prepared to be implemented;
similarly, if the flow control level configured in the flow control policy is a sub-table level, the table name and the operation type of the SQL and the corresponding sub-table number need to be obtained from the third-party cache, and the sub-table level flow control can be prepared to be implemented until the specific sub-table to be operated by the SQL is determined. It should be noted that, because the SQL is intercepted from the application server, the table name obtained at this time is the table name before the table is not divided, and the table number needs to be calculated according to the table dividing field of the sub-library and the table dividing rule, and the method of calculating the table number by the table dividing rule here is well known to those skilled in the art, and this embodiment will not be described in detail here.
Similarly, if the flow control level configured in the flow control policy is table level, the table name and the operation type of the SQL need to be acquired from the third-party cache, and the table-level flow control can be prepared to be implemented until the specific table to be operated by the SQL is determined.
Similarly, if the flow control level configured in the flow control policy is at a library level, the table name and the operation type of the SQL and the corresponding database server number need to be obtained from the third-party cache, and the library-level flow control can be prepared to be implemented until the specific database server to be operated by the SQL is determined to be completed. It should be noted that the method for calculating the number of the database server through the sub-database and sub-table field and the sub-table rule is well known to those skilled in the art, and this embodiment will not be described in detail herein.
The database server number is selected for the flow control strategy at the database level, and the database IP is not used, because a layer of database sub-table components are required to be added from an application server layer to the middle of a database layer in the database sub-table system to serve as a sub-table routing layer, and the application server is directly connected with the database sub-table components, the database IP cannot be directly acquired.
Exemplarily, as shown in fig. 3, a single table is divided into 512 sub-tables, and the numbers of the sub-tables are sequentially assigned to the 512 sub-tables, where the sub-table with the number 1-100 belongs to the database server 1, the sub-table with the number 101-.
In the above embodiment, the method for generating the character string by splicing the field information and obtaining the primary key value of the SQL resource through hash calculation includes:
splicing the table name, the operation type and the index field value to generate a character string, and hashing the character string through MD5 to be used as a SQL resource primary key value; splicing the table name, the operation type and the sublist number to generate a character string, and hashing the character string through MD5 to be used as a SQL resource primary key value; splicing the table name and the operation type to generate a character string, and hashing the character string through MD5 to be used as a SQL resource primary key value; and splicing the table name, the operation type and the database server number to generate a character string, and hashing the character string through MD5 to be used as a SQL resource primary key value.
In the above embodiment, the method for taking the primary resource KEY value as the resource access amount of the KEY value statistical database in unit time and implementing the corresponding level flow control when the resource access amount exceeds the flow control threshold includes:
taking the primary KEY VALUEs of the resources as KEY VALUEs to be counted in a third-party cache, and taking the resource access amount of the database in unit time as a VALUE VALUE to be accumulated; and when the accumulated result exceeds the flow control threshold, implementing one or more of the matched record-level flow control, the table-level flow control and the library-level flow control.
The resource primary key value can be understood as: after matching the corresponding character string of a certain database resource according to the flow control policy, hashing the character string by using MD5 as a unique key value in a third-party cache for identifying the resource consumption, such as: if there is an account table accountinfo, where the account 0000001 transaction amount is large, and the number of updates of the account in unit time needs to be limited, the character string corresponding to the resource is updateaccountinfo0000001, then the character string is hashed through MD5, the hashed VALUE is stored as a KEY VALUE in a third-party cache, and a VALUE in the third-party cache is the remaining number of updates of the account in unit time.
The operation types in the embodiment are insertion operation, deletion operation, updating operation and query operation, and the flow control threshold is a byte flow control threshold and a frequency flow control threshold;
when the operation type is an insertion operation or a deletion operation, selecting a byte flow control threshold value as the flow control threshold value; and when the operation type is an updating operation or an inquiring operation, selecting a flow control threshold for times.
In specific implementation, the embodiment can realize control over the access amount of database IO, CPU, and hotspot data, and the specific process is described as follows:
using the primary KEY VALUE of the resource obtained in the above embodiment as a KEY VALUE to perform statistics in a third-party cache, using a VALUE as a resource access amount, firstly checking whether the primary KEY VALUE of the resource exists in the third-party cache, if so, directly accumulating the VALUE, and if not, firstly creating a record. The central processing unit obtains a corresponding resource consumption value in the third-party cache according to the resource primary key value, compares the resource consumption value with a flow control threshold value in flow control item configuration, performs flow control if the resource consumption value is greater than or equal to the flow control threshold value, and returns an sqlcode value defined for the flow control by the application server; and if the resource consumption value is smaller than the flow control threshold value, normally submitting the SQL to the database.
The whole statistical process uses atomic operation, and the accuracy of statistical data under a multithread execution scene is guaranteed. The flow control thresholds for each operation type are illustrated as follows:
because the insertion and deletion operations consume large database resources IO, the number of bytes written into the database in unit time of the insertion and deletion operations needs to be controlled, so the number of bytes flow control threshold needs to be set for the insertion and deletion operations, and the number of bytes of the database can be obtained from the table definition information.
Because the access quantity of the database resource CPU and the hotspot data is greatly influenced by the updating and inquiring operations, the execution times of the updating and inquiring operations in unit time need to be controlled, and the times flow control threshold value needs to be set for the updating and inquiring operations.
In conclusion, the embodiment can quickly sense and accurately control the resource consumption of the database, the stable operation of the system is protected, and compared with a service layer flow control mechanism, the embodiment does not need to estimate the transaction amount of each service and develop a corresponding pressure measurement script aiming at different services and different business elements, the workload of research and development personnel is reduced, and the work efficiency of the research and development personnel is greatly improved.
In addition, the present embodiment can implement four levels of flow control policies, including: the method comprises the steps of recording, sorting, updating, table-level and library-level, combining with SQL operation types (inserting, updating and inquiring) submitted to a database by an application server, realizing control on resources such as database IO, CPU, hotspot data updating and the like through multi-dimensional flow control configuration, and meanwhile, configuring corresponding flow control strategies according to different data tables used by different service modules, dynamically adjusting system resources occupied by each service, realizing full utilization of the system resources and reducing excessive deployment of the system resources.
Example two
The present embodiment provides a flow control apparatus for database resources, including:
the data interception unit is used for intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache;
the data acquisition unit is used for acquiring table definition information comprising a table name, a field list, a primary key and an index field from a database at regular time and storing the table definition information into a third-party cache, wherein the field list comprises a field name list, a field type list and a field length list;
a field extraction unit, configured to find the abstract syntax tree from a third-party cache based on the SQL primary key of the SQL and extract field information therein, where the field information includes any one or more of a table name, an operation type, and a pair of field keys;
the flow control item matching unit is used for combining the field information or simultaneously combining the sub-table number or the database server number corresponding to the abstract syntax tree to prepare and implement one or more levels of flow control according to the configured flow control strategy, wherein the levels comprise a record level, a table level and a library level;
and the flow control implementation unit is used for splicing the field information to generate a character string, obtaining the SQL resource primary KEY value through Hash calculation, taking the resource primary KEY value as the resource access amount of the KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
Compared with the prior art, the beneficial effect of the flow control device for the database resources provided by the embodiment of the present invention is the same as that of the flow control method for the database resources provided by the first embodiment, and details are not repeated herein.
EXAMPLE III
The present embodiment provides a computer-readable storage medium, on which a computer program is stored, and the computer program is executed by a processor to perform the steps of the flow control method for database resources.
Compared with the prior art, the beneficial effects of the computer-readable storage medium provided by the embodiment are the same as those of the flow control method for database resources provided by the above technical scheme, and are not described herein again.
It will be understood by those skilled in the art that all or part of the steps in the method for implementing the invention may be implemented by hardware instructions related to a program, the program may be stored in a computer-readable storage medium, and when executed, the program includes the steps of the method of the embodiment, and the storage medium may be: ROM/RAM, magnetic disks, optical disks, memory cards, and the like.
The above description is only for the specific embodiments of the present invention, but the scope of the present invention is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present invention, and the changes or substitutions should be covered within the scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the appended claims.
Claims (10)
1. A flow control method for database resources, comprising:
intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache;
the method comprises the steps that table definition information including a table name, a field list, a main key and an index field is obtained from a database at regular time and stored in a third-party cache, wherein the field list comprises a field name list, a field type list and a field length list;
finding out the abstract syntax tree from a third-party cache based on SQL primary key values of the SQL and extracting field information in the abstract syntax tree, wherein the field information comprises any one or more of table names, operation types and field key value pairs;
preparing to implement one or more levels of flow control according to a configured flow control strategy by combining the field information or simultaneously combining a sub-table number or a database server number corresponding to the abstract syntax tree, wherein the levels comprise a record level, a sub-table level, a table level and a library level;
and splicing the field information to generate a character string, obtaining a primary resource KEY value of the SQL after Hash calculation, taking the primary resource KEY value as a resource access amount of a KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
2. The method of claim 1, wherein intercepting SQL parsing and translation of an application server submitted to a database into an abstract syntax tree comprises:
intercepting SQL submitted to a database by an application server through a proxy JDBC connector, and using an SQL parsing function in a dry connection pool management open source component to parse and convert the SQL into an abstract syntax tree.
3. The method according to claim 1 or 2, wherein the caching the SQL primary key value obtained by SQL hashing and the abstract syntax tree in a third-party cache comprises:
and taking the hash result of the SQL calculated by the MD5 as an SQL main key value, and caching the SQL main key value and the abstract syntax tree in a third-party cache in a key value pair mode.
4. The method of claim 1, wherein the step of periodically retrieving and storing table definition information including table names, field lists, primary keys and index fields from the database into a third-party cache comprises:
obtaining table definition information including a table name, a field list, a primary key and an index field corresponding to each table in a database through a proxy JDBC connector;
and caching the table definition information of each table in the database into a third-party cache, and updating the table definition information in the third-party cache at regular time.
5. The method of claim 1, wherein preparing to implement one or more levels of flow control in conjunction with the field information or in conjunction with a table number or database server number corresponding to the abstract syntax tree according to a configured flow control policy comprises:
the configured flow control strategy comprises one or more of record level flow control, table level flow control and library level flow control;
when the configured flow control strategy is a record level flow control strategy, extracting a table name and an operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, simultaneously acquiring an index field from corresponding table definition information based on the table name, acquiring a corresponding index field value from a field key value pair list of WHERE conditions in the SQL according to the index field, and preparing to implement record level flow control;
when the configured flow control strategy is a table-level flow control strategy, extracting the table name and the operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, and simultaneously acquiring the sub-table number corresponding to the abstract syntax tree to prepare for implementing table-level flow control;
when the configured flow control strategy is a table level flow control strategy, extracting a table name and an operation type corresponding to the SQL from the abstract syntax tree in a third-party cache, and preparing to implement table level flow control;
and when the configured flow control strategy is a library-level flow control strategy, extracting the table name and the operation type corresponding to the SQL from the abstract syntax tree in the third-party cache, and simultaneously acquiring the database server number corresponding to the abstract syntax tree to prepare for implementing the library-level flow control.
6. The method according to claim 5, wherein the method for generating the character string by splicing the field information and obtaining the SQL resource primary key value by hash calculation comprises the following steps:
splicing the table name, the operation type and the index field value to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
splicing the table name, the operation type and the sublist number to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
splicing the table name and the operation type to generate a character string, and hashing the character string through MD5 to be used as a primary resource key value of the SQL;
and splicing the table name, the operation type and the database server number to generate a character string, and hashing the character string through MD5 to be used as the SQL resource primary key value.
7. The method of claim 6, wherein the method for using the primary KEY value of the resource as the resource access amount of the KEY value statistical database in a unit time and implementing the corresponding level flow control when the resource access amount exceeds the flow control threshold value comprises:
taking the primary resource KEY VALUEs as KEY VALUEs to be counted in a third-party cache, and taking the resource access amount of the database in unit time as a VALUE VALUE to be accumulated;
and when the accumulated result exceeds the flow control threshold, correspondingly implementing one or more of the matched record-level flow control, the table-level flow control and the library-level flow control.
8. The method according to claim 7, wherein the operation types are insertion operation, deletion operation, update operation and query operation, and the flow control threshold is byte flow control threshold and time flow control threshold;
when the operation type is an insertion operation or a deletion operation, selecting a byte number flow control threshold value as the flow control threshold value;
and when the operation type is an updating operation or an inquiring operation, selecting a flow control threshold for times.
9. A flow control apparatus for database resources, comprising:
the data interception unit is used for intercepting SQL analysis submitted to a database by an application server and converting the SQL analysis into an abstract syntax tree, and correspondingly caching SQL primary key values obtained by SQL hash and the abstract syntax tree in a third-party cache;
the data acquisition unit is used for acquiring table definition information comprising a table name, a field list, a primary key and an index field from a database at regular time and storing the table definition information into a third-party cache, wherein the field list comprises a field name list, a field type list and a field length list;
a field extraction unit, configured to find the abstract syntax tree from a third-party cache based on the SQL primary key of the SQL and extract field information therein, where the field information includes any one or more of a table name, an operation type, and a pair of field keys;
the flow control item matching unit is used for combining the field information or simultaneously combining the sub-table number or the database server number corresponding to the abstract syntax tree to prepare and implement one or more levels of flow control according to the configured flow control strategy, wherein the levels comprise a record level, a table level and a library level;
and the flow control implementation unit is used for splicing the field information to generate a character string, obtaining the SQL resource primary KEY value through Hash calculation, taking the resource primary KEY value as the resource access amount of the KEY value statistical database in unit time, and implementing flow control of a corresponding level when the resource access amount exceeds a flow control threshold value.
10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method according to any one of the claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010653289.2A CN111897813B (en) | 2020-07-08 | 2020-07-08 | Flow control method and device for database resources |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010653289.2A CN111897813B (en) | 2020-07-08 | 2020-07-08 | Flow control method and device for database resources |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111897813A true CN111897813A (en) | 2020-11-06 |
| CN111897813B CN111897813B (en) | 2022-09-23 |
Family
ID=73192139
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010653289.2A Active CN111897813B (en) | 2020-07-08 | 2020-07-08 | Flow control method and device for database resources |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111897813B (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113064912A (en) * | 2021-03-24 | 2021-07-02 | 西安热工研究院有限公司 | Method for rapidly inquiring historical alarm information by DCS background |
| WO2022126984A1 (en) * | 2020-12-15 | 2022-06-23 | 平安科技（深圳）有限公司 | Cache data detection method and apparatus, computer device and storage medium |
| CN115952185A (en) * | 2023-03-10 | 2023-04-11 | 布比（北京）网络技术有限公司 | Data processing method and device, equipment and storage medium |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021875A (en) * | 2007-03-22 | 2007-08-22 | 金蝶软件（中国）有限公司 | Object-oriented data bank access method and system |
| CN104881352A (en) * | 2015-06-03 | 2015-09-02 | 上海新炬网络信息技术有限公司 | System resource monitoring device based on mobile terminal |
| CN106101006A (en) * | 2016-08-23 | 2016-11-09 | 大连网月科技股份有限公司 | A kind of application type cloud recognizer for flow-control and device |
| CN111209280A (en) * | 2019-12-31 | 2020-05-29 | 上海钧正网络科技有限公司 | Database and table dividing method and device |
| CN111221804A (en) * | 2019-12-27 | 2020-06-02 | 北京健康之家科技有限公司 | Method, device and storage medium for preventing data from being unauthorized based on abstract syntax tree |
-
2020
- 2020-07-08 CN CN202010653289.2A patent/CN111897813B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021875A (en) * | 2007-03-22 | 2007-08-22 | 金蝶软件（中国）有限公司 | Object-oriented data bank access method and system |
| CN104881352A (en) * | 2015-06-03 | 2015-09-02 | 上海新炬网络信息技术有限公司 | System resource monitoring device based on mobile terminal |
| CN106101006A (en) * | 2016-08-23 | 2016-11-09 | 大连网月科技股份有限公司 | A kind of application type cloud recognizer for flow-control and device |
| CN111221804A (en) * | 2019-12-27 | 2020-06-02 | 北京健康之家科技有限公司 | Method, device and storage medium for preventing data from being unauthorized based on abstract syntax tree |
| CN111209280A (en) * | 2019-12-31 | 2020-05-29 | 上海钧正网络科技有限公司 | Database and table dividing method and device |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2022126984A1 (en) * | 2020-12-15 | 2022-06-23 | 平安科技（深圳）有限公司 | Cache data detection method and apparatus, computer device and storage medium |
| CN113064912A (en) * | 2021-03-24 | 2021-07-02 | 西安热工研究院有限公司 | Method for rapidly inquiring historical alarm information by DCS background |
| CN115952185A (en) * | 2023-03-10 | 2023-04-11 | 布比（北京）网络技术有限公司 | Data processing method and device, equipment and storage medium |
| CN115952185B (en) * | 2023-03-10 | 2023-06-30 | 布比（北京）网络技术有限公司 | Data processing method and device, equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111897813B (en) | 2022-09-23 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111897813B (en) | Flow control method and device for database resources | |
| CN106168965B (en) | Knowledge graph construction system | |
| US9479385B1 (en) | System for organizing and fast searching of massive amounts of data | |
| US9600513B2 (en) | Database table comparison | |
| CN107038222B (en) | Database cache implementation method and system | |
| CN109800222A (en) | A kind of HBase secondary index adaptive optimization method and system | |
| US7734581B2 (en) | Vector reads for array updates | |
| US11789923B2 (en) | Compression units in an index block | |
| Tang et al. | Deferred lightweight indexing for log-structured key-value stores | |
| US7895171B2 (en) | Compressibility estimation of non-unique indexes in a database management system | |
| US20190370368A1 (en) | Data consistency verification method and system minimizing load of original database | |
| US10664459B2 (en) | Database managing method, database managing system, and database tree structure | |
| WO2015096609A1 (en) | Method and system for creating inverted index file of video resource | |
| CN104050276A (en) | Cache processing method and system of distributed database | |
| CN106815260B (en) | Index establishing method and equipment | |
| US11645179B2 (en) | Method and apparatus of monitoring interface performance of distributed application, device and storage medium | |
| CN110245134B (en) | Increment synchronization method applied to search service | |
| CN108647266A (en) | A kind of isomeric data is quickly distributed storage, exchange method | |
| CN113407600A (en) | Enhanced real-time calculation method for dynamically synchronizing multi-source large table data in real time | |
| CN109246102B (en) | System and method for supporting large-scale authentication data rapid storage and retrieval | |
| CN114398520A (en) | Data retrieval method, system, device, electronic equipment and storage medium | |
| US8200673B2 (en) | System and method for on-demand indexing | |
| Doddegowda et al. | Extraction of behavioral patterns from pre-processed web usage data for web personalization | |
| CN112835918A (en) | MySQL database increment synchronization implementation method | |
| Suganya et al. | Efficient fragmentation and allocation in distributed databases |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |