CN115269345A

CN115269345A - Database testing method, device, equipment and storage medium

Info

Publication number: CN115269345A
Application number: CN202110489117.0A
Authority: CN
Inventors: 曾文杰
Original assignee: Jingdong Technology Holding Co Ltd
Current assignee: Jingdong Technology Holding Co Ltd
Priority date: 2021-04-30
Filing date: 2021-04-30
Publication date: 2022-11-01

Abstract

The invention discloses a database testing method, which comprises the following steps: acquiring test data generation parameters and test instruction parameters corresponding to the test items; determining a target database corresponding to the test item, and generating test data in the target database according to the test data generation parameters; and testing the target database according to the test data and the test instruction parameters. Therefore, when the target database is tested, the corresponding script does not need to be generated for each test item, and the test data can be automatically generated in the corresponding target database and tested only after the test data generation parameter and the test instruction parameter corresponding to the test item are acquired, so that the time for writing the script by technicians is reduced, the workload is reduced, and the test efficiency is improved. The invention also discloses a database testing device, equipment and a storage medium, which can also realize the technical effects.

Description

Database test method, device, equipment and storage medium

Technical Field

The present invention relates to the field of database testing technologies, and in particular, to a database testing method, apparatus, device, and storage medium.

Background

The database test is an important link for optimizing the database, and the optimization effect of the database can be reflected by the test result of the database, so the database test is very important in the database optimization process. In various services of the internet, the operation of online databases may be very careful, such as: sometimes, the actual data of the online database cannot be exported for testing, and the test data is hoped to be as close to reality as possible, at the moment, a script for increasing the test data can be manually written, the script increases the data in the test database to a certain amount, the test data as much as the online database is constructed to simulate a real data set, and then the script for executing the test operation is manually written to test the database. It can be seen that, when testing the database at present, the script that increases test data and the script of carrying out test operation need the manual writing of technical staff, and when testing different projects, all need technical staff to write different scripts that increase test data and the script of carrying out test operation, also promptly: the database is tested in the mode, so that more workload needs to be paid by technicians, and the testing efficiency is lower.

Disclosure of Invention

The invention aims to provide a database test method, a database test device and a database test storage medium, so that the database test time is saved, and the test efficiency is improved.

In order to achieve the above object, the present invention provides a database testing method, which includes:

acquiring test data generation parameters and test instruction parameters corresponding to the test items;

determining a target database corresponding to the test item, and generating test data in the target database according to the test data generation parameters;

and testing the target database according to the test data and the test instruction parameters.

Determining a target database corresponding to the test item, and generating test data in the target database according to the test data generation parameters includes:

acquiring database parameters corresponding to the test items;

connecting the corresponding target database by using the database parameters;

and generating test data in a target data table corresponding to the table name parameter in the target database according to the table name parameter and the data volume parameter in the test data generation parameters.

The generating of the test data in the target data table corresponding to the table name parameter in the target database according to the table name parameter and the data volume parameter in the test data generation parameters includes:

determining a target data table of the test data to be generated from a target database by using the table name parameter;

determining the field type contained in the target data table;

and generating test data corresponding to the field type in a target data table of the target database according to the data quantity parameter.

The acquiring of the test data generation parameter and the test instruction parameter corresponding to the test item includes:

acquiring test item parameters through a first parameter input interface;

creating a corresponding test item according to the test item parameters;

and acquiring a test data generation parameter through a second parameter input interface corresponding to the test item, and acquiring a test instruction parameter through a third parameter input interface corresponding to the test item.

Wherein, the testing the target database according to the test data and the test instruction parameters comprises:

testing the target database according to the test data and the test instruction parameters to obtain a first test result;

performing optimization operation on a target data table of the target database;

continuously executing the step of testing the target database according to the test data and the test instruction parameters by using the optimized target database to obtain a second test result;

and generating an optimization effect comparison result according to the first test result and the second test result.

s11, determining a target test statement, an overtime limit parameter and expected test execution times according to the test instruction parameters;

s12, executing a loop test operation on the test data through the target test statement;

s13, detecting whether the current test duration exceeds the overtime limit parameter or not; if not, executing S14; if yes, executing S15;

s14, judging whether the current test times reach the test expected execution times or not; if not, executing S12; if yes, executing S15;

and S15, ending the cycle test operation.

Wherein, the generating an optimization effect comparison result according to the first test result and the second test result comprises:

determining an optimized time reduction ratio according to the first average consumed time length in the first test result and the second average consumed time length in the second test result;

and determining the time saving duration of database processing according to the time reduction proportion, the total execution times of the business system executing the target test statement and the actual average consumed duration of the target test statement.

To achieve the above object, the present invention further provides a database testing apparatus, comprising:

the acquisition module is used for acquiring test data generation parameters and test instruction parameters corresponding to the test items;

the target database determining module is used for determining a target database corresponding to the test item;

the test data generation module is used for generating test data in the target database according to the test data generation parameters;

and the test module is used for testing the target database according to the test data and the test instruction parameters.

To achieve the above object, the present invention further provides an electronic device comprising:

a memory for storing a computer program;

