当前位置：Gxlcms > 数据库问题 > 【SQLSERVER】数据库索引维护/优化

【SQLSERVER】数据库索引维护/优化

时间：2021-07-01 10:21:17 帮助过：7人阅读

最近到新公司后，做了些数据库索引优化和维护上的工作，趁着今天有空，写个博客与大家分享下，其实一些源码也是网上拷贝的，只不过是做了些改进，主要想分享的是一个优化的思路。

一、索引的利弊

优点： 1.大大加快数据的检索速度；

2.创建唯一性索引，保证数据库表中每一行数据的唯一性；

3.加速表和表之间的连接；

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点： 1.索引需要占物理空间；

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度；

3.索引过多，生成执行计划的时候，也可能导致优化器需要更多的时间去选择一个合适的索引，消耗更多的CPU时间。

二、索引的优化方法：

1 索引缺失-------增加索引----新增

2 索引过多-------合并索引----删除合并

3 索引碎片-------索引维护----重建、重组索引

TIP: 索引碎片会降低索引页的数据密度，在查询时造成额外的I/O。

4 更新统计信息

三、索引的优化思路

通过作业调用存储过程，定期、自动地完成索引的优化维护，并记录日志；

索引的整合，不合理索引的删除合并主要还是依赖人工判断、取舍，暂时没想到比较好的自动化处理的办法。

四、相关的存储过程源码

1、索引缺失，根据系统视图，自动创建可能带来最大性能提升的10条索引，并记录日志：

建表 DBA_MissingIndexCreateRecord

IF OBJECT_ID(‘DBA_MissingIndexCreateRecord‘) IS NOT NULL DROP TABLE DBA_MissingIndexCreateRecord
CREATE TABLE [dbo].[DBA_MissingIndexCreateRecord](
    [Datekey] [INT] NULL,
    [PossibleImprovement] [FLOAT] NULL,
    [Last_User_Seek] [DATETIME] NULL,
    [Last_User_Scan] [DATETIME] NULL,
    [DBName] [NVARCHAR](128) NULL,
    [TableName] [NVARCHAR](128) NULL,
    [IndexName] [NVARCHAR](4000) NULL,
    [CreateSql] [NVARCHAR](4000) NULL,
    [Status] [INT] NOT NULL
) 

GO

ALTER TABLE [dbo].[DBA_MissingIndexCreateRecord] ADD  DEFAULT ((0)) FOR [Status]
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘日期‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘Datekey‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘可能的性能提高‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘PossibleImprovement‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘由可能使用了组中建议索引的用户查询所导致的上次查找日期和时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘Last_User_Seek‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘由可能使用了组中建议索引的用户查询所导致的上次扫描日期和时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘Last_User_Scan‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘数据库名称‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘DBName‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘表名‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘TableName‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘索引名称‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘IndexName‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘执行脚本‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘CreateSql‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘状态 0未执行 1已执行‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘Status‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘缺失索引创建记录表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_MissingIndexCreateRecord‘
GO

View Code

过程 DBAMissingIndexCreate

CREATE PROCEDURE [dbo].[DBAMissingIndexCreate] @Result INT = 0 OUTPUT        ----返回结果
AS /*
创建者：TurboWay
申请人：TurboWay
所属模板：数据库维护
所属项目：数据库索引维护
过程说明：top10缺失索引创建并记录到 DBA_DBA_MissingIndexCreateRecord
创建时间：2017-06-27
使用索引：
返回字段信息：
@Result 返回结果 0成功,-2失败
*/
    BEGIN
        SET NOCOUNT ON; 
        DECLARE @undo INT = 1;
        DECLARE @CreateSql NVARCHAR(MAX);
        DECLARE @UUID BIGINT;
        SET @UUID = CONVERT(BIGINT,(((((((YEAR(GETDATE())-1990)*12+MONTH(GETDATE()))*31+DAY(GETDATE()))*24+ 
                    CONVERT(VARCHAR(2),DATEPART(HOUR,GETDATE())))*60+CONVERT(VARCHAR(2),DATEPART(MINUTE,GETDATE())))*60+ 
                    CONVERT(VARCHAR(2),DATEPART(SECOND,GETDATE())))))*1000000+ CAST(CEILING(RAND() * 999999) AS BIGINT)
        
        BEGIN TRY
