数据库分库分表和读写分离

一. 数据分库分表概览

1.海量数据优化阶段

1.索引

单表数据量在百万内优先考虑利用索引解决慢查询

2.分区表

说句实在话，你们面试的时候，其实可以略过这个阶段。由于很多互联网公司都不建议使用分区表，我自己也不太建议使用分区表，采使用这个分区表，坑太多。

什么是mysql的分区表？

答复：所有数据还在一个表中，但物理存储根据肯定的规则放在不同的文件中。这个是mysql支持的功能，业务代码不需要改动，但是sql语句需要改动，sql条件需要带上分区的列。

缺点

(1)分区键设计不太灵活，假如不走分区键，很容易出现全表锁

(2)在分区表用ALTER TABLE?…?ORDER BY，只能在每个分区内进行order by。

(3)分区表的分区键创立索引，那么这个索引也将被分区。分区键没有全局索引一说。

(4)自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定该去哪个分区查，不太可控。

…不列举了，不推荐

3.数据分区

1.数据归档

我们在开发业务系统的时候，很多数据都是具备时间属性的，并且随着系统运行，累计增长

越来越多，数据量达到一定程度就会越来越慢，比如说电商中的订单数据，就是这种情况。

按照我们刚刚说的思想，这个时候就需要拆分数据了。

我们的订单数据一般都是保存在 MySQL 中的订单表里面，说到拆分 MySQL 的表，大多数同学的第一反应都是“分库分表”，别着急，咱现在的数据量还没到非得分库分表那一步呢。

当单表的订单数据太多，多到影响性能的时候，首选的 方案是，归档历史订单。

其主要思路就是按年聚合对应的历史表，然后读的时候，根据查询条件去路由到对应的表中，注意那几张表的数据结构最好能够保持一致。

归档历史订单，大致的流程是这样的

1. 首先我们需要创建一个和订单表结构一模一样的历史订单表；

2. 然后，把订单表中的历史订单数据分批查出来，插入到历史订单表中去。这个过程你怎么实现都可以，用存储过程、写个脚本或者写个导数据的小程序都行，用你最熟悉的方法就行。如果你的数据库已经做了主从分离，那最好是去从库查询订单，再写到主库的历史订单表中去，这样对主库的压力会小一点儿。3. 现在，订单表和历史订单表都有历史订单数据，先不要着急去删除订单表中的数据，你应该测试和上线支持历史订单表的新版本代码。因为两个表都有历史订单，所以现在这

个数据库可以支持新旧两个版本的代码，如果新版本的代码有 Bug，你还可以立刻回滚到旧版本，不至于影响线上业务。

4. 等新版本代码上线并验证无误之后，就可以删除订单表中的历史订单数据了。

5. 最后，还需要上线一个迁移数据的程序或者脚本，定期把过期的订单从订单表搬到历史订单表中去。

2.数据拆分

主要包括垂直拆分和水平拆分等。

2.数据分表

1 索引的极限

单表数据量达到几十万或上百万以上，使用索引性能提升也不明显。

2 分表的边界

分表使用门槛：

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

3.分表适用场景

分库分表主要用于应对当前互联网常见的两个场景——大数据量和高并发。

1.数据量大时，减少单表查询压力

2.减少单表的访问压力

3.单表数据量上百万至千万，索引性能下降

4.索引也很难在提高性能或者更新、插入数据频繁等。

4.分表所造成的复杂度

• join 操作

水平分表后，数据分散在多个表中，如果需要与其他表进行 join 查询，需要在业务代码或者数据库中间件中进行多次 join 查询，然后将结果合并。

• count() 操作

水平分表后，虽然物理上数据分散到多个表中，但某些业务逻辑上还是会将这些表当作一个表来处理。例如，获取记录总数用于分页或者展示，水平分表前用一个 count() 就能完成的操作，在分表后就没那么简单了。常见的处理方式有下面两种：

count() 相加：具体做法是在业务代码或者数据库中间件中对每个表进行 count() 操作，然后将结果相加。这种方式实现简单，缺点就是性能比较低。例如，水平分表后切分为 20 张表，则要进行 20 次 count(*) 操作，如果串行的话，可能需要几秒钟才能得到结果。

