索引

1. 主键-外键

1.1 主键

对于关系表，有个很重要的约束，就是每条记录必须有一个唯一性标识，即通过某个字段可以区分出不同的记录，这个字段即被称为主键（primary key） 。

1. 选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

   如果我们以学生的身份证号作为主键，似乎能唯一定位记录，然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

2. 主键值也不能为NULL。

3. 一般我们把主键这个字段命名为id，常见的可作为id字段的类型有：

   • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
   • GUID 类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID 算法通过网卡 MAC 地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了 GUID 算法，可以自己预算出主键。

1.2 联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即将两个或更多的字段都设置为主键，这种主键被称为联合主键。

显然，对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

id_num	id_type	other columns
1	A	...
2	A	...
2	B	...

1. 如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的 3 条记录都是允许的，因为没有两列主键组合起来是相同的。
2. 没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

1.3 外键

1.3.1 概念说明

外键是某个表中的一列，它包含另一个表的主键值，定义了两个表的关系。

如，当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：

id	name	other columns
1	小明	...
2	小红	...

我们还可以在classes表中确定任意一个班级记录：

id	name	other columns
1	一班	...
2	二班	...

但是我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应，这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

id	class_id	name	other columns
1	1	小明	...
2	1	小红	...
5	2	小白	...

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

1.3.2 逻辑实现

外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的 。

2. 索引

2.1 概念引入

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。索引在 MySQL 中也叫作键（key）。

通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。例如，对于students表：

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95
3	1	小军	M	88

如果要经常根据score列进行查询，就可以对score列创建索引：

ALTER TABLE students
ADD INDEX idx_score (score);

• 使用ADD INDEX idx_score (score)就创建了一个名称为idx_score、使用列score的索引。

• 可以对一张表创建多个索引，其中索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

  ALTER TABLE students
  ADD INDEX idx_name_score (name, score);

2.2 索引的特性

索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

索引提高查询效率的原因：

• 大大减少了服务器需要扫描的数据量。
• 帮助服务器避免排序和临时表。
• 将随机 I/O 变成了顺序 I/O。

索引的效率取决于索引列的值是否散列：

• 该列的值如果越互不相同，那么索引效率越高。
• 反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

2.3 索引的类型

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。

2.3.1 B-tree 索引

当人们谈论索引的时候，如果没有特别指定类型，那么多半说的是 B-tree 索引，它使用 B-tree 数据结构来存储数据（实际上，很多存储引擎实际使用的是 B+ tree 索引，例如 InnoDB，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便遍历叶子节点的范围）。

B-tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-tree 是按照索引列中的数据大小顺序存储的，所以很适合按照范围来查询。例如，在一个基于文本列的索引树上遍历，按字母顺序传递连续的值进行范围查找是非常合适的，所以，像“找出所有以 I 到 K 开头的名字”这样的查找效率会非常高。

请注意，索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。

InnoDB 存储引擎有一个被称为自适应哈希索引的特性：当 InnoDB 发现某些索引值被非常频繁地访问时，它会在原有的 B-tree 索引之上，在内存中再构建一个哈希索引。这就是 B-tree 索引也具备了一些哈希索引的优势，例如，可以实现非常快速的哈希查找。这个过程是完全自动化的，用户无法进行控制或者配置，不过，可以通过参数彻底关闭自适应哈希索引这个特性。

B-tree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。细究而言，可以使用 B-tree 索引的查询类型：

• 全值匹配：指和索引中的所有列匹配。
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列而范围匹配另一列
• 只访问索引的查询

因为索引树中的节点是有序的，所以除了按值查找，索引还可以用于查询中的 ORDER BY 操作（按顺序查找）。

下面是一些关于 B-tree 索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引；
• 不能跳过索引中的列；
• 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查找。

2.3.2 全文索引

FULLTEXT 是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

全文索引和其他几类索引的匹配方式完全不一样，它有许多需要注意的细节，如停用词、词干、复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的 WHERE 条件匹配。

2.4 高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。

2.4.1 前缀索引和索引的选择性

有时候为了提升索引的性能，同时也节省索引空间，可以只对字段值的前一部分字符进行索引，这样做的缺点是会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。索引的选择性越高，则查询的效率越高。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

一般情况下，列前缀的选择性也是足够高的，足以满足查询性能。对于 BLOB, TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 并不支持对这些列的完整内容进行索引。

前缀索引的创建：

ALTER TABLE sakila.city_demo ADD KEY (city(7));

前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点：MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 操作，也无法使用前缀索引做覆盖扫描。

2.4.2 多列索引

多列索引并不是为每列创建独立的索引，这在大部分情况下并不能提高 MySQL 的查询性能。MySQL 引入了一种叫“索引合并”（index merge）的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行，在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。

索引合并有三个变种：

• OR 条件的联合
• AND 条件的相交
• OR 和 AND 的同时组合

通过 EXPLAIN 中的 Extra 列可以看出索引合并的存在：

mysql> EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
    -> WHERE actor_id = 1 OR film_id = 1\G
**************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: film_actor
 partitions: NULL
 type: index_merge
 possible_keys: PRIMARY,idx_fk_film_id
 key: PRIMARY,idx_fk_film_id
 key_len: 2,2
 ref: NULL
 rows: 29
 filtered: 100.00
 Extra: Using union(PRIMARY,idx_fk_film_id); Using where

然而，索引合并策略有时候效果非常不错，但更多的时候，它说明了表中的索引建得很糟糕。如果在 EXPLAIN 中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的。