/*记录TOP10缺失索引*/
        INSERT INTO DBA_MissingIndexCreateRecord(Datekey,PossibleImprovement,Last_User_Seek,Last_User_Scan,DBName,TableName,IndexName,CreateSql)
        SELECT a.Datekey,
               a.PossibleImprovement,
               a.last_user_seek,
               a.last_user_scan,
               a.DbName,
               a.TableName,
               CASE WHEN LEN(a.Index_Name) >= 128 THEN  SUBSTRING(a.Index_Name,0,CHARINDEX( ‘_i‘,a.Index_Name))+‘_IncloudeTooMany‘+CAST((@UUID+ROW_NUMBER()OVER(ORDER BY PossibleImprovement DESC)) AS NVARCHAR(30)) ELSE a.Index_Name END AS NewIndexName, --索引名有长度限制
               CASE WHEN LEN(a.Index_Name) >= 128 THEN  REPLACE(CreateSql,a.Index_Name,SUBSTRING(a.Index_Name,0,CHARINDEX( ‘_i‘,a.Index_Name))+‘_IncloudeTooMany‘+CAST((@UUID+ROW_NUMBER()OVER(ORDER BY PossibleImprovement DESC)) AS NVARCHAR(30))) ELSE CreateSql END AS NewCreateSql
        FROM (
        SELECT  TOP 10
                CONVERT(INT,CONVERT(NVARCHAR(8),GETDATE(),112)) AS Datekey,
                CAST(avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks )AS NUMERIC(18,2)) AS PossibleImprovement ,
                last_user_seek ,
                last_user_scan ,
                DB_NAME() AS DbName ,
                OBJECT_NAME(D.object_id) AS TableName ,
                REPLACE(REPLACE(ISNULL(‘x‘ + REPLACE(equality_columns, ‘, ‘, ‘_x‘), ‘‘)                 
                                + CASE WHEN equality_columns IS NOT NULL
                                            AND inequality_columns IS NOT NULL
                                       THEN ‘_‘
                                       ELSE ‘‘
                                  END + ISNULL(‘x‘ + REPLACE(inequality_columns, ‘, ‘,
                                                             ‘_x‘), ‘‘)
                                + CASE WHEN included_columns IS NOT NULL
                                            AND included_columns IS NOT NULL THEN ‘_‘
                                       ELSE ‘‘
                                  END + ISNULL(‘i‘ + REPLACE(included_columns, ‘, ‘,
                                                             ‘_i‘), ‘‘), ‘[‘, ‘‘), ‘]‘,
                        ‘‘) AS Index_Name ,
                ‘CREATE INDEX [‘ + REPLACE(REPLACE(ISNULL(‘x‘
                                                          + REPLACE(equality_columns,
                                                                    ‘, ‘, ‘_x‘), ‘‘)
                                                   + CASE WHEN equality_columns IS NOT NULL
                                                               AND inequality_columns IS NOT NULL
                                                          THEN ‘_‘
                                                          ELSE ‘‘
                                                     END + ISNULL(‘x‘
                                                                  + REPLACE(inequality_columns,
                                                                      ‘, ‘, ‘_x‘), ‘‘)
                                                   + CASE WHEN included_columns IS NOT NULL
                                                               AND included_columns IS NOT NULL
                                                          THEN ‘_‘
                                                          ELSE ‘‘
                                                     END + ISNULL(‘i‘
                                                                  + REPLACE(included_columns,
                                                                      ‘, ‘, ‘_i‘), ‘‘),
                                                   ‘[‘, ‘‘), ‘]‘, ‘‘) + ‘]‘ + ‘ ON ‘
                + [statement] + ‘ (‘ + ISNULL(equality_columns, ‘‘)
                + CASE WHEN equality_columns IS NOT NULL
                            AND inequality_columns IS NOT NULL THEN ‘,‘
                       ELSE ‘‘
                  END + ISNULL(inequality_columns, ‘‘) + ‘)‘ + ISNULL(‘ INCLUDE (‘
                                                                      + included_columns
                                                                      + ‘)‘, ‘‘) AS CreateSql
        FROM    sys.dm_db_missing_index_groups AS G
                INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
                INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
        WHERE   D.database_id = DB_ID()
        ORDER BY PossibleImprovement DESC  
        ) a

