Database query performance optimization method and device, equipment and medium

Through stream computing technology, the frequency of database slow query statements is identified in real time and the query position is adjusted, which solves the problems of manual judgment error and cache pressure in the existing technology, and improves database query performance and system reliability.

CN118916383APending Publication Date: 2024-11-08INSPUR GENERSOFT CO LTD

Patent Information

Application Number
CN202410986306.2
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-22
Publication Date
2024-11-08

AI Technical Summary

Technical Problem

In the prior art, the processing methods of database slow query statements rely on manual judgment, which has judgment errors and lags, and directly storing all slow query data to the cache increases cache pressure, affecting query efficiency and system reliability.

Method used

The flow calculation technology is used to determine the query frequency based on the time window and the timestamp of the slow query statement, and the query position of the query data is identified and adjusted in real time, avoiding the lag of manual judgment and cache pressure, and performing corresponding operations in the cache or temporary table.

Benefits of technology

It improves the processing efficiency of database slow query statements, reduces excessive use of cache resources, avoids judgment errors and cache pressure, and improves the system's response speed and reliability.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118916383A_ABST
    Figure CN118916383A_ABST
Patent Text Reader

Abstract

The invention relates to the technical field of computers, and provides a database query performance optimization method and device, equipment and a medium. The method comprises the following steps: collecting a timestamp corresponding to a slow query statement with data change in a slow query log; determining a query frequency corresponding to the slow query statement based on a time window of stream calculation and a timestamp corresponding to the slow query statement; and in response to the received data request containing the slow query statement, determining the query position of the corresponding query data based on the query frequency corresponding to the slow query statement. According to the scheme, excessive occupation of cache resources is reduced as much as possible while the processing efficiency of the slow query statements of the database is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method, device, equipment and medium for optimizing database query performance. Background Art

[0002] The slow query log in the database management system is used to record query statements whose execution time exceeds the set threshold. The slow query statements in the slow query log will seriously affect the query efficiency of the database, the response speed of data query and the reliability of the system.

[0003] The existing method for processing slow query statements in the database is to place a monitoring tool in the database or cache and track the basic performance indicators of the database based on the monitoring tool. However, this method of identifying slow query statements by tracking the basic performance of the database relies heavily on manual judgment, which has large judgment errors and lags, affecting the processing efficiency of slow query statements. Another processing method is to directly store the query data corresponding to all slow query statements in the cache, which increases the cache pressure accordingly. Summary of the invention

[0004] In view of this, in order to solve the problem in the prior art of how to improve the processing efficiency of slow query statements in a database while minimizing excessive occupation of cache resources, the present disclosure provides a method, device, equipment and medium for optimizing database query performance.

[0005] According to a first aspect of the present invention, there is provided a method for optimizing database query performance, comprising: Collect the timestamps corresponding to the slow query statements where data changes occur in the slow query log; Determine the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement; In response to receiving a data request including the slow query statement, a query position of corresponding query data is determined based on a query frequency corresponding to the slow query statement.

[0006] In some embodiments, the step of determining the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement includes: Set the size of the time window for stream computing; Based on the size of the time window, all query statements with timestamps falling within the time window are collected and the number of collections corresponding to each query statement is counted to obtain the number of collections of the slow query statement within the time window; The query frequency corresponding to the slow query statement is calculated based on the number of collection times of the slow query statement in the time window and the size of the time window.

[0007] In some embodiments, the step of collecting timestamps corresponding to slow query statements in which data changes occur in the slow query log includes: Based on CDC technology, collect text fields where data is added / deleted / modified in slow query logs; Based on the text field where the data is added / deleted / modified, a corresponding slow query statement is matched and a timestamp corresponding to the slow query statement is obtained.

[0008] In some embodiments, the step of matching a corresponding slow query statement based on the text field where the data is added / deleted / modified and obtaining a timestamp corresponding to the slow query statement includes: The text fields where the data is added / deleted / modified are stored in the message queue in the order of collection; In response to the message queue receiving a query message, a corresponding text field is matched from all stored text fields based on the query message, and a corresponding slow query statement is matched based on the corresponding text field to obtain a timestamp when the slow query statement is executed.

