A slow query statistics method, device, equipment and storage medium

Abstract

The present invention discloses a slow query statistics method, device, equipment and storage medium. It includes: constructing a statistical information table of a distributed database, obtaining a query statement, and determining the query situation corresponding to the query statement; when the query situation is a slow query, generating a target summary and a memory record according to the query statement; and updating the statistical information table according to the target summary and the memory record. By constructing a statistical information table, basic data support is provided for subsequent slow query analysis. The generated target summary can succinctly summarize the key features of the slow query, reduce the complexity of the query analysis, and realize the centralized management and analysis of similar queries. Through intermediate memory records and batch update strategies, the efficiency and accuracy of the statistical information table can be ensured, and the performance overhead can be reduced. It provides more accurate and comprehensive slow query statistical information for administrators and developers of distributed databases, which helps to timely discover problems, optimize query statements and improve database performance.

Description

Technical Field

[0001] The present invention relates to the field of distributed databases, and in particular to a slow query statistics method, device, equipment and storage medium. Background Art

[0002] As the application scenarios of distributed database systems become increasingly complex, slow queries have become one of the main bottlenecks affecting the performance of distributed database systems. Slow queries refer to query statements that take a long time to execute in the database. Traditional distributed database systems usually use slow query log files to record queries whose execution time exceeds a specified threshold. This method consumes a lot of performance in a high-concurrency environment and is difficult to analyze and optimize queries in real time.

[0003] In addition, the existing slow query log system lacks the ability to merge similar queries and cannot perform unified statistics and analysis on a large number of similar queries. Therefore, a more efficient and refined slow query log statistics method is urgently needed to support in-depth analysis and optimization of database performance. Summary of the invention

[0004] The present invention provides a slow query statistics method, device, equipment and storage medium to significantly improve the efficiency of query analysis in a distributed database system and reduce the consumption of system resources.

[0005] According to one aspect of the present invention, a slow query statistics method is provided, the method comprising:

[0006] Build a statistical information table of the distributed database, obtain the query statement, and determine the query situation corresponding to the query statement;

[0007] When the query situation is a slow query, a target summary and memory record are generated based on the query statement;

[0008] The statistics tables are updated based on the target summary and memory records.

[0009] Optionally, determining a query situation corresponding to the query statement includes: determining a query time corresponding to the query statement; judging whether the query time exceeds a slow query threshold, and if so, determining that the query situation is a slow query; otherwise, determining that the query situation is a non-slow query.

[0010] Optionally, a target summary and a memory record are generated based on a query statement, including: performing a hash calculation on the query statement to generate a target summary; determining each target item included in the statistical information table, and obtaining relevant information based on each target item; and temporarily storing the relevant information to generate a memory record.

[0011] Optionally, the statistical information table is updated according to the target summary and the memory record, including: obtaining the historical summary; judging whether the target summary appears for the first time based on the historical summary, and if so, directly updating the statistical information table according to the memory record; otherwise, updating the statistical information table according to the memory record.

[0012] Optionally, the statistical information table is updated directly according to the memory record, including: matching the memory record with the statistical information table to determine each matching item between the memory record and the statistical information table; and inserting the memory record into the statistical information table based on each matching item.

[0013] Optionally, updating the statistical information table according to the memory record includes: determining update information from the memory record, wherein the update information includes execution time and execution count; and updating the statistical information table according to the update information.

[0014] Optionally, the target items in the statistics table include library name, summary, summary information, the time when the statement with the given summary appeared most recently, the number of slow queries, the total cumulative time of slow queries, the cumulative time for slow queries to open cache tables and data dictionaries, the total number of rows scanned by slow queries, the total command execution time of slow queries, the total time from obtaining network events to obtaining events from the queue, the total time for slow queries to be submitted, the total time for slow queries to obtain metadata locks, the total time for slow queries to obtain engine locks, the total time for slow queries to write data to cache, the total storage engine submission time for slow queries, the total standby response time for slow queries, the total cleanup time from the end of slow query transaction submission to writing slow logs, the total time for slow queries to read network cache, the total time for slow queries to write network cache, the total time for slow queries to write to disk, and the total waiting time in the submission queue for slow queries.

[0015] According to another aspect of the present invention, a slow query statistics device is provided, the device comprising:

