Method, system, device, and storage medium for improving data query efficiency and stability

Abstract

The present invention provides a method for improving the efficiency and stability of full-scale data queries in a distributed database, comprising: setting a timed task framework based on a distributed database; obtaining the data volume of each physical node of the distributed database as a variable value of a sample of each physical node; obtaining the average of the data volume of different physical nodes of the distributed database based on the variable value; determining the standard deviation of the degree of discreteness of data volume differences of samples of different physical nodes of the distributed database based on the variable value, the number of physical nodes, and the average of the data volume of different physical nodes; determining a standard score for calculating the degree of discreteness of each data volume of samples of different physical nodes of the distributed database based on the standard deviation; identifying the query efficiency and stability differences between physical nodes with different data volumes based on the standard score, and determining a decision-making mechanism; and determining the amount of data that needs to be queried and cached in advance based on the variable value and the standard score.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed database full data query performance optimization, and in particular to a method and system for improving the efficiency and stability of distributed database full data query. Background Art

[0002] Distributed database architectures are used in database storage architectures for various business scenarios, such as enterprise production data, financial data, and process data, due to their scalability, high availability, and high performance. Data is stored redundantly and cross-wise between physical nodes in distributed databases, meeting people's needs for data read performance and data security storage. As business volume grows year by year, the amount of data also grows accordingly. Due to the differences in data volume between different physical nodes in the distributed database and the large degree of data dispersion, full data query statements for a table in the entire distributed database, when routed to each physical node by the database middleware, also produce time differences in returning data. Under the premise that the physical characteristics and disk performance between physical nodes in the distributed database remain consistent, nodes with large data volumes take longer to return full data, thereby slowing down the time it takes for the middleware nodes to summarize and return the entire full data to the user, resulting in a bottleneck in the stability of full data query performance.

[0003] In this context, for full data queries in a distributed database architecture, the difference in return time for physical nodes with different data volumes can no longer meet the requirements for efficiency and stability of full data queries in distributed databases. Summary of the Invention

[0004] The purpose of the present invention is to provide a method and system for improving the efficiency and stability of full-data query in a distributed database, in order to solve the technical problems existing in the background technology.

[0005] In order to achieve the above object, the present invention adopts the following technical solutions:

[0006] A method for improving the efficiency and stability of full-data query in a distributed database, comprising:

[0007] Set up a scheduled task framework based on a distributed database;

[0008] Obtaining the data volume of each physical node of the distributed database as a variable value of a sample of each physical node;

[0009] Obtaining an average of data volumes of different physical nodes of the distributed database based on the variable value;

[0010] Determine the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes;

[0011] Determine, based on the standard deviation, a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database;

[0012] Based on the standard scoring, identify the differences in query efficiency and stability between physical nodes with different data volumes, and determine the decision-making mechanism;

[0013] Based on the variable value and the standard score, the amount of data that needs to be queried and cached in advance is determined.

[0014] In some embodiments, the decision-making mechanism includes:

[0015] For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

[0016] In some embodiments, determining the amount of data that needs to be cached in advance includes:

[0017] For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0.

[0018] For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

[0019] In some embodiments, the method further comprises:

[0020] Before optimizing data cache on different physical nodes of a distributed database, clear the data cache.

[0021] At the same time, the present invention also discloses a system for improving the efficiency and stability of full-data query in a distributed database, comprising:

[0022] Setting module, used to set the scheduled task framework based on the distributed database;

[0023] A first acquisition module is used to obtain the data volume of each physical node of the distributed database as a variable value of each physical node sample;

[0024] A second acquisition module is used to obtain an average of the data volume of different physical nodes of the distributed database based on the variable value;

[0025] A first determining module is used to determine the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes;

[0026] A calculation module, configured to determine, based on the standard deviation, a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database;

[0027] An identification module, configured to identify differences in query efficiency and stability between physical nodes with different data volumes based on the standard scoring, and determine a decision-making mechanism;

