CN114218277A

CN114218277A - Efficient query method and device for relational database

Info

Publication number: CN114218277A
Application number: CN202111544353.4A
Authority: CN
Inventors: 李�浩; 罗多多
Original assignee: Gf Fund Management Co ltd
Current assignee: Gf Fund Management Co ltd
Priority date: 2021-12-16
Filing date: 2021-12-16
Publication date: 2022-03-22

Abstract

The invention discloses a method and a device for efficiently inquiring a relational database, wherein the method comprises the following steps: writing records obtained by querying a relational database into Redis to generate a second two-dimensional table as a cache, wherein a first time stamp of a first two-dimensional table is stored on the relational database; a second timestamp of the second two-dimensional table is stored in the second two-dimensional table; and judging whether the second two-dimensional table needs to be generated or updated or not by comparing the time sequence of the first time stamp with the time sequence of the second time stamp, and inquiring records meeting specified conditions from a relational database or Redis. According to the invention, because part of records can be obtained by inquiring from the two-dimensional table of the Redis cache, the pressure of the relational database is reduced, and the inquiring efficiency is improved.

Description

Efficient query method and device for relational database

Technical Field

The invention relates to the technical field of databases, in particular to a high-efficiency query method and device for a relational database.

Background

The application field of the database is very wide, and the database is required to be used for storing data information in families, companies or large enterprises or government departments. Among them, the most used are relational databases such as Oracle, Mysql, SqlServer, etc.

The relational database is based on a single relational model, is structurally stored, has integrity constraint and is similar to common tables, the relation between data is established through a two-dimensional table, and Structured Query Language (SQL) is adopted for data reading and writing. The method has the advantages that the data consistency is kept through transaction processing, the data updating cost is low, and complex queries such as Join can be carried out. However, the relational database queries the two-dimensional table by using the select statement, and if the relational database is directly queried, the database is stressed greatly, and the query efficiency is low.

In view of this, it is urgently needed to improve the existing query method for the relational database, so as to reduce the pressure of querying the relational database and improve the query efficiency.

Disclosure of Invention

In view of the above drawbacks, the technical problem to be solved by the present invention is to provide a method and an apparatus for efficiently querying a relational database, so as to solve the problems of large pressure on the database and low query efficiency of the existing relational database query method.

Therefore, the efficient query method for the relational database provided by the invention comprises the following steps of:

writing records obtained by querying a relational database into a first two-dimensional table and writing the first two-dimensional table into Redis to generate a second two-dimensional table, wherein the second two-dimensional table is used as a cache of the first two-dimensional table, and a first timestamp when the first two-dimensional table is generated or updated is stored in the relational database; a second timestamp when the second two-dimensional table is generated or updated is stored in the second two-dimensional table; updating the second timestamp in accordance with the first timestamp;

judging whether the two-dimensional table needs to be generated or updated or not by comparing the time sequence of the first timestamp with the time sequence of the second timestamp, wherein the second timestamp is not considered as the first timestamp;

if the second two-dimensional table needs to be generated or updated, reading a record meeting the query condition from a relational database according to the query condition, and writing the record and the query index into Redis to generate or update the second two-dimensional table; otherwise, the second two-dimensional table is not updated, and records meeting the query conditions are inquired from the second two-dimensional table.

In the method, when a record in a relational database is written into the first two-dimensional table, the first timestamp is generated, and a mapping relation between the first timestamp and the second two-dimensional table is constructed into a third two-dimensional table and is stored in the relational database;

and a trigger is arranged on the relational database, and when the first two-dimensional table is subjected to insertion, updating or deletion operation, the first timestamp is updated.

In the above method, preferably, the determining whether the second two-dimensional table needs to be generated or updated includes the following steps:

reading a first timestamp of the first two-dimensional table from a relational database, and reading a second timestamp of a second two-dimensional table corresponding to the first two-dimensional table from Redis;

