Query method, device and storage medium based on structured query language SQL

Abstract

The present application discloses a query method, device and storage medium based on structured query language SQL. The method includes: obtaining native SQL, wherein native SQL is used for data query; parsing native SQL to extract core query content from native SQL; determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if there is a record, adjusting the query to the intermediate table query SQL based on native SQL to obtain the query result. Through the present application, the problem of low query efficiency of SQL in the related technology due to long development time and long adjustment cycle in the data query process is solved.

Description

Technical Field

[0001] The present application relates to the field of data query, and in particular to a query method, device and storage medium based on structured query language SQL. Background Art

[0002] In data analysis and intelligent decision-making systems, reports are the most common and widely used application form. When users access reports, the system directly connects to the database to execute query SQL, which often results in time-consuming operations such as aggregation and table joins, resulting in longer query times and poor user experience.

[0003] In the existing solutions, the cache-based solution caches the results of each query to reduce the number of database queries, thereby saving data return time. However, the cache space is limited. The query results will change if one factor of the same report changes, such as: query conditions change, or paging query situation. Reports rely on hit rates, and if cached data is not hit, SQL queries are not improved. In addition, the industry sometimes relies on manual optimization to design some intermediate tables to optimize query SQL, which has problems such as long development time, long adjustment cycle, and low efficiency.

[0004] With regard to the problem of low query efficiency of SQL in related technologies, no effective solution has been proposed yet. Summary of the invention

[0005] The main purpose of this application is to provide a query method, device and storage medium based on structured query language SQL to solve the problem of low query efficiency of SQL due to long development time and long adjustment cycle in the data query process in the related technology.

[0006] In order to achieve the above-mentioned purpose, according to one aspect of the present application, a query method based on structured query language SQL is provided. The method includes: obtaining native SQL, wherein native SQL is used for data query; parsing the native SQL, extracting the core query content from the native SQL; determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if there is a record, adjusting the query to the intermediate table query SQL based on the native SQL to obtain the query result.

[0007] Furthermore, before determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table, the method also includes: obtaining query records of accessing the original table query within a preset time and the query duration exceeding a threshold; extracting SQL content from the query records; performing syntax parsing on each SQL content respectively, extracting the core query content and additional condition content corresponding to each SQL content; and creating an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content.

[0008] Furthermore, based on the core query content and additional condition content corresponding to each SQL content, creating an intermediate table corresponding to each SQL content includes: creating a corresponding intermediate table according to the core query content corresponding to each SQL content; and adding an index to the created intermediate table based on the additional condition content.

[0009] Furthermore, after creating the intermediate table corresponding to each SQL content, the method also includes: recording the information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information includes at least: the intermediate table name, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table and the additional condition content corresponding to the intermediate table.

[0010] Further, the intermediate table to be evaluated is determined from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table in a newly created state; a native SQL query statement corresponding to the intermediate table to be evaluated is obtained; a query time of the native SQL query statement in the original table query is obtained to obtain a query time one; the intermediate table to be evaluated is replaced with the core query content in the native SQL query statement to obtain an adjusted sample statement; the adjusted sample statement is executed to obtain the query time to obtain a query time two; and the intermediate table to be evaluated is evaluated based on the query time one and the query time two.

[0011] Furthermore, evaluating the intermediate table to be evaluated based on query time one and query time two includes: if query time two is less than a preset ratio of query time one; determining that the optimization of query time by the intermediate table to be evaluated meets preset requirements; modifying the status of the intermediate table to be evaluated in the intermediate table management information table to an online status and the effective time of the intermediate table to be evaluated, and setting the survival period of the intermediate table to be evaluated.

[0012] Furthermore, the intermediate table management information table is scanned regularly to determine the intermediate tables that are online and expired; the access frequency of the intermediate tables that are online and expired within a survival cycle is queried; the intermediate tables whose access frequency is lower than the preset frequency are deleted; and the survival cycle of the intermediate tables whose access frequency is not lower than the preset frequency is extended.

[0013] Further, determine the original table query statement of the intermediate table, wherein the original table query statement of the intermediate table is a query statement constructed by the core query attributes of the target intermediate table in the intermediate table management information table; execute the original table query statement of the intermediate table, and if the result is empty, perform the operation of deleting the target intermediate table; if the result is not empty, compare the execution result of the original table query statement of the intermediate table with the data in the target intermediate table; if the comparison finds that the data is different, use the execution result of the original table query statement of the intermediate table to update the data in the target intermediate table.

[0014] In order to achieve the above-mentioned purpose, according to another aspect of the present application, a query device based on structured query language SQL is provided. The device comprises: a first acquisition unit, used to acquire native SQL, wherein native SQL is used to perform data query; a first parsing unit, used to perform syntax parsing on native SQL and extract core query content from native SQL; a first judgment unit, used to judge whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; a first query unit, used to adjust the query to the intermediate table query SQL based on native SQL if there is a record, and obtain the query result.

