Slow query statement processing method applied to multiple types of databases, chip, computer device, storage medium and program product

Abstract

This application relates to a method, chip, computer device, computer-readable storage medium, and computer program product for processing slow query statements in multiple types of databases. The method includes: in response to a data acquisition task triggering instruction, acquiring configuration information of multiple target databases; the configuration information includes database type and slow query statement judgment threshold; matching a corresponding acquisition strategy for each target database according to the database type, and acquiring data for each target database according to the acquisition strategy and the slow query statement judgment threshold to obtain target raw records; extracting the statement principal components of the target raw records, and obtaining flag feature values ​​based on the statement principal components; and aggregating data from each target database according to the flag feature values ​​to obtain data aggregation result information. This method reduces the cost of unified processing of slow query statements in databases.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, computer device, computer-readable storage medium, and computer program product for processing slow query statements applied to multiple types of databases. Background Technology

[0002] As enterprises advance their digital transformation, the databases currently in use are gradually evolving from a single type to a heterogeneous direction with multiple types and brands coexisting. Multiple different types of databases may run simultaneously in the same production environment.

[0003] Against this backdrop, the management of slow query statements has become significantly more challenging. Currently, slow query statement management solutions primarily rely on the monitoring tools built into each database product. However, different types of databases differ significantly in their slow query statement collection interfaces, log formats, and analysis tools. Slow query statement information from different database brands is fragmented, lacking a unified method for collection and identification. This forces database administrators to master the corresponding collection methods and analysis tools for each database brand, easily leading to blind spots in management coverage in environments with multiple database types. Achieving unified processing of slow queries across all enterprise databases is also very costly. Summary of the Invention

[0004] Therefore, it is necessary to provide a method, apparatus, computer device, computer-readable storage medium, and computer program product for processing slow query statements in multiple types of databases, which can reduce the cost of unified processing of slow query statements in databases.

[0005] Firstly, this application provides a method for processing slow query statements applied to multiple types of databases, including:

[0006] In response to the task acquisition trigger command, configuration information of multiple target databases is obtained; the configuration information includes database type and slow query statement judgment threshold.

[0007] According to the database type, a corresponding collection strategy is matched for each target database, and data is collected for each target database according to the collection strategy and the slow query statement determination threshold to obtain the target original records;

[0008] Extract the principal components of the statements from the target original record, and obtain the flag feature values ​​based on the principal components of the statements;

[0009] Based on the characteristic values ​​of each of the aforementioned markers, data is collected from each of the aforementioned target databases to obtain data collection result information.

[0010] In one embodiment, extracting the principal components of the target original record includes:

[0011] The query statement text in the target original record is formatted and preprocessed;

[0012] The pre-processed query text is deparameterized by replacing literal values, string constants, and date constants with placeholders to obtain the query template.

[0013] The query statement template is subjected to structured parsing to extract the main components of the query statement template; the main components of the query statement include at least one of operation type, data table identifier, filter condition structure, table association method and function type.

[0014] In one embodiment, the extraction of the principal components of the target original record further includes:

[0015] Principal component extraction is performed on the execution plan information in the target original record to obtain the execution plan principal components; the execution plan principal components include at least one of the data scanning method, table join algorithm type, and number of table lookup operations.

[0016] Accordingly, obtaining the flag feature value based on the principal components of the statement includes:

[0017] The principal components of the statements and the principal components of the execution plan are merged to obtain the merged principal components;

[0018] The characteristics of the merged principal components are calculated to obtain the characteristic values.

[0019] In one embodiment, the data aggregation result information includes a list of slow query statements; the data aggregation of each target database based on each of the aforementioned flag feature values ​​includes:

[0020] Based on the network address and instance identifier of the target database used when collecting the target original records, the application system identifier corresponding to the target database is found from the configuration information, and the application system identifier is completed into the corresponding aggregate record;

[0021] Based on the application system identifier, the completed aggregate records are classified and summarized to obtain a list of slow query statements.

[0022] In one embodiment, the step of collecting data from each of the target databases based on each of the aforementioned flag feature values ​​further includes:

[0023] Obtain the call chain definition information configured for the target business function; the call chain definition information includes at least one of the following: the link identifier of the target business function, the node identifier of each node in the link, the database type and network address corresponding to each node, the execution order number of each node in the link, and the first execution duration threshold corresponding to the link;

