关系型数据库在 Web 应用程序中正变得越来越流行。这通常是一件好事，使我们能够专注于数据的结构方式，而不是数据在磁盘上的存储方式。将数据存储和检索任务卸载到关系型数据库服务器意味着我们的程序可以更小、更易于维护。

然而，在网站上整合数据库服务器并非万能药。数据库可能负责许多必要的任务，但它无法为您设计表，也无法确定使用表的最佳方式。

本月，我们将探讨数据库设计的艺术，以及我们如何构建表以提高性能。充分利用数据库是一门有点神秘的艺术，这就是为什么优秀的数据库管理员 (DBA) 总是供不应求。但是，通过一些简单的技术，我们可以克服 Web 程序员遇到的一些最基本的性能问题。我们将设计一个可以处理火车时刻表的数据库。在此过程中，我们将探讨数据库程序员在决定如何在数据库中设计表时面临的许多问题。

我喜欢火车，而且当我必须在特拉维夫和海法之间旅行时，经常乘坐火车。有一天，在翻阅以色列铁路管理局分发的小型纸质时刻表后，我意识到计算机化火车时刻表的实现并不像乍看起来那么简单。

铁路时刻表通常以印刷表格的形式出现，列代表各个火车，行代表每个车站。每个表格列出一条铁路线上的火车，方向单一。

由于关系型数据库将其所有数据存储在表中，您可能会认为这是存储信息的完美方式。为了让我们更容易地添加和删除火车，我们将交换印刷时刻表的轴，将各个火车放在行中，将车站放在列中。

为了在 SQL 中定义这样的表，我们可以使用如下查询

CREATE TABLE HaifaToTelAviv (
   haifa_central   TIME NOT NULL,
   haifa_bat_galim TIME NOT NULL,
   binyamina       TIME NOT NULL,
   hof_hacarmel    TIME NOT NULL,
   ta_central      TIME NOT NULL,
   ta_hashalom     TIME NOT NULL
);

给定这样的表，我们可以按如下方式输入我们的火车

INSERT INTO HaifaToTelAviv
   (haifa_central, haifa_bat_galim, binyamina, hof_hacarmel,
     ta_central, ta_hashalom)
VALUES
   ("12:05", "12:10", "12:17", "12:37", "13:16",
    "13:21");

此外，尝试从上面的 HaifaToTelAviv 表中查询信息将很困难，需要我们知道与每个车站对应的列的名称。问题还在继续——例如，如果快车经过 Binyamina 会发生什么？我们可以将“binyamina”列定义为 NULL，并在该列中输入 NULL 值。但是，NULL 通常表示值未知或缺失，而这种情况下的原因要简单得多。

最后，如果出现新的时刻表，使每列火车都晚点不同的时间量会发生什么？以这种格式编辑时刻表将非常困难。

那么，如果我们不能从印刷时刻表开始，我们应该如何建模火车时刻表呢？解决方案是将信息分解为更小的表，将它们组合在一起以回答问题。关系型数据库专门从事这种操作，允许我们将两个或多个表“连接”在一起。

将单个大型表分解为许多较小的表使数据库更灵活，使我们能够提出比其他方式更多的查询。例如，我们应该能够提出如下问题：

如果我们正确地建模我们的数据，将其分解为足够小且灵活的表，那么应该可以使用单个 SQL 查询来回答所有这些问题。

这些示例都使用了 MySQL，这是一种在许多网站上流行的“大部分免费”数据库。MySQL 缺少其他数据库的一些高级功能，例如事务和引用完整性。但是，它易于安装和管理，并且速度极快。您可以在 https://mysqlserver.cn/ 了解更多关于 MySQL 的信息。

例如，以下是 RailStations 表的定义

CREATE TABLE RailStations (
   id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   UNIQUE(name)
);

RailStations 存在的唯一理由是将数字 ID 与每个车站关联起来。创建一个这样的表似乎很傻，因为我们可以直接在我们需要的任何地方输入车站名称。

