ITPub博客

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

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

原创 数据库开发技术 作者:mouqj 时间:2007-08-31 07:28:21 0 删除 编辑
每个索引会使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/,如需转载,请注明出处,否则将追究法律责任。

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