CN114218287A - Query time prediction method for time sequence database - Google Patents

Abstract

A query time prediction method for a time sequence database relates to the technical field of computers, and aims at the problem of low query time prediction speed in the prior art, and comprises the following steps: the method comprises the following steps: reading time sequence data; step two: writing the time series data into a CnOSDB, wherein the CnOSDB uses a CnoSQL query statement to query and retrieve the time series data and records query time; step three: encoding the query statement into vectorized data; step four: extracting data distribution characteristics from the opposite quantitative data; step five: using PCA to reduce the dimension of the data distribution characteristics; step six: training a gradient lifting regression tree model by using vectorized data and data distribution characteristics after dimension reduction as input and query time as output; step seven: and predicting the query time by using the trained gradient lifting regression tree model. In the prediction time, the model can give a prediction result in tens of milliseconds in the experiment, and has considerable response speed.

Description

Query time prediction method for time sequence database

Technical Field

The invention relates to the technical field of computers, in particular to a query time prediction method facing a time sequence database.

Background

Query time prediction is the technical basis for multiple hot spot problems in the field of databases, such as admission control, query optimization, query scheduling and the like. For example, in database optimization, the main objectives of the optimization are query response time and space utilization, and therefore, the execution time of the query will be used as an important feedback index to indicate the quality of the optimization result. In practice, however, if the query is physically executed on the query load, the actual execution time is obtained, which may bring unacceptable cost to the optimization process, because the load is often executed for hundreds of thousands of rounds repeatedly.

At present, the related research for inquiring the time prediction direction is relatively mature. In general, there are two schemes for the query time prediction technology on the database: one is to encode the query at the query plan level and the other is to encode the query at the physical operator level. The coding granularity of the query plan layer is low, the prediction effect depends on the feature similarity of a training set and a test set, the prediction effect on unknown queries is poor, the coding of the physical operator layer generally utilizes depth-first traversal of the query tree to obtain the sequence of operators, so that the structural features in the query tree can be extracted, the coding granularity is higher, the prediction effect is better than that of the query plan layer in terms of dealing with unknown queries, but the prediction speed of the test queries with the features similar to the training set is low.

Disclosure of Invention

The purpose of the invention is: aiming at the problem of low query time prediction speed in the prior art, a query time prediction method facing a time sequence database is provided.

The technical scheme adopted by the invention to solve the technical problems is as follows:

a query time prediction method facing a time sequence database comprises the following steps:

the method comprises the following steps: reading time sequence data;

step two: writing the time series data into a CnOSDB, wherein the CnOSDB uses a CnoSQL query statement to query and retrieve the time series data and records query time;

step three: encoding the query statement into vectorized data;

step four: extracting data distribution characteristics from the opposite quantitative data;

step five: using PCA to reduce the dimension of the data distribution characteristics;

step six: training a gradient lifting regression tree model by using vectorized data and data distribution characteristics after dimension reduction as input and query time as output;

step seven: and predicting the query time by using the trained gradient lifting regression tree model.

Further, before the third step, the following steps are also included: the CnoSQL is rewritten to standard SQL.

Further, the encoding in the third step includes encoding a join graph and encoding column information, and the results of encoding the join graph and encoding the column information are connected as the encoding of the whole query.

Further, the specific steps of the join graph coding are as follows:

analyzing tables of joining joins related in CnoSQL or SQL query sentences, analyzing the connection relation of every two tables of joining joins, judging whether every two tables of joining joins are connected or not, if so, setting codes corresponding to the joins as 1, if not, setting the codes corresponding to the joins as 0, and finally, reserving an upper triangular matrix part of the two-dimensional matrix and expanding the upper triangular matrix part into a one-dimensional matrix according to rows.

Further, the specific steps of the column information encoding are as follows:

and aiming at each table participating in the join, setting the coding column participating in the query predicate in each table participating in the join as 1, setting the coding column not participating in the query predicate in each table participating in the join as 0, and connecting the column set as 1 with the column set as 0 to obtain the query code.

Further, the column information is encoded by a one-hot encoding method.

The invention has the beneficial effects that:

the method generates a quote _ Big table by using stock transaction data of a 2016, 10, 24 and 24 new york exchange on an NYSE website, and tests 11 commonly used queries on the quote _ Big table, and by applying the method, under the condition that the data scale of a database does not change elastically, the coefficient of the query prediction regression model can reach over 0.996, the Mean Absolute Percentage Error (MAPE) can reach within 17 percent, and when the data scale of the database changes elastically within 20 percent, the coefficient of the query prediction regression model can reach over 0.971, and the Mean Absolute Percentage Error (MAPE) can reach within 19 percent.

In addition, in the prediction time, the model can give a prediction result in tens of milliseconds in the experiment, and has very considerable response speed.

Drawings

Fig. 1 is a schematic diagram of SQL rewritten to the standard by CnoSQL;

FIG. 2 is a sequence diagram of one-dimensional encoding by compressing the upper half area;

fig. 3 is a schematic overall flow chart of the present application.

Detailed Description

It should be noted that, in the present invention, the embodiments disclosed in the present application may be combined with each other without conflict.

The first embodiment is as follows: specifically, the present embodiment is described with reference to fig. 3, and the query time prediction method for a time series database according to the present embodiment includes the following steps:

the method comprises the following steps: reading time sequence data;

step two: writing the time series data into a CnOSDB, wherein the CnOSDB uses a CnoSQL query statement to query and retrieve the time series data and records query time;

step three: encoding the query statement into vectorized data;

step four: extracting data distribution characteristics from the opposite quantitative data;

step five: using PCA to reduce the dimension of the data distribution characteristics;

step six: training a gradient lifting regression tree model by using vectorized data and data distribution characteristics after dimension reduction as input and query time as output;

step seven: and predicting the query time by using the trained gradient lifting regression tree model.

The second embodiment is as follows: this embodiment is a further description of the first embodiment, and the difference between this embodiment and the first embodiment is that the following steps are further included before the third step: the CnoSQL is rewritten to standard SQL.

The third concrete implementation mode: this embodiment is a further description of the second embodiment, and the difference between this embodiment and the second embodiment is that the encoding in step three includes join graph encoding and column information encoding, and the results of join graph encoding and column information encoding are connected as the encoding of the whole query.

The fourth concrete implementation mode: this embodiment is a further description of a third embodiment, and the difference between this embodiment and the third embodiment is that the specific steps of the join graph encoding are:

analyzing tables of joining joins related in CnoSQL or SQL query sentences, analyzing the connection relation of every two tables of joining joins, judging whether every two tables of joining joins are connected or not, if so, setting codes corresponding to the joins as 1, if not, setting the codes corresponding to the joins as 0, and finally, reserving an upper triangular matrix part of the two-dimensional matrix and expanding the upper triangular matrix part into a one-dimensional matrix according to rows.

The fifth concrete implementation mode: the present embodiment is further described with respect to a fourth embodiment, and the difference between the present embodiment and the fourth embodiment is that the specific step of the column information encoding is:

and aiming at each table participating in the join, setting the coding column participating in the query predicate in each table participating in the join as 1, setting the coding column not participating in the query predicate in each table participating in the join as 0, and connecting the column set as 1 with the column set as 0 to obtain the query code.

The sixth specific implementation mode: this embodiment mode is a further description of a fifth embodiment mode, and the difference between this embodiment mode and the fifth embodiment mode is that the column information encoding is performed by a one-hot encoding method.

Time series data, referred to simply as time series data, refers to a sequence of data points acquired by a fixed acquisition device at fixed time intervals over a period of time, each data point being associated with a time stamp indicating the time of acquisition. By analyzing time series data of a period of time, the development trend of the transaction in the period of time can be obtained, and therefore future prediction is achieved. The time sequence data has obvious characteristics, fixed data structure, fixed write-in interval, large write-in quantity, large occupied original data space, wide time range of each query, less updating operation requirements and less single-point query requirements. These characteristics result in that the conventional relational database does not support the time-series data well. Databases that are specifically optimized for time series data are referred to as time series databases.

The characterized coding scheme provided by the application achieves the aim of quickly and accurately inquiring the time prediction under the condition of the business requirement of intensive inquiring workload.

Corresponding to SQL query of a relational database, CnosDB carries out query retrieval on time-series data by using CnosQL query language. In actual prediction, the CnoSQL is first rewritten to standard SQL as shown in fig. 1.