and the processor is used for realizing the steps of the database testing method when executing the computer program.

To achieve the above object, the present invention further provides a computer-readable storage medium having a computer program stored thereon, where the computer program is executed by a processor to implement the steps of the above database testing method.

According to the scheme, the embodiment of the invention provides a database testing method, which comprises the following steps: acquiring test data generation parameters and test instruction parameters corresponding to test items; determining a target database corresponding to the test item, and generating test data in the target database according to the test data generation parameters; and testing the target database according to the test data and the test instruction parameters. Therefore, when the target database is tested, the corresponding script does not need to be generated for each test item, and the test data can be automatically generated in the corresponding target database and tested only after the test data generation parameter and the test instruction parameter corresponding to the test item are acquired, so that the time for writing the script by technicians is reduced, the workload is reduced, and the test efficiency is improved. The invention also discloses a database testing device, equipment and a storage medium, which can also realize the technical effects.

Drawings

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

FIG. 1 is a schematic flow chart of a database testing method according to an embodiment of the present invention;

FIG. 2 is a schematic flow chart of another database testing method disclosed in the embodiments of the present invention;

FIG. 3 is a schematic view of a first parameter input interface according to an embodiment of the disclosure;

FIG. 4 is a schematic diagram of an interface display after a new test item is added according to an embodiment of the present disclosure;

FIG. 5 is a schematic diagram of a second parameter input interface according to an embodiment of the disclosure;

FIG. 6 is a schematic diagram of a test data generation process according to an embodiment of the present invention;

FIG. 7 is a schematic diagram of a test record list disclosed in an embodiment of the present invention;

FIG. 8 is a schematic diagram of a third input interface according to the disclosure;

FIG. 9 is a schematic flow chart illustrating another database testing method according to an embodiment of the present invention;

FIG. 10 is a flowchart illustrating a database testing process according to an embodiment of the present invention;

FIG. 11 is a schematic diagram of an overall testing process disclosed in the embodiments of the present invention;

FIG. 12 is a schematic diagram of an optimized listing of a database according to an embodiment of the present invention;

FIG. 13 is a schematic diagram of a database optimization list disclosed in an embodiment of the present invention;

FIG. 14 is a schematic diagram of a double test record disclosed in an embodiment of the present invention;

FIG. 15 is a graph showing the results of two tests before and after comparative optimization as disclosed in the examples of the present invention;

FIG. 16 is a schematic structural diagram of a database testing apparatus according to an embodiment of the present invention;

fig. 17 is a schematic structural diagram of an electronic device according to an embodiment of the disclosure.

Detailed Description

At present, when a technician tests a database each time, the technician needs to manually write a script for adding test data, and then manually write a script for executing a test operation, for example, when an inquiry statement is tested, the technician needs to manually write a first script first, the first script can add test data to the test database after being executed, and then manually write a second script, the second script includes the inquiry statement, the second script can test the inquiry statement based on the test data after being executed, so as to obtain a test result, and the test result is an average execution time of the inquiry statement. Therefore, when the database is tested in the mode, technicians need to compile a large number of scripts, and the testing efficiency is low.

Therefore, in order to solve the above technical problems, the present application provides a database testing method, apparatus, device and storage medium, so as to save the database testing time and improve the testing efficiency.

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. 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.

Referring to fig. 1, a schematic flow chart of a database testing method according to an embodiment of the present invention is shown; in this embodiment, the database testing method specifically includes the following steps:

s101, acquiring test data generation parameters and test instruction parameters corresponding to test items;

in this embodiment, the test item is a test on a certain function of the database, such as: items for testing the query function of the database, items for testing the data management function of the database, and the like, are not particularly limited herein. According to the scheme, when the test data in the database is increased and the database is tested, corresponding scripts are not required to be written, and corresponding operations can be executed only by acquiring corresponding parameters. When a user tests the database, the system can be filled with corresponding test data generation parameters and test instruction parameters based on different directions of test items, so that the system can automatically execute test operation on the target database through the test data generation parameters and the test instruction parameters.

Specifically, the test data generation parameter is used to specify how many test data the test item needs to generate for which table in the database, and therefore, the test data generation parameter may include: table name parameters and data volume parameters, and the like, where the table name parameters are names of data tables of test data to be generated, and the data volume parameters are generation numbers of the test data, such as: to generate 20000 pieces of test data for the table "ffp _ employee _ security _ batch", the data amount parameter is "ffp _ employee _ security _ batch", and the data amount parameter is 20000. The test instruction parameters are used to specify the test operation performed by the test item on the database, such as: the test instruction parameters can be modified according to different test items, so that when different items are tested, the different items can be tested only by setting different parameters according to different items, different scripts do not need to be written according to different test items, and the test efficiency is improved.

S102, determining a target database corresponding to the test item, and generating test data in the target database according to the test data generation parameters;

it should be noted that, databases corresponding to different test items may be different, so in this embodiment, before generating test data, the database corresponding to the test item is first defined. When the target database is determined, the target database corresponding to the test project can be determined through information such as a database server, a database port, a database account, a database password and the like, connection is performed, and after the connection is successful, the system can generate corresponding test data in the target database according to the acquired test data generation parameters. The generation manner of the test data is not specifically limited in this embodiment, and may be generated in various manners, such as: the test data may be generated by writing a statement, or by a tool, etc.

