Method for optimizing database index, storage medium and device

Abstract

The application provides an optimization method of database index, a storage medium and equipment. The method comprises the following steps: obtaining a database operation; determining an index according to the database operation; searching for an index partition needing to perform the operation in a preset index system table according to the index, the preset index system table being used for recording the correspondence between the index and a self partition; and performing a corresponding operation according to the type of the database operation. Through the method, the index is partitioned in the database, the index partition needing to perform the operation is found according to the preset system table when the interface calls the database, the high-concurrency pressure is dispersed into each index partition, the concurrent access conflict of each index is reduced, the performance of the database is improved, and the index partition can be determined according to the information in the interface calling the database, so that the convenience of using the database is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to database technology, and in particular, to a database index optimization method, a storage medium and an apparatus. BACKGROUND

[0002] In the buffer management logic of a database, a reference count is usually used to determine how many people are currently using the buffer, and further to determine whether the buffer can be evicted when it is replaced.

[0003] When operating on a database, the data to be operated on is usually found according to an index, and the process of finding the data is generally a query operation based on a B-tree and its variants. Querying data is generally a process of descending layer by layer from a root node to branch nodes and leaf nodes. All read and write operations based on a B-tree need to access the buffer of the root node, and accessing the buffer of the root node requires adding / subtracting 1 from the reference count. In a high concurrency situation, the node is obviously likely to become a single-point bottleneck.

[0004] Furthermore, in a high-throughput situation, the lower nodes of the root node can also form a bottleneck point. SUMMARY

[0005] An object of the present application is to improve the performance of a database.

[0006] A further object of the present application is to reduce the concurrent access conflicts of each index.

[0007] A further object of the present application is to reasonably partition the indexes.

[0008] In particular, the present application provides a database index optimization method, comprising:

[0009] obtaining a database operation;

[0010] determining an index according to the database operation;

[0011] finding an index partition that needs to perform the operation according to the index in a preset index system table, the preset index system table being used to record the correspondence between the index and the index partition;

[0012] performing a corresponding operation according to the type of the database operation.

[0013] Optionally, the step of obtaining the database operation comprises:

[0014] obtaining an interface for calling the database;

[0015] determining whether the interface needs to create an index partition;

[0016] If yes, the number of index partitions and the range of the index partitions are determined according to the parameters in the interface;

[0017] The correspondence between the index and the index partitions is recorded in a preset index system table.

[0018] Optionally, the step of searching for the index partitions that need to perform the operation in the preset index system table according to the index comprises:

[0019] The restriction condition of the index is determined from the database operation;

[0020] The index partitions corresponding to the index are searched for in the preset index system table;

[0021] The index partitions are pruned according to the restriction condition to obtain the index partitions that need to perform the operation.

[0022] Optionally, the step of performing the corresponding operation according to the type of the database operation comprises:

[0023] A target index scan path corresponding to the database operation is created;

[0024] The corresponding operation is performed according to the target index scan path.

[0025] Optionally, the step of performing the corresponding operation according to the target index scan path comprises:

[0026] It is judged whether the database operation is a query operation;

[0027] If no, the index and the index partitions are updated according to the execution result after the target index scan path is executed.

[0028] Optionally, the step of updating the index according to the execution result comprises:

[0029] The value of the index column is obtained according to the execution result,

[0030] The corresponding index partition in the preset index system table is queried and updated according to the value of the index column.

[0031] Optionally, the step of creating the target index scan path corresponding to the database operation comprises:

[0032] It is judged whether the database operation only exists in a single index partition;

[0033] If only a single index partition exists, an index scan path corresponding to the single index partition is created as the target index scan path.

[0034] Optionally, the step of judging whether the database operation only exists in a single index partition further comprises:

[0035] In the case of multiple index partitions, the index scan paths corresponding to the index partitions are merged by a splicing node to generate a target index scan path.

[0036] According to another aspect of the present application, there is also provided a machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements any of the above-mentioned database index optimization methods.

[0037] According to yet another aspect of the present application, there is also provided a computer device comprising a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implements any of the above-mentioned database index optimization methods when executing the machine executable program.

