Database statistical information processing method, device, computer equipment and storage medium

Abstract

The embodiment of the present application provides a method, device, computer equipment and storage medium for processing database statistical information. The method includes obtaining the to-be-updated statistical information of the query statement when the query statement is executed; determining the target partition corresponding to the query statement in the shared memory according to the query statement; writing the to-be-updated statistical information of the query statement into the target partition based on the partition lock of the target partition, and the partition lock is used to control the access rights to the partition of the shared memory. Compared with storing the statistical information in a file, storing the statistical information in the shared memory allows all processes to directly read the statistical information in the shared memory, reducing the time overhead of the disk I / O operation; and subdividing the shared memory into multiple partitions, combined with the partition lock, can reduce lock competition and waiting time, and improve data processing efficiency.

Description

Technical Field

[0001] The present application relates to the field of databases, and in particular to a method, device, computer equipment and storage medium for processing database statistical information. Background Art

[0002] A large amount of statistical information is generated during the operation of the database. They are the basis for efficient query optimization of the database management system (DBMS) and also affect the overall performance and stability of the database. Statistical information is stored in disk files. When other processes obtain statistical information, they need to read the statistical information through files. This method will cause IO problems and performance bottlenecks when the amount of statistical information is large. Summary of the invention

[0003] The embodiments of the present application provide a method, apparatus, computer device and storage medium for processing database statistical information, so as to achieve the effect of improving data processing efficiency.

[0004] In a first aspect, an embodiment of the present application provides a method for processing database statistical information, including:

[0005] When the query statement is monitored for execution, obtain the query statement's statistics to be updated;

[0006] According to the query statement, determine the target partition corresponding to the query statement in the shared memory;

[0007] Based on the partition lock of the target partition, the statistics to be updated of the query statement are written into the target partition. The partition lock is used to control the access rights to the partition of the shared memory.

[0008] In a possible implementation, determining, according to the query statement, a target partition corresponding to the query statement in the shared memory includes:

[0009] Calculate the hash value corresponding to the query statement;

[0010] Compare the hash value with the hash value range corresponding to each partition in the shared memory to determine the target hash value range in which the hash value is located, and the hash value range corresponding to each partition in the shared memory does not overlap;

[0011] The partition corresponding to the target hash value range is determined as the target partition corresponding to the query statement.

[0012] In a possible implementation, based on the partition lock of the target partition, the to-be-updated statistical information of the query statement is written into the target partition, including:

[0013] Request a shared lock for the target partition. If the shared lock for the target partition is obtained, determine whether the query statement exists in the target partition.

[0014] If the query statement exists in the target partition, request the spin lock of the target partition, and if the spin lock of the target partition is obtained, update the statistics to be updated to the target partition;

[0015] If the query statement does not exist in the target partition, an exclusive lock of the target partition is requested. If the exclusive lock of the target partition is obtained, the statistical information to be updated is inserted into the target partition.

[0016] In a possible implementation, the method further includes:

[0017] When receiving a deletion instruction, determining a target deletion partition according to the deletion instruction;

[0018] Request an exclusive lock for the target deleted partition. If the exclusive lock is obtained, delete the statistics of all query statements in the target deleted partition.

[0019] In a possible implementation, the method further includes:

[0020] When it is detected that the number of all statistical information in the shared memory is greater than the preset threshold, the shared locks of all partitions are requested in sequence according to the sorting of all partitions;

[0021] After obtaining the shared locks of all partitions, determine the target merge information in all partitions, and release the shared locks of all partitions in reverse order;

[0022] According to the sorting of all partitions, exclusive locks of all partitions are requested in sequence. When the exclusive lock of each partition is obtained, the target merged data in each partition is processed, and the exclusive lock of each partition is released in reverse order.

[0023] In a possible implementation, the statistical information includes at least one of the number of queries and the last query timestamp; determining the target merge information in all partitions includes:

[0024] Sort all statistical information according to the query count or the last query timestamp in the statistical information to obtain the sorting result;

