mysql之分区，分表，分库

Author Avatar
Sean Yu 12月 03, 2020
  • 在其它设备中阅读本文章

分区：物理数据库的设计方式，还是一张表，但是表的数据存储在不同的硬盘。

分表：最终由N张表，系统读写时需要通过一定的规则找到数据需要映射到的表然后进行操作。

分库：多个数据库，每个数据库中有多张表。

切分方式

垂直切分

简单来说就是竖着切，试想一下，把一个库中很多张表竖着切，这些表就会散开。其实垂直切分就是这个意思，将不同模块的表放到不同的数据库中。
比如支付的放在支付数据库，用户的放在用户数据库，会员的放在会员数据库等等，可以减少耦合性。
常见有 垂直分库 和 垂直分表 两种。

垂直分库

就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与”微服务治理”的做法相似，每个微服务使用单独的一个数据库。

垂直分表

  • 基于数据库中的”列”进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。
  • 在字段很多的情况下（例如一个大表有100多个字段），通过”大表拆小表”，更便于开发与维护，也能避免跨页问题。
  • MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。
  • 另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

优点

  • 解决业务系统层面的耦合，业务清晰
  • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点

  • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题（需要水平切分）

水平切分

将某张访问非常频繁的表，按照某个特定的规则（通常是某个字段进行hash），然后将数据分散到多个表，甚至是多个数据库中，这样每张表或者每张库都含有一部分数据。

优点

  • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
  • 应用端改造较小，不需要拆分业务模块

缺点

  • 跨分片的事务一致性难以保证
  • 跨库的join关联查询性能较差
  • 数据多次扩展难度和维护量极大

分区

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的，而没有全局索引。

MySQL在创建表时使用PARTITIONBY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查找包含需要数据的分区就可以了。

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

在下面的场景中，分区可以起到非常大的作用：

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
  • 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
  • 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
  • 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表本身也有一些限制，下面是其中比较重要的几点：

  • 一个表最多只能有1024个分区。
  • 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区。
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
  • 分区表中无法使用外键约束。

分区原理

SELECT
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT
当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。

DELETE
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

UPDATE
当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

分区类型

RANGE分区
基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。本例中使用to_days函数

-- DATETIME分区
CREATE TABLE my_range_datetime(
    id INT,
    hiredate DATETIME
) 
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，
    PARTITION p11 VALUES LESS THAN (MAXVALUE) 
);

-- TIMESTAMP分区
CREATE TABLE my_range_timestamp (
    id INT,
    hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
);

p11是一个默认分区，所有大于20171211的记录都会在这个分区。MAXVALUE是一个无穷大的值。p11是一个可选分区。如果在定义表的没有指定的这个分区，当我们插入大于20171211的数据的时候，会收到一个错误。

我们在执行查询的时候，必须带上分区字段。这样可以使用分区剪裁功能.

LIST 分区
LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败，这点和其它的分区不一样，RANGE分区会将其作为最小分区值存储，HASH\KEY分为会将其转换成0存储，主要LIST分区只支持整形，非整形字段需要通过函数转换成整形.

create table t_list( 
　　a int(11), 
　　b int(11) 
　　)(partition by list (b) 
　　partition p0 values in (1,3,5,7,9), 
　　partition p1 values in (2,4,6,8,0) 
　　);

Hash 分区
我们在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大。为了把这类的数据进行分区打散mysql 提供了hash分区。基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作，所以不建议使用复杂的表达式这样会影响性能。