[0015] In order to achieve the above-mentioned purpose, according to another aspect of the present application, a computer program product is provided, including a computer program / instruction, and when the computer program / instruction is executed by a processor, any one of the above-mentioned methods is executed.

[0016] In order to achieve the above-mentioned purpose, according to another aspect of the present application, a storage medium is provided, the storage medium includes a stored program, wherein the program executes any one of the above-mentioned methods.

[0017] Through this application, the following steps are adopted: obtaining native SQL, wherein native SQL is used for data query; parsing native SQL, extracting core query content from native SQL; judging whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if there is a record, adjusting the intermediate table query SQL to execute the query based on native SQL to obtain the query result, solving the problem of low SQL query efficiency due to long development time and long adjustment cycle in the data query process in the related technology. The present invention can create and manage the intermediate table in an automated manner, support a certain degree of query reuse, make up for the shortcomings of the query result cache of the prior art, ensure the optimization quality, save costs, and thus achieve the effect of improving the overall SQL query efficiency of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings constituting a part of the present application are used to provide a further understanding of the present application. The illustrative embodiments and descriptions of the present application are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0019] Figure 1 It is a flowchart of a query method based on structured query language SQL provided according to an embodiment of the present application;

[0020] Figure 2 It is a schematic diagram of the logical architecture of the query method based on the structured query language SQL provided according to an embodiment of the present application;

[0021] Figure 3 It is a schematic diagram of a query device based on structured query language SQL provided according to an embodiment of the present application. DETAILED DESCRIPTION

[0022] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0023] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present application.

[0024] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present application described here. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0025] For the convenience of description, some nouns or terms involved in the embodiments of the present application are explained below:

[0026] Intermediate table: Intermediate table is a concept in business logic. It is a data table in the database that is used to store intermediate calculation results. A typical scenario is that the original data table in the database needs to undergo complex calculations before it can be displayed on the report system. The intermediate table is used to save the results of some complex calculations first, and then calculations are performed on this basis to facilitate data operations.

[0027] Index: In a relational database, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. It is a collection of values ​​of one or more columns in a table and a corresponding list of logical pointers to the data pages in the table that physically identify these values. The index provides pointers to the data values ​​stored in the specified columns of the table, and then sorts these pointers according to the sort order you specify. The database uses the index to find a specific value, and then follows the pointer to find the row containing the value. This allows the SQL statement corresponding to the table to execute faster and quickly access specific information in the database table.

[0028] Druid: is a component library that provides an efficient, powerful, and scalable database connection pool.

[0029] SQL Parser: is the parsing component of the above Druid.

[0030] AST syntax tree: (Abstract Syntax Tree): The popular understanding is to abstractly generate the corresponding tree structure according to the source code syntax.

[0031] SQLSelectQueryBlock: is an object.

[0032] getSelectList(): is a method that can be called by an object.

[0033] SQLExpr: SQL function expression.

[0034] According to an embodiment of the present application, a query method based on structured query language SQL is provided.

[0035] Figure 1 1 is a flow chart of a query method based on structured query language SQL according to an embodiment of the present application. Figure 1 As shown, the method comprises the following steps:

[0036] Step S101, obtaining native SQL, wherein the native SQL is used for data query.

[0037] like Figure 2 As shown, Figure 2 It is a schematic diagram of the logical architecture of the query method based on the structured query language SQL according to the embodiment of the present application, and the main modules include: SQL interceptor, intermediate table creation component, intermediate table evaluation component, and intermediate table maintenance component.

[0038] During the data query process of the report system, the SQL interceptor decides to execute the native SQL query, and the above-mentioned native SQL can be obtained.

[0039] Step S102, parse the native SQL and extract the core query content from the native SQL.

[0040] The native SQL obtained in step S101 is parsed and divided into a core query part and an additional condition part.

[0041] Native SQL statement parsing can be implemented using the SQL Parser component in Druid. After constructing the AST syntax tree based on the native SQL statement, each layer of the AST tree obtains a SQLSelectQueryBlock object. The getSelectList() method of the object can be called to obtain the SELECT part of the current statement. The other parts are similar. In addition, various implementation classes of SQLExpr can complete field extraction and conditional analysis.

[0042] For example, the native SQL statement is as follows:

[0043]

[0044] As shown above, the execution order of SQL statements is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Since ORDER BY and LIMIT will not affect the query result set, they are not considered for the time being. GROUP BY and HAVING are mainly based on GROUP BY. This application only transforms the outermost SQL, and the target range of the intermediate table replacement is SELECT and FROM, which means that the SELECT and FROM parts are the core query content parts that we need to parse and extract in the next operation.

[0045] The specific parsing and extraction operations of the native SQL core query content are as follows:

[0046] First, when parsing the SELECT statement in the native SQL statement, the SELECT part can be obtained by calling the getSelectList() method of the SQLSelectQueryBlock object obtained through the AST tree. When parsing the FROM statement in the native SQL statement, the FROM part can be obtained by calling the getFromList() method of the SQLFromQueryBlock object obtained through the AST tree. The WHERE part can be obtained by calling the getWhereList() method of the SQLWhereQueryBlock object obtained through the AST tree, and the other parts are similar. The main parts used in this application are the SELECT, FROM, and WHERE parts, in which field parsing extraction and conditional analysis are completed through various implementation classes of SQLExpr, which are not described here one by one.

[0047] Then, the FROM content, SELECT list and WHERE list parsed from the above native SQL are grouped according to the FROM content. The SELECT list and WHERE list under each group are traversed, and the set of all SELECT list fields and aliases, such as "table1.a", "a", are parsed through the above field parsing method and conditional analysis method. The field set used in the WHERE list is parsed, and then the WHERE field set is merged into the SELECT set. The added fields need to be aliased. Taking table.a as an example, the alias is table_a, and the rules are not limited to this method.

[0048] Finally, merge the parsed SELECT and FROM contents to complete the operation of extracting the core query part. After parsing the native SQL statement, the merged statement content is: For example,

[0049]

[0050] As shown above, we get the SELECT part as SELECTtable1.a AS a,table2.b AS b,table1.cAS table1_c,table2.d AS table2_d, where table1.c AS table1_c,table2.d AS table2_d contain the aliases of the WHERE fields, such as the field alias of table1.c's WHERE is table1_c, and the field alias of table2.d's WHERE is table2_d. In other words, the WHERE lists table1_c and table1_c under the group are the attachment conditions corresponding to the core query part.

[0051] Specifically, we use the core query part obtained above as the basis for subsequent construction of the intermediate table, and the additional condition part as the basis for subsequent addition of indexes to the intermediate table. At the same time, we record the correspondence between the native SQL and the extracted core query part in the evaluation sample table.

[0052] Step S103, determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table.

[0053] It should be noted that before determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table, there is no intermediate table information in the intermediate table management information table at the beginning. When the data is queried by SQL, there is only the core query part parsed from the native SQL statement. Therefore, we parse and extract the core query content based on step S102 and create the corresponding intermediate table.

[0054] Optionally, in the query method based on the structured query language SQL provided in the embodiment of the present application, before determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table, the method also includes: obtaining query records that access the original table query within a preset time and the query time exceeds a threshold; extracting SQL content from the query records; performing syntax parsing on each SQL content respectively, extracting the core query content and additional condition content corresponding to each SQL content; and creating an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content.

[0055] Optionally, in the query method based on the structured query language SQL provided in an embodiment of the present application, based on the core query content and additional condition content corresponding to each SQL content, creating an intermediate table corresponding to each SQL content includes: creating a corresponding intermediate table based on the core query content corresponding to each SQL content; adding an index to the created intermediate table based on the additional condition content.

[0056] Specifically, after creating the intermediate table corresponding to each SQL content, the method also includes: recording the information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information includes at least: the intermediate table name, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table and the additional condition content corresponding to the intermediate table.

[0057] The status of the intermediate table includes online status, new status, and deleted status (this status is generally not displayed). Among them, the online status of the intermediate table means that when the system executes the sample statements merged after native SQL query parsing, the average query time of the intermediate table sample statements established by the core query statements is calculated. If the average query time of the intermediate table samples is less than the preset ratio of the native query time, such as 80%, it means that the optimization of the query time of the intermediate table has met the requirements, and the intermediate table status is updated to the online status.

[0058] The effective time of the intermediate table refers to that, generally speaking, the intermediate table has a survival period pre-set. When we determine that the intermediate table is online, we also set the survival period of the intermediate table, such as 30 minutes.

[0059] The above is some relevant content for establishing intermediate tables. The specific table creation actions will be described in detail later. Let's go back to the first item of this step. Before performing a data SQL query, we must first determine whether there is an intermediate table that has been created before and is online and not expired in the intermediate table management information table, which corresponds to the core query content. Then decide whether to create the table. Assume that there are many online and unexpired intermediate tables in the intermediate table management information table at the beginning, but there is no intermediate table corresponding to the core query statement of the current table. At this time, an intermediate table is created based on the core query part statement. Next, perform the table creation operation to create an intermediate table that corresponds to the current core query part.

[0060] The specific implementation method of table building is as follows: Since each database method is different, this application takes the commonly used MySQL as an example. It is necessary to build the table in two steps. The first step: add a WHERE condition that is not true after the CREATE TABLE table name AS SELECT statement. At this time, only the table structure will be created. The second step: INSERT INTO table name SELECT statement to insert data into the intermediate table. The intermediate table name can also be replaced by the MD5 encrypted value of the core query part. The naming method is not limited to this method. After creation, a new record is added to the intermediate table management information table, and the intermediate table status is set to the new status. Set the field attributes of the intermediate table, that is, the original field name and alias set of SELECT in the previous step. The alias here is actually the field name of the intermediate table, set the core query, additional condition attributes, etc.

