分库分表常见问题和解决方案

置顶泡^泡

已于 2024-10-29 23:25:45 修改

阅读量9k

点赞数 13

分类专栏： MySQL 文章标签：数据库全局唯一id 数据迁移方案分库分表详解

于 2022-01-02 00:12:20 首次发布

本文链接：https://blog.csdn.net/xiaowanzi_zj/article/details/122270389

版权

MySQL 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

MySQL出现的性能问题

表数据量过大
sql查询太复杂
sql查询没走索引
数据库服务器的性能过低等

Mysql常见的优化手段

增加索引，索引是直观也是最快速优化检索效率的方式。
基于Sql语句的优化，比如最左匹配原则，用索引字段查询、降低sql语句的复杂度等。
表的合理设计，比如符合三范式、或者为了一定的效率破坏三范式设计等。
数据库参数优化，比如并发连接数、数据刷盘策略、调整缓存大小。
数据库服务器硬件升级。
mysql搭建主从复制方案，实现读写分离。

大数据表优化方案

对于大数据表的优化最直观的方式就是减少单表数据量，所以常见的解决方案是:

分库分表，大表拆小表。
冷热数据分离，所谓的冷热数据，其实就是根据访问频次来划分的，访问频次较多的数据是热数据，访问频次少的数据是冷数据。冷热数据分离就是把这两类数据分离到不同的表中，从而减少热数据表的大小。
历史数据归档，简单来说就是把时间比较久远的数据分离出来存档，保证实时库的数据的有效生命周期。(磁带等，低价的存储介质)

详解分库分表

分库分表是非常常见针对单个数据表数据量过大的优化方式，它的核心思想是把一个大的数据表拆分成多个小的数据表，这个过程也叫（数据分片），它的本质其实有点类似于传统数据库中的分区表，比如mysql和oracle都支持分区表机制。

分库分表是一种水平扩展手段，每个分片上包含原来总的数据集的一个子集。这种分而治之的思想在技术中很常见，比如多CPU、分布式架构、分布式缓存等等，像前面我们讲redis cluster集群时，slot槽的分配就是一种数据分片的思想。

如图6-1所示，数据库分库分表一般有两种实现方式：
在这里插入图片描述

垂直拆分

垂直拆分有两种，一种是单库的垂直拆分，另一种是多个数据库的垂直拆分。

单库垂直分表

单个表的字段数量建议控制在20~50个之间，之所以建议做这个限制，是因为如果字段加上数据累计的长度超过一个阈值后，数据就不是存储在一个页上，就会产生分页的问题，而这个问题会导致查询性能下降。

所以如果当某些业务表的字段过多时，我们一般会拆去垂直拆分的方式，把一个表的字段拆分成多个表，如图6-2所示，把一个订单表垂直拆分成一个订单主表和一个订单明细表。
在这里插入图片描述
在Innodb引擎中，单表字段最大限制为1017，参考mysql官网。

多库垂直分表

多库垂直拆分实际上就是把存在于一个库中的多个表，按照一定的纬度拆分到多个库中，如图6-3所示。这种拆分方式在微服务架构中也是很常见，基本上会按照业务纬度拆分数据库，同样该纬度也会影响到微服务的拆分，基本上服务和数据库是独立的。
在这里插入图片描述
多库垂直拆分最大的好处就是实现了业务数据的隔离。其次就是缓解了请求的压力，原本所有的表在一个库的时候，所有请求都会打到一个数据库服务器上，通过数据库的拆分，可以分摊掉请求，在这个层面上提升了数据库的吞吐能力。

水平拆分

垂直拆分的方式并没有解决单表数据量过大的问题，所以我们还需要通过水平拆分的方式把大表数据做数据分片。

水平切分也可以分成两种，一种是单库的，一种是多库的。

单库水平分表

如图6-4所示，表示把一张有10000条数据的用户表，按照某种规则拆分成了4张表，每张表的数据量是2500条。
在这里插入图片描述
两个案例：
银行的交易流水表，所有进出的交易都需要登记这张表，因为绝大部分时候客户都是查询当天的交易和一个月以内的交易数据，所以我们根据使用频率把这张表拆分成三张表：

当天表：只存储当天的数据。

