Four advanced features of SQL Server Indexes

Four advanced features of SQL Server Indexes

 

I. Index building filter (filtering during index creation)

Some indexes are very inefficient. For example, if the query status is in progress, 99% of orders are completed, and 1% are in progress. Therefore, we have created an index in the Order Status field, performance is improved, but it seems that 99% of the completed State data saved in the index will never be queried, which is a waste of space. It is not better if our index does not save the completed data at the time it was created. Index building filter is used to solve this problem.

Sqlserver
Supported. Syntax example:

 
Create index idx_3 on Order (Status) where status = 'running'
 

MySQL:Not Supported

ORACLE:Not Supported. You can use partitions to solve the problem.

Ii. index include column (index includes column)

We often need to create composite indexes for two reasons:
1. You can use the composite index to Improve Index filtering.
For example, the Order table has two fields: Member ID and order date,

Select * from order where member_id =? And order_date? And?
 

It is appropriate to create an index for member ID + order date.

 
Create index idx_1 on Order (member_id, order_date );
 

2. Index overwrite read
For example, to read the Order ID + status list of a member order, the SQL statement is as follows:

 
Select order_id, status from order where member_id =?
 

If our index only contains the member_id field, you need to query the order_id and status data in the table to return the result. If you create a combined index of member_id + order_id + status:

 
Create index idx_2 on Order (member_id, order_id, status );
 

 

As long as the index is accessed, the data can be returned. Although the performance is improved, the index creation cost and index space are increased because the index has more fields.

Sqlserver

In addition to composite indexes, sqlserver also supports the index include column feature. index include column is a variant of composite indexes. It can be used to specify which columns in a composite index are sortedcolumns, which columns only store the content in the index. This feature can not only meet the requirements of index overwrite reading, but also reduce the impact of indexes on DML performance. Syntax:

 
Create index idx_2 on Order (member_id) include (order_id, status );
 

The member_id field is a common index column, and the order_id and status columns are content include columns.

Example of data storage structure of common composite indexes:
 
Example of the data storage structure of the include Column Composite Index:
 

The include column method is often recommended for SQL optimization and Automatic Indexing of SQL Server Manager.

MySQL:Not supported. It can only be replaced by composite indexes.
ORACLE:Not supported. It can only be replaced by composite indexes.

3. Clustered Index)

Databases usually use two storage methods. One is a heap table, that is, the data in the table is basically unordered, just like a heap box (record) to a room (data block, as long as there is space, put it in it. When it is full, prepare a new room and try again.

The other is clustered storage, where data is stored in order by one or more fields in the table, as shown in.

 

To sort data, indexes are required to ensure efficiency. Therefore, clustered storage and clustered index storage usually mean one thing.

Sqlserver
If a table does not have a primary key, it is a heap table by default. If a primary key exists, it is stored by primary key aggregation by default. Sqlserver supports clustered storage of non-primary key indexes. This feature is very meaningful. For example, the Order table has an order ID (primary key) and a member ID, because order IDs are generally randomly accessed and a single record is returned, there is no performance improvement for querying by order ID. Assume that you need to query by member ID. If a member has many orders and returns 20 records by PAGE, 20 Discrete Data Accesses are required.
If you can store data by member ID, you can use the member ID to query discrete data only once, and the performance can be improved a lot. This method has some performance impact on order insertion, if there are not many orders inserted and query by members is frequent, it is very effective to create a clustered index by member ID for order ID clustering.
MySQL
MySQL MyISAM storage engine only supports Heap Storage and does not support clustered indexes.
MySQL InnoDB Storage engine can only aggregate by primary key. If there is no primary key, replace it with an internal hidden primary key.

Oracle
By default, hybriddb for Oracle uses Heap Storage. If an index is created and an organizational table is created, hybriddb for MySQL aggregates data based on the primary key. Oracle also has a more advanced clustering storage, called a cluster, which can define a Cluster Object, then, one or more tables are stored in the cluster in the order of fields, so that multiple tables can be stored together. This applies to some Master/Slave tables, such as order and order details, their data is stored in a data block by associated fields, and orders and order details are often queried together. Therefore, this logic only needs to read data blocks once. If it is not a cluster, it is OK to read multiple data blocks.

 

Iv. View index)

It doesn't make sense to create an index on a view, because the view itself is a logical concept and does not store physical data.

Sqlserver
Yes. To create an index on a view, you must first bind the view to the architecture. In the view, you must first create a unique clustered index to make the data persistent. After persistence, you can create other new indexes, which are processed like normal tables.

Data persistence can be achieved by indexing a view.
1. Performance Optimization of Statistics Data Query
If select sum (amount) from T2 is frequently performed, the performance is not optimized, and t table data does not change much, you can create a view (Note: The with schemabinding option must be added ):

 
Create view v2with schemabindingasselect sum (amount) as sum_amount, count_big (*) as cntfrom T2
 

Then a unique clustered index is created in this view, and the data becomes persistent.

 
Create unique clustered index idx_4 on V2 (sum_amount)
 

Then we use the noexpand method to query the V2 index view, as shown below:

 
Select sum_amount from V2 with (noexpand)
 

The performance will be very good, because there is only one row of data in the view, you can directly read it, you do not need to scan the summary from the T2 full table.

2. Automatic Multi-dimensional clustered Storage
Generally, database tables can only be designed for clustered storage in one way (only one clustered index is allowed). However, there are some queries in multiple dimensions in the actual business, such as transaction tables, query by buyer or seller. A common table can only be selected. If the performance of both dimensions is very good and difficult, sometimes only two tables can be manually divided. One table is clustered by buyer, and the other table is clustered by seller.ProgramOr the trigger maintains data consistency between the two tables, which looks awkward. After the View index is used, you can create a view on the master table (buyer dimension table), and then create a clustered index on the view with the seller dimension. If you want to query the master table by buyer, if the view is queried by seller, the index view is queried.
This performance optimization method is only a solution design that has not been verified in practice.

MySQL:Not Supported
ORACLE:Not Supported. materialized views can play a similar role and provide more data synchronization control features.

My Sina Weibo (http://weibo.com/yzsind)