[0061] After the table is created, a new record is added to the intermediate table management information table, and the intermediate table status is set to the newly created status. In addition, the native SQL query statement of the current table changes as follows: Based on the syntax tree for constructing each native query statement, the original table fields in the SELECT, WHERE, GROUP BY, and ORDER BY parts are replaced with the intermediate table fields; the intermediate table name replaces the original FROM part, for example:

[0062]

[0063] If the amount of data in the intermediate table is greater than a preset value, an index needs to be created for it to improve SQL execution efficiency. According to the additional condition part corresponding to the core query part obtained in step S102, a table index is added based on the additional condition.

[0064] Specifically, the method for adding table indexes based on additional conditions is as follows: traverse the number of WHERE groups in the additional conditions, which represents the number of types of conditions in the query table. Traverse each item in each WHERE group, parse the condition expression type, exclude conditions that operate on the field, such as: YEAR(table.x) = '2020', and count the fields. Select fields whose field counts account for more than half of the number of query table types, create multiple single-column indexes, and let the database optimizer decide how to use the indexes in subsequent queries.

[0065] The above steps complete the table creation. At this time, the intermediate table is in the newly created state. We need to evaluate the newly created intermediate table to further ensure the optimization effect.

[0066] Optionally, in the query method based on structured query language SQL provided in an embodiment of the present application, the intermediate table to be evaluated is determined from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table in a newly created state; a native SQL query statement corresponding to the intermediate table to be evaluated is obtained; the query time of the native SQL query statement in the original table query is obtained to obtain query time one; the intermediate table to be evaluated is replaced with the core query content in the native SQL query statement to obtain an adjusted sample statement; the adjusted sample statement is executed to obtain the query time to obtain query time two; and the intermediate table to be evaluated is evaluated based on query time one and query time two.

[0067] It should be noted that when the evaluation sample table finds the native query statement corresponding to the intermediate table for evaluation, the intermediate table query and the native table query operation environment are evaluated simultaneously and independently. For each intermediate table evaluation, some native query statement samples are first extracted. The query log record table uses the period before the intermediate table creation time point as the interval to calculate the average query duration of the native sample statement as the standard for the native query duration.

[0068] Optionally, in the query method based on the structured query language SQL provided in the embodiment of the present application, the intermediate table to be evaluated is evaluated based on the query duration 1 and the query duration 2, including: if the query duration 2 is less than the preset ratio of the query duration 1; determining that the optimization of the query duration by the intermediate table in the newly created state to be evaluated meets the preset requirements; modifying the newly created state in the intermediate table management information table to the online state, and setting the effective time of the intermediate table, and setting the survival period of the online state intermediate table. And recording the information in the intermediate table management information table.

[0069] For example, when the system executes the sample statements merged after native SQL query parsing, the average query time 2 of the intermediate table sample statements established by the core query statements is calculated. If the average query time 2 of the intermediate table samples is less than the preset proportion of the native query time 1, such as 80%, it means that the optimization of the query time by the intermediate table has met the requirements, and the intermediate table status is updated to the online status.

[0070] In actual operations, intermediate tables have a life cycle. In order to improve the optimization efficiency of the overall system, we also need to clean up intermediate tables regularly. By regularly scanning the intermediate table management information table, we can determine the intermediate tables that are online and expired, query the data access of the intermediate table in the log record table within a life cycle, and decide whether to delete it by querying its access frequency.

[0071] Optionally, in the query method based on the structured query language SQL provided in an embodiment of the present application, the intermediate table management information table is scanned periodically to determine the intermediate tables that are online and expired; the access frequency of the intermediate tables that are online and expired within a survival period is queried; the intermediate tables whose access frequency is lower than the preset frequency are deleted; and the survival period of the intermediate tables whose access frequency is not lower than the preset frequency is extended.

[0072] For example, when scanning the intermediate table management information table, if an intermediate table table_a is found to be in an online state and expired, query the data of the intermediate table table_a in the log record table within a survival cycle and calculate the access frequency. If the access frequency of the intermediate table table_a is too low or non-existent, delete table_a; otherwise, set a survival cycle to extend the time.

[0073] The above operations allow us to regularly clean up the intermediate table to avoid storage waste. At the same time, to ensure the accuracy of data query, the intermediate table data also needs to be updated regularly.

[0074] Optionally, in the query method based on the structured query language SQL provided in an embodiment of the present application, an intermediate table original table query statement is determined, wherein the intermediate table original table query statement is a query statement constructed by the core query attributes of the target intermediate table in the intermediate table management information table; the intermediate table original table query statement is executed, and if the result is empty, an operation of deleting the target intermediate table is performed; if the result is not empty, the execution result of the intermediate table original table query statement is compared with the data in the target intermediate table; if the comparison finds that the data are different, the execution result of the intermediate table original table query statement is used to update the data in the target intermediate table.