To predict the query time using an algorithm, the query statement needs to be encoded first. The invention carries out the characteristic coding of the query statement of a CnosDB time sequence database, mainly carries out the analysis and the coding from the query level, and is divided into two main parts: and encoding the join graph and the column information, wherein the results of the two encodings are connected to be used as the encoding of the whole query.

The Join graph coding means that a Join list involved in the whole SQL query is analyzed, the connection relation of the Join list is analyzed, whether connection is needed between every two Join lists is judged in sequence, and the number of the connection needed is set to be 1 in a two-dimensional matrix. Considering that the connection is required to be bidirectional, the two-dimensional matrix only needs to be half reserved and compressed into a one-dimensional matrix for subsequent encoding. As shown, there is a connection between a and B, B and D, a and C, i.e. 1 is set in the two-dimensional array, compressing the upper half into a one-dimensional encoding resulting in the sequence shown in fig. 2.

The column information encoding means that for each table, the column of the query predicate is set to be 1, and one-hot encoding is adopted for encoding, that is, for a table with n columns, n bits are used for representing the condition that each column participates in the query predicate, 1 represents that the column participates in the query predicate, and 0 represents that the column does not participate. The two are connected to obtain the final inquiry code

In order to adapt the characteristic code to the flexible change of the database data scale in the actual service, the data distribution characteristics need to be extracted. For this reason, it is assumed that the data size has an upper limit value within a certain service range, and the upper limit value is set by experience according to actual service requirements. The characteristic coding length of data distribution is recorded to be equal to the number of tables, indexes of characteristic vectors correspond to table sequence numbers, and values of coding bits are ratios of current table data entries to upper limits of the table data entries. And the data distribution feature vector is spliced to the front of the query feature vector.

After the query and data distribution are characterized and encoded, the PCA dimension reduction is performed on the data matrix. Because the query load in the actual service generally cannot cover the tags in all the time series data, some relation tables are not used, and therefore a large number of zero values are stored in the data matrix and are sparse. The PCA dimension reduction processing can effectively remove redundant components of the data matrix, accelerate the model regression speed and improve the accuracy.

Finally, the training data are used for training a gradient lifting regression tree model, so that the model can be used for accurately and quickly predicting the time of the query.

It should be noted that the detailed description is only for explaining and explaining the technical solution of the present invention, and the scope of protection of the claims is not limited thereby. It is intended that all such modifications and variations be included within the scope of the invention as defined in the following claims and the description.

Claims (6)

1. A query time prediction method facing a time sequence database is characterized by comprising the following steps:

the method comprises the following steps: reading time sequence data;

step two: writing the time series data into a CnOSDB, wherein the CnOSDB uses a CnoSQL query statement to query and retrieve the time series data and records query time;

step three: encoding the query statement into vectorized data;

step four: extracting data distribution characteristics from the opposite quantitative data;

step five: using PCA to reduce the dimension of the data distribution characteristics;

step six: training a gradient lifting regression tree model by using vectorized data and data distribution characteristics after dimension reduction as input and query time as output;

step seven: and predicting the query time by using the trained gradient lifting regression tree model.

2. The method according to claim 1, wherein the step three is preceded by the steps of: the CnoSQL is rewritten to standard SQL.

3. The method according to claim 2, wherein the encoding in three steps comprises join graph encoding and column information encoding, and the results of join graph encoding and column information encoding are concatenated as the encoding of the whole query.

4. The method as claimed in claim 3, wherein the encoding of the join graph comprises the following steps:

analyzing tables of joining joins related in CnoSQL or SQL query sentences, analyzing the connection relation of every two tables of joining joins, judging whether every two tables of joining joins are connected or not, if so, setting codes corresponding to the joins as 1, if not, setting the codes corresponding to the joins as 0, and finally, reserving an upper triangular matrix part of the two-dimensional matrix and expanding the upper triangular matrix part into a one-dimensional matrix according to rows.

5. The method according to claim 4, wherein the column information is encoded by the following steps:

and aiming at each table participating in the join, setting the coding column participating in the query predicate in each table participating in the join as 1, setting the coding column not participating in the query predicate in each table participating in the join as 0, and connecting the column set as 1 with the column set as 0 to obtain the query code.

6. The method of claim 5, wherein the column information is encoded by a one-hot encoding method.

Images