Data query method and device, server and storage medium

Abstract

The application provides a data query method and device, a server and a storage medium. In the method, a query request sent by a user is acquired, and a target database for which the query request is sent is determined according to the query request and basic information of a relational database MySQL. The basic information is a data magnitude corresponding to the MySQL under a query condition and a data table index. Then, a query text corresponding to the target database is generated according to query condition information, a query result sorting rule, query result paging information, a query table and a type of the target database. Finally, the query text is executed to obtain a query result in the target database. The technical solution starts from the data magnitude that can be processed by the MySQL database and information in the query request, and improves the data query efficiency and performance.

Description

Technical Field

[0001] This application relates to the field of financial technology, and in particular to a data query method, apparatus, server, and storage medium. Background Technology

[0002] With the development of computer technology, more and more technologies are being applied in the financial field, and the traditional financial industry is gradually transforming into Fintech. Massive data query technology is no exception. However, due to the security and real-time requirements of the financial industry, higher demands are being placed on the technology. When servers handle the storage and querying of massive amounts of data, this business is primarily supported by the relational database MySQL. However, MySQL databases have limitations on the amount of data in a single table and the inability to create unlimited indexes, which leads to low query efficiency.

[0003] In existing technologies, an open-source distributed search and data analysis engine (Elasticsearch, ES), a non-relational database, has been proposed to solve the above problems. ES can support the creation of indexes for any field, and its various tokenizers can well support fuzzy matching queries. Moreover, it supports distributed deployment and can store massive amounts of data.

[0004] However, in practice, in order to improve query efficiency, developers often need to manually select a relational or non-relational database in a system and write multiple sets of different code to operate on different databases. This process makes the query steps more cumbersome. Summary of the Invention

[0005] This application relates to the field of financial technology (Fintech), and more particularly to a data query method, apparatus, server, and storage medium for improving query efficiency during data retrieval.

[0006] In a first aspect, embodiments of this application provide a data query method, including:

[0007] Obtain a query request sent by a user. The query request includes: query condition information, query result sorting rules, query result pagination information, and a query table. The query condition information includes at least one query condition, and the query result pagination information is used to indicate the recording method of the query results.

[0008] Based on the query request and the basic information of the relational database MySQL, the target database for the query is determined. The basic information is the data volume and table indexes of the MySQL database under the query conditions.

[0009] Based on the query conditions, the query result sorting rules, the query result pagination information, the query table, and the type of the target database, generate the query text corresponding to the target database;

[0010] Execute the query text to obtain the query results in the target database.

[0011] In one possible design of the first aspect, determining the target database for the requested query based on the query request and basic information of the relational database MySQL includes:

[0012] Based on the data volume corresponding to the query table and the preset data volume, it is determined whether the query request executed in the MySQL is a slow query. The preset data volume is the maximum data volume that can be called when querying the MySQL.

[0013] If the data volume corresponding to the query table is less than or equal to the preset data volume, then the MySQL database is determined to be the target database.

[0014] If the data volume corresponding to the query table is greater than the preset data volume, generate the corresponding structured query statement SQL for MySQL according to the query request;

[0015] The target database is determined based on the SQL and a preset slow query condition table, wherein the slow query condition table records the correspondence between query conditions and slow queries.

[0016] The target database is either MySQL or the non-relational database Elasticsearch (ES), and the ES database is synchronized with the data in the MySQL database.

[0017] In this possible design, determining the target database based on the SQL and a preset slow query condition table includes:

[0018] Parse the SQL to obtain the corresponding execution plan;

[0019] Based on the execution plan corresponding to the SQL and the slow query condition table, determine whether there is a slow query in the execution plan corresponding to the SQL;

[0020] If the execution plan corresponding to the SQL statement contains slow queries, then the Elasticsearch (ES) database is determined to be the target database.

[0021] If the execution plan corresponding to the SQL statement does not contain any slow queries, then the MySQL database is determined to be the target database.

[0022] Optionally, the execution plan corresponding to the SQL includes: the number of rows scanned;

[0023] Accordingly, determining whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table includes:

[0024] If the number of rows scanned is greater than the allowed number of rows scanned configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation;

[0025] Optionally, the execution plan corresponding to the SQL may further include: query type, access type, and query parameters;

[0026] Accordingly, determining whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table includes:

[0027] If at least one of the query type, the access type, and the query parameters matches the query condition corresponding to the slow query configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0028] In another possible design of the first aspect, generating the query text corresponding to the target database based on the query condition information, the query result sorting rules, the query result pagination information, the query table, and the type of the target database includes:

[0029] Based on the query condition information, construct a general query condition object;

[0030] Iterate through the general query condition objects to obtain at least one simple query instance object;

[0031] Based on the at least one simple query instance object, the query result sorting rules, and the query result pagination information, a full-text query instance object is generated;

[0032] Based on the query table and the full-text query instance object, generate a search function instance object;

[0033] Based on the search function instance object and the target database, generate the query text corresponding to the target database.

[0034] In this possible design, generating the query text corresponding to the target database based on the search function instance object and the type of the target database includes:

[0035] If the target database is Elasticsearch, the attribute information in the search function instance is parsed using Java reflection to generate JSON query text.

[0036] If the target database is MySQL, the attribute information in the search function instance is parsed using Java reflection to generate SQL query text.

[0037] Secondly, embodiments of this application provide a data query device, including: an acquisition module, a determination module, a generation module, and a processing module;

[0038] The acquisition module is used to acquire a query request sent by a user. The query request includes: query condition information, query result sorting rules, query result pagination information, and a query table. The query condition information includes at least one query condition, and the query result pagination information is used to indicate the recording method of the query results.