[0025] According to the sorting result, a preset number of statistical information with the least query times is determined as the target merge information, or a preset number of statistical information with the earliest last query timestamp is determined as the target merge information.

[0026] In a possible implementation, the method further includes:

[0027] When receiving a query instruction, determining a target query partition according to the query instruction;

[0028] An exclusive lock of the target query partition is requested. When the exclusive lock of the target query partition is obtained, the statistical information of all query statements in the target query partition is read, and the statistical information of all query statements in the target query partition is sent to the issuer of the query instruction.

[0029] In a second aspect, an embodiment of the present application provides a database statistical information processing device, the device comprising:

[0030] The information acquisition module is used to acquire the to-be-updated statistical information of the query statement when the query statement execution is monitored;

[0031] A partition determination module is used to determine the target partition corresponding to the query statement in the shared memory according to the query statement;

[0032] The update module is used to write the to-be-updated statistical information of the query statement into the target partition based on the partition lock of the target partition. The partition lock is used to control the access rights to the partition of the shared memory.

[0033] In a third aspect, an embodiment of the present application provides a computer device, including: a memory, a processor;

[0034] Memory stores computer-executable instructions;

[0035] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the above first aspect and / or various possible implementations of the first aspect.

[0036] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0037] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0038] The database statistical information processing method, device, computer equipment and storage medium provided by the embodiment of the present application include obtaining the to-be-updated statistical information of the query statement when the execution of the query statement is monitored; determining the target partition corresponding to the query statement in the shared memory according to the query statement; writing the to-be-updated statistical information of the query statement into the target partition based on the partition lock of the target partition, and the partition lock is used to control the access rights to the partition of the shared memory. Compared with storing the statistical information in a file, storing the statistical information in the shared memory allows all processes to directly read the statistical information in the shared memory, reducing the time overhead of the disk I / O operation. In addition, subdividing the shared memory into multiple partitions, combined with the partition lock, can reduce lock contention and waiting time, and improve data processing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The accompanying drawings herein, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0040] Figure 1 A schematic diagram of a statistical information generation and storage process provided in one embodiment;

[0041] Figure 2 A schematic diagram of a process flow of a method for processing database statistical information provided in one embodiment;

[0042] Figure 3 A schematic diagram of a flow chart of a method for processing database statistical information provided in another embodiment;

[0043] Figure 4 A schematic diagram of a flow chart of a method for processing database statistical information provided in yet another embodiment;

[0044] Figure 5 A schematic diagram of the structure of a database statistical information processing device provided in one embodiment;

[0045] Figure 6 The figure is a diagram of the internal structure of a computer device provided in one embodiment.

[0046] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0047] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0048] In the description of this application, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of the features. In the description of this application, the meaning of "plurality" is two or more, unless otherwise clearly and specifically defined.

[0049] First, let’s explain the nouns that appear in this article:

[0050] Archiver Stats: refers to the statistical information related to the archiving process in the PostgreSQL database.

[0051] DBEntry: In a database, it usually refers to a database entry or record. It is the basic unit in a database table for storing data. Each DBEntry contains a series of fields that define the specific content of the record.

[0052] SQL Wait: refers to the time a thread needs to wait when performing certain operations in database operations.

[0053] RAC (Real Application Clusters) environment is a high availability and scalability solution for Oracle database. RAC allows multiple server nodes to access and operate the same database through shared storage and network interconnection. In this architecture, each node runs a database instance, and these instances share data and communicate through high-speed network connections and shared storage.

[0054] The statistics module is a subsystem that supports the collection and reporting of information about server activity. It has the following features: It can count accesses to tables and indexes, both by disk block and by individual row. It also tracks the total number of rows in each table, and information about cleanup and analysis actions for each table. It also counts the number of calls to user-defined functions and the total time spent in each call. It also supports reporting dynamic information about what the system is doing, such as commands currently being executed by other server processes and what other connections exist in the system.

