分区、分表、分片

1. 分区

• 一张表数据分成N个块，在逻辑上看只是一张表，但底层由N个物理区块组成
• 突破磁盘的读写能力，从而达到提高mysql性能的目的
• 特点
  • 数据库自身特性
  • 无法跨库，可跨存储设备
  • 数据库自身保证数据完整性，一致性
• 缺点
  • 分区数限制：1024
  • 不支持外键
• mysql5开始支持分区
  • mysql表由三个文件组成：.myd数据文件，.myi索引文件，.frm表结构文件
  • 例

1. 1.  TABLE `access_log` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `access_time` datetime NOT NULL,
        PRIMARY KEY (`id`,`access_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      50100 PARTITION BY RANGE (to_days(access_time))
      (PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
       PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
       PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) ;
       
      -- 新增分区
      alter table access_log add partition(
          partition p4 values less than (to_days('20190105'))
      );
      -- 删除分区
      alter table access_log drop partition p1;
      -- 拆分分区
      alter table access_log reorganize partition p4 into(
          -> partition s0 values less than(to_days('20190104')),
          -> partition s1 values less than(to_days('20190105'))
          -> );
      -- 合并分区
      alter table access_log reorganize partition s0,s1 into (
          partition p4 values less than (to_days('20190105'))
      );

 

• 分区类型
  • Range
    • 连续区间
  • List
    • 枚举值集合
  • Hash
    • 只能针对整数进行Hash
    • 单列
  • Key
    • 多列
• 常见问题
  • A PRIMARY KEY must include all columns in the table's partitioning function：这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区
  • MAXVALUE can only be used in last partition definition：RANGE表分区后不能带MAXVALUE分区，否则无法增加分区。或者就只能重新分区了

1.  table access_log partition by range(to_days(access_time))(
   partition p1 values less than (to_days('20191202')),
   partition p2 values less than (to_days('20191203')),
       partition po values less than (maxvalue)
   )
   • Table has no partition for value 737425：因为分区的范围没有包含所有可能的记录的值

2. 分表

• 类型
  • 水平分表
  • 垂直分表
• 优点
  • 单表并发提高，磁盘I/O性能提高，写效率提高
• 访问量大，且表数据比较大时，使用分表
• 缺点
  • 需要业务处理，较复杂
  • sharding-JDBC，MyCat

3. 分片（分库）

• 场景
  • 单台DB存储空间不足
  • 高并发场景下，当数据库Master无法承载写操作压力时，为提高数据库写入能力
• 目的
  • 突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题
• 拆分种类
  • 水平分库
    • 将表数据拆分到不同的数据库中
    • 解决单表数据量增长出现的读写压力
    • 表结构相同
  • 垂直分库
    • 按功能模块拆分
    • 解决表之间的IO竞争
• 要求
  • 数据分布均匀
  • 负载均衡
  • 数据迁移少

4. 分区与分片区别

• Scale Out - 横向扩展，添加机器达到很好的扩展性
• Scale Up - 纵向扩展，添加更多的CPU，存储设备，内存时达到很好扩展性