[0075] The specific implementation method of updating data is to take the current table table_a as an example, construct the intermediate table original table table_a composed of the query statement based on its core query attributes, execute the query statement of the intermediate table original table table_a of the current intermediate table, and check the data access frequency of the log record table. If the result is empty, execute the operation of deleting the intermediate table. If the result is not empty after executing the query statement of the intermediate table original table table_a of the current intermediate table, and it is found that there is a difference with the data of the current intermediate table table_a, update the intermediate table data with the execution result of the query statement of the intermediate table original table. This operation achieves the purpose of automatically updating the intermediate table data to ensure the accuracy of the query.

[0076] The above is a series of table creation operations we perform when it is determined that there is no intermediate table corresponding to the core query content in the intermediate table management information table.

[0077] Step S104: If there are records, the query is adjusted to the intermediate table query SQL based on the native SQL to obtain the query result.

[0078] Specifically, according to the content of step S103, before creation, check whether the intermediate table already exists in the intermediate table management information table. If not, execute the table creation statement. When there are records in the intermediate table, the native query SQL is adjusted to the intermediate table query. And the current data query information will be recorded in the log record table at the same time.

[0079] The specific operation of recording the current data query information to the log record table is that when executing the intermediate table data SQL query, according to Figure 2 As shown, the SQL interceptor acts as a query port, which indicates that the current table is output from the intermediate table and records the query information when it is returned into the log record table. The information of the intermediate table corresponding to each SQL content is recorded in the intermediate table management information table.

[0080] In summary, the query method based on structured query language SQL provided by the embodiment of the present application obtains native SQL, wherein native SQL is used for data query; performs syntax analysis on native SQL, extracts core query content from native SQL; determines whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if there is a record, the query is adjusted to the intermediate table query SQL based on native SQL to execute the query and obtain the query result, which solves the problem of low query efficiency of SQL in the related technology due to long development time and long adjustment cycle in the data query process. The present invention can create and manage the intermediate table in an automated manner, support a certain degree of query reuse, make up for the deficiencies in the query result cache of the prior art, ensure the optimization quality, save costs, and thus achieve the effect of improving the overall SQL query efficiency of the system.

[0081] It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and that, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0082] The embodiment of the present application also provides a query device based on structured query language SQL. It should be noted that the query device based on structured query language SQL in the embodiment of the present application can be used to execute the query method based on structured query language SQL provided in the embodiment of the present application. The query device based on structured query language SQL provided in the embodiment of the present application is introduced below.

[0083] Figure 3 Schematic diagram of a query device based on structured query language SQL according to an embodiment of the present application. Figure 3 As shown, the device includes: a first acquisition unit 301, a first parsing unit 302, a first judgment unit 303, and a first query unit 304.

[0084] Specifically, the first acquisition unit 301 is used to acquire native SQL, where the native SQL is used to perform data query;

[0085] The first parsing unit 302 is used to perform syntax parsing on the native SQL and extract core query content from the native SQL;

[0086] The first judgment unit 303 is used to judge whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table;

[0087] The first query unit 304 is used to adjust the native SQL to the intermediate table query SQL to execute the query if there is a record, and obtain the query result.

[0088] In summary, the query device based on the structured query language SQL provided by the embodiment of the present application obtains native SQL through the first acquisition unit 301, wherein the native SQL is used for data query; the first parsing unit 302 performs syntax analysis on the native SQL and extracts the core query content from the native SQL; the first judgment unit 303 judges whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if the first query unit 304 has a record, it adjusts the query to the intermediate table query SQL based on the native SQL to obtain the query result, thereby solving the problem of low query efficiency of SQL in the related technology due to the long development time and long adjustment cycle in the data query process. The present invention can create and manage the intermediate table in an automated manner, support a certain degree of query reuse, make up for the shortcomings of the query result cache of the prior art, ensure the optimization quality, save costs, and thus achieve the effect of improving the overall SQL query efficiency of the system.

[0089] Optionally, in the query device based on the structured query language SQL provided in the embodiment of the present application, the first judgment unit 303 includes: a first acquisition module, used to obtain query records of accessing the original table query within a preset time and the query time exceeds a threshold; a first extraction module, used to extract SQL content from the query record; a first parsing module, used to perform syntax parsing on each SQL content respectively, and extract the core query content and additional condition content corresponding to each SQL content; a first creation module, used to create an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content.

[0090] Optionally, in the query device based on the structured query language SQL provided in an embodiment of the present application, the first creation module includes: a first creation sub-module, used to create a corresponding intermediate table based on the core query content corresponding to each SQL content; a first addition sub-module, used to add an index to the created intermediate table based on additional condition content.

[0091] Optionally, in the query device based on the structured query language SQL provided in an embodiment of the present application, the first creation module includes: a first recording sub-module, used to record the information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information includes at least: the table name of the intermediate table, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table and the additional condition content corresponding to the intermediate table.

