分类

  1. RANGE 分区
    1. 根据连续区间值切分数据
  2. LIST 分区
    1. 根据枚举值切分数据
  3. HASH 分区
    1. 对整数求模划分数据
  4. KEY 分区
    1. 对任何数据类型求模切分数据

注意点

和 mycat 切分规则没有关系

  • 各分各的，mycat 区分集群，表分区区分存储位置

分区 key 和主键关系

  • 分区 key 必须包含在 PRIMARY KEY 或者 UNIQUE KEY 中

单独的表分区创建语句无法存在

  • 要么前面跟一个表定义
  • 要么用 ALTER TABLE 来给表创建分区

查看分区表信息

  1. SELECT
  2.     PARTITION_NAME,                 -- major
  3.     PARTITION_METHOD,
  4.     PARTITION_EXPRESSION,        -- major
  5.     PARTITION_DESCRIPTION,
  6.     TABLE_ROWS,                            -- major
  7.     SUBPARTITION_NAME,
  8.     SUBPARTITION_METHOD,
  9.     SUBPARTITION_EXPRESSION
  10. FROM information_schema.`PARTITIONS`
  11. WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME="<table_name>";

创建表分区

Range 分区

  • 按照范围分区

    例子

  • 按照主键范围分区

    • 很少用
  • 按照月份分区 ```sql CREATE TABLE t_range ( id INT UNSIGNED, name VARCHAR(200) NOT NULL, judge_id DATE UNSIGNED NOT NULL, — 分区 字段 PRIMARY KEY (id, judge_id) — 分区 key 必须包含在 PRIMARY KEY 或者 UNIQUE KEY 中 )

— 表分区 根据连续区间值切分数据 PARTITION BY RANGE(MONTH(birthday)) ( — 指定数据将要划分到指定位置 — 该位置必须让 mysql 具有 chown 权限 PARTITION part1 VALUES LESS THEN(6) DATA DIRECTORY=’/mnt/p0/data’, PARTITION part2 VALUES LESS THEN(12) DATA DIRECTORY=’/mnt/p1/data’ )

  2. <a name="rYEAX"></a>
  3. ### LIST 分区
  5. - 用于按需分区
  6. <a name="LU1fz"></a>
  7. #### 例子
  8. ```sql
  9. CREATE TABLE t_list (
  10.     id INT UNSIGNED,
  11.     name VARCHAR(200) NOT NULL,
  12.     judge_id INT UNSIGNED NOT NULL,    -- 枚举值分片字段
  13.     PRIMARY KEY (id, judge_id)    -- 分区 key 必须包含在 PRIMARY KEY 或者 UNIQUE KEY 中
  14. )
  16. -- 表分区 枚举值切分指定数据
  17. PARTITION BY LIST(judge_id) (
  18.     -- 指定数据将要划分到指定位置
  19.   -- 该位置必须让 mysql 具有 chown 权限
  20.     PARTITION part1 VALUES IN (1,2)
  21.         DATA DIRECTORY='/mnt/p0/data',
  22.     PARTITION part2 VALUES IN (3,4)
  23.         DATA DIRECTORY='/mnt/p1/data'
  24. )

  • 插入几条数据

  • 查看对应硬盘是否有数据 ```shell [root@localhost p0]# tree . ├── data │ └── test │ └── t_list#P#part1.ibd └── lost+found

3 directories, 1 file

  2. <a name="uAkJ8"></a>
  3. ### HASH 分区
  5. - 用于均匀数据
  6. <a name="7Smgl"></a>
  7. #### 支持类型
  9. - 支持很多类型
  10. - 可以对整数，或者函数运算结果是整数的字段
  11.    - 即只要 `HASH(<condition>)` 中只要 `condition` 为整数即可
  13. <a name="63Bv6"></a>
  14. #### 和 Mycat 的主键求模区别
  16. - 仅支持整数
  18. <a name="Bpn66"></a>
  19. #### 例子
  20. ```sql
  21. CREATE TABLE t_hash (
  22.     id INT UNSIGNED,
  23.     name VARCHAR(200) NOT NULL,
  24.     judge_id INT UNSIGNED NOT NULL,    -- hash 字段
  25.     PRIMARY KEY(id, judge_id) -- 分区 key 必须包含在 PRIMARY KEY 或者 UNIQUE KEY 中
  26. )
  27. -- 表分区 对整数求模划分数据
  28. -- 后面 enum 是取模
  29. PARTITION BY HASH(judge_id) PARTITIONS 2(
  30.     PARTITION p0 
  31.         DATA DIRECTORY="/mnt/p0/data",
  32.     PARTITION p1 
  33.         DATA DIRECTORY="/mnt/p1/data"
  34. )

