如何进行分库分表？

1、为什么分库分表？

• 主库写入瓶颈或硬件瓶颈（如网络带宽），通过加从库或分区表解决不了，而提升硬件配置ROI 不高
• 数据量太大，不得不分表
• 分库的优点：分库往往部署在多套集群中，也就意味着降低了单个集群的负载压力，提升整体的读写性能。

2、分库分表优点和挑战？

分表的优点：

• 提高数据操作的效率。举个例子说明，比如user表中现在有4000w条数据，此时我们需要在这个表中增加（insert）一条新的数据，insert完毕后，数据库会针对这张表重新建立索引，4000w行数据建立索引的系统开销还是不容忽视的。
• 假如我们将这个大表分成4个分表，从user_0到user_3，4000w行数据平均下来，每个子表里只有1000W行数据，对1000W行的表中insert数据，建立索引的时间就会下降，从而提高了DB的运行时效率，进而提高并发量。
• 除了提高写的效率，更重要的是提高读效率，提高查询的性能。当然分表的好处还不止这些，还有诸如减少写操作时锁范围等，都会带来很多明显的优点。

分库分表的挑战：

• 基本的数据库增删改功能：对于开发人员而言，虽然分库分表，但还是希望能和单库单表那样的去简单地操作数据库，包括查询与DML操作。
• 分布式id：在分库分表后，我们不能再使用mysql的自增主键。因为在插入记录的时候，不同的库生成的记录的自增id会出现冲突。因此需要有一个全局的id生成器。
• 分布式事务：分布式事务是分库分表绕不过去的一个坎，因为涉及到了同时更新多个数据库，如何保证要么同时成功，要么同时失败。关于分布式事务，mysql支持XA事务，但是效率较低。柔性事务是目前比较主流的方案，柔性事务包括：最大努力通知型、可靠消息最终一致性方案以及TCC两阶段提交。但是无论XA事务还是柔性事务，实现起来都是非常复杂的。
• 动态扩容：动态扩容指的是增加分库分表的数量。例如原来的user表拆分到2个库的4张表上。现在我们希望将分库的数量变为4个，分表的数量变为8个。这种情况下一般要伴随着数据迁移。例如在4张表的情况下，id为7的记录，7%4=3，因此这条记录位于user_3这张表上。但是现在分表的数量变为了8个，而7%8=7，而user_7这张表上根本就没有id=7的这条记录，因此如果不进行数据迁移的话，就会出现记录找不到的情况。
• 数据迁移：对于新的应用，如果预估到未来数据量比较大，可以提前进行分库分表。但是对于一些老的应用，单表数据量已经比较大了，这个时候就涉及到数据迁移的过程。

3、分库还是分表？

• 若是 DB 硬件性能瓶颈，那么需要分数据源，即需要更多的主从集群
• 若是逻辑数据库引起的性能瓶颈，那么需要在逻辑数据库层面分库
• 若是单表数据量过大、锁竞争等表维度资源引发的性能问题，那么需要分表

4、分片数选择？

• 存量有效数据，需排除掉可以归档的数据
• 数据增长趋势，根据业务规划，预估 3～5 年的数据增长情况

表数目决策：

• 按行数计算：（未来3到5年内总共的记录行数) / 单张表建议记录行数（单张表建议记录行数 = 1000万）
• 表的数量不宜过多，涉及到聚合查询或者分表键在多个表上的SQL语句，就会并发到更多的表上进行查询。举个例子，分了4个表和分了2个表两种情况，一种需要并发到4表上执行，一种只需要并发到2张表上执行，显然后者效率更高。
• 表的数目不宜过少，少的坏处在于一旦容量不够就又要扩容了，而分库分表的库想要扩容是比较麻烦的。一般建议一次分够。
• 建议表的数目是2的幂次个数，方便未来可能的迁移。

库数目决策：

• 按照存储容量来计算 = （3到5年内的存储容量）/ 单个库建议存储容量（单个库建议存储容量 <300G以内）
• DBA的操作，一般情况下，会把若干个分库放到一台实例上去。未来一旦容量不够，要发生迁移，通常是对数据库进行迁移。所以库的数目才是最终决定容量大小。
  最差情况，所有的分库都共享数据库机器。最优情况，每个分库都独占一台数据库机器。一般建议一个数据库机器上存放8个数据库分库。

5、分表策略选择？

5.1 按照UserID进行Hash分表，根据UserID进行查询

