开发者社区> 潇湘隐者> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MS SQL巡检系列——检查外键字段是否缺少索引

简介: 前言感想:一时兴起,突然想写一个关于MS SQL的巡检系列方面的文章,因为我觉得这方面的知识分享是有价值,也是非常有意义的。一方面,很多经验不足的人,对于巡检有点茫然,不知道要从哪些方面巡检,另外一方面,网上关于MS SQL巡检方面的资料好像也不是特别多。
+关注继续查看

前言感想一时兴起,突然想写一个关于MS SQL的巡检系列方面的文章,因为我觉得这方面的知识分享是有价值,也是非常有意义的。一方面,很多经验不足的人,对于巡检有点茫然,不知道要从哪些方面巡检,另外一方面,网上关于MS SQL巡检方面的资料好像也不是特别多。写这个系列只是一个分享,自己的初衷是一个知识梳理、总结提炼过程,有些知识和脚本也不是原创,文章很多地方融入了自己的一些想法和见解的,不足和肤浅之处肯定也非常多,抛砖引玉,也希望大家提意见和建议、补充,指正其中的不足之处。Stay Hungry Stay Foolish!

 

MS SQL巡检系列——检查重复索引

MS SQL巡检系列——检查外键字段是否缺少索引

MS SQL巡检系列——检查数据库上一次DBCC CHECKDB的时间

 

对于绝大部分情况,外键字段都有必要建立对应的索引(注意,外键约束并不会自动建立索引),关于外键字段为什么要建立索引?下面从几个简单的例子来分析一下。我们先准备测试环境数据。

CREATE TABLE PRIMARY_TB
(
    PRODUCT_CD        VARCHAR(12)      ,
    PRODUCT_DATE    DATE             ,
    PRODUCT_DESC    VARCHAR(120)     ,
    CONSTRAINT PK_PRIMARY_TB  PRIMARY KEY CLUSTERED (PRODUCT_CD)
);
 
 
SET NOCOUNT ON;
GO
DECLARE @Index INT=1;
 
 
BEGIN TRAN
WHILE @Index <= 3000
BEGIN
 
    INSERT INTO dbo.PRIMARY_TB
    SELECT 'Prd' + CAST(@Index AS VARCHAR(4)), GETDATE() - CEILING(RAND()*200), 'production description' + CAST(@Index AS VARCHAR(4));
 
    SET @Index +=1;
END
 
COMMIT;
 
 
 
CREATE TABLE FK_TB
(
    FK_ID            INT IDENTITY(1,1),
    SALES_REGION    VARCHAR(32),
    SALES_CITY        VARCHAR(32),
    PRODUCT_CD        VARCHAR(12),
    SALIES_SUM        INT,
    CONSTRAINT PK_FK_TB PRIMARY KEY CLUSTERED (FK_ID)
)
GO
 
ALTER TABLE [dbo].[FK_TB]  WITH CHECK ADD  CONSTRAINT [FK_PRIMARY_TB_PRODUCT_CD] FOREIGN KEY([PRODUCT_CD])
REFERENCES [dbo].[PRIMARY_TB] ([PRODUCT_CD]) ON  DELETE CASCADE;
GO
 
 
SET NOCOUNT ON;
GO
DECLARE @Index INT=1;
 
BEGIN TRAN
WHILE @Index <=1000000
BEGIN
    INSERT INTO FK_TB
    SELECT 'REGION'+CAST(CEILING(RAND()*20) AS VARCHAR(2)), CAST(CEILING(RAND()*300) AS VARCHAR(3)),'Prd'+ CAST(CEILING(RAND()*3000) AS VARCHAR(8)),CEILING(RAND()*100000);
 
    SET @Index +=1;
 
END
 
COMMIT;
 
 
UPDATE STATISTICS dbo.PRIMARY_TB WITH FULLSCAN;
UPDATE STATISTICS dbo.FK_TB WITH FULLSCAN;
GO

 

1: 外键字段建立索引,在主表与子表JOIN操作时能提高性能,减少IO操作。

DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
SELECT  p.PRODUCT_CD ,
        p.PRODUCT_DATE ,
        f.SALES_REGION ,
        f.SALES_CITY ,
        f.SALIES_SUM
FROM    dbo.PRIMARY_TB p
        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD
WHERE p.PRODUCT_CD ='Prd131';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

 

如下截图所示,如果外键字段缺少索引,这两个表关联查询时,子表就会走扫描(此处测试是聚集索引扫描),如果子表非常大(例如此处案例所示),IO开销就比较大。

 

我们对外键约束字段PRODUCT_CD建立下面非聚集索引IDX_FK_TB,然后对比两者的执行计划和IO开销