[0016] The information table construction and query statement acquisition module is used to construct the statistical information table of the distributed database, obtain the query statement, and determine the query situation corresponding to the query statement;

[0017] A summary and memory record generation module is used to generate a target summary and memory record according to a query statement when the query situation is a slow query;

[0018] The statistics table update module is used to update the statistics table according to the target summary and memory records.

[0019] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0020] at least one processor;

[0021] and a memory communicatively coupled to the at least one processor;

[0022] The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute a slow query statistics method described in any embodiment of the present invention.

[0023] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement a slow query statistics method described in any embodiment of the present invention when executed.

[0024] The technical solution of the embodiment of the present invention provides basic data support for subsequent slow query analysis by constructing a statistical information table. The generated target summary can succinctly summarize the key features of slow queries, reduce the complexity of query analysis, and realize centralized management and analysis of similar queries. Through intermediate memory records and batch update strategies, the efficiency and accuracy of the statistical information table can be ensured, and performance overhead can be reduced. It provides more accurate and comprehensive slow query statistics for administrators and developers of distributed databases, which helps to timely discover problems, optimize query statements, and improve database performance.

[0025] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0027] Figure 1 This is a flowchart of a slow query statistics method provided according to the first embodiment of the present invention;

[0028] Figure 2 is a flowchart of another slow query statistics method provided according to the second embodiment of the present invention;

[0029] Figure 3 is a schematic diagram of the structure of a slow query statistics device provided according to Embodiment 3 of the present invention;

[0030] Figure 4 It is a structural schematic diagram of an electronic device for implementing a slow query statistics method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0031] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0032] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0033] Embodiment 1

[0034] Figure 1 A flowchart of a slow query statistics method is provided for the first embodiment of the present invention. This embodiment is applicable to the case of performing slow query statistics on a distributed database. The method can be executed by a slow query statistics device. The slow query statistics device can be implemented in the form of hardware and / or software. The slow query statistics device can be configured in a computer controller. Figure 1 As shown, the method includes:

[0035] S110: construct a statistical information table of the distributed database, obtain a query statement, and determine a query situation corresponding to the query statement.

[0036] Among them, a distributed database refers to a database system composed of a set of data sets distributed on different nodes. The nodes are interconnected and work together through the network to provide data storage and access services to the outside world. The statistical information table refers to the information used to store slow query statistics based on the summary dimension. It contains multiple fields. By filling in the relevant data of each query according to the set format and rules, subsequent analysis can be performed based on the data in the table to understand the execution status of different query statements, especially the frequency and time consumption of slow queries. Performance-related conditions. A query statement refers to a statement written by a user or application when initiating a query request to a database. It can be an SQL statement used to retrieve data that meets specific conditions from the database. The query status refers to the description of the overall performance status of a query statement during execution, which is divided into slow queries and non-slow queries.

[0037] S120: When the query situation is a slow query, a target summary and a memory record are generated according to the query statement.

[0038] Among them, the emergence of slow queries often implies that there may be performance problems in the database system, such as unreasonable query statement design, poor database configuration, hardware resource bottlenecks, etc. It is necessary to analyze the data related to slow queries to find the causes and optimize them to improve the overall response speed and operation efficiency of the database. The target summary refers to a concise and representative identification information generated after the query statement is hashed. It can uniquely or highly distinguish different query statements, facilitate subsequent rapid comparison, identification, and classification of query statements in related records such as statistical information tables, which helps to improve the efficiency of slow query statistics and analysis work and avoid repeated processing and comparison of complex original query statements. Memory records are a form of data that temporarily stores detailed information related to slow queries in memory, which can include query start time, end time, data tables involved, and resource usage of the system at that time. Due to the fast reading and writing speed of memory, by generating memory records, data can be quickly obtained when the statistical information table is updated in the future, ensuring the efficient and smooth operation of the entire slow query statistics process.

[0039] It is known that in a distributed database environment, data is stored in multiple nodes, and the constructed statistical information table is equivalent to a center for centralized management and recording of query-related data. By pre-defining a suitable table structure, such as fields including query statement identifiers, execution time, and data tables involved, it is possible to collect various query information to be analyzed later in an organized manner, facilitate subsequent statistical analysis based on structured data, and provide basic support for fully understanding the slow query performance of the database.