[0039] The determining module is used to determine the target database for the query based on the query request and the basic information of the relational database MySQL, wherein the basic information is the data volume and data table index of the MySQL corresponding to the query conditions;

[0040] The generation module is used to generate query text corresponding to the target database based on the query condition information, the query result sorting rules, the query result pagination information, the query table, and the type of the target database.

[0041] The processing module is used to execute the query text and obtain the query results in the target database.

[0042] In one possible design of the second aspect, the determining module is specifically used for:

[0043] Based on the data volume corresponding to the query table and the preset data volume, it is determined whether the query request executed in the MySQL is a slow query. The preset data volume is the maximum data volume that can be called when querying the MySQL.

[0044] If the data volume corresponding to the query table is less than or equal to the preset data volume, then the MySQL database is determined to be the target database.

[0045] If the data volume corresponding to the query table is greater than the preset data volume, generate the corresponding structured query statement SQL for MySQL according to the query request;

[0046] The target database is determined based on the SQL and a preset slow query condition table, wherein the slow query condition table records the correspondence between query conditions and slow queries.

[0047] The target database is either MySQL or the non-relational database Elasticsearch (ES), and the ES database is synchronized with the data in the MySQL database.

[0048] In this possible design, the determining module determines the target database based on the SQL and a preset slow query condition table, specifically for:

[0049] Parse the SQL to obtain the corresponding execution plan;

[0050] Based on the execution plan corresponding to the SQL and the slow query condition table, determine whether there is a slow query in the execution plan corresponding to the SQL;

[0051] If the execution plan corresponding to the SQL statement contains slow queries, then the Elasticsearch (ES) database is determined to be the target database.

[0052] If the execution plan corresponding to the SQL statement does not contain any slow queries, then the MySQL database is determined to be the target database.

[0053] Optionally, the execution plan corresponding to the SQL includes: the number of rows scanned;

[0054] Accordingly, the determining module determines whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table, specifically for:

[0055] If the number of rows scanned is greater than the allowed number of rows scanned configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation;

[0056] Optionally, the execution plan corresponding to the SQL may further include: query type, access type, and query parameters;

[0057] Accordingly, the determining module determines whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table, specifically for:

[0058] If at least one of the query type, the access type, and the query parameters matches the query condition corresponding to the slow query configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0059] In another possible design of the second aspect, the generation module is specifically used for:

[0060] Based on the query condition information, construct a general query condition object;

[0061] Iterate through the general query condition objects to obtain at least one simple query instance object;

[0062] Based on the at least one simple query instance object, the query result sorting rules, and the query result pagination information, a full-text query instance object is generated;

[0063] Based on the query table and the full-text query instance object, generate a search function instance object;

[0064] Based on the search function instance object and the target database, generate the query text corresponding to the target database.

[0065] In this possible design, the generation module, based on the search function instance object and the type of the target database, generates the query text corresponding to the target database, specifically for:

[0066] If the target database is Elasticsearch, the attribute information in the search function instance is parsed using Java reflection to generate JSON query text.

[0067] If the target database is MySQL, the attribute information in the search function instance is parsed using Java reflection to generate SQL query text.

[0068] Thirdly, embodiments of this application provide a server, including: a processor and a memory;

[0069] The memory stores computer-executed instructions;

[0070] The processor executes the computer execution instructions, causing the computer server to perform the data query method as described in the first aspect and various possible designs above.

[0071] Fourthly, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the data query method as described in the first aspect and various possible designs above.

[0072] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, is used to implement the data query method as described in the first aspect and various possible designs above.

[0073] This application provides a data query method, apparatus, server, and storage medium. The method involves acquiring a user-sent query request and determining the target database based on the query request and basic information about the relational database MySQL. The basic information includes the data volume and table indexes of the MySQL database under the query conditions. Then, based on the query conditions, query result sorting rules, query result pagination information, the query table, and the type of the target database, a query text corresponding to the target database is generated. Finally, the query text is executed to obtain the query results in the target database. This technical solution improves data query efficiency and performance by considering the data volume that the MySQL database can handle and the information in the query request. Attached Figure Description

[0074] The accompanying drawings, which are incorporated in and form a part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure.

[0075] Figure 1 This is a schematic diagram illustrating an application scenario of the data query method provided in the embodiments of this application;

[0076] Figure 2 A schematic diagram of the architecture of the data query method provided in the embodiments of this application;

[0077] Figure 3 A flowchart illustrating an embodiment of the data query method provided in this application;

[0078] Figure 4 A flowchart illustrating Embodiment 2 of the data query method provided in this application;

[0079] Figure 5 A flowchart illustrating Embodiment 3 of the data query method provided in this application;

[0080] Figure 6 A flowchart illustrating Embodiment 4 of the data query method provided in this application;

[0081] Figure 7 A flowchart illustrating Embodiment 5 of the data query method provided in this application;

[0082] Figure 8 This is a schematic diagram of the structure of the data query device provided in the embodiments of this application;

[0083] Figure 9 This is a schematic diagram of the server structure provided in an embodiment of this application.

[0084] The accompanying drawings have illustrated specific embodiments of this disclosure, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concepts of this disclosure to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0085] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this disclosure. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this disclosure as detailed in the appended claims.

[0086] Before introducing the embodiments of this application, the background technology involved in this application will be explained first:

[0087] With the increasing number of internet users and the continuous development of internet technology, server backends often need to store and retrieve massive amounts of data.

[0088] Common data storage uses relational databases like MySQL. However, for requests requiring multi-condition queries, such as historical data queries or transaction log queries, the following problems can easily occur when using MySQL relational databases:

[0089] Firstly, when the amount of data in a single MySQL table exceeds a certain amount, the performance of Structured Query Language (SQL) operations drops sharply.