[0009] In some embodiments, the step of determining the query position of the corresponding query data based on the query frequency corresponding to the slow query statement includes: In response to the query frequency being greater than the frequency threshold, performing a corresponding operation on the query data corresponding to the slow query statement based on a cache or a temporary table of the database; In response to the query frequency being not greater than the frequency threshold, a corresponding operation is performed on the query data corresponding to the slow query statement based on a non-temporary table of the database.

[0010] In some embodiments, in response to the query frequency being greater than the frequency threshold, the step of performing a corresponding operation on the query data corresponding to the slow query statement based on the cache or the temporary table of the database includes: In response to the query frequency being greater than the frequency threshold, it is determined that the slow query statement is a single data query mode or a list data query mode, and based on the determination result, it is confirmed that a corresponding operation is performed on the query data corresponding to the slow query statement based on the cache or the temporary table of the database.

[0011] In some embodiments, the step of confirming based on the judgment result that a corresponding operation is performed on the query data corresponding to the slow query statement based on the cache or the temporary table of the database includes: In response to the slow query statement being a single data query mode, confirming to perform a corresponding operation on the query data corresponding to the slow query statement based on the cache; In response to the slow query statement being a list data query mode, confirming to perform a corresponding operation on the query data corresponding to the slow query statement based on the temporary table of the database.

[0012] According to a second aspect of the present invention, a database query performance optimization device is provided, the device comprising: The first module is used to collect the timestamps corresponding to the slow query statements where data changes occur in the slow query log; The second module is used to determine the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement; The third module is used to determine the query position of the corresponding query data based on the query frequency corresponding to the slow query statement in response to receiving the data request containing the slow query statement.

[0013] According to a third aspect of the present invention, there is further provided an electronic device, the electronic device comprising: at least one processor; and The memory stores a computer program that can be run on the processor, and the processor executes the aforementioned database query performance optimization method when executing the program.

[0014] According to a fourth aspect of the present invention, a computer-readable storage medium is further provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the aforementioned database query performance optimization method is executed.

[0015] The above-mentioned method for optimizing database query performance determines the corresponding query frequency based on the time window of stream computing and the timestamp corresponding to the slow query statement where data changes occur, and determines the query location of the corresponding query data based on the query frequency corresponding to the slow query statement. This method improves the processing efficiency of slow query statements in the database while minimizing the excessive occupation of cache resources. Specifically, it dynamically identifies slow query statements in real time and makes corresponding data query adjustments, avoiding the lag of manual judgment and the risk of misjudgment, improving the processing efficiency of slow query statements in the database, and at the same time, avoiding putting all query data corresponding to slow query statements into the cache, reducing cache pressure.

[0016] In addition, the present invention also provides a database query performance optimization device, an electronic device and a computer-readable storage medium, which can also achieve the above-mentioned technical effects and will not be described in detail here. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0018] Figure 1 A flowchart of a method for optimizing database query performance provided by one embodiment of the present invention; Figure 2 A schematic flow chart of a method for optimizing database query performance based on a stream computing system provided by another embodiment of the present invention is shown; Figure 3 A flowchart of processing a slow query statement provided by another embodiment of the present invention; Figure 4 A schematic diagram of collecting timestamps corresponding to slow query statements based on a time window of stream computing provided by another embodiment of the present invention; Figure 5 A flowchart of a slow query statement for real-time collection of data changes based on CDC technology provided by another embodiment of the present invention; Figure 6 A schematic diagram of the structure of a database query performance optimization device provided by another embodiment of the present invention; Figure 7 is an internal structure diagram of an electronic device in another embodiment of the present invention; Figure 8 This is a structural diagram of a computer-readable storage medium in another embodiment of the present invention. DETAILED DESCRIPTION

[0019] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the embodiments of the present invention are further described in detail below in combination with specific embodiments and with reference to the accompanying drawings.

[0020] It should be noted that all expressions using "first" and "second" in the embodiments of the present invention are for distinguishing two non-identical entities with the same name or non-identical parameters. It can be seen that "first" and "second" are only for the convenience of expression and should not be understood as limitations on the embodiments of the present invention. The subsequent embodiments will not explain this one by one.

