CN114238374A

CN114238374A - Data query method, device, server and storage medium

Info

Publication number: CN114238374A
Application number: CN202111545327.3A
Authority: CN
Inventors: 杜德金; 孙明堃; 陈政
Original assignee: WeBank Co Ltd
Current assignee: WeBank Co Ltd
Priority date: 2021-12-16
Filing date: 2021-12-16
Publication date: 2022-03-25

Abstract

The method comprises the steps of obtaining a query request sent by a user, determining a target database requested to be queried according to the query request and basic information of a relational database MySQL, wherein the basic information is a data magnitude and a data table index corresponding to the MySQL under a query condition, then generating a query text corresponding to the target database according to the query condition information, a query result ordering rule, query result paging information, a query table and the type of the target database, and finally executing the query text to obtain a query result in the target database. The technical scheme starts from the data magnitude which can be processed by the MySQL database and the information in the query request, and improves the data query efficiency and performance.

Description

Data query method, device, server and storage medium

Technical Field

The application relates to the technical and financial field, in particular to a data query method, a data query device, a data query server and a storage medium.

Background

With the development of computer technology, more and more technologies are applied in the financial field, the traditional financial industry is gradually changing to financial technology (Finteh), mass data query technology is no exception, but higher requirements are provided for the technologies due to the requirements of the financial industry on safety and real-time performance. When a server relates to storage and query services of mass data, the support of the services is mainly based on a relational database MySQL, and in the MySQL database, single-table data cannot exceed a certain amount and indexes cannot be created infinitely, so that the conditions of low query efficiency and the like are caused.

In the prior art, an open source distributed search and data analysis Engine (ES), i.e. a non-relational database, is proposed to solve the above problems, where the ES may support any field to create an index, and various word segmenters of the ES may well support fuzzy matching query, and support distributed deployment, and may store mass data.

However, in practical use, developers often need to manually select a relational database or a non-relational database in one system in order to improve query efficiency, and also write a plurality of sets of different codes to operate different databases, so that the query procedure is more complicated.

Disclosure of Invention

The embodiment of the application relates to the field of financial technology (Fintech), in particular to a data query method, a data query device, a server and a storage medium, which are used for improving the query efficiency during data query.

In a first aspect, an embodiment of the present application provides a data query method, including:

acquiring a query request sent by a user, wherein the query request comprises: the query result sorting method comprises query condition information, query result sorting rules, query result paging information and a query table, wherein at least one query condition is contained in the query condition information, and the query result paging information is used for indicating a recording mode of the query result;

determining a target database requesting for query according to the query request and basic information of a relational database MySQL, wherein the basic information is a data magnitude and a data table index corresponding to the MySQL under the query condition;

generating a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table and the type of the target database;

and executing the query text to obtain a query result in the target database.

In a possible design of the first aspect, the determining, according to the query request and basic information of the relational database MySQL, a target database that requests a query includes:

determining whether the query request executed in the MySQL is slow query or not according to the data magnitude corresponding to the query table and a preset data magnitude, wherein the preset data magnitude is the maximum data magnitude which can be called when the MySQL is queried;

and if the data magnitude corresponding to the query table is smaller than or equal to the preset data magnitude, determining the MySQL as the target database.

If the data magnitude corresponding to the query table is larger than the preset data magnitude, generating a structured query statement SQL corresponding to the MySQL according to the query request;

determining the target database according to the SQL and a preset slow query condition table, wherein the slow query condition table records the corresponding relation between query conditions and slow queries;

the target database is any one of MySQL and a non-relational database ES, and the ES synchronizes data in the MySQL.

In this possible design, the determining the target database according to the SQL and a preset slow query condition table includes:

analyzing the SQL to obtain an execution plan corresponding to the SQL;

determining whether the execution plan corresponding to the SQL has the condition of slow query or not according to the execution plan corresponding to the SQL and the slow query condition table;

if the execution plan corresponding to the SQL has the condition of slow query, determining the ES as the target database;

and if the slow query does not exist in the execution plan corresponding to the SQL, determining the MySQL as the target database.

Optionally, the execution plan corresponding to the SQL includes: scanning line number;

correspondingly, the determining whether the execution plan corresponding to the SQL has the slow query condition according to the execution plan corresponding to the SQL and the slow query condition table includes:

if the number of scanning lines is larger than the number of allowed scanning lines configured in the slow query condition table, determining that the slow query exists in the execution plan corresponding to the SQL;

optionally, the execution plan corresponding to the SQL further includes: query type, access type and query parameters;

and if at least one of the query type, the access type and the query parameter hits the query condition corresponding to the slow query configured in the slow query condition table, determining that the execution plan corresponding to the SQL has the slow query condition.

In another possible design of the first aspect, the generating a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table, and the type of the target database includes:

constructing a general query condition object according to the query condition information;

traversing the general query condition object to obtain at least one simple query instance object;

generating a full text query instance object according to the at least one simple query instance object, the query result sorting rule and the query result paging information;

generating a search function instance object according to the query table and the full text query instance object;

and generating a query text corresponding to the target database based on the search function instance object and the target database.

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

if the target database is the ES, analyzing attribute information in the search function instance through a Java reflection principle to generate a Json query text;

