分库分表的垂直切分与水平切分看这篇就够了

🤞作者小三是刚刚毕业不久全栈工程师，写的技术文章基本上是学习过程中笔记整理而来，大家看了之后如果喜欢可以给小弟点点赞哦。
🤞例外小弟还有个程序员交流群，欢迎各位大佬来摸鱼哈。点击加群

什么是分库分表

分库分表：在一些数据库大的项目中，随着时间的推移和业务量的增加，数据库里的表中数据就会越来越多，如果单单还使用上面的模式，显然是不够用的。这时候就想到了把一个库里的数据分散到多个库里，并且把存在一个表里的数据分散到多个表里。分库分表的方式有垂直切分，水平切分

分库分表能带来什么样的好处

第一、能解决数据库本身的瓶颈，当连接数过多时，就会出现‘too many connections’的错误，这种访问量 太大或者是数据库设置的最大连接数太小的原因。mysql的默认的最大连接数是100，可以进行修改，而mysql服务允许最大的连接数为16384。数据库分表可以解决单表海量数据的查询性能问题，数据库分库可以解决单台数据库的并发访问压力问题。

第二、解决系统本身的IO、CPU瓶颈。磁盘读写IO瓶颈：热点数据量很多的情况下，尽量使用了数据库本身的缓存，但是依旧有大量IO，导致sql执行速度慢。网络IO瓶颈：请求的数据太多，数据传输量大了，网络带宽就显得不够用了，链路的响应时间就变长了。CPU瓶颈，在进行基础的数据量大单机复制SQL计算的时候，SQL语句执行占用CPU的使用率就会变高，不单只这种原因，也有扫描行数大、锁冲突、锁等待等等原因。

可以通过show processlist; 、show full processlist，发现 CPU 使用率比较高的SQL。常见的对于查询时间长，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能问题SQL，清楚相关影响问题的情况可以kill掉。也存在执行时间短，但是CPU占用率高的SQL，通过上面命令查询不到，这个时候最好通过执行计划分析explain进行分析

什么时候考虑使用分库分表呢？

能不使用分库分表就尽量不使用分库分表，并不是所有的表都需要进行切分的，主要还是看数据的增长速度。切分后是对在某一些成都市提高了业务的复杂程度，数据库除了承载数据的存储和查询以外，能更好的协助业务实现需求也是重要的工作。

不到万不得已不要轻易使用分库分表，避免“过度设计”和“过早优化”。在进行分库分表之前，不要是因为想分而分，更是要先去做力所能及的事情，比如说：给硬件升级、给网络升级、读写分离等等。数据量达到单表的瓶颈时在考虑使用分库分表。

数据量过大会影响业务的正常访问，对数据库的备份如果是单表很大的话，备份的时候就需要大量的磁盘IO和网络IO。对一个很大的表进行DDL修改的时候，Mysql会锁住全表，这个时间会挺长，这个时间段里业务是不能访问这个表的，造成的影响会很大。因为数据量大的表会经常访问与更新，这种情况就会有可能出现锁的等待。这时候将数据进行切分，用空间换时间，降低访问压力。

安全性和可用性，在业务的层面进行垂直切分的话，将不相关的数据库来进行分隔，因为的话每个业务的数据量，访问量都不一样，不能因为一个业务就把数据库搞挂掉牵连到其他的业务。利用水平切分的话，当一个数据库出现了问题，不会影响到全部的用户，因为每个库只承担了业务的一小部分数据，这样的整体可用性就可以得到提高。

Mysql数据库垂直分表讲解

垂直分表也就是把“大表拆成小表”，基于列字段进行的。拆分的原则一般是表中的字段较多，将不常用的或者是数据较大，长度较长的拆分到扩展表里，如text类类型字段。把访问频次低、字段大的商品描述信息单独的放在一张表里，访问频次较为多的商品基本信息单独的放在一张表里。

