mysql约束和索引

• 约束和索引

★★★第24.5章 约束和索引

★★★24.5.1 约束

• 关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用（由此产生了术语引用完整性（referential integrity））。

  正确地进行关系数据库设计，需要一种方法保证只在表中插入合法数据。例如，如果 Orders 表存储订单信息，OrderItems 表存储订单详细内容，应该保证 OrderItems 中引用的任何订单 ID 都存在于 Orders 中。类似地，在 Orders 表中引用的任意顾客必须存在于 Customers 表中。

  虽然可以在插入新行时进行检查（在另一个表上执行 SELECT，以保证所有值合法并存在），但最好不要这样做，原因如下。

  • 如果在客户端层面上实施数据库完整性规则，则每个客户端都要被迫实施这些规则，一定会有一些客户端不实施这些规则。
  • 在执行 UPDATE 和 DELETE 操作时，也必须实施这些规则。
  • 执行客户端检查是非常耗时的，而 DBMS 执行这些检查会相对高效。

• 约束（constraint）：管理如何插入或处理数据库数据的规则。

• DBMS 通过在数据库表上施加约束来实施引用完整性。在 MySQL 中，约束包括：

  1. NOT NULL -> 非空，一般来说，将 NOT NULL 和 DEFAULT 组合使用，是一种设计习惯
  2. UNIQUE -> 唯一
  3. PRIMARY KEY -> 主键
  4. FOREIGN KEY -> 外键
  5. CHECK -> 检查

  五种。

24.5.1.1 主键PRIMARY KEY

• 主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。表中任意列只要满足以下条件，都可以用于主键：

  • 任意两行的主键值都不相同。
  • 每行都具有一个主键值（即列中不允许 NULL 值）。
  • 包含主键值的列从不修改或更新。
  • 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

• 主键的基本使用：在创建表时，在字段后面加上 PRIMARY KEY 关键字：

  字段名 字段数据类型 PRIMARY KEY

• 主键的使用细节：

  1. PRIMARY KEY 不能重复而且不能为 NULL。

  2. 一张表最多只能有一个主键，但可以是复合主键，例如：

     CREATE TABLE test_table(
     	id INT NOT NULL AUTO_INCREMENT,
     	`name` VARCHAR(32) NOT NULL,
     	job VARCHAR(255),
     	PRIMARY KEY(id, `name`)		-- 复合主键
     )ENGINE INNODB;

  3. 主键的指定方式有两种：

     • 直接在字段名后指定：字段名 PRIMARY KEY。
     • 在表定义最后写 PRIMARY KEY(列名)。

  4. 使用 DESC 表名，可以看到 PRIMARY KEY 的情况。

  5. 在实际开发中，每个表往往都会设计有主键。

24.5.1.2 外键FOREIGN KEY

• 外键是表中的一列，其值必须列在另一表的主键中。外键用于定义主表和从表之间的关系，是保证引用完整性的极其重要部分。外键约束要定义在从表上，主表则必须具有主键约束或者是 UNIQUE 约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或者为 NULL。

• 外键的基本语法：

  FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或UNIQUE字段名)

• 我们举个例子来理解外键：

  • Orders 表将录入到系统的每个订单作为一行包含其中。顾客信息存储在Customers 表中。Orders 表中的订单通过顾客 ID 与 Customers 表中的特定行相关联。顾客 ID 为 Customers 表的主键，每个顾客都有唯一的ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。Orders 表中顾客 ID 列的值不一定是唯一的。如果某个顾客有多个订单，则有多个行具有相同的顾客 ID（虽然每个订单都有不同的订单号）。同时，Orders 表中顾客 ID 列的合法值为 Customers 表中顾客的 ID。

  这就是外键的作用。在这个例子中，在 Orders 的顾客 ID 列上定义了一个外键，因此该列只能接受 Customers 表的主键值。

• 外键有助防止意外删除：在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。

  有的 DBMS 支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从 Customers 表中删除某个顾客，则任何关联的订单行也会被自动删除。

  

  

  

• 外键的使用细节：

  1. 外键指向的表的字段，要求是 PRIMARY KEY 或者是 UNIQUE。
  2. 表的引擎类型要是 InnoDB，这样的表才支持外键。
  3. 外键字段的类型要和主键字段的类型一致（长度可不同）。
  4. **外键字段的值，必须在主键字段中出现过，或者为 NULL [前提是外键字段允许NULL]**。
  5. 一旦建立主外键的关系，数据不能随意删除了。

  第四点和第五点可以总结为：主表记录不能乱删（要先把从表中相关记录删完，才能删），从表记录不能乱加（主表中要有相应的相关记录）。

24.5.1.3 唯一约束UNIQUE

