Virtual index-based database test method and device and database query method and device

Abstract

The embodiment of the invention provides a database testing method and device and a database query method and device based on a virtual index, and belongs to the technical field of computers. The method comprises the following steps: obtaining index metadata based on a pre-created virtual index, extracting a target field of a query statement test instance, storing the index metadata to a system directory of a test database according to the target field, generating a query execution plan according to the query statement test instance and the index metadata of the system directory, and the target execution cost of the query execution plan is calculated through the cost calculation model, the query execution plan is screened according to the target execution cost to obtain the target execution plan of the query statement test instance, and the query performance test is performed on the test database according to the target execution plan, so that the resource consumption of the database test can be reduced.

Description

Technical Field

[0001] The present application relates to the field of computer technology and is applied to the fields of financial technology and digital medicine, and in particular to a database testing method, database query method, and device based on virtual indexing. Background Art

[0002] Indexes help databases quickly locate and retrieve data and are a crucial tool for optimizing query performance. For example, in a fintech scenario, a banking database is a data-intensive database that stores user information, account transaction records, loan records, and more. When a banking database receives a transaction record query request, it can create an index on the account field. This index allows the database to quickly locate the target transaction record without scanning the entire transaction record table. Another example is in a digital healthcare scenario, where a medical database stores patient information and electronic medical records. When a medical database receives a medical record query request, it can create an index on the medical record number field. This index allows the database to quickly locate the target record without scanning the entire medical record table. However, as data volume and business growth increase, database query performance can degrade, necessitating regular database testing.

[0003] In related technologies, database query performance testing is performed by creating real indexes. However, database testing based on real indexes consumes a large amount of computing resources. Therefore, how to reduce the resource consumption of database testing has become a technical problem that needs to be solved urgently. Summary of the Invention

[0004] The main purpose of the embodiments of the present application is to propose a database testing method, a database query method, and a device based on virtual indexes, aiming to reduce resource consumption in database testing.

[0005] To achieve the above objectives, a first aspect of an embodiment of the present application proposes a database testing method based on a virtual index, the database testing method based on a virtual index comprising:

[0006] Get index metadata based on pre-created virtual index;

[0007] Extracting a target field of a query statement test instance, and storing the index metadata in a system directory of a test database according to the target field;

[0008] Generate a query execution plan based on the query statement test instance and the index metadata of the system catalog, and calculate a target execution cost of the query execution plan using a cost calculation model; wherein the target execution cost is used to indicate an estimated resource consumed by executing the query execution plan;

[0009] Filtering the query execution plan according to the target execution cost to obtain a target execution plan for the query statement test instance;

[0010] Perform query performance testing on the test database according to the target execution plan.

[0011] In some embodiments, generating a query execution plan based on the query statement test instance and the index metadata of the system catalog includes:

[0012] Obtaining a hidden index list; wherein the hidden index list includes a target hidden index;

[0013] comparing the target hidden index with the virtual index of the system directory;

[0014] If the target hidden index is different from the virtual index, the query execution plan is generated according to the query statement test instance and the index metadata.

[0015] In some embodiments, obtaining the hidden index list includes:

[0016] Obtaining the object role and environment variables of the query statement test instance;

[0017] Acquire the interface startup status of the hidden interface according to the object role and the environment variable;

[0018] Obtaining database table structure parameters of the query statement test instance according to the interface startup state;

[0019] The hidden index list is obtained according to the database table structure parameters.

[0020] In some embodiments, storing the index metadata in a system directory of a test database according to the target field includes:

[0021] Creating an identity namespace based on the target field;

[0022] Obtaining an index identifier of the virtual index from the identifier namespace;

[0023] The index identifier and the index metadata are stored in a system directory of the test database.

[0024] In some embodiments, the system catalog further includes an original real index, and performing a query performance test on the test database according to the target execution plan includes:

[0025] Generate a reference execution plan based on the query statement test instance and the original real index, and calculate a reference execution cost of the reference execution plan;

[0026] Filtering the reference execution plan according to the reference execution cost to obtain a candidate execution plan;

[0027] Screening the target execution plan and the candidate execution plans to obtain a baseline execution plan;

[0028] Perform a query performance test on the test database according to the benchmark execution plan.

[0029] In some embodiments, before acquiring index metadata based on a pre-created virtual index, the database testing method based on a virtual index further includes:

[0030] In response to the database connection request, connecting to the test database and creating a current session;

[0031] The virtual index is created in the current session.

[0032] To achieve the above-mentioned purpose, a second aspect of the embodiments of the present application provides a database query method, which includes:

[0033] Obtain candidate virtual indexes and database query statements;

[0034] Creating a target real index based on the candidate virtual index;

[0035] The database query statement is executed according to the target real index.

[0036] To achieve the above-mentioned purpose, a third aspect of the embodiments of the present application provides a database query device, comprising:

[0037] An acquisition module is used to obtain candidate virtual indexes and database query statements;

[0038] A creation module, configured to create a target real index based on the candidate virtual index;

[0039] An execution module is used to execute the database query statement according to the target real index.

[0040] To achieve the above-mentioned purpose, the fourth aspect of an embodiment of the present application proposes an electronic device, which includes a memory and a processor, and the memory stores a computer program. When the processor executes the computer program, it implements the virtual index-based database testing method described in the first aspect or the database query method described in the second aspect.

[0041] To achieve the above-mentioned purpose, the fifth aspect of an embodiment of the present application proposes a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, it implements the virtual index-based database testing method described in the first aspect or the database query method described in the second aspect.

[0042] The database testing method, database query method, database query device, electronic device and computer-readable storage medium based on virtual index proposed in the embodiments of the present application need to write the real index to the disk when creating a real index, which involves a large number of input and output operations and the use of disk space, and consumes a large amount of computing resources. In order to reduce the resource consumption of database testing, a virtual index is created. The virtual index does not create an actual index structure on the disk. By obtaining the index metadata of the virtual index, the behavior of the real index is simulated based on the index metadata. The target field of the query statement test instance is extracted, and the index metadata is stored in the system directory of the test database according to the target field, so that the virtual index can participate in the execution plan generation process. Based on the query statement test instance and the index metadata of the system directory, a query execution plan is generated so that the test database can efficiently execute the query statement test instance based on the query execution plan, thereby improving the efficiency of database testing. The target execution cost of the query execution plan is calculated by a cost calculation model to predict the system resources required to execute the query statement test instance. In order to obtain the optimal execution plan, the query execution plan is screened according to the target execution cost to obtain the target execution plan of the query statement test instance. Perform query performance testing on the test database according to the target execution plan. By selecting the optimal execution plan for database testing, the resource consumption of database testing can be reduced. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 This is a flowchart of a database testing method based on virtual indexes provided in an embodiment of the present application;

[0044] Figure 2 is another flow chart of the database testing method based on virtual indexes provided in an embodiment of the present application;

[0045] Figure 3 yes Figure 1 Flowchart of step S120 in FIG.

[0046] Figure 4 yes Figure 1 Flowchart of step S130 in FIG.

[0047] Figure 5 yes Figure 4 Another flowchart of step S410 in FIG.

[0048] Figure 6yes Figure 1 Another flowchart of step S150 in FIG.

[0049] Figure 7 This is a flowchart of the database query method provided by an embodiment of the present application;

[0050] Figure 8 It is a structural diagram of a database query device provided in an embodiment of the present application;

[0051] Figure 9 This is a schematic diagram of the hardware structure of the electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0052] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0053] It should be noted that although the device schematics illustrate functional module divisions and the flowcharts illustrate logical sequences, in certain circumstances, the steps shown or described may be performed in a sequence that differs from the module divisions in the device or the sequence in the flowcharts. The terms "first," "second," and so on, in the specification, claims, and drawings, are used to distinguish similar items and are not necessarily used to describe a specific sequence or precedence.

[0054] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by those skilled in the art to which this application pertains. The terms used herein are for the purpose of describing the embodiments of this application only and are not intended to limit this application.

[0055] Indexes help databases quickly locate and retrieve data and are a crucial tool for optimizing query performance. For example, in a fintech scenario, a banking database is a data-intensive database that stores user information, account transaction records, loan records, and more. When a banking database receives a transaction record query request, it can create an index on the account field. This index allows the database to quickly locate the target transaction record without scanning the entire transaction record table. Another example is in a digital healthcare scenario, where a medical database stores patient information and electronic medical records. When a medical database receives a medical record query request, it can create an index on the medical record number field. This index allows the database to quickly locate the target record without scanning the entire medical record table. However, as data volume and business growth increase, database query performance can degrade, necessitating regular database testing.

[0056] In related technologies, database query performance testing is performed by creating real indexes. However, database testing based on real indexes consumes a large amount of computing resources. Therefore, how to reduce the resource consumption of database testing has become a technical problem that needs to be solved urgently.

[0057] Based on this, embodiments of the present application provide a database testing method, a database query method, a database query device, an electronic device, and a computer-readable storage medium based on a virtual index, aiming to reduce resource consumption in database testing.

[0058] The database testing method based on virtual index, database query method, database query device, electronic device and computer-readable storage medium provided in the embodiments of the present application are specifically illustrated through the following embodiments. First, the database testing method based on virtual index in the embodiments of the present application is described.

[0059] The database testing method based on virtual index provided in the embodiment of the present application relates to the field of computer technology. The database testing method based on virtual index provided in the embodiment of the present application can be applied to a terminal, can be applied to a server side, or can be software running in a terminal or a server side. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, etc.; the server side can be configured as an independent physical server, or as a server cluster or distributed system composed of multiple physical servers, or as a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms; the software can be an application that implements a database testing method based on virtual index, etc., but is not limited to the above forms.