and if the target database is the MySQL, analyzing the attribute information in the search function instance through a Java reflection principle to generate an SQL query text.

In a second aspect, an embodiment of the present application provides a data query apparatus, including: the device comprises an acquisition module, a determination module, a generation module and a processing module;

the obtaining module is configured to obtain a query request sent by a user, where the query request includes: the query result sorting method comprises query condition information, query result sorting rules, query result paging information and a query table, wherein at least one query condition is contained in the query condition information, and the query result paging information is used for indicating a recording mode of the query result;

the determining module is used for determining a target database which requests to be queried according to the query request and basic information of a relational database MySQL, wherein the basic information is a data magnitude and a data table index corresponding to the MySQL under the query condition;

the generating module is configured to generate a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table, and the type of the target database;

and the processing module is used for executing the query text and obtaining a query result in the target database.

In a possible design of the second aspect, the determining module is specifically configured to:

In this possible design, the determining module determines the target database according to the SQL and a preset slow query condition table, and is specifically configured to:

analyzing the SQL to obtain an execution plan corresponding to the SQL;

correspondingly, the determining module determines whether the execution plan corresponding to the SQL has a slow query condition according to the execution plan corresponding to the SQL and the slow query condition table, and is specifically configured to:

In another possible design of the second aspect, the generating module is specifically configured to:

In this possible design, the generating module generates, based on the search function instance object and the type of the target database, a query text corresponding to the target database, and is specifically configured to:

In a third aspect, an embodiment of the present application provides a server, including: a processor, a memory;

the memory stores computer-executable instructions;

the processor executes the computer executable instructions to cause the computer server to perform the data query method as described in the first aspect and various possible designs above.

In a fourth aspect, embodiments of the present application provide a computer-readable storage medium, in which computer-executable instructions are stored, and when the computer-executable instructions are executed by a processor, the computer-readable storage medium is configured to implement the data query method as described in the first aspect and various possible designs.

In a fifth aspect, embodiments of the present application provide a computer program product, which includes a computer program, and when the computer program is executed by a processor, the computer program is used to implement the data query method as described in the first aspect and various possible designs.

According to the data query method, the data query device, the server and the storage medium, a query request sent by a user is obtained, a target database requested to be queried is determined according to the query request and basic information of a relational database MySQL, the basic information is a data magnitude and a data table index corresponding to the MySQL under a query condition, then a query text corresponding to the target database is generated according to the query condition information, a query result ordering rule, query result paging information, the types of the query table and the target database, and finally the query text is executed to obtain a query result in the target database. The technical scheme starts from the data magnitude which can be processed by the MySQL database and the information in the query request, and improves the data query efficiency and performance.

Drawings

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

Fig. 1 is a schematic view of an application scenario of a data query method according to an embodiment of the present application;

fig. 2 is a schematic diagram of an architecture of a data query method according to an embodiment of the present application;

fig. 3 is a schematic flowchart of a first embodiment of a data query method according to an embodiment of the present application;

fig. 4 is a schematic flowchart of a second embodiment of a data query method according to the present application;

fig. 5 is a schematic flowchart of a third embodiment of a data query method according to the present application;

fig. 6 is a schematic flowchart of a fourth embodiment of a data query method according to the present application;

fig. 7 is a schematic flowchart of a fifth embodiment of a data query method according to an embodiment of the present application;

fig. 8 is a schematic structural diagram of a data query device according to an embodiment of the present application;

fig. 9 is a schematic structural diagram of a server according to an embodiment of the present application.

With the foregoing drawings in mind, certain embodiments of the disclosure have been shown and described in more detail below. These drawings and written description are not intended to limit the scope of the disclosed concepts in any way, but rather to illustrate the concepts of the disclosure to those skilled in the art by reference to specific embodiments.

Detailed Description

Reference will now be made in detail to the exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, like numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the exemplary embodiments below are not intended to represent all implementations consistent with the present disclosure. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present disclosure, as detailed in the appended claims.

Before introducing the embodiments of the present application, the background art to which the present application relates will be explained first:

with the increasing number of network users and the continuous development of network technologies, the background of the server often needs to store and query massive data.

The conventional data storage uses the relational database MySQL, and for some requests requiring multi-condition query retrieval, such as historical data query, pipeline query, etc., when the relational database of MySQL is used, the following problems easily occur:

firstly, when the MySQL single table data exceeds a certain amount, the operation performance of Structured Query Language (SQL) is sharply reduced;

secondly, because the index of MySQL cannot be created infinitely, when the data to be accessed exceeds a certain amount, the query efficiency without the index is low, and the fuzzy query of MySQL cannot go through the index, so that the query performance is very low.

Therefore, the prior art provides a distributed, high-expansion, high-real-time search and data analysis Engine (ES), i.e., a non-relational database, which can support any field to create an index, and various word segmenters of the ES can well support fuzzy matching query, and support distributed deployment, and can store mass data, so that the ES is selected for use, and query and search of mass data can be efficiently realized.

However, in some scenarios, when a server receives a certain query request, it is more efficient to specifically query by using ES or MySQL, and further determination is needed, and when querying different databases, corresponding Application Programming Interfaces (APIs) are different, and developers need to learn, which is more repetitive physical activity and low efficiency. At present, although frameworks similar to MyBatis, Hibernate and the like integrate a plurality of databases, the frameworks only support relational databases and do not support non-relational databases, and cannot provide more efficient data query services.