垂直拆分的原则：把不常用的字段单独的放在一张表里，把大字段拆分出来放在附表中，把业务经常组合查询的列放在一张表中。

为什么大字段的IO效率低下呢？

第一点就是由于数据量本身的庞大的，需要更长的读取时间；第二点就是跨页，也是数据库的存储单位，很多操作包括查找这些都是以页为单位的，单页里的数据行越多的话数据库的整体性能就越好，并且的话字段大所占用的空间也大，单页内的存储行数少，所以IO效率就较为低下；第三就是数据库以行为单位把数据加载到内存当中，这样的话表里的字段长度较为短并访问的频率高，内存就能加载到了更多的数据，命中率就更高了，减少了磁盘的IO，提升了数据库的性能。

//拆分前
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `total` int(10) DEFAULT '0' COMMENT '总库存',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
  
  `learn_base` text COMMENT '课前须知，学习基础',
  `learn_result` text COMMENT '达到水平',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
  `detail` text COMMENT '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//拆分后
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `total` int(10) DEFAULT '0' COMMENT '总库存',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `product_detail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL COMMENT '产品主键',
  `learn_base` text COMMENT '课前须知，学习基础',
  `learn_result` text COMMENT '达到水平',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',  
  `detail` text COMMENT '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Mysql数据库垂直分库讲解

垂直分库是针对的是一个系统中不同业务进行拆分的，数据库的连接资源比较宝贵并且单机的处理能力也是有限的。在没拆分之前全部都是落到单一的库上的，这时候单库的处理能力有瓶颈，与之还有的是磁盘的空间、内存、tps等限制。拆分之后，避免不同库竞争同一个物理机上的CPU、内存/网络IO、磁盘，所以在高并发的场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。垂直分库可以更好的解决业务层面的耦合，业务清晰并且方便管理和维护。一般从单体项目升级改造成为微服务项目的话，那就是垂直分库。

Mysql数据库水平分表

水平分表都是大表拆小表，垂直分表是按表结构进行拆分，水平分表是按数据结构进行拆分。把一个表的数据分到一个数据库的多张表里，每个表只有这个表的部分数据，其核心就是把一个大表分割成多个小表，每一个的结构是一样的，数据不一样，全部表的数据合起来就是全部的数据，针对数据量巨大的单张表（比如订单表）照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据量过大的问题。减少锁表时间，没分表前，如果是DDL(create/alter/add等)语句，当需要添加一列的时候mysql会锁表，期间所有的读写操作只能等待。

Mysql数据库水平分库讲解

把同个表的数据按照一定的规则分到不同的数据库里，数据库在不同的服务器上，水平分库就是把不同的表拆分到不同的数据库里，它是对数据的行拆分，不会影响表的结构。每个库的结构都一样，但是每个库的数据都不一样，没有交集，库的并集就是全量数据了。但水平分库的粒度会比水平分表更大。

分库分表总结：

垂直角度（表结构不一样）

垂直分表: 将一个表字段拆分成多个表，每个表存储部分字段。好处是避免IO时锁表的次数，分离热点字段和非热点字段，避免大字段IO导致性能下降。原则是业务经常组合查询的字段一个表；不常用字段一个表；text、blob类型字段作为附属表

垂直分库：根据业务将表分类放到不同的数据库服务器上，好处是避免表之间竞争同个物理机的资源，比如CPU/内存/硬盘/网络IO，原则是根据业务相关性进行划分，领域模型，微服务划分一般就是垂直分库。

水平角度（表结构一样）

水平分库：把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上。好处:是多个数据库，降低了系统的IO和CPU压力。原则是选择合适的分片键和分片策略，和业务场景配合；避免数据热点和访问不均衡、避免二次扩容难度大

水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构。好处是单个表的数据量少了，业务SQL执行效率高，降低了系统的IO和CPU压力。原则是选择合适的分片键和分片策略，和业务场景配合；避免数据热点和访问不均衡、避免二次扩容难度大