Shulou(Shulou.com)06/03 Report--

In the relational database, the data in the table is generally stored on disk in an unordered state. When there is no corresponding index, if you want to query the data in the table, you can only retrieve the whole table, read all the records one by one, and then compare them with the query conditions. Obviously, this approach will lead to a large number of disk Ithanso operations and CPU calculations, consuming a lot of system time, so Indexing becomes an option that must be considered.

Use CREATE INDEX [index name] on table name (column name, …) Statement can establish the most commonly used key-value index for the data in the table, and the key-value index is mostly implemented by B + tree data structure, which has the following properties:

1. It is a balanced tree, that is, the depth difference from the root node to the leaf node is no more than 1.

2. Non-leaf nodes only save key values and pointers to child nodes, but do not save data

3. The leaf node stores the key value, the address of the corresponding record and the linked list pointer of the leaf node. The leaf node in the linked list has ordered key values.

But can these properties ensure that the query performance meets the needs of users? Next, we take the time-period query of bank accounts as an example to explore the performance of the index.

To facilitate illustration, we simplify the B+ tree here to the B+ tree shown in the following figure, with account number and transaction date as key values, as shown in the following figure:

If we want to query the transaction pipeline of account A002 from 2000-01-01 to 2000-01-07, the database system will first look for the leaf node where the key value of account A002 and the date is not earlier than 2000-01-01. The result is to read index blocks A, B, C in turn, then find out the record address corresponding to the key values that meet the conditions in index block C, and read out the record return. If the last date in index block C is earlier than or equal to 2001-01-07, index column D can be read directly according to the linked list of leaf nodes, and so on, until a date of an index block is larger than 2001-01-07.

Observing the above process, we find that the records corresponding to 2000-01-01 and 2000-01-01 in data page 1, the corresponding records in data page 2, and the records corresponding to 4 records in data page 3 need to read 3 data pages. in extreme cases, even any record is on different data pages, and if the records in the data area have been stored in order of key values, the disk IO can be significantly reduced. Furthermore, if the recorded data is saved directly in the leaf node, the skip between the index page and the data page can be reduced, which has a great impact on the performance of the mechanical hard disk.

These problems can be easily solved for the group tables of the aggregator.

Let's take stock trading data as an example to explain the use of group tables.

A1=file ("d:/test/stktrade.ctx") 2=A1.create@r (# sid,#tdate,open,close,volume) 3=connect ("mysql") 4=A3.cursor ("select * from stktrade order by sid,tdate") 5=A2.append (A4) 6=A3.close () 7=A2.index (idx1;sid,tdate)

A2: create a group table with a data structure of (sid,tdate,open,close,volume), and specify sid and tdate as keys, and @ r specify data to be stored by row

A5: append data ordered by sid and tdate to the group table

A6: index idx1 with sid and tdate as key values

A1=file ("d:/test/stktrade.ctx") .create () 2=A1.icursor (sid== "600036" & & tdate > = date ("2018-01-01") & & tdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.