记录数表：具体做法是新建一张表，假如表名为“记录数表”，包含 table_name、row_count 两个字段，每次插入或者删除子表数据成功后，都更新“记录数表”。

这种方式获取表记录数的性能要大大优于 count() 相加的方式，因为只需要一次简单查询就可以获取数据。缺点是复杂度增加不少，对子表的操作要同步操作“记录数表”，如果有一个业务逻辑遗漏了，数据就会不一致；且针对“记录数表”的操作和针对子表的操作无法放在同一事务中进行处理，异常的情况下会出现操作子表成功了而操作记录数表失败，同样会导致数据不一致。

此外，记录数表的方式也增加了数据库的写压力，因为每次针对子表的 insert 和 delete 操作都要 update 记录数表，所以对于一些不要求记录数实时保持精确的业务，也可以通过后台定时更新记录数表。定时更新实际上就是“count() 相加”和“记录数表”的结合，即定时通过 count() 相加计算表的记录数，然后更新记录数表中的数据。

• order by 操作

水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或者数据库中间件分别查询每个子表中的数据，然后汇总进行排序。

3.分库和分表选型

1.为何不用分布式数据库替换

我认为主要因为分布式数据库对事务的支持，远没有关系性数据库好。

解决海量数据的问题，必须要用到分布式的存储集群，因为 MySQL 本质上是一个单机数据库，所以很多场景下不是太适合存 TB 级别以上的数据。

但是，绝大部分的电商大厂，它的在线交易这部分的业务，比如说，订单、支付相关的系统，还是舍弃不了 MySQL，原因是， 只有 MySQL 这类关系型数据库，才能提供金融级的

事务保证 。我们之前也讲过分布式事务，那些新的分布式数据库提供的所谓的分布式事务，

多少都有点儿残血，目前还达不到这些交易类系统对数据一致性的要求。

2.分库和分表适用场景

在考虑到底是分库还是分表之前，我们需要先明确一个原则，那就是能不拆就不拆，能少拆不多拆。原因也很简单，你把数据拆分得越散，开发和维护起来就越麻烦，系统出问题的概率就越大。

基于这个原则我们想一下，什么情况下适合分表，什么情况下不得不分库？

那我们分库分表的目的是为了解决两个问题：

第一，是数据量太大查询慢的问题。这里面我们讲的“查询”其实主要是事务中的查询和更新操作，因为只读的查询可以通过缓存和主从分离来解决，这个我们在之前的“MySQL 如何应对高并发”的两节课中都讲过。那我们上节课也讲到过，解决查询慢，只要减少每次查 询的数据总量就可以了，也就是说，分表就可以解决问题。

第二，是为了应对高并发的问题。应对高并发的思想我们之前也说过，一个数据库实例撑不住，就把并发请求分散到多个实例中去，所以，解决高并发的问题是需要分库的。

简单地说，数据量大，就分表；并发高，就分库。

二. 数据分表-拆分思想

1.垂直拆分

垂直分库

是指按功能模块拆分，比如分为订单库、商品库、用户库…这种方式多个数据库之间的表结构不同。

垂直分表

即当一张表有有太多的字段后，需要按照业务模块进行拆分，如用户表拆成用户基础信息，用户登陆信息表，用户地址信息表等等，然后通过用户ID进行关联。

假如你有幸能够在什么经营商、银行等公司上班，你会发现他们一个表，几百个字段都是很常见的事情。所以，应该要进行拆分，拆分准则一般是如下三点:

(1)把不常使用的字段单独放在一张表。

(2)把常使用的字段单独放一张表

(3)经常组合查询的列放在一张表中（联合索引）。

垂直拆分的好处

1.单表的数据行数变少了，查询检索的字段也变少了，性能得到了一定的提升

垂直拆分的坏处

垂直分表引入的复杂性主要体现在表操作的数量要增加。例如，原来只要一次查询就可以获取 name、age、sex、nickname、description，现在需要两次查询，一次查询获取 name、age、sex，另外一次查询获取 nickname、description。

2.水平拆分

将同一个表的数据进行分块保存到不同的数据库中，这些数据库中的表结构完全相同。

1.Sharding Key选型