[0040] Specifically, generating a target summary means extracting key feature information from complex query statements, such as the names of the core data tables involved and the main query conditions, and integrating them into a concise summary that represents the essential features of the query statement, so as to facilitate the subsequent rapid identification and classification of similar slow queries. Generating memory records uses the fast read and write characteristics of memory to temporarily store more detailed information related to the slow query, such as the query start time, end time, and system resource usage at the time, so that memory records can be quickly obtained during subsequent operations such as updating statistical information tables. Memory operations are faster than disk operations and can efficiently assist the entire statistical process.

[0041] Optionally, a target summary and a memory record are generated based on a query statement, including: performing a hash calculation on the query statement to generate a target summary; determining each target item included in the statistical information table, and obtaining relevant information based on each target item; and temporarily storing the relevant information to generate a memory record.

[0042] Among them, hash calculation is an algorithm mechanism that converts data of any length into a fixed-length hash value. The hash value of the query statement, that is, the target summary, can be calculated through the hash algorithm. The hash value is more concise than the original query statement, which is convenient for comparison, storage and management in the subsequent process, which helps to improve the efficiency of the entire slow query statistics process. At the same time, based on the hash value, it can quickly determine whether it is a similar or repeated slow query situation. The statistical information table organizes data according to a pre-designed structure. Each target item in it is a field used to record key information of different dimensions related to the query. Based on the determined target items, the database system will obtain the corresponding relevant information through internal parsing, query execution tracking and other mechanisms. For example, to obtain the name of the data table involved, the system can identify which data tables are operated in the statement when parsing the query statement. Finally, the control will temporarily store the relevant information to generate a memory record. The memory has the advantage of fast reading and writing speed. When generating a memory record, the controller can temporarily store the relevant information obtained based on each target item in the memory. Temporary storage in memory can facilitate efficient use of data in the short process of the entire slow query statistics, avoid performance loss caused by frequent interaction with the disk, and ensure the efficient operation of the entire statistical process.

[0043] S130: Update the statistical information table according to the target summary and the memory record.

[0044] Specifically, based on the generated target summary, the controller can accurately find the corresponding record location in the statistical information table, and then update the corresponding fields in the statistical information table in combination with the detailed data in the memory record, such as the number of occurrences of this type of slow query, the total accumulated time consumed, and other field contents.

[0045] Optionally, the statistical information table is updated according to the target summary and the memory record, including: obtaining the historical summary; judging whether the target summary appears for the first time based on the historical summary, and if so, directly updating the statistical information table according to the memory record; otherwise, updating the statistical information table according to the memory record.

[0046] Among them, in the statistical information table, the summary of various query statements previously recorded and generated by hash calculation is the historical summary. The controller can compare the newly generated target summary with the obtained historical summary one by one. If no matching one is found in all historical summaries, it means that the query statement corresponding to the current target summary appears for the first time. At this time, since it is the first time that the slow query occurs, there is no record about it in the statistical information table, so the statistical information table can be directly updated according to the memory record. That is, according to the relevant detailed information such as query time and query number stored in the memory record, the corresponding memory record is filled in according to the established structure and field requirements of the statistical information table. On the contrary, if it is found through comparison that the target summary already exists in the historical summary, it means that this slow query situation has appeared before, but at this time the controller still needs to update the statistical information table according to the memory record. This is because each time the slow query occurs, the relevant specific situation will be different, such as the query time of this time, the resource usage of the system at that time, etc. Therefore, the controller needs to integrate the new data in the memory records into the existing statistical information table records. For example, add 1 to the field that records the number of slow query occurrences, and add the current query time to the field that records the cumulative query time. Through continuous updates, it can more accurately reflect the frequency of slow queries over time, the degree of impact on system performance, and other situations, thereby providing more comprehensive and reliable data support for subsequent targeted optimization of database performance.

[0047] Optionally, the statistical information table is updated directly according to the memory record, including: matching the memory record with the statistical information table to determine each matching item between the memory record and the statistical information table; and inserting the memory record into the statistical information table based on each matching item.

[0048] Among them, the memory record stores multi-dimensional detailed information related to the current slow query, such as the query start time, end time, current statement database library name, etc. The statistical information table has a pre-designed table structure, which contains many fields corresponding to the record query situation, such as "query start time", "query end time", "current statement database library name" and other related target items. When matching the two, the matching items are determined based on the correlation and correspondence between the information. Through matching, the controller can clearly determine the specific location of the statistical information table where each data in the memory record should be filled in, ensuring that subsequent insertion operations can be carried out accurately and orderly, so that the statistical information table can integrate new slow query related information in an orderly manner.

