CN116361288A

CN116361288A - Index recommendation method, device, equipment and storage medium

Info

Publication number: CN116361288A
Application number: CN202310289465.2A
Authority: CN
Inventors: 张兰坤
Original assignee: New H3C Big Data Technologies Co Ltd
Current assignee: New H3C Big Data Technologies Co Ltd
Priority date: 2023-03-22
Filing date: 2023-03-22
Publication date: 2023-06-30

Abstract

The application provides an index recommendation method, device, equipment and storage medium, which are used for solving the technical problem of selecting and determining a database index to optimize SQL sentence execution performance. The method improves SQL commands for checking execution plans provided by a database, after a query tree is generated, a first execution plan without using virtual indexes is generated first, the query tree is analyzed to obtain table and column information related to SQL sentences, virtual indexes are created based on the table and column information, a second execution plan is generated based on the created virtual indexes, and finally index information used by the second execution plan is output to obtain recommended indexes. According to the technical scheme, the automatic recommending function of the database index is realized by utilizing the hook function interface and the virtual indexing technology provided by the database, so that the efficiency of database development and optimization can be improved.

Description

Index recommendation method, device, equipment and storage medium

Technical Field

The present disclosure relates to the field of database technologies, and in particular, to an index recommendation method, apparatus, device, and storage medium.

Background

Currently, most object relational databases such as PostgreSQL are usually used for storing data in a persistent manner in a storage system constructed by a disk, but are limited by the current disk read-write speed, and the structured query language SQL command of many read data is slow to execute, so that the speed of SQL query can be obviously improved by creating a proper index, but for a relatively complex SQL statement, how to create a proper index is not well determined, or it is considered that a valid index is not actually used.

Disclosure of Invention

In view of this, the present application provides an index recommendation method, apparatus, device, and storage medium for solving the technical problem of selecting and determining a database index to optimize the performance of SQL statements.

Based on an aspect of the embodiments of the present application, the present application provides an index recommendation method, which includes:

generating a query tree of SQL sentences;

generating a first execution plan based on the query tree that does not use a virtual index;

analyzing the query tree to obtain table and column information related to the query conditions of the SQL sentence, and creating a corresponding virtual index for the arrangement and combination of columns in the related table;

generating a second execution plan based on the created virtual index;

and acquiring an index used by the second execution plan and outputting the acquired index as a recommendation index.

Further, the method further comprises: if the first execution plan uses the entity index, before the second execution plan is generated, removing the permutation and combination corresponding to the entity index from the created virtual index.

Further, the method is applied to SQL commands of database checking execution plans, introduces the virtual index for the second execution plan by means of registering a hook function, and acquires an index used by the second execution plan.

Further, the costs of the second execution plan and the first execution plan are compared, when the costs of the second execution plan are smaller than the costs of the first execution plan and the difference is larger than a preset proportion, the recommendation index is output, and otherwise, the recommendation index is not output.

According to another aspect of the embodiments of the present application, there is further provided an index recommendation apparatus, including:

the query tree generation module is used for generating a query tree of the SQL sentence;

the virtual index module is used for analyzing the query tree to obtain the table and column information related to the query conditions of the SQL sentence, and creating a corresponding virtual index for the arrangement and combination of columns in the related table;

an execution plan generation module for generating a first execution plan based on the query tree that does not use a virtual index; and generating a second execution plan based on the created virtual index;

and the recommendation index output module is used for acquiring the index used by the second execution plan and outputting the acquired index as a recommendation index.

Further, the virtual index module is further configured to, in a case where the first execution plan uses an entity index, reject, from the created virtual index, an permutation and combination corresponding to the entity index before generating a second execution plan.

Further, the device is applied to SQL commands of database checking execution plans, introduces the virtual index for the second execution plan by means of registering a hook function, and acquires the index used by the second execution plan.

Further, the apparatus further comprises: and the comparison module is used for comparing the spending of the second execution plan and the first execution plan, and when the spending of the second execution plan is smaller than the spending of the first execution plan and the difference value is larger than a preset proportion, the recommendation index output module is instructed to output the recommendation index, otherwise, the recommendation index is not output.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following description will briefly describe the drawings that are required to be used in the embodiments of the present application or the description in the prior art, and it is obvious that the drawings in the following description are only some embodiments described in the present application, and other drawings may also be obtained according to these drawings of the embodiments of the present application for a person having ordinary skill in the art.

FIG. 1 is a schematic diagram of an improved process for database-provided displain SQL commands;

