MySQL（十四）------- MySQL分区

最新推荐文章于 2022-09-15 14:41:44 发布

TheGodsHand

最新推荐文章于 2022-09-15 14:41:44 发布

阅读量302

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/Coding___Man/article/details/85275940

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

分区是根据一定的规则把数据库中的一张表分解成多个更小的、更容易管理的部分，这些部分作为一个独立的对象可以存放在不同的地方。对于用户来说，访问表里的数据跟不分区没什么差别，但是对于数据库本身及其管理维护来说有很多好处：

和单个磁盘或者文件系统相比，分区可以存储更多的数据；
优化查询。在where字句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及SUM() 和 COUNT()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总结果即可；
对于已经过期或者不需要保存的数据，可以删除与这些数据有关的分区来快速删除数据；
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

一、分区概述

分区有利于管理非常大的表，将大表分区存储分成小块；查看当前MySQL是否支持分区：

MySQL5.1版本开始支持分区功能，MySQL5.6版本之前使用以下命令查看是否支持分区
mysql> show variables like '%partition%';
+-----------------------+-------------+
| Variable_name         |   Value     |                                                                                
+-----------------------+-------------+
| have_partition_engine |   YES       |         YES表明支持分区
+-----------------------+-------------+
1 row in set (0.00 sec)

MySQL5.6及其之后的版本使用以下命令查看
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

关键字partition的值为ACTIVE表明支持分区

MySQL支持MyISAM、InnoDB、Memory等存储引擎创建分区表，不支持MERGE、CSV存储引擎来创建分区表；另外，同一张表的不同分区存储引擎必须一致。指定引擎的语句（engine=innodb）必须在分区语句（partition by ...）之前。

二、分区类型

主要有以下几种：

RANGE分区：分区的值是连续的，不间断；比如1~10，11~20，...
LIST分区：分区的值是离散的、无序的；比如（1，3），（2，6），（4，5），......
HASH分区：给定分区的个数，利用一定的规则将数据分到各个区中；
KEY分区：类似于HASH分区；
COLUMNS分区：MySQL5.5版本后新引入的，可细分为RANGE COLUMNS，LIST COLUMNS；主要解决的是RANGE分区和LIST分区只支持整型作为分区键的局限性；

	RANGE分区	LIST分区	HASH分区	KEY分区	RANGE COLUMNS分区	LIST COLUMNS分区
分区键的类型	INT类型	INT类型	INT类型	除BLOB、TEXT类型外的其它类型	整型、日期型、字符串型	整型、日期型、字符串型
分区键的选用	表中有主键或唯一键时必须选该列作为分区字段，如果没有则选择满足条件的即可
分区名	分区名字对大小写不敏感，不能用大小写来区分同一个分区名

有主键但是不用主键分区键时会报错：