[0049] Optionally, updating the statistical information table according to the memory record includes: determining update information from the memory record, wherein the update information includes execution time and execution count; and updating the statistical information table according to the update information.

[0050] Specifically, the execution time is a time record of the query, including the total query time and the total submission time, etc., which reflects the efficiency of this query operation. The number of executions can be determined through a counting mechanism. For example, every time the query statement that meets the slow query judgment occurs, the corresponding counter is increased by 1 to accurately record the frequency of the slow query. For the execution time, there may be a "cumulative execution time" field in the statistical information table. When updating, the controller will add the current execution time in the memory record to the existing value of this field. For the number of executions, there is often a "slow query number" field in the statistical information table, which will perform corresponding value updates based on the number of executions in the memory record, such as adding 1.

[0051] Optionally, the target items in the statistics table include library name, summary, summary information, the time when the statement with the given summary appeared most recently, the number of slow queries, the total cumulative time of slow queries, the cumulative time for slow queries to open cache tables and data dictionaries, the total number of rows scanned by slow queries, the total command execution time of slow queries, the total time from obtaining network events to obtaining events from the queue, the total time for slow queries to be submitted, the total time for slow queries to obtain metadata locks, the total time for slow queries to obtain engine locks, the total time for slow queries to write data to cache, the total storage engine submission time for slow queries, the total standby response time for slow queries, the total cleanup time from the end of slow query transaction submission to writing slow logs, the total time for slow queries to read network cache, the total time for slow queries to write network cache, the total time for slow queries to write to disk, and the total waiting time in the submission queue for slow queries.

[0052] The database name is used to specify the name of the database used to execute the slow query statement. In a complex database environment such as a distributed database, there may be multiple databases with different purposes and business scopes. The database name is recorded to distinguish the use of different databases to execute the same slow query statement, so as to facilitate subsequent performance analysis and optimization management according to different databases, such as distinguishing whether the slow query occurs in the business database or the log database. Summary refers to a concise identifier generated by hashing the query statement, which can be used to quickly distinguish different query statements, help determine whether it is a repeated slow query, and facilitate the rapid location and classification of similar query situations in many query records, providing a convenient index basis for subsequent statistical and comparative analysis. Summary information is more detailed than abstract, and may contain key feature content extracted from the query statement, such as the main data tables involved, core query conditions, etc., to further enrich the description of the essence of the query statement, and help to deeply analyze the specific business relevance of the slow query and find the root cause of the slow query.

[0053] Specifically, recording the time when a statement with a given summary appeared most recently can intuitively reflect the time when a specific query statement appeared most recently as a slow query. By observing its time change trend, we can know the activity of the slow query at different stages, such as whether it appears frequently recently, and provide a time dimension data reference for timely discovering the trend of worsening performance problems and judging whether optimization measures are effective. The number of slow queries refers to the number of times a query statement is judged as a slow query. The total time accumulation of slow queries refers to the total time consumed by slow queries, which can help evaluate the long-term cumulative loss of system performance.

[0054] Among them, cache tables and data dictionaries play an important role in accelerating data acquisition and assisting analysis during query execution. Recording the cumulative time of slow queries opening cache tables and data dictionaries helps analyze whether there is a performance bottleneck in this link, such as whether slow queries occur due to frequent cache invalidation and slow data dictionary retrieval. The total number of rows scanned by slow queries reflects the total number of data rows scanned by the slow query during execution. The more rows, the greater the workload of the query operation, which may consume more time and system resources. The total number of rows scanned by slow queries can be used to judge the efficiency of the query statement, whether there is excessive scanning of unnecessary data, and then guide the optimization and adjustment of the query statement, such as optimizing the filtering conditions to reduce the number of scanned rows. The total command execution time of slow queries refers to the total time spent from receiving the query command to completing all operations. By counting the total command execution time of slow queries, the time consumption of the slow query in the execution link can be grasped as a whole. By comparing this time of different query statements, slow queries with particularly low execution efficiency can be found for key optimization. Recording the time from when a slow query gets a network event to when it gets an event from the queue helps to check whether the slow query is caused by network-related factors such as network delays and slow queue processing, so that optimization measures such as optimizing network configuration and adjusting queue management strategies can be taken. Recording the total time for slow query submissions can be used to analyze whether there is a problem of too long time consumption in this link, such as lock waiting during submission, transaction processing delays, etc. By optimizing submission-related processes and resource allocation, the overall query performance can be improved and the probability of slow queries can be reduced. Recording the total time for slow queries to obtain metadata locks can be used to find out whether there is fierce lock competition, long waits for lock acquisition, etc. If the time is too long, it means that the access control of metadata in a concurrent environment may need to be optimized to avoid slow queries caused by lock problems. Recording the total time for slow queries to obtain engine locks can help determine whether there is a performance barrier caused by the lock mechanism at the engine level, such as whether some queries frequently wait for the release of engine locks, resulting in slow execution, so as to optimize the engine lock management strategy in a targeted manner.