FIG. 2 is a flowchart illustrating steps of an index recommendation method according to an embodiment of the present disclosure;

fig. 3 is a schematic structural diagram of an electronic device for implementing the index recommendation method provided in an embodiment of the present application.

Detailed Description

The terminology used in the embodiments of the application is for the purpose of describing particular embodiments only and is not intended to be limiting of the embodiments of the application. As used in the embodiments of the present application, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should be understood that although the terms first, second, third, etc. may be used in embodiments of the present application to describe various information, these information should not be limited to these terms. These terms are only used to distinguish one from another or similar information, entity or step, but not to describe a particular sequence or order. For example, the first information may also be referred to as second information, and similarly, the second information may also be referred to as first information, without departing from the scope of embodiments of the present application. Furthermore, the word "if" as used may be interpreted as "at … …" or "at … …" or "in response to a determination". The term "and/or" in this application is merely an association relation describing an association object, and means that three kinds of relations may exist, for example, a and/or B may mean: there are three cases, a alone, a and B together, and B alone, wherein a, B may be singular or plural. Also, in the description of the present application, unless otherwise indicated, "a plurality" means two or more than two. "at least one of" or the like means any combination of these items, including any combination of single item(s) or plural items(s). For example, at least one (one) of a, b, or c may represent: a, b, c, a-b, a-c, b-c, or a-b-c, wherein a, b, c may be single or plural.

The technical scheme can be applied to SQL command programs or optimization tools of database checking execution plans or provided as plug-ins for users. In order to implement the technical scheme provided by the application, the database system needs to support the establishment of a virtual index and the provision of a hook function to allow a user to customize the construction and the introduction of the virtual index. The basic idea of the application is: the method comprises the steps of improving SQL commands such as displain for checking execution plans provided by a database, after a query tree QueryTree is generated, firstly generating a first execution plan without using virtual indexes, analyzing the query tree to obtain table and column information related to SQL sentences, creating a virtual index based on the table and column information, generating a second execution plan based on the created virtual index, and finally outputting index information used by the second execution plan to obtain a recommended index. According to the technical scheme, the automatic recommending function of the database index can be realized by utilizing the hook function interface and the virtual indexing technology provided by the database, and the efficiency of database development and optimization can be improved.

Based on the basic ideas of the present application, the following describes the specific implementation of the present application in detail with reference to the accompanying drawings.

FIG. 1 is a schematic diagram of an improvement of the database provided displain SQL command. Many databases (e.g., postgreSQL, SQLServer, etc.) provide SQL commands like displain, which a developer or database administrator outputs an execution Plan by adding displain keywords before an SQL statement, the displain outputting the execution Plan of the SQL statement without actually executing the Plan. The performance bottleneck information of the SQL statement can be checked by using the displain, and related optimization of SQL is performed according to the result. The Select query statement in the SQL is preceded by an displain keyword, the SQL statement is not really executed during execution, and execution plan information corresponding to the SQL query statement is returned. The step flow of the execution plan of the Explin output SQL query statement is as follows:

step 110, analyzing SQL sentences to generate an analysis tree ParseTree;

step 120, generating a query tree QueryTree based on the analysis tree ParseTree;

step 130, generating an optimal execution Plan according to the query tree QueryTree;

and 140, outputting the execution Plan and the overhead information according to the execution Plan.

In the process of generating an execution plan according to a query tree, the database query compiling engine has the optimization capability of the execution plan, can automatically select the best execution path to generate the execution plan according to the spending of various possible execution paths, and if the fields in the related table are built with indexes, the database query compiling engine can consider the influence of the actually created indexes on query performance when generating the execution plan and output the optimal execution plan. Under the condition that the displain command is not used, the database query compiling engine can obtain an actual query result according to the execution plan.

The method mainly aims at improvement of steps after a query tree is generated according to SQL commands of an execution plan of an SQL statement such as displain, a first execution plan is generated according to a primary basic flow of a database query compiling engine, table and column information in the table, which are involved in query in the SQL statement, are obtained through analysis of QueryTree, virtual indexes which possibly improve query efficiency are built based on the table and the column information, a virtual index descriptor is filled through a Hook function interface provided by a database, a second execution plan is generated on the basis of the created virtual indexes, the database query compiling engine selects an optimal execution path based on the virtual indexes in the process of generating the second execution plan so as to generate the second execution plan, and recommended indexes can be obtained through outputting information of the virtual indexes used by the second execution plan.

In the technical scheme of the application, hook functions provided by a database are needed, and a PostgreSQL database is taken as an example, the application can be realized by using hook function interfaces reserved by PostgreSQL, and mainly relates to 3 hook function interfaces, which are respectively:

ExpainOneQuery_hook: the Hook function is output by an execution plan for short, the Hook function interface is a program entry of an index recommendation method provided by the application, and input parameters are QueryTree;

get_relation_info_hook: the Hook function interface is mainly used for introducing a virtual index to the database query compiling engine so that the database query compiling engine can generate a second execution plan;

displain_get_index_name_hook: in short, the Hook function of the index name is obtained, and the information of the index used by the second execution plan is obtained through the Hook function interface.

Before the procedure of the index recommendation method provided in the present application is implemented, the_pg_init and_pg_final functions need to be registered using the standard plug-in framework of PostgreSQL.

the_PG_init is mainly used for initializing hook functions, memory and other resources;

PG_final is mainly to remove the hook function and the release of the resources such as the memory.

The application registers the 3 hook functions in the_PG_init function, and registers the 3 hook functions in the_PG_fini function. After registering the 3 hook functions, the query compiling engine automatically calls the 3 hook functions at proper time when executing the displain command so as to obtain the index recommendation result expected by the application.

In the case of an actual production database, if there are many data records in the table, it will take a long time to actually create an index, and the larger the data volume, the slower the data volume, and also cause a large number of disk reads and writes, especially if it is not determined whether the index to be created will be used by the new execution Plan, it is not suitable to directly create the index. In order to optimize database performance and improve flexibility, a large number of hook function interfaces are provided for many databases, such as PostgreSQL databases, so that a function of introducing virtual indexes can be realized, and by introducing the virtual indexes, a database query compiling engine can access index information without actually creating the indexes, so that an execution plan using the virtual indexes is generated.

In the PostgreSQL database, creating a virtual index requires populating a virtual index descriptor as in table 1:

TABLE 1

vindex attribute names	Virtual index vindex descriptor attribute meaning
		oid	Virtual indexed object identification
table_oid	Object identification of a table to which a virtual index belongs
		Name	Virtual index name
Pages	Virtual index page number
		tuples	Virtual index tuple number
column	Columns referred to by virtual index
		…	…

Fig. 2 is a flowchart illustrating steps of an index recommendation method according to an embodiment of the present application. Taking the PostgreSQL database as an example, the present application executes the program steps of the index recommendation method provided in the present application by registering and instantiating the displaineonequery_hook function, the program code executed by the displaineonequery_hook function is part of the displain command program, after generating the query tree in step 120 of fig. 1, the database query compilation engine will automatically call the displaineonequery_hook function registered in the present application and transmit the query tree through the input parameters, and the steps executed by the function specifically include:

step 210, generating a first execution plan without using virtual indexes based on the query tree;

in this step, the query compilation engine generates the first execution plan according to the standard execution plan generation process, and does not use the virtual index.

In the process of generating the first execution plan, the database query compiling engine knows whether the table related to the SQL sentence is built with entity indexes and which entity indexes according to the data dictionary. The entity index is an index created on the basis of the data actually stored in the table, and the entity index has corresponding index files. The database query compilation engine may also use the entity index when generating the first execution plan, and if the first execution plan uses the entity index, the application may also eliminate combinations of fields for which the entity index has been created in a later step of generating the virtual index.

Step 220, analyzing the query tree to obtain the table and column information related to the query condition of the SQL sentence, and creating a corresponding virtual index for the arrangement and combination of columns in the related table;

analyzing the transmitted query tree QueryTree, obtaining table and column information in the table related to the query condition of the SQL sentence, and according to the query tree analysis result, arranging and combining the columns of the related table to generate all possible virtual indexes. For example, the SQL sentence involves a test table, the test table has three columns a, b and c, and by analyzing the QueryTree, two columns a and b are found in the query condition of the SQL sentence, and the permutation and combination of the columns a and b are (a), (a, b), (b, a) and (b), so that corresponding virtual indexes are created for the four possible cases of creating index indexes.

If an entity index is created for field column a of the test table before the SQL statement is optimally analyzed, then the case of a can be eliminated from the permutation and combination of a and b in the step, and only virtual indexes corresponding to the three cases of (a, b), (b, a) and (b) are generated.

The step of creating a virtual index is mainly to populate the virtual index descriptor as exemplified in the foregoing table 1, and is fast because creating a virtual index does not actually create an entity index file. Some of the attribute contents in the virtual index descriptor, such as notes, pages, etc., may be populated based on the condition of the data present in the table.

Step 230, generating a second execution plan based on the created virtual index;

