CN109933601A - Data base management method, system, computer installation and readable storage medium storing program for executing - Google Patents
Data base management method, system, computer installation and readable storage medium storing program for executing Download PDFInfo
- Publication number
- CN109933601A CN109933601A CN201910070262.8A CN201910070262A CN109933601A CN 109933601 A CN109933601 A CN 109933601A CN 201910070262 A CN201910070262 A CN 201910070262A CN 109933601 A CN109933601 A CN 109933601A
- Authority
- CN
- China
- Prior art keywords
- time
- monitor task
- query statement
- response time
- sql query
- 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
- 238000007726 management method Methods 0.000 title claims abstract description 33
- 238000009434 installation Methods 0.000 title claims abstract description 28
- 230000004044 response Effects 0.000 claims abstract description 138
- 238000012549 training Methods 0.000 claims description 46
- 238000003062 neural network model Methods 0.000 claims description 41
- 238000004590 computer program Methods 0.000 claims description 22
- 238000013515 script Methods 0.000 claims description 10
- 238000000034 method Methods 0.000 claims description 9
- 238000012544 monitoring process Methods 0.000 claims description 9
- 230000005012 migration Effects 0.000 claims description 8
- 238000013508 migration Methods 0.000 claims description 8
- 210000002569 neuron Anatomy 0.000 claims description 6
- 238000012795 verification Methods 0.000 claims description 6
- 230000006870 function Effects 0.000 description 9
- 238000010586 diagram Methods 0.000 description 4
- 230000000694 effects Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 239000011159 matrix material Substances 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 230000008901 benefit Effects 0.000 description 2
- 238000004422 calculation algorithm Methods 0.000 description 2
- 238000012423 maintenance Methods 0.000 description 2
- 238000004364 calculation method Methods 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 238000013523 data management Methods 0.000 description 1
- 235000013399 edible fruits Nutrition 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 230000002123 temporal effect Effects 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention provides a kind of data base management method, system, computer installation and computer readable storage medium.The data base management method includes: the operating parameter established monitor task for target database table, and configure the monitor task；The monitor task is run in the target database table according to the operating parameter of the monitor task；It obtains and judges that the monitor task has executed whether response time of each SQL query statement in primary overall response time and the monitor task is greater than corresponding threshold value under the target database table current data amount；The SQL query statement inventory for executing time time-out is exported according to the judging result, and is pushed and suggested with the matched control of the judging result.The present invention is monitored database system data by configuring the realization of SQL monitor task, can find the excessive problem of the data volume of database table in time, ensures that database operates normally.
Description
Technical field
The present invention relates to database field more particularly to a kind of data base management method, system, computer installation and calculating
Machine readable storage medium storing program for executing.
Background technique
Database Systems are the data management systems that upper layer application generally uses, in Database Systems shared by inquiry operation
Ratio it is maximum.Some database tables in database may largely be inserted into data (such as data acquisition tables), data daily
Since its recorded amounts is little when library table is just online, quickly, with the increase of database table data, inquiry should for query responding time
The response time of database table becomes more and more longer, when database table data amount reaches a certain amount magnitude (such as million/ten million
Grade) when, inquiring the database table can become slower and slower, affect user experience.
Summary of the invention
In view of above-mentioned, the present invention provides a kind of data base management method, system, computer installation and computer-readable storage
Medium can accurately find the excessive problem of the data volume of database table, to take corresponding management and control measures in time.
One embodiment of the application provides a kind of data base management method, which comprises
Monitor task is established for target database table, and configures the operation frequency and the starting time point of the monitor task,
Wherein the monitor task includes at least one SQL query statement；
The prison is run in the target database table according to the operation frequency of the monitor task and starting time point
Control task；
It obtains the monitor task and has executed primary overall response time under the target database table current data amount
And in the monitor task each SQL query statement response time；
Judge whether the response time of each SQL query statement is greater than the corresponding default execution time, and described in judgement
Whether the overall response time of monitor task is greater than default total execution time, wherein each SQL query statement one-to-one correspondence is set
It is equipped with the default execution time；And
The SQL query statement inventory for executing time time-out is exported according to the judging result, and is pushed and tied with the judgement
The matched control of fruit is suggested.
Preferably, the monitor task is the SQL script collected according to default query statement keyword, the SQL
Script includes at least one SQL query statement.
Preferably, the acquisition monitor task has executed primary overall response time in the target database table
And include: the step of the response time of each SQL query statement in the monitor task
The historical data amount of the target database table is obtained, and the monitor task executes under the historical data amount
The response time of complete primary overall response time and each SQL query statement；
A neural network model is established, and the monitor task under the historical data amount has been executed into primary overall response
Time and the response time of each SQL query statement are as training sample data；
The neural network model is trained using the training sample data, obtains response time prediction mould
Type；And
The current data amount of the target database table is inputted to the response time prediction model, obtains the number of targets
According to the response time of overall response time and each SQL query statement of the library table under current data amount.
Preferably, described that the neural network model is trained using the training sample data, obtain a response
The step of time prediction model includes:
The training sample data are divided into training set and verifying collection；
The neural network model is trained using the training set；
The neural network model after training is verified using verifying collection, and is counted according to each verification result
To a model prediction accuracy rate；
Judge whether the model prediction accuracy rate is less than preset threshold；And
When the model prediction accuracy rate is not less than the preset threshold, by the neural network model of training completion
As the response time prediction model.
Preferably, it is described judge the step of whether the model prediction accuracy rate is less than preset threshold after further include:
When the model prediction accuracy rate is less than the preset threshold, the parameter of the neural network model is adjusted, and
Neural network model adjusted is trained again using the training set；
The neural network model of re -training is verified using verifying collection, and again according to each verification result
Statistics obtains model prediction accuracy rate, and judges whether the model prediction accuracy rate counted again is less than preset threshold；
When the model prediction accuracy rate counted again is not less than the preset threshold, by the re -training
Obtained neural network model is as the response time prediction model；And
When the model prediction accuracy rate counted again be less than the preset threshold when, repeat the above steps until
The model prediction accuracy rate obtained by the verifying collection verifying is not less than the preset threshold；
Wherein, the parameter of the neural network model includes the neuron number of total number of plies, each layer.
Preferably, it is described according to the judging result export execute time time-out SQL query statement inventory, and push and
The step of matched control of the judging result is suggested include:
To be greater than corresponding default execution time and the monitoring times when there are the response times of at least one SQL query statement
When the overall response time of business is no more than default total execution time, output executes the SQL query statement inventory of time time-out, and
Push is the control suggestion that the target database table increases table index newly.
Preferably, it is described according to the judging result export execute time time-out SQL query statement inventory, and push and
The step of matched control of the judging result is suggested include:
To be greater than corresponding default execution time and the monitoring times when there are the response times of at least one SQL query statement
When the overall response time of business is greater than default total execution time, output executes the SQL query statement inventory of time time-out, and pushes away
Send the control suggestion for increasing table index newly for the target database table and/or carrying out Data Migration to the target database table.
One embodiment of the application provides a kind of data base management system, the system comprises:
Module is established, for establishing monitor task for target database table, and configures the operation frequency of the monitor task
And starting time point, wherein the monitor task includes at least one SQL query statement；
Module is run, for the operation frequency and starting time point according to the monitor task in the target database table
The middle operation monitor task；
Module is obtained, has been executed once under the target database table current data amount for obtaining the monitor task
Overall response time and the monitor task in each SQL query statement response time；
Judgment module, when for judging whether the response time of each SQL query statement is greater than corresponding default execution
Between, and judge whether the overall response time of the monitor task is greater than default total execution time, wherein each SQL query language
Sentence has been arranged in a one-to-one correspondence the default execution time；And
Output module for exporting the SQL query statement inventory for executing time time-out according to the judging result, and pushes
Suggest with the matched control of the judging result.
One embodiment of the application provides a kind of computer installation, and the computer installation includes processor and memory,
Several computer programs are stored on the memory, the processor is for when executing the computer program stored in memory
The step of realizing data base management method as elucidated before.
One embodiment of the application provides a kind of computer readable storage medium, is stored thereon with computer program, described
The step of data base management method as elucidated before is realized when computer program is executed by processor.
Above-mentioned data base management method, system, computer installation and computer readable storage medium, by need to be supervised
The target database table of control establishes monitor task, and the operating parameter of configuration monitoring task, is obtaining the monitor task in institute
It states and has executed each SQL in primary overall response time and the monitor task under target database table current data amount and look into
It askes the response time of sentence, and judges whether the target database table needs to be adjusted according to result is obtained, Ke Yishi
Now determine that precise positioning is which database table table, which SQL query statement when the target matrix needs to be adjusted
The problem of, and then targetedly handled, guarantee that database operates normally, promotes user experience.
Detailed description of the invention
It, below will be to required in embodiment description in order to illustrate more clearly of the technical solution of embodiment of the present invention
The attached drawing used is briefly described, it should be apparent that, the accompanying drawings in the following description is some embodiments of the present invention, for
For those of ordinary skill in the art, without creative efforts, it can also be obtained according to these attached drawings other
Attached drawing.
Fig. 1 is the step flow chart of data base management method in one embodiment of the invention.
Fig. 2 is the functional block diagram of data base management system in one embodiment of the invention.
Fig. 3 is computer schematic device in one embodiment of the invention.
Specific embodiment
To better understand the objects, features and advantages of the present invention, with reference to the accompanying drawing and specific real
Applying mode, the present invention will be described in detail.It should be noted that in the absence of conflict, presently filed embodiment and reality
The feature applied in mode can be combined with each other.
In the following description, numerous specific details are set forth in order to facilitate a full understanding of the present invention, described embodiment
Only some embodiments of the invention, rather than whole embodiments.Based on the embodiment in the present invention, this field
Those of ordinary skill's every other embodiment obtained without making creative work, belongs to guarantor of the present invention
The range of shield.
Unless otherwise defined, all technical and scientific terms used herein and belong to technical field of the invention
The normally understood meaning of technical staff is identical.Term as used herein in the specification of the present invention is intended merely to description tool
The purpose of the embodiment of body, it is not intended that in the limitation present invention.
Preferably, data base management method of the invention is applied in one or more computer installation.The calculating
Machine device is that one kind can be according to the instruction for being previously set or storing, the automatic equipment for carrying out numerical value calculating and/or information processing,
Its hardware includes but is not limited to microprocessor, specific integrated circuit (Application Specific Integrated
Circuit, ASIC), programmable gate array (Field-Programmable Gate Array, FPGA), digital processing unit
(Digital Signal Processor, DSP), embedded device etc..
The computer installation can be the calculating such as desktop PC, laptop, tablet computer, server and set
It is standby.The computer installation can carry out people by modes such as keyboard, mouse, remote controler, touch tablet or voice-operated devices with user
Machine interaction.
Embodiment one:
Fig. 1 is the step flow chart of data base management method preferred embodiment of the present invention.The stream according to different requirements,
The sequence of step can change in journey figure, and certain steps can be omitted.
As shown in fig.1, the data base management method specifically includes following steps.
Step S11, monitor task is established for target database table, and configures the operation frequency and starting of the monitor task
Time point, wherein the monitor task includes at least one SQL query statement.
In one embodiment, the monitor task is the SQL script collected according to preset rules, the SQL foot
This includes at least one SQL query statement.The preset rules can be set according to actual monitoring requirements, such as described
Preset rules can be default query statement keyword, be collected from a SQL statement library by default query statement keyword
To the SQL script.The tables of data in database that the target database table preferably needs to monitor.The database can
To include one or more database table, each database table can be with one monitor task of correspondence establishment, to carry out the response time
Monitoring.When a target database table runs the SQL query statement, may be implemented to be deposited in the target database table
The operations such as access evidence, inquiry data, more new data.
In one embodiment, the operation frequency can refer to the frequency for running the monitor task daily, such as often
Day executes 4 SQL scripts.The starting time point is for setting the timing node for executing the monitor task every time, institute
Stating starting time point is preferably provided at the period that the target database is in idle, avoids monitor database table data volume inaccurate
Really.
Step S12, it is transported in the target database table according to the operation frequency of the monitor task and starting time point
The row monitor task.
In one embodiment, it after the operation frequency and starting time point for setting the monitor task, can use
Jekins in the target database table executes the monitor task, also can use other can monitor and continues repeated works
Software tool.
Step S13 obtains the monitor task and has executed once total under the target database table current data amount
The response time of each SQL query statement in response time and the monitor task.
In one embodiment, during executing the monitor task, one SQL query statement of every execution, record should
The response time of SQL query statement, the response time can be corresponding to receiving from starting to execute the SQL query statement
The time returned the result.The overall response time of the monitor task is the response time summation of every SQL query statement.
In one embodiment, it is operating on it, is leading since database table may have other users at any time
The response time inaccuracy that causes to run the monitor task and count, can by establish a response time prediction model come
It predicts to obtain the response time according to the current data amount of database table.It establishes the response time prediction model and utilizes the model
It predicts that obtaining the response time can be accomplished by the following way: obtaining the historical data amount of the target database table, and in institute
When stating the monitor task under historical data amount and having executed the response of primary overall response time and each SQL query statement
Between；A neural network model is established, and the monitor task under the historical data amount has been executed into primary overall response time
And the response time of each SQL query statement is as training sample data；Using the training sample data to the mind
It is trained through network model, obtains a response time prediction model；And the current data amount of the input target database table
To the response time prediction model, overall response time and each institute of the target database table under current data amount are obtained
State the response time of SQL query statement.
In one embodiment, the neural network model includes input layer, multiple hidden layers and output layer.Input layer is used
In response time training sample data of the reception target database table under multiple and different data volumes, each hidden layer includes
Corresponding multiple nodes (neuron), each node in each hidden layer are configured to the adjacent lower in the model
At least one node output execution linearly or nonlinearly convert.Wherein, the input of the node of upper layer hidden layer can be based on
The output of a node or several nodes in adjacent lower.Each hidden layer has corresponding weight, and wherein the weight is base
It is obtained in training sample data.It, can be by carrying out mould using there is the learning process of supervision when being trained to model
The pre-training of type obtains the initial weight of each hidden layer.To fine-tuning for the weight of each hidden layer, can by using to
Propagate (Back propagation, BP) algorithm afterwards to carry out, output layer is for receiving the output from the last layer hidden layer
Letter.
In one embodiment, the training sample data can be divided into training set and verifying collection.Wherein, training
Collection is for being trained neural network model, and verifying collection is for verifying the neural network model after training.Specifically,
Neural network model is trained first with the training set to obtain a mid-module, the training sample that the verifying is concentrated
Data, which are input in the mid-module, carries out response time estimation verifying (if estimation response time and actual response time are poor
Value within a preset range, is verified), it can be counted to obtain a model prediction accuracy rate according to each verification result, judge institute
State whether model prediction accuracy rate is less than preset threshold.When the model prediction accuracy rate be not less than the preset threshold, show
This mid-module prediction effect is preferable, satisfies the use demand, and can predict mould for the mid-module as the response time
Type.When model prediction accuracy rate is less than the preset threshold, shows that this mid-module prediction effect is bad, changed
It is kind, the parameter of the neural network model is adjusted at this time, and using the training set again to neural network model adjusted
It is trained to obtain a new mid-module, then the mid-module retrieved is verified using verifying collection again
Obtain a new model prediction accuracy rate.The parameter of the adjustment neural network model can be adjustment neural network model
Total number of plies, adjust each layer of neuron number etc..The preset threshold can be set according to actual use demand (such as
90%) preset threshold is.
In one embodiment, if the new model prediction accuracy rate needs again still less than the preset threshold
It repeats the above steps until being not less than the preset threshold by verifying the model prediction accuracy rate that collection obtains.It is available when obtaining
After response time prediction model, the current data amount of the target database table is inputted to the response time prediction model, i.e.,
It is predictable to obtain the monitor task under current data amount and executed in primary overall response time and the monitor task every
The response time of SQL query statement.
Step S14, judge whether the response time of each SQL query statement is greater than the corresponding default execution time, and
Judge whether the overall response time of the monitor task is greater than default total execution time, wherein each SQL query statement one
One is correspondingly arranged on the default execution time.
In one embodiment, the response time of each SQL query statement is distinguished to corresponding default execution
Time is compared, and then judges whether the response time of each SQL query statement is greater than corresponding default execution to realize
Time；It summation operation is carried out to response time of each SQL query statement obtains overall response time, and will be calculated
Overall response time is compared with default total execution time, to judge it is default whether the overall response time of the monitor task is greater than
It is total to execute the time.
In one embodiment, the default execution time of each SQL query statement, it is described it is default total execute the time can
To be set according to actual needs.For example, the default execution time that the first SQL query statement in the monitor task is arranged
For 8ms, the default execution time of the second SQL query statement setting is 20ms, when the default execution of third SQL query statement setting
Between be 8ms.
Step S15, the SQL query statement inventory for executing time time-out, and push and institute are exported according to the judging result
The matched control of judging result is stated to suggest.
In one embodiment, to be greater than corresponding default execution when there are the response times of at least one SQL query statement
When the overall response time of time and the monitor task is no more than default total execution time, output executes time time-out
SQL query statement inventory, and push the control suggestion for increasing table index newly for the target database table.When there are at least one
The response time of SQL query statement is greater than described greater than the overall response time of corresponding default execution time and the monitor task
When default total execution time, output executes the SQL query statement inventory of time time-out, and pushing is that the target database table is new
Increase table index and/or carries out the control suggestion of Data Migration to the target database table.
In one embodiment, the SQL query statement inventory for executing time time-out can be sent out by way of mail
Give operation maintenance personnel.The Data Migration can refer to the Data Migration that will be specified in target database table to other history lists,
And then reduce the current data amount of the target database table.
In one embodiment, during progress table index is newly-increased, requiring to look up new data directory item should be inserted
The position entered determines the need for dividing the node further according to the memory space for the node for being inserted into new data index entry, and
In the case where needing to divide the node, the memory space for considering the father node of the node is also needed, to determine the need for dividing
The father node.So circulation is handled repeatedly, to ensure the balance indexed.
Specifically, an index entry is constructed for the data that target database table increases newly, searches the index entry and is inserted in original index
Specified node is searched in the position entered；Judge whether the memory space of the node is enough；It is empty in the storage for judging the node
Between in enough situations, the index entry newly constructed is inserted into the node；The memory space inadequate for judging the node the case where
Under, divide the node.When dividing the node, need to redistribute one section of continuous memory space, for saving and the node
The brother of node with identical father node.In addition, also needing the memory space for the father node for judging the node to be when dividing the node
It is no enough, and in the enough situations of memory space for judging the father node, it can directly modify the father node and be directed toward section
The pointer of point then needs to continue to complete index upgrade, and in the case where judging the memory space inadequate of the father node enough
Divide the father node.
Above-mentioned data base management method by establishing monitor task for the target database table that need to be monitored, and configures
The operating parameter of monitor task has executed once obtaining the monitor task under the target database table current data amount
Overall response time and the monitor task in each SQL query statement response time, and sentence according to result is obtained
Whether the target database table that breaks needs to be adjusted, and may be implemented when determining that the target matrix needs to be adjusted
The problem of which database table table precise positioning is, which SQL query statement, and then targetedly handled, guarantee number
It is operated normally according to library, promotes user experience.
Embodiment two:
Fig. 2 is the functional block diagram of data base management system preferred embodiment of the present invention.
As shown in fig.2, the data base management system 10 may include establishing module 101, operation module 102, obtaining
Module 103, judgment module 104, output module 105.
The module 101 of establishing and configures the fortune of the monitor task for establishing monitor task for target database table
Line frequency time and starting time point, wherein the monitor task includes at least one SQL query statement.
In one embodiment, the monitor task is the SQL script collected according to preset rules, the SQL foot
This includes at least one SQL query statement.The preset rules can be set according to actual monitoring requirements, such as described
Preset rules can be default query statement keyword, be collected from a SQL statement library by default query statement keyword
To the SQL script.The tables of data in database that the target database table preferably needs to monitor.The database can
To include one or more database table, each database table can be with one monitor task of correspondence establishment, to carry out the response time
Monitoring.When a target database table runs the SQL query statement, may be implemented to be deposited in the target database table
The operations such as access evidence, inquiry data, more new data.
In one embodiment, the operation frequency can refer to the frequency for running the monitor task daily, such as often
Day executes 4 SQL scripts.The starting time point is for setting the timing node for executing the monitor task every time, institute
Stating starting time point is preferably provided at the period that the target database is in idle, avoids monitor database table data volume inaccurate
Really.
The operation module 102 is used for the operation frequency and starting time point according to the monitor task in the number of targets
According to running the monitor task in the table of library.
In one embodiment, after the operation frequency and starting time point for setting the monitor task, the operation
Module 102 can use Jekins to execute the monitor task in the target database table, also can use other energy
Monitor the software tool for continuing repeated work.
The acquisition module 103 executes under the target database table current data amount for obtaining the monitor task
The response time of each SQL query statement in complete primary overall response time and the monitor task.
In one embodiment, during executing the monitor task, one SQL query statement of every execution is described to obtain
Modulus block 103 records the response time of the SQL query statement, and the response time can be from starting to execute the SQL query language
Sentence is to receiving the corresponding time returned the result.The overall response time of the monitor task is the sound of every SQL query statement
Answer temporal summation.
In one embodiment, it is operating on it, is leading since database table may have other users at any time
Cause the response time for running the monitor task and counting inaccurate, the acquisition module 103 can be by establishing a sound
Prediction model is predicted to obtain the response time according to the current data amount of database table between seasonable.The acquisition module 103 is established
The response time prediction model simultaneously obtains the response time using the model prediction and can be accomplished by the following way: described in acquisition
The historical data amount of target database table, and when the monitor task has executed primary overall response under the historical data amount
Between and each SQL query statement response time；A neural network model is established, and will be described under the historical data amount
Monitor task has executed the response time of primary overall response time and each SQL query statement as number of training
According to；The neural network model is trained using the training sample data, obtains a response time prediction model；And it is defeated
Enter the current data amount of the target database table to the response time prediction model, obtains the target database table and working as
The response time of overall response time and each SQL query statement under preceding data volume.
In one embodiment, the neural network model includes input layer, multiple hidden layers and output layer.Input layer is used
In response time training sample data of the reception target database table under multiple and different data volumes, each hidden layer includes
Corresponding multiple nodes (neuron), each node in each hidden layer are configured to the adjacent lower in the model
At least one node output execution linearly or nonlinearly convert.Wherein, the input of the node of upper layer hidden layer can be based on
The output of a node or several nodes in adjacent lower.Each hidden layer has corresponding weight, and wherein the weight is base
It is obtained in training sample data.It, can be by carrying out mould using there is the learning process of supervision when being trained to model
The pre-training of type obtains the initial weight of each hidden layer.To fine-tuning for the weight of each hidden layer, can by using to
Propagate (Back propagation, BP) algorithm afterwards to carry out, output layer is for receiving the output from the last layer hidden layer
Letter.
In one embodiment, the training sample data can be divided into training set and verifying collection.Wherein, training
Collection is for being trained neural network model, and verifying collection is for verifying the neural network model after training.Specifically,
Neural network model is trained first with the training set to obtain a mid-module, the training sample that the verifying is concentrated
Data, which are input in the mid-module, carries out response time estimation verifying (if estimation response time and actual response time are poor
Value within a preset range, is verified), it can be counted to obtain a model prediction accuracy rate according to each verification result, judge institute
State whether model prediction accuracy rate is less than preset threshold.When the model prediction accuracy rate be not less than the preset threshold, show
This mid-module prediction effect is preferable, satisfies the use demand, and can predict mould for the mid-module as the response time
Type.When model prediction accuracy rate is less than the preset threshold, shows that this mid-module prediction effect is bad, changed
It is kind, the parameter of the neural network model is adjusted at this time, and using the training set again to neural network model adjusted
It is trained to obtain a new mid-module, then the mid-module retrieved is verified using verifying collection again
Obtain a new model prediction accuracy rate.The parameter of the adjustment neural network model can be adjustment neural network model
Total number of plies, adjust each layer of neuron number etc..The preset threshold can be set according to actual use demand (such as
90%) preset threshold is.
In one embodiment, if the new model prediction accuracy rate needs again still less than the preset threshold
It repeats the above steps until being not less than the preset threshold by verifying the model prediction accuracy rate that collection obtains.It is available when obtaining
After response time prediction model, the current data amount of the target database table is inputted to the response time prediction model, i.e.,
It is predictable to obtain the monitor task under current data amount and executed in primary overall response time and the monitor task every
The response time of SQL query statement.
The judgment module 104 is for judging it is corresponding default whether the response time of each SQL query statement is greater than
The time is executed, and judges whether the overall response time of the monitor task is greater than default total execution time, wherein each SQL
Query statement has been arranged in a one-to-one correspondence the default execution time.
In one embodiment, the judgment module 104 by the response time of each SQL query statement respectively with its
The corresponding default execution time is compared, and then to realize the judging whether response time of each SQL query statement is big
In the corresponding default execution time；The judgment module 104 also carries out summation fortune to the response time of each SQL query statement
Calculation obtains overall response time, and the overall response time being calculated is compared with default total execution time, described to judge
Whether the overall response time of monitor task is greater than default total execution time.
In one embodiment, the default execution time of each SQL query statement, it is described it is default total execute the time can
To be set according to actual needs.For example, the default execution time that the first SQL query statement in the monitor task is arranged
For 8ms, the default execution time of the second SQL query statement setting is 20ms, when the default execution of third SQL query statement setting
Between be 8ms.
The output module 105 is used to export the SQL query statement inventory for executing time time-out according to the judging result,
And it pushes and suggests with the matched control of the judging result.
In one embodiment, to be greater than corresponding default execution when there are the response times of at least one SQL query statement
When the overall response time of time and the monitor task is no more than default total execution time, the output module 105 is exported
The SQL query statement inventory of time time-out is executed, and pushes the control suggestion for increasing table index newly for the target database table.When
There are the response times of at least one SQL query statement to be greater than the corresponding default overall response for executing time and the monitor task
When time is greater than default total execution time, the SQL query statement that the output of output module 105 executes time time-out is clear
It is single, and pushing is that the target database table increases table index newly and/or carries out the pipe of Data Migration to the target database table
Control is suggested.
In one embodiment, the SQL query statement inventory for executing time time-out can be sent out by way of mail
Give operation maintenance personnel.The Data Migration can refer to the Data Migration that will be specified in target database table to other history lists,
And then reduce the current data amount of the target database table.
In one embodiment, during progress table index is newly-increased, requiring to look up new data directory item should be inserted
The position entered determines the need for dividing the node further according to the memory space for the node for being inserted into new data index entry, and
In the case where needing to divide the node, the memory space for considering the father node of the node is also needed, to determine the need for dividing
The father node.So circulation is handled repeatedly, to ensure the balance indexed.
Specifically, an index entry is constructed for the data that target database table increases newly, searches the index entry and is inserted in original index
Specified node is searched in the position entered；Judge whether the memory space of the node is enough；It is empty in the storage for judging the node
Between in enough situations, the index entry newly constructed is inserted into the node；The memory space inadequate for judging the node the case where
Under, divide the node.When dividing the node, need to redistribute one section of continuous memory space, for saving and the node
The brother of node with identical father node.In addition, also needing the memory space for the father node for judging the node to be when dividing the node
It is no enough, and in the enough situations of memory space for judging the father node, it can directly modify the father node and be directed toward section
The pointer of point then needs to continue to complete index upgrade, and in the case where judging the memory space inadequate of the father node enough
Divide the father node.
Above-mentioned data base management system by establishing monitor task for the target database table that need to be monitored, and configures
The operating parameter of monitor task has executed once obtaining the monitor task under the target database table current data amount
Overall response time and the monitor task in each SQL query statement response time, and sentence according to result is obtained
Whether the target database table that breaks needs to be adjusted, and may be implemented when determining that the target matrix needs to be adjusted
The problem of which database table table precise positioning is, which SQL query statement, and then targetedly handled, guarantee number
It is operated normally according to library, promotes user experience.
Fig. 3 is the schematic diagram of computer installation preferred embodiment of the present invention.
The computer installation 1 includes memory 20, processor 30 and is stored in the memory 20 and can be in institute
State the computer program 40 run on processor 30, such as data base administrator.The processor 30 executes the computer
The step in above-mentioned data base management method embodiment, such as step S11~S15 shown in FIG. 1 are realized when program 40.Alternatively,
The processor 30 realizes the function of each module in above-mentioned data base management system embodiment when executing the computer program 40,
Such as the module 101~105 in Fig. 2.
Illustratively, the computer program 40 can be divided into one or more module/units, it is one or
Multiple module/units are stored in the memory 20, and are executed by the processor 30, to complete the present invention.Described one
A or multiple module/units can be the series of computation machine program instruction section that can complete specific function, and described instruction section is used
In implementation procedure of the description computer program 40 in the computer installation 1.For example, the computer program 40 can be with
It is divided into establishing module 101, operation module 102, obtaining module 103, judgment module 104, output module 105 in Fig. 2.Respectively
Module concrete function is referring to embodiment two.
The computer installation 1 can be the calculating such as desktop PC, notebook, palm PC and cloud server and set
It is standby.It will be understood by those skilled in the art that the schematic diagram is only the example of computer installation 1, do not constitute to computer
The restriction of device 1 may include perhaps combining certain components or different components, example than illustrating more or fewer components
Such as described computer installation 1 can also include input-output equipment, network access equipment, bus.
Alleged processor 30 can be central processing unit (Central Processing Unit, CPU), can also be
Other general processors, digital signal processor (Digital Signal Processor, DSP), specific integrated circuit
(Application Specific Integrated Circuit, ASIC), ready-made programmable gate array (Field-
Programmable Gate Array, FPGA) either other programmable logic device, discrete gate or transistor logic,
Discrete hardware components etc..General processor can be microprocessor or the processor 30 is also possible to any conventional processing
Device etc., the processor 30 are the control centres of the computer installation 1, utilize various interfaces and the entire computer of connection
The various pieces of device 1.
The memory 20 can be used for storing the computer program 40 and/or module/unit, and the processor 30 passes through
Operation executes the computer program and/or module/unit being stored in the memory 20, and calls and be stored in memory
Data in 20 realize the various functions of the computer installation 1.The memory 20 can mainly include storing program area and deposit
Store up data field, wherein storing program area can application program needed for storage program area, at least one function (for example sound is broadcast
Playing function, image player function etc.) etc.；Storage data area, which can be stored, uses created data (ratio according to computer installation 1
Such as audio data, phone directory) etc..In addition, memory 20 may include high-speed random access memory, it can also include non-easy
The property lost memory, such as hard disk, memory, plug-in type hard disk, intelligent memory card (Smart Media Card, SMC), secure digital
(Secure Digital, SD) card, flash card (Flash Card), at least one disk memory, flush memory device or other
Volatile solid-state part.
If the integrated module/unit of the computer installation 1 is realized in the form of SFU software functional unit and as independence
Product when selling or using, can store in a computer readable storage medium.Based on this understanding, of the invention
It realizes all or part of the process in above-described embodiment method, can also instruct relevant hardware come complete by computer program
At the computer program can be stored in a computer readable storage medium, and the computer program is held by processor
When row, it can be achieved that the step of above-mentioned each embodiment of the method.Wherein, the computer program includes computer program code, institute
Stating computer program code can be source code form, object identification code form, executable file or certain intermediate forms etc..It is described
Computer-readable medium may include: any entity or device, recording medium, U that can carry the computer program code
Disk, mobile hard disk, magnetic disk, CD, computer storage, read-only memory (ROM, Read-Only Memory), arbitrary access
Memory (RAM, Random Access Memory), electric carrier signal, telecommunication signal and software distribution medium etc..It needs
It is bright, the content that the computer-readable medium includes can according in jurisdiction make laws and patent practice requirement into
Row increase and decrease appropriate, such as do not include electric load according to legislation and patent practice, computer-readable medium in certain jurisdictions
Wave signal and telecommunication signal.
In several embodiments provided by the present invention, it should be understood that disclosed computer installation and method, it can be with
It realizes by another way.For example, computer installation embodiment described above is only schematical, for example, described
The division of unit, only a kind of logical function partition, there may be another division manner in actual implementation.
It, can also be in addition, each functional unit in each embodiment of the present invention can integrate in same treatment unit
It is that each unit physically exists alone, can also be integrated in same unit with two or more units.Above-mentioned integrated list
Member both can take the form of hardware realization, can also realize in the form of hardware adds software function module.
It is obvious to a person skilled in the art that invention is not limited to the details of the above exemplary embodiments, Er Qie
In the case where without departing substantially from spirit or essential attributes of the invention, the present invention can be realized in other specific forms.Therefore, no matter
From the point of view of which point, the present embodiments are to be considered as illustrative and not restrictive, and the scope of the present invention is by appended power
Benefit requires rather than above description limits, it is intended that all by what is fallen within the meaning and scope of the equivalent elements of the claims
Variation is included in the present invention.Any reference signs in the claims should not be construed as limiting the involved claims.This
Outside, it is clear that one word of " comprising " does not exclude other units or steps, and odd number is not excluded for plural number.It is stated in computer installation claim
Multiple units or computer installation can also be implemented through software or hardware by the same unit or computer installation.The
One, the second equal words are used to indicate names, and are not indicated any particular order.
Finally it should be noted that the above examples are only used to illustrate the technical scheme of the present invention and are not limiting, although reference
Preferred embodiment describes the invention in detail, those skilled in the art should understand that, it can be to of the invention
Technical solution is modified or equivalent replacement, without departing from the spirit and scope of the technical solution of the present invention.
Claims (10)
1. a kind of data base management method, which is characterized in that the described method includes:
Monitor task is established for target database table, and configures the operation frequency and the starting time point of the monitor task, wherein
The monitor task includes at least one SQL query statement；
The monitoring is run in the target database table according to the operation frequency of the monitor task and starting time point to appoint
Business；
It obtains the monitor task and has executed primary overall response time and institute under the target database table current data amount
State the response time of each SQL query statement in monitor task；
Judge whether the response time of each SQL query statement is greater than the corresponding default execution time, and judges the monitoring
Whether the overall response time of task is greater than default total execution time, wherein each SQL query statement has been arranged in a one-to-one correspondence
One default execution time；And
The SQL query statement inventory for executing time time-out is exported according to the judging result, and is pushed and the judging result
The control suggestion matched.
2. data base management method as described in claim 1, which is characterized in that the monitor task is according to default inquiry language
The SQL script that sentence keyword is collected, the SQL script include at least one SQL query statement.
3. data base management method as claimed in claim 1 or 2, which is characterized in that described to obtain the monitor task in institute
State the sound that each SQL query statement in primary overall response time and the monitor task has been executed in target database table
The step of between seasonable includes:
The historical data amount of the target database table is obtained, and the monitor task has executed one under the historical data amount
The response time of secondary overall response time and each SQL query statement；
A neural network model is established, and the monitor task under the historical data amount has been executed into primary overall response time
And the response time of each SQL query statement is as training sample data；
The neural network model is trained using the training sample data, obtains a response time prediction model；And
The current data amount of the target database table is inputted to the response time prediction model, obtains the target database
The response time of overall response time and each SQL query statement of the table under current data amount.
4. data base management method as claimed in claim 3, which is characterized in that described to utilize the training sample data to institute
The step of stating neural network model to be trained, obtaining a response time prediction model include:
The training sample data are divided into training set and verifying collection；
The neural network model is trained using the training set；
The neural network model after training is verified using verifying collection, and counts to obtain one according to each verification result
Model prediction accuracy rate；
Judge whether the model prediction accuracy rate is less than preset threshold；And
When the model prediction accuracy rate be not less than the preset threshold when, will training complete the neural network model as
The response time prediction model.
5. data base management method as claimed in claim 4, which is characterized in that described to judge that the model prediction accuracy rate is
After no the step of being less than preset threshold further include:
When the model prediction accuracy rate is less than the preset threshold, the parameter of the neural network model is adjusted, and utilize
The training set is again trained neural network model adjusted；
The neural network model of re -training is verified using verifying collection, and is counted again according to each verification result
Model prediction accuracy rate is obtained, and judges whether the model prediction accuracy rate counted again is less than preset threshold；
When the model prediction accuracy rate counted again is not less than the preset threshold, the re -training is obtained
Neural network model as the response time prediction model；And
When the model prediction accuracy rate counted again is less than the preset threshold, repeat the above steps until passing through
The model prediction accuracy rate that the verifying collection verifying obtains is not less than the preset threshold；
Wherein, the parameter of the neural network model includes the neuron number of total number of plies, each layer.
6. data base management method as claimed in claim 1 or 2, which is characterized in that described to be exported according to the judging result
The SQL query statement inventory of time time-out is executed, and pushes the step of suggesting with the matched control of the judging result and includes:
To be greater than corresponding default time and the monitor task of executing when there are the response times of at least one SQL query statement
When overall response time is no more than default total execution time, output executes the SQL query statement inventory of time time-out, and pushes
Increase the control suggestion of table index newly for the target database table.
7. data base management method as claimed in claim 1 or 2, which is characterized in that described to be exported according to the judging result
The SQL query statement inventory of time time-out is executed, and pushes the step of suggesting with the matched control of the judging result and includes:
To be greater than corresponding default time and the monitor task of executing when there are the response times of at least one SQL query statement
When overall response time is greater than default total execution time, output executes the SQL query statement inventory of time time-out, and pushes and be
The target database table increases table index newly and/or carries out the control suggestion of Data Migration to the target database table.
8. a kind of data base management system, which is characterized in that the system comprises:
Module is established, for establishing monitor task for target database table, and the operation frequency of the monitor task is configured and opens
Dynamic time point, wherein the monitor task includes at least one SQL query statement；
Module is run, for transporting in the target database table according to the operation frequency and starting time point of the monitor task
The row monitor task；
Module is obtained, has executed once total under the target database table current data amount for obtaining the monitor task
The response time of each SQL query statement in response time and the monitor task；
Judgment module executes the time for judging whether the response time of each SQL query statement is greater than corresponding preset, and
Judge whether the overall response time of the monitor task is greater than default total execution time, wherein each SQL query statement one
One is correspondingly arranged on the default execution time；And
Output module, for exporting the SQL query statement inventory for executing time time-out, and push and institute according to the judging result
The matched control of judging result is stated to suggest.
9. a kind of computer installation, the computer installation includes processor and memory, is stored on the memory several
Computer program, which is characterized in that such as right is realized when the processor is for executing the computer program stored in memory
It is required that the step of data base management method described in any one of 1-7.
10. a kind of computer readable storage medium, is stored thereon with computer program, which is characterized in that the computer program
The step of data base management method as described in any one of claim 1-7 is realized when being executed by processor.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910070262.8A CN109933601A (en) | 2019-01-24 | 2019-01-24 | Data base management method, system, computer installation and readable storage medium storing program for executing |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910070262.8A CN109933601A (en) | 2019-01-24 | 2019-01-24 | Data base management method, system, computer installation and readable storage medium storing program for executing |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN109933601A true CN109933601A (en) | 2019-06-25 |
Family
ID=66985257
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910070262.8A Pending CN109933601A (en) | 2019-01-24 | 2019-01-24 | Data base management method, system, computer installation and readable storage medium storing program for executing |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109933601A (en) |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110362611A (en) * | 2019-07-12 | 2019-10-22 | 拉卡拉支付股份有限公司 | A kind of data base query method, device, electronic equipment and storage medium |
| CN110515807A (en) * | 2019-09-02 | 2019-11-29 | 北京启迪区块链科技发展有限公司 | A kind of database table monitoring method, device, equipment and storage medium |
| CN111913937A (en) * | 2020-07-31 | 2020-11-10 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN112115158A (en) * | 2020-09-04 | 2020-12-22 | 浪潮电子信息产业股份有限公司 | SQL statement execution time recording method, device, equipment and readable storage medium |
| CN112181669A (en) * | 2019-07-04 | 2021-01-05 | 中兴通讯股份有限公司 | Deadlock detection control method and device, communication equipment and computer storage medium |
| CN112650770A (en) * | 2020-12-31 | 2021-04-13 | 东北大学 | MySQL parameter recommendation method based on query workload analysis |
| CN112764728A (en) * | 2020-12-31 | 2021-05-07 | 北京谊安医疗系统股份有限公司 | Automatic programming method and system suitable for embedded software of breathing machine |
| CN112765196A (en) * | 2019-10-21 | 2021-05-07 | 拉扎斯网络科技（上海）有限公司 | Data processing and data recognition method and device, electronic equipment and storage medium |
| CN113722347A (en) * | 2021-02-10 | 2021-11-30 | 京东数字科技控股股份有限公司 | Database management method and device, electronic equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108052394A (en) * | 2017-12-27 | 2018-05-18 | 福建星瑞格软件有限公司 | The method and computer equipment of resource allocation based on SQL statement run time |
| CN108363768A (en) * | 2018-02-07 | 2018-08-03 | 深圳壹账通智能科技有限公司 | A kind of document search method, storage medium and server based on Lucene |
| CN108628909A (en) * | 2017-03-24 | 2018-10-09 | 北京京东尚科信息技术有限公司 | Information-pushing method and device |
-
2019
- 2019-01-24 CN CN201910070262.8A patent/CN109933601A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108628909A (en) * | 2017-03-24 | 2018-10-09 | 北京京东尚科信息技术有限公司 | Information-pushing method and device |
| CN108052394A (en) * | 2017-12-27 | 2018-05-18 | 福建星瑞格软件有限公司 | The method and computer equipment of resource allocation based on SQL statement run time |
| CN108363768A (en) * | 2018-02-07 | 2018-08-03 | 深圳壹账通智能科技有限公司 | A kind of document search method, storage medium and server based on Lucene |
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112181669A (en) * | 2019-07-04 | 2021-01-05 | 中兴通讯股份有限公司 | Deadlock detection control method and device, communication equipment and computer storage medium |
| CN110362611A (en) * | 2019-07-12 | 2019-10-22 | 拉卡拉支付股份有限公司 | A kind of data base query method, device, electronic equipment and storage medium |
| CN110362611B (en) * | 2019-07-12 | 2021-07-09 | 拉卡拉支付股份有限公司 | Database query method and device, electronic equipment and storage medium |
| CN110515807A (en) * | 2019-09-02 | 2019-11-29 | 北京启迪区块链科技发展有限公司 | A kind of database table monitoring method, device, equipment and storage medium |
| CN112765196B (en) * | 2019-10-21 | 2023-01-31 | 拉扎斯网络科技（上海）有限公司 | Data processing and data recognition method and device, electronic equipment and storage medium |
| CN112765196A (en) * | 2019-10-21 | 2021-05-07 | 拉扎斯网络科技（上海）有限公司 | Data processing and data recognition method and device, electronic equipment and storage medium |
| CN111913937A (en) * | 2020-07-31 | 2020-11-10 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN111913937B (en) * | 2020-07-31 | 2024-03-08 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN112115158A (en) * | 2020-09-04 | 2020-12-22 | 浪潮电子信息产业股份有限公司 | SQL statement execution time recording method, device, equipment and readable storage medium |
| CN112764728A (en) * | 2020-12-31 | 2021-05-07 | 北京谊安医疗系统股份有限公司 | Automatic programming method and system suitable for embedded software of breathing machine |
| CN112650770B (en) * | 2020-12-31 | 2023-08-01 | 东北大学 | MySQL parameter recommendation method based on query work load analysis |
| CN112650770A (en) * | 2020-12-31 | 2021-04-13 | 东北大学 | MySQL parameter recommendation method based on query workload analysis |
| CN113722347A (en) * | 2021-02-10 | 2021-11-30 | 京东数字科技控股股份有限公司 | Database management method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109933601A (en) | Data base management method, system, computer installation and readable storage medium storing program for executing | |
| CN109634801A (en) | Data trend analysis method, system, computer installation and readable storage medium storing program for executing | |
| US9195693B2 (en) | Transaction prediction modeling method | |
| US11163760B2 (en) | Providing a data query service to a user based on natural language request data | |
| CN109634818A (en) | Log analysis method, system, terminal and computer readable storage medium | |
| CN109669837A (en) | Equipment state method for early warning, system, computer installation and readable storage medium storing program for executing | |
| CN109684162A (en) | Equipment state prediction method, system, terminal and computer readable storage medium | |
| CN109033277A (en) | Class brain system, method, equipment and storage medium based on machine learning | |
| CN110109955A (en) | Data call amount statistical method, system, computer installation and readable storage medium storing program for executing | |
| US20130339279A1 (en) | Optimal persistence of a business process | |
| CN110347706A (en) | For handling method, Database Systems and the computer readable storage medium of inquiry | |
| US20180336459A1 (en) | Unstructured key definitions for optimal performance | |
| CN109558384A (en) | Log classification method, device, electronic equipment and storage medium | |
| CN106547870A (en) | Point table method and device of data base | |
| US20200160211A1 (en) | Machine learning based database anomaly prediction | |
| CN110096258A (en) | A method of the OpenStack infrastructure architecture management based on Terraform | |
| CN112669188A (en) | Critical event early warning model construction method, critical event early warning method and electronic equipment | |
| US11887017B2 (en) | Automatically predicting transaction likelihood information and transaction-related temporal information using machine learning techniques | |
| CN111930944A (en) | File label classification method and device | |
| CN107861769B (en) | Application cleaning method and device, storage medium and electronic equipment | |
| CN114490526B (en) | Course selection index file updating method, course searching method and device | |
| CN116956994A (en) | Service platform capacity expansion prediction method and device | |
| US11782918B2 (en) | Selecting access flow path in complex queries | |
| CN109815082A (en) | KAFKA theme monitoring method, device, electronic equipment and storage medium | |
| US7577649B2 (en) | Engine for validating proposed changes to an electronic entity |
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 |