创建INDEX标准化流程.docx

创建INDEX标准化流程检查创建 INDEX的先决条件-1检查创建INDEX的执行计划-1.1检查无效 INDEX-1.2控制INDEX数量-1.3创建INDEX-2命名规则-2.1检查序列-2.2指定文件组-2.3维护INDEX-3定时检查INDEX Fragmentation-3.1定时清理无效INDEX-3.2一、 创建INDEX的先决条件INDEX的设计是数据库设计中比较重要的一个环节，对数据库的性能至关重要，然而并不是所有的INDEX都是好的，很多情况由于创建不合适的INDEX，最后使得其效果适得其反，因此在创建INDEX之前需检查几个重要的步骤：1. 所有的INDEX都是基于其查询条件而建立，因此在创建INDEX之前需检查相关程序中具体的执行计划才可以根据实际情况建立相应的Index2. 检查是否有无效INDEX，即重复INDEX3. 保持一个表中最多控制在8个INDEX，如果超过其数量需检查重复INDEX二、 创建INDEX1. 创建INDEX的命名规则INDEX的命名分为前缀、标识、后缀前缀除了PK和FK外，应以IX为前缀，标识部分为中间段，为了显示INDEX名字在系统表中容易区分标识部分都以表名为中间部分，而后缀则可以用创建INDEX的列名为后缀名，如多个列所组成的组合索引，可选择第一个列名为后缀例：INDEX_NAME=IX_MES_WIPTRANSACTION_TXNSEQUENCEIX为前缀MES_WIPTRANSACTION为标识部分（其表名）TXNSEQUENCE为后缀（第一个列名）2. 检查排序在创建INDEX的同时，需检查列的排序规则，如果是按照升序排列则在列后面为ASC，如果是降序排列则在列后面为DESC3. 指定文件组文件组决定了INDEX在物理存储上的位置所在，制定相应的文件组可以有效的避免INDEX的增量不影响DB的数据增量三、 维护INDEX当INDEX创建完以后，需定期的对其进行维护，确保INDEX的可用性1.1 平均所有表中的INDEX一个月检查一次，主要检查INDEX的碎片量及扫描密度检查SQL：SELECT object_name(a.object_id) TableName,a.index_id,name IndexName,avg_fragmentation_in_percent,ALTER INDEX +name+ ON +object_name(a.object_id)+ REBUILD FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS aJOIN sys.indexes AS bON a.object_id = b.object_id AND a.index_id = b.index_id where name is not null and avg_fragmentation_in_percent40order by avg_fragmentation_in_percent desc1.2 碎片含量小于40%的需对其进行Reorganize具体SQL：USE DBGOALTER INDEX INDEX_NAME ON Table_Name REORGANIZE WITH ( LOB_COMPACTION = ON )GO1.3 当碎片含量大于40%的时候需对其Rebuild具体SQL：USE DB_NAMEGOALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )GO2.清理无效INDEXSQL如下：select tablename,indexname,drop index +tablename+.+indexname as dropIndexCommand from ( select object_name(i.object_id) as tablename, as indexname from sys.indexes i left outer join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id and s.database_id = db_id() where objectproperty(i.object_id, IsUserTable) = 1 and objectproperty(i.object_id,IsMSShipped)=0 and i.index_id 1 - 0 indicates the heap 1 indicates the clustered index and i.is_primary_key = 0 - 1 indicates the primary key and s.object_id is null union all select object_name(i.object_id) as tablename, as indexname from sys.indexes i inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id and s.database_id = db_id() where objectproperty(i.object_id, IsUserTable) = 1 and objectproperty(i.object_id,IsMSShipped)=0 and i.index_id 1 - 0 indicates the heap 1 indi