[0024] Based on the call chain definition information, each aggregated record is matched and associated with the corresponding node identifier to obtain the associated aggregated record of each node, and the associated aggregated record is added to the data collection result information.

[0025] In one embodiment, the call chain definition information further includes a second execution duration threshold for each node; the method further includes:

[0026] Compare the execution time in the associated aggregated records corresponding to each node with the second execution time threshold;

[0027] Nodes that exceed the second execution time threshold are marked as timeout nodes, and the timeout nodes are recorded in the associated aggregate record.

[0028] Secondly, this application also provides a chip, which includes a processor and a data interface. The processor reads instructions stored in a memory through the data interface and can execute the steps of the method described in the first aspect.

[0029] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the method described in the first aspect.

[0030] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described in the first aspect.

[0031] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the method described in the first aspect.

[0032] The aforementioned slow query processing method, chip, computer device, computer-readable storage medium, and computer program product applied to multiple types of databases, responding to a data acquisition task trigger command, acquires configuration information for multiple target databases. It can initiate data acquisition processes for multiple different types of target databases from a unified entry point, incorporating dispersed multi-type databases into a single data acquisition system. The configuration information includes database type and slow query threshold. Based on the database type, it matches corresponding acquisition strategies for each target database and performs data acquisition for each target database according to the acquisition strategy and slow query threshold, obtaining the target raw records. It then extracts the statement main components from the target raw records. This method enables the comparability of slow query statements from different types of databases at the principal component level. It then obtains flag feature values ​​based on the principal components of the statements, further converting each type of slow query statement into a unique feature identifier. This allows for identification and tracking of slow query statements regardless of their database type using a unified flag feature value. Finally, based on these flag feature values, data from each target database is aggregated to obtain aggregated data results. By aggregating slow query statements from multiple target databases based on these flag feature values, the method achieves the ability to summarize and analyze slow query statements from different types of databases under a unified dimension, reducing the cost of unified processing of slow query statements in databases. Attached Figure Description

[0033] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0034] Figure 1 This is an application environment diagram of a slow query processing method applied to multiple types of databases in one embodiment;

[0035] Figure 2 This is a flowchart illustrating a slow query processing method applied to multiple types of databases in one embodiment.

[0036] Figure 3 This is a schematic diagram of the database governance process in one embodiment.

[0037] Figure 4 This is a schematic diagram of data aggregation based on microservices in one embodiment.

[0038] Figure 5 This is a flowchart illustrating step S208 of a slow query statement processing method applied to multiple types of databases in one embodiment.

[0039] Figure 6 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0040] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0041] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.

[0042] The slow query processing method for multiple types of databases provided in this application can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or located on the cloud or other network servers. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, drones, low-altitude aircraft, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can include smartwatches, smart bracelets, head-mounted devices, etc. Head-mounted devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. Server 104 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.

[0043] In one exemplary embodiment, such as Figure 2 As shown, a method for handling slow query statements applied to multiple types of databases is provided. This method is applied to... Figure 1 Taking server 104 as an example, the explanation includes the following steps S202 to S208. Wherein:

[0044] Step S202: In response to the acquisition task trigger command, obtain the configuration information of multiple target databases.

[0045] The configuration information includes the database type and the threshold for judging slow queries. The target database refers to a database instance in the enterprise's production environment that is under unified management, and can include different brands and types of database products such as Oracle and MySQL. The database type refers to the brand or product type of the target database; different types of databases differ in their slow query collection interfaces, log formats, and monitoring mechanisms.

[0046] The slow query threshold refers to a pre-configured execution time threshold. When the actual execution time of a database operation statement exceeds this threshold, the statement can be identified as a slow query. Configuration information refers to structured data pre-maintained by the system that describes the basic attributes and collection parameters of each target database. In addition to the database type and slow query threshold, configuration information may also include the network address, port number, access credentials, instance identifier, and the application system identifier and corresponding responsible person information for each target database.

[0047] The task acquisition trigger instruction can be a signal issued by the scheduling system at a preset execution frequency to start a round of slow query statement acquisition process. Its execution frequency can be flexibly configured according to business needs.

[0048] For example, in response to a data collection task trigger command, the server can read configuration information of multiple target databases from a configuration storage pre-maintained by the system. When reading the configuration information, the server can traverse all managed target database records to obtain the database type and slow query threshold of each target database. It can also obtain information such as network address, port number, access credentials, instance identifier, application system identifier, and corresponding responsible person, forming a set of target database configuration information required for the data collection task.