And S103, testing the target database according to the test data and the test instruction parameters.

In this embodiment, after the test data is generated, the test data may be subjected to a test operation according to the test instruction parameters input by the user, for example: the test instruction parameters are set with test statements and test times for executing test operations, so that when the target database is tested, the test operations are executed on the test data through the test statements, and if the test times are 20000, the test operations are executed on the test data through the test statements for 20000 times, and corresponding test results are generated.

It should be noted that, in this embodiment, in step S101, when the test data generation parameter and the test instruction parameter are obtained, the test data generation parameter and the test instruction parameter may be obtained together, or only the test data generation parameter may be obtained, and after the test data is generated, the test instruction parameter is obtained to perform a test, and both obtaining manners may be used, which is not limited specifically herein.

In summary, when the target database is tested, the corresponding script does not need to be generated for each test item, and the test data can be automatically generated in the corresponding target database and tested only after the test data generation parameter and the test instruction parameter corresponding to the test item are acquired, so that the time for writing the script by technicians is reduced, the workload is reduced, and the test efficiency is improved.

Referring to fig. 2, a schematic flow chart of another database testing method according to an embodiment of the present invention is shown; it should be noted that, the same points of the database testing method described in this embodiment and the database testing method described in the foregoing embodiment may be referred to each other, and are not described herein again.

In this embodiment, the database testing method specifically includes the following steps:

s201, acquiring test project parameters through a first parameter input interface;

s202, creating a corresponding test item according to the test item parameters;

in this embodiment, when a new test item is added, a test item parameter input by a user may be specifically obtained through a first parameter input interface, where the test item parameter records item-related information and a database parameter of the test, and referring to fig. 3, it can be seen that, in the test item parameter input by the user through the first parameter input interface, the item-related information includes: the project name, project English mark, database parameter include: after a user inputs test item data through the first parameter input interface, if a submit button in the first parameter input interface is clicked, an item is created according to the test item parameters input by the user, and if a cancel button in the first parameter input interface is clicked, the created item is cancelled. Moreover, after the user clicks a submit button in the first parameter input interface, a test item table can be generated locally, the test item table can show that a new test item is created, and the test item parameters can be recorded. Referring to table 1, a test item table is provided according to an embodiment of the present invention.

TABLE 1

And after the user inputs the test item data through the first parameter input interface, a new test item can be created in the system according to the test item parameters. Referring to fig. 4, which is a schematic view of an interface display after a test item is newly added according to an embodiment of the present invention, in fig. 4, contents corresponding to an item name, an item english flag, a database server, a database port, a database account, a database password, and a database name are all contents input by a user, and an ID (Identity document) in fig. 4 is automatically generated after a test item table is created, is the same as an ID in a first row in table 1, and is used to distinguish different test items, for example: when a first test item is created, the ID of the test item is set to be 1 and recorded in a test item table corresponding to the first test item, when a second test item is created, the ID of the test item is set to be 2 and recorded in a test item table corresponding to the second test item, and because the IDs of different test items are different, even if the item names and English item marks of the test items are the same, the test items can be distinguished by the IDs of the test items.

S203, acquiring a test data generation parameter through a second parameter input interface corresponding to the test item;

in this embodiment, a user may input a test data generation parameter through a parameter input interface corresponding to a test item, see fig. 4, after creating the test item, three operations of [ generating test data ], [ performing actual SQL execution test ], and [ performing database optimization ] are performed in one column of operation, and after clicking [ generating test data ], a second parameter input interface is displayed, see fig. 5, which is a schematic diagram of the second parameter input interface provided in the embodiment of the present invention, and a user may input a test data generation parameter based on the second parameter input interface, where a first item in fig. 5 is a table name parameter for a table, and a second item in fig. 5 is a data volume parameter for how many pieces of test data are generated for the table. After clicking a submit button in the second parameter input interface, the user can create corresponding test data based on the test data generation parameters input by the user, and if clicking a cancel button in the second parameter input interface, the generation of the test data is cancelled. And after the user clicks a submit button in the second parameter input interface, a test data table can be locally generated, the test data table can represent that test data are created in the data table of the test item, and the generation process of the test data is recorded. Referring to table 2, a test data table provided for the embodiment of the present invention:

TABLE 2

Name of field	Type of field	Description of the invention
			id	int(11)	unsigned
project_id	int(11)	Affiliate test item
			table_name	varchar(255)	Aiming at the watch
gen_number	int(11)	How many test data to generate
			gen_flag	int(11)	Whether a task is completed 0 not completed 1 completed
start_number	int(11)	The data size of the data table before the task starts
			end_number	int(11)	The data amount of the data table at the end of the task
creator	varchar(255)	Creator
			created_time	datetime	Creation time
modified_time	timestamp	Modifying time