[0060] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and the like. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, and the like that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments in which tasks are performed by remote processing devices connected via a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media, including storage devices.

[0061] Figure 1 This is an optional flowchart of the database testing method based on virtual indexes provided in an embodiment of the present application. Figure 1 The method may include but is not limited to steps S110 to S150.

[0062] Step S110, obtaining index metadata based on a pre-created virtual index;

[0063] Step S120 , extracting the target field of the query statement test instance, and storing the index metadata into the system directory of the test database according to the target field;

[0064] Step S130: Generate a query execution plan based on the query statement test instance and the index metadata of the system catalog, and calculate the target execution cost of the query execution plan using a cost calculation model; wherein the target execution cost is used to indicate the estimated resources consumed by executing the query execution plan;

[0065] Step S140, screening the query execution plan according to the target execution cost to obtain the target execution plan of the query statement test instance;

[0066] Step S150: Perform query performance testing on the test database according to the target execution plan.

[0067] In steps S110 to S150 shown in the embodiment of the present application, virtual indexes are used instead of real indexes, the virtual indexes are involved in the generation process of the execution plan, and the optimal execution plan is selected for database testing, thereby reducing resource consumption of database testing.

[0068] See also Figure 2 In some embodiments, before step S110, the database testing method based on virtual index may further include but is not limited to steps S210 to S220:

[0069] Step S210, in response to the database connection request, connecting to the test database and creating a current session;

[0070] Step S220: Create a virtual index in the current session.

[0071] In step S210 of some embodiments, before testing the database, it is necessary to establish a connection between the client and the test database. A database connection request sent by the client is obtained. The client can be an application, a script, or a database tool for database testing. The database connection request is used to establish a communication channel between the client and the test database so as to interact with the test database and perform operations such as query, update, insert, or delete. In response to the database connection request, check whether the user name and password in the database connection request are correct, and confirm whether the user has the authority to connect to the test database. If the user name and password are correct and the user has the connection authority, connect to the test database and create the current session, and allocate memory space and a session identifier for the current session. The session identifier is used to distinguish different sessions.

[0072] In step S220 of some embodiments, creating a real index will incur a certain trial and error cost, which will lead to resource waste when performing database testing based on the real index. The formula for the trial and error cost is defined as:

[0073]

[0074] Among them, m represents the number of candidate columns in the table, t single It represents the test time of a single column, and k represents the number of columns selected for testing.

[0075] Assuming that it takes 5 minutes to create an index, when m=10, the total cost will reach about 85 hours. When using real indexes for database testing, the persistence operation of a single index will significantly increase the storage space, and the process of building a real index will generate a higher amount of data writes than the original data, increasing the disk IO busy rate. The newly created real index will cause memory competition with the existing real index, resulting in a decrease in the efficiency of database testing. In order to improve the efficiency of database testing and reduce the consumption of memory resources by real indexes, the embodiment of the present application uses virtual indexes instead of real indexes. Virtual indexes only need to maintain a lightweight memory structure to simulate index behavior, without the need to create an actual index structure like a real index, and virtual indexes do not perform actual disk operations, do not need to scan the entire table data, and do not need to maintain the consistency of the index, which greatly reduces the resource consumption of database testing. Specifically, the client sends an index creation instruction through the current session, creates a virtual index according to the index creation instruction, and saves the virtual index in the memory space of the current session, without occupying physical memory. Index types of virtual indexes include btree, brin, hash, and bloom. The test database is a multi-process system, with each session corresponding to a process. By creating a virtual index in the current session, the virtual index only affects the transaction operations of the current session, without affecting the transactions of other sessions. This enables session-level lifecycle management. When a session ends, the virtual index in the session's memory space is also cleared.

[0076] In the above steps S210 to S220, the real index needs to occupy a large amount of memory to store the index structure, while the virtual index simulates the index behavior by using a lighter index structure, which occupies less memory resources and greatly saves memory resources.

[0077] In step S110 of some embodiments, index metadata is obtained based on a pre-created virtual index. The index metadata is used to describe the index structure, attributes, and related information of the virtual index. The index metadata includes the index name, index type, the table name to which the virtual index belongs, and the column fields involved in the virtual index. Taking the fintech scenario as an example, the index type can be a btree type, the table name to which the virtual index belongs can be a transaction record table, and the column fields can be fields such as user ID, transaction date, and transaction amount. Taking the digital medical scenario as an example, the index type can be a bloom type, the table name to which the virtual index belongs can be a health record table, and the column fields can be fields such as patient ID, diagnosis date, and symptom description.