[0055] The statistics module uses the local UDP protocol for information transmission. Each backend process sends a UDP message to the statistics process. A data packet is a maximum of 968 bytes of data, including a 16-byte header and 952 bytes of valid data. The statistics process will process the received UDP packets in sequence and continue to listen to the port after completing the task. Figure 1 As shown in the figure, the Stats Collector stores the statistical information in the Stats file, and other statistical processes read the information through the file. In the RAC environment, each node has its own statistical process and its own statistical data. The statistical data do not cross nodes and are not interoperable.

[0056] In the above solution, it takes about 30 seconds to obtain the number of queries per second when there are 20 million pieces of statistical information; it takes 15 minutes to generate a KWR snapshot; and in an environment where the number of tables reaches 110,000 (including 40,000 toast tables) and the number of indexes reaches 240,000, it takes eight hours to vacuum a table. The above three problems are all caused by reading statistical data through files, which may cause IO problems and performance bottlenecks.

[0057] In view of the above technical problems, the embodiment of the present application provides a method for processing database statistical information, which abandons the use of files as an intermediate layer for data transmission, places the statistical information in the shared memory, and directly obtains all the statistical information from the shared memory when each process needs to read the latest statistical information. And by accurately using partition locks, the performance problems caused by the update operation are reduced.

[0058] Specifically, in one embodiment, a method for processing database statistical information is provided, such as Figure 2 As shown, including:

[0059] Step 202, when the query statement is monitored to be executed, obtain the to-be-updated statistical information of the query statement;

[0060] Step 204, according to the query statement, determining the target partition corresponding to the query statement in the shared memory;

[0061] Step 206: based on the partition lock of the target partition, write the to-be-updated statistical information of the query statement into the target partition. The partition lock is used to control access rights to the partition of the shared memory.

[0062] Among them, the query statement refers to the SQL (Structured Query Language) statement that retrieves data from the database. When an SQL query statement is executed, the statistical module first records the unique identifier (SQLID) of the query and its text content (SQLTEXT). If the same SQL query statement has been executed multiple times in the past period of time, for example, it has been executed more than 5 times within 24 hours, then its corresponding statistical information will be cached. The statistical information to be updated refers to the statistical information related to the execution determined by the statistical module when the query statement is executed, which needs to be updated into the cache. By adding a statistical cache mechanism, statistical information can be read more efficiently.

[0063] Specifically, the background process will generate new simplified statistical data by parsing the SQLTEXT content and based on the specific information required for execution, and determine the statistical information to be updated based on the new simplified statistical data. The statistical information to be updated is stored in a hash table, and a record in the hash table corresponds to the statistical information of a query statement. For example, the KEY is the SQLID, the VALUE is the timestamp of the last access, the memory address of the simplified statistical data, and a lock.

[0064] The hash table is stored in the shared memory. According to the amount of data to be stored, the statistical information hash table in the shared memory is subdivided into multiple independent partitions, each partition is mapped to a set of specific SQL statements and their related statistical information, and corresponding lock resources are allocated to these multiple partitions. It should be noted that the number of independent partitions is 2n, where n is a positive integer, and the specific size is determined according to the number of statistical information. For example, in one embodiment, the shared memory is divided into 128 independent partitions.

[0065] The correspondence between partitions and query statements is one-to-one, that is, the statistical information of a query statement must be recorded in the corresponding partition. After obtaining the statistical information to be updated of the query statement, determine the target partition corresponding to the query statement, and write the statistical information to be updated into the target partition according to the partition lock of the partition. Among them, the partition lock includes shared lock and exclusive lock.

[0066] In the method provided by the above embodiment, when the execution of the query statement is monitored, the to-be-updated statistical information of the query statement is obtained; according to the query statement, the target partition corresponding to the query statement in the shared memory is determined; based on the partition lock of the target partition, the to-be-updated statistical information of the query statement is written into the target partition, and the partition lock is used to control the access rights to the partition of the shared memory. Compared with storing the statistical information in a file, storing the statistical information in the shared memory allows all processes to directly read the statistical information in the shared memory, reducing the time overhead of the disk I / O operation. In addition, subdividing the shared memory into multiple partitions and combining the partition lock can reduce lock contention and waiting time, thereby improving data processing efficiency.

