A database maximum value query method and device
By optimizing the maximum value query method of the database and using index scan records for marking and filtering, the problem of low query efficiency in the existing technology when the filter field is not an index field is solved, and a more efficient maximum value query is achieved.
Patent Information
- Application Number
- CN202010630358.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-07-03
- Publication Date
- 2025-01-24
- Estimated Expiration
- 2040-07-03
AI Technical Summary
In the prior art, when the filtering field is not an index field, when finding the maximum or minimum value of the index field, the query efficiency is low and the index field cannot be effectively utilized, resulting in full table scanning, which takes a long time.
By judging whether the filter field is an index field, if not, mark the index scan record associated with the index field to obtain the index scan record for the most value query, and when executing the structured query statement SQL of the most value query, Filter using index fields.
It significantly reduces the time required to query the maximum value command, avoids full table scans, reduces IO consumption, and avoids multiple reads and writes of memory caused by calculating the maximum value.
Smart Images
Figure CN113886414B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of databases, and in particular to a database maximum value query method and device. Background Art
[0002] Database services often require fast querying of the maximum value of data in a table under a certain condition. For relational databases such as MySQL and Oracle, the filtering conditions will affect the data search strategy: when the filtering field is an index, at least the index or other more efficient query strategies are used, and the query efficiency is high; however, when the filtering field is not an index, the query strategy is Figure 1 As shown, it is a full table scan, which takes a long time and has low query efficiency. It cannot effectively utilize the index field, and reads and writes the memory multiple times during the calculation of the maximum value.
[0003] When relational databases such as MySQL and Oracle seek the maximum value (MAX) and minimum value (MIN) of an index field when the filter field is not an index field, they do not effectively use all the information of the index field and perform a full table scan, resulting in low efficiency. The SQL (Structured Query Language) statement type corresponding to this scenario is: "SELECT MAX (index field) / MIN (index field) FROM table_name WHERE non-index field = constant", where other types of comparisons can be used between non-index fields and constants in the filter conditions. The present invention starts from this point and solves the problem of low efficiency when seeking the maximum value function in this scenario. Summary of the invention
[0004] The purpose of the present invention is to provide a database maximum value query method and device, which is used to solve the technical problem of low efficiency in finding the maximum value function in the scenario of finding the maximum value of the index field when the filter field is not the index field in the prior art.
[0005] According to a first aspect of the present invention, there is provided a database maximum value query method, comprising:
[0006] When performing a maximum value query, determine whether the filter field is an index field;
[0007] If it is determined that the filter field is not an index field, the index scan record associated with the index field is marked to obtain the index scan record for the maximum value query;
[0008] After obtaining the index scan record for the maximum value query, executing the structured query statement SQL for the maximum value query to query the maximum value;
[0009] If it is determined that the filter field is an index field, the index field is used for filtering and the maximum value is queried when the structured query statement SQL is executed.
[0010] Preferably, executing the structured query statement SQL for the maximum value query includes: for the maximum value function in the structured query statement SQL, scanning and filtering the index scanning records for the maximum value query until a record that meets the condition is found and then scanned.
[0011] Preferably, for the maximum value function in the structured query statement SQL, scanning and filtering the index scan records for the maximum value query includes:
[0012] Determine whether the maximum value function in the structured query statement SQL is the MAX function;
[0013] If it is determined that the maximum value function in the structured query statement SQL is the MAX function, scan forward from the last record of the index scan record to obtain each first scan record;
[0014] Use the WHERE condition in the structured query statement SQL to filter each scanned record until a record that meets the condition is found.
[0015] Preferably, for the maximum value function in the structured query statement SQL, scanning and filtering the index scan records for the maximum value query includes:
[0016] Determine whether the maximum value function in the structured query statement SQL is the MIN function;
[0017] If it is determined that the maximum value function in the structured query statement SQL is the MIN function, then scan backward from the first record of the index scan record to obtain each second scan record;
[0018] Each second scan record obtained is filtered using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
[0019] The above method of the present invention further includes monitoring a query statement for finding the maximum value before determining whether the filter field is an index field.
[0020] Preferably, the monitoring of the query statement for the maximum value includes: when the relational database monitors the connection request from the client, creating a link to receive the request of the structured query statement SQL; parsing the structured query statement SQL of the request to obtain the maximum value query statement, so as to perform the maximum value query.
[0021] According to a second aspect of the present invention, there is provided a database maximum value query device, comprising:
[0022] A judgment module is used to judge whether the filter field is an index field when performing a maximum value query;
[0023] A marking module, used for obtaining index scan records for maximum value query by marking index scan records associated with the index field when it is determined that the filter field is not an index field;
[0024] The query module executes the structured query statement SQL of the maximum value query after obtaining the index scan record for the maximum value query; and executes the structured query statement SQL according to the index field filtering method.
[0025] Preferably, the query module scans and filters the index scan records used for the maximum value query according to the maximum value function in the structured query statement SQL until a record satisfying the condition is found and then scanned.
[0026] Preferably, the query module scans and filters the index scan records used for the maximum value query for the maximum value function in the structured query statement SQL, including: determining whether the maximum value function in the structured query statement SQL is the MAX function; if the maximum value function in the structured query statement SQL is determined to be the MAX function, scanning forward from the last record of the index scan record to obtain each first scan record; and filtering each scan record obtained by using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
[0027] Preferably, the query module targets the maximum value function in the structured query statement SQL, and scanning and filtering the index scan record used for the maximum value query also includes: determining whether the maximum value function in the structured query statement SQL is the MIN function; if the maximum value function in the structured query statement SQL is determined to be the MIN function, scanning backward from the first record of the index scan record to obtain each second scan record; filtering each second scan record obtained by using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanning the record.
[0028] Compared with the prior art, the beneficial technical effects of the present invention are: 1) the time consumption of executing the query maximum value command is greatly reduced; 2) it is not necessary to read all data files to query the maximum value, and the IO consumption is greatly reduced; 3) it is avoided that multiple reading and writing of memory is caused by calculating the maximum value when a large number of filtering conditions are met.
[0029] The present invention will be described in detail below with reference to specific examples in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS
[0030] Figure 1 It is an execution flow chart of the maximum value query statement when the filter field in the prior art is a non-index field;
[0031] Figure 2A It is a schematic diagram of a database maximum value query method of the present invention;
[0032] Figure 2B It is a schematic diagram of a database maximum value query device of the present invention;
[0033] Figure 3 It is an execution flow chart of the non-indexed maximum value query statement of the filtering field of the present invention;
[0034] Figure 4 is the process of the optimizer module of the present invention;
[0035] Figure 5 It is the process of the execution module of the present invention to find the maximum value statement;
[0036] Figure 6 This is the process of the execution module of the present invention to find the minimum value statement
[0037] Figure 7 It is a schematic diagram of optimizing the maximum value query of the MySQL database of the present invention. DETAILED DESCRIPTION
[0038] Figure 2A A database maximum value query method of the present invention is shown, which comprises:
[0039] When performing a maximum value query, determine whether the filter field is an index field;
[0040] If it is determined that the filter field is not an index field, the index scan record associated with the index field is marked to obtain the index scan record for the maximum value query;
[0041] After obtaining the index scan record for the maximum value query, executing the structured query statement SQL for the maximum value query to query the maximum value;
[0042] If it is determined that the filter field is an index field, the index field is used for filtering and the maximum value is queried when the structured query statement SQL is executed.
[0043] Specifically, the structured query statement SQL for executing the maximum value query of the present invention includes: for the maximum value function in the structured query statement SQL, scanning and filtering the index scanning records used for the maximum value query until a record that meets the condition is found and then scanned.
[0044] The present invention targets the MAX function (i.e., the maximum value function) of the maximum value function in the structured query statement SQL, and scans and filters the index scan records used for the maximum value query, including: judging whether the maximum value function in the structured query statement SQL is the MAX function; if the maximum value function in the structured query statement SQL is judged to be the MAX function, scanning forward from the last record of the index scan record to obtain each first scan record; and filtering each scan record obtained by using the WHERE condition in the structured query statement SQL until a record satisfying the condition is found and then scanned.
[0045] The present invention targets the MIN function (i.e., the minimum function) of the maximum value function in the structured query statement SQL, and scans and filters the index scan record used for the maximum value query, including: judging whether the maximum value function in the structured query statement SQL is the MIN function; if the maximum value function in the structured query statement SQL is judged to be the MIN function, scanning backward from the first record of the index scan record to obtain each second scan record; then filtering each obtained second scan record with the WHERE condition in the structured query statement SQL, until a record satisfying the condition is found and then scanned.
[0046] In summary, the present invention proposes a maximum value query technology for the scenario of finding the maximum value of an index field when the filter field is not an index field. The main features are: (1) After identifying the scenario of finding the maximum value of an index field when the filter field is not an index field, the index scan record associated with the index field is marked in the query plan; (2) If the statement is a MAX function, scan forward from the last record of the index; if the statement is a MIN function, scan backward from the first record of the index; (3) Each time the record is retrieved, it is filtered according to the WHERE condition in the statement. If the condition is met, the current record is the query result. If not, the scan continues until the condition is met. When the condition is met, the scan stops immediately and the query result is returned.
[0047] It should be pointed out that since most general indexes are stored sequentially, the index fields are recorded in the index in ascending order. Therefore, if the statement contains the MAX function, it is necessary to scan forward from the last record of the index, and if the statement contains the MIN function, it is necessary to scan backward from the first record of the index. For special indexes stored in reverse order, the index fields are recorded in the index in descending order. On the contrary, if the statement contains the MIN function, it is necessary to scan forward from the last record of the index, and if the statement contains the MAX function, it is necessary to scan backward from the first record of the index.
[0048] The above method of the present invention also includes monitoring the query statement for the maximum value before determining whether the filter field is an index field, specifically: when the relational database monitors the connection request from the client, it creates a link to receive the request for the structured query statement SQL; parses the structured query statement SQL of the request to obtain the maximum value query statement in order to perform the maximum value query.
[0049] Figure 2B A database maximum value query device is shown, which includes: a judgment module, which is used to judge whether a filter field is an index field when performing a maximum value query; a marking module, which is used to obtain an index scan record for the maximum value query by marking the index scan record associated with the index field when judging that the filter field is not an index field; a query module, which executes a structured query statement SQL for the maximum value query after obtaining the index scan record for the maximum value query; or executes a structured query statement SQL according to the index field filtering method, thereby querying the maximum value.
[0050] In a specific implementation of the present invention, the judgment module and the marking module of the present invention may be integrated into the optimizer module described below, and the query module may be integrated into the execution module described below.
[0051] Specifically, the query module scans and filters the index scan records for the maximum value query according to the maximum value function in the structured query statement SQL until a record that meets the condition is found.
[0052] On the one hand, the query module of the present invention targets the scenario where the maximum value function in the structured query statement SQL is the MAX function (i.e., the maximum value function), and scans and filters the index scan records used for the maximum value query, including: determining whether the maximum value function in the structured query statement SQL is the MAX function; if the maximum value function in the structured query statement SQL is determined to be the MAX function, scanning forward from the last record of the index scan record to obtain each first scan record; and filtering each scan record obtained using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
[0053] On the other hand, the query module of the present invention targets the scenario where the maximum value function in the structured query statement SQL is the MIN function (i.e., the minimum function), and scanning and filtering the index scan records used for the maximum value query also includes: determining whether the maximum value function in the structured query statement SQL is the MIN function; if the maximum value function in the structured query statement SQL is determined to be the MIN function, scanning backward from the first record of the index scan record to obtain each second scan record; and filtering each second scan record obtained using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
[0054] Combine the following Figure 3-Figure 7 The details of the invention will be described.
[0055] Figure 3 The execution flow of the maximum value query statement of the present invention in which the filter field is a non-index field is shown. Figure 1 Compared with the prior art shown in the figure, the main difference is that the full table scan is optimized as follows: effectively utilizing the index field, scanning according to the index, and then matching the filter condition, the query can be terminated in advance, and the maximum value can be directly output. Scanning according to the index includes: scanning forward from the last row when finding the maximum value; scanning backward from the first row when finding the minimum value. Therefore, the present invention does not need to read and write the memory multiple times, the efficiency is greatly improved, and the IO pressure is reduced.
[0056] The present invention provides a technique for optimizing the maximum value of an index field when the filter field is not an index. The technique is divided into two modules according to functions: an optimizer module and an execution module. Figure 3 As shown, the execution module is divided into two branches according to the specific implementation differences of statement execution: the process of finding the maximum value and the process of finding the minimum value.
[0057] Figure 4 The process of the optimizer module of the present invention is shown. The present invention adds a new scene recognition and processing to the process of the optimizer module. It includes:
[0058] 1. Identify new scenarios, such as "SELECT MAX(index field) / MIN(index field) FROM table_name WHERE non-index field = constant", where other types of comparisons can be used between non-index fields and constants in the filter conditions.
[0059] The WHERE condition can be the following: the index field and a constant are compared using =, >, >=, <, <=, or !=, or the index field is in (constant 1, constant 2, ...), or the index field is between constant 1 and constant 2, etc.
[0060] 2. Mark the index scan records associated with the index fields in the query plan.
[0061] The execution module then executes the statement using the query plan.
[0062] Figure 5 The flow of the execution module filtering non-index fields and finding the maximum value of index fields is shown, including: using the index, scanning forward from the last row of the index record until the full table scan is completed;
[0063] Determine whether the row meets the filtering condition. If so, end the scan; otherwise, continue scanning the next row.
[0064] Figure 6 The flow of the execution module filtering non-index fields and finding the minimum value of index fields is shown, including:
[0065] Using the index, scan backward from the first row until the full table scan is complete.
[0066] Determine whether the row meets the filtering condition. If so, end; otherwise, scan the next row.
[0067] Figure 7 The embodiment of optimizing the maximum value query of MySQL database is shown, and the whole process of optimizing the filtering field of MySQL database when the filtering field is not indexed is shown.
[0068] Figure 7 The optimized query based on MySQL database shown includes:
[0069] Start MySQL, specifically: (1) MySQL initializes the global environment such as global variables, configuration items, and memory pools; (2) After the database is successfully started, it loops to listen for SQL requests from the client.
[0070] Business thread: After MySQL is started, it starts to listen for connection requests from the client. After listening to the connection request, it creates a link to receive SQL requests on the link and creates a business thread to handle these requests. Specifically, it parses the received query statement; the optimizer identifies it as an optimizable scenario and obtains the optimal query plan; executes the query; returns the result set; and ends.
[0071] Compared with the prior art, the technical advantages of the present invention are:
[0072] The present invention is aimed at SQL (Structured Query Language) statements in special scenarios, that is, finding the maximum value (MAX) or minimum value (MIN) of the index field when the filter field is not the index field;
[0073] The present invention optimizes the full table scan into an index scan, significantly improving the query efficiency;
[0074] The present invention can terminate in advance when the filtering condition is met, without the need to scan the entire table, thereby improving query efficiency and reducing IO consumption;
[0075] The present invention avoids multiple reading and writing of memory caused by calculating the maximum value, thereby improving efficiency.
[0076] The present invention can bring the following beneficial effects:
[0077] Using index scanning and early termination can greatly reduce the time consumption of maximum value query. The specific degree of reduction depends on the specific data. When querying a table with 1,000,000 data items on a SAS disk, the optimal time consumption is reduced from 5.68s to 0.34ms.
[0078] The maximum value query statement does not need to scan the entire table. It ends early when the conditions are met, reducing the IO used and ensuring efficient and smooth business operation.
[0079] In addition, the present invention can also avoid multiple reading and writing of memory caused by calculating the maximum value when a large number of filtering conditions are met, thereby improving efficiency. For example: when the birth year is used as an index and the earliest birth year is required when the gender is male, the calculation method before optimization will result in the need to compare and calculate the maximum values between 50% of the data obtained after the full table scan and filtering, which is very inefficient. The method of the present invention starts to search forward from the data with the largest year. Since the earliest birth year is the smallest birth year, it starts from the data with the smallest birth year, that is, based on the index field of birth year, it starts from the first data of the index and searches backward until the filtering condition is met. This data is the earliest data of the birth year that meets the filtering condition. Until the filtering condition is met, there is no need to compare and calculate the maximum values, and the efficiency is greatly improved.
[0080] Although the present invention is described in detail above, the present invention is not limited thereto, and those skilled in the art can make various modifications based on the principles of the present invention. Therefore, all modifications made based on the principles of the present invention should be understood to fall within the protection scope of the present invention.
Claims
1. A database maximum value query method, characterized in that: include: When performing a maximum value query, determine whether the filter field is an index field; If it is determined that the filter field is not an index field, the index scan record associated with the index field is marked to obtain the index scan record for the maximum value query; After obtaining the index scan record for the maximum value query, executing the structured query statement SQL for the maximum value query to query the maximum value; The structured query statement SQL for executing the maximum value query includes: for the maximum value function in the structured query statement SQL, scanning and filtering the index scanning records for the maximum value query until a record that meets the condition is found; The method for scanning and filtering the index scan records used for the maximum value query for the maximum value function in the structured query statement SQL includes: determining whether the maximum value function in the structured query statement SQL is the MAX function; if the maximum value function in the structured query statement SQL is determined to be the MAX function, scanning forward from the last record of the index scan record to obtain each first scan record; and filtering each scan record obtained by using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
2. The method according to claim 1, characterized in that For the maximum value function in the structured query statement SQL, scanning and filtering the index scan records used for the maximum value query include: Determine whether the maximum value function in the structured query statement SQL is the MIN function; If it is determined that the maximum value function in the structured query statement SQL is the MIN function, then scan backward from the first record of the index scan record to obtain each second scan record; Each second scan record obtained is filtered using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
3. The method according to claim 1, characterized in that It also includes a query statement for monitoring and finding the maximum value before determining whether the filter field is an index field.
4. The method according to claim 3, characterized in that The query statement for monitoring the maximum value includes: When the relational database listens for connection requests from the client, it creates a link to receive requests for structured query statements SQL; The structured query statement SQL of the request is parsed to obtain a maximum value query statement so as to perform a maximum value query.
5. A database maximum value query device, characterized in that: include: A judgment module is used to judge whether the filter field is an index field when performing a maximum value query; A marking module, used for obtaining index scan records for maximum value query by marking index scan records associated with the index field when it is determined that the filter field is not an index field; A query module, after obtaining the index scan record for the maximum value query, executes a structured query statement SQL for the maximum value query to query the maximum value; The query module scans and filters the index scan records for the maximum value query according to the maximum value function in the structured query statement SQL until a record that meets the condition is found; The query module scans and filters the index scan records for the maximum value query for the maximum value function in the structured query statement SQL, including: determining whether the maximum value function in the structured query statement SQL is a MAX function; If it is determined that the maximum value function in the structured query statement SQL is the MAX function, scan forward from the last record of the index scan record to obtain each first scan record; use the WHERE condition in the structured query statement SQL to filter each scan record obtained until a record that meets the condition is found and then scanned.
6. The device according to claim 5, characterized in that The query module scans and filters the index scan records for the maximum value query for the maximum value function in the structured query statement SQL, and further includes: Determine whether the maximum value function in the structured query statement SQL is the MIN function; If it is determined that the maximum value function in the structured query statement SQL is the MIN function, then scan backward from the first record of the index scan record to obtain each second scan record; Each second scan record obtained is filtered using the WHERE condition in the structured query statement SQL until a record that meets the condition is found and then scanned.
Citation Information
Patent Citations
Data record searching method and device
CN105159941A
Database access method
KR1020090113499A