当月表：我们在夜间运行一个定时任务，前一天的数据，全部迁移到当月表。用的是insert intoselect，然后delete。

历史表：同样是通过定时任务，把登记时间超过30天的数据，迁移到history历史表（历史表的数据非常大，我们按照月度，每个月建立分区）。

费用表： 消费金融公司跟线下商户合作，给客户办理了贷款以后，消费金融公司要给商户返费用，或者叫提成，每天都会产生很多的费用的数据。为了方便管理，我们每个月建立一张费用表，例如fee_detail_201901…fee_detail_201912。

但是注意，跟分区一样，这种方式虽然可以一定程度解决单表查询性能的问题，但是并不能解决单机存储瓶颈的问题。

多库水平分表

多库水平分表，其实有点类似于分库分表的综合实现方案，从分表来说是减少了单表的数据量，从分库层面来说，降低了单个数据库访问的性能瓶颈，如图6-5所示。
在这里插入图片描述

常见的水平分表策略

哈希取模分片

哈希分片，其实就是通过表中的某一个字段进行hash算法得到一个哈希值，然后通过取模运算确定数据应该放在哪个分片中，如图6-6所示。这种方式非常适合随机读写的场景中，它能够很好的将一个大表的数据随机分散到多个小表。
在这里插入图片描述

hash取模的问题

hash取模运算有个比较严重的问题，假设根据当前数据表的量以及增长情况，我们把一个大表拆分成了4个小表，看起来满足目前的需求，但是经过一段时间的运行后，发现四个表不够，需要再增加4个表来存储，这种情况下，就需要对原来的数据进行整体迁移，这个过程非常麻烦。

一般为了减少这种方式带来的数据迁移的影响，我们会采用一致性hash算法。

一致性hash算法

https://blog.csdn.net/xiaowanzi_zj/article/details/121092285

按照范围分片

按范围分片，其实就是基于数据表的业务特性，按照某种范围拆分，这个范围的有很多含义，比如：

时间范围，比如我们按照数据创建时间，按照每一个月保存一个表。基于时间划分还可以用来做冷热数据分离，越早的数据访问频次越少。
区域范围，区域一般指的是地理位置，比如一个表里面存储了来自全国各地的数据，如果数据量较大的情况下，可以按照地域来划分多个表。
数据范围，比如根据某个字段的数据区间来进行划分。

如图6-7所示，表示按照数据范围进行拆分。
在这里插入图片描述

分库分表实战

假设存在一个用户表，用户表的字段如下。
该表主要提供注册、登录、查询、修改等功能。
在这里插入图片描述
该表的具体的业务情况如下（需要注意，在进行分表之前，需要了解业务层面对这个表的使用情况，然后再决定使用什么样的方案，否则脱离业务去设计技术方案是耍流氓）。

用户端： 前台访问量较大，主要涉及两类请求：

用户登录，面向C端，对可用性和一致性要求较高，主要通过login_name、email、phone来查询用户信息，1%的请求属于这种类型。
用户信息查询，登录成功后，通过uid来查询用户信息，99%属于这种类型。

运营端： 主要是运营后台的信息访问，需要支持根据性别、手机号、注册时间、用户昵称等进行分页查询，由于是内部系统，访问量较低，对可用性一致性要求不高。

根据uid进行水平分表

由于99%的请求是基于uid进行用户信息查询，所以毫无疑问我们选择使用uid进行水平分表。那么这里我们采用uid的hash取模方法来进行分表，具体的实施如图6-9所示，根据uid进行一致性hash取模运算得到目标表进行存储。

在这里插入图片描述
按照图6-9的结构，分别复制user_info表，重新命名为01~04，如图6-10所示。

如何实现全局唯一ID

数据库自增ID（定义全局表）
UUID
Redis的原子递增
Twitter-Snowflake算法
美团的leaf
MongoDB的ObjectId
百度的UidGenerator

分布式ID的特性

唯一性：确保生成的ID是全局唯一的。
有序递增性：确保生成的ID是对于某个用户或者业务是按一定的数字有序递增的。
高可用性：确保任何时候都能正确的生成ID。
带时间：ID里面包含时间，一眼扫过去就知道哪天的数据。

