A method and device for updating a non-unique index structure based on postgresql

Abstract

The application provides a non-unique index structure updating method and device based on PostgreSQL. The method comprises the following steps: obtaining a non-unique index, wherein the data organization structure of the non-unique index is a B+ tree structure, the leaf node in the B+ tree comprises a TID pointer structure pointing to a data tuple, the TID pointer structure is provided with a count value representing the number of repeated key values and an address list; and in response to an updating operation on the non-unique index, the count value and the address list are synchronously updated. The application optimizes the index structure under the premise of maintaining the B+ tree, combines the repeated key value rows in the leaf tuple, creates an address list for each group of key values, stores the physical addresses of each row of the repeated key values, saves the storage space of the non-unique index, and improves the scanning efficiency of the non-unique index.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database index, in particular to a method and device for updating non-unique index structure based on postgresql. BACKGROUND

[0002] PostgreSQL is the most advanced open source database, and is increasingly favored by database users. The current PostgreSQL non-unique index data structure uses B+ tree. In the database table data, if the value of the index column of the non-unique index is repeated, each repeated key value will occupy a leaf tuple in the index page, and these repeated key values are repeatedly stored in the index page, which brings great space waste, and also reduces the scanning efficiency of the non-unique index. SUMMARY

[0003] The technical problem to be solved by the present application is that if the value of the index column of the non-unique index is repeated, each repeated key value will occupy a leaf tuple in the index page, and these repeated key values are repeatedly stored in the index page, which brings great space waste, and also reduces the scanning efficiency of the non-unique index. In view of this, the present application provides a method and device for updating non-unique index structure based on postgresql, and an electronic device.

[0004] The technical solution adopted by the present application is that the method for updating non-unique index structure based on postgresql comprises:

[0005] Step S1, obtaining an index, the type of the index is a non-unique index, wherein the data organization structure of the non-unique index is a B+ tree structure, the leaf node in the B+ tree includes a TID pointer structure pointing to a data tuple, and the TID pointer structure is provided with a count value representing the number of repeated key values and an address list;

[0006] Step S2, in response to an update operation on the non-unique index, synchronously updating the count value and the address list.

[0007] In one embodiment, in response to the update operation on the non-unique index, synchronously updating the count value and the address list, comprising:

[0008] When the update operation is an addition operation, the count value is increased by one, and an element is added to the address list;

[0009] When the update operation is a deletion operation, the count value is reduced by one, and an element is deleted from the address list.

[0010] In one embodiment, the method further comprises:

[0011] When the type of the newly created index is a non-unique index, the TID pointer structure is configured in the leaf node of the current index structure, so that the TID pointer structure includes a count value representing the number of duplicate key values and an address list.

[0012] In one embodiment, the count value is equal to the number of elements in the address list.

[0013] Another aspect of the present application also provides an updating device for a non-unique index structure based on postgresql, comprising:

[0014] An obtaining unit configured to obtain an index, the type of the index being a non-unique index, wherein the data organization structure of the non-unique index is a B+ tree structure, the leaf node in the B+ tree including a TID pointer structure pointing to a data tuple, and the TID pointer structure being provided with a count value representing the number of duplicate key values and an address list;

[0015] An updating unit configured to, in response to an updating operation on the non-unique index, synchronously update the count value and the address list.

[0016] In one embodiment, the updating unit is further configured to:

[0017] When the updating operation is an adding operation, the count value is increased by one, and an element is added to the address list;

[0018] When the updating operation is a deleting operation, the count value is decreased by one, and an element is deleted from the address list.

[0019] In one embodiment, the device further comprises:

[0020] A creating unit configured to, when the type of the newly created index is a non-unique index, configure the TID pointer structure in the leaf node of the current index structure, so that the TID pointer structure includes a count value representing the number of duplicate key values and an address list.

[0021] In one embodiment, the count value is equal to the number of elements in the address list.

[0022] Another aspect of the present application also provides an electronic device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of the method for updating a non-unique index structure based on PostgreSQL according to any one of the preceding aspects.

[0023] Another aspect of the present application also provides a computer storage medium, wherein a computer program is stored on the computer storage medium, and the computer program, when executed by a processor, implements the steps of the method for updating a non-unique index structure based on PostgreSQL according to any one of the preceding aspects.