Based on the problems in the prior art, fig. 1 is a schematic view of an application scenario of a data query method provided in an embodiment of the present application, so as to solve the technical problems. As shown in fig. 1, the application scenario diagram includes: terminal equipment 11, server 12.

The database corresponding to the server 12 may include MySQL 121 and ES 122, and relevant data of MySQL 121 is synchronized in the ES 122.

Optionally, the terminal device 11 may be a mobile phone, a computer, a tablet, or the like, and the server 12 is configured to provide a service for the terminal device 11.

In a possible implementation, a user sends a query request to the server 12 through the terminal device 11 for requesting related data, after receiving the query request from the terminal device 11, the server 12 determines, based on the query request and related information of MySQL 121, which database is more efficient to query in MySQL 121 and ES 122 (taking ES 122 as an example), based on which, a query text capable of executing ES 122 is generated according to the query request and ES 122, and the server 12 obtains the related data according to the query text.

It is understood that no details of this implementation are disclosed in the examples below.

In order to solve the technical problems, the technical conception process of the inventor is as follows: in the prior art, a proper database needs to be manually selected to avoid the low query efficiency caused by slow query, if the query can be determined to be slow query by using the comparison of the query table and the maximum data magnitude that can be called in MySQL, a proper database is selected, and then a query text corresponding to the database that needs to be called is generated according to the information such as the query condition in the query request, so that the server executes the query text, and the result that needs to be queried can be efficiently obtained.

The technical solution of the present application is described in detail below with reference to an application scenario diagram shown in fig. 1 by specific embodiments. It should be noted that the following specific embodiments may be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments. Embodiments of the present application will be described below with reference to the accompanying drawings.

First, the architecture provided by the embodiment of the present application is introduced, and fig. 2 is a schematic architecture diagram of a data query method provided by the embodiment of the present application, as shown in fig. 2, an interface layer 21, a parser 22, an executor 23, and a database set 24.

The database set 24 includes MySQL 121 and ES 122 shown in fig. 1.

Optionally, the architecture of the data query method may be based on a custom operating multi-database persistent layer framework WeBatis.

Specifically, the interface layer 21 may be a WeBatis interface layer, and is configured to receive data query services, including ObjExample and ApiInterface, where ObjExample is a core data operation unified object, and ApiInterface is an operation unified interface, and shields a difference between a relational database and a non-relational database.

The Parser 22 may be a WeBatis Parser, including MySQL Parser and ES Parser, and parses the different types of databases into their corresponding operation objects, and since the parsing process needs to determine what database needs to be used in the current scenario, the Parser 22 further includes an index configuration center IndexConfig. When the existing plug-ins do not meet the requirements of developers, the developers can develop customized plug-ins to analyze the corresponding database operations.

The Executor 23 may be a WeBatis Executor, which has a database proxy factory Db proxyfactor, and supports configuration and connection of various databases, and the Executor 23 further includes a MySQL Executor and an ES Executor, which respectively support configuration and connection of their respective databases, take the operation object parsed by the parser 22, and then use the corresponding database operation object to actually operate the database (e.g., ES 122). Like parser 22, a developer can develop custom plug-ins to perform their corresponding database operations if existing plug-ins do not meet the developer's requirements.

It should be understood that ObjExampe is a query condition object common to multiple databases in WeBatis; ObjParser is an object which is resolved into different database operations by WeBatis; ObjExecutor is the object of WeBatis to perform different database operations.

It should be noted that the database type in the embodiment of the present application may also be other types than MySQL and ES, and in a specific implementation, the data query method according to the embodiment of the present application may be used.

On the basis of the architecture shown in fig. 2, fig. 3 is a schematic flowchart of a first embodiment of a data query method provided in the present application, and as shown in fig. 3, the data query method includes:

and step 31, acquiring the query request sent by the user.

Wherein, the parameters in the query request include: the query result Page information comprises query condition information ConditionList, a query result sorting rule OrderByClause, query result paging information Page and a query table, wherein at least one query condition is arranged in the query condition information, and the query result paging information is used for indicating the recording mode of the query result.

In this step, the user sends a data query request to the server through the terminal device, and the server receives the query request sent by the terminal device of the user, specifically, the query request includes a condition of query data, how to arrange the queried result, and a record of the result displayed on a page.

In a possible implementation, in an inquiry request sent by a user through a terminal device, information of users with ages greater than 50 (one of inquiry conditions) needs to be inquired, inquiry results are arranged from large to small according to the ages (inquiry result paging information), and specifically, 35 pieces of inquiry result paging information are displayed on each page.

In this step, after receiving the query request, it is further required to perform validity verification on the parameters in the query request, for example, whether an account corresponding to the query request is authorized or not.

In a possible implementation, for an example of validity verification, such as querying information of a user with an identity number of 62589665242, but the default identity number of an actual system is 18 bits, the validity verification fails, and an error report may be returned to the terminal device of the user.

Optionally, the query request at least carries a query table.

Further, step 32 may be entered after the verification passes.

And step 32, determining a target database which is requested to be queried according to the query request and the basic information of the relational database MySQL.