[0078] In step S120 of some embodiments, the query statement test instance is a structured query statement for performing database testing, and the query statement test instance adopts Structured Query Language (SQL). The test database is the database to be tested, which is a relational database. The test database has a built-in query optimizer, which is used to manage and optimize the execution process of SQL queries. The hook function is passed as a parameter to the transaction processing component, and the transaction processing component intercepts the query statement test instance based on the multiple hook mechanisms indicated by the hook function, and extracts the target field of the query statement test instance, where the target field is the keyword in the query statement test instance. If the target field is the EXPLAIN field used to analyze and display the query execution plan, it means that the query statement test instance is an EXPLAIN statement, and the index metadata of the virtual index is stored in the system directory of the test database, so that the query optimizer mistakenly believes that the virtual index exists, but no actual index file is generated. The system directory is a special table in the database used to store metadata, which stores all metadata required for database operation.

[0079] See also Figure 3 In some embodiments, step S120 may include but is not limited to steps S310 to S330:

[0080] Step S310, creating an identification namespace according to the target field;

[0081] Step S320, obtaining a virtual index identifier of the virtual index from the identifier namespace;

[0082] Step S330: Store the virtual index identifier and index metadata in the system directory of the test database.

[0083] In step S310 of some embodiments, an object identifier (OID) is used to uniquely identify an object in a database. Indexes are objects in a database. During database testing, a real object identifier is assigned to each index, which can lead to object identifier resource exhaustion. To avoid exhaustion of real OID space resources, if the target field is an EXPLAIN field used to analyze and display query execution plans, an identifier namespace is created. The identifier namespace is a custom virtual OID space, not a real OID space.

[0084] In step S320 of some embodiments, a custom object identifier is obtained from the identifier namespace and used as a virtual index identifier of the virtual index. The virtual index identifier is a unique identifier of the virtual index and is used to distinguish different virtual indexes.

[0085] In step S330 of some embodiments, the virtual index identifier and the index metadata of the virtual index are stored in the system directory of the test database. It should be noted that after the session ends, the virtual index identifier and the index metadata of the virtual index need to be deleted to achieve session-level lifecycle management.

[0086] Through the above steps S310 to S330, the use of the real OID of the database can be avoided, and zero occupation of the real OID of the database can be achieved, thereby avoiding database abnormalities caused by exhaustion of the real OID and avoiding system directory pollution and execution plan errors caused by OID duplication.

[0087] In step S130 of some embodiments, multiple hook mechanisms are used to intercept the execution plan generation process, and the index metadata of the virtual index is injected to participate in the execution plan generation process. In the execution plan generation process, it is necessary to go through stages such as query parsing, semantic analysis, logical optimization, and physical optimization. The virtual index is added to the system directory during the physical optimization stage, so that the query optimizer can recognize the virtual index and enable the virtual index to participate in the physical optimization process. Specifically, through the query optimizer built into the test database, the query statement test instance, that is, the SQL statement, is converted into an executable structure of the database according to the index metadata of the system directory to obtain a query execution plan. The query execution plan refers to a set of operation steps generated by the test database for executing the query statement test instance, which is used to guide the execution engine of the test database to efficiently execute the query statement. The query execution plan describes in detail how the test database executes the query statement, including how to access data, which indexes to use, how to connect tables, how to sort and group, etc. The query optimizer estimates the target execution cost of a query execution plan using its built-in cost calculation model. This target execution cost indicates the estimated resources consumed by the test database to execute the query execution plan, rather than the actual resource consumption. The target execution cost is the sum of the estimated input / output costs, processor costs, memory costs, and network transmission costs. Input / output costs refer to the cost of accessing disk data, processor costs refer to the cost of performing computational operations such as sorting and joins, memory costs refer to the memory resources required to execute the query, and network transmission costs refer to the cost of transmitting data across the network in a distributed database.

[0088] See also Figure 4 In some embodiments, step S130 may include but is not limited to steps S410 to S430:

[0089] Step S410: Obtain a hidden index list; wherein the hidden index list includes a target hidden index;

[0090] Step S420, comparing the target hidden index with the virtual index of the system directory;

[0091] Step S430: If the target hidden index and the virtual index are different, a query execution plan is generated according to the query statement test instance and the index metadata of the virtual index.

[0092] In step S410 of some embodiments, to increase the flexibility of index analysis, a hidden index list is obtained. The hidden index list includes at least one target hidden index. A target hidden index is a special index that exists in the test database but is not automatically used by the query optimizer. By setting the hidden index list, the use of a virtual index is temporarily disabled, preventing the query optimizer from automatically selecting the virtual index. By intervening in the query optimizer's selection, the query performance of other indexes can be tested, supporting more complex query optimization.