[0067] In one embodiment, if Figure 3 As shown, according to the query statement, determining the target partition corresponding to the query statement in the shared memory includes:

[0068] Step 302, calculating the hash value corresponding to the query statement;

[0069] Step 304, compare the hash value with the hash value range corresponding to each partition in the shared memory to determine the target hash value range in which the hash value is located, and the hash value range corresponding to each partition in the shared memory does not overlap;

[0070] Step 306: determine the partition corresponding to the target hash value range as the target partition corresponding to the query statement.

[0071] A hash value is a method of mapping an input (such as a string) to a fixed-size number (usually an integer). A query statement is essentially a string, and the hash value corresponding to the query statement is calculated according to the set hash function. Multiple partitions of shared memory are mapped according to the hash value of the query statement when mapping to a specific set of query statements. Specifically, each partition of the shared memory corresponds to a hash value range, and the hash value ranges corresponding to all partitions do not overlap, to ensure that the hash value corresponding to each query statement only exists in the hash value range corresponding to one partition.

[0072] After calculating the hash value corresponding to the query statement, determine which partition's hash value range the hash value corresponding to the query statement is in, take the hash value range as the target hash value range, take the partition corresponding to the target hash value range as the target partition, and write the query statement's to-be-updated statistical information into the target partition.

[0073] It should be noted that multiple partitions of the shared memory can be sorted according to the size of the corresponding hash value range. When comparing the hash value corresponding to the query statement with the hash value range corresponding to each partition in the shared memory, the comparison can be made in sequence. After determining the target hash value range, it will no longer be compared with the hash value range of the subsequent partitions.

[0074] When there are multiple query statements, that is, when multiple statistical information to be updated is obtained, the multiple statistical information to be updated can be grouped according to the hash value corresponding to each query statement and the hash value range corresponding to each partition. One group corresponds to one target partition. According to the partition lock of the target partition, all updated statistical information in the corresponding group is written to the target partition, thereby realizing the insertion of batch data into shared memory.

[0075] In the method provided in the above embodiment, the corresponding target partition is uniquely determined by the hash value corresponding to the query statement. Each time the statistical information to be updated is processed, only an exclusive lock or a shared lock needs to be applied for the specific partition involved, which greatly reduces lock contention and waiting time.

[0076] In one embodiment, based on the partition lock of the target partition, the to-be-updated statistical information of the query statement is written into the target partition, including:

[0077] Request a shared lock for the target partition. If the shared lock for the target partition is obtained, determine whether the query statement exists in the target partition.

[0078] If the query statement exists in the target partition, request the spin lock of the target partition, and if the spin lock of the target partition is obtained, update the statistics to be updated to the target partition;

[0079] If the query statement does not exist in the target partition, an exclusive lock of the target partition is requested. If the exclusive lock of the target partition is obtained, the statistical information to be updated is inserted into the target partition.

[0080] Among them, the shared lock (S lock), also known as the read lock, allows multiple transactions or processes to read the same resource at the same time, but does not allow them to modify it. The exclusive lock, also known as the write lock, allows a transaction or process to exclusively access a data resource, preventing other transactions or processes from reading or modifying the resource at the same time. The spin lock is a busy wait lock, that is, when a thread tries to acquire a lock that is already held by another thread, it will keep looping to check whether the lock has been released without entering a blocked state.

[0081] When writing the to-be-updated statistical information of the query statement to the target partition, you need to first obtain the shared lock of the target partition, read the data in it, and determine whether the statistical information of the query statement exists in the target partition. If it exists, obtain the spin lock to update the to-be-updated statistical information to the target partition; if it does not exist, obtain the exclusive lock and insert the to-be-updated statistical information into the target partition.