The basic information is data magnitude and data table index corresponding to MySQL under the query condition, the target database is any one of MySQL and a non-relational database ES, and the ES is synchronized with data in MySQL.

Before the step, MySQL acquires data in real time and continuously stores the data in a cache path of the MySQL, and the data in ES can acquire the data in MySQL through the Bilog, so that on one hand, the Bilog data is analyzed and cached in ES, and the ES synchronizes the data of MySQL; on the other hand, by analyzing the Binlog data, the total recorded quantity magnitude of the current data is saved, and a basis is provided for subsequently determining the target database.

In this step, for MySQL queries, if there is no index and the amount of data is large, the query performance is poor.

Specifically, whether the DATA magnitude of the current table exceeds the configured maximum callable DATA magnitude or not is judged, and a value BIG _ DATA of slow query does not appear, if the DATA magnitude of the current table does not exceed the configured maximum callable DATA magnitude, the DATA volume is not large, no matter whether the index is hit or not, the query performance cannot be influenced, and the MySQL query is directly selected at this moment.

Further, if the query exceeds the BIG _ DATA, a structured query statement SQL for querying MySQL needs to be generated, then the SQL execution plan is analyzed, whether slow query occurs in the current query is judged according to the analysis result, if no slow query occurs, the target database is determined to be MySQL, and if not, the target database is determined to be ES.

And step 33, generating a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table and the type of the target database.

In this step, the type (MySQL or ES) of the target database is determined, and at this time, the query condition information ConditionList, the query result sorting rule orderbyclass, the query result paging information Page, and the query table are analyzed according to the query condition information, so as to generate a query text capable of operating the specific target database.

In a possible implementation, the step may be implemented by objextract and ObjParser in the parser, specifically, the query condition information is parsed to generate a general query condition object objextract, then ObjParser parses the general query condition object, and adds a query result sorting rule, query result paging information, and a query table to convert into a search function instance object SearchRequest.

Further, the search function instance object SearchRequest is converted into a specific query text according to the type of the target database.

Optionally, if the target database is an ES, generating a Json query text; and if the target database is MySQL, generating an SQL query text.

And step 34, executing the query text to obtain a query result in the target database.

In this step, the query text generated by the parser is transmitted to the executor, the executor proxies the connection access connection of MySQL and ES when the system of the server is initialized, and selects different objexecutors to execute the query operation according to the database of the identifier of the query text, so as to obtain the query result of the data.

In one possible implementation, when the query text is an SQL query text, a MySQL Executor Executor is called to execute related query operations in SQL; and when the query text is the ES query text, calling an ES executer to execute related query operations in the ES.

After that, the query result is returned to the terminal device of the user.

That is, the query request sent to the server by the terminal device obtains the query result after the server responds, and returns the query result to the terminal device of the user.

In a possible implementation, the query result is suspicious and displayed on a graphical user interface of the terminal device, employees with the query request as payroll exceeding 5000 are sorted from small to large according to the amount, the number of the displayed employees is 30, and the query result displayed on the graphical user interface can be one page, 25 pages in total, and the like.

It should be understood that the examples described in this application are only examples, and do not represent implementations in real scenarios.

According to the data query method provided by the embodiment of the application, a query request sent by a user is obtained, a target database requesting for query is determined according to the query request and basic information of a relational database MySQL, the basic information is a data magnitude and a data table index corresponding to the MySQL under a query condition, the target database is any one of the MySQL and a non-relational database ES, the ES is synchronous with data in the MySQL, then a query text corresponding to the target database is generated according to the query condition information, a query result sorting rule, query result paging information, the types of the query table and the target database, and finally the query text is executed to obtain a query result in the target database. The technical scheme starts from the data magnitude which can be processed by the MySQL database and the information in the query request, and improves the data query efficiency and performance.

On the basis of the foregoing embodiment, fig. 4 is a schematic flowchart of a second embodiment of a data query method provided in the embodiment of the present application, and as shown in fig. 4, the foregoing step 22 may be implemented by the following steps:

and step 41, determining whether the query request executed in the MySQL is slow query or not according to the data magnitude corresponding to the query table and the preset data magnitude.

The preset data magnitude is the maximum data magnitude which can be called when the MySQL is inquired.

In this step, the DATA magnitude corresponding to the lookup table is also the DATA magnitude that needs to be queried in response to the query request sent by the user, and the preset DATA magnitude is the maximum DATA magnitude that can be called when querying MySQL, that is, the value BIG _ DATA of slow query does not appear.

Optionally, the data magnitude of MySQL is queried according to the data table index of MySQL.

Optionally, when the data magnitude corresponding to the lookup table is less than or equal to the preset data magnitude, the data magnitude is considered to be not large when the MySQL is queried, and no matter whether the index is hit or not, the query performance is not affected, and it is determined that the query request executed in the MySQL is not slow query.

Optionally, when the data magnitude corresponding to the lookup table is greater than the preset data magnitude, it is further determined whether the query in querying MySQL is a slow query.

And 42, if the data magnitude corresponding to the query table is smaller than or equal to the preset data magnitude, determining MySQL as a target database.

In this step, when the data magnitude corresponding to the lookup table is less than or equal to the preset data magnitude, the data size is considered to be not large when querying the MySQL, and the query performance is not affected, and it can be considered that the later execution process is not slow query when the query request hits the MySQL.