CREATE INDEX IDX_FK_TB ON dbo.FK_TB(PRODUCT_CD);
 
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT  p.PRODUCT_CD ,
        p.PRODUCT_DATE ,
        f.SALES_REGION ,
        f.SALES_CITY ,
        f.SALIES_SUM
FROM    dbo.PRIMARY_TB p
        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD
WHERE p.PRODUCT_CD ='Prd131'
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

 

你会发现执行计划从原来的聚集索引扫描(Clustered Index Scan)变成了索引查找(Index Seek),IO的减少也是非常明显的。因为这里仅仅是测试数据,复杂的生产环境,性能的提升有可能比这更加明显。

 

2:如果外键约束为CASCADE(on update/delete)时,则当修改被引用行(referenced row)时,所有引用行(referencing rows )也必须修改(更新或级联删除)。外键列上的索引能减小锁的粒度和范围,从而提高效率和性能。如下所示:

 

我们先看看缺少索引的情况。

DROP INDEX IDX_FK_TB ON dbo.FK_TB;
 
 
 
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
DELETE FROM dbo.PRIMARY_TB WHERE PRODUCT_CD IN ('Prd132','Prd133')
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

CREATE INDEX IDX_FK_TB ON dbo.FK_TB(PRODUCT_CD);
 
GO
 
DBCC DROPCLEANBUFFERS;
 
GO
 
DBCC FREEPROCCACHE;
 
GO
 
SET STATISTICS IO ON;
 
SET STATISTICS TIME ON;
 
DELETE FROM dbo.PRIMARY_TB WHERE PRODUCT_CD IN ('Prd134','Prd135')
 
GO
 
SET STATISTICS IO OFF;
 
SET STATISTICS TIME OFF;

 

3:如果外键关系为NO ACTION(on update/delete)时,那么被引用的行不能被删除,因为这个操作会导致引用行变成“孤立”。删除之前,数据库会为了有效地查找、定位行,外键列上创建索引也非常有帮助。

 

上面肤浅的构造了简单案例,并对比外键约束字段拥有索引和缺少索引时,SQL的执行计划和IO的差异,那么接下来,我们进入正题,巡检的时候,我们必须通过脚本找到数据库里面外键字段没有索引的相关表和信息,并生成对应的创建索引的脚本。如下所示。

/*
One or more tables found, with foreign key constraint defined but no supporting indexes created on the foreign key columns.
SQL Server doesnt put an index on foreign key columns by default and indexing foreign key fields in referencing tables is not required. 
Foreign key columns usage must evaluated to determine whether or not indexing this column will help up increase the current
workloads performance by enhancing join performance, reducing table locking (for full table scans) while cascading updates and 
deletes, etc.
*/
 
