range

If there were you, the world would be just right

分表?
将单个大的数据库表进行拆分,拆分成多个数据表,然后用户访问的时候,根据一定的算法(hash/取模),让用户访问不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力,缩短查询时间提升了数据库访问性能。

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

分表缺点
查询汇总数据难度增大

分区

  • 逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件)
  • 一个表最多只能有1024个分区。
  • 如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。
    即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
  • 分区表中无法使用外键约束

分区类型

  • RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
  • LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

建立分区表注意:

  • 顺应MYSQL的要求,primary key和unique key必须包含在分区key的一部分 所以把分区字段加入到主键中,组成复合主键
  • 查询sql 必须走分区键,避免查所有区
  • 在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;
  • 插入数据,如果数据对应本该插入的分区不存在会报错
  • 单张表最多是只能有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) 无法直接定位数据在哪个分区

添加新评论 »

在这里输入你的评论...