但是，为每个车站指定一个 ID 号码给我们带来了两个优势。首先，我们可以确保车站名称的拼写一致，没有拼写、大小写和缩写的变化。其次，整数比它指向的名称占用更少的空间。每个 tinyint 占用一个字节，而 20 个字符的车站名称将占用 20 个字节。因此，引用全名将占用 20 倍的 RAM 和磁盘空间。

请注意，我们将 id 定义为 TINYINT UNSIGNED 类型的列。这使我们能够分配 0 到 255 之间的值。拥有超过 255 个车站的大型铁路系统将需要使用 SMALLINT UNSIGNED，其范围在 0 到 65535 之间。

我们通过给 RailStations 中的每个车站名称 UNIQUE 限定符来确保其唯一性。ID 号码已经保证是唯一的，因为它们已被声明为主键。更好的是，因为我们指定了 AUTO_INCREMENT，如果 INSERT 查询忽略它，MySQL 将自动分配一个 ID 号码。例如

INSERT INTO RailStations (name)
VALUES ("Nahariya");

如果我们现在查询数据库

SELECT id
FROM RailStations
WHERE name = "Nahariya";

我们可以使用单个 INSERT 语句将一个或多个新行插入到表中。例如，以下代码向 RailStations 添加了更多行

INSERT INTO RailStations (name)
VALUES ("Akko"),
       ("Hof Hacarmel"),
       ("Tel Aviv Central"),
       ("Tel Aviv Hashalom"),
       ("Lod"),
       ("Rehovot"),
       ("Herzliya")
;

与汽车、公共汽车和飞机不同，火车沿着固定的线路运行。每条线路必须至少有两个车站，每个车站都在一条或多条线路上。

我们可以在 RailStations 中包含一个额外的“线路”列，以标识每列火车关联的线路。但是，给定这样的表，我们如何处理位于多条线路上的车站？将车站视为两个独立的车站是没有意义的，特别是如果人们需要换乘火车。

更好的解决方案是使用单独的 RailLines 表，其定义类似于 RailStations

CREATE TABLE RailLines (
   id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   UNIQUE(name)
);

现在我们有了线路和车站的列表，我们将创建一个第三个表来描述两者之间的交集

CREATE TABLE StationLines (
   station_id TINYINT UNSIGNED NOT NULL,
   line_id    TINYINT UNSIGNED NOT NULL,
   north_to_south TINYINT UNSIGNED NOT NULL,
   UNIQUE(station_id, line_id),
   INDEX(station_id),
   INDEX(line_id),
   INDEX(north_to_south)
);

由于每个车站可以位于多条铁路线上，并且每条铁路线包含多个车站，因此我们不应在这些列中使用 UNIQUE 修饰符。但是，我们不希望任何车站和线路的组合在表中出现多次。我们通过将 station_id 和 line_id 都命名为 UNIQUE 的参数来强制执行此操作。这两个列中的任何一个都可以多次出现，但任何两个值的组合只能出现一次。

例如，以下行将 station_id 1 放置为 line_id 1 上的最北端的车站

INSERT INTO StationLines
   (station_id, line_id, north_to_south)
VALUES
   (1, 1, 1);

以下内容表示 station_id 7 距离 line_id 4 的起点有 11 站

INSERT INTO StationLines
   (station_id, line_id, north_to_south)
VALUES
   (7, 4, 11);

定义了 StationLines 后，我们可以开始向数据库提出基本问题。例如，我们可以列出二号线上的车站

SELECT station_id
FROM StationLines
WHERE line_id = 2
ORDER BY north_to_south;

此查询产生以下结果

+------------+
| station_id |
+------------+
|          6 |
|          4 |
|          5 |
+------------+

幸运的是，关系型数据库允许我们连接两个表，从而将车站的 ID 号码与其名称连接起来。为了避免混淆来自两个表的列，我们使用 table.column 语法命名每个列，用句点分隔两者。为了减少我们必须键入的量，我们为每个表指定一个昵称。