judging whether the second timestamp exists, if the second timestamp does not exist, writing the first two-dimensional table into Redis to generate the second two-dimensional table, and writing the first timestamp into the second two-dimensional table to form the second timestamp; otherwise, comparing the time sequence of the first timestamp and the second timestamp, and if the first timestamp is later, updating the second two-dimensional table and the second timestamp according to the first two-dimensional table; if the first timestamp is the same as the second timestamp, the second two-dimensional table does not need to be updated.

In the above method, preferably, the record obtained by querying the relational database and the query index are written into Redis to generate or update the second two-dimensional table, which includes the following contents:

a second timestamp identifying whether the second two-dimensional table needs to be updated;

recording the total number, and querying the second two-dimensional table;

a column name;

record content whose key is a subscript of a record in the second two-dimensional table;

and querying the index for quickly locating the record meeting the specified column in the second two-dimensional table.

In the above method, preferably, when storing the column names of the second two-dimensional table on the Redis, all the column names in the second two-dimensional table are concatenated together by using a separator to be stored as one character string; and setting keys of each row of records in the second two-dimensional table as subscripts, using separators for values of each field, and splicing the values of each field together to store the values as a character string.

In the above method, preferably, when writing to the second two-dimensional table is started, the transaction is committed using extx after the writing is completed, using the WATHCH command first.

In the above method, preferably, the method of querying the second two-dimensional table is as follows: and reading the total number of records of the second two-dimensional table, then taking out the field names, and performing cyclic traversal on the record subscripts according to the total number of records of the second two-dimensional table to take out all records in the second two-dimensional table.

In the above method, the method for querying the second two-dimensional table according to the columns is as follows: firstly, acquiring a record subscript by using the query index of the second two-dimensional table according to a query condition, then taking out the field name, and finally taking out the specified record according to the record subscript.

The invention also provides a high-efficiency query device of the relational database, which comprises the following components:

the two-dimensional table creating module is used for writing records obtained by inquiring the relational database into a first two-dimensional table and writing the first two-dimensional table into Redis to generate a second two-dimensional table, and the second two-dimensional table is used as a cache of the first two-dimensional table;

the timestamp generation module is used for generating a first timestamp when the first two-dimensional table is generated, and constructing a third two-dimensional table by using the mapping relation between the first timestamp and the first two-dimensional table and storing the third two-dimensional table in a relational database; writing the first timestamp into the second two-dimensional table to form the second timestamp;

the timestamp updating module is used for updating the first timestamp when the first two-dimensional table is subjected to insertion, updating or deleting operation; updating the second timestamp according to the first timestamp;

the comparison module is used for comparing the time sequence of the first timestamp and the second timestamp and judging whether the second two-dimensional table needs to be generated or updated or not, and if no second timestamp exists, the first timestamp is regarded as the next timestamp;

the second two-dimensional table updating module is used for reading a record meeting the query condition from a relational database according to the query condition if the second two-dimensional table needs to be updated according to the result whether the second two-dimensional table needs to be generated or updated or not obtained by the comparing module, and then writing the record and the query index into Redis to update the second two-dimensional table; otherwise, the second two-dimensional table is not updated.

In the above apparatus, preferably, a trigger is provided on the relational database, and when the first two-dimensional table is subjected to an insert operation, an update operation, or a delete operation, the first timestamp is updated by the trigger.

According to the technical scheme, the efficient query method and device for the relational database provided by the invention solve the problems that the existing relational database query method causes great pressure on the database and the query efficiency is low. Compared with the prior art, the invention has the following beneficial effects:

establishing a two-dimensional table on Redis as a cache of a relational database, judging whether the two-dimensional table on the Redis needs to be updated or not by comparing the sequence of a time stamp of the two-dimensional table on the Redis and the time stamp of the two-dimensional table on the relational database, if so, reading the two-dimensional table of the relational database according to query conditions to obtain records meeting the query conditions, and then writing data such as the two-dimensional table of the relational database, query indexes and the like into the Redis; otherwise, the records meeting the specified conditions are directly queried from Redis. Because partial records can be obtained by inquiring from a two-dimensional table of Redis cache, the pressure of a relational database is reduced, and the inquiring efficiency is improved.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments of the present invention or the prior art will be briefly described and explained. It is obvious that the drawings in the following description are only some embodiments of the invention, and that for a person skilled in the art, other drawings can be derived from them without inventive effort.

