MySQL数据库表的设计和优化（下）

二、基于单表设计的多表设计原则：
（1）表关系：
一）一对一关系：
        定义：
        在这种关系中，关系表的每一边都只能存在一个记录。每个数据表中的关键字在对应的关系表中只能存在一个记录或者没有对应的记录。这种关系和一对配偶之间的关系非常相似——要么你已经结婚，你和你的配偶只能有一个配偶，要么你没有结婚没有配偶。大多数的一对一的关系都是某种商业规则约束的结果，而不是按照数据的自然属性来得到的。如果没有这些规则的约束，你通常可以把两个数据表合并进一个数据表，而且不会打破任何规范化的规则。
        一对一关系又分为：一对一外键关联和一对一主键关联。
        一对一主键关联：要求两个表的主键必须完全一致，通过两个表的主键建立关联关系。
        可以看到下图，很明显的，班级编号作为主键的话，就是一个主键关联了。

                        

 

        一对一外键关联：
        下面又很明显看到，以班主任ID作为外键关联起来的一个表。
            

二）一对多关系（多对一）：
        定义：
        主键数据表中只能含有一个记录，而在其关系表中这条记录可以与一个或者多个记录相关，也可以没有记录与之相关。这种关系类似于你和你的父母之间的关系。你只有一位母亲，但是你母亲可以有几个孩子。
        下图可以看到：一对多-班级表有多个学生；多对一-多个学生属于一个班级。

                     

   
    三）多对多关系：
        定义：
        两个数据表里的每条记录都可以和另一个数据表里任意数量的记录（或者没有记录）相关。例如，如果你有多个兄弟姐妹，这对你的兄弟姐妹也是一样（有多个兄弟姐妹），多对多这种关系需要引入第三个数据表，这种数据表称为联系表或者连接表，因为关系型系统不能直接实现这种关系。

        在RDBMS中，必须使用中间表来表示多对多的关系。中间表我们可以分成两种，一种是纯粹表示关系的中间表，一种是表示中间实体的中间表。

        纯粹表示关系的中间表很简单，只需要两列：AID和BID，AID以外键关联到A表的主键，BID以外键关联到B表的主键，然后这两个列组成联合主键。这个中间表纯粹是表示多对多关系而存在，在业务上不会有对应的实体与之对应。比如前面提到的学生和课程的关系，如果我们只需要知道哪些学生上哪些课，哪些课有哪些学生选，不需要有更多的信息的情况下，我们就可以建立“学生课程”中间表，里面只有学生ID和课程ID两个字段。

             

        中间实体是在纯粹的中间关系表的基础上，加上了更多的属性，从而形成了一个新的实体。比如上面提到的学生和课程的关系，如果我们需要记录学生选课的时间、学生选择这门课程后的考试成绩，那么我们就像建立一个“选课”实体，
        该实体具有如下属性：
        选课ID，主键
        学生ID，与学生表做外键关联
        课程ID，与课程表做外键关联
        选课时间，DateTime类型
        考试成绩，记录选修该课程后考试的最终成绩

        注意：
        一）外键与索引：
        外键是一种约束，与索引的概念不一样，只是大多数情况下，我们建立外键时，都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查，如果不满足外键约束，则禁止数据的插入或修改，这必然带来一个问题，就是在数据量特别大的情况下，每一次约束检查必然导致性能的下降。索引其实也有类似的问题，索引如果建多了，那么在插入删除修改数据时也要去维护对应的索引，所以索引的存在也会导致数据操作变慢。
        不过外键与索引的优点不同，外键只是保证数据的一致性，并不能给系统性能带来任何好处，所以由于外键导致的插入数据变慢会随着数据量的增长而越来越严重。而索引的目的是为了检索数据更快，维护数据时导致的索引数据的变更，对性能的影响不会像外键那样随着数据量增长而变得严重（当然大数量时的索引树维护会比小数据量的索引树维护更麻烦，但至少不是像外键那样）。
        出于性能的考虑，如果我们的系统完全由我们开发的程序使用，而不需要提供数据库给其他应用系统写入数据，而且对性能要求较高，那么我们可以考虑在生产环境中不使用外键，只需要建立能够提高性能的索引。由于整个数据库的操作都是由我们开发的程序来完成的，所以我们程序可以在开发过程中做好各方面的一致性检查，保证操作的数据是满足外键约束的，而不需要真正的存在这样一个外键约束。怎么做到这一点呢，首先，我们在建立数据库时有多个脚本，包括创建表、创建初始化数据、创建索引、创建外键等，我们在开发和测试环境中，都把这些脚本运行了，以使开发测试环境中的数据库是完整的，经过大量测试保证应用程序能够维护数据之间的约束的情况下，那么我们在生产时，并不需要运行创建外键这个脚本文件，只需要创建表、初始化数据、创建索引等即可。
        二）建立关系
        在开始着手考虑建立关系表之间的关系之前，你可能需要对数据非常熟悉。只有在熟悉数据之后，关联会比你刚开始的时候更明显。你的数据库系统依赖于在两个数据表中找到的匹配值来建立关系。
        进行匹配的值都是主键和外键的值。（关系模型不要求一个关系必须对应的使用一个主键来确定。你可以使用数据表中的任何备选关键字来建立关系，但是使用主键是大家都已经接受的标准。）主键（primary key）唯一的识别表中的每个记录。而外键（foreign key）只是简单的将一个数据表中的主键存放在另外一个数据表中。同样地，对于你来说也不需要做太多的工作——只是简单地将主键加到关系表中，并将其定义为外键。