例如，我们可以构建我们的查询，使其从 RailStations 和 StationLines 中选择信息

SELECT S.name
FROM RailStations S, StationLines L
WHERE L.line_id = 2
  AND S.id = L.station_id
ORDER BY north_to_south;

该查询现在产生以下结果

+-------------------+
| name              |
+-------------------+
| Lod               |
| Tel Aviv Central  |
| Tel Aviv Hashalom |
+-------------------+

在上面的示例中，数据库服务器首先创建一个包含 112 行的表（RailStations 中的 8 行 x StationLines 中的 14 行）。然后，它删除 L.line_id 不是 2 的所有行，生成 24 行。然后，它应用最终条件，删除 S.id 和 L.station_id 不相等的行。结果是三行。

因为我们将数据分解为三个表，并且我们可以使用任何一组条件连接任何表组合，所以我们的数据库已经可以帮助我们回答一些基本问题。例如，哪些铁路线连接到特拉维夫中央车站？知道该车站的 ID 是 4，我可以编写以下查询

SELECT L.name
FROM  RailLines L, StationLines SL
WHERE SL.station_id = 4
  AND L.id = SL.line_id;

该查询产生以下结果

+-------------------------------+
| name                          |
+-------------------------------+
| Nahariya  - Tel Aviv          |
| Tel Aviv  - Be'er Sheva       |
| Binyamina - Tel Aviv suburban |
+-------------------------------+

SELECT L.name
FROM RailLines L, StationLines SL, RailStations S
WHERE L.id = SL.line_id
  AND SL.station_id = S.id
  AND S.name = "Tel Aviv Central";

<select name="station">
  <option value="4">Tel Aviv Central
</select>

除了列定义和 UNIQUE 限定符之外，我们对 StationLines 表的定义还包括三行 INDEX——每行分别对应 station_id、line_id 和 north_to_south 列。

虽然将关系型数据库表视为带有行和列的增强型电子表格通常很有帮助，但两者之间存在一些重要的差异。其中之一是数据库表不以任何特定顺序存储其行。如果我们有兴趣以特定顺序从表中检索行，我们必须在查询中使用 ORDER BY 子句指定它。

由于行不以任何特定方式排序，因此 SELECT 查询通常需要相当长的时间才能完成。例如，以下查询

SELECT id
FROM RailStations
WHERE name = "Tel Aviv Central";

考虑到它只涉及单个表和一个简单的 WHERE 子句，这似乎不是一个耗时的查询。但是，由于 RailStations 的行不以任何特定顺序存储，因此查找名称为“Tel Aviv Central”的行可能需要相当长的时间。对于 100 行的表来说，这可能是一个可以忽略不计的时间，但是当表包含 1,000 或 10,000 行时，时间可能会变得很明显。在这个特定示例中，数据库服务器可能足够智能，可以意识到 RailStations.name 已声明为 UNIQUE，这意味着我们的查询将返回一行（如果返回任何内容）。这意味着服务器平均必须搜索一半的行——但这仍然可能需要相当长的时间。

索引通过添加指向每个列值的指针来改变这种情况。如果 RailStations.name 被索引，MySQL 服务器几乎可以立即找到包含特定值的那些行。它还可以确定值是否存在。

如果索引可以如此显着地提高查询速度，为什么默认情况下行是未索引的？主要答案是每次对表执行 INSERT 或 UPDATE 操作时，都会写入和更新索引。由于大多数数据库查询都是 SELECT，索引可以在其中显着提高性能，因此这通常是可以接受的权衡。但是，某些应用程序必须以最大速度 INSERT 和 UPDATE，在这种情况下，创建索引可能会导致问题。

由于索引用于定位具有特定值的列，因此它们仅对于将在 WHERE 子句中命名的那些列是必要的。没有必要为显示的列编制索引，但很少用作搜索条件。