FIG. 1 is a flow chart of an efficient query method for a relational database according to the present invention;

FIG. 2 is a flowchart of determining whether a second two-dimensional table on Redis needs to be generated or updated according to the present invention.

Detailed Description

The technical solutions of the embodiments of the present invention will be described clearly and completely with reference to the accompanying drawings, and it is to be understood that the embodiments described below are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments of the present invention without any inventive step, are within the scope of the present invention.

The realization principle of the invention is as follows:

and creating a two-dimensional table on Redis as a cache of Oracle, and inquiring records meeting specified conditions from Oracle or Redis by comparing the sequence of the time stamp of the two-dimensional table on Redis and the time stamp of the two-dimensional table on Oracle. Because partial records can be obtained by inquiring from a two-dimensional table of Redis cache, the pressure of an Oracle relational database is reduced, and the inquiring efficiency is improved.

In order to make the technical solution and implementation of the present invention more clearly explained and illustrated, several preferred embodiments for implementing the technical solution of the present invention are described below.

It should be noted that the terms of orientation such as "inside, outside", "front, back" and "left and right" are used herein as reference objects, and it is obvious that the use of the corresponding terms of orientation does not limit the scope of protection of the present invention.

In the following description, an Oracle database is taken as an example for explanation, and it is understood that the present disclosure is not limited to the Oracle database, but may be applied to all relational databases such as Mysql and sql server.

In addition, in the following description, the Oracle database is abbreviated as Oracle and the Redis database is abbreviated as Redis.

Referring to fig. 1, fig. 1 is a flowchart of an efficient query method for a relational database according to the present invention.

As shown in fig. 1, the efficient query method for a relational database provided by the present invention includes the following steps:

step 110, writing the record obtained by querying Oracle into the first two-dimensional table R1, writing the first two-dimensional table R1 into Redis to generate a second two-dimensional table R2, and using the second two-dimensional table R2 as a cache of the first two-dimensional table R1 for subsequent quick query.

Oracle stores a first time stamp T1 when the first two-dimensional table R1 is generated or updated, and the second two-dimensional table R2 stores a second time stamp T2 when the second two-dimensional table is generated or updated.

Specifically, the first two-dimensional table R1 is generated while recording the generation time of the first two-dimensional table R1 as the first timestamp T1. And constructing a third two-dimensional table by the mapping relation between the first timestamp T1 and the first two-dimensional table R1, storing the third two-dimensional table in Oracle, forming the third two-dimensional table, and inquiring the third two-dimensional table to obtain the first timestamp T1 of the first two-dimensional table R1 (the latest modification time of the first two-dimensional table R1).

Meanwhile, when the first two-dimensional table R1 is written to Redis to generate the second two-dimensional table R2, the first timestamp T1 is written to the second two-dimensional table R2 at the same time to form a second timestamp T2.

Redis has stored thereon a second two-dimensional table R2 corresponding to the first two-dimensional table R1, the second two-dimensional table R2 acts as a cache for Oracle, and the second two-dimensional table R2 stores therein a second timestamp T2 for each second two-dimensional table.

Redis is a high-performance key-value database, belongs to a key value database, has high performance, supports query by keys, does not support query according to values, and does not support query by two-dimensional tables according to columns. The application scene comprises the following steps: caching systems ("hot" data: high frequency read, low frequency write), counters, message queuing systems, leaderboard, social networking, real-time systems, and the like.

And a trigger is arranged on the Oracle, and when the first two-dimensional table T1 is subjected to operations such as insertion, updating, deletion and the like, the trigger triggers the operation of updating the first timestamp T1 in the first two-dimensional table R1 and updates the first timestamp T1 to the latest modification time of the first two-dimensional table R1.

The updating manner of the second timestamp T2 can be implemented in the following two manners.

The first mode is as follows: the first timestamp T1 is periodically polled and compared to the second timestamp T2, and if not, the second timestamp T2 is updated with the first timestamp T1.