[0038] The database index optimization method of the present application, after obtaining a database operation, determines the index needed according to the database operation; finds the corresponding index partition in the preset index system table according to the index, prunes the index partition according to the query restriction condition in the database operation, to obtain the index partition needed for operation; creates an index scan path corresponding to the database operation according to the above-mentioned index partition; executes the corresponding operation according to the index scan path, and judges whether the database operation is a query operation after the operation is completed; if not, updates the index according to the execution result after the other index scan paths are executed. Through this method, the high-concurrency access conflicts of each node can be reduced by using the index partition operation in high concurrency, thereby improving the performance of the database.

[0039] Further, the database index optimization method of the present application determines whether to start the index partition operation according to the interface of the database, and after determining to start, the number and range of the index partitions can be determined according to the parameters in the interface, and the relationship between the index and the partition index is recorded through the preset index system table. Through this method, the index is reasonably partitioned according to the actual demand.

[0040] The above and other objects, advantages and features of the present application will become more apparent from the following detailed description of some embodiments thereof, when taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0041] Some specific embodiments of the present application will be described in detail below with reference to the accompanying drawings, in an exemplary and non-limiting manner. The same reference numerals in the drawings denote the same or similar components or parts. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0042] Figure 1 is a flowchart of a database index optimization method according to an embodiment of the present application;

[0043] Figure 2 is an index partition flowchart of an optimization method of a database index according to an embodiment of the present application;

[0044] Figure 3 is a schematic diagram of a machine readable storage medium in an optimization method of a database index according to an embodiment of the present application; and

[0045] Figure 4 is a schematic diagram of a computer device in an optimization method of a database index according to an embodiment of the present application. DETAILED DESCRIPTION

[0046] Those skilled in the art should understand that the embodiments described below are only a part of the embodiments of the present application, and are not intended to limit the protection scope of the present application. Based on the embodiments provided by the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor shall fall within the protection scope of the present application.

[0047] For the node bottleneck caused by high concurrency, an optional method is to fix the buffer area of the root node and part of the intermediate nodes, so as to ensure that these buffer areas will not be swapped out, avoid the reference count operation of these buffer areas, and thus reduce the concurrent access conflict.

[0048] However, this method also has some problems. The fixed buffer area needs to be manually specified, and when these nodes need to be split, it needs to switch between fixed and non-fixed states. This operation requires relatively delicate logic control and needs to be locked, which may cause performance jitter.

[0049] To solve the above problems, the present method proposes to disperse the access of a root node to N root partitions, that is, to introduce the concept of table partitioning into the index, to split one index into N indexes, and the concurrent access conflict of each index is only 1 / N of the original.

[0050] Figure 1 is a flowchart of an optimization method of a database index according to an embodiment of the present application. The schematic flowchart includes:

[0051] In step S101, a database operation is obtained. Before this step, the database will first determine whether to create an index partition according to the parameters in the interface calling the database; after confirming that an index partition needs to be created, the database will determine the number of index partitions and the range of index partitions according to the parameters in the above interface, and record the correspondence between the index and the partition index in a pre-set system table after the index partition.

[0052] Step S102, determine the index according to the database operation. This step includes: determining whether the index can be used according to the query condition in the database operation obtained in step S101, and determining the index to be used in the case where the index can be used.

[0053] Step S103, find the index partition to be executed according to the index in the preset index system table. This step includes: determining the limit condition of the index from the database operation; then finding the index partition corresponding to the index from the preset index system table; pruning the index partition according to the obtained limit condition, thereby filtering out the index partition that does not need to be executed. It should be noted that the pruning operation in this step is a preferred execution mode that can improve the execution speed of the database, and the person skilled in the art can choose whether to perform the pruning operation according to the actual situation.

[0054] In order to more clearly embody the role of partition pruning, an example is provided as follows:

[0055] Select*from test where id=150;

[0056] When executing the above SQL statement, the execution case using the index partition but not using the partition pruning operation is as follows:

[0057] Append

[0058] ->Index Scan using idx_test_part1 on test

[0059] Index Cond:(id=150)

[0060] ->Index Scan using idx_test_part2 on test

[0061] Index Cond:(id=150)

[0062] ->Index Scan using idx_test_part3 on test

[0063] Index Cond:(id=150)

[0064] ->Index Scan using idx_test_part4 on test

[0065] Index Cond:(id=150)

[0066] The example after using the partition pruning operation is as follows:

[0067] Index Scan using idx_test_part2 on test

[0068] Index Cond:(id=150)

[0069] The database will find the index partition that needs to be executed according to the limit condition in the SQL statement and the range of the index partition.