The ID in table 2 is used to indicate the ID of the test data table generated this time, the project _ ID in table 2 is used to identify which test item the test data table generates test data for, and the project _ ID may be the ID of the first item in the test item parameters in table 1. Table name parameter table _ name and data amount parameter gen _ number in table 2 are all input by the user through the second parameter input interface, and gen _ flag, start _ number and end _ number are all used for recording the data amount of the data table before and after test data generation and the test data generation result in the test data generation process.

S204, obtaining database parameters corresponding to the test items;

s205, connecting corresponding target databases by using database parameters;

in this embodiment, before generating the test data, first, a database parameter corresponding to the test item needs to be obtained, where the database parameter may be specifically obtained from a test item parameter input by a user when creating the test item, for example: the database parameters obtained from the test item parameters described in table 1 may specifically be: the system comprises a database server, a database port, a database account and a database password. After the database parameters are acquired, the remote target database can be connected through the database parameters so as to generate corresponding test data in the target database.

S206, generating test data in a target data table corresponding to the table name parameter in the target database according to the table name parameter and the data volume parameter in the test data generation parameters;

specifically, when test data are generated in a target data table according to a table name parameter and a data volume parameter, firstly, the table name parameter is used for determining the target data table of the test data to be generated from a target database, the field type included in the target data table is determined, and then the test data corresponding to the field type are generated in the target data table of the target database according to the data volume parameter.

Referring to fig. 6, a schematic diagram of a test data generation flow provided in the embodiment of the present invention is shown in fig. 6, where the test data generation flow specifically includes: receiving $ table _ name and $ gen _ number, adding a record to a test data table, wherein gen _ flag =0, start _number =0 and end _number =0, generating a self-increasing test data table ID after inserting the record, and recording the ID to a variable $ table Id of the test data table; acquiring relevant database parameters corresponding to the test items, and connecting the relevant database parameters with a remote database; obtaining a target data table for making simulation data and the data volume before the start of a task in a remote target database through a select count from $ table _ name, and updating the data volume before the start of the task to a start _ number field corresponding to $ tableId in a local database; through desc $ table _ name command, the table structure of the target data table is obtained, and the table structure can embody the structure information of the target data table, such as: the type of field contained, and so on.

When generating test data, cycling from 1 to $ gen _ number, the cycling variable is $ i, after each piece of parameter data is generated, the cycling variable $ i is automatically added with 1 until the cycle is ended when the cycling variable $ i is $ gen _ number, and the generation of the test data is stopped. When circulating each time, traversing fields of the target data table so as to construct a simulation insertion statement; the simulation insert statement constructed in this embodiment can generate test data according to the loop variables and the field types, for example: if the traversed field type is a character string field, the constructed simulation insertion statement can convert the current cycle variable $ i into a value after the character string as test data; if the traversed field type is a digital integer field, the constructed simulation insert statement can take the current loop variable $ i as test data; if the traversed field type is a floating-point field, the constructed simulation insert statement may force the current loop variable $ i to translate the generated floating-point as test data. If the traversed field type is a time field, the constructed simulation insertion statement can take the current time as test data; it should be noted that, the specific generation manner of the test data may be adjusted according to actual requirements, and the present solution is not limited in particular, and is only described by way of example in the foregoing manner.

After the simulation insert statement is constructed in the above way, the simulation insert statement is executed in the remote target database, so that the test data is generated in the target data table of the target database. After the loop variable $ i reaches $ gen _ number, the task is complete. Further, the data volume of the target data table in the remote target database when the task is completed can be obtained through a select count from $ table _ name, the data volume when the task is completed is updated to the end _ number field corresponding to $ table id in the local database, gen _ flag =1 is set to indicate that the task is completed, and the remote database is closed.

It should be noted that, if the difference between the values of the end _ number field and the start _ number field is smaller than gen _ number, it indicates that in the process of generating test data, test data is not generated in each cycle, that is: the number of pieces of data actually generated is smaller than the number of pieces of data planned to be generated. Under normal conditions, the data quantity parameter preset in the present scheme may generate data table data similar to or identical to the number of online items in the target data table, and if the generated test data is too small, the test effect may be affected, so in this embodiment, a user may preset whether to supplement the test data again, for example: non-supplementary test data can be set, and a threshold value can be set, if the number of actually generated pieces/the number of planned generated pieces > the threshold value, the supplementary test data is not needed, otherwise, the supplementary test data is needed, such as: the threshold value is set to be 70%, and the actual generation number/the planned generation number =90%, so that test data does not need to be supplemented.

S207, acquiring a test instruction parameter through a third parameter input interface corresponding to the test item;

and S208, testing the target database according to the test data and the test instruction parameters.

In this embodiment, when the user inputs the test instruction data, the button [ perform actual SQL execution test ] in the test item interface in fig. 4 may be clicked, see fig. 7, which is a schematic diagram of a test record list provided in the embodiment of the present invention, that is: after each test item is tested, the test records corresponding to the test items are displayed in a list form, and the test records can be conveniently inquired in the future. A third parameter input interface can be generated by clicking the new data adding button in fig. 7, see fig. 8, which is a schematic diagram of the third input interface provided in the embodiment of the present invention, and a user can input a test instruction parameter based on the third parameter input interface, where "the test description of this time" in fig. 8 can fill in relevant information of this time of the test, such as: optimizing information and the like of the test relative to the last test; the "execute SQL" in fig. 8 is a target test statement in the test instruction parameters, the "definition of random variable in SQL" in fig. 8 is used to assign a value to the random variable in the target test statement, the "test expected execution times" in fig. 8 is used to determine the test times of the planned execution, and the "timeout limit" in fig. 8 is used to limit the test time to avoid the execution time being too long. After the test instruction parameters are obtained through the third parameter input interface, the test data can be tested by using the test instruction parameters.