/*创建索引，执行脚本*/
            WHILE @undo <> 0
                BEGIN
                    SET @CreateSql = ( SELECT TOP 1
                                                CreateSql
                                       FROM     dbo.DBA_MissingIndexCreateRecord
                                       WHERE    Status = 0
                                                AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112))
                                     );
                    EXEC sp_executesql @CreateSql;
                    UPDATE  dbo.DBA_MissingIndexCreateRecord
                    SET     Status = 1
                    WHERE   Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112))
                            AND Status = 0
                            AND CreateSql = @CreateSql;
                    SET @undo = ( SELECT    COUNT(1)
                                  FROM      dbo.DBA_MissingIndexCreateRecord
                                  WHERE     Status = 0
                                            AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112))
                                )
                END

        END TRY
        BEGIN CATCH
            SET @Result = -2;
            PRINT ‘执行失败的语句是：‘‘‘ + @CreateSql + ‘‘‘ 错误信息: ‘ + ERROR_MESSAGE();
        END CATCH
     
        SELECT  @Result AS Result; 

        SET NOCOUNT OFF
    END

View Code

2、索引过多，将所有的索引及使用情况记录到表，再人为分析判断，删除合并：

建表 DBA_IndexMergeRecord

IF OBJECT_ID(‘DBA_IndexMergeRecord‘) IS NOT NULL DROP TABLE DBA_IndexMergeRecord
CREATE TABLE [dbo].[DBA_IndexMergeRecord](
    [DbName] [NVARCHAR](255) NULL,
    [TableName] [NVARCHAR](255) NULL,
    [IndexName] [NVARCHAR](255) NULL,
    [IndexType] [NVARCHAR](60) NULL,
    [IsUnique] INT NULL,
    [Key_Cols] [NVARCHAR](MAX) NULL,
    [Included_Cols] [NVARCHAR](MAX) NULL,
    [StartDate] [DATETIME] NULL,
    [IsDrop] INT DEFAULT 0 ,
    [IsAuto] INT DEFAULT 0 ,
    [DropDate]  AS (CASE WHEN IsDrop = 1 THEN GETDATE() ELSE ‘1900-01-01 00:00:00.000‘ END ),
    [DropSql] [NVARCHAR](MAX) NULL,
    [USER_SEEKS] BIGINT NULL ,
    [USER_SCANS] BIGINT NULL ,
    [USER_LOOKUPS] BIGINT NULL ,
    [USER_UPDATES] BIGINT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘数据库名‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_IndexMergeRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘DbName‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘表名‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBA_IndexMergeRecord‘, @level2type=N‘COLUMN‘,@level2name=N‘TableName‘
GO

EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘索引名

                  

	 	
                    
                        
                            
                                < 上一篇
mysql 处理中文字符编码乱码
                            
                            
                                 下一篇 >
dbgrideh标题排序

人气教程排行

229次 1 oracle 用户解锁和修改用户密码

229次 2 解决：安装SQL Server 2008 Native Client遇到错误（在Navicat premium新建sqlserver连接时需要）：An error occurred during ...HRESULT: 0x80070422（注意尾部的错误号）

229次 3 rocksdb 编译步骤

229次 4 adb命令开关蓝牙及NFC

229次 5 解决数据库连接错误您在wp-config.php文件中提供的数据库用户名和密码可能不正确，或者无法连接到localhost上的数据库服务器，这意味着您的主机数据库服务器已停止工作。

229次 6 使用java自动填充，实现mysql的创建修改时间的自动填充

229次 7 Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb

228次 8 关于mysql创建数据库中字符集和排序规则的选择

228次 9 Linux设置Mysql开机自启动服务

227次 10 SQL2008：WITH MOVE 子句可用于重新定位一个或多个文件

227次 11 图数据库Neo4j在GIS系统的应用

227次 12 SpringBoot配置 druid 数据源配置慢SQL记录

227次 13 SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

227次 14 docker-compose启动MySQL并配置远程登录

225次 15 Mysql安装（for mac）

225次 16 kettle将图片转换至二进制存储至数据库

225次 17 adb的安装及配置

224次 18 使用PHP控制MODBUS-RTU设备

224次 19 MySQL 查询时间差值大于某一个值的记录

224次 20 MongoDB内存配置 --wiredTigerCacheSizeGB