[0093] Taking FinTech scenarios as an example, financial data is highly dynamic, with data volume and distribution fluctuating frequently. Consider a banking database that stores a transaction record table containing fields such as transaction date and amount. If transaction dates are concentrated on a few days, using an index based on transaction date won't improve query performance when querying transaction records spanning a large time period due to the uneven distribution of transaction date data. Hiding the index on transaction date allows you to observe the query optimizer's behavior without this index, eliminating the need to frequently rebuild and delete the index. This allows for flexible testing of database performance without impacting other queries.

[0094] For example, consider a digital healthcare scenario. Suppose a medical database stores a table of health check results, which includes fields such as patient ID and test date. When the table has a small amount of data, an index based on test date performs well. However, as the data volume increases, this index may not be the best choice. By setting this index to hidden, we can prevent the query optimizer from misusing it, thereby ensuring query performance.

[0095] In step S420 of some embodiments, in order to determine whether the virtual index is disabled, the target hidden index is compared with the virtual index of the system directory.

[0096] In step S430 of some embodiments, if the target hidden index and the virtual index are different, indicating that the virtual index is not disabled, a query execution plan is generated by a query optimizer built into the test database based on the query statement test instance and the index metadata of the virtual index. If the target hidden index and the virtual index are the same, indicating that the virtual index is disabled.

[0097] Through the above steps S410 to S430, the flexibility of index analysis can be increased during database testing.

[0098] See also Figure 5In some embodiments, step S410 may include but is not limited to steps S510 to S540:

[0099] Step S510, obtaining the object role and environment variables of the query statement test instance;

[0100] Step S520, obtaining the interface startup status of the hidden interface according to the object role and the environment variable;

[0101] Step S530, obtaining database table structure parameters of the query statement test instance according to the interface startup state;

[0102] Step S540: Obtain a hidden index list according to the database table structure parameters.

[0103] In some embodiments, step S510 obtains an object role and environment variables for the query statement test instance. The object role is the role of the creator of the query statement test instance, and defines the creator's access rights to the hidden index list. The environment variables are used to indicate the execution environment of the query statement test instance, including the production environment or the test environment, and can limit the scenarios in which the hidden index list can be obtained.

[0104] In step S520 of some embodiments, if the object role has the permission to obtain the hidden index list, and the environment variable is a test environment, it means that the creator of the query statement test instance has the permission to obtain the hidden index list, and is currently in a database test scenario, that is, the creator can obtain the hidden index list for database testing, then the state of the hidden interface is set to the interface startup state. If the object role does not have the permission to obtain the hidden index list, then the state of the hidden interface is set to the interface shutdown state. If the environment variable is a production environment, it means that the current scenario is a real database query scenario. In order to ensure the stability of the database query, the state of the hidden interface is set to the interface shutdown state.

[0105] In step S530 of some embodiments, if the state of the hidden interface is the interface startup state, the database table structure parameters of the query statement test instance are obtained. The database table structure parameters are parameters that define and describe the structure and properties of the database table. These parameters determine the composition of the table, data storage method, constraints and relationship with other tables.

[0106] In step S540 of some embodiments, the purpose of setting a hidden index list is to disable some indexes, so the database table structure parameters need to include indexes. If the database table structure parameters include virtual indexes, the hidden index list is obtained, and whether to disable the virtual index is determined based on the hidden index list. It should be noted that the metadata information of the virtual index includes the mapping relationship between the virtual index and the columns of the database table, and the database table structure parameters include the virtual index, the table name of the database table to which the virtual index is mapped, the column name, etc.

[0107] Through the above steps S510 to S540, the security of the hidden index list acquisition process can be ensured, and the impact of the test process on the real database query is avoided, thereby ensuring the stability of the real database query.

[0108] In step S140 of some embodiments, the query optimizer generates at least one query execution plan for the query statement test instance. If the number of query execution plans is one, the query execution plan is used as the target execution plan for the query statement test instance. If the number of query execution plans is greater than one, to improve the efficiency of database testing and reduce resource consumption during the database testing process, the query execution plan with the lowest target execution cost is selected as the target execution plan for the query statement test instance.

[0109] In step S150 of some embodiments, to avoid memory contention between the newly created real index and the original real index during database testing, which would reduce database query efficiency, the target execution plan is executed to perform query performance testing on the test database, and query performance parameters such as response time, resource consumption, and throughput of the test database for the query statement test instance are output.

[0110] See also Figure 6 In some embodiments, the system directory further includes an original real index, which is a real index with an actual index structure. Step S150 may include but is not limited to steps S610 to S640:

[0111] Step S610: Generate a reference execution plan based on the query statement test instance and the original real index, and calculate the reference execution cost of the reference execution plan;

[0112] Step S620 , screening the reference execution plans according to the reference execution costs to obtain candidate execution plans;

[0113] Step S630, screening the target execution plan and candidate execution plans to obtain a baseline execution plan;

[0114] Step S640: Perform query performance testing on the test database according to the benchmark execution plan.