In conclusion, according to the scheme, when the test item parameter is acquired, the test item is created, the test data is acquired, the test data is generated according to the test data, and the test instruction parameter is acquired to execute the test operation, the related parameters can be input through the visual parameter input interface, compared with a mode of executing the related operation by writing a script, the method is simpler and more convenient in process, and the script coding time is reduced; in addition, after the data base parameters are connected with the corresponding target data base, enough test data can be generated in the target data table according to the table structure and the data quantity parameters of the target data table in the target data base, and the test requirements are met.

Referring to fig. 9, a schematic flow chart of another database testing method according to an embodiment of the present invention is shown; it should be noted that, the same points of the database testing method described in this embodiment and the database testing method described in the foregoing embodiment may be referred to each other, and are not repeated herein.

s301, acquiring test project parameters through a first parameter input interface;

s302, creating a corresponding test item according to the test item parameters;

s303, acquiring a test data generation parameter through a second parameter input interface corresponding to the test item;

s304, obtaining database parameters corresponding to the test items;

s305, connecting the corresponding target database by using the database parameters;

s306, generating test data in a target data table corresponding to the table name parameters in the target database according to the table name parameters and the data volume parameters in the test data generation parameters;

s307, acquiring a test instruction parameter through a third parameter input interface corresponding to the test item;

s308, testing the target database according to the test data and the test instruction parameters to obtain a first test result;

in this embodiment, after the target database is tested through the test data and the test instruction parameters, a first test result is generated, and in order to check the optimization effect of the database, after the optimization operation can be performed on the target database, the target database is tested through the optimized target database and the test data to obtain a second test result, and after the first test result is compared with the second test result, an optimization effect comparison result after the optimization of the database can be obtained.

It should be noted that, both the test processes are the same whether the target database is tested by the target database and the test data before the optimization or the target database is tested by the target database and the test data after the optimization. Referring to fig. 10, a database test flow chart provided in the embodiment of the present invention specifically includes the following steps:

s12, executing a circular test operation on the test data through the target test statement;

s13, detecting whether the current test duration exceeds an overtime limit parameter or not; if not, executing S14; if yes, executing S15;

and S15, ending the cycle test operation.

It can be understood that, in this embodiment, through the process of executing the loop test operation on the test data by the target test statement, a variable i may be set, the variable i is added with 1 after each loop is finished, and the maximum value of the variable i is the expected execution times of the test; when the scheme detects whether the current test duration exceeds the overtime limit parameter, the detection can be carried out in a periodic mode, such as: and executing S13 every 5S to detect whether the current test duration exceeds the timeout limiting parameter, or executing S13 after each cycle is finished, which may be implemented in any manner, and is not specifically limited herein. The current test duration in this embodiment refers to a test duration from the start of the test to the current detection time, such as: the test is started at 11 points 10 minutes and 1 second, and the current detection time is as follows: at 11 points, 10 minutes and 51 seconds, the current testing time length is 50 seconds; if yes, the loop test operation needs to be ended, if not, whether the current test frequency reaches the expected test execution frequency or not is judged, if yes, the loop test operation also needs to be ended, and if not, the loop test operation continues to be executed in S12. In addition, when the test is executed, the present solution needs to establish an SQL execution test table to record the actual process of performing the SQL execution test, refer to table 3, which is an SQL execution test table provided in the embodiment of the present invention:

TABLE 3

Referring to fig. 11, a schematic diagram of an overall test flow provided in the embodiment of the present invention is shown; here, a specific test procedure is described with reference to table 3 and fig. 11: according to the scheme, firstly, test instruction parameters submitted by a user need to be obtained and inserted into the local, an SQL execution test table shown in a table 3 is generated, and a record is newly added in the SQL execution test table, wherein the record specifically comprises the following parameters:

expected number of executions of the test: sql _ times for setting the expected number of times the test is executed;

timeout limit parameter: time _ limit, which is used for setting the overtime control seconds of the test;

target test statement: the sql _ txt is recorded with the sql to be executed;

variable configuration information: the sql _ config records the definition of the random variable in the sql to be executed and the configuration range of the random variable according to lines;

the parameters are all input by the user through the third parameter input interface. Further, in order to test the target database, related database parameters of the affiliated test items are required to be obtained, the remote target database is connected based on the database parameters, when the test is started, the task starting time bt is set to be the current time, the variable i is set to circulate from 1 to sql _ times, and when the numerical value of i plus 1 and i is increased to the sql _ times after the circulation is finished, the test is stopped. Each test requires traversing sql _ config line by line, and modifying sql _ txt according to the random variables and configuration ranges defined in sql _ config, for example: one of the behaviors of sql _ config: { virtual _ model }1 200000;