• 唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

  • 表可包含多个唯一约束 UNIQUE ，但每个表只允许一个主键。

  • 如果没有指定 NOT NULL，则唯一约束列可包含 NULL 值，且可以有多个 NULL 值。

    如果一个列指定为 UNIQUE NOT NULL，则其使用效果类似于 PRIMARY KEY。

  • 唯一约束列可修改或更新。

  • 唯一约束列的值可重复使用。

  • 与主键不一样，唯一约束不能用来定义外键。

  employees 表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员 ID（主键）。雇员 ID 是主键，可以确定它是唯一的。你可能还想使 DBMS 保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义 UNIQUE 约束做到。唯一约束的语法类似于其他约束的语法。唯一约束既可以用 UNIQUE 关键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。

  

24.5.1.4 检查约束CHECK

• 检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

  • 检查最小或最大值。例如，防止 0 个物品的订单（即使 0 是合法的数）。
  • 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
  • 只允许特定的值。例如，在性别字段中只允许 M 或 F。

  换句话说，数据类型限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS 本身将会拒绝任何无效的数据。

• CHECK 的基本语法：在创建表时，在字段后面加上 CHECK 关键字及约束条件：列名 类型 CHECK (CHECK 条件)。或者如下图所示：

  

  

  

• 注意：mysql5.7 目前还不支持 CHECK，只做语法校验，但不会生效。

24.5.2 索引INDEX

• 索引用来排序数据以加快搜索和排序操作的速度。想像一本书后的索引（如本书后的索引），可以帮助你理解数据库的索引。

  假如要找出本书中所有的“数据类型”这个词，简单的办法是从第 1 页开始，浏览每一行。虽然这样做可以完成任务，但显然不是一种好的办法。浏览少数几页文字可能还行，但以这种方式浏览整部书就不可行了。随着要搜索的页数不断增加，找出所需词汇的时间也会增加。

  这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位置。为了搜索“数据类型”一词，可在索引中找出该词，确定它出现在哪些页中。然后再翻到这些页，找出“数据类型”一词。

  使索引有用的因素是什么？很简单，就是恰当的排序。找出书中词汇的困难不在于必须进行多少搜索，而在于书的内容没有按词汇排序。如果书的内容像字典一样排序，则索引没有必要（因此字典就没有索引）。

  数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。

  但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。

  解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

• 在开始创建索引前，应该记住以下内容。

  • 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
  • 索引数据可能要占用大量的存储空间。
  • 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
  • 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
  • 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

• 下面的语句在 Products 表的产品名列上创建一个简单的索引。

  CREATE INDEX prod_name_ind ON products (prod_name);

  索引必须唯一命名。这里的索引名 prod_name_ind 在关键字 CREATE INDEX 之后定义。ON 用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

• 注意：创建索引后，只对创建了索引的列有效。

25.5.2.1 索引的原理

1. 当我们没有索引时，在寻找符合 WHERE 过滤条件的记录时，要进行全表扫描寻找，查询速度慢。
2. 在对表中的某列创建索引时，会建立关于该索引列的数据结构，例如二叉搜索树、B树、B+树。
3. 索引的代价：
   • 在创建索引后，会增大表对磁盘存储空间的占用。
   • 同时会对 UPDATE、DELETE、INSERT 语句的效率产生影响，因为要对已经建立好的数据结构进行同步调整。

25.5.2.2 MySQL索引类型

• 索引的类型：
  1. 主键索引，主键自动的为主索引（类型 PRIMARY KEY）。-> 主键，同时也是索引（主键索引）
  2. 唯一索引（UNIQUE）。
  3. 普通索引（INDEX）。
  4. 全文索引（FULLTEXT）[适用于 MyISAM ]。
     • 开发中对全文搜索考虑使用：Solr 和 ElasticSearch (ES)，而不用 MySQL 自带的 FULLTEXT。

25.5.2.3 索引的使用

添加索引

1. CREATE [UNIQUE] INDEX index_name ON table_name (column_name); ：

   • 添加唯一索引，等同于在创建字段时添加 UNIQUE 关键字。

     如果某列的值是不会重复的，则优先考虑使用 UNIQUE 索引，否则使用普通索引。

     CREATE UNIQUE INDEX id_index ON t25 (id);

   • 添加普通索引：

     CREATE INDEX id_index ON t25 (id);

2. ALTER TABLE table_name ADD INDEX index_name (column_name);。

• 添加主键索引：ALTER TABLE table_name ADD PRIMARY KEY(列名...);

删除索引

• DROP INDEX index_name ON table_name;：

  DROP INDEX id_index ON t25;

• 删除主键索引：ALTER TABLE table_name DROP PRIMARY KEY;

修改索引

• 先删除，再添加新的索引。

查询索引

1. SHOW INDEXES FROM table_name;
2. SHOW INDEX FROM table_name;
3. SHOW KEYS FROM table_name;

• 在 SHOW INDEX，SHOW INDEXES 和 SHOW KEYS 之间没有区别。它们有类似的含义。

25.5.2.4 创建索引规则

1. 较频繁的作为查询条件字段应该创建索引。

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。

   select * from emp where sex = '男';

3. 更新非常频繁的字段不适合创建索引。

4. 不会出现在 WHERE 子句中的字段不该创建索引。