[0049] The slow query threshold can be dynamically adjusted during runtime, and the updated threshold can take effect in the next collection task without restarting the collection service.

[0050] In some embodiments, the server can set a global default threshold, a type threshold, and a system threshold for slow query judgment. The global default threshold is a baseline threshold that applies uniformly to all target databases; the type threshold is a threshold configured specifically for a particular database type; and the system threshold is a threshold configured specifically for a particular application system identifier. When obtaining the slow query judgment threshold for each target database, the server can search sequentially in the order of system threshold taking precedence over type threshold, and type threshold taking precedence over global default threshold. The first matching threshold is used as the slow query judgment threshold for that target database in this round of data collection. Furthermore, the slow query judgment thresholds at each level can also be configured separately for time periods. When obtaining the threshold, the server can read the time period to which the current data collection task was triggered and match the currently effective threshold from the corresponding level's threshold configuration based on that time period. This allows different slow query judgment thresholds to be used during peak and off-peak business periods, thus more closely reflecting the actual business execution situation at each time period.

[0051] For example, after the data collection task is triggered, the server can first perform an operation to query the database device information, read the device information of each target database from the configuration storage, and the device information may include the database type, network address, port number, access credentials, instance identifier and the application system identifier to which it belongs, and simultaneously read the slow query statement judgment threshold configuration corresponding to each target database to complete the data preparation work.

[0052] The above steps, by uniformly reading the configuration information of multiple target databases, can bring scattered multi-type databases into the same collection and management system. This enables the server to initiate collection processes for multiple different types of target databases from a single entry point, eliminating the problem of fragmented management of different types of databases. Furthermore, by dynamically configuring and prioritizing the judgment threshold for slow query statements, different types of databases, different application systems, and different time periods can each adopt judgment standards that are consistent with their actual business, avoiding misjudgment and omission of slow query statements under a uniform fixed threshold.

[0053] Step S204: Match the corresponding collection strategy to each target database according to the database type, and collect data for each target database according to the collection strategy and the threshold for slow query statements to obtain the target original records.

[0054] The collection strategy refers to a specific execution plan tailored to a particular database type, used to obtain slow query information from that type of database. Because different database types have fundamental differences in their slow query monitoring mechanisms, access interfaces, and log formats, each database type can have its own independent collection strategy. The target raw record refers to the raw slow query data obtained from the target database through the collection strategy. Each target raw record can contain the query text, execution duration, execution timestamp, executing user, and execution plan information. The execution plan information describes the specific execution path selected by the database optimizer when executing the query, and may include data scanning methods, table join algorithm types, etc.

[0055] For example, the server can traverse the target database records based on the target database configuration information set. For each target database, it queries a pre-maintained collection strategy registry using its database type as the key, and obtains the collection strategy corresponding to that target database from the collection strategy registry. The collection strategy registry can be stored with the database type as the key and the corresponding collection strategy implementation as the value. The collection strategies corresponding to each database type are independent of each other, and the execution logic of each collection strategy does not affect each other.

[0056] When it's necessary to add a new collection strategy for a specific database type, the server only needs to register a new collection strategy in the collection strategy registry, without modifying any logic of existing collection strategies. This allows the entire collection framework to flexibly extend to new database types. After matching the corresponding collection strategy, the server can inject the target database's network address, port number, access credentials, instance identifier, and slow query threshold into the collection strategy. The collection strategy then drives the server to execute specific data collection operations to obtain the target raw records.

[0057] Furthermore, the server can perform collection operations using a collection method adapted to the matched collection strategy type of the database. On one hand, the server can establish a direct connection to the target database, using a slow query statement threshold as a filtering condition, to directly query the target database's built-in slow query monitoring view or system table. This filters out internal management statements generated by the database system itself, retains query statements generated by the business application, and retrieves query results in batches using a paginated approach, avoiding excessive performance pressure on the target database from a single large collection volume. On the other hand, the server can also obtain slow query statement information by subscribing to a message queue. A log collection component deployed on the target database server continuously monitors the slow query log files written to the target database, pushing newly generated log entries to the message queue in real time. The server can continuously subscribe to the message queue and consume slow query log entries from it, obtaining the target's original records in real time.