[0092] Optionally, in the query device based on the structured query language SQL provided in the embodiment of the present application, the first creation module includes: a first determination sub-module, used to determine the intermediate table to be evaluated from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table whose status is a newly created status; a first acquisition sub-module, used to obtain the native SQL query statement corresponding to the intermediate table to be evaluated; a second acquisition sub-module, used to obtain the query time of the native SQL query statement in the original table query, and obtain query time one; a first replacement sub-module, used to replace the core query content in the native SQL query statement with the intermediate table to be evaluated, and obtain an adjusted sample statement; a third acquisition sub-module, used to execute the adjusted sample statement to obtain the query time, and obtain query time two; a first evaluation sub-module, used to evaluate the intermediate table to be evaluated based on query time one and query time two.

[0093] Optionally, in the query device based on the structured query language SQL provided in the embodiment of the present application, the first evaluation submodule includes: a second determination submodule, which is used to determine if the query duration two is less than a preset ratio of the query duration one; whether the optimization of the query duration by the intermediate table to be evaluated meets preset requirements; a first processing submodule, which is used to modify the status of the intermediate table to be evaluated in the intermediate table management information table to an online status and the effective time of the intermediate table to be evaluated, and at the same time set the survival period of the intermediate table to be evaluated.

[0094] Optionally, in the query device based on the structured query language SQL provided in the embodiment of the present application, the first processing submodule includes: a third determination submodule, used to periodically scan the intermediate table management information table to determine the intermediate tables that are online and expired; a first query submodule, used to query the access frequency of the intermediate tables that are online and expired within a survival period; a first deletion submodule, used to delete the intermediate tables whose access frequency is lower than the preset frequency; and a first extension submodule, used to extend the survival period of the intermediate tables whose access frequency is not lower than the preset frequency.

[0095] Optionally, in the query device based on the structured query language SQL provided in the embodiment of the present application, the first processing submodule includes: a fourth determination submodule, used to determine the intermediate table original table query statement, wherein the intermediate table original table query statement is a query statement constructed by the core query attributes of the target intermediate table in the intermediate table management information table; a first judgment submodule, used to execute the intermediate table original table query statement, and if the result is empty, execute the operation of deleting the target intermediate table; a second judgment submodule, used to compare the execution result of the intermediate table original table query statement with the data in the target intermediate table if the result is not empty; and a first update submodule, used to update the data in the target intermediate table using the execution result of the intermediate table original table query statement if the comparison finds differences in the data.

[0096] The query device based on structured query language SQL includes a processor and a memory, and the first acquisition unit 301, the first parsing unit 302, the first judgment unit 303, the first query unit 304, etc. are all stored in the memory as program units, and the processor executes the program units stored in the memory to realize corresponding functions.

[0097] The processor includes a kernel, which calls the corresponding program unit from the memory. The kernel can be set to one or more, and queries based on the structured query language SQL can be adjusted by adjusting kernel parameters.

[0098] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0099] An embodiment of the present invention provides a computer-readable storage medium on which a program is stored. When the program is executed by a processor, a query method based on a structured query language SQL is implemented.

[0100] An embodiment of the present invention provides a processor, which is used to run a program, wherein the program executes a query method based on a structured query language SQL when running.

[0101] An embodiment of the present invention provides a device, which includes a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, the following steps are implemented: obtaining native SQL, wherein the native SQL is used for data query; performing syntax parsing on the native SQL, and extracting core query content from the native SQL; determining whether an intermediate table corresponding to the core query content is recorded in an intermediate table management information table; and if a record exists, adjusting the query to the intermediate table query SQL based on the native SQL to obtain a query result.

[0102] When the processor executes the program, the following steps are also implemented: obtaining query records of accessing the original table query within a preset time and the query duration exceeding a threshold; extracting SQL content from the query records; performing syntax analysis on each SQL content respectively, extracting the core query content and additional condition content corresponding to each SQL content; and creating an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content.

[0103] When the processor executes the program, the following steps are also implemented: creating a corresponding intermediate table according to the core query content corresponding to each SQL content; and adding an index to the created intermediate table based on the additional condition content.

[0104] When the processor executes the program, the following steps are also implemented: the information of the intermediate table corresponding to each SQL content is recorded in the intermediate table management information table, wherein the information includes at least: the name of the intermediate table, the core query content corresponding to the intermediate table, the status of the intermediate table, the amount of data in the intermediate table, the effective time of the intermediate table, and the additional condition content corresponding to the intermediate table.

[0105] When the processor executes the program, the following steps are also implemented: determining the intermediate table to be evaluated from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table in a newly created state; obtaining a native SQL query statement corresponding to the intermediate table to be evaluated; obtaining a query time of the native SQL query statement in the original table query to obtain a query time one; replacing the core query content in the native SQL query statement with the intermediate table to be evaluated to obtain an adjusted sample statement; executing the adjusted sample statement to obtain the query time to obtain a query time two; and evaluating the intermediate table to be evaluated based on the query time one and the query time two.