回到多列索引本身，必须要清楚，多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个重要的经验法则：将选择性最高的列放到索引最前列。

2.4.3 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚簇索引时，它的数据行实际上放在索引的叶子页（leaf page）中，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

有些数据库服务器允许你选择用于聚簇的索引，但是 MySQL 内置的存储引擎都不支持这个特性。InnoDB 根据主键聚族索引。

聚簇索引有一些重要的优点：

• 你可以把相互关联的数据保存在一起：如，在实现电子邮箱应用时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可以导致一次磁盘 I/O。
• 数据访问更快：聚簇索引将索引和数据保存在同一个 B-tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

同时，聚簇索引也有一些缺点：

• 聚簇数据最大限度地提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序：按照主键的顺序插入行是将数据加载到 InnoDB 表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为它会强制 InnoDB 将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

2.4.4 覆盖索引

大家通常都会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单是 WHERE 条件部分。索引的确是一种高效找到数据的方式，但是如果 MySQL 还可以使用索引直接获取列的数据，这样就不再需要读取数据行了。

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。需要注意的是，只有 B-tree 索引可以用于覆盖索引。

覆盖索引是非常有用的工具，这样的查询只需要扫描索引而无须回表，能够极大地提高性能。

当执行一个被索引覆盖的查询（也叫作索引覆盖查询）时，在 EXPLAIN 的 Extra 列可以看到Using index的信息。

例如，表 sakila.inventory 有一个多列索引（store_id,film_id），MySQL 如果只需要访问这两列，就可以使用这个索引做覆盖索引：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
***************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: inventory
 partitions: NULL
 type: index
 possible_keys: NULL
 key: idx_store_id_film_id
 key_len: 3
 ref: NULL
 rows: 4581
 filtered: 100.00
 Extra: Using index

2.4.5 使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：

• 通过排序操作。
• 按索引顺序扫描：如果在 EXPLAIN 的输出结果中，type 列的值为 index，则说明 MySQL 使用了索引扫描来做排序（注意不要和 Extra 列的 Using index 搞混）。

MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两项任务，这样是最好的。

只有当索引的顺序和 ORDER BY 字句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能使用索引来对结果做排序。如果查询需要联接多张表，则只有当 ORDER BY 子句引用的字段全部在第一个表中时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL 需要执行排序操作，而无法利用索引排序。

2.4.6 避免重复和冗余索引

不幸的是，MySQL 允许在相同列上创建多个相同的索引，虽然其会抛出一个警告，但它并不会阻止你这么做。

这里所言的重复索引是指在相同的列上按照相同顺序创建的相同类型的索引，应该避免创建这样的重复索引，发现以后应该立即移除。

• 示例：

  CREATE TABLE test (
      ID INT NOT NULL PRIMARY KEY,
      A INT NOT NULL,
      B INT NOT NULL,
      UNIQUE(ID),
      INDEX(ID)
  ) ENGINE=InnoDB;

• 说明：上述代码可以是想创建一个主要，先加上唯一限制，然后再加上索引以供查询使用。而事实上，MySQL 的唯一限制和主键限制都是通过索引实现的，因此，上述写法实际上在相同的列上合建了三个重复的索引。

这里所言的冗余索引是指，如果创建了索引 (A, B)，再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引。大部分情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引，但有时候出于性能方面的考虑也需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

2.5 维护索引和表

找到并修复损坏的表：

• 损坏的索引会导致查询返回错误的结果或者出现莫须有的主键冲突等问题。
• 可以使用 REPAIR TABLE 命令来修复损坏的表。但同样不是所有的存储引擎都支持该命令，如果存储引擎不支持，可通过一个不做任何操作的 ALTER 操作来重建表，例如，把表的存储引擎修改为当前的引擎 ALTER TABLE <table> ENGINE=INNODB;

更新索引统计信息：

• 可以使用 SHOW INDEX FROM 命令来查看索引的基数（cardinality）：

  mysql> SHOW INDEX FROM sakila.actor\G
  **************************** 1. row ****************************
   Table: actor
   Non_unique: 0
   Key_name: PRIMARY
   Seq_in_index: 1
   Column_name: actor_id
   Collation: A
   Cardinality: 200
   Sub_part: NULL
   Packed: NULL
   Null:
   Index_type: BTREE
   Comment:
  **************************** 2. row ****************************
   Table: actor
   Non_unique: 1
   Key_name: idx_actor_last_name
   Seq_in_index: 1
   Column_name: last_name
   Collation: A
   Cardinality: 200
   Sub_part: NULL
   Packed: NULL
   Null:
   Index_type: BTREE
   Comment:

减少索引和数据的碎片：

• B-tree 索引可能会产生碎片化，这会降低查询的效率。
• 表的数据存储也可能分生碎片化，有三种类型的数据碎片：
  • 行碎片（Row fragmentation）：指数据行被存储在多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
  • 行间碎片（Intra-row fragmentation）：指逻辑上顺序的页或者行，在磁盘上不是顺序存储的。行间碎片对诸如全表索和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
  • 剩余空间碎片（Free space fragmentation）：指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

2.6 唯一性索引 vs 唯一性约束

某一列是索引，但并不意味着该列的值是不能重复的。

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。

例如，我们假设students表的name不能重复，通过下面的UNIQUE关键字我们就添加了一个唯一索引：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

也可以只对某一列添加一个唯一约束而不创建唯一索引，下面这种情况，name列没有索引，但仍然具有唯一性保证。

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

3. 约束

一张表中的主键一定是唯一的，但约束可以有多个。

约束：

• Default约束
• Check约束
• Unique约束