首页 > 数据库 > 数据库开发技术 > 利用索引的一些经验(SQLSERVER)


原创 数据库开发技术 作者:mouqj 时间:2007-08-31 07:28:21 0 删除 编辑



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


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

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


可以用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.


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
  • 博文量
  • 访问量