Understanding the indexes in the Sybase ASE database

I. Basic Concepts 

Sybase ASE mainly uses B-tree indexes. However, due to actual application and performance considerations, its indexes are further subdivided. Tree B is also a general term.

The Cluster Index and non-cluster index can be divided into two types: Cluster Index and non-cluster index ). 

Clustered index: the data storage order on each data page must be exactly the same as that on the key-value storage order. On each data page, the data page can be linked in key-Value Order. Index pages at different levels are actually linked in the order of key values.Based on this requirement, a single table can have at most one clustered index.

Non-clustered index: the data storage sequence has nothing to do with the key-value sequence. The association between index pages and data pages is Row-level. That is, the key value of each item in the page subnode of the index corresponds to the key value of the row, and the row address corresponding to the key value is stored. From this perspective, it requires more level-1 index access than clustering indexes. Recall that it is similar to the B-tree index.

 

From the Locking Scheme, the tables in the ASE can be divided into the APL lock table and the Dol lock table.

APL lock table: when the index page is locked, the corresponding data page is also locked. The address chain on the data page appears in a two-way linked list.

The Cluster Index of the APL lock table is as follows:

(Figure 1: Cluster Index of the APL lock table)

The non-clustered index 2 of the APL lock table is shown below:

 

Figure 2: Non-clustered index of the APL lock table

DOL lock table: Only data pages or data rows must be locked. It only has a two-way linked list on the index page. This is not available on the data page. It uses the row ID to store the actual row Address [Note: the row is composed of (page number, row number)]. 

In my personal understanding, the so-called clustered index is essentially a B + tree (see the data structure for details ). The leaf node of the B + tree must be the data itself, and the leaf nodes are connected to each other in an orderly manner. However, for the clustered index of the Dol lock table, there is a slight change, and its leaf node is only the key value of the data, it has an additional row address (data page number and row number) pointing to the actual data row ). The physical storage method with the Dol lock table is weird, and there is no two-way linked list.

Example of the Dol lock table clustered index, as shown in 3:

 

Figure 3: DOL lock table clustered Index

 

The Dol lock table's non-clustered index 4 is shown in:

Figure 4: Non-clustered index of the Dol lock table

 

Another concept is heap table: it refers to a table without a clustered index. when inserting data, it is directly inserted to the last page until the page is full and a new page is allocated, to the last page.

 

Ii. Impact of data update on Indexes

1. Insert data

For APL tables:

For a clustered index, it will adjust the rows on the data page or index page according to the index order. Multiple page splits may be involved in the middle; if it is just a normal heap table, a new row will be placed at the last position of the last page (note that this insert operation will lock the page, what are the effects of locking multiple concurrent operations on this page? conflicts may easily occur, reducing efficiency. At the same time, only one transaction can be inserted)

For Dol tables:

Because there is no location index, it can add a new row to the specified insert page. That is, first find the page number of the inserted page, and then put it in. As long as the data is not on the same data page, the insertion efficiency should be high.

2. delete data:

APL table:

When the last row of the data page or leaf page is deleted, the page is also deleted from the page chain and marked as "available" on the allocation page ". At the same time, when the row (index row and record row) on the page is deleted, the row after the row is increased in turn, to ensure that the remaining space is always at the end of the page. In short, there may be many adjustment actions for one delete operation.

DOL table:

Only logically, rows are marked as deleted, but they are not physically deleted (a bit similar to the recycle bin space. As time goes forward, there will be a lot of space, the reorg command must be used for reorganization.) If there is enough space, a table with a location index will add a new row to a suitable page. If there is not enough space, you have to put it on the nearest available page.

 

Iii. Selection of indexes:

Concurrent Data insertion, update, and deletion may use the Dol lock, resulting in fewer conflicts than the APL lock.

However, for queries using range queries or sorting, clustering indexes are much better than non-clustering indexes. The APL clustered Index seems to have less index-level access than the Dol clustered index.