一、为什么需要分库分表?
随着业务越来越大,表的数量越来越多,单表数据量越来越大,受单台服务器资源(CPU、内存、磁盘IO)的限制导致响应时间越来越长,按正常来说,一个查询语句超过1秒就属于慢SQL,为了提高数据库的效率、缩短响应时间,有以下几个优化方案:
- sql + nosql,将读多写少的数据放入nosql中,比如redis,将数据放入内存中,减少磁盘IO;
- 优化sql语句,加索引;
- 读写分离,如果写请求越来越多,如果是一主多从的架构对主数据库的压力较大;
- 分库分表;
- 分区,主要用于数据归档
分库分表前后使用情况的比较:
# | 分库分表前 | 分库分表后 |
---|---|---|
并发支撑情况 | mysql单机部署,扛不住高并发; | mysql变为多机部署,能承受的并发增加了多倍 |
磁盘使用情况 | mysql单机磁盘容量占用较大 | 拆分为多个库,数据库服务器磁盘使用率大大降低 |
SQL执行性能 | 单表数量大,SQL越跑越慢 | 单表数据量减少,SQL执行效率提升 |
二、什么是分库分表?
将一个数据库分为多个数据库,将一个表拆分为多个表;
三、分库分表的方式
3.1 垂直
大表拆小表,大库拆小库,基于数据库中的列进行的。以分库为例,如电商平台,将整个网站中的订单、商品、用户放在不同的数据库中。
优点:
- 拆分后业务清晰,专库专用;
- 数据维护简单
缺点:
- 如果其中一个数据库中某个表数据量大,读写压力大,如双十一订单会大量增加,商品和用户数据库中数据变化小;
- 部分业务的信息无法通过表的join连接获得,只能在java中通过接口调用。
特点:
- 每个库(表)的结构都不一样;
- 每个库(表)中都至少有一列数据和其他库(表)中字段相同,如订单库中的订单表中会有商品表中id;
- 所有库(表)的并集是全量数据。
3.2 水平
以某个字段按照一定的规律(如取模)将一个表中的数据分到多个表中。
如水平拆分用户表,当前有三个数据库mysql1、mysql2、mysql3,将用户的id和一个指定的数3取模,如果结果为1将数据放入mysql1中,结果为2放入mysql2中。
优点:
- 单库(表)的数据保持在一定量,相比集中在一个数据库(表)中减少,提升数据库的性能;
- 切分的表的结构相同,程序改造较少;
缺点:
- 数据的扩容较难,如果三个数据库都到达了极限,再增加一个数据库,需要将之前数据库的数据按照新的规则重新存放;
- 拆分的规则很难抽象出来;
- 部分业务的信息无法通过表的join连接获得,只能在java中通过接口调用;
特点
- 每个库(表)的结构都是一样的;
- 每个库(表)的数据都不一样;
- 所有库(表)的并集是全量数据。
结合两种拆分方式的特点,可以将两种方式结合起来,垂直分库水平分表,按照业务将数据垂直拆分到不同的数据库中,再将每个数据库中数量较大的表按照一定的规则拆分到多个表中。
3.3 分库分表带来的问题
- 维护成本增加
- 需要使用分布式事务
- 跨库的的数据表不能用join
3.4 分库分表算法
- 哈希取模:例如通过id和指定的数取模,数据分散均衡,但是扩容麻烦;
- RANGE:范围划分,例如将6个月之前的数据切出去放在另外的一张表中,按省份进行划分等,但是有可能会使热点数据划分到一个表中;
四、分库分表中间件
4.1 中间件的产品
分为两类:基于Proxy的 和 基于JDBC的。
Proxy:myCat,atlas(360开发,基于mysql-proxy二次开发),
JDBC:
- shardingSphere,Apache的孵化项目,它的前身是sharding-jdbc,由sharding-jdbc、sharding-proxy、sharding-sidecar三个产品组成,所以它既有基于proxy的又有基于JDBC的中间件;
- TDDL,淘宝,部分开源,核心内容收费
4.2 中间件原理
不加中间件时的请求:java项目直接请求mysql数据库
Proxy:java项目请求mysql数据库之前,先经过Proxy中间件,判断要访问哪个数据库。
JDBC:java项目中引入sharding-jdbc.jar,在项目内部就决定了要访问的数据库。
优缺点:
# | 优点 | 缺点 |
---|---|---|
Proxy | 程序员不需要关注底层,不会增加开发难度;增删节点不需要重启项目;跨语言; | 性能下降,因为在应用和数据库之间多了一层传输;不支持跨数据库,因为每个数据库的连接协议不一样; |
Jdbc | 不影响性能;支持跨数据库,因为底层是JDBC,可以连接多个数据库; | 需要在代码中引入,增加开发难度;现在的版本仍需要重启,目前正在开发的4.X版本会解决;不支持跨语言,jdbc只能用在java中; |
4.3 sharding-jdbc
官网:http://shardingsphere.apache.org/,
4.3.1 分库分表代码及解释
public class JavaSytleTest {
public static void main(String[] args) throws SQLException {
// 配置真实数据源
//size 2 为两个数据库的datasource
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/orders_0");
dataSource1.setUsername("root");
dataSource1.setPassword("password");
dataSourceMap.put("orders_0", dataSource1);
// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/orders_1");
dataSource2.setUsername("root");
dataSource2.setPassword("password");
dataSourceMap.put("orders_1", dataSource2);
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
shardingRuleConfig.getBindingTableGroups().add("t_order,t_order_item");
//采用user_id进行分库
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
new InlineShardingStrategyConfiguration("user_id",
"orders_${user_id % 2}"));//分别得出orders_0 orders1
//采用order_id进行分表
shardingRuleConfig.setDefaultTableShardingStrategyConfig(
new StandardShardingStrategyConfiguration("order_id",
new PreciseShardingAlgorithm<Long>() {
@Override
public String doSharding(Collection<String> collection, final PreciseShardingValue<Long> preciseShardingValue) {
for (String each : collection) {
System.out.println(each+"--------"+preciseShardingValue.getValue()+"---------"+preciseShardingValue.getValue() % 2);
if (each.endsWith(preciseShardingValue.getValue()