mysql数据表分区/分表场景优缺点及注意事项详解

数据库  /  管理员 发布于 1年前   395

什么是分表？

将单个大的数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，

根据一定的算法（hash/取模），让用户访问不同的表，这样数据分散到多个数据表中，

减少了单个数据表的访问压力，缩短查询时间提升了数据库访问性能。

场景1：
1000万用户user表，拆分为100个分表，命名 user_00 - user_99 ,通过用户id 取模的方法
把数据分散到这100个表中
场景2:
用户登录操作日志类，日流量高达500万+，通过使用日期分表方法，
将数据按日期保存表名 xxlog_20190918

分表缺点？

查询汇总数据难度增大

什么是分区？

1、逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件）

2、一个表最多只能有1024个分区。

3、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。

     即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

4、分区表中无法使用外键约束

分区类型

RANGE分区：

基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，

该表达式使用将要插入到表中的这些行的列值进行计算。

这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，

且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

建立分区表注意：

1、顺应MYSQL的要求，primary key和unique key必须包含在分区key的一部分 

     所以把分区字段加入到主键中，组成复合主键

2、查询sql 必须走分区键，避免查所有区

3、在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率;

4、插入数据，如果数据对应本该插入的分区不存在会报错

5、单张表最多是只能有1024个分区的（所以不按日做表）

分区选择

这个看个人业务，此处主要说我用到的

1、list 分区键必须为整数类型（ 弃用 ）

2、RANGE 区间范围（ 与日期相关的统一采用range ）

#维护相关
  查分区内容
    SELECT * FROM tableName PARTITION (p1,p2)
  添加分区：
     //只能添加大于分区键的分区 
    alter table tableName add partition (partition p0 values less than(1991));  
  删除分区数据
    alter table tableName  truncate partition p1,p2;  
    alter table tableName  truncate partition all; 
  删除分区
    alter table tableName drop partition p0; //可以删除任意分区

3、hash 分区键必须为整数类型（ 可用hash则不用key,userId 为整形选择hash作为用户表分区 ）

TIPS：
1）hash的分区必须是整数列
2）drop partition命令只能用在RANGE和LIST分区中。
3）分区的字段必须是要包含在主键字段之内，不然会报错
维护相关
  添加分区：
    ALTER TABLE tableName ADD PARTITION PARTITIONS 6; // 加6个区
  减少分区:
    ALTER TABLE tableName COALESCE PARTITION 4; // 减4个区
  移除表的分区
    ALTER TABLE tablename REMOVE PARTITIONING ;
注意：
使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，
后者会连同数据一起删除

4、key 分区键字段不限制为整数类型（ 设备为字符串可选择key分区 ）

TIPS：
1) KEY分区支持除text和BLOB之外的所有数据类型的分区
2) 数据会分布不均（多次测试结果，创建分区个数为奇数 如99，可使数据相对分布更均匀）
3) 无法直接定位数据在哪个分区