[0070] In step S104, the corresponding operation is executed according to the type of the database operation. This step includes: judging whether there is only a single index partition for the database operation; if there is only a single index partition, creating an index scan path corresponding to the single index partition as a target index scan path; in the case where there are multiple index partitions, merging the index scan paths corresponding to the index partitions through a splicing node to generate a target index scan path.

[0071] After the execution according to the target index scan path is completed, it is judged whether the operation is a query operation. If it is a query operation, it is indicated that there will be no impact on the index, and the operation is ended; if it is not a query operation, other operations such as addition, deletion and modification may have an impact on the index and its index partition, so the value of the corresponding index column is obtained according to the execution result, and then the information of the index partition in the preset index system table is updated according to the value of the index column.

[0072] Through the method, the index is partitioned in the database, so that the index partition that needs to be executed is found according to the preset system table when the database is called through an interface, so that the high-concurrency pressure is dispersed to each index partition, so as to improve the performance of the database, and the index partition can be determined by itself according to the information in the interface calling the database, improving the convenience of using the database.

[0073] Figure 2 It is an index partition flowchart of the optimization method of the database index according to an embodiment of the application. The schematic diagram includes:

[0074] In step S201, an interface calling a database is obtained.

[0075] In step S202, it is judged whether to create an index partition. This step includes: judging whether to create an index partition according to the parameters in the interface. For example: when a certain setting attribute in the interface is true or an instruction to create an index partition appears, it is determined to create an index partition.

[0076] In step S203, in the case where it is judged to be yes in step S202, the number and range of the index partition are determined according to the parameters in the interface. This step includes: determining the number and range of the index partitions to be partitioned in the database according to the values of other setting attributes in the interface.

[0077] In step S204, the correspondence between the index and the index partition is recorded in a preset system table.

[0078] According to the method, the database can generate different index partitions according to the actual requirements of the calling interface, and the convenience of using the index partition of the database is improved.

[0079] In order to more clearly illustrate the method, a specific example of the preset system table is shown in Table 1:

[0080] Table 1

[0081] index_name index_oid parent_index_oid range idx_test 10010 0 idx_test_part1 10011 10010 ( negative infinity, 100 ] idx_test_part2 10012 10010 (100,200] idx_test_part3 10013 10010 (200,300] idx_test_part4 10014 10010 ( 300, positive infinity ]

[0082] index_name represents the index name;

[0083] index_oid represents the index id;

[0084] parent_index_oid represents the parent index id of the index;

[0085] range represents the range of the index partition;

[0086] The correspondence between the index and the index partition is recorded in such a table.

[0087] It should be noted that the example shown in Table 1 is only an example of the correspondence, and a person skilled in the art can determine the format and content of the system table according to actual needs.

[0088] The embodiment also provides a machine readable storage medium and a computer device. Figure 3 is a schematic diagram of a machine readable storage medium 301 according to an embodiment of the present application, Figure 4 is a schematic diagram of a computer device 403 according to an embodiment of the present application.

[0089] The machine readable storage medium 301 has a machine executable program 302 stored thereon, and the machine executable program 302 is executed by the processor to implement the database index optimization method of any of the above embodiments.

[0090] The computer device 403 can include a memory 401, a processor 402, and a machine executable program 302 stored on the memory 401 and running on the processor 402, and the processor 402 executes the machine executable program 302 to implement the database index optimization method of any of the above embodiments.

[0091] It should be noted that the logical and / or steps represented in the flowcharts or otherwise described herein, such as partitioning the index, can be embodied in any machine-readable medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor- containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions, or a combination thereof.

[0092] For the description of the present embodiments, the machine-readable storage medium 301 can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device or a combination thereof. More specific examples (a non-exhaustive list) of the machine-readable storage medium 301 include the following: an electrical connection having one or more wires (electrical apparatus), a portable computer diskette (magnetic apparatus), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber (optical apparatus), and a portable compact disc read-only memory (CDROM). Additionally, the machine-readable storage medium 301 can even be paper or another suitable medium upon which the program is printed, as the program can be electronically captured, for example, via an optical scanner, then compiled, interpreted, or otherwise processed, and stored in a computer memory in order to be executed.

[0093] It should be understood that portions of the application can be implemented in hardware, software, firmware, or combinations thereof. In the above-described embodiments, multiple steps or methods can be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.