[0028] The second determining module is configured to determine the amount of data that needs to be queried and cached in advance based on the variable value and the standard score.

[0029] In some embodiments, the decision-making mechanism includes:

[0030] For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

[0031] In some embodiments, the second determining module is further configured to:

[0032] For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0.

[0033] For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

[0034] In some embodiments, a clearing module is further included for clearing the data cache before optimizing the data cache of different physical nodes of the distributed database.

[0035] At the same time, the present invention also discloses a device for improving the efficiency and stability of full-scale data query in a distributed database, the device including a processor and a memory; the memory is used to store instructions, and when the instructions are executed by the processor, the device causes the device to implement any of the above-mentioned methods for improving the efficiency and stability of full-scale data query in a distributed database.

[0036] At the same time, the present invention also discloses a computer-readable storage medium, which stores computer instructions. When the computer reads the computer instructions in the storage medium, the computer runs any of the above-mentioned methods for improving the efficiency and stability of full-data query in a distributed database.

[0037] Beneficial effects

[0038] Compared with the prior art, the present invention has the following significant advantages:

[0039] Through the solution of the present invention, it is possible to supplement the lower limit without affecting the data consistency and integrity between the physical data nodes of the current distributed database architecture, and complete the full data query of all physical nodes at a faster speed, thereby reducing the overall time for returning the full data and improving the efficiency and stability of the full data query of the distributed database. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 Schematic diagram of a system for improving the efficiency and stability of full-data query in a distributed database according to this embodiment;

[0041] Figure 2 This is a flow chart of a method for improving the efficiency and stability of full-data query in a distributed database involved in this embodiment;

[0042] Figure 3 This is a schematic diagram of an application scenario of the method for improving the efficiency and stability of full-data query in a distributed database involved in this embodiment. DETAILED DESCRIPTION

[0043] In order to make the purpose, technical solutions and advantages of this application more clearly understood, the present application is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0044] On the contrary, this application covers any alternatives, modifications, equivalents, and solutions made within the spirit and scope of this application as defined by the claims. Furthermore, to facilitate a better understanding of this application, certain specific details are described in detail below in the detailed description of this application. Those skilled in the art will be able to fully understand this application without these details.

[0045] The following will be combined Figure 1-3 A method for improving the efficiency and stability of full-data query in a distributed database according to an embodiment of the present application is described in detail. It should be noted that the following embodiments are only used to explain the present application and do not constitute a limitation of the present application.

[0046] Example 1

[0047] like Figure 1 As shown, a system 100 for improving the efficiency and stability of full-data query in a distributed database includes:

[0048] Setting module 101, for setting a scheduled task framework based on a distributed database.

[0049] In some embodiments, the setting module 101 may set a scheduled task framework for the entire distributed database, wherein the framework includes an internal mechanism for identifying a standard scoring of the discreteness of the data volumes of different physical nodes in the distributed database.

[0050] The first acquisition module 102 is configured to acquire the data volume of each physical node of the distributed database as a variable value of a sample of each physical node.

[0051] In some embodiments, the first acquisition module 102 may obtain the data volume of each physical node of the distributed database as the variable value x of different physical node samples.

[0052] The second acquisition module 103 is configured to acquire an average of data volumes of different physical nodes of the distributed database based on the variable value.

[0053] In some embodiments, the second acquisition module 103 may be based on the data volume x of each physical node sample, the number n of physical nodes, and the average data volume t of different physical nodes in the entire distributed database.

[0054] The first determining module 104 is configured to determine the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes.

[0055] In some embodiments, the first determination module 104 can obtain the standard deviation s of the degree of dispersion of data volume differences of different physical node samples in the entire distributed database based on the data volume x of each physical node sample and the average t of data volume of different physical nodes in the entire distributed database.

[0056] The calculation module 105 is configured to determine, based on the standard deviation, a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database.