KEY 分区

  • 比 hash 更好的适应分区键
  • 也可用于均匀字段
  • 支持所有类型
    • 整数
    • 日期
    • 时间戳
    • 字符串
    • 二进制

      例子

      ```sql CREATE TABLE t_key ( id INT UNSIGNED, name VARCHAR(200) NOT NULL, judge_id VACHAR(32) NOT NULL, — key 字段 PRIMARY KEY(id, judge_id) )

— 表分区 对任何数据类型求模切分数据 PARTITION BY KEY (judge_id)( PARTITION p0 DATA DIRECTORY=”/mnt/p0/data”, PARTITION p1 DATA DIRECTORY=”/mnt/p1/data” )

  2. ---
  4. <a name="I47Jk"></a>
  5. ## 管理表分区
  7. - 都是使用 `ALERT TABLE` 语句
  8. <a name="UaDVb"></a>
  9. ### 创建
  10. ```sql
  11. ALTER TABLE <表名> PARTITION BY <分区类型>(<分区字段or值>) [<额外选项>](
  12.       PARTITION <自定义分区名> <分区表达式>
  13.       [DATA DIRECTORY="<分区表存储位置>"],
  14.       ...
  15.  )

添加

  • db 会自动迁移分区数据
    1. ALTER TABLE <表名> ADD PARTITION (
    2.    PARTITION <自定义分区名> <分区表达式>
    3.     [DATA DIRECTORY="<分区表存储位置>"],
    4.     ...
    5. )

切分和合并

  • db 会自动迁移分区数据

    切分

    1. ALTER TABLE <表名> REORGANIZE PARTITION <老分区名> INTO (
    2.    PARTITION <新分区名> <分区表达式>
    3.     [DATA DIRECTORY="<分区表存储位置>"],
    4.     ...
    5. )

合并

  1. ALTER TABLE <表名> REORGANIZE PARTITION <老分区名1>,  ..., <老分区名n> INTO (
  2.      PARTITION <新分区名> <分区表达式>
  3.       [DATA DIRECTORY="<分区表存储位置>"],
  4. )

删除

删除数据

  • 仅支持 RANGE 和 LIST 分区
  • 会删除表空间和数据！
  • 如果仅有一个表空间，将无法 DROP 掉
    • 先删除表空间
    • 再删除表
      1. ALTER TABLE <表名> DROP PARTITION <分区名1>, ... <分区名n>;

仅删除表空间

  • 仅剔除表空间，不会删除数据
    1. ALTER TABLE <表名> REMOVE PARTITIONING ;

子分区

  • 在已有的分区上再创建分区

    限制

  • 可创建子分区的分区类型: RANGE, LIST

  • 子分区类型限制: HASH, KEY
  • 分区的子分区数量必须相同

语句

  • 和普通的 PARTITION sql 语句有所区别

    创建

    1. -- 方式一
    2. ALTER TABLE <表名> PARTITION BY <ANGE,  LIST分区类型>(<分区字段or值>) [<额外选项>]
    3. -- 分区的子分区数量必须相同
    4. SUBPARTITION BY <HASH, KEY 分区类型>(<分区字段or值>) SUBPARTITIONS <子分区大小>
    5. (
    6.     PARTITION <自定义分区名> <分区表达式>
    7.     [DATA DIRECTORY="<分区表存储位置>"],
    8.     ...
    9. )
    11. -- 方式二
    12. ALTER TABLE <表名> PARTITION BY <ANGE,  LIST分区类型>(<分区字段or值>) [<额外选项>]
    13. (
    14.     PARTITION <自定义分区名> <分区表达式>
    15.     [DATA DIRECTORY="<分区表存储位置>"] 
    16.     -- 分区的子分区数量必须相同, 各个分区下定义的子分区要相同
    17.     (SUBPARTITION <子分区名1>, ..., SUBPARTITION <子分区名n>),
    18.     ...
    19. )

其他操作

  • 只要在要操作的分区带上 (SUBPARTITION <子分区名1>, ..., SUBPARTITION <子分区名n>) 即可

查看分区表信息

  1. SELECT
  2.     PARTITION_NAME,                 -- major
  3.     PARTITION_METHOD,
  4.     PARTITION_EXPRESSION,        -- major
  5.     PARTITION_DESCRIPTION,
  6.     TABLE_ROWS,                            -- major
  7.     SUBPARTITION_NAME,
  8.     SUBPARTITION_METHOD,
  9.     SUBPARTITION_EXPRESSION
  10. FROM information_schema.`PARTITIONS`
  11. WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME="<table_name>";