The second way is: every time the second two-dimensional table R2 is queried, the second timestamp T2 in the second two-dimensional table R2 is first obtained, then the first timestamp T1 in the first two-dimensional table R1 is obtained, whether the first timestamp T1 and the second timestamp T2 are the same or not is compared, if the first timestamp T1 and the second timestamp T2 are the same, the second timestamp T2 is not updated, and if the first timestamp T1 and the second timestamp T2 are different, the second timestamp T1 is updated.

Step 120, when performing Oracle query, by comparing the time sequence of the second timestamp T2 of the second two-dimensional table on the Redis with the time sequence of the first timestamp T1 of the first two-dimensional table on the Oracle, judging whether the second two-dimensional table on the Redis needs to be generated or updated, if so, performing step 130; otherwise, step 140 is performed.

Specifically, as shown in fig. 2, the step of determining whether the second two-dimensional table T2 on Redis required to be generated or updated includes the following steps:

step 121, reading a first timestamp T1 of the first two-dimensional table from Oracle, and reading a second timestamp T2 of the second two-dimensional table corresponding to the first two-dimensional table from Redis.

Step 122, judging whether the second timestamp T2 exists, if the second timestamp T2 does not exist, it indicates that the Redis does not have a second two-dimensional table corresponding to the first two-dimensional table, and Redis required to be updated, so that the record obtained by querying Oracle and the query index are written into the Redis to generate a second two-dimensional table R2, and the first timestamp T1 is written into the second two-dimensional table R2, so as to form a second timestamp T2.

If the second timestamp T2 exists, step 123 is executed, the time sequence of the first timestamp T1 and the second timestamp T2 is compared, if the first timestamp T1 is later, that is, the time of the first timestamp T1 is later than the time of the second timestamp T2, it is indicated that the first two-dimensional table R1 is newer, the second two-dimensional table on Redis needs to be updated, so the second two-dimensional table R2 on Redis is updated according to the first two-dimensional table R1 on Oracle, and the second timestamp T2 is updated by the first timestamp T1; if the first timestamp T1 is the same as the second timestamp T2, it is indicated that the first two-dimensional table R1 on Oracle is the same as the second two-dimensional table R2 on Redis, and the first two-dimensional table T1 on Oracle is not updated, and the second two-dimensional table on Redis does not need to be updated.

Step 130, reading records meeting the query conditions from Oracle according to the query conditions, writing the records into a first two-dimensional table R1, and then writing data such as the first two-dimensional table R1 and query indexes into Redis, so that a second two-dimensional table R2 corresponding to the first two-dimensional table R1 is generated in Redis, and the query is finished.

In step 140, the records meeting the query condition are directly queried from the second two-dimensional table R2 on the Redis.

In step 130 of the above method, the process of reading records satisfying the query condition from Oracle according to the query condition and writing the records into the first two-dimensional table is as follows:

the data is read from Oracle by using a select statement, the whole complete two-dimensional table can be directly read, or records meeting certain conditions are inquired by using an sql query statement, and the read records are written into the first two-dimensional table.

If the application program is realized by using C + + language, vector < map < string, string > can be used to store the read whole complete two-dimensional table in the memory, or the record which meets a certain condition is stored and inquired by using sql query statement, and the record is written into the first two-dimensional table.

In step 130, the first two-dimensional table obtained by querying Oracle and the query index are written into Redis to generate or update the second two-dimensional table, which includes the following contents:

a second timestamp identifying whether the second two-dimensional table needs to be updated. And the second timestamp uses string object storage, and when cache data of the first two-dimensional table on Oracle is written into Redis or the second two-dimensional table is updated, the first timestamp T1 of the corresponding first two-dimensional table on Oracle is written into Redis to be used as a second timestamp T2.

Recording the total number, using string object storage for inquiring the second two-dimensional table;

and column names are stored by using string objects, when the column names of the second two-dimensional table are stored, all the column names are spliced together to be stored as a character string by using "|" as a separator because the second two-dimensional table may contain a plurality of columns. "is used as a delimiter because it is assumed that the character is not included in the column name. For example, a second two-dimensional table T2 contains two columns, F1 and F2, which are then stored in Redis as T2: FIELDS- > F1| F2.

