CN104268298A - Method for creating database index and inquiring data - Google Patents
Method for creating database index and inquiring data Download PDFInfo
- Publication number
- CN104268298A CN104268298A CN201410583221.6A CN201410583221A CN104268298A CN 104268298 A CN104268298 A CN 104268298A CN 201410583221 A CN201410583221 A CN 201410583221A CN 104268298 A CN104268298 A CN 104268298A
- Authority
- CN
- China
- Prior art keywords
- data
- inquiry
- extended field
- property value
- value
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
Abstract
The invention relates to a method for creating a database index and inquiring data. The method comprises the following steps: (1) reserving an extension field in a table, and creating a unique index for the extension field; (2) during data storage, splicing a mark ID (identifier) and data sampling time to generate a value of the extension field; (3) storing the value of the extension field and values of other fields into the table together, wherein the values of the extension field and the other fields are in a data record; (4) during data inquiry, splicing the mark ID for user inquiry and the data sampling time to obtain the value of the extension field needing to be inquired, and performing data inquiry by using the value of the extension field as an inquiry condition. According to the method, in the simultaneous process of database storage and inquiry, key fields are spliced into a field, and the unique index is built, so that the requirements for data uniqueness are met, the inquiry efficiency is improved, and the speed of storing the data into a database is further guaranteed.
Description
Technical field
The present invention relates to database field, particularly relate to a kind of method of creation database index and inquiry thereof.
Background technology
In a lot of industry, all the time all mass data is produced, thus the memory property demand defined mass data, and along with the development of every profession and trade infosystem, create again the query performance demand to these mass historical datas, such as inquire about all records of a certain specific sign id in section sometime, thus require that we are the demand that this sign id and time field set up that corresponding index meets fast query.Simultaneously owing to importing in storage system at the new data demand of continuous generation, so also require that Database Systems have high boot speed.
And index is a kind of structure sorted to the value of row or multiple row in database table.Its mode is binary tree or Hash lookup, avoids the situation scanning full table.More popular, the effect of index is equivalent to the catalogue of books, can find required content fast according to the page number in catalogue.Have in the table of a large amount of record at one, if when not creating the index of querying condition, its way of search is exactly entirely show search, record and querying condition in all tables is compared, then returns Query Result, but can consume plenty of time and disk I/O operation like this; Another kind of mode is exactly rationally create index, first searches the index value meeting querying condition in the index, then finds by the line number that index value is corresponding the result set meeting querying condition fast, can have very great help like this to query performance.
For this inquiry scene comparing universality of sign id+ data sampling time, the uniqueness index can setting up sign id and data sampling time respectively or the joint index setting up these two fields are to improve query performance and to ensure data uniqueness, but along with the increase of single table data capacity, Database Systems need to safeguard a large amount of indexes, create the performance that too much index will certainly reduce storage.
Such as have in certain data logger and indicate id, sampling time and some other field etc.Wherein querying condition is for indicating id and sampling time, if joint index sign id and sampling time field being created respectively to index or establishment two fields no doubt can promote query performance, but along with the increase Database Systems of data volume will safeguard a large amount of indexes, the performance that database additions and deletions change can not be improved, its performance can be affected on the contrary, we have proposed the another kind of method creating index based on this reason, the method effectively can ensure that data uniqueness and inquiry velocity can reduce again too much index to storing the negative effect brought.
Summary of the invention
The present invention overcomes above-mentioned weak point, object is a kind of method providing creation database index and inquiry thereof, the method uniquely can determine the thought of a data record according to indicating id and time time, by an extended field reserved in table, and it is this extended field establishment uniqueness index.When data store, the value of extended field is spliced by the value indicating id and time time two fields to get, stored in database together with other fields.During data query, query argument is indicated id and time time by with the value storing identical mode and be spliced into extended field, extended field is carried out data query as SQL parameter.The method not only meets the uniqueness requirement of data, improves search efficiency, but also ensure that the speed of data stored in database.
The present invention achieves the above object by the following technical programs: a kind of method of creation database index and inquiry thereof, comprises the steps:
(1) create data logger and reserve extended field;
(2) for extended field creates uniqueness index;
(3) be attribute assignment each in data storage object;
(4) property value of extended field in id and data sampling time splicing generation data storage object will be indicated;
(5) using the property value of extended field property value and other fields as a data record stored in database;
(6) property value of the extended field needing inquiry is obtained by sign id and data sampling time splicing;
(7) utilize extended field property value to carry out data query, obtain data record.
As preferably, the type of described extended field to be decimal system length be 20 long.
As preferably, described step (4) is identical with the joining method of data sampling time to indicating id with step (6).
As preferably, described splicing step is as follows:
1) data sampling time is converted to the number of seconds amount of data sampling time representative, the type of number of seconds amount to be length the be long of 10;
2) sign id is converted to the long number that length is no more than 10, long number is multiplied by 10 powers of 10;
3) by step 1) and step 2) result carry out the property value being added the field that is expanded.
As preferably, described step (7) utilizes extended field property value to carry out data query for extended field value is carried out data query as the parameter of SQL.
Beneficial effect of the present invention is: (1) the present invention only sets up uniqueness index to a field, not only effectively ensure that data uniqueness and inquiry velocity, but also avoids too much index to storing the negative effect brought; (2) the present invention carries out database at the same time stored in the process with inquiry, critical field is spliced into a field, ensure that the speed of data stored in database.
Accompanying drawing explanation
Fig. 1 is one-piece construction schematic diagram of the present invention;
Fig. 2 is the process flow diagram that data of the present invention store;
Fig. 3 is the process flow diagram of data logger of the present invention inquiry.
Embodiment
Below in conjunction with specific embodiment, the present invention is described further, but protection scope of the present invention is not limited in this:
Embodiment 1: a kind of method of creation database index and inquiry thereof, key step comprises: (1) is a reserved extended field in table, and is this extended field establishment uniqueness index.
The definition of uniqueness index is here usual database software (oracle, mysql etc.) a kind of index type of supporting, the index type of extended field is set to " uniqueness index " to ensure that the total data in index column is unique, can not repeating data be comprised.
The overall schematic of creation database indexing means of the present invention as shown in Figure 1, this invention relates to database, storage system and inquiry system three modules.
Suppose that data logger is defined as follows shown in table 1 in the database, being called for short this table is here datarecord:
| id | int(11)NULL |
| time | datetime?NOT?NULL |
| data | decimal(15,3)NOT?NULL |
| extend | bigint(20)NULL |
Table 1
Described storage system to described in database table in stored in data.
Described inquiry system is data query from table described in database.
In the database, when creating datarecord table, while structure elementary field, a reserved extended field, the type of extended field to be decimal system length be 20 long.For described extended field creates uniqueness index.
(2) when data store, be attribute assignment each in data storage object, will the property value of id and data sampling time splicing generation extended field be indicated.
Within the storage system, be attribute assignment each in data storage object, the extended field property value in described data storage object is spliced by sign id and data sampling time time to generate, and as shown in Figure 2, described splicing comprises:
1) be attribute assignment each in data storage object, id=123456 in this example; Time=" 2014-8-610:12:36 "; Data=100.5;
2) described sign id is converted to the long number that length is no more than 10, described long number is multiplied by 10 powers of 10, obtaining result is 1234560000000000;
3) described data sampling time time is converted to the number of seconds amount of its representative, the type of described number of seconds amount is the long of length 10, and obtaining result is 1407291156;
4) by step 2) result that obtains and step 3) results added that obtains is expanded the value of field extend;
5) using the value of extended field extend and the value of other fields as a data record stored in database.
In this example, suppose to have indicate id be 123456 equipment be carved with data when 2014-8-6 10:12:36 and be deposited into database, the value that so extended field is corresponding is 1234561407291156.
(3) by the property value of extended field property value and other fields in the lump stored in data logger, the property value of extended field and other fields is in a data record.
(4) during data query, query argument is indicated id and time time by with the value storing identical mode and be spliced into extended field, extended field is carried out data query as SQL parameter, obtains corresponding data record.
In described inquiry system, the parameter of user's inquiry indicates id and data sampling time time, and splice indicating id and data sampling time time the value generating extended field, as shown in Figure 3, described splicing comprises:
1) user initiates inquiry request, and required parameter indicates id and data sampling time time;
2) described sign id is converted to the long number that length is no more than 10, described long number is multiplied by 10 powers of 10, obtains result A;
3) described data sampling time is converted to the number of seconds amount of its representative, the type of described number of seconds amount is the long of length 10, obtains result B;
4) by step 2) the result A that obtains and step 3) the result B that obtains is added the value of the field extend that is expanded;
5) described extended field value is carried out data query as the parameter of SQL, obtain corresponding data record.
In this example, suppose that user wants to inquire about the equipment that the is denoted as 123456 data record in the 2014-8-6 10:12:36 moment, so after splicing, the value of extended field extend is 1234561407291156, and query SQL statement is select*from datarecord where extend=1234561407291156.
Owing to only creating index to extended field extend, under single table storage is 2,000 ten thousand record cases, database throughput still can reach 1000/s, inquires about 15000 data records and only need about 1s from described table.
Embodiment 2: a kind of method of creation database index and inquiry thereof, key step comprises: (1) is a reserved extended field in table, and is this extended field establishment uniqueness index.
The definition of uniqueness index is here usual database software (oracle, mysql etc.) a kind of index type of supporting, the index type of extended field is set to " uniqueness index " to ensure that the total data in index column is unique, can not repeating data be comprised.
The overall schematic of creation database indexing means of the present invention as shown in Figure 1, this invention relates to database, storage system and inquiry system three modules.
Suppose that data logger is defined as follows table in the database: shown in 2, being called for short this table is here sensordata:
| id | int(11)NULL |
| time | datetime?NOT?NULL |
| data1 | decimal(15,3)NOT?NULL |
| data2 | decimal(15,3)NOT?NULL |
| … | … |
| dataN | decimal(15,3)NOT?NULL |
| extend | bigint(20)NULL |
Table 2
Described storage system to described in database table in stored in data.
Described inquiry system is data query from table described in database.
In the database, when creating sensordata table, while structure elementary field, a reserved extended field, the type of extended field to be decimal system length be 20 long.For described extended field creates uniqueness index.
(2) when data store, be attribute assignment each in data storage object, will the property value of id and data sampling time splicing generation extended field be indicated.
Within the storage system, be attribute assignment each in data storage object, the extended field property value in described data storage object is spliced by sign id and data sampling time time to generate, and as shown in Figure 2, described splicing comprises:
1) be attribute assignment each in data storage object, id=10010 in this example; Time=" 2014-10-1410:51:32 "; Data1=35.1; Data2=47.63; DataN=23.0;
2) described sign id is converted to the long number that length is no more than 10, described long number is multiplied by 10 powers of 10, obtaining result is 100100000000000;
3) described data sampling time time is converted to the number of seconds amount of its representative, the type of described number of seconds amount is the long of length 10, and obtaining result is 1413255092;
4) by step 2) result that obtains and step 3) results added that obtains is expanded the value of field extend;
5) using the value of extended field extend and the value of other fields as a data record stored in database.
In this example, suppose to have indicate id be 10010 equipment be carved with data when 2014-10-1410:51:32 and be deposited into database, the value that so extended field is corresponding is 100101413255092.
(3) by the property value of extended field property value and other fields in the lump stored in data logger, the property value of extended field and other fields is in a data record.
(4) during data query, query argument is indicated id and time time by with the value storing identical mode and be spliced into extended field, extended field is carried out data query as SQL parameter, obtains corresponding data record.
In described inquiry system, the parameter of user's inquiry indicates id and data sampling time time, and splice indicating id and data sampling time time the value generating extended field, as shown in Figure 3, described splicing comprises:
1) user initiates inquiry request, and required parameter indicates id and data sampling time time;
2) described sign id is converted to the long number that length is no more than 10, described long number is multiplied by 10 powers of 10, obtains result A;
3) described data sampling time is converted to the number of seconds amount of its representative, the type of described number of seconds amount is the long of length 10, obtains result B;
4) by step 2) the result A that obtains and step 3) the result B that obtains is added the value of the field extend that is expanded;
5) described extended field value is carried out data query as the parameter of SQL, obtain corresponding data record.
In this example, suppose that user wants to inquire about the equipment that the is denoted as 10010 data record in the 2014-10-1410:51:32 moment, so after splicing, the value of extended field extend is 100101413255092, and query SQL statement is select*from sensordata where extend=100101413255092.
Owing to only creating index to extended field extend, under single table storage is 2,000 ten thousand record cases, database throughput still can reach 1000/s, inquires about 15000 data records and only need about 1s from described table.
Core concept of the present invention is under normal circumstances, indicates id and time time and uniquely can determine a data record.Therefore, be no matter the uniqueness in order to ensure data, or in order to improve efficiency data query, all need to set up the index relevant with time time with sign id.
The know-why being specific embodiments of the invention and using described in above, if the change done according to conception of the present invention, its function produced do not exceed that instructions and accompanying drawing contain yet spiritual time, must protection scope of the present invention be belonged to.
Claims (8)
1. a method for creation database index and inquiry thereof, is characterized in that comprising the steps:
(1) create data logger and reserve extended field;
(2) for extended field creates uniqueness index;
(3) be attribute assignment each in data storage object;
(4) property value of extended field in id and data sampling time splicing generation data storage object will be indicated;
(5) using the property value of extended field property value and other fields as a data record stored in database;
(6) property value of the extended field needing inquiry is obtained by sign id and data sampling time splicing;
(7) utilize extended field property value to carry out data query, obtain data record.
2. the method for a kind of creation database index according to claim 1 and inquiry thereof, is characterized in that, the type of described extended field to be decimal system length be 20 long.
3. the method for a kind of creation database index according to claim 1 and 2 and inquiry thereof, is characterized in that, described step (4) is identical with the joining method of data sampling time to indicating id with step (6).
4. the method for a kind of creation database index according to claim 1 and 2 and inquiry thereof, is characterized in that, described splicing step is as follows:
1) data sampling time is converted to the number of seconds amount of data sampling time representative, the type of number of seconds amount to be length the be long of 10;
2) sign id is converted to the long number that length is no more than 10, long number is multiplied by 10 powers of 10;
3) by step 1) and step 2) result carry out the property value being added the field that is expanded.
5. the method for a kind of creation database index according to claim 3 and inquiry thereof, is characterized in that, described splicing step is as follows:
1) data sampling time is converted to the number of seconds amount of data sampling time representative, the type of number of seconds amount to be length the be long of 10;
2) sign id is converted to the long number that length is no more than 10, long number is multiplied by 10 powers of 10;
3) by step 1) and step 2) result carry out the property value being added the field that is expanded.
6. a kind of creation database index according to claim 1 or 2 or 5 and the method for inquiry thereof, it is characterized in that, described step (7) utilizes extended field property value to carry out data query for extended field value is carried out data query as the parameter of SQL.
7. the method for a kind of creation database index according to claim 3 and inquiry thereof, is characterized in that, described step (7) utilizes extended field property value to carry out data query for extended field value is carried out data query as the parameter of SQL.
8. the method for a kind of creation database index according to claim 4 and inquiry thereof, is characterized in that, described step (7) utilizes extended field property value to carry out data query for extended field value is carried out data query as the parameter of SQL.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410583221.6A CN104268298B (en) | 2014-10-27 | 2014-10-27 | A kind of method for creating database index and its inquiry |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410583221.6A CN104268298B (en) | 2014-10-27 | 2014-10-27 | A kind of method for creating database index and its inquiry |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN104268298A true CN104268298A (en) | 2015-01-07 |
| CN104268298B CN104268298B (en) | 2018-05-04 |
Family
ID=52159819
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201410583221.6A Active CN104268298B (en) | 2014-10-27 | 2014-10-27 | A kind of method for creating database index and its inquiry |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104268298B (en) |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106874329A (en) * | 2016-07-15 | 2017-06-20 | 阿里巴巴集团控股有限公司 | The implementation method and device of database table index |
| CN106919637A (en) * | 2016-07-04 | 2017-07-04 | 阿里巴巴集团控股有限公司 | Unique index is created and uniqueness querying method and device |
| CN108763363A (en) * | 2018-05-17 | 2018-11-06 | 阿里巴巴集团控股有限公司 | A kind of method and device for examining record to be written |
| CN109558405A (en) * | 2018-11-09 | 2019-04-02 | 浙江数链科技有限公司 | Object publishing method and device, system |
| CN112612777A (en) * | 2020-12-24 | 2021-04-06 | 浙江大学 | MySQL database-based marine data management and visualization system and method |
| CN113204794A (en) * | 2021-07-02 | 2021-08-03 | 深圳市深圳通有限公司 | Transaction record safe storage method, device, equipment and storage medium |
| CN113626490A (en) * | 2020-05-08 | 2021-11-09 | 杭州海康威视数字技术股份有限公司 | Data query method, device and equipment and storage medium |
| WO2023220973A1 (en) * | 2022-05-18 | 2023-11-23 | 京东方科技集团股份有限公司 | Data processing method and apparatus, and electronic device and computer-readable storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100185629A1 (en) * | 2009-01-09 | 2010-07-22 | Microsoft Corporation | Indexing and querying data stores using concatenated terms |
| CN102110104A (en) * | 2009-12-28 | 2011-06-29 | 北大方正集团有限公司 | Method for establishing index library, search method and method for updating index library |
| CN103218364A (en) * | 2012-01-19 | 2013-07-24 | 阿里巴巴集团控股有限公司 | Searching method and system |
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
-
2014
- 2014-10-27 CN CN201410583221.6A patent/CN104268298B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100185629A1 (en) * | 2009-01-09 | 2010-07-22 | Microsoft Corporation | Indexing and querying data stores using concatenated terms |
| CN102110104A (en) * | 2009-12-28 | 2011-06-29 | 北大方正集团有限公司 | Method for establishing index library, search method and method for updating index library |
| CN103218364A (en) * | 2012-01-19 | 2013-07-24 | 阿里巴巴集团控股有限公司 | Searching method and system |
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
Cited By (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106919637A (en) * | 2016-07-04 | 2017-07-04 | 阿里巴巴集团控股有限公司 | Unique index is created and uniqueness querying method and device |
| CN106874329A (en) * | 2016-07-15 | 2017-06-20 | 阿里巴巴集团控股有限公司 | The implementation method and device of database table index |
| CN108763363A (en) * | 2018-05-17 | 2018-11-06 | 阿里巴巴集团控股有限公司 | A kind of method and device for examining record to be written |
| CN108763363B (en) * | 2018-05-17 | 2022-02-18 | 创新先进技术有限公司 | Method and device for checking record to be written |
| CN109558405A (en) * | 2018-11-09 | 2019-04-02 | 浙江数链科技有限公司 | Object publishing method and device, system |
| CN113626490A (en) * | 2020-05-08 | 2021-11-09 | 杭州海康威视数字技术股份有限公司 | Data query method, device and equipment and storage medium |
| CN113626490B (en) * | 2020-05-08 | 2023-08-25 | 杭州海康威视数字技术股份有限公司 | Data query method, device and equipment and storage medium |
| CN112612777A (en) * | 2020-12-24 | 2021-04-06 | 浙江大学 | MySQL database-based marine data management and visualization system and method |
| CN112612777B (en) * | 2020-12-24 | 2023-12-12 | 浙江大学 | Ocean data management and visualization system and method based on MySQL database |
| CN113204794A (en) * | 2021-07-02 | 2021-08-03 | 深圳市深圳通有限公司 | Transaction record safe storage method, device, equipment and storage medium |
| CN113204794B (en) * | 2021-07-02 | 2021-10-15 | 深圳市深圳通有限公司 | Transaction record safe storage method, device, equipment and storage medium |
| WO2023220973A1 (en) * | 2022-05-18 | 2023-11-23 | 京东方科技集团股份有限公司 | Data processing method and apparatus, and electronic device and computer-readable storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN104268298B (en) | 2018-05-04 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN104268298A (en) | Method for creating database index and inquiring data | |
| CN109299102B (en) | HBase secondary index system and method based on Elastcissearch | |
| JP6338817B2 (en) | Data management system and method using database middleware | |
| US10628449B2 (en) | Method and apparatus for processing database data in distributed database system | |
| CN106227800B (en) | Storage method and management system for highly-associated big data | |
| CN103106249B (en) | A kind of parallel data processing system based on Cassandra | |
| US9507875B2 (en) | Symbolic hyper-graph database | |
| CN102999526B (en) | A kind of fractionation of database relational table, querying method and system | |
| CN111382226B (en) | Database query and retrieval method and device and electronic equipment | |
| CN104182405A (en) | Method and device for connection query | |
| CN109815254B (en) | Cross-region task scheduling method and system based on big data | |
| CN104408159A (en) | Data correlating, loading and querying method and device | |
| CN105808622A (en) | File storage method and device | |
| CN106970929A (en) | Data lead-in method and device | |
| CN105096174A (en) | Transaction matching method and transaction matching system | |
| CN106471501A (en) | The method of data query, the storage method data system of data object | |
| CN103823846A (en) | Method for storing and querying big data on basis of graph theories | |
| CN102521364A (en) | Method for inquiring shortest path between two points on map | |
| CN104572862A (en) | Mass data storage access method and system | |
| CN104636368A (en) | Data retrieval method and device and server | |
| CN105069101A (en) | Distributed index construction and search method | |
| CN103092886A (en) | Achieving method, device and system for data query operation | |
| CN104376014A (en) | Structured P2P network resource publishing and querying method | |
| CN103902614A (en) | Data processing method, device and system | |
| CN114064707A (en) | Data query method and device for data virtualization server and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| CB02 | Change of applicant information | ||
| CB02 | Change of applicant information |
Address after: Yuhang District, Hangzhou City, Zhejiang Province, 311121 West No. 1500 Building 1 room 311 Applicant after: Zhong electricity Haikang Group Co.,Ltd Address before: Ma Cheng Road Hangzhou City, Zhejiang province 310012 No. 36 Applicant before: Zhong electricity Haikang Group Co.,Ltd |
|
| GR01 | Patent grant | ||
| GR01 | Patent grant |