[0024] With the above technical solution, the present application has at least the following advantages:

[0025] The method for updating a non-unique index structure based on PostgreSQL provided by the present application optimizes the index structure while keeping the data structure of the non-unique index of PostgreSQL using a B+ tree, merges the repeated key value rows in the leaf tuples, creates an address list for each group of key values, and stores the physical address of each row of the repeated key values. In this way, the storage space of the non-unique index is greatly saved, and the scanning efficiency of the non-unique index is improved. BRIEF DESCRIPTION OF DRAWINGS

[0026] Figure 1 The flowchart of the method for updating a non-unique index structure based on PostgreSQL according to an embodiment of the present application is shown in FIG. 1.

[0027] Figure 2 The schematic diagram of the non-unique index structure according to an embodiment of the present application is shown in FIG. 2.

[0028] Figure 3 The detailed flowchart of an actual application according to an embodiment of the present application is shown in FIG. 3.

[0029] Figure 4 The schematic diagram of the composition structure of the updating device for a non-unique index structure based on PostgreSQL according to an embodiment of the present application is shown in FIG. 4.

[0030] Figure 5 The schematic diagram of the composition of the electronic device according to an embodiment of the present application is shown in FIG. 5. DETAILED DESCRIPTION

[0031] In order to further illustrate the technical means and effects taken by the present application to achieve the predetermined purposes, the present application is described in detail below in combination with the drawings and preferred embodiments.

[0032] It should be understood that the words "comprise", "comprising", "includes", "including", "contain", "containing", "have", "having", "engage", "engaging", "characterized by" and the like are to be open-ended. As such, the terms "comprise", "comprising", "includes", "including", "contain", "containing", "have", "having", "characterized by" and the like are to be construed in an inclusive sense as opposed to an exclusive or exhaustive sense; that is to say, in the sense of "including, but not limited to". Additionally, it should be understood that references to "one" or "the" item or steps means "one or more", "at least one", or "one or more than one", unless otherwise clear from the context.

[0033] As used herein, the terms "substantially", "approximately", and similar terms are utilized by way of the term approximately that means nearly correct, within a close range of values, within acceptable limits, or with minor deviations.

[0034] Unless otherwise defined, all terms (including technical and scientific terms) used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. It will be further understood that terms, such as those defined in commonly used dictionaries, should be interpreted as having a meaning that is consistent with their meaning in the context of the relevant art and will not be interpreted in an overly literal or overly formal sense unless expressly so defined herein.

[0035] It should be noted that the embodiments and features of the embodiments in the present application can be combined with each other, if not conflicting. The description of the method flow in the specification of the present application and the steps of the flow chart in the drawings of the present application are not necessarily executed in strict accordance with the step number, and the method steps can change the execution order. Moreover, some steps can be omitted, a plurality of steps can be combined into one step, and / or one step can be divided into a plurality of steps.

[0036] The description of the method flow in the specification of the present application and the steps of the flow chart in the drawings of the present application are not necessarily executed in strict accordance with the step number, and the method steps can change the execution order. Moreover, some steps can be omitted, a plurality of steps can be combined into one step, and / or one step can be divided into a plurality of steps.

[0037] A first embodiment of the present application is a method for updating a non-unique index structure based on PostgreSQL, as shown in Figure 1 The specific steps include:

[0038] Step S1, an index is acquired, the type of the index is a non-unique index, wherein a data organization structure of the non-unique index is a B+ tree structure, leaf nodes in the B+ tree include a TID pointer structure pointing to a data tuple, and a count value representing a number of repeated key values and an address list are set in the TID pointer structure.

[0039] Step S2, in response to an update operation on the non-unique index, the count value and the address list are synchronously updated.

[0040] The method provided in the embodiment will be described in detail in steps. Figures 1 to 3 , the method provided in the embodiment will be described in detail in steps.

[0041] Step S1, an index is acquired, the type of the index is a non-unique index, wherein a data organization structure of the non-unique index is a B+ tree structure, leaf nodes in the B+ tree include a TID pointer structure pointing to a data tuple, and a count value representing a number of repeated key values and an address list are set in the TID pointer structure.