[0115] In step S610 of some embodiments, a query optimizer built into the test database generates an execution plan based on the query statement test instance and the original real index, obtaining a reference execution plan. The reference execution plan is then executed using the EXPLAIN ANALYZE command to obtain a reference execution cost for the query statement test instance. The reference execution cost indicates the actual resources consumed by the test database in executing the reference execution plan. The reference execution cost is the sum of the actual input and output costs, processor costs, memory costs, and network transmission costs.

[0116] In step S620 of some embodiments, the query optimizer generates at least one reference execution plan for the query statement test instance based on the original real index. If the number of reference execution plans is one, the reference execution plan is used as a candidate execution plan for the query statement test instance. If the number of reference execution plans is greater than one, the reference execution plan with the lowest reference execution cost is selected as the candidate execution plan for the query statement test instance.

[0117] In step S630 of some embodiments, the index can reduce lock contention, improve transaction concurrency processing capabilities, and accelerate database queries. For example, the query time in the range of 1 billion can be reduced from minutes to milliseconds through the btree index. However, some real indexes have not been actually used by the database after they are created. In order to obtain an execution plan with higher query execution efficiency and identify real indexes with poor query performance, the reference execution cost of the candidate execution plan and the target execution cost of the target execution plan are compared. If the reference execution cost is less than the target execution cost, the candidate execution plan is used as the baseline execution plan. If the reference execution cost is greater than or equal to the target execution cost, the target execution plan is used as the baseline execution plan.

[0118] In step S640 of some embodiments, the benchmark execution plan is executed to perform a query performance test on the test database, and query performance parameters of the test database are output.

[0119] Through steps S610 to S640, the efficiency of database testing can be improved and the resource overhead generated during the testing process can be reduced. Furthermore, due to the participation of virtual indexes, the database can reduce memory competition with real indexes when processing high-concurrency requests, thereby improving database query performance.

[0120] The database testing method based on virtual indexes in the embodiment of the present application uses custom OIDs to avoid database system anomalies caused by exhaustion of real OIDs due to test operations, enhances the stability of the database during index analysis, and improves the security and robustness of the database. Virtual indexes do not take up disk space, and the memory usage is only at the metadata level. The embodiment of the present application also uses virtual indexes to replace real indexes for database testing, achieving zero storage overhead, which can support ultra-large-scale index testing scenarios and significantly reduce the cost of index testing. At the same time, virtual indexes can be used to identify real indexes with poor efficiency in advance, avoiding the problem of decreased database write performance due to index expansion. In addition, the embodiment of the present application can operate on virtual indexes using standard SQL statements, and the learning cost is low.

[0121] The embodiment of the present application further provides a database testing device based on a virtual index, which can implement the above-mentioned database testing method based on a virtual index. The database testing device based on a virtual index includes:

[0122] A metadata acquisition module is used to acquire index metadata based on a pre-created virtual index;

[0123] The storage module is used to extract the target fields of the query statement test instance and store the index metadata in the system directory of the test database according to the target fields;

[0124] A generation module is used to generate a query execution plan based on the query statement test instance and the index metadata of the system catalog, and calculate the target execution cost of the query execution plan using a cost calculation model. The target execution cost indicates the estimated resources consumed by executing the query execution plan.

[0125] A screening module is used to screen the query execution plan according to the target execution cost to obtain the target execution plan of the query statement test instance;

[0126] The test module is used to perform query performance testing on the test database according to the target execution plan.

[0127] The specific implementation of the virtual index-based database testing device is substantially the same as the specific embodiment of the virtual index-based database testing method described above, and will not be described in detail herein.

[0128] In some embodiments, the generation module is further configured to:

[0129] Obtain a hidden index list, where the hidden index list includes the target hidden index; compare the target hidden index with the virtual index of the system catalog; if the target hidden index and the virtual index are different, test the instance and index metadata based on the query statement and generate a query execution plan.

[0130] In some embodiments, the generation module is further configured to:

[0131] Obtain the object role and environment variables of the query statement test instance; obtain the interface startup status of the hidden interface according to the object role and environment variables; obtain the database table structure parameters of the query statement test instance according to the interface startup status; obtain the hidden index list according to the database table structure parameters.

[0132] In some embodiments, the storage module is further configured to:

[0133] Create an identity namespace based on the target field; obtain the index identity of the virtual index from the identity namespace; and store the index identity and index metadata in the system catalog of the test database.

[0134] In some embodiments, the test module is further configured to:

[0135] Based on the query statement test instance and the original real index, a reference execution plan is generated and the reference execution cost of the reference execution plan is calculated; the reference execution plan is screened based on the reference execution cost to obtain candidate execution plans; the target execution plan and candidate execution plans are screened to obtain a baseline execution plan; and query performance testing is performed on the test database based on the baseline execution plan.