in this step, the execution Plan generating function of the database will be called again to generate a second execution Plan based on the created virtual index, and in the process of generating the second execution Plan2, the previously registered get_relation_info_hook function will be called, and the created virtual index will be introduced into the execution Plan creating process through this function, so that the PostgreSQL will obtain the virtual index information and then regenerate into the second execution Plan2.

Step 240, outputting the index used by the second execution plan as a recommendation index;

in this step, after the second execution plan is generated, the registered function of obtaining the index name hook is called to obtain the index used by the second execution plan, the index used by the second execution plan is used as the recommended index, the recommended index information is output and stored in the designated database or the local file, and the virtual index not used by the second execution plan is discarded.

In an embodiment of the present application, the SQL command for checking the execution Plan may further output information of the first execution Plan1 and the second execution Plan2, and the output execution Plan information may include overhead cost data of the execution Plan, and by comparing cost values of the Plan2 and the Plan1, an efficiency value of improvement of the Plan2 may be obtained.

Taking a postgreSQL database as an example, the index recommendation method provided by the application is applied to the displain command, and a process example of obtaining a recommendation index through the displain command is as follows:

there are two tables of test1 (a, b) and test2 (a, c) in the database, and there are more than 10000 rows of data records, and the SQL statement to be optimized is:

select*from test2 join test1 on test2.a<test1.a；

example (1): an example of viewing the execution plan of the SQL statement through displain without using the technical scheme of the present application is as follows:

as can be seen from the information output, no index is used in the generated execution plan.

Example (2): an example of viewing the execution plan of the SQL statement by using the displain of the technical scheme of the present application is as follows:

in the process of generating Plan2, first, the execution Plan generated in example (1) is parsed from the parse tree query to the a-column related to the test1 table and the a-column related to the test2 table, virtual indexes of the a-column are respectively established for the two tables, and if a plurality of columns are related, it is necessary to perform permutation and combination on the related columns and generate virtual indexes corresponding to each permutation and combination. The created virtual index is then introduced in the process of generating Plan2, ultimately generating execution Plan2. As can be seen from the above-described output information, that is, the information of Plan2, although virtual index is created on both test1 (a) and test2 (a), plan2 uses only virtual index on test1 (a), that is, only virtual index on test1 (a) is valid and index on test2 (a) is invalid. The SQL statement execution overhead for Plan1 is "150003136.00", while the overhead for Plan2 is "91674487.00", making it apparent that Plan2 is much more efficient than Plan 1. Finally, the modified displain command program of the technical scheme can output the virtual index used by Plan2, namely the index on test1 (a), as a recommended index to a user so as to provide basis for database optimization.

For complex SQL statements, multiple tables and multiple fields may be involved, so multiple recommendation indexes may be given for the multiple tables involved, and considering that the resource consumption for creating the entity index is large, in an embodiment of the present application, comparing the overhead of the second execution plan with the overhead of the first execution plan is further included, when it is determined that the overhead of the second execution plan is smaller than the overhead of the first execution plan and the difference is greater than a preset proportion (for example, 30%), the recommendation indexes are explicitly output, otherwise, performance improvement is not considered to be obvious, and the recommendation indexes are not explicitly output.

Fig. 3 is a schematic structural diagram of an electronic device for implementing the index recommendation method provided in the present application, where the device 300 includes: a processor 310 such as a Central Processing Unit (CPU), a communication bus 320, a communication interface 340, and a memory 330. Wherein the processor 310 and the memory 330 may communicate with each other via a communication bus 320. The memory 330 has stored therein a computer program that, when executed by the processor 310, performs the functions of one or more steps in the index recommendation method provided herein.

Memory refers to a device for storing computer programs and/or data based on some storage medium, which may be a Volatile Memory (VM) or a Non-Volatile Memory (NVM). The memory is an internal memory for directly exchanging data with the processor, and can read and write data at any time, and has high speed, and is used as a storage medium for temporary data of an operating system and other running programs. The memory may be synchronous dynamic random access memory (Synchronous Dynamic Random Access Memory, SDRAM), dynamic random access memory (Dynamic Random Access Memory, DRAM), or the like. The nonvolatile memory is a memory using a persistent storage medium, and has a large capacity and can store data permanently, and may be a storage class memory (Storage Class Memory, SCM), a Solid State Disk (SSD), a NAND flash memory, a magnetic Disk, or the like. SCM is a common name for new storage medium between memory and flash memory, and is a composite storage technology combining persistent storage characteristic and memory characteristic, and has access speed slower than that of DRAM and SSD hard disk.