[0082] It should be noted that in actual application scenarios, batch update statistics are usually processed, so there will be multiple statistics to be updated corresponding to the target partition. At this time, the above update process will be adjusted. The update process for all statistics to be updated corresponding to the same target partition is as follows: first apply for a shared lock for the target partition, find out whether the query statement corresponding to each statistical information to be updated is in the target partition, if it is already in the target partition, use the spin lock to update the data, if it is not in the hash table, first mark it as needing to be inserted, first process all the statistics to be updated corresponding to the target partition, release the shared lock, apply for an exclusive lock to start the insertion operation, if it has been inserted by other processes, update the data. If not, insert the data into the target partition.

[0083] In the method provided in the above embodiment, partition locks are used to reduce the performance impact caused by updates, so that efficient data processing capabilities can still be maintained in a high-concurrency environment.

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

[0085] When receiving a deletion instruction, determining a target deletion partition according to the deletion instruction;

[0086] Request an exclusive lock for the target deleted partition. If the exclusive lock is obtained, delete the statistics of all query statements in the target deleted partition.

[0087] The delete instruction is triggered when the statistical information in the hash table needs to be deleted. After being triggered, the target deletion partition to be deleted is determined, and the exclusive lock of the target deletion partition is requested to delete the information. When the target deletion partition is all partitions, the exclusive lock of all partitions will be added (that is, all partitions are locked), so as to ensure that the deletion operation will not be affected by other processes.

[0088] In one embodiment, if Figure 4 As shown, the method also includes:

[0089] Step 402: when it is detected that the amount of all statistical information in the shared memory is greater than a preset threshold, request shared locks of all partitions in sequence according to the order of all partitions;

[0090] Step 404, when the shared locks of all partitions are acquired, determine the target merge information in all partitions, and release the shared locks of all partitions in reverse order;

[0091] Step 406: According to the order of all partitions, exclusive locks of all partitions are requested in sequence. When the exclusive lock of each partition is acquired, the target merged data in each partition is processed, and the exclusive lock of each partition is released in reverse order.

[0092] The preset number is the upper limit of the set statistical information. Each statistical category has a predefined maximum number of information (maximum 1 million records) to limit the total amount of data in that category. When the actual number of records in any statistical category reaches the preset threshold, the background process is triggered to merge the data to reduce the amount of data stored in the memory and simplify the statistical information required for the query operation. The amount of data to be merged can be preset, such as 10%, 15%, etc.

[0093] In one embodiment, the statistical information includes at least one of the number of queries and the last query timestamp; all statistical information is sorted according to the number of queries or the last query timestamp in the statistical information to obtain a sorting result; based on the sorting result, a preset number of statistical information with the least number of queries is determined as the target merge information, or a preset number of statistical information with the earliest last query timestamp is determined as the target merge information.

[0094] During execution, the sort of all partitions obtains the shared locks of all partitions to sort and filter the data, and determine the target merge information, such as the 10% of statistical information with the least query times as the target merge information, or the 10% of statistical information that has not been used for the longest time according to the last query timestamp as the target merge information. Then release the shared lock in reverse order; and sequentially increase the exclusive lock, delete the target merge information in each partition, and after the operation is completed, release the exclusive lock in reverse order.

[0095] It should be noted that in addition to determining whether merging is required based on the number of all statistical information in shared memory, the background process can also periodically check (every hour) the last query timestamp and discard the data if it has been more than 24 hours.

[0096] In the method provided in the above embodiment, by setting the automatic merging mechanism, the background process performs data compression and optimization when triggered, thereby reducing the amount of data stored in the memory and simplifying the statistical information required for the query operation.

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

[0098] When receiving a query instruction, determining a target query partition according to the query instruction;

[0099] An exclusive lock of the target query partition is requested. When the exclusive lock of the target query partition is obtained, the statistical information of all query statements in the target query partition is read, and the statistical information of all query statements in the target query partition is sent to the issuer of the query instruction.