数据库自增方案

在数据库中专门创建一张序列表，利用数据库表中的自增ID来为其他业务的数据生成一个全局ID，那么每次要用ID的时候，直接从这个表中获取即可。

CREATE TABLE `uid_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`business_id` int(11)  NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE (business_type)
)

在应用程序中，每次调用下面这段代码，就可以持续获得一个递增的ID。

begin;
REPLACE INTO uid_table (business_id) VALUES (2);
SELECT LAST_INSERT_ID();
commit;

其中，replace into是每次删除原来相同的数据，同时加1条，就能保证我们每次得到的就是一个自增的ID。

这个方案的优点是非常简单，它也有缺点，就是对于数据库的压力比较大，而且最好是独立部署一个DB，而独立部署又会增加整体的成本。

优点：

非常简单，利用现有数据库系统的功能实现，成本小，有DBA专业维护。
ID号单调自增，可以实现一些对ID有特殊要求的业务。

缺点:

强依赖DB，当DB异常时整个系统不可用，属于致命问题。配置主从复制可以尽可能的增加可用性，但是数据一致性在特殊情况下难以保证。主从切换时的不一致可能会导致重复发号。
ID发号性能瓶颈限制在单台MySQL的读写性能。

UUID

UUID的格式是： xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 8-4-4-4-12共36个字符，它是一个128bit的二进制转化为16进制的32个字符，然后用4个 - 连接起来的字符串。

UUID的五种生成方式:

基于时间的UUID（date-time & MAC address）：主要依赖当前的时间戳及机器mac地址，因此可以保证全球唯一性。（使用了Mac地址，因此会暴露Mac地址和生成时间。）
分布式安全的UUID（date-time & group/user id）将版本1的时间戳前四位换为POSIX的UID或GID。
基于名字空间的UUID-MD5版（MD5 hash & namespace），基于指定的名字空间/名字生成MD5散列值得到，标准不推荐。
基于随机数的UUID（pseudo-random number）：基于随机数或伪随机数生成。
基于名字空间的UUID-SHA1版（SHA-1 hash & namespace）：将版本3的散列算法改为SHA1。

在Java中，提供了基于MD5算法的UUID、以及基于随机数的UUID。
优点:

本地生成，没有网络消耗，生成简单，没有高可用风险。

缺点:

不易于存储：UUID太长，16字节128位，通常以36长度的字符串表示，很多场景不适用。
信息不安全：基于MAC地址生成UUID的算法可能会造成MAC地址泄露，这个漏洞曾被用于寻找梅丽莎病毒的制作者位置。
无序查询效率低：由于生成的UUID是无序不可读的字符串，所以其查询效率低。
UUID不适合用来做数据库的唯一ID，如果用UUID做主键，无序的不递增，大家都知道，主键是有
索引的，然后mysql的索引是通过b+树来实现的，每一次新的UUID数据的插入，为了查询的优
化，都会对索引底层的b+树进行修改，因为UUID数据是无序的，所以每一次UUID数据的插入都会对主键的b+树进行很大的修改，严重影响性能。

雪花算法

SnowFlake 算法，是 Twitter 开源的分布式 id 生成算法。其核心思想就是：使用一个 64 bit 的 long 型的数字作为全局唯一 id。雪花算法比较常见，在百度的UidGenerator、美团的Leaf中，都有用到雪花算法的实现。

如图6-11所示，表示雪花算法的组成，一共64bit，这64个bit位由四个部分组成。

第一部分， 1bit位，用来表示符号位，而ID一般是正数，所以这个符号位一般情况下是0。
第二部分， 占41 个 bit：表示的是时间戳，是系统时间的毫秒数，但是这个时间戳不是当前系统的时间，而是当前 系统时间-开始时间 ，更大的保证这个ID生成方案的使用的时间！
那么我们为什么需要这个时间戳，目的是为了保证有序性，可读性,我一看我就能猜到ID是什么时候生成的。

41位可以2 41 - 1表示个数字，
如果只用来表示正整数（计算机中正数包含0），可以表示的数值范围是：0 至 2 41 -1，减1
是因为可表示的数值范围是从0开始算的，而不是1。
也就是说41位可以表示2 41 -1个毫秒的值，转化成单位年则是(2 41 -1)/1000 * 60 * 60 * 24
*365=69年，也就是能容纳69年的时间

第三部分， 用来记录工作机器id，id包含10bit，意味着这个服务最多可以部署在 2^10 台机器上，也就是 1024 台机器。
其中这10bit又可以分成2个5bit，前5bit表示机房id、5bit表示机器id，意味着最多支持2^5个机房（32），每个机房可以支持32台机器。
第四部分， 第四部分由12bit组成，它表示一个递增序列，用来记录同毫秒内产生的不同id。

那么我们为什么需要这个序列号，设想下，如果是同一毫秒同一台机器来请求，那么我们怎么保证他的唯一性，这个时候，我们就能用到我们的序列号，

目的是为了保证同一毫秒内同一机器生成的ID是唯一的，这个其实就是为了满足我们ID的这个高
并发，就是保证我同一毫秒进来的并发场景的唯一性。

12位（bit）可以表示的最大正整数是2^12-1=4095，即可以用0、1、2、3、…4094这4095个数字，来表示同一机器同一时间截（毫秒)内产生的4095个ID序号。

12位2进制，如果全部都是1的情况下，那么最终的值就是4095，也就是12bit能够存储的最大的数字是4095.

在这里插入图片描述

优点：

毫秒数在高位，自增序列在低位，整个ID都是趋势递增的。
作为DB表的主键，索引效率高。
不依赖数据库等第三方系统，以服务的方式部署，稳定性更高，生成ID的性能也是非常高的。
高性能高可用：生成时不依赖于数据库，完全在内存中生成。
容量大，每秒中能生成数百万的自增ID。
可以根据自身业务特性分配bit位，非常灵活。

缺点：

强依赖机器时钟，如果机器上时钟回拨，会导致发号重复或者服务会处于不可用状态。
不是严格全局递增的。
原标准实现代码中是直接抛异常，短暂停止对外服务，这样在实际生产中是无法忍受的。

时钟回拨问题

由于雪花算法是依赖于服务器的时间的，所以如果机器发生了故障或者别的情况，对服务器的时间进行了回拨，那么会导致生成的ID可能发生重复。

解决方案

保存过去一段时间内每一台机器在当前这一毫秒产生的ID的最大值，比如使用Map形式，就是<machine_id,max_id>，这样如果某台机器发生了时钟回拨，直接在这台机器对应的max_id的基础上继续自增生成ID即可。(自己存一下使用过的时间戳，每次新建ID的时候用新时间戳和使用过的最大时间戳对比)
不依赖机器时钟驱动，就没时钟回拨的事儿了。即定义一个初始时间戳，在初始时间戳上自增，不跟随机器时钟增加。时间戳何时自增？当序列号增加到最大时，此时时间戳+1，这样完全不会浪费序列号，适合流量较大的场景，如果流量较小，可能出现时间断层滞后。
依然依赖机器时钟，如果时钟回拨范围较小，如几十毫秒，可以等到时间回到正常；如果流量不大，前几百毫秒或者几秒的序列号肯定有剩余，可以将前几百毫秒或者几秒的序列号缓存起来，如果发生时钟回拨，就从缓存中获取序列号自增。
java版雪花算法:

public class SnowflakeIdWorker {

    /**
     * 起始的时间戳,2020-01-01 00:00:00
     */
    private final static long START_TIMESTAMP = 1577808000000L;

    /**
     * 数据中心占用的位数
     */
    private final static long DATACENTER_ID_BIT = 5;

    /**
     * 工作机器标识占用的位数
     */
    private final static long WORKER_ID_BIT = 5;

    /**
     * 序列号占用的位数
     */
    private final static long SEQUENCE_BIT = 12;

    /**
     * 支持的数据中心最大值
     */
    private final static long MAX_DATACENTER_ID = ~(-1L << DATACENTER_ID_BIT);

    /**
     * 支持的工作机器标识最大值
     */
    private final static long MAX_WORKER_ID = ~(-1L << WORKER_ID_BIT);

    /**
     * 支持的序列号最大值
     */
    private final static long MAX_SEQUENCE = ~(-1L << SEQUENCE_BIT);

    /**
     * 工作机器标识向左移12位
     */
    private final static long WORKER_ID_LEFT = SEQUENCE_BIT;

    /**
     * 数据中心向左移(12+5)位
     */
    private final static long DATACENTER_ID_LEFT = SEQUENCE_BIT + WORKER_ID_BIT;

    /**
     * 时间戳向左移(12+5+5)位
     */
    private final static long TIMESTAMP_LEFT = DATACENTER_ID_LEFT + DATACENTER_ID_BIT;

    /**
     * 数据中心
     */
    private final long datacenterId;

    /**
     * 工作机器标识
     */
    private final long workerId;

    /**
     * 序列号
     */
    private long sequence = 0L;

    /**
     * 上一次时间戳
     */
    private long lastTimestamp = -1L;

    /**
     * 构造函数
     *
     * @param datacenterId 数据中心 (0~31)
     * @param workerId    工作机器标识 (0~31)
     */
    public SnowflakeIdWorker(long datacenterId, long workerId) {
        if (datacenterId > MAX_DATACENTER_ID || datacenterId < 0) {
            throw new IllegalArgumentException("datacenterId can't be greater than MAX_DATACENTER_ID or less than 0");
        }
        if (workerId > MAX_WORKER_ID || workerId < 0) {
            throw new IllegalArgumentException("workerId can't be greater than MAX_WORKER_ID or less than 0");
        }
        this.datacenterId = datacenterId;
        this.workerId = workerId;
    }

    /**
     * 获取下一个ID (该方法是线程安全的)
     *
     * @return long SnowflakeId
     */
    public synchronized long nextId() {
        long currentTimestamp = currentTimeMillis();
        //如果当前时间小于上一次ID生成的时间戳，说明系统时钟回退过这个时候应当抛出异常
        if (currentTimestamp < lastTimestamp) {
            throw new RuntimeException("Clock moved backwards.  Refusing to generate id");
        }

        //如果是同一时间生成的，则进行毫秒内序列
        if (currentTimestamp == lastTimestamp) {
            //相同毫秒内，序列号自增
            sequence = (sequence + 1) & MAX_SEQUENCE;
            //同一毫秒的序列数已经达到最大
            if (sequence == 0L) {
                currentTimestamp = nextTimeMillis();
            }
        } else {
            //不同毫秒内，序列号置为0
            sequence = 0L;
        }

        //上次生成ID的时间戳
        lastTimestamp = currentTimestamp;

        //移位并通过或运算拼到一起组成64位的ID
        //时间戳部分
        return (currentTimestamp - START_TIMESTAMP) << TIMESTAMP_LEFT
                //数据中心部分
                | datacenterId << DATACENTER_ID_LEFT
                //工作机器标识部分
                | workerId << WORKER_ID_LEFT
                //序列号部分
                | sequence;
    }

    /**
     * 阻塞到下一个毫秒，直到获得新的时间戳
     *
     * @return 当前时间戳
     */
    private long nextTimeMillis() {
        long mill = currentTimeMillis();
        while (mill <= lastTimestamp) {
            mill = currentTimeMillis();
        }
        return mill;
    }

    /**
     * 返回以毫秒为单位的当前时间
     *
     * @return 当前时间(毫秒)
     */
    private long currentTimeMillis() {
        return System.currentTimeMillis();
    }

    public static void main(String[] args) {
        SnowflakeIdWorker snowflake = new SnowflakeIdWorker(2, 3);

        for (int i = 0; i < (1 << 10); i++) {
            System.out.println(snowflake.nextId());
        }
    }

}

美团Leaf

DROP TABLE IF EXISTS `leaf_alloc`;

CREATE TABLE `leaf_alloc` (
  `biz_tag` varchar(128)  NOT NULL DEFAULT '' COMMENT '业务key',
  `max_id` bigint(20) NOT NULL DEFAULT '1' COMMENT '当前已经分配了的最大id',
  `step` int(11) NOT NULL COMMENT '初始步长，也是动态调整的最小步长',
  `description` varchar(256)  DEFAULT NULL COMMENT '业务key的描述',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据库维护的更新时间',
  PRIMARY KEY (`biz_tag`)
) ENGINE=InnoDB;