CN101963993B - Method for fast searching database sheet table record - Google Patents

Abstract

The invention discloses a method for quickly searching a single table record in a database, which is used in a table record query module in a computer database, including: optimizing the query statement, regenerating a new query statement, and applying for a special data area in the computer memory at the same time, using To save the result data of the last query; extract the name of the database single table according to the new query statement, read the data in the extracted database single table according to certain rules, and store the read data into the data exchange area, used for data exchange during the entire query process; dynamically update the data in the data exchange area; modify the query object of the new query statement to the data in the data area, link the query results to the applied data area, and at the same time update the database list The value of the query number field in the query result record in the table is increased by one; on the one hand, the present invention can obviously improve the search time of query data, and at the same time does not increase additional hardware and hard disk storage space, saving hardware costs.

Description

A Method of Quickly Finding Records in a Single Table of Database

technical field

The invention relates to the technical field of single-table record data access in a database, and more precisely relates to a method for quickly searching a single-table record in a database.

Background technique

With the rapid development of science and technology, databases have been widely used in various fields, and databases are the core of major information websites, and the requirements for data acquisition time of tables in databases are getting higher and higher.

Due to the current data-centric websites such as social networks and forums, thousands or more pieces of data need to be stored in the information database every day, such as posting content and reply content of forums, social network Data such as user basic information, user shared information, and messages are currently stored by category, that is, each category of data is stored in a table, so the amount of data in the data table is relatively large in this case .

If the user needs to query related data records, the query speed will be the first consideration. The current method is to find the corresponding table in the information database, then query all the data in the table, and finally display the data. Obviously, it is relatively time-consuming to directly search for data in a table with a relatively large amount of data. The present invention is used for table query modules in computer databases. Developers can use the technology of the present invention to improve the efficiency of single-table record search in databases. For speed issues, the required information is first displayed through the search part, and then the subsequent search results are displayed to the user one after another.

The existing database table record lookup method is to build an index on the data table, use hardware to share disk I/O and other methods, this method requires additional hardware and extra space to take care of it, the cost is relatively expensive, and the search time did not improve significantly.

Contents of the invention

In order to solve the problem of searching time for records in data tables, the main purpose of the present invention is to provide a method for quickly searching records in a single table of a database, so that users can quickly find the required details from a data table with a relatively large amount of data. data to meet user needs.

In order to achieve the above object, the present invention adopts the following technical solutions:

The present invention relates to a method for quickly searching a single table record in a database, which is used for a table query module in a computer database, and comprises the following steps:

Step 1: Perform the following method optimization according to the query statement, regenerate a new query statement, and apply for the last query result data area in the computer memory at the same time to save the last query final result data;

Step 2: extracting the name of the single table of the database according to the new query statement, reading the data in the single table of the database extracted according to certain rules, and allocating a data exchange area of the size of the read data in the computer memory, Store the read data into the data exchange area for data exchange during the entire query process;

Step 3: update the data in the data exchange area;

Step 4: Change the query object of the new query statement to the data in the data area, link the query result to the previously applied query result data area, and add one to the value of the query times field in the query result record in the database single table;

Step 5: Repeat Step 2 and Step 4 until all the data in the single table of the database is fetched;

Step 6: Return the query result.

Wherein, the following method optimization described in step 1 includes: identifying keyword information and optimization rules.

Wherein, the identified keyword information includes: HAVING, >, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION information;

The optimization rule is to optimize the query statement according to the following optimization principles, including: replacing the HAVING clause with the WHERE clause; using the DECODE function; replacing > with >=; using the alias of the table; replacing DELETE with TRUNCATE; Access; replace IN with EXISTS; replace NOT IN with NOT EXISTS; replace EXISTS with table joins; replace DISTINCT with EXISTS; replace UNION rules with UNION-ALL.

Among them, reading the data in the single table of the database according to certain rules as described in step 2 includes:

Configure the reading rules, including: data sorting type, sorting field, number of read record rows, current record set flag;

In the process of reading data storage, further required parameters include: database IP address, database name, login user name, login password, data table name, data sorting type, number of read record rows, and current record set flag.

Wherein, the data in the update data exchange area described in the step 3 is regularly updated. At first an update thread is created in the query module, and a timer is started in the update thread. A certain rule in the data acquisition to the data table.

Wherein, the data area in the internal memory described in step 4 includes the data exchange area and the last query result data area. When querying for the first time, the data area first inquired is the last query result data area data; after searching the last query Search the data in the data exchange area after the result data area.

Wherein, the result data area of the last query refers to the data storage area of the last final query result, which is used for the preferred query data of this query;

The data exchange area refers to reading the data record of the specified row in the single table of the database. After the data in the data exchange area has been queried, the next round of data update is performed, including the following steps:

Step a, sort the data in the single table of the database according to the sorting field in the configuration read rule, and then fetch the data record of the specified row;

Step b, when the query statement finishes querying the data exchange area, link the query result to the previous query result data area;

Step c, then get the data records of the specified row in the remaining records in the table.

Adopt the method described in the present invention, compared with prior art, the query sentence after optimization, add the data to be queried is the data that records frequently accessed data in the table, and the data to be queried all is stored in the internal memory data area , and the speed of accessing the memory is much faster than the speed of accessing the data table, so the present invention can significantly improve the search time of the query data. This technology uses software technology without adding additional hardware and hard disk storage space, saving hardware costs.

Description of drawings

FIG. 1 is a flow chart of optimizing query statements in the present invention.

Fig. 2 is a flow chart of executing a query by a query statement in the present invention.

Fig. 3 is a flow chart of quickly searching data records in a data table according to the present invention.