[0136] In some embodiments, the metadata acquisition module is further configured to: connect to the test database and create a current session in response to a database connection request; and create a virtual index in the current session.

[0137] Figure 7 This is an optional flowchart of the database query method provided in the embodiment of the present application. Figure 7 The method may include but is not limited to steps S710 to S730.

[0138] Step S710, obtaining a candidate virtual index and a database query statement;

[0139] Step S720: creating a target real index based on the candidate virtual index;

[0140] Step S730: Execute the database query statement according to the target real index.

[0141] In step S710 of some embodiments, a database query statement is obtained. The database query statement adopts a structured query language and may include query operations, insert operations, update operations, and delete operations. Referring to steps S110 to S140, a virtual index is created and the index metadata of the virtual index is obtained. The database query statement is intercepted by a multi-hook mechanism, and it is determined whether the database query statement contains an EXPLAIN field. If the judgment result is yes, the index metadata is stored in the system directory of the target database. The target database is the database to be accessed. The target database can be a test database or other database. The query optimizer set by the target database generates an execution plan based on the database query statement and the index metadata of the system directory, and calculates the execution cost of the execution plan through a cost calculation model. The execution cost is used to indicate the estimated resources consumed by the execution plan. The virtual index with the smallest execution cost is selected as the candidate virtual index, so that the target database can accurately identify a high-performance candidate index set and avoid ineffective investment in indexes with low query efficiency.

[0142] In step S720 of some embodiments, to avoid creating a real index with poor query performance, a candidate virtual index is obtained in advance, and a real index is created based on the index metadata of the candidate virtual index to obtain the target real index, thereby reducing the time and resource overhead consumed by maintaining an invalid real index. By creating a virtual index, the impact of the virtual index on query performance can be tested without actually occupying storage space. If the virtual index significantly improves database query performance, it is converted to a real index, reducing the impact on database queries in the production environment.

[0143] In step S730 of some embodiments, the database query statement is executed according to the target real index to improve the execution efficiency of the database query statement.

[0144] Suppose that the transaction record table in the banking business database has 2,000 transaction records, and this table does not have any indexes. If an index needs to be added, the database administrator conducts an empirical analysis of the transaction record table, selects several feasible indexing schemes, and creates a real index for verification. The large amount of data in the transaction record table will cause the creation time of the real index to be prolonged, increasing the disk IO pressure. Without verifying that the real index is valid, it is equivalent to conducting a dangerous experiment directly in the production environment, and the real index will affect the data transaction performance of the banking business database. The embodiment of the present application uses a virtual index for database testing. Since the virtual index only exists in memory and only saves metadata, it will not scan the table data, solving the problem of long index creation time. At the same time, the virtual index only exists in the session memory opened by the database administrator and has no effect on the data transaction performance of the banking business database.

[0145] Through the above steps S710 to S730, the query performance of the database can be improved, and the execution efficiency of the database query statements can be improved.

[0146] See also Figure 8 , the embodiment of the present application further provides a database query device that can implement the above database query method, the database query device:

[0147] An acquisition module 810 is configured to acquire a candidate virtual index and a database query statement;

[0148] A creation module 820 is configured to create a target real index based on the candidate virtual index;

[0149] The execution module 830 is used to execute the database query statement according to the target real index.

[0150] The specific implementation of the database query device is basically the same as the specific embodiment of the above-mentioned database query method, and will not be repeated here.

[0151] The present application also provides an electronic device comprising a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the aforementioned virtual index-based database testing method or database query method. The electronic device can be any smart terminal, such as a tablet computer or an in-vehicle computer.

[0152] See also Figure 9 , Figure 9 The hardware structure of an electronic device according to another embodiment is shown. The electronic device includes:

[0153] The processor 910 may be implemented as a general-purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is configured to execute relevant programs to implement the technical solutions provided in the embodiments of the present application.

[0154] The memory 920 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 920 can store an operating system and other application programs. When the technical solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 920 and is called by the processor 910 to execute the database testing method or database query method based on virtual indexes in the embodiments of this application.

[0155] Input / output interface 930, used to implement information input and output;

[0156] Communication interface 940, used to implement communication interaction between this device and other devices, which can be achieved through wired means (such as USB, network cable, etc.) or wireless means (such as mobile network, WiFi, Bluetooth, etc.);

[0157] bus 950 , which transmits information between various components of the device (e.g., processor 910 , memory 920 , input / output interface 930 , and communication interface 940 );

[0158] The processor 910 , the memory 920 , the input / output interface 930 , and the communication interface 940 are connected to each other in communication within the device via a bus 950 .

[0159] An embodiment of the present application further provides a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the above-mentioned database testing method or database query method based on virtual indexing.

