mysql表的设计和关联问题

这个问题问的好，要弄一个表很容易，关键是表设计出来是否合理！

如果表设计的好，则会相当清晰，易于理解，后续开发上事半功倍，维护也方便；如果设计的不好，则难以理解，维护困难，代价大。

表与表之间的关系有三种：1.一对一，2.一对多，3.多对多

一对一的表，两表的属性实际上完全可以合并成一个表，共用一个主键即可；

一对多的表，可以设中间关联表，也可以将关联表并入“多”这头；若设独立关联表，则可引入“多”这头的主键作为其主键，也可另立主键，并将“一”和“多”两表的主键作为关联表的外键；

多对多的表，则必须设中间关联表，关联表设独立主键，并引入两个“多”头的表的主键作为关联表的外键。

这是上述三种关系表在键处理上的基本原则。

范式还是要遵循的，这套理论还是科学合理的。不要相信反范式设计，反范式设计在规模庞大时，数据冗余多，编码及维护会变得困难，万一考虑漏掉的将导致数据不一致，甚至酿成灾难。严格按照范式理论来设计数据库，将使你编码及维护时少 *** 很多心。

一般来说，先进行需求分析，然后画出数据流图，然后再根据数据流图画出ER图，然后再根据ER图创建各种表。表是根据ER图来创建的，表设计的合不合理，关键是ER图抽像的合不合理。在抽像ER图时，一般遵循这样的原则：

能用1对1的，就不用1对多；能用1对多的，就不用多对多，往简单化方向靠；

能当属性处理的，尽量当属性，而不是当实体处理去另立新表，这样可使问题简化。

把意义相近联系紧密的属性放在一张表内，而不是拆在多张表中。

看了一下你上述几张表，我认为不合理，户主是人，家庭成员也是人，把他们分在户主表和家庭成员表中不合理，他们是同一类的，宜合在一张家庭成员表中，并增加一个标志性字段，以指明哪个人是户主。另外，宜建立一张地址表，以取代户主表，地址表中宜指明乡场镇、村巷道、几区、门牌号等与地址关系紧密的属性，把户籍、联系方式、户主等字段拿走，他们不是地址属性，这几个宜放在成员关系表中，户籍是人的属性，并非地址的属性，联系方式就更明显了，要联系的是人，而不是地址。

很明显，地址和家庭成员是一对多关系，一个地址同时可以住着多个成员，而一个成员同时只能住一个地址，这样，设计成地址表和家庭成员表之后，要在家庭成员表中再加一个地址外键字段，把地址表的主键当作家庭成员表的外键填入，这样，成员表中的每个人都可以通过地址外键字段到地址表中找到其所住地址。另外，成员表中也指明了哪个人是户主，也指明了每个人的户籍和联系方式，这些信息你都可以找得到。

最左原则顾名思义就是从最左边开始匹配的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，其针对的是组合索引（又名联合索引）。

假设我们现在对A、B、C三个字段建立组合索引，来剖析什么时候会用到索引。

A=nickName, B=groupId, C=sign。

首先来看下我的表结构和索引如图1-2，在这里就不作过多赘述。

我先来介绍下图3中sql在expalin执行计划后得一些参数。

id： 为选择标识符。

select_type： 表示查询的类型，SIMPLE表示简单的select,没有union和子查询。

table： 输出结果集的表。

partitions： 匹配的分区。

type： 表示表的连接类型，range是指给定范围内的检索，比如 in(xx, xx) 或者 between。该类最好的时候是const（即表示为通过索引一次就找到了），最差的时候是all（需要遍历全表）。

possible_keys： 表示查询时，可能使用的索引。（显示可能应用在这张表中的索引，不一定能应用到。）

key： 表示实际使用的索引。

key_len： 索引字段的长度。

ref： 列与索引的比较。

rows： 找到所需的记录所需要读取的行数。

filtered： 按表条件过滤的行百分比。

Extra： 执行情况的描述和说明。

由图3的key字段看出A、B、C下，我们使用上了nickName_id_sign联合索引，rows字段看出，读取了1行。

那我们来看看B、C、A与C、B、A呢？

图4-5看出B、C、A与C、B、A也用到了索引，为什么呢？

这是因为当客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。这个解析的工作是在服务器端所进行的，解析动作又可分为很多小动作。其中最重要的一步就是确定最佳执行计划。服务器进程会根据一定的规则，对这条语句进行优化。（在执行计划开始之前会有一步查询转换，如：视图合并、子查询解嵌套、谓语前推及物化视图重写查询等。【此处不理解可以忽略，大概可以理解为优化器寻找最低成本的执行计划】）。最终确定可能的最低成本的执行计划。当服务器进程的优化器确定这条查询语句的最佳执行计划后， 就会将这条SQL语句与执行计划保存到数据高速缓存，提高SQL语句处理效率。

在mysql中会使用Index Merge intersection algorithm算法来调整条件子句顺序(可以理解为上面所表述的 寻找最低成本的执行计划 )，详情请看 官方文档 。

由图6可以看出A、B组合也使用到了索引，看到type值为ref（非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。），再看rows，为40条，比A、B、C下略多，但还好，可以接受。

由图7可以看出A、C组合也使用到了索引，可以看到key_len索引中使用的字节数比A、B、C与A、B下小得多，再看rows，为七万多条， filtered 的过滤条件仅为百分之十，这是因为A、B、C组合索引覆盖了（A）、（A，B）、（A，B，C）三个索引 点击查看官方文档 ，而A、C组合用到了A索引，我们来看图8只有A条件nickName下与图7的rows参数都一致。filtered参数不一致是因为where的后置条件决定其过滤比例的。

图9可看出B、C组合下没有用到索引，type类型为ALL遍历整表去寻找记录， possible_keys与key 都为空，rows几乎为全表记录。这是因为组合索引的最左匹配原则，mysql会根据A来确定下一步的搜索方向，当没有A时，就只能去全记录去寻找。

有什么问题请留言，大家一起探讨学习