选择这个 Sharding Key 最重要的参考因素是，我们的业务是如何访问数据的。

1.多分片聚合

比如我们把订单 ID 作为 Sharding Key 来拆分订单表，那拆分之后，如果我们按照订单 ID来查订单，就需要先根据订单 ID 和分片算法计算出，我要查的这个订单它在哪个分片上，

也就是哪个库哪张表中，然后再去那个分片执行查询就可以了。

但是，当我打开“我的订单”这个页面的时候，它的查询条件是用户 ID，这里没有订单ID，那就没法知道我们要查的订单在哪个分片上，就没法查了。当然你要强行查的话，那就只能把所有分片都查一遍，再合并查询结果，这个就很麻烦，而且性能很差，还不能分页。

那要是把用户 ID 作为 Sharding Key 呢？也会面临同样的问题，使用订单 ID 作为查询条件来查订单的时候，就没办法找到订单在哪个分片了。

这个问题的解决办法是，在生成订单ID 的时候，把用户 ID 的后几位作为订单 ID 的一部分，比如说，可以规定，18 位订单号中，第 10-14 位是用户 ID 的后四位，这样按订单 ID 查询的时候，就可以根据订单 ID 中的用户 ID 找到分片。

2.多分片聚合实战

用户ID和租户ID都是频繁使用到的场景，那么应该如何聚合？

写入算法
128张表，租户id映射到0-99，用户id映射到0-28, 相加得到的值对应分片建，最终映射到0-127。

读算法
按照写入算法逆向进行即可。

快速定位到是哪张表
同写算法一样，先计算学员映射的值时多少再计算租户映射的值是多少，然后两个一起想加计算出是路由到哪张表里。

像这种场景，一搬会在代码里封装成一个公共的方法，排查问题时可快速使用。

算法的核心代码如下：

public class MultiColumnsShardingAlgorithm implements MultipleKeysTableShardingAlgorithm {

    private static final int HUNDRED = 100;


    @Override
    public Collection<String> doSharding(Collection<String> tableNames,
            Collection<ShardingValue<?>> shardingValues) {

        Long tenantId = null;
        List<Long> customerIds = null;
        for (ShardingValue<?> shardingValue : shardingValues) {
            if (shardingValue.getColumnName().equals("tenant_id")) {
                tenantId = parseLong(shardingValue.getValue());
            }
            if (shardingValue.getColumnName().equals("customer_id")) {
                customerIds = parseListLong(shardingValue);
            }
        }
        // 128张表，租户id映射到0-99，用户id映射到0-28, 相加最终映射到0-127
        List<Integer> suffixs = new ArrayList<>();
        if (!CollectionUtils.isEmpty(customerIds)) {
            tenantId = null == tenantId ? SaasContextHolder.getTenantContext().getTenantId() : tenantId;
            int prefix = (int) (tenantId % HUNDRED);
            for (Long customerId : customerIds) {
                suffixs.add(prefix + (int) (customerId % (tableNames.size() % HUNDRED + 1)));
            }
        } else if (null != tenantId) {
            // 手动指定tenantId时
            int prefix = (int) (tenantId % HUNDRED);
            int suffix = tableNames.size() % HUNDRED;
            for (int i = 0; i <= suffix; i++) {
                suffixs.add(prefix + i);
            }
        }
        Collection<String> result = new ArrayList<>();
        for (int suffix : suffixs) {
            for (String tableName : tableNames) {
                if (tableName.endsWith("" + suffix)) {
                    result.add(tableName);
                    break;
                }
            }
        }
        return result;
    }

    /**
     *
     * @param value
     * @return
     */
    private Long parseLong(Object value) {
        return Long.parseLong(String.valueOf(value));
    }

    /**
     *
     * @param shardingValue
     * @return
     */
    private List<Long> parseListLong(ShardingValue<?> shardingValue) {
        List<Long> result = new ArrayList<>();
        if (shardingValue.getType().equals(ShardingValue.ShardingValueType.SINGLE)) {
            result.add(parseLong(shardingValue.getValue()));
        } else if (shardingValue.getType().equals(ShardingValue.ShardingValueType.LIST)) {
            for (Object value : shardingValue.getValues()) {
                result.add(parseLong(value));
            }
        }
        return result;
    }
}