[0090] Secondly, since MySQL indexes cannot be created arbitrarily indefinitely, when the amount of data to be accessed exceeds a certain limit, queries without indexes are inefficient, and MySQL's fuzzy queries cannot use indexes, resulting in very poor query performance.

[0091] Therefore, existing technologies have proposed a distributed, highly scalable, and real-time search and data analysis engine (Elasticsearch, ES), which is a non-relational database. It can support the creation of indexes for any field, and ES's various tokenizers can well support fuzzy matching queries. Moreover, it supports distributed deployment and can store massive amounts of data. Therefore, choosing to use ES can efficiently realize the query and search of massive amounts of data.

[0092] However, in some scenarios, when a server receives a query request, further judgment is needed to determine whether Elasticsearch or MySQL is more efficient. Querying different databases requires different Application Programming Interfaces (APIs), which developers need to learn, resulting in repetitive and inefficient work. While frameworks like MyBatis and Hibernate integrate multiple databases, these frameworks only support relational databases and not non-relational databases, thus failing to provide more efficient data query services.

[0093] Based on the problems existing in the above-mentioned prior art Figure 1 This diagram illustrates an application scenario of the data query method provided in this application, used to solve the aforementioned technical problems. For example... Figure 1 As shown in the diagram, the application scenario includes: terminal device 11 and server 12.

[0094] Among them, the database corresponding to server 12 may include MySQL 121 and ES 122, and ES 122 synchronizes the relevant data of MySQL 121.

[0095] Optionally, the terminal device 11 can be a mobile phone, computer, tablet, or other device, and the server 12 is used to provide services to the terminal device 11.

[0096] In one possible implementation, a user sends a query request to a server 12 via a terminal device 11 to request relevant data. After receiving the query request from the terminal device 11, the server 12 makes a judgment based on the query request and relevant information from MySQL 121. The server 12 determines which database, MySQL 121 or ES 122, has higher query efficiency (taking ES 122 as an example). Based on this, the server 12 generates a query text that can execute ES 122, according to the query request and ES 122. The server 12 then uses this query text to obtain the relevant data.

[0097] It should be understood that the undisclosed parts of this implementation process are detailed in the following embodiments.

[0098] This application addresses the aforementioned technical problems. The inventor's technical conception process is as follows: In the prior art, it is necessary to manually select a suitable database to avoid low query efficiency caused by slow queries. If it is possible to compare the query table with the maximum data volume that can be called in MySQL to determine whether the query is a slow query, and then select a suitable database, and then generate the query text corresponding to the database to be called based on the query conditions and other information in the query request, so that the server can execute the query text and obtain the required query results efficiently.

[0099] The following is based on Figure 1 The illustrated application scenario diagrams provide a detailed explanation of the technical solutions of this application through specific embodiments. It should be noted that the following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described in conjunction with the accompanying drawings.

[0100] First, the architecture provided in the embodiments of this application will be introduced. Figure 2 This is a schematic diagram of the architecture of the data query method provided in the embodiments of this application, such as... Figure 2 As shown, the interface layer 21, the parser 22, the executor 23, and the database set 24 are all included.

[0101] Database set 24 includes Figure 1 The examples shown are MySQL 121 and ES 122.

[0102] Optionally, the architecture of this data query method can be based on a custom multi-database persistence layer framework, WeBatis.

[0103] Specifically, interface layer 21 can be the WeBatis interface layer, used to receive data query services, including ObjExample and ApiInterface. Among them, ObjExample is the core unified object for data operations, and ApiInterface is the unified interface for operations, shielding the differences between relational databases and non-relational databases.

[0104] Parser 22 can be a WeBatis parser, including MySQL Parser and ES Parser. It parses different database types into their corresponding operation objects. Because the parsing process needs to determine which database to use in the current scenario, parser 22 also includes an index configuration center, IndexConfig. When existing plugins do not meet the developer's requirements, the developer can develop custom plugins to parse the corresponding database operations.

[0105] Executor 23 can be a WeBatis executor with a database proxy factory (Db ProxyFactory) that supports configuration to connect to multiple databases. Executor 23 also includes a MySQL Executor and an ES Executor, each supporting configuration to connect to its respective database. It obtains the operation objects parsed by parser 22 and then uses the corresponding database operation objects to actually operate on the database (e.g., ES 122). Similar to parser 22, if existing plugins do not meet the developer's requirements, the developer can develop custom plugins to perform the corresponding database operations.

[0106] It should be understood that ObjExample is a query condition object that is common to multiple databases in WeBatis; ObjParser is an object that WeBatis parses into different database operations; and ObjExecutor is an object that WeBatis uses to execute different database operations.

[0107] It is worth noting that the database type in this application embodiment can also be other types besides MySQL and ES. In specific implementation, the data query method in this application embodiment can be used.

[0108] exist Figure 2 Based on the architecture shown, Figure 3 A flowchart illustrating an embodiment of the data query method provided in this application is shown below. Figure 3 As shown, the data query method includes:

[0109] Step 31: Obtain the query request sent by the user.

[0110] The parameters in the query request include: query condition information ConditionList, query result sorting rule OrderByClause, query result pagination information Page, and query table. The query condition information must contain at least one query condition, and the query result pagination information is used to indicate the recording method of the query results.

[0111] In this step, the user sends a data query request to the server through their terminal device. The server receives the query request from the user's terminal device. Specifically, the query request includes the conditions for querying data, how the query results are arranged, and how the results are displayed on the page.

[0112] In one possible implementation, a user sends a query request through a terminal device to retrieve information about users whose age is greater than 50 (one of the query criteria). The query results are arranged in descending order of age (query result pagination information), specifically displaying 35 results per page (query result pagination information), etc.