[0057] In some embodiments, the calculation module 105 can measure the standard deviation s of the degree of dispersion of data volume differences of samples of different physical nodes based on the entire distributed database, thereby obtaining a standard score z for calculating the degree of dispersion of data volume of samples of different physical nodes in the entire distributed database.

[0058] The identification module 106 is configured to identify the query efficiency and stability differences between physical nodes with different data volumes based on the standard scoring, and determine a decision-making mechanism.

[0059] In some embodiments, the decision-making mechanism includes:

[0060] For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

[0061] In some embodiments, the identification module 106 can identify differences in query efficiency and stability among physical nodes with different data volumes based on a standard score z for the degree of data volume dispersion of different physical nodes in the distributed database, and automatically make a decision. If a distributed database physical node has a data volume variance standard score z less than or equal to 0, data cache optimization for that physical node is not triggered; if a distributed database physical node has a data volume variance standard score z greater than 0, partial data cache optimization for that physical node is triggered.

[0062] The second determining module 107 is configured to determine the amount of data that needs to be queried and cached in advance based on the variable value and the standard score.

[0063] In some embodiments, the second determining module is further configured to:

[0064] For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0.

[0065] For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

[0066] In some embodiments, the second determination module 107 may obtain the amount of data c that needs to be cached in advance based on the amount of data x of each physical node sample and the standard score z of the degree of data dispersion of different physical nodes in the distributed database. If the standard score z of the degree of data dispersion of the distributed database physical node is less than or equal to 0, the amount of data that needs to be cached by the physical node is 0; if the standard score z of the degree of data dispersion of the distributed database physical node is greater than 0, the amount of data that needs to be cached by the physical node is the product of the standard deviation of the degree of data dispersion and the standard score.

[0067] In some embodiments, a clearing module 108 is further included, which is used to clear the data cache before optimizing the data cache of different physical nodes of the distributed database.

[0068] As can be seen from the above, the present invention is mainly aimed at the distributed database architecture. Under the premise of ensuring the consistency of the physical characteristics and disk performance of each physical storage node in the distributed database, when each physical storage node completes the full data query demand and returns the query result to the database middleware, due to the difference in data volume between different physical nodes, the physical node with a large amount of data takes a long time to return the data to the database middleware, thereby causing the problem of the efficiency and stability of the full data query of the entire distributed database. By identifying the physical nodes with large data volume dispersion and long data return time in the distributed database architecture, the data is cached in advance for optimization, thereby improving the stability and efficiency of the full data query performance of the entire distributed database, reducing the data return time of the physical nodes with large data volume and low query performance, and thus shortening the full data return time.

[0069] It should be noted that the above description of the system and its modules is for convenience only and does not limit this specification to the scope of the embodiments. It is understandable that those skilled in the art, after understanding the principles of the system, may arbitrarily combine the modules or form subsystems connected with other modules without departing from the principles. In some embodiments, Figure 1 The setting module 101, first acquisition module 102, second acquisition module 103, first determination module 104, calculation module 105, identification module 106, second determination module 107, and clearing module 108 disclosed herein may be different modules within a system, or a single module may implement the functions of two or more of the aforementioned modules. For example, each module may share a storage module, or each module may have its own storage module. Such variations are within the scope of protection of this specification.

[0070] like Figure 2 As shown, a method for improving the efficiency and stability of full data query in a distributed database includes:

[0071] Step 210: Setting a scheduled task framework based on a distributed database.

[0072] For example, a scheduled task framework is set up for the entire distributed database, which includes an internal mechanism to identify the standard scoring of the degree of data size dispersion of different physical nodes in the distributed database. This includes designing an internal identification mechanism for the distributed database to identify the standard scoring of the degree of data size dispersion of different physical nodes in the distributed database.

[0073] Step 220: Obtain the data volume of each physical node of the distributed database as the variable value of each physical node sample.

[0074] For example, taking different physical nodes in a distributed database architecture as samples, let the number of physical nodes be n, and let the data volume of each physical node sample be the variable value x.