[0042] The embodiment continues to use the existing B+ tree as the index data organization structure, and reduces the space occupation of the non-unique index, improves the scanning efficiency of the non-unique index, and improves the efficiency of the statistical query by optimizing the structure of the index tuple leaf node.

[0043] Reference can be made to Figure 2 , wherein K represents a key value, p represents a pointer between index pages, and T represents a TID (a pointer structure pointing to a data tuple). The structure of the TID is {Key = xxx, count, TID = [(block = xxx, Offset = xxx), (block = xxx, Offset = xxx)...]}, the count value and the address list in the TID are newly added parts of the original non-unique index tuple structure in the embodiment. The newly added part mainly uses the address list of the TID to store the addresses of the repeated key values in the non-unique index, thereby saving the storage space, and uses the count value to store the number of repeated key values, thereby improving the efficiency of the statistical query.

[0044] Correspondingly, the count value is equal to the number of elements in the address list.

[0045] Step S2, in response to an update operation on the non-unique index, the count value and the address list are synchronously updated.

[0046] Specifically, the update operation can be one of adding and deleting.

[0047] Exemplarily, when the update operation is an adding operation, the count value is increased by one, and an element is added to the address list;

[0048] Correspondingly, when the update operation is a deletion operation, the count value is reduced by one, and the address list is deleted by one element.

[0049] It can be understood that the newly created non-unique index can also be subjected to corresponding structural optimization according to the configuration format of the TID in the present embodiment.

[0050] Specifically, when the type of the newly created index is a non-unique index, the TID pointer structure is configured in the leaf node of the current index structure, so that the TID pointer structure includes a count value representing the number of repeated key values and an address list.

[0051] In an application example, reference can be made to Figure 3 The obtained index can be subjected to type determination and subsequent optimization (update and creation) processing according to the following processing flow.

[0052] 1) When creating an index, determine whether to use the present structural optimization according to the index type. If it is not a non-unique index, the present structural optimization is not used, and the original processing logic is still followed. If it is a non-unique index, the present structural optimization is used.

[0053] 2) When updating an index, determine the processing logic according to the index type. If it is not a non-unique index, the original processing logic is still followed. If it is a non-unique index, proceed to the next step.

[0054] 3) If it is an addition operation, perform two-step operations: (A) increase the count value in the TID by 1; (B) add an element to the address list in the TID. If it is a deletion operation, proceed to the next step.

[0055] 4) If it is a deletion operation, perform two-step operations: (A) reduce the count value in the TID by 1; (B) delete an element from the address list in the TID.

[0056] Compared with the prior art, the present embodiment has at least the following advantages:

[0057] The present embodiment optimizes the structure of the index tuple in the non-unique index in postgresql from {Key=xxx, TID=(block=xxx, Offset=xxx)} to {Key=xxx, count, TID=[(block=xxx, Offset=xxx), (block=xxx, Offset=xxx)...]}, which can be used for:

[0058] 1) Reducing the space occupation of the postgresql non-unique index, avoiding storage waste, and improving storage utilization efficiency;

[0059] 2) improve the scanning efficiency of the non-unique index of postgresql;

[0060] 3) can improve the efficiency of statistical query using non-unique index of postgresql.

[0061] The second embodiment of the present application corresponds to the first embodiment, and the embodiment introduces an updating device based on the non-unique index structure of postgresql, as shown in the following formula (I): Figure 4 The updating device includes the following components:

[0062] The obtaining unit is configured to obtain an index, and the type of the index is a non-unique index, wherein the data organization structure of the non-unique index is a B+ tree structure, the leaf node in the B+ tree includes a TID pointer structure pointing to a data tuple, and the TID pointer structure is provided with a count value representing the number of repeated key values and an address list;

[0063] The updating unit is configured to, in response to an updating operation on the non-unique index, synchronously update the count value and the address list.

[0064] In one embodiment, the updating unit is further configured to:

[0065] When the updating operation is an adding operation, the count value is increased by one, and an element is added to the address list;

[0066] When the updating operation is a deleting operation, the count value is decreased by one, and an element is deleted from the address list.

[0067] In one embodiment, the device further includes:

[0068] The new unit is configured to, when the type of the newly created index is a non-unique index, configure the TID pointer structure in the leaf node of the current index structure, so that the TID pointer structure includes a count value representing the number of repeated key values and an address list.