[0113] In this step, after receiving a query request, it is also necessary to verify the validity of the parameters in the query request, such as whether the account corresponding to the query request is authorized.

[0114] In one possible implementation, for example of a validity verification, such as querying information of a user with an ID number of xxxxxxxxxxx, but the system's default ID number is 18 digits, the validity verification will fail, and an error report can be returned to the user's terminal device.

[0115] Optionally, the query request must include at least a query table.

[0116] Furthermore, only after the verification is successful can we proceed to step 32.

[0117] Step 32: Based on the query request and the basic information of the relational database MySQL, determine the target database for the query.

[0118] The basic information includes the data volume and table indexes of MySQL under the query conditions, and the target database is either MySQL or Elasticsearch (ES), with data synchronized between ES and MySQL.

[0119] Prior to this step, MySQL continuously stores data in its own cache path by acquiring data in real time. Data in Elasticsearch can be collected from MySQL through Binlog. On the one hand, Binlog data is parsed and cached in Elasticsearch to achieve synchronization of data between Elasticsearch and MySQL. On the other hand, by parsing Binlog data, the total number of records in the current data is saved, providing a basis for determining the target database in the future.

[0120] In this step, for MySQL queries, if there is no index and the data volume is large, the query performance will be very poor.

[0121] Specifically, first determine whether the current table data volume exceeds the configured maximum data volume that can be called without causing a slow query (BIG_DATA). If it does not exceed BIG_DATA, it means that the data volume is not large, and the query performance will not be affected regardless of whether the index is hit. In this case, directly choose to use the MySQL query.

[0122] Furthermore, if the query exceeds BIG_DATA, a structured query statement (SQL) for querying MySQL needs to be generated. Then, the execution plan of the SQL is parsed using `explain`. Based on the parsing results, it is determined whether the current query will result in a slow query. If there is no slow query, then the target database is determined to be MySQL; otherwise, the target database is determined to be Elasticsearch (ES).

[0123] Step 33: Generate the query text corresponding to the target database based on the query conditions, query result sorting rules, query result pagination information, query table, and target database type.

[0124] In this step, the type of the target database (MySQL or Elasticsearch) has been determined. At this point, based on the query condition information, the query condition information ConditionList, the query result sorting rule OrderByClause, the query result pagination information Page, and the query table are parsed to generate query text that can operate on the specific target database.

[0125] In one possible implementation, this step can be achieved through ObjExeample and ObjParser in the parser. Specifically, the query condition information is parsed to generate a general query condition object ObjExeample. Then, ObjParser parses the general query condition object and adds query result sorting rules, query result pagination information, and query table, converting it into a search function instance object SearchRequest.

[0126] Furthermore, depending on the type of the target database, the SearchRequest instance object is converted into specific query text.

[0127] Optionally, if the target database is Elasticsearch, generate a JSON query text; if the target database is MySQL, generate an SQL query text.

[0128] Step 34: Execute the query text and obtain the query results in the target database.

[0129] In this step, the query text generated by the parser is passed to the executor. The executor proxies the connection access between MySQL and Elasticsearch during the system initialization of the server. Based on the database identified by the query text, the executor selects different ObjExecutors to perform the query operation in order to obtain the query results.

[0130] In one possible implementation, if the query text is an SQL query text, the MySQL Executor is invoked to execute the relevant query operations in the SQL; if the query text is an ES query text, the ES Executor is invoked to execute the relevant query operations in ES.

[0131] After that, the query results will be returned to the user's terminal device.

[0132] That is, a query request is sent from a terminal device to the server, and after the server responds, the query result is obtained and returned to the user's terminal device.

[0133] In one possible implementation, the query results could be displayed on the graphical user interface of the terminal device. For example, if the query request is for employees with a salary of more than 5,000, and the results are sorted by amount from largest to smallest, with 30 results displayed per page, then the query results displayed on the graphical user interface could be one page with 25 results, etc.

[0134] It should be understood that the examples in the embodiments of this application are merely illustrative and do not represent implementations in real-world scenarios.

[0135] The data query method provided in this application obtains a query request sent by a user and determines the target database for the query based on the query request and basic information of the relational database MySQL. The basic information includes the data volume and table indexes of the MySQL database under the query conditions. The target database can be either MySQL or a non-relational database Elasticsearch (ES), with ES synchronizing data from MySQL. Then, based on the query conditions, query result sorting rules, query result pagination information, query table type, and target database type, a query text corresponding to the target database is generated. Finally, the query text is executed to obtain the query results in the target database. This technical solution improves data query efficiency and performance by considering the data volume that the MySQL database can handle and the information in the query request.

[0136] Based on the above embodiments, Figure 4 A flowchart illustrating Embodiment 2 of the data query method provided in this application is shown below. Figure 4 As shown, step 22 above can be achieved through the following steps:

[0137] Step 41: Determine whether the query request is a slow query in MySQL based on the data volume corresponding to the query table and the preset data volume.

[0138] The preset data volume level is the maximum data volume that can be retrieved when querying MySQL.

[0139] In this step, the data volume corresponding to the query table is the data volume that needs to be queried in response to the query request sent by the user. The preset data volume is the maximum data volume that can be called when querying MySQL, that is, the value BIG_DATA that will not result in a slow query.

[0140] Optionally, query the data volume of MySQL based on the MySQL table indexes.

[0141] Optionally, if the data volume corresponding to the query table is less than or equal to the preset data volume, it is considered that the data volume when querying MySQL is small, and there will be no impact on query performance regardless of whether the index is hit. Therefore, it is determined that the query request executed in MySQL is not a slow query.

