兼职招募 | 51CTO 社区编辑加盟指南
哈喽大家好呀! 我是小三. 今天来讲分库分表:)
什么是分库分表
分库分表: 在一些数据库大的项目中, 随着时间的推移和业务量的增加, 数据库里的表中数据就会越来越多, 如果单单还使用上面的模式, 显然是不够用的. 这时候就想到了把一个库里的数据分散到多个库里, 并且把存在一个表里的数据分散到多个表里.
「分库分表的方式有垂直切分, 水平切分」
分库分表能带来什么样的好处
第一, 能解决数据库本身的瓶颈, 当连接数过多时, 就会出现'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 压力. 原则是选择合适的分片键和分片策略, 和业务场景配合; 避免数据热点和访问不均衡, 避免二次扩容难度大
来源: http://database.51cto.com/art/202201/698289.htm