[0106] When the processor executes the program, the following steps are also implemented: if the query duration 2 is less than the preset ratio of the query duration 1; determine that the optimization of the query duration by the intermediate table to be evaluated meets the preset requirements; modify the state of the intermediate table to be evaluated in the intermediate table management information table to the online state and the effective time of the intermediate table to be evaluated, and set the survival period of the intermediate table to be evaluated.

[0107] When the processor executes the program, the following steps are also implemented: periodically scanning the intermediate table management information table to determine the intermediate tables that are online and expired; querying the access frequency of the intermediate tables that are online and expired within a survival cycle; deleting the intermediate tables whose access frequency is lower than the preset frequency; and extending the survival cycle of the intermediate tables whose access frequency is not lower than the preset frequency.

[0108] When the processor executes the program, the following steps are also implemented: determining the original table query statement of the intermediate table, wherein the original table query statement of the intermediate table is a query statement constructed by the core query attributes of the target intermediate table in the intermediate table management information table; executing the original table query statement of the intermediate table, if the result is empty, executing the operation of deleting the target intermediate table; if the result is not empty, comparing the execution result of the original table query statement of the intermediate table with the data in the target intermediate table; if the comparison finds that the data is different, using the execution result of the original table query statement of the intermediate table to update the data in the target intermediate table.

[0109] The devices in this article can be servers, PCs, PADs, mobile phones, etc.

[0110] The present application also provides a computer program product, which, when executed on a data processing device, is suitable for executing a program that initializes the following method steps: obtaining native SQL, where the native SQL is used for data query; parsing the native SQL and extracting core query content from the native SQL; determining whether an intermediate table corresponding to the core query content is recorded in the intermediate table management information table; if a record exists, adjusting the query to the intermediate table query SQL based on the native SQL to obtain a query result.

[0111] When executed on a data processing device, the method is also suitable for executing an initialization program having the following method steps: obtaining query records of accessing the original table query within a preset time and the query duration exceeding a threshold; extracting SQL content from the query records; performing syntax analysis on each SQL content respectively, extracting the core query content and additional condition content corresponding to each SQL content; and creating an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content.

[0112] When executed on a data processing device, it is also suitable for executing a program that is initialized with the following method steps: creating a corresponding intermediate table according to the core query content corresponding to each SQL content; and adding an index to the created intermediate table based on the additional condition content.

[0113] When executed on a data processing device, it is also suitable for executing an initialization program having the following method steps: recording the information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information includes at least: the intermediate table name, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table and the additional condition content corresponding to the intermediate table.

[0114] When executed on a data processing device, it is also suitable for executing an initialization program having the following method steps: determining an intermediate table to be evaluated from an intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table whose status is a newly created status; obtaining a native SQL query statement corresponding to the intermediate table to be evaluated; obtaining the query duration of the native SQL query statement in the original table query to obtain query duration one; replacing the core query content in the native SQL query statement with the intermediate table to be evaluated to obtain an adjusted sample statement; executing the adjusted sample statement to obtain the query duration to obtain query duration two; and evaluating the intermediate table to be evaluated based on query duration one and query duration two.

[0115] When executed on a data processing device, it is also suitable for executing an initialization program having the following method steps: if query duration two is less than a preset ratio of query duration one; determine that the optimization of the query duration by the intermediate table to be evaluated meets the preset requirements; modify the status of the intermediate table to be evaluated in the intermediate table management information table to the online status and the effective time of the intermediate table to be evaluated, and set the survival period of the intermediate table to be evaluated.

[0116] When executed on a data processing device, it is also suitable for executing an initialization program having the following method steps: periodically scanning the intermediate table management information table to determine the intermediate tables that are online and expired; querying the access frequency of the intermediate tables that are online and expired within a survival cycle; deleting the intermediate tables whose access frequency is lower than the preset frequency; and extending the survival cycle of the intermediate tables whose access frequency is not lower than the preset frequency.

[0117] When executed on a data processing device, it is also suitable for executing an initialization program having the following method steps: determining an intermediate table original table query statement, wherein the intermediate table original table query statement is a query statement constructed by the core query attributes of the target intermediate table in the intermediate table management information table; executing the intermediate table original table query statement, and if the result is empty, executing the operation of deleting the target intermediate table; if the result is not empty, comparing the execution result of the intermediate table original table query statement with the data in the target intermediate table; if the comparison finds that the data is different, using the execution result of the intermediate table original table query statement to update the data in the target intermediate table.

[0118] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0119] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0120] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0121] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0122] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0123] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0124] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0125] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0126] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0127] The above are only embodiments of the present application and are not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included within the scope of the claims of the present application.

Claims

