MySql DB 设计 - 两者中哪一个是最好的 - 规范化与否

Question

请查看下面的分析，让我知道两者中最好的数据库设计 (InnoDB)。要求 - 当存在许多并发数据库连接时，用户无需等待的更快写入和读取，预计这些连接将呈指数增长。如果用户必须等待，则磁盘空间优势无关紧要。

假设——单CPU(仅供比较)

方法 1 (M1) Table1 UserProfile -> UserID, City, State, Country

Method2 (M2)(Normalized) Table2a UserProfile->UserID,LocationsID Table2b Locations-> LocationsID, City, State, Country

写(顺序不对)

一个。写入表

M1-直接写= t1 M2-(查表2b看有记录=t2+不匹配则插入=t1 在表2a中写入UserID和LocationsID=t3)(t1+t2+t3) > t1

b.CPU 中断

M1=1,M2=2

c.磁盘I/O

M1=1,M2=2

d.行锁&释放

M1=1,M2=2

磁盘空间

M1=More, M2=Less(只在M2有优势)

读取(假设记录不在缓存中)

一个。从表中读取

M1-直读=t4，M2-加入-t5 t5>t4

CPU 中断

M1=1, M2=2

c.磁盘I/O

M1=1,M2=2

我相信，如果预先填充 Table2b 或对国家、州、城市下拉列表进行数字标记，则可以缩短在方法 2 中花费的时间。即使你负载均衡 M1 似乎也是一个有吸引力的设计。增加 BW 可能会使情况变得更糟，因为会有更多的并发数据库连接。告诉我你的想法

Answer 1

Method2 (M2)(Normalized) Table2a UserProfile->UserID,LocationsID Table2b Locations-> LocationsID, City, State, Country

您已将城市、州和国家/地区替换为 ID 号。虽然在某些情况下这可能是一个好的设计决策，但它并不总是一个好的设计决策。它与规范化无关。 (没有“我使用了一个身份证号码”这样的正常形式。)

当有国际标准时，使用它通常是有意义的。参见 ISO 3166-1 .三字母代码可能更有意义。

-- Untested code.
create table countries (
  iso_country_code char(2) not null,
  country_name varchar(35) not null,
  primary key (iso_country_code),
  unique (country_name)
);

create table states (
  state_code char(2) not null,          -- application-dependent, consider ISO 3166-2
  state_abbrev varchar(7) not null,
  state_name varchar(35) not null,
  iso_country_code char(2) not null,
  primary key (state_code, iso_country_code),
  unique (state_abbrev, iso_country_code),
  unique (state_name, iso_country_code),
  foreign key (iso_country_code) references countries (iso_country_code)
);

create table cities (
  city_name varchar(35) not null,
  state_code char(2) not null,
  iso_country_code char(2) not null,
  primary key (city_name, state_code, iso_country_code),
  foreign key (state_code, iso_country_code) 
    references states (state_code, iso_country_code)
);

create table UserProfile (
  UserID integer not null,
  city_name varchar(35) not null,
  state_code char(2) not null,
  iso_country_code char(2) not null,
  primary key (UserID),
  foreign key (city_name, state_code, iso_country_code) 
    references cities (city_name, state_code, iso_country_code)
);

国家、州和城市的单独表格使使用 SELECT 语句填充组合框变得容易。他们不需要数字“标签”。这三个表都是关键；他们没有非素数属性。我认为他们在 5NF 中。

根据经验，不要搜索一行以查看它是否存在，如果不存在则插入。这需要两次往返数据库。

相反，只需插入该行，并捕获如果它是重复的就会出现的错误。无论如何，您必须捕获错误——除了复制之外，还有很多事情可以阻止插入成功。