Further, MySQL is determined as a target database to execute the implementation process of subsequently generating the query text.

And 43, if the data magnitude corresponding to the query table is larger than the preset data magnitude, generating the SQL corresponding to the MySQL according to the query request.

In this step, when the data magnitude corresponding to the lookup table is greater than the preset data magnitude, the SQL corresponding to the MySQL needs to be generated at this time, and whether slow query exists in the SQL is further determined.

Optionally, the SQL corresponding to the MySQL is generated according to the parameters in the query request.

And step 44, determining the target database according to the SQL and a preset slow query condition table.

Wherein, the slow query condition table records the corresponding relationship between the query condition and the slow query.

In this step, the explain analyzes the execution plan of the SQL, and the analysis result includes a query type select _ type, an access type, an estimated scanning line rows, and extra information extra (query parameters), and further compares the analysis result with the slow query condition table to determine the target database, where table 1 is the slow query condition table provided in this embodiment, and shows a corresponding relationship between a common query condition and a slow query.

Table 1:

optionally, when the execution plan corresponding to the SQL includes: and when the number of scanning lines is greater than the allowed number of scanning lines configured in the slow query condition table, determining that the slow query exists in the execution plan corresponding to the SQL.

Optionally, when the execution plan corresponding to the SQL further includes: when at least one of the query type, the access type and the query parameter hits a query condition corresponding to the slow query configured in the slow query condition table, determining that the execution plan corresponding to the SQL has the slow query condition.

Specifically, with reference to table 1, the implementation of the above example may include the following steps:

step 1, analyzing SQL to obtain an execution plan corresponding to the SQL, wherein the execution plan corresponding to the SQL comprises the following steps: query type, access type, number of scan lines, and query parameters.

Optionally, in order to ensure that slow query is avoided in MySQL, the returned results of the four fields, i.e., select _ type, rows, and Extra, are combined, that is, SQL is analyzed, so as to obtain an execution plan corresponding to SQL.

And step 2, determining whether the execution plan corresponding to the SQL has the condition of slow query according to the execution plan corresponding to the SQL and the slow query condition table.

Optionally, according to an execution plan corresponding to SQL, in table 1, it is determined whether a slow query condition exists.

And 3, if the slow query exists in the execution plan corresponding to the SQL, determining the ES as the target database.

As an example, when the execution plan corresponding to SQL is UNION | ALL | Using fileshort query combination, the query performance of MySQL is greatly reduced, and there is a slow query condition, that is, it is determined that ES is the target database.

As another example, when the execution plan corresponding to SQL is UNION | ref | Using fileport query combination, where UNION and Using fileport are slow queries, it is determined that ES is the target database.

As yet another example, rows must be smaller than the number of scan lines allowed by externally configured tables, otherwise, if slow query is still possible, ES is determined to be the target database.

And 4, if the slow query does not exist in the execution plan corresponding to the SQL, determining the MySQL as the target database.

As an example, when rows is smaller than the number of scanning lines allowed by each externally configured table, and the execution plan corresponding to SQL is a simple | ref | Using index query combination, MySQL is determined as the target database.

As another example, when rows is smaller than the number of scanning rows allowed by each externally configured table, and the execution plan corresponding to SQL is simple | eq _ ref | Using index query combination, MySQL is determined as the target database.

According to the data query method provided by the embodiment of the application, whether the query request executed in the MySQL is slow query is determined according to the data magnitude and the preset data magnitude corresponding to the query table, then if the data magnitude corresponding to the query table is smaller than or equal to the preset data magnitude, the MySQL is determined as the target database, if the data magnitude corresponding to the query table is larger than the preset data magnitude, a structured query statement SQL corresponding to the MySQL is generated according to the query request, and finally the target database is determined according to the SQL and the preset slow query condition table. The scheme provides a basis for follow-up more efficient data query from the judgment of slow query.

On the basis of the foregoing embodiment, fig. 5 is a schematic flowchart of a third embodiment of a data query method provided in the embodiment of the present application, and as shown in fig. 5, the foregoing step 23 may be implemented by the following steps:

and step 51, constructing a general query condition object according to the query condition information.

In this step, after the validity of the query request is verified, the query condition information in the query request is analyzed to construct a universal query condition object ObjExample.

The core parameters of the universal query condition object are ConditionList, OrderByClause and Page, and mainly control query condition information, query result sorting rules and query result paging information, namely query conditions, sorting rules of query results, recording modes and the like.

Step 52, traversing the general query condition object to obtain at least one simple query instance object.

In this step, the condition list is circulated, all conditions, value1 and value2 in the general query condition object are taken out, and at least one simple query instance object query builder is constructed, wherein the conditions are the types of the query conditions.

In the examples of the present application, common types of conditions are: whether it is null (IsNull, IsNotNull), whether it is equal (EqualTo, NotEqualTo), greater than less than (GreaterThan, LessThan), fuzzy matching (Like, NotLike), and range (In, Between), etc., different condition type conditions may be combined to convert into corresponding different simple query instance objects QueryBuider.

