23、MySQL 教程 - MySQL 索引和关系

本篇来介绍下Mysql中的索引和关系，关系主要有一对一和一对多以及多对多关系。

索引

前面在删除唯一键的部分提到了索引。几乎所有的索引都是建立在字段之上，所以索引也属于列属性。索引的定义是：系统根据某种算法，将已有的数据（未来可能新增的数据）单独建立一个文件，这个文件能够实现快速的匹配数据，并且能够快速的找到对应的表中的记录。

索引的意义：

1、 提升查询数据的效率；

2、 约束数据的有效性（唯一性等）；

增加索引是有前提条件的：索引本身会产生索引文件（有时候有可能比数据文件还大），会非常耗费磁盘空间。如果某个字段需要作为查询的条件经常使用，那么使用索引。或者如果某个字段需要进行数据的有效性约束，也可能使用索引。

Mysql提供了多种索引。

1、 主键索引：primarykey；

2、 唯一索引：uniquekey；

3、 全文索引：fulltextindex；

4、 普通索引：index；

全文索引：针对文章内部的关键字进行索引，全文索引最大的问题在于如何确定关键字。英文关键字很容易，英文单词与单词之间有空格。但是中午很难，中文没有空格，而且中文可以各种随意组合，中文的分词（sphinx）最难。索引涉及到SQL优化的问题，很复杂的课题，这里先放放。

下面了解数据库设计的一些规范，这里来讨论下关系。关系就是实体与实体之间的关系，我们将这种关系分为三种：一对一，一对多，多对多。一般我们指的关系是表与表之间关系。

一对一

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

下面用学生表来举例。

学生表：姓名，性别，年龄，身高，体重，婚姻状况，籍贯，家庭住址，紧急联系人。

表设计成以上这种形式，不是不可以，它确实符合要求。但是其中性别，年龄，体重生属于常用数据，但是婚姻状况，籍贯，住址，联系人属于不常用数据。如果每次查询都是查询所有数据，那么这些不常用数据会影响查询效率，实际这些不常用数据又不用。这种情况，解决方案就是，将常用数据和不常用数据分离，分离成两张表。

常用信息表

不常用信息表

如果两张表分成这样，那么问题就来，如果我们需要查询某个人的家庭住址，那么怎么查询呢？也就是两个表之间没有关联关系。解决办法就是，让不常用信息和常用信息一定能够对应得上，找一个具有唯一性的字段来共同连接两张表。通过分析和观察，我们可以拿ID这个字段来将两张表共同连接起来。

所以不常用信息表可以改成这样。

因为id是主键，是唯一的（上面表中P表示主键，以下也是P代表主键），这样两张表就有了关系，在常用表中的一条记录，永远只能在不常用表中匹配一条记录，反过来，在不常用表中一条记录，永远只能在常用表中匹配一条记录，这个关系就是一对一。

一对多（多对一）

定义：一张表中有一条记录可以对应另外一张表中的多条记录，但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多，或者多对一。

下面通过一个国家有多个省份来举例一对多的关系

国家表

省份表

以上关系，一个国家可以在省份表中找到多条记录（也有可能是一条），但是一个省份表永远只能找到一个国家。这就是典型的一对多关系。但是以上设计，解决了实体的设计表问题，但是没有解决关系问题，省份找不出国家，国家找不到省份。

解决方案：在某一张表中增加一个字，能够找到另外一张表的中的记录。应该在省份表中增加一个字段指向国家表，因为省份表的记录只能匹配到一条国家记录。

修改后的省份表如下

多对多

定义：一张表（A）中的一条记录能够对应另外一张表（B）中的多条记录，同时B表中的一条记录也能在对应A表中的多条记录，这就是多对多关心。

以下通过教师教学，老师和学生表来设计举例。

老师表

学生表

以上设计方案实现了实体的设计，但是没有维护实体之间的关系。一个老师教过多个学生，一个学生也被多个老师教过，这种场景以上表就体现不了。

解决方案：不管在哪张表增加字段，都会出现一个问题，该字段要保存多个数据，而且是与气体表有关系的字段，不符合表设计规范，这种情况，我们增加一个中间关系表。

具体设计如下：

老师表

学生表

中间关系表：

增加中间表之后，中间表与老师表形成一对多关系，而且中间关系表是多表。这样就能够唯一找到一表的关系。同样的学生表与中间表也是一对多的关系。

学生找老师的过程：1 找出学生id-> 2中间表寻找匹配记录（多条）-> 3老师表匹配（一条）

老师找学生过程： 1 找出学生id –> 2中间表寻找匹配记录（多条）-> 3学生表匹配（一条）

这样就从一对多，然后到多对一，从而就得到结果就是多对多的效果。这种多对多，在现实项目中最常见的就是电子商务网站上的订单和会员之间的关系。