使用键与索引

在 SQL 中存在多种键，通常用于约束和查询。本文将介绍主键、唯一键、非空值、默认值、外键与索引。

开始前的一个小芝士

本文将介绍键和索引的使用，这首先需要学会查看已有键和索引，但是初学者往往不知道如何查看表的结构。不同 SQL 语言查看的方法各不相同，对于 MySQL 可以使用下面的命令。

1	SHOW CREATE TABLE {表名};

1	SHOW CREATE TABLE MOvies;

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                         |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MOvies | CREATE TABLE `movies` (
  `title` text,
  `year` int DEFAULT NULL,
  `length` int DEFAULT NULL,
  `movieType` text,
  `studioName` text,
  `producerC` int DEFAULT NULL,
  UNIQUE KEY `uniqueTitle` (`title`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

主键

什么是主键

在介绍主键前首先需要了解一些相关的概念。

超键：可唯一确定一条记录的字段组成的元组，可以只有一个字段。
候选键：没有子集是超键的超键。通俗地讲，候选键是没有冗余字段的用于唯一确定字段的元组。
主键：用户从候选键中选择一个（或不选）作为主键，包含多个字段的主键称为联合主键。用户应选择合适的候选键作主键。

那么为什么要引入主键的概念？主键被用户指定用于唯一确定一行，于是在查找时，主键起到了非常重要的作用。

一张表只能有一个主键或没有主键，主键在插入操作时要求主键不能重复，否则将插入失败。

创建和删除主键

在建表时创建主键

1	CREATE TABLE test (id int PRIMARY KEY, name text, age smallint);

另外，如果需要创建联合主键，可以使用下面这种方法。联合主键在实际中很少使用。

1	CREATE TABLE test (id int, name text, age smallint, PRIMARY KEY (id, name(4)));

注意事项

在创建主键时，如果要对 BLOB / TEXT 等变长类型创建键，必须指定用于创建键的长度。如上面这个示例中的 name(4) 表示取前 $4$ 个字符创建主键。

这里的键不仅指主键，还包括：唯一键、外键、索引等。

在建表后创建主键

在创建表后修改主键不是一个很好的解决方案，一般建议在创建表时即确定表的结构。那么如果仍然需要，可以使用 ALTER TABLE 语句。

1 2	CREATE TABLE test (id int, name text, age smallint); ALTER TABLE test ADD PRIMARY KEY (id, name(4));

上面这个示例与 在建表时创建主键 是一样的效果。但创建主键时，会检查主键是否重复以及是否已有主键存在，否则创建失败。在创建键（不仅是主键）时 KEY 关键字后必须有 ()，不能遗漏。

如果创建的主键不是联合主键，可以使用：

1 2	CREATE TABLE test (id int, name text, age smallint); ALTER TABLE test MODIFY COLUMN id int PRIMARY KEY; -- Mysql 中使用 MODIFY

删除主键

和 在建表后创建主键 相似，删除主键使用 ALTER TABLE 语句。修改主键必须先删除主键。

1 2	CREATE TABLE test (id int, name text, age smallint, PRIMARY KEY (id, name(4))); ALTER TABLE test DROP PRIMARY KEY;

唯一键

唯一键和主键相似，同样可以唯一标识一行但不被指定为主键。主键只能有一个，但唯一键不受限制。在实际应用中，例如学生表中，可以将学号定义为主键，因为它被认为是学生的标识，但如身份证信息不适合用于标识学生但也是唯一确定的，这类信息可以使用唯一键进行约束。

创建唯一键

唯一键对应的关键字为 UNIQUE KEY，由于唯一键可以有多个，相较于主键语法更复杂。

1 2	CREATE TABLE {表名} ({字段名} {字段类型} [UNIQUE], ...); -- CREATE TABLE test (id int UNIQUE, name text, age smallint);

1 2	ALTER TABLE {表名} ADD CONSTRAINT [{约束名}] UNIQUE KEY (创建字段); -- ALTER TABLE test ADD CONSTRAINT uniqueId UNIQUE KEY (id);

可以注意到约束名是可缺省的，缺省时将自动命名，通常与创建字段同名。另外，唯一键也可以使用联合键，可以参考创建主键。

删除唯一键

1	ALTER TABLE {表名} DROP CONSTRAINT {约束名};

非空值与默认值

与上面两种约束不同的是，字段非空约束和默认值只能对单字段使用，其语法也不太一样。字段非空约束和默认值也可以在创建表时或创建表后设置。

1
2
3

CREATE TABLE {表名} ({字段名} {字段类型} [NOT NULL] [DEFAULT {默认值}], ...);
-- CREATE TABLE test (id int NOT NULL, name text, age smallint);
-- CREATE TABLE test (id int DEFAULT 0, name text, age smallint);

ALTER TABLE {表名} MODIFY COLUMN {字段名} {字段类型} [NOT NULL] [DEFAULT {默认值}]
-- ALTER TABLE test MODIFY COLUMN id int NOT NULL;  -- 设置为非空
-- ALTER TABLE test MODIFY COLUMN id int DEFAULT 0;  -- 设置默认值 0
-- ALTER TABLE test MODIFY COLUMN id int;  -- 设置为空且无默认值

注意：主键不能去除非空约束。BLOB / TEXT / GEOMETRY / JSON 不能设置默认值。在 ALTER TABLE 时设置和移除非空值与默认值都使用 MODIFY COLUMN。

外键

什么是外键

外键是一种重要的约束，用于保证联结表的数据安全性。外键保证用于联结的键在外表中始终存在。

我们经常会使用下面的方式进行联结：

1	SELECT * FROM Movies;

1	SELECT * FROM MovieExec;

SELECT 
    title, 
    year, 
    length, 
    movieType, 
    studioName, 
    name AS execName 
FROM 
    Movies 
LEFT JOIN 
    MovieExec 
ON 
    producerC = cert;

+--------------------------+------+--------+-----------+----------------+-----------+
| title                    | year | length | movieType | studioName     | producerC |
+--------------------------+------+--------+-----------+----------------+-----------+
| Empire Strikes Back      | 1980 |    111 | drama     | Fox            |       555 |
| My people,My country     | 2019 |    158 | feature   | huaxia         |       100 |
| Gone With the Wind       | 1938 |    238 | drama     | MGM            |       123 |
| Logan's run              | 1977 |    120 | drama     | MGM            |       888 |
| Pretty Woman             | 1990 |    119 | drama     | Disney         |       999 |
| Star Trek                | 1979 |   NULL | sciFic    | Paramount      |       444 |
| Star Trek: Nemesis       | 2002 |    116 | sciFic    | Paramount      |       321 |
| Star Wars                | 1977 |    124 | sciFic    | Fox            |       555 |
| Terms of Endearment      | 1983 |    132 | drama     | MGM            |       123 |
| The Man Who Wasn't There | 2001 |    116 | comedy    | USA Entertainm |       777 |
| The Usual Suspects       | 1995 |    106 | drama     | MGM            |       999 |
+--------------------------+------+--------+-----------+----------------+-----------+

+-------------------+-------------+------+-----------+
| name              | address     | cert | netWorth  |
+-------------------+-------------+------+-----------+
| Huangjianxin      | NULL        |  100 |      NULL |
| Calvin Coolidge   | Fast Lane   |  123 |  20000000 |
| Merv Griffin      | Riot Rd.    |  199 | 112000000 |
| Stephen Spielberg | 123 ET road |  222 | 100000000 |
| Ted Turner        | Turner Av.  |  333 | 125000000 |
| George Lucas      | Oak Rd.     |  555 | 200000000 |
| Jane Fonda        | Turner Av.  |  567 | 200000000 |
+-------------------+-------------+------+-----------+

+--------------------------+------+--------+-----------+----------------+-----------------+
| title                    | year | length | movieType | studioName     | execName        |
+--------------------------+------+--------+-----------+----------------+-----------------+
| Empire Strikes Back      | 1980 |    111 | drama     | Fox            | George Lucas    |
| My people,My country     | 2019 |    158 | feature   | huaxia         | Huangjianxin    |
| Gone With the Wind       | 1938 |    238 | drama     | MGM            | Calvin Coolidge |
| Logan's run              | 1977 |    120 | drama     | MGM            | NULL            |
| Pretty Woman             | 1990 |    119 | drama     | Disney         | NULL            |
| Star Trek                | 1979 |   NULL | sciFic    | Paramount      | NULL            |
| Star Trek: Nemesis       | 2002 |    116 | sciFic    | Paramount      | NULL            |
| Star Wars                | 1977 |    124 | sciFic    | Fox            | George Lucas    |
| Terms of Endearment      | 1983 |    132 | drama     | MGM            | Calvin Coolidge |
| The Man Who Wasn't There | 2001 |    116 | comedy    | USA Entertainm | NULL            |
| The Usual Suspects       | 1995 |    106 | drama     | MGM            | NULL            |
+--------------------------+------+--------+-----------+----------------+-----------------+

在上面这个示例使用联结时，我们将 Movies 表（主表）与 MovieExec 表（外表）按照字段 producerC（制片人标识）与 cert 联结。但这里也看到了一些问题，例如我们使用左联结时，在表中出现了大量的 NULL 字段，因为没有在 MovieExec 表中找到对应的标识。此时如果在主表中插入一个外表不存在的记录或从外表删除一个主表依赖的记录，可能都会导致必要数据缺失。

首先一个外键具有三个参数：

外键约束名（用于标识外键）
主表中的被联结字段名（主字段）
外表中的联结字段名（外字段）

外键即保证了约束：主字段中元素一定包含于外字段。主字段可重复，外字段也可重复。

创建和删除外键

创建外键

创建外键的语法相对复杂，需要特别注意主字段外必须要有括号。通常外字段是外表的主键。

1	ALTER TABLE {主表名} ADD CONSTRAINT {约束名} FOREIGN KEY ({主字段}) REFERENCES {外表名}({外字段}};

ALTER TABLE 
    Movies 
ADD CONSTRAINT 
    foreignProducerC 
FOREIGN KEY 
    (producerC) 
REFERENCES 
    MovieExec(cert);

1	SELECT * FROM Movies;

1	SELECT * FROM MovieExec;

SELECT 
    title, 
    year, 
    length, 
    movieType, 
    studioName, 
    name AS execName 
FROM 
    Movies 
LEFT JOIN 
    MovieExec 
ON 
    producerC = cert;

1 2	Query OK, 11 rows affected (0.12 sec) Records: 11 Duplicates: 0 Warnings: 0

+--------------------------+------+--------+-----------+----------------+-----------+
| title                    | year | length | movieType | studioName     | producerC |
+--------------------------+------+--------+-----------+----------------+-----------+
| Empire Strikes Back      | 1980 |    111 | drama     | Fox            |       555 |
| My people,My country     | 2019 |    158 | feature   | huaxia         |       100 |
| Gone With the Wind       | 1938 |    238 | drama     | MGM            |       123 |
| Logan's run              | 1977 |    120 | drama     | MGM            |       888 |
| Pretty Woman             | 1990 |    119 | drama     | Disney         |       999 |
| Star Trek                | 1979 |   NULL | sciFic    | Paramount      |       444 |
| Star Trek: Nemesis       | 2002 |    116 | sciFic    | Paramount      |       321 |
| Star Wars                | 1977 |    124 | sciFic    | Fox            |       555 |
| Terms of Endearment      | 1983 |    132 | drama     | MGM            |       123 |
| The Man Who Wasn't There | 2001 |    116 | comedy    | USA Entertainm |       777 |
| The Usual Suspects       | 1995 |    106 | drama     | MGM            |       999 |
+--------------------------+------+--------+-----------+----------------+-----------+

+-------------------+-------------+------+-----------+
| name              | address     | cert | netWorth  |
+-------------------+-------------+------+-----------+
| Huangjianxin      | NULL        |  100 |      NULL |
| Calvin Coolidge   | Fast Lane   |  123 |  20000000 |
| Merv Griffin      | Riot Rd.    |  199 | 112000000 |
| Stephen Spielberg | 123 ET road |  222 | 100000000 |
| NULL              | NULL        |  321 |      NULL |
| Ted Turner        | Turner Av.  |  333 | 125000000 |
| NULL              | NULL        |  444 |      NULL |
| George Lucas      | Oak Rd.     |  555 | 200000000 |
| Jane Fonda        | Turner Av.  |  567 | 200000000 |
| NULL              | NULL        |  777 |      NULL |
| NULL              | NULL        |  888 |      NULL |
| NULL              | NULL        |  999 |      NULL |
+-------------------+-------------+------+-----------+

+--------------------------+------+--------+-----------+----------------+-----------------+
| title                    | year | length | movieType | studioName     | execName        |
+--------------------------+------+--------+-----------+----------------+-----------------+
| Empire Strikes Back      | 1980 |    111 | drama     | Fox            | George Lucas    |
| My people,My country     | 2019 |    158 | feature   | huaxia         | Huangjianxin    |
| Gone With the Wind       | 1938 |    238 | drama     | MGM            | Calvin Coolidge |
| Logan's run              | 1977 |    120 | drama     | MGM            | NULL            |
| Pretty Woman             | 1990 |    119 | drama     | Disney         | NULL            |
| Star Trek                | 1979 |   NULL | sciFic    | Paramount      | NULL            |
| Star Trek: Nemesis       | 2002 |    116 | sciFic    | Paramount      | NULL            |
| Star Wars                | 1977 |    124 | sciFic    | Fox            | George Lucas    |
| Terms of Endearment      | 1983 |    132 | drama     | MGM            | Calvin Coolidge |
| The Man Who Wasn't There | 2001 |    116 | comedy    | USA Entertainm | NULL            |
| The Usual Suspects       | 1995 |    106 | drama     | MGM            | NULL            |
+--------------------------+------+--------+-----------+----------------+-----------------+

在这个示例中可以看到，这里的 MovieExec 表与上一次不同，原因是直接使用上次的外表无法创建外键，因为存在值属于主字段但不属于外字段，不满足外键约束。

删除外键

1 2	ALTER TABLE Movies DROP CONSTRAINT {约束名}; -- ALTER TABLE Movies DROP CONSTRAINT foreignProducerC;

外键约束下行为

外键约束下有很强的约束，不满足约束将导致操作失败。另外，外键还有一些额外的行为。

创建外键时
- 约束
  - 主字段中元素必须均包含于外字段元素（外键约束）
  - 外字段必须已经创建索引，通常外字段是外表的主键
- 行为
  - 主字段自动创建索引
  - 创建外键约束
删除外键时
- 行为
  - 不会删除自动创建的主字段索引
  - 删除外键约束
在主表插入或修改记录
- 约束
  - 操作后将满足外键约束
在外表删除或修改记录
- 约束
  - 操作后将满足外键约束

索引

什么是索引

在使用查询语句时，常常使用 WHERE 子句进行筛选，但对于一般的字段，使用逐行判断的方法进行筛选，这种筛选效率低下。索引的本质是用于加速字段筛选的数据结构。字段使用索引后，可以在对数时间内查找需要的值。

通过创建索引，在数据量很大时有显著的查询效率提升，但在频繁插入和修改时，效率下降明显。

为什么索引的数据结构是 B+ 树

刚刚说了索引是一种数据结构，那么为什么选择 B+ 树？我们要先从 B 树讲起。

B 树是一棵多路搜索平衡树，每个节点有多个值和多个子节点。与二叉树不同的是，B 树通过在同一节点存放多个值，拥有更小的树深。虽然 B 树在一个节点存放多个值弱化了二叉搜索，但连续存储使得内存访问更加高效（局部性原理）。数据库的数据存储在磁盘中，与内存不同的是，磁盘中的 IO 操作远比内存中的 IO 慢，此时局部性原理显得比二叉搜索更为重要。B 树具有阶的概念，即在一个节点最多存放几个值，其实就是对局部性原理与二叉搜索性质的权衡。下图是一棵 B 树：

B 树

B+ 树是对 B 树的优化，在 B 树中使用连续访问相对低效且不稳定，B+ 树即在 B 树的基础上建立后继索引，当进行区间询问时，效率就会有显著提升。B+ 树中的阶数由系统分页大小决定，例如分页大小为 16KB 时，阶数通常设置为 1000 左右。下图是一棵 B+ 树：

B+ 树

创建和删除索引

创建索引

1	CREATE INDEX {索引名} ON {表名}({字段名});

如果要对 BLOB / TEXT 等变长类型创建索引，必须指定用于创建索引的长度。可以对联合键创建索引。

下面是使用索引提速的示例：

1	SELECT COUNT(*) FROM test;

1	SELECT * FROM test WHERE name="zhaoliu";

1	CREATE INDEX indexName ON test(name(4));

1	SELECT * FROM test WHERE name="zhaoliu";

+----------+
| COUNT(*) |
+----------+
|   511232 |
+----------+
1 row in set (0.05 sec)

+-------+---------+------+
| id    | name    | age  |
+-------+---------+------+
| 23435 | zhaoliu |   35 |
| 23436 | zhaoliu |   44 |
| 23890 | zhaoliu |   35 |
+-------+---------+------+
3 rows in set (0.12 sec)

1 2	Query OK, 0 rows affected (1.78 sec) Records: 0 Duplicates: 0 Warnings: 0

+-------+---------+------+
| id    | name    | age  |
+-------+---------+------+
| 23435 | zhaoliu |   35 |
| 23436 | zhaoliu |   44 |
| 23890 | zhaoliu |   35 |
+-------+---------+------+
3 rows in set (0.00 sec)

可以看到在这个 50000 的测试集中，使用索引将其从 0.12 秒提速到了几乎 0 秒，在更大的数据集中效果显著。

删除索引

1 2	DROP INDEX {索引名} ON {表名}; -- DROP INDEX indexName ON test;

适用索引的场景

索引并非可以盲目使用，索引适用的字段必须是：

数据集大，索引是比较复杂的数据结构，当数据集小时，没必要使用索引。
查询频繁，索引本身用于提速查询，查询越频繁使用索引的价值就越大。
很少修改，索引提速查询的代价是，修改时开销巨大。

在上面的示例中，我们也可以看到创建索引非常慢，就是因为修改操作代价高昂。当数据集足够大（一般在 $10^6$ 级别）时，用户应该在查询和修改频率中权衡是否使用索引。

自动创建索引的场景

一些场景下会自动创建索引：

创建主键时，自动创建索引。删除主键时自动删除索引。
创建外键时，自动创建索引。但删除时不会自动删除索引。
创建外键时，强制外字段已创建索引。