mysql的分库分表和表关系建立

一、mysql分库分表

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。当使用MySQL数据库的时候，单表超出了2000万数据量就会出现性能上的分水岭。并且物理服务器的CPU、内存、存储、连接数等资源有限，某个时段大量连接同时执行操作，会导致数据库在处理上遇到性能瓶颈。为了解决这个问题，行业先驱门充分发扬了分而治之的思想，对大表进行分割，然后实施更好的控制和管理，同时使用多台机器的CPU、内存、存储，提供更好的性能。而分而治之则有两种方式：垂直拆分和水平拆分。

• IO瓶颈
  ​ 1：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。
  ​ 2：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

• CPU瓶颈
  1：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。
  2：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

1、垂直切分（纵向切分）：

垂直切分是对不同的表（或者Schema）进行切分，存储到不同的数据库（主机）之上。垂直切分，强调的是业务的拆分。一个数据库由多个表构成，每个表对应不同的业务，那么我们可以指按照业务的不同将表进行分类，并将其分布到不同的数据库上，这样就将数据分摊到了不同的库上面，做到专库专用。

1.1 垂直拆分的优点：

• 解决业务系统层面的耦合，业务清晰
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
• 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

1.2 垂直拆分的缺点：

• 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
• 分布式事务处理复杂
• 依然存在单表数据量过大的问题（需要水平切分）

1.3 垂直分库： 垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。业务划分专库专用。

1.4 垂直分表： 把一个表的多个字段分别拆成多个表，一般按字段的冷热拆分，热字段一个表，冷字段一个表。从而提升了数据库性能。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题。数据库是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，因而可以加载更多数据到内存中，来增加查询的命中率，减少磁盘IO，以此来提升数据库性能。

2、水平切分（横向切分）：解决单库单表数据量大的问题

水平切分是对同一个表中的数据进行切分，存储到不同的数据库（主机）之上。规则是根据表中数据的逻辑关系，按照某种条件拆分。水平切分，强调的是技术层面的拆分。是将其按照一定的逻辑规则将一个表中的数据分散到多个库中，在每个表中包含一部分数据，所有表加起来就是全量的数据。简单来说，我们可以将对数据的水平切分理解为按照数据行进行切分，就是将表中的某些行切分到一个数据库表中，而将其他行切分到其他数据库表中。当某张表数据量达到一定的程度的时候，MySQL单表出现2000万以上数据就会出现性能上的分水岭。此时发现没有办法根据业务规则再进行拆分了，就会导致单表单库上的读写性能出现瓶颈。此时就只能进行水平拆分了。

2.1 水平切分的优点：

• 解决高并发时单库单库数据量过大的问题，提升系统稳定性和负载能力，够较好的应对高并发，同时可以将热点数据打散。
• 水平扩展能无线扩展。不存在某个库某个表过大的情况。
• 应用侧的改动较小，不需要根据业务来拆分。

2.2 水平切分的缺点：

• 需要处理分布式事务的一致性问题。
• 跨库的join关联查询性能较差。
• 扩容的难度和维护量较大。

2.3 水平分库： 水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。它带来的提升是：

• 解决了单库大数据，高并发的性能瓶颈。
• 提高了系统的稳定性及可用性。

2.4 水平分表： 假设当我们的订单表达到了5000万行记录的时候，非常影响数据库的读写效率，怎么办呢？我们可以考虑按照订单编号的进行rang分区,就是把订单编号在1-1000万的放在order1表中，将编号在1000万-2000万的放在order2中。**水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。**它带来的提升是：

• 优化单一表数据量过大而产生的性能问题

• 避免IO争抢并减少锁表的几率

如果数据库因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合，那么规则简单明了、容易实施的垂直切分必是首选。如果数据库中的表不多，但单表的数据量很大、或数据热度很高，就选择水平切分。

---

二、应用扩展

垂直扩展：提升单机处理能力，通过向现有计算机中添加更多的电源（CPU，RAM）来进行扩展。例如：增加CPU核数如32核，升级更好的网卡如万兆，升级更好的硬盘如SSD，扩充硬盘容量如2T，扩充系统内存如128G；