The contents of the record, the key of which is the subscript of the record in the second two-dimensional table R2, are stored using the string object. Since the column names of the second two-dimensional table are stored independently, the column names do not need to be stored when the second two-dimensional table is stored. Setting keys of each row of records as subscripts, using "|" as a separator for values of each field, and splicing the values of each field together to store as a character string. It should be understood that using "|" as a delimiter, it is assumed that the values of the various fields do not contain the character.

And inquiring the index, storing by using a hash object, and quickly positioning the record meeting the specified column in the second two-dimensional table.

When writing the second two-dimensional table into the Redis, the old data of the second two-dimensional table in the Redis is cleared firstly, and the cleared data comprises a related second timestamp, a total number of records, a column name, a record and query index data in the second two-dimensional table.

The two-dimensional table lookup index is used to satisfy the two-dimensional table record index that quickly locates to a specified column value. Assuming that a query needs to be made to column F1 of a two-dimensional table T (either the first two-dimensional table or the second two-dimensional table) (T may contain more columns), there are V1, V2, V3,. at the value of the column F1 field, the index of the two-dimensional table record when the value of F1 is V1 is 1, 4; the two-dimensional table when the value of F1 is V2 is labeled 2, 5; when the two-dimensional table record for the value of F1, V3, is labeled 3, 6, 7, then the index constructed for the F1 field is:

T:F1:V1->1|4

T:F1:V2->2|5

T:F1:V3->3|6|7

string objects are employed to store record suffixes, with the suffixes separated by "|". To improve storage efficiency, a hash object of Redis is used to store the above relationships. Wherein, the key is T: F1, the fields are V1, V2 and V3 respectively, and the values are 1|4, 2|5 and 3|6|7 respectively.

Similarly, assume that the index constructed for field F2 is as follows:

T:F2:T1->1|4|7

T:F2:T2->2|3|5|6

then, after the query index is constructed, the query can be performed on the column F1 or F2, or the conditions of the column F1 and the column F2 can be specified at the same time.

For example, it is desirable to look up all records in the two-dimensional table T that satisfy the column F1 value of V2 and the column F2 value of T2. The record with the column F1 value of V2 is denoted by 2, 5 by the index T: F1: V2- >2|5, the record with the column F2 value of T2 is denoted by 2, 3, 5, 6 by the index T: F2: T2- >2|3|5|6, and the two record subscripts are intersected to obtain a subscript 2, 5, namely the record 2 and the record 5 satisfy the query condition.

The process of writing the second two-dimensional table into Redis involves multi-step operation, and in order to avoid other application programs from modifying the data of the second two-dimensional table in the process of writing data, the Redis transaction mechanism is used in the method.

When writing a second two-dimensional table record to Redis is started, the WATHCH command of Redis is used firstly, and the EXEX of Redis is used for committing the transaction in the last step. If there are other application programs modifying the second two-dimensional table data after executing the WATHCH command, the transaction execution fails, and the relevant data of the second two-dimensional table cannot be written into Redis but is written by other application programs.

After writing the latest second two-dimensional table and the related query index into the Redis, the records satisfying the specified condition can be queried, and in step 140, the process of querying the second two-dimensional table record from the Redis is as follows:

and during query, distinguishing whether the whole second two-dimensional table needs to be read or not. If the whole second two-dimensional table needs to be inquired, the total number of records of the second two-dimensional table is read firstly, then the field names are taken out, the record subscripts are circularly traversed according to the total number of the records of the second two-dimensional table, and all the records of the second two-dimensional table are taken out.

If the second two-dimensional table needs to be queried according to the columns, the record subscript is obtained by using the query index of the second two-dimensional table according to the query condition, then the field name is taken out, and finally the specified record is taken out according to the record subscript. The record index satisfying the specified query condition is obtained, and the construction and the use of the query index can be referred to above.