namely: since the random variable described in sql _ config is virtual _ model and the allocation range is 1 to 20000, it is necessary to set the virtual _ model as a random number and the random range is 1 to 20000, that is: tmpValue = rand (1, 200000), and then replacement is made for sql _ txt, replacing { virtual _ model } in sql _ txt with tmpValue. And the replaced sql _ txt is an executable sql statement containing the desired random variable, and the statement is executed in a remote database.

In the circulating process, whether the current testing time length exceeds time _ limit needs to be detected, so et needs to be set as the current time, the time difference dt = et-bt is calculated, and the time difference dt is the current testing time length; checking whether the time difference dt exceeds a preset time _ limit, if so, cutting off circulation, and if not, continuing circulation; the loop is ended when the number of tests reaches sql _ times or dt exceeds a preset time _ limit. After the loop is finished, a test result needs to be determined, in this embodiment, the test result is an average consumed duration, that is, an average consumed duration for executing each test, so that after the loop is finished, the end time et needs to be calculated again, and the time difference dt = et-bt is calculated, where the calculated time difference is an actual test duration; then, the actual execution times i are determined, and the average consumption duration avg _ time of the average executed sql _ txt is calculated according to the following formula: avg _ time = actual test duration dt/actual number of executions i.

For example: before testing, the set sql _ times is 10000 times, the time _ limit is 1000 seconds, and each test needs 600 seconds in a single cycle, so when the current test time is 10001 seconds, the time _ limit is already exceeded, at this time, the cycle test operation needs to be ended, and if the cycle test operation is ended and a test is being executed, the cycle needs to be ended after the test is ended; therefore, when the second test operation is completed, the calculated actual test time period is 1200 seconds, the actual number of executions is 2, and the average consumption time period is 600.

After the test result is obtained, the actual execution times i can be updated to a local record real _ time field, dt is updated to a local record dt _ time field, avg _ time is updated to an avg _ time field, and sql _ flag =1 is set to indicate that the task execution is finished; it should be noted that the time _ limit cut-off loop is set in this embodiment, in order to avoid the loop time process entering an uncontrollable stage, even though the time _ limit cut-off loop is passed, the test is still successfully performed, and therefore the sql _ flag is still 1.

S309, performing optimization operation on a target data table of the target database;

s310, testing the target database by using the optimized target database and the test data to obtain a second test result;

and S311, generating an optimization effect comparison result according to the first test result and the second test result.

It should be noted that, in this embodiment, when an optimization operation is performed on a target database, a test item [ perform database optimization ] in fig. 4 may be clicked to enter a database optimization list of the test item, see fig. 12, which is a schematic diagram of the database optimization list provided in the embodiment of the present invention, and with reference to fig. 12, it is possible to learn, with respect to an optimization operation performed before the test item, an optimization operation performed on the database before a new optimization operation is performed, a button [ add data ] in fig. 12 may be clicked and a fourth parameter input interface is popped up, see fig. 13, which is a schematic diagram of the fourth parameter input interface provided in the embodiment of the present invention, and a user may input relevant optimization parameters through the fourth parameter input interface, for example: in fig. 13, "for table" indicates the table name parameter of the optimized data table in the database, "execute SQL" is a statement for optimizing the database, "whether or not the task is completed" indicates whether or not the last test task is completed, and after the user inputs the above optimization parameter, the user needs to click the submit button, at this time, the system will obtain the optimization parameter input by the user, add a corresponding record in the optimized change record table of the database, and record the above optimization parameter to the optimized change record table of the database. Referring to table 4, a change record table is optimized for the database provided in the embodiment of the present invention. Furthermore, according to the scheme, database parameters corresponding to the test item are required to be obtained, the target database is connected, the sql _ txt content input by the user is sent to the target database to be executed, so that the target data table is optimized, and after the optimization is finished, the task completion mark is sent to the local database optimization change record table.

TABLE 4

Name of field	Type of field	Description of the preferred embodiment
			id	int(11)	unsigned
project_id	int(11)	Affiliate test item
			table_name	varchar(255)	Watch for indicating purpose
sql_txt	longtext	Performing sql
			sql_flag	int(11)	Whether the task is completed
creator	varchar(255)	Creator
			created_time	datetime	Creation time
modified_time	timestamp	Modifying time

Specifically, the content input by the user in the "execute SQL" in fig. 13 is SQL _ txt in table 4, where the statement for optimizing the data table is recorded, for example: and optimizing manners such as modifying field attributes of the data table, establishing indexes of the data fields, establishing joint indexes of the data fields, and the like, which are not specifically limited herein. After the optimization of the target data table is finished, in order to detect the optimization effect, the [ actually perform sql execution test ] in fig. 4 needs to be clicked again to perform a new test, so as to obtain a test result of the new test, see fig. 14, which is a schematic diagram of two test records provided by the embodiment of the present invention; it can be seen that, according to the scheme, the test item for testing SQL performance optimization is tested twice, the IDs of the two tests are respectively 2 and 3, in order to obtain the comparison result of the optimization effect, the two test results with the IDs of 2 and 3 can be selected, and then the comparison result of the optimization effect can be obtained by clicking (comparing the two tests).

