SQLServer自动重建索引的应用研究.docVIP

SQL Server自动重建索引的应用研究 蔡悦 (上海交通大学软件学院 上海 200240) 摘 要：索引是数据库用于提高数据查询速度的，设计良好的索引对于提高整个数据库的性能很有帮助。然而建好索引并不意味着一劳永逸。由于插、删、改这些操作会造成索引碎片，而索引碎片会导致查询效率大幅下降。因此，定期维护索引，是数据库管理员一项重要的职责。但是，手工重建索引效率不高，如果管理的数据库比较多，那将是非常繁重的工作。我在工作中设计出了一套自动重建索引的程序，结合SQL Server的“作业”，可以做到自动分析碎片程度，自动重建索引，在一轮重建索引过后可以自动开始第二轮分析、重建的工作。完全不需要人工干预。并且已经被证明在SQL Server 2000以上版本都能良好地运行。 关键词：索引；重建索引；分析碎片 Research of SQL Server Rebuilding Indexes Automatically Cai Yue (School of Software, Shanghai Jiao Tong University, Shanghai, 200240) 【Abstract】Indexes are important components of database which are used to improve query speeds. Well designed indexes can largely improve database performance. But it isn’t the end of the efforts. Insert, delete and update manipulations create fragments of indexes. These fragments decrease query speeds. Therefore, it is a quite important duty for DBAs to maintain indexes timely. But rebuilding indexes manually is low effective and make you exhausted if you are in charge of a lot of databases. I have designed a program of rebuilding indexes which works with SQL Server jobs. It can analyze the fragments and rebuild or defrag indexes, then start the second round automatically. The program proved to be working smoothly on SQL Server 2000 and above.【Key words】Index, rebuild index, analyze fragments 1 引 言 索引主要是提高查询的速度，设计良好的索引对于提高整个数据库的性能很有帮助。但是，即使是设计良好的索引，也会在长时间的使用中出现很多碎片，并导致性能大幅下降。这是因为一般的索引填充率不可能是100%[1]。因为聚集索引是按某个字段的顺序排列的，新插入的行未必会排在最后一页，如果这行的这个字段值比较靠前，它可能会被插入前面的页。如果前面的页没有空间的话，那就会造成所有的页都要重组一次，这会大大地影响性能。所以，一般索引默认的填充率是80%，也就是留下20%的空间来应付这种插入。当然，留出空间也不是没有代价的，这会造成在一段时间的插、删、改操作后，索引的碎片越来越多，索引的访问效率越来越低[2]。 既然是这样，索引就需要经常性地维护。索引的维护有2种方式，索引重组，或索引重建。索引重组的时候，该表仍然可以访问，但索引重组的速度慢，效果也不如重建。索引重建会锁表[3]，因此只能放到深夜的维护时间窗口进行。这也决定了索引重建的工作必须寻找一种自动的方式。 由于重建索引程序完全是SQL语句写成，然后生成3 个存储过程，结合SQL Server 代理本身的“作业”功能，可以定时运行这些存储过程，另外还需要新建两张表。这些工作并不涉及 .net开发。因此经过技术部讨论决定，这个项目由我一个人承担开发、测试工作。 2重建索引程序的设计原理 如图2-1所示，重建索引的程序涉及到3个存储过程和2张表。Rebuildindex是重建索引过程中所用到的表，它记录了索引的行数、碎片信息、完成情况，开始和结束时间等等。因此，该索引是否需要重建，采用哪种方式重建，依据的都是这张表中的信息。在