[0100] When executing a query operation, the query instruction determines the target query partition. Data reading is achieved based on the partition locks of the target query partition, such as shared locks and exclusive locks. When there are multiple target query partitions, it is necessary to apply for shared locks for multiple target query partitions in sequence. Since the statistical information of each query statement is independent and unique, the shared locks on each target query partition can be added sequentially during the query process, and the data of each of the multiple target query partitions can be read one by one.

[0101] When querying, each part of the data can be cached in a local hash table for subsequent operations, thereby reducing competition for shared resources and improving the system's response speed. This approach ensures that no additional lock contention is generated during the query process and that the entire data set can be scanned quickly.

[0102] In addition, if the reset command is triggered manually and all statistical information needs to be cleared, exclusive locks will also be added to all partitions to ensure data consistency and integrity.

[0103] In order to facilitate understanding of the scheme provided in the above embodiment, the following explanation is given: when an SQL query is executed, the statistics module first records the unique identifier (SQLID) of the query and its text content (SQLTEXT). If the same SQL statement has been executed more than 5 times in the past 24 hours, the corresponding statistical information will be cached. The background process will generate new simplified statistical data by parsing the SQLTEXT content and based on the specific information required for execution, and insert a new record in a hash table, the KEY is SQLID, the VALUE is the timestamp of the last access, the memory address of the simplified statistical data, and a lock. This hash table has the above two upper lines, for example, up to 5,000, and after reaching 5,000, the 10% of data that has not been used for the longest time will be discarded according to the last access timestamp sorting. The background process will also regularly check (every hour) the last access time, and discard the data if it has exceeded 24 hours.

[0104] When you need to query specific records of a statistical category, first look in the hash table based on the SQLID to see if there is a cached version of the entry; if so, update the last access time and return the data in the corresponding memory address; otherwise, obtain and process the original statistical information according to the traditional process.

[0105] The hash table generated above is stored in shared memory. Each entry in the hash table corresponds to the statistical information of a SQL statement. In a PL / SQL or when executing an explicit transaction, it is necessary to ensure the consistency of addition, deletion, query and modification. This solution subdivides the statistical information hash table stored in shared memory into 128 independent partitions. Each partition is mapped to a specific set of SQL statements and their related statistical data, and corresponding lock resources are allocated to these 128 partitions. Each time the hash table in shared memory is inserted or updated, multiple entries (N records) are involved, which in turn involves M (1≤M≤128) different partitions. Addition, deletion, query and modification of data in each partition must comply with the partition lock of each partition.

[0106] By adding a statistical cache mechanism, simplified statistical data and related information (such as the last access timestamp, memory address, etc.) are stored in the hash table to optimize query efficiency and reduce storage requirements. The system prioritizes checking the simplified version in the cache when executing a query, thereby increasing the speed of data reading. The storage mechanism of shared memory and partition locks can greatly improve system performance and efficiency while ensuring data consistency. By directly accessing statistical data from shared memory and accurately using partition locks to reduce the performance impact of updates, efficient data processing capabilities can still be maintained in a high-concurrency environment.

[0107] It should be understood that, although the steps in the flowcharts involved in the above embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0108] Based on the same inventive concept, an embodiment of the present application also provides a database statistical information processing device. The implementation solution for solving the problem provided by the device is similar to the implementation solution recorded in the above method. Therefore, the specific limitations in one or more database statistical information processing device embodiments provided below can be referred to the limitations on the database statistical information processing method above, and will not be repeated here.

[0109] In one embodiment, Figure 5 As shown, the database statistical information processing device includes an information acquisition module 501, a partition determination module 502 and an update module 503, wherein:

[0110] The information acquisition module 501 is used to acquire the to-be-updated statistical information of the query statement when the query statement execution is monitored;

[0111] A partition determination module 502 is used to determine a target partition corresponding to a query statement in a shared memory according to the query statement;