在某些情况下，仅索引每列的第一部分而不是整个列就足够了。例如，如果我们正在索引 VARCHAR(50) 类型的列，那么我们可能只能索引其中 10 个字符。这将保留完整索引的大部分优点（因为在前十个字符中，此类文本字段很少相同），同时减少索引必须存储的信息量。

现在我们已经彻底检查了描述火车系统的表，是时候将一些火车放到这些轨道上了。如何建模这些数据是一个棘手的问题，因为有很多方法可以实现它。我决定将此信息拆分为两个表：Trains 和 DepartureTimes。

Trains 的每一行都描述了一列特定的火车，指示它运行的线路、其始发站和目的站的 ID 号码，以及它从始发站出发的时间

CREATE TABLE Trains (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  line_id TINYINT UNSIGNED NOT NULL,
  origin_id TINYINT UNSIGNED NOT NULL,
  destination_id TINYINT UNSIGNED NOT NULL,
  depart_origin_time TIME NOT NULL,
  UNIQUE(line_id, origin_id, destination_id,
    depart_origin_time),
  INDEX(line_id),
  INDEX(origin_id),
  INDEX(destination_id),
  INDEX(depart_origin_time)
);

第一列是主键，允许我们用一个数字描述每列火车。线路、始发地、目的地和小时的组合应该是唯一的，因此我们要求数据库服务器使用 UNIQUE 关键字强制执行此条件。

最后，我们定义 DepartureTimes 表，该表存储有关火车何时从特定车站出发的信息

CREATE TABLE DepartureTimes (
   train_id SMALLINT UNSIGNED NOT NULL,
   station_id TINYINT UNSIGNED NOT NULL,
   departure_time TIME NOT NULL,
   INDEX(train_id),
   INDEX(station_id),
   INDEX(departure_time)
);

一旦我们将信息输入到这些表中，我们就可以开始执行复杂的查询。例如，哪些火车在早上 8 点之前到达“特拉维夫中央车站”？

SELECT train_id
FROM DepartureTimes
WHERE departure_time < "08:00"
  AND station_id = 4;

+----------+
| train_id |
+----------+
|        1 |
|        2 |
+----------+

SELECT S.name, T.depart_origin_time
FROM DepartureTimes DT, Trains T, RailStations S
WHERE DT.departure_time < "08:00"
  AND DT.station_id = 4
  AND DT.train_id = T.id
  AND S.id = T.origin_id;

假设我们想乘坐当天的第一班火车（ID 1），我们可以打印出它到达每个车站的时间表

SELECT T.id, S.name, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
  StationLines SL
WHERE T.id = DT.train_id
  AND T.id = 1 AND T.line_id = SL.line_id
  AND SL.station_id = DT.station_id
  AND DT.station_id = S.id
ORDER BY T.id, SL.north_to_south
;

我们甚至可以打印到特拉维夫的火车（ID 5）的完整时刻表

SELECT T.id, S.name, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
  StationLines SL
WHERE T.id = DT.train_id
  AND T.line_id = SL.line_id
  AND SL.station_id = DT.station_id
  AND DT.station_id = S.id
  AND T.destination_id = 5
ORDER BY T.id, SL.north_to_south
;

SELECT T.id, S.name, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
  StationLines SL
WHERE T.id = DT.train_id
  AND T.line_id = SL.line_id
  AND SL.station_id = DT.station_id
  AND DT.station_id = S.id
  AND T.destination_id = 5
  AND T.depart_origin_time > "09:00"
ORDER BY T.id, SL.north_to_south
;

虽然在 Web 应用程序中使用数据库来存储和检索信息通常是一个好主意，但如何构建该数据库中的表并不总是显而易见的。正如我们所看到的，将信息拆分为单独的表，可以以多种方式混合和匹配数据。通过使用数字主键并索引我们最需要的列，我们可以使我们的查询既高效又灵活。

现在我们已经了解了如何以智能的方式定义我们的数据库表，现在是时候创建一些应用程序来使用它们了。下个月，我们将研究各种可以使用这些表的应用程序，为它们提供适合 Web 用户的界面。