Specifically, based on the responsibility chain mode, the condition object can be circularly transmitted into the ObjParser each time, which type of query builder instance needs to be generated is determined according to the operation attribute of the condition object, each type of query builder instance can only identify and process the operation which can be processed by itself, the instantiated object is returned if the generation is successful, and if the processing is not successful, the instantiated object is transmitted to the next step for processing.

Further, based on the example, different conditions of external request transmission are realized, the internal processing of the server is a uniform flow, the coupling between the request condition and the final query builder is reduced, the condition is added or deleted or the sequence is changed, the instantiated logic does not need to be changed, the logic is very flexible, the code does not need too many if … else …, and a fussy execution process is avoided.

Alternatively, the different simple query instance objects QueryBuider may be bootquerybuider, termquerybuider, rangequerybuider, WildcardQuery Builder, existquerybuider, and the like.

And 53, generating a full text query instance object according to at least one simple query instance object, a query result sorting rule and query result paging information.

In the step, BoolQueryBuilder connects QueryBuilder generated each time in the cycle through must, mustNot, filter and should to form a complex query condition, after the condition cycle is finished, ObjParser takes OrderByClause and Page attribute in ObjExample out, sets a query result sorting rule and query result paging information, and generates at least one simple query instance object QueryBuider to generate SearchSource.

A Builder object.

Optionally, the query result sorting rule and the query result paging information may include: setting pagination, setting sequencing, setting query timeout time and the like.

Step 54, generating a search function instance object according to the query table and the full text query instance object.

In this step, a final search function instance object SearchRequest is generated according to the full text query instance object SearchSourceBuilder and the set query table.

Optionally, the searching for the function instance object includes: and setting a query table and setting a full text query instance object.

And step 55, generating a query text corresponding to the target database based on the search function instance object and the target database.

In this step, the attributes in the search function instance object are processed according to the type corresponding to the target database used by the previous calculation of the query request, so as to obtain a query text corresponding to the operable target database.

In a possible implementation, if the target database is ES, analyzing attribute information in a search function instance through a Java reflection principle to generate a Json query text.

In another possible implementation, if the target database is MySQL, the attribute information in the search function instance is analyzed by the Java reflection principle to generate an SQL query text.

According to the data query method provided by the embodiment of the application, the general query condition object is constructed according to the query condition information, the general query condition object is traversed to obtain at least one simple query instance object, the full text query instance object is generated according to the at least one simple query instance object, the query result sorting rule and the query result paging information, then the search function instance object is generated according to the query table and the full text query instance object, and finally the query text corresponding to the target database is generated based on the search function instance object and the target database.

On the basis of the foregoing embodiment, fig. 6 is a schematic flowchart of a fourth embodiment of a data query method provided in the embodiment of the present application, and as shown in fig. 6, a specific example of the data query method may be as follows:

step 1, acquiring a query request sent by a user;

step 2, determining the legality of the parameters in the query request;

step 3, looking up the index field of the current object MySQL;

step 4, inquiring the data magnitude of the current object;

step 5, whether the data magnitude is larger than the preset data magnitude or not is judged, and if yes, the step 6 is executed; if not, executing the step 9;

step 6, generating SQL according to the query request;

step 7, obtaining a slow query result according to the SQL and the slow query condition table;

step 8, judging whether the slow query result is slow query or not, if so, executing step 10; if not, executing the step 9;

step 9, selecting MySQL;

and step 10, selecting an ES.

According to the data query method provided by the embodiment of the application, the query request sent by the user is obtained, the validity of the parameters in the query request is determined, the index field of the MySQL of the current object is consulted to query the data magnitude of the current object, when the data magnitude is larger than the preset data magnitude, the SQL is generated according to the query request, the slow query result is obtained according to the SQL and the slow query condition table, when the slow query result is slow query, the database is determined to be selected as ES, and the MySQL is selected under other conditions. In the technical scheme, a foundation is provided for efficient data query realization.

On the basis of the foregoing embodiment, fig. 7 is a schematic flowchart of a fifth embodiment of a data query method provided in the embodiment of the present application, and as shown in fig. 7, a specific example of the data query method may be as follows:

step 1, obtaining conditions and values corresponding to each condition according to query condition information;

step 2, respectively constructing simple query instance objects according to the condition list and the conditions and values corresponding to the conditions;

step 3, generating a complex query instance object according to all the simple query instance objects;

step 4, generating a full text query instance object according to the complex query instance object, the query result sorting rule and the query result paging information;

step 5, generating a search function instance object according to the query table and the full text query instance object;

step 6, judging the type of the target database, if so, executing step 7; if yes, executing step 8;

step 7, generating a Json query text according to the search function instance object, and executing step 9;

step 8, generating an SQL query text according to the search function instance object;

step 9, executing the query text;

and step 10, returning a query result.

According to the data query method provided by the embodiment of the application, conditions and values corresponding to each condition are obtained according to query condition information, then simple query example objects are respectively constructed according to a condition list and the conditions and values corresponding to each condition, complex query example objects are generated according to all the simple query example objects, then full-text query example objects are generated according to the complex query example objects, query result sorting rules and query result paging information, then search function example objects are generated according to the query list and the full-text query example objects, Json query texts or SQL query texts are generated according to the search function example objects based on types of different databases, and then more efficient data query is realized according to the query texts, so that overall planning among the databases is also realized.