[0094] The computer device 403 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 403 can be a cloud computing node. The computer device 403 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and the like, that perform particular tasks or implement particular abstract data types. The computer device 403 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules can be located in both local and remote computer system storage media including memory storage devices.

[0095] The computer device 403 can include a processor 402 adapted to execute instructions stored in a memory 401, which in operation provides temporary storage of instructions during execution by the processor 402. The processor 402 can be a single core processor, multi-core processor, computing cluster, or any number of other configurations. The memory 401 can include random access memory (RAM), read only memory (ROM), flash memory, or any other suitable memory systems.

[0096] The processor 402 can be connected through a system interconnect (e.g., a PCI, PCI-Express, or other interconnect) to an I / O interface adapted to connect the computer device 403 to one or more I / O devices (input / output devices). The I / O devices can include, for example, a keyboard and a pointing device, where the pointing device can include a touchpad or touchscreen, among others. The I / O devices can be built-in components of the computer device 403, or can be devices externally connected to the computer device.

[0097] The processor 402 can also be linked through the system interconnect to a display interface adapted to connect the computer device 403 to a display device. The display device can include a display screen that is a built-in component of the computer device 403. The display device can also include a computer monitor, television, or projector, among others, that is externally connected to the computer device 403. Further, a network interface controller (NIC) can be adapted to connect the computer device 403 to a network through the system interconnect. In some embodiments, the NIC can use any suitable interface or protocol (such as Internet Small Computer Systems Interface, among others) to transfer data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, among others. Remote devices can be connected to the computer device through the network.

[0098] The flow diagrams provided herein are not intended to indicate that the operations of the methods are to be executed in any particular order, or that all of the operations of the methods are re quired, in all circumstances, to achieve the purposes of the methods. Further, the methods can include additional operations. Additional changes can be made within the scope of the techniques provided in the methods.

[0099] To this end, those skilled in the art will realize that the application is not limited to the exemplary embodiments described herein, but can be practiced with determination by one skilled in the art using the teachings disclosed herein. Accordingly, the scope of the application is to be construed as encompassing all such modifications and alterations.

Claims

1. A method for optimizing database index, comprising: obtaining a database operation; determining an index according to the database operation; finding an index partition that needs to be operated according to the index in a preset index system table, wherein the preset index system table is used to record the correspondence between an index and an index partition; performing a corresponding operation according to the type of the database operation; the step of performing a corresponding operation according to the type of the database operation comprises: creating a target index scan path corresponding to the database operation; and performing a corresponding operation according to the target index scan path; the step of creating a target index scan path corresponding to the database operation comprises: judging whether there is only a single index partition for the database operation; and if there is only a single index partition, creating an index scan path corresponding to the single index partition as the target index scan path; after the step of judging whether there is only a single index partition for the database operation, the method further comprises: in the case where there are multiple index partitions, merging the index scan paths corresponding to the index partitions by splicing nodes to generate the target index scan path.

2. The method for optimizing database index according to claim 1, wherein, the step of obtaining a database operation comprises: obtaining an interface for calling the database; judging whether the interface needs to create an index partition; if yes, determining the number of index partitions and the range of the index partitions according to the parameters in the interface; recording the correspondence between the index and the index partition in a preset index system table.

3. The method for optimizing database index according to claim 1, wherein, the step of finding an index partition that needs to be operated according to the index in a preset index system table comprises: determining a limit condition of the index from the database operation; finding the index partition corresponding to the index from the preset index system table; pruning the index partition according to the limit condition to obtain an index partition that needs to be operated.

4. The method for optimizing database index according to claim 1, wherein, after the step of performing a corresponding operation according to the target index scan path, the method further comprises: judging whether the database operation is a query operation; if no, updating the index and its index partition according to the execution result after the target index scan path is executed.

5. The method for optimizing database index according to claim 4, wherein, the step of updating the index according to the execution result comprises: obtaining the value of an index column according to the execution result, querying and updating the corresponding index partition in the preset index system table according to the value of the index column.

6. A machine readable storage medium having stored thereon a machine executable program, wherein the machine executable program is executed by a processor to implement the method for optimizing database index according to any one of claims 1 to 5.

7. A computer device comprising a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implements the method for optimizing database index according to any one of claims 1 to 5 when executing the machine executable program.

Images