3.非分片建查询

那我们系统对订单的查询方式，肯定不只是按订单 ID 或者按用户 ID 这两种啊。

比如说，商家希望看到的是自己店铺的订单，还有各种和订单相关的报表。对于这些查询需求，我们一旦对订单做了分库分表，就没法解决了。那怎么办呢？

一般的做法是，把订单数据同步到其他的存储系统中去，在其他的存储系统里面解决问题。

比如说，我们可以再构建一个以店铺 ID 作为 Sharding Key 的只读订单库，专门供商家来使用。或者，把订单数据同步到 HDFS 中，然后用一些大数据技术来生成订单相关的报表。

以下是相关解决方案

1. 可以做⼀个mapping表，⽐如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做⼀个映射关系表，保存商家和⽤户的关系，如将映射表存储到es中，查询的时候先通过商家查询到⽤户列表，再通过user_id去查询。
2. 打宽表，⼀般⽽⾔，商户端对数据实时性要求并不是很⾼，⽐如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成⼀张宽表。
3. 数据量不是很⼤的话，⽐如后台的⼀些查询之类的，也可以通过多线程扫表，然后再聚合结果的⽅式来做。或者异步的形式也是可以的

2.分片算法选型

1.范围路由拆分

选取有序的数据列（例如，整形、时间戳等）作为路由的条件，不同分段分散到不同的数据库表中。

以最常见的用户 ID 为例，路由算法可以按照 1000000 的范围大小进行分段，1 ~ 999999 放到数据库 1 的表中，1000000 ~ 1999999 放到数据库 2 的表中，以此类推

再如可以按订单的日前按年份才分，2003年的放在db1中，2004年的db2,以此类推。当然也可以按主键标准拆分。

范围路由设计的复杂点主要体现在分段大小的选取上，分段太小会导致切分后子表数量过多，增加维护复杂度；分段太大可能会导致单表依然存在性能问题，一般建议分段大小在 100 万至 2000 万之间，具体需要根据业务选取合适的分段大小。

1.优点

可部分迁移。范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如，现在的用户是 100 万，如果增加到 1000 万，只需要增加新的表就可以了，原有的数据不需要动。

2.缺点

数据分布不均，造成热点问题。可能2003年的订单有100W，2008年的有500W。且跨年查询实现方式困难可能会查多张表。再如按照 1000 万来进行分表，有可能某个分段实际存储的数据量只有 1000 条，而另外一个分段实际存储的数据量有 900 万条。

3.适用场景

基于范围来分片容易产生热点问题，不适合作为订单的分片方法，但是这种分片方法的优点也很突出，那就是对查询非常友好，基本上只要加上一个时间范围的查询条件，原来该怎么查，分片之后还可以怎么查。

范围分片特别适合那种数据量非常大，但并发访问量不大的 ToB 系统。

比如说，电信运营商的监控系统，它可能要采集所有人手机的信号质量，然后做一些分析，这个    数据量非常大，但是这个系统的使用者是运营商的工作人员，并发量很少。这种情况下就很适合范围分片

4.范围路由拆分实战

1.月份分片

比如说，我分 12 个分片，每个月一个分片，这样对查询的兼容要好很多，毕竟查询条件中带上时间范围，让查询只落到某一个分片上，还是比较容易的，我在查询界面上强制用户必须指定时间范围就行了。

这种做法有个很大的问题，比如现在是 3 月份，那基本上所有的查询都集中在 3 月份这个分片上，其他 11 个分片都闲着，这样不仅浪费资源，很可能你 3 月那个分片根本抗不住几乎全部的并发请求。

这个问题就是“热点问题”。

另外还有一个弊端就是月份分片只能分12张表，无法处理大数据。除非你是将年和月组合一起作为分片建，那这样分的表就会太细，一旦更改数据表结构执行ddl将会造成较长的锁表时间

2.hash取模 　　

比如说，我们要分 24 个分片，选定了Sharding Key 是用户 ID，那我们决定某个用户的订单应该落到那个分片上的算法是，拿用户 ID 除以 24，得到的余数就是分片号。

这是最简单的取模算法，一般就可以满足大部分要求了。当然也有一些更复杂的哈希算法，像一致性哈希之类的，特殊情况下也可以使用。