On the basis of the foregoing method embodiment, fig. 8 is a schematic structural diagram of a data query apparatus provided in the embodiment of the present application, and as shown in fig. 8, the apparatus includes: an acquisition module 81, a determination module 82, a generation module 83 and a processing module 84;

an obtaining module 81, configured to obtain an inquiry request sent by a user, where the inquiry request includes: the query result sorting method comprises query condition information, query result sorting rules, query result paging information and a query table, wherein at least one query condition is contained in the query condition information, and the query result paging information is used for indicating a recording mode of a query result;

the determining module 82 is configured to determine a target database requested to be queried according to the query request and basic information of the relational database MySQL, where the basic information is a data magnitude and a data table index corresponding to MySQL under a query condition;

the generating module 83 is configured to generate a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table, and the type of the target database;

and the processing module 84 is used for executing the query text to obtain a query result in the target database.

In one possible design of the embodiment of the present application, the determining module 82 is specifically configured to:

and if the data magnitude corresponding to the query table is less than or equal to the preset data magnitude, determining the MySQL as the target database.

determining a target database according to SQL and a preset slow query condition table, wherein the slow query condition table records the corresponding relation between query conditions and slow queries;

the target database is MySQL or a non-relational database ES, and the ES synchronizes data in MySQL.

In this possible design, the determining module 82 determines the target database according to SQL and a preset slow query condition table, and is specifically configured to:

analyzing SQL to obtain an execution plan corresponding to the SQL, wherein the execution plan corresponding to the SQL comprises the following steps: query type, access type, scanning line number and query parameter;

if the execution plan corresponding to the SQL has the condition of slow query, determining the ES as a target database;

correspondingly, the determining module 82 determines whether the slow query exists in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table, and is specifically configured to:

if the number of scanning lines is larger than the number of allowed scanning lines configured in the slow query condition table, determining that the slow query condition exists in the execution plan corresponding to the SQL;

optionally, the execution plan corresponding to SQL further includes: query type, access type and query parameters;

and if at least one item of the query type, the access type and the query parameter hits the query condition corresponding to the slow query configured in the slow query condition table, determining that the slow query exists in the execution plan corresponding to the SQL.

Optionally, the determining module 82 is further configured to:

if the query execution request in MySQL is a slow query, determining ES as the target database.

In another possible design of the embodiment of the present application, the generating module 83 is specifically configured to:

traversing the general query condition object to obtain at least one simple query instance object, wherein a plurality of different query conditions are recorded in the condition list;

generating a full text query instance object according to at least one simple query instance object, a query result sorting rule and query result paging information;

In this possible design, the generating module 83 generates a query text corresponding to the target database based on the types of the search function instance object and the target database, and is specifically configured to:

if the target database is ES, analyzing attribute information in the search function instance through a Java reflection principle to generate a Json query text;

and if the target database is MySQL, analyzing the attribute information in the search function instance through a Java reflection principle to generate an SQL query text.

The data query device provided in the embodiment of the present application may be used to execute the technical solutions corresponding to the data query method in the embodiments, and the implementation principle and the technical effect are similar, which are not described herein again.

It should be noted that the division of the modules of the above apparatus is only a logical division, and the actual implementation may be wholly or partially integrated into one physical entity, or may be physically separated. And these modules can be realized in the form of software called by processing element; or may be implemented entirely in hardware; and part of the modules can be realized in the form of calling software by the processing element, and part of the modules can be realized in the form of hardware. In addition, all or part of the modules can be integrated together or can be independently realized. The processing element described herein may be an integrated circuit having signal processing capabilities. In implementation, each step of the above method or each module above may be implemented by an integrated logic circuit of hardware in a processor element or an instruction in the form of software.

Fig. 9 is a schematic structural diagram of a server according to an embodiment of the present application. As shown in fig. 9, the server may include: a processor 90, a memory 91, and computer program instructions stored on the memory 91 and executable on the processor 90.

The processor 90 executes computer-executable instructions stored by the memory 91 to cause the processor 90 to perform the aspects of the embodiments described above. The processor 90 may be a general-purpose processor including a central processing unit CPU, a Network Processor (NP), and the like; but also a digital signal processor DSP, an application specific integrated circuit ASIC, a field programmable gate array FPGA or other programmable logic device, discrete gate or transistor logic, discrete hardware components.

Optionally, the server may further include: a transceiver 92.

A memory 91 and a transceiver 92 are coupled to the processor 90 via the system bus and communicate with each other, the memory 91 storing computer program instructions.

The transceiver 92 is used for communication with other servers, and the transceiver 92 constitutes a communication interface.

Optionally, in terms of hardware implementation, the obtaining module 81 in the embodiment shown in fig. 8 corresponds to the transceiver 92 in this embodiment.

The system bus may be a Peripheral Component Interconnect (PCI) bus, an Extended Industry Standard Architecture (EISA) bus, or the like. The system bus may be divided into an address bus, a data bus, a control bus, and the like. For ease of illustration, only one thick line is shown, but this does not mean that there is only one bus or one type of bus.

The server provided in the embodiment of the present application may be used to execute the technical solution corresponding to the data query method in the above embodiments, and the implementation principle and the technical effect are similar, which are not described herein again.