[0021] In one embodiment, please refer to Figure 1 As shown, the present invention provides a database query performance optimization method 100, specifically, comprising the following steps: Step 101, collecting the timestamps corresponding to the slow query statements in which data changes occur in the slow query log; Step 102: determine the query frequency corresponding to the slow query statement based on the time window of the stream calculation and the timestamp corresponding to the slow query statement; Step 103: in response to receiving a data request including the slow query statement, determining a query position of corresponding query data based on a query frequency corresponding to the slow query statement.

[0022] Stream computing is a real-time computing model for streaming data, which processes data immediately when it arrives, rather than waiting for all data to arrive before batch processing. Specifically, stream computing divides data into continuous, infinite data streams, which can continuously receive and process data without waiting for all data to arrive.

[0023] The above database query performance optimization method is conducive to realizing real-time dynamic identification of slow query statements that need to be optimized and making corresponding data query adjustments, avoiding the lag and risk of misjudgment of manual judgment. At the same time, only the query data of the slow query statements that need to be optimized are put into the cache, reducing the cache pressure and improving the processing efficiency.

[0024] In order to further understand the database query performance optimization method of the present invention, Figure 2 FIG. 1 is a flow chart of a method for optimizing database query performance based on a stream computing system according to another embodiment of the present invention. Figure 2 As shown, the database query performance optimization method of the stream computing-based system includes three parts: query control, core monitoring and processing, and optimization monitoring.

[0025] Specifically, for the query control part, when the system receives a query request from a user, it first determines whether the query data corresponding to the query request already exists in the cache. If it does, it will be directly queried from the cache and returned. Otherwise, it is necessary to query from the database based on the query request. Among them, the query statement in the database will be recorded in the slow query log if it exceeds the preset time. The slow query log records the detailed information of the slow query statement, including the execution timestamp, the lock timestamp, the query statement itself, the text field, the number of rows returned, etc.

[0026] Figure 3 FIG. 1 shows a flowchart of processing a slow query statement provided by another embodiment of the present invention. For the core monitoring and processing part, please Figure 2 Further reference based on Figure 3If the query statement corresponding to the user's query request is detected as slow SQL (Structured Query Language) based on the slow query log of the database, the slow SQL is stored in the system table. The system table is used to store the slow SQL table. The system table is monitored based on the CDC (Change Data Capture) technology to obtain data change operations in the database in real time, such as adding, modifying, and deleting operations. Based on the CDC technology, the text fields with data changes in the system table are collected, such as data addition, deletion, or modification, and stored in the message queue in the order of collection. Based on different query methods of slow SQL, the message queue is queried to obtain cache data from the cache or obtain temporary table data from the temporary table, so as to prepare for the subsequent query-based cache data or temporary table data and obtain the corresponding work order based on the analysis of slow SQL, so as to realize the operation and maintenance of slow SQL.

[0027] For the optimization monitoring part, please Figure 2 Further reference based on Figure 3 , set the trigger task for real-time dynamic collection of detailed information of slow SQL, and collect detailed information of slow SQL in real-time and dynamically according to the collection requirements. The collection content includes data fields, query conditions, storage methods, etc. If it is a single data query method, the collected content is put into the cache. If it is a list data query method, the collected content is added to the temporary table. Automatically generate pending work orders based on the information in the cache or temporary table, including detailed information of slow SQL, performance indicators, possible optimization suggestions, etc., so that users can understand the real-time situation of the corresponding slow SQL in a timely manner and perform corresponding query, analysis and optimization work. Pending work orders can be sent to users via email, SMS or system notification to operate and maintain slow SQL.

[0028] According to several embodiments of the present invention, the step of determining the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement includes: setting the size of the time window of stream computing; based on the size of the time window, collecting all query statements whose timestamps fall within the time window and counting the number of collections corresponding to each query statement to obtain the number of collections of the slow query statement within the time window; and calculating the query frequency corresponding to the slow query statement based on the number of collections of the slow query statement within the time window and the size of the time window.

[0029] The above method calculates the query frequency corresponding to the slow query statement with data change based on the time window of stream computing and the timestamp of the slow query statement with data change, and performs real-time monitoring on the slow query statement with data change, which is conducive to the subsequent timely acquisition of the slow query statement with query frequency exceeding the frequency threshold, and is conducive to the subsequent timely response to the slow query statement with query frequency exceeding the frequency threshold, thereby reducing the response time and improving the processing efficiency of the slow query statement.