1.优点

数据分布均匀

2.缺点

数据量大后，数据重新分表后迁移的时候麻烦;不能按照机器性能分摊数据 。

二次扩容解决建议：先同步到大数据平台，然后在大数据平台重新根据分片算法路由到各个表，最后同步给业务平台mysql数据表。但这样的操作最好建议是在凌晨操作，程序员又要熬夜了。。。

3.配置路由

配置路由就是路由表，也叫查表法。用一张独立的表来记录路由信息。

同样以用户 ID 为例，我们新增一张 user_router 表，这个表包含 user_id 和 table_id 两列，根据 user_id 就可以查询对应的 table_id。

对应的执行流程如下：

配置路由设计简单，使用起来非常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，然后修改路由表就可以了。

配置路由的缺点就是必须多查询一次，会影响整体性能；而且路由表本身如果太大（例如，几亿条数据），性能同样可能成为瓶颈，如果我们再次将路由表分库分表，则又面临一个死循环式的路由算法选择问题。

3.数据分表后的利弊分析

1.弊端分析

1.统计变得复杂。

由于数据被拆分到不同的表里面，所以如果要对多个分片键的数据进行统计，则会路由全表，性能会急数降低。

解决建议

1.T+1方案离线统计

既然实时统计比较耗性能则进行离线统计，即使用T+1方案，凌晨时提前计算好上日的数据，并存储下来作为一个离线统计结果表

2.以分片键维度离线统计

半离线方案，按照分片键维度定时统计每日的数据然后汇总到一张表上，此时调度周期可以设置得短一些。

3.采用大数据技术栈的存储数据库

可以采用es,ck等平台做大数据存储。

2.二次扩容

我个人不建议你在方案中考虑二次扩容的问题，也就是考虑未来的数据量，把这次分库分表设计的容量都填满了之后，数据如何再次分裂的问题。

现在技术和业务变化这么快，等真正到了那个时候，业务早就变了，可能新的技术也出来 了，你之前设计的二次扩容方案大概率是用不上的，所以没必要为了这个而增加方案的复杂 程度。

还是那句话， 越简单的设计可靠性越高 。

二. 数据分库-主从同步

 

2.主从延迟

1.MySQL数据库主从同步延迟是怎么产生的

场景二

从业务层面解决主从延迟

拿订单系统来举例，我们自然的设计思路是，用户从购物车里发起结算创建订单，进入订单页，打开支付页面进行支付，支付完成后，按道理应该再返回支付之前的订单页。但如果这个时候马上自动返回订单页，就很可能会出现订单状态还是显示“未支付”。因为，支付完成后，订单库的主库中，订单状态已经被更新了，而订单页查询的从库中，这条订单记录的状态有可能还没更新。

怎么解决？

这种问题其实没什么好的技术手段来解决，所以你看大的电商，它支付完成后是不会自动跳回到订单页的，它增加了一个无关紧要的“支付完成”页面，其实这个页面没有任何有效的信息，就是告诉你支付成功，然后再放一些广告什么的。你如果想再看刚刚支付完成的订单，需要手动点一下，这样就很好地规避了主从同步延迟的问题。

场景三

强制路由主库解决主从延迟

我们需要特别注意的，是那些数据更新后，立刻需要查询更新后的数据，然后再更新其他数据这种情况。比如说在购物车页面，如果用户修改了某个商品的数量，需要重新计算优惠和总价。更新了购物车的数据后，需要立即调用计价服务，这个时候如果计价服务去读购物车的从库，非常可能读到旧数据而导致计算的总价错误。

对于这个例子，你可以把“更新购物车、重新计算总价”这两个步骤合并成一个微服务，然后放在一个数据库事务中去，同一个事务中的查询操作也会被路由到主库，这样来规避主从不一致的问题

2. MySQL数据库主从同步延迟解决方案

 参考我的另一篇博客

mysql高可用架构设计-CSDN博客

参考：深入解析Mysql 主从同步延迟原理及解决方案 https://www.cnblogs.com/cnmenglang/p/6393769.html

 三、数据分库-读写分离

参考我的另一篇博客

mysql高可用架构设计-CSDN博客