[0055] Specifically, recording the total data write cache time of slow queries can help us understand whether the data write cache link is efficient. If the time is too long, it may indicate that there are deficiencies in the cache write strategy, cache space allocation, etc., which will affect the overall query performance. Based on this, we can optimize the cache-related processing mechanism. Recording the total storage engine commit time of slow queries analyzes whether there are performance bottlenecks in the storage engine when processing operations such as transaction commits, such as whether the storage engine's disk write strategy, transaction log processing, etc. affect the query execution speed, and provide data basis for optimizing the storage engine performance. The total read network cache time of slow queries reflects the cumulative time spent reading data from the network cache. The network cache plays an important role in reducing network transmission and speeding up data acquisition. If this time is too long, it may indicate problems such as poor network cache configuration and low cache hit rate, which affect the query response speed. Based on this, we can optimize the network cache-related strategies. The total write network cache time of slow queries corresponds to the read network cache time. By counting the total time of writing network cache, we can analyze whether the data write link to the network cache is efficient, such as whether the overall query performance is affected by problems such as frequent cache write failures and slow write speeds, and then take optimization measures to improve the efficiency of write cache. Recording the total disk write time of slow queries helps to optimize disk-related configurations and adjust data storage layout to improve query performance. Recording the total waiting time in the submission queue for slow queries The processing efficiency of the submission queue will affect the overall execution progress of the query. It can be used to determine whether the queue management is reasonable and whether there are too many queries waiting to be submitted, resulting in slow queries, so as to optimize the queue scheduling strategy, adjust the queue capacity, and improve the overall query performance.

[0056] The technical solution of the embodiment of the present invention provides basic data support for subsequent slow query analysis by constructing a statistical information table. The generated target summary can succinctly summarize the key features of slow queries, reduce the complexity of query analysis, and realize centralized management and analysis of similar queries. Through intermediate memory records and batch update strategies, the efficiency and accuracy of the statistical information table can be ensured, and performance overhead can be reduced. It provides more accurate and comprehensive slow query statistics for administrators and developers of distributed databases, which helps to timely discover problems, optimize query statements, and improve database performance.

[0057] Embodiment 2

[0058] Figure 2 This is a flowchart of a slow query statistics method provided in the second embodiment of the present invention. This embodiment adds a specific process for determining the query situation corresponding to the query statement on the basis of the above-mentioned first embodiment. Among them, the specific contents of steps S260-S270 are roughly the same as steps S120-S130 in the first embodiment, so they will not be repeated in this embodiment. Figure 2 As shown, the method includes:

[0059] S210: Build a statistical information table of the distributed database and obtain a query statement.

[0060] S220: Determine the query time corresponding to the query statement.

[0061] S230, determine whether the query time exceeds the slow query threshold, if so, execute S240, otherwise, execute S250.

[0062] Among them, during the operation of the database, when a query statement is submitted for execution, there will be a corresponding timing mechanism inside the database system to record the time it takes from the start of execution to the end of execution. Users can set the slow query threshold according to the needs of the business scenario. Different business scenarios have different tolerances for query response speed. For example, for an e-commerce transaction system with extremely high real-time requirements, a query time of 1 second may be considered relatively long, so the threshold will be set lower; while for some back-end data analysis businesses, a query time of several seconds or even more than ten seconds may be considered a slow query, so the threshold will be set higher accordingly.

[0063] S240: Determine that the query situation is a slow query.

[0064] S250: Determine that the query situation is not a slow query.