[0142] Optionally, when the data volume corresponding to the query table is greater than the preset data volume, it is necessary to further determine whether the query when querying MySQL is a slow query.

[0143] Step 42: If the data volume corresponding to the query table is less than or equal to the preset data volume, then MySQL is determined as the target database.

[0144] In this step, if the data volume corresponding to the query table is less than or equal to the preset data volume, it is considered that the data volume when querying MySQL is not large and will not affect the query performance. It can be assumed that when this query request hits MySQL, the subsequent execution process is not a slow query.

[0145] Furthermore, MySQL is selected as the target database to execute the subsequent process of generating query text.

[0146] Step 43: If the data volume corresponding to the query table is greater than the preset data volume, generate the corresponding SQL in MySQL according to the query request.

[0147] In this step, when the data volume corresponding to the query table is greater than the preset data volume, it is necessary to generate the corresponding SQL query for MySQL to further determine whether there is a slow query in the SQL.

[0148] Optionally, generate the corresponding SQL for MySQL based on the parameters in the query request.

[0149] Step 44: Determine the target database based on the SQL and the preset slow query condition table.

[0150] The slow query condition table records the correspondence between query conditions and slow queries.

[0151] In this step, `explain` parses the execution plan of the SQL. The parsing result includes the query type `select_type`, the access type `type`, the estimated number of rows scanned `rows`, and additional information `extra` (query parameters). This is then compared with the slow query condition table to determine the target database. Table 1 is the slow query condition table provided in this application embodiment, showing the correspondence between common query conditions and slow queries.

[0152] Table 1:

[0153]

[0154] Optionally, when the execution plan corresponding to the SQL includes the number of rows scanned, it is determined whether the number of rows scanned is greater than the allowed number of rows scanned configured in the slow query condition table. If it is greater, it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0155] Optionally, when the execution plan corresponding to the SQL also includes: query type, access type and query parameters, if at least one of the query type, access type and query parameters matches the query condition corresponding to the slow query configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0156] Specifically, referring to Table 1, the implementation of the above example may include the following steps:

[0157] Step 1: Parse the SQL to obtain the corresponding execution plan. The execution plan includes: query type, access type, number of rows scanned, and query parameters.

[0158] Optionally, to avoid slow queries in MySQL, the results returned by the four fields select_type, type, rows, and Extra will be combined, that is, the SQL will be parsed to obtain the corresponding execution plan.

[0159] Step 2: Based on the execution plan and slow query condition table corresponding to the SQL, determine whether there are slow queries in the execution plan corresponding to the SQL.

[0160] Optionally, based on the execution plan corresponding to the SQL, determine in Table 1 whether there are slow queries.

[0161] Step 3: If the execution plan corresponding to the SQL contains slow queries, then Elasticsearch is determined as the target database.

[0162] As an example, when the execution plan for an SQL query is a combination of UNION | ALL | Using filesort, the MySQL query performance drops significantly, indicating a slow query situation. In this case, Elasticsearch should be identified as the target database.

[0163] As another example, if the execution plan for an SQL query is a combination of UNION | ref | Using filesort, where UNION and Using filesort are slow queries, then Elasticsearch is determined to be the target database.

[0164] As another example, rows must be less than the number of rows allowed to be scanned in each table according to the external configuration; otherwise, it is still a slow query, and Elasticsearch is determined to be the target database.

[0165] Step 4: If the execution plan corresponding to the SQL does not contain any slow query cases, then MySQL is determined as the target database.

[0166] As an example, if rows are less than the number of rows allowed to be scanned in each table according to the external configuration, and the execution plan for the SQL query is a combination of SIMPLLE | ref | Using index, then MySQL is determined to be the target database.

[0167] As another example, if rows are less than the number of rows allowed to be scanned in each table according to the external configuration, and the execution plan for the SQL query is a combination of SIMPLLE | eq_ref | Using index, then MySQL is determined to be the target database.

[0168] The data query method provided in this application determines whether a query request executed in MySQL is a slow query based on the data volume corresponding to the query table and a preset data volume. If the data volume corresponding to the query table is less than or equal to the preset data volume, then MySQL is determined as the target database. If the data volume corresponding to the query table is greater than the preset data volume, a structured query statement (SQL) corresponding to MySQL is generated based on the query request. Finally, the target database is determined based on the SQL and a preset slow query condition table. This approach, starting with the determination of slow queries, provides a foundation for more efficient data queries in the future.

[0169] Based on the above embodiments, Figure 5 The flowchart of the data query method embodiment three provided in this application is shown below. Figure 5 As shown, step 23 above can be achieved through the following steps:

[0170] Step 51: Construct a general query condition object based on the query condition information.

[0171] In this step, after the query request passes the validity verification, the query condition information in the query request is parsed out and constructed into a general query condition object ObjExample.

[0172] The core parameters of this general query condition object are ConditionList, OrderByClause, and Page, which mainly control the query condition information, the sorting rules of the query results, and the pagination information of the query results, i.e., the query conditions, the sorting rules of the query results, and the recording method.

[0173] Step 52: Traverse the general query condition objects to obtain at least one simple query instance object.

[0174] In this step, loop through ConditionList, extract all condition, value1, and value2 from the general query condition object, and construct at least one simple query instance object QueryBuilder, where condition represents the type of query condition.

[0175] In the embodiments of this application, common condition types include: whether it is empty (IsNull, IsNotNull), whether it is equal (EqualTo, NotEqualTo), greater than or less than (GreaterThan, LessThan), fuzzy matching (Like, NotLike), and range (In, Between), etc. Different condition types can be combined and converted into corresponding simple query instance objects QueryBuilder.

