Index Building Filter (filter when the index is created)
There are some very inefficient index, such as regular query the status of ongoing orders, order 99% of the state is complete, one percent is in progress, so we built an index on the order status field, performance is improved, but the feeling in the index holds 99% of the completion status is never queried, it is a waste of space. If we do not build the index in time to save the data completion status, it is not better. Index Building Filter is used to solve this problem.
SQL Server: Support, syntax examples:
create index idx_3 on order(status) where status=’running’;
MySQL: not supported
Oracle: does not support, consider using partition resolution
Index Include Column (index contains column)
We often need to build some composite index, there are two reasons:
- Adoption of the composite index can improve index filtration degree
- Index covering read
For example, the Orders table has a membership ID and order date two fields, if we regularly according to members and order time queries
Select * from order where member_id=? and order_date between ? and ?;
That establishment Member ID + index order date is very appropriate.
create index idx_1 on order(member_id,order_date);
For example, we need to read a Member Order Order ID + Status List, SQL as follows:
select order_id,status from order where member_id=?;
If our index only member_id field, you also need to return to the table query order_id and status data to return results if building a member_id + order_id + status of the composite index:
create index idx_2 on order(member_id,order_id,status);
That as long as you can access the index return data, and that although performance improved, but more because the index field, thus increasing the cost of indexing and index space.
SQLServer addition to supporting composite index, it also supports Index Include Column feature, Index Include Column is a variant of the composite index, which is characterized by a combination of the index can specify which columns are sorted column, which column is only the content stored in the index, this feature not only meet the index covers read, and can reduce the impact of DML performance index. Syntax such as:
create index idx_2 on order(member_id) include(order_id,status);
Member_id field where ordinary index column, order_id and the status column is content include columns.
Ordinary composite index data storage structure Example:
SQLServer SQL optimization Manager automatically indexes often recommended way to see the recommendation Include Column.
MySQL: no support, only to be replaced by a composite index
Oracle: does not support, can only be replaced by a composite index
Databases are usually stored in two ways, one is the heap table, that data in the table is the basic disorder, like go to a room (block) stack boxes (records), as long as there is space to fill it up, filled the ready room and put a new.
Another is the aggregate storage, data sorted in one or several fields in the table memory.
Due to the sort needed to ensure the efficiency of the index, so the clustered index clustered storage and storage usually refers to a meaning.
If the table has no primary key defaults to the heap table, if there is a primary key by default by the primary key clustered storage. SQLServer support non-primary key index clustered storage, this feature is very meaningful, such as the Orders table have an order ID (primary key) and Member ID, if you press the order ID clustered storage, because the order ID is generally random access, return a single record, so the press Order ID query is no performance improvement. Suppose basis having members ID inquiry, a member of many orders, a return to 20 pages, it would take 20 times the discrete data access.
If you can gather Member ID memory, that with membership ID query may only need 1 discrete data can be accessed, performance can improve a lot, is inserted in this way have some performance impact on orders, if the order is to insert small, frequent inquiries by members it was built by members of the clustered index ID with the order ID to gather good effect.
MySQL MYISAM storage engine only supports heap memory, does not support the clustered index.
MySQL INNODB storage engine can only be gathered by the primary key, if not the primary key on the inside with a hidden primary key instead.
Oracle default heap memory, if built index-organized table primary key clustered storage press. Oracle there is one kind of more advanced aggregation storage concept called cluster (Cluster), you can define a cluster object, and then one or more tables gathered by field order stored in the cluster in order to achieve multiple tables clustered storage, applies to some master from the table, such as orders and order details, their data is gathered by the associated field is stored in a data block, orders and Order Details frequent queries together, so this logic with just one data block can be read, If you use a non-Cluster, a plurality of data blocks that need to read it OK.
Built on view index, feeling no sense, because the view itself is a logical concept, not a physical data storage, how to index said.
First view on view indexing needs to bind architecture. We need to build on the view that a unique clustered index, the data persistence, persistence can also build after other new index, the same as an ordinary table deal.
On the View indexing allows data persistence, there are two purposes
- The Statistical class data query performance optimization
- Automatic multi-dimensional aggregates storage
As often do select sum (amount) from t2 such operations, poor performance optimization, and t is not much change table data, you can build a view (Note: A plus with schemabinding option):
CREATE VIEW V2 with schemabinding AS SELECT SUM(amount) as sum_amount,COUNT_BIG (*) as cnt FROM t2;
Then build a unique clustered index on this view, the data can be persisted.
CREATE UNIQUE CLUSTERED INDEX idx_4 ON V2 (sum_amount);
Then we noexpand way indexed views query v2, as follows:
SELECT sum_amount FROM v2 WITH(NOEXPAND);
Performance will be very good, because the view there is only one line of data, it can be read directly, do not need a full table scan summary from t2
Table database design generally only one way to press gathering storage (allowing only one clustered index), but there are multiple dimensions of some queries in real business, such as transaction table, the dimensions required by the buyer inquiries also need to press seller dimensional queries. Ordinary table can only choose one, if you want Two dimensions of the performance is very good very difficult, and sometimes only a human into two tables, a table by the buyers gather, gather a table by the seller, or triggered by the program maintains the consistency of two tables of data, so it looks very awkward. With a view index can build a view of the main table (buyer dimension table), then build a clustered index on the view dimension with the seller, buyer, if you want to query after query the primary table, if the query by the seller only query the index view.
This performance optimization is just one aspect of the design, the practice has not been verified.
MySQL: not supported
Oracle: does not support materialized views can play a similar role, and provide more data synchronization control features.