CN-114238374-B - Data query method, device, server and storage medium

CN114238374BCN 114238374 BCN114238374 BCN 114238374BCN-114238374-B

Abstract

The application provides a data query method, a device, a server and a storage medium, wherein in the method, a query text corresponding to a target database is generated by acquiring a query request sent by a user, determining a target database 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 a query condition, and then generating a query text corresponding to the target database according to 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.

Inventors

DU DEJIN
SUN MINGKUN
CHEN ZHENG

Assignees

深圳前海微众银行股份有限公司

Dates

Publication Date: 20260512
Application Date: 20211216

Claims (8)

1. A method of querying data, comprising: Obtaining a query request sent by a user, wherein the query request comprises query condition information, a query result ordering rule, query result paging information and a query table, wherein at least one query condition exists in the query condition information, and the query result paging information is used for indicating a recording mode of the query result; Determining whether the query request is a slow query in a relational database MySQL 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; if the data magnitude corresponding to the query table is smaller than or equal to the preset data magnitude, determining the MySQL as a target database for requesting query; 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 a target database for requesting query 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 query, and the target database is any one of the MySQL and a non-relational database ES, and the ES synchronizes data in the MySQL; 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 ordering rule and the query result paging information; generating a search function instance object according to the lookup table and the full-text query instance object; generating a query text corresponding to the target database based on the search function instance object and the target database; and executing the query text to obtain a query result in the target database.
2. The method of claim 1, wherein determining the target database for the query based on the SQL and a predetermined slow query criteria table comprises: Analyzing the SQL to obtain an execution plan corresponding to the SQL; Determining whether the slow query condition exists in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table; if the slow query condition exists in the execution plan corresponding to the SQL, determining that the ES is the target database; And if the slow query condition does not exist in the execution plan corresponding to the SQL, determining the MySQL as the target database.
3. The method of claim 2, wherein the SQL corresponding execution plan comprises a scan line number; correspondingly, the determining whether the slow query condition exists in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table comprises the following steps: and if the scanning line number is greater than the allowed scanning line number configured in the slow query condition table, determining that the execution plan corresponding to the SQL has a slow query condition.
4. The method of claim 2, wherein the SQL corresponding execution plan further comprises a query type, an access type, and a query parameter; correspondingly, the determining whether the slow query condition exists in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table comprises the following steps: and if at least one of the query type, the access type and the query parameters 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 condition of slow query.
5. The method of claim 1, wherein generating query text corresponding to the target database based on the search function instance object and the target database comprises: If the target database is the ES, analyzing attribute information in the search function instance through Java reflection principle to generate Json query text; and if the target database is the MySQL, analyzing attribute information in the search function instance through Java reflection principle to generate SQL query text.
6. The data query device is characterized by comprising an acquisition module, a determination module, a generation module and a processing module; The acquisition module is used for acquiring a query request sent by a user, wherein the query request comprises query condition information, a query result ordering rule, query result paging information and a query table, wherein at least one query condition exists 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 whether the query request is a slow query in a relational database MySQL 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, determining the MySQL as a target database for requesting the query if the data magnitude corresponding to the query table is smaller than or equal to the preset data magnitude, generating a structured query statement SQL corresponding to the MySQL according to the query request if the data magnitude corresponding to the query table is larger than the preset data magnitude, determining a target database for requesting the query according to the SQL and a preset slow query condition table, and recording the corresponding relation between the query condition and the slow query in the slow query condition table, wherein the target database is any one of the MySQL and an unconnected database ES, and the ES synchronizes the data in the MySQL; The generation module is used for 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 ordering 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; and the processing module is used for executing the query text and obtaining a query result in the target database.
7. A server comprising a processor, a memory and computer program instructions stored on the memory and executable on the processor, the processor implementing the data query method of any of the preceding claims 1 to 5 when executing the computer program instructions.
8. A computer readable storage medium, characterized in that the computer readable storage medium has stored therein computer executable instructions, which when executed by a processor are adapted to implement the data query method according to any of the preceding claims 1 to 5.

Description

Data query method, device, server and storage medium Technical Field The present application relates to the technical and financial field, and in particular, to a data query method, device, server and 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 changed to the financial technology (Finteh), and massive data query technology is not exceptional, but due to the requirements of safety and instantaneity of the financial industry, the requirements of the technology are also higher. When the server relates to the storage and query service of mass data, the support of the service is mainly based on a relational database MySQL, and in the MySQL database, the single table data cannot exceed a certain amount and the index 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 problem, where the ES can support any field to create an index, various word segmenters of the ES can well support fuzzy matching queries, and support distributed deployment, and can store massive data. However, in practical use, in order to improve the query efficiency, a developer often needs to manually select a relational database or a non-relational database in a system, and write a plurality of different sets of codes to operate different databases, so that the query steps are more complicated. Disclosure of Invention The embodiment of the application relates to the field of financial science and technology (Fintech), in particular to a data query method, a data query device, a server and a storage medium, which are used for improving query efficiency during data query. In a first aspect, an embodiment of the present application provides a data query method, including: Obtaining a query request sent by a user, wherein the query request comprises query condition information, a query result ordering rule, query result paging information and a query table, wherein at least one query condition exists 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 for requesting 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 ordering 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 one possible design of the first aspect, the determining the target database for requesting the query according to the query request and basic information of the relational database MySQL includes: Determining whether the query request is executed in the MySQL 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 lookup table is smaller than or equal to the preset data magnitude, determining that the MySQL is 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 query; The target database is any one of the 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 slow query condition exists in the execution plan corresponding to the SQL according to the execution plan corresponding to the SQL and the slow query condition table; if the slow query condition exists in the execution plan corresponding to the SQL, determining that the ES is the target database; And if the slow query condition 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 comprises a scanning line number; correspondingly, the determining whether the slow query condition exists in the execution plan corresponding to the SQL according to th