Imagine that dbo. MyTable Col1, Col2 ;. It is important to remember that the execution plan and performance may vary considerably depending on which index is applied however, so choosing the correct covering index is important. The clustered index on this table is on a column not referenced by the query called SalesOrderID. A big reduction can be seen in the number of logical reads compared to the first query.
Logical reads are the pages read from the data cache regardless of whether the pages had to be read from disk or not. As the second one read less pages from the data cache, that is still a good indicator that the covering index has improved performance. You can see the differences in the number of pages read from disk and this helps to show the benefit of having the covering index in place. Once the data pages are read from disk, they enter the data cache.
All queries get their results by reading the data cache. A table hint that works well today, may not work well in the future when the selectivity of the key columns change. Forcing SQL Server to use the index significantly affected performance, and not for the better!
Figure 7. Properties of the Key Lookup for the table hint. So, if Key Lookups can be detrimental to performance during query resolution for large result sets, the natural question is: how can we avoid them?
Figure 8 illustrates this updated query along with its actual execution plan. Figure 8. Reduced query to eliminate the Key Lookup.
Looking at the properties of the operation providers further evidence of the improvement. The properties are shown in Figure 9. Figure 9. Reduced query to eliminate the Key Lookup properties. The Estimated Operator Cost went down dramatically, from The observed improvement is due to the fact that the nonclustered index contained all of the required information to resolve the query.
No Key Lookups were required. Recall that if a table has a clustered index, those key columns are automatically part of the nonclustered index. So, the following query is a covering query by default. Figure Covering index using the clustered index keys. However unless your clustered index contains the required columns, which is not the case in our example, it will be insufficient for covering our query.
To increase the likelihood that a nonclustered index is a covering index, it is tempting to begin adding additional columns to the index key. Before doing so, it is important to remember that indexes must be maintained by SQL Server during data manipulation operations. Too many index hurts performance during write operations.
Additionally, the wider the index, that is to say the more bytes that make up the index keys, the more data pages it will take to store the index. Furthermore, there are some built in limitations for indexes. A covering index is a special case of an index in InnoDB where all required fields for a query are included in the index; in other words, the index itself contains the required data to execute the queries without having to execute additional reads.
Therefore, the primary key is included in the leaf node of all secondary indexes in order to establish a reference from the secondary indexes to the actual data row. So, any lookup involving a secondary index must navigate starting from root node through the branch nodes to the correct leaf node to take the primary key value, and then execute a random IO read on the primary key index once again navigating from the root node through the branch nodes to the correct leaf node to get the data row.
In this article, I'll explain the concepts related to covering indexes using real world scenarios and how we can really take advantage of them to improve the performance of our enterprise solutions. This is a sample query of one real-world scenario that I faced several months ago.
When the table is very big, a query like this can spend several seconds or even minutes to execute. If we want to optimize this query, we can create a covering index for it, so there's no need to hit the big table to fetch the rows; just fetching the data fields from the index it-self.
Function-based indexes can also cause unpleasant surprises in connection with index-only scans. Always aim to index the original data as that is often the most useful information you can put into an index.
Aggregating queries like the one shown above make good candidates for index-only scans. They query many rows but only a few columns, making a slim index sufficient for supporting an index-only scan. The more columns you query, the more columns you have to add to the indexed to support an index-only scan.
As a developer you should therefore only select the columns you really need. Regardless of the fact that indexing many rows needs a lot of space, you can also reach the limits of your database.
Most databases impose rather rigid limits on the number of columns per index and the total size of an index entry. That means you cannot index an arbitrary number of columns nor arbitrarily long columns. The following overview lists the most important limitations. Nevertheless there are indexes that cover an entire table as we see in the next section. They are—other than the key columns, which we discussed so far—only stored in the leaf nodes and can thus not be used for access predicates.
This allows you to extend an unique index with additional columns so that the index can be used for an index-only scan without changing semantics of the uniqueness. This was default up to and including MySQL 5. MyISAM indexes are limited to 16 columns and a maximum key length of bytes. This means indexing only the first few characters of a column—so it has nothing to do with the partial indexes described in Chapter 2.
If you index a column that exceeds the allowed column length , or bytes as described above , MySQL might—depending on the SQL mode and row format —truncate the column accordingly. A B-tree index is limited to 32 columns. When using Oracle 11 g with all defaults in place 8k blocks , the maximum index key length is bytes.
The PostgreSQL database supports index-only scans since release 9. The length of B-tree entries is limited to bytes hardcoded, approx.
0コメント