[0176] Specifically, based on the chain of responsibility pattern, the condition object is passed to ObjParser in each loop. Based on the operate property of the condition object, it is determined which type of QueryBuilder instance to generate. Each QueryBuilder instance can only recognize and process the operations it can handle. If the generation is successful, the instantiated object is returned. If it cannot be processed, it is passed to the next one for processing.

[0177] Furthermore, based on this example, different conditions passed in by external requests are handled by the server using a unified process, reducing the coupling between the request condition and the final QueryBuilder. Adding or deleting conditions or changing their order does not require changing the instantiation logic, ensuring highly flexible logic. The code does not require too many if...else... statements, avoiding cumbersome execution processes.

[0178] Optionally, different simple query instance objects QueryBuilder can be BoolQueryBuilder, TermQueryBuilder, TermsQueryBuilder, RangeQueryBuilder, or WildcardQuery.

[0179] Builder, ExistsQueryBuilder, etc.

[0180] Step 53: Generate a full-text query instance object based on at least one simple query instance object, the query result sorting rules, and the query result pagination information.

[0181] In this step, BoolQueryBuilder connects each QueryBuilder generated in the loop with must, mustNot, filter, and should to form complex query conditions. After the condition loop is complete, ObjParser retrieves the OrderByClause and Page properties from ObjExeample, sets the sorting rules and pagination information for the query results, and adds at least one simple query instance object QueryBuilder generated earlier to generate SearchSource.

[0182] Builder object.

[0183] Optionally, the query result sorting rules and query result pagination information may include: setting pagination, setting sorting, and setting query timeout, etc.

[0184] Step 54: Generate a search function instance object based on the query table and the full-text query instance object.

[0185] In this step, the final search function instance object SearchRequest is generated based on the full-text query instance object SearchSourceBuilder and the set query table.

[0186] Optionally, the search function instance objects include: setting the query table and setting the full-text query instance object.

[0187] Step 55: Based on the search function instance object and the target database, generate the query text corresponding to the target database.

[0188] In this step, based on the type of the target database used in this query request calculated earlier, the attributes in the search function instance object are processed to obtain the query text that can operate on the target database.

[0189] In one possible implementation, if the target database is Elasticsearch, the attribute information in the search function instance is parsed using Java reflection to generate JSON query text.

[0190] In another possible implementation, if the target database is MySQL, the attribute information in the search function instance is parsed using Java reflection to generate SQL query text.

[0191] The data query method provided in this application constructs a general query condition object based on query condition information, traverses the general query condition object to obtain at least one simple query instance object, and generates a full-text query instance object based on at least one simple query instance object, query result sorting rules, and query result pagination information. Then, a search function instance object is generated based on the query table and the full-text query instance object. Finally, a query text corresponding to the target database is generated based on the search function instance object and the target database. This technical solution starts from query condition information and realizes the generation of query text that can manipulate the database, providing a foundation for efficiently obtaining query results.

[0192] Based on the above embodiments, Figure 6 The flowchart of Embodiment 4 of the data query method provided in this application is shown below. Figure 6 As shown, a specific example of this data query method can be illustrated with the following steps:

[0193] Step 1: Obtain the query request sent by the user;

[0194] Step 2: Determine the validity of the parameters in the query request;

[0195] Step 3: Check the MySQL index fields of the current object;

[0196] Step 4: Query the data volume of the current object;

[0197] Step 5: Is the data volume greater than the preset data volume? If yes, proceed to step 6; otherwise, proceed to step 9.

[0198] Step 6: Generate SQL based on the query request;

[0199] Step 7: Obtain the slow query results based on the SQL and the slow query condition table;

[0200] Step 8: Determine if the slow query result is indeed a slow query. If yes, proceed to step 10; otherwise, proceed to step 9.

[0201] Step 9: Select MySQL;

[0202] Step 10: Select ES.

[0203] The data query method provided in this application obtains a query request sent by a user, and after determining the validity of the parameters in the query request, checks the index fields of the current object in MySQL to query the data volume of the current object. If the data volume is greater than a preset data volume, SQL is generated according to the query request. Based on the SQL and a slow query condition table, the slow query result is obtained. If the slow query result is a slow query, Elasticsearch is selected as the database; otherwise, MySQL is selected. This technical solution provides a foundation for efficient data query implementation.

[0204] Based on the above embodiments, Figure 7 The flowchart of Embodiment 5 of the data query method provided in this application is shown below. Figure 7 As shown, a specific example of this data query method can be illustrated with the following steps:

[0205] Step 1: Based on the query conditions, obtain the conditions and values ​​corresponding to each condition;

[0206] Step 2: Based on the list of conditions and the conditions and values ​​corresponding to each condition, construct simple query instance objects respectively;

[0207] Step 3: Generate complex query instance objects based on all simple query instance objects;

[0208] Step 4: Generate a full-text query instance object based on the complex query instance object, the query result sorting rules, and the query result pagination information;

[0209] Step 5: Generate a search function instance object based on the query table and the full-text query instance object;

[0210] Step 6: Determine the type of the target database. If it is Elasticsearch, proceed to step 7; if it is MySQL, proceed to step 8.

[0211] Step 7: Generate JSON query text based on the search function instance object, and then proceed to Step 9;

[0212] Step 8: Generate SQL query text based on the search function instance object;

[0213] Step 9: Execute the query text;

[0214] Step 10: Return the query results.