[0065] Specifically, after obtaining the actual query time of the query statement, the controller will compare the query time with the set slow query threshold. If the actual query time is greater than the slow query threshold, it means that the execution efficiency of the query statement is low, which has affected the overall smoothness of the system or the timely response of the business. At this time, it can be determined that the query is a slow query. If the query time is less than or equal to the slow query threshold, it means that the execution speed of the query statement is within an acceptable range. Accordingly, the query is determined to be a non-slow query, and there is no need to perform slow query-related optimization and statistical analysis operations.

[0066] S260: When the query situation is a slow query, a target summary and a memory record are generated according to the query statement.

[0067] Optionally, a target summary and a memory record are generated based on a query statement, including: performing a hash calculation on the query statement to generate a target summary; determining each target item included in the statistical information table, and obtaining relevant information based on each target item; and temporarily storing the relevant information to generate a memory record.

[0068] S270: Update the statistical information table according to the target summary and the memory record.

[0069] Optionally, the statistical information table is updated according to the target summary and the memory record, including: obtaining the historical summary; judging whether the target summary appears for the first time based on the historical summary, and if so, directly updating the statistical information table according to the memory record; otherwise, updating the statistical information table according to the memory record.

[0070] Optionally, the statistical information table is updated directly according to the memory record, including: matching the memory record with the statistical information table to determine each matching item between the memory record and the statistical information table; and inserting the memory record into the statistical information table based on each matching item.

[0071] Optionally, updating the statistical information table according to the memory record includes: determining update information from the memory record, wherein the update information includes execution time and execution count; and updating the statistical information table according to the update information.

[0072] Optionally, the target items in the statistics table include library name, summary, summary information, the time when the statement with the given summary appeared most recently, the number of slow queries, the total cumulative time of slow queries, the cumulative time for slow queries to open cache tables and data dictionaries, the total number of rows scanned by slow queries, the total command execution time of slow queries, the total time from obtaining network events to obtaining events from the queue, the total time for slow queries to be submitted, the total time for slow queries to obtain metadata locks, the total time for slow queries to obtain engine locks, the total time for slow queries to write data to cache, the total storage engine submission time for slow queries, the total standby response time for slow queries, the total cleanup time from the end of slow query transaction submission to writing slow logs, the total time for slow queries to read network cache, the total time for slow queries to write network cache, the total time for slow queries to write to disk, and the total waiting time in the submission queue for slow queries.

[0073] The technical solution of the embodiment of the present invention provides basic data support for subsequent slow query analysis by constructing a statistical information table. The generated target summary can succinctly summarize the key features of slow queries, reduce the complexity of query analysis, and realize centralized management and analysis of similar queries. Through intermediate memory records and batch update strategies, the efficiency and accuracy of the statistical information table can be ensured, and performance overhead can be reduced. It provides more accurate and comprehensive slow query statistics for administrators and developers of distributed databases, which helps to timely discover problems, optimize query statements, and improve database performance.

[0074] Embodiment 3

[0075] Figure 3 This is a schematic diagram of the structure of a slow query statistics device provided by Embodiment 3 of the present invention. Figure 3 As shown, the device includes: an information table construction and query statement acquisition module 310, which is used to construct a statistical information table of a distributed database, and obtain a query statement, and determine the query situation corresponding to the query statement;

[0076] A summary and memory record generation module 320, used to generate a target summary and a memory record according to a query statement when the query situation is a slow query;

[0077] The statistical information table updating module 330 is used to update the statistical information table according to the target summary and the memory record.

[0078] Optionally, the information table construction and query statement acquisition module 310 is specifically used to: determine the query time corresponding to the query statement; determine whether the query time exceeds the slow query threshold, if so, determine that the query situation is a slow query; otherwise, determine that the query situation is a non-slow query.

[0079] Optionally, the summary and memory record generation module 320 is specifically used to: perform hash calculation on the query statement to generate a target summary; determine each target item included in the statistical information table, and obtain relevant information based on each target item; temporarily store the relevant information to generate a memory record.

[0080] Optionally, the statistical information table update module 330 is specifically used to: obtain historical summaries; determine whether the target summary appears for the first time based on the historical summaries, and if so, directly update the statistical information table according to the memory records; otherwise, update the statistical information table according to the memory records.

