mysql-外键

mysql 外键在我个人的理解中是一个 DB 级别的提供数据一致性保障的工具。

实体完整性：

实体完整性(Entity integrity)是指关系的主关键字不能重复也不能取”空值”。一个关系对应现实世界中一个实体集。现实世界中的实体是可以相互区分、识别的，也即它们应具有某种惟一性标识。在关系模式中，以主关键字作为惟一性标识，而主关键字中的属性(称为主属性)不能取空值，否则，表明关系模式中存在着不可标识的实体(因空值是”不确定\”的)，这与现实世界的实际情况相矛盾，这样的实体就不是一个完整实体。按实体完整性规则要求，主属性不得取空值，如主关键字是多个属性的组合，则所有主属性均不得取空值。

参照完整性：

参照完整性(Referential Iintigrity)是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。关系数据库中通常都包含多个存在相互联系的关系，关系与关系之间的联系是通过公共属性来实现的。所谓公共属性，它是一个关系R(称为被参照关系或目标关系)的主关键字，同时又是另一关系K(称为参照关系)的外部关键字。如果参照关系K中外部关键字的取值，要么与被参照关系R中某元组主关键字的值相同，要么取空值，那么，在这两个关系间建立关联的主关键字和外部关键字引用，符合参照完整性规则要求。如果参照关系K的外部关键字也是其主关键字，根据实体完整性要求，主关键字不得取空值，因此，参照关系K外部关键字的取值实际上只能取相应被参照关系R中已经存在的主关键字值。

用户定义完整性：

实体完整性和参照完整性适用于任何关系型数据库系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束。用户定义完整性(user defined integrity)则是根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。这一约束机制一般不应由应用程序提供，而应有由关系模型提供定义并检验，用户定义完整性主要包括字段有效性约束和记录有效性。

外键就是在数据库中参照完整性的具体实现。

外键的定义：一个属性不是他所在关系的主键，但却是另外一个关系的主键。

mysql 中建立外键

在mysql数据库中有innodb engine 支持的不同表中是可以建立外键的，建立外键的表必须满足这几个条件：

两张表必须都是InnoDB表，并且它们没有临时表。
建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
建立外键关系的对应列必须建立了索引。(在可视化工具会自动建立)

现在有两个表：

CREATE TABLE `order` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `order_user` (`user_id`),
  CONSTRAINT `order_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE  CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

在order 表中有一个外键，此时 user 表是主表，order 是子表。(主表：外键中主键所在的表。子表：外建中非主键的属性所在的表)

建立外键：

1	CONSTRAINT `order_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

执行上面的语句后，此时在 user 和 order 建立了一种关系，当你没有把 order 中的 user_id 的属性设置为NULL时此时 order 中的每一行数据中的 user_id 的值只能是 user 表的 user_id的
子集。例如:如果 order.user_id = {1,2,3} 则 order.user_id 在插入数据的时候只能选择 1,2,3 这三个值。当子表试图插入一个在主表中对应的外键不存在的值时，子表会拒绝插入。

新建外键是可以选择这5个选线 DELETE CASCADE ON UPDATE CASCADE 当主表更新或删除存在于外键关系中的主键时，子表应该采取的动作，当然也可以不添加。此时共有5*5+1 种动作

CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
NO ACTION: InnoDB拒绝删除或者更新父表。
RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
SET DEFAULT: InnoDB目前不支持。

这里所指的更新或删除子表指的是只对外键关系中的属性起作用。

mysql 外键使用场景

外键约束是满足数据库参照完整性的

参照完整性(Referential Iintigrity)是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。

来假设一个情况：一个购物网站的数据库有这两张表(当然不止两张) user 和 order (订单)

mysql 建表语句如下:

CREATE TABLE `order` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `order_user` (`user_id`),
  CONSTRAINT `order_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

在order 中每一条订单都属于一位用户，用户在order 表中拥有多条记录，此时在order 中表中不可能出现一条记录的user_id 不在user的 user_id 的记录，如果这条记录的拥有者没有

存在与我的user 表中，它就不是我的用户，所以order 中不可能存在他的记录。这是一个很简单逻辑。在应用程序层次可以用一些不简单的代码实现。但是有没有想过如果你有数十个表每个表与其他表都有上述 user和order 这样的关系，你还能够驾驭他们在代码间的关系吗？当你的网站的程序员跳槽后，你能保证你新聘请的程序员不会对着这复杂和晦涩的逻辑嘶吼吗？

在这种情况下，在数据库层次添加外键约束，用一个统一的标准来表达上述的关系可以更加的有利于代码的维护和更新。

在 order 表中我们可以添加：

1 2	CONSTRAINT `order_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ONDELETE CASCADE ON UPDATE CASCADE

这样，在order 中和就不能添加user 中 user_id 不存在的值了，也就保证了order 和 user的参照完整性
ON DELETE CASCADE ON UPDATE CASCADE 指的是在主表也就是 user 表的user_id 发生update 或delete 时，子表order 的 user_id 发生update user_id那一行数据 delete，这里还有其他的一些动作。

使用外键约束，不但简化了业务逻辑还是一层对数据的完整性的保护，你一定不会想在你的order表中出现一条莫名其妙的订单吧，外键约束也在某种程度上保护了数据。在一些对数据一致性要求比较高的场景中，我们可以借助 DB 的外键来避免业务逻辑上的疏漏。

mysql-索引

索引的目的

在我个人的理解中，mysql 的索引在实际应用中是一种通过空间换取查询时间的方式，它是通过对选定列在持久化存储上建立一个单独的专门用来查询的数据结构来达到提升查询速度的目的。一般这个数据结构是 BTree，在 where 语句中对已经建立好索引的列查询时，mysql 的查询引擎会在索引上进行数据的查询操作，而不是在原有的列上扫描，从而提升了查询的速度。

索引的实现

索引解决的是查找的问题，一些数据结构如有序list ，二叉查找树，红黑树，BTree。mysql 使用了 Btree 作为索引的数据结构。在初始化索引时，调用引擎新建索引，每当该列有新的数据插入时，再更新索引，所以由此得出，索引比较适合一些读多写少的场景中，大量的写会对索引的更新产生压力。

索引的使用方式

流行的 Mysql 引擎有 MyISAM和InnoDB数据存储引擎，笔者主要对 InnoDB 有部分使用经验，对索引的建立，主要遵守最左前缀匹配规则即可，具体解释就是:

有一个表(stu),有三列 : px , uid , score ，在业务流程中可能会对 uid 和 score 同时使用where 查询时，可以考虑建立一个联合索引，索引包含两列 <uid,score> ，这个联合索引有最左匹配的性质，在以下两条 sql 中都可以起到加速查询的作用:

1 2	SELECT uid , score FROM stu WHERE score = 123 AND uid = 123 ; #mysql 会自己调整 where 的条件以达到使用索引的目的 SELECT uid , score FROM stu WHERE uid=123;

但是下面这条语句就无法使用索引

1	SELECT uid , score FROM stu WHERE score=123;

需要专门新建一个的索引