[0030] In a specific example, Figure 4 FIG. 1 is a schematic diagram of collecting timestamps corresponding to slow query statements based on a time window of stream computing according to another embodiment of the present invention. Figure 4 As shown in the figure, the time window size of stream computing is set to 10s. When the timestamp of the first slow query statement is 2, a time window [0, 10) is created based on timestamp 2 and the query data of the slow query statement with timestamp 2 is stored. The query data of the subsequent slow query statements with timestamps falling in the time window [0, 10) are all stored. In other words, the query data of the slow query statements corresponding to timestamps 3, 5, and 9 are all stored. When a slow query statement with timestamp 11 is received, a time window [10, 20) is created and the query data of the slow query statement with timestamp 11 is stored. Similarly, when a slow query statement with timestamp greater than 20 is received, a time window [20, 30) is created and the query data of the slow query statement with the corresponding timestamp is stored. When the next time window is created, the previous time window is triggered to start calculation. The number of collections corresponding to each query statement in the time window is counted, and the query frequency corresponding to the slow query statement is calculated based on the number of collections corresponding to the slow query statement and the size of the time window.

[0031] According to several embodiments of the present invention, the step of collecting the timestamps corresponding to the slow query statements where data changes occur in the slow query log includes: collecting the text fields where data addition / deletion / modification occurs in the slow query log based on the CDC technology; matching the corresponding slow query statements based on the text fields where data addition / deletion / modification occurs and obtaining the timestamps corresponding to the slow query statements. Based on the CDC technology, it is possible to monitor the data changes in the slow query log in real time, obtain the text fields where data addition / deletion / modification occurs in a timely manner, and obtain the timestamps corresponding to the slow query statements, thereby improving the real-time recognition and response of slow query statements.

[0032] According to several embodiments of the present invention, the steps of matching a corresponding slow query statement based on a text field where data is added / deleted / modified and obtaining a timestamp corresponding to the slow query statement include: storing the text fields where data is added / deleted / modified in a message queue in the order of collection; in response to the message queue receiving a query message, matching a corresponding text field from all stored text fields based on the query message, and matching a corresponding slow query statement based on the corresponding text field to obtain a timestamp when the slow query statement is executed.

[0033] In a specific example, Figure 5 The flowchart of slow query statements that collect data changes in real time based on CDC technology is shown in Figure 2 Further reference based on Figure 5 , based on CDC technology, real-time monitoring of slow SQL statements with data changes is performed, and the statements are stored in the message queue. Based on different query methods of slow SQL statements, the corresponding query data is stored in different storage locations. If it is a single data query method, the query data corresponding to the slow SQL statement is stored in the cache medium, and the user queries the corresponding data from the cache medium; if it is a list data query method, the query data corresponding to the slow SQL statement is stored in the temporary table of the database, and the user directly queries the corresponding data from the temporary table. The corresponding slow query statements in the message queue are matched based on different text fields, and the corresponding timestamps are obtained based on the detailed information of the slow query statements. For example, if a text field with increased data is collected, a query message is sent to the message queue. The message queue matches the corresponding text field from all stored text fields based on the query message, and matches the corresponding slow query statement based on it, and obtains the corresponding timestamp from the detailed information of the slow query statement.

[0034] According to several embodiments of the present invention, the step of determining the query location of corresponding query data based on the query frequency corresponding to the slow query statement includes: in response to the query frequency being greater than the frequency threshold, performing corresponding operations on the query data corresponding to the slow query statement based on the cache or the temporary table of the database; in response to the query frequency being not greater than the frequency threshold, performing corresponding operations on the query data corresponding to the slow query statement based on the non-temporary table of the database. Among them, the corresponding operations performed on the query data corresponding to the slow query statement include add / modify / delete operations, etc. Based on the comparison result of the query frequency and the frequency threshold, confirming that the query data corresponding to the slow query statement is performed based on the cache or the temporary table of the database or the non-temporary table of the database, it is possible to realize real-time judgment on whether the slow query statement is a query statement that needs to be optimized, rather than directly treating all slow query statements as the same, which is conducive to the real-time dynamic identification of slow query statements and making corresponding data query adjustments.

