外键减速删除 [英] Foreign Keys Slowing down Delete

问题描述

我有一个表X，它有一个自动递增的ID列作为主键。我有其他的表A，B，C和D，它们补充了表X中的信息。每个表都必须包含一个引用表X中的ID的列。我已经完成了这个工作，并且在我的代码中（Java）将每个条目的ID返回到表X并在插入到其他表时使用它的方式。一切正常。

现在，我建议将表A，B，C和D上的ID列指定为FOREIGN KEYS，因为这是正确的做法。我做到了。 现在从表X中删除行需要花费大量的时间来完成。插入到其他表格也需要更长的时间。

请不要误解我的意思，我知道为什么外键与指定DB上表的关系有关。但是，这似乎只是礼仪而非实际的相关，尤其是当我的交易变得越来越慢的时候。

问题：

是否值得失去一些表现，以保持关系正式指定，即使它不是必要的？

2。有什么办法可以加快我的交易，并保持FOREIGN KEY的规格。
$ b 谢谢。

REPLIES

以下是表格的创建方式。

CREATE TABLE SQL：

  CREATE TABLE [dbo]。[ MainTableX]（
 [col1] [smalldatetime]，
 [col2] [varchar]（20），
 [ID] [int] IDENTITY（1,1）NOT NULL，
 CONSTRAINT [PK_MainTableX] PRIMARY KEY CLUSTERED 
（
 [ID] ASC 
）
）
 GO 
 
 
 CREATE TABLE [dbo]。[RelatedTableA]（
 [differentID] [varchar]（50），
 [detail1] [varchar]（40），
 [detail2] [varchar]（40）， 
 CONSTRAINT [PK_RelatedTableA] PRIMARY KEY CLUSTERED 
（
 [differentID] ASC 
）
 GO 
 
  - 表B，CD很漂亮非常类似于表格A 
  

添加外键SQL：

  ALTER TABLE RelatedTableA ADD ID INT 
 CONSTRAINT fk_refTableX_A FOREIGN KEY（ID）
 REFERENCES MainTableX（ID）
 GO 
  - 同样的事情与其他相关表
  

解决方案

我做了Foreign键列索引。

 在RelatedTableA（ID）上创建非聚集索引IX_RelatedTableA 
 
 GO 
  

解决方案

如果FOREIGN KEY列被正确索引（这应该与声明自动发生，我相信），那么你应该看到在最坏的情况下一个小的性能影响。请列出每个表的CREATE TABLE SQL，因为这听起来有点不对劲。

SQL Server不会自动在FK列上创建索引（b）使用FOREIGN KEY关系的目的不是为了正式声明任何东西（或者不是只要就可以做到，无论如何）。相反，它为数据库引擎提供了足够的信息来执行数据的完整性。这意味着您不能错误地让您的应用程序添加或删除记录，以至于违反了关系。另外，使用数据库的其他应用程序（例如Management Studio）也不能这样做。正是由于这个原因 - 保证数据库引擎执行规则 - 声明约束是非常重要的。

I have a table X which has an auto-incremented ID column as its primary key. I have other tables A, B, C and D that compliment the info in table X. Each of these have to contain a column that references the ID from table X. I have done that and in my code (Java) and I have a way of returning the ID of each entry to table X and using that when inserting to the other tables. All is working well.

Now, I've been advised to assign those ID columns on tables A, B, C and D as FOREIGN KEYS because "it's the right thing to do". I did that. Now deleting rows from table X takes an incredible amount of time to complete. Insertion to the other tables takes longer too.

Please don't get me wrong, I know why Foreign Keys are relevant to specify relationships of tables on a DB. But it's starting to seem only ceremonial rather than actually relevant especially as my transactions are becoming slower.

Questions:

1. Is it worth it to lose some performance in a bid to keep relationships officially specified even though it's not that necessary?

2. Is there any way I can speed up my transactions and still keep the FOREIGN KEY specifications.

Thanks.

REPLIES

Here is how the tables were created.

CREATE Tables SQL:

CREATE TABLE [dbo].[MainTableX](
    [col1] [smalldatetime] ,
    [col2] [varchar] (20) ,
    [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_MainTableX] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
)
GO


CREATE TABLE [dbo].[RelatedTableA](
    [differentID] [varchar] (50),
    [detail1] [varchar] (40),
    [detail2] [varchar] (40),
 CONSTRAINT [PK_RelatedTableA] PRIMARY KEY CLUSTERED 
(
    [differentID] ASC
)
GO

-- Tables B, C D are pretty much similar to table A

Add Foreign Keys SQL:

ALTER TABLE RelatedTableA ADD ID INT 
CONSTRAINT fk_refTableX_A FOREIGN KEY (ID)
REFERENCES MainTableX(ID) 
GO
-- Same thing with the other related tables

SOLUTION

I made the Foreign Key columns an index. Now my queries are fast again.

Create nonclustered index IX_RelatedTableA 
on RelatedTableA (ID) 
GO

解决方案

If the FOREIGN KEY columns are correctly indexed (which should happen automatically with the declaration, I believe) then you should see at worst a small performance hit. Please list the CREATE TABLE SQL for each of the tables because it sounds like something is wrong there.

SQL Server does not automatically create an index on the FK column(s), so make sure you do this yourself.

The purpose of using FOREIGN KEY relationships is not to "officially declare" anything (or not just to do that, in any case). Instead, it gives the database engine sufficient information to enforce the integrity of your data. This means that you can't erroneously have your application add or delete records such that a relationship is violated. In addition, no other application using the database (such as Management Studio) can do so, either. It's for this reason -- guaranteed enforcement of rules by the database engine -- that declaring constraints is important.

这篇关于外键减速删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！