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

Abstract

Translated fromChinese

本发明公布了一种数据库单表记录快速查找的方法，用于计算机数据库中表记录查询模块，包括：对查询语句进行优化，重新生成新查询语句，同时在计算机内存中申请特别数据区，用于保存上一次查询的结果数据；根据新查询语句提取出数据库单表名，将所述提取出的数据库单表中的数据按照一定规则进行读取，将读取出的数据存入该数据交换区，用在整个查询过程中的数据交换；动态更新数据交换区中的数据；将新查询语句查询对象修改为数据区中的数据，将查询结果链接在已申请的数据区，同时将数据库单表中含有查询结果记录中查询次数字段的值加一；本发明一方面能够明显提高查询数据的查找时间，同时没有增加额外的硬件和硬盘存储空间，节省了硬件成本。

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

Translated fromChinese

一种数据库单表记录快速查找的方法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.

现有数据库表记录查找的方法是在数据表上建立索引，使用硬件来分担磁盘I/O等方法，这样实现的方法需要额外的硬件以及额外的空间来担当，则成本比较昂贵，而且查找时间并没有明显提高。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.

其中，所述识别关键字信息，包括：HAVING，>，DELETE，IN，NOT IN，EXISTS，DISTINCT，UNION信息；Wherein, the identified keyword information includes: HAVING, >, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION information;

所述优化规则，是根据以下优化原理来优化查询语句，包括：用Where子句替换HAVING子句；使用DECODE函数；用>=替代>；使用表的别名；用TRUNCATE替代DELETE；用ROWID进行表访问；用EXISTS替代IN；用NOT EXISTS替代NOT IN；用表连接替换EXISTS；用EXISTS替换DISTINCT；用UNION-ALL 替换UNION规则。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;

读数据存储过程，进一步需要的参数包括：数据库IP地址、数据库名、登入用户名、登入密码、数据表名、数据排序类型、读取记录行数、当前记录集标志。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:

步骤a，将数据库单表中数据按照配置读取规则中的排序字段进行排序，然后取指定行的数据记录；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;

步骤b，当查询语句查询完该数据交换区后，将查询结果链接到上一次查询结果数据区；Step b, when the query statement finishes querying the data exchange area, link the query result to the previous query result data area;

步骤c，接着取表中剩下记录中的指定行的数据记录。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

图1为本发明优化查询语句的流程图。FIG. 1 is a flow chart of optimizing query statements in the present invention.

图2为本发明查询语句执行查询的流程图。Fig. 2 is a flow chart of executing a query by a query statement in the present invention.

图3为本发明快速查找数据表中数据记录的流程图。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:

在图1中，本发明说明了优化查询语句的流程图。其中优化的步骤包括：识别关键字信息、优化规则，各部分的说明如下：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:

步骤11，识别关键字信息，包括：HAVING，>，DELETE，IN，NOT IN，EXISTS，DISTINCT，UNION等信息。Step 11, identify keyword information, including: HAVING, >, DELETE, IN, NOT IN, EXISTS, DISTINCT, UNION and other information.

步骤12，优化规则，是根据以下优化原理来优化查询语句，包括：用Where子句替换HAVING子句；使用DECODE函数；用>=替代>；使用表的别名；用TRUNCATE替代DELETE；用ROWID进行表访问；用EXISTS替代IN；用NOT EXISTS替代NOT IN；用表连接替换EXISTS；用EXISTS替换DISTINCT；用UNION-ALL 替换UNION等规则。 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 SELECT ID, NAME, SUM(AGE) FROM EMPLOYEE

GROUP BY ID HAVING ID>8GROUP BY ID HAVING ID>8

则此时识别到关键字有HAVING，>等，对照优化规则中的第一条和第三条规则进行优化，即最终优化后的查询语句如下所示：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 EMPLOYEESELECT ID, NAME, SUM(AGE) FROM EMPLOYEE

WHERE ID>=9 GROUP BY IDWHERE ID>=9 GROUP BY ID

在图2中，本发明说明了查询语句执行查询的流程图。In FIG. 2, the present invention illustrates a flow chart of query statement execution.

（1）在图1结束后将产生新的查询语句，判断该查询是否是第一次查询，如果是，则执行（2），如果不是，则执行（3）。(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）获取读取规则中的数据排序类型、按照指定的排序类型和排序字段进行排序，获取读取记录行数和当前记录集标志，从该表中获取指定记录行数的数据，保证该数据是按照排序字段的最靠前指定记录行数的数据，并将该数据存储到内存数据交换区和上一次查询结果数据区。(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）将查询语句查询对象修改为内存数据区。首先查询上一次查询结果数据区，查询完该语句后将清除上一次查询结果数据区，将之前的查询结果链接到上一次查询结果数据区上。(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）查询数据交换区，将查询结果链接到上一次查询结果数据区上。(4) Query the data exchange area, and link the query result to the previous query result data area.

（5）判断表中是否还有可读数据，如果有，执行（6），如果没有，则执行（7）。(5) Determine whether there is any readable data in the table, if yes, execute (6), if not, execute (7).

（6）获取表中下一记录集，将记录集存储在数据交换区，执行（4）。(6) Obtain the next record set in the table, store the record set in the data exchange area, and execute (4).

（7）将表中查询次数字段的值加一。(7) Add one to the value of the query times field in the table.

（8）返回上一次查询结果数据区的数据。(8) Return the data in the last query result data area.

在图3中，本发明说明了快速查找数据表中数据记录的流程图。In FIG. 3, the present invention illustrates a flow chart of quickly looking up data records in a data table.

（1）获取查询语句，并进行优化该查询语句，生成新查询语句。(1) Obtain the query statement, optimize the query statement, and generate a new query statement.

（2）进行新查询语句的执行，将执行后的查询结果链接在上一次查询结果数据区上，并定时更新内存中的数据区。(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）将内存中的上一次查询结果数据区中的数据返回显示。(3) Return and display the data in the last query result data area in the memory.

Claims (7)

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: 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 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 5: repeated execution of steps two and 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 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;

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.

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:

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.

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;

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.

Images