[0058] For example, for Oracle databases, the server can perform data collection using a view-based query strategy. The server can establish a database access connection using network address, port number, access credentials, and instance identifier. It can then use the Structured Query Language (SQuery Language) view `sql_monitor` to automatically monitor queries, filtering for business query records whose execution time exceeds the threshold. These records are then retrieved in batches using pagination to obtain the target raw records for the Oracle database. For MySQL databases, the server can perform data collection using a log-based strategy. The target MySQL database server has enabled slow query logging and configured the log file path. A Filebeat instance deployed on this server continuously monitors for new content in the slow query log file, pushing newly generated slow query log entries to a Kafka message queue in real time. The server can continuously subscribe to this Kafka message queue, consuming slow query log entries to obtain the target raw records for the MySQL database.

[0059] Step S206: Extract the principal components of the statements in the target original record, and obtain the flag feature values ​​based on the principal components of the statements.

[0060] For example, the server can perform formatted preprocessing on the query statement text in the target original record; perform parameterization processing on the formatted preprocessed query statement text, replacing literal values, string constants, and date constants in the query statement text with placeholders to obtain a query statement template; perform structured parsing on the query statement template to extract the principal components of the query statement template; wherein, the principal components of the statement can reflect the structural characteristics of the statement, and are used to remove the specific parameter values ​​that change with each execution in the query statement, retaining the essential characteristics of the query statement at the logical structure level. The principal components of the statement may include at least one of operation type, data table identifier, filter condition structure, table join method, and function type.

[0061] Among them, operation type refers to the type of database operation performed by the query statement, such as query, delete, update, etc.; table identifier refers to the name of the data table involved in the query statement; filter condition structure refers to the combination of condition fields and their logical relationship used to filter data in the query statement; table association method refers to the association type and associated fields between tables in a multi-table query; function type refers to the type of built-in database function called in the query statement.

[0062] Furthermore, the server can perform principal component extraction on the execution plan information in the target original record to obtain the execution plan principal components. The execution plan principal components are extracted from the execution plan information generated by the database optimizer and can reflect the actual execution path characteristics of the query statement. The execution plan principal components may include at least one of the following: data scanning method, table join algorithm type, and number of table lookup operations.

[0063] Among them, data scanning method refers to the way the database reads data when executing a query, such as full table scan or index scan and the index identifier used; table join algorithm type refers to the join algorithm selected by the database optimizer when joining multiple tables, such as nested loop join, hash join or sort merge join; table lookup operation number refers to the number of operations that can be performed after locating data through the index and then returning to the main table to obtain the complete data row. The more table lookup operations, the more performance problems the query statement has due to insufficient index utilization efficiency.

[0064] Next, the server can merge the statement principal components and the execution plan principal components to obtain merged principal components. Feature calculations are then performed on the merged principal components to obtain a flag feature value. The flag feature value is a globally unique identifier generated after feature calculations on the statement principal components or merged principal components. It can be used to uniquely identify a class of slow query statements with the same structural characteristics within the system. The query statement template is obtained after deparameterizing the query statement text. It is a normalized statement structure that replaces all specific parameter values ​​with placeholders. Query statements with the same logical structure can generate completely identical query statement templates regardless of changes in their specific parameter values. The server can perform formatting preprocessing on the query statement text in the target original record, extract the statement principal components of the target original record, and obtain the flag feature value based on the statement principal components. The server can directly perform hash calculations on the entire formatted preprocessed query statement text, using the hash calculation result as the flag feature value to achieve fast and unique identification of the same query statement text.

[0065] For example, formatting preprocessing may include converting keywords in the query statement text to the same case, standardizing consecutive whitespace characters such as spaces, newlines, and tabs into single spaces, and removing line comments and block comments from the query statement text. This results in a query statement text with a uniform format, eliminating meaningless text differences introduced by different coding habits. The server can then perform parameterization on the formatted query statement text, replacing all literal values, string constants, and date constants in the query statement text with uniform placeholders to obtain a query statement template. Parameterization allows multiple query statements with the same logical structure but different query parameters to be merged into the same query statement template. For example, multiple query statements executed on the same data table with different account identifiers as conditions can all be merged into the same query statement template after parameterization, thereby converging the originally discrete, massive number of slow query statement records into a finite set of query statement templates.

[0066] For example, the server can input a query statement template into an SQL syntax parser, perform structured parsing on the query statement template, and extract the principal components of the statement from the parsing results. The principal components of the statement can include at least one of operation type, data table identifier, filter condition structure, table join method, and function type. Since query statements from different types of databases differ in syntactic details, the SQL syntax parser can uniformly parse query statement templates from various database types into a universal intermediate model independent of the database type, describing the components of the query statement with a unified data structure. This eliminates the differences in syntactic details between different types of databases, making query statements from different types of databases comparable at the principal component level. The server can also extract the principal components from the execution plan information in the target original record to obtain the execution plan principal components. The execution plan principal components can include at least one of data scanning method, table join algorithm type, and number of table lookup operations. The server can merge the extracted principal components of the statement with the execution plan principal components to obtain merged principal components. When performing feature calculations on the merged principal components, the server can serialize the merged principal components into a canonical string according to preset deterministic rules, calculate a hash value for the canonical string, and use the hash value as a flag feature value. Incorporating the principal components of the execution plan into the calculation of the flag feature value allows the same query template to be distinguished into different feature categories and tracked separately under different execution plans. This helps to discover performance issues caused by changes in the execution plan (such as degenerating from an index scan to a full table scan), and improves the accuracy of locating the root cause of slow query performance.

[0067] Furthermore, after obtaining the flag feature value, the server can use the flag feature value as an index to query whether there is an existing aggregate record with the same flag feature value in the system storage. If it does not exist, the server can create a new aggregate record, which includes the flag feature value, query statement template, and execution plan principal components, and initialize statistical fields, including the first execution time, most recent execution time, number of executions, total execution time, maximum single execution time, and minimum single execution time. If it already exists, the server can update the statistical fields of the existing aggregate record, increment the number of executions by one, include the current execution time in the total execution time, compare the current execution time with the recorded maximum and minimum single execution times respectively and update the extreme value, and update the most recent execution time to the timestamp of the current execution.

[0068] The above steps abstract query statements from different types of databases into unified statement principal components, and combine them with execution plan principal components to generate flag feature values ​​that take into account both statement structure characteristics and execution path characteristics. This achieves unified cross-database identification of slow query statements across multiple database types. The aggregation and update mechanism based on flag feature values ​​continuously merges the execution statistics of similar slow query statements into the same aggregation record, transforming the discrete massive original records into highly refined aggregation statistics. This allows the identification of slow query statements to shift from relying on manual, line-by-line investigation to precise location based on flag feature values, improving the identification efficiency and analysis accuracy of slow query statements in multi-type database environments.

[0069] Step S208: Based on the characteristic values ​​of each marker, data is collected from each target database to obtain data collection result information.

[0070] The data aggregation results may include a list of slow query statements. This data aggregation result information refers to the structured summary data output after aggregation processing, which may include the slow query statement list, rectification task records, and notification messages. The slow query statement list can be a structured list compiled by application system identifier, summarizing query statement templates and their execution statistics fields corresponding to various flag feature values ​​under that application system. It can also be a data view used by developers and database administrators to view, analyze, and manage slow query statements. Rectification task records refer to automatically generated management task entries for flag feature values ​​that appear for the first or second time in the slow query statement list. Each rectification task record includes a task identifier, flag feature value, query statement template, execution statistics fields, the identifier of the application system to which it belongs, and the corresponding responsible person information.

[0071] For example, the server can aggregate slow query data from each target database based on each aggregate record, using the corresponding flag feature value as an index, to obtain data aggregation results. The server can directly categorize and summarize each aggregate record by database type, merging aggregate records under the same database type into a slow query list for that type of database, allowing database administrators to view and analyze it uniformly by database type.

[0072] For example, the server can look up the application system identifier corresponding to the target database from the configuration information based on the network address and instance identifier of the target database used when collecting the target original records, and complete the application system identifier into the corresponding aggregate record; based on the application system identifier, the completed aggregate records are classified and summarized to obtain a list of slow query statements.

[0073] In some embodiments, such as Figure 3 As shown, the server can find the application system identifier corresponding to the target database from the configuration information based on the network address and instance identifier of the target database used when collecting the original target records. The application system identifier is then added to the corresponding aggregate record to complete the application system information for the aggregate record. This application system information completion adds clear business attribution information to each aggregate record, building upon the flag feature value, query statement template, and execution statistics fields. This establishes a link between the technical data of slow query statements and the business responsibility system.

[0074] Furthermore, the server can categorize all aggregate records based on the application system identifiers in the completed aggregate records, grouping those with the same application system identifier into the same group. Then, it summarizes the aggregate records within each group according to statistical fields such as total execution time and execution count, resulting in a slow query statement list based on the application system identifier. Within the slow query statement list, each application system corresponds to an independent slow query statement view. This view displays the query statement templates and their execution statistics fields for each characteristic value under that application system, including quantitative indicators such as execution count, total execution time, maximum single execution time, and minimum single execution time. This allows developers and database administrators to intuitively grasp the overall picture of slow queries for a given application system. The server can implement role-based access control for the slow query statement list; the responsible person for each application system can only access the slow query statement list under their respective application system identifier, ensuring data isolation between different application systems.

[0075] Furthermore, the server can persistently store aggregated records from various application systems by partitioning them according to the collection date. This supports querying and analyzing historical trends of slow query statements within any date range, enabling developers and database administrators to longitudinally compare the changing trends of the number and execution time of slow queries for a specific application system over different time periods. For the first appearance of a flag value in the slow query statement list of each application system, the server can automatically generate a corresponding rectification task record and send this record as a notification message to the responsible person for that application system. The notification message includes a summary of newly discovered slow query statements and the entry identifier for the rectification task. Figure 3 The development of a rectification list and notification process for swimming lanes ensures that slow query issues can be promptly communicated to the relevant responsible persons with the ability to handle them, thereby driving the initiation of subsequent rectification tracking processes.

[0076] Based on the aggregation at the application system level, such as Figure 4 As shown, the server can also support aggregated processing at the microservice call chain level to solve the problem of slow queries across multiple microservice nodes and various database types in a microservice architecture. In a microservice architecture, an external request starts from the entry gateway, passes through multiple microservice nodes sequentially, and each microservice node accesses its corresponding database to perform a query operation, ultimately returning a response. For example... Figure 4 As shown, a complete business request flows sequentially through the gateway to microservices A, B, and C. The SQL execution time for microservice A is two seconds, for microservice B it's two seconds, and for microservice C it's one second. While the execution time of individual microservice nodes does not exceed the slow query threshold, the total execution time of the entire chain reaches five seconds, exceeding the acceptable response time range for users. In this scenario, if slow query monitoring is only performed at the level of a single microservice node, the aforementioned chain-level performance issue will not be detected or alerted, creating a governance blind spot.

[0077] For the above scenario, the server can receive a pre-configured call chain definition for a specific business function. The call chain definition can include a chain identifier, node identifiers of each node in the chain, the database type, network address, instance identifier, and execution user corresponding to each node, as well as the execution order number of each node in the chain, the independent node-level execution time threshold for each node, and the chain-level execution time threshold for the entire chain. The server can match and associate each aggregate record with its corresponding node identifier based on the database type, network address, instance identifier, and execution user of each node in the call chain definition to obtain the associated aggregate records for each node. The server then sequentially accumulates the execution times in the associated aggregate records of each node according to the execution order number in the call chain definition to obtain the total chain execution time. The total chain execution time is compared with the chain-level execution time threshold. When the total chain execution time exceeds the chain-level execution time threshold, it is determined that the current chain has a chain-level slow query problem. The server can also simultaneously compare the execution time in the associated aggregate records of each node with the corresponding node-level execution time threshold, marking nodes that exceed the node-level execution time threshold as node timeout nodes. After determining that there is a slow query problem at the link level, the server can identify the node with the largest proportion of the execution time of each node as the performance bottleneck node from the proportion of the execution time of each node to the total execution time of the link. The node identifier of the performance bottleneck node is marked in the link-level rectification task record, generating a link-level rectification task record containing the link identifier, the execution time of each node and its proportion, the performance bottleneck node mark, and the total execution time of the link. The link-level rectification task record is then pushed to the corresponding responsible person along with the notification message.

[0078] The aforementioned slow query processing method applied to multiple database types responds to the collection task trigger command, acquires configuration information for multiple target databases, and initiates a collection process for multiple different types of target databases from a unified entry point. This integrates dispersed databases into a single collection system. The configuration information includes database type and slow query threshold. Based on the database type, a corresponding collection strategy is matched for each target database. Data is collected for each target database according to the collection strategy and slow query threshold, yielding the target raw records. The principal components of the statements in the target raw records are extracted, making slow queries from different database types comparable at the principal component level. A flag feature value is obtained based on the statement principal components, further converting each type of slow query into a unique feature identifier. This ensures that regardless of the database type from which the slow query originates, it can be identified and tracked using a unified flag feature value. Finally, based on each flag feature value, data from each target database is aggregated, yielding data aggregation results. Aggregating slow queries from multiple target databases based on the flag feature values ​​enables the summarization and analysis of slow queries from different database types under a unified dimension, reducing the cost of unified processing of slow queries in the database.

[0079] In one exemplary embodiment, such as Figure 5 As shown, the data collection result information includes a list of slow query statements; step S208 may also include steps S302 to S304. Wherein:

[0080] Step S302: Obtain the call chain definition information configured for the target business function.

[0081] The call chain definition information includes at least one of the following: the link identifier of the target business function, the node identifier of each node in the chain, the database type and network address corresponding to each node, the execution order number of each node in the chain, and the first execution time threshold corresponding to the chain. The call chain definition information can be manually configured by developers in advance on the slow query management platform for the target business function. The link identifier is a number or name used to uniquely identify a call chain within the system. The node identifier is a number or name used to uniquely identify a microservice node in the call chain. The first execution time threshold is the upper limit of the configured execution time for the entire call chain. When the sum of the execution times of all nodes in the chain exceeds the first execution time threshold, it can be determined that the chain has a chain-level slow query problem.

[0082] Reference Figure 4In a microservice architecture, a complete business request flows sequentially from the gateway entry point through multiple microservice nodes, each accessing its corresponding database to perform a query operation. For such cross-service and cross-database call scenarios, developers can pre-configure the call chain definition information for the target business function on a slow query management platform.

[0083] Step S304: Based on the call link definition information, match and associate each aggregation record with the corresponding node identifier to obtain the associated aggregation record of each node, and add the associated aggregation record to the data collection result information.

[0084] The call chain definition information may also include a second execution duration threshold for each node. This second execution duration threshold refers to the upper limit of the execution duration configured individually for a specific node in the call chain, used for independent monitoring of the execution duration of each node. Associated aggregation records are data entries obtained by matching and associating aggregation records with the corresponding node identifiers in the call chain definition information. In addition to the original aggregation record's flag feature values, query statement templates, and execution statistics fields, associated aggregation records can further carry link-dimensional information such as the link identifier, node identifier, and execution order number.

[0085] For example, the server can, based on the call chain definition information, traverse the node identifiers of each node in the chain. For each node, using the database type, network address, instance identifier, and execution user corresponding to that node as matching conditions, it searches the aggregate record set for aggregate records that match the above matching conditions. The successfully matched aggregate records are then associated with their corresponding node identifier, chain identifier, and execution sequence number to obtain the associated aggregate records for that node. The server can sequentially perform the above matching and association operations on each node in the call chain definition information to obtain the associated aggregate record set for all nodes in the chain.

[0086] Furthermore, the server can sequentially accumulate the execution times in the associated aggregation records of each node according to the execution order number of each node in the call link definition information to obtain the total execution time of this link. The server can compare the total execution time of the link with a first execution time threshold. When the total execution time of the link exceeds the first execution time threshold, it is determined that there is a link-level slow query problem in the current link. The server can determine the node with the largest proportion from the ratio of the execution time of each node's associated aggregation record to the total execution time of the link as the performance bottleneck node. The node identifier of the performance bottleneck node is marked in the link-level rectification task record, generating a link rectification task record containing the link identifier, the execution time of each node and its proportion, the performance bottleneck node mark, and the total execution time of the link.

[0087] For example, the server can compare the execution time in the associated aggregation record corresponding to each node with a second execution time threshold; mark nodes exceeding the second execution time threshold as timeout nodes and record them in the associated aggregation record. A timeout node is a node in the associated aggregation record whose execution time exceeds the second execution time threshold. The server can compare the execution time in the associated aggregation record corresponding to each node with the second execution time threshold separately, mark nodes whose execution time exceeds the second execution time threshold as timeout nodes, and record the timeout nodes in the corresponding associated aggregation record. The introduction of the second execution time threshold enables the server to independently mark and record nodes in the link whose execution time exceeds the threshold even when the total execution time of the link does not exceed the first execution time threshold and the overall link has not triggered a link-level slow query judgment, thereby providing early warning of potential node-level performance risks when the overall link performance is still acceptable.

[0088] Furthermore, the server can add the associated aggregation records of each node, along with timeout node markers, performance bottleneck node markers, and link-level rectification task records, to the data aggregation result information. This allows the data aggregation result information to simultaneously include aggregation data from both the application system dimension and the microservice call link dimension, forming a multi-dimensional comprehensive aggregation view of slow query statements from various database types. The server can push link-level rectification task records to the corresponding responsible persons in the form of notification messages. The notification messages include the time consumption distribution of each node in the link, performance bottleneck node information, and timeout node markers, guiding the responsible persons to prioritize the development of optimization plans for performance bottleneck nodes and timeout nodes.

[0089] The above steps, by matching and associating aggregated records with node identifiers in the call chain definition information and performing a chain accumulation judgment on the execution time of each node, extend the governance of slow query statements from a single database instance to the complete business chain across services and databases. This solves the governance blind spot problem in microservice architecture where the execution time of a single node does not exceed the threshold but the overall performance of the chain is substandard.

[0090] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0091] Based on the same inventive concept, this application also provides a device for processing slow query statements in multi-type databases, which implements the aforementioned method for processing slow query statements in multi-type databases. The solution provided by this device is similar to the implementation described in the above method. Therefore, the specific limitations of one or more embodiments of the device for processing slow query statements in multi-type databases provided below can be found in the limitations of the method for processing slow query statements in multi-type databases described above, and will not be repeated here.

[0092] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 6 As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and databases. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media to run. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements a slow query processing method applied to multiple types of databases.

[0093] Those skilled in the art will understand that Figure 6The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0094] In one exemplary embodiment, a chip is provided, which includes a processor and a data interface. The processor reads instructions stored in a memory through the data interface and is able to execute the steps in the above-described method embodiments.

[0095] In one embodiment, a computer device is also provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above method embodiments.

[0096] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.

[0097] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0098] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0099] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0100] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0101] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for handling slow query statements applied to multi-type databases, characterized in that, The method includes: In response to the task acquisition trigger command, configuration information of multiple target databases is obtained; the configuration information includes database type and slow query statement judgment threshold. According to the database type, a corresponding collection strategy is matched for each target database, and data is collected for each target database according to the collection strategy and the slow query statement determination threshold to obtain the target original records; Extract the principal components of the statements from the target original record, and obtain the flag feature values ​​based on the principal components of the statements; Based on the characteristic values ​​of each of the aforementioned markers, data is collected from each of the aforementioned target databases to obtain data collection result information.

2. The method according to claim 1, characterized in that, The extraction of the principal components of the target original record includes: The query statement text in the target original record is formatted and preprocessed; The pre-processed query text is deparameterized by replacing literal values, string constants, and date constants with placeholders to obtain the query template. The query statement template is subjected to structured parsing to extract the main components of the query statement template; the main components of the query statement include at least one of operation type, data table identifier, filter condition structure, table association method and function type.

3. The method according to claim 2, characterized in that, The extraction of the principal components of the target original record further includes: Principal component extraction is performed on the execution plan information in the target original record to obtain the execution plan principal components; the execution plan principal components include at least one of the data scanning method, table join algorithm type, and number of table lookup operations. Accordingly, obtaining the flag feature value based on the principal components of the statement includes: The principal components of the statements and the principal components of the execution plan are merged to obtain the merged principal components; The characteristics of the merged principal components are calculated to obtain the characteristic values.

4. The method according to any one of claims 1 to 3, characterized in that, The data aggregation results include a list of slow query statements; The step of collecting data from each target database based on each of the aforementioned flag feature values ​​includes: Based on the network address and instance identifier of the target database used when collecting the target original records, the application system identifier corresponding to the target database is found from the configuration information, and the application system identifier is completed into the corresponding aggregate record; Based on the application system identifier, the completed aggregate records are classified and summarized to obtain a list of slow query statements.

5. The method according to claim 4, characterized in that, The step of collecting data from each target database based on each of the aforementioned flag feature values ​​further includes: Obtain the call chain definition information configured for the target business function; the call chain definition information includes at least one of the following: the link identifier of the target business function, the node identifier of each node in the link, the database type and network address corresponding to each node, the execution order number of each node in the link, and the first execution duration threshold corresponding to the link; Based on the call chain definition information, each aggregated record is matched and associated with the corresponding node identifier to obtain the associated aggregated record of each node, and the associated aggregated record is added to the data collection result information.

6. The method according to claim 5, characterized in that, The call chain definition information also includes a second execution duration threshold for each node; the method further includes: Compare the execution time in the associated aggregated records corresponding to each node with the second execution time threshold; Nodes that exceed the second execution time threshold are marked as timeout nodes, and the timeout nodes are recorded in the associated aggregate record.

7. A chip, characterized in that, The chip includes a processor and a data interface. The processor reads instructions stored in the memory through the data interface and can execute the steps of the method according to any one of claims 1 to 6.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images