During comparison, firstly, the IDs of the test records on the two sides need to be obtained, the ID with the smaller ID is the record before optimization, and the ID with the larger ID is the record after optimization, as shown in fig. 14, the IDs of the two tests are respectively 2 and 3, so that the test record with the ID of 2 is the record before optimization, and the test record with the ID of 3 is the record after optimization, therefore, when comparing the test results of the two tests, the test result with the ID of 3 is compared with the test result with the ID of 2, thereby calculating how much performance is improved. It should be noted that, before comparing the test results, the present solution first needs to determine whether the target test statements (SQL statements) of the two tests are the same, if so, the comparison continues, and if not, it cannot be determined whether the optimization effect is caused by optimizing the data table or caused by sending changes of the test statements, so that when the SQL statements of the two tests are different, the comparison is not performed, and an error message is prompted.

As can be seen from the above, the test result obtained by the present scheme is the average consumed time, so that the first test result before optimization is specifically the first average consumed time, and the second test result after optimization is specifically the second average consumed time, and when the optimization effect comparison result is generated, the optimized time reduction proportion is determined according to the first average consumed time in the first test result and the second average consumed time in the second test result; and determining the time saving period of the database processing according to the time reduction proportion, the total execution times of the business system executing the target test statement and the actual average consumed time period of the execution of the target test statement.

For example: the first average elapsed time length is 0.8070, the second average elapsed time length is 0.0012, and the calculated time reduction ratio is: (first average elapsed time-second average elapsed time)/first average elapsed time =99.85%. In this embodiment, the total execution times of the business system executing the target test statement is set as the execution times in one day, and the database processing time saving length generated by the present solution is the expected database processing time saving length in one day. If the total execution times of the business system executing the target test statement is 38400, and the actual average consumed time for executing the target test statement is 0.4, in this embodiment, the expected one-day saving database processing saving time = the total execution times of the business system executing the target test statement per day ×/the actual average consumed time for the business system executing the target test statement per day = the time reduction ratio =38400 × 0.4 × 99.85% =15336.56. After the optimization effect comparison result is generated, a table can be drawn according to the optimization effect comparison result, see fig. 15, which is a result schematic diagram of two tests before and after the comparison optimization provided by the embodiment of the present invention, and the effect of the optimization can be clearly and clearly understood through the diagram.

It should be noted that, the whole process of testing, optimizing and comparing the data table at present is as follows: firstly, adding test data to a certain amount in a test database by writing a script, writing and executing the script with the SQL statistical effect, counting the SQL execution time through the script, and calculating the average execution time; and after the data table is optimized, a script for executing the statistical SQL effect needs to be written again, the SQL execution time after optimization is counted through the script, and the average execution time is calculated. Furthermore, the effects before and after optimization need to be compared manually, and thus, in the whole process, technicians need to continuously write scripts to control the whole process at present, and no overtime control is performed during the test of the database at present, which easily causes the situation that the scripts for executing the sql statistics effect are uncontrollable. Therefore, in this solution, in order to solve the above technical problem, each operation before and after database optimization is systematically integrated, and it is not necessary for a technician to continuously write a script in each operation process, and it is only necessary to input corresponding parameters at different stages to perform related operations, for example: after the user inputs the test data generation parameters, enough test data can be quickly generated in a target data table of the target database; in addition, in the test process, the execution results can be automatically counted, overlong test time can be avoided by setting an overtime limiting parameter, automatic cutting-off of the cycle test is realized, and test results before and after optimization can be selected for comparison and display.

In the following, the test apparatus, the device and the storage medium provided by the embodiments of the present invention are introduced, and the test apparatus, the device and the storage medium described below and the test method described above may be referred to each other.

Referring to fig. 16, a schematic structural diagram of a database testing apparatus according to an embodiment of the present invention is shown in fig. 16, and as can be seen from fig. 16, the database testing apparatus includes:

the acquisition module 11 is configured to acquire a test data generation parameter and a test instruction parameter corresponding to a test item;

a target database determining module 12, configured to determine a target database corresponding to the test item;

a test data generation module 13, configured to generate test data in the target database according to the test data generation parameter;

and the test module 14 is configured to test the target database according to the test data and the test instruction parameters.

Wherein the target database determination module is specifically configured to: acquiring database parameters corresponding to the test items, and connecting a corresponding target database by using the database parameters;

the test data generation module is specifically configured to: and generating test data in a target data table corresponding to the table name parameter in the target database according to the table name parameter and the data volume parameter in the test data generation parameters.

Wherein the test data generation module comprises:

the target data table determining unit is used for determining a target data table of the test data to be generated from a target database by using the table name parameter;

a field type determining unit, configured to determine a field type included in the target data table;

and the data generation unit is used for generating test data corresponding to the field type in a target data table of the target database according to the data quantity parameter.

Wherein the acquisition module comprises:

the first parameter acquisition unit is used for acquiring test item parameters through a first parameter input interface;

the project creating unit is used for creating a corresponding test project according to the test project parameters;