[0160] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely arranged relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0161] The embodiments of the present application provide a database testing method, a database query method, a database query device, an electronic device, and a computer-readable storage medium based on a virtual index. By replacing a real index with a virtual index, the virtual index is involved in the generation process of the execution plan, and the optimal execution plan is selected for database testing, thereby reducing the resource consumption of the database test.

[0162] The embodiments described in the embodiments of this application are intended to more clearly illustrate the technical solutions of the embodiments of this application and do not constitute a limitation on the technical solutions provided by the embodiments of this application. Those skilled in the art will appreciate that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided in the embodiments of this application are also applicable to similar technical problems.

[0163] Those skilled in the art will understand that the technical solutions shown in the figures do not constitute a limitation on the embodiments of the present application, and may include more or fewer steps than shown in the figures, or a combination of certain steps, or different steps.

[0164] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, i.e., they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of this embodiment.

[0165] Those skilled in the art will appreciate that all or some of the steps in the methods, systems, and functional modules / units in the devices disclosed above may be implemented as software, firmware, hardware, or appropriate combinations thereof.

[0166] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0167] It should be understood that in this application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following items" or similar expressions refers to any combination of these items, including any combination of single items or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.

[0168] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely schematic. For example, the division of the above-mentioned units is only a logical function division. In actual implementation, there may be other division methods, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0169] The units described above as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of these units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0170] In addition, the functional units in the various embodiments of the present application may be integrated into a single processing unit, or each unit may exist physically separately, or two or more units may be integrated into a single unit. The aforementioned integrated units may be implemented in the form of hardware or software functional units.

[0171] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, which is stored in a storage medium and includes multiple instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of various embodiments of the present application. The aforementioned storage medium includes: various media that can store programs, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0172] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but are not intended to limit the scope of the present invention. Any modifications, equivalent substitutions, and improvements made by those skilled in the art without departing from the scope and essence of the present invention should be within the scope of the present invention.

Claims

1. A database testing method based on virtual index, characterized in that: The method comprises: Get index metadata based on pre-created virtual index; Extracting a target field of a query statement test instance, and storing the index metadata in a system directory of a test database according to the target field; Generate a query execution plan based on the query statement test instance and the index metadata of the system catalog, and calculate a target execution cost of the query execution plan using a cost calculation model; wherein the target execution cost is used to indicate an estimated resource consumed by executing the query execution plan; Filtering the query execution plan according to the target execution cost to obtain a target execution plan for the query statement test instance; Perform query performance testing on the test database according to the target execution plan.

2. The method according to claim 1, characterized in that Generating a query execution plan according to the query statement test instance and the index metadata of the system catalog includes: Obtaining a hidden index list; wherein the hidden index list includes a target hidden index; comparing the target hidden index with the virtual index of the system directory; If the target hidden index is different from the virtual index, the query execution plan is generated according to the query statement test instance and the index metadata.

3. The method according to claim 2, characterized in that The step of obtaining the hidden index list includes: Obtaining the object role and environment variables of the query statement test instance; Acquire the interface startup status of the hidden interface according to the object role and the environment variable; Obtaining database table structure parameters of the query statement test instance according to the interface startup state; The hidden index list is obtained according to the database table structure parameters.

4. The method according to claim 1, wherein Storing the index metadata in a system directory of a test database according to the target field includes: Creating an identity namespace based on the target field; Obtaining an index identifier of the virtual index from the identifier namespace; The index identifier and the index metadata are stored in a system directory of the test database.

5. The method according to claim 1, wherein The system catalog further includes an original real index. The query performance test on the test database according to the target execution plan includes: Generate a reference execution plan based on the query statement test instance and the original real index, and calculate a reference execution cost of the reference execution plan; Filtering the reference execution plan according to the reference execution cost to obtain a candidate execution plan; Screening the target execution plan and the candidate execution plans to obtain a baseline execution plan; Perform a query performance test on the test database according to the benchmark execution plan.

6. The method according to any one of claims 1 to 5, characterized in that Before acquiring index metadata based on the pre-created virtual index, the method further includes: In response to the database connection request, connecting to the test database and creating a current session; The virtual index is created in the current session.

7. A database query method, characterized in that: The method comprises: Obtain candidate virtual indexes and database query statements; Creating a target real index based on the candidate virtual index; The database query statement is executed according to the target real index.

8. A database query device, characterized in that: The device comprises: An acquisition module is used to obtain candidate virtual indexes and database query statements; A creation module, configured to create a target real index based on the candidate virtual index; An execution module is used to execute the database query statement according to the target real index.

9. An electronic device, characterized in that: The electronic device includes a memory and a processor, the memory stores a computer program, and when the processor executes the computer program, it implements the database testing method based on virtual indexes according to any one of claims 1 to 6 or the database query method according to claim 7.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the virtual index-based database testing method according to any one of claims 1 to 6 or the database query method according to claim 7 is implemented.

Images