<?xml version="1.0" encoding="UTF-8"?>
<router-rule>  
<table-shard-rule table="Order" generatedPK="id">    
    <shard-dimension dbRule="#UserID#.toInteger()%32" dbIndexes="order_test[0-31]"       
    tbRule="#UserID#.toInteger().intdiv(32)%32" tbSuffix="alldb:[0,1023]"      
    isMaster="true">    
  </shard-dimension>  
  </table-shard-rule>
</router-rule>

Order表的UserID维度一共分了32个库，分别是order_test0到order_test31。一共分了1024张表，表名分表是Order0到Order1023，平均分到了32个库中，每个库32张表。

<?xml version="1.0" encoding="UTF-8"?>
<router-rule>
    <table-shard-rule table="Order" generatedPK="OrderID">
        <shard-dimension dbRule="crc32(#字符串#)%10000%32" dbIndexes="order_test[0-31]" 
            tbRule="(crc32(#字符串#)%10000).intdiv(32) %32" tbSuffix="alldb:[0,1023]"
            isMaster="true">
        </shard-dimension>
    </table-shard-rule>
</router-rule>

crc32是目前zebra的一个内置函数。

5.2 根据UserID进行Hash分表，根据UserID和OrderID进行查询

<?xml version="1.0" encoding="UTF-8"?>
<router-rule>
    <table-shard-rule table="Order" generatedPK="OrderID">
        <shard-dimension dbRule="#UserID#.toInteger()%10000%32" dbIndexes="order_test[0-31]" 
            tbRule="(#UserID#.toInteger()%10000).intdiv(32) %32" tbSuffix="alldb:[0,1023]"
            isMaster="true">
        </shard-dimension>
        <shard-dimension dbRule="#OrderID#[13..16].toInteger() % 32" dbIndexes="order_test[0-31]" 
            tbRule="(#OrderID#[13..16].toInteger()).intdiv(32) %32" tbSuffix="alldb:[0,1023]"
            isMaster="false">
        </shard-dimension>
    </table-shard-rule>
</router-rule>

Order表的OrderID维度的数据和UserID的数据是一致的，并没有冗余。但是由于OrderID中的13到16位就是UserID，所以可以使用UserID的数据进行查询。本质上，OrderID和UserID肯定能一一对应，其实是一个维度。

6、如何迁移数据？

• 双写（注意目标表 upsert场景）、数据DIFF（某个更新时间点后数据 DIFF）、历史数据迁移（DTS）、数据量级DIFF、流量录制线上&预发切读 DIFF、切读（此刻写以目标表为准）、停写

7、一般单表超过多少行建议分表？

行业内有种说法是超2000 万数据就需要考虑分表啦，这个经验值如下计算出来的：

• 三层B+树的搜索路径为3次磁盘I/O，以达普通磁盘的瓶颈。
  计算公式：总行数 = 叶子节点数 × 单页存储行数
  非叶子节点容量：假设主键为bigint（8字节）+指针（6字节），单页（16KB）可存储约1170个索引条目。叶子节点容量：若单行数据约1KB，单页可存16行。三层B+树总容量：1170（根节点）× 1170（中间层）× 16（叶子层）≈ 2190万行。
• 行数据大小的影响
  实际数据量受字段类型影响较大。例如：
  若单行仅34字节（如bigint主键+少量字段），三层B+树可存储约6.6亿行。
  若单行达1KB，则容量降至约2000万行，成为经验值的理论依据。
• 历史硬件条件与性能瓶颈
  早期机械硬盘IOPS仅约100，磁盘I/O是主要瓶颈。三层B+树的3次I/O已接近极限，超过2000万行会导致树高增加，性能骤降。
  现代SSD IOPS可达数万，树高增加的影响减弱。但分表仍被推荐，因SMO（结构修改操作）的锁竞争问题未完全解决。

综上：这个经验值不一定准，取决于磁盘硬件配置和行大小。。

8、为什么不建议用分区表，而推荐分库分表呢？

风险方面：

• 分区表的分区数量如果特别多的时候，在MySQL第一次加载分区表时，可能会超过linux的open file limit上限，提示打开文件过多的问题。
• 分区表在增加、删除分区时要执行alter语句，需要获取MDL锁，这是一把全局锁，可能会对业务产生慢查的影响。
• 接上一条，获取MDL锁时，如果表上有大事务，会导致Alter拿不到MDL写锁而进入等待状态，等待期间会阻塞后续所有读写请求。

扩展性方面：

• 分区表仅在单集群内分区，单集群的吞吐量上限没有变化。分库分表可以分布在多个集群上，可以提高整体的吞吐量。
• 分区规则确定后，如需更改代价很大。
• 分区表对索引有一定要求，例如主键必须包含分区键等，因此将普通表转成分区表时，要考虑主键变化是否存在业务层面的风险。