[0035] For a specific example, see Figure 2-5, based on the time window of stream computing and the corresponding timestamp of the slow query statement, the query frequency corresponding to the slow query statement is obtained, and based on the predefined rules, whether the slow SQL is the SQL to be optimized is determined, wherein the predefined rules include setting the query frequency to exceed the frequency threshold, or the query time to exceed the time threshold and the query frequency to exceed the frequency threshold, etc. For example, through the real-time comparison of the query frequency and the frequency threshold, it is determined in real time whether the slow query statement is the query statement to be optimized. If the query frequency is greater than the frequency threshold, the slow query statement is confirmed to be the query statement to be optimized, and the corresponding operation needs to be performed on the temporary table based on the cache or database; if the query frequency is not greater than the frequency threshold, it is confirmed that the slow query statement is not the query statement to be optimized, and there is no need to perform the corresponding operation on it based on the cache, and the corresponding operation is still performed based on the non-temporary table of the database.

[0036] According to several embodiments of the present invention, the step of performing corresponding operations on the query data corresponding to the slow query statement based on the cache or the temporary table of the database includes: in response to the query frequency being greater than the frequency threshold, judging that the slow query statement is a single data query mode or a list data query mode, and confirming based on the judgment result that the corresponding operation is performed on the query data corresponding to the slow query statement based on the cache or the temporary table of the database. Among them, the temporary table of the database is used to store temporary data or intermediate result sets, which is only valid during the current database session or transaction, and is automatically deleted after the session ends or the transaction is committed / rolled back, saving storage pressure and storage resources. Performing corresponding operations on the query data corresponding to the slow query statement based on the cache or the temporary table of the database can avoid frequently querying data from the persistent storage space of the database, reduce the query pressure of the database itself, and improve the processing efficiency of the slow query statement.

[0037] According to several embodiments of the present invention, the step of confirming that the query data corresponding to the slow query statement is executed based on the cache or the temporary table of the database based on the judgment result includes: in response to the slow query statement being a single data query mode, confirming that the query data corresponding to the slow query statement is executed based on the cache; in response to the slow query statement being a list data query mode, confirming that the query data corresponding to the slow query statement is executed based on the temporary table of the database. Executing corresponding operations on the query data corresponding to the slow query statement based on different query modes can be applicable to different data requirements and data sizes, which is conducive to improving the flexibility of slow query statement processing.

[0038] For a specific example, see Figure 2-5If the query mode of the slow SQL is a list query mode, the query data corresponding to the slow SQL is stored in a temporary table, and corresponding operations such as adding, deleting, and modifying are performed on the query data based on the temporary table. If the query mode of the slow SQL is a single query mode, the query data corresponding to the slow SQL is stored in the cache, and corresponding operations such as adding, deleting, and modifying are performed on the query data based on the cache. When the user's next data request is received, the query data corresponding to the slow SQL can be directly obtained from the cache or the database temporary table without obtaining the query data from the non-temporary table of the database, thereby improving the processing efficiency of the slow SQL.

[0039] In some embodiments, please refer to Figure 6 As shown, the present invention also provides a database query performance optimization device 200, the device comprising: The first module 201 is used to collect the timestamps corresponding to the slow query statements in which data changes occur in the slow query log; The second module 202 is used to determine the query frequency corresponding to the slow query statement based on the time window of the stream calculation and the timestamp corresponding to the slow query statement; The third module 203 is configured to determine, in response to receiving a data request including a slow query statement, a query position of corresponding query data based on a query frequency corresponding to the slow query statement.

[0040] It should be noted that each module in the above-mentioned database query performance optimization device can be implemented in whole or in part by software, hardware, or a combination thereof. Each of the above-mentioned modules can be embedded in or independent of a processor in an electronic device in the form of hardware, or can be stored in a memory in an electronic device in the form of software, so that the processor can call and execute operations corresponding to each of the above modules.

[0041] According to another aspect of the present invention, an electronic device is provided. The electronic device may be a server. Figure 7 As shown. The electronic device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the electronic device is used to provide computing and control capabilities. The memory of the electronic device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the electronic device is used to store data. The network interface of the electronic device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, the above-mentioned database query performance optimization method is implemented.

[0042] According to another aspect of the present invention, a computer readable storage medium is provided. Figure 8 As shown, a computer program is stored thereon, and when the computer program is executed by a processor, the above-mentioned database query performance optimization method is implemented.