the second parameter acquisition unit is used for acquiring test data generation parameters through a second parameter input interface corresponding to the test items;

and the third parameter acquisition unit is used for acquiring the test instruction parameters through a third parameter input interface corresponding to the test items.

Wherein the test module comprises:

the test unit is used for testing the target database according to the test data and the test instruction parameters to obtain a first test result;

the optimization unit is used for executing optimization operation on a target data table of the target database;

the test unit is further configured to: testing the target database by using the optimized target database and the test instruction parameters to obtain a second test result;

and the comparison result generation unit is used for generating an optimization effect comparison result according to the first test result and the second test result.

Wherein the test unit includes:

the determining unit is used for determining a target test statement, an overtime limit parameter and expected test execution times according to the test instruction parameters;

a loop operation executing unit, configured to execute a loop test operation on the test data through the target test statement when the current test duration does not exceed the timeout limiting parameter, or when the current test frequency does not reach the expected test execution frequency;

the detection unit is used for detecting whether the current test duration exceeds the overtime limit parameter or not;

the judging unit is used for judging whether the current testing times reach the expected testing execution times or not when the current testing time does not exceed the overtime limit parameter;

and the ending unit is used for ending the cycle test operation when the current test duration exceeds the overtime limit parameter or the current test frequency reaches the expected test execution frequency.

Wherein the comparison result generating unit includes:

a first determining subunit, configured to determine an optimized time reduction ratio according to a first average consumed time length in the first test result and a second average consumed time length in the second test result;

and the second determining subunit is used for determining the time saving period of database processing according to the time reduction proportion, the total execution times of the business system executing the target test statement and the actual average consumed time for executing the target test statement.

Fig. 17 is a schematic structural diagram of an electronic device disclosed in the embodiment of the present invention; referring to fig. 17, the apparatus includes:

a memory 21 for storing a computer program;

a processor 22 for implementing the steps of the database testing method according to any of the above method embodiments when executing the computer program.

In this embodiment, the device may be a PC (Personal Computer), or may be a terminal device such as a smart phone, a tablet Computer, a palmtop Computer, or a portable Computer.

The device may include a memory 21, a processor 22, and a bus 23.

The memory 21 includes a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program, and the memory provides an environment for the operating system and the execution of computer-readable instructions in the non-volatile storage medium. The processor 22 may be a Central Processing Unit (CPU), a controller, a microcontroller, a microprocessor or other data Processing chip in some embodiments, and provides computing and controlling capability for the gateway device, and when executing the computer program stored in the memory 21, the steps of executing the database test method disclosed in any of the foregoing embodiments may be implemented.

The bus 23 may be a Peripheral Component Interconnect (PCI) bus, an Extended Industry Standard Architecture (EISA) bus, or the like. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one thick line is shown in FIG. 7, but this is not intended to represent only one bus or type of bus.

Further, the device may further include a network interface 24, and the network interface 24 may optionally include a wired interface and/or a wireless interface (e.g., WI-FI interface, bluetooth interface, etc.), which are generally used to establish a communication connection between the device and other electronic devices.

Fig. 17 shows only a device with components 21-24, and it will be understood by those skilled in the art that the structure shown in fig. 17 does not constitute a limitation of the device, and may comprise fewer or more components than those shown, or some components may be combined, or a different arrangement of components.

The embodiment of the invention also discloses a computer readable storage medium, wherein a computer program is stored on the computer readable storage medium, and when the computer program is executed by a processor, the steps of the database testing method in any method embodiment are realized.

Wherein the storage medium may include: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk, an optical disk, or other various media capable of storing program codes.

The embodiments in the present description are described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments are referred to each other.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the invention. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for testing a database, comprising:

2. The method for testing the database according to claim 1, wherein the determining a target database corresponding to the test item and generating test data in the target database according to the test data generation parameters comprises:

acquiring database parameters corresponding to the test items;

connecting the corresponding target database by using the database parameters;

3. The database test method according to claim 2, wherein the generating test data in the target database in the target data table corresponding to the table name parameter according to the table name parameter and the data amount parameter in the test data generation parameter includes:

determining the field type contained in the target data table;

4. The database testing method according to claim 1, wherein the obtaining of the test data generation parameter and the test instruction parameter corresponding to the test item comprises:

acquiring test item parameters through a first parameter input interface;

creating a corresponding test item according to the test item parameters;

5. The method for testing the database according to any one of claims 1 to 4, wherein the step of testing the target database according to the test data and the test instruction parameters comprises:

performing an optimization operation on a target data table of the target database;

6. The database testing method of claim 5, wherein the testing the target database according to the test data and the test instruction parameters comprises:

and S15, ending the cycle test operation.

7. The database testing method of claim 6, wherein the generating an optimization effect comparison result according to the first test result and the second test result comprises:

8. A database testing apparatus, comprising:

9. An electronic device, comprising:

a memory for storing a computer program;

a processor for implementing the steps of the database testing method according to any one of claims 1 to 7 when executing the computer program.

10. A computer-readable storage medium, characterized in that a computer program is stored on the computer-readable storage medium, which computer program, when being executed by a processor, carries out the steps of the database testing method according to any one of claims 1 to 7.