[0112] The updating module 503 is used to write the to-be-updated statistical information of the query statement into the target partition based on the partition lock of the target partition. The partition lock is used to control the access rights to the partition of the shared memory.

[0113] In one embodiment, the partition determination module 502 is further configured to:

[0114] Calculate the hash value corresponding to the query statement;

[0115] Compare the hash value with the hash value range corresponding to each partition in the shared memory to determine the target hash value range in which the hash value is located, and the hash value range corresponding to each partition in the shared memory does not overlap;

[0116] The partition corresponding to the target hash value range is determined as the target partition corresponding to the query statement.

[0117] In one embodiment, the update module 503 is further configured to:

[0118] Request a shared lock for the target partition. If the shared lock for the target partition is obtained, determine whether the query statement exists in the target partition.

[0119] If the query statement exists in the target partition, request the spin lock of the target partition, and if the spin lock of the target partition is obtained, update the statistics to be updated to the target partition;

[0120] If the query statement does not exist in the target partition, an exclusive lock of the target partition is requested. If the exclusive lock of the target partition is obtained, the statistical information to be updated is inserted into the target partition.

[0121] In one embodiment, the database statistical information processing device further includes a deletion module 504, which is used to:

[0122] When receiving a deletion instruction, determining a target deletion partition according to the deletion instruction;

[0123] Request an exclusive lock for the target deleted partition. If the exclusive lock is obtained, delete the statistics of all query statements in the target deleted partition.

[0124] In one embodiment, the database statistical information processing device further includes a merging module 505, which is used to:

[0125] When it is detected that the number of all statistical information in the shared memory is greater than the preset threshold, the shared locks of all partitions are requested in sequence according to the sorting of all partitions;

[0126] After obtaining the shared locks of all partitions, determine the target merge information in all partitions, and release the shared locks of all partitions in reverse order;

[0127] According to the sorting of all partitions, exclusive locks of all partitions are requested in sequence. When the exclusive lock of each partition is obtained, the target merged data in each partition is processed, and the exclusive lock of each partition is released in reverse order.

[0128] In one embodiment, the merging module 505 is further configured to:

[0129] Sort all statistical information according to the query count or the last query timestamp in the statistical information to obtain the sorting result;

[0130] According to the sorting result, a preset number of statistical information with the least query times is determined as the target merge information, or a preset number of statistical information with the earliest last query timestamp is determined as the target merge information.

[0131] In one embodiment, the database statistical information processing device further includes a query module 506, which is used to:

[0132] When receiving a query instruction, determining a target query partition according to the query instruction;

[0133] An exclusive lock of the target query partition is requested. When the exclusive lock of the target query partition is obtained, the statistical information of all query statements in the target query partition is read, and the statistical information of all query statements in the target query partition is sent to the issuer of the query instruction.

[0134] Each module in the above-mentioned database statistical information processing device can be implemented in whole or in part by software, hardware and a combination thereof. Each of the above-mentioned modules can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute the operations corresponding to each of the above modules.

[0135] The computer device provided in this embodiment can execute the method provided in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be described in detail here.

[0136] Figure 6 This is a schematic diagram of the structure of the computer device provided in this application. Figure 6As shown, the computer device 60 provided in this embodiment includes: at least one processor 601 and a memory 602. Optionally, the device 60 also includes a communication component 603. The processor 601, the memory 602 and the communication component 603 are connected via a bus 604.

[0137] In a specific implementation process, at least one processor 601 executes the computer execution instructions stored in the memory 602, so that at least one processor 601 executes the above method.

[0138] The specific implementation process of the processor 601 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0139] In the above embodiments, it should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the invention may be directly implemented as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0140] The memory may include a high-speed memory (Random Access Memory, RAM), and may also include a non-volatile memory (Non-volatile Memory, NVM), such as at least one disk memory.

[0141] The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.

[0142] The present application also provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0143] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0144] The above-mentioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The readable storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0145] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (Application Specific Integrated Circuits, referred to as: ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0146] The division of units is only a logical function division, and there may be other divisions in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interface, device or unit, which can be electrical, mechanical or other forms.