[0215] The data query method provided in this application embodiment obtains the conditions and values ​​corresponding to each condition based on the query condition information. Then, based on the condition list and the conditions and values ​​corresponding to each condition, simple query instance objects are constructed respectively. Based on all the simple query instance objects, complex query instance objects are generated. Then, based on the complex query instance objects, the query result sorting rules, and the query result pagination information, a full-text query instance object is generated. Then, based on the query table and the full-text query instance object, a search function instance object is generated. Based on different database types, JSON query text or SQL query text is generated based on the search function instance object. Then, based on the query text, more efficient data query is achieved, thereby also realizing the overall coordination between databases.

[0216] Based on the above method embodiments, Figure 8 This is a schematic diagram of the structure of the data query device provided in the embodiments of this application, such as... Figure 8 As shown, the device includes: an acquisition module 81, a determination module 82, a generation module 83, and a processing module 84;

[0217] The acquisition module 81 is used to acquire the query request sent by the user. The query request includes: query condition information, query result sorting rules, query result pagination information and query table. The query condition information includes at least one query condition, and the query result pagination information is used to indicate the recording method of the query results.

[0218] The determination module 82 is used to determine the target database for the query based on the query request and the basic information of the relational database MySQL. The basic information is the data volume and data table indexes of MySQL under the query conditions.

[0219] The generation module 83 is used to generate the query text corresponding to the target database based on the query conditions, query result sorting rules, query result pagination information, query table and target database type;

[0220] Processing module 84 is used to execute the query text and obtain the query results in the target database.

[0221] In one possible design of this application embodiment, the determining module 82 is specifically used for:

[0222] Based on the data volume corresponding to the query table and the preset data volume, determine whether the query request is a slow query in MySQL. The preset data volume is the maximum data volume that can be called when querying MySQL.

[0223] If the data volume corresponding to the query table is less than or equal to the preset data volume, then MySQL is determined as the target database.

[0224] If the data volume corresponding to the query table is greater than the preset data volume, generate the corresponding structured query statement SQL for MySQL based on the query request;

[0225] Based on the SQL and the preset slow query condition table, the target database is determined. The slow query condition table records the correspondence between query conditions and slow queries.

[0226] The target database can be either MySQL or Elasticsearch (ES), and ES synchronizes data from MySQL.

[0227] In this possible design, module 82 determines the target database based on the SQL and a preset slow query condition table, specifically for:

[0228] Parse the SQL to obtain the corresponding execution plan. The execution plan for the SQL includes: query type, access type, number of rows scanned, and query parameters.

[0229] Based on the execution plan and slow query condition table corresponding to the SQL, determine whether there are slow queries in the execution plan corresponding to the SQL;

[0230] If the execution plan corresponding to the SQL statement contains slow queries, then Elasticsearch is determined to be the target database.

[0231] If the execution plan for the SQL query does not contain any slow query cases, then MySQL is determined to be the target database.

[0232] Optionally, the execution plan corresponding to the SQL includes: the number of rows scanned;

[0233] Accordingly, module 82 determines whether there are slow queries in the execution plan corresponding to the SQL based on the slow query condition table. Specifically, it is used for:

[0234] If the number of rows scanned is greater than the allowed number of rows scanned configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0235] Optionally, the execution plan for the SQL query may also include: query type, access type, and query parameters;

[0236] Accordingly, module 82 determines whether there are slow queries in the execution plan corresponding to the SQL based on the slow query condition table. Specifically, it is used for:

[0237] If at least one of the query type, access type, and query parameters matches the query condition configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

[0238] Optionally, the determining module 82 is also used for:

[0239] If a query is slow when executed in MySQL, then Elasticsearch is determined as the target database.

[0240] In another possible design of this application embodiment, the generation module 83 is specifically used for:

[0241] Construct a general query condition object based on the query condition information;

[0242] Iterate through the general query condition object to obtain at least one simple query instance object, with the condition list recording multiple different query conditions;

[0243] Generate a full-text query instance object based on at least one simple query instance object, the query result sorting rules, and the query result pagination information;

[0244] Generate a search function instance object based on the query table and the full-text query instance object;

[0245] Based on the search function instance object and the target database, generate the query text corresponding to the target database.

[0246] In this possible design, the generation module 83 generates the query text corresponding to the target database based on the search function instance object and the type of the target database, specifically for:

[0247] If the target database is Elasticsearch, the attribute information in the search function instance is parsed using Java reflection to generate JSON query text.

[0248] If the target database is MySQL, the attribute information in the search function instance is parsed using Java reflection to generate SQL query text.

[0249] The data query device provided in this application embodiment can be used to execute the technical solution corresponding to the data query method in the above embodiment. Its implementation principle and technical effect are similar, and will not be described again here.

[0250] It should be noted that the division of the various modules in the above device is merely a logical functional division. In actual implementation, they can be fully or partially integrated into a single physical entity, or they can be physically separated. Furthermore, these modules can be implemented entirely in software through processing element calls; they can be fully implemented in hardware; or some modules can be implemented through processing element calls in software, while others are implemented in hardware. Moreover, these modules can be fully or partially integrated together, or implemented independently. The processing element mentioned here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each of the above modules can be completed through the integrated logic circuits in the hardware of the processor element or through software instructions.

[0251] Figure 9 This is a schematic diagram of the server structure provided in an embodiment of this application. Figure 9 As shown, the server may include: a processor 90, a memory 91, and computer program instructions stored in the memory 91 and executable on the processor 90.

[0252] Processor 90 executes computer execution instructions stored in memory 91, causing processor 90 to perform the scheme in the above embodiments. Processor 90 can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0253] Optionally, the server may also include: transceiver 92.

[0254] The memory 91 and transceiver 92 are connected to the processor 90 via the system bus and communicate with each other. The memory 91 is used to store computer program instructions.

[0255] Transceiver 92 is used to communicate with other servers, and transceiver 92 constitutes a communication interface.

