什么是分库分表
分库分表:在一些数据库大的项目中,随着时间的推移和业务量的增加,数据库里的表中数据就会越来越多,如果单单还使用上面的模式,显然是不够用的。这时候就想到了把一个库里的数据分散到多个库里,并且把存在一个表里的数据分散到多个表里。
分库分表能带来什么样的好处
第一、能解决数据库本身的瓶颈,当连接数过多时,就会出现‘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,提升了数据库的性能。
- //拆分前
- CREATETABLE `product` (
- `id` int(11) unsigned NOTNULL AUTO_INCREMENT,
- `title` varchar(524) DEFAULTNULL COMMENT ‘视频标题’,
- `cover_img` varchar(524) DEFAULTNULL COMMENT ‘封面图’,
- `price` int(11) DEFAULTNULL 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) DEFAULTNULL COMMENT ‘概述’,
- `detail` text COMMENT ‘视频商品详情’,
- PRIMARYKEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- //拆分后
- CREATETABLE `product` (
- `id` int(11) unsigned NOTNULL AUTO_INCREMENT,
- `title` varchar(524) DEFAULTNULL COMMENT ‘视频标题’,
- `cover_img` varchar(524) DEFAULTNULL COMMENT ‘封面图’,
- `price` int(11) DEFAULTNULL COMMENT ‘价格,分’,
- `total` int(10) DEFAULT’0′ COMMENT ‘总库存’,
- `left_num` int(10) DEFAULT’0′ COMMENT ‘剩余’,
- PRIMARYKEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- CREATETABLE `product_detail` (
- `id` int(11) unsigned NOTNULL AUTO_INCREMENT,
- `product_id` int(11) DEFAULTNULL COMMENT ‘产品主键’,
- `learn_base` text COMMENT ‘课前须知,学习基础’,
- `learn_result` text COMMENT ‘达到水平’,
- `summary` varchar(1026) DEFAULTNULL COMMENT ‘概述’,
- `detail` text COMMENT ‘视频商品详情’,
- PRIMARYKEY (`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压力。原则是选择合适的分片键和分片策略,和业务场景配合;避免数据热点和访问不均衡、避免二次扩容难度大。