（var）char作为性能列的类型？ [英] (var)char as the type of the column for performance?

问题描述

我在PostgreSQL中有一列称为状态的列。首先，它曾经是类型为 integer 的 status_id。这些值保留在客户端上，因此服务器上没有 no 表，称为statuss，我将保留这些状态，然后使用内部联接第一个表。

I have a column called "status" in PostgreSQL. First it used to be "status_id" of type integer. The values were kept on client, so there was no table on the server called statuses where I'd keep those statuses and then do inner join with the first table.

我曾经从客户端发送状态ID（它们在客户端上有名称）。但是，从某种程度上来说，我知道我最好使服务器保持这些状态。不是在单独的表中，而是在第一个表中，我想使它们成为字符串。因此，初始表将具有字符串类型的 status 列（更具体地说，为 varchar ）。我读起来不会那么慢。

I used to send the ids of the statuses from the client (they had the names on the client). However, at some point I understood I'd better make the server hold those statuses. Not in a separate table but in the first one and I want to make them strings. So the initial table will have a status column of type string (varchar, to be more specific). I read it wouldn't be that slow.

总的来说，这是个好主意吗？我想这是因为每次进行内部联接（以防我将状态保留在单独的表中）以及从客户端发送ID都很昂贵。

In general, is it a good idea? I suppose it is because doing inner join (in case I'd keep statuses in the separate table) each time is expensive as well as sending ids from the client.

1）我唯一关心的是列 status 的类型应为 char ，而不是 varchar 。我认为它应该使它更有效。

1) The only concern I have is that the column status should be of type char, not varchar. It should make it more effective I suppose. Is that so?

2）如果第一种情况是正确的，那么我不确定是否可以使用完全相同的字符数来命名所有状态，假设5个字符。其中一些可能会更长一些，一些可能会更短一些。我该如何解决呢？

2) If the first case is correct then I'm not sure I'll be able to name all the statuses using exactly the same amount of characters, let's say, 5 characters. Some of them might be longer, some shorter. How can I solve this?

更新：

这不是非国有化，因为我在谈论1张单桌。没有，也从来没有第二个表叫做状态，这些表带有字段（id，status_name）。

It's not denationalization because I'm talking about 1 single table. There is no and has never been the second table called Statuses with the fields (id, status_name).

我要传达的是我可以使用char（ n）为status_name并在其上添加索引。然后它应该足够快。但是，可能无法或不可能用一定数量的字符（n）来命名所有状态，这是唯一需要考虑的问题。

What I'm trying to convey is that I could use char(n) for status_name and also add index on it. Then it should be fast enough. However, it might be or not possible to name all the statuses with the certain (n) amount of characters and that's the only concern.

推荐答案

我不认为使用char或varchar代替integer是个好主意。很难预料它会比整数PK慢多少，但是这种设计会更慢-当您加入更大的表时，影响将更加可怕。如果可以，请改用ENUM类型。

I don't think so using char or varchar instead integer is good idea. It is hard to expect how much slower it will be than integer PK, but this design will be slower - impact will be more terrible when you will join larger tables. If you can, use ENUM types instead.

http://www.postgresql.org/docs/9.2/static/datatype-enum.html

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

PostgreSQL的varchar和char类型非常相似。内部实现是相同的-由于空格相加，char可能会（有点自相矛盾）变慢。

PostgreSQL varchar and char types are very similar. Internal implementation is same - char can be (it is paradox) little bit slower due addition by spaces.

这篇关于（var）char作为性能列的类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！