[0147] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple 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.

[0148] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0149] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0150] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0151] Finally, it should be noted that those skilled in the art will readily conceive of other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present invention, which follow the general principles of the present invention and include common knowledge or customary technical means in the art not disclosed by the present invention, are not limited to the precise structure described above and shown in the drawings, and may be modified and changed in various ways without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

Claims

1. A method for processing database statistical information, characterized in that: The method comprises: When the query statement is detected to be executed, the updated statistical information of the query statement is obtained; According to the query statement, determining a target partition corresponding to the query statement in the shared memory; Based on the partition lock of the target partition, the to-be-updated statistical information of the query statement is written into the target partition, and the partition lock is used to control access rights to the partition of the shared memory.

2. The method according to claim 1, characterized in that The step of determining, according to the query statement, a target partition corresponding to the query statement in the shared memory includes: Calculate the hash value corresponding to the query statement; Compare the hash value with a hash value range corresponding to each partition in the shared memory to determine a target hash value range in which the hash value is located, wherein the hash value range corresponding to each partition in the shared memory does not overlap; The partition corresponding to the target hash value range is determined as the target partition corresponding to the query statement.

3. The method according to claim 2, characterized in that The partition lock based on the target partition writes the to-be-updated statistical information of the query statement into the target partition, including: Requesting a shared lock for the target partition, and if the shared lock for the target partition is obtained, determining whether the query statement exists in the target partition; In the case where the query statement exists in the target partition, requesting a spin lock of the target partition, and in the case where the spin lock of the target partition is acquired, updating the to-be-updated statistical information into the target partition; In the case that the query statement does not exist in the target partition, an exclusive lock of the target partition is requested, and in the case that the exclusive lock of the target partition is acquired, the to-be-updated statistical information is inserted into the target partition.

4. The method according to claim 1, characterized in that: The method further comprises: In case of receiving a deletion instruction, determining a target deletion partition according to the deletion instruction; An exclusive lock of the target deletion partition is requested, and when the exclusive lock of the target deletion partition is acquired, statistical information of all query statements in the target deletion partition is deleted.

5. The method according to claim 4, characterized in that The method further comprises: When it is detected that the amount of all statistical information in the shared memory is greater than a preset threshold, requesting shared locks of all the partitions in sequence according to the order of all the partitions; When the shared locks of all the partitions are acquired, determine the target merge information in all the partitions, and release the shared locks of all the partitions in reverse order; According to the order of all the partitions, exclusive locks of all the partitions are requested in sequence. When the exclusive lock of each partition is acquired, the target merged data in each partition is processed, and the exclusive lock of each partition is released in reverse order.

6. The method according to claim 5, characterized in that The statistical information includes at least one of the query timestamp and the last query timestamp; the determining the target merge information in all the partitions includes: Sorting all the statistical information according to the query times or the last query timestamp in the statistical information to obtain a sorting result; According to the sorting result, a preset number of statistical information with the least query times is determined as the target merge information, or a preset number of statistical information with the earliest last query timestamp is determined as the target merge information.

7. The method according to claim 5, characterized in that The method further comprises: When receiving a query instruction, determining a target query partition according to the query instruction; Request an exclusive lock for the target query partition, and when the exclusive lock for the target query partition is obtained, read the statistical information of all query statements in the target query partition, and send the statistical information of all query statements in the target query partition to the issuer of the query instruction.

8. A database statistical information processing device, characterized in that: The device comprises: An information acquisition module, used to acquire the to-be-updated statistical information of the query statement when the query statement is monitored to be executed; A partition determination module, used to determine, according to the query statement, a target partition corresponding to the query statement in the shared memory; An update module is used to write the to-be-updated statistical information of the query statement into the target partition based on the partition lock of the target partition, wherein the partition lock is used to control access rights to the partition of the shared memory.

9. A computer device, characterized in that: include: Memory, processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

Images