;
WITH    FKTable
          AS ( SELECT   SCHEMA_NAME(po.schema_id) AS 'parent_schema_name' ,
                        OBJECT_NAME(fkc.parent_object_id) AS 'parent_table_name' ,
                        OBJECT_NAME(constraint_object_id) AS 'constraint_name' ,
                        SCHEMA_NAME(ro.schema_id) AS 'referenced_schema' ,
                        OBJECT_NAME(referenced_object_id) AS 'referenced_table_name' ,
                        ( SELECT    '[' + COL_NAME(k.parent_object_id,
                                                   parent_column_id) + ']' AS [data()]
                          FROM      sys.foreign_key_columns (NOLOCK) AS k
                                    INNER JOIN sys.foreign_keys  (NOLOCK) ON k.constraint_object_id = object_id
                                                              AND k.constraint_object_id = fkc.constraint_object_id
                          ORDER BY  constraint_column_id
                        FOR
                          XML PATH('')
                        ) AS 'parent_colums' ,
                        ( SELECT    '[' + COL_NAME(k.referenced_object_id,
                                                   referenced_column_id) + ']' AS [data()]
                          FROM      sys.foreign_key_columns (NOLOCK) AS k
                                    INNER JOIN sys.foreign_keys  (NOLOCK) ON k.constraint_object_id = object_id
                                                              AND k.constraint_object_id = fkc.constraint_object_id
                          ORDER BY  constraint_column_id
                        FOR
                          XML PATH('')
                        ) AS 'referenced_columns'
               FROM     sys.foreign_key_columns fkc ( NOLOCK )
                        INNER JOIN sys.objects po ( NOLOCK ) ON fkc.parent_object_id = po.object_id
                        INNER JOIN sys.objects ro ( NOLOCK ) ON fkc.referenced_object_id = ro.object_id
               WHERE    po.type = 'U'
                        AND ro.type = 'U'
               GROUP BY po.schema_id ,
                        ro.schema_id ,
                        fkc.parent_object_id ,
                        constraint_object_id ,
                        referenced_object_id
             ),
 
        /* Index Columns */
        IndexColumnsTable
          AS ( SELECT   SCHEMA_NAME(o.schema_id) AS 'schema_name' ,
                        OBJECT_NAME(o.object_id) AS TableName ,
                        ( SELECT    CASE key_ordinal
                                      WHEN 0 THEN NULL
                                      ELSE '[' + COL_NAME(k.object_id,
                                                          column_id) + ']'
                                    END AS [data()]
                          FROM      sys.index_columns (NOLOCK) AS k
                          WHERE     k.object_id = i.object_id
                                    AND k.index_id = i.index_id
                          ORDER BY  key_ordinal ,
                                    column_id
                        FOR
                          XML PATH('')
                        ) AS cols
               FROM     sys.indexes (NOLOCK) AS i
                        INNER JOIN sys.objects o ( NOLOCK ) ON i.object_id = o.object_id
                        INNER JOIN sys.index_columns ic ( NOLOCK ) ON ic.object_id = i.object_id
                                                              AND ic.index_id = i.index_id
                        INNER JOIN sys.columns c ( NOLOCK ) ON c.object_id = ic.object_id
                                                              AND c.column_id = ic.column_id
               WHERE    o.type = 'U'
                        AND i.index_id > 0
               GROUP BY o.schema_id ,
                        o.object_id ,
                        i.object_id ,
                        i.name ,
                        i.index_id ,
                        i.type
             ),
        FKWithoutIndexTable
          AS ( SELECT   fk.parent_schema_name AS SchemaName ,
                        fk.parent_table_name AS TableName ,
                        fk.referenced_schema AS ReferencedSchemaName ,
                        fk.referenced_table_name AS ReferencedTableName ,
                        fk.constraint_name AS ConstraintName ,
                        fk.referenced_columns AS Referenced_Columns ,
                        fk.parent_colums AS Parent_Columns
               FROM     FKTable fk
               WHERE    NOT EXISTS ( SELECT 1
                                     FROM   IndexColumnsTable ict
                                     WHERE  fk.parent_schema_name = ict.schema_name
                                            AND fk.parent_table_name = ict.TableName
                                            AND fk.parent_colums = LEFT(ict.cols,
                                                              LEN(fk.parent_colums)) )
             )
    SELECT  @@SERVERNAME AS InstanceName ,
            DB_NAME() AS DatabaseName ,
            SchemaName ,
            TableName ,
            Parent_Columns ,
            ReferencedSchemaName ,
            ReferencedTableName ,
            Referenced_Columns ,
            ConstraintName
    INTO    #ForeignKeyWithOutIndex
    FROM    FKWithoutIndexTable
    ORDER BY DatabaseName ,
            SchemaName ,
            TableName;
 
 
 
 
--输出临时表数据
SELECT  *
FROM    #ForeignKeyWithOutIndex;
 
 
--生成外键字段缺少的索引,请抽查、检验,确认后批量执行
SELECT  'CREATE INDEX IX_' + LTRIM(RTRIM(TableName)) + '_'
        + SUBSTRING(Parent_Columns, 2, LEN(Parent_Columns) - 2) + '  ON '
        + LTRIM(RTRIM(SchemaName)) + '.' + LTRIM(RTRIM(TableName)) + '('
        + Parent_Columns + ');'
FROM    #ForeignKeyWithOutIndex;
 
 
--删除临时表
DROP TABLE #ForeignKeyWithOutIndex;
 

在创建这些索引前最好检查、确认一下,外键字段创建索引能提高性能,但是肯定也要特殊的场景和上下文不适合,所以最好根据实际情况决定。索引创建之后,通过监控工具监控一下数据库性能、等待事件的变化。

 

参考资料:

http://stackoverflow.com/questions/3650690/should-every-sql-server-foreign-key-have-a-matching-index

http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx

http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
986 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1087 0
SQLSERVER中如何忽略索引提示
原文:SQLSERVER中如何忽略索引提示 SQLSERVER中如何忽略索引提示 当我们想让某条查询语句利用某个索引的时候,我们一般会在查询语句里加索引提示,就像这样 SELECT id,name from TB with (index(IX_xttrace_bal)) where ba...
626 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
536 0
SQL Server使用登录名连接数据库报错:18456
SQL Server使用登录名连接数据库报错:18456
0 0
【走进RDS】之SQL Server性能诊断案例分析
数据库性能诊断不仅对其数据库技能要求较高,而且需要大量的前期准备工作,如收集各种性能基线、性能指标和慢SQL日志等,尤其是面对多数据库性能调优时,往往事倍功半。
0 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Sever迁移PG经验
立即下载
时序数据库TSDB新功能 - 如何用SQL进行时序查询
立即下载
MaxCompute SQL计算成本调优以及优化方法
立即下载