CN101963993A

CN101963993A - Method for fast searching database sheet table record

Info

Publication number: CN101963993A
Application number: CN 201010514250
Authority: CN
Inventors: 刘镇; 孙敏; 张笑非; 钱萍
Original assignee: Jiangsu University of Science and Technology
Current assignee: Jiangsu University of Science and Technology; Nantong Power Supply Co of Jiangsu Electric Power Co Ltd
Priority date: 2010-10-21
Filing date: 2010-10-21
Publication date: 2011-02-02
Anticipated expiration: 2030-10-21
Also published as: CN101963993B

Abstract

The invention discloses a method for fast searching a database sheet table record, which is used for a table record querying module in a computer database. The method comprises the following steps of: optimizing a query statement, regenerating a new query statement, and applying for a special data area in a computer memory at the same time for storing the result data of the last query; extracting database sheet table names according to a new query statement, reading the data in the extracted database sheet table according to certain rules, and storing the read data into a data exchange area for exchanging the data in the whole query process; dynamically updating the data in the data exchange area; and modifying query objects of the new query statement into the data in the data area, linking the query result to the data area which is applied for, and adding one to the value of query frequency fields in the record containing the query result in the database sheet table at the same time. The method can obviously improve search time for querying the data, does not have additional hardware and hard disk storage spaces and saves hardware cost.

Description

The method that a kind of database list table record is searched fast

Technical field

The present invention relates to single table record data access technology field in the database, more precisely relate to a kind of method that database list table record is searched fast.

Background technology

Along with the fast development of science and technology, database has been widely used in various fields, and database is the core of each big information site, requires also more and more higher to the data acquisition time of showing in the database.

Because the data-centered website of current social network and forum and so on, all there are every day thousands of or more data need be stored in the information database, post content and money order receipt to be signed and returned to the sender content as forum's class, data such as the user basic information of social network class, user's sharing information and message, and present data storage is the category storage, the data that are each classification are to be stored in the table, and the data volume under therefore such situation in the tables of data also is relatively big.

If the user needs the query-relevant data record, then inquiry velocity will be the problem that need at first consider, present method is to find corresponding table in information database, total data in the his-and-hers watches is inquired about again, at last data are finally shown, obviously, it is consuming time relatively directly searching data in the relatively big table of this data volume, the present invention is used for Computer Database to show enquiry module, the developer can utilize the technology of the present invention to improve single table record searching speed problem in the database, with the information of needs earlier by searching showing of part, then with follow-up lookup result successively be shown to the user.

The method that the existing database table record is searched is to set up index on tables of data, use hardware to share methods such as magnetic disc i/o, the method that realizes needs extra hardware and additional space to take on like this, and then cost is relatively more expensive, and the time of searching is not significantly improved.

Summary of the invention

In order to solve the problem of record search time in the tables of data, fundamental purpose of the present invention provides the method that a kind of database list table record is searched fast, make the detailed data that the user can find fast to be needed from the tables of data of bigger data volume, to satisfy user's needs.

The present invention adopts following technical scheme for achieving the above object:

The method that a kind of database list of the present invention table record is searched fast is used for Computer Database and shows enquiry module, comprises the steps:

Step 1: carry out following method optimization 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;

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

Step 3: upgrade the data in the data exchange zone;

Step 4: new query statement query object is revised as the data of 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;

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

Step 6: return Query Result.

Wherein, the following method optimization described in the step 1 comprises: the identidication key information and the principle of optimality.

Wherein, described identidication key information comprises: HAVING,, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION information;

The described 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 NOT EXISTS; Connect replacement EXISTS with table; Replace DISTINCT with EXISTS; Replace the UNION rule with UNION-ALL.

Wherein, comprise according to the data in certain regular reading database list table described in the step 2:

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

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

Wherein, the data of upgrading described in the step 3 in the data exchange zone 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 obtaining of data according to the certain rule in the step 2 to tables of data in the time interval of setting.

Wherein, the data field described in the step 4 in the internal memory comprises data exchange zone and last Query Result data field, and when inquiring about for the first time, at first Cha Xun data field is last Query Result data field data; Search the data exchange zone data after having searched last Query Result data field.

Wherein, the result data district of described last inquiry is meant the deposit data district of last final Query Result, is used for the first-selected data query of this inquiry;