The process of reading the second two-dimensional table to the Redis involves multi-step operation, and in order to avoid other application programs from modifying the data of the second two-dimensional table in the process of reading the data, the application also uses a transaction mechanism of the Redis. After the data of the whole second two-dimensional table is read, firstly executing a WATCH command, after the total number of records or the subscripts of the records are obtained, executing a MULTII command, and after all Redis query commands are executed, executing an EXEC command. And if other application programs modify the data of the second two-dimensional table in the process of reading the data of the second two-dimensional table, the reading fails. At this time, the application program can read again according to actual needs, or prompt an error.

According to the efficient query method of the relational database, the Redis storage and the cache of the query two-dimensional table are used, the Redis cache is not simply constructed according to the query conditions, but the whole two-dimensional table and the query condition index are cached, the probability that the query hits the Redis cache is improved, the characteristics of Redis high performance can be fully utilized, most requests are processed on the Redis, the pressure of the Oracle database is favorably reduced, and the query efficiency is improved.

By combining the description of the above specific embodiments, the efficient query method and apparatus for a relational database provided by the present invention have the following advantages compared with the prior art:

firstly, a two-dimensional table is created on Redis as an Oracle cache, records can be inquired and obtained from the two-dimensional table of the Redis cache, pressure of an Oracle relational database is relieved, and inquiry efficiency is improved. .

And secondly, judging whether the two-dimensional table on the Redis needs to be updated or not by comparing the time stamp of the two-dimensional table on the Redis with the time stamp of the two-dimensional table on the Oracle, and ensuring the accuracy and consistency of data recording.

Thirdly, when the two-dimensional table on the Oracle is subjected to insertion, updating or deletion operation, the trigger is used for updating the first timestamp of the first two-dimensional table on the Oracle, and the second timestamp is updated according to the first timestamp when the second two-dimensional table on the Redis is polled or read, so that the accuracy and consistency of data recording are further ensured.

Fourthly, the transaction mechanism of Redis is used, and other application programs are prevented from modifying the data of the two-dimensional table in the process of reading and writing the data.

Finally, it should also be noted that the terms "comprises," "comprising," or any other variation thereof, as used herein, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The present invention is not limited to the above-mentioned preferred embodiments, and any structural changes made under the teaching of the present invention shall fall within the scope of the present invention, which is similar or similar to the technical solutions of the present invention.

Claims

1. An efficient query method for a relational database is characterized by comprising the following steps:

2. The method of claim 1,

generating the first time stamp when the record in the relational database is written into the first two-dimensional table, constructing a third two-dimensional table by the mapping relation between the first time stamp and the first two-dimensional table, and storing the third two-dimensional table in the relational database;

3. The method of claim 2, wherein determining whether the second two-dimensional table needs to be generated or updated comprises:

4. The method of claim 1, wherein writing the record obtained by querying a relational database and a query index into Redis generates or updates the second two-dimensional table, comprising:

recording the total number, and querying the second two-dimensional table;

a column name;

5. The method of claim 4,

when the column names of the second two-dimensional table are stored on Redis, all the column names in the second two-dimensional table are spliced together by using separators to be stored as a character string;

and setting keys of each row of records in the second two-dimensional table as subscripts, using separators for values of each field, and splicing the values of each field together to store the values as a character string.

6. Method according to claim 1, characterized in that when starting writing to Redis the second two-dimensional table, the WATHCH command is used first and after the writing is completed the transaction is committed using EXEX.

7. The method of claim 1, wherein the second two-dimensional table is queried as follows: and reading the total number of records of the second two-dimensional table, then taking out the field names, and performing cyclic traversal on the record subscripts according to the total number of records of the second two-dimensional table to take out all records in the second two-dimensional table.

8. The method of claim 1, wherein the second two-dimensional table is queried according to columns as follows: firstly, acquiring a record subscript by using the query index of the second two-dimensional table according to a query condition, then taking out the field name, and finally taking out the specified record according to the record subscript.

9. An efficient query device for relational databases, comprising:

10. The apparatus of claim 9, wherein a trigger is provided on the relational database, and the first timestamp is updated by the trigger when an insert, update, or delete operation occurs on the first two-dimensional table.