mysql> create table emp(id INT not null primary key,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
       partition by range (store_id) (
                   partition p0 values less than (10),
                   partition p1 values less than (20),
                   partition p2 values less than (30));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

当去掉主键后就可成功创建
mysql> create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
       partition by range (store_id) (
                   partition p0 values less than (10),
                   partition p1 values less than (20),
                   partition p2 values less than (30));
Query OK, 0 rows affected (0.03 sec)

2.1 Range分区

比如上面的的例子，成功创建了表emp，按照字段store_id进行分区，一共分为三个区，小于10的在p0区，10~19的在p1区，20~29的在p2区；区间要连续而且不能相互重叠。

但如果此时有一条大于30的数据插入，应该放在哪个区呢？，显然，这时候会出错，因为系统也不知道该把这个数据放到哪个区里：

mysql> insert into emp values(1,'tom','1982-01-23','2001-02-03','clerk',50);
ERROR 1526 (HY000): Table has no partition for value 50

那这时候应该怎么办呢？增加一个分区，当值超过指定范围时都放入到该分区里面来：

增加p3分区，操过p2分区最大值的数据将全放到该分区中
mysql> alter table emp add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into emp values(1,'tom','1982-01-23','2001-02-03','clerk',50);
Query OK, 1 row affected (0.00 sec)

前面还提到Range分区的分区键必须是整型，比如上面的store_id是int，但并不绝对，你也可以使用表达式或函数来将一个不是整型的字段处理后返回一个整型，这样也可，比如：

创建一个表emp_date，使用年份来分区
mysql> create table emp_date(id INT not null,
                             ename varchar(30),
                             hired date not null default '1970-01-01',
                             separated date not null default '9999-12-31',
                             job varchar(30) not null,
                             store_id int not null) 
       partition by range (YEAR(separated)) ( 
                             partition p0 values less than (1995),
                             partition p1 values less than (2000),
                             partition p2 values less than (2005));
Query OK, 0 rows affected (0.03 sec)

使用这样转换的方式可以达到效果，但也有缺陷，比如日期类型的数据只有YEAR()、MONTH()、TO_DAYS()、TO_SECONDS()等转换函数可用，如果在此之外无法转换的该怎么办呢？MySQL5.5版本之后提供了新的解决办法，使用RANGE COLUMNS分区，它支持多种类型作为分区键：

mysql> drop table emp_date;
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp_date(id INT not null,
                             ename varchar(30), 
                             hired date not null default '1970-01-01',
                             separated date not null default '9999-12-31',
                             job varchar(30) not null,
                             store_id int not null) 
       partition by range columns(separated) (
                             partition p0 values less than ('1996-01-01'),
                             partition p1 values less than ('2001-01-01'),
                             partition p2 values less than ('2006-01-01'));
Query OK, 0 rows affected (0.03 sec)

RANGE分区功能特别适合以下两种情况：

当需要删除过期的数据时，只需要用一个简单的删除分区语句就可以将某个日期前的分区删掉，这对于大表来说比用DELETE删除查询的数据要快很多；
经常运行包含分区键的查询，这样MySQL就可以只扫描包含该数据的分区，提高了效率；例如查emp表中store_id大于25的记录，MySQL只需扫描p2、p3分区即可：

mysql> explain partitions select count(1) from emp where store_id >= 25 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

2.2 LIST分区

LIST分区跟RANGE分区类似，只不过它用的是离散值，相当于枚举，如果数据不在分区的集合中，那么它就不在该分区，LIST分区不必声明特定的顺序：

mysql> create table expenses(expense_date date not null,
                             category int,
                             amount decimal(10,3)) 
       partition by list(category) (
                             partition p0 values in (3,5),
                             partition p1 values in (1,10),
                             partition p2 values in (4,9),
                             partition p3 values in (2),
                             partition p4 values in (6));
Query OK, 0 rows affected (0.05 sec)

用LIST分区创建的表在插入数据时比如能在所有分区的集合中找得到，否则它就无法插入；它不存在像RANGE分区中那样定义一个maxvalue来囊括范围外的值。

同样，LIST分区的分区键只能是INT类型，如果想要用别的类型需使用LIST COLUMNS来创建：

mysql> create table expenses(expense_date date not null,
                             category varchar(30),
                             amount decimal(10,3)) 
       partition by list columns (category) (
                             partition p0 values in ('lind','food'),
                             partition p1 values in ('fight','begin'),
                             partition p2 values in ('lisa','mon'),
                             partition p3 values in ('talk'),
                             partition p4 values in ('fees'));
Query OK, 0 rows affected (0.08 sec)

2.3 Columns 分区

前面也介绍了一些该分区的相关内容，它主要解决的就是RANGE分区和LIST分区只支持整型作为分区键导致如果需要用别的类型时需要额外的函数计算得到整数或通过额外的转换表来转换为整数再分区的问题。

COLUMNS分区支持的数据类型：

所有整型：tinyint, smallint, mediumint, int, bigint；其它数值类型不支持，如Decimal和Float；
日期时间类型：date和datetime；
字符类型：char, varchar, binary, varbinary；不支持blob和text类型；

对比RANGE和LIST分区，COLUMNS分区除了支持的数据类型增加之外，它还支持多列划分，例如下面的例子：

1. 创建表cr3，使用a，b两列作为分区键：
mysql> create table rc3(a int,b int) 
       partition by range columns (a,b) (
                 partition p01 values less than (0,10),
                 partition p02 values less than (10,10),
                 partition p03 values less than (10,20),
                 partition p04 values less than (10,35),
                 partition p05 values less than (10,maxvalue),
                 partition p06 values less than (maxvalue,maxvalue));
Query OK, 0 rows affected (0.11 sec)

2. 插入数据（1，10），可以看到插入到p02中去了
mysql> insert into rc3 values(1,10);
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,10             |          1 |
| p03  | `a`,`b` | 10,20             |          0 |
| p04  | `a`,`b` | 10,35             |          0 |
| p05  | `a`,`b` | 10,MAXVALUE       |          0 |
| p06  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
6 rows in set (0.00 sec)

3. 插入数据（10，9），可以看到也插入p02分区中了
mysql> insert into rc3 values(10,9);
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,10             |          2 |
| p03  | `a`,`b` | 10,20             |          0 |
| p04  | `a`,`b` | 10,35             |          0 |
| p05  | `a`,`b` | 10,MAXVALUE       |          0 |
| p06  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
6 rows in set (0.00 sec)

4. 插入数据（10，10），可以看到被插入p03中了
mysql> insert into rc3 values(10,10);
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,10             |          2 |
| p03  | `a`,`b` | 10,20             |          1 |
| p04  | `a`,`b` | 10,35             |          0 |
| p05  | `a`,`b` | 10,MAXVALUE       |          0 |
| p06  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
6 rows in set (0.00 sec)

总结：这种分区键的比较其实就是多列排序，先根据a字段排序，再根据b字段排序；(a, b)插入到哪个分区时它是这么比较的：先判断p01分区 (a < 0) OR ((a =0) AND (b < 10))，如果成立则放在p01分区，如果不成立再检查p02分区 (a < 10) OR ((a = 10) AND (b < 10))，依次类推。例如插入数据（10，10）时是这么比较：p01、p02都不满足，到p03时(10 < 10) OR ((10 = 10) AND (10 < 20))成立，因此存放到p03分区。

2.4 HASH分区

HASH分区主要用来分散热点读，确保数据在预先定义的分区中尽量平均分布。MySQL支持两种HASH分区，常规HASH分区和线性HASH分区；下面分别说明一下在这两种情况下插入数据时如何确定插入的是哪个分区。

常规HASH分区：

1. 用hash分区创建表emp，分为4个区
mysql> create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
       partition by hash (store_id) partitions 4;
Query OK, 0 rows affected (0.07 sec)

2. 插入了一条数据234，可以看到它被插入了p2分区中
mysql> insert into emp values (1,'tom','2010-10-10','9999-12-31','clerk',234);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select * from emp where store_id = 234 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

确定数据插入到哪个分区中的规则是取余法，比如上面的234插入有4个分区的表中，余数N = MOD(234, 4) = 2，因此插入到第二个分区里面；再例如229，余数N = MOD(229, 4) = 1，因此它会被插入到第1个分区中。

这样的规则可以确保数据比较均匀的插入到定义的多个分区中，但是每当插入\更新\删除一行数据时，PARTITION BY HASH(expr）里面的expr都需要重新计算一次，这会引起性能问题，因此MySQL也不推荐使用涉及多列的哈希表达式。

常规HASH分区有一个严重弊端，就是增加分区或者合并分区的时候，原来的数据需要重新计算重新分区，这样做的代价非常大。比如原来4个分区，都存了一些数据，现在要增加一个分区到5个分区，那么原来的余数是0~3，现在的余数是0~4，原来存储的数据都需要重新计算并存储到新区里面。线性HASH分区就是来解决这个问题的。

线性HASH分区：在创建时只需加一个关键字linear

mysql> create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
        partition by linear hash (store_id) partitions 4;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into emp values (1,'tom','2010-10-10','9999-12-31','clerk',234);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select * from emp where store_id = 234 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

线性HASH分区的规则是线性的2的幂的运算法则。具体规则如下：

N = F(column_list) & (V - 1) ;  N表示分区的位置，F(column_list)就是插入的数据值，V表示2的幂值
V = Power(2, Ceiling(Log(2, num))) ; num表示总的分区个数
当N < num时，N是多少就放到第几个分区；
当N > num时，V = Ceiling(V/2), N = N & (V - 1) .

例如上面的例子，4个分区，插入数值234，这样计算得到V = Power(2, Ceiling(Log(2, 4))) = Power(2, Ceiling(2)) = Power(2, 2) = 4，N = 234 & 3 = (1101010 & 0000011 = 10) = 2 ; 2小于4，所以直接放入第二个分区。

特例，当线性HASH分区的个数是2的N次幂时，线性HASH分区的结果和常规HASH分区的结果是一致的。

线性HASH分区的优点是在分区维护（增加、删除、合并、拆分分区）时，MySQL能处理得更加迅速；缺点是线性HASH各个分区之间的数据分布不太均衡。

2.5 KEY分区

KEY分区域HASH分区很类似，主要的区别有三点：

KEY分区不允许使用用户自定义的表达式，而HASH分区可以；
KEY分区支持除BLOB、TEXT外的数据类型作为分区键，而HASH分区只支持整型；
KEY分区可以不指定分区键，前提是需要有主键或唯一键，而HASH分区必须指明分区键。

创建语句如下：使用job字段将表emp分为4个区
mysql> create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
       partition by key (job) partitions 4;
Query OK, 0 rows affected (0.07 sec)

不指定分区键时默认用主键，没有主键时默认用唯一键（必须非空，否则报错），当唯一键也没有时就会报错，此时不能省略分区键
mysql> create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null,
       primary key (id)) partition by key () partitions 4;
Query OK, 0 rows affected (0.06 sec)

注意：在KEY分区的分区的分区表上不能够执行“ ALTER TABLE DROP PRIMARY KEY"来删除主键，会报错。

同样，使用关键字LINEAR创建线性分区，作用与规则和HASH分区一样。

2.6 子分区

子分区（subpartition）是分区表中对每个分区再次进行分割，又称为复合分区；它可以在分区的基础上再次进行分区，两次分区的类型可以不相同。

mysql> create table ts(id int,purchased date) 
       partition by range(year(purchased)) 
       subpartition by hash(to_days(purchased)) subpartitions 2 
       ( partition p0 values less than (1990),
         partition p1 values less than (2000),
         partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.10 sec)

表ts有3个range分区，这三个range分区（p0、p1、p2）又被进一步分割成2个hash分区，故整个表一共有6个分区。

复合分区适用于保存非常大量的数据记录。

2.7 MYSQL分区处理NULL值的方式

MySQL不禁止在分区键值上使用NULL，但是，对于null值的处理，不同的分区方式处理方式不同。

RANGE分区中NULL值会被当做最小值来处理；
LIST分区中NULL值必须出现在枚举列表中，否则不被接受；
HASH/KEY分区中NULL值会被当做0值来处理。

range分区中null值处理举例：可以看到null值被放在最小分区中。

mysql> create table tb_range(id int,name varchar(5)) 
       partition by range(id) (
                 partition p0 values less than (-6),
                 partition p1 values less than (0),
                 partition p2 values less than (1),
                 partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tb_range values (null,'null');
Query OK, 1 row affected (0.01 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='tb_range';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | -6       |          1 |
| p1   | id   | 0        |          0 |
| p2   | id   | 1        |          0 |
| p3   | id   | MAXVALUE |          0 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

LIST分区中null值处理举例：可以看到null值必须在枚举列表中存在，否则不被插入。

mysql> create table tb_list(id int,name varchar(5)) partition by list(id) (partition p0 values in (0),partition p1 values in (1));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb_list values (null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL

mysql> drop table tb_list;
Query OK, 0 rows affected (0.03 sec)

mysql> create table tb_list(id int,name varchar(5)) partition by list(id) (partition p0 values in (0,null),partition p1 values in (1));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb_list values (null,'null');
Query OK, 1 row affected (0.01 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='tb_list';
+------+------+--------+------------+
| part | expr | descr  | table_rows |
+------+------+--------+------------+
| p0   | id   | NULL,0 |          1 |
| p1   | id   | 1      |          0 |
+------+------+--------+------------+
2 rows in set (0.00 sec)

HASH分区中null值处理举例：可以看到null值被当做0值插入到第一个分区中。

mysql> create table tb_hash(id int,name varchar(5)) partition by hash(id) partitions 2;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb_hash values (null,'null');
Query OK, 1 row affected (0.01 sec)

mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='tb_hash';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | id   | NULL  |          1 |
| p1   | id   | NULL  |          0 |
+------+------+-------+------------+
2 rows in set (0.00 sec)

由于不同的分区类型对null值的处理不一样，因此，为了避免在处理NULL值时出现误判，推荐在设置字段时通过非空约束和默认值来绕开NULL值带来的麻烦。

三、分区管理

MySQL支持对分区进行添加、删除、重定义、合并、拆分分区的命令，都是通过ALTER TABLE来实现。

3.1 RANGE & LIST分区管理

由于在删除、添加、重定义分区的处理上，这两个类型很相似，所以合并来说。

首先准备好表和数据：

mysql> create table emp_date(id INT not null,
    ->                              ename varchar(30),
    ->                              hired date not null default '1970-01-01',
    ->                              separated date not null default '9999-12-31',
    ->                              job varchar(30) not null,
    ->                              store_id int not null)
    ->        partition by range (YEAR(separated)) (
    ->                              partition p0 values less than (1995),
    ->                              partition p1 values less than (2000),
    ->                              partition p2 values less than (2005),
    ->                              partition p3 values less than (2015));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into emp_date values
    -> (7499,'allen','1981-02-20','2003-08-03','salesman',30),
    -> (7521,'al','1981-02-22','1993-09-01','salesman',30),
    -> (7566,'alfj','1981-04-02','2000-08-01','manager',20),
    -> (7654,'alli','1981-09-28','2012-12-31','salesman',30),
    -> (7698,'adby','1981-05-02','1998-09-08','manager',30),
    -> (7782,'asy','1981-09-02','2007-08-01','manager',10),
    -> (7788,'aofxd','1981-09-12','2012-05-01','analyst',20),
    -> (7839,'aloxd','1981-09-02','2011-09-03','president',10),
    -> (7844,'alsef','1981-09-28','2010-12-31','salesman',30),
    -> (7876,'ancsr','1981-09-12','2000-01-01','clerk',20),
    -> (7900,'anvuzy','1981-09-12','2004-09-02','clerk',30),
    -> (7902,'aksad','1981-09-12','2010-10-31','analyst',20),
    -> (7934,'ffgjg','1981-09-12','2011-12-31','clerk',10);
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> create table expenses(expense_date date not null,
    ->                              category int,
    ->                              amount decimal(10,3))
    ->        partition by list(category) (
    ->                              partition p0 values in (3,5),
    ->                              partition p1 values in (1,10),
    ->                              partition p2 values in (4,9),
    ->                              partition p3 values in (2),
    ->                              partition p4 values in (6));
Query OK, 0 rows affected (0.07 sec)

操作类型	RANGE分区	LIST分区
删除分区 ALTER TABLE DROP PARTITION	mysql> alter table emp_date drop partition p2; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0	mysql> alter table expenses drop partition p3; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
	检查发现无论是表结构还是数据均被删除了 mysql> show create table emp_date \G ************************* 1. row ************************* Table: emp_date Create Table: CREATE TABLE `emp_date` ( `id` int(11) NOT NULL, `ename` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job` varchar(30) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /!50100 PARTITION BY RANGE (YEAR(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB) / 1 row in set (0.00 sec) mysql> select * from emp_date where separated between '2000-01-01' and '2004-12-31'; Empty set (0.00 sec)	mysql> show create table expenses \G ************************* 1. row ************************* Table: expenses Create Table: CREATE TABLE `expenses` ( `expense_date` date NOT NULL, `category` int(11) DEFAULT NULL, `amount` decimal(10,3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /!50100 PARTITION BY LIST (category) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB, PARTITION p4 VALUES IN (6) ENGINE = InnoDB) / 1 row in set (0.01 sec)
	当删除p2分区后插入一条原本应该放在p2分区的数据，发现此时存放在p3分区了，这说明了range分区哪怕删除一个也还要保持分区的连续性 mysql> select * from emp_date where separated between '2000-01-01' and '2004-12-31'; Empty set (0.00 sec) mysql> select partition_name part,partition_expression expr, partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='emp_date'; +------+-----------------+-------+------------+ \| part \| expr \| descr \| table_rows \| +------+-----------------+-------+------------+ \| p0 \| YEAR(separated) \| 1995 \| 0 \| \| p1 \| YEAR(separated) \| 2000 \| 0 \| \| p3 \| YEAR(separated) \| 2015 \| 7 \| +------+-----------------+-------+------------+ 3 rows in set (0.00 sec) mysql> insert into emp_date values (7566,'jons','1981-04-02','2000-08-01','manager',20); Query OK, 1 row affected (0.00 sec) mysql> select partition_name part, partition_expression expr,partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='emp_date'; +------+-----------------+-------+------------+ \| part \| expr \| descr \| table_rows \| +------+-----------------+-------+------------+ \| p0 \| YEAR(separated) \| 1995 \| 0 \| \| p1 \| YEAR(separated) \| 2000 \| 0 \| \| p3 \| YEAR(separated) \| 2015 \| 8 \| +------+-----------------+-------+------------+ 3 rows in set (0.00 sec)	当list分区的p3区被删除后，原来能插入数值2，现在不可以了
增加分区 ALTER TABLE ADD PARTITION	增加p4分区，注意只能从分区列表的最大端增加，比如这里增加的2030是大于2015的，所以能够成功 mysql> alter table emp_date add partition (partition p4 values less than (2030)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0	增加p5分区，注意增加的值不能在原有分区上出现过；比如这里的7、8都没有在原有分区上出现过，所以能成功 mysql> alter table expenses add partition (partition p5 values in (7,8)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
拆分分区 ALTER TABLE REORGANIZE PARTITION INTO	将p3分区(2000~2015)拆分为p2(2000~2005)分区和p3(2005~2015)分区 mysql> alter table emp_date reorganize partition p3 into ( partition p2 values less than (2005), partition p3 values less than (2015)); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0	将p2分区(4,9)拆分为p2(4)分区和p3(9)分区 mysql> alter table expenses reorganize partition p2 into (partition p2 values in (4), partition p3 values in (9)); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
合并分区 ALTER TABLE REORGANIZE PARTITION INTO	将p1、p2、p3三个分区合并为一个分区p1(1995~2015) mysql> alter table emp_date reorganize partition p1,p2,p3 into (partition p1 values less than (2015)); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0	将p3分区(9)和p4分区(6)合并为p3分区(9,6) mysql> alter table expenses reorganize partition p3,p4 into (partition p3 values in (9,6)); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
	注意：重新定义分区时（拆分、合并），只能够定义相邻的分区，并且拆分或合并前后分区的覆盖区间应相同且连续；另外，不能通过重定义分区来更改分区的类型，例如不可将range分区变为hash分区。	注意：重新定义分区时(拆分、合并)，只能够定义相邻的分区，并且拆分或合并前后分区的覆盖区间应相同；另外，不能通过重定义分区来更改分区的类型，例如不可将list分区变为hash分区。
	无法在原有分区上增加值的范围，比如想在p2分区(2000~2005)上将范围扩大是不可以的，但想在p4分区(2015~2030)上将范围扩大是可行的；连续性要求	单纯的使用add语句将p4 (6)调增为p4 (6,11)是不可行的，但可以通过新增分区然后再合并的方式来达到这样的目的。

操作类型

RANGE分区

LIST分区

删除分区

ALTER TABLE DROP PARTITION

mysql> alter table emp_date drop partition p2;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table expenses drop partition p3;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

检查发现无论是表结构还是数据均被删除了

mysql> show create table emp_date \G
*************************** 1. row ***************************
Table: emp_date
Create Table: CREATE TABLE `emp_date` (
`id` int(11) NOT NULL,
`ename` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job` varchar(30) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> select * from emp_date where separated between '2000-01-01'

and '2004-12-31';
Empty set (0.00 sec)

mysql> show create table expenses \G
*************************** 1. row ***************************
Table: expenses
Create Table: CREATE TABLE `expenses` (
`expense_date` date NOT NULL,
`category` int(11) DEFAULT NULL,
`amount` decimal(10,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (category)
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
PARTITION p4 VALUES IN (6) ENGINE = InnoDB) */
1 row in set (0.01 sec)

当删除p2分区后插入一条原本应该放在p2分区的数据，

发现此时存放在p3分区了，这说明了range分区哪怕删除一个也还要保持分区的连续性

mysql> select * from emp_date where separated between

'2000-01-01' and '2004-12-31';
Empty set (0.00 sec)

mysql> select partition_name part,partition_expression expr,

partition_description descr,table_rows from

information_schema.partitions where table_schema = schema()

and table_name='emp_date';
+------+-----------------+-------+------------+
| part | expr | descr | table_rows |
+------+-----------------+-------+------------+
| p0 | YEAR(separated) | 1995 | 0 |
| p1 | YEAR(separated) | 2000 | 0 |
| p3 | YEAR(separated) | 2015 | 7 |
+------+-----------------+-------+------------+
3 rows in set (0.00 sec)

mysql> insert into emp_date values

(7566,'jons','1981-04-02','2000-08-01','manager',20);
Query OK, 1 row affected (0.00 sec)

mysql> select partition_name part,

partition_expression expr,partition_description descr,

table_rows from information_schema.partitions

where table_schema = schema() and table_name='emp_date';
+------+-----------------+-------+------------+
| part | expr | descr | table_rows |
+------+-----------------+-------+------------+
| p0 | YEAR(separated) | 1995 | 0 |
| p1 | YEAR(separated) | 2000 | 0 |
| p3 | YEAR(separated) | 2015 | 8 |
+------+-----------------+-------+------------+
3 rows in set (0.00 sec)

当list分区的p3区被删除后，原来能插入数值2，现在不可以了

增加分区

ALTER TABLE ADD PARTITION

增加p4分区，注意只能从分区列表的最大端增加，比如

这里增加的2030是大于2015的，所以能够成功

mysql> alter table emp_date add partition (partition p4 values less than (2030));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

增加p5分区，注意增加的值不能在原有分区上出现过；

比如这里的7、8都没有在原有分区上出现过，所以能成功

mysql> alter table expenses add partition (partition p5 values in (7,8));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

拆分分区

ALTER TABLE REORGANIZE

PARTITION INTO

将p3分区(2000~2015)拆分为p2(2000~2005)分区和p3(2005~2015)分区

mysql> alter table emp_date reorganize partition p3 into (

partition p2 values less than (2005),

partition p3 values less than (2015));
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

将p2分区(4,9)拆分为p2(4)分区和p3(9)分区

mysql> alter table expenses reorganize partition

p2 into (partition p2 values in (4),

partition p3 values in (9));
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

合并分区

ALTER TABLE REORGANIZE

PARTITION INTO

将p1、p2、p3三个分区合并为一个分区p1(1995~2015)

mysql> alter table emp_date reorganize partition p1,p2,p3 into (partition p1 values less than (2015));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

将p3分区(9)和p4分区(6)合并为p3分区(9,6)

mysql> alter table expenses reorganize partition p3,p4 into (partition p3 values in (9,6));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

注意：重新定义分区时（拆分、合并），只能够定义

相邻的分区，并且拆分或合并前后分区的覆盖区间应

相同且连续；另外，不能通过重定义分区来更改分

区的类型，例如不可将range分区变为hash分区。

注意：重新定义分区时(拆分、合并)，只能够定义

相邻的分区，并且拆分或合并前后分区的覆盖区间应相同；另外，不能通过重定义分区来更改分

区的类型，例如不可将list分区变为hash分区。

无法在原有分区上增加值的范围，比如想在p2分区(2000~2005)上将范围扩大是不可以的，但想在p4分区(2015~2030)上将范围扩大是可行的；连续性要求

单纯的使用add语句将p4 (6)调增为p4 (6,11)是不可行的，但可以通过新增分区然后再合并的方式来达到这样的目的。

3.2 HASH & KEY 分区管理

在改变分区设置方面，HASH分区和KEY分区非常类似。

准备一个HASH分区表，分为4个区：

create table emp(id INT not null,
                        ename varchar(30),
                        hired date not null default '1970-01-01',
                        separated date not null default '9999-12-31',
                        job varchar(30) not null,
                        store_id int not null) 
       partition by hash (store_id) partitions 4;

删除两个分区（其实也可以理解为修改、合并），原有的4个分区变为2个：ALTER TABLE COALESCE PARTITION

mysql> alter table emp coalesce partition 2;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 2 */
1 row in set (0.01 sec)

增加分区ALTER TABLE ADD PARTITION：

mysql> alter table emp add partition partitions 8;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 10 */
1 row in set (0.00 sec)

注意：删除分区是在原有的基础上删除n个，比如上面的例子原有4个分区，删除了两个还剩2个分区；增加分区是在原有基础上增加n个，比如上面的例子在原有2个分区的基础上增加8个分区，因此一共有10个分区了。

TheGodsHand

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL（十四）------- MySQL分区

分区是根据一定的规则把数据库中的一张表分解成多个更小的、更容易管理的部分，这些部分作为一个独立的对象可以存放在不同的地方。对于用户来说，访问表里的数据跟不分区没什么差别，但是对于数据库本身及其管理维护来说有很多好处：和单个磁盘或者文件系统相比，分区可以存储更多的数据；优化查询。在where字句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及SUM...
复制链接

扫一扫