创建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_percent>40order by avg_fragmentation_in_percent desc1.2 碎片含量小于 40%的需对其进行 Reorganize具体 SQL：USE [DB]GOALTER INDEX [INDEX_NAME] ON [Table_Name] REORGANIZE WITH ( LOB_COMPACTION = ON )GO1.3 当碎片含量大于 40%的时候需对其 Rebuild具体 SQL：USE [DB_NAME]GOALTER 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, i.name 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, i.name 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 indicates the clustered index and i.is_primary_key = 0 -- 1 indicates the primary key and (s.user_seeks + s.user_scans + s.user_lookups)=0 )a。