CREATE TABLE my_member (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    created DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(id)
PARTITIONS 4;

HASH分区可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，则默认分区数为1。

LINEAR HASH分区
LINEAR HASH分区是HASH分区的一种特殊类型，与HASH分区是基于MOD函数不同的是，它基于的是另外一种算法。它的优点是在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

CREATE TABLE my_members (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( id )
PARTITIONS 4;

KEY分区
KEY分区其实跟HASH分区差不多，不同点如下：

  • KEY分区允许多列，而HASH分区只允许一列。
  • 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
  • KEY分区对象必须为列，而不能是基于列的表达式。
  • KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。
CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,    
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
--在没有主键或者唯一键的情况下，格式如下：
CREATE TABLE tm1 (
    s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

如何使用分区表

这里需要再陈述一遍：在数据量超大的时候，B+Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。

这正是分区要做的事情。理解分区时还可以将其当作索引的最初形态，以代价非常小的方式定位到需要的数据在哪一片“区域”。

为了保证大数据量的可扩展性，一般有下面两个策略：

  • 全量扫描数据，不要任何索引。
  • 索引数据，并分离热点。

分区局限性

  • NULL值会使分区过滤无效
  • 分区列和索引列不匹配，会导致查询无法进行分区过滤。
  • 选择分区的成本可能很高
  • 打开并锁住所有底层表的成本可能很高
  • 维护分区的成本可能很高
  • 查询参数最好带上分区列。否则就得扫全表

分库分表

因为垂直切分没啥好讲的，这里我们主要说水平切分的分库分表，多库多表的场景：

切分规则

参考https://zhuanlan.zhihu.com/p/88896222 https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html

查询切分

首先数据库分片，将sharding key记录在一个单独的库中，你每次要查询数据库的时候，请先到mapping db里面去查一下你应该到那个数据库去拿数据。

image.png

优点：ID和库的Mapping算法可以随意更改。
缺点：引入额外的单点。

范围切分

按照范围来切分，比如说按照时间范围和ID的范围来进行切分
例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1 ~ 9999的记录分到第一个库，10000 ~ 20000的分到第二个库，以此类推。

image.png

优点：单表大小可控，天然水平扩展。
缺点：无法解决集中写入瓶颈的问题。

Hash切分

一般采用Mod来切分，下面着重讲一下Mod的策略。

image.png

优点：数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈
缺点：

  • 后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
  • 容易面临跨分片查询的复杂问题。如果频繁用到的查询条件中不带sharding key时，将会导致无法定位数据库，从而需要同时向多个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

找到一种均匀分配的一致性哈希算法，guava里有，可惜我看不懂：https://zhuanlan.zhihu.com/p/104124045

切分实现

最后说一下一般我们怎么做切分实现

客户端分片

  • 在应用层直接实现
    优点：实现简单
    缺点：业务入侵
  • 通过定制orm实现（mybatis增加表的sharding key）
    优点：实现简单
    缺点：业务入侵
  • 通过定制jdbc协议实现（shardingjdbc）
    优点：无入侵业务
    缺点：实现困难

代理分片

添加代理服务器去专门做分片

优点：无入侵代码，代理还可以通过心跳包等形式，保证节点的可用性
缺点：需要独立的代理，增加了一层网络开销

支持事务的分布式数据库

比如tidb

选择sharding key

尽量选择查询的列，且唯一，例如user_id，order_id等。

分库分表带来的问题

  • 分布式id（snowflake）
  • 分布式事务（tcc）
  • 跨区join
    • 全局表，每个 DataNode 上都有一份全量数据，例如一些数据字典表，数据很少修改，可以避免跨库 Join 的性能问题。
    • 数据同步，通过数据同步工具将 需要连表的数据同步到对应库中，比较依赖于同步工具的稳定性，如果同步有延迟，就会导致数据不一致，产生脏数据。
  • 跨库排序分页
    • 把范围扩大，分表sql上的limit x,y 变成 limit 0, x+y ，这样改写后，相当于分表中把”每页最后一条数据”之前的所有数据全都取出来了（当然：这里面可能会有不需要的多余数据)，然后内存中合并在一起，再取x偏移量后的y条数据。这样取数据太多，效率低
    • 在某1页的数据均摊到各分表的前提下
      • 原sql中的limit offset,pagesize 改写成 limit offset/n ,pagesize (注：n为分表个数，如果offset/n除不尽，向下取整，避免最后的结果丢数据）– 这个的意思，其实就是假设原表这一页的数据，会均分到各个分表(所以，我一再强调，前提是数据是均摊的，如果某个分表的记录很少，极端情况下，甚至是空的，这个就不对了，最终结果会少数据)
      • 分表上，执行改写后的sql，得到一堆结果集，然后找出这堆结果中的最小id (假设id是关键的排序字段)，记为min_id – 这一步的目的，是为了找出最小的起始点，保证第1页数据起点正确。
      • 各分表上的sql，where条件部分改写成 id between min_id and origin_max_id (注：origin_max_id为上一步，每个分表查询结果集中的最大值，显然min_id=自身最小id的那张分表，不用再重复查询) – 这一步的目的在于，因为 步骤1）查出来的结果，通常会比原表上该页的数据少，所以这里重新将起始点设置到正确的位置，即：min_id，再查1次，相当于范围扩大了，以保证数据不会丢。不过，这里有一个可优化的地方，仔细想想，这1次查询出来的结果，跟步骤1）中的查出来的结果，必然有一部分是重复的，因此改写部分，只需要 id between min_id and origin_min_id就可以了(origin_min_id 即为原来分表结果上的最小id)
      • 将上一步查询出来的结果，在内存中合并排序去重（注：如果上一步采用了优化方案，就应该是把1）与3）这二次查询的结果全取出来合并排序去重），然后从开始连续取pagesize条数据即可（注：offset/n除不尽的话，向下取整了，也就是起始点可能向前多移了，所以有可能开始的第1条记录，其实是上1页的最后1条记录，要追求精确的话，可以在应用层记录上一页最后1条记录的id，然后跟本次查询结果前1条记录对比，如果发现是一样的，开始取数据的位置，就要向后移1位，如果考虑id有重复的话，就要根据情况多移几位）
    • 避免使用 LIMIT 进行分页
      • 构建行记录数量与行偏移量的二级索引
      • 使用上次分页数据结尾 ID 作为下次查询条件的分页方式
  • 查询sql时，没有sharding key
    • 双写，用查询的条件在sharding一份，做数据冗余
    • 写一份查询条件和sharding key的映射，作为全局表，在进行二次查询

题外话，我以前一直觉得分库后，对limit，group by，join 都有影响，就希望能找到一个能不用多数据集归并，只利用索引操作的方式，但找了半天也找不到。
实际上这个方向就是错的，有取必有舍，高性能mysql上介绍，在巨大的数据量下，b+tree的索引基本上就是无效的，选择了分库缓解查询压力，就必须执行多步sql，并进行归并。
这里放一个sharding-jdbc的图，来帮助更好的理解分库分表的执行过程。

image.png