The embodiment of the application also provides a chip for running the instruction, and the chip is used for executing the technical scheme of the data query method in the embodiment.

The embodiment of the present application further provides a computer-readable storage medium, where a computer instruction is stored in the computer-readable storage medium, and when the computer instruction runs on a server, the server is enabled to execute the technical solution of the data query method in the foregoing embodiment.

The embodiment of the present application further provides a computer program product, which includes a computer program, and the computer program is used for executing the technical solution of the data query method in the above embodiments when being executed by a processor.

The computer-readable storage medium described above may be implemented by any type of volatile or non-volatile storage server or 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 memory, flash memory, magnetic or optical disk. Readable storage media can be any available media that can be accessed by a general purpose or special purpose computer server.

It will be understood that the present disclosure is not limited to the precise arrangements described above and shown in the drawings and that various modifications and changes may be made without departing from the scope thereof. The scope of the present disclosure is limited only by the appended claims.

Claims

1. a data query method, is characterized in that, comprises:

Obtain a query request sent by a user, where the query request includes: query condition information, query result sorting rules, query result paging information, and a query table, the query condition information has at least one query condition, and the query result paging information is used for indicating how the query results are recorded;

According to the query request and the basic information of the relational database MySQL, determine the target database of the query request, and the basic information is the data magnitude and the data table index corresponding to the MySQL under the query condition;

Execute the query text to obtain query results in the target database.

2. method according to claim 1, is characterized in that, described according to the basic information of described query request and relational database MySQL, determine the target database of request query, comprising:

Determine whether the query request executed in the MySQL is a slow query according to the data magnitude corresponding to the query table and a preset data magnitude, and the preset data magnitude is a value that can be called when querying the MySQL maximum data level;

If the data magnitude corresponding to the query table is less than or equal to the preset data magnitude, then determine that the MySQL is the target database;

If the data magnitude corresponding to the query table is greater than the preset data magnitude, generate a structured query statement SQL corresponding to the MySQL according to the query request;

Determine the target database according to the SQL and a preset slow query condition table, where the slow query condition table records the correspondence between the query conditions and the slow query;

Wherein, the target database is any one of the MySQL and the non-relational database ES, and the ES synchronizes the data in the MySQL.

3. The method according to claim 2, wherein, determining the target database according to the SQL and a preset slow query condition table, comprising:

Parse the SQL to obtain an execution plan corresponding to the SQL;

According to 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 there is a situation of slow query in the execution plan corresponding to the SQL, then determine that the ES is the target database;

If there is no slow query in the execution plan corresponding to the SQL, it is determined that the MySQL is the target database.

4. The method according to claim 3, wherein the execution plan corresponding to the SQL comprises: scanning the number of rows;

Correspondingly, determining whether there is a slow query in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table includes:

If the number of scan rows is greater than the allowable number of scan rows configured in the slow query condition table, it is determined that there is a slow query in the execution plan corresponding to the SQL.

5. The method according to claim 3, wherein the execution plan corresponding to the SQL further comprises: a query type, an access type and a query parameter;

If at least one of the query type, the access type, and the query parameter hits the query condition corresponding to the slow query configured in the slow query condition table, it is determined that the execution plan corresponding to the SQL has a slow query condition. Happening.

6. The method according to claim 2 or 3, characterized in that, according to the query condition information, the query result sorting rule, the query result paging information, the query table and the target database Type, generate the query text corresponding to the target database, including:

According to the query condition information, construct a general query condition object;

Traverse the general query condition object to obtain at least one simple query instance object;

generating a full-text query instance object according to the at least one simple query instance object, the query result sorting rule, and the query result paging information;

generating a search function instance object according to the query table and the full-text query instance object;

Based on the search function instance object and the target database, a query text corresponding to the target database is generated.

7. The method according to claim 6, wherein generating a query text corresponding to the target database based on the search function instance object and the type of the target database, comprising:

If the target database is the ES, analyze the attribute information in the search function instance through the Java reflection principle, and generate a Json query text;

If the target database is the MySQL, the attribute information in the search function instance is parsed through the Java reflection principle to generate SQL query text.

8. A data query device, comprising: an acquisition module, a determination module, a generation module and a processing module;

The obtaining module is configured to obtain a query request sent by a user, where the query request includes: query condition information, query result sorting rules, query result paging information, and a query table, and the query condition information includes at least one query condition, and the The query result paging information is used to indicate the record mode of the query result;

The determining module is used to determine the target database to be queried according to the query request and the basic information of the relational database MySQL, where the basic information is the data magnitude and data table index corresponding to the MySQL under the query conditions ;

The generating module is configured to generate a query text corresponding to the target database according to the query condition information, the query result sorting rule, the query result paging information, the query table and the type of the target database;

The processing module is configured to execute the query text and obtain query results in the target database.

9. A server, characterized by comprising: a processor, a memory and computer program instructions stored on the memory and executable on the processor, the processor implementing the above-mentioned rights when executing the computer program instructions The data query method described in any one of requirements 1 to 7 is required.

10. A computer-readable storage medium, characterized in that, computer-executable instructions are stored in the computer-readable storage medium, and when the computer-executable instructions are executed by a processor, the computer-executable instructions are used to implement any one of claims 1 to 7 above. The data query method described in item.