[0075] Step 230: Obtain the average amount of data in different physical nodes of the distributed database based on the variable value. Specifically, the average amount of data in different physical nodes of the entire distributed database obtained by the variable value x is t, where the average t is:

[0076] Step 240 : determining the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes.

[0077] Specifically, the standard deviation s of the degree of dispersion of sample data volumes at different physical nodes in the entire distributed database can be obtained based on the data volume x of each physical node sample, the number of physical nodes n, and the average data volume t of different physical nodes in the entire distributed database:

[0078] Step 250: Determine a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database based on the standard deviation.

[0079] Specifically, we can obtain the standard deviation s of the degree of dispersion of data volume of samples from different physical nodes in the entire distributed database based on the data volume x of each physical node sample and the average data volume t of different physical nodes in the entire distributed database. We can also obtain the standard score z of the degree of dispersion of data volume of samples from different physical nodes in the entire distributed database:

[0080] Right now

[0081] Step 260 : Based on the standard scoring, identify the query efficiency and stability differences between physical nodes with different data volumes, and determine a decision-making mechanism.

[0082] Specifically, based on the standard score z of the data volume dispersion of different physical nodes in the distributed database, the differences in query efficiency and stability of physical nodes with different data volumes can be identified and a decision-making mechanism can be made.

[0083] In some embodiments, the decision-making mechanism includes:

[0084] For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

[0085] That is, if the data volume difference standard score z is less than or equal to 0 for a distributed database physical node, data cache optimization for that physical node will not be triggered;

[0086] If the data volume difference standard score z of a distributed database physical node is greater than 0, partial data cache optimization of the physical node is triggered.

[0087] Step 270 determines the amount of data that needs to be cached in advance based on the variable value and the standard score. Specifically, the amount of data that needs to be cached in advance is determined based on the amount of data x of each physical node sample and the standard score z of the degree of data dispersion of different physical nodes in the distributed database.

[0088] In some embodiments, determining the amount of data that needs to be cached in advance includes:

[0089] For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0.

[0090] For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

[0091] That is, if the data volume dispersion standard score z is less than or equal to 0 for a distributed database physical node, the amount of data that the physical node needs to cache is 0:

[0092] {c i |0, -∞<z i <=0};

[0093] If the data volume dispersion standard score of a distributed database physical node is greater than 0, the formula for the amount of data that the physical node needs to cache is as follows:

[0094] {c i |s×z i ,0 <z i <+∞}.

[0095] In some embodiments, the method further comprises step 280:

[0096] Before optimizing data cache on different physical nodes of a distributed database, clear the data cache.

[0097] In summary, the technical solution of the present invention is a method for identifying and optimizing the differences in the stability of the performance of full-scale data queries in distributed databases among physical nodes. On the basis of not affecting the integrity of the distributed database data, an identification mechanism is designed, and the data volume of different physical nodes is set as a variable. Standard scores are given to different physical nodes of the distributed database, and data cache preprocessing is performed according to the standard score size of the discrete degree of data volume difference. Physical node samples with a standard score of the discrete degree of data volume difference less than or equal to 0 are not processed. Physical node samples with a standard score of the discrete degree of data volume difference greater than 0 are processed according to the standard score of the discrete degree of data volume difference multiplied by the discrete degree value to obtain the amount of data to be cached, and this amount of data is cached in advance, which saves the time for large-scale physical database nodes in the distributed data architecture to return the full amount of data, makes up for the shortcomings, and thus reduces the time for the entire distributed database node to return the full amount of data.

[0098] It should be noted that the above description of the relevant processes is for illustration and purpose only and does not limit the scope of application of this specification. For those skilled in the art, various modifications and changes can be made to the processes under the guidance of this specification. However, such modifications and changes are still within the scope of this specification.

[0099] At the same time, the present invention also discloses a device for improving the efficiency and stability of full-scale data query in a distributed database, the device including a processor and a memory; the memory is used to store instructions, and when the instructions are executed by the processor, the device causes the device to implement any of the above-mentioned methods for improving the efficiency and stability of full-scale data query in a distributed database.

