5. SQL server indexing)

5.2 clustered indexes (Clustered index)(Page 122)

 

The clustered index determines the physical order of a table's data. Therefore, a table can have a clustered index. Limit 5.1 indicates the structure of a clustered index.

Fig 5.1

The structure of a clustered Index

 

The lowest level of a clustered index is determined by its data pages. the number of clustered indexes is the leaf level of the index ). The rest of the index is composed of index pages. The most recent index is the index root ). Refers to the index between the specified vertex and the root vertex, which is called intermediate-level pages ).

 

The shards of each interval in the index are connected together. Each metric on the index contains a key value and the lowest level pointing to the next index). A clustered index refers to a page number, and a file ID is added as the prefix, this is because zookeeper is unique in the same data file. A file ID and a response are added to form a response ID (page ID ).

 

So, how does the SQL Server reader use clustered indexes to retrieve data?

1. Return a single number of rows

Select * from MERs where customer_lname = 'green'

 

First, SQL Server obtains the customers root page from the system table sysindexes. In this example, as shown in Example 5.1, its root segment is 42. there may be many parameter values in the root segment. SQL server will find the parameter value that is not greater than the maximum value of green. In segment 5.1, this value is Adams, it applies to indexes, that is, the page number is 58, so index indexes with 58 values will be obtained.

 

Because 58 rows are still indexed, the SQL server will locate the value greater than green in the region, as shown in section 5.1, this value is date, and the value corresponding to this parameter points to the latest 337,337 numeric value, SQL Server will record 337 rows to get and return the green data. It should be noted that SQL server does not know whether the row existence before the data volume is obtained.

 

The number of I/O times consumed by this query is three times: the root cause is 42 times, the middle end is 58 times, and the last result is 337 times.

 

2. Return the number of rows in a specified range.

Select * from MERs

Where customer_lname between 'date' and 'kirk'

Because the clustered index sorts the dustomer_lname values by region, the data in a field must be put together. In limit 5.1, the number of items to be queried is in the three regions of 337,338,339. Therefore, three I/O operations can result in six rows of results to be queried.

 

Assume that a data volume can store the number of 15 rows in the cumstomers table. For example, for 8 I/O operations, you can set the number to 15*8 = 120 rows. If no clustered index exists, the rows may cause 120 I/O queries.

 

 

5.3 Non-clustered indexes (Non-clustered index)(Page 127)

 

Similar to clustered indexes, non-clustered indexes have root pages and leaf-level pages (clustered indexes) at the bottom ), intermediate-level page ).

 

The number of tables without clustered indexes is not connected together, even if the table has non-clustered indexes. The data volume of this table is managed through Iam APIs.

 

The physical sorting sequence of the non-clustered index on the data table does not affect the data, and the index sequence at the bottom of the index is not a data volume. Limit 5.3 indicates the structure of a non-clustered index, and its table does not have a clustered index.

 

Fig 5.3

Structure of a nonclustered index when no clustered index on the table

 

 

 

As shown in the preceding figure, each data volume in this table points to a non-clustered index, which is different from a clustered index, non-clustered index indexes contain the minimum index value pointing to each data volume. This means that non-clustered indexes are generally larger than clustered indexes, because non-clustered indexes must contain many indexes. Based on the preceding example, if a data volume is set to false, 15 rows of data in the MERs table can be saved, therefore, this non-clustered index must be saved more than 15 times that of the clustered index. In this case, non-clustered indexes have more indexes than clustered indexes.

 

Similar to clustered indexes, each condition of a non-clustered index contains a limit value, and the minimum limit value that points to the next index. This refers to an upload ID (File ID + database page number ). However, the indexes on the clustered indexes may be different. If the table in which it is located does not have a clustered index, then the row ID is used in its primary indexes ). A row ID is composed of a row ID plus a row number (a row ID is a page ID plus a row number ). For example, for example, for Ben in Ben 5.3, the shard ID is, And the row ID is 2.

 

From the 5.3 perspective, we can see that the indexes of non-clustered indexes are sorted in ascending order, but the number of indexes is not. This means that if a non-clustered index is used to obtain the parameter value, data rows are used to fetch (logical read) the required data rows for each related batch operation ).

 

 

5.4 online indexes (Index in bytes)(Page 129)

 

When you use the indexing function of SQL Server to create or recreate an index, you do not have to wait until the index is created, instead, Zookeeper uses the original structure until the index is created.