Detailed ways

Below in conjunction with accompanying drawing, the implementation process of technical scheme is described in further detail basically according to the order of accompanying drawing:

In FIG. 1, the present invention illustrates a flow chart of optimizing query statements. The optimization steps include: identifying keyword information and optimizing rules. The descriptions of each part are as follows:

Step 11, identify keyword information, including: HAVING, >, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION and other information.

Step 12, the optimization rule, is to optimize the query statement according to the following optimization principles, including: replace the HAVING clause with the WHERE clause; use the DECODE function; use >= instead of >; use table aliases; use TRUNCATE instead of DELETE; use ROWID Table access; replace IN with EXISTS; replace NOT IN with NOT EXISTS; replace EXISTS with table join; replace DISTINCT with EXISTS; replace UNION with UNION-ALL, etc. the

After the query module is started, when there is a single-table query request in the module, the query statement is obtained, as follows:

SELECT ID, NAME, SUM(AGE) FROM EMPLOYEE

GROUP BY ID HAVING ID>8

At this time, it is recognized that the keywords include HAVING, >, etc., and optimize according to the first and third rules in the optimization rules, that is, the final optimized query statement is as follows:

SELECT ID, NAME, SUM(AGE) FROM EMPLOYEE

WHERE ID>=9 GROUP BY ID

In FIG. 2, the present invention illustrates a flow chart of query statement execution.

(1) After the end of Figure 1, a new query statement will be generated to determine whether the query is the first query, if yes, execute (2), if not, execute (3).

(2) Get the data sorting type in the read rule, sort according to the specified sorting type and sorting field, get the number of read record rows and the current record set flag, and get the data of the specified record row number from the table to ensure the The data is the data of the number of specified record rows according to the top of the sorting field, and the data is stored in the memory data exchange area and the last query result data area.

(3) Modify the query object of the query statement to the memory data area. First query the last query result data area, after querying the statement, the last query result data area will be cleared, and the previous query result will be linked to the last query result data area.

(4) Query the data exchange area, and link the query result to the previous query result data area.

(5) Determine whether there are readable data in the table, if yes, execute (6), if not, execute (7).

(6) Obtain the next record set in the table, store the record set in the data exchange area, and execute (4).

(7) Add one to the value of the query times field in the table.

(8) Return the data in the last query result data area.

In FIG. 3, the present invention illustrates a flow chart of quickly looking up data records in a data table.

(1) Obtain the query statement, optimize the query statement, and generate a new query statement.

(2) Execute the new query statement, link the executed query result to the data area of the previous query result, and regularly update the data area in the memory.

(3) Return and display the data in the last query result data area in the memory.

Claims (3)

1. the method that database list table record is searched fast is used for Computer Database and shows enquiry module, it is characterized in that, comprises the steps:

Step 1: be optimized according to query statement, regenerate new query statement, in calculator memory, apply for once Query Result data field simultaneously, be used to preserve last inquiry net result data;

The method of said optimization comprises: the identidication key information and the principle of optimality;

Said identidication key information comprises: HAVING,＞, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION information;

The said principle of optimality is to optimize query statement according to the following principle of optimality, comprising: replace HAVING clause with Where clause; Use the DECODE function; With＞=substitute＞; Use the another name of table; Substitute DELETE with TRUNCATE; Carry out table access with ROWID; Substitute IN with EXISTS; Substitute NOT IN with NOTEXISTS; Connect replacement EXISTS with table; Replace DISTINCT with EXISTS; With UNION-ALL replacement UNION rule;

Step 2: extract database list table name according to new query statement; Data in the said database list table that extracts are read according to certain rule; And in calculator memory, distribute the data exchange zone of said reading of data size; Deposit the data that read out in this data exchange zone, be used for exchanges data at whole query script;

Saidly comprise according to the data in certain regular reading database list table:

Rule is read in configuration, comprising: data sorting type, sort field, reading and recording line number and current record collection sign;

The read data storing process, the parameter that further needs comprises: database IP address, database name, login user name, login password, data table name, data sorting type, reading and recording line number and current record collection sign;

Step 3: the data in the exchange area that Updates Information;

Step 4: the query object of new query statement is revised as the data in the data field, Query Result is linked at the last Query Result data field of having applied for, simultaneously the value that contains inquiry times field in the Query Result record in the database list table is added one;

Described data field comprises data exchange zone and last Query Result data field, and when inquiring about for the first time, at first the data field of inquiry is last Query Result data field; After having inquired about last Query Result data field, the data field of inquiry is a data exchange zone again;

Step 5: repeated execution of steps two is to step 4, all data in having got database list table;

Step 6: return Query Result.

2. the method that a kind of database list table record according to claim 1 is searched fast; It is characterized in that: the data in the exchange area that Updates Information described in the step 3 are regularly to upgrade; At first in enquiry module, create a more new thread; More starting a timer in the new thread, timer carries out Data Acquisition according to the certain rule in the step 2 to tables of data in the time interval of setting.

3. the method that a kind of database list table record according to claim 1 is searched fast is characterized in that: the result data district of said last inquiry is meant the deposit data district of last final Query Result, is used for the first-selected data query of this inquiry;

Said data exchange zone is meant the data recording of nominated bank in the reading database list table, is used for the data query of this data exchange zone and carries out the next round updating data after intact, comprises the steps:

Step a, the sort field that data in the database list table are read in the rule according to configuration sorts, and gets the data recording of nominated bank then;

Step b after query statement has been inquired about this data exchange zone, is linked to last Query Result data field with Query Result;

Step c then gets the data recording of being left the nominated bank in the record in the table.

Images