The processor may be a general-purpose processor including a central processing unit (Central Processing Unit, CPU), a network processor (Network Processor, NP), etc.; but also digital signal processors (Digital Signal Processing, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

It should be appreciated that embodiments of the present application may be implemented or realized by computer hardware, a combination of hardware and software, or by computer instructions stored in non-transitory (or referred to as non-persistent) memory. The method may be implemented in a computer program using standard programming techniques, including a non-transitory storage medium configured with a computer program, where the storage medium so configured causes a computer to operate in a specific and predefined manner. Each program may be implemented in a high level procedural or object oriented programming language to communicate with a computer system. However, the program(s) can be implemented in assembly or machine language, if desired. In any case, the language may be a compiled or interpreted language. Furthermore, the program can be run on a programmed application specific integrated circuit for this purpose. Furthermore, the operations of the processes described herein may be performed in any suitable order unless otherwise indicated herein or otherwise clearly contradicted by context. The processes (or variations and/or combinations thereof) described herein may be performed under control of one or more computer systems configured with executable instructions, and may be implemented as code (e.g., executable instructions, one or more computer programs, or one or more applications), by hardware, or combinations thereof, collectively executing on one or more processors. The computer program includes a plurality of instructions executable by one or more processors.

Further, the method may be implemented in any type of computing platform operatively connected to a suitable computing platform, including, but not limited to, a personal computer, mini-computer, mainframe, workstation, network or distributed computing environment, separate or integrated computer platform, or in communication with a charged particle tool or other imaging device, and so forth. Aspects of the present application may be implemented in machine-readable code stored on a non-transitory storage medium or device, whether removable or integrated into a computing platform, such as a hard disk, an optical read and/or write storage medium, RAM, ROM, etc., such that it is readable by a programmable computer, which when read by a computer, is operable to configure and operate the computer to perform the processes described herein. Further, the machine readable code, or portions thereof, may be transmitted over a wired or wireless network. When such media includes instructions or programs that, in conjunction with a microprocessor or other data processor, implement the steps described above, the invention described herein includes these and other different types of non-transitory computer-readable storage media. The present application also includes the computer itself when programmed according to the methods and techniques described herein.

The foregoing is merely exemplary of the present application and is not intended to limit the present application. Various modifications and changes may be made to the present application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc. that fall within the spirit and principles of the present application are intended to be included within the scope of the present application.

Claims

1. An index recommendation method, comprising:

generating a query tree of SQL sentences;

generating a second execution plan based on the created virtual index;

2. The method according to claim 1, wherein the method further comprises:

if the first execution plan uses the entity index, before the second execution plan is generated, removing the permutation and combination corresponding to the entity index from the created virtual index.

3. The method of claim 2, wherein the step of determining the position of the substrate comprises,

the method is applied to SQL commands of database checking execution plans, introduces the virtual index for the second execution plan by means of a registered hook function, and acquires the index used by the second execution plan.

4. The method of claim 2, wherein the step of determining the position of the substrate comprises,

and comparing the cost of the second execution plan with the cost of the first execution plan, outputting the recommendation index when the cost of the second execution plan is smaller than the cost of the first execution plan and the difference value is larger than a preset proportion, otherwise, not outputting the recommendation index.

5. An index recommendation device, comprising:

6. The apparatus of claim 5, wherein the device comprises a plurality of sensors,

the virtual index module is further configured to, in a case where the entity index is used by the first execution plan, reject, from the created virtual index, an permutation and combination corresponding to the entity index before generating a second execution plan.

7. The apparatus of claim 6, wherein the device comprises a plurality of sensors,

the device is applied to SQL commands of database checking execution plans, introduces the virtual index for the second execution plan by means of registering a hook function, and acquires the index used by the second execution plan.

8. The apparatus of claim 6, wherein the apparatus further comprises:

and the comparison module is used for comparing the spending of the second execution plan and the first execution plan, and when the spending of the second execution plan is smaller than the spending of the first execution plan and the difference value is larger than a preset proportion, the recommendation index output module is instructed to output the recommendation index, otherwise, the recommendation index is not output.

9. An electronic device is characterized by comprising a processor, a communication interface, a storage medium and a communication bus, wherein the processor, the communication interface and the storage medium are communicated with each other through the communication bus;

a storage medium storing a computer program;

a processor for carrying out the method steps of any one of claims 1-4 when executing a computer program stored on a storage medium.

10. A storage medium having stored thereon a computer program which, when executed by a processor, implements the method of any of claims 1 to 4.