[0043] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0044] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, 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 specification.

[0045] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A method for optimizing database query performance, characterized in that: The method comprises: Collect the timestamps corresponding to the slow query statements where data changes occur in the slow query log; Determine the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement; In response to receiving a data request including the slow query statement, a query position of corresponding query data is determined based on a query frequency corresponding to the slow query statement.

2. The method for optimizing database query performance according to claim 1, characterized in that: The step of determining the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement includes: Set the size of the time window for stream computing; Based on the size of the time window, all query statements with timestamps falling within the time window are collected and the number of collections corresponding to each query statement is counted to obtain the number of collections of the slow query statement within the time window; The query frequency corresponding to the slow query statement is calculated based on the number of collection times of the slow query statement in the time window and the size of the time window.

3. The method for optimizing database query performance according to claim 1, characterized in that: The step of collecting the timestamps corresponding to the slow query statements in which data changes occur in the slow query log includes: Based on CDC technology, collect text fields where data is added / deleted / modified in slow query logs; Based on the text field where the data is added / deleted / modified, a corresponding slow query statement is matched and a timestamp corresponding to the slow query statement is obtained.

4. The method for optimizing database query performance according to claim 3, characterized in that: The step of matching a corresponding slow query statement based on the text field where the data is added / deleted / modified and obtaining a timestamp corresponding to the slow query statement includes: The text fields where the data is added / deleted / modified are stored in the message queue in the order of collection; In response to the message queue receiving a query message, a corresponding text field is matched from all stored text fields based on the query message, and a corresponding slow query statement is matched based on the corresponding text field to obtain a timestamp when the slow query statement is executed.

5. The method for optimizing database query performance according to claim 1, characterized in that: The step of determining the query position of the corresponding query data based on the query frequency corresponding to the slow query statement includes: In response to the query frequency being greater than a frequency threshold, performing a corresponding operation on the query data corresponding to the slow query statement based on a cache or a temporary table of the database; In response to the query frequency being not greater than the frequency threshold, a corresponding operation is performed on the query data corresponding to the slow query statement based on a non-temporary table of the database.

6. The method for optimizing database query performance according to claim 5, characterized in that: The step of performing a corresponding operation on the query data corresponding to the slow query statement based on the cache or the temporary table of the database in response to the query frequency being greater than the frequency threshold comprises: In response to the query frequency being greater than the frequency threshold, it is determined that the slow query statement is a single data query mode or a list data query mode, and based on the determination result, it is confirmed that a corresponding operation is performed on the query data corresponding to the slow query statement based on the cache or the temporary table of the database.

7. The method for optimizing database query performance according to claim 6, characterized in that: The step of confirming, based on the judgment result, that a corresponding operation is performed on the query data corresponding to the slow query statement based on the cache or the temporary table of the database includes: In response to the slow query statement being a single data query mode, confirming to perform a corresponding operation on the query data corresponding to the slow query statement based on the cache; In response to the slow query statement being a list data query mode, confirming to perform a corresponding operation on the query data corresponding to the slow query statement based on the temporary table of the database.

8. A database query performance optimization device, characterized in that: The device comprises: The first module is used to collect the timestamps corresponding to the slow query statements where data changes occur in the slow query log; The second module is used to determine the query frequency corresponding to the slow query statement based on the time window of stream computing and the timestamp corresponding to the slow query statement; The third module is used to determine the query position of the corresponding query data based on the query frequency corresponding to the slow query statement in response to receiving the data request containing the slow query statement.

9. An electronic device, characterized in that: include: at least one processor; as well as A memory storing a computer program executable in the processor, wherein the processor executes the method for optimizing database query performance according to any one of claims 1 to 7 when executing the program.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the database query performance optimization method according to any one of claims 1 to 7 is executed.

Citation Information

Patent Citations

  • Database query optimization method and equipment

    CN104765731A

  • Caching method, query method, caching apparatus and query apparatus for database data

    CN105354193A

  • Data processing method and device, electronic equipment and storage medium

    CN112084210A

  • Data query method, middleware, electronic device and storage medium

    CN113553339A

  • A slow query operation processing method and device

    CN114036179A

Cited By

  • Database management method and device

    CN121009116A