CN118210825B - Database query performance optimization method and device, electronic equipment and storage medium - Google Patents
Database query performance optimization method and device, electronic equipment and storage medium Download PDFInfo
- Publication number
- CN118210825B CN118210825B CN202410634809.3A CN202410634809A CN118210825B CN 118210825 B CN118210825 B CN 118210825B CN 202410634809 A CN202410634809 A CN 202410634809A CN 118210825 B CN118210825 B CN 118210825B
- Authority
- CN
- China
- Prior art keywords
- buffer area
- index
- buffer
- sharing
- index data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 143
- 238000005457 optimization Methods 0.000 title claims abstract description 28
- 239000000872 buffer Substances 0.000 claims abstract description 533
- 230000008569 process Effects 0.000 claims abstract description 86
- 230000015654 memory Effects 0.000 claims abstract description 49
- 230000008030 elimination Effects 0.000 claims abstract description 39
- 238000003379 elimination reaction Methods 0.000 claims abstract description 39
- 238000013500 data storage Methods 0.000 claims description 9
- 238000004590 computer program Methods 0.000 claims description 6
- 238000003491 array Methods 0.000 claims description 4
- 238000010586 diagram Methods 0.000 description 9
- 230000006870 function Effects 0.000 description 7
- 238000012545 processing Methods 0.000 description 5
- 238000004891 communication Methods 0.000 description 3
- 230000008878 coupling Effects 0.000 description 3
- 238000010168 coupling process Methods 0.000 description 3
- 238000005859 coupling reaction Methods 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 230000007246 mechanism Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000005192 partition Methods 0.000 description 2
- 238000006467 substitution reaction Methods 0.000 description 2
- 230000001934 delay Effects 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000003780 insertion Methods 0.000 description 1
- 230000037431 insertion Effects 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000000737 periodic effect Effects 0.000 description 1
- 230000002035 prolonged effect Effects 0.000 description 1
- 238000010926 purge Methods 0.000 description 1
Abstract
The invention provides a database query performance optimization method, a device, an electronic device and a storage medium, wherein an index sharing buffer area is obtained by expanding PostgresSQL memory buffer areas, is used for index data application, is maintained independently of the sharing buffer area, and is considered to be read and accessed more frequently when index data is closer to a root node in the index retrieval process, so that the elimination strategy of the index sharing buffer area ensures that the lower the level of the index data is, the closer to the root node is when the index data is subjected to a swap-in and swap-out operation, the lower the elimination probability is, the less the index data is, the more difficult the index data is to be swapped out, a write-back process of the index sharing buffer area is established, the wakeup time interval of the write-back process is longer, the data quantity of index data which is revised by write-back once is less, the index data can be reserved in the index sharing buffer area for a longer time, the read consumption of the index data is reduced when query statement is executed, and thus the query performance can be greatly improved.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method and apparatus for optimizing query performance of a database, an electronic device, and a storage medium.
Background
PostgresSQL database is a universal database management system with open source, stability, high efficiency and wide application. PostgresSQL provide a powerful indexing function to optimize query performance. An index is a data structure that can help databases locate and access specific data quickly, thereby speeding up queries. By using an index structure, the query performance of PostgresSQL databases can be significantly improved.
The flow of reading the index data is shown in fig. 1, and whether the index data exists in the shared buffer area (Shared Buffers) is determined; if not, the index data is needed to be read from the disk to Shared Buffers, and the index data can be used; if so, the index data in the shared buffer may be used directly. The shared buffer area in PostgresSQL databases can hold both table data and index data and the storage space is limited. Therefore, when the space of the shared buffer area is insufficient, part of the stored data needs to be cleared, so that the required data can be continuously read from the disk. For the cleared data, when it is used again, it needs to be re-read from the disk into the shared buffer, i.e. a situation where the data is swapped in and out occurs, and a certain amount of performance loss occurs. In addition, the table data is larger in data size and more frequent in reading and writing than the index data, so that the index data is swapped in and swapped out more frequently in the shared buffer area, and a great amount of performance loss is caused by frequent swap in and swap out, which affects the execution performance of the query statement to a certain extent.
The index structure is mostly implemented in the form of a B-tree in Postgres, and the B-tree is a balanced multi-path search tree, can keep data orderly, can efficiently support operations of insertion, deletion, search and the like of data, and can start from a root node and traverse layer by layer until a leaf node when the search operation is performed, so that when the search operation is performed for a plurality of times, the lower the level is, the higher the probability of being used is for index data which is closer to the root node, and conversely, the higher the level is, the lower the probability of being used for index data which is closer to the leaf node is. Therefore, index data at a low level can be ensured to be more permanently reserved in the shared buffer area, and the execution performance of the query statement can be effectively improved.
In summary, how to optimize the database query performance is a technical problem that needs to be solved at present.
Disclosure of Invention
Accordingly, the present invention aims to provide a database query performance optimization method, apparatus, electronic device and storage medium, which can improve the reading efficiency of index data and alleviate the technical problem of the prior database query performance with much time consumption.
In a first aspect, an embodiment of the present invention provides a method for optimizing database query performance, including:
Expanding PostgresSQL the memory buffer area to obtain an index sharing buffer area, wherein the PostgresSQL memory buffer area includes: the index sharing buffer area is used for being applied for an index data table;
Determining a table type according to relkind fields of a table of an application buffer zone, and determining a target buffer zone according to the table type, wherein the target buffer zone comprises any one of the following: the minimum data storage unit in the target buffer area is a buffer area, and the shared buffer area and the index shared buffer area consist of a preset number of buffer areas;
If the target buffer area is the index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area;
If the idle array is empty, traversing all the buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with the reference count of more than 0, wherein the reference count is used for indicating the number of processes of the corresponding buffer which is used simultaneously;
If the number of the target buffer areas is greater than 1, determining the level of index data in each target buffer area, taking the target buffer area corresponding to the highest level as an elimination buffer area, and further clearing the index data in the elimination buffer area for the table of the application buffer area to use, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree;
Establishing a write-back process of the index sharing buffer area so that the index sharing buffer area can write back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data of each write-back of the write-back process of the index sharing buffer area is smaller than that of the modified data of each write-back process of the sharing buffer area.
Further, the method further comprises:
And if the idle array is not empty, taking the idle buffer stored in the idle array as a buffer to be used for the table of the application buffer.
Further, the method further comprises:
And if the number of the target buffer areas is equal to 1, taking the target buffer areas as elimination buffer areas, and further clearing index data in the elimination buffer areas for use by a table of the application buffer areas.
Further, the method further comprises:
and if the number of the target buffer areas is smaller than 1, reporting errors.
Further, expanding PostgresSQL the memory buffer area to obtain the index-sharing buffer area includes:
The length of BufferDescriptors arrays is increased so that the BufferDescriptors arrays contain the shared buffer region and the index shared buffer region.
Further, determining a table type according to relkind fields of a table of an application buffer, and determining a target buffer area according to the table type, including:
Determining the table type from relkind field of the table of the application buffer in BufferAlloc;
if the table type is an index data table type, the target buffer area is the index sharing buffer area;
And if the table type is not the index data table type, the target buffer area is the shared buffer area.
Further, the method further comprises:
And carrying out database query based on the index data in the index sharing buffer area.
In a second aspect, an embodiment of the present invention further provides a database query performance optimization apparatus, including:
An expansion unit, configured to expand PostgresSQL the memory buffer area to obtain an index sharing buffer area, where the PostgresSQL memory buffer area includes: the index sharing buffer area is used for being applied for an index data table;
A first determining unit, configured to determine a table type according to a relkind field of a table of an application buffer, and determine a target buffer area according to the table type, where the target buffer area includes any one of the following: the minimum data storage unit in the target buffer area is a buffer area, and the shared buffer area and the index shared buffer area consist of a preset number of buffer areas;
A second determining unit, configured to determine whether an idle array is empty if the target buffer area is the index shared buffer area, where the idle array is used to store an unused idle buffer area in the index shared buffer area;
A third determining unit, configured to traverse all buffers in the index sharing buffer area if the idle array is empty, determine a target buffer with a reference count of 0, and reduce a reference count of a buffer with a reference count greater than 0 by one, where the reference count is used to indicate the number of processes that the corresponding buffer has been used simultaneously;
A fourth determining unit, configured to determine, if the number of the target buffers is greater than 1, a level at which index data in each of the target buffers is located, and use a target buffer corresponding to a highest level as an elimination buffer, and further clear index data in the elimination buffer for use in the table of the application buffer, where a lower level at which index data is located indicates that the corresponding index data is closer to a root node of the search tree;
The establishing unit is used for establishing a write-back process of the index sharing buffer area so that the index sharing buffer area can write back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data written back each time of the write-back process of the index sharing buffer area is smaller than that of the modified data written back each time of the write-back process of the sharing buffer area.
In a third aspect, an embodiment of the present invention further provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor implements the steps of the method according to any one of the first aspects when the processor executes the computer program.
In a fourth aspect, embodiments of the present invention also provide a computer-readable storage medium storing machine-executable instructions which, when invoked and executed by a processor, cause the processor to perform the method of any one of the first aspects.
In an embodiment of the present invention, a database query performance optimization method is provided, including: expanding PostgresSQL the memory buffer area to obtain an index-sharing buffer area, wherein the PostgresSQL memory buffer area includes: the index sharing buffer area is used for index data application; determining a table type according to relkind fields of a table of an application buffer zone, and determining a target buffer zone according to the table type, wherein the target buffer zone comprises any one of the following: the system comprises a shared buffer area and an index shared buffer area, wherein the minimum data storage unit in a target buffer area is a buffer area, and the shared buffer area and the index shared buffer area are formed by a preset number of buffer areas; if the target buffer area is an index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area; if the idle array is empty, traversing all buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with the reference count greater than 0, wherein the reference count is used for indicating the number of processes of which the corresponding buffer is used simultaneously; if the number of the target buffer areas is greater than 1, determining the level of index data in each target buffer area, taking the target buffer area corresponding to the highest level as an elimination buffer area, and further clearing the index data in the elimination buffer area for the use of a table of an application buffer area, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree; and establishing a write-back process of the index sharing buffer area so that the index sharing buffer area writes back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data of each write-back of the write-back process of the index sharing buffer area is smaller than that of the modified data of each write-back process of the sharing buffer area. As can be seen from the above description, in the database query performance optimization method of the present invention, the index sharing buffer area is obtained by expanding PostgresSQL the memory buffer area, and is used for the application of the index data table, and is maintained independently of the sharing buffer area, considering that during the retrieval process of the index, the index data closer to the root node of the search tree is more frequently read and accessed, so the elimination strategy of the index sharing buffer area ensures that when the swap-in and swap-out operation is performed, the lower the level of the index data is, the closer to the root node of the search tree, the lower the probability of being eliminated, the less easily swapped out, otherwise, the higher the level of the index data is, The closer to the leaf node of the search tree, the higher the probability of being eliminated, the easier the index data to be swapped out, in addition, an independent write-back process of the index sharing buffer area is established, a wake-up frequency threshold value of the write-back process of the index sharing buffer area and the data volume of index data modified by single write-back are set, the wake-up time interval of the write-back process of the index sharing buffer area is longer, the data volume of the index data modified by single write-back is smaller, the index data can stay in the index sharing buffer area for a longer time, namely, the index data can stay in the index sharing buffer area more permanently, the reading consumption of the index data is reduced when query statement is executed (the reading consumption when the index data is read from a disk to the buffer area is reduced), therefore, the query performance can be greatly improved, and the technical problem that the conventional database query performance consumes much time is solved.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings that are needed in the description of the embodiments or the prior art will be briefly described, and it is obvious that the drawings in the description below are some embodiments of the present invention, and other drawings can be obtained according to the drawings without inventive effort for a person skilled in the art.
FIG. 1 is a diagram illustrating a conventional flow of index data reading and using;
FIG. 2 is a flowchart of a database query performance optimization method according to an embodiment of the present invention;
FIG. 3 is a schematic diagram of a PostgresSQL memory buffer according to an embodiment of the present invention;
FIG. 4 is a schematic diagram of a database query performance optimization device according to an embodiment of the present invention;
fig. 5 is a schematic diagram of an electronic device according to an embodiment of the present invention.
Detailed Description
The technical solutions of the present invention will be clearly and completely described in connection with the embodiments, and it is apparent that the described embodiments are some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
The existing database query performance is time-consuming.
Based on this, in the database query performance optimization method of the present invention, the index sharing buffer area is obtained by expanding PostgresSQL the memory buffer area, and is used for index data application, and is maintained independently of the sharing buffer area, considering that the index data which is closer to the root node is read and accessed more frequently in the index retrieval process, so the elimination strategy of the index sharing buffer area ensures that the lower the level of the index data is, the closer to the root node of the search tree is, the lower the elimination probability is, the less easy to be replaced out, and on the contrary, the higher the level of the index data is, the closer to the leaf node of the search tree is, the higher the elimination probability is, the easier to be replaced out, the method has the advantages that the write-back process of the independent index sharing buffer area is established, the wake-up frequency threshold value of the write-back process and the data quantity of index data modified by single write-back are set to be different from those of the shared buffer area, the wake-up time interval of the write-back process of the index sharing buffer area is longer, the data quantity of the index data modified by single write-back is smaller, the index data can stay in the index sharing buffer area for a longer time, namely the index data can stay in the index sharing buffer area more permanently, the reading consumption of the index data is reduced when query sentences are executed (the reading consumption of the index data from a disk to the buffer area is reduced), and therefore the query performance can be greatly improved.
For the sake of understanding the present embodiment, a method for optimizing database query performance disclosed in the present embodiment is first described in detail.
Embodiment one:
In accordance with an embodiment of the present invention, there is provided an embodiment of a database query performance optimization method, it being noted that the steps shown in the flowchart of the figures may be performed in a computer system, such as a set of computer executable instructions, and, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in an order other than that shown or described herein.
FIG. 2 is a flowchart of a database query performance optimization method according to an embodiment of the invention, as shown in FIG. 2, comprising the steps of:
In step S202, expanding PostgresSQL the memory buffer area to obtain an index-sharing buffer area, where PostgresSQL the memory buffer area includes: the index data table comprises a shared buffer area and an index shared buffer area, wherein the index shared buffer area is used for applying for an index data table;
Specifically, the shared buffer area is used for all relation applications except the index data table, such as a common table relation, a temporary table relation and the like. The shared buffer area is denoted Shared Buffers, the index shared buffer area is denoted Index Buffers, and the structure of the PostgresSQL memory buffer area is shown in fig. 3.
Step S204, determining a table type according to relkind fields of a table of an application buffer area, and determining a target buffer area according to the table type, wherein the target buffer area comprises any one of the following: the system comprises a shared buffer area and an index shared buffer area, wherein the minimum data storage unit in a target buffer area is a buffer area, and the shared buffer area and the index shared buffer area are formed by a preset number of buffer areas;
Step S206, if the target buffer area is an index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area;
Specifically, an index-sharing buffer area page elimination algorithm-IndexClockSweep algorithm (i.e., the process from step S206 to step S210) is established for the index-sharing buffer area elimination buffer area (specifically, the index data of the purge buffer area).
A free array is maintained and unused free buffers in the index-sharing buffer are stored in the free array.
When the target buffer area corresponding to the table of the application buffer area is the index sharing buffer area, determining whether the idle array is empty.
Step S208, if the idle array is empty, traversing all buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with a reference count of more than 0, wherein the reference count is used for indicating the number of processes of the corresponding buffer which are used simultaneously;
specifically, if the free array is empty, the elimination strategy of step S208 to step S210 is executed.
Step S210, if the number of the target buffers is greater than 1, determining the level of the index data in each target buffer, taking the target buffer corresponding to the highest level as an elimination buffer, and further clearing the index data in the elimination buffer for use in a table of the application buffer, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree;
In step S212, a write-back process of the index-sharing buffer area is established for the index-sharing buffer area to write back the modified index data in the buffer area to the disk, wherein a wake-up time interval of the write-back process of the index-sharing buffer area is longer than a wake-up time interval of the write-back process of the shared buffer area, and a data size of the modified index data of each write-back of the write-back process of the index-sharing buffer area is smaller than a data size of the modified data of each write-back process of the shared buffer area.
Specifically, a IndexBackgroundWriter process (i.e., a write-back process of the index-sharing buffer area) is established for the index-sharing buffer area to write out the modified index data (i.e., write back to disk).
Adding IndexBackgroundWriter processes, and periodically selecting a fixed number of modified data write-back disks from the shared buffer area by referring to a write-back process BackgroundWriter, backgroundWriter process arranged in the shared buffer area, compared with a write-back process BackgroundWriter in the shared buffer area, the following two modifications are made:
Increasing IndexBackgroundWriter the delay value of the periodic execution of the process;
Compared with the common table data, the scale of the index data table is much smaller, so that the index sharing buffer area is less likely to be fully occupied, the index data table can be ensured to be less likely to be swapped out by lengthening the time interval of the wakeup IndexBackgroundWriter process, and the stay time in the index sharing buffer area is longer.
The data size written out by IndexBackgroundWriter processes every round is reduced;
As the size of the index data table is smaller, the data size written out in each round is reduced, and the index data table can stay in the index sharing buffer area for a longer time.
In an embodiment of the present invention, a database query performance optimization method is provided, including: expanding PostgresSQL the memory buffer area to obtain an index-sharing buffer area, wherein the PostgresSQL memory buffer area includes: the index data table comprises a shared buffer area and an index shared buffer area, wherein the index shared buffer area is used for applying for an index data table; determining a table type according to relkind fields of a table of an application buffer zone, and determining a target buffer zone according to the table type, wherein the target buffer zone comprises any one of the following: the system comprises a shared buffer area and an index shared buffer area, wherein the minimum data storage unit in a target buffer area is a buffer area, and the shared buffer area and the index shared buffer area are formed by a preset number of buffer areas; if the target buffer area is an index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area; if the idle array is empty, traversing all buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with the reference count greater than 0, wherein the reference count is used for indicating the number of processes of which the corresponding buffer is used simultaneously; if the number of the target buffer areas is greater than 1, determining the level of index data in each target buffer area, taking the target buffer area corresponding to the highest level as an elimination buffer area, and further clearing the index data in the elimination buffer area for the use of a table of an application buffer area, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree; and establishing a write-back process of the index sharing buffer area so that the index sharing buffer area writes back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data of each write-back of the write-back process of the index sharing buffer area is smaller than that of the modified data of each write-back process of the sharing buffer area. As can be seen from the above description, in the database query performance optimization method of the present invention, the index sharing buffer area is obtained by expanding PostgresSQL the memory buffer area, and is used for the application of the index data table, and is maintained independently of the sharing buffer area, considering that during the retrieval process of the index, the index data closer to the root node of the search tree is more frequently read and accessed, so the elimination strategy of the index sharing buffer area ensures that when the swap-in and swap-out operation is performed, the lower the level of the index data is, the closer to the root node of the search tree, the lower the probability of being eliminated, the less easily swapped out, otherwise, the higher the level of the index data is, The closer to the leaf node of the search tree, the higher the probability of being eliminated, the easier the index data to be swapped out, in addition, an independent write-back process of the index sharing buffer area is established, a wake-up frequency threshold value of the write-back process of the index sharing buffer area and the data volume of index data modified by single write-back are set, the wake-up time interval of the write-back process of the index sharing buffer area is longer, the data volume of the index data modified by single write-back is smaller, the index data can stay in the index sharing buffer area for a longer time, namely, the index data can stay in the index sharing buffer area more permanently, the reading consumption of the index data is reduced when query statement is executed (the reading consumption when the index data is read from a disk to the buffer area is reduced), therefore, the query performance can be greatly improved, and the technical problem that the conventional database query performance consumes much time is solved.
The foregoing briefly describes the database query performance optimization method of the present invention, and the detailed description of the specific content involved therein follows.
In an alternative embodiment of the invention, the method comprises:
if the free array is not empty, the free buffer stored in the free array is used as a buffer to be used for the table of the application buffer.
In an alternative embodiment of the invention, the method comprises:
If the number of the target buffer areas is equal to 1, the target buffer areas are used as elimination buffer areas, and index data in the elimination buffer areas are cleared for use by a table of the application buffer areas;
If the number of the target buffer areas is less than 1, reporting errors.
The IndexClockSweep algorithm is further described in its entirety as follows:
maintaining an idle array, storing an unused idle buffer in the index sharing buffer area in the idle array, and directly returning the idle buffer for use (an index data table of an application buffer) if the idle array is not empty when the application buffer is applied; if the free array free buffer is used up, then the retirement policy is executed.
In this embodiment, the elimination policy executed by the index sharing buffer area specifically includes the following steps:
Traversing the index sharing buffer area to find a buffer area meeting the requirement, wherein the buffer area in the index sharing buffer area records a reference count for recording the number of processes which are used simultaneously by the current buffer area, traversing the whole index sharing buffer area, and finding a buffer area with the reference count of 0 as a target buffer area to be eliminated; if the reference count is greater than 0, then the buffer is not replaced, but the reference count is decremented by one; if there are multiple buffers meeting the requirement, it is determined that the buffer corresponds to the level of the index data, and the buffer with higher level is eliminated (i.e. the index data therein is cleared). Lower levels indicate that the current index data is closer to the root node; traversing all buffers in the index-sharing buffer area, if all buffers do not meet the requirement (i.e. the reference count of all buffers is not 0), reporting errors, and then, failing to find available buffers meeting the requirement.
In an alternative embodiment of the present invention, expanding PostgresSQL the memory buffer area to obtain the index-sharing buffer area specifically includes the following steps:
the length of the BufferDescriptors array is increased so that the BufferDescriptors array contains a shared buffer region and an index shared buffer region.
Specifically, the PostgresSQL database stores NBuffers buffers contained in the shared buffer area through the BufferDescriptors array, and in this embodiment, the length of the BufferDescriptors array is increased, so that the BufferDescriptors array contains NBuffers (16384 by default) + NindexBuffers (4 by default) buffers, where the first NBuffers buffers belong to the shared buffer area, and the last NIndexBuffers buffers belong to the index shared buffer area. The index shared buffer area is only used for index data table application, and the shared buffer area can be used for all relation applications except the index data table, such as common table relation, temporary table relation and the like.
In an alternative embodiment of the present invention, determining a table type according to relkind fields of a table of an application buffer, and determining a target buffer area according to the table type, specifically includes the following steps:
(1) Determining a table type from relkind field of the table of the application buffer in BufferAlloc;
(2) If the table type is the index data table type, the target buffer area is an index sharing buffer area;
(3) If the table type is not the index data table type, the target buffer area is the shared buffer area.
Specifically, the relkind field in the system table pg_class of PostgresSQL database may represent a table type, where 'r' represents a normal data table, 'I' represents an index data table, 'S' represents a sequence data table,'t' represents a TOAST table, 'v' represents a view data table,'m' represents a materialized view, 'c' represents a combined data table, 'f' represents an external table, 'p' represents a partition table, 'I' represents a partition index, and therefore when the entry function BufferAlloc of the application buffer determines the table type of the table of the application buffer, such as the table type is 'I' or 'I', a buffer meeting the requirement is selected in the index sharing buffer area for the application table.
In an alternative embodiment of the invention, the method further comprises:
database queries are performed based on the index data table in the index shared buffer.
The database query performance optimization method solves the problem that index data in a shared buffer area are frequently changed in and out, and ensures that the index data can stay in a memory for a long time so as to improve the query performance. Specifically, a part of buffer area is expanded in PostgresSQL memory buffer area to be called as index shared buffer area (Index Buffers), the part of buffer area is exclusively used by index data and is maintained independently of shared buffer area (Shared Buffers), when maintaining, the write-back process IndexBackgroundWriter of the index shared buffer area is added, and the write-back time of the index data is prolonged independently of the write-back mechanism of the shared buffer area; the index shared buffer area page elimination algorithm IndexClockSweep is added, which is different from the shared buffer area page elimination algorithm, and delays the replacement of index data which is closer to the root node.
The invention has the following advantages:
The number of times that the index data is changed in and out in the memory buffer area is reduced by increasing the index sharing buffer area which is exclusively used and independently maintained by the index data, so that the query performance is effectively improved; by designing a brand new index sharing buffer area elimination mechanism, the index data of which the hierarchy is closer to the root node is ensured to be more permanently reserved in the memory, and the reading time of the index data is reduced.
Embodiment two:
The embodiment of the invention also provides a database query performance optimization device which is mainly used for executing the database query performance optimization method provided in the first embodiment of the invention, and the database query performance optimization device provided in the embodiment of the invention is specifically introduced below.
FIG. 4 is a schematic diagram of a database query performance optimization apparatus according to an embodiment of the present invention, as shown in FIG. 4, the apparatus mainly includes: an expansion unit 10, a first determination unit 20, a second determination unit 30, a third determination unit 40, a fourth determination unit 50, and a setup unit 60, wherein:
An expansion unit, configured to expand PostgresSQL the memory buffer area to obtain an index sharing buffer area, where the PostgresSQL memory buffer area includes: the index data table comprises a shared buffer area and an index shared buffer area, wherein the index shared buffer area is used for applying for an index data table;
A first determining unit, configured to determine a table type according to a relkind field of a table of an application buffer, and determine a target buffer area according to the table type, where the target buffer area includes any one of the following: the system comprises a shared buffer area and an index shared buffer area, wherein the minimum data storage unit in a target buffer area is a buffer area, and the shared buffer area and the index shared buffer area are formed by a preset number of buffer areas;
A second determining unit, configured to determine whether an idle array is empty if the target buffer area is an index shared buffer area, where the idle array is used to store an unused idle buffer area in the index shared buffer area;
a third determining unit, configured to traverse all buffers in the index sharing buffer area if the idle array is empty, determine a target buffer with a reference count of 0, and decrease by one the reference count of a buffer with a reference count greater than 0, where the reference count is used to indicate the number of processes that the corresponding buffer has been used simultaneously;
A fourth determining unit, configured to determine, if the number of target buffers is greater than 1, a level at which index data in each target buffer is located, and use a target buffer corresponding to a highest level as an elimination buffer, and further clear index data in the elimination buffer for use in a table of application buffers, where a lower level at which index data is located indicates that the corresponding index data is closer to a root node of the search tree;
The establishing unit is used for establishing a write-back process of the index sharing buffer area so that the index sharing buffer area can write back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data written back each time of the write-back process of the index sharing buffer area is smaller than that of the modified data written back each time of the write-back process of the sharing buffer area.
In an embodiment of the present invention, there is provided a database query performance optimization apparatus, including: expanding PostgresSQL the memory buffer area to obtain an index-sharing buffer area, wherein the PostgresSQL memory buffer area includes: the index sharing buffer area is used for index data application; determining a table type according to relkind fields of a table of an application buffer zone, and determining a target buffer zone according to the table type, wherein the target buffer zone comprises any one of the following: the system comprises a shared buffer area and an index shared buffer area, wherein the minimum data storage unit in a target buffer area is a buffer area, and the shared buffer area and the index shared buffer area are formed by a preset number of buffer areas; if the target buffer area is an index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area; if the idle array is empty, traversing all buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with the reference count greater than 0, wherein the reference count is used for indicating the number of processes of which the corresponding buffer is used simultaneously; if the number of the target buffer areas is greater than 1, determining the level of index data in each target buffer area, taking the target buffer area corresponding to the highest level as an elimination buffer area, and further clearing the index data in the elimination buffer area for the use of a table of an application buffer area, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree; and establishing a write-back process of the index sharing buffer area so that the index sharing buffer area writes back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data of each write-back of the write-back process of the index sharing buffer area is smaller than that of the modified data of each write-back process of the sharing buffer area. As can be seen from the above description, in the database query performance optimization apparatus of the present invention, the index sharing buffer area is obtained by expanding PostgresSQL the memory buffer area, and is used for the application of the index data table, and is maintained independently of the sharing buffer area, considering that during the retrieval process of the index, the index data closer to the root node of the search tree is more frequently read and accessed, so the elimination policy of the index sharing buffer area ensures that when the swap-in and swap-out operation is performed, the lower the level of the index data is, the closer to the root node of the search tree, the lower the probability of being eliminated, the less easily swapped out, otherwise, the higher the level of the index data is, The closer to the leaf node of the search tree, the higher the probability of being eliminated, the easier the index data to be swapped out, in addition, an independent write-back process of the index sharing buffer area is established, a wake-up frequency threshold value of the write-back process of the index sharing buffer area and the data volume of index data modified by single write-back are set, the wake-up time interval of the write-back process of the index sharing buffer area is longer, the data volume of the index data modified by single write-back is smaller, the index data can stay in the index sharing buffer area for a longer time, namely, the index data can stay in the index sharing buffer area more permanently, the reading consumption of the index data is reduced when query statement is executed (the reading consumption when the index data is read from a disk to the buffer area is reduced), therefore, the query performance can be greatly improved, and the technical problem that the conventional database query performance consumes much time is solved.
Optionally, the device is further configured to: if the free array is not empty, the free buffer stored in the free array is used as a buffer to be used for the table of the application buffer.
Optionally, the device is further configured to: if the number of the target buffers is equal to 1, the target buffers are used as obsolete buffers, and index data in the obsolete buffers are cleared for use by a table of the application buffers.
Optionally, the device is further configured to: if the number of the target buffer areas is less than 1, reporting errors.
Optionally, the expansion unit is further configured to: the length of the BufferDescriptors array is increased so that the BufferDescriptors array contains a shared buffer region and an index shared buffer region.
Optionally, the first determining unit is further configured to: determining a table type from relkind field of the table of the application buffer in BufferAlloc; if the table type is the index data table type, the target buffer area is an index sharing buffer area; if the table type is not the index data table type, the target buffer area is the shared buffer area.
Optionally, the device is further configured to: database queries are performed based on index data in the index-sharing buffer.
The device provided by the embodiment of the present invention has the same implementation principle and technical effects as those of the foregoing method embodiment, and for the sake of brevity, reference may be made to the corresponding content in the foregoing method embodiment where the device embodiment is not mentioned.
As shown in fig. 5, an electronic device 600 provided in an embodiment of the present application includes: the system comprises a processor 601, a memory 602 and a bus, wherein the memory 602 stores machine-readable instructions executable by the processor 601, the processor 601 and the memory 602 communicate through the bus when the electronic device is running, and the processor 601 executes the machine-readable instructions to perform the steps of the database query performance optimization method as described above.
Specifically, the memory 602 and the processor 601 can be general-purpose memories and processors, which are not limited herein, and the database query performance optimization method can be executed when the processor 601 runs a computer program stored in the memory 602.
The processor 601 may be an integrated circuit chip with signal processing capabilities. In implementation, the steps of the above method may be performed by integrated logic circuits of hardware in the processor 601 or instructions in the form of software. The processor 601 may be a general-purpose processor, including a central processing unit (Central Processing Unit, abbreviated as CPU), a network processor (Network Processor, abbreviated as NP), and the like; but may also be a digital signal processor (DIGITAL SIGNAL Processing, DSP), application SPECIFIC INTEGRATED Circuit (ASIC), off-the-shelf Programmable gate array (Field-Programmable GATE ARRAY, FPGA) or other Programmable logic device, discrete gate or transistor logic device, discrete hardware components. The disclosed methods, steps, and logic blocks in the embodiments of the present application may be implemented or performed. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like. The steps of the method disclosed in connection with the embodiments of the present application may be embodied directly in the execution of a hardware decoding processor, or in the execution of a combination of hardware and software modules in a decoding processor. The software modules may be located in a random access memory, flash memory, read only memory, programmable read only memory, or electrically erasable programmable memory, registers, etc. as well known in the art. The storage medium is located in a memory 602, and the processor 601 reads information in the memory 602 and performs the steps of the above method in combination with its hardware.
Corresponding to the database query performance optimization method described above, the embodiments of the present application also provide a computer-readable storage medium storing machine-executable instructions that, when invoked and executed by a processor, cause the processor to perform the steps of the database query performance optimization method described above.
The database query performance optimizing device provided by the embodiment of the application can be specific hardware on equipment or software or firmware installed on the equipment and the like. The device provided by the embodiment of the present application has the same implementation principle and technical effects as those of the foregoing method embodiment, and for the sake of brevity, reference may be made to the corresponding content in the foregoing method embodiment where the device embodiment is not mentioned. It will be clear to those skilled in the art that, for convenience and brevity, the specific operation of the system, apparatus and unit described above may refer to the corresponding process in the above method embodiment, which is not described in detail herein.
In the embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other manners. The above-described apparatus embodiments are merely illustrative, for example, the division of the units is merely a logical function division, and there may be other manners of division in actual implementation, and for example, multiple units or components may be combined or integrated into another system, or some features may be omitted, or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be through some communication interface, device or unit indirect coupling or communication connection, which may be in electrical, mechanical or other form.
As another example, the flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of apparatus, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional unit in the embodiments provided in the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit.
The functions, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored in a computer-readable storage medium. Based on such understanding, the technical solution of the present application may be embodied essentially or in a part contributing to the prior art or in a part of the technical solution, in the form of a software product stored in a storage medium, comprising several instructions for causing an electronic device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the database query performance optimization method according to the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (Random Access Memory RAM), a magnetic disk, or an optical disk, etc., which can store program codes.
It should be noted that: like reference numerals and letters in the following figures denote like items, and thus once an item is defined in one figure, no further definition or explanation of it is required in the following figures, and furthermore, the terms "first," "second," "third," etc. are used merely to distinguish one description from another and are not to be construed as indicating or implying relative importance.
Finally, it should be noted that: the above examples are only specific embodiments of the present application, and are not intended to limit the scope of the present application, but it should be understood by those skilled in the art that the present application is not limited thereto, and that the present application is described in detail with reference to the foregoing examples: any person skilled in the art may modify or easily conceive of the technical solution described in the foregoing embodiments, or perform equivalent substitution of some of the technical features, while remaining within the technical scope of the present disclosure; such modifications, changes or substitutions do not depart from the spirit of the corresponding technical solutions. Are intended to be encompassed within the scope of the present application. Therefore, the protection scope of the application is subject to the protection scope of the claims.
Claims (10)
1. A method for optimizing database query performance, comprising:
Expanding PostgresSQL the memory buffer area to obtain an index sharing buffer area, wherein the PostgresSQL memory buffer area includes: the index sharing buffer area is used for being applied for an index data table;
Determining a table type according to relkind fields of a table of an application buffer zone, and determining a target buffer zone according to the table type, wherein the target buffer zone comprises any one of the following: the minimum data storage unit in the target buffer area is a buffer area, and the shared buffer area and the index shared buffer area consist of a preset number of buffer areas;
If the target buffer area is the index sharing buffer area, determining whether an idle array is empty, wherein the idle array is used for storing unused idle buffer areas in the index sharing buffer area;
If the idle array is empty, traversing all the buffers in the index sharing buffer area, determining a target buffer with a reference count of 0, and subtracting one from the reference count of a buffer with the reference count of more than 0, wherein the reference count is used for indicating the number of processes of the corresponding buffer which is used simultaneously;
If the number of the target buffer areas is greater than 1, determining the level of index data in each target buffer area, taking the target buffer area corresponding to the highest level as an elimination buffer area, and further clearing the index data in the elimination buffer area for the table of the application buffer area to use, wherein the lower the level of the index data is, the closer the corresponding index data is to the root node of the search tree;
Establishing a write-back process of the index sharing buffer area so that the index sharing buffer area can write back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data of each write-back of the write-back process of the index sharing buffer area is smaller than that of the modified data of each write-back process of the sharing buffer area.
2. The method according to claim 1, wherein the method further comprises:
And if the idle array is not empty, taking the idle buffer stored in the idle array as a buffer to be used for the table of the application buffer.
3. The method according to claim 1, wherein the method further comprises:
And if the number of the target buffer areas is equal to 1, taking the target buffer areas as elimination buffer areas, and further clearing index data in the elimination buffer areas for use by a table of the application buffer areas.
4. The method according to claim 1, wherein the method further comprises:
and if the number of the target buffer areas is smaller than 1, reporting errors.
5. The method of claim 1 wherein expanding PostgresSQL the memory buffer area to obtain the index-sharing buffer area comprises:
The length of BufferDescriptors arrays is increased so that the BufferDescriptors arrays contain the shared buffer region and the index shared buffer region.
6. The method of claim 1, wherein determining a table type from a relkind field of a table of an application buffer and determining a target buffer area from the table type comprises:
Determining the table type from relkind field of the table of the application buffer in BufferAlloc;
if the table type is an index data table type, the target buffer area is the index sharing buffer area;
And if the table type is not the index data table type, the target buffer area is the shared buffer area.
7. The method according to claim 1, wherein the method further comprises:
And carrying out database query based on the index data in the index sharing buffer area.
8. A database query performance optimization apparatus, comprising:
An expansion unit, configured to expand PostgresSQL the memory buffer area to obtain an index sharing buffer area, where the PostgresSQL memory buffer area includes: the index sharing buffer area is used for being applied for an index data table;
A first determining unit, configured to determine a table type according to a relkind field of a table of an application buffer, and determine a target buffer area according to the table type, where the target buffer area includes any one of the following: the minimum data storage unit in the target buffer area is a buffer area, and the shared buffer area and the index shared buffer area consist of a preset number of buffer areas;
A second determining unit, configured to determine whether an idle array is empty if the target buffer area is the index shared buffer area, where the idle array is used to store an unused idle buffer area in the index shared buffer area;
A third determining unit, configured to traverse all buffers in the index sharing buffer area if the idle array is empty, determine a target buffer with a reference count of 0, and reduce a reference count of a buffer with a reference count greater than 0 by one, where the reference count is used to indicate the number of processes that the corresponding buffer has been used simultaneously;
A fourth determining unit, configured to determine, if the number of the target buffers is greater than 1, a level at which index data in each of the target buffers is located, and use a target buffer corresponding to a highest level as an elimination buffer, and further clear index data in the elimination buffer for use in the table of the application buffer, where a lower level at which index data is located indicates that the corresponding index data is closer to a root node of the search tree;
The establishing unit is used for establishing a write-back process of the index sharing buffer area so that the index sharing buffer area can write back the modified index data in the buffer area to the disk, wherein the wake-up time interval of the write-back process of the index sharing buffer area is longer than that of the write-back process of the sharing buffer area, and the data size of the modified index data written back each time of the write-back process of the index sharing buffer area is smaller than that of the modified data written back each time of the write-back process of the sharing buffer area.
9. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the steps of the method of any of the preceding claims 1 to 7 when the computer program is executed.
10. A computer readable storage medium storing machine executable instructions which, when invoked and executed by a processor, cause the processor to perform the method of any one of the preceding claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410634809.3A CN118210825B (en) | 2024-05-22 | Database query performance optimization method and device, electronic equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410634809.3A CN118210825B (en) | 2024-05-22 | Database query performance optimization method and device, electronic equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN118210825A CN118210825A (en) | 2024-06-18 |
| CN118210825B true CN118210825B (en) | 2024-07-26 |
Family
ID=
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0606461A1 (en) * | 1992-07-24 | 1994-07-20 | Microsoft Corporation | Computer method and system for allocating and freeing memory |
| CN117370414A (en) * | 2023-09-14 | 2024-01-09 | 浙江智臾科技有限公司 | Application method and device of time sequence database |
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0606461A1 (en) * | 1992-07-24 | 1994-07-20 | Microsoft Corporation | Computer method and system for allocating and freeing memory |
| CN117370414A (en) * | 2023-09-14 | 2024-01-09 | 浙江智臾科技有限公司 | Application method and device of time sequence database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10303596B2 (en) | Read-write control method for memory, and corresponding memory and server | |
| JP5996088B2 (en) | Cryptographic hash database | |
| KR101467589B1 (en) | Dynamic fragment mapping | |
| US11314689B2 (en) | Method, apparatus, and computer program product for indexing a file | |
| CN103019887A (en) | Data backup method and device | |
| US9892004B2 (en) | Space efficient persistence of an in-memory table | |
| CN109407985B (en) | Data management method and related device | |
| CN106164874B (en) | Method and device for accessing data visitor directory in multi-core system | |
| US7502778B2 (en) | Apparatus, system, and method for efficient adaptive parallel data clustering for loading data into a table | |
| KR100907477B1 (en) | Apparatus and method for managing index of data stored in flash memory | |
| CN112612419B (en) | Data storage structure, storage method, reading method, device and medium of NVM (non-volatile memory) | |
| US7080206B2 (en) | System and method for adaptively loading input data into a multi-dimensional clustering table | |
| CN118210825B (en) | Database query performance optimization method and device, electronic equipment and storage medium | |
| CN108804571B (en) | Data storage method, device and equipment | |
| CN118210825A (en) | Database query performance optimization method and device, electronic equipment and storage medium | |
| CN112800057B (en) | Fingerprint table management method and device | |
| US7085888B2 (en) | Increasing memory locality of filesystem synchronization operations | |
| CN111880739A (en) | Near data processing system for super fusion equipment | |
| CN114296630A (en) | Updating deduplication fingerprint indexes in cache storage | |
| US9824105B2 (en) | Adaptive probabilistic indexing with skip lists | |
| CN111880900A (en) | Design method of near data processing system for super fusion equipment | |
| CN102479213A (en) | Data buffering method and device | |
| KR100982591B1 (en) | File system, main storage and flash storage for progressive indexing and data management method using the progressive indexing | |
| CN107844511B (en) | Game resource caching method and system based on cycle cost | |
| US20140095792A1 (en) | Cache control device and pipeline control method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant |