Structured query language tracking method, device and system in micro-service environment
By generating and sending structured query language tracking data in a microservice environment, the problem of difficulty in quickly locate SQL operations in traditional methods is solved, efficient operation and maintenance and performance optimization is achieved, and the reliability and stability of the system is improved.
Patent Information
- Application Number
- CN202510305866.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-03-14
- Publication Date
- 2025-07-04
AI Technical Summary
In the microservice architecture, traditional operation and maintenance and development methods are difficult to quickly locate SQL operations requested by specific users, resulting in low operation and maintenance efficiency and difficulty in accurately locate problems, affecting the normal operation of the business and user experience.
It provides a structured query language tracking method in a microservice environment. By generating program tracking context and tracking data, recording detailed time information, generating tracking data I and II, and sending them to the program tracking center, ensuring the integrity and consistency of the data, and realizing accurate calculation of the execution time of SQL calls and positioning performance bottlenecks.
It improves the efficiency of problem detection, reduces the time cost of development and operation and maintenance personnel in massive logs, reduces business losses caused by system failures and performance problems, and improves the reliability and stability of the system.
Smart Images
Figure CN120256177A_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of query language execution, and particularly relates to a structured query language tracking method, device and system in a microservices environment. Background Art
[0002] In today's microservices architecture system, the complexity of business systems has increased exponentially. An ordinary business function request often needs to span multiple microservice nodes deployed at different network locations, and a large number of SQL (Structured Query Language) statements are executed during the request processing.
[0003] Traditional operation and maintenance and development means mainly rely on viewing the running logs of applications to determine data operations and the involved data tables. However, this method exposes many drawbacks in a microservices environment.
[0004] On the one hand, the number of microservice nodes is huge, and each node generates a large amount of logs, making it extremely difficult to filter out useful information from the massive logs, and it is difficult to quickly locate the SQL operations related to specific user requests, resulting in extremely low operation and maintenance efficiency. On the other hand, when business data is abnormal or system performance deteriorates, due to the lack of systematic association and structured information in the logs, it is very difficult to accurately locate the problematic SQL statements through the logs, increasing the difficulty of problem troubleshooting and repair, and seriously affecting the normal operation of the business and user experience. Summary of the Invention
[0005] Aiming at the problem that traditional tracking methods are difficult to quickly locate SQL operations related to specific user requests and the operation and maintenance efficiency is extremely low, the present invention provides a structured query language tracking method, device and system in a microservices environment.
[0006] In a first aspect, the technical solution of the present invention provides a structured query language tracking method in a microservices environment. The microservices environment includes multiple microservice nodes, and the tracking method executed by each microservice node includes the following steps: S1: After receiving a user request, obtain the program tracking parameters of the current user; S2: Generate a program tracking context corresponding to the user request according to the program tracking parameters; the program tracking context is a structured data set for controlling tracking behavior and associating tracking data, including a tracking start state, a parent tracking data ID, and a tracking data ID; S3: When it is determined to start tracking according to the tracking start state, in response to the user request, generate tracking data for this user request, defined as tracking data I, and update the tracking data ID in the program tracking context according to the tracking data I; S4: Before making a Structured Query Language (SQL) call, create trace data II, and update the trace data ID in the program trace context again according to trace data II. At the same time, update the parent trace data ID in the program trace context according to trace data I. S5: After the SQL call is completed, update the trace data II according to the call operation end time to generate complete trace data. S6: Send the complete trace data to the program trace center. After the sending is completed, restore the trace data ID in the program trace context based on trace data I.
[0007] By creating and updating trace data before and after an SQL call and recording detailed time information (such as the relevant content in steps S3 - S5), the execution duration of the SQL call can be accurately calculated. By analyzing this trace data, developers can clearly understand the sequence and respective execution durations of multiple SQL calls during the HTTP request processing, so as to identify the performance bottlenecks and optimize them accordingly. For example, if it is found that a certain SQL call takes too long, the SQL statement or related database configuration can be optimized to improve the overall system performance.
[0008] As a structured data set, the program trace context effectively controls the tracing behavior and associates the trace data. This makes the trace data have good structure and systematicness, and is more convenient for management and analysis compared with the traditional logging method. At the same time, in steps S5 - S6, sending the complete trace data to the program trace center and restoring the trace context ensures the integrity and consistency of the data, providing a solid data foundation for subsequent data analysis and system optimization.
[0009] As a further limitation of the technical solution of the present invention, step S1 includes: After receiving a user request, call the interface of the program trace center to obtain the program trace parameters of the current user.
[0010] As a further limitation of the technical solution of the present invention, step S2 includes: Create a program trace context, and at the same time generate a trace user ID and save it in the program trace context; Judge whether the current user request enables tracing according to the program trace parameters; If so, update the trace enable status in the program trace context to true; If not, update the trace enable status in the program trace context to False.
[0011] As a further limitation of the technical solution of the present invention, each generated trace data includes multiple fields, namely trace user ID, trace node, parent trace data ID, trace data ID, request type, operation start time, and operation end time; In step S3, the step of updating the trace data ID in the program trace context according to trace data I includes: updating the trace data ID in the program trace context according to the trace data ID in trace data I.
[0012] As a further limitation of the technical solution of the present invention, in step S4, the step of updating the trace data ID in the program trace context again according to trace data II and simultaneously updating the parent trace data ID in the program trace context according to trace data I includes: Updating the trace data ID in the program trace context according to the trace data ID in the trace data II, and simultaneously updating the parent trace data ID in the program trace context according to the trace data ID in trace data I.
[0013] As a further limitation of the technical solution of the present invention, step S5 specifically includes: After the structured query language call is completed, record the call operation end time, and update the call operation end time to the operation end time field in the trace data II to generate complete trace data.
[0014] As a further limitation of the technical solution of the present invention, in step S6, the step of restoring the trace data ID in the program trace context based on trace data I includes: Restoring the trace data ID in the program trace context to the data trace ID in trace data I.
[0015] In a second aspect, the technical solution of the present invention further provides a structured query language tracing device in a microservices environment, including a parameter acquisition module, a program trace context generation module, a first trace data generation module, a second trace data generation module, a data update module, and a sending and processing module; The parameter acquisition module is configured to acquire the program trace parameters of the current user after receiving a user request; The program trace context generation module is configured to generate a program trace context corresponding to the user request according to the program trace parameters; the program trace context is a structured data set for controlling trace behavior and associating trace data, and includes a trace start state, a parent trace data ID, and a trace data ID; The first trace data generation module is used to determine to start tracing according to the trace start status, respond to a user request when starting tracing, generate trace data regarding this user request, defined as trace data I, and update the trace data ID in the program trace context according to trace data I at the same time; The second trace data generation module is used to create trace data II before a Structured Query Language (SQL) call, update the trace data ID in the program trace context again according to trace data II, and update the parent trace data ID in the program trace context according to trace data I at the same time; The data update module is used to update the trace data II to generate complete trace data according to the call operation end time after the Structured Query Language (SQL) call is completed; The sending processing module is used to send the complete trace data to the program trace center. After the sending is completed, restore the trace data ID in the program trace context based on trace data I.
[0016] As a further limitation of the technical solution of the present invention, the parameter acquisition module is specifically used to call the interface of the program trace center to obtain the program trace parameters of the current user after receiving a user request.
[0017] As a further limitation of the technical solution of the present invention, the program trace context generation module is used to create a program trace context, and generate a trace user ID and save it in the program trace context at the same time; determine whether to start tracing for this user request according to the program trace parameters; if so, update the trace start status in the program trace context to true; if not, update the trace start status in the program trace context to False.
[0018] As a further limitation of the technical solution of the present invention, each generated trace data includes multiple fields, namely trace user ID, trace node, parent trace data ID, trace data ID, request type, operation start time, and operation end time; The steps for the first trace data generation module to specifically update the trace data ID in the program trace context according to trace data I include: updating the trace data ID in the program trace context according to the trace data ID in trace data I.
[0019] As a further limitation of the technical solution of the present invention, the second trace data generation module is specifically used to update the trace data ID in the program trace context according to the trace data ID in the trace data II, and update the parent trace data ID in the program trace context according to the trace data ID in trace data I at the same time.
[0020] As a further limitation of the technical solution of the present invention, the data update module is specifically configured to record the end time of the call operation after the Structured Query Language (SQL) call is completed, and update the end time of the call operation to the operation end time field in the tracking data II to generate complete tracking data.
[0021] As a further limitation of the technical solution of the present invention, the sending and processing module is used to restore the tracking data ID in the program tracking context to the data tracking ID in the tracking data I.
[0022] In a third aspect, the technical solution of the present invention further provides a Structured Query Language (SQL) tracking system in a microservices environment, including a program tracking center and a number of microservice nodes distributed in the microservices environment. Each microservice node is installed with a tracking tool, and the tracking tool executes the tracking method described in the first aspect; The tracking tool of each microservice node is connected to the program tracking center; The user obtains the required tracking data by calling the query interface of the program tracking center.
[0023] As a further limitation of the technical solution of the present invention, the program tracking center parses the tracking data of each microservice node received, obtains the Structured Query Language (SQL) operation data, and organizes and generates an Excel file with the tracked metadata and the parsed Structured Query Language (SQL) operation data.
[0024] It can be seen from the above technical solutions that the present application has the following advantages: By generating a program tracking context and tracking data, in a microservices environment, each microservice node can accurately track user requests. In steps S1 - S6, starting from obtaining the user program tracking parameters, to generating various tracking data and sending them to the program tracking center, each link has clear records and identifiers, such as being associated through a unique tracking data ID and a parent tracking data ID. This enables developers to quickly locate the SQL operations executed by a specific user request on each microservice node, greatly improving the efficiency of problem troubleshooting.
[0025] This method changes the traditional mode that relies on a large amount of log analysis, reducing the time cost for developers and operators to find problems in a large amount of logs. Through the system's tracking and analysis mechanism, problems can be quickly located and solved, reducing business losses caused by system failures or performance issues, and overall reducing the system's operation and maintenance costs, and improving the reliability and stability of the system. Description of the Drawings
[0026] To more clearly illustrate the technical solutions of this application, the following will briefly introduce the attached drawings required for the description. Obviously, the attached drawings in the following description are only some embodiments of this application. For those of ordinary skill in the art, without creative efforts, other attached drawings can also be obtained based on these drawings.
[0027] Figure 1 It is a flowchart of the method provided by the embodiment of the present invention.
[0028] Figure 2 It is a connection block diagram of the device provided by the embodiment of the present invention.
[0029] Figure 3 It is a connection block diagram of the system provided by the embodiment of the present invention. Detailed implementation manners
[0030] To make the application purpose, features, and advantages of this application more obvious and understandable, the following will use specific embodiments and attached drawings to clearly and completely describe the technical solutions protected by this application. Obviously, the embodiments described below are only some embodiments of this application, not all embodiments. Based on the embodiments in this patent, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope protected by this patent.
[0031] As Figure 1 shown, the embodiment of the present invention provides a structured query language tracking method in a microservice environment. The microservice environment includes multiple microservice nodes, and the tracking method executed by each microservice node includes the following steps: S1: After receiving a user request, obtain the program tracking parameters of the current user; In this step, after receiving the user request, call the interface of the program tracking center to obtain the program tracking parameters of the current user.
[0032] S2: Generate a program tracking context corresponding to the user request according to the program tracking parameters; the program tracking context is a structured data set for controlling tracking behaviors and associating tracking data, including a tracking start status, a parent tracking data ID, and a tracking data ID; In this step, it specifically includes: creating a program tracking context, and at the same time generating a tracking user ID and saving it in the program tracking context; Judge whether the current user request enables tracking according to the program tracking parameters; If so, update the tracking start status in the program tracking context to true; If not, update the tracking start status in the program tracking context to False.
[0033] In the above two steps, the user operates the function interface, that is, sends a request. After receiving the user request, the microservice node calls the interface of the program tracing center to obtain the program tracing parameters of the current user; determines whether to start tracing for this request based on the received parameters and creates a program tracing context; the program tracing context includes the program tracing enable status, traced user ID, and traced data ID, in the form of {"enabled": true, "traceId": "ti10", "spanId": null}. If the program tracing needs to be started for this user request, the program tracing enable status in the program tracing context is true; when creating the program tracing context, a tracing ID is generated and saved in the program tracing context. This tracing ID is globally unique and is included in all the traced data created later. Each time traced data is created, the ID of the traced data is saved to the program tracing context.
[0034] S3: When it is determined to start tracing according to the tracing enable status, in response to the user request, traced data I about this user request is generated, and the traced data ID in the program tracing context is updated according to the traced data I; In step S3, the step of updating the traced data ID in the program tracing context according to the traced data I includes: updating the traced data ID in the program tracing context according to the traced data ID in the traced data I.
[0035] S4: Before making a Structured Query Language (SQL) call, traced data II is created, and the traced data ID in the program tracing context is updated again according to the traced data II, and the parent traced data ID in the program tracing context is updated according to the traced data I; specifically, the traced data ID in the program tracing context is updated according to the traced data ID in the traced data II, and the parent traced data ID in the program tracing context is updated according to the traced data ID in the traced data I.
[0036] When each component in the microservice node calls each other, it will record the program tracing information according to the tracing context, and the generated traced data is sent to the unified program tracing center. The program tracing data is a data set with a certain structure, including the tracing ID, parent traced data ID, traced data ID, start time, end time, etc., and has component-specific information for each type of component, such as DB TYPE, SQL, PARAMS, etc. for the database component. The program tracing center provides a query interface, and the traced data with type = sql can be obtained by calling the query interface.
[0037] S5: After the Structured Query Language call is completed, the traced data II is updated according to the end time of the call operation to generate complete traced data; specifically included in this step: After the Structured Query Language call is completed, record the end time of the call operation, and update the end time of the call operation to the operation end time field in the Trace Data II to generate complete trace data.
[0038] S6: Send the complete trace data to the program trace center. After the sending is completed, restore the trace data ID in the program trace context based on Trace Data I. Specifically, restore the trace data ID in the program trace context to the data trace ID in Trace Data I.
[0039] Create and update trace data before and after the SQL call, record detailed time information (such as in steps S3 - S5), and the execution duration of the SQL call can be accurately calculated. If it is found that a certain SQL call takes too long, the SQL statement or related database configuration can be optimized to improve the overall performance of the system.
[0040] The program trace context, as a structured data set, effectively controls the trace behavior and associates the trace data. At the same time, in steps S5 - S6, send the complete trace data to the program trace center and restore the trace context, ensuring data integrity and consistency, and providing a solid data foundation for subsequent data analysis and system optimization.
[0041] This example contains a microservice node (Node A). The user will call Node A to complete a certain function through operations. During this function call, a SQL call occurs inside Node A. The user and Node A communicate using the http protocol.
[0042] User a performs a business function operation on the browser. The browser responds to the user's operation and sends a user request to call Node A to complete a certain business function. Assume that during the business processing corresponding to this user request, a SQL call occurs inside Node A, and Node A also calls a certain function of Node B. After user a initiates a user request to Node A, since the user request contains Cookie:trace=1, Node A obtains the program trace parameters of user a from the program trace center and generates a program trace context corresponding to this user request, such as {{"enabled":true, "traceId": "ti10","spanId":null}. The above parameters indicate that program tracing is enabled for this request, the trace ID of this request is ti10, and there is currently no program trace data.
[0043] The user performs a specific business function operation on the browser. The browser responds to the user's operation, generates a user request containing the field "Cookie:trace=1", and sends it to Node A.
[0044] In the front-end page, use JavaScript to capture the user's operation events (such as clicking a button, submitting a form, etc.). When the event is triggered, use the XMLHttpRequest object or the modern fetch API to construct an HTTP request. Add the field "Cookie:trace=1" to the request header.
[0045] Node A receives the user request and identifies the field "Cookie:trace=1" in the request. In the back-end service of Node A, use the middleware or request handling mechanism provided by the framework (such as Spring Boot for Java, Express for Node.js, etc.) to obtain the request header. Based on this field, Node A obtains the program trace parameters corresponding to user a from the program trace center.
[0046] Based on the obtained parameters, Node A generates a program trace context for the user request. At this time, the initial value of the trace data ID is set to null. Node A starts to process the user request and generates trace data I for this HTTP request. Specifically, in response to the user request of user a, Node A generates a trace data I, and the trace data is as follows: { "tranceId": "ti10" "node": "Node A" "pSpanId": null "spanId": "1" "type": "http" "method": "GET" "startTime": 1591856949939 "endTime": null "httpCode": null } The meaning of the above data is that the trace data does not have a parent trace data ID (because this is the first piece of trace data), the ID of this trace data is 1, the type is an http request, the method of the http request is GET, the timestamp when the request is received is 1591856949939, the end time of this request is null (because the request has not ended yet), the response code of this request is null (because the request has not ended yet), and after this trace data is created, the trace data ID in the program trace context becomes 1.
[0047] According to the example, Node A will make an SQL call. Before the SQL call, a trace data II will be created. The trace data is as follows: { "tranceId": "ti10" "node": "Node A" "pSpanId": 1 "spanId": "2" "type": "sql" "statement": "select * from tableb" "startTime": 1591856949959 "endTime": null } In the above trace data, the parent trace ID is 1 (i.e., pSpanId). Since the HTTP request has not been completed when this trace data is created, the trace data created due to the HTTP request is the parent of this trace data. The type is an SQL call, the SQL statement is select * from tableb, the start timestamp is 1591856949959, and the end timestamp is null (because this SQL has not been completed). After this trace data is created, the trace data ID in the program trace context becomes 2.
[0048] When the SQL call is executed, Node A obtains the timestamp at the end of the SQL call. Specifically, in the database connection library that executes the SQL, the end timestamp is obtained by listening to events or getting the system time after execution. After the SQL call is completed, the end time in the trace data will be supplemented. Finally, the trace data for the SQL call is: { "tranceId": "ti10" "node": "Node A" "pSpanId": 1 "spanId": "2" "type": "sql" "statement": "select * from tableb" "startTime": 1591856949959 "endTime": 1591856949963 } At this point, this piece of trace data has been completed. This trace data will be sent to the program trace center, and the trace data ID in the program trace context is set to 1.
[0049] Based on the above trace data, the details are as follows: traceId: It is a globally unique identifier used to identify a complete request link. During the entire request processing, all related trace data is associated through this traceId, which facilitates accurately locating and concatenating all operation records related to a specific request among numerous requests in a complex distributed system. For example, ti10 represents the unique identifier of this request. No matter how many SQL calls or other operations occur subsequently, as long as the trace data is generated under this request, the traceId is ti10.
[0050] node: Records the name of the processing node corresponding to the current trace data. Here it is shown as "Node A", indicating that this trace data is related to the operations of Node A, which is convenient for locating the data source and processing location.
[0051] pSpanId: That is, the parent span identifier, used to establish the parent-child relationship between trace data. When pSpanId is null, as in the trace data I of the initial HTTP request, it means this is the starting point of the entire trace chain and there is no parent trace data. In the subsequent trace data of SQL calls, it will reference the spanId of the previous level (i.e., the trace data I corresponding to the HTTP request) to reflect the hierarchical relationship.
[0052] spanId: Is the unique identifier of each piece of trace data, used to distinguish operations in different stages under the same traceId. For example, the spanId of the trace data I corresponding to the HTTP request is "1". Each new trace data created before each subsequent SQL call will be assigned a new unique spanId value. For example, the spanId of the trace data created before the second SQL call may be "3", etc., to identify each independent operation record.
[0053] type: Represents the operation type corresponding to the trace data. Here, "http" clearly indicates that this piece of trace data is a record of operations related to the HTTP request. In the trace data of SQL calls, type will be set to "sql" to distinguish trace data of different operation types.
[0054] method: For the trace data of HTTP request types, record the method used in the HTTP request. "GET" in the example is a common HTTP request method. There are also "POST", "PUT", "DELETE", etc. By recording this field, the specific operation method of the request can be understood.
[0055] startTime: Record the timestamp when the corresponding operation starts. In the HTTP request trace data I, 1591856949939 represents the time when node A receives the HTTP request, accurate to milliseconds. In the trace data of SQL calls, startTime records the time when the SQL call starts, which is used to calculate the execution duration of subsequent operations.
[0056] endTime: Record the timestamp when the corresponding operation ends. When the request or operation is not completed, such as the HTTP request trace data I in the example, endTime is null. When the operation is completed, the value of this field will be filled. For example, after the SQL call is completed, the end time will be written into endTime. By subtracting startTime from endTime, the execution duration of this operation can be obtained.
[0057] httpCode: Used to record the response status code of the HTTP request. When the request is not completed, such as the HTTP request trace data I in the example, httpCode is null. When node A finishes processing the HTTP request and returns a response, the response status code (e.g., 200 indicates success, 404 indicates not found, etc.) will be recorded in this field, which is used to judge the processing result of the HTTP request.
[0058] Here, the tracking data I is mainly used to record the complete process information starting from the reception of an HTTP request initiated by the user at node A. Its "type" is "http", which clarifies that this is a tracking for an HTTP request. Among them, "traceId" is the unique identifier associated with the entire request process, ensuring that all relevant tracking data for this request can be accurately located in complex multi-request scenarios. "node" records that the request processing node is "node A", facilitating the positioning of the request processing location. "pSpanId" is null, indicating that it is the starting data in the entire tracking chain. "spanId" assigns it a unique number in the current request tracking, used to identify the uniqueness of this data within the tracking system. "method" records the HTTP request method (such as GET, POST, etc.), enabling developers to know the request type. "startTime" records the timestamp when the request is received, providing the starting time point for subsequent performance analysis. Although "endTime" and "httpCode" are initially null, positions are reserved to record the request end time and response code, so that these information can be completed when the request ends, thus presenting the entire process of the HTTP request from start to end, for analyzing the performance, processing duration, and response status of the HTTP request, etc.
[0059] As the starting point of the entire tracking chain, the "spanId" of the tracking data I will be referenced by the "pSpanId" of the subsequently created tracking data II, thereby establishing a parent-child relationship between the tracking data and constructing a complete request tracking link.
[0060] Tracking data II Tracking data II focuses on recording the SQL calls made inside node A. Its "type" is "sql", indicating that it is tracking SQL operations. It is also associated with the tracking data I of the HTTP request through "traceId" in the same request process. "pSpanId" references the "spanId" of tracking data I to establish a parent-child relationship, indicating that this SQL call is generated during the processing of the corresponding HTTP request. "spanId" gives a unique identifier to the SQL call tracking data. "statement" records the executed SQL statement in detail (such as "select * fromtableb"), so that developers can intuitively see the content of the executed database operation. "startTime" records the timestamp when the SQL call starts, which is used to calculate the execution time of the SQL operation. After the SQL call is completed, "endTime" is added to accurately calculate the time consumed by the SQL operation, providing key data for database performance optimization, such as determining whether there are problems such as slow queries.
[0061] Through tracing data II, developers can deeply analyze the performance of SQL calls in the entire business request processing, locate whether there are problems such as inefficiency in database operations, and perform targeted optimizations, unlike tracing data I which focuses on the overall HTTP request process.
[0062] In some embodiments, it should be noted that before each SQL call by node A, a new trace data is created according to the established rules. Similar to the trace data II created by the first SQL call, each new trace data has its own spanId for unique identification. For example, the trace data created before the second SQL call can have a spanId of "3", the third one of "4", and so on.
[0063] The pSpanId of the newly generated trace data will reference the spanId of the trace data I corresponding to the currently processed HTTP request. Because all these SQL calls occur in the process of processing the HTTP request, a clear parent-child relationship is established. This is like attaching each SQL call to the "trunk" of the HTTP request to which it belongs.
[0064] The traceId of the newly generated trace data remains consistent with the traceId of the HTTP request trace data I, which is used to associate all the trace data under the entire request process. The node field is still "Node A", the type is "sql", the startTime records the timestamp when each SQL call starts, and the statement records the specific SQL statement executed.
[0065] When each SQL call is completed, just like when the first SQL call is completed, the endTime field in the corresponding trace data will be supplemented to accurately record the end time of this SQL call for calculating the execution duration.
[0066] The completed trace data will be sent to the program trace center. After sending, the trace data ID in the program trace context will be restored to the spanId of the trace data I. This is because each SQL call is independently completed within the framework of the HTTP request. After completion, it has to return to the state dominated by the HTTP request trace data to prepare for the next possible SQL call or other trace operations.
[0067] Through the traceId, developers can associate all the HTTP request trace data I under the same user request and the trace data generated by multiple SQL calls. During analysis, they can understand the sequence of multiple SQL calls, their respective execution durations, and the comprehensive impact on the performance of the entire request from an overall perspective. For example, by comparing the startTime and endTime of different SQL calls, it can be determined whether there are performance bottlenecks caused by unreasonable SQL call sequences; by summarizing the durations of multiple SQL calls and comparing them with the total processing duration of the HTTP request, the performance proportion of database operations in the entire business process can be evaluated.
[0068] The HTTP request refers to the request sent by the user to Node A through the browser. The user performs business function operations on the browser, and the browser responds to this operation by sending a request containing "Cookie:trace=1" to Node A. Node A responds to the received request from the user browser and generates the corresponding trace data I. It is not the response request returned by Node A to the user after processing the user request, but the trace data record created by Node A for the original request initiated by the user during the process of processing the original request.
[0069] Such as Figure 2As shown in the figure, an embodiment of the present invention further provides a Structured Query Language (SQL) tracking device in a microservices environment, including a parameter acquisition module, a program tracking context generation module, a first tracking data generation module, a second tracking data generation module, a data update module, and a sending and processing module; The parameter acquisition module is configured to acquire the program tracking parameters of the current user after receiving a user request; The program tracking context generation module is configured to generate a program tracking context corresponding to the user request according to the program tracking parameters; the program tracking context is a structured data set for controlling tracking behaviors and associating tracking data, including a tracking start status, a parent tracking data ID, and a tracking data ID; The first tracking data generation module is configured to, when determining to start tracking according to the tracking start status, in response to the user request, generate tracking data for this user request, defined as tracking data I, and at the same time update the tracking data ID in the program tracking context according to tracking data I; The second tracking data generation module is configured to create tracking data II before a Structured Query Language call, update the tracking data ID in the program tracking context again according to tracking data II, and at the same time update the parent tracking data ID in the program tracking context according to tracking data I; The data update module is configured to, after the Structured Query Language call is completed, update the tracking data II according to the call operation end time to generate complete tracking data; The sending and processing module is configured to send the complete tracking data to the program tracking center, and after the sending is completed, restore the tracking data ID in the program tracking context based on tracking data I.
[0070] In some embodiments, the parameter acquisition module is specifically configured to, after receiving a user request, call the interface of the program tracking center to acquire the program tracking parameters of the current user.
[0071] The program tracking context generation module is configured to create a program tracking context, and at the same time generate a tracking user ID and save it in the program tracking context; determine whether the current user request starts tracking according to the program tracking parameters; if so, update the tracking start status in the program tracking context to true; if not, update the tracking start status in the program tracking context to False.
[0072] Each generated tracking data includes multiple fields, namely a tracking user ID, a tracking node, a parent tracking data ID, a tracking data ID, a request type, an operation start time, and an operation end time; The first trace data generation module, specifically the steps of updating the trace data ID in the program trace context according to the trace data I include: updating the trace data ID in the program trace context according to the trace data ID in the trace data I.
[0073] The second trace data generation module is specifically used to update the trace data ID in the program trace context according to the trace data ID in the trace data II, and at the same time update the parent trace data ID in the program trace context according to the trace data ID in the trace data I.
[0074] The data update module is specifically used to record the end time of the call operation after the Structured Query Language call is completed, and update the end time of the call operation to the operation end time field in the trace data II to generate complete trace data.
[0075] The sending processing module is used to restore the trace data ID in the program trace context to the data trace ID in the trace data I.
[0076] As Figure 3 shown, an embodiment of the present invention further provides a Structured Query Language trace system in a microservice environment, including a program trace center and several microservice nodes distributed in a microservice environment. Each microservice node is installed with a trace tool, and the trace tool executes the trace method described in the first aspect; The trace tool of each microservice node is connected to the program trace center; The user obtains the required trace data by calling the query interface of the program trace center.
[0077] The program trace center parses the trace data of each microservice node received, obtains the Structured Query Language operation data, and organizes and generates an Excel file with the traced metadata and the parsed Structured Query Language operation data.
[0078] It should be noted that the trace tool is provided for use in the form of a jar package. Before use, the user needs to deploy it to the environment (here the environment is the microservice node in the microservice architecture). After successful deployment, when logging in to iGIX, the SQL capture tool menu will be seen. Enter the SQL capture tool menu and click Start to start the tracing.
[0079] The specific tracking steps are the steps of the method described in the above embodiments. Click the page stop button to stop tracking. After simultaneous tracking, the tracking data will be sent to the program tracking center, where the JSON-formatted tracking data is parsed, the SQL statements inside are extracted, and the SQL third-party toolkit (jsqlparser-4.3.jar) is referenced to analyze the SQL operations in the SQL statements to distinguish insert, delete, and update operations. The metadata obtained by tracking and the parsed SQL operation data are finally sorted out to generate an Excel file. The Excel data file contains data columns such as requestUrl (request), HTTP method, number of SQLs, elapsed time (milliseconds), original SQL statement, related tables, related database operations, etc.
[0080] It should be noted that when the tracking method is encapsulated into a tracking tool, a jar is generated through the compilation and packaging tool of the IDEA integrated development tool, and a function menu is created on the igix product, associating with the SQL tracking page.
[0081] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to these embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A structured query language tracking method in a microservices environment, characterized in that, The microservice environment includes multiple microservice nodes, and the tracing method executed by each microservice node includes the following steps: S1: After receiving a user request, obtain the program tracing parameters of the current user; S2: Generate a program tracing context corresponding to the user request according to the program tracing parameters; the program tracing context is a structured data set used to control tracing behavior and associate tracing data, including a tracing start status, a parent tracing data ID, and a tracing data ID; S3: When it is determined to start tracing according to the tracing start status, in response to the user request, generate the tracing data of the user request, defined as tracing data I, and at the same time update the tracing data ID in the program tracing context according to tracing data I; S4: Before making a Structured Query Language (SQL) call, create tracing data II, and update the tracing data ID in the program tracing context again according to tracing data II, and at the same time update the parent tracing data ID in the program tracing context according to tracing data I; S5: After the SQL call is completed, update the tracing data II according to the call operation end time to generate complete tracing data; S6: Send the complete tracing data to the program tracing center. After the sending is completed, restore the tracing data ID in the program tracing context based on tracing data I.
2. The structured query language tracking method in a microservices environment according to claim 1, wherein Step S1 includes: After receiving a user request, call the interface of the program tracing center to obtain the program tracing parameters of the current user.
3. The structured query language tracking method in a microservices environment according to claim 2, characterized in that Step S2 includes: Create a program tracing context, and at the same time generate a tracing user ID and save it in the program tracing context; Judge whether to start tracing for the current user request according to the program tracing parameters; If so, update the tracing start status in the program tracing context to true; If not, update the tracing start status in the program tracing context to False.
4. The structured query language tracking method in a microservices environment according to claim 3, wherein Each generated tracing data includes multiple fields, namely tracing user ID, tracing node, parent tracing data ID, tracing data ID, request type, operation start time, and operation end time; In step S3, the step of updating the tracing data ID in the program tracing context according to tracing data I includes: updating the tracing data ID in the program tracing context according to the tracing data ID in tracing data I.
5. The structured query language tracking method in a microservices environment according to claim 4, characterized in that In step S4, the step of updating the tracing data ID in the program tracing context again according to tracing data II, and at the same time updating the parent tracing data ID in the program tracing context according to tracing data I includes: Update the tracing data ID in the program tracing context according to the tracing data ID in the tracing data II, and at the same time update the parent tracing data ID in the program tracing context according to the tracing data ID in tracing data I.
6. The structured query language tracking method in a microservice environment according to claim 5, characterized in that, Step S5 specifically includes: After the SQL call is completed, record the call operation end time, and update the call operation end time to the operation end time field in the tracing data II to generate complete tracing data.
7. The method for tracking structured query language in a microservices environment according to claim 6, characterized in that, In step S6, the step of restoring the tracing data ID in the program tracing context based on tracing data I includes: Restore the trace data ID in the program trace context to the data trace ID in Trace Data I.
8. A Structured Query Language tracing device in a microservices environment, characterized in that, It includes a parameter acquisition module, a program trace context generation module, a first trace data generation module, a second trace data generation module, a data update module, and a sending and processing module; The parameter acquisition module is used to acquire the program trace parameters of the current user after receiving a user request; The program trace context generation module is used to generate a program trace context corresponding to the user request according to the program trace parameters; The program trace context is a structured data set for controlling trace behavior and associating trace data, including a trace start state, a parent trace data ID, and a trace data ID; The first trace data generation module is used to, when determining to start tracing according to the trace start state, respond to the user request, generate trace data regarding this user request, defined as Trace Data I, and update the trace data ID in the program trace context according to Trace Data I; The second trace data generation module is used to create Trace Data II before a Structured Query Language (SQL) call, update the trace data ID in the program trace context again according to Trace Data II, and update the parent trace data ID in the program trace context according to Trace Data I; The data update module is used to update the Trace Data II to generate complete trace data according to the call operation end time after the SQL call is completed; The sending and processing module is used to send the complete trace data to the program trace center. After the sending is completed, restore the trace data ID in the program trace context based on Trace Data I.
9. A Structured Query Language tracing system in a microservices environment, characterized in that, It includes a program trace center and several microservice nodes distributed in a microservice environment. Each microservice node is installed with a tracing tool, and the tracing tool executes the SQL tracing method according to any one of claims 1-7; The tracing tool of each microservice node is connected to the program trace center; The user obtains the required trace data by calling the query interface of the program trace center.
10. The structured query language tracking system in a microservices environment according to claim 9, wherein The program trace center parses the trace data of each microservice node received, obtains the SQL operation data, and organizes the traced metadata and the parsed SQL operation data to generate an Excel file.