1. A query method based on structured query language SQL, characterized in that: include: Obtaining native SQL, wherein the native SQL is used to perform data query; Parsing the native SQL and extracting core query content from the native SQL; Determine whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; If there is a record, the query is performed based on the native SQL adjusted to the intermediate table query SQL to obtain the query result; Before determining whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table, the method further includes: obtaining query records of accessing the original table query within a preset time and the query duration exceeding a threshold; extracting SQL content from the query records; performing syntax analysis on each SQL content respectively, extracting the core query content and additional condition content corresponding to each SQL content; creating an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content; After creating the intermediate table corresponding to each SQL content, the method further includes: recording information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information at least includes: the intermediate table name, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table, and the additional condition content corresponding to the intermediate table; The method also includes: determining an intermediate table to be evaluated from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table in a newly created state; obtaining a native SQL query statement corresponding to the intermediate table to be evaluated; obtaining a query duration of the native SQL query statement in the original table query to obtain a query duration one; replacing the core query content in the native SQL query statement with the intermediate table to be evaluated to obtain an adjusted sample statement; executing the adjusted sample statement to obtain a query duration to obtain a query duration two; and evaluating the intermediate table to be evaluated based on the query duration one and the query duration two.

2. The method according to claim 1, characterized in that Based on the core query content and additional condition content corresponding to each SQL content, the intermediate table corresponding to each SQL content is created, including: Create corresponding intermediate tables based on the core query content corresponding to each SQL content; An index is added to the created intermediate table based on the additional condition content.

3. The method according to claim 1, characterized in that: Evaluating the intermediate table to be evaluated based on the query duration 1 and the query duration 2 includes: If the second query duration is less than a preset ratio of the first query duration; determining that the optimization of the query duration by the intermediate table to be evaluated meets the preset requirement; The state of the intermediate table to be evaluated in the intermediate table management information table is modified to the online state and the effective time of the intermediate table to be evaluated, and the survival period of the intermediate table to be evaluated is set.

4. The method according to claim 3, characterized in that The method further comprises: Periodically scan the intermediate table management information table to determine the intermediate tables that are online and expired; Query the access frequency of the intermediate table that is online and expired within a survival cycle; Delete the intermediate table whose access frequency is lower than the preset frequency; Extend the life cycle of intermediate tables that are accessed at a frequency not less than a preset frequency.

5. The method according to claim 4, characterized in that The method further comprises: Determine an intermediate table original table query statement, wherein the intermediate table original table query statement is a query statement constructed by the core query attribute of the target intermediate table in the intermediate table management information table; Execute the query statement of the original table of the intermediate table. If the result is empty, delete the target intermediate table. If the result is not empty, compare the execution result of the original table query statement of the intermediate table with the data in the target intermediate table; If the comparison finds that the data are different, the execution result of the query statement of the original table of the intermediate table is used to update the data in the target intermediate table.

6. A query device based on structured query language SQL, characterized in that: include: A first acquisition unit is used to acquire native SQL, wherein the native SQL is used to perform data query; A first parsing unit, configured to perform syntax parsing on the native SQL and extract core query content from the native SQL; A first judgment unit, used to judge whether the intermediate table corresponding to the core query content is recorded in the intermediate table management information table; A first query unit, configured to adjust the native SQL to the intermediate table query SQL to execute the query if the record exists, and obtain the query result; The first judgment unit includes: a first acquisition module, used to acquire query records of accessing the original table query within a preset time and whose query duration exceeds a threshold; a first extraction module, used to extract SQL content from the query records; a first parsing module, used to perform syntax parsing on each SQL content respectively, and extract the core query content and additional condition content corresponding to each SQL content; a first creation module, used to create an intermediate table corresponding to each SQL content based on the core query content and additional condition content corresponding to each SQL content; The first creation module includes: a first recording submodule, which is used to record the information of the intermediate table corresponding to each SQL content in the intermediate table management information table, wherein the information at least includes: the name of the intermediate table, the core query content corresponding to the intermediate table, the status of the intermediate table, the data volume of the intermediate table, the effective time of the intermediate table, and the additional condition content corresponding to the intermediate table; The first creation module includes: a first determination submodule, used to determine the intermediate table to be evaluated from the intermediate table management information table, wherein the intermediate table to be evaluated is an intermediate table in a newly created state; a first acquisition submodule, used to obtain the native SQL query statement corresponding to the intermediate table to be evaluated; a second acquisition submodule, used to obtain the query time of the native SQL query statement in the original table query, and obtain the query time one; a first replacement submodule, used to replace the core query content in the native SQL query statement with the intermediate table to be evaluated, and obtain an adjusted sample statement; a third acquisition submodule, used to execute the adjusted sample statement to obtain the query time, and obtain the query time two; a first evaluation submodule, used to evaluate the intermediate table to be evaluated based on the query time one and the query time two.

7. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instructions are executed by a processor, the method according to any one of claims 1 to 5 is performed.

8. A storage medium, characterized in that: The storage medium includes a stored program, wherein the program executes the method according to any one of claims 1 to 5.

Images