优缺点： 在互联网业务发展非常迅猛的早期，如果预算不是问题，强烈建议使用“增强单机硬件性能”的方式提升系统并发能力，因为这个阶段，公司的战略往往是发展业务抢时间，而“增强单机硬件性能”往往是最快的方法。不管是提升单机硬件性能，还是提升单机架构性能，都有一个致命的不足：单机性能总是有极限的。所以互联网分布式架构设计高并发终极解决方案还是水平扩展。

水平扩展：通过向资源池中添加更多计算机来进行扩展。只要增加服务器数量，就能线性扩充系统性能。

优缺点： 易扩容，但是水平扩展对系统架构设计是有要求的，难点在于：如何在架构各层进行可水平扩展的设计、可扩展性。

服务层的水平扩展，是通过“服务连接池”实现的。站点层通过RPC-client调用下游的服务层RPC-server时，RPC-client中的连接池会建立与下游服务多个连接，当服务成为瓶颈的时候，只要增加服务器数量，新增服务部署，在RPC-client处建立新的下游服务连接，就能扩展服务层性能，做到理论上的无限高并发。如果需要优雅的进行服务层自动扩容，这里可能需要注册中心里服务自动发现功能的支持。

数据层的水平扩展：在数据量很大的情况下，数据层（缓存，数据库）涉及数据的水平扩展，将原本存储在一台服务器上的数据（缓存，数据库）水平拆分到不同服务器上去，以达到扩充系统性能的目的。

---

三、应用拆分

应用垂直拆分：对业务的不同进行分类，不同的业务划分到不同的应用和数据库中。这种拆分往往是根据系统的改造，将原来的功能模块按照更加细粒度的拆分成多个弱耦合的服务。

示例：例如，可以根据业务逻辑，将“电商项目”拆分成“订单项目”、“用户项目”和“秒杀项目”。显然这三个拆分后的项目，仍然可以作为独立的项目使用。像这种拆分的方法，就成为垂直拆分，每个拆分项都可以进行独立处理。

应用水平拆分：将一个大的应用根据分层的概念拆分为几部分进行协助处理。可以将一个项目根据“三层架构”拆分成 表示层（jsp+servlet）、业务逻辑层（service）和数据访问层（dao）；然后再分开部署：把表示层部署在服务器A上，把service和dao层部署在服务器B上，然后服务器A和服务器B之间通过dubbo等RPC进行进行整合

示例：最经典的就是将整个应用分层。数据库访问层和业务逻辑层拆分、网关层和业务逻辑层拆分等等。

---

四 ：一对多、多对的关系的处理

1、一对多关系建立

一对多关系处理：

​ 通过学生和班级问题了解一对多：

​ 设计数据库表：只需在 学生表 中多添加一个班级号的ID；

注：在数据库中创建表时，还是通过添加主外键约束，避免删除数据时造成数据混乱！

2、多对多关系处理：

通过学生选课了解多对多问题的处理：

​ 在多对多中在一个表中添加一个字段就行不通了，所以处理多对多表问题时，就要考虑建立关系表了

例：

学生表： 课程表： 关系表：

注：所以对于多对多表，通过关系表就建立起了两张表的联系！多对多表时建立主外键后，要先删除约束表内容再删除主表内容

---

五：级联更新 外键约束

外键的功能：设置外键的作用很明显，就是将两个表的数据产生关联，就比如个人信息表和部门表之间，部门表的部门id就可以作为一个外键存放于个人信息表中（特性什么的就不再赘述~），除此之外设定外键在父子表中的维护关系，可以达到一个类似于级联的操作，就是当父级表（主键作为外键的表）数据变化时，子表（拿着别的表的主键作为自己的外键的表）就会有相应的数据变化；外键的约束（外键的限制、外键对父子表数据的维护关系）
CASCADE、NO ACTION、RESTRICT、SET NULL这几项可以理解为是：外键对父子表之间数据关系维护(限制)级别；

|-- 父表：被拿主键作为外键的表；
|-- 子表：拿别的表的主键作为外键的表；

REFERENCES : 引用作用, 引用其它表中的字段

create table sc(
scid int UNSIGNED primary key auto_increment,
sid int UNSIGNED not null,
score varchar(20) default '0',
index (sid),   --外键必须加索引
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
TYPE=InnoDB charset=utf8;