[0256] Optionally, in terms of hardware implementation, the above Figure 8 The acquisition module 81 in the illustrated embodiment corresponds to the transceiver 92 in this embodiment.

[0257] The system bus can be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus, etc. The system bus can be divided into address bus, data bus, control bus, etc. For ease of representation, only one thick line is used in the diagram, but this does not indicate that there is only one bus or one type of bus.

[0258] The server provided in this application embodiment can be used to execute the technical solution corresponding to the data query method in the above embodiment. Its implementation principle and technical effect are similar, and will not be described again here.

[0259] This application also provides a chip for executing instructions, which is used to execute the data query method described in the above embodiments.

[0260] This application also provides a computer-readable storage medium storing computer instructions. When the computer instructions are executed on a server, the server performs the data query method described in the above embodiments.

[0261] This application also provides a computer program product, including a computer program, which, when executed by a processor, is used to perform the technical solution of the data query method described in the above embodiments.

[0262] The aforementioned computer-readable storage media can be implemented by any type of volatile or non-volatile storage server or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The readable storage medium can be any available medium accessible to general-purpose or special-purpose computer servers.

[0263] It should be understood that this disclosure is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this disclosure is limited only by the appended claims.

Claims

1. A data query method, characterized in that, include: Obtain a query request sent by a user. The query request includes: query condition information, query result sorting rules, query result pagination information, and a query table. The query condition information includes at least one query condition, and the query result pagination information is used to indicate the recording method of the query results. Based on the data volume corresponding to the query table and the preset data volume, it is determined whether the query request executed in the relational database MySQL is a slow query. The preset data volume is the maximum data volume that can be called when querying the MySQL database. If the data volume corresponding to the query table is less than or equal to the preset data volume, then the MySQL database is determined to be the target database for the requested query. If the data volume corresponding to the query table is greater than the preset data volume, generate the corresponding structured query statement SQL for MySQL according to the query request; Based on the SQL and a preset slow query condition table, the target database for the requested query is determined. The slow query condition table records the correspondence between query conditions and slow queries. The target database can be either MySQL or a non-relational database Elasticsearch (ES), and ES synchronizes data from MySQL. Based on the query condition information, construct a general query condition object; Iterate through the general query condition objects to obtain at least one simple query instance object; Based on the at least one simple query instance object, the query result sorting rules, and the query result pagination information, a full-text query instance object is generated; Based on the query table and the full-text query instance object, generate a search function instance object; Based on the search function instance object and the target database, generate the query text corresponding to the target database; Execute the query text to obtain the query results in the target database.

2. The method according to claim 1, characterized in that, The step of determining the target database for the requested query based on the SQL and a preset slow query condition table includes: Parse the SQL to obtain the corresponding execution plan; Based on the execution plan corresponding to the SQL and the slow query condition table, determine whether there is a slow query in the execution plan corresponding to the SQL; If the execution plan corresponding to the SQL statement contains slow queries, then the Elasticsearch (ES) database is determined to be the target database. If the execution plan corresponding to the SQL statement does not contain any slow queries, then the MySQL database is determined to be the target database.

3. The method according to claim 2, characterized in that, The execution plan corresponding to the SQL includes: the number of rows scanned; Accordingly, determining whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table includes: If the number of rows scanned is greater than the allowed number of rows scanned configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

4. The method according to claim 2, characterized in that, The execution plan corresponding to the SQL also includes: query type, access type, and query parameters; Accordingly, determining whether there is a slow query in the execution plan corresponding to the SQL based on the execution plan corresponding to the SQL and the slow query condition table includes: If at least one of the query type, the access type, and the query parameters matches the query condition corresponding to the slow query configured in the slow query condition table, then it is determined that the execution plan corresponding to the SQL has a slow query situation.

5. The method according to claim 1, characterized in that, The step of generating query text corresponding to the target database based on the search function instance object and the target database includes: If the target database is Elasticsearch, the attribute information in the search function instance is parsed using Java reflection to generate JSON query text. If the target database is MySQL, the attribute information in the search function instance is parsed using Java reflection to generate SQL query text.

6. A data query device, characterized in that, include: Acquisition module, determination module, generation module, and processing module; The acquisition module is used to acquire a query request sent by a user. The query request includes: query condition information, query result sorting rules, query result pagination information, and a query table. The query condition information includes at least one query condition, and the query result pagination information is used to indicate the recording method of the query results. The determining module is used to determine whether executing the query request in the relational database MySQL is a slow query based on the data volume corresponding to the query table and a preset data volume, wherein the preset data volume is the maximum data volume that can be called when querying MySQL; if the data volume corresponding to the query table is less than or equal to the preset data volume, then MySQL is determined to be the target database for the requested query; if the data volume corresponding to the query table is greater than the preset data volume, a structured query statement (SQL) corresponding to MySQL is generated according to the query request; the target database for the requested query is determined based on the SQL and a preset slow query condition table, wherein the slow query condition table records the correspondence between query conditions and slow queries; wherein the target database is either MySQL or a non-relational database Elasticsearch (ES), and ES synchronizes data from MySQL; The generation module is configured to: construct a general query condition object based on the query condition information; traverse the general query condition object to obtain at least one simple query instance object; generate a full-text query instance object based on the at least one simple query instance object, the query result sorting rules, and the query result pagination information; generate a search function instance object based on the query table and the full-text query instance object; and generate the query text corresponding to the target database based on the search function instance object and the target database. The processing module is used to execute the query text and obtain the query results in the target database.

7. A server, characterized in that, include: A processor, a memory, and computer program instructions stored in the memory and executable on the processor, wherein the processor, when executing the computer program instructions, implements the data query method as described in any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the data query method as described in any one of claims 1 to 5.

Images