[0081] Optionally, the statistical information table updating module 330 specifically includes: a new summary updating unit, which is used to: match the memory record with the statistical information table to determine each matching item between the memory record and the statistical information table; and insert the memory record into the statistical information table based on each matching item.

[0082] Optionally, the statistical information table updating module 330 specifically includes: an old summary updating unit, which is used to: determine update information from the memory record, wherein the update information includes execution time and execution times; and update the statistical information table according to the update information.

[0083] The technical solution of the embodiment of the present invention provides basic data support for subsequent slow query analysis by constructing a statistical information table. The generated target summary can succinctly summarize the key features of slow queries, reduce the complexity of query analysis, and realize centralized management and analysis of similar queries. Through intermediate memory records and batch update strategies, the efficiency and accuracy of the statistical information table can be ensured, and performance overhead can be reduced. It provides more accurate and comprehensive slow query statistics for administrators and developers of distributed databases, which helps to timely discover problems, optimize query statements, and improve database performance.

[0084] A slow query statistics device provided by an embodiment of the present invention can execute a slow query statistics method provided by any embodiment of the present invention, and has functional modules and beneficial effects corresponding to the execution method.

[0085] Embodiment 4

[0086] Figure 4 A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0087] like Figure 4 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0088] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0089] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as a slow query statistics method.

[0090] In some embodiments, a slow query statistics method may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of a slow query statistics method described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to execute a slow query statistics method in any other appropriate manner (e.g., by means of firmware).

[0091] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0092] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0093] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0094] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).

[0095] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0096] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.

[0097] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and this document does not limit this.

[0098] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A slow query statistics method, characterized in that: include: Build a statistical information table of the distributed database, obtain the query statement, and determine the query situation corresponding to the query statement; When the query situation is a slow query, a target summary and a memory record are generated according to the query statement; The statistical information table is updated according to the target summary and the memory record.

2. The method according to claim 1, characterized in that The determining of the query situation corresponding to the query statement includes: Determine the query time corresponding to the query statement; Determine whether the query time exceeds a slow query threshold, and if so, determine that the query is a slow query; Otherwise, the query is determined to be a non-slow query.

3. The method according to claim 1, characterized in that The generating of the target summary and the memory record according to the query statement includes: Performing hash calculation on the query statement to generate a target digest; Determine each target item included in the statistical information table, and obtain relevant information based on each target item; The related information is temporarily stored to generate a memory record.

4. The method according to claim 1, characterized in that: The updating of the statistical information table according to the target summary and the memory record comprises: Get historical summary; Determine whether the target summary appears for the first time based on the historical summary, and if so, directly update the statistical information table according to the memory record; Otherwise, the statistical information table is updated according to the memory record.

5. The method according to claim 4, characterized in that The directly updating the statistical information table according to the memory record includes: Matching the memory record with the statistical information table to determine matching items between the memory record and the statistical information table; Inserting a memory record into the statistics table based on each of the matching items.

6. The method according to claim 4, characterized in that The updating of the statistical information table according to the memory record includes: Determining update information from the memory record, wherein the update information includes execution time and execution count; The statistical information table is updated according to the update information.

7. The method according to claim 3, characterized in that The target items in the statistical information table include library name, summary, summary information, the time when the statement with a given summary appeared most recently, the number of slow queries, the total cumulative time of slow queries, the cumulative time of slow queries opening cache tables and data dictionaries, the total number of rows scanned by slow queries, the total command execution time of slow queries, the total time from when slow queries obtain network events to when they obtain events from queues, the total time for slow queries to be submitted, the total time for slow queries to obtain metadata locks, the total time for slow queries to obtain engine locks, the total time for slow queries to write data to cache, the total storage engine submission time for slow queries, the total standby response time for slow queries, the total cleanup time from the end of slow query transaction submission to writing slow logs, the total time for slow queries to read network cache, the total time for slow queries to write network cache, the total time for slow queries to write to disk, and the total waiting time in the submission queue for slow queries.

8. A slow query statistics device, characterized in that: include: The information table construction and query statement acquisition module is used to construct the statistical information table of the distributed database, obtain the query statement, and determine the query situation corresponding to the query statement; A summary and memory record generation module, used to generate a target summary and a memory record according to a query statement when the query situation is a slow query; A statistical information table updating module is used to update the statistical information table according to the target summary and the memory record.

9. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the method according to any one of claims 1 to 7.

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

Images