Day.24 提升大数据资料管理 - 资料表分区 ( MYSQL Partition)

随着业务需求的增加，资料量会伴随着系统成长而增长。在储存的资料越来越多的况下，势必会产生一些存取效能上的问题。这时除了建立合适的索引之外，对於数据量多的资料表可透过将资料分割，表切分区的方式维护做查询优化，使查询时不但能缩小资料范围(只要搜寻指定范围内的分区资料)，还能方便管理旧资料的删除作业(指定分区删除)。

范例表结构:

CREATE TABLE `act` (
  `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
  `status` bigint(20) DEFAULT NULL COMMENT '活动状态 0(下架) 1(上架) 2(准备中)',
  `start_time` int(11) unsigned NOT NULL,
  `end_time` int(11) unsigned NOT NULL,
  `platform_id` bigint(20) DEFAULT NULL,
  `updated_at` int(11) unsigned NOT NULL,
  `created_at` int(11) unsigned NOT NULL,
  PRIMARY KEY (`actives_id`)
);

注意:

1. 当表有Primary key或unique index的状况下，使用於分区的栏位必须包含在其中，不然会无法建立。
   

2. 当要插入的数据值超出partition设定范围时是无法INSERT的。
   example.

介绍一下 MYSQL Partition以下种类:

• range

  • Range partition : 一般最常用的，根据定义的partition key做范围分区。

  Example. 针对actives_id定义范围做分区。

  ALTER TABLE act
  PARTITION BY RANGE (actives_id) (
      PARTITION p0 VALUES LESS THAN (5),
      PARTITION p1 VALUES LESS THAN (10),
      PARTITION p2 VALUES LESS THAN (15),
      PARTITION pmax VALUES LESS THAN MAXVALUE
  );
  

  • 根据以上设定，资料分区储存的范围(ps.目前表中已有10笔数据，看一下各笔资料分布。)

  查询分区内资料:

  SELECT * FROM table_name PARTITION (partition_name);
  

  分区名称	值范围
  p0	<5
  p1	5 ~ <10
  p2	10 ~ <15
  pmax	15之後的值

  

  • 当我们查询条件有包含到partition key时，语法分析中的partition栏位就会显示使用了哪个分区内的资料去过滤缩小资料范围的。

  

  • MYSQL数据目录下将资料表分区(前/後)资料储存的变化～
    

• list

  • List partition: 用选择栏位的值选项做资料分类的分区。

  Example. 使用act表中的status栏位(判断活动目前状态)做list分区。

  (1.) 先将Primary key改成复合式PK -> (actives_id,status)

  ALTER TABLE `t2`.`act` DROP PRIMARY KEY, ADD PRIMARY KEY (`actives_id`,     `status`);
  

  (2.) 新增list分区

  ALTER TABLE act
  PARTITION BY LIST (status) (
     PARTITION p0 VALUES IN (0),
     PARTITION p1 VALUES IN (1),
     PARTITION p2 VALUES IN (2),
     PARTITION p3 VALUES IN (4,5)
  );
  

  (3.) 测试资料确认

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'); 
  

  

• 分区使用，试一下比较差异点

删除单一分区(该分区内资料会被删除):

ALTER TABLE act DROP PARTITION p0;   //partition & 数据都被删除

mysql> SELECT * FROM act PARTITION (p0);
ERROR 1735 (HY000): Unknown partition 'p0' in table 'act'

移除表分区定义(现有资料不会被删除):

ALTER TABLE act REMOVE PARTITIONING;   //变回未分区前的表结构

查询各分区资讯:

语法:
select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'database_name' and table_name = 'table_name';

mysql> select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'active' and table_name = 'act';
+--------------+------------+----------------+------------------+-----------------------+------------+
| table_schema | table_name | partition_name | partition_method | partition_description | table_rows |
+--------------+------------+----------------+------------------+-----------------------+------------+
| t1l          | act        | p1             | RANGE            | 10                    |          5 |
| t1l          | act        | p2             | RANGE            | 15                    |          0 |
| t1l          | act        | pmax           | RANGE            | MAXVALUE              |          0 |
+--------------+------------+----------------+------------------+-----------------------+------------+
3 rows in set (0.01 sec)

今天介绍了几个分区使用，明天继续把剩下的介绍完～ 在来透过procedure实作我们的自动切表流程。