利用索引的一些經驗(SQLSERVER)

每個索引會使insert,update,delete執行效率降低，因此，一個表中索引數量不宜過多，4-5是極限值，當然，如果一個表屬於只讀表，那麼可以適當的增加索引；

索引越集中越好，這樣可以減少索引的大小以及減少讀取索引次數；

如果建立了一個多欄位索引，在安排欄位的次序時，將產生最小集的欄位安排在最左；

多表join時，建議建立代理integer 鍵，以及為這些鍵建立索引；

如果某個表不涉及許多insert操作，建立代理integer鍵(例如indentity)；

如果要對資料進行排序及分組(GROUP BY or ORDER BY)首選聚焦索引(Clustered indexes);

如果要對某個表進行數次相同的掃描，考慮建立完全索引（）

考慮使用SQLSERVER的索引最佳化嚮導：時間探查器－》工具－》索引最佳化嚮導

可以用sp_Msforeachtable對索引進行重建，語法：sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

附錄：

covering index
· if you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.
On the other hand, if the covering index gets too big (has too many columns), this can increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:
· If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
· The covering index should not add significantly to the size of the key. If it does, then it its use may not outweigh the benefits it provides.
· The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.
One clue to whether or not a query can be helped by a covering index is if the execution plan of the query uses a Bookmark Lookup. If it does, then adding a covering index is almost always beneficial.