[0069] In one embodiment, the count value is equal to the number of elements in the address list.

[0070] The third embodiment of the present application is an electronic device, as shown in the following formula (II): Figure 5 The electronic device can be understood as a physical device, including a processor and a memory storing instructions executable by the processor, and when the instructions are executed by the processor, the following operations are performed:

[0071] In step S1, an index is acquired, and the index is a non-unique index, wherein a data organization structure of the non-unique index is a B+ tree structure, leaf nodes in the B+ tree include a TID pointer structure pointing to a data tuple, and the TID pointer structure is provided with a count value representing a number of repeated key values and an address list;

[0072] In step S2, in response to an update operation on the non-unique index, the count value and the address list are synchronously updated.

[0073] In the fourth embodiment of the present application, the method for updating the non-unique index structure based on PostgreSQL is the same as that in the first, second or third embodiment, and the difference is that in engineering implementation, the fourth embodiment can be implemented by means of software and a general hardware platform, and of course, it can also be implemented by hardware, but in many cases, the former is a better implementation manner. Based on this understanding, the method of the present application can be embodied in the form of a computer software product stored in a storage medium (such as a ROM / RAM, a magnetic disk, an optical disk), and includes a plurality of instructions for causing a device to execute the method described in the embodiments of the present application.

[0074] Through the description of the specific embodiments, the technical means and effects taken by the present application to achieve the predetermined purposes can be more deeply and specifically understood. However, the accompanying drawings are provided for reference and illustration only, and are not used to limit the present application.

Claims

1. An update method based on a non-unique index structure in PostgreSQL, characterized in that, include: Obtain the index, wherein the index is a non-unique index, and the data organization structure of the non-unique index is a B+ tree structure. The leaf nodes of the B+ tree include TID pointer structures pointing to data tuples. The TID pointer structure is set with a count value representing the number of duplicate key values ​​and an address list. In response to the update operation on the non-unique index, the count value and the address list are updated synchronously. Specifically, in response to the update operation on the non-unique index, the count value and the address list are synchronously updated, including: When the update operation is a new operation, the count value is incremented by one, and a new element is added to the address list; When the update operation is a deletion operation, the count value is decremented by one, and an element is deleted from the address list.

2. The method for constructing a non-unique index structure based on PostgreSQL according to claim 1, characterized in that, The method further includes: When the newly created index is a non-unique index, the TID pointer structure is configured in the leaf node of the current index structure so that the TID pointer structure includes a count value representing the number of duplicate key values ​​and an address list.

3. The method for constructing a non-unique index structure based on PostgreSQL according to claim 1, characterized in that, The count value is equal to the number of elements in the address list.

4. An update device based on a non-unique index structure in PostgreSQL, characterized in that, include: The acquisition unit is configured to acquire an index, wherein the index is a non-unique index, and the data organization structure of the non-unique index is a B+ tree structure. The leaf nodes in the B+ tree include TID pointer structures pointing to data tuples. The TID pointer structure is set with a count value representing the number of duplicate key values ​​and an address list. The update unit is configured to synchronously update the count value and the address list in response to an update operation on the non-unique index; The update unit is further configured as follows: When the update operation is a new operation, the count value is incremented by one, and a new element is added to the address list; When the update operation is a deletion operation, the count value is decremented by one, and an element is deleted from the address list.

5. The update device based on a non-unique index structure of PostgreSQL according to claim 4, characterized in that, The device further includes: The newly created unit is configured to configure the TID pointer structure in the leaf node of the current index structure when the newly created index is a non-unique index, so that the TID pointer structure includes a count value representing the number of duplicate key values ​​and an address list.

6. The update device based on a non-unique index structure of PostgreSQL according to claim 4, characterized in that, In the acquisition unit: The count value is equal to the number of elements in the address list.

7. An electronic device, characterized in that, The electronic device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of an update method based on a non-unique index structure of PostgreSQL as described in any one of claims 1 to 3.

8. A computer storage medium, characterized in that, The computer storage medium stores a computer program, which, when executed by a processor, implements the steps of an update method based on a non-unique index structure of PostgreSQL as described in any one of claims 1 to 3.

Images