如果我只是在做查找表，我应该使用自动生成的主键吗？

I have a table which has two varchar(Max) columns

我有一个表有两个varchar(Max)列

Column 1      Column 2
-----------------------
URLRewitten   OriginalURL

its part of my url re-writing for an asp.net webforms site.

我的网址重写为asp.net webforms网站。

when a url comes in I do a check to see if its in the table if it is i use the OriginalURL.

当一个网址进来时,我会检查它是否在表中,如果它是我使用的是OriginalURL。

My question is, if all I'm doing is querying the table for urls and no other table in the database will ever link to this table does it need a dedicated primary key field? like an auto-number? will this make queries faster?

我的问题是,如果我正在做的是查询表中的URL,并且数据库中没有其他表将链接到此表是否需要专用的主键字段?像一个自动编号?这会使查询更快吗?

and also how can I make the query's run as faster?

以及如何让查询运行得更快?

Edit: I do have a unique constraint on URLRewitten.

编辑:我对URLRewitten有一个独特的约束。

Edit: ways i'm using this table..

编辑:我正在使用这张桌子的方式..

Query when a new Request comes in.. search on URLRewitten to find OriginalURL

查询新请求何时进入..搜索URLRewitten以查找OriginalURL

When needing to display a link on the site, i query on the OriginalURL to find the URLRewitten url i should use.

当需要在网站上显示链接时,我在OriginalURL上查询以找到我应该使用的URLRewitten网址。

When adding a new url to the table i make sure that it doesn't already exist.

向表中添加新URL时,请确保它尚不存在。

thats all the querys i do.. at the moment.

这就是我所做的所有查询..此刻。

Both columns together would be unique.

两列一起是唯一的。

7 个解决方案

#1

Do you need a primary key? Yes. Always. However, it looks like in your case OriginalURL could be your primary key (I'm assuming that there wouldn't be more than one value for URLRewritten for a given value in OriginalURL).

你需要一把钥匙吗?是。总是。但是,看起来在你的情况下,OriginalURL可能是你的主键(我假设对于OriginalURL中的给定值,URLRewritten的值不会超过一个)。

This is what's known as a "natural key" (where a component of the data itself is, by its nature, unique). These can be convenient, though I have found that they're generally more trouble than they're worth under most circumstances, so yes, I would recommend some sort of opaque key (meaning a key that has no relation to the data in the row, other than to identify a single row). Whether or not you want an autonumber is up to you. It's certainly convenient, though identity columns come with their own set of advantages and disadvantages.

这就是所谓的“自然键”(数据本身的一个组成部分本质上是唯一的)。这些可能很方便,但我发现它们在大多数情况下通常比它们的价值更麻烦,所以是的,我会推荐某种不透明的键(意思是与行中的数据无关的键) ,除了识别单行之外)。您是否想要自动编号取决于您自己。这当然很方便,虽然标识栏有各自的优点和缺点。

For now I suppose I would advise creating two things:

现在我想我会建议创建两件事:

A primary key on your table of an identity column

您的标识列表的主键

A unique constraint on OriginalURL to enforce data integrity.

对OriginalURL的唯一约束,以强制数据完整性。

#2

I'd put one in there anyway... it'll make updating alot easier or duplicating an existing rule...

无论如何我会把一个放在那里......它会使更新更容易或复制现有规则......

i.e. this is easier

即这更容易

UPDATE Rules SET OriginalURL = 'http://www.domain.com' WHERE ID = 1

--OR

INSERT INTO Rules SELECT OriginalUrl, NewUrl FROM Rules WHERE ID = 1

Than this

this is easier

这更容易

UPDATE Rules SET OriginalURL = "http://www.domain.com" WHERE OriginalURL = 'http://old.domain.com'

--OR

INSERT INTO Rules SELECT OriginalUrl, NewUrl FROM Rules WHERE OriginalURL = 'http://old.domain.com'

In terms of performance, if your going to be searching by OriginalURL, you should add an index to that column,

在性能方面,如果您要通过OriginalURL进行搜索,则应该为该列添加索引,

#3

I would use the OriginalURL as your primary key as I would assume this is unique. Assuming your are using SQL-Server you could create an index on RewrittenURL with OrigionalURL as an "Included column" to speed up the performance of the query.

我会使用OriginalURL作为您的主键,因为我认为这是唯一的。假设您正在使用SQL-Server,您可以使用OrigionalURL在RewrittenURL上创建一个索引作为“包含的列”,以加快查询的性能。

#4

An identity column can help when you search for recent events:

搜索最近事件时,标识列可以提供帮助:

select top 100 * from table order by idcolumn desc

We'd have to know what kind of queries you are running, before we can search for a way to make them faster.

我们必须知道您正在运行什么类型的查询,然后我们才能找到一种方法来加快它们的运行速度。

#5

As you are doing your query on the URLRewritten column I don't think adding an auto-generated primary key would help you.

当您在URLRewritten列上进行查询时,我认为添加自动生成的主键不会对您有所帮助。

Have you got an index on your URLRewritten column? If not, create one: that should see a big increase in the speed of your queries (perhaps just make URLRewritten your primay key?).

你的URLRewritten列上有索引吗?如果没有,创建一个:应该看到查询速度大幅提高(或许只是让URLRewritten成为你的主要密钥?)。

#6

Yes there should be a Primary Key Because you can set INDEX on that Primary Key for Fast Access

是的,应该有一个主键,因为您可以在该主键上设置INDEX以进行快速访问

#7

I don't think adding auto generated primary key will make your query faster.

我不认为添加自动生成的主键会使您的查询更快。

However there are are a few things to consider:

但是有几件事需要考虑:

I would not be so sure, that never ever nothing will link to this table :(.

我不会那么肯定,从来没有任何东西会链接到这张桌子:(。

I've seen a lot of people asking about how to i.e. remove duplicates from table like that -- with primary key it is much easier.

我见过很多人询问如何从表中删除重复项 - 使用主键更容易。

To make this query faster we need to know more about this table and ways of using it...

为了更快地进行此查询,我们需要了解有关此表及其使用方法的更多信息...

In my opinion, every table, must have auto generated primary key (i.e. identity in MSSQL).

在我看来,每个表都必须有自动生成的主键(即MSSQL中的标识)。

I don't believe in unique natural keys.

我不相信独特的自然键。

#1