利用索引的一些经验(SQLSERVER)

每个索引会使insert,update,delete执行效率降低，因此，一个表中索引数量不宜过多，4-5是极限值，当然，如果一个表属于只读表，那么可以适当的增加索引；

索引越集中越好，这样可以减少索引的大小以及减少读取索引次数；

如果创建了一个多字段索引，在安排字段的次序时，将产生最小集的字段安排在最左；

多表join时，建议创建代理integer 键，以及为这些键创建索引；

如果某个表不涉及许多insert操作，创建代理integer键(例如indentity)；

如果要对数据进行排序及分组(GROUP BY or ORDER BY)首选聚焦索引(Clustered indexes);

如果要对某个表进行数次相同的扫描，考虑建立完全索引（covering index）

考虑使用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.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11049438/viewspace-967198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11049438/viewspace-967198/