[0100] At the same time, the present invention also discloses a computer-readable storage medium, which stores computer instructions. When the computer reads the computer instructions in the storage medium, the computer runs any of the above-mentioned methods for improving the efficiency and stability of full-data query in a distributed database.

[0101] In summary, the technical solution of the present application involves an identification mechanism to identify physical nodes in the entire distributed database architecture whose data volume is highly discrete, resulting in poor full data query performance, and to optimize the cache of their data to reduce the time difference between the full data query of this node and other physical nodes, thereby improving the stability of the full data query efficiency of the entire distributed database architecture.

[0102] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for improving the efficiency and stability of full-data query in a distributed database, characterized in that: include: Set up a scheduled task framework based on a distributed database; Obtaining the data volume of each physical node of the distributed database as a variable value of a sample of each physical node; Obtaining an average of data volumes of different physical nodes of the distributed database based on the variable value; Determine the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes; Determine, based on the standard deviation, a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database; Based on the standard scoring, identify the differences in query efficiency and stability between physical nodes with different data volumes, and determine the decision-making mechanism; Based on the variable value and the standard score, the amount of data that needs to be queried and cached in advance is determined.

2. A method for improving the efficiency and stability of full-data query in a distributed database according to claim 1, characterized in that: The decision-making mechanism includes: For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

3. A method for improving the efficiency and stability of full-data query in a distributed database according to claim 2, characterized in that: Determining the amount of data that needs to be cached in advance includes: For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0. For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

4. A method for improving the efficiency and stability of full-data query in a distributed database according to claim 1, characterized in that: The method further comprises: Before optimizing data cache on different physical nodes of a distributed database, clear the data cache.

5. A system for improving the efficiency and stability of full-data query in a distributed database, characterized in that: include: Setting module, used to set the scheduled task framework based on the distributed database; A first acquisition module is used to obtain the data volume of each physical node of the distributed database as a variable value of each physical node sample; A second acquisition module is used to obtain an average of the data volume of different physical nodes of the distributed database based on the variable value; A first determining module is used to determine the standard deviation of the degree of dispersion of sample data volumes of different physical nodes of the distributed database according to the variable value, the number of physical nodes, and the average of the data volumes of different physical nodes; A calculation module, configured to determine, based on the standard deviation, a standard score for calculating the degree of dispersion of data volumes of samples of different physical nodes of the distributed database; An identification module, configured to identify differences in query efficiency and stability between physical nodes with different data volumes based on the standard scoring, and determine a decision-making mechanism; The second determining module is configured to determine the amount of data that needs to be queried and cached in advance based on the variable value and the standard score.

6. A system for improving the efficiency and stability of full-data query in a distributed database according to claim 5, characterized in that: The decision-making mechanism includes: For a distributed database physical node whose standard score is less than or equal to 0, data cache optimization for the physical node is not triggered; otherwise, data cache optimization for the physical node is triggered.

7. A system for improving the efficiency and stability of full-data query in a distributed database according to claim 6, characterized in that: The second determining module is further configured to: For a distributed database physical node with the standard score less than or equal to 0, the amount of data that the physical node needs to cache is 0. For a distributed database physical node with a standard score greater than 0, the amount of data that the physical node needs to cache is the product of the standard deviation of the data volume dispersion and the standard score.

8. The system for improving the efficiency and stability of full-data query in a distributed database according to claim 5, characterized in that: It also includes a clearing module for clearing the data cache before optimizing the data cache of different physical nodes of the distributed database.

9. A device for improving the efficiency and stability of full-data query in a distributed database, comprising a processor, wherein the processor is used to execute the method for improving the efficiency and stability of full-data query in a distributed database according to any one of claims 1 to 4.

10. A computer-readable storage medium storing computer instructions. When a computer reads the computer instructions in the storage medium, the computer executes the method for improving the efficiency and stability of full-data query in a distributed database according to any one of claims 1 to 4.

Images