Described data exchange zone is meant the data recording of nominated bank in the reading database list table, is used for the renewal that the data query of this data exchange zone carries out the next round 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.

Adopt the method for the invention, compared with prior art, through the query statement after optimizing, add that the data that will inquire about are frequent accessed data of record in the table, and the data that will inquire about all are to leave in the internal storage data district, and the speed of access memory is more a lot of soon than the speed of visit data table, therefore the present invention can obviously improve searching the time of data query, this technology has all been used software engineering, does not increase extra hardware and hard-disc storage space, has saved hardware cost.

Description of drawings

Fig. 1 optimizes the process flow diagram of query statement for the present invention.

Fig. 2 carries out the process flow diagram of inquiry for query statement of the present invention.

Fig. 3 searches the process flow diagram of data recording in the tables of data fast for the present invention.

Embodiment

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

In Fig. 1, the present invention has illustrated the process flow diagram of optimizing query statement.Wherein the step of You Huaing comprises: identidication key information, the principle of optimality, and being described as follows of each several part:

Step 11, identidication key information comprises: HAVING,, DELETE, IN, NOT IN, EXISTS, DISTINCT, information such as UNION.

Step 12, the 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 NOT EXISTS; Connect replacement EXISTS with table; Replace DISTINCT with EXISTS; Replace rules such as UNION with UNION-ALL.

After enquiry module starts, when list table query requests is arranged in this module, obtain query statement, as follows:

SELECT?ID，NAME，SUM(AGE) FROM EMPLOYEE

GROUP?BY?ID?HAVING?ID>8

Then recognize key word this moment HAVING,〉etc., article one and the 3rd rule in the contrast principle of optimality are optimized, and promptly the query statement after the final optimization pass is as follows:

SELECT?ID，NAME，SUM(AGE) FROM?EMPLOYEE

WHERE?ID>=9?GROUP?BY?ID

In Fig. 2, the present invention has illustrated the process flow diagram of query statement execution inquiry.

(1) after Fig. 1 finishes, will produce new query statement, judge whether this inquiry is inquiry for the first time, if, then carry out (2), if not, then carry out (3).

(2) obtain the data sorting type that reads in the rule, sort according to the sort type and the sort field of appointment, obtain reading and recording line number and current record collection sign, from this table, obtain the data of designated recorder line number, guarantee that these data are the data according to the most forward designated recorder line number of sort field, and this data storage is arrived internal storage data exchange area and last Query Result data field.

(3) the query statement query object is revised as the internal storage data district.At first inquire about last Query Result data field, will remove last Query Result data field after having inquired about this statement, before Query Result is linked on the last Query Result data field.

(4) data query exchange area is linked to Query Result on the last Query Result data field.

(5) judge in the table whether also have readable data,, carry out (6),, then carry out (7) if do not have if having.

(6) obtain next record collection in the table, record set is stored in data exchange zone, carry out (4).

(7) will show in the value of inquiry times field add one.

(8) return the data of last Query Result data field.

In Fig. 3, the present invention has illustrated the process flow diagram of searching data recording in the tables of data fast.

(1) obtains query statement, and be optimized this query statement, generate new query statement.

(2) carry out the execution of new query statement, the Query Result after carrying out is linked on the last Query Result data field, and the data field in the updating memory regularly.

(3) data in the last Query Result data field in the internal memory are returned demonstration.

Claims

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 3: upgrade the data in the data exchange zone;

Step 4: new query statement query object is revised as 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;

Step 6: return Query Result.

2. the method that a kind of database list table record according to claim 1 is searched fast is characterized in that: the following method optimization described in the step 1 comprises: the identidication key information and the principle of optimality.

3. the method that a kind of database list table record according to claim 2 is searched fast is characterized in that:

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

4. the method that a kind of database list table record according to claim 1 is searched fast is characterized in that: comprise according to the data in certain regular reading database list table described in the step 2:

5. the method that a kind of database list table record according to claim 1 is searched fast, it is characterized in that: the data of upgrading described in the step 3 in the data exchange zone 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 obtaining of data according to the certain rule in the step 2 to tables of data in the time interval of setting.

6. the method that a kind of database list table record according to claim 1 is searched fast, it is characterized in that: the data field described in the step 4 comprises data exchange zone and last Query Result data field, when inquiring about for the first time, at first Cha Xun data field is last Query Result data field data; Search the data exchange zone data after having searched last Query Result data field.

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