（2）分表原则:
分表主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。 同时分表分库等思想也将引出以后的数据库集群，主从复制、读写分离方案…

为什么我们要分表分区？？？
日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

（一）表拆分方式：
    1）垂直切分：
 
        定义：
        把主键和一些数据表的列放在一个表中，然后把主键和另一些数据表的列放在一个表中。 
        如果一个表的某些列常用，另一些不常用，则可以采用垂直拆分。垂直拆分可以使数据行变小，一个数据页就可以存放更多的数据，在查询时候可以减少I/O次数。其缺点是需要管理冗余列，查询所有数据时候需要join查找。
        优点：
        使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)。
        可以达到最大化利用Cache的目的。

        缺点：
        表垂直分割后，主码(主键)出现冗余，需要管理冗余列
        会引起表连接JOIN操作（增加CPU开销）需要从业务上规避

    2）水平拆分（分表，分区）–按表中某一字段值的范围划分： 


        定义：
        根据列的范围值进行合理切分，放在多个独立的表或分区中。

        适用场景：
        表很大，分割后可以降低查询时候需要读取的数据和索引的页数，同时降低索引的层数，提高查询速度。
        表中的数据是独立的，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另一些数据不常用。
        需要把数据放在多个存储介质上。
        需要把历史数据和当前的数据拆分开。

        例子：
        当伴随着某一个表的数据量越来越大，以至于不能承受的时候，就需要对它进行进一步的切分。一种选择是根据key 的范围来做切分，譬如ID 为 1-10000的放到表A上，ID 为10000~20000的放到表B。这样的扩展就是可预见的。另一种是根据某一字段值来划分，譬如根据用户名的首字母，如果是A-D，就属于表A，E-H就属于表B。这样做也存在不均衡性，当某个范围超出了单点所能承受的范围就需要继续切分。还有按日期切分等等。

        可以使用Mrg_Myisam引擎实现水平分表。
        优点：
        单表大小可控，天然水平扩展。降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，加快了查询速度。

        缺点：
        无法解决集中写入瓶颈的问题。同时，水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。

    3）散列库表（基于hash算法的切分）：
        定义：
        表散列与水平分割相似，但没有水平分割那样的明显分割界限，采用Hash算法把数据分散到各个分表中, 这样IO更加均衡。一般采用mod来切分，一开始确定切分数据库的个数，通过hash取模来决定使用哪台。这种方法能够平均地来分配数据，但是伴随着数据量的增大，需要进行扩展的时候，这种方式无法做到在线扩容。每增加节点的时候，就需要对hash 算法重新运算。
        我们会按照业务或者功能模块将数据库进行分离，不同的模块对应不同的数据库或者表，再按照一定的策略对某个页面或者功能进行更小的数据库散列，比如用户表，按照用户ID进行表散列，散列128张表，则应就能够低成本的提升系统的性能并且有很好的扩展性

        优点：
        数据分布均匀

        缺点：
        数据迁移的时候麻烦，不能按照机器性能分摊数据

（二）在了解完分表了，我们先来理解区分分区与分表吧。
    分区：
    定义：
    分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

    分表定义：
    分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

    mysql分表和分区有什么联系呢？：
    1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。
    2.分表和分区不矛盾，可以相互配合
    ，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

    3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
    4.表分区相对于分表，操作方便，不需要创建子表。
（三）表拆分建议：（针对大系统）
    其实这点没有明确的判断标准，比较依赖实际业务情况和经验判断。一般MySQL单表1000W左右的数据是没有问题的（前提是应用系统和数据库等层面设计和优化的比较好）。

    1）对记录多的表进行拆分。（几百-上千万级别的表）
    2）需要拆分的表分为动态表和相对静态表。动态表拆分到不同库，静态表存在于公共库。从公共库同步到分库。实现表的连接。
    3）按照年、月、地域等来分割，或者根据时间范围、和很固定又清晰的字段值范围等，具有确定的分割标志来分割。

转载于:https://www.cnblogs.com/peteremperor/p/10951250.html